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