insert data into mysql table using python

Question

im a beginner in python language , and im trying to insert my json file data into my database table using python but the problem is i have no errors i just get :

tweet number 49634 is uploading to the server

i don't get where the problem is, please i would appreciate any help

import mysql.connector
import json

mydb = mysql.connector.connect(host='localhost', port='3306', user='root', password='nihad147', database='tweets')
mycursor = mydb.cursor()
sql_request='insert ignore into tweet_location (latitude, longitude, tweet_id) values (%s,%s,%s)'""

myJsonFile = open('tweet.json', encoding="utf-8")
c = 0



for line in myJsonFile:
  c = c + 1
print("tweet number ", c, " is uploading to the server")
data = json.loads(line)
#line = line.replace('','')

tweet = "SELECT * FROM tweet WHERE tweet_id = '" + str(data['tweet_id']) + "'"
mycursor.execute(tweet)

myresult = mycursor.fetchall()

row_count = mycursor.rowcount
if row_count == 0:
 if 'location' in data.keys() and data['location'] != None and 'address' in data['location']:
  latitude = data ['location']['lat']
  longitude=data ['location']['lon']


mycursor.execute(sql_request, (latitude,longitude,data['tweet_id']))
print ('------------')


mydb.commit ()

here's an example of my 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"}

and this my table's attributes tweet: tweet_id, id_user, text, tweet_location, created_at, name_screen, categorie_id,

Please use proper 4-space indents, it's really hard to read the code with just 1 space. — Barmar
– Barmar, Commented Jun 3, 2021 at 23:13
Why are you only getting location from the JSON when the tweet ID isn't found in the tweet table. — Barmar
– Barmar, Commented Jun 3, 2021 at 23:30

Barmar · Accepted Answer · 2021-06-03 23:26:24Z

Don't read the JSON file one line at a time. Use json.load() to load the entire file into a dictionary.

Use a parameter in the query that selects the tweet, rather that concatenating data['tweet_id'] into the SQL.

The code that inserts the new row should be inside all the if statements that set latitude and longitude from the data. In fact, you might as well put all the database code inside the if statement that checks whether location is set in the JSON.

import mysql.connector
import json

mydb = mysql.connector.connect(host='localhost', port='3306', user='root', password='nihad147', database='tweets')
mycursor = mydb.cursor()
sql_request='insert ignore into tweet_location (latitude, longitude, tweet_id) values (%s,%s,%s)'""

with open('tweet.json', encoding="utf-8") as myJsonFile:
    data = json.load(myJsonFile)

if data.get('location') and 'address' in data['location']:
    tweet = "SELECT 1 FROM tweet WHERE tweet_id = %s"
    mycursor.execute(tweet, (data['tweet_id'],))
    myresult = mycursor.fetchall()

    row_count = len(myresult)
    if row_count == 0:
        print(f"Inserting {data['tweet_id']} to the database");
        latitude = data['location']['lat']
        longitude = data['location']['lon']
        mycursor.execute(sql_request, (latitude,longitude,data['tweet_id']))
        mydb.commit ()
        print ('------------')
    else:
        print(f"Tweet {data['tweet_id']} is already in the database")

Collectives™ on Stack Overflow

insert data into mysql table using python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related