0

I am able to access tables of name "TABLE_NAME" from the schema "SCHEMA_NAME" using the following code:

pyodbc.setDecimalSeparator(',')
password = "MYPASSWORD"
database = "MYDATABASE"
username = "MYUSERNAME"

cnxn = pyodbc.connect(DSN=database, UID=username, pwd=password)
Sql_str = "select *\
              FROM SCHEMA_NAME.TABLE_NAME"
table = pd.read_sql(Sql_str, cnxn)

This works. I am getting several tables this way, perform some merging operations and some processing in Python. Ultimately, I end up with a new Pandas DataFrame that I want to pass back.

I use the following code, using the same connection:

new_table.to_sql(name = "MYNEWTABLE", con = cnxn, schema = "SCHEMA_NAME")

However, this returns the following error message:

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', '[42S02] [IBM][CLI Driver][DB2/AIX64] SQL0204N  "MYUSERNAME.SQLITE_MASTER" is an undefined name.  SQLSTATE=42704\n (-204) (SQLExecDirectW)')

I feel like I am missing something quite fundamental, because in my head it feels intuitive that I should just be able to perform the "inverse" of the pd.read_sql() with the dataframe.to_sql() using the same connection, but obviously this is not the case.

Felix Darke
  • 35
  • 7
  • Does'nt the pandas`.to_sql(...)` method need the sqlalchemy interface ? – mao Jul 02 '21 at 08:09
  • That could be it. It does feel weird that I can use pyodbc for the read_sql( ... ) method however. However, the documentation for both methods suggests that the sqlalchemy or sqlite3 interfaces are required. I will try to set up the same connection as an sqlalchemy engine instead and see what happens. – Felix Darke Jul 02 '21 at 08:25

0 Answers0