Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
 
For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))
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
Y113 - Python and SQL databases
  [2745] Connecting Python to sqlite and MySQL databases - (2010-04-28)
  [2746] Model - View - Controller demo, Sqlite - Python 3 - Qt4 - (2010-04-29)
  [2786] Factory methods and SqLite in use in a Python teaching example - (2010-05-29)
  [2790] Joining a MySQL table from within a Python program - (2010-06-02)
  [3136] A framework with python - Django - first steps - (2011-01-17)
  [3139] Steering our Python courses towards wxPython, SQLite and Django - (2011-01-19)
  [4086] Cacheing class for Python - using a local SQLite database as a key/value store - (2013-05-14)
  [4436] Accessing a MySQL database from Python with mysql.connector - (2015-02-21)
  [4445] Graphing presentations in Python - huge data, numpy and matplotlib - (2015-02-28)
  [4535] SQLAlchemy - first examples with a Python Object Relationship Mapping system - (2015-10-14)
  [4537] example of SQLite using a local database file through SQLalchemy - (2015-10-14)

S200 - MySQL - SQLite
  [2561] The future of MySQL - (2010-01-03)
  [2744] PyQt (Python and Qt) and wxPython - GUI comparison - (2010-04-28)
  [3099] Perl - database access - DBD, DBI and DBIx modules - (2010-12-22)
  [3269] Files or Databases? MySQL, SQLite, or Oracle? - (2011-04-28)
  [4006] Ruby / SQLite3 example program, showing JOIN v LEFT JOIN - (2013-02-16)
  [4007] Which database should I use? MySQL v SQLite - (2013-02-16)


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., 2024: 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/4024_SQL ... ample.html • PAGE BUILT: Sun Oct 11 16:07:41 2020 • BUILD SYSTEM: JelliaJamb