Hey Glenn, thanks for getting back to me so quick. I used this code block as found on your site.
import mysql.connector
import sshtunnel
sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0
with sshtunnel.SSHTunnelForwarder(
('ssh.pythonanywhere.com'),
ssh_username='your PythonAnywhere username', ssh_password='the password you use to log in to the PythonAnywhere website',
remote_bind_address=('your PythonAnywhere database hostname, eg. yourusername.mysql.pythonanywhere-services.com', 3306)
) as tunnel:
connection = mysql.connector.connect(
user='your PythonAnywhere username', password='your PythonAnywhere database password',
host='127.0.0.1', port=tunnel.local_bind_port,
database='your database name, eg yourusername$mydatabase',
)
# Do stuff
connection.close()
Question is now... I basically want to create (and subsequently update..) a MySQL db by feeding it pandas dataframes. Do I need to use SQL Alchemy to do this?
I have failed so far with the following block, getting the following error.
sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0
with sshtunnel.SSHTunnelForwarder(
('ssh.pythonanywhere.com', 22),
ssh_username='xx', ssh_password='xxxxxx',
remote_bind_address=('xx.mysql.pythonanywhere-services.com', 3306)
) as tunnel:
connection = MySQLdb.connect(
user='xxx', password='xxx',
host='127.0.0.1', port=tunnel.local_bind_port,
database='xxx$xxx',
)
df.to_sql('test2', connection, schema=None, if_exists='fail', index=False, index_label=None, chunksize=None, dtype=None)
connection.close()
This pushes the below error:
DatabaseError: Execution failed on sql: SELECT name FROM sqlite_master WHERE type='table' AND name=?;
not all arguments converted during string formatting
unable to rollback
Thanks for all your help!