python 操作 PostgreSQL 資料庫,執行緒並行修改 5w 條資料,效能優化

娃哈哈店長發表於2019-12-31

 獲取新xls表中的所有資料並整理為列表形式返回

其實修改的程式碼量不大,但是要考慮保留之前我們用的函式和方法還要繼續能使用。

excel2sql.py中:

在我們建立物件OpExcel時,為檔案url和sheet名新增預設的值:


class OpExcel(object):

    def __init__(

        self, 

        url:"str型別的檔案路徑" = config.src_path+"\\data\\2019最新全國城市省市縣區行政級別對照表(194).xls", 

        sheet:"excel中的表單名" = "全國城市省市縣區域列表"):

當我們不給予引數時,我們開啟的檔案時原始檔案。

當我們開啟新的擁有地域名和所有經緯度的xls檔案時像如下:


test = OpExcel(config.src_path+"\\data\\test.xls","全國城市省市縣區域列表")

則我們可以查詢到新的檔案和資料

之前我們寫過的函式init_SampleViaProvince_name,時通過傳參而返回指定省的第1級別的地點和地域名(list)形式

我們修改為預設不輸入傳輸則返回所有的資料:


# 通過高德地圖API查詢每個地域名的經緯度,並返回為list資料

    def init_SampleViaProvince_name(

        self, 

        Province_name:"省名" = None

        ) ->"insert的資料,列表形式[('地域名1','1','經緯度'),('地域名2','1','經緯度')]":

        geo_app = Geo_mapInterface(config.geo_key)

        all_data = [self.sh_data.row_values(i) for i in range(self.rows)]

        print(all_data)

        if Province_name:# 生成測試用資料

            cities_data=[ [ ["".join(i[0:3]),1,'test'], ["".join(i[1:3]),1,'test']][i[0]==i[1]] for i in all_data if i[0] == Province_name]

        else: # 生成實際資料

            # 生成第1級別地點和經緯度

            cities_data=[[["".join(i[0:3]),1,i[4]],["".join(i[1:3]),1,i[4]]][i[0]==i[1]] for i in all_data[1:]]

            # 繼續新增第0級別的地點和經緯度:

            for i in all_data:

                cities_data.append(["".join(i[0:2-(i[0]==i[1])]),0,i[4]])

        # for i in cities_data:

        #     i.append(geo_app.get_coordinatesViaaddress("".join(i[0])))

        return cities_data

測試一下:


f:/workspace/env/Scripts/python.exe f:/workspace/城市距離爬取/process_data/excel2sql.py

-----

犁哈薩克自治州', 0, 12345.0], ['新疆省伊犁哈薩克自治州', 0, 12345.0], ['新疆省伊犁哈薩

(展示部分資料)

---

成功。

新的函式已經定義好,那麼我們可以在主函式中同樣利用之前的方法將所有的資料傳入到資料庫中。

 優化程式碼,寫入更便捷的列印日誌系統

在config.py檔案中寫入:


# 構造列印日誌格式,呼叫config.logger.info()即可

