使用“:”让 Python 接受 csv 到 postgreSQL 表中在标题中
我每 10 分钟收到一次 .csv 导出，我想将其导入到 postgreSQL 服务器中。使用测试 csv，我让一切正常工作，但没有注意到我的实际 csv 文件在每个列标题的末尾有一个强制的“：”（但由于某种原因不在第一个标题上）（内置于出口商的后端，所以我无法将其删除，已经问过该公司）。因此，我将“：”添加到我的测试 csv 中，如链接所示，
我不再插入函数工作并给我语法错误。首先，我尝试使用以下代码添加它们，
print("Reading file contents and copying into table...")
with open('C:\\Users\\admin\\Desktop\\test2.csv') as csvfile:
readCSV = csv.reader(csvfile, delimiter=',')
columns = next(readCSV) #skips the header row
query = 'insert into test({0}) values ({1})'
query = query.format(','.join(columns), ','.join('?' * len(columns)))
for data in readCSV:
cursor.execute(query, data)
con.commit()
导致第二列标题中“：”附近出现“42601”错误。
实际列出列标题和 ? 时的结果是相同的？ ?s 在 INSERT INTO 部分中。
让脚本接受列标题上的“：”的语法是什么？如果没有办法，有没有办法扫描标题并删除每个标题末尾的“：”？
I receive a .csv export every 10 minutes that I'd like to import into a postgreSQL server. Working with a test csv, I got everything to work, but didn't take notice that my actual csv file has a forced ":" at the end of each column header (but not on the first header for some reason)(built into the back-end of the exporter, so I cant get it removed, already asked the company). So I added the ":"s to my test csv as shown in the link,
My insert into functions no longer work and give me syntax errors. First I'm trying to add them using the following code,
print("Reading file contents and copying into table...")
with open('C:\\Users\\admin\\Desktop\\test2.csv') as csvfile:
readCSV = csv.reader(csvfile, delimiter=',')
columns = next(readCSV) #skips the header row
query = 'insert into test({0}) values ({1})'
query = query.format(','.join(columns), ','.join('?' * len(columns)))
for data in readCSV:
cursor.execute(query, data)
con.commit()
Resulting in '42601' error near ":" in the second column header.
The results are the same while actually listing column headers and ? ? ?s out in the INSERT INTO section.
What is the syntax to get the script to accept ":" on column headers? If there's no way, is there a way to scan through headers and remove the ":" at the end of each?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
因为
:是一个特殊字符，如果您的列在数据库中名为year:，则必须双引号其名称 -->select "year:" from test;您收到 PG 错误，因为您引用的是未加引号的列名称 (
insert into test({0}))，因此添加 double那里有引号。query = 'insert into test("year:","day:", "etc:") values (...)'话虽这么说，删除所有出现的 < 可能会更简单code>: 在 csv 的第一行
Because
:is a special character, if your column is namedyear:in the DB, you must double quote its name -->select "year:" from test;You are getting a PG error because you are referencing the unquoted column name (
insert into test({0})), so add double quotes there.query = 'insert into test("year:","day:", "etc:") values (...)'That being said, it might be simpler to remove every occurrence of
:in your csv's 1st line非常感谢 JGH 和 Adrian。我采纳了您的建议，通过在第一个
columns = ...语句后添加以下行来删除所有出现的 : ，效果很好。
Much appreciated JGH and Adrian. I went with your suggestion to remove every occurrence of : by adding the following line after the first
columns = ...statementIt worked well.