I'm writing a Python Script to store JSON data into MySQL Database. I used pandas to store into MySQL Database.
I used two different modules (MySQLdb and sqlalchemy) to connect to MySQL dtaabase.
Python Code:
user@sys:~$ cat pandas_to_mysql.py
import pandas
from pandas.io import sql
from sqlalchemy import create_engine
import MySQLdb
json_string_final='[{"id": 772, "name": "abcd"}]'
df=pandas.DataFrame(eval(json_string_final))
print(df)
#----Using MySQLdb---
con=MySQLdb.connect(host='localhost',user='user1',passwd='pwd',db='myDB')
try:
df.to_sql(con=con, name='myTable1', if_exists='replace')
except Exception as error1:
print("Error with connection 1 ------> ")
print(error1)
#----Using Engine----
engine = create_engine("mysql+pymysql://{user}:{pw}@localhost/{db}".format(user="user1",pw="pwd",db="myDB"))
try:
df.to_sql(con=engine, name='myTable2', if_exists='replace')
except Exception as error1:
print("Error with connection 2 ----->")
print(error1)
I used two diffrent connections to store the data into MySQL dtabase. But connection was not established. Below is the error message.
Output:
user@sys:~$ python3 pandas_to_mysql.py
id name
0 772 abcd
Error with connection 1 ------>
Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during bytes formatting
Error with connection 2 ----->
(pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'localhost' (timed out)")
(Background on this error at: http://sqlalche.me/e/e3q8)
But i'm able to connect to MySQL database manually from the same terminal using the following command.
MySQL Connection:
user@sys:~$ mysql -u user1 -h localhost -D myDB -p
It means, there is no issues with host, user, password, database name. Then what is the issue?
