python通过MySQLdb操作mysql

这里提供一个MySQLdb操作MYSQL数据库的例子。

在此之前首先需要知道的是MySQLdb仅仅支持到python 2.xx；另外MySQLdb的安装这里我们也不做介绍。

一、如下为一个使用实例。

#!/usr/bin/python
#-*-- coding:utf-8 -*--
 
import MySQLdb
 
#打开数据库连接
db = MySQLdb.connect(host='11.xxx.xx.122',port=3306, user='my_reader_user',passwd='my_reader_user@123',db='db_my_corp')
 
#使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# 使用execute方法执行SQL语句
cursor.execute('SELECT VERSION()')
 
# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()
print "Database version : %s " % data
sum = 0
#开始for循环累加主号数
#for i in (69,70):
for i in range(0,100):
        table = 't_corp_basic_info_{0}'.format(i)
        sql = 'select count(*) from {0};'.format(table)
        print 'sql:{0}'.format(sql)
        cursor.execute(sql)
        data = cursor.fetchone()
        print "count of {0} is {1}".format(table, data)
        sum = sum + data[0]
 
print 'sum:{0}'.format(sum)
 
# 关闭数据库连接db.close()
db.close()

二、访问库表 db_qidian_corp.t_corp_basic_info_xx中的主号，然后依次再去访问qidian_user.t_user_basic_info_xx中的每个主号对应的工号数据，导出至文档。

#!/usr/bin/python
#-*-- coding:utf-8 -*--
 
import MySQLdb
import time,datetime

#打开数据库连接
db_kfuin = MySQLdb.connect(host='11.xxx.xx.xxx',port=33xx, user='xxx_root',passwd='xxx@123',db='db_qidian_corp')
db_kfext = MySQLdb.connect(host='11.xxx.xx.xxx',port=33xx, user='xxx_root',passwd='xxx@123',db='qidian_user')

#使用cursor()方法获取操作游标 
cursor_kfuin = db_kfuin.cursor()
cursor_kfext = db_kfext.cursor()

# 使用execute方法执行SQL语句
cursor_kfuin.execute('SELECT VERSION()')
 
# 使用 fetchone() 方法获取一条数据
data = cursor_kfuin.fetchone()
print "Database version : %s " % data

# 创建文件,将符合条件的数据写入
filename = "kfuin_kfext_data_0~1.txt"
file = open(filename,'a')
file.write('   kfuin ' + '  ' + '  kfext ' + '     expire ' + ' ' + '        date ')
file.write('\n')

sum = 0
# select FCorpUin from db_qidian_corp.t_corp_basic_info_78 where (FExpireTime < 1670577452 and FExpireTime > 1564591835)\G;
#开始for循环累加主号数
#for i in (69,70):
for i in range(0,1):
        table_kfuin = 't_corp_basic_info_{0}'.format(i)
        #这里最彻底的方式还是直接指定过期时间小于三个月以前。。
        #1662860431 2022-09-11 09:40:31
        #1284169231 2010-09-11 09:40:31
        #sql = 'select FCorpUin,FExpireTime from {0} where (FExpireTime < 1662860431 and FExpireTime > 1284169231);'.format(table)
        sql = 'select FCorpUin,FExpireTime from {0} where (FExpireTime < 1662860431);'.format(table_kfuin)
        print 'sql:{0}'.format(sql)
        cursor_kfuin.execute(sql)
        arr_kf_exp = cursor_kfuin.fetchall()
        sum = sum + len(arr_kf_exp)
        for j in range(0, len(arr_kf_exp)):
                kfuin,expire = arr_kf_exp[j]
                dt = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(expire))
                table_kfext = 't_user_basic_info_{0}'.format(i)
                sql_kfext = 'select FUserUin from {0} where FCorpUin = {1};'.format(table_kfext,kfuin)
                print 'sql_kfext:{0}'.format(sql_kfext)
                cursor_kfext.execute(sql_kfext)
                arr_kfext = cursor_kfext.fetchall()
                for k in range(0, len(arr_kfext)):
                        kfext = arr_kfext[k][0]
                        file.write(str(kfuin) + ' ' + str(kfext) + ' ' + str(expire) + ' ' + dt)
                        file.write('\n')
print 'sum:{0}'.format(sum)

# 关闭数据库连接db.close()
db_kfuin.close()