Python MySQL Create Table


Use the statement "CREATE TABLE" to create a MySQL table.

When you create your connection, make sure that you set the name of the database

We'll name the table "books" and give it three columns : "id", "name", and "price" :

CREATE TABLE books (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30),
price FLOAT(5, 2)
)

Notes on the table above :

You can choose additional optional attributes for each column after data type like AUTO INCREMENT, PRIMARY KEY, DEFAULT, NOT NULL and etc.

A main key column should be present in every table like above table (in this case: the "id" column). For each record in the table, its value must be different.

Example :- Create a table named "books" :

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

qry = """CREATE TABLE books (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30),
price FLOAT(5, 2)
)"""

mycursor.execute(qry)

print("Table created successfully!")

Output :-

Table created successfully!

If you did not perform the preceding code without errors, you now built a table successfully.

You can also search for these topics, python mysql create table if not exists, primary key to create table using python mysql, python mysql example for create table, how to create table with mysql generator, working of python mysql function to create table, check the memory to create a table in python mysql, option to create a python mysql database, check the python temporary table to create table.

Check if Table Exists

The "SHOW TABLES" statement allows you to check if a table exists by listing all tables of your database.

Example :- List all tables from "mydatabase" :

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SHOW TABLES")

for x in mycursor:
     print(x)

Output :-

('books',)

You can also search for these topics, databricks python check if table exists, how to empty the table existing using python, working of element and function to check if table exists, python how to check if table exists, table keyword to check the exist using python, how to locate the python existing table, python rename the existing table, get the value with open the python exists table.

Primary Key

When constructing a table, a column with a single key for each entry should also be created.

This can be achieved when a PRIMARY KEY is defined.

We have used the statement "AUTO_INCREMENT PRIMARY KEY" for id column, which inserts for each entry a unique number. Starting at 1, each record was incremented by one.

Use the ALTER TABLE Keyword if it already exists.

You can also search for these topics, python primary key definition, define the python primary key code, use the primary exception key using python, access the field of python primary key, python function to generate primary key, primary key how to use in python, python primary key identity, Example for python primary key.

Delete a Table

The "DROP TABLE" statement lets you remove an existing table from mysql database.

Example :- Delete the table "books" :

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "DROP TABLE books"

mycursor.execute(sql)

print("Table deleted!")

Output :-

Table deleted!

You can also search for these topics, python delete a table all the files, entry the delete table record in python, python environment to delete a table, delete a table in python mysql keys, list of files in the python delete table, python table of delete record contents, unused variables using python delete a table, example for python delete a table.

Drop Table Only if Exist

You can use the IF EXISTS keyword to avoid an error, if the table you want to delete is previously deleted or no other reason exists.

Example :- When it exists, remove the table "books" :

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "DROP TABLE IF EXISTS books"

mycursor.execute(sql)

Output :-

Table deleted!

You can also search for these topics, python drop only if exist it doesnt, keys to drop exist table in python, open file to drop only python table if exist, update the python drop only if exist, Example for drop only if exist using python.