Insert into SQL Db from Python

Question

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?

Why not df.to_sql() using a sqlalchemy engine? Surely more simple and likely more efficient. pandas docs here and sqlalchemy connstr docs here. — s3dev
– s3dev, Commented Nov 1, 2020 at 21:56
@S3DEV - Tried that as well using the below code: t6.to_sql(api_nic_data,engine,index=False,if_exists="append",schema="dbo") The error says name api_nic_data is not defined. This is the table name, and it definitely exists — user10
– user10, Commented Nov 1, 2020 at 22:07
Interesting. Even 'DWWorking.dbo.api_Nic_Data'? I’d keep trying with this, you’re on the right track. TBH I’m not sure how much of the tablename pandas is looking for; (I avoid MSSQL at all cost). But again, you’re on the right track. — s3dev
– s3dev, Commented Nov 1, 2020 at 22:12
@S3DEV - turns out I was missing the single quotes for the table name in my code above. That fixed the 1st error, but now I see:InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect); [IM002] [Microsoft][ODBC Driver Manager] Invalid connection string attribute (0)') (Background on this error at: sqlalche.me/e/rvf5) — user10
– user10, Commented Nov 1, 2020 at 22:19
Glad that helped. I’d recommend raising a new question, and provide the (sanitised) details of the connection string you’re using to create the sqlalchemy engine for df.to_sql(). — s3dev
– s3dev, Commented Nov 1, 2020 at 22:35

Parfait · Accepted Answer · 2020-11-02 00:00:26Z

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())

s3dev · Accepted Answer · 2020-11-01 22:55:35Z

0

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.

answered Nov 1, 2020 at 22:55

s3dev

9,8713 gold badges34 silver badges49 bronze badges

Collectives™ on Stack Overflow

Insert into SQL Db from Python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related