INSERT csv file into MySQL via python. Runs but data does not populate in Table

Question

First off I am very new to python so be nice. I'm trying to take a CSV file that was generated from a SQL query that is located on my drive in a folder and "upload"/INSERT the data on that file into a MySQL table via python. I do not want to simply just upload the file manually. The script runs and shows the data processing but when I go into the table and try to search for it nothing appears. Below is what I wrote:

cursor = cnx.cursor()

if cnx.is_connected():
    print('Successfully connected to MySQL database')

cursor.execute("create database if not exists Character_Stats;")

cursor.execute("use Character_Stats;")

DB_NAME = 'Character_Stats_DB'
TABLES = {}
cursor.execute('DROP TABLE IF EXISTS Local_Players')

cursor.execute("""CREATE TABLE IF NOT EXISTS `Local_Players` (
  `game_account_id` int,
  `character_key` char(20) DEFAULT NULL,
  `game_region_key` int(20) DEFAULT NULL,
  `character_id` int(20) DEFAULT NULL,
  `class_name` TEXT(20) DEFAULT NULL,
  `specialization_id` int(20) DEFAULT NULL,
  `character_name` TEXT(20) DEFAULT NULL,
  `name` TEXT DEFAULT NULL,
  `character_level` int(20) DEFAULT NULL,
  `total_dmg` int(20) DEFAULT NULL,
  `avg_dmg_per_game` int(20) DEFAULT NULL,
  `total_healing` int(20) DEFAULT NULL,
  `total_dmg_absorbed` int(20) DEFAULT NULL,
  `total_heal_absorbed` int(20) DEFAULT NULL,
  `avg_heal_absorbed_per_game` int(20) DEFAULT NULL,
  `total_killing_blows` int(20) DEFAULT NULL,
  `avg_killing_blows_per_game` int(20) DEFAULT NULL,
  `total_deaths` int(20) DEFAULT NULL,
  `avg_deaths_per_game` int(20) DEFAULT NULL,
  `total_time_hrs` int(20) DEFAULT NULL,
  `total_games_played` int(20) DEFAULT NULL,
  PRIMARY KEY (game_account_id))
  ENGINE = InnoDB""")

cnx.commit()

for data in query:
    with open('Player_stats.csv', 'r', encoding="utf-8", newline='') as playerdata:
        csv_reader = csv.reader(playerdata, delimiter=',')
        for row in csv_reader:
            print(row)
            character_key = row[0]
            game_region_key = row[1]
            character_id = row[2]
            class_name = row[3]
            character_name = row[4]
            realm_key = row[5]
            name = row[6]
            character_level = row[7]
            total_dmg = row[8]
            avg_dmg_per_game = row[9]
            total_healing = row[10]
            avg_healing_per_game = row[11]
            total_dmg_absorbed = row[12]
            total_heal_absorbed = row[13]
            avg_heal_absorbed_per_game = row[14]
            total_killing_blows = row[15]
            avg_killing_blows_per_game = row[16]
            total_deaths = row[17]
            avg_deaths_per_game = row[18]
            total_time_hrs = row[19]
            total_games_played = row[20]
            cursor.execute('''INSERT INTO Character_Stats.Local_Players(character_key,game_region_key
        , character_id, class_name, character_name, realm_key, name, character_level, total_dmg, avg_dmg_per_game, 
        total_healing, avg_healing_per_game, total_dmg_absorbed, total_heal_absorbed, avg_heal_absorbed_per_game, 
        total_killing_blows, avg_killing_blows_per_game, total_deaths, avg_deaths_per_game, total_time_hrs, total_games_played),
        VALUES(%s,%s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s)''', multi=True)
            print(data)


print("Data loading complete. \n")

Any help would be greatly appreciated.

This is the output of my CSV:

Would u mind If I tell you another way, which is lot easier.?and can u show ur Player_stats.csv? — ashishmishra
– ashishmishra, Commented May 12, 2020 at 19:56
My code is working, you can execute it and lmk if any error comes. — ashishmishra
– ashishmishra, Commented May 12, 2020 at 21:14
You need to call cnx.commit() after doing all the inserts. — snakecharmerb
– snakecharmerb, Commented May 13, 2020 at 6:46

