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?

`source FileName.sql` is not a valid SQL statement. So I am not surprised it is not working. — Burhan Khalid, Feb 23 '15 at 07:08
possible duplicate of [execute \*.sql file with python MySQLdb](http://stackoverflow.com/questions/4408714/execute-sql-file-with-python-mysqldb) — Ozgur Vatansever, Feb 23 '15 at 07:12
but when I am executing this SQL statement outside python script, then its working. — Yamini Gera, Feb 23 '15 at 07:13

Iskren · Answer 1 · 2015-02-23T10:59:09.050

4

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()

edited Feb 23 '15 at 10:59

answered Feb 23 '15 at 07:15

Iskren

1,301
10
15

its giving below error: File "test6.py", line 5, in 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:/# – Yamini Gera Feb 23 '15 at 09:16
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. – Iskren Feb 23 '15 at 11:04

score 2 · Answer 2 · answered Feb 14 '19 at 17:48

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

score 1 · Answer 3 · answered Mar 26 '19 at 18:57

Separate the scripts in SQL file in python using ";" as delimiter
Execute each command iteratively.
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()

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

3 Answers3