1

I have a pandas dataframe which i want to write over to sql database

dfmodwh
date subkey amount age 
09/12 0012  12.8   18
09/13 0009  15.0   20

there is an existing table in sql warehouse with the same column names. The table is called dim.h2oresults

I tried

import pyodbc
conn = pyodbc.connect('dsn=azure_warehouse_dev;'
                      'Trusted_Connection=yes;')


from sqlalchemy import create_engine, MetaData, Table, select
dfmodwh.to_sql(name='dim.h2oresults',con=conn, index=False, if_exists='append')

But this just gives me an execution error. Is there a way to write to the table through pyodbc instead of sqlalchemy such that if there is a new data everyday in dfmodwh it just keeps appending and not over writing?

  • What did the error say? – Gord Thompson Jul 07 '22 at 19:11
  • @GordThompson DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)") –  Jul 07 '22 at 19:40

1 Answers1

2

I believe you are going to want to use to_sql()

df.to_sql('<Table_Name', con=<Your_Connection>, if_exists='append')
ArchAngelPwn
  • 2,891
  • 1
  • 4
  • 17
  • but this will over write the data. i plan to feed the table everyday with new data –  Jul 07 '22 at 19:37
  • The "if_exists" should allow you to write to this database and simply add the new data from the dataframe. – ArchAngelPwn Jul 07 '22 at 21:39
  • i get an error saying cur.execute(*args, **kwargs) ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)") –  Jul 08 '22 at 01:34
  • I'm assuming you changed the ' and to the connection engine and table name within your SQL database? – ArchAngelPwn Jul 08 '22 at 02:47
  • import pyodbc conn = pyodbc.connect('dsn=azure_warehouse_dev;' 'Trusted_Connection=yes;') cursor = conn.cursor() dfmodwh.to_sql('dim.h2oresults',con=conn,if_exists='append', index=False) This is my current code –  Jul 08 '22 at 03:40