How can I execute "source FileName.sql" in a python script?

Question

I would like to execute the MySQL query source FileName.sql in a Python script on Linux.

I am able to execute other queries like SELECT * FROM table_name but this one is giving an error. I am executing this on a Linux server with a MySQL database using Python. The frontend I am using is Putty.

The Python script I have used is:

import MySQLdb
db = MySQLdb.connect("hostname","username","pswrd","dbname")
cursor = db.cursor()
cursor.execute("source FileName.sql")
db.close()

How can I execute the query source FileName.sql on the location where this file-> FileName.sql is located?

Solution 1

source is not a SQL command. It's a MySQL CLI command, it only exists in the console application mysql (and wherever else implemented). All it does is to read the contents of FileName.sql and issue the SQL commands inside.

To do this in python, you can use something like

Edit: This assumes you have 1 query per line! If you have multi-line queries, you'll have to find the means to extract each query from the file.

import MySQLdb
db = MySQLdb.connect("hostname","user","pass","db")
cursor = db.cursor()
for line in open("FileName.sql"):
    cursor.execute(line)
db.close()

Solution 2

You can execute a bash command with Python and import your SQL file.

This exemple is for MySQL

import subprocess
command = "mysql -u username --password=p@55W0rD database_name < file.sql".split()
p = subprocess.Popen(command, stdout=subprocess.PIPE)
p.communicate()  # you can see if errors are returned

if your SQL file creates a database, remove database_name.

sources:

https://docs.python.org/3/library/subprocess.html#popen-constructor https://dev.mysql.com/doc/refman/8.0/en/mysql-batch-commands.html

Solution 3

1. Separate the scripts in SQL file in python using ";" as delimiter
2. Execute each command iteratively.
3. awesome5team had developed a nice solution in https://github.com/awesome5team/General-Resources-Box/issues/7

Code snippet from the same:

import mysql.connector
cnx = mysql.connector.connect(user='root',
                             password='YOUR-PASSWORD-FOR-MYSQL',
                             host='localhost',
                             database='YOUR-DATABASE-NAME')
cursor =cnx.cursor()

def executeScriptsFromFile(filename):
    fd = open(filename, 'r')
    sqlFile = fd.read()
    fd.close()
    sqlCommands = sqlFile.split(';')

    for command in sqlCommands:
        try:
            if command.strip() != '':
                cursor.execute(command)
        except IOError, msg:
            print "Command skipped: ", msg

executeScriptsFromFile('SQL-FILE-LOCATION')
cnx.commit()

Comments

[Yamini Gera]

its giving below error: File "test6.py", line 5, in <module> cursor.execute(line) File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute self.errorhandler(self, exc, value) File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/*' at line 1") root@ubuntu:/#

[Iskren]

I've added a comment in the answer, see above. Also, if you have any external commands for the mysql CLI tool it won't work.