CONNECTING TO SQLITE

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 .

EXECUTING STATEMENTS

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.

CREATING TABLES

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

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

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.

conn.commit()

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.

conn.close()

INSERTING VALUES

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)''' )
conn.commit()
conn.close()

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.

SELECTING VALUES

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())
conn.commit()
conn.close()

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

CLASSES AND TABLES

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.

FROM TABLE TO OBJECT

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()
person1.clone_person(c.fetchone())
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

FROM OBJECT TO TABLE

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
('{}', '{}', '{}')'''
.format(person2.first,
person2.last,
person2.age))
conn.commit()
conn.close()

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

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