Have a lot of data you want to populate into a SQL database? Most likely you have that data in a spreadsheet or can easily put it in one. In this case use the following script I wrote to convert your Spreadsheet data to SQL statements and then import them to your SQL database of choice. All that is required is putting your data in the spreadsheet with headers, exporting to TSV and then updating the script for file names and running it.
Here is the code. Enjoy!
#!/usr/bin/env python3
# Generates SQL from TSV
# Author: Jason Riedel ( Follow me on twitter - jasonriedel.com )
import pandas
import numpy as np
def parse_tsv_to_df(path):
df = pandas.read_csv(path, header=0, sep="\t", dtype=object)
# replace NaN's with empty string
df_new = df.replace(np.nan, "", regex=True)
return df_new.replace("'", "''", regex=True)
def print_sql(table_name, fname, df):
with open(fname, "w") as f:
row_values = df.values.tolist()
headers = list(df.columns)
cols = ", ".join(headers)
for row in row_values:
values = ", ".join("'{0}'".format(col) for col in row)
sql = f"INSERT INTO {table_name} ({cols}) VALUES ({values});\n"
f.write(sql)
if __name__ == "__main__":
df = parse_tsv_to_df("tsv/my_table.tsv")
print_sql("my_table", "sql/my_table_seed.sql", df)
