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