Python操作MySQL

Edited on In 
1
pip install mysqlclient -i https://mirrors.aliyun.com/pypi/simple/

多线程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

import os
import re
import urllib.request

import MySQLdb
import m3u8
import requests
import threading

from dotenv import load_dotenv

from py import m3u8Merge

load_dotenv()
outPath = 'F:/资料文档/zztt/'

# 自动提交事务
def getConn():
    return MySQLdb.connect(
        host=os.getenv("db_pic_host"),
        port=int(os.getenv("db_pic_port")),
        user=os.getenv("db_pic_user"),
        passwd=os.getenv("db_pic_password"),
        db=os.getenv("db_pic_server"),
        autocommit=True
    )

def saveInfo(id, name, url, href):
    sql = '''
        insert into z(id,name,url,href) values("{}","{}","{}","{}")''' \
        .format(id, name, url, href)
    cursor = 1;
    try:
        connection = getConn()
        cursor = connection.cursor()
        cursor.execute(sql)
        cursor.fetchall()
        # connection.commit()
        cursor.close()
        return True
    except Exception as e:
        try:
            cursor.close()
        except Exception as e:
            print('1')
        print("添加数据库数据失败:", e, sql)
        return False
def listNeedDownload():
    sql = '''
    select id,name,url from z where download  = 2  limit 10
    '''
    try:
        connection = getConn()
        cursor = connection.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        # connection.commit()
        result = []
        for row in data:
            result.append({
                'id': row[0],
                'name': row[1],
                'url': row[2]
            })
        cursor.close()
        return result
    except Exception as e:
        print("listNeedDownload失败:", e, sql)
        return []

数据库连接池

1
2
pip install DBUtils -i https://mirrors.aliyun.com/pypi/simple/
pip install pymysql  -i https://mirrors.aliyun.com/pypi/simple/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
import pymysql
from dbutils.pooled_db import PooledDB
from dotenv import load_dotenv
load_dotenv()
pool = PooledDB(
    creator=pymysql,
    maxconnections=20,
    mincached=2,
    maxcached=10,
    blocking=True,
    host=os.getenv("db_pic_host"),
    port=int(os.getenv("db_pic_port")),
    user=os.getenv("db_pic_user"),
    passwd=os.getenv("db_pic_password"),
    db=os.getenv("db_pic_server"),
    charset='utf8mb4'
)
def a():
      sql = '''
        insert into img(id,name,tab_url,url) values("{}","{}","{}","{}")''' \
        .format(id, name, tab_url, url)
    cursor = 1
    connection = pool.connection()
    try:
        cursor = connection.cursor()
        cursor.execute(sql)
        cursor.fetchall()
        connection.commit()
        close(cursor)
        connection.close()
        return True
    except Exception as e:
        close(cursor)
        print("添加数据库数据失败:", e, sql)
        connection.close()
        return False

本文地址： https://github.com/maxzhao-it/blog/post/5fc222/