Set column collation only if driver == 'mysql'

[Stefan Scherfke]

Hi all,

I need to use a MySQL db in production but want to run some tests locally on Sqlite.

In general, this is no problem with sqlalchemy. BUT: I need to store 4-byte unicode characters (emoji an stuff), so I need to set mysql charset (and collation) to utf8mb4 (and utf8mb4_unicode_520_ci):

class MyTable(Base):
    __tablename__ = 'my_table'
    __table_args__ = (
        {'mysql_character_set': 'utf8mb4', 'mysql_collate': 'utf8mb4_unicode_520_ci'},
    )

    id = Column(Integer, primary_key=True)
    # name = Column(String(500), nullable=False, index=True)
    name = Column(String(500, collation=COLLATION), nullable=False, index=True)

The snippet above works with mysql, but the *collation* keyword for `Column` breaks Sqlite.

Is there a way to only set the collation attribute if the driver is mysql? Or maybe to change it on the fly (e.g., via pytests's monkeypatch?)

Using an if-else-Statement on module level *nearly* works, but with this I still can't use pytest.monkeypatch.

Cheers,
Stefan

[Mike Bayer]

On Mon, Aug 14, 2017 at 6:29 AM, Stefan Scherfke
<stefan....@gmail.com> wrote:
> Hi all,
>
> I need to use a MySQL db in production but want to run some tests locally on
> Sqlite.
>
> In general, this is no problem with sqlalchemy. BUT: I need to store 4-byte
> unicode characters (emoji an stuff), so I need to set mysql charset (and
> collation) to utf8mb4 (and utf8mb4_unicode_520_ci):
>
> class MyTable(Base):
> __tablename__ = 'my_table'
> __table_args__ = (
> {'mysql_character_set': 'utf8mb4', 'mysql_collate':
> 'utf8mb4_unicode_520_ci'},
> )
>
> id = Column(Integer, primary_key=True)
> # name = Column(String(500), nullable=False, index=True)
> name = Column(String(500, collation=COLLATION), nullable=False,
> index=True)
>
>
> The snippet above works with mysql, but the *collation* keyword for `Column`
> breaks Sqlite.
>
> Is there a way to only set the collation attribute if the driver is mysql?
> Or maybe to change it on the fly (e.g., via pytests's monkeypatch?)

we have the with_variant() method which should get you through this:

http://docs.sqlalchemy.org/en/latest/core/type_api.html?highlight=variant#sqlalchemy.types.TypeEngine.with_variant

in fact the example right there illustrates your use case:

from sqlalchemy.types import String
from sqlalchemy.dialects import mysql

s = String(500)

s = s.with_variant(mysql.VARCHAR(500, collation='foo'), 'mysql')

>
> Using an if-else-Statement on module level *nearly* works, but with this I
> still can't use pytest.monkeypatch.
>
> Cheers,
> Stefan
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Stefan Scherfke]

Am Montag, 14. August 2017 15:12:19 UTC+2 schrieb Mike Bayer:

we have the with_variant() method which should get you through this:

http://docs.sqlalchemy.org/en/latest/core/type_api.html?highlight=variant#sqlalchemy.types.TypeEngine.with_variant

in fact the example right there illustrates your use case:

from sqlalchemy.types import String
from sqlalchemy.dialects import mysql

s = String(500)

s = s.with_variant(mysql.VARCHAR(500, collation='foo'), 'mysql')

Thank you very much! Didn’t find it in the docs.

I also found out that I actually don’t need to set the charset and collaction on clumnlevel if it should be the same as on table level, so the both examples would produce the same results:

class MyTable(Base):
    __tablename__ = 'my_table'
    __table_args__ = (
        {'mysql_character_set': 'utf8mb4', 'mysql_collate': 'utf8mb4_unicode_520_ci'},
    )

    id = Column(Integer, primary_key=True)

    name = Column(String(500), nullable=False, index=True)

class MyTableVariant(Base):
    __tablename__ = 'my_table_variant'

    __table_args__ = (
       {'mysql_character_set': 'utf8mb4', 'mysql_collate': 'utf8mb4_unicode_520_ci'},
    )

    id = Column(Integer, primary_key=True)

    name = Column(String(500).with_variant(String(500, collation='utf8mb4_unicode_520_ci'), 'mysql'), nullable=False, index=True)