Search code examples
pythonmysqllinuxterminalcommand

Execute mysql commands in linux terminal using Python

I would like to automate the setup of a database and I would like to do it by using a python script to execute some commands in a linux terminal.

But I cannot see any way of executing commands in the terminal after connection to mysql database.

Below you can see a part of the script:

from time import sleep
from os import system

print("Setting up the database...\n")
system("sudo mysql -u root")
sleep(2)

This starts mysql, and after this none of the commands I try to execute from python get executed.

For example, I would like to run commands like these:

system("CREATE DATABASE mydatabase;")
system("CREATE USER 'user'@'localhost' IDENTIFIED BY '[PASSWORD]';")
system("GRANT ALL PRIVILEGES ON mydatabase.* TO 'user'@'localhost';")

Is this possible?

Edit 1: Potential solution as suggested by @AndHeFallen:

In python I create an sql file named db.sql, then run this in the terminal:

with open("db.sql","w") as db:
    db.write("CREATE DATABASE mydatabase;\n")
    db.write("CREATE USER 'user'@'localhost' IDENTIFIED BY '[PASSWORD]';\n")
    db.write("GRANT ALL PRIVILEGES ON mydatabase.* TO 'user'@'localhost';\n")

system("sudo mysql -u root < db.sql")
Solution

  • I don't think it's possible to do that. If you want to interact with your MySQL database with a Python script, you'll need to connect directly to your db with an api like MySQLdb. (I may be wrong but the way you want to do may cause security issues)