For what it's worth, I believe this double quoting issue is also happening
with the .label() method of the column object (assuming that I do have the
dialect appropriately set). The following code...
*tbl_col = self.tbl.c[formula_col['table_column']].label(tmplt_col['name'])*
Results in the following result in the Pycharm debugger (notice the double
quotes)...
*'s_42331a8d8a8f3ca73660cc078731dc72.t_ec37b6f3271c0f97429d5301a41be3da."ApplicationId"'*
and then running...
*sql = sql.add_columns(tbl_col)*
Results in the following...
*SELECT
s_42331a8d8a8f3ca73660cc078731dc72.t_ec37b6f3271c0f97429d5301a41be3da."ApplicationId"
AS "Application Id" FROM
s_42331a8d8a8f3ca73660cc078731dc72.t_ec37b6f3271c0f97429d5301a41be3da*
Any suggestions? Thanks for your help!
Ben
On Saturday, May 13, 2023 at 5:12:11 PM UTC-4 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).
> 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 sqlalchemy+...@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/31836dbd-bb43-4c33-ab95-46342dbc852an%40googlegroups.com.
