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

Answer

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)

Advertisement

Answer