SQLAlchemy : how can I execute a raw INSERT sql query in a Postgres database?

Question

I’m building an app using Python and the clean architecture principles, with TDD.

Some unit tests require executing some raw SQL queries against an in-memory database.

I am trying to switch from sqlite to postgresql inmemory data, using pytest-postgres.

Problem

When using sqlite inmemory database, I can both insert and select data.
When using Postgresql inmemory database, I can only SELECT (raw INSERT fails).

Insert work in sqlite…

    s_tb_name = "tb_customer"
    ls_cols = ["first_name", "last_name", "email"]
    ls_vals = ['("John", "Doe", "[email protected]")',
               '("Jane", "Doe", "[email protected]")',
               '("Eric", "Dal", "[email protected]")']
    s_cols = ', '.join(ls_cols)
    s_vals = ', '.join(ls_vals)
    session.execute(f"INSERT INTO {s_tb_name} ({s_cols}) VALUES ({s_vals})")

…but fail in Postgres:

E       sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "John" does not exist
E       LINE 1: ..., email) VALUES (("John"....

From this psycopg documentation page, I understand this is due to pyscopg2.
It prevents injecting raw dynamic SQL, and it seems I should add this :

tb_sql_id = sql.Identifier(s_tb_name)
cols_sql_id = sql.SQL(' ,').join(map(sql.Identifier, ls_cols))
vals_sql_id = sql.SQL(' ,').join(map(sql.Literal, ls_vals))
psycopg2_query = sql.SQL(f"INSERT INTO {tb_sql_id} ({cols_sql_id}) VALUES ({vals_sql_id})")

but logically, sqlalchemy refuses to execute the psycopg2_query :

sqlalchemy.exc.ArgumentError: SQL expression object expected, got object of type <class 'psycopg2.sql.SQL'> instead

Question

Is there a way to execute raw dynamic insert queries in Postgres using SQL Alchemy?

Im not aware of the syntax but, you should never use interpolation when doing queries because they open an window for sql injection vulnerability. In psycopg2 you can cursor.execute("select foo from bar where zar = %s and tar = %s", (zar, tar)). sqlalchemy seems to be the same but with session.execute(qry, parmstuple) instead — geckos
– geckos, Commented Sep 17, 2020 at 11:05

Mike Organek · Accepted Answer · 2020-09-17 11:05:43Z

5

I am compelled to warn you about SQL injection, but since this is for your tests that should not be a concern.

Two changes are needed:

The values in ls_vals need to be enclosed in single, rather than double quotes
The extra parens after VALUES need to be removed

    s_tb_name = "tb_customer"
    ls_cols = ["first_name", "last_name", "email"]
    ls_vals = ["('John', 'Doe', '[email protected]')",
               "('Jane', 'Doe', '[email protected]')",
               "('Eric', 'Dal', '[email protected]')"]
    s_cols = ', '.join(ls_cols)
    s_vals = ', '.join(ls_vals)
    session.execute(f"INSERT INTO {s_tb_name} ({s_cols}) VALUES {s_vals}")

answered Sep 17, 2020 at 11:05

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lionel Hamayon Over a year ago

Thank your for pointing me to the right direction. I had to remove parens from ls_vals as well.

Lionel Hamayon · Accepted Answer · 2020-09-17 14:08:59Z

5

As pointed by others, injecting SQL like this is to be avoided in most cases.

Here, the SQL is written in the unit test itself. There is no external input leaking to the SQL injection, which alleviates the security risk.

Mike Organek’s solution did not fully work for me, but it pointed me to the right direction : I just had to also remove the parens from ls_vals.

    s_tb_name = "tb_customer"
    ls_cols = ["first_name", "last_name", "email"]
    ls_vals = ["'John', 'Doe', '[email protected]'",
               "'Jane', 'Doe', '[email protected]'",
               "'Eric', 'Dal', '[email protected]'"]
    s_cols = ', '.join(ls_cols)
    s_vals = '(' + '), ('.join(ls_vals) + ')'
    session.execute(f"INSERT INTO {s_tb_name} ({s_cols}) VALUES {s_vals}")

This made the insert test pass, both when using the sqlite engine and the postgres engine.

answered Sep 17, 2020 at 14:08

Lionel Hamayon

1,4202 gold badges17 silver badges29 bronze badges

2 Comments

Ilja Everilä Over a year ago

Injection is not always intentional. Manually formatting values to SQL is all in all more error prone, compared to just using the library's placeholders. In case of PostgreSQL psycopg2 even offers tools for passing identifiers safely, not to mention SQLAlchemy Core.

Lionel Hamayon Over a year ago

Thank you for your feedback. I failed to mention in my quiestion that I first tried to use SQL Alchemy placeholder, in the form :variable_name, but that gave me the same error as the modified version that I posted.

Collectives™ on Stack Overflow

SQLAlchemy : how can I execute a raw INSERT sql query in a Postgres database?

Problem

Insert work in sqlite…

…but fail in Postgres:

Question

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Problem

Insert work in sqlite…

…but fail in Postgres:

Question

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related