Python MySQL Create Table

February 1, 2022

In This Article, You Will Know About Python MySQL Table.

Python MySQL Create Table – Before moving ahead, let’s know a bit about Python MySQL Create a Database.

1. Creating a Table

Use “CREATE TABLE” statement to create a table in MySQL.

Don’t forget to create a database before moving ahead.

Define the database name when you create a connection.

Syntax: CREATE TABLE table_name (column_name column_type)

Example: Create a Table named “stu_information”.

				
					import mysql.connector
my_user_details = mysql.connector.connect(host="localhost",
                                          username="your_MySQL_username",
                                          password="Your_MySQL_password",
                                          database="Your_MySQL_database_Name")
database_for_mysql = my_user_details.cursor()

database_for_mysql.execute(
    "CREATE TABLE stu_information (name char(15),class char(4), roll_no int, section char(1))")

Here’s output –

If the above code was executed with no errors, you have now successfully created a table.

CREATE TABLE statement is used to create table.

As a result, we followed the CREATE TABLE syntax by mentioning table_name (stu_information) followed by column_name & column_type.

2. Check if Table Exists

You can check if a table exist by listing all tables in your database with the “SHOW TABLES” statement.

Example: Use the “SHOW TABLES” statement to see all the tables in the database.

				
					import mysql.connector
my_user_details = mysql.connector.connect(host="localhost",
                                          username="your_MySQL_username",
                                          password="Your_MySQL_password",
                                          database="Your_MySQL_database_Name")
database_for_mysql = my_user_details.cursor()

database_for_mysql.execute("SHOW TABLES")

for x in mydatabaseformysql:
    print(x)

Here’s output –

If the above code was executed with no errors, it means table exists in given database.

3. Primary Key

Primary Key identifies each record with the help of unique value.
Primary Key contains only a unique value. In other words, it does not contain a duplicate value.
Primary cannot contain Null Value.
In Table, Primary Key can be assigned to only one column.
A table can have a maximum of one Primary Key.

Example: Create primary Key when creating table.

				
					import mysql.connector
my_user_details = mysql.connector.connect(host="localhost",
                                          username="your_MySQL_username",
                                          password="Your_MySQL_password",
                                          database="YOUR_MYSQL_DATABASE_NAME")

mydatabaseformysql = my_user_details.cursor()

mydatabaseformysql.execute(
    "CREATE TABLE stu_information (number INT AUTO_INCREMENT PRIMARY KEY, name char(15),class char(4), roll_no int, section char(1))")

Here’s output –

AUTO_INCREMENT statement is used to increment value by 1 automatically for each record.

PRIMARY KEY statement is used to give primary key to the table.

As a result, we first followed the “CREATE TABLE” syntax in that we started with mentioning column name and column type in which AUTO_INCREMENT & PRIMARY KEY has to add, in the end, defined the rest of column_name & column_type of the table.

4. Alter Column

Syntax: ALTER TABLE table_name ADD column_name.

Example: Delete column that contains primary key.

				
					import mysql.connector
my_user_details = mysql.connector.connect(host="localhost",
                                          username="your_MySQL_username",
                                          password="Your_MySQL_password",
                                          database="YOUR_MYSQL_DATABASE_NAME")

mydatabaseformysql = my_user_details.cursor()

mydatabaseformysql.execute(
    "ALTER TABLE stu_information DROP COLUMN number")

ALTER statement is used here to modify or to make changes in the table.

DROP statement is used to delete the specified column.

As shown above, we first used ALTER TABLE statement followed by the DROP COLUMN statement to delete a specified column, i.e., number.

Example: Create primary key when table is already existing.

				
					import mysql.connector
my_user_details = mysql.connector.connect(host="localhost",
                                          username="your_MySQL_username",
                                          password="Your_MySQL_password",
                                          database="YOUR_MYSQL_DATABASE_NAME")
database_for_mysql = my_user_details.cursor()

database_for_mysql.execute("ALTER TABLE stu_information ADD COLUMN number INT AUTO_INCREMENT PRIMARY KEY")

ADD COLUMN statement is used to add column in the table.

As a result, we first used ALTER TABLE statement followed by ADD COLUMN statement to add “number” (column) with column_type “INT” and at the end, used AUTO_INCREMENT & PRIMARY KEY to increment value for each record by 1 and added the primary key to “number” column respectively.

If you find anything incorrect in the above-discussed topic and have further questions, please comment below.

Connect on: