Problem with saving data and loading data to mysql

[kirito85]

Hi all,

I have problems with the saving my data to mysql and loading it back again from mysql.
I have to load the data first with pandas from a csv file, and that part works fine.
But after when i tried to save the data to mysql, only the table is created but the data is not saved.

Below is the code.

Appreicate your kind help. Thanks.

import numpy as np
import matplotlib.pyplot as plt
import mysql.connector,sys, pandas as pd
from datetime import date, datetime, timedelta
from dateutil.parser import parse
from bokeh.io import output_notebook
from bokeh.plotting import figure, show
from sqlalchemy import create_engine
engine = create_engine('sqlite://', echo=False)
from pandas.io import sql

### Read the hdb resale price index csv file with the pandas readcsv() function
hdbrpi = pd.read_csv('housing-and-development-board-resale-price-index-1q2009-100-quarterly.csv', 
                   index_col=None, delimiter=',')
user,pw, host,db = 'root','S8534107c!','127.0.0.1','mydatabase'
cnx = mysql.connector.connect(user=user, password=pw, host=host, database=db)
cursor = cnx.cursor()
print("Successfully loaded dataset housing-and-development-board-resale-price-index-1q2009-100-quarterly.csv from sql database")
print()

query_for_creating_database = 'CREATE DATABASE mydatabase'
query_for_creating_table = ("CREATE TABLE `table_hdbrpi` ("
    "`quarter` varchar(10) NOT NULL,"
    "`index` int(11) NOT NULL,"
    "PRIMARY KEY (`quarter`)"
    ") ENGINE=InnoDB")

try:
#     cursor.execute(query_for_creating_database)
#     cursor.execute(query_for_creating_table)
    
    hdbrpi.to_sql(name='table_hdbrpi', con=engine, if_exists='replace', index=False)
    cnx.commit()
    print("Data saved!")
    #engine.execute("SELECT * FROM table_hdbrpi").fetchall()
    pull_hdbrpi = pd.read_sql("SELECT * FROM table_hdbrpi;", engine)
    print("Data loaded!")

except:
   print("Unexpected error:", sys.exc_info()[0])
   print("Unexpected error:", sys.exc_info()[1])
   print("Unexpected error:", sys.exc_info()[2])
   exit()

finally:
  cursor.close()
  cnx.close()