Python Forum

I pass the database name from form to the cdb.py it is successful, but the mariadb query "CREATE DATABASE ?" cannot get the value i think so, this is cdb.py

# Get Cursor
cur = conn.cursor()
# creating database 
cur.execute("CREATE DATABASE ?", (val,)) 
  
cur.execute("SHOW DATABASES")
databaseList = cur.fetchall()
  
for database in databaseList:
  print(database)
    
conn.close()

when i run the cdb.py directly from command line this is the error:

<!-- The above is a description of an error in a Python program, formatted
     for a Web browser because the 'cgitb' module was enabled.  In case you
     are not reading this in a Web browser, here is the original traceback:

Traceback (most recent call last):
  File "/srv/http/cgi-bin/./cdb.py", line 31, in &lt;module&gt;
    cur.execute("CREATE DATABASE ?", (val,))
mariadb.ProgrammingError: 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 '?' at line 1

-->

This is the error printing in web browser when i run from the web browser:

 /srv/http/cgi-bin/cdb.py in <module>
     29 cur = conn.cursor()
     30 # creating database 
=>   31 cur.execute("CREATE DATABASE ?", (val,)) 
     32   
     33 cur.execute("SHOW DATABASES")
cur = <mariadb.connection.cursor object>, cur.execute = <built-in method execute of mariadb.connection.cursor object>, val = 'mydb'

ProgrammingError: 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 '?' at line 1
      args = ("You have an error in your SQL syntax; check the ...on for the right syntax to use near '?' at line 1",)
      errmsg = "You have an error in your SQL syntax; check the ...on for the right syntax to use near '?' at line 1"
      errno = 1064
      msg = "You have an error in your SQL syntax; check the ...on for the right syntax to use near '?' at line 1"
      sqlstate = '42000'
      with_traceback = <built-in method with_traceback of ProgrammingError object> 

Hi @shams,
I have no MariaDB so I cannot check it but as far as I know the use of placeholders ( ? ) in a query is limited to DML statements. (DML = Data Manipulation Language, so only INSERT, UPDATE, DELETE).
So in the case of "Create database" you must provide the complete statement.

I was trying something like this recently. I wanted to put the name of the db and weeknr both as %s in a SELECT command. No go.

This (val,) puts the string, the name of your db in the CREATE command, but it will have ordinary apostrophes, i.e. ' around it. This is not acceptable in SQL.

Gotta be those funny backstroke apostrophes!

I haven't found a way around this yet! If you find one, I'd be glad to hear it!

This worked for me

sql = f"SELECT studentnr, score FROM allstudentsAnswers{clas} WHERE weeknr = %s"
cur.execute(sql, (weeknr,))

I know it is frowned upon, but sometimes you just have to revert to building the string yourself.

cur.execute(f"CREATE DATABASE {val}")