python - 将一个json文件数据连接到mysql数据库（空表）

问题描述

我是 python 的初学者，尝试使用我的程序中加载的 json 文件将访问数据库连接到 python，以便我可以阅读它并最终分析它的某些内容。但我无法连接到它并尝试了不同的方法仍然得到相同的错误。

 import mysql.connector
import json

# create the key
from mysql.connector import cursor

mydb = mysql.connector.connect(host='localhost', port='3306', user='root', password='nihad147', database='tweets')
mycursor = mydb.cursor()

sql_tweet = """INSERT INTO tweet (  tweet_id,
                                    id_user,
                                    text,
                                    tweet_location,
                                    created_at,
                                    name_screen,
                                    categorie_id,
                                    )
                                    VALUES (%s,%s,%s,%s,%s,%s,%s)"""

sql_user = """INSERT INTO tweetuser (
                                        id_user,
                                        name_screen,
                                        location_user,
                                        count_followers,
                                        friends_count,
                                        statuse_count) 
                                        VALUES (%s,%s,%s,%s,%s,%s)"""
sql_location = """"insert into tweet_location (
                            
                                         location_id,
                                         latitude,
                                         longitude
                                          tweet_id
                                        VALUES(%s,%s,%s,%s)"""
myJsonFile = open('tweets.json', encoding="utf-8")
mycursor.execute("DELETE FROM tweet")
mycursor.execute("DELETE FROM tweetuser")
mycursor.execute("DELETE FROM tweet_location")
c = 0
for line in myJsonFile:
    c = c + 1
    print("tweet number ", c, " is uploading to the server")
    data = json.loads(line)
    # insert into tweet
    val_tweet = (
    data['tweet_id'], data['user_id_str'], data['raw_text'],data['location']['address']['city'],data['date'], data['user_screen_name'])

    mycursor.execute(sql_tweet,sql_location, val_tweet)

    mydb.commit()


    # testing ifthe user already exist
    user = "SELECT * FROM tweetuser WHERE id_user = '" + str(data['user_id_str']) + "'"
    mycursor.execute(user)

    myresult = mycursor.fetchall()

    row_count = mycursor.rowcount
    if row_count == 0:

        val_user = (data['user_id_str'], data['user_screen_name'], data['location']['address']['city'],data['user_followers_count'],
                    data['user_friends_count'],  data['user_statuses_count'])

        mycursor.execute(sql_user, val_user)

        mydb.commit()
print('done')

这是 json 文件数据的示例：

{
"tweet_id":"1261276320878788609",
"date":"Fri May 15 12:44:42 +0000 2020",
"raw_text":"برنامج وطني لدعم المبدعين في مواجهة #كورون",
"geo_source":"user_location",
"location":{
"address":{
"country":"Tunisia",
"country_code":"tn",
"state_district":"غزالة",
"county":"العرب",
"state":"Bizerte"
},
"response":"{'place_id': 235309103, 'licence': 'Data © OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright', 'osm_type': 'relation', 'osm_id': 7124228, 'boundingbox': ['37.105957', '37.2033466', '9.4739053', '9.6124953'], 'lat': '37.1551868', 'lon': '9.54834183807249', 'display_name': 'العرب, غزالة, Bizerte, Tunisia', 'class': 'boundary', 'type': 'administrative', 'importance': 0.45, 'icon': '/data/nominatimimages/mapicons/poi_boundary_administrative.p.20.png','address':{'county': 'العرب', 'state_district': 'غزالة', 'state': 'Bizerte', 'country': 'Tunisia', 'country_code': 'tn'}}",
"geohash":"snwg37buskzd",
"query_term":"arab",
"lon":9.54834183807249,
"lat":37.1551868
},
"user_friends_count":61,
"user_description":"I love UAE and his great leadership",
"user_created_at":"Wed Oct 09 11:41:41 +0000 2013",
"user_screen_name":"SikandarMirani",
"user_id_str":"706377881",
"user_verified":false,
"user_statuses_count":50804,
"user_followers_count":946,
"user_location":"Dubai United Arab Emirates"
}

感谢你们，我能够解决之前的错误，因为我没有检查 id 用户的数据类型，它必须是 bigint 而不是 int，因为它是一个大数据。我将我的 jsonfile 连接到我的数据库没有问题，但它只插入到 tweetuser 表中，但没有插入到 tweet 表中。推文表是空的。

我将不胜感激任何帮助谢谢

标签： pythonmysqldatabaseconnection

python - 将一个json文件数据连接到mysql数据库（空表）

问题描述

解决方案

推荐阅读