Is there a way to get the constraints of a table in SQLite?

sqlite list constraints pragma
fran picture fran · Mar 9, 2012 · Viewed 9.2k times · Source

The command "pragma table_info('tablename')" lists the columns information and "pragma foreign_key_list('tablename')" the foreign keys. How can I display other constraints (check, unique) of a table? Only parsing the field "sql" of the table "sqlite_master"?

Answer

jftuga picture jftuga · Aug 27, 2012

I think the only way is to do this is the way you suggested, parse the sql column of the sqlite_master database.

Python code to do this:

import sqlite3

con = sqlite3.connect("example.sqlite3")
cur = con.cursor()
cur.execute("select sql from sqlite_master where type='table' and name='example_table'")
schema = cur.fetchone()
con.close()

entries = [ tmp.strip() for tmp in schema[0].splitlines() if tmp.find("constraint")>=0 or tmp.find("unique")>=0 ]
for i in entries: print(i)

Related questions

Python and SQLite: insert into table

I have a list that has 3 rows each representing a table row: >>> print list [laks,444,M] [kam,445,M] [kam,445,M] How to insert this list into a table? My table structure is: tablename(name varchar[100], age int, …

Read More
How can I get the list of a columns in a table for a SQLite database?

I am looking to retrieve a list of columns in a table. The database is the latest release of SQLite (3.6, I believe). I am looking for code that does this with a SQL query. Extra bonus points for metadata related …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More