Thank you, now there are no errors and SQLAlchemy works fine but probably alembic do it wrong.
When I created first migration it doesn't detect qouted (case sensitive) tables like "doctor" (https://gist.github.com/uralbash/e83fef54003cef3111d9d4cd18145708#file-alembic-bash) and not quoted tables added twice in migration file (https://gist.github.com/uralbash/e83fef54003cef3111d9d4cd18145708#file-2bcdb50b589a_create_doctors_patients_appointments-py) INFO [alembic.autogenerate.compare] Detected added table 'PATIENTS' INFO [alembic.autogenerate.compare] Detected removed table 'patients' I tested it a few times and create everything from scratch (https://gist.github.com/uralbash/e83fef54003cef3111d9d4cd18145708#file-db_create-sql) but always I get this wrong result среда, 16 ноября 2016 г., 19:30:34 UTC+5 пользователь Mike Bayer написал: > > still trying to get fdb to work but looking at the source it seems like > https://bitbucket.org/zzzeek/sqlalchemy/issues/3548 needs to be ported > to firebird, as the steps taken for Oracle weren't replicated. should > be easy if this is the case. > > > > On 11/16/2016 01:31 AM, uralbash wrote: > > 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:> > <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+...@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. > > For more options, visit 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. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
