Is it possible for alembic or other migration tool to handle case sensitivity or insensitivity across database engines

[junepeach]

For case insensitive columns:
MySQL - use utf8_general_ci
SQLite - use NOCASE collation

....

Can migration tool handle that for most databases or it should be better done in application code?

Thanks and best regards,

[Michael Bayer]

On Dec 6, 2012, at 3:17 PM, junepeach wrote:

For case insensitive columns:
MySQL - use utf8_general_ci
SQLite - use NOCASE collation

....

Can migration tool handle that for most databases or it should be better done in application code?

sure:

def character_type(length):

    return VARCHAR(length).with_variant(

                VARCHAR(length, collation='utf8_general_ci'), 'mysql'

            ).with_variant(

                VARCHAR(length, collation='NOCASE'), 'sqlite'

            )

Table("sometable", metadata,

    Column("some_text", character_type(200))

)

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

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

Thanks and best regards,

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/cgxdiM7P4yMJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[junepeach]

Thanks, this is nice, I will use it in my code.

[junepeach]

I tested my code, and got below:

Traceback (most recent call last):
  File "/usr/local/bin/alembic", line 9, in <module>
    load_entry_point('alembic==0.4.0', 'console_scripts', 'alembic')()
  File "/usr/local/lib/python2.7/dist-packages/alembic/config.py", line 255, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/usr/local/lib/python2.7/dist-packages/alembic/config.py", line 250, in main
    self.run_cmd(cfg, options)
  File "/usr/local/lib/python2.7/dist-packages/alembic/config.py", line 241, in run_cmd
    **dict((k, getattr(options, k)) for k in kwarg)
  File "/usr/local/lib/python2.7/dist-packages/alembic/command.py", line 97, in revision
    script.run_env()
  File "/usr/local/lib/python2.7/dist-packages/alembic/script.py", line 191, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/usr/local/lib/python2.7/dist-packages/alembic/util.py", line 185, in load_python_file
    module = imp.load_source(module_id, path, open(path, 'rb'))
  File "alembic/env.py", line 18, in <module>
    from dbmodule import SAINT8
  File "/home/bethesda/Documents/dbmodule/mytables.py", line 35, in <module>
    Column('data', character_type(32)),
  File "/home/bethesda/Documents/dbmodule/mytables.py", line 21, in character_type

    VARCHAR(length, collation='utf8_general_ci'), 'mysql'

TypeError: __init__() got an unexpected keyword argument 'collation'

Did I miss importing a class related with 'collation'? I tried 'from sqlalchemy.types import *', and some others, but didn't get it resolved, what is wrong?

Thanks,
jp

[Michael Bayer]

versionadded is 0.8

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/JvxaQU8BifsJ.

[junepeach]

Oh, I see, mine is not sqlalchemy 0.8. Thanks.

[junepeach]

How to upgrade to a newer sqlalchemy version? I can not find a related document. Should I just use pip to install the current one? Will both version conflict?
Thank you very much for your quick response and help!

JP

[Audrius Kažukauskas]

On Thu, 2012-12-06 at 17:43:45 -0800, junepeach wrote:
> How to upgrade to a newer sqlalchemy version? I can not find a related
> document. Should I just use pip to install the current one? Will both
> version conflict?

