Hey, Mike!
I just wanted to let you know I figured this out. Your sample code led me
to something I hadn't considered. After the routine I was stuck in, I take
the SQLAlchemy-generated code, turn it into a string, and add some
manually-generated SQL. I used a command like *str(SQL)* to get this
string. I am pretty sure that this worked in v1.4. However, it turns out
that I now have to use *str(sql.compile(common.qry_engine))*.
I'm not sure if this jives with the behavior you would expect, but it seems
to be working. Please let me know if you have any questions.
Thank you so much for your help!
Ben
On Sunday, May 14, 2023 at 11:26:27 PM UTC-4 Mike Bayer wrote:
>
>
> On Sun, May 14, 2023, at 12:39 PM, Benjamin Taub wrote:
>
> 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()*
>
>
> Modern SQLAlchemy SQL constructs like select() have no connection to any
> dialect. Only if you are using 1.x and using the very dated "bound
> metadata" concept which is now removed, is there any truth to this. But
> any SQL construct can be compiled with any dialect at all times (Assuming
> it contains no dialect-specific constructs).
>
>
> 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?
>
>
> I would need to see an example of what you're doing as this does not
> really indicate why there would be any problem. as mentioned previously,
> any SQL construct when compiled is always given a dialect with which to
> compile with, and you can always pass this in.
>
> it's still not clear here if you are using .compile() directly or not as I
> dont really have an understanding of what you're doing.
>
>
>
> 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 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+...@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+...@googlegroups.com.
>
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/a17b5dbd-2378-481d-98b6-543e27e37752n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/a17b5dbd-2378-481d-98b6-543e27e37752n%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/16e1c15c-790d-40d9-9fbe-20fca5b25fcdn%40googlegroups.com.
