Unit 4

Write a program to create a database named “Sample_DB” in MySQL(). [First ensure connection is made or not and then check if the database Sample_DB already exists or not, if yes then print appropriate message

import mysql.connector as mydb

try:
    pydb=mydb.connect(host="localhost",user="root",passwd="")
except:
    print("error in create database")
 
try:
    pycursor=pydb.cursor()
    pycursor.execute("create database d4")
    pycursor.close()
    print("created")
except:
    print("wrong")

Write a program to retrieve and display all the rows in the employee table. [First create an employee table in the Sample_DB with the fields as eid, name, sal . Also enter some valid records]

import mysql.connector as mydb

try:

    pydb=mydb.connect(host="localhost",user="root",passwd="",database="d4")

except:

    print("connection")

try:

    pycursor=pydb.cursor()

    pycursor.execute("select * from t3")

    res = pycursor.fetchall()

    for i in res:

        print(i)

    pycursor.close()

except:

    print("wrong")

Write a program to insert several rows into employee table from the keyboard.

import mysql.connector as mydb

try:

    pydb=mydb.connect(host="localhost",user="root",passwd="",database="d4")

except:

    print("connection")

#try:

''' pycursor=pydb.cursor()

    pycursor.execute("CREATE TABLE t3 (id int(5) , name varchar(255) , address varchar(255))")

    pycursor.close()

    pycursor=pydb.cursor()

    pycursor.execute("Show tables")

    for i in pycursor:

        print(i)

    pycursor.close()

  '''

try:

    pycursor=pydb.cursor()

    pycursor.execute("insert into t3 values (1,'a','vatva')")

    pydb.commit()

    pycursor.close()

    print("inserted")

except:

    print("wrong")

Write a program to delete a row from an employee table by accepting the employee identity number (eid) from the user.

import mysql.connector as mydb

try:

    pydb=mydb.connect(host="localhost",user="root",passwd="",database="d4")

except:

    print("connection")

try:

    pycursor=pydb.cursor()

    pycursor.execute("delete from t3 where id=2")

    pydb.commit()

    pycursor.close()

except:

    print("wrong")

Write a program to increase the salary (sal) of an employee in the employee table by accepting the employee identity number (eid) from the user.

import mysql.connector as mydb

try:

    pydb=mydb.connect(host="localhost",user="root",passwd="",database="d4")

except:

    print("connection")

try:

    pycursor=pydb.cursor()

    pycursor.execute("update t3 set name='vicky' where name='a'")

    pydb.commit()

    pycursor.close()

except:

    print("wrong")

Write a program to create a table named new_employee_tbl with the fields eno , ename , gender and salary in Sample_DB database. The datatypes of the fields are eno-int, ename-char(30), gender-char(1) and salary-float.

import mysql.connector as mydb

#(eno int(5) , ename varchar(10), gender varchar(5), sal int(7))

def create_tb():

    try:

        pycursor=pydb.cursor()

        pycursor.execute("create table t6 (eno int(5) , ename varchar(10), gender varchar(5), sal int(7))")

        pycursor.close()

    except:

        print("wrong in table create")

    else:

        print("created")

#start

        

try:

    pydb=mydb.connect(host="localhost",user="root",passwd="",database="d4")

except:

    print("connection wrong")

else:

    try:

        create_tb()

    except:

        print("wrong")