Creating SQLite Databases and Tables in Python

Creating a new database is so easy you could do it almost by accident. In the previous post I described how you connect to an existing database with a few lines of code:
import sqlite
conn = sqlite.connect("C:\\sqlite\\db\\base1.db")

Well, you do exactly the same for a new database, just use a filename that doesn't exist yet. And if you try to connect to an existing database but misspell the filename, you might do just that. When I've made that sort of mistake, my first clue is that the tables I expect to be there aren't. The first time this led to a panic about the whole database being deleted, when in fact I had just created and was looking into a new empty database file. The intended database file was still there, unconnected.

Let's assume that we intentionally create a new database file. SQLite databases hold their data in tables. I don't feel this is the place to go into the details of deciding how your data should be structured - I shall leave it to the reader to decide what the tables and columns of each table should be. But let's say we do the following:

>>> conn = sqlite3.connect("C:\\sqlite\\db\\example.db")

Then, as mentioned in the previous post, we create a cursor object so that we can send commands to and receive responses from SQLite. 

>>> curs = conn.cursor()

Let's say, for example, we want a database of famous historical figures. We start off with one table, with the following columns/fields with data types:

• Name (text) - Primary Key (i.e. the unique identifier for each record)
• Year of Birth (integer)
• Year of Death (integer)
• Role (text)
• Nation (text)

Note that SQLite has data types similar to Python data types, though slightly different. SQLite has integers (int() in Python), text (strings or str() in Python) and real (floating point numbers - float() in Python). There are other types but I haven't got round to them yet.  

How do we create this table? The SQLite command is in the following format: 

CREATE table tablename (column1 column1_type, column2 column2_type, ... );

So for our database of historical figures, we could create a table using something like:

CREATE table historicalfigures (Name text primary key, YearBirth integer, YearDeath integer, Role text, Nation text);

However, that is purely SQL. To get this to happen using Python, we need to get Python to pass it to SQLite using the cursor object curs: 

curs.execute("CREATE table historicalfigures (Name text primary key, YearBirth integer, YearDeath integer, Role text, Nation text);")

There is no immediate response, unless there is something wrong, in which case you will get an error message. Assuming there are no error messages, we have just used Python to create a SQLite database and an empty table.  

  

And finally, we close the connection
conn.close()