Page 1 of 1

sqlite3 in Python

#1 Eric115  Icon User is offline

  • coderさん
  • member icon

Reputation: 63
  • View blog
  • Posts: 696
  • Joined: 19-January 09

Posted 15 January 2011 - 07:59 PM

Hello,
Databases are great for a huge rage of things, from storing customers to storing variables for games. If you want a fairly simple way to write multiple pieces of information to a single file with an easy way to extract the information, your in need of a database. In this tutorial, I will cover the basics of using sqlite3 in python.
Python has a great implementation of sqlite. Sqlite is included in python from version 2.5 and up. If you have a python version lower than 2.5, you will need to download and install sqlite3. I am using python 2.5 for this tutorial. I will be using the Sqlite Manager to view the database, this is not necessary but is helpful so you can see what information is in your database and you can edit/create databases. You can download it from here. It is also is available as Firefox add-on (see the link)

Creating a database
Before we can start using our database, we have to create it. So in order to create our database, we have to make a connection to it (similar to the open() command for working with a file).
import sqlite3
con = sqlite3.connect('DatabaseName.sql')

You can use any extension you like, but I will use .sql for the purpose of this tutorial.
After creating the connection to the database, we need to make a cursor object so we can interact with the database.
cur = con.cursor()
Now that we have made the connection and made a cursor object, we can execute a command to the database by using the .execute() command. When you are sending sqlite commands, it is not case-sensitive but I will make the sqlite commands in CAPITALS, and the names in lowercase. So, to make the database, we need to tell sqlite what we want it to do, the name of the database, the columns we want it to have and what type to columns are (INT, TEXT, etc).
import sqlite3
con = sqlite3.connect('MyBusiness.sql')
cur = con.cursor()
cur.execute("""CREATE TABLE Customers (Fname TEXT, Lname TEXT, Phone INTEGER, joined_club BOOL)"""))
cur.close()#always a good habit to close your connections
con.close()#and your cursor when you're done!

Now open up your database in sqlite Manager (or in any program which allows you to view an sqlite database). When you load in your database, select the name of the table you created (mine was Customers). Select browse and search and you should be able to see all of the rows you put into your database along the top. You will also notice that there is a column that we did not put in at the start called rowid. Rowid is just an auto-incrementing value which gives each record it's own number. So after we insert our first record, the rowid will automatically become 1. We do not need to tell it to update rowid it is done when the record is put in. In the above example, after specifying the name of the table (Customers) I named each column. What you will notice after each column is the type of information that column is made for. For example the column Fname (First name) will be used to store text, but the Phone column will be used to store a number. If you wanted to store a decimal number you would use the FLOAT type. The other type I used is BOOL, this is short for Boolean (True or False values), in the sqlite database if you write True to this column it becomes 1 and False becomes 0 (This means the same as True or False and python isn't fussy about you using True, False or 1 and 0).

Writing to the database
So, our database is made, let's write something to it!
Before writing to our database we need to once again connect to it (if you closed the connections, if you didn't you don't need to connect to it again) just connect to your database and create a cursor object (same as you did when creating the database). When you write to your database, you will often want to use a variable. This is fine, but sqlite is vulnerable to sqlite injections, so to protect the database from that, you replace the variables with a ? and then at the end of the execute command before you close the second bracket you give your variables. So, this is how it should look:
con = sqlite3.connect('DatabaseName.sql')
cur = con.cursor()
Fname = 'John'
Lname = 'Doe'
Phone = 12345678
joined_club = True
cur.execute("""INSERT INTO Customers (Fname, Lname, Phone, joined_club) VALUES 
(?,?,?,?)""", (Fname, Lname, Phone, joined_club))
con.commit()#Do not forget this!!
cur.close()
con.close()


After you have given the insert command, you must commit the changes to the database using the con.commit() or the changes you have made will not be written to your database. I still often forget to commit the changes and sit there wondering why the changes don't show up in my database! There is also another way to write values using a for loop.
it is much the same as the above method.
Fname = 'John'
Lname = 'Doe'
Phone = 12345678
joined_club = True
for i in [(Fname, Lname, Phone, joined_club)]:
    cur.execute("""INSERT INTO Customers (Fname, Lname, Phone, joined_club) VALUES 
(?,?,?,?)""", i)
con.commit()



Retrieving values
There are a few different ways to get values back from your database depending on what you need and what is available to you at the time. Sometimes you will know exactly what you want to take out, other times you may need to take out something from a certain row. First, I will show you how to fetch the entire table. Before you can fetch data from a table, you need to tell sqlite what you want exactly, or just get the entire table. * means all
con = sqlite3.connect('DatabaseName.sql')
cur = con.cursor()
cur.execute("""SELECT * FROM Customers""")
all = cur.fetchall()
print all


