On Sat, May 13, 2023, at 5:12 PM, Benjamin Taub wrote:
> Thank you, Mike, but aren't I using the correct dialect with this
> create_engine() command?
>
> * qry_engine = create_engine('mysql+pymysql://' + db_uid + ':' + db_pw
> + '@' + db_addr, connect_args=connect_args,
> pool_recycle=3600, echo=False,
> future=True)*
>
> Or, am I missing something? (BTW, I'm using core, not ORM).
looks fine. I would need to see a complete example in how you are seeing it
generate quotes. if you try the program I attached previously, you should note
that it generates backticks.
> Thanks again!
> Ben
> On Friday, May 12, 2023 at 4:48:45 PM UTC-4 Mike Bayer wrote:
>>
>>
>> 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 [email protected].
>>> 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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/a11f244c-e91e-4cfa-a093-037a5f19eba9n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/a11f244c-e91e-4cfa-a093-037a5f19eba9n%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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/a885c1d9-30a2-4ee9-89b6-859879adddf1%40app.fastmail.com.
