On Mon, May 15, 2023, at 1:28 PM, Benjamin Taub wrote: > 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.
yes, it was fairly clear you were likely doing something like this. > > > > I am pretty sure that this worked in v1.4. in only "worked" out of luck, that the generic SQL happened to match what MySQL wanted. There are many points at which this would fail with SQL constructs that have some idiosyncratic rendering in MySQL. That said, it's not exactly familiar to me how identifiers that start with numbers started getting quoted for all dialects; there was definitely something there regarding the Oracle backend but I dont know offhand if/how that got generalized. > > > > However, it turns out that I now have to use > *str(sql.compile(common.qry_engine))*. this is definitely the way to do this for stringifying SQL, as every backend has lots of idiosyncracies in its SQL format. now there is a bigger issue which is that it's not the best way to add text to SQL by running str() on it, when you run str(stmt) and then just run the text, a whole lot of valuable internal data is lost when you execute the statement which helps with things like applying the correct types to result rows, fetching ORM objects, etc. The str() process is also not that cheap as there is no caching used, running statements directly allows the engine to take advantage of SQL caching. There's other ways to add literal strings inside of SQL, with two general categories of injection of strings as the object is constructed, and mutation of the final compiled SQL string as it is executed. depending on what you are doing, one or the other approach is appropriate and will retain all the advantages of compiled execution and caching. > -- 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/aa9d5fc2-fedf-46b0-95c5-ee58dc2372c0%40app.fastmail.com.
