Python: MySQL Create Table

MySQL is a Relational Database Management System (RDBMS) whereas the structured Query Language (SQL) is the language used for handling the RDBMS using commands i.e Creating, Inserting, Updating and Deleting (CRUD) the data from the databases. SQL commands are case insensitive i.e "CREATE" and "create" signify the same command.

Connector Module

1. connect(): used to establish a connection with the MySQL server. It takes the following arguments:

• user: Username associated with the MySQL server.
• password: Password for authentication.
• database: The database to connect to.

2. cursor(): creates a cursor in the system memory. The cursor is the workspace for executing SQL commands. It remains open for the entire session.

3. execute(): takes an SQL query as an argument and executes it. You can use this method to perform operations like creating, inserting, updating, or deleting data.

Creating Database

A database is an organized collection of data stored in tables. These tables are designed to make data manipulation (like creation, insertion, updates, and deletions) easy and efficient.

Syntax:

CREATE DATABASE database_name;

Example: Creating a database called "College".

import mysql.connector as SQLC

DataBase = SQLC.connect(
  host="localhost",
  user="root",
  password="your_password"
)

Cursor = DataBase.cursor()
Cursor.execute("CREATE DATABASE College")
print("College database is created")

Output

College Data base is created

Note: Make sure to enter your own MySQL user and password to connect with MySQL server.

Table

The table is a collection of data organized in the form of rows and columns.

• Rows are also called tuples
• Columns are called the attributes of the table

Syntax:

As we have discussed that tables are created inside a database so we need to first make sure to select or move into to the database before creating a table. To select the "College" database use this command:

USE college;

This will select the "College" database, now we can proceed to create tables inside it. Here's how to create a table:

CREATE TABLE table_name
(
column_name_1 column_Data_type,
column_name_2 column_Data_type,
:
:
column_name_n column_Data_type
);

SQL Data Types:

• Numeric: INT, FLOAT, DOUBLE
• Character/String: VARCHAR, CHAR, TEXT
• Date/Time: DATE, TIME, DATETIME
• Unicode: NCHAR, NVARCHAR
• Binary: BLOB

For instance, let's suppose we have to create a table named "STUDENT" having two columns, here's how we can do it:

CREATE TABLE Student (
Name VARCHAR(255),
Roll_no INT
);

Creating a Table

Let’s consider an example where we create a table named Student inside the "College" database. The Student table will have two columns: Name (for the student's name) and Roll_no (for the student's roll number).

import mysql.connector as SQLC

def CreateTable():
    DataBase = SQLC.connect(
        host="localhost", 
        user="root",       
        password="your_password", 
        database="College" 
    )

    Cursor = DataBase.cursor()
    TableName = """CREATE TABLE Student (
                    Name VARCHAR(255),
                    Roll_no INT
                  );"""

    Cursor.execute(TableName)
    print("Student Table is Created in the Database")

CreateTable()

Output

Student Table is Created in the Database