python连接postgres，创建表和插入数据

冷不防 2023-02-13 11:24 14阅读 0赞

### 一、python脚本文件 ###

**1. 创建create\_data.py 文件，内容如下**

#!/usr/bin/python
    # -*- coding: UTF-8 -*-
    import string
    
    import psycopg2
    import random
    
    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()
    cur.execute('''CREATE TABLE COMPANY
                       (ID INT PRIMARY KEY     NOT NULL,
                       NAME           TEXT    NOT NULL,
                       AGE            INT     NOT NULL,
                       ADDRESS        CHAR(50),
                       SALARY         REAL);''')
    
    print("Table created successfully")
    
    conn.commit()
    
    for i in range(1, 20000):
        # 生成5位随机字符串
        name = ''.join(random.sample(string.ascii_letters + string.digits, 5))
        age = random.randint(18, 80)
        address = random.sample(address_list, 1)[0]
        salary = random.randrange(5000, 100000)
        cur.execute(f"INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) "
                    f"VALUES ({++i}, '{name}', {age}, '{address}', {salary} )")
    
    
    conn.commit()
    print("Records created successfully")
    
    conn.close()

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

**1. 执行create\_data.py文件**

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

![20200528184837247.png][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70][]

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