Thank you, I have prepared a simple example with three tables
https://gist.github.com/uralbash/a623e621093a6a10fd2ea85b5a1ee124
To avoid install FireBird in my system I use Docker + Vagrant
https://github.com/uralbash/docker-template/blob/master/vagrant/databases/firebird/Vagrantfile
and GUI client FlameRobin
понедельник, 14 ноября 2016 г., 20:19:52 UTC+5 пользователь Mike Bayer
написал:
On 11/14/2016 01:43 AM, uralbash wrote:
> I use quoted_name to describe the table schema (declarative
method) in
> my project like this:
>
> |
> classPeople(Base):
> """
> .. restapi::
> :table: people
> """
> __tablename__ =quoted_name('people',quote=True)
>
> id =Column(
> quoted_name("id",quote=True),
> Integer,Sequence('GEN_people_ID'),
> primary_key=True,autoincrement=True
> )
> name =Column(
> quoted_name("name",quote=True),
> Unicode
> )
> |
>
> And it's work ok for me.
> Now I want to make migration for other FireBird database with auto
> reflect table, because it designed outside of python and SQLAlchemy.
> I redid env.py as you suggested. At first I get all tablenames by raw
> query and then autoload table and put it to metadata.
>
> |
> # future
> from__future__ importwith_statement
>
> # standard library
> fromlogging.config importfileConfig
>
> # SQLAlchemy
> importsqlalchemy
> fromsqlalchemy importTable,MetaData,pool,engine_from_config
> fromsqlalchemy.ext.automap importautomap_base
> fromsqlalchemy.sql.elements importquoted_name
> fromsqlalchemy.ext.declarative importdeclarative_base
>
> # third-party
> fromalembic importcontext
> fromalembic.ddl.impl importDefaultImpl
>
>
> classFirebirdImpl(DefaultImpl):
> __dialect__ ='firebird'
> transactional_ddl =True
>
>
> # this is the Alembic Config object, which provides
> # access to the values within the .ini file in use.
> config =context.config
>
> metadata =MetaData()
>
> engine =engine_from_config(
> config.get_section(config.config_ini_section),
> prefix='sqlalchemy.',
> poolclass=pool.NullPool)
>
> q =engine.execute('''
> select rdb$relation_name
> from rdb$relations
> where rdb$view_blr is null
> and (rdb$system_flag is null or rdb$system_flag = 0)
> ''')
> tables =[x[0].strip()forx inq.fetchall()]
>
> create_done =0
>
> while(notcreate_done):
> create_done =1
> fortable intables:
> print(table)
> iftable.isupper():
> try:
> Table(table,metadata,autoload_with=engine)
> exceptsqlalchemy.exc.NoSuchTableErrorase:
> create_done =0
> continue
> try:
> _table =Table(quoted_name(table,True),metadata,
> autoload_with=engine)
> exceptsqlalchemy.exc.NoSuchTableErrorase:
> create_done =0
>
> print(metadata.tables)
> print(metadata.tables.keys())
the stack trace indicates it's failing when it iterates through the
list
of foreign key constraints for a table, finds one that refers to a
table
called "readers", and then the case sensitivity isn't working out such
that it can't actually locate a table of that name.
if you can isolate this single pair of tables, then do a simple test
script that's like :
Table(<tablename>, metadata, autoload_with=engine)
where <tablename> is the table that has a foreign key reference to
"readers".
then if you could show me the CREATE TABLE statements for those two
tables verbatim, if I can get a firebird running somewhere I can try to
find time to run this and look into it.
>
> # Interpret the config file for Python logging.
> # This line sets up loggers basically.
> fileConfig(config.config_file_name)
>
> # add your model's MetaData object here
> # for 'autogenerate' support
> # from myapp import mymodel
> # target_metadata = mymodel.Base.metadata
> target_metadata =metadata
>
> # other values from the config, defined by the needs of env.py,
> # can be acquired:
> # my_important_option = config.get_main_option("my_important_option")
> # ... etc.
>
>
> defrun_migrations_offline():
> """Run migrations in 'offline' mode.
>
> This configures the context with just a URL
> and not an Engine, though an Engine is acceptable
> here as well. By skipping the Engine creation
> we don't even need a DBAPI to be available.
>
> Calls to context.execute() here emit the given string to the
> script output.
>
> """
> url =config.get_main_option("sqlalchemy.url")
> context.configure(
> url=url,
> target_metadata=target_metadata,
> literal_binds=True
> )
>
> withcontext.begin_transaction():
> context.run_migrations()
>
>
> defrun_migrations_online():
> """Run migrations in 'online' mode.
>
> In this scenario we need to create an Engine
> and associate a connection with the context.
>
> """
>
> withengine.connect()asconnection:
> context.configure(
> connection=connection,
> target_metadata=target_metadata
> )
>
> withcontext.begin_transaction():
> context.run_migrations()
>
>
> ifcontext.is_offline_mode():
> run_migrations_offline()
> else:
> run_migrations_online()
>
> |
>
> It seems now the tables are created properly but alembic still raise
> exception bytable names
>
>
> |
> INFO [alembic.runtime.migration]Contextimpl FirebirdImpl.
> INFO [alembic.runtime.migration]Willassume transactional DDL.
> INFO [alembic.autogenerate.compare]Detectedadded table 'COMPANIES'
> INFO [alembic.autogenerate.compare]Detectedadded table 'CARD'
> INFO [alembic.autogenerate.compare]Detectedadded table 'LOCATIONS'
> INFO [alembic.autogenerate.compare]Detectedadded table 'RADIO'
> Traceback(most recent call last):
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/bin/alembic",line
> 9,in<module>
> load_entry_point('alembic==0.8.8','console_scripts','alembic')()
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/config.py",line
> 479,inmain
> CommandLine(prog=prog).main(argv=argv)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/config.py",line
> 473,inmain
> self.run_cmd(cfg,options)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/config.py",line
> 456,inrun_cmd
> **dict((k,getattr(options,k))fork inkwarg)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/command.py",line
> 117,inrevision
> script_directory.run_env()
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/script/base.py",line
> 407,inrun_env
> util.load_python_file(self.dir,'env.py')
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/util/pyfiles.py",line
> 93,inload_python_file
> module=load_module_py(module_id,path)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/util/compat.py",line
> 68,inload_module_py
> module_id,path).load_module(module_id)
> File"<frozen importlib._bootstrap_external>",line
> 388,in_check_name_wrapper
> File"<frozen importlib._bootstrap_external>",line 809,inload_module
> File"<frozen importlib._bootstrap_external>",line 668,inload_module
> File"<frozen importlib._bootstrap>",line 268,in_load_module_shim
> File"<frozen importlib._bootstrap>",line 693,in_load
> File"<frozen importlib._bootstrap>",line 673,in_load_unlocked
> File"<frozen importlib._bootstrap_external>",line 665,inexec_module
> File"<frozen importlib._bootstrap>",line
222,in_call_with_frames_removed
> File"spgt/env.py",line 123,in<module>
> run_migrations_online()
> File"spgt/env.py",line 117,inrun_migrations_online
> context.run_migrations()
> File"<string>",line 8,inrun_migrations
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/runtime/environment.py",line
> 797,inrun_migrations
> self.get_context().run_migrations(**kw)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/runtime/migration.py",line
> 303,inrun_migrations
> forstep inself._migrations_fn(heads,self):
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/command.py",line
> 97,inretrieve_migrations
> revision_context.run_autogenerate(rev,context)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/autogenerate/api.py",line
> 369,inrun_autogenerate
> self._run_environment(rev,migration_context,True)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/autogenerate/api.py",line
> 405,in_run_environment
> autogen_context,migration_script)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/autogenerate/compare.py",line
> 22,in_populate_migration_script
> _produce_net_changes(autogen_context,upgrade_ops)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/autogenerate/compare.py",line
> 48,in_produce_net_changes
> autogen_context,upgrade_ops,schemas
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/util/langhelpers.py",line
> 314,ingo
> fn(*arg,**kw)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/autogenerate/compare.py",line
> 77,in_autogen_for_tables
> inspector,metadata,upgrade_ops,autogen_context)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/autogenerate/compare.py",line
> 138,in_compare_tables
> inspector.reflecttable(t,None)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/sqlalchemy/engine/reflection.py",line
> 605,inreflecttable
> exclude_columns,reflection_options)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/sqlalchemy/engine/reflection.py",line
> 727,in_reflect_fk
> **reflection_options
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/sqlalchemy/sql/schema.py",line
> 436,in__new__
> metadata._remove_table(name,schema)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py",line
> 60,in__exit__
> compat.reraise(exc_type,exc_value,exc_tb)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/sqlalchemy/util/compat.py",line
> 186,inreraise
> raisevalue
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/sqlalchemy/sql/schema.py",line
> 431,in__new__
> table._init(name,metadata,*args,**kw)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/sqlalchemy/sql/schema.py",line
> 507,in_init
> self._autoload(metadata,autoload_with,include_columns)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/sqlalchemy/sql/schema.py",line
> 519,in_autoload
> self,include_columns,exclude_columns
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/sqlalchemy/engine/base.py",line
> 1528,inrun_callable
> returncallable_(self,*args,**kwargs)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/sqlalchemy/engine/default.py",line
> 364,inreflecttable
> returninsp.reflecttable(table,include_columns,exclude_columns)
>
>
File"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/sqlalchemy/engine/reflection.py",line
> 598,inreflecttable
> raiseexc.NoSuchTableError(table.name <http://table.name>)
> sqlalchemy.exc.NoSuchTableError:readers
> |
>
>
>
>
>
> пятница, 11 ноября 2016 г., 19:48:49 UTC+5 пользователь Mike Bayer
написал:
>
> SQLAlchemy has a case sensitivity behavior that assumes an all
> lowercase
> name to indicate "case insensitive". Firebird and Oracle both
use
> ALL_UPPERCASE to indicate "case insensitive". SQLAlchemy
converts
> between these two.
>
> Therefore if your table shows up in Firebird as SOME_TABLE,
assuming it
> is not a *quoted* name, call upon it in SQLAlchemy as:
>
> # if name is *not* quoted
> t = Table("some_table", m, autoload_with=engine)
>
> if the name is truly the case-sensitive, quoted "SOME_TABLE",
then you
> can manually pass this in as:
>
> # if name *is* quoted
> from sqlalchemy.sql.elements import quoted_name
> t = Table(quoted_name("SOME_TABLE", True), m,
autoload_with=engine)
>
> quoted_name is at
>
http://docs.sqlalchemy.org/en/latest/core/sqlelement.html?highlight=quoted#sqlalchemy.sql.elements.quoted_name
<http://docs.sqlalchemy.org/en/latest/core/sqlelement.html?highlight=quoted#sqlalchemy.sql.elements.quoted_name>
>
<http://docs.sqlalchemy.org/en/latest/core/sqlelement.html?highlight=quoted#sqlalchemy.sql.elements.quoted_name
<http://docs.sqlalchemy.org/en/latest/core/sqlelement.html?highlight=quoted#sqlalchemy.sql.elements.quoted_name>>
>
>
> --
> 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
<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 <javascript:>
> <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> To post to this group, send email to sqlal...@googlegroups.com
<javascript:>
> <mailto:sqlal...@googlegroups.com <javascript:>>.
> Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
