On Fri, May 12, 2023, at 4:30 PM, Benjamin Taub wrote:
> I have code that worked under SQLAlchemy 1.4 but recently upgraded to 2. I am
> using the add_columns() method to add columns to an existing SQL statement.
> The resultant queries sometimes, but not always, crash. I believe the issue
> happens when the schema/database name (I'm using MySQL) starts with a number.
> When the schema name starts with a letter, the result runs fine. However,
> when it starts with a number, the query double-quotes the schema name,
> causing the query to crash.
>
> Here is an example...
> My code: *sql = sql.add_columns(self.tbl.c[field])*
>
> When the schema holding self.tbl.c[field] starts with a letter
> (c6961a19b7ed031ce902f056c725b3e3), the following SQL is generated:
> *SELECT NULL AS "Application Id", NULL AS "First Name", NULL AS "Last Name",
> NULL AS "Email",
> c6961a19b7ed031ce902f056c725b3e3.t_31392eb2e6980f4d5082b7861182f2b4.master_key
>
> FROM c6961a19b7ed031ce902f056c725b3e3.t_31392eb2e6980f4d5082b7861182f2b4*
>
> However, when the schema name starts with a number
> (283ac7717fe770c5ed6d425c0c739cba), the following SQL results:
> *SELECT NULL AS "Application Id", NULL AS "First Name", NULL AS "Last Name",
> NULL AS "Email",
> "283ac7717fe770c5ed6d425c0c739cba".t_59a33cbea3617986d810e9fbae60ba19.master_key
>
> FROM "283ac7717fe770c5ed6d425c0c739cba".t_59a33cbea3617986d810e9fbae60ba19*
>
> Note the double quotes around the schema name. This second SQL crashes as
> invalid. Back quotes (`) would probably work fine in this situation, and
> could be helpful, but double quotes (") are, I think, the cause of my problem.
>
> Is there some parameter or assumption that I'm not understanding, or did I
> find a bug?
The quoting, if it were the correct quoting format, should be fine. As to why
it's the quote char and not the backtick, are you compiling these queries
manually? You would want to make sure a MySQL dialect is in use, which would
be using backticks for quoting, unless that dialect were initialized against a
MySQL database that has ANSI_QUOTES set.
TL;DR quoting is a new thing here but SQLAlchemy should render the correct
quotes when used with the MySQL dialect.
Here's a demo:
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy import select
from sqlalchemy import String
from sqlalchemy.orm import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 't_59a33cbea3617986d810e9fbae60ba19'
__table_args__ = {
"schema": "283ac7717fe770c5ed6d425c0c739cba"
}
id = Column(Integer, primary_key=True)
data = Column(String)
e = create_engine("mysql://")
stmt = select(A)
print(stmt.compile(e))
output:
SELECT
`283ac7717fe770c5ed6d425c0c739cba`.t_59a33cbea3617986d810e9fbae60ba19.id,
`283ac7717fe770c5ed6d425c0c739cba`.t_59a33cbea3617986d810e9fbae60ba19.data
FROM `283ac7717fe770c5ed6d425c0c739cba`.t_59a33cbea3617986d810e9fbae60ba19
>
> Thank you!
> Ben
>
>
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/76d38390-e17f-4b90-a438-ee078944b5ffn%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/76d38390-e17f-4b90-a438-ee078944b5ffn%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/a50db81a-5271-4f3e-81c4-d4f02cc38732%40app.fastmail.com.
