使用Python和Psycopg2将JSON数据文件导入PostgreSQL [英] Importing JSON data file into PostgreSQL using Python and Psycopg2
问题描述
我无法使查询正常工作.我有一个超过80k行数据的JSON文件.由于遇到了很多问题,我将文档缩减为三行,只是在尝试尝试完整的80k行之前,看看是否可以获取数据:
I am having trouble getting my query to work. I have a JSON file with over 80k lines of data. Since I have been having so many problems I cut the document down to three lines just to see if I can get the data in before I attempt the full 80k lines:
Import psycopg2
import io
readTest1 = io.open("C:\Users\Samuel\Dropbox\Work\Python and Postgres\test1.json", encoding = "utf-8")
readAll = readTest1.readlines()
我在网上看到,使用readlines并不是最好的方法,但这是我所知道的唯一方法.此方法读取文件中的三行.我不确定,但是我希望这也可以使其成为数组.
I have seen online that using readlines is not the best method but it is the only method i know. This method read the three lines in the file. I am not sure but I expected this to make it an array also.
conn = psycopg2.connect("dbname = python_trial user = postgres")
cur = conn.cursor()
cur.execute("CREATE TABLE test4 (data json);")
创建仅包含JSON数据的表:
Create a table that only takes JSON data:
cur.executemany("INSERT INTO test4 VALUES (%s)", readAll)
错误:
Traceback (most recent call last):
File "<pyshell#13>", line 1, in <module>
cur.executemany("INSERT INTO test4 VALUES (%s)", readAll)
TypeError: not all arguments converted during string formatting
我不确定自己做错了什么.我在打印(readAll)时也看到"\ n".我认为这是由使用readlines方法引起的,我不确定这是否也弄乱了我的查询.
I am not exactly sure what I am doing incorrectly. I am also seeing "\n" when i print (readAll). I think that is caused by using the readlines method and I am not sure if that is messing up my query also.
推荐答案
使用此:
cur.executemany("INSERT INTO test4 VALUES ('{0}')".format(readAll))
或(readAll,):
cur.executemany("INSERT INTO test4 VALUES (%s)", (readAll,))
请参阅: http://initd.org /psycopg/docs/usage.html#passing-parameters-to-sql-queries
这篇关于使用Python和Psycopg2将JSON数据文件导入PostgreSQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!