postgresql 将日期时间传递给 psycopg2
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43881999/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Passing a datetime into psycopg2
提问by Steve Scott
I am trying to insert a datetime value into my postgres database using Psycopg2.
我正在尝试使用 Psycopg2 将日期时间值插入到我的 postgres 数据库中。
My code was working before, but I switched from %s notation to {} notation, and my code broke.
我的代码以前可以工作，但我从 %s 表示法切换到 {} 表示法，我的代码坏了。
here is my code
这是我的代码
for j in eveLists.itemList:
tempPrice = fetchSellPrice(i, j)
database_name = eveLists.DatabaseDict[i]
now = datetime.datetime.utcnow()
cur.execute("INSERT INTO {0} VALUES ({1}, {2}, {3}, NULL, {4}, {5}, NULL);".format(
database_name,
str(i),
str(j),
float(tempPrice),
datetime.date.today(),
now))
I get the following error:
我收到以下错误：
psycopg2.ProgrammingError: syntax error at or near "00"
LINE 1: ...0000142, 2268, 3.11, NULL, 2017-05-09, 2017-05-10 00:40:03.3...
^
It is treating the date and the time as two separate objects.
它将日期和时间视为两个单独的对象。
I have tried several different ways which are commented out, and all throw various error messages. I have tried wrapping the datetime in quotes,
我尝试了几种不同的注释方式，并且都抛出了各种错误消息。我试过用引号包裹日期时间，
now = ("'%s'") % str(datetime.datetime.utcnow())
which give an error
这给出了一个错误
psycopg2.ProgrammingError: column "mydate" is of type date but expression is of type integer
LINE 1: ...NTO temp_jita VALUES (30000142, 2268, 3.11, NULL, 2017-05-09...
^
HINT: You will need to rewrite or cast the expression.
It thinks my date is an integer, even though I wrapped it in quotes.
它认为我的日期是一个整数，即使我用引号将它括起来。
I have also tried making a psycopg2 timestamp manually:
我也尝试过手动制作 psycopg2 时间戳：
now = psycopg2.Timestamp(now.strftime("%Y"),
now.strftime("%m"),
now.strftime("%d"),
now.strftime("%h"),
now.strftime("%M"),
int(now.strftime("%-S")))
Which gives the following error:
这给出了以下错误：
int(now.strftime("%-S")))
TypeError: an integer is required (got type str)
I don't know how it thinks this is a string, especially since I cast it as an int!
我不知道它是如何认为这是一个字符串的，尤其是因为我将它转换为 int！
Any help would be appreciated.
任何帮助，将不胜感激。
EDIT: The reason I passed the variables using {} notation and not %s notation is because I received an error for the following code:
编辑：我使用 {} 表示法而不是 %s 表示法传递变量的原因是因为我收到以下代码的错误：
for j in eveLists.itemList:
tempPrice = fetchSellPrice(i, j)
database_name = eveLists.DatabaseDict[i]
now = datetime.datetime.utcnow()
now = str(now)
cur.execute("INSERT INTO %s VALUES (%s, %s, %s, NULL, %s, %s, NULL);", [database_name,
str(i),
str(j),
float(tempPrice),
datetime.date.today(),
now
])
psycopg2.ProgrammingError: syntax error at or near "'temp_jita'"
LINE 1: INSERT INTO 'temp_jita' VALUES ('30000142', '2268', 3.03, NU...
^
Please refer to my previous post on the subject: How to remove the quotes from a string for SQL query in Python?
请参阅我之前关于该主题的帖子：如何从 Python 中的 SQL 查询的字符串中删除引号？
EDIT: following @Zorg 's advice from this link (http://initd.org/psycopg/docs/sql.html), this code worked for me:
编辑：按照@Zorg 来自此链接（http://initd.org/psycopg/docs/sql.html）的建议，此代码对我有用：
cur.execute(sql.SQL("INSERT INTO {} VALUES (%s, %s, %s, NULL, %s, %s, NULL);").format(sql.Identifier(database_name)),[
str(i),
str(j),
float(tempPrice),
datetime.date.today(),
now
])
采纳答案by Zorg
Have you read "Psycopg2 usage"?
你读过“Psycopg2 用法”吗？
If not, don't hesitate to do so.
如果没有，请不要犹豫。
Warning NEVER, NEVER, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.
警告 永远、永远、永远不要使用 Python 字符串连接 (+) 或字符串参数插值 (%) 将变量传递给 SQL 查询字符串。甚至不是在枪口下。
Proper way of passing params into a query is shown below:
将参数传递给查询的正确方法如下所示：
SQL = "INSERT INTO authors (name) VALUES (%s);" # Note: no quotes
data = ("O'Reilly", )
cur.execute(SQL, data) # Note: no % operator
回答by Clodoaldo Neto
from psycopg2.extensions import AsIs, quote_ident
cur.execute("""
INSERT INTO %s
VALUES (%s, %s, %s, NULL, %s, %s, NULL);
""", (
AsIs(quote_ident(database_name, cur)),
str(i),
str(j),
float(tempPrice),
datetime.date.today(),
now
))
