phpmyadmin Question posted in
The official documentation can be found here.

Can't recreate dropped tables to any MySQL database using Python and PHPMyAdmin

Our class has an assignment to create 3 tables in a database using PyCharm for programming the .py code and PHPMyAdmin to view said tables.

I was able to create the three tables on my database through Python programming on the first try. However, our programming professor required us to screen record our output, so I dropped my tables and tried to re-run my .py file and recreate them so that I can show in the screen recording that my .py code does work, but they won’t create. I tried:

  1. Changing the name of the database
  2. Changing the name of tables
  3. Creating the tables on test database, but the tables are not getting created.

My code has a try and except that displays an SQL error if it happens to cross by one, however in all cases above, the SQL queries have shown to be successful. When I check the tables in PHPMyAdmin to verify, however, the database returns 0 tables.

This is my conn file

import mysql.connector  # allows to connect to db
from mysql.connector import Error  # easily get error

try:
    conx = mysql.connector.connect(host='127.0.0.1', database="_dbmidtermlab_maceren", user='root', password='')
    if conx.is_connected():
        db_info = conx.get_server_info()  # get info about sql server
        print("Connected to MYSQL Server Version: ", db_info)
        cursor = conx.cursor()
        cursor.execute("select database();")
        db = cursor.fetchone()
        print("You're connected to database: ", db)
except Error as e:
    print("Error connecting to MYSQL", e)

My Create Tables file

from conn import conx
from mysql.connector import Error

try:
    createTable = """CREATE TABLE Order (OrderNum varchar(5) NOT NULL ,OrderDate date NULL,CustomerNum VARCHAR(3) NULL, PRIMARY KEY (OrderNum)); 
                     CREATE TABLE Parts (PartNum varchar(4) NOT NULL ,Description varchar(15) NULL,onHand decimal(4,0) NULL, Class varchar(2) NULL,Warehouse varchar(1) NULL, Price decimal(6,2) NULL, PRIMARY KEY (PartNum)); 
                     CREATE TABLE Customers (CustomerNum varchar(3) NOT NULL ,CustomerName varchar(35) NOT NULL,Street varchar(15) NULL, City varchar(15) NULL,State varchar(2) NULL, Zip varchar(5) NULL,  Balance decimal(8,2) NULL,  CreditLimit decimal(8,2) NULL,  RepNum varchar(2) NULL, PRIMARY KEY (CustomerNum)); """
    cursor = conx.cursor() #cursor allows to execute query (simiplay to mysqli_query())
    result = cursor.execute(createTable, multi=True)
    conx.commit()
    #multi=True allows multiple queries in one variable

    print("Tables created successfully")
except Error as error:
    print("Failed to create table in MySQL: {}".format(error))
#finally:
 #   if conx.is_connected():
  #      cursor.close()
   ##    print("MySQL connection is closed")

Based on what I’ve Googled, apparently dropping the tables was a bad idea. It somehow affected the functionality? What should I do?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you so much to @Barmar and @IODEV!

    What solved my problem:

    1. Separating my CREATE TABLE queries as suggested by @Barmar
    2. Adding `` to the ORDER table as suggested by @IODEV

    Still wondering why my multi-query worked the first time and bugged out afterwards, but for now, my project is finally working! Thank you!


  2. 0

    The issue is that MySQL requires backticks for identifiers if the name used is a reserved keyword. In this case table name Order is a reserved keyword (ie sql order by…) thus you have to use backticks around Order ie:

    CREATE TABLE `Order` (OrderNum varchar(5) NOT NULL, OrderDate date NULL, CustomerNum VARCHAR(3) NULL, PRIMARY KEY (OrderNum));

    In order to make it easier to trace similar problems in the future I would suggest you execute the SQL DDL-statements separately ie one cursor.execute for each create table ...

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search