Python 2.7 Tutorial Pt 12

Python 2.7 TutorialIn this Python Video Tutorial, I will show you how to create databases using the Python SQLite module. SQLite is great because it:

  • Provides most of the basic database structure you want
  • Doesn’t require you to have an actual database server running
  • Provides SQL querying capabilities
  • Is included with Python by default

You enter and retrieve information from an SQLite database just like you do with any other database, by using SQL. Here is a tutorial on how to program with SQL if you don’t know how SQL Statements Video Tutorial. In that tutorial I focus on MySQL, but everything a say about SQL is nearly identical.

Like always, a lot of code follows the video. If you have any questions or comments leave them below. And, if you missed my other Python Tutorials they are available here:

Here is all the Code from the Video

#! /usr/bin/python
import sqlite3

# Create a version of the database in RAM, this could also be a filename ending with .db
createDb = sqlite3.connect(‘:memory:’)

# Creates the SQLite cursor that is used to query the database
queryCurs = createDb.cursor()

def createTable():
# Calls the execute method that will submit a create table SQL Query
# id will auto increment and doesn’t require values to be entered
queryCurs.execute(”’create table customers
(id integer primary key, name text, street text, city text, state text, balance real)”’)

def addCust(name, street, city, state, balance):

# Calls the execute method that will submit a insert SQL Query
queryCurs.execute(”’insert into customers (name, street, city, state, balance)
values (?, ?, ?, ?, ?)”’,(name, street, city, state, balance))

def main():
# Call the method createTable, that will create the table in the database

# Add customers to the database
addCust(‘Derek Banas’, ‘5708 Highway St’, ‘Verona’, ‘PA’, 150.76)
addCust(‘Monty Davis’, ‘5709 Highway St’, ‘Verona’, ‘PA’, 350.60)
addCust(‘Paul Smith’, ‘5710 Highway St’, ‘Verona’, ‘PA’, 0.00)
addCust(‘Sue Smith’, ‘5712 Highway St’, ‘Verona’, ‘PA’, 50.90)

# Force the database to make changes with the commit command

queryCurs.execute(‘select * from customers’)

# Cycles through the tuple and prints the entries to screen
for i in queryCurs:
print “\n”
for j in i:
print j

# Print customers ordered by lowest balance and with titles
queryCurs.execute(‘select * from customers order by balance’)

# Creates a list that contains the titles for my database data
listTitle = [‘Id Num ‘,’Name ‘,’Street ‘,’City ‘,’State ‘,’Balance ‘]
k = 0

# Cycles through the tuple and prints the entries to screen
for i in queryCurs:
print “\n”
for j in i:
print listTitle[k],
print j
if k < 5: k += 1
else: k = 0

# Closes the database

if __name__ == ‘__main__’: main()

12 Responses to “Python 2.7 Tutorial Pt 12”

  1. VideoVillain says:


    Thanks so much for the great tutorials on Python! They’ve been a great help to me and I plan on using your PHP, JavaScript, and WordPress tutorials as well to get my sight up and running.

    I have a question about web browser file encoding though. I’m working with the ‘xlrd’ library in Python 2.7 to upload a file and the strip its contents into a database.

    The example works fine, but I don’t want to have to input the file in the web browser, I want to automate the process and have it just grab files and read them one by one.

    The problem is that when I try to bypass uploading the file through the browser – no longer using the request message that I get back and getting the file this way:
    upload_file = request.FILES[‘upload_file’]

    But instead try to just get the file like this:
    upload_file = file(‘PATH/filename.xls’)

    It doesn’t work.

    If I print out the ‘’ in the first scenario, I get lots of garbage characters. If I print it out using the second scenario, I get only 3 garbage characters.

    What is happening to the file when it is uploaded through the browser before being used that is being missed when I try to just grab the file directly?

    Thanks so much for your help!

  2. Ray says:

    I’m an old guy trying to get updated, and frustrated with my learning curve and choices. I previously programmed the early TRS80 and then the first PC’s in BASIC. Then programmed embedded microprocessors like the 8051 with assembly level. But all that was 30 years ago.

    I’m thinking I need to learn Python but have three things I can’t seem to do. First, how can I clear the screen (like cls in Basic or Clear in Linux)? Second, how can I jump to a label (like JP or JMP in assy level or goto in BASIC)? Third, Is there any way to output to the printer other than saving a page and then printing it in a second step from another program?

    I’m very grateful for your website. I like the faster pace you use. It helps me learn.

    Thanks for your time.


    • admin says:

      I used all of the same computers as you. I used to use punch cards and saved code on cassettes 🙂

      If you are working with the python gui tkinter you’ll see how to clear the screen later in the tutorial. If you are just executing code in a console though you are stuck with something like
      clear = “\n” * 50
      print clear

      Nasty I know.

      As per jumping around in the code. There is no goto statement for Python. You could hack one, but that’s probably not a good idea.

      If you want to output to a printer, this code should help you out if you’re on a windows machine output to printer

      I hope that helps

  3. daniel says:


    thank you for the tutorials. They are great! I caught some of my errors but i am still having trouble with this one. invalid syntax on line def createTable(): (below)

    def createTable(): 
    queryCurs.execute(”’create table customers
    (id integer primary key, name text, street text, city text, state text, balance real)”’)

    I copied the code from the website and it still did not work for some reason. I am running and am using spyder as my editor. thanks

  4. Remi Brisson says:

    This Python imbebbed SQLite database system seems neat. I work with MySQL. Do you have a tutorial that shows how to connect to an RDBMS and read, process, and update the data?

Leave a Reply

Your email address will not be published.