0
$\begingroup$

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?

$\endgroup$

1 Answer 1

0
$\begingroup$

The following works for me:

df.to_sql(con="mysql://user:password@localhost/database_name, name='myTable2', if_exists='replace')
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.