Asyncpg exceptions postgressyntaxerror ошибка синтаксиса примерное положение

Hello!

If you execute a simple parameterized query using AsyncSession, like:

# db_session is instance of AsyncSession
stmt = text('SET local statement_timeout = :value').bindparams(value=500)
await db_session.execute(stmt)

you end up with:

E                   sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.PostgresSyntaxError'>: syntax error at or near "$1"
E                   [SQL: SET local statement_timeout = %s]
E                   [parameters: (500,)]
E                   (Background on this error at: https://sqlalche.me/e/14/f405)

But it perfectly works if you just use an f-string, but using f-string does not sound cool.

I’ve found some explanations from asyncpg side (MagicStack/asyncpg#605 (comment)):

asyncpg does not do query argument interpolation in any form. The query you write gets passed directly to PostgreSQL. This is unlike psycopg2 which has extensive query rewriting/interpolation mechanisms.

Is it maybe fixable on SQLAlchemy side? Like SQLAlchemy passes asyncpg queries with parameter values already placed.

7

jamesdeath123
13 Сен 2019 в 23:53

I’m still learning PostgreSQL. During my testing, I have only been using INSERT statement in either psycopg2 and now asyncpg. I now have the need to UPDATE data in my test database, instead of replacing all of it.

I’m currently trying to do a simple replacement test in a testing table, before I move to development table with more data.

I want to replace any $1 name that is in CONFLICT with a name that is already in the table users. I’m trying the query code, which is passed to the DB via asyncpg. I keep getting a syntax errors, so I’m a little lost on how to correct these errors.

What is the proper syntax for this query?

'''INSERT INTO users(name, dob) 

   VALUES($1, $2)

   ON CONFLICT (name)

   DO 

     UPDATE "users"

     SET name = 'TEST'

     WHERE name = excluded.name '''

UPDATE:

I’m getting this error message when using asyncpg:

asyncpg.exceptions.PostgresSyntaxError: syntax error at or near ""users""

I’m getting this error message when using psycopg2:

psycopg2.ProgrammingError: syntax error at or near ""users""

This is the asyncpg code that I have been using to do the INSERTs:

async def insert_new_records(self, sql_command, data):


    print (sql_command)


    async with asyncpg.create_pool(**DB_CONN_INFO, command_timeout=60) as pool:

        async with pool.acquire() as conn:

            try:

                stmt = await conn.prepare(sql_command)

                async with conn.transaction():

                    for value in data:

                        async for item in stmt.cursor(*value):

                            pass

            finally:

                await pool.release(conn)



test_sql_command = '''

INSERT INTO users(name, dob)

VALUES($1, $2)

ON CONFLICT (name)

DO

  UPDATE "users"

  SET name = 'TEST'

  WHERE name = excluded.name '''


# The name 'HELLO WORLD' exists in the table, but the other name does not.

params = [('HELLO WORLD', datetime.date(1984, 3, 1)),

          ('WORLD HELLO', datetime.date(1984, 3, 1))]


loop = asyncio.get_event_loop()

loop.run_until_complete(db.insert_new_records(test_sql_command, params))

python-3.x postgresql psycopg2 asyncpg

I’m still learning PostgreSQL. During my testing, I have only been using INSERT statement in either psycopg2 and now asyncpg. I now have the need to UPDATE data in my test database, instead of replacing all of it.

I’m currently trying to do a simple replacement test in a testing table, before I move to development table with more data.

I want to replace any $1 name that is in CONFLICT with a name that is already in the table users. I’m trying the query code, which is passed to the DB via asyncpg. I keep getting a syntax errors, so I’m a little lost on how to correct these errors.

What is the proper syntax for this query?

'''INSERT INTO users(name, dob) 

   VALUES($1, $2)

   ON CONFLICT (name)

   DO 

     UPDATE "users"

     SET name = 'TEST'

     WHERE name = excluded.name '''

UPDATE:

I’m getting this error message when using asyncpg:

asyncpg.exceptions.PostgresSyntaxError: syntax error at or near ""users""

I’m getting this error message when using psycopg2:

psycopg2.ProgrammingError: syntax error at or near ""users""

This is the asyncpg code that I have been using to do the INSERTs:

async def insert_new_records(self, sql_command, data):


    print (sql_command)


    async with asyncpg.create_pool(**DB_CONN_INFO, command_timeout=60) as pool:

        async with pool.acquire() as conn:

            try:

                stmt = await conn.prepare(sql_command)

                async with conn.transaction():

                    for value in data:

                        async for item in stmt.cursor(*value):

                            pass

            finally:

                await pool.release(conn)



test_sql_command = '''

INSERT INTO users(name, dob)

VALUES($1, $2)

ON CONFLICT (name)

DO

  UPDATE "users"

  SET name = 'TEST'

  WHERE name = excluded.name '''


# The name 'HELLO WORLD' exists in the table, but the other name does not.

params = [('HELLO WORLD', datetime.date(1984, 3, 1)),

          ('WORLD HELLO', datetime.date(1984, 3, 1))]


loop = asyncio.get_event_loop()

loop.run_until_complete(db.insert_new_records(test_sql_command, params))

python-3.x postgresql psycopg2 asyncpg

I’m still learning PostgreSQL. During my testing, I have only been using INSERT statement in either psycopg2 and now asyncpg. I now have the need to UPDATE data in my test database, instead of replacing all of it.

I’m currently trying to do a simple replacement test in a testing table, before I move to development table with more data.

I want to replace any $1 name that is in CONFLICT with a name that is already in the table users. I’m trying the query code, which is passed to the DB via asyncpg. I keep getting a syntax errors, so I’m a little lost on how to correct these errors.

What is the proper syntax for this query?

'''INSERT INTO users(name, dob) 

   VALUES($1, $2)

   ON CONFLICT (name)

   DO 

     UPDATE "users"

     SET name = 'TEST'

     WHERE name = excluded.name '''

UPDATE:

I’m getting this error message when using asyncpg:

asyncpg.exceptions.PostgresSyntaxError: syntax error at or near ""users""

I’m getting this error message when using psycopg2:

psycopg2.ProgrammingError: syntax error at or near ""users""

This is the asyncpg code that I have been using to do the INSERTs:

async def insert_new_records(self, sql_command, data):


    print (sql_command)


    async with asyncpg.create_pool(**DB_CONN_INFO, command_timeout=60) as pool:

        async with pool.acquire() as conn:

            try:

                stmt = await conn.prepare(sql_command)

                async with conn.transaction():

                    for value in data:

                        async for item in stmt.cursor(*value):

                            pass

            finally:

                await pool.release(conn)



test_sql_command = '''

INSERT INTO users(name, dob)

VALUES($1, $2)

ON CONFLICT (name)

DO

  UPDATE "users"

  SET name = 'TEST'

  WHERE name = excluded.name '''


# The name 'HELLO WORLD' exists in the table, but the other name does not.

params = [('HELLO WORLD', datetime.date(1984, 3, 1)),

          ('WORLD HELLO', datetime.date(1984, 3, 1))]


loop = asyncio.get_event_loop()

loop.run_until_complete(db.insert_new_records(test_sql_command, params))

python-3.x postgresql psycopg2 asyncpg

edited Nov 9 ’18 at 18:40

edited Nov 9 ’18 at 18:40

Life is complexLife is complex

Life is complexLife is complex

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin(‘.new-post-login’, ‘https%3a%2f%2fstackoverflow.com%2fquestions%2f53229783%2fupsert-syntax-error-linked-to-update-in-postgresql-python%23new-answer’, ‘question_page’);
}
);

