0

I try to write a SQL Script that creates a database and tables, whereby the name of the database is given as a parameter. I use mariadb and the perfect scenario would be to call the script by python and providing the name of the database as parameter.

I tried different things:

  1. Defining a variable in a SQL script and use the variable in the create statement:
SET @dbname = 'my_db';

-- SELECT @dbname;

CREATE DATABASE @dbname;

The select statement was just to test, if the variable was assigned corretly. As expected the select return 'my_db' whereby the create database statement fails with an SQL error new '@dbname'

  1. Call from Python with parameters:

I created a SQL file with the create statement and the db_name as a parameter:

create database %(dbname)s

I have found no good way on how to run the sql file, so I read the content from a file and execute the statement:

sql_statement = ''
for line in open('create_db.sql'):
        sql_statement += line

Then I used the python mariadb connector and after establishing a connection and getting the cursor I use the statement:

cur.execute(sql_statement, data={'dbname': 'my_db'})

Here I have to problem, that the variable for the database is replaced with the string, so the statement transforms to

create database 'my_db'

and I get the a SQL error near 'my_db' (So the variable was at least replaced, but the '' are causing an error).

Question:

What is the best pratice for a SQL script, where some parts of the script (expressions but also identifiers for tables and databases) are provided by a variable?

My best solution was to use string manipulation:

sql_statement = sql_statement.replace('%(dbname)s', 'my_db')

and then execute the statement:

cur.execute(sql_statement)

But somehow this seems not elegant. Are there better solutions? In my opinion this is a realy simple usecase and I am surprised, that it seems to be so hard.

Thanks.

user22254901
  • 9
  • 2

0 Answers0