SQLAlchemy quoting of table names - Can't redefine 'quote' or 'quote_schema' arguments

[Adam Darwin]

Whilst upgrading from sqlalchemy 0.8 to 1.0.4 my ORM has broken with the error Can't redefine 'quote' or 'quote_schema' arguments

I connect to a sybase db, and use a declarative_base

Base = declarative_base()

Using a standard method to create the mapping below

class RiskAggregationGroup(Base): 
    __tablename__ = 'RISK_AGGREGATION_GROUP' 
    __table_args__ = {'quote':False,'extend_existing':True} 
    id = Column(Integer, name='id_risk_agg', primary_key=True) 
    name = Column(String(50), name='nm_risk_agg') 
    description = Column(String(100), name='tx_desc')

This worked fine in sqlalchemy 0.8 but breaks in 1.0.4 as it doesn't like me specifying quote as a table arg. I've tried a whole host of things to get around this, setting it in the base, e.g.

class Base(object): 
    __table_args__ = {'quote':False,'extend_existing':True} 

Base = declarative_base(cls=Base)

throws the same error. If I change it to use the @declared_attr the quoting is not turned off. I'm unable to change the sybase settings and my table names are all caps (which is the cause of the quoting). I've got about 20 tables defined here, so am loathe to change them all to Table creations, such as:

class RiskAggregationGroup(Base): 
    __tablename__ = 'RISK_AGGREGATION_GROUP'
    __table__ = Table(__tablename__, Base.metadata, 
    Column(Integer, name='id_risk_agg', primary_key=True, key='id'), 
    Column(String(50), name='nm_risk_agg', key='name'), quote=False)

Does anyone have a more elegant solution, so far google has failed me?

[Mike Bayer]

   

On 5/27/15 10:34 PM, Adam Darwin wrote:

Whilst upgrading from sqlalchemy 0.8 to 1.0.4 my ORM has broken with the error Can't redefine 'quote' or 'quote_schema' arguments

I connect to a sybase db, and use a declarative_base

Base = declarative_base()

Using a standard method to create the mapping below

class RiskAggregationGroup(Base): 
    __tablename__ = 'RISK_AGGREGATION_GROUP' 
    __table_args__ = {'quote':False,'extend_existing':True} 
    id = Column(Integer, name='id_risk_agg', primary_key=True) 
    name = Column(String(50), name='nm_risk_agg') 
    description = Column(String(100), name='tx_desc')

This worked fine in sqlalchemy 0.8 but breaks in 1.0.4 as it doesn't like me specifying quote as a table arg. I've tried a whole host of things to get around this, setting it in the base, e.g.

The message you are getting refers to when the table metadata has already been reflected, and the correct identifiers have already been loaded from the database.  This is not illustrated here but it seems likely that you are also running a reflection step before you create this declarative base; no such error is emitted otherwise.  I would need clarification on that.

throws the same error. If I change it to use the @declared_attr the quoting is not turned off. I'm unable to change the sybase settings and my table names are all caps (which is the cause of the quoting). I've got about 20 tables defined here, so am loathe to change them all to Table creations, such as:

If your table is named in ALL_CAPS (on the database side) and Sybase is considering this in a case-sensitive manner, then you need the quotes.   Quoting means, "this name is in exactly this case", so if your statement "my table names are all caps (on the database side, right?)" is true, then you need the quoting.

If you mean "my table names are all caps" on the *Python* side, but they are case insensitive on the Sybase side, then the code is wrong.  Change all the identifier names to be lower case.

class RiskAggregationGroup(Base): 
    __tablename__ = 'RISK_AGGREGATION_GROUP'
    __table__ = Table(__tablename__, Base.metadata, 
    Column(Integer, name='id_risk_agg', primary_key=True, key='id'), 
    Column(String(50), name='nm_risk_agg', key='name'), quote=False)

Does anyone have a more elegant solution, so far google has failed me?

--
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.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Adam Darwin]

Thanks for the response Michael as far as I can tell there is no reflective step in created the declarative_base the code is as posted.

My table names in sybase are uppercase, if i try lower case it fails:

ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Sybase][ODBC Driver][Adaptive Server Enterprise]risk_aggregation_group not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).\n (208) (SQLExecDirectW)') [SQL: u'SELECT risk_aggregation_group.id_risk_agg AS risk_aggregation_group_i_1, risk_aggregation_group.nm_risk_agg AS risk_aggregation_group_n_2, risk_aggregation_group.tx_desc AS risk_aggregation_group_t_3, risk_aggregation_group.saves_pl AS risk_aggregation_group_s_4, risk_aggregation_group.id_market_making AS risk_aggregation_group_i_5, risk_aggregation_group.id_bond_trading AS risk_aggregation_group_i_6, risk_aggregation_group.id_trader AS risk_aggregation_group_i_7 \nFROM risk_aggregation_group \nWHERE risk_aggregation_group.id_market_making = 1']

