some gotchas in SQLAlchemy 2.0 and how to fix

[Victor Olex]

Hi guys,

I came across a bunch of issues in a relatively simple code I got when upgraded to SQLA 2.0. Below I provided issues seen and solutions, but very much welcome any corrections. I thought this might come in handy for some. As a side note not since the move from like 0.4 to 0.5 or 0.6 did I experience SQLA working so different.

TypeError: MetaData.__init__() got an unexpected keyword argument 'bind'
meta = MeteaData(bind=e); meta.reflect(...) -> meta = MetaData(); meta.reflect(bind=e, ...)

TypeError: Connection.execute() got an unexpected keyword argument
connection.execute(q, par1=v1, par2=v2) -> connection.execute(q, dict(par1=v1, par2=v2))

sqlalchemy.exc.InvalidRequestError: This connection has already initialized a SQLAlchemy Transaction() object via begin() or autobegin; can't call begin() here unless rollback() or commit() is called first.
conn.execute() automatically starts transaction. If you use contect mgr with conn.begin() after that without commit() or rollback() you will get that error. Probably best to always use context manager? I would have preferred an option to not throw when connection is already in transaction, i.e. conn.begin(existing_ok=True).

https://stackoverflow.com/questions/70067023/pandas-and-sqlalchemy-df-to-sql-with-sqlalchemy-2-0-future-true-throws-an-err

TypeError: tuple indices must be integers or slices, not str
cur.one()['DocumentID'] -> cur.one().DocumentID

An insert statement into a varchar column with bound parameter being of type float resulted in different fomatting. Previouisly '0.1', currently
'0.1000000001'. PyODBC, MSSQL, fast_executemany=False.

AttributeError: 'Select' object has no attribute 'execute'
query.execute() does not work, must use conn.execute(query)

Kind regards,

V.

[Mike Bayer]

On Thu, Feb 23, 2023, at 9:53 AM, Victor Olex wrote:

Hi guys,

I came across a bunch of issues in a relatively simple code I got when upgraded to SQLA 2.0. Below I provided issues seen and solutions, but very much welcome any corrections. I thought this might come in handy for some. As a side note not since the move from like 0.4 to 0.5 or 0.6 did I experience SQLA working so different.

well it's 2.0, it's of similar magnitude as python 2 to 3.    the API changes described below are all referred towards in the migration document at https://docs.sqlalchemy.org/en/20/changelog/migration_20.html , these are all top-level API changes that are prominently noted and are also emit warnings under 1.4 when using SQLALCHEMY_WARN_20 with future=True.

TypeError: MetaData.__init__() got an unexpected keyword argument 'bind'

meta = MeteaData(bind=e); meta.reflect(...) -> meta = MetaData(); meta.reflect(bind=e, ...)

TypeError: Connection.execute() got an unexpected keyword argument

connection.execute(q, par1=v1, par2=v2) -> connection.execute(q, dict(par1=v1, par2=v2))

sqlalchemy.exc.InvalidRequestError: This connection has already initialized a SQLAlchemy Transaction() object via begin() or autobegin; can't call begin() here unless rollback() or commit() is called first.

conn.execute() automatically starts transaction. If you use contect mgr with conn.begin() after that without commit() or rollback() you will get that error. Probably best to always use context manager? I would have preferred an option to not throw when connection is already in transaction, i.e. conn.begin(existing_ok=True).

https://stackoverflow.com/questions/70067023/pandas-and-sqlalchemy-df-to-sql-with-sqlalchemy-2-0-future-true-throws-an-err

TypeError: tuple indices must be integers or slices, not str

cur.one()['DocumentID'] -> cur.one().DocumentID

An insert statement into a varchar column with bound parameter being of type float resulted in different fomatting. Previouisly '0.1', currently

'0.1000000001'. PyODBC, MSSQL, fast_executemany=False.

AttributeError: 'Select' object has no attribute 'execute'

query.execute() does not work, must use conn.execute(query)

Kind regards,

V.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/430d3237-e718-444c-beaf-8385bd5d0aean%40googlegroups.com.