Learn Python – Creating the table- Basic and advance

In this part of the tutorial, we will create the new table Employee. We have to point out the database title while organising the connection object.

We can create the new table by means of using the CREATE TABLE announcement of SQL. In our database PythonDB, the desk Employee will have the 4 columns, i.e., name, id, salary, and department_id initially.

The following query is used to create the new desk Employee.

>  create table Employee (name varchar(20) not null, id int primary key, salary float not null, Dept_Id int not null)  

Example

import mysql.connector  
  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
  
#creating the cursor object  
cur = myconn.cursor()  
  
try:  
    #Creating a table with name Employee having four columns i.e., name, id, salary, and department id  
    dbs = cur.execute("create table Employee(name varchar(20) not null, id int(20) not null primary key, salary float not null, Dept_id int not null)")  
except:  
    myconn.rollback()  
  
myconn.close()  

Now, we may test that the desk Employee is current in the database.

Alter Table

Sometimes, we can also forget about to create some columns, or we can also need to replace the table schema. The alter statement used to alter the desk schema if required. Here, we will add the column branch_name to the desk Employee. The following SQL query is used for this purpose.

alter table Employee add branch_name varchar(20) not null  

Consider the following example.

Example

import mysql.connector  
  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
  
#creating the cursor object  
cur = myconn.cursor()  
  
try:  
    #adding a column branch name to the table Employee  
    cur.execute("alter table Employee add branch_name varchar(20) not null")  
except:  
    myconn.rollback()  
  
myconn.close()