Python) Database connection with MySQL

Install pymysql first

To connect to database

In Python, you need to create cursor first 

import pymysql

# To connect database
con = pymysql.connect(host = 'localhost',
                      user = 'root',
                      passwd = '1234',
                      port = 3306,
                      db = 'mysql',
                      charset = 'utf8')
# To create cursor
cursor = con.cursor()

# To execute SQL
cursor.execute('select * from user')

row = cursor.fetchone()         
print(type(row))                # 'tuple'
print(row)

rows = cursor.fetchall()        
print(type(rows))               # 'tuple'
print(rows)

for r in rows:
    print(r)

Create a table on MySQL workbench.

create table contact(
num int primary key auto_increment,
name varchar(100) not null,
phone varchar(20));

To insert data

import pymysql

try:
    con = pymysql.connect(host='localhost',
                          user='jspid',
                          passwd='jsppass',
                          port=3306,
                          db='jsptest',
                          charset='utf8')


    cursor = con.cursor()

    sql ="insert into contact(name, phone) values('Meadow','010-1111-2222')"

    cursor.execute(sql)
    con.commit()

    print('Successfully inserted')

except Exception as err:
    print(err)
finally:
    con.close()

If you search the inserted data on MySQL workbench, you will see them successfully inserted. 

To search data

import pymysql

try:
    con = pymysql.connect(host='localhost',
                          user='jspid',
                          passwd='jsppass',
                          port=3306,
                          db='jsptest',
                          charset='utf8')

    cursor = con.cursor()

    cursor.execute('select * from contact')

    row = cursor.fetchone()       
    print(type(row))                # 'tuple'
    print(row)                     

    for r in row:
        print(r)

except Exception as err:
    print(err)
finally:
    con.close()

To search all data

fetchall() prints tuples in python whereas, in sqlite and oracle, they print lists. 

import pymysql

try:
    con = pymysql.connect(host='localhost',
                          user='jspid',
                          passwd='jsppass',
                          port=3306,
                          db='jsptest',
                          charset='utf8')

    cursor = con.cursor()
    cursor.execute('select * from contact')
    rows = cursor.fetchall()    
    print(type(rows))               # 'tuple'
    print(rows)                     # cf. sqlite, oracle -> list (return)

    for r in rows:
        print(r)

except Exception as err:
    print(err)
finally:
    con.close()

To update

import pymysql

try:
    con = pymysql.connect(host='localhost',
                          user='jspid',
                          passwd='jsppass',
                          port=3306,
                          db='jsptest',
                          charset='utf8')
    cursor = con.cursor()
    sql = "update contact set phone='1234' where num=1"
    cursor.execute(sql)
    con.commit()
    
    print('Successfully updated')

except Exception as err:
    print(err)
finally:
    con.close()

To delete

import pymysql

try:
    con = pymysql.connect(host='localhost',
                          user='jspid',
                          passwd='jsppass',
                          port=3306,
                          db='jsptest',
                          charset='utf8')
    cursor = con.cursor()

    num = input('Insert the number to delete.')

    sql = 'delete from contact where num = %s'
    cursor.execute(sql, num)
    print('Delete')

# To search all data
    cursor.execute('select * from contact')
    rows = cursor.fetchall()         

    for r in rows:
        print(r)

#To count total data
    cursor.execute('select count(*) from contact')
    count = cursor.fetchone()          
    for c in count:
        print('Total data:', c)

    con.commit()

except Exception as err:
    print(err)
finally:
    con.close()