can't use to_sql or read_sql with driver 3.4.1

[grongierisc]

When using the driver 3.4.1 with the latest version of SQLAlchemy (2.0.1), the error below is raised when using to_sql or read_sql:

Traceback (most recent call last):
  File "/Users/grongier/git/iris-fhirsqlbuilder/src/df/test.py", line 19, in <module>
    df.to_sql('iris_table', engine, if_exists='replace', schema='sqlalchemy')
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/pandas/core/generic.py", line 2987, in to_sql
    return sql.to_sql(
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/pandas/io/sql.py", line 695, in to_sql
    return pandas_sql.to_sql(
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/pandas/io/sql.py", line 1728, in to_sql
    table = self.prep_table(
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/pandas/io/sql.py", line 1631, in prep_table
    table.create()
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/pandas/io/sql.py", line 827, in create
    if self.exists():
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/pandas/io/sql.py", line 814, in exists
    return self.pd_sql.has_table(self.name, self.schema)
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/pandas/io/sql.py", line 1761, in has_table
    return insp.has_table(name, schema or self.meta.schema)
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py", line 283, in has_table
    return self.dialect.has_table(conn, table_name, schema)
  File "<string>", line 2, in has_table
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/sqlalchemy/engine/reflection.py", line 47, in cache
    return fn(self, con, *args, **kw)
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/sqlalchemy_iris/base.py", line 877, in has_table
    return bool(connection.execute(s).scalar())
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1262, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 324, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1451, in _execute_clauseelement
    ret = self._execute_context(
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1813, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1998, in _handle_dbapi_exception
    util.raise_(exc_info[1], with_traceback=exc_info[2])
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context
    self.dialect.do_execute(
  File "/Users/grongier/git/iris-fhirsqlbuilder/.venv/lib/python3.9/site-packages/sqlalchemy_iris/base.py", line 811, in do_execute
    cursor.execute(query, *params)
TypeError: execute() takes from 2 to 3 positional arguments but 4 were given

Code to reproduce:

from sqlalchemy import create_engine

import pandas as pd

engine = create_engine('iris://_SYSTEM:SYS@localhost:33782/FHIRSERVER')

# create fake dataframes with a bunch of columns types
# and a bunch of rows
# columns types are: int, float, string, datetime, bool
df = pd.DataFrame({
    'int': [1, 2, 3, 4, 5],
    'float': [1.1, 2.2, 3.3, 4.4, 5.5],
    'string': ['a', 'b', 'c', 'd', 'e'],
    'varchar': ['a', 'b', 'c', 'd', 'e'],
    'datetime': pd.date_range('20130101', periods=5),
    'bool': [True, False, True, False, True]
})

# create a table in IRIS
df.to_sql('iris_table', engine, if_exists='replace', schema='sqlalchemy', dtype={'varchar': types.VARCHAR(length=30)})

# read the table back from IRIS 
df2 = pd.read_sql('select * from sqlalchemy.iris_table', engine)

# print the dataframe
print(df2)

# print the table types in iris with sql type and class type
sql_def = """SELECT Tables.TABLE_SCHEMA, Tables.TABLE_NAME, Columns.COLUMN_NAME, Columns.DATA_TYPE,  Prop.Type
FROM INFORMATION_SCHEMA.TABLES AS Tables
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS Columns 
   ON (Columns.TABLE_SCHEMA = Tables.TABLE_SCHEMA) AND (Columns.TABLE_NAME = Tables.TABLE_NAME)
INNER JOIN %Dictionary.CompiledProperty AS Prop ON (Prop.parent = Tables.CLASSNAME and Prop.Name = Columns.COLUMN_NAME)
WHERE Tables.TABLE_NAME = 'iris_table' and Tables.TABLE_SCHEMA = 'sqlalchemy'"""

# execute the sql
df3 = pd.read_sql(sql_def, engine)

# print the dataframe
print(df3)

[daimor]

check the recent version 0.5.0 of sqlalchemy-iris

pip install sqlalchemy-iris==0.5.0

[grongierisc]

Problem solved for to_sql but not for read_sql :

Traceback (most recent call last):
  File "/Users/grongier/git/formation-template-python/test.py", line 22, in <module>
    df2 = pd.read_sql('select * from sqlalchemy.iris_table', engine)
  File "/Users/grongier/git/formation-template-python/.venv/lib/python3.9/site-packages/pandas/io/sql.py", line 590, in read_sql
    return pandas_sql.read_query(
  File "/Users/grongier/git/formation-template-python/.venv/lib/python3.9/site-packages/pandas/io/sql.py", line 1560, in read_query
    result = self.execute(*args)
  File "/Users/grongier/git/formation-template-python/.venv/lib/python3.9/site-packages/pandas/io/sql.py", line 1405, in execute
    return self.connectable.execution_options().execute(*args, **kwargs)
AttributeError: 'OptionEngine' object has no attribute 'execute'

[daimor]

This particular error with Pandas and SQLAlchemy 2.0, and it seems the issue
try with SQLAlchemy==1.4.46

[grongierisc]

ok, this works with SQLAlchemy==1.4.46, why is it happening with 2.0.X ?

[daimor]

pandas-dev/pandas#40686
pandas-dev/pandas#51015

[grongierisc]

ok then, welcome to the opensource world 🥳