Name: [116] Erik Thompson
Member: 90 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: http://code.google.com/p/sqlite-manager/

Here is a link to SQLite which includes some documentation: http://www.sqlite.org/

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

sql = """CREATE TABLE Settings (id INTEGER PRIMARY KEY AUTOINCREMENT,
                                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"

cursor.execute(sql)

#print cursor.lastrowid   # get the autoincremented id from the previously inserted record

data = cursor.fetchall()
print data

connection.commit()
connection.close()


                              

Got any questions?

Get answers in the ShowMeDo Learners Google Group.

Video statistics:

  • Video's rank shown in the most popular listing
  • Video plays: 6321 (since July 30th)
  • Plays in last week: 1
  • Published: 67 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. Jay Godse Thu, 07 Oct 2010 20:01

Check out http://www.youtube.com/watch?v=__eI1sbEfLw for a nice video on using SQLite with Python.


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


clear and easy to follow


40. zak_neutron Sat, 11 Sep 2010 03:20

Really useful intro to sqlite - Thanks


39. Greg Krakow 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. Gary Washington Fri, 03 Sep 2010 13:47

Very good presentation.


hi,

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

-mia


36. Vaughn Smith Tue, 03 Aug 2010 09:28

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. Bryan Bedal Tue, 29 Jun 2010 18:29

Just what I needed. Thanks.


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


33. Angela Tejada 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.


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


31. Newbie2Py Tue, 13 Apr 2010 12:24

Nice simple video


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


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


27. Rajaseelan Sun, 28 Feb 2010 07:57

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. Bharat Krishna Wed, 17 Feb 2010 19:45

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


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


24. Matt Ball Sun, 17 Jan 2010 22:26

Outstanding, exactly what I needed to see.


23. Angelica Echavarria 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!


16. anonymous Wed, 21 Oct 2009 15:42

Neato.

Explains the intricacies that have been blown up into almost unmanageable, task master, drudgeny.

Good work on the simplification of the procedure of engaging SQLite with some good old fashioned Python.


15. anonymous Fri, 16 Oct 2009 08:14

thanks very good.

would be nice with foreign key explained as well.. :-)


14. anonymous Wed, 07 Oct 2009 05:35

Excellent and understandable tutorial!

Keep up the good work


13. anonymous Sun, 04 Oct 2009 07:30

I really liked your quick pysql tutorial and hopefully I will be able to use it in my studies of Computer Linguistics in Uppsala, Sweden.


12. anonymous Sun, 20 Sep 2009 23:43

Thanks really useful


11. anonymous Thu, 10 Sep 2009 12:25

Very nice intro to sql and sqlite.


I'm looking for something in python which would allow me to put buttons, text and radio buttons on the screen. I've written programs in excel vba which I would like to change over to python. Trying to learn how to place these wigits on the screen at a location of my choice. I also realize that are many different verison of pythons. Which version is better for visual display, database and speech options? My email: bush938@gmail.com


9. anonymous Mon, 17 Aug 2009 10:17

Hey this was great thanks.... exactly what I was looking for. I do neuroscience and just started doing some work on the computational side of the field so I'm brand new to all things python. This was a great introduction to interfacing databases with python!


8. anonymous Sat, 25 Jul 2009 18:20

Great Video! Thanks!


7. anonymous Tue, 16 Jun 2009 08:46

very very good!


6. anonymous Thu, 14 May 2009 07:41

Good content, straightforward, easy to follow, saved a newbie quite a bit of time. Thanks!

Audio not A+, though. Had to go back a few times...couldn't make out many of your sentence endings...


5. anonymous Thu, 14 May 2009 04:56

Very good video presentation. It is really helpful to the beginners in Python like me. Thanks for valuable stuff being explained.


4. anonymous Thu, 07 May 2009 05:39

Well done! I liked the video, pretty clear and useful. I'll start using the Firefox add-on


3. anonymous Fri, 10 Apr 2009 09:43

Very helpful.


2. anonymous Fri, 09 Jan 2009 15:41

Thank you for sharing the knowledge with the rest of us. Video tutorials make learning so much easier. I have been reading python books for several months now but this is the icing on the cake. You definitely have a knack for teaching! Keep it up!!


Thanks, really useful database programming.


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

Content

Feedback

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.

feedback@showmedo.com