Python MySQL - Create Table

The CREATE TABLE statement is used to create tables in MYSQL database. Here, you need to specify the name of the table and, definition (name and datatype) of each column.

Syntax

Following is the syntax to create a table in MySQL −

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

Example

Following query creates a table named EMPLOYEE in MySQL with five columns namely, FIRST_NAME, LAST_NAME, AGE, SEX and, INCOME.

mysql> CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT);
Query OK, 0 rows affected (0.42 sec)

The DESC statement gives you the description of the specified table. Using this you can verify if the table has been created or not as shown below −

mysql> Desc Employee;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| FIRST_NAME | char(20) | NO   |     | NULL    |       |
| LAST_NAME  | char(20) | YES  |     | NULL    |       |
| AGE        | int(11)  | YES  |     | NULL    |       |
| SEX        | char(1)  | YES  |     | NULL    |       |  
| INCOME     | float    | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
5 rows in set (0.07 sec)

Creating a Table in MySQL Using Python

The method named execute() (invoked on the cursor object) accepts two variables−

  • A String value representing the query to be executed.

  • An optional args parameter which can be a tuple or, list or, dictionary, representing the parameters of the query (values of the place holders).

It returns an integer value representing the number of rows effected by the query.

Once a database connection is established, you can create tables by passing the CREATE TABLE query to the execute() method.

In short, to create a table using python −

  • Import mysql.connector package.

  • Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.

  • Create a cursor object by invoking the cursor() method on the connection object created above.

  • Then, execute the CREATE TABLE statement by passing it as a parameter to the execute() method.

Example

Following example creates a table named Employee in the database mydb.

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(
   user='root', password='password', host='127.0.0.1', database='mydb'
)

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Dropping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

#Creating table as per requirement
sql ='''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
)'''
cursor.execute(sql)

#Closing the connection
conn.close()
Advertisements