When you use fetchall, you get a list with each row as another list returned. Like this:
[(u'John' , u'Doe', 12345678, 1), (u'Jane', u'Dae', u'98371234', 0)]
I tend to use the fetchall function a lot when I am looking for a value. To get a record from this list, you can use an index position (like a normal list) e.g. all[2] This will give you the entire record! If you want a specific value from a specific record, you have to give the index position of the record and the value you want. e.g.
all[2][1]. Sqlite has some other commands such as fetchone() Which will fetch the first record and then the next record along progressively. If there is no record to fetch, it returns an empty list [].
con = sqlite3.connect('DatabaseName.sql')
cur = con.cursor()
cur.execute("""SELECT * FROM Customers""")
one = cur.fetchone()
print one
next = cur.fetchone()
print next

This returns the first record as all and then the second record as next.
Another way to fetch data is using the fetchmany() command. With this you pass the function the number of rows you want returned to you and it will return a list with that number of records in the list.
con = sqlite3.connect('DatabaseName.sql')
cur = con.cursor()
cur.execute("""SELECT * FROM Customers""")
many = cur.fetchmany(3)
print many


You will notice that through out these, I have still used the
cur.execute("""SELECT * FROM Customers""")
before I fetch the data. This is because if you don't use this, you will get an empty list returned to you.

Updating values
So, you can write and read your database, but what if you want to change a value? Well sqlite can do that as well! When you execute your command, you need to tell sqlite that you are changing a value. You can do this by starting the command with UPDATE. So lets say that you want to change someones First name (Fname) from Jessica to Jennifer. This is how it would look:
con = sqlite3.connect('DatabaseName.sql')
cur = con.cursor()
cur.execute("""UPDATE Customers SET Fname='Jennifer' WHERE Fname='Jessica'""")
con.commit()


This is telling sqlite that where Fname is equal to Jessica, change the Fname value to Jennifer. You don't always have to use the same column to change the value for example, you could use the rowid column to find the record and then change Fname.
cur.execute("""UPDATE Customers SET Fname='Jennifer' WHERE rowid=4""")
con.commit()


You can also change a entire column of values. e.g. You want to set the joined_club column to 1 if the current value is 0. It would look like this:
cur.execute("""UPDATE Customers SET joined_club=1 WHERE joined_club=0""")
con.commit()


It's the same as the other update command, but this time we didn't specify a single item, so it changed all items matching that description.

Deleting Values
Sometimes, your program will no longer need a record that you have, so it's time to delete it. You can delete values in almost the same way you change them. It you wanted to delete a record with the rowid of 4, you would use
cur.execute("""DELETE FROM Customers WHERE rowid=4""")
con.commit()


But what if you don't know the rowid, well just like in changing a value, you can replace rowid with a different value for example you might know the Fname, so you can change rowid=4 to Fname='Jessica' or whatever Fname is. But be careful if you have multiple entries where the Fname is equal to Jessica, it will delete all of them! This can be good and bad depending on what you want. If you want to make sure you are deleting the correct value, you can use the 'AND' to specify too requirements that it must meet to delete that row.
cur.execute("""DELETE FROM Customers WHERE rowid=4 and Fname='Jessica'""")
con.commit()


If you had two requirements and it only had to meet one or both of them to proceed, you can use the 'OR' statement in place of AND. These also work in UPDATING values if you need them.

Well, if you followed the tutorial you should be able to create a database, write, read, update and delete values from your database. This should be enough for you to use an sqlite database with your program!
Good luck with your database!

Is This A Good Question/Topic? 4
  • +

Replies To: sqlite3 in Python

#2 grimofdoom  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 17
  • Joined: 14-August 12

Posted 16 August 2012 - 01:05 PM

Is this offline or online?
Was This Post Helpful? 0
  • +
  • -

#3 Eric115  Icon User is offline

  • coderさん
  • member icon

Reputation: 63
  • View blog
  • Posts: 696
  • Joined: 19-January 09

Posted 26 October 2012 - 08:22 AM

This tutorial uses an offline database.
Was This Post Helpful? 0
  • +
  • -

#4 manue  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 12-September 13

Posted 12 September 2013 - 09:10 AM

Hi!

I was practicing and tried to update and delete rows in my sqlite database done with python but it seems value rowid does not work. Is it necessary to include value rowid at the beginning with CREATE TABLE or it creates by default?
Was This Post Helpful? 0
  • +
  • -

#5 liherb  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 21
  • Joined: 25-November 12

Posted 17 September 2013 - 05:47 AM

Is there a tool that allows you to graphically change sqlite schema as simple as editing a spreadsheet? For example if I wanted to change a column name from "my_driving_hours" to "driving_time", instead of writing a line of code, I can just click on that column and type in the new name, letting the tool automatically create a new database with the new schema and migrate the old data into the new one. I found it very useful.

I am not sure if there exists such a tool after a long google search. Could any pros give some thoughts? Many thanks.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1