Hi, Mike!
I tried your code, and it worked after I set the dialect to mysql+pymysql.
Given this, in my case, I believe the problem stems from the fact that I am
starting with a generic SELECT call that isn't moored to a dialect. I start
with
* sql = select()*
Which I have now changed to (self.tbl is a sqlalchemy table object attached
to MySQL)...
* sql = self.tbl.select()*
The problem now is that I have a null column object with a label that isn't
attached to self.tbl, and I can't figure out how to tell it to generate as
MySQL...
tbl_col = null().label(tmplt_col['name'])
It is tbl_col that eventually gives me the quote problem in SQL generation.
Do you have any ideas for me on how to have this generate in the right
dialect? Am I going about this the wrong way?
Thanks again for your help!
Ben
On Saturday, May 13, 2023 at 11:33:03 PM UTC-4 Mike Bayer wrote:
>
>
> 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/a17b5dbd-2378-481d-98b6-543e27e37752n%40googlegroups.com.
