Creating and Populating a DB using Sqlite3

A database is usually stored in a file or in a collection of files. These files aren’t formatted as plain text—if you open them in an editor, they will look like garbage, and any changes you make will probably corrupt the data and make the database unusable. Instead you must interact with the database in one of two ways:

• By typing commands into a database GUI, just as you type commands into a  Python  interpreter. This is good for simple tasks but not for writing applications of your own.

• By writing programs in Python (or some other language). These programs import a library that knows how to work with the kind of database you are using and use that library to create tables, insert records, and fetch the data you want. Your code can then format the results in a web page, calculate statistics, or do whatever else you like.

Our required table should have one column that contains the names of regions and another that contains the populations of regions, so each row of the table  must represent a region and its population. Lets creating our table:

1. import sqlite3

The first statement import sqlite3 is telling Python that we want to use sqlite3 

2. Establish connection to the DB

We now make a connection to our database by calling the database module’s connect method.

con = sqlite3.connect('employees.db')

This method takes one string as a parameter, which identifies the database to connect to. Since SQLite stores each entire database in a single file on disk, this is just the path to the file. Since the database population.db doesn’t exist, it will be created.

3. Get a cursor

cur = con.cursor()

This keeps track of where we are in the database so that if several programs are accessing the database at the same time, the database can keep track of who is trying to do what.

4. Create the database table

Here the SQL comes into effect which will be used to create our table and store the employees data in to it. The syntax to create a table is as follows:

CREATE TABLE «TableName»(«ColumnName» «Type», ...)

The TableName value will the name for our table to be created, ColumnName value will be provided by us and the Type which represents the types of the data in each of the table’s columns, can be chosen from the types the database supports. Some of the supported types are:

NULL             NoneType      Means “know nothing about it”
INTEGER      int                   Integers
REAL             float                8-byte floating-point numbers
TEXT             str                   Strings of characters
BLOB             bytes              Binary data

Now let's create a table Sales which will store region names as strings in the Region column and the working employees as integers in the Total_Employees column. The SQL statement to create the table is as follows:

CREATE TABLE Sales (Region TEXT, Total_Employees INTEGER)

In our program we'll put this SQL statement in a string and pass it as an argument to a Python method that will execute the SQL command:

cur.execute('CREATE TABLE Sales (Region TEXT, Total_Employees INTEGER)')

When we call the execute(), it returns the cursor object that it was called on. Since cur refers to that same cursor object, we don’t need to do anything with the value returned by execute.

 5. Populate the created table

After we create the Sales table, our next task is to insert data (record) into it which is again done using the SQL. The syntax to insert record in to a table is:

INSERT INTO «TableName» VALUES(«Value», ...)

Thus to insert data into the Sales table we will use:

INSERT INTO Sales VALUES("EAST", 150) 

In our program we'll put this SQL statement in a string and pass it as an argument to a Python method that will execute the SQL command:

cur.execute('INSERT INTO Sales VALUES("EAST", 150) ')
cur.execute('INSERT INTO sales VALUES("WEST",200 )')
cur.execute('INSERT INTO sales VALUES("NORTH",180 )')
cur.execute('INSERT INTO sales VALUES("SOUTH",300 )')

The records are inserted one at a time using the above method. We can do this insertion directly into the DB using the SQLite Manager. The number and type of values in the INSERT statements matches
the number and type of columns in the database table. If we try to insert a value of a different type than the one declared for the column, the library will try to convert it, just as it converts the integer 9 to a floating-point number when we do 1.1 + 9. For example, if we insert the integer 16 into a TEXT column, it will automatically be converted to "16"; similarly, if we insert a string into an INTEGER column, it is parsed to see whether it represents a number. If so, the number is inserted.

Usually if the number of values being inserted doesn’t match the number of columns in the table, the database reports an error and the data is not inserted. But, if we try to insert a value that cannot be converted to the correct type, such as the string “string” into an INTEGER field, SQLite will actually do it (though other databases will not). 


6. Saving the changes

After the data is inserted into the DB or any changes are made to the DB we must save the changes using the commit() as shown below:

con.commit()

If we fail to commit then our changes are not actually stored and are not visible to anyone else who is using the database at the same time.

The complete program to create and populate the employees DB is shown below:


import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('CREATE TABLE Sales (Region TEXT, Total_Employees INTEGER)')

cur.execute('INSERT INTO sales VALUES("EAST",150 )')
cur.execute('INSERT INTO sales VALUES("WEST",200 )')
cur.execute('INSERT INTO sales VALUES("NORTH",180 )')
cur.execute('INSERT INTO sales VALUES("SOUTH",300 )')

con.commit() 

Here I am ending today's discussion.In the next post we'll focus on retrieving data from the database. Till we meet next keep practicing and learning Python as Python is easy to learn!