Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
 
Python and Tcl - public course schedule [here]
Private courses on your site - see [here]
Please ask about maintenance training for Perl, PHP, Lua, etc
 
SQL databases from Python - an SQLite example

There's a new example of relational database use in Python [here] on our site - from the Python course I ran last week. If you cut and paste the script onto your system, it should run with little fuss (and without needing a separate database engine) as it uses sqlite - the built in SQL database - as its engine.

Run the script with the word "seed" as your parameter:
  munchkin:pf13 grahamellis$ python databases_python seed
  Sample select using a program generated query
  (u'Python Programming', 3)
  (u'Intermediate Python', 3)
  Total of 2 records selected
  Sample select using a join of 2 tables
  (1, u'Python Programming', 3, 3, u'Wednesday')
  (2, u'Intermediate Python', 3, 3, u'Wednesday')
  (3, u'Feeding your Python', 2, 2, u'Tuesday')
  Total of 3 records selected

and the database file and tables are created, populated, and reported all in a single run. Run it without a parameter, and it will report on the data. With a numeric parameter, it reports all courses over that length that are in our simple little database:
  munchkin:pf13 grahamellis$ python databases_python 2
  Sample select using a program generated query
  (u'Python Programming', 3)
  (u'Intermediate Python', 3)
  Total of 2 records selected
  Sample select using a join of 2 tables
  (1, u'Python Programming', 3, 3, u'Wednesday')
  (2, u'Intermediate Python', 3, 3, u'Wednesday')
  (3, u'Feeding your Python', 2, 2, u'Tuesday')
  Total of 3 records selected


Sqlite is a good testing and development database - and for many applications will be all you need for production code too. A great way to store "files" of data, but providing the facilities that you'll need for a database type system such as handling data by fields, easy edits, joins and other selects, and the whole thing accessed via SQL strings. It's not a separate database engine, but it acts like one ... and that will make it easy later on for you to move - should you wish - to a different database. Wrap your sqlite calls in a thin common wrapper / access the data via a common class, and all you need to do is install your full database and then extend that class or re-implement the API for the new database. See [here] for an example of MySQL used from Python.

A note for newcomers to SQL and databases. It looks simple, it IS straightforward to use - but it's vital that you learn about designing and using databases before you start filling tables with data, or writing too much live code. There's just a few simple calls to be made from Python, but get the design (and thus the calls) wrong, and your users can invest weeks and months of work on data entry to produce something that's hard for them to use and maintain. We have a whole section on designing an SQL database [here], and indeed a complete and separate MySQL course [here].
(written 2013-03-02)

 
Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
S200 - MySQL - SQLite
  [4007] Which database should I use? MySQL v SQLite - (2013-02-16)
  [4006] Ruby / SQLite3 example program, showing JOIN v LEFT JOIN - (2013-02-16)
  [3269] Files or Databases? MySQL, SQLite, or Oracle? - (2011-04-28)
  [3139] Steering our Python courses towards wxPython, SQLite and Django - (2011-01-19)
  [3099] Perl - database access - DBD, DBI and DBIx modules - (2010-12-22)
  [2786] Factory methods and SqLite in use in a Python teaching example - (2010-05-29)
  [2746] Model - View - Controller demo, Sqlite - Python 3 - Qt4 - (2010-04-29)
  [2745] Connecting Python to sqlite and MySQL databases - (2010-04-28)
  [2744] PyQt (Python and Qt) and wxPython - GUI comparison - (2010-04-28)
  [2561] The future of MySQL - (2010-01-03)

Y113 - Python and SQL databases
  [4537] example of SQLite using a local database file through SQLalchemy - (2015-10-14)
  [4535] SQLAlchemy - first examples with a Python Object Relationship Mapping system - (2015-10-14)
  [4445] Graphing presentations in Python - huge data, numpy and matplotlib - (2015-02-28)
  [4436] Accessing a MySQL database from Python with mysql.connector - (2015-02-21)
  [4086] Cacheing class for Python - using a local SQLite database as a key/value store - (2013-05-14)
  [3136] A framework with python - Django - first steps - (2011-01-17)
  [2790] Joining a MySQL table from within a Python program - (2010-06-02)


Back to
Melksham to Chippenham (Station) bus services - times and routes
Previous and next
or
Horse's mouth home
Forward to
Backups, Codebase, Strategy and more - dealing with forum incidents
Some other Articles
Really Simple Class and Inheritance example in Python
Collections in Python - list tuple dict and string.
The coffee of the Stars comes to Melksham
Backups, Codebase, Strategy and more - dealing with forum incidents
SQL databases from Python - an SQLite example
Melksham to Chippenham (Station) bus services - times and routes
A request for all of Melksham transport groups to work integrated
Spike solution, refactored and reusable, Python - Example
Melksham Community - Annual Report / MCAP
Coffee Shop in Melksham
4759 posts, page by page
Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96 at 50 posts per page


This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2019: 404 The Spa • Melksham, Wiltshire • United Kingdom • SN12 6QL
PH: 01225 708225 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/4024_SQL ... ample.html • PAGE BUILT: Sat May 27 16:49:10 2017 • BUILD SYSTEM: WomanWithCat