【Python】Python 操作数据库合集

Mysql

---

pymysql

1、 获取连接

def get_mysql_conn():
    while True:
        try:
            conn = pymysql.connect(
                host = "127.0.0.1",
                port=3306,
                user="root",
                password="123",
                db="ezrealer",
                charset="utf8mb4"
            )
            cur = conn.cursor()
            break
        except Exception as e:
            print(e)
            print(f"连接{host}失败，1分钟后重试...")
            time.sleep(60)
    return cur,conn

2、常用操作

查询

def execute_select(cur,sql):
    while True:
        try:
            print(sql)
            cur.execute(sql)
            results = cur.fetchall()
            break
        except Exception as e:
            print("execute select error,retry after 5s...")
            print(e)
            time.sleep(5)
            cur, conn = get_mysql_conn()
    return results

插入字典

需要字典的key 能和 数据库表的字段有对应

def execute_insert(cur,conn,table,item):
    keys = ', '.join(item.keys())
    values = ', '.join(['%s'] * len(item))
    sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
    while 1:
        try:
            cur.execute(sql, tuple(item.values()))
            conn.commit()
            break
        except Exception as e:
            print("execute insert error,retry after 5s...")
            print(e)
            time.sleep(5)
            cur,conn = get_mysql_conn()

mysql 数据库连接池

1、创建一批连接到连接池，供所有线程共享使用

import pymysql
from DBUtils.PooledDB import PooledDB

pool = PooledDB(
    pymysql,
    maxconnections = 100,
    host='127.0.0.1',
    user='root',
    password='123',
    database='ezrealer',
    port=3306,
    charset="utf8mb4"
)

conn = pool.connection()
cur = conn.cursor()

print('链接被拿走了', conn1._con)
print('池子里目前有', pool._idle_cache, '\r\n')


# close 只是将连接放回连接池
conn.close() 

MongoDB

---

pymongodb

1、安装

pip install pymongodb

2、连接

import pymongo

myclient = pymongo.MongoClient("mongodb://root:passwd@ip:port/")
# 连接副本集只需要把多个 ip:port 写在一起，用逗号隔开即可
mydb = myclient["db_name"]
mycol = mydb['col_name']

3、查询

myquery = {"send_time": query_send_time,"type":types}
mydoc = mycol.find(myquery)

4、更新

newvalue = {"$set": {"letterproof": letterproof_new}}
myquery = {"rightsId": rights_id}
mycol.update_one(myquery, newvalue)

Elasticsearch

连接 es

from elasticsearch import Elasticsearch

es = Elasticsearch(hosts="127.0.0.1", port=9200, http_auth=('elastic', 'mypasswd'), timeout=3600)

创建 index

res = es.indices.create(index = 'web-data-template',mappings)

查询

# 按 id 查询
data_result = es.get(index='image_info', id=ids)

# 查询所有
query = {
  "query": {
    "match_all": {}
  }
}
result = es.search(index="megacorp", body=query)
print(result)

# 条件查询
body = {
    "size": get_size,
    "query": {
        "range": {
            "record_date.keyword": {
                "gte": earlist_time,
                #"lte": latest_time,
            }
        }
    },
    "sort": {
        "record_date.keyword": "asc"
    },
}

filter_path = ["_scroll_id","hits.total", "hits.hits._id", "hits.hits._source.record_date", "hits.hits._source.relocal_url","hits.hits._source.website"]

es.search(index="image_info", body=body, filter_path=filter_path, scroll='5m')

1、查询所有

es.search(index="index_name", doc_type="type_name")
# 或
body = {
    "query":{
        "match_all":{}
    }
}
es.search(index="index_name", doc_type="type_name", body=body)

2、等于查询

body = {
    "query":{
        "term":{
            "name":"python"
        }
    }
}
# 查询name="python"的所有数据
es.search(index="index_name",doc_type="type_name",body=body)

body = {
    "query":{
        "terms":{
            "name":[
                "ios","android"
            ]
        }
    }
}

# 查询出name="ios"或name="android"的所有数据
es.search(index="index_name",doc_type="type_name",body=body)

3、包含查询

# name 包含 python
body = {
    "query":{
        "match":{
            "name":"python"
        }
    }
}

# 多个字段，有一个包含即可
body = {
    "query":{
        "multi_match":{
            "query":"深圳",
            "fields":["name", "addr"]
        }
    }
}

4、按 id 查询

body = {
    "query":{
        "ids":{
            "type":"type_name",
            "values":[
                "1","2"
            ]
        }
    }
}

5、切片查询

body = {
    "query":{
        "match_all":{}
    }
    "from":2    # 从第二条数据开始
    "size":4    # 获取4条数据
}

6、范围查询

body = {
    "query":{
        "range":{
            "age":{
                "gte":18,       # >=18
                "lte":30        # <=30
            }
        }
    }
}

7、前缀查询

body = {
    "query":{
        "prefix":{
            "name":"p"
        }
    }
}

8、通配符查询

body = {
    "query":{
        "wildcard":{
            "name":"*id"
        }
    }
}

9、排序

body = {
    "query":{
        "match_all":{}
    }
    "sort":{
        "age":{                 # 根据age字段升序排序
            "order":"asc"       # asc升序，desc降序
        }
    }
}

10、返回结果过滤

es.search(index="index_name",doc_type="type_name",filter_path=["hits.hits._id"])

11、获取返回数量

es.count(index="index_name",doc_type="type_name")

12 最小值、最大值、和、平均值

插入

body = {}
result = es.index(index="web-data-template", body=body)
print(result)