python--Python脚本执行SQL

说明：在Linux写python脚本执行SQL，废话不多说，直接贴代码解释。

#!/usr/bin/env python

##python脚本格式，这里有一点要说明的是：#!/usr/bin/env python与#!/usr/bin/python的区别？
##如下：#!/usr/bin/python 的意思是说去/usr/bin/目录下找python去执行#!/usr/bin/env python 的意思是在#!/usr/bin/env中找到python的安装路径，然后去执行它，这个可执行的python路径不管是不是在/usr/bin下，用法比较灵活，而#!/usr/bin/python 就写成了绝对路径，只能去/usr/bin下找。

import pyhs2        ##导入相关的模块，要操作MySQL这个import pymysql是必须的
import sys
import datetime
import pymysql

default_encoding = 'utf-8'    ##设置默认的编码格式为'utf-8'

##执行SQL
def exec_sql(sql):
    cursor = db.cursor() ##利用db方法创建游标对象
    try:
        cursor.execute(sql)
        return db.commit() ##链接不会默认提交需要您手动提交
    except Exception as e:
        print "Error: unable to fecth data", e
        return db.rollback() ##若错误则回滚
##链接数据库
db = pymysql.connect('host', '用户名', '密码', '数据库', port=端口)

conn = pyhs2.connect(host='',
                                  port=,
                                  authMechanism='',
                                  user='',
                                  password='',
                                  database='',)
##定义时间参数
now = datetime.datetime.now()
yday = (now + datetime.timedelta(days=-1)).strftime('%Y%m%d')
##下面写SQL语句
sql = '''
SELECT route,
         launchscene,
         count(1),
         count(DISTINCT uid),
         (sum(CASE
                  WHEN hcnt>0 THEN 0
                  ELSE 1
              END) / count(1)) AS jjlost,
         avg(duration) AS duration
  FROM
    (SELECT UID,
            route,
            pid,
            launchscene,
            sum(hcnt) AS hcnt,
            sum(cnt) AS cnt,
            (max(cts)-min(cts))/1000 AS duration
     FROM
       (SELECT t2.uid,
               t2.route,
               t2.launchscene,
               t2.cts,
               CASE
                   WHEN action IN ('onLoad',
                                   'onHide',
                                   'onShow',
                                   'onUnload',
                                   'login',
                                   'onLaunch',
                                   'onReady') THEN 0
                   ELSE 1
               END AS cnt,
               CASE
                   WHEN praction='onHide' THEN 1
                   ELSE 0
               END AS hcnt,
               sum(CASE
                       WHEN route <> prroute
                            OR praction='onHide'
                            OR praction='onUnload' THEN 1
                       ELSE 0
                   END) over (partition BY UID ROWS BETWEEN unbounded preceding AND CURRENT ROW) AS pid
        FROM
          (SELECT UID,
                  route,
                  launchscene,
                  action,
                  lag(action, 1) over(partition BY UID
                                      ORDER BY cts) AS praction,
                  lag(route, 1) over(partition BY UID
                                     ORDER BY cts) AS prroute,
                  cts
           FROM crs_events
           WHERE ds='20190429'
             AND UID IS NOT NULL
             AND route != '') t2) t3
     GROUP BY UID,
              route,
              pid,
              launchscene) t4 WHERE duration>0.1
GROUP BY route,
         launchscene
'''
##插入数据到指定MySQL的表中
cur = conn.cursor()
cur.execute(sql)
for i in cur.fetch():
    sql = "insert into crs_page_scene_stat values('%s', '%s', %d, %d, %d, %f, %f) on duplicate key update pv=%d, uv=%d, jumpr=%f, duration=%f;" % (yday, i[0], i[1], i[2], i[3], i[4], i[5], i[2], i[3], i[4], i[5])
    print exec_sql(sql)