How to insert column from dataframe into SQL Server with pyodbc?

Question

I'm trying to insert data from a CSV (or DataFrame) into MS SQL Server. The table has already been created, and I created the columns in SQL using pyodbc.

I'm trying to populate the first column in a table with one column from the DataFrame but I get this error:

Error: ('07002', '[07002] [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')

I've tried adding a different column to see if it was an issue with that specific column and I get the same error. (There are no null values in the column.)

conn = pyodbc.connect("Driver={SQL Server};" 
                      "Server=natsqldevap2ncr\ins2;"
                      "Database=ECDWDEV;"
                      "Trusted_Connection=yes;")

for i in df.iloc[:,0]:
    insert_query1 =  """INSERT INTO TABLE_NAME(COLUMN_NAME) 
                   VALUES 
                   (?)"""

    cursor = conn.cursor()
    result = cursor.execute(insert_query1)
    conn.commit()

Expecting all the values in the column (df.iloc[:,0]) to be inserted into the column "COLUMN_NAME" in the SQL table "TABLE_NAME".

Instead get:

Error: ('07002', '[07002] [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')

Pushing data from PYODBC to SQL is a very bad idea. I would just find another solution conpletely. Such as saving your CSV and manually uploading, or some efficient bulk-upload methods from Python. — FatihAkici
You're not doing anything with i you're just running your insert_query1 as many times as there are rows in df. Is that what you intent to do? — it's-yer-boy-chet
@FatihAkici Why is it a bad idea? What bulk-upload method would be better? — Mitch
@it's-yer-boy-chet well ideally I would just load the entire column in one go, what is the best way to do that? — Mitch

Gord Thompson Gord Thompson · Accepted Answer · 2019-08-21T22:32:41

You are getting that error message because you are trying to use a parameterized query but you are not passing the parameter values to the execute method.

Besides, you don't need to insert row-by-row by calling execute in an explicit for loop. You can use executemany instead:

# test data
df = pd.DataFrame(
    [(1, 'Josh', 10000), (2, 'Michael', 5000), (3, 'Sara', 8000)],
    columns=['id', 'name', 'salary'])
#
crsr = cnxn.cursor()
crsr.fast_executemany = True
sql = "INSERT INTO Table_1 (id) VALUES (?)"
# extract column and convert to list of single-value tuples
data = [(x,) for x in df['id']]
crsr.executemany(sql, data)
cnxn.commit()

How to insert column from dataframe into SQL Server with pyodbc?

1 Answers