KeyError: 'sqlnotapplicable' when running alembic revision --autogenerate
I added new model, then run $ pipenv run alembic revision --autogenerate -m "Add new table TRAVIS_ACTIVE_USERS" --depends-on= Then I got this: INFO [alembic.runtime.migration] Context impl Db2Impl. INFO [alembic.runtime.migration] Will assume non-transactional DDL. /root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py:943: SAWarning: index key 'sqlnotapplicable' was not located in columns for table ‘github_active_users' "columns for table '%s'" % (flavor, c, table_name) Traceback (most recent call last): File "/root/.local/share/virtualenvs/butane-c47Aapyx/bin/alembic", line 8, in sys.exit(main()) File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/config.py", line 577, in main CommandLine(prog=prog).main(argv=argv) File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/config.py", line 571, in main self.run_cmd(cfg, options) File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/config.py", line 551, in run_cmd **dict((k, getattr(options, k, None)) for k in kwarg) File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/command.py", line 214, in revision script_directory.run_env() File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/script/base.py", line 489, in run_env util.load_python_file(self.dir, "env.py") File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file module = load_module_py(module_id, path) File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/util/compat.py", line 184, in load_module_py spec.loader.exec_module(module) File "", line 728, in exec_module File "", line 219, in _call_with_frames_removed File "butane/warehouse/env.py", line 91, in run_migrations_online() File "butane/warehouse/env.py", line 85, in run_migrations_online context.run_migrations() File "", line 8, in run_migrations File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/runtime/environment.py", line 846, in run_migrations self.get_context().run_migrations(**kw) File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/runtime/migration.py", line 509, in run_migrations for step in self._migrations_fn(heads, self): File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/command.py", line 190, in retrieve_migrations revision_context.run_autogenerate(rev, context) File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/autogenerate/api.py", line 442, in run_autogenerate self._run_environment(rev, migration_context, True) File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/autogenerate/api.py", line 482, in _run_environment autogen_context, migration_script File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 25, in _populate_migration_script _produce_net_changes(autogen_context, upgrade_ops) File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 51, in _produce_net_changes autogen_context, upgrade_ops, schemas File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/util/langhelpers.py", line 303, in go fn(*arg, **kw) File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 83, in _autogen_for_tables autogen_context, File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 170, in _compare_tables autogen_context, modify_table_ops, s, tname, t, None File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/util/langhelpers.py", line 303, in go fn(*arg, **kw) File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 493, in _compare_indexes_and_uniques conn_indexes = set(_make_index(ix, conn_table) for ix in conn_indexes) File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 493, in conn_indexes = set(_make_index(ix, conn_table) for ix in conn_indexes) File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 235, in _make_index *[conn_table.c[cname] for cname in params["column_names"]], File "/root/.local/share/virtualenvs/butane-c47Aapyx/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 235, in
Re: [sqlalchemy] ORM 3 level hieracrchy
It looks like I can keep the @declared_attr by using
"inherit_condition": cls.node_id == cls.__mro__[1].node_id
The __mro__[1] gets the leftmost (or only) parent class. As long as I
make sure that it the inheritance tree (and I wasn't planning on
multiple inheritance here) I should be ok.
On 9/3/20 10:25 AM, Mike Bayer wrote:
> yup that was the idea
>
>
> On Thu, Sep 3, 2020, at 10:24 AM, Richard Damon wrote:
>> I have a large number (around a dozen or more, and likely to grow) of
>> derived classes, so I was hoping to cut down repetition with the
>> @declared_attr.
>> So, 3rd (or farther) derived classes need the inherit_condition to point
>> to their immediate base. That does seem to remove the warning.
>>
>> On 9/3/20 9:58 AM, Mike Bayer wrote:
>> > you might be able to use the declared_attr __mapper__ but you would
>> > need to omit that erroneous inherit condition if the class is
>> > "Name". IMO it would be easier to follow and understand by simply
>> > using explicit __mapper_args__ on each class but this depends on what
>> > you're doing.
>> >
>> >
>> >
>> > On Thu, Sep 3, 2020, at 7:24 AM, Richard Damon wrote:
>> >> I've tried taking my code and changing the ForeignKey to be to
>> Node, and
>> >> that doesn't change the Warning.
>> >> Is the problem trying to DRY with the @declared_attr __mapper__?
>> >>
>> >> On 9/2/20 11:29 PM, Mike Bayer wrote:
>> >> > well you are giving Name an inherit condition that conflicts
>> with how
>> >> > you set up the foreign key.
>> >> >
>> >> > Name.node_id FKs to Property.node_id
>> >> >
>> >> > but then inherit condition is Name.node_id -> Node.node_id
>> >> >
>> >> > There seems to be a little unsmoothness to actually being able to
>> >> > configure it that way, that is, skipping over Property.node_id,
>> but in
>> >> > this case your FKs make it clear what you're going for which is the
>> >> > "normal' setup of Name->Property->Node so you can remove "inherit
>> >> > condition" from Name and it works fine:
>> >> >
>> >> > class Name(Property):
>> >> > __tablename__ = 'Name'
>> >> >
>> >> > node_id = Column(Integer, ForeignKey("Property.node_id"),
>> >> > primary_key=True)
>> >> >
>> >> > __mapper_args__ = {
>> >> > "polymorphic_identity": "Name",
>> >> > }
>> >> >
>> >> >
>> >> > or set it:
>> >> >
>> >> > class Name(Property):
>> >> > __tablename__ = 'Name'
>> >> >
>> >> > node_id = Column(Integer, ForeignKey("Property.node_id"),
>> >> > primary_key=True)
>> >> >
>> >> > __mapper_args__ = {
>> >> > "polymorphic_identity": "Name",
>> >> > "inherit_condition": node_id == Property.node_id,
>> >> > }
>> >> >
>> >> >
>> >> >
>> >> > On Wed, Sep 2, 2020, at 10:08 PM, Richard Damon wrote:
>> >> >> Here is the code, note in all cases node_id are foreign
>> >> key/primary_key
>> >> >> to a primary_key down the chain:
>> >> >>
>> >> >>
>> >> >> class Base:
>> >> >> """Base Class for SQLAlchemy ORM Classes"""
>> >> >> @declared_attr
>> >> >> def __tablename__(cls):
>> >> >> """Default the Table Name to the Class Name"""
>> >> >> return cls.__name__
>> >> >>
>> >> >> Base = declarative_base(cls=Base)
>> >> >>
>> >> >> class Node(Base):
>> >> >> """Class repesents the base of the User Data types."""
>> >> >> node_id = Column(Integer, primary_key=True)
>> >> >> type_name = Column(String(255), nullable=False) # todo should
>> >> come
>> >> >> from type_id
>> >> >>
>> >> >> @declared_attr
>> >> >> def __mapper_args__(cls):
>> >> >> if cls.__name__ == 'Node' :
>> >> >> __mapper_args__ = {
>> >> >> 'polymorphic_identity': 'Node',
>> >> >> 'polymorphic_on': cls.type_name,
>> >> >> }
>> >> >> else:
>> >> >> __mapper_args__ = {
>> >> >> 'polymorphic_identity': cls.__tablename__,
>> >> >> "inherit_condition": cls.node_id == Node.node_id
>> >> >> }
>> >> >> return __mapper_args__
>> >> >>
>> >> >> class Property(Node):
>> >> >> node_id = Column(Integer, ForeignKey('Node.node_id'),
>> >> >> primary_key=True)
>> >> >> ref_id = Column(Integer, ForeignKey('Node.node_id'))
>> >> >>
>> >> >> class Name(Property):
>> >> >> node_id = Column(Integer, ForeignKey('Property.node_id'),
>> >> >> primary_key=True)
>> >> >>
>> >> >>
>> >> >> On 9/2/20 9:39 PM, Mike Bayer wrote:
>> >> >> > there's an FAQ entry, a little bit dated but the general idea is
>> >> still
>> >> >> > there, at:
>> >> >> >
>> >> >>
>> >>
>> >
>> >https://docs.sqlalchemy.org/en/13/faq/ormconfiguration.html#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y
>> >> >> >
>> >> >> > for joined table inheritance, where Name(Node) -> node_id are
>> FK ->
>> >> >> > PK, the warning isn't emitted. so please share the mapping
>> if it is
>> >> >> > doing this when it shouldnt.
>> >> >> >
>> >> >> >
>> >> >> > On Wed, Sep 2, 2020, at 9:08 PM,
Re: [sqlalchemy] ORM 3 level hieracrchy
yup that was the idea
On Thu, Sep 3, 2020, at 10:24 AM, Richard Damon wrote:
> I have a large number (around a dozen or more, and likely to grow) of
> derived classes, so I was hoping to cut down repetition with the
> @declared_attr.
> So, 3rd (or farther) derived classes need the inherit_condition to point
> to their immediate base. That does seem to remove the warning.
>
> On 9/3/20 9:58 AM, Mike Bayer wrote:
> > you might be able to use the declared_attr __mapper__ but you would
> > need to omit that erroneous inherit condition if the class is
> > "Name". IMO it would be easier to follow and understand by simply
> > using explicit __mapper_args__ on each class but this depends on what
> > you're doing.
> >
> >
> >
> > On Thu, Sep 3, 2020, at 7:24 AM, Richard Damon wrote:
> >> I've tried taking my code and changing the ForeignKey to be to Node, and
> >> that doesn't change the Warning.
> >> Is the problem trying to DRY with the @declared_attr __mapper__?
> >>
> >> On 9/2/20 11:29 PM, Mike Bayer wrote:
> >> > well you are giving Name an inherit condition that conflicts with how
> >> > you set up the foreign key.
> >> >
> >> > Name.node_id FKs to Property.node_id
> >> >
> >> > but then inherit condition is Name.node_id -> Node.node_id
> >> >
> >> > There seems to be a little unsmoothness to actually being able to
> >> > configure it that way, that is, skipping over Property.node_id, but in
> >> > this case your FKs make it clear what you're going for which is the
> >> > "normal' setup of Name->Property->Node so you can remove "inherit
> >> > condition" from Name and it works fine:
> >> >
> >> > class Name(Property):
> >> > __tablename__ = 'Name'
> >> >
> >> > node_id = Column(Integer, ForeignKey("Property.node_id"),
> >> > primary_key=True)
> >> >
> >> > __mapper_args__ = {
> >> > "polymorphic_identity": "Name",
> >> > }
> >> >
> >> >
> >> > or set it:
> >> >
> >> > class Name(Property):
> >> > __tablename__ = 'Name'
> >> >
> >> > node_id = Column(Integer, ForeignKey("Property.node_id"),
> >> > primary_key=True)
> >> >
> >> > __mapper_args__ = {
> >> > "polymorphic_identity": "Name",
> >> > "inherit_condition": node_id == Property.node_id,
> >> > }
> >> >
> >> >
> >> >
> >> > On Wed, Sep 2, 2020, at 10:08 PM, Richard Damon wrote:
> >> >> Here is the code, note in all cases node_id are foreign
> >> key/primary_key
> >> >> to a primary_key down the chain:
> >> >>
> >> >>
> >> >> class Base:
> >> >> """Base Class for SQLAlchemy ORM Classes"""
> >> >> @declared_attr
> >> >> def __tablename__(cls):
> >> >> """Default the Table Name to the Class Name"""
> >> >> return cls.__name__
> >> >>
> >> >> Base = declarative_base(cls=Base)
> >> >>
> >> >> class Node(Base):
> >> >> """Class repesents the base of the User Data types."""
> >> >> node_id = Column(Integer, primary_key=True)
> >> >> type_name = Column(String(255), nullable=False) # todo should
> >> come
> >> >> from type_id
> >> >>
> >> >> @declared_attr
> >> >> def __mapper_args__(cls):
> >> >> if cls.__name__ == 'Node' :
> >> >> __mapper_args__ = {
> >> >> 'polymorphic_identity': 'Node',
> >> >> 'polymorphic_on': cls.type_name,
> >> >> }
> >> >> else:
> >> >> __mapper_args__ = {
> >> >> 'polymorphic_identity': cls.__tablename__,
> >> >> "inherit_condition": cls.node_id == Node.node_id
> >> >> }
> >> >> return __mapper_args__
> >> >>
> >> >> class Property(Node):
> >> >> node_id = Column(Integer, ForeignKey('Node.node_id'),
> >> >> primary_key=True)
> >> >> ref_id = Column(Integer, ForeignKey('Node.node_id'))
> >> >>
> >> >> class Name(Property):
> >> >> node_id = Column(Integer, ForeignKey('Property.node_id'),
> >> >> primary_key=True)
> >> >>
> >> >>
> >> >> On 9/2/20 9:39 PM, Mike Bayer wrote:
> >> >> > there's an FAQ entry, a little bit dated but the general idea is
> >> still
> >> >> > there, at:
> >> >> >
> >> >>
> >> > https://docs.sqlalchemy.org/en/13/faq/ormconfiguration.html#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y
> >> >> >
> >> >> > for joined table inheritance, where Name(Node) -> node_id are FK ->
> >> >> > PK, the warning isn't emitted. so please share the mapping if it is
> >> >> > doing this when it shouldnt.
> >> >> >
> >> >> >
> >> >> > On Wed, Sep 2, 2020, at 9:08 PM, Richard Damon wrote:
> >> >> >> I am getting the following error:
> >> >> >>
> >> >> >> SAWarning: Implicitly combining column Node.node_id with column
> >> >> >> Name.node_id under attribute 'node_id'. Please configure one
> >> or more
> >> >> >> attributes for these same-named columns explicitly.
> >> >> >>
> >> >> >> In my case I am using poymorphic classes by joining with the
> >> ORM. Node
> >> >> >> is the base of the hierarchy (derived from declarative_base),
> >> then
Re: [sqlalchemy] How can I use a composite foreign-key constraint with a "mixin" class using declarative?
dialect specific options are prefixed with the dialect name,e.g.
"mysql_engine", so that they only take place for the dialect currently
interacting with the schema object. they are ignored by any dialect that does
not have that name.
On Thu, Sep 3, 2020, at 10:15 AM, Simon King wrote:
> You'd have to wait for a response from Mike to be certain, but it
> seems overwhelmingly likely to me that dialect-specific options will
> always be ignored by other dialects. One of the strengths of
> SQLAlchemy is that it makes it easier to write code that works against
> multiple database dialects, so I can't see that changing.
>
> Simon
>
> On Thu, Sep 3, 2020 at 2:00 PM Nicolas Lykke Iversen
> wrote:
> >
> > Thanks Simon,
> >
> > Just to be clear:
> >
> >> dialect-specific options end up in table.dialect_options:
> >> https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.Table.dialect_options
> >> But different dialects seem to handle them in different ways.
> >
> >
> > Does this mean that it's not safe in general to mix dialect-specific
> > options in __table_args__, since it's arbitrary how they are handled?
> > Indeed, in your case it works (MySQL and SQLite), but that might not be the
> > case for other dialects like MSSQL?
> >
> > Best regards
> > Nicolas
> >
> >
> >
> >
> >
> >
> >
> >
> > Den tor. 3. sep. 2020 kl. 11.00 skrev Simon King :
> >>
> >> To be honest, I looked for documentation before I wrote my reply to
> >> you, and couldn't find anything. I just know that I often use MySQL in
> >> production but sqlite for tests, and sqlite never complained about the
> >> mysql-specific options in the table args.
> >>
> >> dialect-specific options end up in table.dialect_options:
> >>
> >> https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.Table.dialect_options
> >>
> >> But different dialects seem to handle them in different ways. For
> >> example, here's how mysql consumes table-level options:
> >>
> >> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/mysql/base.py#L1871
> >>
> >> whereas sqlite does this:
> >>
> >> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/sqlite/base.py#L1120
> >>
> >> Simon
> >>
> >> On Thu, Sep 3, 2020 at 5:38 AM Nicolas Lykke Iversen
> >> wrote:
> >> >
> >> > Thank you, Simon.
> >> >
> >> > Yes, __table_args__ is the only reason I’m creating separate modules.
> >> >
> >> > Where do you see that arguments that don’t match the database dialect of
> >> > the engine get ignored? I looked at the source code for answering this
> >> > question myself, but couldn't find an answer to that question.
> >> >
> >> > Will constructs like table-level foreign keys also just get ignored for
> >> > databases that don’t support them?
> >> >
> >> > In what scenarios would it make sense to use multiple modules, like I do?
> >> >
> >> > If possible, please provide a link to relevant part of the
> >> > documentation, I’m eager to learn more.
> >> >
> >> > Best wishes
> >> > Nicolas
> >> >
> >> > On Tue, 1 Sep 2020 at 10.49, Simon King wrote:
> >> >>
> >> >> Is __table_args__ the only reason why you are creating separate
> >> >>
> >> >> modules for the different databases? You can specify parameters for
> >> >>
> >> >> different database dialects in __table_args__, and the ones that don't
> >> >>
> >> >> match the current engine will be ignored. For example:
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> import sqlalchemy as sa
> >> >>
> >> >> from sqlalchemy.ext.declarative import declarative_base
> >> >>
> >> >>
> >> >>
> >> >> Base = declarative_base()
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> class SomeTable(Base):
> >> >>
> >> >> __tablename__ = "sometable"
> >> >>
> >> >> __table_args__ = {
> >> >>
> >> >> "mysql_default_charset": "utf8",
> >> >>
> >> >> "mysql_engine": "InnoDB",
> >> >>
> >> >> "sqlite_autoincrement": True,
> >> >>
> >> >> }
> >> >>
> >> >> id = sa.Column(sa.Integer(), primary_key=True)
> >> >>
> >> >> name = sa.Column(sa.Text())
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> engine = sa.create_engine("sqlite:///", echo=True)
> >> >>
> >> >> Base.metadata.create_all(engine)
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> Simon
> >> >>
> >> >>
> >> >>
> >> >> On Fri, Aug 28, 2020 at 10:35 AM Nicolas Lykke Iversen
> >> >>
> >> >> wrote:
> >> >>
> >> >> >
> >> >>
> >> >> > Hi all,
> >> >>
> >> >> >
> >> >>
> >> >> > I need to create identical models (mapped classes) for several
> >> >> > database backends, e.g. MySQL and MSSQL, that take different
> >> >> > __table_args__.
> >> >>
> >> >> >
> >> >>
> >> >> > Thus, I've opted for created one base for each database backend
> >> >> > defining the __table_args__ (base.py), while using common mixins for
> >> >> > defining the columns (mixin.py). The bases and mixins are then
> >> >> > combined in mssql.py and mysql.py to
Re: [sqlalchemy] ORM 3 level hieracrchy
I have a large number (around a dozen or more, and likely to grow) of
derived classes, so I was hoping to cut down repetition with the
@declared_attr.
So, 3rd (or farther) derived classes need the inherit_condition to point
to their immediate base. That does seem to remove the warning.
On 9/3/20 9:58 AM, Mike Bayer wrote:
> you might be able to use the declared_attr __mapper__ but you would
> need to omit that erroneous inherit condition if the class is
> "Name". IMO it would be easier to follow and understand by simply
> using explicit __mapper_args__ on each class but this depends on what
> you're doing.
>
>
>
> On Thu, Sep 3, 2020, at 7:24 AM, Richard Damon wrote:
>> I've tried taking my code and changing the ForeignKey to be to Node, and
>> that doesn't change the Warning.
>> Is the problem trying to DRY with the @declared_attr __mapper__?
>>
>> On 9/2/20 11:29 PM, Mike Bayer wrote:
>> > well you are giving Name an inherit condition that conflicts with how
>> > you set up the foreign key.
>> >
>> > Name.node_id FKs to Property.node_id
>> >
>> > but then inherit condition is Name.node_id -> Node.node_id
>> >
>> > There seems to be a little unsmoothness to actually being able to
>> > configure it that way, that is, skipping over Property.node_id, but in
>> > this case your FKs make it clear what you're going for which is the
>> > "normal' setup of Name->Property->Node so you can remove "inherit
>> > condition" from Name and it works fine:
>> >
>> > class Name(Property):
>> > __tablename__ = 'Name'
>> >
>> > node_id = Column(Integer, ForeignKey("Property.node_id"),
>> > primary_key=True)
>> >
>> > __mapper_args__ = {
>> > "polymorphic_identity": "Name",
>> > }
>> >
>> >
>> > or set it:
>> >
>> > class Name(Property):
>> > __tablename__ = 'Name'
>> >
>> > node_id = Column(Integer, ForeignKey("Property.node_id"),
>> > primary_key=True)
>> >
>> > __mapper_args__ = {
>> > "polymorphic_identity": "Name",
>> > "inherit_condition": node_id == Property.node_id,
>> > }
>> >
>> >
>> >
>> > On Wed, Sep 2, 2020, at 10:08 PM, Richard Damon wrote:
>> >> Here is the code, note in all cases node_id are foreign
>> key/primary_key
>> >> to a primary_key down the chain:
>> >>
>> >>
>> >> class Base:
>> >> """Base Class for SQLAlchemy ORM Classes"""
>> >> @declared_attr
>> >> def __tablename__(cls):
>> >> """Default the Table Name to the Class Name"""
>> >> return cls.__name__
>> >>
>> >> Base = declarative_base(cls=Base)
>> >>
>> >> class Node(Base):
>> >> """Class repesents the base of the User Data types."""
>> >> node_id = Column(Integer, primary_key=True)
>> >> type_name = Column(String(255), nullable=False) # todo should
>> come
>> >> from type_id
>> >>
>> >> @declared_attr
>> >> def __mapper_args__(cls):
>> >> if cls.__name__ == 'Node' :
>> >> __mapper_args__ = {
>> >> 'polymorphic_identity': 'Node',
>> >> 'polymorphic_on': cls.type_name,
>> >> }
>> >> else:
>> >> __mapper_args__ = {
>> >> 'polymorphic_identity': cls.__tablename__,
>> >> "inherit_condition": cls.node_id == Node.node_id
>> >> }
>> >> return __mapper_args__
>> >>
>> >> class Property(Node):
>> >> node_id = Column(Integer, ForeignKey('Node.node_id'),
>> >> primary_key=True)
>> >> ref_id = Column(Integer, ForeignKey('Node.node_id'))
>> >>
>> >> class Name(Property):
>> >> node_id = Column(Integer, ForeignKey('Property.node_id'),
>> >> primary_key=True)
>> >>
>> >>
>> >> On 9/2/20 9:39 PM, Mike Bayer wrote:
>> >> > there's an FAQ entry, a little bit dated but the general idea is
>> still
>> >> > there, at:
>> >> >
>> >>
>> >
>> >https://docs.sqlalchemy.org/en/13/faq/ormconfiguration.html#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y
>> >> >
>> >> > for joined table inheritance, where Name(Node) -> node_id are FK ->
>> >> > PK, the warning isn't emitted. so please share the mapping if it is
>> >> > doing this when it shouldnt.
>> >> >
>> >> >
>> >> > On Wed, Sep 2, 2020, at 9:08 PM, Richard Damon wrote:
>> >> >> I am getting the following error:
>> >> >>
>> >> >> SAWarning: Implicitly combining column Node.node_id with column
>> >> >> Name.node_id under attribute 'node_id'. Please configure one
>> or more
>> >> >> attributes for these same-named columns explicitly.
>> >> >>
>> >> >> In my case I am using poymorphic classes by joining with the
>> ORM. Node
>> >> >> is the base of the hierarchy (derived from declarative_base),
>> then I
>> >> >> have a class Property derived from it, and a class Name derived
>> from
>> >> >> Property. Each class has a primary_key named node_id, with a
>> >> foreign key
>> >> >> constraint one step done the hierarchy. I have also tried making
>> >> all the
>> >> >> Foreign key constraints point to Node.node_id and it doesn't make a
Re: [sqlalchemy] How can I use a composite foreign-key constraint with a "mixin" class using declarative?
You'd have to wait for a response from Mike to be certain, but it
seems overwhelmingly likely to me that dialect-specific options will
always be ignored by other dialects. One of the strengths of
SQLAlchemy is that it makes it easier to write code that works against
multiple database dialects, so I can't see that changing.
Simon
On Thu, Sep 3, 2020 at 2:00 PM Nicolas Lykke Iversen wrote:
>
> Thanks Simon,
>
> Just to be clear:
>
>> dialect-specific options end up in table.dialect_options:
>> https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.Table.dialect_options
>> But different dialects seem to handle them in different ways.
>
>
> Does this mean that it's not safe in general to mix dialect-specific options
> in __table_args__, since it's arbitrary how they are handled? Indeed, in your
> case it works (MySQL and SQLite), but that might not be the case for other
> dialects like MSSQL?
>
> Best regards
> Nicolas
>
>
>
>
>
>
>
>
> Den tor. 3. sep. 2020 kl. 11.00 skrev Simon King :
>>
>> To be honest, I looked for documentation before I wrote my reply to
>> you, and couldn't find anything. I just know that I often use MySQL in
>> production but sqlite for tests, and sqlite never complained about the
>> mysql-specific options in the table args.
>>
>> dialect-specific options end up in table.dialect_options:
>>
>> https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.Table.dialect_options
>>
>> But different dialects seem to handle them in different ways. For
>> example, here's how mysql consumes table-level options:
>>
>> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/mysql/base.py#L1871
>>
>> whereas sqlite does this:
>>
>> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/sqlite/base.py#L1120
>>
>> Simon
>>
>> On Thu, Sep 3, 2020 at 5:38 AM Nicolas Lykke Iversen
>> wrote:
>> >
>> > Thank you, Simon.
>> >
>> > Yes, __table_args__ is the only reason I’m creating separate modules.
>> >
>> > Where do you see that arguments that don’t match the database dialect of
>> > the engine get ignored? I looked at the source code for answering this
>> > question myself, but couldn't find an answer to that question.
>> >
>> > Will constructs like table-level foreign keys also just get ignored for
>> > databases that don’t support them?
>> >
>> > In what scenarios would it make sense to use multiple modules, like I do?
>> >
>> > If possible, please provide a link to relevant part of the documentation,
>> > I’m eager to learn more.
>> >
>> > Best wishes
>> > Nicolas
>> >
>> > On Tue, 1 Sep 2020 at 10.49, Simon King wrote:
>> >>
>> >> Is __table_args__ the only reason why you are creating separate
>> >>
>> >> modules for the different databases? You can specify parameters for
>> >>
>> >> different database dialects in __table_args__, and the ones that don't
>> >>
>> >> match the current engine will be ignored. For example:
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> import sqlalchemy as sa
>> >>
>> >> from sqlalchemy.ext.declarative import declarative_base
>> >>
>> >>
>> >>
>> >> Base = declarative_base()
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> class SomeTable(Base):
>> >>
>> >> __tablename__ = "sometable"
>> >>
>> >> __table_args__ = {
>> >>
>> >> "mysql_default_charset": "utf8",
>> >>
>> >> "mysql_engine": "InnoDB",
>> >>
>> >> "sqlite_autoincrement": True,
>> >>
>> >> }
>> >>
>> >> id = sa.Column(sa.Integer(), primary_key=True)
>> >>
>> >> name = sa.Column(sa.Text())
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> engine = sa.create_engine("sqlite:///", echo=True)
>> >>
>> >> Base.metadata.create_all(engine)
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> Simon
>> >>
>> >>
>> >>
>> >> On Fri, Aug 28, 2020 at 10:35 AM Nicolas Lykke Iversen
>> >>
>> >> wrote:
>> >>
>> >> >
>> >>
>> >> > Hi all,
>> >>
>> >> >
>> >>
>> >> > I need to create identical models (mapped classes) for several database
>> >> > backends, e.g. MySQL and MSSQL, that take different __table_args__.
>> >>
>> >> >
>> >>
>> >> > Thus, I've opted for created one base for each database backend
>> >> > defining the __table_args__ (base.py), while using common mixins for
>> >> > defining the columns (mixin.py). The bases and mixins are then combined
>> >> > in mssql.py and mysql.py to create the models.
>> >>
>> >> >
>> >>
>> >> > The problem is that I don't know how to create a table-level composite
>> >> > foreign-key constraint (ForeignKeyConstraint) by reading the following
>> >> > documentation:
>> >>
>> >> >
>> >>
>> >> > https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/mixins.html#mixing-in-relationships
>> >>
>> >> >
>> >>
>> >> > Indeed, it can create column-level foreign-keys (ForeignKey), but
>> >> > defining the ForeignKeyConstraint on any of the below classes yield
>> >> > errors, e.g.:
>> >>
>> >> >
>> >>
>> >> > class Project():
>> >>
>> >> >id = Column(Integer,
Re: [sqlalchemy] ORM 3 level hieracrchy
you might be able to use the declared_attr __mapper__ but you would need to
omit that erroneous inherit condition if the class is "Name". IMO it would be
easier to follow and understand by simply using explicit __mapper_args__ on
each class but this depends on what you're doing.
On Thu, Sep 3, 2020, at 7:24 AM, Richard Damon wrote:
> I've tried taking my code and changing the ForeignKey to be to Node, and
> that doesn't change the Warning.
> Is the problem trying to DRY with the @declared_attr __mapper__?
>
> On 9/2/20 11:29 PM, Mike Bayer wrote:
> > well you are giving Name an inherit condition that conflicts with how
> > you set up the foreign key.
> >
> > Name.node_id FKs to Property.node_id
> >
> > but then inherit condition is Name.node_id -> Node.node_id
> >
> > There seems to be a little unsmoothness to actually being able to
> > configure it that way, that is, skipping over Property.node_id, but in
> > this case your FKs make it clear what you're going for which is the
> > "normal' setup of Name->Property->Node so you can remove "inherit
> > condition" from Name and it works fine:
> >
> > class Name(Property):
> > __tablename__ = 'Name'
> >
> > node_id = Column(Integer, ForeignKey("Property.node_id"),
> > primary_key=True)
> >
> > __mapper_args__ = {
> > "polymorphic_identity": "Name",
> > }
> >
> >
> > or set it:
> >
> > class Name(Property):
> > __tablename__ = 'Name'
> >
> > node_id = Column(Integer, ForeignKey("Property.node_id"),
> > primary_key=True)
> >
> > __mapper_args__ = {
> > "polymorphic_identity": "Name",
> > "inherit_condition": node_id == Property.node_id,
> > }
> >
> >
> >
> > On Wed, Sep 2, 2020, at 10:08 PM, Richard Damon wrote:
> >> Here is the code, note in all cases node_id are foreign key/primary_key
> >> to a primary_key down the chain:
> >>
> >>
> >> class Base:
> >> """Base Class for SQLAlchemy ORM Classes"""
> >> @declared_attr
> >> def __tablename__(cls):
> >> """Default the Table Name to the Class Name"""
> >> return cls.__name__
> >>
> >> Base = declarative_base(cls=Base)
> >>
> >> class Node(Base):
> >> """Class repesents the base of the User Data types."""
> >> node_id = Column(Integer, primary_key=True)
> >> type_name = Column(String(255), nullable=False) # todo should come
> >> from type_id
> >>
> >> @declared_attr
> >> def __mapper_args__(cls):
> >> if cls.__name__ == 'Node' :
> >> __mapper_args__ = {
> >> 'polymorphic_identity': 'Node',
> >> 'polymorphic_on': cls.type_name,
> >> }
> >> else:
> >> __mapper_args__ = {
> >> 'polymorphic_identity': cls.__tablename__,
> >> "inherit_condition": cls.node_id == Node.node_id
> >> }
> >> return __mapper_args__
> >>
> >> class Property(Node):
> >> node_id = Column(Integer, ForeignKey('Node.node_id'),
> >> primary_key=True)
> >> ref_id = Column(Integer, ForeignKey('Node.node_id'))
> >>
> >> class Name(Property):
> >> node_id = Column(Integer, ForeignKey('Property.node_id'),
> >> primary_key=True)
> >>
> >>
> >> On 9/2/20 9:39 PM, Mike Bayer wrote:
> >> > there's an FAQ entry, a little bit dated but the general idea is still
> >> > there, at:
> >> >
> >> > https://docs.sqlalchemy.org/en/13/faq/ormconfiguration.html#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y
> >> >
> >> > for joined table inheritance, where Name(Node) -> node_id are FK ->
> >> > PK, the warning isn't emitted. so please share the mapping if it is
> >> > doing this when it shouldnt.
> >> >
> >> >
> >> > On Wed, Sep 2, 2020, at 9:08 PM, Richard Damon wrote:
> >> >> I am getting the following error:
> >> >>
> >> >> SAWarning: Implicitly combining column Node.node_id with column
> >> >> Name.node_id under attribute 'node_id'. Please configure one or more
> >> >> attributes for these same-named columns explicitly.
> >> >>
> >> >> In my case I am using poymorphic classes by joining with the ORM. Node
> >> >> is the base of the hierarchy (derived from declarative_base), then I
> >> >> have a class Property derived from it, and a class Name derived from
> >> >> Property. Each class has a primary_key named node_id, with a
> >> foreign key
> >> >> constraint one step done the hierarchy. I have also tried making
> >> all the
> >> >> Foreign key constraints point to Node.node_id and it doesn't make a
> >> >> difference.
> >> >>
> >> >> It is just a warning, and the lookup does seem to make the double
> >> join,
> >> >> so it doesn't seem to be a big problem, but it seems it want me to
> >> >> configure something for these, but I am not sure what.
> >> >>
> >> >> Two level inheritance is working just fine, it is just where it
> >> hits the
> >> >> 3rd level that it seems to want something explicit.
> >> >>
> >> >> --
> >> >> Richard Damon
> >>
> >>
> >> --
> >> Richard
Re: [sqlalchemy] How can I use a composite foreign-key constraint with a "mixin" class using declarative?
Thanks Simon,
Just to be clear:
dialect-specific options end up in table.dialect_options:
>
> https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.Table.dialect_options
> But different dialects seem to handle them in different ways.
Does this mean that it's not safe in general to mix dialect-specific
options in __table_args__, since it's arbitrary how they are handled?
Indeed, in your case it works (MySQL and SQLite), but that might not be the
case for other dialects like MSSQL?
Best regards
Nicolas
Den tor. 3. sep. 2020 kl. 11.00 skrev Simon King :
> To be honest, I looked for documentation before I wrote my reply to
> you, and couldn't find anything. I just know that I often use MySQL in
> production but sqlite for tests, and sqlite never complained about the
> mysql-specific options in the table args.
>
> dialect-specific options end up in table.dialect_options:
>
>
> https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.Table.dialect_options
>
> But different dialects seem to handle them in different ways. For
> example, here's how mysql consumes table-level options:
>
>
> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/mysql/base.py#L1871
>
> whereas sqlite does this:
>
>
> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/sqlite/base.py#L1120
>
> Simon
>
> On Thu, Sep 3, 2020 at 5:38 AM Nicolas Lykke Iversen
> wrote:
> >
> > Thank you, Simon.
> >
> > Yes, __table_args__ is the only reason I’m creating separate modules.
> >
> > Where do you see that arguments that don’t match the database dialect of
> the engine get ignored? I looked at the source code for answering this
> question myself, but couldn't find an answer to that question.
> >
> > Will constructs like table-level foreign keys also just get ignored for
> databases that don’t support them?
> >
> > In what scenarios would it make sense to use multiple modules, like I do?
> >
> > If possible, please provide a link to relevant part of the
> documentation, I’m eager to learn more.
> >
> > Best wishes
> > Nicolas
> >
> > On Tue, 1 Sep 2020 at 10.49, Simon King wrote:
> >>
> >> Is __table_args__ the only reason why you are creating separate
> >>
> >> modules for the different databases? You can specify parameters for
> >>
> >> different database dialects in __table_args__, and the ones that don't
> >>
> >> match the current engine will be ignored. For example:
> >>
> >>
> >>
> >>
> >>
> >> import sqlalchemy as sa
> >>
> >> from sqlalchemy.ext.declarative import declarative_base
> >>
> >>
> >>
> >> Base = declarative_base()
> >>
> >>
> >>
> >>
> >>
> >> class SomeTable(Base):
> >>
> >> __tablename__ = "sometable"
> >>
> >> __table_args__ = {
> >>
> >> "mysql_default_charset": "utf8",
> >>
> >> "mysql_engine": "InnoDB",
> >>
> >> "sqlite_autoincrement": True,
> >>
> >> }
> >>
> >> id = sa.Column(sa.Integer(), primary_key=True)
> >>
> >> name = sa.Column(sa.Text())
> >>
> >>
> >>
> >>
> >>
> >> engine = sa.create_engine("sqlite:///", echo=True)
> >>
> >> Base.metadata.create_all(engine)
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> Simon
> >>
> >>
> >>
> >> On Fri, Aug 28, 2020 at 10:35 AM Nicolas Lykke Iversen
> >>
> >> wrote:
> >>
> >> >
> >>
> >> > Hi all,
> >>
> >> >
> >>
> >> > I need to create identical models (mapped classes) for several
> database backends, e.g. MySQL and MSSQL, that take different __table_args__.
> >>
> >> >
> >>
> >> > Thus, I've opted for created one base for each database backend
> defining the __table_args__ (base.py), while using common mixins for
> defining the columns (mixin.py). The bases and mixins are then combined in
> mssql.py and mysql.py to create the models.
> >>
> >> >
> >>
> >> > The problem is that I don't know how to create a table-level
> composite foreign-key constraint (ForeignKeyConstraint) by reading the
> following documentation:
> >>
> >> >
> >>
> >> >
> https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/mixins.html#mixing-in-relationships
> >>
> >> >
> >>
> >> > Indeed, it can create column-level foreign-keys (ForeignKey), but
> defining the ForeignKeyConstraint on any of the below classes yield errors,
> e.g.:
> >>
> >> >
> >>
> >> > class Project():
> >>
> >> >id = Column(Integer, primary_key=True)
> >>
> >> >scan_id = Column(Integer, nullable=False)
> >>
> >> >...
> >>
> >> >
> >>
> >> > class Project(Base, mixin.Project):
> >>
> >> >ForeignKeyConstraint(['project.scan_id'], ['stash_scan.id'])
> >>
> >> >
> >>
> >> > sqlalchemy.exc.NoForeignKeysError: Could not determine join condition
> between parent/child tables on relationship Scan.projects - there are no
> foreign keys linking these tables. Ensure that referencing columns are
> associated with a ForeignKey or ForeignKeyConstraint, or specify a
> 'primaryjoin' expression.
> >>
> >> >
> >>
> >> > Is it not possible to use ForeignKeyConstraint
Re: [sqlalchemy] ORM 3 level hieracrchy
I've tried taking my code and changing the ForeignKey to be to Node, and
that doesn't change the Warning.
Is the problem trying to DRY with the @declared_attr __mapper__?
On 9/2/20 11:29 PM, Mike Bayer wrote:
> well you are giving Name an inherit condition that conflicts with how
> you set up the foreign key.
>
> Name.node_id FKs to Property.node_id
>
> but then inherit condition is Name.node_id -> Node.node_id
>
> There seems to be a little unsmoothness to actually being able to
> configure it that way, that is, skipping over Property.node_id, but in
> this case your FKs make it clear what you're going for which is the
> "normal' setup of Name->Property->Node so you can remove "inherit
> condition" from Name and it works fine:
>
> class Name(Property):
> __tablename__ = 'Name'
>
> node_id = Column(Integer, ForeignKey("Property.node_id"),
> primary_key=True)
>
> __mapper_args__ = {
> "polymorphic_identity": "Name",
> }
>
>
> or set it:
>
> class Name(Property):
> __tablename__ = 'Name'
>
> node_id = Column(Integer, ForeignKey("Property.node_id"),
> primary_key=True)
>
> __mapper_args__ = {
> "polymorphic_identity": "Name",
> "inherit_condition": node_id == Property.node_id,
> }
>
>
>
> On Wed, Sep 2, 2020, at 10:08 PM, Richard Damon wrote:
>> Here is the code, note in all cases node_id are foreign key/primary_key
>> to a primary_key down the chain:
>>
>>
>> class Base:
>> """Base Class for SQLAlchemy ORM Classes"""
>> @declared_attr
>> def __tablename__(cls):
>> """Default the Table Name to the Class Name"""
>> return cls.__name__
>>
>> Base = declarative_base(cls=Base)
>>
>> class Node(Base):
>> """Class repesents the base of the User Data types."""
>> node_id = Column(Integer, primary_key=True)
>> type_name = Column(String(255), nullable=False) # todo should come
>> from type_id
>>
>> @declared_attr
>> def __mapper_args__(cls):
>> if cls.__name__ == 'Node' :
>> __mapper_args__ = {
>> 'polymorphic_identity': 'Node',
>> 'polymorphic_on': cls.type_name,
>> }
>> else:
>> __mapper_args__ = {
>> 'polymorphic_identity': cls.__tablename__,
>> "inherit_condition": cls.node_id == Node.node_id
>> }
>> return __mapper_args__
>>
>> class Property(Node):
>> node_id = Column(Integer, ForeignKey('Node.node_id'),
>> primary_key=True)
>> ref_id = Column(Integer, ForeignKey('Node.node_id'))
>>
>> class Name(Property):
>> node_id = Column(Integer, ForeignKey('Property.node_id'),
>> primary_key=True)
>>
>>
>> On 9/2/20 9:39 PM, Mike Bayer wrote:
>> > there's an FAQ entry, a little bit dated but the general idea is still
>> > there, at:
>> >
>> >
>> >https://docs.sqlalchemy.org/en/13/faq/ormconfiguration.html#i-m-getting-a-warning-or-error-about-implicitly-combining-column-x-under-attribute-y
>> >
>> > for joined table inheritance, where Name(Node) -> node_id are FK ->
>> > PK, the warning isn't emitted. so please share the mapping if it is
>> > doing this when it shouldnt.
>> >
>> >
>> > On Wed, Sep 2, 2020, at 9:08 PM, Richard Damon wrote:
>> >> I am getting the following error:
>> >>
>> >> SAWarning: Implicitly combining column Node.node_id with column
>> >> Name.node_id under attribute 'node_id'. Please configure one or more
>> >> attributes for these same-named columns explicitly.
>> >>
>> >> In my case I am using poymorphic classes by joining with the ORM. Node
>> >> is the base of the hierarchy (derived from declarative_base), then I
>> >> have a class Property derived from it, and a class Name derived from
>> >> Property. Each class has a primary_key named node_id, with a
>> foreign key
>> >> constraint one step done the hierarchy. I have also tried making
>> all the
>> >> Foreign key constraints point to Node.node_id and it doesn't make a
>> >> difference.
>> >>
>> >> It is just a warning, and the lookup does seem to make the double
>> join,
>> >> so it doesn't seem to be a big problem, but it seems it want me to
>> >> configure something for these, but I am not sure what.
>> >>
>> >> Two level inheritance is working just fine, it is just where it
>> hits the
>> >> 3rd level that it seems to want something explicit.
>> >>
>> >> --
>> >> Richard Damon
>>
>>
>> --
>> Richard Damon
--
Richard Damon
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
Re: [sqlalchemy] SQLAlchemy MYSQL query utf8 character problem
On Thu, Sep 3, 2020 at 9:55 AM chat...@gmail.com wrote:
>
> Trying to query all items from a mysql (charset:utf8) table which has a field
> that contains rows with chinese and other special characters I am taking the
> above error
>
> items = session.query(Item).all()
>
> File
> "/root/.local/share/virtualenvs/server-WesSANjA/lib/python3.8/site-packages/MySQLdb/cursors.py",
> line 355, in _post_get_result self._rows = self._fetch_row(0) File
> "/root/.local/share/virtualenvs/server-WesSANjA/lib/python3.8/site-packages/MySQLdb/cursors.py",
> line 328, in _fetch_row return self._result.fetch_row(size,
> self._fetch_type) File "/usr/local/lib/python3.8/encodings/cp1252.py", line
> 15, in decode return codecs.charmap_decode(input,errors,decoding_table)
>
> UnicodeDecodeError: 'charmap' codec can't decode byte 0x9d in position 30:
> character maps to
>
You mention utf8, but the error suggests that the data is being
decoded as cp1252. Are you declaring an explicit charset when you
create your engine, as suggested here:
https://docs.sqlalchemy.org/en/13/dialects/mysql.html#unicode
What does this output:
for row in dbsession.execute("show variables like 'character%'").fetchall():
print(row)
Warning: I've run an application for a long time where I didn't
specify the charset in the connection string. SQLAlchemy defaulted to
encoding strings as utf8 (because the dialect didn't support unicode
strings). However, my output from the above command looked something
like this:
('character_set_client', 'latin1')
('character_set_connection', 'latin1')
('character_set_database', 'utf8')
('character_set_filesystem', 'binary')
('character_set_results', 'latin1')
('character_set_server', 'latin1')
('character_set_system', 'utf8')
('character_sets_dir', '/usr/share/mysql/charsets/')
This meant that SQLAlchemy was sending utf-8 strings, but the database
was interpreting them as latin1. To make things worse, some of my
tables have a default charset of latin1, and the others are utf8. The
result is that the tables that are declared to hold latin1 data
actually hold utf8, and the tables that are declared to hold utf8
actually hold double-encoded utf8.
Simon
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexfzHCGuwR3Xe-yBRLfMOm%3DjEOpY3KDrbkfPFA20zuB3Zw%40mail.gmail.com.
Re: [sqlalchemy] How can I use a composite foreign-key constraint with a "mixin" class using declarative?
To be honest, I looked for documentation before I wrote my reply to
you, and couldn't find anything. I just know that I often use MySQL in
production but sqlite for tests, and sqlite never complained about the
mysql-specific options in the table args.
dialect-specific options end up in table.dialect_options:
https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.Table.dialect_options
But different dialects seem to handle them in different ways. For
example, here's how mysql consumes table-level options:
https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/mysql/base.py#L1871
whereas sqlite does this:
https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/sqlite/base.py#L1120
Simon
On Thu, Sep 3, 2020 at 5:38 AM Nicolas Lykke Iversen wrote:
>
> Thank you, Simon.
>
> Yes, __table_args__ is the only reason I’m creating separate modules.
>
> Where do you see that arguments that don’t match the database dialect of the
> engine get ignored? I looked at the source code for answering this question
> myself, but couldn't find an answer to that question.
>
> Will constructs like table-level foreign keys also just get ignored for
> databases that don’t support them?
>
> In what scenarios would it make sense to use multiple modules, like I do?
>
> If possible, please provide a link to relevant part of the documentation, I’m
> eager to learn more.
>
> Best wishes
> Nicolas
>
> On Tue, 1 Sep 2020 at 10.49, Simon King wrote:
>>
>> Is __table_args__ the only reason why you are creating separate
>>
>> modules for the different databases? You can specify parameters for
>>
>> different database dialects in __table_args__, and the ones that don't
>>
>> match the current engine will be ignored. For example:
>>
>>
>>
>>
>>
>> import sqlalchemy as sa
>>
>> from sqlalchemy.ext.declarative import declarative_base
>>
>>
>>
>> Base = declarative_base()
>>
>>
>>
>>
>>
>> class SomeTable(Base):
>>
>> __tablename__ = "sometable"
>>
>> __table_args__ = {
>>
>> "mysql_default_charset": "utf8",
>>
>> "mysql_engine": "InnoDB",
>>
>> "sqlite_autoincrement": True,
>>
>> }
>>
>> id = sa.Column(sa.Integer(), primary_key=True)
>>
>> name = sa.Column(sa.Text())
>>
>>
>>
>>
>>
>> engine = sa.create_engine("sqlite:///", echo=True)
>>
>> Base.metadata.create_all(engine)
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> Simon
>>
>>
>>
>> On Fri, Aug 28, 2020 at 10:35 AM Nicolas Lykke Iversen
>>
>> wrote:
>>
>> >
>>
>> > Hi all,
>>
>> >
>>
>> > I need to create identical models (mapped classes) for several database
>> > backends, e.g. MySQL and MSSQL, that take different __table_args__.
>>
>> >
>>
>> > Thus, I've opted for created one base for each database backend defining
>> > the __table_args__ (base.py), while using common mixins for defining the
>> > columns (mixin.py). The bases and mixins are then combined in mssql.py and
>> > mysql.py to create the models.
>>
>> >
>>
>> > The problem is that I don't know how to create a table-level composite
>> > foreign-key constraint (ForeignKeyConstraint) by reading the following
>> > documentation:
>>
>> >
>>
>> > https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/mixins.html#mixing-in-relationships
>>
>> >
>>
>> > Indeed, it can create column-level foreign-keys (ForeignKey), but defining
>> > the ForeignKeyConstraint on any of the below classes yield errors, e.g.:
>>
>> >
>>
>> > class Project():
>>
>> >id = Column(Integer, primary_key=True)
>>
>> >scan_id = Column(Integer, nullable=False)
>>
>> >...
>>
>> >
>>
>> > class Project(Base, mixin.Project):
>>
>> >ForeignKeyConstraint(['project.scan_id'], ['stash_scan.id'])
>>
>> >
>>
>> > sqlalchemy.exc.NoForeignKeysError: Could not determine join condition
>> > between parent/child tables on relationship Scan.projects - there are no
>> > foreign keys linking these tables. Ensure that referencing columns are
>> > associated with a ForeignKey or ForeignKeyConstraint, or specify a
>> > 'primaryjoin' expression.
>>
>> >
>>
>> > Is it not possible to use ForeignKeyConstraint with the base/mixin design
>> > I'm using?
>>
>> >
>>
>> > SQLAlchemy Version: 1.3.17.
>>
>> >
>>
>> > base.py:
>>
>> > class SqlBase():
>>
>> >@declared_attr
>>
>> > def __tablename__(cls):
>>
>> > return f'stash_{cls.__name__.lower()}'
>>
>> >
>>
>> >def __repr__(self):
>>
>> > return f'<{self.__class__.__name__}(id=\'{self.id}\')>'
>>
>> >
>>
>> > class MySqlBase(SqlBase):
>>
>> >__table_args__ = {'mysql_default_charset': 'utf8',
>>
>> > 'mysql_collate': 'utf8_bin'}
>>
>> >
>>
>> > class MsSqlBase(SqlBase):
>>
>> >__table_args__ = {}
>>
>> >
>>
>> > mixin.py:
>>
>> > class Project():
>>
>> >id = Column(Integer, primary_key=True)
>>
>> >key = Column(Text, nullable=False)
>>
>> >name = Column(Text, nullable=False)
>>
>> >href = Column(Text,
[sqlalchemy] SQLAlchemy MYSQL query utf8 character problem
Trying to query all items from a mysql (charset:utf8) table which has a field that contains rows with chinese and other special characters I am taking the above error items = session.query(Item).all() File "/root/.local/share/virtualenvs/server-WesSANjA/lib/python3.8/site-packages/MySQLdb/cursors.py", line 355, in _post_get_result self._rows = self._fetch_row(0) File "/root/.local/share/virtualenvs/server-WesSANjA/lib/python3.8/site-packages/MySQLdb/cursors.py", line 328, in _fetch_row return self._result.fetch_row(size, self._fetch_type) File "/usr/local/lib/python3.8/encodings/cp1252.py", line 15, in decode return codecs.charmap_decode(input,errors,decoding_table) UnicodeDecodeError: 'charmap' codec can't decode byte 0x9d in position 30: character maps to -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/e2698164-9a9d-42ed-851f-4d9f9c88a229n%40googlegroups.com.
