CODEWITHSUNDEEP
pythonsql-server
user10
user10

Reputation: 167

Insert into SQL Db from Python

I am looking to insert a python dataframe into MSSQL table using the below code:

conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};\
                       Server=esql.ecs.local;\
                       Database=drgt;\
                       Trusted_Connection=yes;')
cursor = conn.cursor()  #Create cursor


for row_count in range(0, t6.shape[0]):
    chunk = t6.iloc[row_count:row_count+1,:].values.tolist()
    tuple_of_tuples = tuple(tuple(x) for x in chunk)
    cursor.executemany("insert into DWWorking.dbo.api_Nic_Data"+"([[a],[b],[c],[d],[e],[f],\
                                                      [g],[h],[i],[j],[k],[l],[m],[n],\
                                                      [o],[p],[q],[r],[s],[t],[u],[v],\
                                                      [w],[x],[y],[z],[ab],[cd],\
                                                      [ef],[gh],[ij],[kl]])\
                                                        values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                                                                    tuple_of_tuples)

The cursor is opened and I checked that the table exists and has the required columns. I still receive the below error while executing:

ProgrammingError: ('The SQL contains 0 parameter markers, but 33 parameters were supplied', 'HY000')

How can I resolve?

Upvotes: 0

Views: 399

Answers (2)

Parfait
Parfait

Reputation: 107767

Original query should work with proper SQL that does not have brackets enclosing the INSERT INTO column list. Additionally, executemany should not require any for loop across data frame rows and can be faster than df.to_sql. Also, table and column identifiers should not use single quotes.

By the way, for easier multi-line strings, consider Python's triple quote string. Do note, since Pandas v0.24, .to_numpy is recommended method over .values (see docs). Below assumes data frame contains exactly 32 columns that each map to corresponding table column.

sql = """INSERT INTO DWWorking.dbo.api_Nic_Data 
            ([a],[b],[c],[d],[e],[f],[g],[h],[i],[j],
             [k],[l],[m],[n],[o],[p],[q],[r],[s],[t],
             [u],[v] [w],[x],[y],[z],[ab],[cd],[ef],[gh],[ij],[kl])
         VALUES (?,?,?,?,?,?,?,?,?,?,
                 ?,?,?,?,?,?,?,?,?,?,
                 ?,?,?,?,?,?,?,?,?,?,?,?)
      """ 

cursor.executemany(sql, t6.to_numpy().tolist())

Upvotes: 1

s3dev
s3dev

Reputation: 9721

To provide a proper answer for future readers ...

Following the comments, it would be more straight-forward and likely more efficient to use the following:

  • pandas DataFrame.to_sql() function
  • sqlalchemy engine object

The docs for both can be found here:

As use cases for this can vary greatly from case to case, please refer to the official docs for code examples.

Upvotes: 0

Related Questions