Closed dtemkin-volpe closed 2 months ago
@ssmith55 yep, it's generating the SQLite database, then creating the SQL files by reading the table schemas from it. The relevant lines of code are below.
# Use https://alpha.sqliteviewer.app/ for verification!
os.remove(db_path / "gmns.sqlite")
create_db = package.publish(
"sqlite:///" + (db_path / "gmns.sqlite").absolute().as_posix()
)
# Get list of every table and its schema
connection = sqlite3.connect(db_path / "gmns.sqlite")
cursor = connection.cursor()
cursor.execute("SELECT name, sql FROM sqlite_master WHERE type='table';")
list_of_tables = cursor.fetchall()
for table_name, table_sql in list_of_tables:
with open(db_path / f"{table_name}.sql", "w") as table_file:
# Add "IF NOT EXISTS" condition
table_sql = table_sql.replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS")
table_file.write(table_sql)
cursor.close()
connection.close()
Uses Frictionless Framework to automatically generate SQLite database, then queries it for each tables' schema and outputs it as a
.sql
file. More details in the documentation: https://framework.frictionlessdata.io/docs/formats/sql.html