python mysql 套路 集合

##########################################
import pymysql
mysql_ip = ' '
mysql_username = ' '
mysql_password = ' '
mysql_name = ' '
##########################################
con = pymysql.connect(mysql_ip, mysql_username, mysql_password, mysql_name)
......
con.close()
##########################################

获取执行结果

mysql = 'SELECT `IP`,`Port`,`Type` FROM `data` ORDER BY `IP` LIMIT '+str(i)+',1'
with con.cursor() as cursor:
    cursor.execute(mysql)
    result = cursor.fetchone()
    IP = str(result[0])
    Port = str(result[1])
    Type = str(result[2])
    print i, IP, Port, Type

执行是否正确

mysql = 'INSERT INTO data(`IP`,`High Risk`) VALUES ("' + ip +'","Yes")'
try:
    with con.cursor() as cursor:
        result = cursor.execute(mysql)
    if result == 1:
        con.commit()
        print i, ip, 'ok'
        
except Exception as e:    
    print i, ip,'no', e

Mysql命令
统计

mysql = 'SELECT COUNT(*) FROM `data`'

添加

mysql = 'INSERT INTO data(`IP`,`High Risk`) VALUES ("' + ip +'","Yes")'

更新整列 条件加WHERE

UPDATE `alldata` SET `AddDate` = '2020-03-31'

查找首条

mysql = 'SELECT `url` FROM `caijiypsorturls` WHERE `n`=0'

排序获取第N条

mysql = 'SELECT `IP`,`Port`,`Type` FROM `data` ORDER BY `IP` LIMIT 0,1'

无限循环执行，防止长时间连接断开，每执行500次重新连接数据，执行错误重新连接数据库
刚写的，未执行，逻辑上正确。

x = 1
while True:
    try:
        if x > 500:
            try:
               con.close()
            except:
                pass
            con = pymysql.connect(mysql_ip, mysql_username, mysql_password, mysql_name)
            print 'reconnect mysql'
            x = 1
        ......
        x = x + 1
    except Exception as e:
        time.sleep(10)
        print e, 'plese wait 10s'
        x = 500
        continue
con.close()