Get Schema in SQLite with Python
This is how to get the schema in SQLite with Python.
You can check if you already have SQLite installed:
$ sqlite3
If the prompt doesn’t change to sqlite>
then try the download options from the official docs here.
Create a SQLite database
Let’s setup this tutorial by creating a SQLite database. I created a simple table and added one record:
$ sqlite3
sqlite> CREATE TABLE Users(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT);
sqlite> INSERT INTO Users(name, email) VALUES('homer', 'homer@simpson.com');
sqlite> SELECT * FROM Users;
1|homer|homer@simpson.com
You can also create a SQLite database and table using a Python script:
import sqlite3
conn = sqlite3.connect('users.sqlite')
cur = conn.cursor()
cur.execute('CREATE TABLE Users(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT')
cur.execute('INSERT INTO Users(name, email) VALUES(?, ?)', ('homer', 'homer@simpson.com'))
conn.commit()
cur.close()
Get the SQLite schema with PRAGMA
Given that the table name is Users
:
PRAGMA table_info('Users')
PRAGMA
is a SQL
extension for SQLite
.
- A
PRAGMA
can take0
or1
argument. - The argument could be in parenthesis
()
or with an equal=
- The argument could be boolean:
1 yes true on
,0 no false off
. - The argument could be a string literal.
- There could be an optional
schema-name
before thePRAGMA
name. - The
schema-name
is the name of the attached database ormain
ortemp
. - If the name is omitted, the defaul is
main
. - In some pragmas the
schema-name
is ignored.
More details about PRAGMA on the official SQLite docs here
SQLite schema syntax
The syntax is:
PRAGMA schema.table_info(table-name);
This one returns one row for each colum in the table.
Each row includes:
- column name
- data type
- whether or not the column can be
NULL
- the default value for the column
- the primary key in the result set is
0
for columns that are not part of the primary key - otherwise the primary key in the result set is the index of the column
Get Schema in SQLite with Python
Get the SQLite schema:
PRAGMA table_info('Users')
Let’s add it here:
$ sqlite3
sqlite> CREATE TABLE Users(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT);
sqlite> INSERT INTO Users(name, email) VALUES('homer', 'homer@simpson.com');
sqlite> SELECT * FROM Users;
1|homer|homer@simpson.com
sqlite> PRAGMA table_info('Users')
0|id|INTEGER|0||1
1|name|TEXT|0||0
2|email|TEXT|0||0
Get the SQLite schema with Python:
cur.execute("PRAGMA table_info('Courses')").fetchall()
Using the Python shell:
>>> import sqlite3
>>> conn = sqlite3.connect('users.sqlite')
>>> cur = conn.cursor()
>>> cur.execute('CREATE TABLE Users(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT)')
>>> cur.execute('INSERT INTO Users(name, email) VALUES(?, ?)', ('homer', 'homer@simpson.com'))
>>> cur.execute("PRAGMA table_info('Users')")
<sqlite3.Cursor object at 0x7f590a00cea0>
>>> cur.execute("PRAGMA table_info('Users')").fetchall()
[(0, 'id', 'INTEGER', 0, None, 1), (1, 'name', 'TEXT', 0, None, 0), (2, 'email', 'TEXT', 0, None, 0)]
>>> for row in cur.execute("PRAGMA table_info('Users')").fetchall():
... print(row)
...
(0, 'id', 'INTEGER', 0, None, 1)
(1, 'name', 'TEXT', 0, None, 0)
(2, 'email', 'TEXT', 0, None, 0)