How to commit df to SQL database using pyodbc?

Question

I have a connection to a database (using pyodbc) and I need to commit a df to a new table. I've done this with SQL, but don't know how to do it with a df. Any ideas on how to alter the below code to make it work for a df?

code for SQL:

import pyodbc
import pandas as pd    

conn= pyodbc.connect(r'DRIVER={Teradata};DBCNAME=foo; UID=name; PWD=password;QUIETMODE=YES;Trusted_Connection=yes') 

cursor = conn.cursor()
cursor.execute(
"""
CREATE TABLE SCHEMA.NEW_TABLE AS 
(
SELECT ... FROM ....
)
"""
)
conn.commit()

I tried this code, no errors but didn't create in the database:

import pyodbc
import pandas as pd    

conn= pyodbc.connect(r'DRIVER={Teradata};DBCNAME=foo; UID=name; PWD=password;QUIETMODE=YES;Trusted_Connection=yes') 

sheet1.to_sql(con=conn, name='new_table', schema='Schema', if_exists='replace', index=False)

Did you already look at the documentation of pandas.DataFrame.to_sql? Would that be a way at least to get an idea? Esp. the SQLAlchemy engines do look promising to me, but I may be to superficially looking at it ;-) — Dilettant
– Dilettant, Commented Jul 5, 2016 at 14:08
If you are asking about this particular SQL (CREATE TABLE .. AS SELECT ...), then you don't need to commit it, because it's a DDL, which should always be followed by an implicit commit (at least all RDBMS i know [Oracle, MySQL, MS SQL] do it this way). For more generic solution look at to_sql() as @Dilettant has already mentioned - it will does commit for you. But I would use SQLAlchemy, instead of pyODBC as it's officially supported by pandas — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Jul 5, 2016 at 14:12
I tried to use .to_sql, but probably used it incorrectly... Here was what I tried: sheet1.to_sql(con=conn, name='new_table', schema='Schema', if_exists='replace', index=False) — nonegiven72
– nonegiven72, Commented Jul 5, 2016 at 14:18

DocZerø · Accepted Answer · 2016-07-05 14:37:40Z

1

The documentation for to_sql() clearly states:

con : SQLAlchemy engine or DBAPI2 connection (legacy mode)

Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported.

Thus, you need to pass a SQLAlchemy engine to the to_sql() function to write from Pandas directly to your Teradata database.

Another way would be to dump the data to a different data structure (e.g. to_dict()) and then use pyODBC to perform DML statements on the database, preferably using binding variables to speed up processing.

answered Jul 5, 2016 at 14:37

DocZerø

8,61511 gold badges44 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to commit df to SQL database using pyodbc?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related