Forums

Hi guys, I am not sure what I'm doing wrong here...bit of a newbie, but seems I am suffering from this OperationError 2006 like many before me. What is different is that it seems to only occur when I execute on the Cursor within Python? I am using Jupyter Notebook on my local PC.

I think I have connected to the db fine, as the code does not throw any errors - but can't proceed to execute anything to the db.

Any help would be greatly appreciated!

How long is the query taking? It may be timing out before it gets its results.

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!

I'm not sure why you're getting that exact error, but you do need to indent the df.to_sql and the connection.close() lines to the same level as the connection = ... line. They have to run inside the scope of the SSHTunnelForwarder in order to work.

Thanks Giles! Unfortunately, that didn't fix things so back to the drawing board for me. At the end of the day, all I need to do is create a SQL database from a csv file / dataframe (not too picky!). Any resources you could point me to would be greatly appreciated but I'm sure I should find something on this forum soon enough.

I just googled the error message -- according to this Stack Overflow answer, it looks like you might get somewhere by adding flavor='mysql' to your call to to_sql...?

If not then yes, I think you might need to switch over to using SQLAlchemy.