The answer depends on how and where SA is installed already. The best
way IMHO is to use separate virtualenvs for each of your projects (if
you're not doing this already). Since SQLAlchemy 0.8 is not released
yet, you'd need to issue pip (from within virtualenv) as follows:

pip install -U -e hg+https://bitbucket.org/sqlalchemy/sqlalchemy#egg=SQLAlchemy

--
Audrius Kažukauskas
http://neutrino.lt/

[junepeach]

Thank you Mike and Audrius, this is very helpful. I have  installed  SQLAlchemy 0.8.0b1 and tried the code Mike gave to me:

Base = declarative_base()

def character_type(length):
    return String(length).with_variant(String(length, collation='utf8_general_ci'), 'mysql').with_variant(String(length, collation='NOCASE'), 'sqlite')

Atable = Table("atable", Base.metadata,
    Column("name", character_type(200))
    )

 

It works in sqlalchemy, but not in alembic.

after running 'alembic revision --autogenerate', and got migration code:

 

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('atable',
    sa.Column('name', sa.Variant(length=200), nullable=True),
    sa.PrimaryKeyConstraint()
    )

Surely 'Variant' is strange to me, but I have no idea, then I got below after I typed command 'alembic upgrade head':

INFO  [alembic.migration] Context impl MySQLImpl.
INFO  [alembic.migration] Will assume non-transactional DDL.
INFO  [alembic.migration] Running upgrade None -> 1ba36c080bdb

Traceback (most recent call last):
  File "/usr/local/bin/alembic", line 9, in <module>

    load_entry_point('alembic==0.3.6', 'console_scripts', 'alembic')()
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.3.6-py2.7.egg/alembic/config.py", line 229, in main

    **dict((k, getattr(options, k)) for k in kwarg)

  File "/usr/local/lib/python2.7/dist-packages/alembic-0.3.6-py2.7.egg/alembic/command.py", line 121, in upgrade
    script.run_env()
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.3.6-py2.7.egg/alembic/script.py", line 192, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.3.6-py2.7.egg/alembic/util.py", line 185, in load_python_file

    module = imp.load_source(module_id, path, open(path, 'rb'))

  File "alembic/env.py", line 76, in <module>
    run_migrations_online()
  File "alembic/env.py", line 69, in run_migrations_online
    context.run_migrations()
  File "<string>", line 7, in run_migrations
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.3.6-py2.7.egg/alembic/environment.py", line 467, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python2.7/dist-packages/alembic-0.3.6-py2.7.egg/alembic/migration.py", line 211, in run_migrations
    change(**kw)
  File "alembic/versions/1ba36c080bdb_.py", line 25, in upgrade
    sa.Column('name', sa.Variant(length=200), nullable=True),
AttributeError: 'module' object has no attribute 'Variant'

 

Did alembic  accept user defined data type? Can I get it to my alembic migration script?

 

Thank you very much for your help!

[Michael Bayer]

On Dec 7, 2012, at 4:01 PM, junepeach wrote:

Thank you Mike and Audrius, this is very helpful. I have  installed  SQLAlchemy 0.8.0b1 and tried the code Mike gave to me:

Base = declarative_base()

def character_type(length):
    return String(length).with_variant(String(length, collation='utf8_general_ci'), 'mysql').with_variant(String(length, collation='NOCASE'), 'sqlite')

Atable = Table("atable", Base.metadata,
    Column("name", character_type(200))
    )

 

It works in sqlalchemy, but not in alembic.

after running 'alembic revision --autogenerate', and got migration code:

 

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('atable',
    sa.Column('name', sa.Variant(length=200), nullable=True),
    sa.PrimaryKeyConstraint()
    )

OK, Alembic autogen isn't doing __repr__() for  "variant" correctly right now, we can look into that (added http://www.sqlalchemy.org/trac/ticket/2628) , for now you'd need to put your character_type() into the migration scripts manually, or subclass TypeDecorator to do it:

from sqlalchemy.types import TypeDecorator

class character_type(TypeDecorator):

    def __init__(self, length):

        self.impl = String(length).with_variant(... everything from before ...)

   def __repr__(self):

        return "character_type(%d)" % self.impl.length

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/EdMyQ7t_pokJ.

[junepeach]

Mike,

 

Thanks very much for your quick response. Now it works, sqlite works fine,  and the result from mysql seems not exact what I need, see below.

 

......

from sqlalchemy.types import TypeDecorator, String

class character_type(TypeDecorator):
    def __init__(self, length):

        self.impl = String(length).with_variant(String(length, collation='utf8_general_ci'), 'mysql').with_variant(String(length, collation='NOCASE'), 'sqlite')

    def __repr__(self):
        return "character_type(%d)" % self.impl.length

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('atable',

    sa.Column('name', character_type(length=200), nullable=True),
    sa.PrimaryKeyConstraint()
    )
......
and after running 'alembic upgrade head', created table in mysql:
CREATE TABLE `atable` (
  `name` varchar(200) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and created table in sqlite:
CREATE TABLE atable (
    name VARCHAR(200) COLLATE "NOCASE"
);

 

Do I need to change something in the code?

[Michael Bayer]

turn on your echo=True, SQLAlchemy is spitting it out.   SHOW CREATE TABLE doesn't seem to give it back to us:

mysql> create table x (name varchar(200) collate 'utf8_general_ci');

Query OK, 0 rows affected (0.06 sec)

mysql> show create table x;

+-------+--------------------------------------------------------------------------------------------+

| Table | Create Table                                                                               |

+-------+--------------------------------------------------------------------------------------------+

| x     | CREATE TABLE `x` (

  `name` varchar(200) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

+-------+--------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> 

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/189ndo8qN6gJ.

[junepeach]

Sorry, I haven't touched that issue recently. Where I should turn on 'echo=true', in module or migration script? I saw 'create_engine' has this, but I didn't directly use 'create_engine'.

SHOW CREATE TABLE gave something back to me:

utf8_general_ci is default collation rule in charset utf8.
mysql> CREATE TABLE t1(col1 CHAR(10) COLLATE utf8_unicode_ci)CHARACTER SET latin1 COLLATE latin1_bin;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`col1` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin |

mysql> CREATE TABLE t2
-> (
-> col1 CHAR(10) COLLATE utf8_general_ci
-> ) CHARACTER SET latin1 COLLATE latin1_bin;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t2;
+-------+---------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`col1` char(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin |
+-------+---------------------------------------------------------------------------------------------------------------------------------+

[junepeach]

Please ignore it, actually you are right, when I changed to collation 'utf8_unicode_ci', it works perfectly fine.
Thanks a lot!