File "/appli/ostic/venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line…
- User
File "/appli/ostic/venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1379, in execute meth = statement._execute_on_connection AttributeError: 'Composed' object has no attribute 'execute_on_connection'query = sql.SQL("""CREATE TABLE IF NOT EXISTS "CS{}" AS SELECT DISTINCT "NUMEN" FROM public WHERE "NUMEN" IS NOT NULL AND {} = %s""").format(sql.Identifier(nom_os), sql.Identifier(" rg")) connection.execute(query, (rg,))
- Assistant
From your description, it looks like you're encountering an issue with the execution of an SQL statement using SQLAlchemy in Python 3.6. Your attempt involves creating a table based on a select query with conditions and you're using the
psycopg2SQL module (sql.SQL,sql.Identifier) to construct the query, intending to ensure SQL injection safety by properly escaping identifiers and literals.The error you're encountering:
AttributeError: 'Composed' object has no attribute '_execute_on_connection'is due to the way you're trying to execute a dynamically composed SQL query using SQLAlchemy's
executemethod. Theexecutemethod expects a string query or an SQLAlchemy expression language statement, but gets aComposedobject, resulting in the AttributeError.Let's try to refactor your code snippet to make it work correctly. We will convert the
Composedobject into a string then pass it along with the parameters separately to theexecutemethod. Since you're using PostgreSQL (as indicated by usingpsycopg2), SQLAlchemy's text function can be used to safely create a query string from aComposedobject.Firstly, ensure you have the necessary imports:
from sqlalchemy import text from psycopg2 import sqlNow, let's address the issue by rendering the SQL string safely:
# Assuming nom_os and rg are properly defined somewhere in your code nom_os = 'your_table_suffix' rg = 'your_rg_value' # Here, we use psycopg2's SQL module to create SQL identifiers safely query = sql.SQL("""CREATE TABLE IF NOT EXISTS "CS_{}" AS SELECT DISTINCT "NUMEN" FROM public WHERE "NUMEN" IS NOT NULL AND {} = %s""").format(sql.Identifier(nom_os), sql.Identifier("rg")) # Convert the query and parameters to strings that can be executed by SQLAlchemy query_str = query.as_string(connection) # Assuming 'connection' is an SQLAlchemy connection # Execute your query connection.execute(text(query_str), [rg])Note: The connection object in
query.as_string(connection)needs to be an object that has a.dialectattribute, typically an Engine or Connection object from SQLAlchemy, not a raw DB-API connection.However, the snippet above tries to render the query using the connection's dialect directly which might not work smoothly in all scenarios due to differences in handling parameters between SQLAlchemy and psycopg2's
as_stringmethod.A safer alternative is composing the query directly without relying on psycopg2’s
as_string():# Directly passing the query parameters without using psycopg2's as_string() query_str = f"""CREATE TABLE IF NOT EXISTS "CS_{nom_os}" AS SELECT DISTINCT "NUMEN" FROM public WHERE "NUMEN" IS NOT NULL AND "rg" = :rg""" # Now using SQLAlchemy's `text` to create a SQL expression connection.execute(text(query_str), {'rg': rg})Ensure you replace placeholders (
nom_os,rg) with actual values or variables from your application context. The revised approach constructs the query directly using string formatting for table names (which should be handled with care to avoid SQL injection) and uses bind parameters for values, leveraging SQLAlchemy's text() function to securely prepare and execute the query.