Script for creating tables in mysql using Python

Asked

Viewed 95 times

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.

  • 2

    I believe the table name has to be in single quotes. This is because the name has only numbers.

  • @Paulomarques is not that the problem all variables are of type string, even the CPF having numerical symbols it is a string

  • I’m talking about the same table name. Maybe it’s CREATE TABLE '123' (...

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

  • 1

    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 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.

Show 1 more comment
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.