So I'm just trying to play around in MySQL to start getting some experience. I downloaded the Yelp dataset which is in JSON format. Since I already know Python, I figured it wouldn't be too hard to import the data into MySQL using Python because I couldn't find an easy way to do it with standalone MySQL. I set up a loop to add in the JSON row by row, and this loop ran for more than 12 hours. But now that I go to query the database in MySQL, the table appears to be empty.
SELECT COUNT(*) FROM reviews;
Returns a big fat 0.
I've tried querying different things to no avail--the database is empty. I've checked to make sure that the INSERT statement is correct with the given variables, and the string turned out to be exactly what it should.
import mysql.connector as mysql
import json
import os
db = mysql.connect(
user=('root'),
password=('{secret_password}'),
database='yelpdb',
host='localhost'
)
cursor = db.cursor()
with open('filepath', 'r') as f:
for line in f:
data = json.loads(line)
cursor.execute("CREATE TABLE reviews (review_id VARCHAR(255), user_id VARCHAR(255), business_id VARCHAR(255), stars FLOAT(2), useful INT(6), funny INT(6), cool INT(6), text VARCHAR(5001), date DATETIME(2))")
table = 'reviews'
with open('{path}', 'r') as f:
for line in f:
data = json.loads(line)
placeholders = ', '.join(['%s'] * len(data))
columns = ', '.join(data.keys())
values = [each for each in data.values()]
sql = "INSERT INTO %s (%s) VALUES (%s)" % (table, columns, placeholders)
cursor.execute(sql, values)
I thought--especially since the code took so long--that it was a lot of inserts, so it would just take a long time. But empty. It's hard for me to troubleshoot when it takes so long to do.
