Re: [sqlalchemy] How to reflect database with auto quote_name of __tablename__ ?

Mon, 14 Nov 2016 07:20:06 -0800 


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)
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>


--
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.


--

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.






















					Reply via email to