ashishmishra · Accepted Answer · 2020-05-12 21:16:02Z

This code will fill all your columns in your table with your csv file. Just try to read documentation of these modules, As Python is a very smart and simple language with libraries:

install pandas, pymsql and sqlalchemy:

pip install sqlalchemy
pip install pymysql
pip install pandas

Try this:

import pandas as pd
from sqlalchemy import create_engine
connection_string = "mysql+pymysql://%s:%s@%s:%s/%s" % ('user_name','ur_connection_password' , 'host', PORT, 'schema_name')
engine = create_engine(connection_string)
connection = engine.connect()
connection.execute("""
CREATE TABLE IF NOT EXISTS `Local_Players` (
  `game_account_id` int NOT NULL AUTO_INCREMENT,
  `character_key` varchar(20) DEFAULT NULL,
  `game_region_key` int(20) DEFAULT NULL,
  `character_id` int(20) DEFAULT NULL,
  `class_name` TEXT(20) DEFAULT NULL,
  `specialization_id` int(20) DEFAULT NULL,
  `character_name` TEXT(20) DEFAULT NULL,
  `name` TEXT DEFAULT NULL,
  `character_level` int(20) DEFAULT NULL,
  `total_dmg` int(20) DEFAULT NULL,
  `avg_dmg_per_game` int(20) DEFAULT NULL,
  `total_healing` int(20) DEFAULT NULL,
  `total_dmg_absorbed` int(20) DEFAULT NULL,
  `total_heal_absorbed` int(20) DEFAULT NULL,
  `avg_heal_absorbed_per_game` int(20) DEFAULT NULL,
  `total_killing_blows` int(20) DEFAULT NULL,
  `avg_killing_blows_per_game` int(20) DEFAULT NULL,
  `total_deaths` int(20) DEFAULT NULL,
  `avg_deaths_per_game` int(20) DEFAULT NULL,
  `total_time_hrs` int(20) DEFAULT NULL,
  `total_games_played` int(20) DEFAULT NULL,
  PRIMARY KEY (`game_account_id`))""")
pd.read_sql_table(table_name='Local_Players',schema='Test_DB', con=engine)
df = pd.read_csv(r"Player_stats.csv")
df.to_sql(name="Local_Players", if_exists='append', chunksize=1000, con=engine, index=False)

Shubham Jain · Accepted Answer · 2020-05-12 20:08:34Z

0

Nothing is getting inserted because your string formatting is wrong

cursor.execute('''INSERT INTO Character_Stats.Local_Players(character_key,game_region_key
        , character_id, class_name, character_name, realm_key, name, character_level, total_dmg, avg_dmg_per_game, 
        total_healing, avg_healing_per_game, total_dmg_absorbed, total_heal_absorbed, avg_heal_absorbed_per_game, 
        total_killing_blows, avg_killing_blows_per_game, total_deaths, avg_deaths_per_game, total_time_hrs, total_games_played),
        VALUES(%s,%s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s)''' %(character_key,game_region_key
        , character_id, class_name, character_name, realm_key, name, character_level, total_dmg, avg_dmg_per_game, 
        total_healing, avg_healing_per_game, total_dmg_absorbed, total_heal_absorbed, avg_heal_absorbed_per_game, 
        total_killing_blows, avg_killing_blows_per_game, total_deaths, avg_deaths_per_game, total_time_hrs, total_games_played), multi=True)

You forgot to assign values, test this and you should now see the data.

answered May 12, 2020 at 20:08

Shubham Jain

5,6162 gold badges20 silver badges42 bronze badges

1 Comment

Bradley Over a year ago

I tried this and received the same results. The scripts runs and I can tell that it's pulling the data but when I enter into my table it's not there.

Collectives™ on Stack Overflow

INSERT csv file into MySQL via python. Runs but data does not populate in Table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related