logging.basicConfig(stream=open(src_path + '/log/syserror.log', encoding="utf-8", mode="a"), level = logging.DEBUG, format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s')

logger = logging.getLogger(__name__)

再需要列印日誌的時候再來列印日誌。

比如我們初始化資料庫的連結的程式碼:

opsql.py


#!/usr/bin/python

# -*- coding: utf-8 -*-

#__author__: stray_camel

'''

定義對mysql資料庫基本操作的封裝

1.資料插入

2.表的清空

3.查詢表的所有資料

'''

import logging,datetime,time,sys,os

import psycopg2

import asyncio,multiprocessing,threading

# 執行緒池

from concurrent.futures import ThreadPoolExecutor, wait, ALL_COMPLETED, FIRST_COMPLETED

absPath = os.path.abspath(__file__)   #返回程式碼段所在的位置,肯定是在某個.py檔案中

temPath = os.path.dirname(absPath)    #往上返回一級目錄,得到檔案所在的路徑

temPath = os.path.dirname(temPath)    #在往上返回一級,得到資料夾所在的路徑

sys.path.append(temPath)

from public import config

class OperationDbInterface(object):

    #定義初始化連線資料庫

    def __init__(self, 

    host_db : '資料庫服務主機' = 'localhost', 

    user_db: '資料庫使用者名稱' = 'postgres', 

    passwd_db: '資料庫密碼' = '1026shenyang', 

    name_db: '資料庫名稱' = 'linezone', 

    port_db: '埠號,整型數字'=5432):

        # 建立任務池

        self.all_task = []

        # 建立預設返回result,預設為失敗

        result={'code':'9999','message':'預設message','data':'預設data'}

        try:

            self.conn=psycopg2.connect(database=name_db, user=user_db, password=passwd_db, host=host_db, port=port_db)#建立資料庫連結

            print("建立資料庫成功|postgresql, %s,user:%s,host:%s,port:%s"%(name_db,user_db,host_db,port_db))

            self.cur=self.conn.cursor()

        except psycopg2.Error as e:

            self.conn = ""

            self.cur = ""

            print("建立資料庫連線失敗,退出執行|postgresql Error,請看系統日誌!")

            config.logger.exception(e)

            sys.exit(0)

修改我們之前OP資料庫的時候的清理某表資料和查詢函式:


#定義對錶的清空

def ini_table(self,

tablename:"表名")->"清空表資料結果":

    try:

        rows_affect = self.cur.execute("select count(*) from {}".format(tablename))

        test = self.cur.fetchone()  # 獲取一條結果

        self.cur.execute("truncate table {}".format(tablename))

        self.conn.commit()

        result={'code':'0000','message':'執行清空表操作成功','data':test[0]}

        config.logger.info("清空{}表,運算元據{}條,操作:{}!".format(tablename,result['data'],result['message']))

    except psycopg2.Error as e:

        self.conn.rollback()  # 執行回滾操作

        result={'code':'9999','message':'執行批量插入異常','data':[]}

        print ("資料庫錯誤|insert_data : %s" % (e.args[0]))

        config.logger.exception(e)

    return result

    #定義對錶的清空

    def ini_table(self,

    tablename:"表名")->"清空表資料結果":

        try:

            rows_affect = self.cur.execute("select count(*) from {}".format(tablename))

            test = self.cur.fetchone()  # 獲取一條結果

            self.cur.execute("truncate table {}".format(tablename))

            self.conn.commit()

            result={'code':'0000','message':'執行清空表操作成功','data':test[0]}

            config.logger.info("清空{}表,運算元據{}條,操作:{}!".format(tablename,result['data'],result['message']))

        except psycopg2.Error as e:

            self.conn.rollback()  # 執行回滾操作

            result={'code':'9999','message':'執行批量插入異常','data':[]}

            print ("資料庫錯誤|insert_data : %s" % (e.args[0]))

            config.logger.exception(e)

        return result

    #查詢表的所有資料

    def select_all(self, 

    tablename:"表名")->"返回list,存放查詢的結果":

        try:

            rows_affect = self.cur.execute("select * from {}".format(tablename))

            test = self.cur.fetchall()

            self.conn.commit()

            result={'code':'0000','message':'查詢表成功','data':test}

            config.logger.info("查詢{}表,共查詢資料{}條,操作:{}!".format(tablename,len(result['data']),result['message']))

        except psycopg2.Error as e:

            self.conn.rollback()  # 執行回滾操作

            result={'code':'9999','message':'查詢資料異常','data':[]}

            print ("資料庫錯誤| select_all,請看系統日誌!")

            config.logger.exception(e)

        return result

以及呼叫完畢後的連結斷開程式碼:


    #資料庫關閉

    def __del__(self):

        # 情況執行緒池的任務

        if self.all_task != []:

            wait(self.all_task, return_when=ALL_COMPLETED)

            print(len(self.all_task))

        if self.conn:

            self.conn.close()

            print("資料庫斷開連結...")

            config.logger.info("資料庫斷開連結...")

測試一下清空操作:


if __name__ == "__main__":

    op_postgre = OperationDbInterface()

    # 初始化樣表

    result =op_postgre.ini_table("sample_data")

    if result['code']=='0000':

        print("運算元據{}條,操作:{}!".format(result['data'],result['message']))

    else:

        print(result)


(env) PS F:\workspace> & f:/workspace/env/Scripts/python.exe f:/workspace/城市距離爬取/op_postgresql/opsql.py

建立資料庫成功|postgresql, linezone,user:postgres,host:localhost,port:5432

運算元據3206條,操作:執行清空表操作成功!

(env) PS F:\workspace>

ok!nice

 同樣採用執行緒併發池,來op資料庫,修改之前的程式碼,優化?

還是採用執行緒池的併發:


    #定義在城市樣本表中插入資料操作

    def insert_sample_data(self, 

    condition : "insert語句" = "insert into sample_data(address,ad_type,coordinates) values (%s,%s,%s)", 

    params : "insert資料,列表形式[('地域名1','1','經緯度'),('地域名2','1','經緯度')]" = [('地域名1','1','經緯度'),('地域名2','1','經緯度')]

    ) -> "字典形式的批量插入資料結果" :

        def test(l):

            # 插入一條資料

            self.cur.execute(condition,l)

            self.conn.commit()

        executor = ThreadPoolExecutor(max_workers=99)

        def do_calculation(i):

            self.all_task.append(executor.submit(test,i))

        try:

            # 利用執行緒池--concurrent.futures模組來管理多執行緒:

            self.all_task.append(executor.submit(test,params))

            result={'code':'0000','message':'執行批量插入操作成功','data':len(params)}

            config.logger.info("在樣本表sample_data中插入資料{}條,操作:{}!".format(result['data'],result['message']))

        except psycopg2.Error as e:

            self.conn.rollback()  # 執行回滾操作

            result={'code':'9999','message':'執行批量插入異常','data':[]}

            print ("資料庫錯誤|insert_data : %s" % (e.args[0]))

            config.logger.exception(e)

        return result

好的我們先在main.py寫個測試函式用測試資料看一下:


@config.logging_time

def test_2():

     # 定義excel中操作物件excel_data

    excel_data = excel2sql.OpExcel(config.src_path+"\\data\\"+config.new_file,"全國城市省市縣區域列表")

    all_cities = excel_data.sh_data

    # 獲取excel_data的資料總量

    print("資料總共條數,和列數:",all_cities.nrows,all_cities.ncols)

    # data = [all_cities.row_values(i) for i in range(15,22)]

    # 定義資料庫操作物件

    op_postgre = opsql.OperationDbInterface()

    # 獲取插入樣表資料的sql語句,並插入資料,預設不輸入省名就查詢所有

    data_insert_sample = excel_data.init_SampleViaProvince_name()

    # print(data_insert_sample)

    test_data = [['新疆省省直轄行政單位', 0, 12345.0], ['新疆省省直轄行政單位', 0, 12345.0], ['新疆省省直轄行政單位', 0, 12345.0], ['新疆省省直轄行政單位', 0, 12345.0]]

    print("獲取的資料量",len(data_insert_sample))

    result = op_postgre.insert_sample_data("insert into sample_data(address,ad_type,coordinates) values (%s,%s,%s)",test_data)

    # 初始化樣表

    # result =op_postgre.ini_table("sample_data")

    if result['code']=='0000':

        print("運算元據{}條,操作:{}!".format(result['data'],result['message']))

    else:

        print(result)

執行成功!:


(env) PS F:\workspace> & f:/workspace/env/Scripts/python.exe f:/workspace/城市距離爬取/main.py

test_2 is running

資料總共條數,和列數: 2867 5

建立資料庫成功|postgresql, linezone,user:postgres,host:localhost,port:5432

獲取的資料量 3206

運算元據4條,操作:執行批量插入操作成功!

test_2 time func takes 0:00:00.351036

1

資料庫斷開連結...

(env) PS F:\workspace>

速度有點離譜的快,我們看看資料是否真的存進去了?

nice!

 從資料庫中調取地點資料,並1-1地來通過高德API來獲取距離和路徑,處理少量資料(0級城市)

因為我們需要比較地地點是:

- 0級別和0級別距離

- 1級別和1級別距離

那我我們將兩種地點分類:

我們需要從城市的list中生成1-1關係,根據握手原則,這裡我採用了無向圖的滿圖計算節點的方法,並用遞迴的演算法來實現:


    def get_disViaCoordinates(self,

    addList:"一個列表存放地址資料",

    ) ->  "{'origin':[],'destination':[],'distance':[],'route':[]}":

        def test(origin, destination):

            # API炸了,先用假資料吧

            # url2='https://restapi.amap.com/v3/direction/driving?origin='+origin+'&destination='+destination+'&extensions=all&output=json&key=3e2235273dd2c0ca2421071fbb96def4'

            # #編碼

            # newUrl2 = parse.quote(url2, safe="/:=&?#+!$,;'@()*[]")

            # #傳送請求

            # response2 = urllib.request.urlopen(newUrl2)

            # #接收資料

            # data2 = response2.read()

            # #解析json檔案

            # jsonData2 = json.loads(data2)

            # #輸出該json中所有road的值

            # # print(jsonData2)

            # road=jsonpath.jsonpath(jsonData2,'$..road')

            # #從json檔案中提取距離

            # distance = jsonData2['route']['paths'][0]['distance']

            # 假資料

            distance = 999

            road = ['路徑1', '路徑2', '路徑3']

            #字典dict_route中追加資料

            self.dict_route['origin'].append('origin')

            self.dict_route['destination'].append('destination')

            self.dict_route['distance'].append('distance')

            self.dict_route['road'].append('road')

        def base(a,b=0):

            for i in range(len(a)-1):

                # print(a[b][0],a[i+1][0])

                origin = a[b][0]

                destination = a[i+1][0]

                with ThreadPoolExecutor(max_workers=99) as executor:

                    # 利用執行緒池--concurrent.futures模組來管理多執行緒:

                    self.all_task.append(executor.submit(test,origin, destination))

                if i+1 == len(a)-1:

                    b += 1

                    a = a[b:]

                    if len(a) == 1:

                        break

                    base(a,b=0)

        base(addList)

        if self.all_task != []:

            wait(self.all_task, return_when=ALL_COMPLETED)

        return self.dict_route

編寫測試函式從資料庫中查詢資料,根據握手原理 並查詢一對一地點路徑,向資料庫注入0級別城市的距離等資料,0級別城市資料又5w條


@config.logging_time

def test_3(a):

     # generate_sampledata()

    op_postgre = opsql.OperationDbInterface()

    addList = op_postgre.select_all("sample_data")['data']

    test = geo_map.Geo_mapInterface()

    print("所有地點個數",len(addList))

    lsit_city = []

    # 第0級別的資料

    lsit_city.append([list(i) for i in addList if i[1] == 0])

    # 第1級別的資料

    lsit_city.append([list(i) for i in addList if i[1] == 1])

    print(len(lsit_city[1]))

    asd = lsit_city[a][:]

    all_test = test.get_disViaCoordinates(asd)

    # print(all_test[:10])

    num = len(all_test['origin'])

    all_data = []

    # print(len(all_data))

    all_data = [[str(all_test[i][x]) for i in all_test] for x in range(len(all_test['origin']))]

    result = op_postgre.insert_sample_route("insert into sample_route(origin,destination,distance,route) values (%s,%s,%s,%s)",all_data)

    # # # 初始化樣表

    # result =op_postgre.ini_table("sample_route")

    if result['code']=='0000':

        print("運算元據{}條,操作:{}!".format(len(result['data']),result['message']))

    else:

        print(result)

當我們執行的時候可以明顯的看到執行緒急劇的增加還是很有樂趣,哈哈

我們將查詢的餓資料存到引數中,通過除錯臺可以看到 大約又57630條資料:

我們向資料庫中插入城市0型別的資料:


(env) PS F:\workspace> & f:/workspace/env/Scripts/python.exe f:/workspace/城市距離爬取/main.py

test_3 is running

建立資料庫成功|postgresql, linezone,user:postgres,host:localhost,port:5432

所有地點個數 3206

2866

運算元據57630條,操作:執行批量插入操作成功!

this function < test_3 > takes time: 0:00:14.047413

資料庫斷開連結...

(env) PS F:\workspace>

檢視資料庫:

這個資料量應該是和我們之前算的一樣!

操作成功!

but!

1級別城市2800多個但是0級別的只有300多個。當握手原理去匹配資料之後,數量級肯定不是一個單位。

腦殼疼?

最後丟擲一個討論,我們利用公式(握手原理,滿無向圖的邊為N(N-1)/2)算出,2800多條城市1-1關係為400w條,如何更好的插入資料庫呢?

本作品採用《CC 協議》,轉載必須註明作者和本文連結

文章!!首發於我的部落格Stray_Camel(^U^)ノ~YO

相關文章