I’m converting a postgres script into asyncpg.
im getting «asyncpg.exceptions.PostgresSyntaxError: syntax error at or near «%»»
i assume my placeholder format is incorrect but i cant find an example of a correct format.
Original working psycopg2 code:
async def commit_trade_postgres(response_data_input):
conn = await psycopg2.connect(
"dbname='postgres' user='postgres' password = 'postgres123' host='localhost' port= '5432'")
cur = conn.cursor()
cur.execute(
"CREATE TABLE IF NOT EXISTS trade_{symbol} (time timestamptz NOT NULL ,side text, size float, price float, tick_direction text)".format(**response_data_input))
conn.commit()
cur.execute(
"SELECT create_hypertable('trade_{symbol}', 'time', if_not_exists => TRUE)".format(**response_data_input))
conn.commit()
cur.execute("INSERT INTO trade_{symbol} (time, side, size, price, tick_direction) VALUES (now(), %(side)s, %(size)s, %(price)s, %(tick_direction)s)".format(
**response_data_input), (response_data_input))
conn.commit()
print("commited trade")
My attempt as per the example code supplied int he docs:
async def commit_trade_postgres(response_data_input):
conn = await asyncpg.connect(database='postgres', user='postgres', password='postgres123', host='localhost', port='5432')
await conn.execute(
"CREATE TABLE IF NOT EXISTS trade_{symbol} (time timestamptz NOT NULL ,side text, size float, price float, tick_direction text)".format(**response_data_input))
await conn.execute(
"SELECT create_hypertable('trade_{symbol}', 'time', if_not_exists => TRUE)".format(**response_data_input))
await conn.execute("INSERT INTO trade_{symbol} (time, side, size, price, tick_direction) VALUES (now(), %(side)s, %(size)s, %(price)s, %(tick_direction)s)".format(
**response_data_input), (response_data_input))
print("commited trade")
EDIT: Sample Query, Which i’m extracting ‘data’ as a dict.
response_dict_instrument = {'topic': 'instrument.BTCUSD', 'data': [{'symbol': 'BTCUSD', 'mark_price': 12367.29, 'index_price': 12360.1}]}
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.
Hi,
The same error happens if you run the same query with asyncpg:
>>> async def go(): ... conn = await asyncpg.connect('postgresql://scott:tiger@localhost:5432/test') ... await conn.execute('SET local statement_timeout = $1', 1000) >>> asyncio.run(go()) Traceback (most recent call last): ... File "asyncpgprotocolprotocol.pyx", line 168, in prepare asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "$1"
The issue here seems of asyncpg and in part of postgresql that does not accept parameters in that query. My suggestion is to ask for advice in the asyncpg repository.
Is it maybe fixable on SQLAlchemy side? Like SQLAlchemy passes asyncpg queries with parameter values already placed.
No, this is not something SQLAlchemy does, and in general not something we are interested in doing, since it has a lot of security implications
3 replies
Thanks for suggestions.
My suggestion is to ask for advice in the asyncpg repository.
Somebody already asked some time ago -> MagicStack/asyncpg#605
and unfortunately their answer is:
This is outside of scope and can be solved by a wrapper library.
Somebody already asked some time ago -> MagicStack/asyncpg#605
It was for a different case ,a create table compared to a set, but I guess the answer should be similar also for this query.
Я все еще изучаю PostgreSQL. Во время тестирования я использовал оператор INSERT только в psycopg2, а теперь и в asyncpg. Теперь мне нужно ОБНОВЛЯТЬ данные в моей тестовой базе данных вместо того, чтобы заменять их все.
В настоящее время я пытаюсь выполнить простой тест замены в таблице тестирования, прежде чем перейти к таблице разработки с дополнительными данными.
Я хочу заменить любое имя $ 1, находящееся в CONFLICT, на имя, которое уже есть в таблице users. Я пробую код запроса, который передается в БД через asyncpg. Я продолжаю получать синтаксические ошибки, поэтому я немного не понимаю, как исправить эти ошибки.
Каков правильный синтаксис этого запроса?
'''INSERT INTO users(name, dob)
VALUES($1, $2)
ON CONFLICT (name)
DO
UPDATE "users"
SET name = 'TEST'
WHERE name = excluded.name '''
Обновлено:
Я получаю это сообщение об ошибке при использовании asyncpg:
asyncpg.exceptions.PostgresSyntaxError: syntax error at or near ""users""
Я получаю это сообщение об ошибке при использовании psycopg2:
psycopg2.ProgrammingError: syntax error at or near ""users""
Это код asyncpg, который я использовал для вставки INSERT:
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))
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
Life is complexLife is complex
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
Life is complexLife is complex
1
1
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
Life is complexLife is complex
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
python-3.x postgresql psycopg2 asyncpg
edited Nov 9 ’18 at 18:40
Life is complexLife is complex
edited Nov 9 ’18 at 18:40
Life is complexLife is complex
edited Nov 9 ’18 at 18:40
edited Nov 9 ’18 at 18:40
edited Nov 9 ’18 at 18:40
Life is complexLife is complex
Life is complexLife is complex
Life is complexLife is complex
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
Your Answer
StackExchange.ifUsing(«editor», function () {
StackExchange.using(«externalEditor», function () {
StackExchange.using(«snippets», function () {
StackExchange.snippets.init();
});
});
}, «code-snippets»);
StackExchange.ready(function() {
var channelOptions = {
tags: «».split(» «),
id: «1»
};
initTagRenderer(«».split(» «), «».split(» «), channelOptions);
StackExchange.using(«externalEditor», function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using(«snippets», function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: ‘answer’,
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: «»,
imageUploader: {
brandingHtml: «Powered by u003ca class=»icon-imgur-white» href=»https://imgur.com/»u003eu003c/au003e»,
contentPolicyHtml: «User contributions licensed under u003ca href=»https://creativecommons.org/licenses/by-sa/3.0/»u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href=»https://stackoverflow.com/legal/content-policy»u003e(content policy)u003c/au003e»,
allowUrls: true
},
onDemand: true,
discardSelector: «.discard-answer»
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave(‘#login-link’);
});
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Required, but never shown
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 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
2
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 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
edited Nov 10 ’18 at 12:31
edited Nov 10 ’18 at 12:31
edited Nov 10 ’18 at 12:31
answered Nov 9 ’18 at 17:05
answered Nov 9 ’18 at 17:05
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
0
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
answered Nov 23 ’18 at 22:51
answered Nov 23 ’18 at 22:51
answered Nov 23 ’18 at 22:51
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave(‘#login-link’);
});
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave(‘#login-link’);
});
Sign up using Email and Password
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
В моем коде Python, использующем asyncpg, я передаю кортеж (‘PENDING’,) в запрос where-in, который регистрируется как:
args=('TYPE_1', ('PENDING',))
query=SELECT * FROM actions where type = $1 AND status IN $2
Похоже, что SQL-запрос, наконец, должен быть
SELECT * FROM actions where type = TYPE_1 AND status in ('PENDING',);
Но приведенный выше код приводит к:
asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "$2"
Я думаю, что это, вероятно, из-за запятой в кортеже, но я не знаю, как от нее избавиться ..
1 ответ
В случае, если это может помочь другим людям. В asyncpg WHERE IN больше не поддерживается. Правильный способ — использовать ANY, поэтому при генерации SQL-кодов это должно быть:
WHERE type = $1 AND status = ANY($2::text[])
Где $ 2 — обычный список питонов. Глядя на args, теперь это:
('TYPE_1', ['PENDING'])
7
jamesdeath123
13 Сен 2019 в 23:53
Ошибка синтаксиса ошибки postgres в или около «int» при создании функции
Я новичок в postgres. Я получил эту ошибку при попытке запустить следующий скрипт:
CREATE OR REPLACE FUNCTION xyz(text) RETURNS INTEGER AS
'DECLARE result int;
BEGIN
SELECT count(*) into result from tbldealercommissions
WHERE
txtdealercode = $1;
if result < 1 then returns 1;
else returns 2 ;
end if;
END;
'
LANGUAGE sql VOLATILE;
Ошибка
ERROR: syntax error at or near "int"
LINE 3: 'DECLARE result int;
не уверен, что вызывает эту ошибку. Любая помощь приветствуется.
2 ответы
Это не подходит:
LANGUAGE sql
используйте это вместо:
LANGUAGE plpgsql
Синтаксис, который вы пытаетесь использовать, — это не чистый язык SQL, а процедурный язык PL / pgSQL. В PostgreSQL вы можете устанавливать разные языки, и PL / pgSQL является в этом отношении только первыми. Это также означает, что вы можете получить сообщение об ошибке, что этот язык не установлен. В этом случае используйте
CREATE LANGUAGE plpgsql;
который его активизирует. В зависимости от версии PostgreSQL для выполнения этого шага вам могут потребоваться права суперпользователя.
Удачи.
ответ дан 06 окт ’11, 14:10
Вы не только используете неправильный язык (как отмечает AH), но и returns ключевое слово, вы хотите return. Возможно, вы захотите использовать другой разделитель, чтобы избежать проблем со строковыми литералами в ваших функциях, $$ довольно часто. Я думаю, ваша функция должна выглядеть примерно так:
CREATE OR REPLACE FUNCTION xyz(text) RETURNS INTEGER AS $$
DECLARE result int;
BEGIN
select count(*) into result
from tbldealercommissions
where txtdealercode = $1;
if result < 1 then return 1;
else return 2;
end if;
END;
$$ LANGUAGE plpgsql VOLATILE;
ответ дан 06 окт ’11, 18:10
Не тот ответ, который вы ищете? Просмотрите другие вопросы с метками
postgresql
or задайте свой вопрос.