Postgresql 和 Psycopg2 无法正确选择两个日期之间的数据

Question

我创建了一个 API 服务，它以 JSON 形式返回股票代码、汇率和日期，但是当我尝试选择多个参数时，它无法正常工作，就像这里的示例一样，假设end_at=2010-05-10和start_at= 2010-05-15：

数据库模型：

class Currency(Base):

   __tablename__ = "currency"

   ticker = Column(String)

   date = Column(Date)

   rates = Column(JSONB, primary_key=True)

更新了 Mike Orgenek 的回答中的查询代码：

if end_at and start_at:

        currency = cursor.execute("""

        SELECT rates,date,ticker

        FROM currency

        WHERE ticker = %s

        AND date BETWEEN SYMMETRIC %s AND %s """, (base, start_at, end_at, ))

start_at打印= 2010-05-10& end_at=的查询后2010-05-15

Out: 2020-07-04T09:32:30.898337+00:00 app[web.1]: b"\n        SELECT rates,date,ticker\n        FROM currency\n        WHERE ticker = 'EUR'\n        AND date BETWEEN SYMMETRIC '2010-05-10' AND '2010-05-15' "

start_at即使使用正确的查询FULL API 输出，它也无法识别我的参数

它包括早于start_at类似"2010-01-28", "2010-01-07","2010-04-16"

Answer 1

请参数化您的查询以避免 SQL 注入攻击。if end_at and start_at:&nbsp; &nbsp; &nbsp;currency = cursor.execute("""SELECT rates,date,ticker&nbsp;&nbsp; &nbsp; &nbsp;FROM currency&nbsp; &nbsp; &nbsp;WHERE ticker = %s&nbsp;&nbsp; &nbsp; &nbsp;AND date BETWEEN SYMMETRIC %s AND %s """, (base, start_at, end_at, ))&nbsp; &nbsp; &nbsp;print(cursor.query)&nbsp; # <--- This should log the complete query sent to the db server要诊断手头的问题，请执行类似这样的操作以查看您发送的内容，但在将所有调用更改为参数化形式之前不要让您的应用程序生效。execute()if end_at and start_at:&nbsp; &nbsp; &nbsp;query = f"""SELECT rates,date,ticker&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM currency&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE ticker = '{base}'&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND date BETWEEN SYMMETRIC '{start_at}' AND '{end_at}' """&nbsp; &nbsp; &nbsp;print(query)&nbsp; &nbsp; &nbsp;currency = cursor.execute(query)在修复您的查询以使用参数后，要查看查询中发送到服务器的内容，请使用 作为LoggingConnection您现有的替代品psycopg2.Connection，如从我如何使用 Psycopg2 的 LoggingConnection 中提取的？.import loggingimport psycopg2from psycopg2.extras import LoggingConnectionlogging.basicConfig(level=logging.DEBUG)logger = logging.getLogger(__name__)conn=psycopg2.connect(connection_factory=LoggingConnection, database='some_database')conn.initialize(logger)c = conn.cursor()c.execute("select count(*) from some_table where id > %s", (1000, ))使用我的基本配置，日志记录进入控制台：DEBUG:__main__:b'select count(*) from some_table where id > 1000'