sqlalchemy-tickets Mailing List for SQLAlchemy (Page 17)
Brought to you by:
zzzeek
Menu
▾
▴
sqlalchemy-tickets — trac tickets and updates forwarded to this list
You can subscribe to this list here.
| 2006 |
Jan
|
Feb
|
Mar
(174) |
Apr
(50) |
May
(71) |
Jun
(129) |
Jul
(113) |
Aug
(141) |
Sep
(82) |
Oct
(142) |
Nov
(97) |
Dec
(72) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2007 |
Jan
(159) |
Feb
(213) |
Mar
(156) |
Apr
(151) |
May
(58) |
Jun
(166) |
Jul
(296) |
Aug
(198) |
Sep
(89) |
Oct
(133) |
Nov
(150) |
Dec
(122) |
| 2008 |
Jan
(144) |
Feb
(65) |
Mar
(71) |
Apr
(69) |
May
(143) |
Jun
(111) |
Jul
(113) |
Aug
(159) |
Sep
(81) |
Oct
(135) |
Nov
(107) |
Dec
(200) |
| 2009 |
Jan
(168) |
Feb
(109) |
Mar
(141) |
Apr
(128) |
May
(119) |
Jun
(132) |
Jul
(136) |
Aug
(154) |
Sep
(151) |
Oct
(181) |
Nov
(223) |
Dec
(169) |
| 2010 |
Jan
(103) |
Feb
(209) |
Mar
(201) |
Apr
(183) |
May
(134) |
Jun
(113) |
Jul
(110) |
Aug
(159) |
Sep
(138) |
Oct
(96) |
Nov
(116) |
Dec
(94) |
| 2011 |
Jan
(97) |
Feb
(188) |
Mar
(157) |
Apr
(158) |
May
(118) |
Jun
(102) |
Jul
(137) |
Aug
(113) |
Sep
(104) |
Oct
(108) |
Nov
(91) |
Dec
(162) |
| 2012 |
Jan
(189) |
Feb
(136) |
Mar
(153) |
Apr
(142) |
May
(90) |
Jun
(141) |
Jul
(67) |
Aug
(77) |
Sep
(113) |
Oct
(68) |
Nov
(101) |
Dec
(122) |
| 2013 |
Jan
(60) |
Feb
(77) |
Mar
(77) |
Apr
(129) |
May
(189) |
Jun
(155) |
Jul
(106) |
Aug
(123) |
Sep
(53) |
Oct
(142) |
Nov
(78) |
Dec
(102) |
| 2014 |
Jan
(143) |
Feb
(93) |
Mar
(35) |
Apr
(26) |
May
(27) |
Jun
(41) |
Jul
(45) |
Aug
(27) |
Sep
(37) |
Oct
(24) |
Nov
(22) |
Dec
(20) |
| 2015 |
Jan
(17) |
Feb
(15) |
Mar
(34) |
Apr
(55) |
May
(33) |
Jun
(31) |
Jul
(27) |
Aug
(17) |
Sep
(22) |
Oct
(26) |
Nov
(27) |
Dec
(22) |
| 2016 |
Jan
(20) |
Feb
(24) |
Mar
(23) |
Apr
(13) |
May
(17) |
Jun
(14) |
Jul
(31) |
Aug
(23) |
Sep
(24) |
Oct
(31) |
Nov
(23) |
Dec
(16) |
| 2017 |
Jan
(24) |
Feb
(20) |
Mar
(27) |
Apr
(24) |
May
(28) |
Jun
(18) |
Jul
(18) |
Aug
(23) |
Sep
(30) |
Oct
(17) |
Nov
(12) |
Dec
(12) |
| 2018 |
Jan
(27) |
Feb
(23) |
Mar
(13) |
Apr
(19) |
May
(21) |
Jun
(29) |
Jul
(11) |
Aug
(22) |
Sep
(14) |
Oct
(9) |
Nov
(24) |
Dec
|
|
From: Michael B. <iss...@bi...> - 2017-03-23 21:55:43
|
New issue 3944: support for post-loading of relationships, joined inh subtables via IN loading https://bitbucket.org/zzzeek/sqlalchemy/issues/3944/support-for-post-loading-of-relationships Michael Bayer: this would be a new loader "selectin" for relationships that does "SELECT related FROM parent JOIN related WHERE parent.id IN (...loaded ids...)" and a similar idea for joined inheritance. The loader fires within a new section of the instance load that is after each batch of objects is loaded. because it's per-batch it would also work with yield-per. |
|
From: Nick H. <iss...@bi...> - 2017-03-21 17:27:17
|
New issue 3943: engine.has_table() throws an error instead of returning False when a table does not exist https://bitbucket.org/zzzeek/sqlalchemy/issues/3943/enginehas_table-throws-an-error-instead-of Nick Hahner: Hi there, I've got the following versions: SQLAlchemy==1.1.6 mysql-connector==2.1.4 When running the following code I get an error: ``` #!python import mysql.connector from sqlalchemy import create_engine engine = create_engine('mysql://', creator=lambda: mysql.connector.connect( option_files='/Users/me/.mylogin.cnf' ) ) engine.has_table('table_that_exists', schema='my_schema') # -> returns True engine.has_table('table_that_does_not_exist', schema='my_schema') --------------------------------------------------------------------------- ProgrammingError Traceback (most recent call last) <ipython-input-18-c38b6b523753> in <module>() ----> 1 engine.has_table('table_that_does_not_exist',schema='my_schema') /usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in has_table(self, table_name, schema) 2131 2132 """ -> 2133 return self.run_callable(self.dialect.has_table, table_name, schema) 2134 2135 def _wrap_pool_connect(self, fn, connection): /usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in run_callable(self, callable_, *args, **kwargs) 2034 """ 2035 with self.contextual_connect() as conn: -> 2036 return conn.run_callable(callable_, *args, **kwargs) 2037 2038 def execute(self, statement, *multiparams, **params): /usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in run_callable(self, callable_, *args, **kwargs) 1523 1524 """ -> 1525 return callable_(self, *args, **kwargs) 1526 1527 def _run_visitor(self, visitorcallable, element, **kwargs): /usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.pyc in has_table(self, connection, table_name, schema) 1659 try: 1660 rs = connection.execution_options( -> 1661 skip_user_error_events=True).execute(st) 1662 have = rs.fetchone() is not None 1663 rs.close() /usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, object, *multiparams, **params) 937 """ 938 if isinstance(object, util.string_types[0]): --> 939 return self._execute_text(object, multiparams, params) 940 try: 941 meth = object._execute_on_connection /usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_text(self, statement, multiparams, params) 1095 statement, 1096 parameters, -> 1097 statement, parameters 1098 ) 1099 if self._has_events or self.engine._has_events: /usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args) 1187 parameters, 1188 cursor, -> 1189 context) 1190 1191 if self._has_events or self.engine._has_events: /usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context) 1394 ) 1395 else: -> 1396 util.reraise(*exc_info) 1397 1398 finally: /usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args) 1180 statement, 1181 parameters, -> 1182 context) 1183 except BaseException as e: 1184 self._handle_dbapi_exception( /usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in do_execute(self, cursor, statement, parameters, context) 468 469 def do_execute(self, cursor, statement, parameters, context=None): --> 470 cursor.execute(statement, parameters) 471 472 def do_execute_no_params(self, cursor, statement, context=None): /usr/local/lib/python2.7/site-packages/mysql/connector/cursor.pyc in execute(self, operation, params, multi) 513 else: 514 try: --> 515 self._handle_result(self._connection.cmd_query(stmt)) 516 except errors.InterfaceError: 517 if self._connection._have_next_result: # pylint: disable=W0212 /usr/local/lib/python2.7/site-packages/mysql/connector/connection.pyc in cmd_query(self, query, raw, buffered, raw_as_string) 486 if not isinstance(query, bytes): 487 query = query.encode('utf-8') --> 488 result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query)) 489 490 if self._have_next_result: /usr/local/lib/python2.7/site-packages/mysql/connector/connection.pyc in _handle_result(self, packet) 393 return self._handle_eof(packet) 394 elif packet[4] == 255: --> 395 raise errors.get_exception(packet) 396 397 # We have a text result set ProgrammingError: 1146 (42S02): Table 'my_schema.table_that_does_not_exist' doesn't exist ``` the ~/.mylogin.cnf' file is an unencrypted config file https://dev.mysql.com/doc/refman/5.7/en/option-files.html |
|
From: Markus M. <iss...@bi...> - 2017-03-18 16:43:56
|
New issue 3942: Add a way to fetch a model or create one if it doesn't exist https://bitbucket.org/zzzeek/sqlalchemy/issues/3942/add-a-way-to-fetch-a-model-or-create-one Markus Meskanen: Django has a `get_or_create()` method for times when you don't know if data for an entity already exists. There are multiple implementations available for SQLAlchemy at http://stackoverflow.com/questions/2546207/does-sqlalchemy-have-an-equivalent-of-djangos-get-or-create/, but I thought it would be nice to have a native support for such widely used feature. I'm not sure of the ideal implementation, but one that pleases my eye is to add a keyword arugment for `one()` etc: session.query(Player).filter_by(id=my_player_id).one(create=True) Or simply add a new method: session.query(Player).filter_by(id=my_player_id).get_or_create() |
|
From: Bartek W. <iss...@bi...> - 2017-03-17 15:10:28
|
New issue 3941: Premature autoflush in _AssociationList.append https://bitbucket.org/zzzeek/sqlalchemy/issues/3941/premature-autoflush-in Bartek Wójcicki: Appending to association list fails, because object is flushed before it is appended (and appending sets primary key value). Minimal example of this issue below, tested with sqlalchemy-1.1.6: ``` #!python from sqlalchemy import ( Boolean, Column, ForeignKey, Integer, String, engine_from_config, ) from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.associationproxy import association_proxy from sqlalchemy.orm import ( backref, relationship, scoped_session, sessionmaker, ) CONFIG = { 'sqlalchemy.url': 'postgresql+psycopg2://postgres:postgres@localhost/dummy' } engine = engine_from_config(CONFIG) DBSession = scoped_session(sessionmaker()) DBSession.configure(bind=engine) Base = declarative_base() class UserAlias(Base): __tablename__ = 'user_aliases' user_id = Column(Integer, ForeignKey('user.id'), primary_key=True) alias_id = Column(Integer, ForeignKey('aliases.id'), primary_key=True) valid = Column(Boolean) alias = relationship('Alias', backref=backref('user_assocs', uselist=False)) user = relationship('User', backref=backref('alias_assocs')) def __init__(self, alias, valid=True): self.alias = alias self.valid = valid class Alias(Base): __tablename__ = 'aliases' id = Column(Integer, primary_key=True) name = Column('name', String(64)) user = association_proxy('user_assocs', 'user') def __init__(self, name): self.name = name class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key=True) name = Column(String(64)) aliases = association_proxy('alias_assocs', 'alias') def __init__(self, name): self.name = name def test_assoc(): user = User(name='John') DBSession.add(user) DBSession.commit() johny = Alias(name='Johny') user.aliases.append(johny) DBSession.commit() jan = Alias(name='Jan') DBSession.add(jan) # in my real use case proxied object is already attached to session before appending, here I simulate it with DBSession.add # with DBSession.no_autoflush: # works with autoflush disabled # user.aliases.append(jan) user.aliases.append(jan) # autoflush fails here DBSession.commit() return user test_assoc() ``` and stacktrace: ``` #! /home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/sql/crud.py:692: SAWarning: Column 'user_aliases.user_id' is marked as a member of the primary key for table 'user_aliases', but has no Python-side or server-side default generator indicated, nor does it indicate 'autoincrement=True' or 'nullable=True', and no explicit value is passed. Primary key columns typically may not store NULL. Note that as of SQLAlchemy 1.1, 'autoincrement=True' must be indicated explicitly for composite (e.g. multicolumn) primary keys if AUTO_INCREMENT/SERIAL/IDENTITY behavior is expected for one of the columns in the primary key. CREATE TABLE statements are impacted by this change as well on most backends. util.warn(msg) Traceback (most recent call last): File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context context) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute cursor.execute(statement, parameters) psycopg2.IntegrityError: null value in column "user_id" violates not-null constraint DETAIL: Failing row contains (null, 82, t). The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/home/bartek/dev/sqlalchemy-test/sqlalchemy_test/models.py", line 85, in <module> test_assoc() File "/home/bartek/dev/sqlalchemy-test/sqlalchemy_test/models.py", line 79, in test_assoc user.aliases.append(jan) # autoflush fails here File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/ext/associationproxy.py", line 610, in append self.col.append(item) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/ext/associationproxy.py", line 509, in <lambda> col = property(lambda self: self.lazy_collection()) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/ext/associationproxy.py", line 467, in __call__ return getattr(obj, self.target) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/attributes.py", line 237, in __get__ return self.impl.get(instance_state(instance), dict_) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/attributes.py", line 584, in get value = self.callable_(state, passive) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/strategies.py", line 557, in _load_for_state return self._emit_lazyload(session, state, ident_key, passive) File "<string>", line 1, in <lambda> File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/strategies.py", line 635, in _emit_lazyload result = q.all() File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2679, in all return list(self) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2830, in __iter__ self.session._autoflush() File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 1375, in _autoflush util.raise_from_cause(e) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise raise value File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 1365, in _autoflush self.flush() File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2139, in flush self._flush(objects) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2259, in _flush transaction.rollback(_capture_exception=True) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise raise value File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2223, in _flush flush_context.execute() File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/unitofwork.py", line 389, in execute rec.execute(self) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/unitofwork.py", line 548, in execute uow File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj mapper, table, insert) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 835, in _emit_insert_statements execute(statement, params) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 945, in execute return meth(self, multiparams, params) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement compiled_sql, distilled_params File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context context) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception exc_info File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise raise value.with_traceback(tb) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context context) File "/home/bartek/envs/sqlalchemy-test/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (psycopg2.IntegrityError) null value in column "user_id" violates not-null constraint DETAIL: Failing row contains (null, 82, t). [SQL: 'INSERT INTO user_aliases (alias_id, valid) VALUES (%(alias_id)s, %(valid)s)'] [parameters: {'alias_id': 82, 'valid': True}] ``` Autoflush occurs in _AssociationList append method: ``` #!python def append(self, value): item = self._create(value) self.col.append(item) ``` If _create was called before self.col is evaluted created object would not be autoflushed prematurely. |
|
From: Livio B. <iss...@bi...> - 2017-03-16 15:55:06
|
New issue 3940: bulk_insert_mappings does not group inserts properly. https://bitbucket.org/zzzeek/sqlalchemy/issues/3940/bulk_insert_mappings-does-not-group Livio Bieri: ### Summary `bulk_insert_mappings` does not group inserts properly. ### Example ``` from sqlalchemy import Column, VARCHAR, SmallInteger, create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import Session Base = declarative_base() dburl = 'mysql://root:root@127.0.0.1:3306/dirtyfeets' engine = create_engine(dburl, echo=True) class Whatever(Base): __tablename__ = 'whatever' id = Column('id', SmallInteger, nullable=False, index=True, primary_key=True) foo = Column(VARCHAR(255), nullable=False) bar = Column(VARCHAR(255), nullable=False) if __name__ == "__main__": mapping = [{'id': 1, 'foo': '_', 'bar': '_'}, {'id': 2, 'bar': '_'}, {'id': 3, 'foo': '_', 'bar': '_'}, {'id': 4, 'bar': '_'}, {'id': 5, 'foo': '_', 'bar': '_'}, {'id': 6, 'bar': '_'}, {'id': 7, 'foo': '_', 'bar': '_'}, {'id': 8, 'bar': '_'}, {'id': 9, 'foo': '_', 'bar': '_'}] session = Session(bind=engine) session.bulk_insert_mappings(Whatever, mapping) session.commit() ``` ### Actual ``` 017-03-16 16:26:41,010 INFO sqlalchemy.engine.base.Engine () 2017-03-16 16:26:41,011 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin' 2017-03-16 16:26:41,011 INFO sqlalchemy.engine.base.Engine () 2017-03-16 16:26:41,013 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1 2017-03-16 16:26:41,013 INFO sqlalchemy.engine.base.Engine () 2017-03-16 16:26:41,014 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1 2017-03-16 16:26:41,014 INFO sqlalchemy.engine.base.Engine () 2017-03-16 16:26:41,015 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1 2017-03-16 16:26:41,016 INFO sqlalchemy.engine.base.Engine () 2017-03-16 16:26:41,018 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2017-03-16 16:26:41,019 INFO sqlalchemy.engine.base.Engine INSERT INTO whatever (id, foo, bar) VALUES (%s, %s, %s) 2017-03-16 16:26:41,019 INFO sqlalchemy.engine.base.Engine (1, '_', '_') 2017-03-16 16:26:41,020 INFO sqlalchemy.engine.base.Engine INSERT INTO whatever (id, bar) VALUES (%s, %s) 2017-03-16 16:26:41,021 INFO sqlalchemy.engine.base.Engine (2, '_') 2017-03-16 16:26:41,021 INFO sqlalchemy.engine.base.Engine INSERT INTO whatever (id, foo, bar) VALUES (%s, %s, %s) 2017-03-16 16:26:41,022 INFO sqlalchemy.engine.base.Engine (3, '_', '_') 2017-03-16 16:26:41,022 INFO sqlalchemy.engine.base.Engine INSERT INTO whatever (id, bar) VALUES (%s, %s) 2017-03-16 16:26:41,023 INFO sqlalchemy.engine.base.Engine (4, '_') 2017-03-16 16:26:41,023 INFO sqlalchemy.engine.base.Engine INSERT INTO whatever (id, foo, bar) VALUES (%s, %s, %s) 2017-03-16 16:26:41,023 INFO sqlalchemy.engine.base.Engine (5, '_', '_') 2017-03-16 16:26:41,024 INFO sqlalchemy.engine.base.Engine INSERT INTO whatever (id, bar) VALUES (%s, %s) 2017-03-16 16:26:41,024 INFO sqlalchemy.engine.base.Engine (6, '_') 2017-03-16 16:26:41,025 INFO sqlalchemy.engine.base.Engine INSERT INTO whatever (id, foo, bar) VALUES (%s, %s, %s) 2017-03-16 16:26:41,025 INFO sqlalchemy.engine.base.Engine (7, '_', '_') 2017-03-16 16:26:41,026 INFO sqlalchemy.engine.base.Engine INSERT INTO whatever (id, bar) VALUES (%s, %s) 2017-03-16 16:26:41,026 INFO sqlalchemy.engine.base.Engine (8, '_') 2017-03-16 16:26:41,027 INFO sqlalchemy.engine.base.Engine INSERT INTO whatever (id, foo, bar) VALUES (%s, %s, %s) 2017-03-16 16:26:41,027 INFO sqlalchemy.engine.base.Engine (9, '_', '_') 2017-03-16 16:26:41,028 INFO sqlalchemy.engine.base.Engine COMMIT ``` ### Expected ``` EXPECTED LOG, two inserts for each group: 2017-03-16 16:31:57,187 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode' 2017-03-16 16:31:57,187 INFO sqlalchemy.engine.base.Engine () 2017-03-16 16:31:57,191 INFO sqlalchemy.engine.base.Engine SELECT DATABASE() 2017-03-16 16:31:57,191 INFO sqlalchemy.engine.base.Engine () 2017-03-16 16:31:57,193 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin' 2017-03-16 16:31:57,193 INFO sqlalchemy.engine.base.Engine () 2017-03-16 16:31:57,195 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1 2017-03-16 16:31:57,196 INFO sqlalchemy.engine.base.Engine () 2017-03-16 16:31:57,198 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1 2017-03-16 16:31:57,198 INFO sqlalchemy.engine.base.Engine () 2017-03-16 16:31:57,200 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1 2017-03-16 16:31:57,200 INFO sqlalchemy.engine.base.Engine () 2017-03-16 16:31:57,204 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2017-03-16 16:31:57,205 INFO sqlalchemy.engine.base.Engine INSERT INTO whatever (id, bar) VALUES (%s, %s) 2017-03-16 16:31:57,205 INFO sqlalchemy.engine.base.Engine ((2, '_'), (4, '_'), (6, '_'), (8, '_')) 2017-03-16 16:31:57,208 INFO sqlalchemy.engine.base.Engine INSERT INTO whatever (id, foo, bar) VALUES (%s, %s, %s) 2017-03-16 16:31:57,208 INFO sqlalchemy.engine.base.Engine ((1, '_', '_'), (3, '_', '_'), (5, '_', '_'), (7, '_', '_'), (9, '_', '_')) 2017-03-16 16:31:57,210 INFO sqlalchemy.engine.base.Engine COMMIT ``` For me it looks like it's just a simple problem where the `insert` interable is `groupby`-ed before it was `sorted`: I fixed the problem in a [commit](https://github.com/fashwell/sqlalchemy/commit/cbc873bac166522510f970f1e24ccfda3c641e58) but unfortunately this breaks 4 tests. I'd love to submit a PR that fixes this issue properly (assuming this behaviour is a bug) but I'd need some help to understand why the tests fail. ### Failing Tests ``` ================================================================================ FAILURES ================================================================================= __________________________________________________________________ AttachedFileShardTest.test_roundtrip ___________________________________________________________________ [gw2] darwin -- Python 2.7.13 /Users/livio/Fashwell/karl-server/src/env/bin/python Traceback (most recent call last): File "/Users/livio/Desktop/github-sqlalchem/sqlalchemy/test/ext/test_horizontal_shard.py", line 170, in test_roundtrip 'Asia', 'Tokyo')]) File "/Users/livio/Desktop/github-sqlalchem/sqlalchemy/test/../lib/sqlalchemy/testing/assertions.py", line 214, in eq_ assert a == b, msg or "%r != %r" % (a, b) AssertionError: [(5, u'Asia', u'Tokyo')] != [(1, 'Asia', 'Tokyo')] _________________________________________________________________ DistinctEngineShardTest.test_roundtrip __________________________________________________________________ [gw2] darwin -- Python 2.7.13 /Users/livio/Fashwell/karl-server/src/env/bin/python Traceback (most recent call last): File "/Users/livio/Desktop/github-sqlalchem/sqlalchemy/test/ext/test_horizontal_shard.py", line 172, in test_roundtrip 'North America', 'New York'), (3, 'North America', 'Toronto')]) File "/Users/livio/Desktop/github-sqlalchem/sqlalchemy/test/../lib/sqlalchemy/testing/assertions.py", line 214, in eq_ assert a == b, msg or "%r != %r" % (a, b) AssertionError: [(4, u'North America', u'New York'), (5, u'North America', u'Toronto')] != [(2, 'North America', 'New York'), (3, 'North America', 'Toronto')] _________________________________________________________________________ DefaultTest.test_insert _________________________________________________________________________ [gw0] darwin -- Python 2.7.13 /Users/livio/Fashwell/karl-server/src/env/bin/python Traceback (most recent call last): File "/Users/livio/Desktop/github-sqlalchem/sqlalchemy/test/orm/test_unitofwork.py", line 937, in test_insert eq_(h1.hoho, althohoval) File "/Users/livio/Desktop/github-sqlalchem/sqlalchemy/test/../lib/sqlalchemy/testing/assertions.py", line 214, in eq_ assert a == b, msg or "%r != %r" % (a, b) AssertionError: u'im hoho' != 'im different hoho' _________________________________________________________________ BatchInsertsTest.test_batch_interaction _________________________________________________________________ [gw0] darwin -- Python 2.7.13 /Users/livio/Fashwell/karl-server/src/env/bin/python Traceback (most recent call last): File "/Users/livio/Desktop/github-sqlalchem/sqlalchemy/test/orm/test_unitofworkv2.py", line 1700, in test_batch_interaction {'data': 't11', 'id': 11} File "/Users/livio/Desktop/github-sqlalchem/sqlalchemy/test/../lib/sqlalchemy/testing/assertions.py", line 489, in assert_sql_execution asserter.assert_(*rules) File "/Users/livio/Desktop/github-sqlalchem/sqlalchemy/test/../lib/sqlalchemy/testing/assertsql.py", line 331, in assert_ assert False, rule.errormessage AssertionError: Testing for compiled statement 'INSERT INTO t (id, data) VALUES (:id, :data)' partial params [{'data': 't3', 'id': 3}, {'data': 't4', 'id': 4}, {'data': 't5', 'id': 5}], received u'INSERT INTO t (id, data) VALUES (:id, lower(:lower_1))' with params [{u'lower_1': 't6', 'id': 6}] =========================================================== 4 failed, 7226 passed, 844 skipped in 84.29 seconds =========================================================== ``` Let my know who I can help and if this is actually a bug! |
|
From: Michael B. <iss...@bi...> - 2017-03-16 13:38:56
|
New issue 3939: Alias as column expr needs tweak to self_group(), but don't know use case yet https://bitbucket.org/zzzeek/sqlalchemy/issues/3939/alias-as-column-expr-needs-tweak-to Michael Bayer: this produces nonsensical SQL, but putting Alias into a func is something they need for the Postgresql functions: ``` #!python from sqlalchemy import * t = table('t', column('x')) expr = func.foobar(select([t]).alias()) stmt = select([expr]) print stmt ``` self_group() fails because it does not provide for "against" since it never expected to be called in a column context: ``` #!diff diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index b69d667..9db1e08 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -1241,13 +1241,13 @@ class Alias(FromClause): or 'anon')) self.name = name - def self_group(self, target=None): - if isinstance(target, CompoundSelect) and \ + def self_group(self, against=None): + if isinstance(against, CompoundSelect) and \ isinstance(self.original, Select) and \ self.original._needs_parens_for_grouping(): return FromGrouping(self) - return super(Alias, self).self_group(target) + return super(Alias, self).self_group(against=against) @property def description(self): @@ -2269,7 +2269,7 @@ class CompoundSelect(GenerativeSelect): n + 1, len(s.c._all_columns)) ) - self.selects.append(s.self_group(self)) + self.selects.append(s.self_group(against=self)) GenerativeSelect.__init__(self, **kwargs) ``` still, the SQL from above is: ``` #!sql SELECT foobar(SELECT t.x FROM t) AS foobar_1 FROM (SELECT t.x AS x FROM t) AS anon_1 ``` so...I don't know yet what they are trying to make this do that makes any sense. |
|
From: Sean M. <iss...@bi...> - 2017-03-14 10:04:25
|
New issue 3938: TypeError: not enough arguments for format string https://bitbucket.org/zzzeek/sqlalchemy/issues/3938/typeerror-not-enough-arguments-for-format Sean Mars: I get the "TypeError: not enough arguments for format string" when i execute by compiled object with mysql.dialect(). ```python ... table.insert().values(id=id).compile(bind=conn, dialect=mysql.dialect()) conn.execute(ins) ... ``` Responsible: zzzeek |
|
From: Michael B. <iss...@bi...> - 2017-03-13 16:14:00
|
New issue 3937: cx_oracle 5.3 just broke the world https://bitbucket.org/zzzeek/sqlalchemy/issues/3937/cx_oracle-53-just-broke-the-world Michael Bayer: released three days ago, appears to hardcode WITH_UNICODE turned on and all our character tests fail. |
|
From: eqato1991 <iss...@bi...> - 2017-03-13 15:43:08
|
New issue 3936: No stubs for event.py in sqlalchemy! https://bitbucket.org/zzzeek/sqlalchemy/issues/3936/no-stubs-for-eventpy-in-sqlalchemy eqato1991: There is no stub for event.py in sqlalchemy, meaning no .pyi file, therefore mypy can not find it and throws an error. The workaround for the moment would be import it separately from the import list of sqlalchemy and specify type: ignore. This needs to be done in every file that event actually is needed to be imported. |
|
From: Eoghan M. <iss...@bi...> - 2017-03-11 17:00:05
|
New issue 3935: custom type obliterated by from_statement https://bitbucket.org/zzzeek/sqlalchemy/issues/3935/custom-type-obliterated-by-from_statement Eoghan Murray: Somehow the process_result_value is getting lost when from_statement is in use, see attached test case |
|
From: Michael B. <iss...@bi...> - 2017-03-11 15:45:40
|
New issue 3934: call after_rollback() before accounting state is reset? https://bitbucket.org/zzzeek/sqlalchemy/issues/3934/call-after_rollback-before-accounting Michael Bayer: The after_commit() handler is called before the snapshot is removed. It's not clear why the after_rollback() handler is called *after* the snapshot is removed. It is useful for applications to have access to the state of the objects in this event. There's also no test coverage asserting that snapshot is present for after_commit(). |
|
From: Eoghan M. <iss...@bi...> - 2017-03-11 00:45:47
|
New issue 3933: select_entity_from not replacing entity & aliased not adapting on names https://bitbucket.org/zzzeek/sqlalchemy/issues/3933/select_entity_from-not-replacing-entity Eoghan Murray: In the [select_entity_from docs](http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=select_entity_from#sqlalchemy.orm.query.Query.select_entity_from) it reads: "This method is similar to the Query.select_from() method, in that it sets the FROM clause of the query. However, where Query.select_from() only affects what is placed in the FROM, this method also applies the given selectable to replace the FROM which the selected entities would normally select from." As you can hopefully see from the attached test case, 1. aliased fails to recognize that it can adapt an autoloaded view to an existing entity, even when `adapt_on_names` is specified 2. select_entity_from doesn't throw an exception when it can't replace the main entity but instead adds a FROM. This is undesired from what I understand is intended. |
|
From: Michael B. <iss...@bi...> - 2017-03-06 17:32:45
|
New issue 3932: enable sane_multi_rowcount for cx_oracle https://bitbucket.org/zzzeek/sqlalchemy/issues/3932/enable-sane_multi_rowcount-for-cx_oracle Michael Bayer: Per #3926 and testing, it seems cx_oracle supports this now. It's not certain when it did *not* support it, however as the 5.x series is now standard for cx_Oracle it might be safe just to turn it on for any 5.x series detected and assume false for the no-longer-used 4.x series. |
|
From: Bartłomiej B. <iss...@bi...> - 2017-03-06 14:05:04
|
New issue 3931: Memory leak on creating savepoints https://bitbucket.org/zzzeek/sqlalchemy/issues/3931/memory-leak-on-creating-savepoints Bartłomiej Biernacki: I found a small memory leak when using many savepoints to the same DB. Consider this code example: ``` #!python session = create_session() for _ in range(0, 5000): session.begin_nested() session.execute( sa.insert(Test).values(name='test')) session.commit() session.commit() session.close() ``` After this we can observe 5000 strings with savepoints names in memory. Diging deeply into it I found that function `sql/compiler.py:format_savepoint` saves savepoint name to `self._strings`. By default savepoint names are unique for each nested transaction so saving them for future use is redundant and each new savepoint name stays in memory. My proposition is to change the function, so savepoint name won't be saved: ``` #!python def format_savepoint(self, savepoint, name=None): value = name or savepoint.ident if self._requires_quotes(value): return self.quote_identifier(value) return value ``` Complete code example for reproducing bug in attachment. |
|
From: Timothée J. <iss...@bi...> - 2017-03-06 12:23:23
|
New issue 3930: Randomly inserting in the wrong order. https://bitbucket.org/zzzeek/sqlalchemy/issues/3930/randomly-inserting-in-the-wrong-order Timothée Jeannin: When trying to insert sequentially a model (team in the example) then another model with a foreign key pointing to the first model (user in the example) I was expecting sqlalchemy to either insert in the order I called `db.session.add` or resolve the correct insertion order. It looks like it randomly resolve to an incorrect insertion order. It look like the insertion order is computed in the `sort_as_subsets` method from the `topological.py` module. Sometimes the insertion is in the correct order: ``` #! INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit) INFO:sqlalchemy.engine.base.Engine:INSERT INTO team (team_id) VALUES (%s) INFO:sqlalchemy.engine.base.Engine:(1,) INFO:sqlalchemy.engine.base.Engine:INSERT INTO user (user_id, team_id) VALUES (%s, %s) INFO:sqlalchemy.engine.base.Engine:((1, 1), (2, 1), (3, 1)) INFO:sqlalchemy.engine.base.Engine:COMMIT ``` And sometimes it starts by inserting the users instead of inserting the team: ``` #! INFO:sqlalchemy.engine.base.Engine:COMMIT INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit) INFO:sqlalchemy.engine.base.Engine:INSERT INTO user (user_id, team_id) VALUES (%s, %s) INFO:sqlalchemy.engine.base.Engine:((1, 1), (2, 1), (3, 1)) INFO:sqlalchemy.engine.base.Engine:ROLLBACK Error Traceback (most recent call last): File "/home/tim/Workspace/dota_memories/website/tests/test_sqla.py", line 45, in test_method db.session.commit() File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 157, in do return getattr(self.registry(), name)(*args, **kwargs) File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 874, in commit self.transaction.commit() File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 461, in commit self._prepare_impl() File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 441, in _prepare_impl self.session.flush() File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2139, in flush self._flush(objects) File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2259, in _flush transaction.rollback(_capture_exception=True) File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2223, in _flush flush_context.execute() File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 389, in execute rec.execute(self) File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 548, in execute uow File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj mapper, table, insert) File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 799, in _emit_insert_statements execute(statement, multiparams) File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute return meth(self, multiparams, params) File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement compiled_sql, distilled_params File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context context) File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception exc_info File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _execute_context context) File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 113, in do_executemany rowcount = cursor.executemany(statement, parameters) File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 262, in executemany r = self._query('\n'.join([query[:p], ',\n'.join(q), query[e:]])) File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 354, in _query rowcount = self._do_query(q) File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 318, in _do_query db.query(q) IntegrityError: (_mysql_exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`memories_tests`.`user`, CONSTRAINT `user_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `team` (`team_id`))') [SQL: u'INSERT INTO user (user_id, team_id) VALUES (%s, %s)'] [parameters: ((1, 1), (2, 1), (3, 1))] ``` I created a simple example that runs 100 times the same test to demonstrate that it is randomly failing. ``` #!python import logging from unittest import TestCase from flask import Flask from flask.ext.sqlalchemy import SQLAlchemy logging.basicConfig() logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) db = SQLAlchemy() app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:toor@localhost:3306/memories_tests' db.init_app(app) class User(db.Model): user_id = db.Column(db.BigInteger, primary_key=True, autoincrement=False) team_id = db.Column(db.BigInteger, db.ForeignKey('team.team_id'), nullable=True) class Team(db.Model): team_id = db.Column(db.BigInteger, primary_key=True, autoincrement=False) class TeamTests(TestCase): def setUp(self): self.context = app.test_request_context() self.context.push() db.drop_all() db.create_all() def tearDown(self): db.session.remove() db.drop_all() self.context.pop() def add_test_method(index): def test_method(self): db.session.add(Team(team_id=1)) db.session.add(User(user_id=1, team_id=1)) db.session.add(User(user_id=2, team_id=1)) db.session.add(User(user_id=3, team_id=1)) db.session.commit() test_method.__name__ = 'test_method_' + str(index) setattr(TeamTests, test_method.__name__, test_method) for i in range(100): add_test_method(i) ``` Here is what I use : * mysql 5.7.17 * Ubuntu 16.04 * SQLAlchemy 1.1.6 * Flask 0.11.1 * Flask-SQLAlchemy 2.1 * MySQL-python 1.2.5 |
|
From: ER <iss...@bi...> - 2017-03-04 21:39:28
|
New issue 3929: Ability to print/compile OnConflictDoUpdate statement https://bitbucket.org/zzzeek/sqlalchemy/issues/3929/ability-to-print-compile ER: Dear all, I am using the OnConflictDoUpdate functionality of PostgreSQL, but I have troubles printing SQL statements (for debugging/ understanding). Is there something special to do to print these statements that are specific to PostgreSQL ? Of course, when I execute the query it works very well. ``` #!python insert_stmt = insert(table).from_select(select_stmt.columns, select_stmt) upsert_stmt = insert_stmt.on_conflict_do_update( constraint=table.primary_key, set_={fieldName: insert_stmt.excluded[fieldName]} ) print str(upsert_stmt) # error print str(upsert_stmt.compile(dialect='postgresql')) # error ``` Here is the error : UnsupportedCompilationError: Compiler <sqlalchemy.sql.compiler.StrSQLCompiler object at 0x7f5b22460a90> can't render element of type <class 'sqlalchemy.dialects.postgresql.dml.OnConflictDoUpdate'> Thanks a lot |
|
From: Ricardo M. <iss...@bi...> - 2017-03-03 22:51:27
|
New issue 3928: Hi! I want to develop a DBF dialect for sqlalchemy https://bitbucket.org/zzzeek/sqlalchemy/issues/3928/hi-i-want-to-develop-a-dbf-dialect-for Ricardo Martinez: Hi, i wanna know if its posible that i write a sqla dialect for acces dbf files? Using a dbf package for Python. Thanks in advanced |
|
From: Xiaojun L. <iss...@bi...> - 2017-03-02 06:20:58
|
New issue 3927: Reliability on connection error https://bitbucket.org/zzzeek/sqlalchemy/issues/3927/reliability-on-connection-error Xiaojun Lin: I'm using postgreql with sqlalchemy 1.0.11. Some connection errors occur randomly with low possibility(once 1 or 2 days) while the DB is running normally. The traceback looks like: ``` #!python 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib/python2.7/site-packages/neutron/db/l3_agentschedulers_db.py", line 428, in get_l3_agents_hosting_routers 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource l3_agents = [binding.l3_agent for binding in query] 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2736, in __iter__ 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource return self._execute_and_instances(context) 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2749, in _execute_and_instances 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource close_with_result=True) 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2740, in _connection_from_session 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource **kw) 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/session.py", line 905, in connection 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource execution_options=execution_options) 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/session.py", line 912, in _connection_for_bind 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource conn = engine.contextual_connect(**kw) 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 2039, in contextual_connect 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource self._wrap_pool_connect(self.pool.connect, None), 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 2078, in _wrap_pool_connect 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource e, dialect, self) 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1401, in _handle_dbapi_exception_noconnection 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource util.raise_from_cause(newraise, exc_info) 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource reraise(type(exception), exception, tb=exc_tb) 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 2074, in _wrap_pool_connect 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource return fn() 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/pool.py", line 376, in connect 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource return _ConnectionFairy._checkout(self) 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/pool.py", line 713, in _checkout 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource fairy = _ConnectionRecord.checkout(pool) 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/pool.py", line 485, in checkout 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource rec.checkin() 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__ 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource compat.reraise(exc_type, exc_value, exc_tb) 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/pool.py", line 482, in checkout 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource dbapi_connection = rec.get_connection() 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/pool.py", line 594, in get_connection 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource self.connection = self.__connect() 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/pool.py", line 607, in __connect 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource connection = self.__pool._invoke_creator(self) 2017-02-07 15:49:41.843 147684 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/strategies.py", li 2017-02-07T15:49:41.889+08:00 localhost neutron-server ERROR [pid:147684] [MainThread][tid:92991152] [req-d70f28f9-7fa7-46d3-bc9a-bace54158571] [resource.py:131 resource] (client error): (psycopg2.OperationalError) SSL connection has been closed unexpectedly ``` I've been looking into this problem for a long time but was unable to find a possible cause from the DB side, so I tried to add some retries on sqlalchemy side when connection errors occur. The code may look like this: before: ``` #!python def __connect(self): try: self.starttime = time.time() connection = self.__pool._invoke_creator(self) self.__pool.logger.debug("Created new connection %r", connection) return connection except Exception as e: self.__pool.logger.debug("Error on connect(): %s", e) raise ``` after: ``` #!python def __connect(self, try_times=3): tmp_exc = Exception("try_times is zero") for i in range(try_times): try: self.starttime = time.time() connection = self.__pool._invoke_creator(self) self.__pool.logger.debug("Created new connection %r", connection) return connection except Exception as e: self.__pool.logger.debug("Error on connect(): %s", e) tmp_exc = e raise tmp_exc ``` Here my problem is: 1. Does anyone have any idea about what the "SSL connection has been closed unexpectedly" error might be caused by? 2. Is the "retry" way a proper solution to this? If not, what's the better suggestion? |
|
From: David M. <iss...@bi...> - 2017-03-01 22:15:44
|
New issue 3926: Usage of dialects without sane multi rowcount leads to unexpected behavior on StaleDataErrors https://bitbucket.org/zzzeek/sqlalchemy/issues/3926/usage-of-dialects-without-sane-multi David Matthus: When using a dialect without sane multi rowcount (like cx_oracle), a StaleDateError will not be raised if several rows are updated (probably due to the usage of executemany since sqlalchemy version 1.0). The following test case shows the different behavior of dialects with and without sane multi rowcount (tested with sqlalchemy 1.1.5): ``` #!python import pytest from sqlalchemy import orm, create_engine, Column, Integer, String from sqlalchemy.orm.exc import StaleDataError from sqlalchemy.dialects.sqlite.base import SQLiteDialect from sqlalchemy.ext.declarative import declarative_base @pytest.mark.parametrize('rowcount', [1, 2, 3]) @pytest.mark.parametrize('sane_rowcount', [True, False]) def test_stale_data_error_on_multi_row_update(monkeypatch, rowcount, sane_rowcount): """ This test provokes a StaleDataError by modifying the primary key of some entries while updating another value using the orm. The StaleDataError triggers a rollback, so that the table should not hold any changed entries afterwards. This should always be true, even if the used dialect does not support sane multi rowcount. """ # Sqlite supports multi rowcount but is easier to use for testing purposes. Hence the # flag 'supports_sane_multi_rowcount' is manually set from True to False. monkeypatch.setattr(SQLiteDialect, "supports_sane_multi_rowcount", sane_rowcount) engine = create_engine("sqlite:///") Base = declarative_base() class Car(Base): __tablename__ = 'car' id = Column(Integer, primary_key=True) owner = Column(String) Base.metadata.create_all(engine) session = orm.sessionmaker(bind=engine)() original_car_data = [(car_id, 'Bob') for car_id in range(1, rowcount + 1)] session.add_all([Car(id=car_id, owner=owner) for car_id, owner in original_car_data]) session.commit() cars = session.query(Car).all() session.execute("update car set id = id + 3 where id < 3") for car in cars: car.owner = 'Peter' try: session.commit() except StaleDataError: session.rollback() assert session.query(Car.id, Car.owner).order_by(Car.id).all() == original_car_data ``` Summary of the test result: rowcount | sane_rowcount | result | content of table 'car' after running the test ---------|---------------|--------|------------------------------------------------------- 1 | True | passed | id=1 owner='Bob' 2 | True | passed | id=1 owner='Bob', id=2 owner='Bob' 3 | True | passed | id=1 owner='Bob', id=2 owner='Bob', id=3 owner='Bob' 1 | False | passed | id=1 owner='Bob' 2 | False | failed | id=4 owner='Bob', id=5 owner='Bob' 3 | False | failed | id=3 owner='Peter', id=4 owner='Bob', id=5 owner='Bob' For cx_oracle, it seems like the flag `supports_sane_multi_rowcount` could be set to True. For other affected dialects it may be saver not to use executemany. |
|
From: Paulo R. <iss...@bi...> - 2017-03-01 15:39:31
|
New issue 3925: Outdated link on manual https://bitbucket.org/zzzeek/sqlalchemy/issues/3925/outdated-link-on-manual Paulo Reis: On the documentation page http://docs.sqlalchemy.org/en/latest/dialects/oracle.html?highlight=limit the link to http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html is outdated, the new is: http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html |
|
From: Michael B. <iss...@bi...> - 2017-02-28 18:03:51
|
New issue 3924: schema_translate_map is running on aliases https://bitbucket.org/zzzeek/sqlalchemy/issues/3924/schema_translate_map-is-running-on-aliases Michael Bayer: Not sure how nobody noticed this, guess the feature hasn't seen any real use yet: ``` #!diff diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index e7504a7..a3311f8 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -3406,6 +3406,26 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): schema_translate_map=schema_translate_map ) + def test_schema_translate_aliases(self): + schema_translate_map = {None: 'bar'} + + alias = table1.alias() + + stmt = select([ + table2, alias + ]).select_from(table2.join(alias, table2.c.otherid == alias.c.myid)).\ + where(alias.c.name == 'foo') + + self.assert_compile( + stmt, + "SELECT bar.myothertable.otherid, bar.myothertable.othername, " + "mytable_1.myid, mytable_1.name, mytable_1.description " + "FROM bar.myothertable JOIN bar.mytable AS mytable_1 " + "ON bar.myothertable.otherid = mytable_1.myid " + "WHERE mytable_1.name = :name_1", + schema_translate_map=schema_translate_map + ) + def test_schema_translate_crud(self): schema_translate_map = {"remote_owner": "foob", None: 'bar'} ``` |
|
From: Michael B. <iss...@bi...> - 2017-02-27 21:29:55
|
New issue 3923: pre-execute column defaults don't apply type processing https://bitbucket.org/zzzeek/sqlalchemy/issues/3923/pre-execute-column-defaults-dont-apply Michael Bayer: it seems like pre-execute is much more rarely used but it still applies to primary keys, fails here as SQLite's column processing is missing: ``` #!python from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) date = Column(DateTime, default=text("datetime()"), primary_key=True) e = create_engine("sqlite://", echo=True) Base.metadata.create_all(e) s = Session(e) s.add(A(id=1)) s.commit() ``` patch: ``` #!diff diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py index 18c3276..0210ad7 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -1039,7 +1039,11 @@ class DefaultExecutionContext(interfaces.ExecutionContext): # TODO: expensive branching here should be # pulled into _exec_scalar() conn = self.connection - c = expression.select([default.arg]).compile(bind=conn) + if not default._arg_is_typed: + default_arg = expression.type_coerce(default.arg, type_) + else: + default_arg = default.arg + c = expression.select([default_arg]).compile(bind=conn) return conn._execute_compiled(c, (), {}).scalar() else: return default.arg diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py index 9bb0eee..f5716af 100644 --- a/lib/sqlalchemy/sql/schema.py +++ b/lib/sqlalchemy/sql/schema.py @@ -2045,6 +2045,14 @@ class ColumnDefault(DefaultGenerator): not self.is_clause_element and \ not self.is_sequence + @util.memoized_property + @util.dependencies("sqlalchemy.sql.sqltypes") + def _arg_is_typed(self, sqltypes): + if self.is_clause_element: + return not isinstance(self.arg.type, sqltypes.NullType) + else: + return False + def _maybe_wrap_callable(self, fn): """Wrap callables that don't accept a context. ``` |
|
From: Dima K. <iss...@bi...> - 2017-02-27 20:33:06
|
New issue 3922: Old entity returns after joinedload usage https://bitbucket.org/zzzeek/sqlalchemy/issues/3922/old-entity-returns-after-joinedload-usage Dima Kukushkin: hello! I've got strange behaviour when querying entity already loaded with joined load. Here is some example: ``` #!python class Company(Base): __tablename__ = 'company' id = Column(Integer, primary_key=True, unique=True, nullable=False) name = Column(String(255), nullable=False) class Account(Base): __tablename__ = 'account' id = Column(Integer, primary_key=True, unique=True, nullable=False) company_id = Column(BigInteger, ForeignKey('company.id', ondelete='SET NULL'), nullable=True, index=True) clan = relationship('Company', foreign_keys=clan_id, uselist=False, backref=backref('accounts', uselist=True)) # Try to load account, then change company in concurrent transaction, then query for company # Begin transaction READ COMMITTED account = db.query(Account).options(joinedload('company')).filter_by(company_id=company_id) old_company_name = account.company.name import pdb; pdb.set_trace() # Here we start concurrent transaction in another terminal session to change company name [1] company = db.query(Company).with_for_update(of=Company, key_share=True).filter_by(company_id=company_id).one_or_none() assert company.name != old_company_name # It fails! ``` Concurrent transaction: ``` db=# begin; db=# select name from company where id=123 for no key update of company; # here we will press [c]ontinue in first transaction to check that it will wait 2nd commited db=# update company set name = 'new_name' where id=123; db=# commit; ``` I would like to say that sometimes `db.query(Company).with_for_update(of=Company, key_share=True).get(company_id)` returns None, while row with this id is present in the table all time. SQLAlchemy version is 1.1.5 PostgreSQL 9.5 |
|
From: Krzysztof M. <iss...@bi...> - 2017-02-27 11:41:09
|
New issue 3921: problems creating records when table has compound primary key and server defaults set https://bitbucket.org/zzzeek/sqlalchemy/issues/3921/problems-creating-records-when-table-has Krzysztof Malinowski: ``` #!python # -*- coding: utf-8 -*- from sqlalchemy import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker engine = create_engine('sqlite:///foo.db') Base = declarative_base() Session = sessionmaker(bind=engine) session = Session() class Project(Base): __tablename__ = 'project' id_ = Column(Integer, primary_key=True, unique=True, nullable=False) class User(Base): __tablename__ = 'user' id_ = Column(Integer, primary_key=True, unique=True, nullable=False) class TS(Base): __tablename__ = 'timesheet' __table_args__ = ( PrimaryKeyConstraint('user_id', 'project_id', 'date'), UniqueConstraint('user_id', 'project_id', 'date') ) user_id = Column(Integer, ForeignKey("user.id_"), nullable=False) project_id = Column(Integer, ForeignKey("project.id_"), nullable=False) # date = Column(DateTime, server_default=text('CURRENT_TIMESTAMP'), nullable=False) # mysql ver. date = Column(DateTime, server_default=text("(datetime('now','localtime'))"), nullable=False) # sqlite ver. Base.metadata.create_all(engine) u1 = User(id_=1) p1 = Project(id_=1) ts1 = TS(user_id=u1.id_, project_id=p1.id_) session.add(u1) session.add(p1) session.add(ts1) session.flush() session.commit() # let's try accesing ts1.date print ts1.date ``` and we get the following traceback: ``` Traceback (most recent call last): File "composite_pk.py", line 90, in <module> print ts1.date File "/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 237, in __get__ return self.impl.get(instance_state(instance), dict_) File "/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 578, in get value = state._load_expired(state, passive) File "/lib/python2.7/site-packages/sqlalchemy/orm/state.py", line 474, in _load_expired self.manager.deferred_scalar_loader(self, toload) File "/lib/python2.7/site-packages/sqlalchemy/orm/loading.py", line 669, in load_scalar_attributes raise orm_exc.ObjectDeletedError(state) sqlalchemy.orm.exc.ObjectDeletedError: Instance '<TS at 0x7fd96e705650>' has been deleted, or its row is otherwise not present. ``` But when we look into the db table - the object has been created correctly. When the *date* column is not part of the compound PK this work just fine. The same happens on a MySQL backend. |
|
From: Vadim Y. <iss...@bi...> - 2017-02-26 21:41:21
|
New issue 3920: MySQLDialect.get_table_names() returns empty list https://bitbucket.org/zzzeek/sqlalchemy/issues/3920/mysqldialectget_table_names-returns-empty Vadim Yanko: I used `mysql-connector==2.1.4`, but accroding to the [documentation](https://dev.mysql.com/doc/relnotes/connector-python/en/news-2-0-0.html) connector returns `bytearray` types: > In **Connector/Python 1.x**, the output is: > Using Python 2: [('1',)] > Using Python 3: [(b'1',)] > In **Connector/Python 2.0**, for both Python versions, the output is: `[(bytearray(b'1'),)]` So, I realize that the only way to make fast solution for my project -- creating monkey-patch of MySQLDialect method mentioned above. ### Solution: Added `.decode('utf-8')` to row[0] and row[1] variables. But, I think that you may know other, more elegant way to solve this problem. If not -- write to me, please. I'd like to write PR with fix by myself. Responsible: zzzeek |
186 messages has been excluded from this view by a project administrator.
