程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
您现在的位置: 程式師世界 >> 編程語言 >  >> 更多編程語言 >> Python

How Python puts millions of data into PostgreSQL Library

編輯:Python

1. PostgreSQL What is it?

PostgreSQL It is a powerful open source object relational database system , He used and extended SQL Language , It combines many functions of safely storing and expanding the most complex data workloads .

PostgreSQL The origins of 1986 year , As the University of California, Berkeley POSTGRES Part of the project , And on the core platform 30 Years of active development .

PostgreSQL With its proven Architecture , reliability , Data integrity , Powerful feature set , Scalability and the dedication of the open source community behind the software have won a good reputation , To consistently deliver high-performance and innovative solutions .

2. Business driven choices PostgreSQL

Because the business needs to pour a large amount of test data into the pressure test , I've tried many ways but it hasn't been solved very well , Finally choose to use Python To achieve data injection into PostgreSQL, The efficiency of rough estimation data processing can reach 6.5W/s.

3. Python Code implementation

There is one in the code batchs Parameters of , It is used to control the batch inserting database , Currently given 1000, The effect is still very good .

The code is as follows :

from openpyxl import load_workbook
import random
import psycopg2
batchs =1000
def data(datas):
    conn = None
    try:
        conn = psycopg2.connect(database="test_62554cf827ca24dc542c4258", user="postgres", password="123456",
                                host="10.10.11.248", port="5432")
        print("connected to postgres db successfully")
    except:
        print("I am unable to connect to the database")
    try:
        cursor = conn.cursor()
        sql = "insert into t_fact_6260d12dcd211247f807e521 values " + datas+";"
        print(sql)
        cursor.execute(sql)
    except (Exception, psycopg2.Error) as error:
        print("Error caught", error)
    finally:
        conn.commit()
        if (conn):
            cursor.close()
            conn.close()
def insertData():
    count = 0
    lw=load_workbook(r'D:\wl\bussiness_study\testdata.xlsx', read_only='read_only')
    ws = lw.active
    print(ws.rows)
    lst=[]
    strs = ""
    for row in ws.rows:
        count += 1
        lst.append("111"+str(random.random()))
        lst.append(0)
        lst.append("1634745600.0")
        lst.append("513567da-2d61-41db-a4cb-f2"+str(random.randint(100000000,999999999)))
        for col in row:
            lst.append(col.value)
        print(" Execution section --%d-- That's ok " % (count))
        # print(lst)
        str2 = str(tuple(lst)).replace("None", "'None'")+","
        # print(str2)
        if(count%batchs==0):
            strs += str2
            # print(strs)
            data(strs[:-1])
            lst = []
            strs = ""
            count=0
        else:
            strs += str2
            # print(strs)
            lst=[]
        str2 = ""
        # print(strs)
if __name__ == '__main__':
    insertData()

Welcome to your attention 【 The way of immeasurable testing 】 official account , reply 【 Claim resources 】

Python+Unittest frame API automation 、

Python+Unittest frame API automation 、

Python+Pytest frame API automation 、

Python+Pandas+Pyecharts Big data analysis 、

Python+Selenium frame Web Of UI automation 、

Python+Appium frame APP Of UI automation 、

Python Programming learning resources dry goods 、

Vue Front end component framework development 、

Resources and code Free ~
Below the official account is two-dimensional code. , You can directly scan wechat and pay attention .

remarks : My official account has been officially opened. , betake IT Sharing of Internet technology .

contain : Data analysis 、 big data 、 machine learning 、 Test Development 、API Interface automation 、 Test operation and maintenance 、UI automation 、 Performance testing 、 code detection 、 Programming technology, etc .

WeChat search official account :“ The way of immeasurable testing ”, Or scan the qr code below :

  

  Add the attention , Let's grow together !


  1. 上一篇文章:
  2. 下一篇文章:
Copyright © 程式師世界 All Rights Reserved