low-level commands in 2.0

[zsol...@gmail.com]

Hi,

I'm trying to run low-level commands like DROP DATABASE / CREATE DATABASE, with psycopg2.

In 1.3 I used the following:

with pg_engine.connect().execution_options(isolation_level="AUTOCOMMIT",autocommit=True) as conn:
    conn.execute(command)

Now in 2.0 I run into many errors. I've read the migration guide and for me this looks like the best:

with pg_engine.connect().execution_options(isolation_level="AUTOCOMMIT") as conn:
   conn.exec_driver_sql(command)

Is this the right one? I'm confused as isolation_level is not written in the migration guide, I just had it from 1.3. But without isolation_level it doesn't work.

Also, what is the difference between exec_driver_sql and execute + text()?

Zsolt

[zsol...@gmail.com]

This is what I see with echo=True using the following block:

select pg_catalog.version()
[raw sql] {}

select current_schema()
[raw sql] {}

show standard_conforming_strings
[raw sql] {}

BEGIN (implicit; DBAPI should not BEGIN due to autocommit mode)
DROP DATABASE IF EXISTS md_server
[raw sql] {}
CREATE DATABASE md_server
[raw sql] {}
ROLLBACK using DBAPI connection.rollback(), DBAPI should ignore due to autocommit mode

[Mike Bayer]

On Thu, Nov 24, 2022, at 3:24 PM, zsol...@gmail.com wrote:

Hi,

I'm trying to run low-level commands like DROP DATABASE / CREATE DATABASE, with psycopg2.

In 1.3 I used the following:

with pg_engine.connect().execution_options(isolation_level="AUTOCOMMIT",autocommit=True) as conn:

    conn.execute(command)

Now in 2.0 I run into many errors. I've read the migration guide and for me this looks like the best:

with pg_engine.connect().execution_options(isolation_level="AUTOCOMMIT") as conn:

   conn.exec_driver_sql(command)

correct

Is this the right one? I'm confused as isolation_level is not written in the migration guide, I just had it from 1.3.

the "isolation_level" parameter wasnt changed in 2.0.  The "autocommit" execution option was removed, though: https://docs.sqlalchemy.org/en/20/changelog/migration_20.html#library-level-but-not-driver-level-autocommit-removed-from-both-core-and-orm

But without isolation_level it doesn't work.

Also, what is the difference between exec_driver_sql and execute + text()?

the text() construct uses a dialect-agnostic bound parameter and escaping format, and only uses named parameters. It also has other features that allow for text() constructs to be embedded within larger select() constructs.   exec_driver_sql is a direct-to-cursor method that allows for both named and positional parameters as handled by the DBAPI.

overall there's no need to have lots of errors if you migrate first to 1.4 and then use the 1.4/2.0 migration process at https://docs.sqlalchemy.org/en/20/changelog/migration_20.html#.  the warnings will tell you most of what you need to change.

Zsolt

--

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/d0f06e70-f341-475e-8176-8bf0ac46ff94n%40googlegroups.com.

[Zsolt Ero]

Thanks, so far my new project works well in 2.0. My question is that I'm trying to migrate to the 2.0 style form using this cheetsheet:

https://docs.sqlalchemy.org/en/20/changelog/migration_20.html#migration-orm-usage

So far I couldn't figure out how to turn this query in to 2.0 style.

session.query(cls).filter(func.lower(cls.username) == username_lower).first()

Without func.lower this is what I could came up with, however I couldn't figure out how to plug func.lower() into this one:

stmt = select(cls).filter_by(username=username_lower)

session.execute(stmt).scalar_one()

Do I need to use .where() for this query?

Also, what is the difference between .where() and .filter_by()? Just syntax and saving a bit of typing or there are more differences which could affect the performance as well?

Also, for a column with a unique constraint, what should I use scalar_one() or first()? Or in this case the difference is all about returning None vs raising an Exception?

Zsolt

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/R7GuWsXz1_c/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/b4e568ca-9b40-4438-9a60-b7cad1e42c7f%40app.fastmail.com.

[Mike Bayer]

On Sat, Nov 26, 2022, at 5:28 PM, Zsolt Ero wrote:

Thanks, so far my new project works well in 2.0. My question is that I'm trying to migrate to the 2.0 style form using this cheetsheet:

https://docs.sqlalchemy.org/en/20/changelog/migration_20.html#migration-orm-usage

So far I couldn't figure out how to turn this query in to 2.0 style.

session.query(cls).filter(func.lower(cls.username) == username_lower).first()

this would be:

stmt = select(cls).filter(func.lower(cls.username) == username_lower)

result = session.scalars(stmt).first()

that is, it's pretty much a mechanical conversion from 1.x to 2.0.

Without func.lower this is what I could came up with, however I couldn't figure out how to plug func.lower() into this one:

stmt = select(cls).filter_by(username=username_lower)

session.execute(stmt).scalar_one()

Do I need to use .where() for this query?

Also, what is the difference between .where() and .filter_by()? Just syntax and saving a bit of typing or there are more differences which could affect the performance as well?

.where() and .filter() are synonymous and select() (and Query I think) has them both.  filter_by() has always been just a keyword-arg syntax on top of .where()/filter().

Also, for a column with a unique constraint, what should I use scalar_one() or first()? Or in this case the difference is all about returning None vs raising an Exception?

one() has the Query semantics of raising errors if you dont get exactly one(), first() doesnt.

in 2.0 style queries, first() does not automatically imply LIMIT on the query, that's one bigger difference.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAKw-smDTY5dZkcMNHy7BitnV7%3D1KFXCoSOXwpH6A2avP3g3w4Q%40mail.gmail.com.