Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0125-preview· openaiHugging Face Suzhen/CodeChat

File "/appli/ostic/venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line…

  1. 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,))

  2. 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 psycopg2 SQL 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 execute method. The execute method expects a string query or an SQLAlchemy expression language statement, but gets a Composed object, resulting in the AttributeError.

    Let's try to refactor your code snippet to make it work correctly. We will convert the Composed object into a string then pass it along with the parameters separately to the execute method. Since you're using PostgreSQL (as indicated by using psycopg2), SQLAlchemy's text function can be used to safely create a query string from a Composed object.

    Firstly, ensure you have the necessary imports:

    from sqlalchemy import text
    from psycopg2 import sql
    

    Now, 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 .dialect attribute, 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_string method.

    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.