Showmedo is financed by a subscription club, membership of which grants access to all of our 160+ specially produced videos. These include our 50 video introduction to Python programming, and our recently completed Open-office migration collection.
 Erik Thompson
Hi! I am currently a full time student at California State University Long Beach. I recently began to do research in molecular dynamics. I have interests in Python programming, physics, chemistry, and 3D simulation.
Also I like bird watching! Here are some of photos I've taken:
SQLite from Python: Database Basics [ID:973] (25/35)
 Erik Thompson
Hi! I am currently a full time student at California State University Long Beach. I recently began to do research in molecular dynamics. I have interests in Python programming, physics, chemistry, ...
Our authors tell us that feedback from you is a big motivator. Please take a few moments to let them know what you think of their work.
(Showmedo is undergoing major changes. To
report any problems viewing the videos please
email us and include
browser and OS specifics. Cheers - Kyran.)
SQLite is a database with support built into python 2.5 and greater. It is notable for storing the data into a very small, portable file while using the same basic SQL syntax used by much larger, and not so portable, databases. SQLite has been released to the public domain so there are no licensing issues to worry about when using it.
SQLite is good for new users learning about what databases are all about and it's good for small single-user applications. Firefox, for instance, uses SQLite to store much of its data.
Below is the python code used in the video for you to play around with.
python sqlite example.
sqlite is included with python 2.5 and later.
basic database vocabulary:
DATABASE: stores one or more tables of data (for use by your programs)
TABLE: stores data in columns (fields) and rows (records)
RECORD: a row of data from the table
FIELD: a specific column in a table/record
SQL: structured query language used to manipulate and retrieve data from a database.
sql syntax is very similar (often identical) for different databases (eg. sqlite,
mysql, sqlserver, access, oracle all use sql)
DATABASE FRONT-END: allows you to browse your data and create/modify your
tables graphically (without sql)
PRIMARY KEY: data that is unique to a record of data (often an automatically
FOREGIN KEY: the primary key of one table entered as a regular field in
This is used to relate those two tables.
For example, consider two tables: a customers table storing
customer info and an orders table storing order info.
the customers id (primary key) can be entered as a regular
field (foreign key) in the orders table to match a particular order
with a particular customer.
CREATE TABLE : create a table
INSERT : insert a record into a table
UPDATE : edit one or more previously inserted record, usually used with a
DELETE : delete one or more previously inserted record, usually used with a
SELECT : retrieve specified fields from one or more previously inserted records,
often used with a WHERE clause
WHERE : filters the records affected by the UPDATE, DELETE, or SELECT commands
based on some condition
JOIN : used to select data from more than one table at a time such as when the
foreign key from one table matches the primary key of another
connection = sqlite3.connect('mydata.db')
cursor = connection.cursor()
sql = """CREATE TABLE Settings (id INTEGER PRIMARY KEY AUTOINCREMENT,
#sql = "INSERT INTO Settings (setting, value) VALUES ('slideshow_delay', '15')"
#sql = "INSERT INTO Settings (setting, value) VALUES ('display_molecule_name', 'True')"
#sql = "UPDATE Settings SET value=15 WHERE setting='slideshow_delay'"
#sql = "DELETE FROM Settings WHERE id=1"
#sql = "SELECT setting, id, value"
#print cursor.lastrowid # get the autoincremented id from the previously inserted record
data = cursor.fetchall()
Are you learning Python? To keep the site running clean and ad-free we've started Club ShowMeDo. The club will allow you access to some special videos we're making to pass on programming skills we consider important to the development and enjoyment of Free Open Source Software (FOSS).
The focus at the moment is on developing with the Python programming language, one of the backbones of the FOSS movement with a huge number of included and third-party libraries.
We welcome feedback and we want to hear your ideas.
Showmedo's development is fairly rapid and bugs will inevitably creep in. If you have any problems please drop us a line using the contact address below. Likewise, any suggestions for improvements to the site are gratefully received.