Create MySQL Table Using Python

In this part, we will learn how to CREATE a TABLE. There is a first post which explains how to connect to the MySQL Server and how to Create a Database which are the two first steps of this series. If you have not yet gone through the first part follow this link: Connect Python to MySQL Server and Create Database

By now your code should at least look like this:

And now we will proceed further.

Create Table

The CREATE TABLE statement is used to create a table in the database. The SQL Syntax is as follows:

/*tablename is the name to be given to the table
column1, column2, column3 are the names to be given to the columns
datatype is just as the name states (type of data) which the columns will hold; they can be characters, numbers, date etc... They are specified with the maximum length of characters (maximum is 255).*/
CREATE TABLE tablename ( 
column1 datatype(length),
column2 datatype(length),
column3 datatype(length),
...
);

Usually there must be a column which will hold the PRIMARY KEY which is a UNIQUE value. Many prefer to name the column as id. When creating the table it must be specified which column will hold the PRIMARY KEY.

We will take as example the Bio of a person to proceed.

Create table using Python

The following code will go below the codes in the picture at the top.

sql = "CREATE TABLE test1 (id INT AUTO_INCREMENT PRIMARY KEY, Last_Name varchar(255), First_Name varchar(255), Age int, Country varchar(255) );"
mycursor.execute(sql)

In this case a table with columns: Last_name, First_Name, Age, Country and there respective datatypes will be created.

The primary key in this case is id with datatype integer (int), AUTO_INCREMENT is used so that it will not be necessary to specify the primary key each time, it will automatically be incremented to have a unique value. A table like this should have been created.

In this method everything is in the code.