Creating a table using mysql python with variable values

Question

I'm trying to create a table in MySQL using python-flask. The table name is the 'id' parameter, which I pull from my server. I want this id to be the name of the table that I'm creating, but when I try to do so, I get the following error:

'mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '46490210 (user varchar(50), message varchar(150))' at line 1'

Here's the python-sql code for reference.

#the file name is chat_db.py

def createTable(id):
    cursor = db.cursor()
    cursor.execute("CREATE TABLE IF NOT EXISTS %s (user varchar(50), message varchar(150))",(id,))
    db.commit()

Here's the Flask code (without the decorators):

import chat_db as roomdb 
import random

def rand_id():
    return random.randint(10000000,99999999)

def roomTable():
    roomdb.createTable(rand_id())

roomTable()

Please help out! :)

that is not allowed in mysql. You have to put a letter before tehnumber or such — nbk
– nbk, Commented May 15, 2021 at 11:30
@nbk That did not rectify my problem. I made the following change in the 'roomTable' function: roomdb.createTable('a'+str(rand_id())) (added 'a' to the beginning of the id) Yet I still receive the following error: "mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''a93937637' (user varchar(50), message varchar(150))' at line 1". — BlazeRod11
– BlazeRod11, Commented May 15, 2021 at 11:47
@nbk If I misinterpreted your solution, please provide a solution with reference to the code I used in the question. Thanks! — BlazeRod11
– BlazeRod11, Commented May 15, 2021 at 11:59

nbk · Accepted Answer · 2021-05-15 12:08:06Z

1

Onlym Number aren't alowwed in mysql, so you need a Letter before the number In my example i used the Letter A, but it can be of course any Text you like.

But i don't see why you want to do such a thing in a loop.

def createTable(id):
    cursor = cnx.cursor()
    cursor.execute("CREATE TABLE IF NOT EXISTS A%s (user varchar(50), message varchar(150))",(id,))
    cnx.commit()

answered May 15, 2021 at 12:08

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BlazeRod11 Over a year ago

Thanks, this worked out. However, I'm curious to know why this modification to my original code didn't work out: roomdb.createTable('a'+str(rand_id()))

nbk Over a year ago

your code adds '(single quote) to the text which mysql doesn't like, when you have a number it doesn't

Collectives™ on Stack Overflow

Creating a table using mysql python with variable values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related