Pythonクエリーmysqlライブラリ

方法1

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import sys
import datetime
import mysql.connector
from mysql.connector import errorcode

reload(sys)
sys.setdefaultencoding('utf-8')

conf = {
  'user': 'bestfei',
  'password': 'password',
  'host': '10.101.196.187',
  'database': 'fei',
  'raise_on_warnings': True,
}


try:
    con = mysql.connector.connect(**conf)
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
else:
    print 'mysql connnect'

    cursor = con.cursor()
    query = ("select order_date, point, city_name,cancel_rate from table_name where order_date>=%s and order_date <=%s and province_id in (%s);")
    provinceIds='310000'
    startTime = datetime.date(2017, 9, 18)
    endTime = datetime.date(2017, 9, 18)

    try:
        #   SQL  
        cursor.execute(query, (startTime, endTime,provinceIds))
        print("sql:"+cursor.statement)
        #         
        results = cursor.fetchall()

        for row in results:
            order_date = row[0]
            point = row[1]
            city_name = row[2]
            cancel_rate = row[3]
            #     
            print "order_date: %s, point: %s, city_name: %s, cancel_rate: %s" % \
                (order_date, point, city_name,cancel_rate)
    except:
        print "Error: unable to fecth data"

    print("affected rows:"+str(len(results)))
    cursor.close()
    con.close()

方法2

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import sys
import datetime
import mysql.connector
from mysql.connector import errorcode

reload(sys)
sys.setdefaultencoding('utf-8')

conf_eleme_report = {
  'user': 'elemeuser',
  'password': 'xBe6pXzy7r',
  'host': '10.101.196.187',
  'database': 'eleme_report',
  'raise_on_warnings': True,
}


try:
    #con = mysql.connector.connect(user='elemeuser', password='xBe6pXzy7r',host='10.101.196.187',database='eleme_report')
    con = mysql.connector.connect(**conf_eleme_report)
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
else:
    print 'mysql connnect'

    cursor = con.cursor()
    query = ("select order_date, point, city_name,cancel_rate from st_report_tms_city_order_status_hour where order_date>=%s and order_date <=%s and province_id in (%s);")
    provinceIds='310000'#,120000
    startTime = datetime.date(2017, 9, 18)
    endTime = datetime.date(2017, 9, 18)

    cursor.execute(query, (startTime, endTime,provinceIds))
    print("sql:"+cursor.statement)

    for (order_date,point,city_name,cancel_rate) in cursor:
        print("{}. cancel_rate at {} {} clock in {} is {}|{:%d %b %Y}".format(cursor.rowcount,order_date,point,str(city_name),cancel_rate,order_date))

    cursor.close()
    con.close()