I have a .sql file and I want to know if we can execute it directly in python using mysql.connector python class provided my mysql community.
Usually I can execute the .sql file in mysql server in terminal using these queries,
CREATE DATABASE test;
USE test;
source data.sql;
but I want to do it in python directly. Is it possible?
Yes you just need to read the file then execute the string contents on the cursor... something like this
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword"
)
with open('something.sql', 'r') as f:
with mydb.cursor() as cursor:
cursor.execute(f.read(), multi=True)
mydb.commit()
multi=True in the execute() method call. dev.mysql.com/doc/connector-python/en/…show tables: query? should I use mydb.execure('show tables:')
cursor.execute("some sql command") but then you would need to fetch the results. results = cursor.fetchall() here results should be an iterable you can look through
mydb.commit(). If I include mydb.commit(), then I get the error message mysql .commit() is out of sync; you can't run this command now (this SO question). If I exclude mydb.commit(), then this approach seems to work.