PostgreSQL Question posted in
The official documentation can be found here.

Sqlalchemy postgresql Reserved key word as column name not able to insert

I have a table with column name "collation" whose schema I cannot change.
I generated some data I want to insert into this table using the following code snippet:

from sqlalchemy.dialects import postgresql

data = [{"collation": "foo"}, {"collation":"bar"}]
stmt = postgresql.insert(table).values(data)
with engine.connect() as conn:
    conn.execute(stmt.compile(dialect=postgresql.dialect()))
    conn.commit()

I get the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "collation"
LINE 1: ... (id, column_name, common_data_type, default_val, collation,...
                                                             ^

I have tried replacing the column name with quoted_string("collation",True) but I still get the same error.
How can I get sqlalchemy to generate the insert query with quotes around the column collation?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This approach, manually constructing the queries works, know that you quote all columns in the insert statement not just keywords.

    data = [{"collation": "foo"}, {"collation":"bar"}]

columns_str = ', '.join(['"'+column.name+'"' for column in columns])
placeholders_str = ', '.join([':' + column.name for column in columns])
insert_statement = text(f'INSERT INTO  {self.schema_name}."{table.name}" ({columns_str}) VALUES ({placeholders_str})')

with engine.connect() as conn:
    conn.execute(insert_statement)
    conn.commit()

  2. 0

    Passing quote=True to Column() seems to work. I’m not sure where your schema is coming from but it seems to work for the ORM class and also with a Table() definition.

    import os

from sqlalchemy import (
    Column,
    String,
    BigInteger,
    create_engine,
    Table,
)
from sqlalchemy.sql import (
    select,
    insert,
)
from sqlalchemy.orm import (
    declarative_base,
    Session,
)


def get_engine(env):
    return create_engine(f"postgresql+psycopg://{env['DB_USER']}:{env['DB_PASSWORD']}@{env['DB_HOST']}:{env['DB_PORT']}/{env['DB_NAME']}", echo=True)

Base = declarative_base()

class Sample(Base):
    __tablename__ = 'samples'
    id = Column(BigInteger, primary_key=True)
    collation = Column(name="collation", type_=String, nullable=False, quote=True)


texts_t = Table(
    "texts",
    Base.metadata,
    Column('id', BigInteger, primary_key=True),
    Column('collation', type_=String, nullable=False, quote=True)
    )

def main():
    engine = get_engine(os.environ)

    with engine.begin() as conn:
        Base.metadata.create_all(conn)

        populate(conn)

        query(conn)


def query(conn):
    with Session(conn) as session:
        for sample in session.scalars(select(Sample)):
            print ('SAMPLE', sample.id, sample.collation)
        for row in session.execute(select(texts_t)):
            print ('TEXTS', row[0], row[1])


def populate(conn):
    with Session(conn) as session:
        session.add(Sample(collation='en_us_ca_morro_bay'))
        session.add(Sample(collation='en_us_ca_santa_margarita'))
        session.execute(insert(texts_t), [{"collation": "en_us_ca_morro_bay_1"}, {"collation": "en_us_ca_santa_margarita_1"}])
        session.commit()


if __name__ == '__main__':
    main()
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search