Python–MySQL Connectivity

pip install mysql-connector-python

import mysql.connector as mc
con = mc.connect(host='localhost', user='root', passwd='root123')
if con.is_connected():
    print('Connected to MySQL')
cur = con.cursor()
cur.execute('CREATE DATABASE IF NOT EXISTS school')
cur.execute('USE school')
cur.execute('DROP TABLE IF EXISTS student')
cur.execute('''CREATE TABLE student (
                 roll INT PRIMARY KEY, name VARCHAR(20),
                 stream VARCHAR(10), marks INT)''')
print('Table created')
con.close()

import mysql.connector as mc
con = mc.connect(host='localhost', user='root', passwd='root123', database='school')
cur = con.cursor()
rows = [(1, 'Anika', 'Science', 92), (2, 'Dev', 'Commerce', 78),
        (3, 'Farhan', 'Science', 64), (4, 'Gauri', 'Arts', 88)]
for r in rows:
    q = "INSERT INTO student VALUES ({}, '{}', '{}', {})".format(*r)
    cur.execute(q)
con.commit()                      # save changes permanently
print(len(rows), 'records inserted')
con.close()

import mysql.connector as mc
con = mc.connect(host='localhost', user='root', passwd='root123', database='school')
cur = con.cursor()
cur.execute('SELECT * FROM student')
data = cur.fetchall()             # list of tuples
print('Rows fetched:', cur.rowcount)
for roll, name, stream, marks in data:
    print(roll, name.ljust(7), stream.ljust(9), marks)
con.close()

import mysql.connector as mc
con = mc.connect(host='localhost', user='root', passwd='root123', database='school')
cur = con.cursor()
cur.execute('SELECT name, marks FROM student ORDER BY marks DESC')
print('fetchone :', cur.fetchone())
print('fetchmany:', cur.fetchmany(2))
print('rest     :', cur.fetchall())
print('rowcount :', cur.rowcount)   # rows read so far
con.close()

import mysql.connector as mc
con = mc.connect(host='localhost', user='root', passwd='root123', database='school')
cur = con.cursor()
stream = input('Stream to search: ')
cur.execute('SELECT roll, name, marks FROM student WHERE stream = %s', (stream,))
for row in cur.fetchall():
    print(row)
con.close()

import mysql.connector as mc
con = mc.connect(host='localhost', user='root', passwd='root123', database='school')
cur = con.cursor()
roll = int(input('Roll to update: '))
new = int(input('New marks: '))
cur.execute('UPDATE student SET marks = %s WHERE roll = %s', (new, roll))
con.commit()
print(cur.rowcount, 'row(s) updated')
cur.execute('SELECT * FROM student WHERE roll = %s', (roll,))
print(cur.fetchone())
con.close()

import mysql.connector as mc
con = mc.connect(host='localhost', user='root', passwd='root123', database='school')
cur = con.cursor()
cur.execute('DELETE FROM student WHERE marks < %s', (75,))
con.commit()
print('Deleted:', cur.rowcount)
cur.execute('SELECT COUNT(*), AVG(marks) FROM student')
cnt, avg = cur.fetchone()
print('Left:', cnt, '| average:', round(float(avg), 2))
con.close()

import mysql.connector as mc
def connect():
    return mc.connect(host='localhost', user='root', passwd='root123', database='school')

def add(roll, name, stream, marks):
    con = connect(); cur = con.cursor()
    try:
        cur.execute('INSERT INTO student VALUES (%s, %s, %s, %s)', (roll, name, stream, marks))
        con.commit(); print('Added', name)
    except mc.Error as e:
        print('Error:', e.msg)
    con.close()

def show():
    con = connect(); cur = con.cursor()
    cur.execute('SELECT * FROM student ORDER BY roll')
    for r in cur.fetchall():
        print(r)
    con.close()

add(5, 'Harsh', 'Commerce', 81)
add(1, 'Copy', 'Arts', 50)          # duplicate primary key
show()