Re: [sqlalchemy] low-level commands in 2.0

[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.


> 
> Zsolt
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> On 25. Nov 2022 at 16:56:52, Mike Bayer  wrote:
>> 
>> 
>> 
>> 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+unsubscr...@googlegroups.com.
>>> To view this discussion on the web visit 
>>> https://groups.google.com/d/msgid/sqlalchemy/d0f06e70-f341-475e-8176-8bf0ac46ff94n%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 a topic in the 
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit 
>> https://groups.google.com/d/topic/sqlalchemy/R7GuWsXz1_c/unsubscribe.

Re: [sqlalchemy] low-level commands in 2.0

[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










On 25. Nov 2022 at 16:56:52, Mike Bayer  wrote:

>
>
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/d0f06e70-f341-475e-8176-8bf0ac46ff94n%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 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+unsubscr...@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
> 
> .
>

-- 
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 
htt

Re: [sqlalchemy] low-level commands in 2.0

[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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/d0f06e70-f341-475e-8176-8bf0ac46ff94n%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/b4e568ca-9b40-4438-9a60-b7cad1e42c7f%40app.fastmail.com.

[sqlalchemy] 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




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