XII CS Unit 3 Notes
XII CS Unit 3 Notes
Note: COUNT(*) will count all the rows in the table, including
NULL values. On the other hand, COUNT(column name) will
count all the rows in the specified column while excluding
NULL values
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root", # Example: change table structure (add, edit, remove column
passwd=",database=“school”) of a table)
print(mydb)
import mysql.connector
After successful execution of above statements in python mydb=mysql.connector.connect(host="localhost",user="root",
following out will be displayed passwd="",database= "student")
<mysql.connector.connection.MySQLConnection object at mycursor=mydb.cursor()
0x022624F0> mycursor.execute("alter table emp add (bonus int(3))")
mycursor.execute("desc emp")
import mysql.connector for x in mycursor:
mydb=mysql.connector.connect(host="localhost",user="root", print(x)
passwd="", database="school")
print(mydb) # Example: Insert record in a table
if mydb.is_connected():
print("working!!") import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",
is_connected() function returns true if connection is passwd="",database= "student")
established otherwise false mycursor=mydb.cursor()
“mys” is an alias of package “mysql.connector” eno=int(input("Enter eno"))
“mycon” is connection object which stores connection ename=input("Enter ename")
established with MySQL edept=input("Enter dept")
Connect() functions is used to establish connection with given sal=int(input("Enter salary"))
parameters. sql=("insert into emp (eno, ename, edept, sal)
values(%s,%s,%s,%s )
Cursor object: val=(101,’Neelu’,’HR’,35000)
The MySQLCursor class instantiates objects that can execute mycursor.execute(sql,val)
operations such as SQL statements. Cursor objects interact
with the MySQL server using a MySQLConnection object. or
sql="insert into emp (eno, ename, edept, sal)
Cursor stores all the data as a temporary container of values({},’{}’,’{}’,{})”.format(101,’Neelu’,’HR’,35000)
returned data and we can fetch data one row at a time from mycursor.execute(sql)
Cursor.
mydb.commit()
TO CREATE CURSOR
Cursor_name = connectionObject.cursor() #Example: Search a record
For e.g. mycursor = mycon.cursor()
import mysql.connector
TO EXECUTE QUERY mydb=mysql.connector.connect(host="localhost",user="root",
We use execute() function to send query to connection passwd="",database="student")
Cursor_name.execute(query) mycursor=mydb.cursor()
For e.g. mycursor.execute(‘select * from emp’) nm=input("enter name")
mycursor.execute ("select * from emp where ename=' "+nm+"
#Example: (creating database) ' ")
for x in mycursor:
import mysql.connector print (x)
mydb=mysql.connector.connect(host="localhost",user="root",
passwd="") #Example: Delete a record
mycursor=mydb.cursor()
mycursor.execute("create database if not exists school") import mysql.connector
mycursor.execute("show databases") mydb=mysql.connector.connect(host="localhost",user="root",
for x in mycursor: passwd="",database= "student")
print(x) mycursor=mydb.cursor()
mycursor.execute("delete from emp where eno=100")
#Example: (creating table) mydb.commit()
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",
passwd="",database= "student")
mycursor=mydb.cursor()
mycursor.execute("select * from emp")
row=mycursor.fetchone()
while row is not None:
print(row)
row = mycursor.fetchone()