If I try enabling quotes it fails:

ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Sybase][ODBC Driver][Adaptive Server Enterprise]Incorrect syntax near 'RISK_AGGREGATION_GROUP.'.\n (102) (SQLExecDirectW)") [SQL: u'SELECT "RISK_AGGREGATION_GROUP".id_risk_agg AS "RISK_AGGREGATION_GROUP_i_1", "RISK_AGGREGATION_GROUP".nm_risk_agg AS "RISK_AGGREGATION_GROUP_n_2", "RISK_AGGREGATION_GROUP".tx_desc AS "RISK_AGGREGATION_GROUP_t_3", "RISK_AGGREGATION_GROUP".saves_pl AS "RISK_AGGREGATION_GROUP_s_4", "RISK_AGGREGATION_GROUP".id_market_making AS "RISK_AGGREGATION_GROUP_i_5", "RISK_AGGREGATION_GROUP".id_bond_trading AS "RISK_AGGREGATION_GROUP_i_6", "RISK_AGGREGATION_GROUP".id_trader AS "RISK_AGGREGATION_GROUP_i_7" \nFROM "RISK_AGGREGATION_GROUP" \nWHERE "RISK_AGGREGATION_GROUP".id_market_making = 1']

I'm struggling to understand the code in sqlalchemy, but from what I can guess the table gets created and then _init_existing is called with the __table_args__ (although I can't find where). 

Would I be correct in saying that what you're expecting is that in _setup_table the table should not already exist in the dict?  I've no idea why it would

[Mike Bayer]

On 5/28/15 7:13 PM, Adam Darwin wrote:

Thanks for the response Michael as far as I can tell there is no reflective step in created the declarative_base the code is as posted.

OK then you do not need the "extend_existing" flag, below is a test case which illustrates that even with this flag, the quote flag is honored, as long as nothing else in the program already made a Table object with that same name on the same MetaData.  If you can modify this test to show what you're doing, that will help me to understand where to go with this.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import CreateTable

Base = declarative_base()

class RiskAggregationGroup(Base):
    __tablename__ = 'RISK_AGGREGATION_GROUP'
    __table_args__ = {'quote': False, 'extend_existing': True}
    id = Column(Integer, name='id_risk_agg', primary_key=True)
    name = Column(String(50), name='nm_risk_agg')
    description = Column(String(100), name='tx_desc')

e = create_engine("sybase://")


# I don't have a sybase database handy, so here's a print
print(CreateTable(RiskAggregationGroup.__table__).compile(e))

If I try enabling quotes it fails:

ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Sybase][ODBC Driver][Adaptive Server Enterprise]Incorrect syntax near 'RISK_AGGREGATION_GROUP.'.\n (102) (SQLExecDirectW)") [SQL: u'SELECT "RISK_AGGREGATION_GROUP".id_risk_agg AS "RISK_AGGREGATION_GROUP_i_1", "RISK_AGGREGATION_GROUP".nm_risk_agg AS "RISK_AGGREGATION_GROUP_n_2", "RISK_AGGREGATION_GROUP".tx_desc AS "RISK_AGGREGATION_GROUP_t_3", "RISK_AGGREGATION_GROUP".saves_pl AS "RISK_AGGREGATION_GROUP_s_4", "RISK_AGGREGATION_GROUP".id_market_making AS "RISK_AGGREGATION_GROUP_i_5", "RISK_AGGREGATION_GROUP".id_bond_trading AS "RISK_AGGREGATION_GROUP_i_6", "RISK_AGGREGATION_GROUP".id_trader AS "RISK_AGGREGATION_GROUP_i_7" \nFROM "RISK_AGGREGATION_GROUP" \nWHERE "RISK_AGGREGATION_GROUP".id_market_making = 1']

that suggests we might be using the wrong quoting character on sybase; it's not failing to locate the table, it's raising a syntax error.   not really sure, I don't have a sybase handy to test with.   It's possible this quoting character should be a bracket.  You can set that like this:

e = create_engine("sybase://")

# if not using quote=False, this will change the quoting character
e.dialect.identifier_preparer.initial_quote = '['
e.dialect.identifier_preparer.final_quote = ']'


if that confirms the issue, file a bug because we should fix that.

[Adam Darwin]

Nailed it, removing quote;False and setting the quoting characters to [] did the job.  Huge thanks for the help,