1
I intend to create a script to create tables in mysql using Python 3 to register users, each table will have the name of the person’s CPF, however there is an error syntax (1064) I couldn’t identify. All variables are string type
The script I have so far is:
import mysql.connector
Database = mysql.connector.connect(
host='localhost',
user='root',
passwd='',
database='datacenter'
)
def cadastra_db(nome,cpf,email,cargo,setor):
cursor = Database.cursor()
comando_createtable = f"CREATE TABLE {cpf}(\nid INT(6) NOT NULL AUTO_INCREMENT,\nnome VARCHAR(50),\ncargo VARCHAR(50),\nsetor VARCHAR(50),\nemail VARCHAR(50),\ncpf VARCHAR(50),\nPRIMARY KEY(id)\n);"
print(comando_createtable)
cursor.execute(comando_createtable)
Database.commit()
In the print(comando_creatable)
the exit is :
CREATE TABLE 123(
id INT(6) NOT NULL AUTO_INCREMENT,
nome VARCHAR(50),
cargo VARCHAR(50),
setor VARCHAR(50),
email VARCHAR(50),
cpf VARCHAR(50),
PRIMARY KEY(id)
);
Based on the output of the print I do not know what is happening so that the command does not right.
I believe the table name has to be in single quotes. This is because the name has only numbers.
– Paulo Marques
@Paulomarques is not that the problem all variables are of type string, even the CPF having numerical symbols it is a string
– LucasRussi
I’m talking about the same table name. Maybe it’s
CREATE TABLE '123' (...
– Paulo Marques
It makes sense what Paulo Marques is talking about. When calling an SQL command via IDE we use different quotes to pass Strings to the bank. In your case it should be done in the variable Cpf. Your command would be: f"CREATE TABLE '{cpf}'(\nid INT(6) NOT NULL AUTO_INCREMENT,\nnome VARCHAR(50),\ncargo VARCHAR(50),\nsetor VARCHAR(50),\nemail VARCHAR(50),\ncpf VARCHAR(50),\nPRIMARY KEY(id)\n);"
– Octávio Lage
The right is to use backtick (`) around the number. But the name of the table is the person’s Cpf? That doesn’t make much sense to me.
– AlexCiuffa
@Alexciuffa very correct your answer the use of backtick has fixed my problem, thank you very much for your help. Just to make some sense the CPF gives person will serve as an identifier because each collaborator will have its unique table.
– LucasRussi