Syntax Error When Attempting To Insert Data Into Postgresql

I am attempting to insert parsed dta data into a postgresql database with each row being a separate variable table, and it was working until I added in the second row 'recodeid_fk'

Solution 1:

To your specific error...

The syntax error probably comes from strings {} that need quotes around them. execute() can take care of this for you automtically. Replace

execute("INSERT INTO tblv00{} (data, recodeid_fk) VALUES({}, {})".format(z, str(row[a]),29))

execute("INSERT INTO tblv00{} (data, recodeid_fk) VALUES(%s, %s)".format(z), (row[a],29))

The table name is completed the same way as before, but the the values will be filled in by execute, which inserts quotes if they are needed. Maybe execute could fill in the table name too, and we could drop format entirely, but that would be an unusual usage, and I'm guessing execute might (wrongly) put quotes in the middle of the name.

But there's a nicer approach...

Pandas includes a function for writing DataFrames to SQL tables. Postgresql is not yet supported, but in simple cases you should be able to pretend that you are connected to sqlite or MySQL database and have no trouble.

What do you intend with z here? As it is, you loop z from '1' to '9' before proceeding to the next for loop. Should the loops be nested? That is, did you mean to insert the contents dr into nine different tables called tblv001 through tblv009?

If you mean that loop to put different parts of dr into different tables, please check the indentation of your code and clarify it.

In either case, the link above should take care of the SQL insertion.

Response to Edit

It seems like t, z, and a are doing redundant things. How about:

import pandas as pd
import string

...

# Loop through columns of dr, and count them as we go.
for i, col in enumerate(dr):
    table_name = 'tblv' + string.zfill(i, 3) # e.g., tblv001 or tblv010
    df1 = DataFrame(dr[col]).reset_index()
    df1.columns = ['data', 'recodeid_fk']
    pd.io.sql.write_frame(df1, table_name, conn)

I used reset_index to make the index into a column. The new (sequential) index will not be saved by write_frame.