Re: [sqlalchemy] some gotchas in SQLAlchemy 2.0 and how to fix

[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.11'. 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/430d3237-e718-444c-beaf-8385bd5d0aean%40googlegroups.com
>  
> .

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/543ba79f-c125-4915-a683-b0077ef6af46%40app.fastmail.com.

[sqlalchemy] 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.11'. 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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/430d3237-e718-444c-beaf-8385bd5d0aean%40googlegroups.com.