MySQL Connectivity

 

MySQL with 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)”)