有没有办法使用pycopg2将json插入postgres数据库？

Question

我正在尝试将以下数据插入postgres数据库

{
    "id": 131739425477632000,
    "user_name": "KithureKindiki",
    "content": "@Fchurii You're right, Francis.",
    "deleted": 1,
    "created": "2011-11-02 14:28:21",
    "modified": "2019-01-10 13:05:42",
    "tweet": "{\"contributors\": null, \"truncated\": false, \"text\": \"@Fchurii You're right, Francis.\", \"is_quote_status\": false, \"in_reply_to_status_id\": 131738250736971778, \"id\": 131739425477632000, \"favorite_count\": 0, \"source\": \"<a href=\\\"http://twitter.com\\\" rel=\\\"nofollow\\\">Twitter Web Client</a>\", \"retweeted\": false, \"coordinates\": null, \"entities\": {\"symbols\": [], \"user_mentions\": [{\"indices\": [0, 8], \"id_str\": \"284946979\", \"screen_name\": \"Fchurii\", \"name\": \"Francis Gachuri\", \"id\": 284946979}], \"hashtags\": [], \"urls\": []}, \"in_reply_to_screen_name\": \"Fchurii\", \"in_reply_to_user_id\": 284946979, \"retweet_count\": 0, \"id_str\": \"131739425477632000\", \"favorited\": false, \"user\": {\"follow_request_sent\": false, \"has_extended_profile\": false, \"profile_use_background_image\": true, \"contributors_enabled\": false, \"id\": 399935104, \"verified\": false, \"translator_type\": \"none\", \"profile_text_color\": \"333333\", \"profile_image_url_https\": \"https://pbs.twimg.com/profile_images/538310980468764672/xpJnlD_-_normal.jpeg\", \"profile_sidebar_fill_color\": \"DDEEF6\", \"entities\": {\"description\": {\"urls\": []}}, \"followers_count\": 23555, \"profile_sidebar_border_color\": \"C0DEED\", \"id_str\": \"399935104\", \"default_profile_image\": false, \"listed_count\": 17, \"is_translation_enabled\": false, \"utc_offset\": null, \"statuses_count\": 246, \"description\": \"Majority Leader, The Senate of the Republic of Kenya\", \"friends_count\": 244, \"location\": \"\", \"profile_link_color\": \"1DA1F2\", \"profile_image_url\": \"http://pbs.twimg.com/profile_images/538310980468764672/xpJnlD_-_normal.jpeg\", \"notifications\": false, \"geo_enabled\": false, \"profile_background_color\": \"C0DEED\", \"profile_background_image_url\": \"http://abs.twimg.com/images/themes/theme1/bg.png\", \"screen_name\": \"KithureKindiki\", \"lang\": \"en\", \"following\": false, \"profile_background_tile\": false, \"favourites_count\": 11, \"name\": \"Kithure Kindiki\", \"url\": null, \"created_at\": \"Fri Oct 28 08:09:57 +0000 2011\", \"profile_background_image_url_https\": \"https://abs.twimg.com/images/themes/theme1/bg.png\", \"time_zone\": null, \"protected\": false, \"default_profile\": true, \"is_translator\": false}, \"geo\": null, \"in_reply_to_user_id_str\": \"284946979\", \"lang\": \"en\", \"created_at\": \"Wed Nov 02 14:28:21 +0000 2011\", \"in_reply_to_status_id_str\": \"131738250736971778\", \"place\": null}",
    "politician_id": 41,
    "approved": 1,
    "reviewed": 1,
    "reviewed_at": "2019-01-10 13:05:42",
    "review_message": null,
    "retweeted_id": null,
    "retweeted_content": null,
    "retweeted_user_name": null
  }

使用以下代码

qwery = f"INSERT INTO deleted_tweets(id,user_name,content,deleted,created,modified,tweet,politician_id,approved,reviewed,reviewed_at,review_message,retweeted_id,retweeted_content,retweeted_user_name) VALUES {row['id'], row['user_name'], row['content'], bool(row['deleted']), row['created'], row['modified'],row['tweet'],row['politician_id'],bool(row['approved']), bool(row['reviewed']),row['reviewed_at'],row['review_message'],row['retweeted_id'],row['retweeted_content'],row['retweeted_user_name']}"
qwery = qwery.replace('None', 'null')
cursor.execute(qwery)

但是，我得到了以下错误

*** psycopg2.errors.SyntaxError: syntax error at or near "re"
LINE 1: ... null, "truncated": false, "text": "@Fchurii You\'re right, ...

我知道这是由于单一的报价，但我不知道如何克服它。我尝试过在字符串中添加反斜杠，比如\"text\": \"@Fchurii You\\'re right, Francis.\", 但仍然得到同样的错误。有没有办法绕过这个问题

Answer 1

尝试：

query = "INSERT INTO deleted_tweets (id,user_name,content,deleted,created,modified,tweet,politician_id,approved,reviewed,reviewed_at,review_message,retweeted_id,retweeted_content,retweeted_user_name) VALUES (%s)"
data = [row['id'], row['user_name'], row['content'], bool(row['deleted']), row['created'], row['modified'], row['tweet'], row['politician_id'], bool(row['approved']), bool(row['reviewed']), row['reviewed_at'], row['review_message'], row['retweeted_id'], row['retweeted_content'], row['retweeted_user_name']]
data_without_nulls = ['null' if x is None else x for x in data]
cursor.execute(query, data_without_nulls)