Python sqlite3 Create Tables

Let’s use Python SQLite3 to create and show tables. SQLite is a lightweight, serverless, embedded database system that runs as part of our script or application. Following are some other features:

1. Developed in C programming language and is open source.
2. Supports SQL language.
3. Writes data directly to a file on the filesystem.
4. Little configuration required.
5. Supports only a small set of datatypes: NULL, INTEGER, REAL, TEXT and BLOB.
6. Ideal for very small datasets.
7. Is not a substitute for a large complex DBMS like Oracle or SQL Server.

Let’s write some code to create a SQLite DB:

import sqlite3
con = sqlite3.connect('data/sample.db')
cur = con.cursor()

# Create table
cur.execute('''CREATE TABLE IF NOT EXISTS Playlist
              (createdate text, id integer, artist text, songTitle text, albumTitle text, duration text, songprice real)''')

#commit changes
con.commit()

# Close connection but commit changes before you do this
con.close()

Let’s explain what’s going on here:

1. First import our sqlite3 library.
2. sqlite3.connect() opens a connection to the SQLite database file supplied in the parameter. Our parameter is the path to the database file sample.db. The connect() method returns a Connection object which we store as con.
3. con.cursor() returns an instance of class Cursor. This is important because class Cursor has the methods that we will use to execute queries. We store our object in cur.
4. cur.execute() is what executes the SQL Statement. It accepts a SQL statement. In this case we are creating one table called Playlist if it doesn’t already exist.
5. con.commit() commits the current transaction. If this is not called before the connection is closed changes to the data in the table will be lost. On this occasion it is not necessary because we are only creating a table. CREATE TABLE is a DDL statement which means that there is an implicit ‘commit’.
6. con.close() closes the database connection. It is always good practice to close a database connection when it is no longer needed.

The above example will not return any output. If we want to know what tables already exist in the database we can execute the following query:

for row in cur.execute('''SELECT name FROM sqlite_master WHERE type='table' ORDER BY name'''):
    print(row)

#Output
#('Playlist',)

Every SQLite database contains a schema table that stores a record of every created table in the database. This table goes by the name sqlite_master or sqlite_schema table, for backwards compatibility. In the above code we simply query that table to discover what tables already exist in our SQLite database sample.db.

Following is our complete code:

import sqlite3
con = sqlite3.connect('data/sample.db')
cur = con.cursor()

# Create table
cur.execute('''CREATE TABLE IF NOT EXISTS Playlist
              (createdate text, id integer, artist text, songTitle text, albumTitle text, duration text, songprice real)''')

cur.execute('''CREATE TABLE IF NOT EXISTS Playlist2
              (createdate text, id integer, artist text, songTitle text, albumTitle text, duration text, songprice real)''')

for row in cur.execute('''SELECT name FROM sqlite_schema WHERE type='table' ORDER BY name'''):
    print(row) #shows two tables 
 
# Close connection
con.close()

con = sqlite3.connect('data/sample.db')
cur = con.cursor()

# Drop table
cur.execute('''DROP TABLE IF EXISTS Playlist2''')

for row in cur.execute('''SELECT name FROM sqlite_master WHERE type='table' ORDER BY name'''):
   print(row) #shows one table 

# Close connection
con.close()


#Output
#('Playlist',)
#('Playlist2',)
#('Playlist',)

So now you know how to use Python SQLite3 to create and show tables. In the next part of this tutorial we will explore how to INSERT and SELECT Rows from our SQLite table. Click HERE for Part 2.