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()

[Larz60+]

Does hdbrpi.to_sql only create insert statement
** guessing **

cur = cnx.cursor()
query = hdbrpi.to_sql(name='table_hdbrpi', con=engine, if_exists='replace', index=False)
cur.execute(cnx)

[kirito85]

Hi Larz60+,

Thanks for the reply. I hoped that line of code would have saved the data from the csv file to the mysql database. However it did not work, it might be connection problem which i copy from elsewhere or i have missing code.

i will try your 3 lines of code first.

[scidam]

Something is wrong with the code above. First of all, one can see a lot of raw sql queries. In the same time
SqlAlchemy is an ORM, ORM is used to avoid such queries.

In line 9 you are creating sqlite engine, but you are talking about mysql connection!?
How is cnx related with engine? (line 33)

[kirito85]

Hi,

I have actually copied and pasted different codes here and there, hence the mess.

I know that the sql engine shouldnt be there but if i take it out i dont know what else to replace it.

---

Hi,

Ok you were right i dont even need that sqllite code, i just commented it.

I try to figure out the rest of error why the cnx doesnt connect to the database and save data.