Main Content

SQL databases from Python - an SQLite example

Archive - Originally posted on "The Horse's Mouth" - 2013-03-02 06:22:59 - Graham Ellis

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].