Data Science Posts and Resources

Articles on Data Science

Python - Database Programming

Introduction to Python Database Programming

Laxmi K Soni

5-Minute Read

Database Programming

Databases are one of the most popular ways to store and manage data in computer science. Because of that, in this post we are going to take a look at database programming with Python. Notice that for most databases we use the query language SQL , which stands for Structured Query Language . We use this language in order to manage the database, the tables and the rows and columns.


The database that comes pre-installed with Python is called SQLite . It is also the one which we are going to use. Of course, there are also other libraries for MySQL, MongoDB etc. In order to use SQLite in Python, we need to import the respective module – sqlite3 .

import sqlite3

Now, to create a new database file on our disk, we need to use the connect method.

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

This right here creates the new file mydata.db and connects to this database. It returns a connection object which we save in the variable conn .


So, we have established a connection to the database. But in order to execute SQL statements, we will need to create a so-called cursor .

c = conn.cursor()

We get this cursor by using the method cursor of our connection object that returns it. Now we can go ahead and execute all kinds of statements.


For example, we can create our first table like this:

c.execute( '''CREATE TABLE persons (
first_name TEXT,
last_name TEXT,
)''' )

Here we use the execute function and write our query. What we are passing here is SQL code. As I already said, understanding SQL is not the main objective here. We are focusing on the Python part. Nevertheless, it’s quite obvious what’s happening here. We are creating a new table with the name persons and each person will have the three attributes first_name, last_name and age .

Now our statement is written but in order to really execute it, we ne need to commit to our connection.


When we do this, our statement gets executed and our table created. Notice that this works only once, since after that the table already exists and can’t be created again. At the end, don’t forget to close the connection, when you are done with everything.



Now let’s fill up our table with some values. For this, we just use an ordinary INSERT statement.

c.execute( '''INSERT INTO persons VALUES
('John', 'Smith', 25),
('Anna', 'Smith', 30),
('Mike', 'Johnson', 40)''' )

So basically, we are just adding three entries to our table. When you run this code, you will see that everything went fine. But to be on the safe side, we will try to now extract the values from the database into our program.


In order to get values from the database, we need to first execute a SELECT statement. After that, we also need to fetch the results.

c.execute( '''SELECT * FROM persons
WHERE last_name = 'Smith'''' )
print (c.fetchall())

As you can see, our SELECT statement that gets all the entries where the last_name has the value Smith . We then need to use the method fetchall of the cursor, in order to get our results. It returns a list of tuples, where every tuple is one entry. Alternatively, we could use the method fetchone to only get the first entry or fetchmany to get a specific amount of entries. In our case however, the result looks like this: [(‘John’, ‘Smith’, 25), (‘Anna’, ‘Smith’, 30)]


Now in order to make the communication more efficient and easier, we are going to create a Person class that has the columns as attributes.

class Person():

def __init__ ( self , first= None ,last= None , age= None ):
  self .first = first
  self .last = last
  self .age = age
def clone_person( self , result):
  self .first = result[ 0 ]
  self .last = result[ 1 ]
  self .age = result[ 2 ]

Here we have a constructor with default parameters. In case we don’t specify any values, they get assigned the value None . Also, we have a function clone_person that gets passed a sequence and assigns the values of it to the object. In our case, this sequence will be the tuple from the fetching results.


So let’s create a new Person object by getting its data from our database.

c.execute( '''SELECT * FROM persons
WHERE last_name = 'Smith'''' )
person1 = Person()
print (person1.first)
print (person1.last)
print (person1.age)

Here we fetch the first entry of our query results, by using the fetchone function. The result is the following: John Smith 25


We can also do that the other way around. Let’s create a person objects, assign values to the attributes and then insert this object into our database.

person2 = Person( 'Bob' , 'Davis' , 23 )
c.execute( '''INSERT INTO persons VALUES
('{}', '{}', '{}')'''

Here we used the basic format function in order to put our values into the statement. When we execute it, our object gets inserted into the database. We can check this by printing all objects of the table persons .

c.execute( 'SELECT * FROM persons' )
print (c.fetchall())
In the results, we find our new object:
[('John', 'Smith', 25), ('Anna', 'Smith', 30), ('Mike', 'Johnson', 40), ('Bob', 'Davis', 23) ]


There is a much more secure and elegant way to put the values of our attributes into the SQL statements. We can use prepared statements .

person = Person( 'Julia' , 'Johnson' , 28 )
c.execute( 'INSERT INTO persons VALUES (?, ?, ?)' ,
(person.first, person.last, person.age))

We replace the values with question marks and pass the values as a tuple in the function. This makes our statements cleaner and also less prone to SQL injections.

Say Something


Nothing yet.

Recent Posts