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. <\/p>\n\n\n\n
Here is the code. Enjoy! <\/p>\n\n\n\n
<\/p>\n\n\n\n
#!\/usr\/bin\/env python3\n# Generates SQL from TSV \n# Author: Jason Riedel ( Follow me on twitter - jasonriedel.com )\nimport pandas\nimport numpy as np\n\n\ndef parse_tsv_to_df(path):\n df = pandas.read_csv(path, header=0, sep=\"\\t\", dtype=object)\n\n # replace NaN's with empty string\n df_new = df.replace(np.nan, \"\", regex=True)\n\n return df_new.replace(\"'\", \"''\", regex=True)\n\n\ndef print_sql(table_name, fname, df):\n with open(fname, \"w\") as f:\n row_values = df.values.tolist()\n headers = list(df.columns)\n cols = \", \".join(headers)\n for row in row_values:\n values = \", \".join(\"'{0}'\".format(col) for col in row)\n sql = f\"INSERT INTO {table_name} ({cols}) VALUES ({values});\\n\"\n f.write(sql)\n\n\nif __name__ == \"__main__\":\n df = parse_tsv_to_df(\"tsv\/my_table.tsv\")\n print_sql(\"my_table\", \"sql\/my_table_seed.sql\", df)\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"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 […]<\/p>\n<\/a>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":{"0":"post-781","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-howtos","7":"h-entry","8":"hentry"},"_links":{"self":[{"href":"https:\/\/tuxlabs.com\/index.php?rest_route=\/wp\/v2\/posts\/781","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tuxlabs.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tuxlabs.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tuxlabs.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tuxlabs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=781"}],"version-history":[{"count":2,"href":"https:\/\/tuxlabs.com\/index.php?rest_route=\/wp\/v2\/posts\/781\/revisions"}],"predecessor-version":[{"id":783,"href":"https:\/\/tuxlabs.com\/index.php?rest_route=\/wp\/v2\/posts\/781\/revisions\/783"}],"wp:attachment":[{"href":"https:\/\/tuxlabs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=781"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tuxlabs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=781"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tuxlabs.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=781"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}