connect a json file data to mysql database (empty table)

Question

im a beginner in python , Trying to connect access database to python with a json file loaded in my program so I can read it and eventually analyze it for certain things. But I can't connect to it and tried different approaches still getting the same error.

 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')

here's an example of json file data :

{
"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"
}
   

thanks to you guys , i was able to solve the previous error since i didn't check tha data type of the id user it has to be bigint not int since it's a large data . i had no problem connecting my jsonfile to my database but it got inserted only in tweetuser table but not in tweet table . the tweet table is empty.

i would appreciate any kind of help thank you

Answer 1

The error

mysql.connector.errors.DataError: 1264 (22003): Out of range value for column 'id_user' at row 1

suggests that the value you are trying to use as the id_user is numerically too large.

Since you haven't posted the table definitions, my guess is you are using MEDIUMINT or SMALLINT or TINYINT for id_user and the actual user ID that you are trying to write into the database is too large for that data type.

In your example user_id_str is 706377881, however, the maximum value for MEDIUMINT is 8388607 and 16777215 (unsigned), respectively.

Check the data types in the table definitions.

Answer 2

You are connecting to your DB, that is not the problem. The problem is that the user id that you are trying to insert has a length that surpasses the maximum allowed by MySQL for the datatype of that field. See here and here for more info related to your error.