Possible error with fully qualified table names

[Benjamin Taub]

schema.py defines a variable fullname for tables as follows:

if self.schema is not None:
    self.fullname = "%s.%s" % (self.schema, self.name)
else:
    self.fullname = self.name

However, this fails for me in some cases, apparently when my table or schema name starts with a number. To address this, shouldn't self.fullname be surrounded by delimiters (e.g. "`%s`.`%s`")?

FWIW, I'm working in MySQL.

Thanks!

Ben

[Mike Bayer]

the .fullname attribute is not used directly in SQL statements, when a schema or table name requires quoting the compiler will apply them as needed.

from sqlalchemy import Column

from sqlalchemy import String

from sqlalchemy.dialects import mysql

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import Session

Base = declarative_base()

class SomeTable(Base):

    __tablename__ = "3table"

    id = Column(String, primary_key=True)

    __table_args__ = {"schema": "4schema"}

s = Session()

print(s.query(SomeTable).statement.compile(dialect=mysql.dialect()))

output:

SELECT `4schema`.`3table`.id

FROM `4schema`.`3table`

--

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/4c572cf3-992a-4527-a2a9-500bc48170e5n%40googlegroups.com.

[Benjamin Taub]

Thank you, Mike. In my case, I am writing a TRUNCATE TABLE statement. I don't think SQLAlchemy provides that so I'm writing it in text and having the engine execute it rather than having SQLAlchemy generate it. Are you recommending, therefore, that I not use the fullname attribute for this?

[Mike Bayer]

you could use "fullname" but you would need to apply string splitting across the dot and then quoting of the individual tokens.

assuming you are using case insensitive identifiers it would be safe to generically do the above for all strings if you wanted:

qualified_name = ".".join(

   "`%s`" % token for token in fullname.split(".")

)

SQLAlchemy has quoting facilities also but they are specific to using the compiler, so if you aren't using the @compiles extension then you would do whatever works to get the quoting you need in your SQL statement.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/22f5f83d-0d53-4922-83e0-24ac9af778acn%40googlegroups.com.

[Benjamin Taub]

Perfect! Thank you, Mike.

Ben

__________________________________________

Benjamin Taub, CEO

Direct: (734) 585-3503

Check out Golden Record, our cloud-based record matching and deduplication solution

Blog: http://www.dataspace.com/insights/blog/

Twitter: twitter.com/dataspaceinc

NOTE: I usually check email only 2 - 3 times / day.  For issues needing immediate attention, please call.

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/PCUvHcDatvM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/6ee23436-8fe5-40d6-b2c0-d1d9545b729a%40www.fastmail.com.