Name: [116] Erik Thompson
Member: 111 months
Authored: 44 videos
Description: 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: http://ww ...

SQLite from Python: Database Basics [ID:973] (25/35)

in series: Developing emol!

video tutorial by Erik Thompson, added 01/09

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

A good front-end for your SQLite database files is the SQLite-Manager firefox extension:

Here is a link to SQLite which includes some documentation:

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
             created integer)
FOREGIN KEY: the primary key of one table entered as a regular field in
             another table.
              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.

sql basics:
CREATE TABLE : create a table
INSERT : insert a record into a table
UPDATE : edit one or more previously inserted record, usually used with a
         WHERE clause
DELETE : delete one or more previously inserted record, usually used with a
         WHERE clause
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

import sqlite3

connection = sqlite3.connect('mydata.db')

cursor = connection.cursor()

                                setting TEXT,
                                value TEXT)

#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()
print data



Got any questions?

Get answers in the ShowMeDo Learners Google Group.

Video statistics:

  • Video's rank shown in the most popular listing
  • Video plays: 46 (since July 30th)
  • Plays in last week: 3
  • Published: 89 months ago

Thank-yous, questions and comments

If this video tutorial was helpful please take some time to say thank-you to the authors for their hard work. Feel free to ask questions. Let the author know why their video tutorial was useful - what are you learning about? Did the video tutorial save you time? Would you like to see more?

You may also want to see our ShowMeDo Google Group to speak to our active users and authors.

Your email address will not be published.

Show some quick comments >>

All comments excluding tick-boxed quick-comments

Very good presentation! I wish that I had watched it prior to writing my own Python/sqlite code. I would have saved me a couple of days of experimentation.

43. Nike Air Max 2014 Baratas Thu, 07 Oct 2010 20:01

Check out for a nice video on using SQLite with Python.

42. jordans for cheap Thu, 30 Sep 2010 09:22

Thanks for your sqlite tut. Good talking and preparing. Now I try myself and dig in.

clear and easy to follow

40. comprar vans mujer Sat, 11 Sep 2010 03:20

Really useful intro to sqlite - Thanks

39. true religion sale jeans cheap Tue, 07 Sep 2010 10:57

Nice video. I like the way you show how to find out about things you don't know using Google. I'm sure most of us do this sort of thing but for new users it may be quite helpful.

38. Fri, 03 Sep 2010 13:47

Very good presentation.

37. Moncler Piumini 2013 Donna Herisson Marrone Wed, 01 Sep 2010 21:03


im a newbie in Python and SQL. Thanks for the videos. very helpful!


Great video. I really liked the SQLite-Manager add-in that you mentioned. Thanks for not demoing tables and fields called tbl1, field1, etc. Those are pretty useless examples. Yours showed a good, simple overview that anyone could understand. Thanks! Vaughn.

35. TimothyKl Tue, 29 Jun 2010 18:29

Just what I needed. Thanks.

34. Sun, 20 Jun 2010 12:03

This is exactly the kind of introduction to databases I needed. Thank you sir.

33. mbt women's maliza oxford grey shoes Tue, 08 Jun 2010 20:18

This video was great! I am doing some research this summer, and was assigned to write a SQlite database. it was a great place to start from. thanks so much.

32. Fri, 16 Apr 2010 01:13

Very clearly explained in a step by step fashion. I like it.

31. mbt shoes squeaking Tue, 13 Apr 2010 12:24

Nice simple video

30. marinir seo 085-635-945-40 Wed, 10 Mar 2010 23:08

I am learning Python and wanted a quick tutorial on interacting with SQL. This was perfect. Thank you for your time and skills.

thanks, very useful

28. Bogner Zipper Regelmäßige Faltbare Down Jacket Tue, 02 Mar 2010 02:29

nice tutorial.which it was more indepth but thanks alot anyway

Thanks for the quick tutorial.

You might wanna do a 2nd part on Variable Substitution & custom datatypes like datetime.

Your tutorial sure beats having to go through the python docs though :)

26. the north face womens coats down Wed, 17 Feb 2010 19:45

Nice video. Gave a quick overview on working with databases in python.

25. Iporn Blog Heisse Sachen für Dein iPhone Wed, 27 Jan 2010 14:32

Very helpful for me to refresh what little I knew, and hear how someone else thinks about it!

24. marinir seo Sun, 17 Jan 2010 22:26

Outstanding, exactly what I needed to see.

23. marinir seo Wed, 13 Jan 2010 22:04

Very nice. I have tonns of data produced by generators in my python application (up to 3 GB in memory), and I am looking for a way of storing in disk temporarily and use it later in the application. I think that thanks to you I'm going to try sqlite3 and may solve my memory problem and therefore run time and therefore my thesis will be ready on time... Thanks!

For the person looking for a GUI designer (buttons, widgets, and ease of connectio)... user PyQt along with Qt designer... perfect match with Python.

22. anonymous Sun, 03 Jan 2010 16:29

Very good video. Thanks. I am wondering whether sqlite3 is a good way to work with and store student grades.

21. anonymous Sat, 02 Jan 2010 22:19

Extremely useful, many thank'x. Please do keep up the good work :-)

20. anonymous Mon, 07 Dec 2009 05:57

good video

19. anonymous Wed, 02 Dec 2009 05:58

Just curious

18. anonymous Sun, 29 Nov 2009 03:51

Thank you Erik , I like this example!

17. anonymous Sun, 15 Nov 2009 18:12

Really great rundown on basic SQL Syntax (python or not), great job mate!

Showmedo is a peer-produced video-tutorials and screencasts site for free and open-source software (FOSS)- with the exception of some club videos, the large majority are free to watch and download.

how to help » about » faq »

Educating the Open-source Community With Showmedo

Although as important as the software it supports, education and documentation are relatively neglected in the Open-source world. Coders love to code, and explaining how best to use or improve the software tends to be deferred or even sidelined.

At Showmedo we believe the community can play a vital role here and also say thanks for the tools and software that make our lives easier. If you have a piece of software you love or a programming langugage you are enthusiastic about, why not make a screencast showing others how to use it? All the stuff you wish you'd been told, the tips, tricks, insights that would have saved you time and frustration.

Screencasting is easier than you think, and we're happy to help you. You can emailus for advice or just use some of the how-to screencasts on the site. This screencasting learning-pathis a good place to start.

Kudos and Thanks for Erik

VPython: Projectile Motion 1

This is very good. Thank You for the Effort.

¥Ö¥ë¥¬¥ê ¥¨¥ë¥´¥ó ¶¨ý dxÑý¹Ö¥¦¥©¥Ã¥Á
67 months ago


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.