MySQL Python Connectivity Commands
#CREATE DATABASE
import mysql.connector
mydb=mysql.connector.connect(host=”localhost”,user=”root”,passwd=”12345″)
mycursor=mydb.cursor()
mycursor.execute(“CREATE DATABASE SCHOOL”)
# SHOW DATABASE
import mysql.connector
mydb=mysql.connector.connect(host=”localhost”,user=”root”,passwd=”12345″)
mycursor=mydb.cursor()
mycursor.execute(“SHOW DATABASE”)
for x in mycursor:
print (x)
# CREATE TABLE
import mysql.connector
mydb=mysql.connector.connect(host=”localhost”,user=”root”,passwd=”12345″,database=”student”)
mycursor=mydb.cursor()
mycursor.execute(“CREATE TABLE FEES (ROLLNO INTEGER(3),NAME VARCHAR(20),AMOUNT INTEGER(10));”)
# SHOW TABLES
import mysql.connector
mydb=mysql.connector.connect(host=”localhost”,user=”root”,passwd=”12345″,database=”student”)
mycursor=mydb.cursor()
mycursor.execute(“SHOW TABLES”)
for x in mycursor:
print(x)
#DESCRIBE TABLE
import mysql.connector
mydb=mysql.connector.connect(host=”localhost”,user=”root”,passwd=”12345″,database=”student”)
mycursor=mydb.cursor()
mycursor.execute(“DESC STUDENT”)
for x in mycursor:
print(x)
# SELECT QUERY
import mysql.connector
conn=mysql.connector.connect(host=”localhost”,user=”root”,passwd=”12345″,database=”student”)
c=conn.cursor()
c.execute(“select * from student”)
r=c.fetchone()
while r is not None:
print(r)
r=c.fetchone()
#WHERE CLAUSE
import mysql.connector
conn=mysql.connector.connect(host=”localhost”,user=”root”,passwd=”12345″,database=”student”)
if conn.is_connected==False:
print(“Error connecting to MYSQL DATABASE”)
c=conn.cursor()
c.execute(“select * from student where marks>90”)
r=c.fetchall()
count=c.rowcount
print(“total no of rows:”,count)
for row in r:
print(row)
# DYNAMIC INSERTION i.e insert rows/records through python
import mysql.connector
mydb=mysql.connector.connect(host=”localhost”,user=”root”,passwd=”12345″,database=”student”)
mycursor=mydb.cursor()
r=int(input(“enter the rollno”))
n=input(“enter name”)
m=int(input(“enter marks”))
mycursor.execute(“INSERT INTO student(rollno,name,marks) VALUES({},'{}’,{})”.format(r,n,m))
mydb.commit()
print(mycursor.rowcount,”RECORD INSERTED”)
# UPDATE COMMAND
import mysql.connector
mydb=mysql.connector.connect(host=”localhost”,user=”root”,passwd=”12345″,database=”student”)
mycursor=mydb.cursor()
mycursor.execute(“UPDATE STUDENT SET MARKS=100 WHERE MARKS=40”)
mydb.commit()
print(mycursor.rowcount,”RECORD UPDATED”)
# DELETE COMMAND
import mysql.connector
mydb=mysql.connector.connect(host=”localhost”,user=”root”,passwd=”12345″,database=”student”)
mycursor=mydb.cursor()
mycursor.execute(“DELETE FROM STUDENT WHERE MARKS<50”)
mydb.commit()
print(mycursor.rowcount,”RECORD DELETED”)
# DROP COMMAND
import mysql.connector
mydb=mysql.connector.connect(host=”localhost”,user=”root”,passwd=”12345″,database=”student”)
mycursor=mydb.cursor()
mycursor.execute(“DROP TABLE STUDENT”)
# ALTER COMMAND
import mysql.connector
mydb=mysql.connector.connect(host=”localhost”,user=”root”,passwd=”12345″,database=”student”)
mycursor=mydb.cursor()
mycursor.execute(“ALTER TABLE STUDENT ADD GRADE CHAR(3)”)