25/07/2021

Create MySQL Table Using Python

In this second part we will learn how to CREATE a TABLE and to use the SELECT. 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: https://blog.nissaar.com/using-mysql-with-python-part1

By now your code should at least look like this:

create-db-method2
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:

CREATE TABLE tablename (
column1 datatype(length),
column2 datatype(length),
column3 datatype(length),
...
);

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).

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.

create-db-method2

In this method everything is in the code.

Follow this link for another method to create a table which takes Table Name, Column Names and Data Types as input from the user:

https://blog.nissaar.com/create-mysql-table-using-python-taking-inputs-from-user

Leave a Reply

Your email address will not be published.