pandas to_sql sqlalchemy connection with secure_transport

user_8504799 0
Viewed 1446

I am trying to send data to a mysql database on a server that has --require_secure_transport=ON.

When i am trying to connect to it with the following code

import pandas as pd
import pymysql
from sqlalchemy import create_engine

connect_string = 'mysql+pymysql://{}:{}@{}/{}'.format(user,pw,host,database)
connector = create_engine(connect_string)   
df = pd.read_csv('iris.data')
df.to_sql('iris',connector,if_exists='replace',index_label='id')

i get the following error

InternalError: (pymysql.err.InternalError) (3159, 'Connections using insecure transport are prohibited while --require_secure_transport=ON.') (Background on this error at: http://sqlalche.me/e/2j85)

I have searched for options into how to make the connector use secure_transport, but i haven't been able to find anything that works.

One solution i found called for adding ?ssl=true to the end of the connect_string

connect_string = 'mysql+pymysql://{}:{}@{}/{}?ssl=true'.format(user,pw,host,database)

But this gave the following error

    ca = sslp.get('ca')

AttributeError: 'str' object has no attribute 'get'

I am able to connect to the database using only pymysql in this way

ssl={'ssl': {'key': 'ssl/client-key.pem','cert': 'ssl/client-cert.pem','ca': 'ssl/ca-cert.pem'}}
con = pymysql.Connect(host,user,pw,database,ssl=ssl)
cur = con.cursor()
cur.execute("show tables;")

But i can't find a way to implement this with the sqlalchemy connector. Anyone have some suggestions on what to do to get this to work?

Regards

pandas sqlalchemy python-3.x ssl python
1 Answers

Figured out the solution after looking around a bit more.

Mysql is usually set up to generate ca files and keys on the fly if it is not supplied to to it, but the issue was sending the correct flag to pymysql so it doesn't go looking for a local ca file.

Found this out here: Unable to make TLS TCP connection to remote MySQL server with PyMySQL, other tools work

You simply pass a dictionary with a "ssl" key that has a dictionary with one key with the value True. I use the keyname "fake_flag_to_enable_tls" for clarity, but it can be anything it seems.

connect_args={'ssl':{'fake_flag_to_enable_tls': True}}
connect_string = 'mysql+pymysql://{}:{}@{}/{}'.format(user,pw,host,database)
connector = create_engine(connect_string,connect_args=connect_args) 
df = pd.read_csv('iris.data')
df.to_sql('iris',connector,if_exists='replace',index_label='id')

Hope someone else finds this useful.

user_85047990
Related