Python handles data with MySQL connector

Recently I help one of my friends to handle data by python, there are some key points here I would like to share.

Scenario:

There are some data in excel, my friend wants to put these data into MySQL database, finally, output summary information to the txt file. Of course, in this case, we ignore the logical part, just focus the key points. Let’s do it.

Env:

Platform: MacPro

Python version: 2.7 or 3.6(I know Python V27 has been stopped the update, both I have tested, so far so good)

connector: Obviously, we need a plugin to connect to MySQL. Just head to this website https://dev.mysql.com/downloads/connector/python/, select your operating system, install it.

xlrd: xlrd is a famous plugin for python to operate excel, install it by a command like easy_install xlrd, or pip install xlrd (Cry, edit function is limited, I suppose I should become the member of Medium)

Show code directly

import sys
import xlrd
import time
import mysql.connector
def connect_mysql():
    print( 'Connect to mysql server...')
    db = mysql.connector.connect(
        user='wow',
        password= 'wow',
        host='127.0.0.1',
        database='wow',
        charset="utf8",
        buffered=True)
    print( 'connect success')
    return db
def createtable(db):
    # using cursor to control database
    cursor = db.cursor()
    # create a table if not exist
    cursor.execute("DROP TABLE IF EXISTS product")
    sql = """CREATE TABLE product (
            update_time CHAR(255),
            entity_a CHAR(255),
            entity_b CHAR(255) )"""

    cursor.execute(sql)
def insertdb(db, update_time, entity_a, entity_b)

    cursor = db.cursor()
    sql = 'INSERT INTO `product`(`update_time`,`entity_a`,`entity_b`) VALUE(%s,%s,%s)'
    try:
        cursor.execute(sql, (update_time, entity_a, entity_b)
        db.commit()
    except:
        # Rollback in case there is any error
        print('Error ... Insert Fail!')
        db.rollback()
def deletedb(db):
    cursor = db.cursor()
    sql = "DELETE FROM product WHERE entity_a = '%d'" % ("content")

    try:
       cursor.execute(sql)
       db.commit()
    except:
        print('Error ... Delete Fail!')
        db.rollback()
def updatedb(db, update_time, entity_a, entity_b):
    cursor = db.cursor()
    sql = "UPDATE product SET entity_a = '%s', update_time = '%s' WHERE entity_b = '%s'" %(entity_a, update_time, entity_b)

    try:
        cursor.execute(sql)
        db.commit()
    except:
        print('Error ... Update Fail!')
        db.rollback()
def closedb(db):
    print('Close DB')
    db.close()
def main():
    db = connect_mysql()
    ...
    to handle your own business
    ...
    closedb(db)
if __name__ == '__main__':
    main()

The above code is a simple CRUD operating. However, how would we get the data from MySQL? Observe below code:

def querydb(db):
    cursor = db.cursor()
    sql = "SELECT * FROM product"
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        for row in results:
            update_time = row[0]
            entity_a = row[1]
            entity_b = row[2]
            # print '\n Query ALL Results'
            # print "Update_Time: %s, Entity_A: %s, Entity_B: %s" % \
            #       (update_time, entity_a, entity_b)
    except:
        print('Error ... unable to fetch data')

Then, how many rows do we have?

cursor.rowcount

If want to get one row

cursor.fetchone()

Get all

cursor.fetchall()

Check some elements if exist

def queryEntityA(db, entity_a):
    cursor = db.cursor()
    sql = "SELECT * FROM product where entity_a='%s'" % (entity_a)
    try:
        cursor.execute(sql)
        results = cursor.fetchone()
        if results == None:
            return True
    except:
        print('Error ... Query Entity A Error: unable to fetch data')

That is all I want to share today. It is pretty useful for me to handle simple data and I hope this is also useful to your guys. Have a nice day!