Post as a guest

You need single quotes around the value for name: SET name='TEST'
The double quotes are for table or column names. In your case, you could just remove the double quotes around users.

After edit:
You should really try your SQL commands in the database console, this has nothing to do with python nor async. It’s pure postgresql syntax.

So, the second issue in your query is that you shouldn’t specify «users» after UPDATE. It’s implied that you’re updating the same table. So just DO UPDATE SET... is good.

Then, you’ll get column reference "name" is ambiguous. You should write DO UPDATE SET name='TEST'. You already are updating the row where name=excluded.name. I am not 100% clear on what you’re trying to do. So if you insert a row once, it’s inserted as usual. If you insert it a second time, the name is replaced with ‘TEST’. The excluded keyword allows you to access the attempted insert values. So for example, if you wanted to update the last_access column when trying to insert an existing name, you would write ON CONFLICT (name) DO UPDATE last_access=excluded.last_access.

You need single quotes around the value for name: SET name='TEST'
The double quotes are for table or column names. In your case, you could just remove the double quotes around users.

After edit:
You should really try your SQL commands in the database console, this has nothing to do with python nor async. It’s pure postgresql syntax.

So, the second issue in your query is that you shouldn’t specify «users» after UPDATE. It’s implied that you’re updating the same table. So just DO UPDATE SET... is good.

Then, you’ll get column reference "name" is ambiguous. You should write DO UPDATE SET name='TEST'. You already are updating the row where name=excluded.name. I am not 100% clear on what you’re trying to do. So if you insert a row once, it’s inserted as usual. If you insert it a second time, the name is replaced with ‘TEST’. The excluded keyword allows you to access the attempted insert values. So for example, if you wanted to update the last_access column when trying to insert an existing name, you would write ON CONFLICT (name) DO UPDATE last_access=excluded.last_access.

You need single quotes around the value for name: SET name='TEST'
The double quotes are for table or column names. In your case, you could just remove the double quotes around users.

After edit:
You should really try your SQL commands in the database console, this has nothing to do with python nor async. It’s pure postgresql syntax.

So, the second issue in your query is that you shouldn’t specify «users» after UPDATE. It’s implied that you’re updating the same table. So just DO UPDATE SET... is good.

Then, you’ll get column reference "name" is ambiguous. You should write DO UPDATE SET name='TEST'. You already are updating the row where name=excluded.name. I am not 100% clear on what you’re trying to do. So if you insert a row once, it’s inserted as usual. If you insert it a second time, the name is replaced with ‘TEST’. The excluded keyword allows you to access the attempted insert values. So for example, if you wanted to update the last_access column when trying to insert an existing name, you would write ON CONFLICT (name) DO UPDATE last_access=excluded.last_access.

edited Nov 10 ’18 at 12:31

answered Nov 9 ’18 at 17:05

you can test
replace :
»’INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE «users»
SET name = ‘TEST’
WHERE name = excluded.name »’

by :
«»»INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE
SET name = ‘TEST’
WHERE name = excluded.name «»»

answered Nov 23 ’18 at 22:51

you can test
replace :
»’INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE «users»
SET name = ‘TEST’
WHERE name = excluded.name »’

by :
«»»INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE
SET name = ‘TEST’
WHERE name = excluded.name «»»

answered Nov 23 ’18 at 22:51

you can test
replace :
»’INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE «users»
SET name = ‘TEST’
WHERE name = excluded.name »’

by :
«»»INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE
SET name = ‘TEST’
WHERE name = excluded.name «»»

answered Nov 23 ’18 at 22:51

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave(‘#login-link’);
});

Sign up using Email and Password

Post as a guest

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave(‘#login-link’);
});

Sign up using Email and Password

Post as a guest

Email

Required, but never shown

20:35 18.06.2022

Во-первых, фетчолл. Во-вторых, это не похоже на тот код, в котором такая ошибка.

20:44 18.06.2022

20:44 18.06.2022

20:45 18.06.2022