python批量插入数据到postgres

分手后的思念是犯贱 2023-02-13 11:25 102阅读 0赞

###  一、python脚本 ###

#!/usr/bin/python
    # -*- coding: UTF-8 -*-
    import string
    
    import psycopg2
    import random
    
    from psycopg2 import extras
    
    address_list = {'北京', '上海', '深圳', '广州', '长沙', '成都', '吉林', '邵阳', '九江', '长春', '拉萨'}
    
    conn = psycopg2.connect(database="test", user="test", password="test", host="127.0.0.1", port="5433")
    print("Opened database successfully")
    
    cur = conn.cursor()
    
    # values 后面直接%s
    sql = '''INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) 
             VALUES %s
        '''
    datalist = []
    for i in range(1, 10000000):
        # 生成6位随机字符串
        name = ''.join(random.sample(string.ascii_letters + string.digits, 8))
        age = random.randint(18, 80)
        address = random.sample(address_list, 1)[0]
        salary = random.randrange(5000, 100000)
        # 行数据是以元组的形式存放
        datalist.append((++i, name, age, address, salary))
    extras.execute_values(cur, sql, datalist, page_size=20000)
    conn.commit()
    
    print("All records created successfully")
    
    conn.close()

### 二、执行结果 ###

E:\Python\Python38\python3.exe F:/IdeaProjects/my-jooq/my-python/batch_insert.py
    Opened database successfully
    All records created successfully
    
    Process finished with exit code 0

### 三、查看结果  ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70][]

**从结果可以看出批量插入成功 **

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200528193839632.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz,size_16,color_FFFFFF,t_70