PostgreSQL表中的TIMESTAMP和NULL

rdrgkggo  于 2023-04-05  发布在  PostgreSQL
关注(0)|答案(1)|浏览(357)

我试图在postgresql中填充一个数据库表,只是不断收到错误。数据是一个客户数据集,有15列,所有列都是对象类型。该表设置为:

customer = ("""CREATE TABLE IF NOT EXISTS customer (
CustID INT PRIMARY KEY NOT NULL,
CustFName VARCHAR(100) NOT NULL,
CustLName VARCHAR(100) NOT NULL,
CustPhone VARCHAR(14),
CustEmail VARCHAR(275),
CustState CHAR(2),
ContactPref VARCHAR(10),
PmtID VARCHAR(20),
AddedStamp TIMESTAMP NOT NULL,
UpdatedStamp TIMESTAMP,
HHI INT,
IsMarried CHAR(1),
HasKids CHAR(1),
TravelsWPet CHAR(1),
Pronoun VARCHAR(20));
""")

cursor.execute(customer)
conn.commit()

数据在updatedstamp列中有示例,其中它是空白的,我需要它在表中放入NULL(没有引号,只是单词),但是当有日期时,我需要它将日期与引号一起放置,例如:'2015-01-01'.
下面是最新的尝试,让它工作:

import pandas as pd

custdf = pd.read_csv('customer.csv')

custdf = custdf.replace('NULL', None)

custdf['custid'] = custdf['custid'].astype(object)

for x in custdf.index:
    cursor.execute("""
    INSERT INTO customer (custid, custfname, custlname, custphone, custemail, custstate, contactpref, pmtid,
                      addedstamp, updatedstamp, hhi, ismarried, haskids, travelswpet, pronoun)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s,
        IF(%s = 'NULL', NULL, '%s'), %s, %s, %s, %s, %s)""",
                   (custdf.loc[x]['custid'],
                    custdf.loc[x]['custfname'],
                    custdf.loc[x]['custlname'],
                    custdf.loc[x]['custphone'],
                    custdf.loc[x]['custemail'],
                    custdf.loc[x]['custstate'],
                    custdf.loc[x]['contactpref'],
                    custdf.loc[x]['pmtid'],
                    custdf.loc[x]['addedstamp'],
                    custdf.loc[x]['updatedstamp'],
                    custdf.loc[x]['hhi'],
                    custdf.loc[x]['ismarried'],
                    custdf.loc[x]['haskids'],
                    custdf.loc[x]['travelswpet'],
                    custdf.loc[x]['pronoun']))
    conn.commit()
    
query = ("""SELECT * FROM customer""")

customer_df = pd.read_sql(query, conn)
print(customer_df)

此代码返回'IndexError:tuple index out of range' -但是列数和占位符的数量是相同的。
我该怎么补救呢?

wpx232ag

wpx232ag1#

我建议您在将SQL语句放入代码之前先尝试一下。例如,SQL语言中没有IF
正如您所发现的,错误是由NULL周围的引号引起的,但是,下面隐藏着一个更深层次的问题:通过使用execute("... '%s' ...", var)构造字符串字面量,您的代码容易受到SQL注入的攻击。使用the method provided by psycopg2以安全的方式构造SQL字符串。这也应该可以解决您的直接问题。

相关问题