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 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/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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/a885c1d9-30a2-4ee9-89b6-859879adddf1%40app.fastmail.com.