python操作mysql基础之查询、比对、求和

# -*- coding: utf-8 -*-
import os
import sys
import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1',
                       user='root',
                       passwd='root',
                       db='cb',
                       port=3306,charset='utf8')
cur = conn.cursor()
#查询不重复记录
goods = cur.execute("select distinct goods_id from sale")
goods = cur.fetchmany(goods)

#清空统计表
cur.execute("TRUNCATE tongji")

for glen in range(len(goods)):  
    if goods[glen][0]==0:
        continue
    sql="select sum(sale) from sale where goods_id= "+str(goods[glen][0])
    
    goods_sum=cur.execute(sql)
    data_temp=cur.fetchone()
    re_sum=data_temp[0]

    #获取产品名
    sql_product_name="select goods_name from es_goods where goods_id="+str(goods[glen][0])
    sql_exe=cur.execute(sql_product_name)
    product_name_array=cur.fetchone()

    sql_insert="INSERT INTO `tongji` (`id`, `gid`, `sale_count`,`goods_name`) VALUES (NULL, %s,%s,%s)" 
    print sql_insert 

    re=cur.execute(sql_insert,(goods[glen][0],re_sum,product_name_array[0]))
    if re==1:
        print str(goods[glen][0])+"completed!"
      
cur.close
conn.commit()
conn.close

 

转载于:https://www.cnblogs.com/codebank/p/3664484.html