Writing to MySQL database with pandas using SQLAlchemy, to_sql

Question

trying to write pandas dataframe to MySQL table using to_sql. Previously been using flavor='mysql', however it will be depreciated in the future and wanted to start the transition to using SQLAlchemy engine.

sample code:

import pandas as pd
import mysql.connector
from sqlalchemy import create_engine

engine = create_engine('mysql+mysqlconnector://[user]:[pass]@[host]:[port]/[schema]', echo=False)
cnx = engine.raw_connection()
data = pd.read_sql('SELECT * FROM sample_table', cnx)
data.to_sql(name='sample_table2', con=cnx, if_exists = 'append', index=False)

The read works fine but the to_sql has an error:

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': Wrong number of arguments during string formatting

Why does it look like it is trying to use sqlite? What is the correct use of a sqlalchemy connection with mysql and specifically mysql.connector?

I also tried passing the engine in as the connection as well, and that gave me an error referencing no cursor object.

data.to_sql(name='sample_table2', con=engine, if_exists = 'append', index=False)
>>AttributeError: 'Engine' object has no attribute 'cursor'

You have to pass the Engine itself, not a raw connection (in the future an sqlalchemy Connection will also be possible, but not a raw connection). Can you see if that solves the problem? — joris, Jun 03 '15 at 22:04
Ah, I see that you already tried that :-) Can you show the error you get in that case? — joris, Jun 03 '15 at 22:04
when using engine: AttributeError: 'Engine' object has no attribute 'cursor' — AsAP_Sherb, Jun 03 '15 at 22:21
Can you show the output of `pd.__versions__`? You're sure not picking up an old pandas version? — joris, Jun 03 '15 at 22:23
pd.__version__ is 0.16.1, assuming you meant `__version__` not `__versions__` — AsAP_Sherb, Jun 03 '15 at 22:29
Can you show the full traceback? (when using IPython eg?) to see from which lines this error comes. We don't use cursors anymore in the new implementation, so this error is a bit strange — joris, Jun 03 '15 at 22:37
Yes, this error was really strange. I restarted my environment and went through the process again this morning. I used a sqlalchemy engine and it worked. I am perplexed at what was different. — AsAP_Sherb, Jun 04 '15 at 20:54

score 81 · Accepted Answer · edited Sep 03 '19 at 10:39

81

Using the engine in place of the raw_connection() worked:

import pandas as pd
import mysql.connector
from sqlalchemy import create_engine

engine = create_engine('mysql+mysqlconnector://[user]:[pass]@[host]:[port]/[schema]', echo=False)
data.to_sql(name='sample_table2', con=engine, if_exists = 'append', index=False)

Not clear on why when I tried this yesterday it gave me the earlier error.

edited Sep 03 '19 at 10:39

SuperShoot

9,880
2
38
55

answered Jun 04 '15 at 20:56

AsAP_Sherb

1,661
1
13
16

Glad it works now! In any case, this is the way to go – joris Jun 04 '15 at 21:31
1

BTW, you may accept your own answer to indicated this is fixed! – joris Jun 07 '15 at 14:18
2

In case you run into problems installing `mysql.connector` or don't know how to install it, see this link https://stackoverflow.com/questions/32754461/how-to-install-mysql-connector-via-pip They recommend `pip install mysql-connector==2.1.4`. It also solved this problem for me. – cheevahagadog Jan 18 '18 at 19:23
Consistently getting error Python installation has no SSL support. Running with python 3.7 with fresh Anaconda install. – alex Jan 15 '19 at 20:59
I have @ in password filed and it's creating an issue. How can we fix this. engine = create_engine("mysql+pymysql://dbuser:DBuser@12345@xyz/table") Here DBuser@12345 is password. but somehow script not able to interpret it correct. It user 12345@xyz as hostname instead of xyz – niraj pandey Aug 27 '21 at 09:25
[Suggestion for a faster way] With `SQLAlchemy>=1.3`, while creating `engine` object, set `fast_executemany=True`. [Reference](https://docs.sqlalchemy.org/en/13/dialects/mssql.html#fast-executemany-mode) – Vishal Gupta Sep 21 '21 at 07:54

score 14 · Answer 2 · answered Mar 24 '17 at 18:43

14

Alternatively, use pymysql package...

import pymysql
from sqlalchemy import create_engine
cnx = create_engine('mysql+pymysql://[user]:[pass]@[host]:[port]/[schema]', echo=False)

data = pd.read_sql('SELECT * FROM sample_table', cnx)
data.to_sql(name='sample_table2', con=cnx, if_exists = 'append', index=False)

answered Mar 24 '17 at 18:43

openwonk

14,023
7
43
39

1

openwonk - can you elaborate on the `[port]/[schema]` portion of this code? Not exactly sure what to put there. Thank you! – elPastor Jul 05 '17 at 18:03
1

Gonna answer my own question here, schema = data base. – elPastor Jul 05 '17 at 19:10
Glad to help, @pshep123 – openwonk Jul 05 '17 at 19:43
Although I'm still having a hell of a time here: https://stackoverflow.com/questions/44933704/pandas-0-20-2-to-sql-using-mysql. Not sure if you have any insight, thanks. – elPastor Jul 05 '17 at 19:47
Are you getting any other specifics about why connection refused? Could be entirely unrelated to Python, Pandas, and SQLAlchemy. – openwonk Jul 05 '17 at 20:18
This is the only solution I found that is working with Python 3 – guidoman Mar 01 '18 at 14:13
1

I spent forever trying to find an answer like this that worked! – alpastor May 03 '23 at 22:36

score 8 · Answer 3 · answered Mar 06 '18 at 17:02

Using pymysql and sqlalchemy, this works for Pandas v0.22:

import pandas as pd
import pymysql
from sqlalchemy import create_engine

user = 'yourUserName'
passw = 'password'
host =  'hostName'  # either localhost or ip e.g. '172.17.0.2' or hostname address 
port = 3306 
database = 'dataBaseName'

mydb = create_engine('mysql+pymysql://' + user + ':' + passw + '@' + host + ':' + str(port) + '/' + database , echo=False)

directory = r'directoryLocation'  # path of csv file
csvFileName = 'something.csv'

df = pd.read_csv(os.path.join(directory, csvFileName ))

df.to_sql(name=csvFileName[:-4], con=mydb, if_exists = 'replace', index=False)

"""
if_exists: {'fail', 'replace', 'append'}, default 'fail'
     fail: If table exists, do nothing.
     replace: If table exists, drop it, recreate it, and insert data.
     append: If table exists, insert data. Create if does not exist.
"""

score 0 · Answer 4 · answered Jan 05 '18 at 15:07

I know in the title of the question is included the word SQLAlchemy, however I see in the questions and answers the need to import pymysql or mysql.connector, and also is possible to do the job with pymysql, withouth calling SQLAlchemy.

import pymysql
user = 'root'
passw = 'my-secret-pw-for-mysql-12ud' # In previous posts variable "pass"
host =  '172.17.0.2'
port = 3306

database = 'sample_table' # In previous posts similar to "schema"

conn = pymysql.connect(host=host,
                       port=port,
                       user=user, 
                       passwd=passw,  
                       db=database)

data.to_sql(name=database, con=conn, if_exists = 'append', index=False, flavor = 'mysql')

I think this solution could be good althought it is not using SQLAlchemy.

Please rename "database" to "table". That way it is very misleading and wrong. — Ivailo Bardarov, Mar 10 '22 at 16:43

Writing to MySQL database with pandas using SQLAlchemy, to_sql

4 Answers4

Linked