0

I'm creating a python script to delete, create and import a database using python

sql file location : /home/myname/Downloads/db/my_db_file.sql

python file location : /var/www/dev-django/my_python_file.py

my file is as follows:

from MySQLdb import _mysql

host = '127.0.0.1'
user = 'test1'
passwd = '123123'
file_location = '/home/myname/Downloads/db/my_db_file.sql'
db_name = 'my_db'

db=_mysql.connect(host,user,passwd)

db.query("""DROP DATABASE """ + db_name + """;""")
print('db deleted')
db.query("""CREATE DATABASE """ + db_name + """;""")
print('db created again')    
db.query("""use """ + db_name + """;""")
print('using the db')
db.query("""SET autocommit=0 ; """)
db.query(""" source """ + file_location + """ ; """)
db.query(""" COMMIT ; """)
print('done')

on running the file, i get the following error:

(venv) [root@XXXX some_folder]# python create_db_for_testing.py 
db deleted
db created again
using the db
Traceback (most recent call last):
  File "create_db_for_testing.py", line 18, in <module>
    db.query(""" source """ + file_location + """ ; """)
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'source /home/myname/Downloads/db/my_db_file.sql ; COMMIT' at line 1")
Irfan Harun
  • 979
  • 2
  • 16
  • 37
  • 1
    You cannot execute multiple statements separated by `;` with `MySqldb`. And there is no reason to terminate your statements with `;`. And the title of your question does not really fit the problem you are having; you are having a syntax error, but why? – Booboo Aug 19 '20 at 17:19
  • @Booboo thank you for the inputs. I changed the last line into 3 separate lines, yet i'm facing the same error – Irfan Harun Aug 19 '20 at 18:17
  • 1
    I would say you need to update your question with updated source and an updated trace, But ultimately I believe the "source" command is strictly a MySql Consile command that causes its input to be temporarily redirected to a file and is not going to be understood by the actual MySql engine. – Booboo Aug 19 '20 at 18:18

0 Answers0