How to Insert Values into Table through MySQLdb Python

How to Insert Values into Table through MySQLdb Python

So here I was trying to store the values of a csv file 'employee_reviews.csv' into my employees table. I'm reading through the file, storing it as a string, then splitting the elements all the way down to a 2D list which is then looped through each row. I'm pretty sure there is a more simple way of doing this. When I run my code, I get an Error ->

So here I was trying to store the values of a csv file 'employee_reviews.csv' into my employees table. I'm reading through the file, storing it as a string, then splitting the elements all the way down to a 2D list which is then looped through each row. I'm pretty sure there is a more simple way of doing this. When I run my code, I get an Error ->

cur.execute("INSERT INTO (id, company, location, dates, job_title, summary, pros, cons, advice_to_management, overall_ratings, work_balance_stars, culture_value_stars, career_opportunities_stars, comp_benefit_stars, senior_management_stars, helpful_count, link) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",(int(row[0]), row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], int(row[9]), row[10], row[11], row[12], row[13], row[14], int(row[15]), row[16] )) ValueError: invalid literal for int() with base 10: 'none'


import csv
import MySQLdb

mydb = MySQL.connect(host="host",user="user",passwd="passwd",db="Employee_Review") cur = mydb.cursor() #cur.execute("CREATE TABLE employees(id INT, company VARCHAR(20), location VARCHAR(40), dates DATE, job_title VARCHAR(20), summary VARCHAR(50), pros VARCHAR(1000), cons VARCHAR(1000), advice_to_management VARCHAR(50), overall_ratings INT, work_balance_stars VARCHAR(10), culture_value_stars VARCHAR(10), career_opportunities_stars VARCHAR(10), comp_benefit_stars VARCHAR(10), senior_management_stars VARCHAR(10), helpful_count INT, link VARCHAR(75) )")

f = open("employee_reviews.csv","r") fString = f.read()

fList = [] for line in fString.split('\n'): fList.append(line.split(','))

for row in fList: if row == fList[0]: continue #skip the first row for it contains the name of the columns

cur.execute("INSERT INTO (id, company, location, dates, job_title, summary, pros, cons, advice_to_management, overall_ratings, work_balance_stars, culture_value_stars, career_opportunities_stars, comp_benefit_stars, senior_management_stars, helpful_count, link) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",(int(row[0]), row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], int(row[9]), row[10], row[11], row[12], row[13], row[14], int(row[15]), row[16] )) mydb.commit()

mydb.close()

The csv file can be found here: https://inclass.kaggle.com/petersunga/google-amazon-facebook-employee-reviews/discussion

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

What are the differences between Standard SQL and Transact-SQL?

In this article, we'll explain syntax differences between standard SQL and the Transact-SQL language dedicated to interacting with the SQL

How to Implement CRUD operations in Python on MySQL

In this post, we will see how to implement CRUD operations on MySQL

Python with SQL Server Database

In this article you will learn: How to connect through SQL Server Database using Python? How can we store some information into the SQL Server database using Python? How can we perform an update and delete operations? How we will retrieve stored information from the SQL Server database?