Feb-06-2019, 07:58 AM
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.
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()