常用数据库查询结果处理

cx_Oracle 操作oracle数据库:

import cx_Oracle
def myoracle():
    conn = cx_Oracle.connect('user/password@ip:host/ServiceName')  # 连自己的数据库，连接参数可以组合写也可以分开写
    cur = conn.cursor()
    sql = "desc table"#查询表结构
    cur.execute(sql)
    selects = cur.fetchall()
    col = ['名称','是否为空','类型']#oracle中默认的
    data = pd.DataFrame(selects,columns=col)
    # data.drop_duplicates("title",'first',inplace=True)
    data.drop_duplicates('summary','first',inplace=True)#去除某列中的重复行，且去重方式为保留第一次出现的重复数据
    return data
data = myoracle()

pymql操作mysql数据库:

import pymysql
def mysql():
    db = pymysql.connect("ip",user = 'user',password='password',database='test',charset='utf8',)#打开数据库连接
    cursor = db.cursor() #获取操作游标
    cursor.execute('desc table')#运行SOL语句
    #获取游标查询结果，并组建为dataframe
    descselects = cursor.fetchall()#接收返回的结果
    descselects = list(descselects)
    col =['field','Type','Null','Key','default','Extra']#mysql默认的
    tableframe = pd.DataFrame(descselects,columns=col)
    table_columns = tableframe['field']#获取所有列
    cursor.execute('select * from table')#查询表数据
    selects = cursor.fetchall()
    selects = list(selects)
    data = pd.DataFrame(selects,columns=table_columns)
    # table.to_csv('expert.csv',index_label=0,encoding='utf8')#输出为文件
    return data
data = mysql()

py2neo中Graph操作Neo4j数据库

from py2neo import Graph

graph = Graph("http://ip:host/", username='username', password='password')

def myneo4j(graph):
    cypher = "match (n:label) return id(n) as id,n.name as name,labels(n)[0] as labels"#查询某一类标签中的节点id,name,以及标签
    data = graph.run(expertcypher).data()
    return data
data = myneo4j(graph)

elasticsearch-py中Python操作ES数据库

pip install elasticsearch

连接ES数据库：

from elasticsearch import Elasticsearch

es = Elasticsearch(hosts=["localhost:9200"])

接下来就可以进行各种操作了，比如创建索引、插入数据、查询数据等等。

创建索引：

index_name = "my_index"

es.indices.create(index=index_name)

插入数据：

doc = {
    "title": "Python Elasticsearch Tutorial",
    "author": "John Doe",
    "content": "This is a tutorial on how to use Elasticsearch with Python."
}

es.index(index=index_name, body=doc)

查询数据：

query = {
    "query": {
        "match": {
            "content": "Elasticsearch"
        }
    }
}

result = es.search(index=index_name, body=query)

print(result)

 es 聚合查询:

from elasticsearch import Elasticsearch

es = Elasticsearch(hosts=["localhost:9200"])

index_name = "my_index"

# 聚合查询
aggs_query = {
    "aggs": {
        "group_by_author": {
            "terms": {
                "field": "author.keyword"
            },
            "aggs": {
                "avg_views": {
                    "avg": {
                        "field": "views"
                    }
                }
            }
        }
    }
}

result = es.search(index=index_name, body=aggs_query)

# 输出结果
for item in result["aggregations"]["group_by_author"]["buckets"]:
    author = item["key"]
    avg_views = item["avg_views"]["value"]
    print("Author: {}, Avg Views: {}".format(author, avg_views))