Skip to content

Instantly share code, notes, and snippets.

@gyoza
Last active November 17, 2018 18:07
Show Gist options
  • Save gyoza/7ef0d511a15b2f78104e55cf1b7d9b18 to your computer and use it in GitHub Desktop.
Save gyoza/7ef0d511a15b2f78104e55cf1b7d9b18 to your computer and use it in GitHub Desktop.
python3.6 - sqlite3 build db and check it or build it again!!! (dicts are insert ordered now)
import sqlite3
db = sqlite3.connect('yomomma.db')
tables = {
"table1" : {
"stuff" : "INTEGER unique PRIMARY KEY",
"stuff2" : "INTEGER",
"stuff3" : "INTEGER",
},
"table2" : {
"stuff" : "INTEGER unique PRIMARY KEY",
"stuff2" : "TEXT",
"stuff3" : "TEXT",
"stuff4" : "TEXT",
"stuff5" : "INTEGER",
"stuff6" : "INTEGER",
},
}
def db_build(db=None, cursor=None, build=None):
if (build and cursor and db):
table = tables[build]
col_list = ["{} {}".format(k, v) for k, v in table.items()]
sql_command = ", ".join(col_list)
print(sql_command)
cursor.execute("create table {}({})".format(build, sql_command))
db.commit()
def db_check(db):
bad_tables = []
cursor = db.cursor()
for table in tables:
print("checking {} table".format(table))
# get table data
cursor.execute("PRAGMA table_info('{}')".format(table))
col_list = [k for k in tables[table].items()]
# get results from pragma
results = cursor.fetchall()
try:
for num in range(len(col_list)):
# get item from col list
check_item = col_list[num][0]
# verify 'col' item from pragma table matches item from col_list
item = True if check_item in results[num][1] else False
except IndexError as e:
bad_tables.append(table)
print("{} table passed!".format(table))
if bad_tables:
print("bad tables found")
print(bad_tables)
for bad in bad_tables:
db_build(db=db, cursor=cursor, build=bad)
db_check(db)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment