d2jsp
Log InRegister
d2jsp Forums > Off-Topic > Computers & IT > Programming & Development > Python Insert Into A Database Table
Add Reply New Topic New Poll
Member
Posts: 5
Joined: Jul 11 2022
Gold: 0.00
Dec 1 2022 02:00am
I'm a newbie in Python, and I'm working on a project where I need to categorize tweets. I've constructed a tweet table in MySQL database with the following attributes: (tweet id,id user,text,tweet location,created at,name screen, categorie name) that will be entered by the information in each tweet that relates to them.
Error:
Code
line 43, in <module>
data['user_screen_name'],data['cat'])
KeyError: 'cat'

Data['cat'] has already been declared in another program that does categorization in the same directory.
Code
import mysql.connector
import json
# create the key

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_name,
)
VALUES (%s,%s,%s,%s,%s,%s,%s)"""
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'],data['cat'])
print('nihad')
mycursor.execute(sql_tweet, val_tweet)

mydb.commit()

Is it necessary to connect the two programs or not? I've read online from various sources ([URL]https://www.scaler.com/topics/insert-in-python/[/URL]) that suggested to use from data.get("date", None) instead of data["date"] this code returns a None value if date key no exists instead of raising an error. Is that correct? Any assistance would be much appreciated.
Member
Posts: 22,234
Joined: Dec 6 2008
Gold: 2,971.74
Trader: Mediator
Dec 1 2022 12:30pm
you can run a debugging terminal and once you get to that line you can type "data['cat']" and see if it's none
it looks like there's nothing in there


you could validate your options before assigning them, so instead of doing everything in val_tweet = ()

to_validate = [data['tweet_id'], data['user_id_str'], data['raw_text'], data['location']['address']['city'], data['date'],
data['user_screen_name'],data['cat']]

for data in to_validate:
if data is None:
print(data + " IS TYPE NONE!")
else:
// However you add to val_tweet

not sure about the data bit, i'm not an sql guy in particular

This post was edited by Candyzcanes on Dec 1 2022 12:39pm
Member
Posts: 8,425
Joined: May 30 2008
Gold: 82.00
Dec 3 2022 07:54am
I’m not sure if this answers your question but I would definitely recommend using get() on dicts/jsons instead of accessing the key directly. And since the category would be a string, I’d probably use data.get(“cat”, “”). This is assuming you’d eventually work with the data in pandas, because iirc pandas will exclude None values in groupbys

I’m also wondering if your direct concatenation of the location/address/city values is causing any issues either. I don’t use MySQL but in pandas you’d need to do something like data[“location”] + data[“address”].

This post was edited by d3skjet on Dec 3 2022 08:08am
Banned
Posts: 14
Joined: Jan 16 2023
Gold: 0.00
Jan 30 2023 03:18am
Hello this is Gulshan Negi
Well to insert data into a database table in Python, you'll need to use a database library such as SQLite, MySQL, or PostgreSQL. Mentioned libraries are looking good in your code but you need to recheck the remaining coding again. You can take help from YouTube.
Thanks
Go Back To Programming & Development Topic List
Add Reply New Topic New Poll