sqlalchemy-tickets Mailing List for SQLAlchemy
Brought to you by:
zzzeek
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: Hannele K. <iss...@bi...> - 2018-11-27 01:24:16
|
New issue 4376: Can't determine horizontal shard for many-to-many relationship https://bitbucket.org/zzzeek/sqlalchemy/issues/4376/cant-determine-horizontal-shard-for-many Hannele Kormano: I'm running into trouble updating a many-to-many relationship with horizontal shards. It looks like there are cases where SQLAlchemy doesn't pass an instance nor a query to the shard_chooser function, so I'm unsure what assumptions we should be making. It looks like a reasonable thing to do might be to return a dummy value in those cases as there shouldn't actually be any action -- it does also look like the shard_chooser is called separately for each kind of many-to-many update, but I'd want to make sure there aren't other reasons we should throw an error in this case. Reproducible in at least SQLAlchemy 1.2, with MySQL 5.6 One consistent way to reproduce this issue is to add to an empty collection -- by definition there are no deletes to process. In the tests below, I've set up the shard_mapper to throw an error in those cases where neither an instance nor a query is passed in. from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.horizontal_shard import ShardedSession from sqlalchemy.testing import fixtures from sqlalchemy import testing class UpdateManyToManyShardedTest(fixtures.DeclarativeMappedTest): @classmethod def setup_classes(cls): Base = cls.DeclarativeBasic class Book(Base): __tablename__ = 'book' id = Column(Integer, primary_key=True) authors = relationship( 'Author', secondary='book_author', back_populates='books') class BookAuthor(Base): __tablename__ = 'book_author' authorid = Column(ForeignKey('author.id'), primary_key=True) bookid = Column(ForeignKey('book.id'), primary_key=True) class Author(Base): __tablename__ = 'author' id = Column(Integer, primary_key=True) books = relationship( 'Book', secondary='book_author', back_populates='authors') def test_update_many_to_many_sharded(self): session = ShardedSession( shards={"test": testing.db}, shard_chooser=self.shard_chooser, id_chooser=lambda *args: None, query_chooser=lambda *args: ['test'] ) Book, Author = self.classes("Book", "Author") book = Book() book.authors.append(Author()) session.add(book) session.commit() def test_update_many_to_many_sharded__save_junction_table_directly(self): session = ShardedSession( shards={"test": testing.db}, shard_chooser=self.shard_chooser, id_chooser=lambda *args: None, query_chooser=lambda *args: ['test'] ) Book, Author, BookAuthor = self.classes("Book", "Author", "BookAuthor") book = Book() author = Author() session.add(book) session.add(author) session.commit() book_author = BookAuthor() book_author.bookid = book.id book_author.authorid = author.id session.add(book_author) session.commit() def shard_chooser(self, mapper, instance, clause=None): if not instance and not clause: raise Exception('Cannot determine shard') return 'test' Both tests end up with a similar stack trace: ====================================================================== ERROR: test.orm.test_sharded_m2m.UpdateManyToManyShardedTest.test_update_many_to_many_sharded__save_junction_table_directly ---------------------------------------------------------------------- Traceback (most recent call last): File "/Users/hkormano/Library/Python/2.7/lib/python/site-packages/nose/case.py", line 197, in runTest self.test(*self.arg) File "/Users/hkormano/Projects/sqlalchemy/test/orm/test_sharded_m2m.py", line 60, in test_update_many_to_many_sharded__save_junction_table_directly session.commit() File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 954, in commit self.transaction.commit() File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 467, in commit self._prepare_impl() File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 447, in _prepare_impl self.session.flush() File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2313, in flush self._flush(objects) File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2440, in _flush transaction.rollback(_capture_exception=True) File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/util/langhelpers.py", line 66, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2404, in _flush flush_context.execute() File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 398, in execute rec.execute(self) File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line 512, in execute self.dependency_processor.process_deletes(uow, states) File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/dependency.py", line 1025, in process_deletes secondary_update, secondary_delete) File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/dependency.py", line 1096, in _run_crud connection = uowcommit.transaction.connection(self.mapper) File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/orm/session.py", line 293, in connection bind = self.session.get_bind(bindkey, **kwargs) File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/ext/horizontal_shard.py", line 237, in get_bind mapper, instance, clause=clause) File "/Users/hkormano/Projects/sqlalchemy/lib/sqlalchemy/ext/horizontal_shard.py", line 215, in _choose_shard_and_assign shard_id = self.shard_chooser(mapper, instance, **kw) File "/Users/hkormano/Projects/sqlalchemy/test/orm/test_sharded_m2m.py", line 71, in shard_chooser raise Exception('Cannot determine shard') Exception: Cannot determine shard |
From: Michael B. <iss...@bi...> - 2018-11-26 00:27:31
|
New issue 4375: changes in git are making requirements.txt in docs/build not work https://bitbucket.org/zzzeek/sqlalchemy/issues/4375/changes-in-git-are-making-requirementstxt Michael Bayer: with git version 2.17.2 that "HEAD" indicator is broken: ``` #!python $ .venv/bin/pip install -r requirements.txt Collecting changelog from git+https://bitbucket.org/zzzeek/changelog.git@HEAD#egg=changelog (from -r requirements.txt (line 1)) Cloning https://bitbucket.org/zzzeek/changelog.git (to revision HEAD) to /tmp/pip-install-xopc8e4h/changelog fatal: 'HEAD' is not a valid branch name. Command "git checkout -b HEAD --track origin/HEAD" failed with error code 128 in /tmp/pip-install-xopc8e4h/changelog ``` this has to be updated back to SQLAlchemy 0.9 and additonall needs to go into dogpile, mako, alembic, they all have the same non-working format |
From: Dave S. <iss...@bi...> - 2018-11-26 00:12:41
|
New issue 4374: Feature Request: Issue warning when mapper discovers a member that is a function. https://bitbucket.org/zzzeek/sqlalchemy/issues/4374/feature-request-issue-warning-when-mapper Dave Scotese: My code required a capital C in "Column" but I used a lower-case c, and this resulted in my database table NOT containing the column I defined. Ultimately, this appeared as "sqlalchemy.exc.NoForeignKeysError" because the column that didn't get put into my table is the foreign key. The code that reference the missing column may have created a problem that wasn't reported in the Werkzeug Traceback, or it may have been reported in a way that I didn't see. Here is the end of it: sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Resource.locale - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression. I finally examined the database and discovered there was no column and traced that problem back to the bad letter case. I explored the two identifiers (Column and column): >>> import sqlalchemy >>> from sqlalchemy import create_engine >>> e = create_engine('sqlite:///:memory:', echo=True) >>> from sqlalchemy.ext.declarative import declarative_base >>> Base = declarative_base() >>> from sqlalchemy import Column, Integer, String File "<stdin>", line 1 from sqlalchemy import Column, Integer, String ^ IndentationError: unexpected indent >>> from sqlalchemy import Column, Integer, String >>> from sqlalchemy import column >>> column(Integer, primary_key=True) Traceback (most recent call last): File "<stdin>", line 1, in <module> TypeError: column() got an unexpected keyword argument 'primary_key' >>> column(Integer) <sqlalchemy.sql.elements.ColumnClause at 0x7f8acaeafac8; <class 'sqlalchemy.sql.sqltypes.Integer'>> >>> column <function column at 0x7f8acafccbf8> >>> Column <class 'sqlalchemy.sql.schema.Column'> So it seems that when SQLAlchemy examines a mapped class, it can find a member of the class that is a function ("column") or an instance of a class ("Column"), and it is silent in both cases. It may be useful to declare a member of a mapped class to be a function, but it seems it would be useful for SQLAlchemy to issue a warning when that happens. |
From: Dmytro S. <iss...@bi...> - 2018-11-23 16:07:07
|
New issue 4373: Load.load_only on polymorphic child doesn't work https://bitbucket.org/zzzeek/sqlalchemy/issues/4373/loadload_only-on-polymorphic-child-doesnt Dmytro Starosud: This is using SQLAlchemy==1.2.14. Please consider following code snippet: ``` #!python from sqlalchemy import Column, Integer, String, create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import Load, Query from sqlalchemy.pool import NullPool engine = create_engine('postgresql://***', poolclass=NullPool) connection = engine.connect() connection.begin_nested() Base = declarative_base() class MyEntity(Base): __tablename__ = 'my_entity' id = Column(Integer, primary_key=True) a = Column(String, nullable=False) b = Column(String, nullable=False) kind = Column(String, nullable=False) __mapper_args__ = {'polymorphic_on': kind} Base.metadata.create_all(connection) class MyChild(MyEntity): __mapper_args__ = {'polymorphic_identity': 'child_1'} print(MyEntity) print(Query(MyEntity).options(Load(MyEntity).load_only('a'))) print(MyChild) print(Query(MyChild).options(Load(MyChild).load_only('a'))) ``` It produces following output: ``` <class '__main__.MyEntity'> SELECT my_entity.id AS my_entity_id, my_entity.a AS my_entity_a, my_entity.kind AS my_entity_kind FROM my_entity <class '__main__.MyChild'> SELECT my_entity.id AS my_entity_id, my_entity.a AS my_entity_a, my_entity.b AS my_entity_b, my_entity.kind AS my_entity_kind FROM my_entity WHERE my_entity.kind IN (:kind_1) ``` Please assist. Did I misread documentation? Note unbound `load_only('a')` works well. |
From: Dmytro S. <iss...@bi...> - 2018-11-22 14:36:02
|
New issue 4372: deferred Column attribute in CheckConstraint https://bitbucket.org/zzzeek/sqlalchemy/issues/4372/deferred-column-attribute-in Dmytro Starosud: This is using SQLAlchemy==1.2.14. ``` #!python from sqlalchemy import CheckConstraint, Column, DateTime, Integer, create_engine, func from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import deferred from sqlalchemy.pool import NullPool engine = create_engine('postgresql://***', poolclass=NullPool) connection = engine.connect() connection.begin_nested() Base = declarative_base() class MyEntity(Base): __tablename__ = 'my_entity' id = Column(Integer, primary_key=True) _internal_modified_at = deferred(Column( DateTime(timezone=True), nullable=False, index=True, server_default=func.now(), onupdate=func.now())) __table_args__ = ( CheckConstraint( _internal_modified_at == func.now(), 'my_entity_is_modified_now'), ) Base.metadata.create_all(connection) ``` Last line produces exception: ``` root@0d982765aeb0:/service# python main.py /usr/local/lib/python3.5/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>. """) Traceback (most recent call last): File "main.py", line 29, in <module> Base.metadata.create_all(connection) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/schema.py", line 4005, in create_all tables=tables) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1549, in _run_visitor **kwargs).traverse_single(element) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single return meth(obj, **kw) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 757, in visit_metadata _is_metadata_operation=True) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single return meth(obj, **kw) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 791, in visit_table include_foreign_key_constraints=include_foreign_key_constraints File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 948, in execute return meth(self, multiparams, params) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection return connection._execute_ddl(self, multiparams, params) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1003, in _execute_ddl if not self.schema_for_object.is_default else None) File "<string>", line 1, in <lambda> File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 442, in compile return self._compiler(dialect, bind=bind, **kw) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/ddl.py", line 26, in _compiler return dialect.ddl_compiler(dialect, self, **kw) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 219, in __init__ self.string = self.process(self.statement, **compile_kwargs) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 245, in process return obj._compiler_dispatch(self, **kwargs) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch return meth(self, **kw) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 2502, in visit_create_table create.include_foreign_key_constraints) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 2549, in create_table_constraints for constraint in constraints File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 2547, in <genexpr> p for p in File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 2555, in <genexpr> not getattr(constraint, 'use_alter', False) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 245, in process return obj._compiler_dispatch(self, **kwargs) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 93, in _compiler_dispatch return meth(self, **kw) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 2732, in visit_check_constraint literal_binds=True) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 245, in process return obj._compiler_dispatch(self, **kwargs) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch return meth(self, **kw) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1078, in visit_binary return self._generate_generic_binary(binary, opstring, **kw) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1111, in _generate_generic_binary self, eager_grouping=eager_grouping, **kw) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch return meth(self, **kw) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1242, in visit_bindparam bindparam, within_columns_clause=True, **kwargs) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1275, in render_literal_bindparam return self.render_literal_value(value, bindparam.type) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/dialects/postgresql/base.py", line 1490, in render_literal_value value = super(PGCompiler, self).render_literal_value(value, type_) File "/usr/local/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py", line 1293, in render_literal_value "Don't know how to literal-quote value %r" % value) NotImplementedError: Don't know how to literal-quote value <ColumnProperty at 0x7f12c9ff8dd8; _internal_modified_at> ``` |
From: cowbert <iss...@bi...> - 2018-11-21 02:35:02
|
New issue 4371: SQLAlchemy fails to ignores parameters that are inside of SQL Text comments in SQLAlchemy 1.2+? https://bitbucket.org/zzzeek/sqlalchemy/issues/4371/sqlalchemy-fails-to-ignores-parameters cowbert: Given a SQL textstring of: ``` #!python s = sqlalchemy.sql.text("""With t as ( SELECT 'value1' v from dual union select 'value2' v from dual) --, comment_cte as ("select 1 from dual") select * from t --where t = :param_t """) ``` In earlier versions of SQLAlchemy (1.1) and cx_Oracle (5.x), this can be executed by SQLAlchemy core with: engine.connect().execute(s) In SQLAlchemy 1.2+ and cxOracle 7, this will return an error reference to: http://sqlalche.me/e/cd3x SQLAlchemy properly ignores comments otherwise (for example, deleting the line `--where t = :param_t` will execute properly.) Changing `--` style comments to the `/* */` style results in the same error. Env: Python 3.6.4, SQLAlchemy 1.2.14, cx-Oracle 7.0.0 Full stack trace: ``` #!python Traceback (most recent call last): File "/usr/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1127, in _execute_context context = constructor(dialect, self, conn, *args) File "/usr/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 631, in _init_compiled self.compiled_parameters = [compiled.construct_params()] File "/usr/lib/python3.6/site-packages/sqlalchemy/sql/compiler.py", line 566, in construct_params % bindparam.key, code="cd3x") sqlalchemy.exc.InvalidRequestError: A value is required for bind parameter 'param_t' (Background on this error at: http://sqlalche.me/e/cd3x) ``` |
From: Andreas R. <iss...@bi...> - 2018-11-15 18:18:00
|
New issue 4370: sqlite 3.25.x docs/tutorial.rst doctests fail https://bitbucket.org/zzzeek/sqlalchemy/issues/4370/sqlite-325x-docs-tutorialrst-doctests-fail Andreas Rammhold: While bumping SQLAlchemy (& sqlite) in the NixOS Package repository (https://github.com/NixOS/nixpkgs/pull/46852) we have been seeing one particular test (`DocTest.test_core`) failing whenever we test with sqlite >= 3.25. Succeeding the tests is a mandatory step for the package to be bumped. SQLAlchemy version: 1.2.13 SQLite version: 2.35.3 ### Steps to reproduce After checking out https://github.com/NixOS/nixpkgs/pull/46852 and building it using `nix-build -I nixpkgs=. -A python3Package.sqlalchemy` the following error will appear after some time: ``` =================================== FAILURES =================================== ______________________________ DocTest.test_core _______________________________ Traceback (most recent call last): File "/build/SQLAlchemy-1.2.13/test/base/test_tutorials.py", line 86, in test_core self._run_doctest("core/tutorial.rst") File "/build/SQLAlchemy-1.2.13/test/base/test_tutorials.py", line 80, in _run_doctest self._run_doctest_for_content(fname, content) File "/build/SQLAlchemy-1.2.13/test/base/test_tutorials.py", line 69, in _run_doctest_for_content assert not runner.failures AssertionError: assert not 2 + where 2 = <doctest.DocTestRunner object at 0x7fffeb84db70>.failures ----------------------------- Captured stdout call ----------------------------- 2018-11-15 00:58:09,857 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1 2018-11-15 00:58:09,857 INFO sqlalchemy.engine.base.Engine () 2018-11-15 00:58:09,858 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1 2018-11-15 00:58:09,858 INFO sqlalchemy.engine.base.Engine () 2018-11-15 00:58:09,859 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users") 2018-11-15 00:58:09,859 INFO sqlalchemy.engine.base.Engine () 2018-11-15 00:58:09,860 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("addresses") 2018-11-15 00:58:09,860 INFO sqlalchemy.engine.base.Engine () 2018-11-15 00:58:09,861 INFO sqlalchemy.engine.base.Engine CREATE TABLE users ( id INTEGER NOT NULL, name VARCHAR, fullname VARCHAR, PRIMARY KEY (id) ) 2018-11-15 00:58:09,862 INFO sqlalchemy.engine.base.Engine () 2018-11-15 00:58:09,862 INFO sqlalchemy.engine.base.Engine COMMIT 2018-11-15 00:58:09,863 INFO sqlalchemy.engine.base.Engine CREATE TABLE addresses ( id INTEGER NOT NULL, user_id INTEGER, email_address VARCHAR NOT NULL, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES users (id) ) 2018-11-15 00:58:09,863 INFO sqlalchemy.engine.base.Engine () 2018-11-15 00:58:09,864 INFO sqlalchemy.engine.base.Engine COMMIT 2018-11-15 00:58:09,867 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname) VALUES (?, ?) 2018-11-15 00:58:09,868 INFO sqlalchemy.engine.base.Engine ('jack', 'Jack Jones') 2018-11-15 00:58:09,868 INFO sqlalchemy.engine.base.Engine COMMIT 2018-11-15 00:58:09,869 INFO sqlalchemy.engine.base.Engine INSERT INTO users (id, name, fullname) VALUES (?, ?, ?) 2018-11-15 00:58:09,870 INFO sqlalchemy.engine.base.Engine (2, 'wendy', 'Wendy Williams') 2018-11-15 00:58:09,870 INFO sqlalchemy.engine.base.Engine COMMIT 2018-11-15 00:58:09,880 INFO sqlalchemy.engine.base.Engine INSERT INTO addresses (user_id, email_address) VALUES (?, ?) 2018-11-15 00:58:09,881 INFO sqlalchemy.engine.base.Engine ((1, 'ja...@ya...'), (1, 'ja...@ms...'), (2, 'ww...@ww...'), (2, 'we...@ao...')) 2018-11-15 00:58:09,881 INFO sqlalchemy.engine.base.Engine COMMIT 2018-11-15 00:58:09,882 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname FROM users 2018-11-15 00:58:09,882 INFO sqlalchemy.engine.base.Engine () 2018-11-15 00:58:09,897 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname FROM users 2018-11-15 00:58:09,897 INFO sqlalchemy.engine.base.Engine () 2018-11-15 00:58:09,899 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname FROM users 2018-11-15 00:58:09,899 INFO sqlalchemy.engine.base.Engine () 2018-11-15 00:58:09,900 INFO sqlalchemy.engine.base.Engine SELECT users.name, users.fullname FROM users 2018-11-15 00:58:09,900 INFO sqlalchemy.engine.base.Engine () 2018-11-15 00:58:09,902 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address FROM users, addresses 2018-11-15 00:58:09,902 INFO sqlalchemy.engine.base.Engine () 2018-11-15 00:58:09,904 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, addresses.id, addresses.user_id, addresses.email_address FROM users, addresses WHERE users.id = addresses.user_id 2018-11-15 00:58:09,904 INFO sqlalchemy.engine.base.Engine () 2018-11-15 00:58:09,916 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ? || addresses.email_address AS title FROM users, addresses WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?) 2018-11-15 00:58:09,917 INFO sqlalchemy.engine.base.Engine (', ', 'm', 'z', '%@aol.com', '%@msn.com') 2018-11-15 00:58:09,920 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ? || addresses.email_address AS title FROM users, addresses WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?) 2018-11-15 00:58:09,920 INFO sqlalchemy.engine.base.Engine (', ', 'm', 'z', '%@aol.com', '%@msn.com') 2018-11-15 00:58:09,922 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ', ' || addresses.email_address AS title FROM users, addresses WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?) 2018-11-15 00:58:09,922 INFO sqlalchemy.engine.base.Engine ('m', 'z', '%@aol.com', '%@msn.com') 2018-11-15 00:58:09,924 INFO sqlalchemy.engine.base.Engine SELECT users.id, addresses.id, users.id, users.name, addresses.email_address AS email FROM users JOIN addresses ON users.id=addresses.user_id WHERE users.id = 1 2018-11-15 00:58:09,924 INFO sqlalchemy.engine.base.Engine () 2018-11-15 00:58:09,929 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ', ' || addresses.email_address AS title FROM users, addresses WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z' AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?) 2018-11-15 00:58:09,929 INFO sqlalchemy.engine.base.Engine ('%@aol.com', '%@msn.com') 2018-11-15 00:58:09,932 INFO sqlalchemy.engine.base.Engine SELECT users.fullname || ? || addresses.email_address AS anon_1 FROM users, addresses WHERE users.id = addresses.user_id AND users.name BETWEEN 'm' AND 'z' AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?) 2018-11-15 00:58:09,933 INFO sqlalchemy.engine.base.Engine (', ', '%@aol.com', '%@msn.com') 2018-11-15 00:58:09,935 INFO sqlalchemy.engine.base.Engine SELECT addresses.user_id, count(addresses.id) AS num_addresses FROM addresses ORDER BY num_addresses 2018-11-15 00:58:09,936 INFO sqlalchemy.engine.base.Engine () ********************************************************************** File "core/tutorial.rst", line 1037, in core/tutorial.rst Failed example: conn.execute(stmt).fetchall() Expected: SELECT addresses.user_id, count(addresses.id) AS num_addresses FROM addresses ORDER BY num_addresses () [(2, 4)] Got: SELECT addresses.user_id, count(addresses.id) AS num_addresses FROM addresses ORDER BY num_addresses () [(1, 4)] 2018-11-15 00:58:09,939 INFO sqlalchemy.engine.base.Engine SELECT addresses.user_id, count(addresses.id) AS num_addresses FROM addresses ORDER BY num_addresses DESC 2018-11-15 00:58:09,939 INFO sqlalchemy.engine.base.Engine () ********************************************************************** File "core/tutorial.rst", line 1054, in core/tutorial.rst Failed example: conn.execute(stmt).fetchall() Expected: SELECT addresses.user_id, count(addresses.id) AS num_addresses FROM addresses ORDER BY num_addresses DESC () [(2, 4)] Got: SELECT addresses.user_id, count(addresses.id) AS num_addresses FROM addresses ORDER BY num_addresses DESC () [(1, 4)] ``` The full log can be retrieved here: https://gist.githubusercontent.com/andir/b4b7f1ef355899c7972bb5ead67afb0f/raw Looking at the documentation for this doctest one of the queries that is executed origins from the following lines: ``` >>> from sqlalchemy import func >>> stmt = select([ ... addresses.c.user_id, ... func.count(addresses.c.id).label('num_addresses')]).\ ... order_by("num_addresses") ``` The difference in output is in the first element of the first tuple. It should represent `addresses.user_id`. Given that there is no ordering, grouping further restriction of the value of that field I'd guess that any value could be presented here. It could probably be explained as "undefined behavior"? In that case the assumption of the test is wrong and should allow for any value from the set of available values (1 or 2). We believe this could be a bug within the testsuite. |
From: Michael B. <iss...@bi...> - 2018-11-15 14:14:49
|
New issue 4369: remove depreacted cx_Oracle connect params, let all keywords through https://bitbucket.org/zzzeek/sqlalchemy/issues/4369/remove-depreacted-cx_oracle-connect-params Michael Bayer: cx_Oracle is moving very fast these days and it now allows a lot of new arguments: https://cx-oracle.readthedocs.io/en/latest/module.html#cx_Oracle.Connection we need to at least allow connect_args to travel through generically and we should also string-coerece the ones that are listed in the above document. Additionally, we should not be setting threaded=True. SQLAlchemy connections are not threadsafe in any case. Allow it as an option, make sure we document that this changed. Remove all the unused and warned as deprecated params auto_setinputsizes, allow_twophase, "threaded" as a dialect flag (what's that about anyway?), use_ansi really should not be part of connect_args, it's a dialect flag, as well as auto_convert_lobs. as a bonus, deprecate that these are part of connect_args, but this is not critical. |
From: Carlo S. <iss...@bi...> - 2018-11-14 16:41:21
|
New issue 4368: Connection reset stale while recycling pool connection https://bitbucket.org/zzzeek/sqlalchemy/issues/4368/connection-reset-stale-while-recycling Carlo Suriano: I am experiencing long periods of stale on customer production environment due to recycling pool parameter. The architecture of the solution is the following: 1. Different concurrent tasks are launched on a job scheduler which has 4 workers. Each worker creates a session pool with SQLAlchemy `create_engine(<connection string>, pool_size=8, max_overflow=0, pool_recycle=3600, pool_timeout=30)` 2. Each task executes a complex workflow, which consists of SELECT queries in cascade; 3. The underlying DB is an Oracle RAC with failover and three available addresses for each of the two failover nodes; Randomically, during the execution of a task, the pool recycler verify the overcoming of the 3600 timeout and triggers the recycling of the connection. This causes a stale that lasts about 15 minutes (sistematically) after which SQLAlchemy succeeds to close and re-open connection and then continues the execution. This is an excerpt of the library logger: ``` [myPID] YYYY-MM-DD 12:07:59,974 pool.py: 513 INFO Connection <cx_Oracle.Connection to <my_connection_string>> exceeded timeout; recycling [myPID] YYYY-MM-DD 12:07:59,974 pool.py: 248 DEBUG Closing connection <cx_Oracle.Connection to <my_connection_string>> [myPID] YYYY-MM-DD 12:23:28,294 pool.py: 255 ERROR Exception closing connection <cx_Oracle.Connection to <my_connection_string>> Traceback (most recent call last): File "myEnv/lib/python2.7/site-packages/sqlalchemy/pool.py", line 250, in _close_connection self._dialect.do_close(connection) File "myEnv/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 412, in do_close dbapi_connection.close() DatabaseError: ORA-25408: can not safely replay call [myPID] YYYY-MM-DD 12:23:28,401 pool.py: 538 DEBUG Created new connection <cx_Oracle.Connection to <my_connection_string>> [myPID] YYYY-MM-DD 12:23:28,405 base.py: 487 INFO BEGIN (implicit) [myPID] YYYY-MM-DD 12:23:28,406 base.py: 912 INFO SELECT XXX FROM YYY WHERE ZZZ ``` Since the connection string is constant for each object of the pool, it is not possible to distinguish between the connection being handled on the logs. A quick fix is to set the recycle param at -1, but could the stale be due to wrong management of the failover on SQLAlchemy? |
From: Michael B. <iss...@bi...> - 2018-11-14 15:35:41
|
New issue 4367: aliased join leaks into any() / has(), only when using declarative https://bitbucket.org/zzzeek/sqlalchemy/issues/4367/aliased-join-leaks-into-any-has-only-when Michael Bayer: the tests we've had for years in https://github.com/zzzeek/sqlalchemy/blob/master/test/orm/test_query.py#L2382 fails on declarative only; the columns contain the "parententity" annotation when using declarative, only "parentmapper" when not. I have no idea what this is about. test: ``` #!python from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.declarative import declared_attr Base = declarative_base() class C(Base): __tablename__ = 'c' id = Column(Integer, primary_key=True) class B(Base): __tablename__ = 'b' id = Column(Integer, primary_key=True) c_id = Column(ForeignKey(C.id)) c = relationship("C") s = Session() print( s.query(B).join(B.c, aliased=True).filter(B.c.has(C.id == 1)) ) ``` classical: ``` #!python from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.declarative import declared_attr m = MetaData() c = Table( 'c', m, Column('id', Integer, primary_key=True) ) b = Table( 'b', m, Column('id', Integer, primary_key=True), Column('c_id', ForeignKey('c.id')) ) class C(object): pass class B(object): pass mapper(B, b, properties={ 'c': relationship(C) }) mapper(C, c) s = Session() print( s.query(B).join(B.c, aliased=True).filter(B.c.has(C.id == 1)) ) ``` outputs: ``` #!python [classic@photon2 sqlalchemy]$ python test2.py SELECT b.id AS b_id, b.c_id AS b_c_id FROM b JOIN c AS c_1 ON c_1.id = b.c_id WHERE EXISTS (SELECT 1 FROM c WHERE c_1.id = b.c_id AND c.id = :id_1) [classic@photon2 sqlalchemy]$ python test3.py SELECT b.id AS b_id, b.c_id AS b_c_id FROM b JOIN c AS c_1 ON c_1.id = b.c_id WHERE EXISTS (SELECT 1 FROM c WHERE c.id = b.c_id AND c.id = :id_1) ``` |
From: Fred L. <iss...@bi...> - 2018-11-13 17:16:13
|
New issue 4366: Use of .has/.any with composite secondary join https://bitbucket.org/zzzeek/sqlalchemy/issues/4366/use-of-has-any-with-composite-secondary Fred Ludlow: Hi, Many times in the past I've started writing a sqlalchemy bug report and realised it was me! - this time I think it might just be a real bug... I've attached a minimal example which hopefully explains it. python 2.7.15, sqla 1.2.13 I'm trying to create a relationship between a child (A) and it's great-grandparent (D), using a secondary join of the intermediate tables (B and C). This loads data correctly, but if I try and filter using .has (.any) on the relationship, it constructs an exists clause but doesn't apply the join condition within the secondary object (i.e. the B-C join). As a result I think it ends up effectively doing "A inner join B full outer join C inner join D". ```python from sqlalchemy import and_, create_engine, Integer, Column, ForeignKey from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy.ext.declarative import declarative_base engine = create_engine("sqlite:///", echo=True) Base = declarative_base() Base.metadata.bind = engine Session = sessionmaker() session = Session() class D(Base): __tablename__ = 'd' id = Column(Integer, primary_key=True) class C(Base): __tablename__ = 'c' id = Column(Integer, primary_key=True) d_id = Column(ForeignKey(D.id)) class B(Base): __tablename__ = 'b' id = Column(Integer, primary_key=True) c_id = Column(ForeignKey(C.id)) class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) b_id = Column(ForeignKey(B.id)) d = relationship( 'D', secondary="join(B, C)", primaryjoin="A.b_id == B.id", secondaryjoin="C.d_id == D.id", uselist=False) d_fixed = relationship( 'D', secondary="join(B, C)", primaryjoin="A.b_id == B.id", secondaryjoin="and_(B.c_id == C.id, C.d_id == D.id)", uselist=False) Base.metadata.create_all() ``` This prints: Explicit join, correct behaviour: ```python print session.query(A).join(A.d).filter(D.id==1) ``` ```sql SELECT a.id AS a_id, a.b_id AS a_b_id FROM a JOIN (b AS b_1 JOIN c AS c_1 ON c_1.id = b_1.c_id) ON a.b_id = b_1.id JOIN d ON c_1.d_id = d.id WHERE d.id = ? ``` Using has, missing b-c join condition ```python print session.query(A).filter(A.d.has(D.id==1)) ``` ```sql SELECT a.id AS a_id, a.b_id AS a_b_id FROM a WHERE EXISTS (SELECT 1 FROM b, c, d WHERE a.b_id = b.id AND c.d_id = d.id AND d.id = ?) ``` Putting b-c join into secondaryjoin, fixes has/any query: ```python print session.query(A).filter(A.d_fixed.has(D.id==1)) ``` ```sql SELECT a.id AS a_id, a.b_id AS a_b_id FROM a WHERE EXISTS (SELECT 1 FROM b, c, d WHERE a.b_id = b.id AND b.c_id = c.id AND c.d_id = d.id AND d.id = ?) ``` But the b-c join condition now appears twice when using query(A).join(A.d) ```python ``` print session.query(A).join(A.d_fixed).filter(D.id==1) ```sql SELECT a.id AS a_id, a.b_id AS a_b_id FROM a JOIN (b AS b_1 JOIN c AS c_1 ON c_1.id = b_1.c_id) ON a.b_id = b_1.id JOIN d ON b_1.c_id = c_1.id AND c_1.d_id = d.id WHERE d.id = ? ``` I'm guessing this is related to #4349 and #4363 |
From: Michael B. <iss...@bi...> - 2018-11-12 21:14:42
|
New issue 4365: dont assume _from_obj[0] when joining without a clear "left" side https://bitbucket.org/zzzeek/sqlalchemy/issues/4365/dont-assume-_from_obj-0-when-joining Michael Bayer: this would allow us to fix the hack in #4363 ``` #!python def test_join_entity_from_multiple_from_clause(self): """test adding joins onto multiple FROM clauses""" User, Address = (self.classes.User, self.classes.Address) sess = create_session() q = sess.query(Address, User).join(Address.dingaling).\ join(User.orders) a1 = aliased(Address) assert_raises_message( sa.exc.InvalidRequestError, "Can't determine which FROM clause to join from, there are " "multiple FROMS which can join to this entity", q.join, a1 ) def test_join_entity_w_onclause_from_multiple_from_clause(self): """test adding joins onto multiple FROM clauses""" User, Order, Address = (self.classes.User, self.classes.Order, self.classes.Address) sess = create_session() q = sess.query(Address, User).join(Address.dingaling).\ join(User.orders) a1 = aliased(Address) q = q.join(a1, Order.address_id == a1.id) self.assert_compile( q, "< query works >" ) ``` |
From: Michael R. <iss...@bi...> - 2018-11-12 13:52:44
|
New issue 4364: Code completion in IDEs with Session objects doesn't work https://bitbucket.org/zzzeek/sqlalchemy/issues/4364/code-completion-in-ides-with-session Michael Rans: If I create a Session object, I do not get code completion for it in my IDE (PyCharm) even if I add a type hint. ``` #!python from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base, declared_attr from sqlalchemy.orm import sessionmaker from sqlalchemy.pool import NullPool class HDXBase(object): @declared_attr def __tablename__(cls): return '%ss' % cls.__name__.lower() engine = create_engine('sqlite:///test.db', poolclass=NullPool, echo=False) Session = sessionmaker(bind=engine) Base = declarative_base(cls=HDXBase) Base.metadata.create_all(engine) session = Session() ``` If after entering the above I type "session." in PyCharm, there are no options given from the Session class. ``` #!python def get_session(db_url): # type: (str) -> sqlalchemy.orm.session.Session engine = create_engine(db_url, poolclass=NullPool, echo=False) Session = sessionmaker(bind=engine) Base = declarative_base(cls=HDXBase) Base.metadata.create_all(engine) return Session() session = get_session('sqlite:///test.db') ``` If after entering the above I type "session." in PyCharm, there are no options given from the Session class. Also I notice an error "Cannot find reference 'orm' in '__init__.py'" |
From: 大易归真 <iss...@bi...> - 2018-11-12 10:14:01
|
New issue 4363: select from clause change when I update version from 1.2.12 to 1.2.13 https://bitbucket.org/zzzeek/sqlalchemy/issues/4363/select-from-clause-change-when-i-update 大易归真: My query code is: ``` funcs = aliased(Function) mapping = db.session.query(FunctionMapping).get(1) foreign_funcs = mapping.peers.join(ForeignFunction.functions).join(funcs) ``` When use version 1.2.12: The SQL is ``` SELECT ... FROM foreign_function_set, foreign_function INNER JOIN foreign_function_map AS foreign_function_map_1 ON foreign_function.id = foreign_function_map_1.foreign_function_id INNER JOIN function ON function.id = foreign_function_map_1.function_id INNER JOIN function AS function_2 ON function.id = function_2.parent_id AND function_2.id = function.parent_id LEFT OUTER JOIN (foreign_function_map AS foreign_function_map_2 INNER JOIN function AS function_1 ON function_1.id = foreign_function_map_2.function_id) ON foreign_function.id = foreign_function_map_2.foreign_function_id ``` When I update sqlalchemy version to 1.2.13, The SQL is: ``` SELECT ... FROM foreign_function_set INNER JOIN `function` AS function_2 ON function_2.id = `function`.id OR function_2.function_id = `function`.id OR function_2.function_group_id = `function`.id, foreign_function INNER JOIN foreign_function_map AS foreign_function_map_1 ON foreign_function.id = foreign_function_map_1.foreign_function_id INNER JOIN `function` ON `function`.id = foreign_function_map_1.function_id LEFT OUTER JOIN (foreign_function_map AS foreign_function_map_2 INNER JOIN `function` AS function_1 ON function_1.id = foreign_function_map_2.function_id) ON foreign_function.id = foreign_function_map_2.foreign_function_id ``` The program raise error `u"Unknown column 'function.id' in 'on clause'"` I find 1.2.13 add `_from_obj ` to query, but don't understand how this attribute change SQL? This is class and secondary defina ``` #!python class Function(db.Model): id = db.Column(db.SmallInteger, autoincrement=True, primary_key=True) function_id = db.Column(db.SmallInteger) function_group_id = db.Column(db.SmallInteger) parent_id = db.Column(db.SmallInteger, db.ForeignKey('function.id')) children = db.relationship( 'Function', lazy='dynamic', backref=db.backref( 'parent', remote_side=[id])) foreign_function_map = db.Table('foreign_function_map', db.Column( 'foreign_function_id', db.SmallInteger, db.ForeignKey( 'foreign_function.id', ondelete='CASCADE'), primary_key=True ), db.Column( 'function_id', db.SmallInteger, db.ForeignKey('function.id'), primary_key=True )) class ForeignFunction(db.Model): id = db.Column(db.SmallInteger, autoincrement=True, primary_key=True) functions = db.relationship( 'Function', secondary=foreign_function_map, lazy='joined') foreign_function_set = db.Table('foreign_function_set', db.Column( 'function_mapping_id', db.SmallInteger, db.ForeignKey( 'function_mapping.id'), primary_key=True), db.Column( 'foreign_function_id', db.SmallInteger, db.ForeignKey( 'foreign_function.id'), primary_key=True)) class FunctionMapping(db.Model): id = db.Column(db.SmallInteger, autoincrement=True, primary_key=True) key = db.Column(db.String(20), index=True, unique=True) peers = db.relationship( 'ForeignFunction', secondary=foreign_function_set, lazy='dynamic', backref=db.backref('site')) ``` |
From: Michael B. <iss...@bi...> - 2018-11-11 02:17:47
|
New issue 4362: add new API for IDENTITY start/increment in mssql https://bitbucket.org/zzzeek/sqlalchemy/issues/4362/add-new-api-for-identity-start-increment Michael Bayer: in #4235 we need Sequence to be a real object for SQL Server so we need to deprecate its current use for IDENTITIY, propose new column params mssql_identity_start, mssql_identity_increment. need deprecation warnings for Sequence |
From: Michael B. <iss...@bi...> - 2018-11-08 15:40:06
|
New issue 4361: mysql 8.0 fix likely breaks on case insensitive https://bitbucket.org/zzzeek/sqlalchemy/issues/4361/mysql-80-fix-likely-breaks-on-case Michael Bayer: e.g. #4344, we have this: ``` #!python correct_for_wrong_fk_case = connection.execute( sql.text(""" select table_schema, table_name, column_name from information_schema.columns where (table_schema, table_name, lower(column_name)) in :table_data; """).bindparams( sql.bindparam("table_data", expanding=True) ), table_data=col_tuples ) ``` if table names/schema are stored in lower case that will fail. user reporting this stack trace: ``` #! INFO [alembic.runtime.migration] Context impl MySQLImpl. INFO [alembic.runtime.migration] Will assume non-transactional DDL. Traceback (most recent call last): File "---/venv/bin/alembic", line 11, in <module> load_entry_point('alembic==1.0.2', 'console_scripts', 'alembic')() File "---/venv/lib/python3.7/site-packages/alembic/config.py", line 502, in main CommandLine(prog=prog).main(argv=argv) File "---/venv/lib/python3.7/site-packages/alembic/config.py", line 496, in main self.run_cmd(cfg, options) File "---/venv/lib/python3.7/site-packages/alembic/config.py", line 479, in run_cmd **dict((k, getattr(options, k, None)) for k in kwarg) File "---/venv/lib/python3.7/site-packages/alembic/command.py", line 176, in revision script_directory.run_env() File "---/venv/lib/python3.7/site-packages/alembic/script/base.py", line 427, in run_env util.load_python_file(self.dir, 'env.py') File "---/venv/lib/python3.7/site-packages/alembic/util/pyfiles.py", line 81, in load_python_file module = load_module_py(module_id, path) File "---/venv/lib/python3.7/site-packages/alembic/util/compat.py", line 82, in load_module_py spec.loader.exec_module(module) File "<frozen importlib._bootstrap_external>", line 728, in exec_module File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed File "alembic/env.py", line 75, in <module> run_migrations_online() File "alembic/env.py", line 70, in run_migrations_online context.run_migrations() File "<string>", line 8, in run_migrations File "---/venv/lib/python3.7/site-packages/alembic/runtime/environment.py", line 836, in run_migrations self.get_context().run_migrations(**kw) File "---/venv/lib/python3.7/site-packages/alembic/runtime/migration.py", line 321, in run_migrations for step in self._migrations_fn(heads, self): File "---/venv/lib/python3.7/site-packages/alembic/command.py", line 156, in retrieve_migrations revision_context.run_autogenerate(rev, context) File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/api.py", line 415, in run_autogenerate self._run_environment(rev, migration_context, True) File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/api.py", line 451, in _run_environment autogen_context, migration_script) File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 22, in _populate_migration_script _produce_net_changes(autogen_context, upgrade_ops) File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 48, in _produce_net_changes autogen_context, upgrade_ops, schemas File "---/venv/lib/python3.7/site-packages/alembic/util/langhelpers.py", line 313, in go fn(*arg, **kw) File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 75, in _autogen_for_tables inspector, upgrade_ops, autogen_context) File "---/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 137, in _compare_tables inspector.reflecttable(t, None) File "---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 633, in reflecttable exclude_columns, _extend_on, reflection_options) File "---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 729, in _reflect_fk table_name, schema, **table.dialect_kwargs) File "---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 447, in get_foreign_keys **kw) File "<string>", line 2, in get_foreign_keys File "---/venv/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 54, in cache ret = fn(self, con, *args, **kw) File "---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 2081, in get_foreign_keys self._correct_for_mysql_bug_88718(fkeys, connection) File "---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 2126, in _correct_for_mysql_bug_88718 for col in fkey['referred_columns'] File "---/venv/lib/python3.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 2126, in <listcomp> for col in fkey['referred_columns'] KeyError: 'id' ``` working on reproducing with an 8.0 with lower_case_table_names set to 1 release immediately upon fix |
From: Michael B. <iss...@bi...> - 2018-11-07 22:49:08
|
New issue 4360: implement SQLite DDL level ON CONFLICT https://bitbucket.org/zzzeek/sqlalchemy/issues/4360/implement-sqlite-ddl-level-on-conflict Michael Bayer: moved from #4010 to represent straight ON CONFLICT, that is, https://www.sqlite.org/lang_conflict.html. https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/700/ |
From: Michael B. <iss...@bi...> - 2018-11-06 15:04:55
|
New issue 4359: very intricate expiration sequence can lead to a detached object buried in a bound parameter https://bitbucket.org/zzzeek/sqlalchemy/issues/4359/very-intricate-expiration-sequence-can Michael Bayer: I've not managed to reduce this further yet, simple attempts are still failing to identify the full issue ``` #!python from sqlalchemy import Column, ForeignKey, Integer, MetaData, create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import backref, relationship, Session Base = declarative_base() class Person(Base): __tablename__ = "persons" id = Column(Integer, primary_key=True) class Car(Base): __tablename__ = "cars" id = Column(Integer, primary_key=True) owner_id = Column(Integer, ForeignKey("persons.id")) owner = relationship("Person") class House(Base): __tablename__ = "houses" id = Column(Integer, primary_key=True) owner_id = Column(Integer, ForeignKey("persons.id")) owner = relationship("Person") class PersonPersonRelationship(Base): __tablename__ = "person_person_relationships" from_person_id = Column(Integer, ForeignKey("persons.id"), primary_key=True) from_person = relationship( "Person", foreign_keys=[from_person_id], backref=backref("relationships"), ) to_person_id = Column(Integer, ForeignKey("persons.id"), primary_key=True) to_person = relationship("Person", foreign_keys=[to_person_id]) engine = create_engine("sqlite://") Base.metadata.create_all(engine) # Set up our data session = Session(bind=engine) person1 = Person(id=1) session.add(person1) person2 = Person(id=2) session.add(person2) PersonPersonRelationship(from_person=person2, to_person=person1) car = Car(owner=person2) session.add(car) session.commit() session.close() print("------------------------------------------------------------") # Start with a fresh session to reproduce the issue new_session = Session(engine) def make_foo_query(sess): owner = sess.query(Person).get(2) return House.owner == owner # TODO: need to figure out how to reduce this mapping # Pull the car, its owner, and the owner's related people into memory car = new_session.query(Car).one() relationships = car.owner.relationships crit = make_foo_query(new_session) for s in sorted( new_session.identity_map.all_states(), key=lambda x: id(x.obj()), reverse=True ): print("object: %s" % s.obj()) new_session.expire(s.obj()) print(crit.left.callable()) ``` ``` #!python ------------------------------------------------------------ object: <__main__.Person object at 0x7fd62fd87d30> object: <__main__.Car object at 0x7fd62fd87898> object: <__main__.PersonPersonRelationship object at 0x7fd62fd214e0> Traceback (most recent call last): File "test.py", line 84, in <module> print(crit.left.callable()) File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/relationships.py", line 1429, in _go value = fn(*arg, **kw) File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 2625, in _get_state_attr_by_column return state.manager[prop.key].impl.get(state, dict_, passive=passive) File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 597, in get value = state._load_expired(state, passive) File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/state.py", line 617, in _load_expired self.manager.deferred_scalar_loader(self, toload) File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/loading.py", line 813, in load_scalar_attributes (state_str(state))) sqlalchemy.orm.exc.DetachedInstanceError: Instance <Person at 0x87b640> is not bound to a Session; attribute refresh operation cannot proceed (Background on this error at: http://sqlalche.me/e/bhk3) ``` for some reason if I just do a simple A/B test and have "B" expired first, the issue does not reproduce. so not sure what's going on yet. |
From: Manuel H. <iss...@bi...> - 2018-11-02 09:16:09
|
New issue 4358: Problem with psycopg2 >=2.5.4 and JSONB field https://bitbucket.org/zzzeek/sqlalchemy/issues/4358/problem-with-psycopg2-254-and-jsonb-field Manuel Holtgrewe: When using psycopg2 >=2.5.4, JSONB fields are directly returned as Python objects and not as strings. This leads to a problem as SQLAlchemy tries to run `json.loads()` on these already decoded JSON files. Also see here: - https://stackoverflow.com/questions/50538699/sqlalchemy-psycopg2-jsonb-columns-are-being-returned-as-strings-instead-of-dict - http://initd.org/psycopg/docs/extras.html#json-adaptation I'm sorry that I do not have time to write a complete example showing the bug but I could work around it by disabling this behaviour in psycopg2 (as shown in the second link). |
From: Michael B. <iss...@bi...> - 2018-11-01 21:30:15
|
New issue 4357: alt-key primary key on bulk update UPDATES the pk as well as having it in the WHERE clause https://bitbucket.org/zzzeek/sqlalchemy/issues/4357/alt-key-primary-key-on-bulk-update-updates Michael Bayer: continuing from #3849: py.test test/orm/test_bulk.py -s --log-debug=sqlalchemy.engine -k test_update_attrs the UPDATE statement is re-SETing the pk attribute needlessly (which can fail on SQL server): UPDATE people_attrs SET person_id=?, name=? WHERE people_attrs.person_id = ? INFO:sqlalchemy.engine.base.Engine:(5, 'newname', 5) the tests need to assert_sql |
From: Thorsten K. <iss...@bi...> - 2018-11-01 16:56:17
|
New issue 4356: Error after upgrading to 1.2.13 https://bitbucket.org/zzzeek/sqlalchemy/issues/4356/error-after-upgrading-to-1213 Thorsten Kampe: "TypeError: __init__() got an unexpected keyword argument 'use_lifo'" after upgrading to 1.2.13. Reverting to 1.2.12 doesn't get rid of the error. Also uninstalling and reinstalling. Removing the sqlalchemy folder and reinstalling removes the issue- A comparison between 1.2.12 and 1.2.13 shows that the pool directory does not exist in 1.2.13. Upgrading and uninstalling via pip does not remove the folder. Attaching the traceback because Bitbucket's Markdown parser produces still an unreadable mess. |
From: Valery T. <iss...@bi...> - 2018-11-01 16:17:47
|
New issue 4355: Avoid unnecessary join on joinedload in custom query https://bitbucket.org/zzzeek/sqlalchemy/issues/4355/avoid-unnecessary-join-on-joinedload-in Valery Tikhonov: F.e. I have 2 entities: `Event` and `Plan`. Event has 1:1 relationship with plan via some foreign key. And I build custom query to get all events with plans for some date and group. Also I add `joinedload` to have all plans populated in relations. ``` query = Event.query \ .join(Plan) \ .filter(Plan.group_id == group_id) \ .filter(Plan.date <= to_date) \ .filter(Plan.color_id == color_id) \ .options(joinedload(Event.plan, innerjoin=True)) ``` This gives me such sql: ``` SELECT <event & plan selector> FROM price_change JOIN plans_monthly ON plans_monthly.month_unique_id = price_change.month_unique_id JOIN plans_monthly AS plans_monthly_1 ON plans_monthly_1.month_unique_id = price_change.month_unique_id WHERE plans_monthly.group_id = ? AND plans_monthly.date <= ? AND plans_monthly.color_id = ? AND plans_monthly.date > ? ``` As you can see - it generates second plans_monthly join (just after the first one), which can be easily avoided, as all data, I need, it in the first one. For now the workaround is building custom query (returning just plan and event rows), then mapping them to events via `from_statement` and then applying each found plan to event via `set_committed_value`. But it would be nice to have it all done automatically. Thanks! |
From: Michael B. <iss...@bi...> - 2018-11-01 15:48:07
|
New issue 4354: __delete__ doesnt work for many to one relationships https://bitbucket.org/zzzeek/sqlalchemy/issues/4354/__delete__-doesnt-work-for-many-to-one Michael Bayer: kind of a big oversight... ``` #!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) data = Column(String) bs = relationship("B", back_populates="a") class B(Base): __tablename__ = 'b' id = Column(Integer, primary_key=True) a_id = Column(ForeignKey("a.id")) data = Column(String) a = relationship("A", back_populates="bs") e = create_engine("sqlite://", echo=True) Base.metadata.create_all(e) s = Session(e) b1 = B() a1 = A(bs=[b1]) s.add_all([a1, b1]) s.commit() b1.a del b1.a s.commit() assert b1.a is None ``` |
From: Alex R. <iss...@bi...> - 2018-11-01 07:03:51
|
New issue 4353: raiseload with sql_only=False is overly aggressive https://bitbucket.org/zzzeek/sqlalchemy/issues/4353/raiseload-with-sql_only-false-is-overly Alex Rothberg: I believe that I am seeing a case where `raiseload` with `sql_only=False` is triggering an exception when it should not be. While setting `sql_only=True` avoids the error for reasons outlined below this is not an ideal workaround. Further, exception is being generated in a case where the look up is entirely not needed (explanation below). The stack trace is: ``` #!python /Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:229: in __set__ instance_dict(instance), value, None) /Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:1079: in set initiator=evt) /Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/collections.py:768: in bulk_replace existing_adapter.fire_remove_event(member, initiator=initiator) /Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/collections.py:698: in fire_remove_event item, initiator) /Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:962: in fire_remove_event fn(state, value, initiator or self._remove_token) /Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:1255: in emit_backref_from_collection_remove_event passive=PASSIVE_NO_FETCH) /Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:634: in pop passive=passive, check_old=value, pop=True) /Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:800: in set LOAD_AGAINST_COMMITTED) /Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/attributes.py:599: in get value = callable_(state, passive) /Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/strategies.py:834: in __call__ return strategy._load_for_state(state, passive) /Users/alex/.pyenv/versions/app/lib/python3.7/site-packages/sqlalchemy/orm/strategies.py:589: in _load_for_state self._invoke_raise_load(state, passive, "raise") _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ self = <sqlalchemy.orm.strategies.LazyLoader object at 0x1176b2f20> state = <sqlalchemy.orm.state.InstanceState object at 0x1162462b0>, passive = 53 lazy = 'raise' ``` I believe that the problem is that an exception is thrown unconditionally here: https://github.com/zzzeek/sqlalchemy/blob/7d372da7385be6a9817a20b6b62f7c4237af7b26/lib/sqlalchemy/orm/strategies.py#L588-L589 even if this escape hatch would have been hit: https://github.com/zzzeek/sqlalchemy/blob/7d372da7385be6a9817a20b6b62f7c4237af7b26/lib/sqlalchemy/orm/strategies.py#L626-L628 I think in the cases where the load is entirely unneeded (as it is in my case), the exception should not be raised. While a workaround is to set `sql_only=True`, I want to avoid that as it can lead to differences is results between testing and production (due to the presence of items in the identity map avoiding exceptions). The reason I say the load us unneeded is that sqla is attempting to load the old value in a soon to be replaced backref (ie this is part of the emit). |
From: Tom M. <iss...@bi...> - 2018-10-30 01:49:03
|
New issue 4352: Single table inheritance conflict resolution breaks for primary keys https://bitbucket.org/zzzeek/sqlalchemy/issues/4352/single-table-inheritance-conflict Tom Manderson: Single table inheritance has a conflict resolution method for columns that appear in multiple subclasses. When the column that appears in multiple subclasses is a primary key, the conflict resolution procedure is skipped and an error is thrown. This makes sense when the primary key isn't on the base class to start with, but if it is (eg. in my use case the primary key is added with `declared_attr.cascading`) then we should allow it. There's an [existing PR](https://github.com/zzzeek/sqlalchemy/pull/483) including the code below as a test case: ``` class TestBase(Base): __abstract__ = True @declared_attr.cascading def id(cls): col_val = None if TestBase not in cls.__bases__: col_val = cls.__table__.c.get('id') if col_val is None: col_val = Column(Integer, primary_key=True) return col_val class Person(TestBase): """single table base class""" __tablename__ = 'person' class Engineer(Person): """ single table inheritance, no extra cols """ class Manager(Person): """ single table inheritance, no extra cols """ ``` |