[sqlalchemy] An error of bind processors of compiler when insert data into a table of mysql with
hi guys, recently I meet a strange error when i use sqlalchemy to insert data into a table of mysql. Traceback (most recent call last): File "C:\Program Files\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 1127, in _execute_context context = constructor(dialect, self, conn, *args) File "C:\Program Files\Python35\lib\site-packages\sqlalchemy\engine\default.py", line 694, in _init_compiled for key in compiled_params File "C:\Program Files\Python35\lib\site-packages\sqlalchemy\engine\default.py", line 694, in for key in compiled_params File "C:\Program Files\Python35\lib\site-packages\sqlalchemy\sql\sqltypes.py", line 877, in process return DBAPIBinary(value) File "C:\Program Files\Python35\lib\site-packages\pymysql\__init__.py", line 85, in Binary return bytes(x) TypeError: string argument without an encoding It seems that the the value of the variable processors in module sqlalchemy\engine\default.py line 690 has some problem param = dict( ( key, processors[key](compiled_params[key]) if key in processors else compiled_params[key] ) for key in compiled_params ) and the variable processsors was assigned at line 652 to equal to compiled. _bind_processors when I try to find out the cause of the error, I find an interesting appearance that if I watch the variable in debug mode or add the expression (in red color) if statement is not None: self.statement = statement self.can_execute = statement.supports_execution if self.can_execute: self.execution_options = statement._execution_options self._bind_processors = self._bind_processors self.string = self.process(self.statement, **compile_kwargs) into the module sqlchemy/sql/compiler.py at line 219, the error will not occur. In fact, if I add any expression about self._bind_processors, for example print(self._bind_processors) before calling the self.process method, the error will always disappear. I guess there may be some bug here. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] sqlalchemy session in same transaction as existing psycopg2 connection
On Tue, Aug 28, 2018 at 5:22 PM, 'Brian DeRocher' via sqlalchemy wrote: > > > On Tuesday, August 28, 2018 at 2:51:47 PM UTC-4, Mike Bayer wrote: >> >> On Tue, Aug 28, 2018 at 11:32 AM, 'Brian DeRocher' via sqlalchemy >> wrote: >> > Hey all, >> > >> > I'm writing some automated tests for some legacy python code using a >> > psycopg2 connection. I'd like to check data in the database by using >> > SQLAlchemy. I rollback the database transaction after each test in >> > tearDown(). >> > >> > The problem is my SQLAlchemy connection doesn't see the database >> > updates. >> > At first I thought they weren't using the same connection, but they are. >> > I'm using create_engine(..., creator=get_conn). >> > >> > The problem appears to be that DefaultDialect is rolling back the >> > transaction. See sqlalchemy/engine/default.py line 167. I had to >> > review PG >> > logs to spot this as it's not logged. >> > >> > self.do_rollback(connection.connection) >> > >> > Is this line really needed? >> >> yup (in general) > > > I ask because it doesn't seem natural to me that the job so the > DefaultDialect is to manage a transaction. >> >> >> > What would it be rolling back? >> >> all of the snapshots and/or locks that are accumulated by the >> Postgresql database as commands on the connection proceed. This >> means literally versions of the database that don't exist outside of >> the transaction, as well as simple things like table/ row locks. See >> https://www.postgresql.org/docs/9.1/static/mvcc.html . As far as why >> there is a transaction in the first place, the pep-249 DBAPI drivers >> like psycopg2 are required for a connection to be in a transaction by >> default, and "autocommit" systems are not standard per spec. >> >> > Can it be >> > avoided? >> >> the rollback you are seeing is likely the connection-pool level >> "reset", which is not strictly necessary if the code that makes use of >> the pool is absolutely disciplined about ensuring transactions are >> cleaned up before returning a connection to the pool, or as in your >> case you are linking it to some larger state and don't actually want >> the connection pool to be managing connection lifecycle. You can set >> this with pool_reset_on_return=None, see >> >> http://docs.sqlalchemy.org/en/latest/core/engines.html?highlight=reset_on_return#sqlalchemy.create_engine.params.pool_reset_on_return, >> however that docstring seems a little typo-ish and the link is broken >> so the actual list of values you can see at >> >> http://docs.sqlalchemy.org/en/latest/core/pooling.html?highlight=reset_on_return#sqlalchemy.pool.Pool.params.reset_on_return >> >> let me fix the formatting on that docstring > > > Unfortunately setting pool_reset_on_return=None did not help. There's still > a path of code down to this do_rollback(). I insert a > traceback.format_stack() near the rollback, and this is the stack at that > point in time (with pool_reset_on_return=None): looks like you missed the last line of the traceback, there is an "initialize" step that must be performed so that the dialect knows what kind of database it's dealing with. you can skip this step like this: engine = create_engine( 'postgresql+psycopg2://', creator=get_conn, pool_reset_on_return=None, _initialize=False) use that until you can fix your program to get its connections from the engine in the first place. All you need to do for that is where you have this: if db_conn is None: db_conn = psycopg2.connect(user="scott", password="tiger", dbname="test") change it to this: def get_conn(): global db_conn global engine if db_conn is None: engine = create_engine( "postgresql+psycopg2://scott:tiger@localhost/test", poolclass=StaticPool) db_conn = engine.raw_connection() return db_conn StaticPool is a "pool" that holds onto exactly one connection like a singleton. db_conn will be your psycopg2 connection inside of a transparent wrapper. the initialize will be done up front before you need to do anything. then use that same "engine" down below. > > DEBUG:root: File "iso.py", line 72, in > test_transaction() > File "iso.py", line 66, in test_transaction > user = session.query(User).get(uid) > File > "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", > line 864, in get > return self._get_impl(ident, loading.load_on_ident) > File > "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", > line 897, in _get_impl > return fallback_fn(self, key) > File > "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", > line 223, in load_on_ident > return q.one() > File > "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", > line 2814, in one > ret = self.one_or_none() > File > "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", > line
Re: [sqlalchemy] sqlalchemy session in same transaction as existing psycopg2 connection
On Tuesday, August 28, 2018 at 2:51:47 PM UTC-4, Mike Bayer wrote: > > On Tue, Aug 28, 2018 at 11:32 AM, 'Brian DeRocher' via sqlalchemy > > wrote: > > Hey all, > > > > I'm writing some automated tests for some legacy python code using a > > psycopg2 connection. I'd like to check data in the database by using > > SQLAlchemy. I rollback the database transaction after each test in > > tearDown(). > > > > The problem is my SQLAlchemy connection doesn't see the database > updates. > > At first I thought they weren't using the same connection, but they are. > > I'm using create_engine(..., creator=get_conn). > > > > The problem appears to be that DefaultDialect is rolling back the > > transaction. See sqlalchemy/engine/default.py line 167. I had to > review PG > > logs to spot this as it's not logged. > > > > self.do_rollback(connection.connection) > > > > Is this line really needed? > > yup (in general) > I ask because it doesn't seem natural to me that the job so the DefaultDialect is to manage a transaction. > > > What would it be rolling back? > > all of the snapshots and/or locks that are accumulated by the > Postgresql database as commands on the connection proceed. This > means literally versions of the database that don't exist outside of > the transaction, as well as simple things like table/ row locks. See > https://www.postgresql.org/docs/9.1/static/mvcc.html . As far as why > there is a transaction in the first place, the pep-249 DBAPI drivers > like psycopg2 are required for a connection to be in a transaction by > default, and "autocommit" systems are not standard per spec. > > > Can it be > > avoided? > > the rollback you are seeing is likely the connection-pool level > "reset", which is not strictly necessary if the code that makes use of > the pool is absolutely disciplined about ensuring transactions are > cleaned up before returning a connection to the pool, or as in your > case you are linking it to some larger state and don't actually want > the connection pool to be managing connection lifecycle. You can set > this with pool_reset_on_return=None, see > > http://docs.sqlalchemy.org/en/latest/core/engines.html?highlight=reset_on_return#sqlalchemy.create_engine.params.pool_reset_on_return, > > > however that docstring seems a little typo-ish and the link is broken > so the actual list of values you can see at > > http://docs.sqlalchemy.org/en/latest/core/pooling.html?highlight=reset_on_return#sqlalchemy.pool.Pool.params.reset_on_return > > > let me fix the formatting on that docstring > Unfortunately setting pool_reset_on_return=None did not help. There's still a path of code down to this do_rollback(). I insert a traceback.format_stack() near the rollback, and this is the stack at that point in time (with pool_reset_on_return=None): DEBUG:root: File "iso.py", line 72, in test_transaction() File "iso.py", line 66, in test_transaction user = session.query(User).get(uid) File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 864, in get return self._get_impl(ident, loading.load_on_ident) File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 897, in _get_impl return fallback_fn(self, key) File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 223, in load_on_ident return q.one() File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2814, in one ret = self.one_or_none() File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2784, in one_or_none ret = list(self) File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2855, in __iter__ return self._execute_and_instances(context) File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2876, in _execute_and_instances close_with_result=True) File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2885, in _get_bind_args **kw File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2867, in _connection_from_session conn = self.session.connection(**kw) File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1013, in connection execution_options=execution_options) File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1018, in _connection_for_bind engine, execution_options) File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 403, in _connection_for_bind conn = bind.contextual_connect() File "/home/brian/fr/venv2/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2112, in contextual_connect
Re: [sqlalchemy] sqlalchemy session in same transaction as existing psycopg2 connection
On Tue, Aug 28, 2018 at 11:32 AM, 'Brian DeRocher' via sqlalchemy wrote: > Hey all, > > I'm writing some automated tests for some legacy python code using a > psycopg2 connection. I'd like to check data in the database by using > SQLAlchemy. I rollback the database transaction after each test in > tearDown(). > > The problem is my SQLAlchemy connection doesn't see the database updates. > At first I thought they weren't using the same connection, but they are. > I'm using create_engine(..., creator=get_conn). > > The problem appears to be that DefaultDialect is rolling back the > transaction. See sqlalchemy/engine/default.py line 167. I had to review PG > logs to spot this as it's not logged. > > self.do_rollback(connection.connection) > > Is this line really needed? yup (in general) > What would it be rolling back? all of the snapshots and/or locks that are accumulated by the Postgresql database as commands on the connection proceed. This means literally versions of the database that don't exist outside of the transaction, as well as simple things like table/ row locks. See https://www.postgresql.org/docs/9.1/static/mvcc.html . As far as why there is a transaction in the first place, the pep-249 DBAPI drivers like psycopg2 are required for a connection to be in a transaction by default, and "autocommit" systems are not standard per spec. > Can it be > avoided? the rollback you are seeing is likely the connection-pool level "reset", which is not strictly necessary if the code that makes use of the pool is absolutely disciplined about ensuring transactions are cleaned up before returning a connection to the pool, or as in your case you are linking it to some larger state and don't actually want the connection pool to be managing connection lifecycle. You can set this with pool_reset_on_return=None, see http://docs.sqlalchemy.org/en/latest/core/engines.html?highlight=reset_on_return#sqlalchemy.create_engine.params.pool_reset_on_return, however that docstring seems a little typo-ish and the link is broken so the actual list of values you can see at http://docs.sqlalchemy.org/en/latest/core/pooling.html?highlight=reset_on_return#sqlalchemy.pool.Pool.params.reset_on_return let me fix the formatting on that docstring When I disable this line of code, the transaction continues and > sqlalchemy can see the updates from psyopg2. > > I've attached a demo file. > > Thanks, > Brian > > -- > 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 post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] sqlalchemy session in same transaction as existing psycopg2 connection
On Tue, Aug 28, 2018 at 5:53 PM Simon King wrote: > > On Tue, Aug 28, 2018 at 4:32 PM 'Brian DeRocher' via sqlalchemy > wrote: > > > > Hey all, > > > > I'm writing some automated tests for some legacy python code using a > > psycopg2 connection. I'd like to check data in the database by using > > SQLAlchemy. I rollback the database transaction after each test in > > tearDown(). > > > > The problem is my SQLAlchemy connection doesn't see the database updates. > > At first I thought they weren't using the same connection, but they are. > > I'm using create_engine(..., creator=get_conn). > > > > The problem appears to be that DefaultDialect is rolling back the > > transaction. See sqlalchemy/engine/default.py line 167. I had to review > > PG logs to spot this as it's not logged. > > > > self.do_rollback(connection.connection) > > > > Is this line really needed? What would it be rolling back? Can it be > > avoided? When I disable this line of code, the transaction continues and > > sqlalchemy can see the updates from psyopg2. > > > > I've attached a demo file. > > > > I haven't tried to run your code, but the usual way to connect a > session to an existing connection is to bind the session directly to > the connection: > > http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites > > Would that work for your situation? > > Hope that helps, > > Simon Sorry, I realised just after I pressed Send that you have a DBAPI-level connection rather than an SQLAlchemy connection, so my suggestion doesn't really help... For what it's worth, the "do_rollback" was apparently added here: https://bitbucket.org/zzzeek/sqlalchemy/commits/39fd3442e306f9c2981c347ab2487921f3948a61#chg-lib/sqlalchemy/engine/default.py Simon -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] sqlalchemy session in same transaction as existing psycopg2 connection
On Tue, Aug 28, 2018 at 4:32 PM 'Brian DeRocher' via sqlalchemy wrote: > > Hey all, > > I'm writing some automated tests for some legacy python code using a psycopg2 > connection. I'd like to check data in the database by using SQLAlchemy. I > rollback the database transaction after each test in tearDown(). > > The problem is my SQLAlchemy connection doesn't see the database updates. At > first I thought they weren't using the same connection, but they are. I'm > using create_engine(..., creator=get_conn). > > The problem appears to be that DefaultDialect is rolling back the > transaction. See sqlalchemy/engine/default.py line 167. I had to review PG > logs to spot this as it's not logged. > > self.do_rollback(connection.connection) > > Is this line really needed? What would it be rolling back? Can it be > avoided? When I disable this line of code, the transaction continues and > sqlalchemy can see the updates from psyopg2. > > I've attached a demo file. > I haven't tried to run your code, but the usual way to connect a session to an existing connection is to bind the session directly to the connection: http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#joining-a-session-into-an-external-transaction-such-as-for-test-suites Would that work for your situation? Hope that helps, Simon -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] sqlalchemy session in same transaction as existing psycopg2 connection
Hey all,
I'm writing some automated tests for some legacy python code using a
psycopg2 connection. I'd like to check data in the database by using
SQLAlchemy. I rollback the database transaction after each test in
tearDown().
The problem is my SQLAlchemy connection doesn't see the database updates.
At first I thought they weren't using the same connection, but they are.
I'm using create_engine(..., creator=get_conn).
The problem appears to be that DefaultDialect is rolling back the
transaction. See sqlalchemy/engine/default.py line 167. I had to review
PG logs to spot this as it's not logged.
self.do_rollback(connection.connection)
Is this line really needed? What would it be rolling back? Can it be
avoided? When I disable this line of code, the transaction continues and
sqlalchemy can see the updates from psyopg2.
I've attached a demo file.
Thanks,
Brian
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
import os
import logging
from dotenv import load_dotenv
import psycopg2
from psycopg2.extras import LoggingConnection
from sqlalchemy import Column, create_engine, Integer, MetaData, Text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
dotenv_path = os.path.join(os.path.dirname(__file__), '.env')
load_dotenv(dotenv_path)
logger = logging.getLogger(__name__)
logging.basicConfig(level=logging.DEBUG)
db_conn = None
def get_conn():
global db_conn
logging.debug('get_conn()')
if db_conn is None:
logging.debug(' theres no db_conn, must create one')
conn_string = "dbname='{0}' host='localhost' port=5432 user='{1}' password='{2}'".format(
os.getenv("DB_NAME"),
os.getenv("DB_USER"),
os.getenv("DB_PASSWORD"))
db_conn = psycopg2.connect(conn_string, connection_factory=LoggingConnection)
db_conn.initialize(logger)
else:
logging.debug(' using exising db_conn')
return db_conn
BaseCi = declarative_base(metadata=MetaData(schema='config'))
class User(BaseCi):
__tablename__ = 'user'
user_id = Column(Integer, primary_key=True)
user_status = Column(Text)
def legacy_psycopg_code(uid, status):
conn = get_conn()
cur = conn.cursor()
query = 'UPDATE config.user SET user_status = %s WHERE user_id = %s '
cur.execute(query, (status, uid,))
# Confirm it's there.
query = 'select user_status from config.user where user_id = %s'
cur.execute(query, (uid,))
uc = cur.fetchone()[0]
logging.debug("user_status is %s" % uc)
def test_transaction():
uid = 78
status = 'ex-parrot'
legacy_psycopg_code(uid, status)
# Use existing connection so we can see inside the transaction.
engine = create_engine('postgresql+psycopg2://', creator=get_conn)
Session = sessionmaker(bind=engine)
session = Session()
user = session.query(User).get(uid)
logging.debug(user.user_status)
# assertEquals(user.user_status, status)
if __name__ == "__main__":
test_transaction()
DEBUG:root:get_conn()
DEBUG:root: theres no db_conn, must create one
DEBUG:__main__:UPDATE config.user SET user_status = 'ex-parrot' WHERE user_id =
78
DEBUG:__main__:select user_status from config.user where user_id = 78
DEBUG:root:user_status is ex-parrot
DEBUG:root:get_conn()
DEBUG:root: using exising db_conn
DEBUG:__main__:select version()
DEBUG:__main__:select current_schema()
DEBUG:__main__:show transaction isolation level
DEBUG:__main__:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
DEBUG:__main__:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
DEBUG:__main__:SELECT 'x' AS some_label
DEBUG:root:DefaultDialect::initialize() calls do_rollback() here.
<-
DEBUG:__main__:show standard_conforming_strings
DEBUG:__main__:SELECT config."user".user_id AS config_user_user_id,
config."user".user_status AS config_user_user_status
FROM config."user"
WHERE config."user".user_id = 78
DEBUG:root:Active
Re: [sqlalchemy] Re: AssertionError seen for queries taking more than 30 seconds to execute
On Tue, Aug 28, 2018 at 7:56 AM, Mohit Agarwal wrote: > I will also like to know is there anytime out setting to sql alchemy query > because of which it is raising an exception after 30 seconds and going into > the except block. We have not explicitly passed statement_timeout in our > implementation. > I would need to see the complete error message you are getting so I can google it. SQLAlchemy itself has no concept of timeouts, this is something that happens either at the driver level or in the server side configuration of your database. > > On Tuesday, August 28, 2018 at 5:20:39 PM UTC+5:30, Mohit Agarwal wrote: >> >> Hi, >> We are seeing exceptions:AssertionError being raised when of our APIs has >> a long running query. In code we are rolling back transaction if any error >> is received while committing. Basically we have this general wrapper >> >> try: >> session.commit() >> except Exception as e: >> session.rollback() >> > raise e > >> >> >> >> Our sql alchemy version - 1.0.6 >> Our database - Azure SQL (sql server) >> >> >> Stack trace - >> File "/code/api/named_location/resources.py", line 258, in >> create_named_locations_dataclass >> File "/code/api/named_location/operations.py", line 94, in do_create >> File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/scoping.py", >> line 150, in do >> File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", >> line 754, in rollback >> File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", >> line 437, in rollback >> File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", >> line 273, in _restore_snapshot >> >> From the code it looks like it fails here - >> def _restore_snapshot(self, dirty_only=False): >> *assert self._is_transaction_boundary* >> What does it mean, why rollback is failing ? >> >> >> >> Thanks >> Mohit >> > -- > 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 post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] AssertionError seen for queries taking more than 30 seconds to execute
On Tue, Aug 28, 2018 at 7:50 AM, Mohit Agarwal wrote: > Hi, > We are seeing exceptions:AssertionError being raised when of our APIs has > a long running query. In code we are rolling back transaction if any error > is received while committing. Basically we have this general wrapper > > try: > session.commit() > except: > session.rollback() > > > > Our sql alchemy version - 1.0.6 > Our database - Azure SQL (sql server) > that's a very old SQLAlchemy version, I can't say what the issue is but at least would need the complete stack trace and the complete error messages including the "inner" stack trace, e.g. what you are actually catching with that "except".The session is likely already being rolled back within the scope of the "commit" and then some state issue is preventing rollback() from working again. There have been fixes in the area of Session state management over the years so the first step would be upgrading to a 1.1 or 1.2 version of SQLAlchemy to see if that resolves. > > > Stack trace - > File "/code/api/named_location/resources.py", line 258, in > create_named_locations_dataclass > File "/code/api/named_location/operations.py", line 94, in do_create > File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/scoping.py", > line 150, in do > File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", > line 754, in rollback > File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", > line 437, in rollback > File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", > line 273, in _restore_snapshot > > From the code it looks like it fails here - > def _restore_snapshot(self, dirty_only=False): > *assert self._is_transaction_boundary* > What does it mean, why rollback is failing ? > > > > Thanks > Mohit > > -- > 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 post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How to update a DateTime column with the SQL server value `NOW()`?
On Tue, Aug 28, 2018 at 5:13 AM, Vikas Prasad
wrote:
> Just in case someone stumbles upon it in future.
> I am using Postgres and I got the following results:
>
> # This worked:
> `the_sample.end_date = func.now()`
> `db.session.commit()`
>
> # This too worked:
> `setattr(the_sample, 'end_date', func.now())`
> `db.session.commit()`
>
> # This didn't work though:
> `Sample.query.filter_by(...).update({'end_date': func.now()})`
> `db.session.commit()`
>
> When using `Flask-SQLAlchemy` and setting `SQLALCHEMY=True`, the first two
> cases logs the `UPDATE` query followed by logging `COMMIT`. The last case
> didn't even logs the `UDPATE` query but directly logs a `ROLLBACK` with no
> other info.
>
> Not sure of underlying cause, just recording it so that someone from future
> saves some time.
the last one should definitely emit an UPDATE or it should be raising
an error. that you only saw a ROLLBACK suggests it raised an error and
your flask setup is suppressing the logging of that error.
>
> On Wednesday, June 15, 2016 at 8:10:30 PM UTC+5:30, Jean-Philippe Morin
> wrote:
>>
>> This is great! Thanks!
>>
>> On Wednesday, June 15, 2016 at 10:36:07 AM UTC-4, Mike Bayer wrote:
>>>
>>>
>>>
>>> On 06/15/2016 10:13 AM, Jean-Philippe Morin wrote:
>>> > (I am using PostgreSQL)
>>> >
>>> > I want to use the TIMESTAMP values of the SQL server machine instead of
>>> > the python `datetime.utcnow()` value of the WEB server machines.
>>> >
>>> > There could be latencies or time diffierences between machines, so I
>>> > would like to use the database server date and time as a common ground.
>>> >
>>> > Here is an example of what I would like to do:
>>> >
>>> > |
>>> > classSample(Base):
>>> > start_date
>>> > =Column('start_date',DateTime,server_default=func.now(),nullable=False)
>>> > last_update_date
>>> >
>>> > =Column('last_update_date',DateTime,server_default=func.now(),onupdate=func.now(),nullable=False)
>>> > end_date =Column('end_date',DateTime,nullable=True)
>>> >
>>> > |
>>> >
>>> > When the model is created, its `start_date` column is set to the
>>> > default
>>> > DateTime value of the SQL server by using `server_default=func.now()`.
>>> > Then, on every updates made on the model, its `last_update_date` is set
>>> > to the current DateTime value of the SQL server by using
>>> > `onupdate=func.now()`.
>>> >
>>> > |
>>> > defstart_acquisition():
>>> > new_sample =Sample()
>>> > # ... set sample columns ...
>>> > DBSession.add(new_sample)
>>> > DBSession.commit()
>>> >
>>> > defstop_acquisition(sample_id):
>>> > the_sample =DBSession.query(Sample).filter_by(...)
>>> > # ... set sample columns ...
>>> > the_sample.end_date =???
>>> > DBSession.commit()
>>> >
>>> > |
>>> >
>>> > How can I tell SQLAlchemy that on THAT particular update in
>>> > `stop_acquisition`, I want to set the `end_date` column to the
>>> > `func.now()` of the SQL server with the rest of the other modified
>>> > columns?
>>>
>>> just assign func.now() to it, it will render as that SQL expression in
>>> the UPDATE.
>>>
>>>
>>> http://docs.sqlalchemy.org/en/rel_1_0/orm/persistence_techniques.html#embedding-sql-insert-update-expressions-into-a-flush
>>>
>>>
>>>
>>>
>>> >
>>> > What would be the proper way of doing that? Is there something like an
>>> > `HybridProperty.expression` that could be used for inserts and updates?
>>> >
>>> > --
>>> > 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 post to this group, send email to sqlal...@googlegroups.com
>>> > .
>>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>>> > For more options, visit https://groups.google.com/d/optout.
>
> --
> 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 post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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
[sqlalchemy] Re: AssertionError seen for queries taking more than 30 seconds to execute
I will also like to know is there anytime out setting to sql alchemy query because of which it is raising an exception after 30 seconds and going into the except block. We have not explicitly passed statement_timeout in our implementation. On Tuesday, August 28, 2018 at 5:20:39 PM UTC+5:30, Mohit Agarwal wrote: > > Hi, > We are seeing exceptions:AssertionError being raised when of our APIs has > a long running query. In code we are rolling back transaction if any error > is received while committing. Basically we have this general wrapper > > try: > session.commit() > except Exception as e: > session.rollback() > raise e > > > > Our sql alchemy version - 1.0.6 > Our database - Azure SQL (sql server) > > > Stack trace - > File "/code/api/named_location/resources.py", line 258, in > create_named_locations_dataclass > File "/code/api/named_location/operations.py", line 94, in do_create > File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/scoping.py", > line 150, in do > File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", > line 754, in rollback > File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", > line 437, in rollback > File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", > line 273, in _restore_snapshot > > From the code it looks like it fails here - > def _restore_snapshot(self, dirty_only=False): > *assert self._is_transaction_boundary* > What does it mean, why rollback is failing ? > > > > Thanks > Mohit > -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] AssertionError seen for queries taking more than 30 seconds to execute
Hi, We are seeing exceptions:AssertionError being raised when of our APIs has a long running query. In code we are rolling back transaction if any error is received while committing. Basically we have this general wrapper try: session.commit() except: session.rollback() Our sql alchemy version - 1.0.6 Our database - Azure SQL (sql server) Stack trace - File "/code/api/named_location/resources.py", line 258, in create_named_locations_dataclass File "/code/api/named_location/operations.py", line 94, in do_create File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/scoping.py", line 150, in do File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 754, in rollback File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 437, in rollback File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 273, in _restore_snapshot >From the code it looks like it fails here - def _restore_snapshot(self, dirty_only=False): *assert self._is_transaction_boundary* What does it mean, why rollback is failing ? Thanks Mohit -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] How to update a DateTime column with the SQL server value `NOW()`?
Just in case someone stumbles upon it in future.
I am using Postgres and I got the following results:
# This worked:
`the_sample.end_date = func.now()`
`db.session.commit()`
# This too worked:
`setattr(the_sample, 'end_date', func.now())`
`db.session.commit()`
# This didn't work though:
`Sample.query.filter_by(...).update({'end_date': func.now()})`
`db.session.commit()`
When using `Flask-SQLAlchemy` and setting `SQLALCHEMY=True`, the first two
cases logs the `UPDATE` query followed by logging `COMMIT`. The last case
didn't even logs the `UDPATE` query but directly logs a `ROLLBACK` with no
other info.
Not sure of underlying cause, just recording it so that someone from future
saves some time.
On Wednesday, June 15, 2016 at 8:10:30 PM UTC+5:30, Jean-Philippe Morin
wrote:
>
> This is great! Thanks!
>
> On Wednesday, June 15, 2016 at 10:36:07 AM UTC-4, Mike Bayer wrote:
>>
>>
>>
>> On 06/15/2016 10:13 AM, Jean-Philippe Morin wrote:
>> > (I am using PostgreSQL)
>> >
>> > I want to use the TIMESTAMP values of the SQL server machine instead of
>> > the python `datetime.utcnow()` value of the WEB server machines.
>> >
>> > There could be latencies or time diffierences between machines, so I
>> > would like to use the database server date and time as a common ground.
>> >
>> > Here is an example of what I would like to do:
>> >
>> > |
>> > classSample(Base):
>> > start_date
>> > =Column('start_date',DateTime,server_default=func.now(),nullable=False)
>> > last_update_date
>> >
>> =Column('last_update_date',DateTime,server_default=func.now(),onupdate=func.now(),nullable=False)
>>
>>
>> > end_date =Column('end_date',DateTime,nullable=True)
>> >
>> > |
>> >
>> > When the model is created, its `start_date` column is set to the
>> default
>> > DateTime value of the SQL server by using `server_default=func.now()`.
>> > Then, on every updates made on the model, its `last_update_date` is set
>> > to the current DateTime value of the SQL server by using
>> > `onupdate=func.now()`.
>> >
>> > |
>> > defstart_acquisition():
>> > new_sample =Sample()
>> > # ... set sample columns ...
>> > DBSession.add(new_sample)
>> > DBSession.commit()
>> >
>> > defstop_acquisition(sample_id):
>> > the_sample =DBSession.query(Sample).filter_by(...)
>> > # ... set sample columns ...
>> > the_sample.end_date =???
>> > DBSession.commit()
>> >
>> > |
>> >
>> > How can I tell SQLAlchemy that on THAT particular update in
>> > `stop_acquisition`, I want to set the `end_date` column to the
>> > `func.now()` of the SQL server with the rest of the other modified
>> columns?
>>
>> just assign func.now() to it, it will render as that SQL expression in
>> the UPDATE.
>>
>>
>> http://docs.sqlalchemy.org/en/rel_1_0/orm/persistence_techniques.html#embedding-sql-insert-update-expressions-into-a-flush
>>
>>
>>
>>
>>
>> >
>> > What would be the proper way of doing that? Is there something like an
>> > `HybridProperty.expression` that could be used for inserts and updates?
>> >
>> > --
>> > 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 post to this group, send email to sqlal...@googlegroups.com
>> > .
>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>> > For more options, visit https://groups.google.com/d/optout.
>>
>
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
