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