Python and MySql Quick Overview

• Home
• Python
• Python and MySql

In order to connect python with MySql, I need to install the MySQL driver package.

cmd>python -m pip install mysql-connector-python

Remember if you want to install MySQL connector in the virtual environment, enable or activate the environment and then install the driver using PIP.

MySql connection

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password=""
)
mycursor = mydb.cursor()
print(mydb)

This will print nothing if the connection is successful else return an error.

Create database

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password=""
)
mycursor = mydb.cursor()
mycursor.execute("Create Database school_database")
print(mydb)

This will create the database name school_database.

Show all databases of MySql

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

mycursor = mydb.cursor()
mycursor.execute("show databases")
for x in mycursor:
    print(x)
print(mydb)

This will print all databases of MySql.

Create Tables

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database="school_database"
)

mycursor = mydb.cursor()

#create table student
mycursor.execute("CREATE TABLE students (name VARCHAR(255), address VARCHAR(255))")

#create table teachers with primary key id
mycursor.execute("CREATE TABLE teachers(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")


#show all tables
mycursor.execute("SHOW TABLES")
for x in mycursor:
  print(x)

Here two tables will create students and teachers and print all tables of the database school_database.

Insert record

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="school_database"
)

mycursor = mydb.cursor()

#method 1
sql = "insert into teachers(name,address) values('amit','noida')"
mysql.execute(sql)

#method 2 protect from sql injection
sql = "insert into teachers(name, address) values(%s,%s)"
val = ("Sunil","Delhi")
mycursor.execute(sql,val)

#method 3 insert many
sql = "insert into teachers(name, address) values(%s, %s)"
val = [
    ("sunil","111"),
    ("amit","222"),
    ("sumit","333")
]
mycursor.executemany(sql,val)

# must run to save
mydb.commit()

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

#print last insert id 
print(mycursor.lastrowid, " row id");

Update record

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database = "school_database"
)

mycursor = mydb.cursor()

# single line
sql = "update teachers set name=%s where address = %s"
val = ('Sunita','Delhi')
mycursor.execute(sql,val)

# multi line 
# \ at the end mean statement continue to next line
sql = "update teachers \
set name=%s \
where address = %s"
val = ('Sunita','Delhi')
mycursor.execute(sql,val)

mydb.commit()

print(mycursor.rowcount, " rows updated")

Delete Record

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database ="school_database"
)

mycursor = mydb.cursor()
sql = "delete from customers where name like %s"
address = ('A%',)
mycursor.execute(sql,address)
mydb.commit()

print(mycursor.rowcount, " Record deleted")

FetchAll Records

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database ="school_database"
)

mycursor = mydb.cursor()

mycursor.execute("select name,address from teachers")

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

Fetch one record

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database ="school_database"
)

mycursor = mydb.cursor()

sql = "select name,address from teachers where address = %s")
val = ('A%',)
mycursor.execute(sql,val)

myresult = mycursor.fetchone()

print(myresult)