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]Detectedadded table 'PATIENTS'
INFO [alembic.autogenerate.compare]Detectedremoved 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
<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
<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
<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>
<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>>
>
> >
>
<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>
> <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>
> <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>
> <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
<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.
