I use quoted_name to describe the table schema (declarative method) in my
project like this:
class People(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__ import with_statement
# standard library
from logging.config import fileConfig
# SQLAlchemy
import sqlalchemy
from sqlalchemy import Table, MetaData, pool, engine_from_config
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.sql.elements import quoted_name
from sqlalchemy.ext.declarative import declarative_base
# third-party
from alembic import context
from alembic.ddl.impl import DefaultImpl
class FirebirdImpl(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() for x in q.fetchall()]
create_done = 0
while (not create_done):
create_done = 1
for table in tables:
print(table)
if table.isupper():
try:
Table(table, metadata, autoload_with=engine)
except sqlalchemy.exc.NoSuchTableError as e:
create_done = 0
continue
try:
_table = Table(quoted_name(table, True), metadata,
autoload_with=engine)
except sqlalchemy.exc.NoSuchTableError as e:
create_done = 0
print(metadata.tables)
print(metadata.tables.keys())
# 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.
def run_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
)
with context.begin_transaction():
context.run_migrations()
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
with engine.connect() as connection:
context.configure(
connection=connection,
target_metadata=target_metadata
)
with context.begin_transaction():
context.run_migrations()
if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()
It seems now the tables are created properly but alembic still raise
exception by table names
INFO [alembic.runtime.migration] Context impl FirebirdImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.autogenerate.compare] Detected added table 'COMPANIES'
INFO [alembic.autogenerate.compare] Detected added table 'CARD'
INFO [alembic.autogenerate.compare] Detected added table 'LOCATIONS'
INFO [alembic.autogenerate.compare] Detected added 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, in main
CommandLine(prog=prog).main(argv=argv)
File
"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/config.py"
, line 473, in main
self.run_cmd(cfg, options)
File
"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/config.py"
, line 456, in run_cmd
**dict((k, getattr(options, k)) for k in kwarg)
File
"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/command.py"
, line 117, in revision
script_directory.run_env()
File
"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/script/base.py"
, line 407, in run_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, in load_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, in load_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, in load_module
File "<frozen importlib._bootstrap_external>", line 668, in load_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, in exec_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, in run_migrations_online
context.run_migrations()
File "<string>", line 8, in run_migrations
File
"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/runtime/environment.py"
, line 797, in run_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, in run_migrations
for step in self._migrations_fn(heads, self):
File
"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/alembic/command.py"
, line 97, in retrieve_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, in run_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, in go
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, in reflecttable
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, in reraise
raise value
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, in run_callable
return callable_(self, *args, **kwargs)
File
"/home/uralbash/Projects/_tmp/_NotAliens/.sacrud_env/lib/python3.5/site-packages/sqlalchemy/engine/default.py"
, line 364, in reflecttable
return insp.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, in reflecttable
raise exc.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
>
>
>
--
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.
