chapter 13: Mysql with Python

13.1 demo mysql test

import mysql.connector

13.2 Create Connection

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="root"
)

print(mydb);

13.3 Create Database

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="root"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE mypython_test_db")

13.4 Check DB Exist

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="root"
)

mycursor = mydb.cursor()
mycursor.execute("SHOW DATABASES")

for x in mycursor:
  print(x)

13.5 ConnectToDB

import mysql.connector
#Create Connection to DB
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="root",
  database="mypython_test_db"
)


mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE employee (name VARCHAR(255), address VARCHAR(255))")

13.6 Insert

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="root",
  database="mypython_test_db"
)

mycursor = mydb.cursor()

sql = "INSERT INTO employee (name, address) VALUES (%s, %s)"
val = ("Rohan", "Jhakle")
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")

13.7 Select

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="root",
  database="mypython_test_db"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM employee")

myresult = mycursor.fetchall()
print("**************All Records*******")
for x in myresult:
  print(x)

#Where Condition

sql1 = "SELECT * FROM employee WHERE address ='Pune'"

mycursor.execute(sql1)

myresult = mycursor.fetchall()
print("***********Records which satisfy where condition ***********")
for x in myresult:
  print(x)

print("**********Record which have 'a' in name************")
sql2 = "SELECT * FROM employee WHERE name LIKE '%ha%'"

mycursor.execute(sql2)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

#Prevent SQL Injection

print("**********Prevent SQL Injection******************************");
sql3 = "SELECT * FROM employee WHERE name = %s"
name = ("Salman Khan", )

mycursor.execute(sql3, name)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

print("********Order By**************");
sql4 = "SELECT * FROM employee ORDER BY name"
#To have DESC order give DESC after the name in above query
mycursor.execute(sql4)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

print("**************Delete Record************************")
sql5 = "DELETE FROM employee WHERE name = 'Amey Wagh'"
mycursor.execute(sql5)
mydb.commit()
#Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table.
#Notice the WHERE clause in the DELETE syntax: The WHERE clause specifies which record(s) that should be deleted. If you omit the WHERE clause, all records will be deleted!
print(mycursor.rowcount, "record(s) deleted")