[sqlalchemy] Re: Testing and deprecation of nested transactions
Hi, Have you tried using this pattern from the documentation? I think you can also use that while using only connections https://docs.sqlalchemy.org/en/14/orm/session_transaction.html?highlight=after_transaction_end#joining-a-session-into-an-external-transaction-such-as-for-test-suites On Saturday, 31 July 2021 at 00:24:36 UTC+2 Jonathan Vanasco wrote: > I typically do local developer testing with sqlite3, and the switch the > database to postgresql for build/deploy/ci testing in the cloud. > > For complex tests, I typically use a fresh database "image". e.g. a sqlite > file or pgdump output that is tracked in git. > > This is not the solution you're looking for, but i've found it very > useful. I spent a long time working on a testing setup like you are trying > to accomplish, but abandoned it when we built out an integrated test suite > and data had to persist across multiple database connections. > On Friday, July 30, 2021 at 4:19:35 AM UTC-4 dcab...@gmail.com wrote: > >> Hello everyone, >> >> I am working on a new project using SqlAlchemy Core 1.4 with Postgresql >> and wanted to implement the following pattern for my tests: >> >> - Before each test I would start a transaction (in a >> @pytest.fixture(autorun=True)) >> - Each test may create its own transactions >> - At the end of each test, I would rollback the transaction >> >> The purpose is to keep the database "clean" between tests and not have to >> manually delete all inserted data. >> >> However, it seems that SqlAlchemy 1.4 is deprecating nested transactions >> and that they will be removed in 2.0. >> >> Is there an alternative approach or best practice that I can use for >> isolating tests in transactions? >> >> I had an alternative idea, like: >> >> - Before each test create the first savepoint (let's call current >> savepoint N) >> - Catch any commit in the code and instead create a savepoint N+1 >> - Catch any rollback and rollback to N-1 >> >> Obviously, that seems like a lot of work and I'm not even sure if I can >> intercept begins, commits and rollbacks that easily. >> >> Alternatively, I could run upgrade and downgrade migrations on every >> test, but that would slow the test suite down a lot. >> >> Any advice and thoughts would be appreciated. >> >> Thanks! >> Dejan >> > -- 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/db653c08-55ab-4392-8200-98db8897e3d1n%40googlegroups.com.
[sqlalchemy] Re: Testing and deprecation of nested transactions
I typically do local developer testing with sqlite3, and the switch the database to postgresql for build/deploy/ci testing in the cloud. For complex tests, I typically use a fresh database "image". e.g. a sqlite file or pgdump output that is tracked in git. This is not the solution you're looking for, but i've found it very useful. I spent a long time working on a testing setup like you are trying to accomplish, but abandoned it when we built out an integrated test suite and data had to persist across multiple database connections. On Friday, July 30, 2021 at 4:19:35 AM UTC-4 dcab...@gmail.com wrote: > Hello everyone, > > I am working on a new project using SqlAlchemy Core 1.4 with Postgresql > and wanted to implement the following pattern for my tests: > > - Before each test I would start a transaction (in a > @pytest.fixture(autorun=True)) > - Each test may create its own transactions > - At the end of each test, I would rollback the transaction > > The purpose is to keep the database "clean" between tests and not have to > manually delete all inserted data. > > However, it seems that SqlAlchemy 1.4 is deprecating nested transactions > and that they will be removed in 2.0. > > Is there an alternative approach or best practice that I can use for > isolating tests in transactions? > > I had an alternative idea, like: > > - Before each test create the first savepoint (let's call current > savepoint N) > - Catch any commit in the code and instead create a savepoint N+1 > - Catch any rollback and rollback to N-1 > > Obviously, that seems like a lot of work and I'm not even sure if I can > intercept begins, commits and rollbacks that easily. > > Alternatively, I could run upgrade and downgrade migrations on every test, > but that would slow the test suite down a lot. > > Any advice and thoughts would be appreciated. > > Thanks! > Dejan > -- 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/e4e452ef-351d-4f92-a87c-1ab52ebc70ffn%40googlegroups.com.
Re: [sqlalchemy] prevent (raise exceptions) on bytestring values for non-byte types
The second option looks perfect. Will try it!
Thank you so much, Simon!
On Friday, July 30, 2021 at 1:32:42 PM UTC-4 Simon King wrote:
> I can think of a couple of options:
>
> 1. Create a TypeDecorator for String and Text columns that raises an
> error if it sees a bytestring. This will only flag the error when the
> session is flushed.
> 2. Listen for mapper_configured events, iterate over the mapper
> properties and add an "AttributeEvents.set" listener for each one.
> This should flag the error when a bytestring is assigned to a mapped
> attribute.
>
> Hope that helps,
>
> Simon
>
> On Fri, Jul 30, 2021 at 5:10 PM 'Jonathan Vanasco' via sqlalchemy
> wrote:
> >
> > Mike, thanks for replying but go back to vacation.
> >
> > Anyone else: I am thinking more about an event that can be used to
> catch, perhaps log, all bytes that go in. I only use a few column classes
> that expect bytestrings, but many that do not. I've gotten every known bug
> so far, but I'd like to make sure I'm not just lucky.
> >
> > On Thursday, July 29, 2021 at 6:05:03 PM UTC-4 Mike Bayer wrote:
> >>
> >> The Unicode datatype will emit a warning if you pass it a bytestring.
> you can use that instead of String, or use a datatype with your own
> assertions based on
> https://docs.sqlalchemy.org/en/14/core/custom_types.html#coercing-encoded-strings-to-unicode
> >>
> >>
> >>
> >> On Thu, Jul 29, 2021, at 5:17 PM, 'Jonathan Vanasco' via sqlalchemy
> wrote:
> >>
> >> I am finally at the tail end of migrating my largest (and hopefully
> last) Python2 application to Python3.
> >>
> >> An issue that has popped up a lot during this transition, is when a py3
> bytestring gets submitted into SqlAlchemy.
> >>
> >> When that happens, it looks like SqlAlchemy just passes the value into
> psycopg2, which wraps it in an object, and I get a psycopg exception that
> bubbles up to SqlAlchemy:
> >>
> >> > sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction)
> operator does not exist: character varying = bytea
> >> > LINE 3: WHERE foo = '\x626337323133...
> >> > HINT: No operator matches the given name and argument type(s). You
> might need to add explicit type casts.
> >> >
> >> > WHERE foo = %(foo)s
> >> > LIMIT %(param_1)s]
> >> > [parameters: {'foo': 0x10fe99060>, 'param_1': 1}]
> >> > (Background on this error at: http://sqlalche.me/e/13/f405)
> >>
> >> Is there an easy way to catch this in SQLAlchemy *before* sending this
> to the driver and executing it on the server? I'd like to ensure I'm
> catching everything I should, and nothing is working just by-chance.
> >>
> >>
> >>
> >>
> >> --
> >> 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/f70bf020-d120-46fb-96d1-d5509ff9b3c3n%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+...@googlegroups.com.
> > To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/d6f8d50c-9465-41bc-a293-d8295c35ecc1n%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/51d32a79-136c-4ec3-9075-b3d1f098d629n%40googlegroups.com.
Re: [sqlalchemy] prevent (raise exceptions) on bytestring values for non-byte types
I can think of a couple of options:
1. Create a TypeDecorator for String and Text columns that raises an
error if it sees a bytestring. This will only flag the error when the
session is flushed.
2. Listen for mapper_configured events, iterate over the mapper
properties and add an "AttributeEvents.set" listener for each one.
This should flag the error when a bytestring is assigned to a mapped
attribute.
Hope that helps,
Simon
On Fri, Jul 30, 2021 at 5:10 PM 'Jonathan Vanasco' via sqlalchemy
wrote:
>
> Mike, thanks for replying but go back to vacation.
>
> Anyone else: I am thinking more about an event that can be used to catch,
> perhaps log, all bytes that go in. I only use a few column classes that
> expect bytestrings, but many that do not. I've gotten every known bug so
> far, but I'd like to make sure I'm not just lucky.
>
> On Thursday, July 29, 2021 at 6:05:03 PM UTC-4 Mike Bayer wrote:
>>
>> The Unicode datatype will emit a warning if you pass it a bytestring. you
>> can use that instead of String, or use a datatype with your own assertions
>> based on
>> https://docs.sqlalchemy.org/en/14/core/custom_types.html#coercing-encoded-strings-to-unicode
>>
>>
>>
>> On Thu, Jul 29, 2021, at 5:17 PM, 'Jonathan Vanasco' via sqlalchemy wrote:
>>
>> I am finally at the tail end of migrating my largest (and hopefully last)
>> Python2 application to Python3.
>>
>> An issue that has popped up a lot during this transition, is when a py3
>> bytestring gets submitted into SqlAlchemy.
>>
>> When that happens, it looks like SqlAlchemy just passes the value into
>> psycopg2, which wraps it in an object, and I get a psycopg exception that
>> bubbles up to SqlAlchemy:
>>
>> >sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction)
>> > operator does not exist: character varying = bytea
>> >LINE 3: WHERE foo = '\x626337323133...
>> >HINT: No operator matches the given name and argument type(s). You
>> > might need to add explicit type casts.
>> >
>> >WHERE foo = %(foo)s
>> >LIMIT %(param_1)s]
>> >[parameters: {'foo': > > 0x10fe99060>, 'param_1': 1}]
>> >(Background on this error at: http://sqlalche.me/e/13/f405)
>>
>> Is there an easy way to catch this in SQLAlchemy *before* sending this to
>> the driver and executing it on the server? I'd like to ensure I'm catching
>> everything I should, and nothing is working just by-chance.
>>
>>
>>
>>
>> --
>> 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/f70bf020-d120-46fb-96d1-d5509ff9b3c3n%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/d6f8d50c-9465-41bc-a293-d8295c35ecc1n%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/CAFHwexcivJM8RxyX6cJKQRBd%3D4cB%2BZ9QtiiwmrKw0MEM%3DFLhuQ%40mail.gmail.com.
Re: [sqlalchemy] prevent (raise exceptions) on bytestring values for non-byte types
Mike, thanks for replying but go back to vacation.
Anyone else: I am thinking more about an event that can be used to catch,
perhaps log, all bytes that go in. I only use a few column classes that
expect bytestrings, but many that do not. I've gotten every known bug so
far, but I'd like to make sure I'm not just lucky.
On Thursday, July 29, 2021 at 6:05:03 PM UTC-4 Mike Bayer wrote:
> The Unicode datatype will emit a warning if you pass it a bytestring. you
> can use that instead of String, or use a datatype with your own assertions
> based on
> https://docs.sqlalchemy.org/en/14/core/custom_types.html#coercing-encoded-strings-to-unicode
>
>
>
> On Thu, Jul 29, 2021, at 5:17 PM, 'Jonathan Vanasco' via sqlalchemy wrote:
>
> I am finally at the tail end of migrating my largest (and hopefully last)
> Python2 application to Python3.
>
> An issue that has popped up a lot during this transition, is when a py3
> bytestring gets submitted into SqlAlchemy.
>
> When that happens, it looks like SqlAlchemy just passes the value into
> psycopg2, which wraps it in an object, and I get a psycopg exception that
> bubbles up to SqlAlchemy:
>
> >sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction)
> operator does not exist: character varying = bytea
> >LINE 3: WHERE foo = '\x626337323133...
> >HINT: No operator matches the given name and argument type(s). You
> might need to add explicit type casts.
> >
> >WHERE foo = %(foo)s
> >LIMIT %(param_1)s]
> >[parameters: {'foo': 0x10fe99060>, 'param_1': 1}]
> >(Background on this error at: http://sqlalche.me/e/13/f405)
>
> Is there an easy way to catch this in SQLAlchemy *before* sending this to
> the driver and executing it on the server? I'd like to ensure I'm catching
> everything I should, and nothing is working just by-chance.
>
>
>
>
> --
> 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/f70bf020-d120-46fb-96d1-d5509ff9b3c3n%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/d6f8d50c-9465-41bc-a293-d8295c35ecc1n%40googlegroups.com.
[sqlalchemy] Testing and deprecation of nested transactions
Hello everyone, I am working on a new project using SqlAlchemy Core 1.4 with Postgresql and wanted to implement the following pattern for my tests: - Before each test I would start a transaction (in a @pytest.fixture(autorun=True)) - Each test may create its own transactions - At the end of each test, I would rollback the transaction The purpose is to keep the database "clean" between tests and not have to manually delete all inserted data. However, it seems that SqlAlchemy 1.4 is deprecating nested transactions and that they will be removed in 2.0. Is there an alternative approach or best practice that I can use for isolating tests in transactions? I had an alternative idea, like: - Before each test create the first savepoint (let's call current savepoint N) - Catch any commit in the code and instead create a savepoint N+1 - Catch any rollback and rollback to N-1 Obviously, that seems like a lot of work and I'm not even sure if I can intercept begins, commits and rollbacks that easily. Alternatively, I could run upgrade and downgrade migrations on every test, but that would slow the test suite down a lot. Any advice and thoughts would be appreciated. Thanks! Dejan -- 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/3addedcc-7f74-4ae3-aff3-947a73635f7en%40googlegroups.com.
