Create Tables In SQLite Database Using Python

Overview:

  • An SQLite table can be created on an in-memory database or on a disk file based database.
  • Creating a table in SQLite involves first selecting a database file and loading it.
  • In SQLite, tables can be created in the in-memory databases as well.

Create a SQLite Table

Creating a SQLite table using Python:

  • The sqlite3 module provides the interface for connecting to the SQLite database.
  • Using the connect() method of sqlite3 module a database connection can be created by specifying the name of the database file. This database will act as the main database.
  • Databases present in other disk files can be attached to the database connection using the ATTACH statement, supported by the SQLite.
  • In-memory databases can me made and connected to by specifying the argument to the connect() function as “:memory:” instead of a file name.
  • Regardless of the classification of in-memory and disk file based databases SQLite will have only one database as the main database and one database as the temp database.
  • A temp database is an in-memory SQLite database that will have one or more temporary tables.
  • Once a database connection is obtained to an SQLite database, CREATE TABLE statement can be executed through a cursor object obtained using the connection object.
  • Example Python programs are given that create tables in in-memory databases and disk file based databases.

Example 1 – Creating a table in an in-memory SQLite database:

# ----Example Python Program to create tables in-memory databases----

 

# Import the sqlite3 module

import sqlite3

 

# Create database connection to an in-memory database

connectionObject    = sqlite3.connect(":memory:")

 

# Obtain a cursor object

cursorObject        = connectionObject.cursor()

 

# Create a table in the in-memory database

createTable = "CREATE TABLE EMP(id int, FirstName varchar(32), LastName varchar(32), dept int)"

cursorObject.execute(createTable)

 

# Print the tables 

 

# .tables command will not work as it is not SQL...hence querying the SQLite_master

cursorObject.execute("select * from SQLite_master where type=\"table\"")

 

print("Tables available in the in-memory database(main):")

tables = cursorObject.fetchall()

 

print("Listing tables from SQLite_master:")

for table in tables:

    print("------------------------------------------------------")

    print("DB Object Name: %s"%(table[0]))

    print("Name of the database object: %s"%(table[1]))

    print("Table Name: %s"%(table[2]))

    print("Root page: %s"%(table[3]))

    print("SQL statement: %s"%(table[4]))

    print("------------------------------------------------------")

connectionObject.close()

 

Output:

Tables available in the in-memory database(main):

Listing tables from SQLite_master:

------------------------------------------------------

DB Object Name: table

Name of the database object: EMP

Table Name: EMP

Root page: 2

SQL statement: CREATE TABLE EMP(id int, FirstName varchar(32), LastName varchar(32), dept int)

------------------------------------------------------

 

Example 2 – Creating a table in a persistent SQLite database:

# ----Example Python Program to create tables in disk file based SQLite databases----

 

# Import the sqlite3 module

import sqlite3

 

# Create a database connection to a disk file based database

connectionObject    = sqlite3.connect("weather.db")

 

# Obtain a cursor object

cursorObject        = connectionObject.cursor()

 

# Drop any existing table with the same name

dropTable           = "drop table temperature"

cursorObject.execute(dropTable)

 

# Create a table in the disk file based database

createTable = "CREATE TABLE temperature(id int, temp numeric(3,1))"

cursorObject.execute(createTable)

 

# Insert EOD stats into the reports table

insertValues = "INSERT INTO temperature values(1,40.1)"

cursorObject.execute(insertValues)

 

insertValues = "INSERT INTO temperature values(2,65.4)"

cursorObject.execute(insertValues)

 

# Select from the temperature table

queryTable = "SELECT * from temperature"

queryResults = cursorObject.execute(queryTable)

 

# Print the Temperature records

print("(CityId, Temperature)")

for result in queryResults:

    print(result)

 

connectionObject.close()

 

Output:

(CityId, Temperature)

(1, 40.1)

(2, 65.4)

 

 


Copyright 2020 © pythontic.com