Python利用Psycopg2模块将Excel表格数据导入Postgressql

1 切记踩坑点

error_tup = () 应该为元组，而不是字典

因为字典的value是可变类型，for循环时，列表添加字典时，如果在for 循环中对字典的值进行修改，此时列表中添加的所有的字典的值都被修改成相同的值了，即字典的值的地址可以修改，故把列表中所有的字典的值修改了，所以要用元祖不可变类型的值来替换字典。

此时的结构应该是[(),(),(),()...]

 

import xlrd
import psycopg2
import redis
conn = redis.Redis(host='localhost', port=6379)
pipe = conn.pipeline()
def check_data():
    book = xlrd.open_workbook('test.xlsx')
    sheet = book.sheet_by_name("Sheet1")
    conn = psycopg2.connect(dbname="ggg",
                            user='postgres',
                            password='521314',
                            host="localhost",
                            port=5432)
    count = 0
    cur = conn.cursor()
    error_tup = ()
    error_list = []
    for r in range(1, sheet.nrows):
        name = sheet.cell(r, 0).value
        email = sheet.cell(r, 1).value
        data = (re.match(r'^[a-zA-Z0-9_-]+(.[a-zA-Z0-9_-]+){0,4}@[a-zA-Z0-9_-]+(.[a-zA-Z0-9_-]+){0,4}$', email)) and 1 or 0
        if not ((email and data) and name):
            count += 1
            error_tup = (name, email)
            error_list.append(error_tup)
            continue
        pipe.hset('name', name, email)
    if not count:
        values = []
        for i in pipe.hget("name", name).command_stack:
            try:
                value = (i[0][2], i[0][3])
                values.append(value)
            except Exception:
                pass
        query = "insert into users_employees (name, email) values(%s,%s);"
        # 批量提交数据 单条数据提交用cur.execute(query, value)
        cur.executemany(query, values)
        conn.commit()
        values.clear()
        conn.close()
        cur.close()
        columns = str(sheet.ncols)
        rows = str(sheet.nrows)
        print("{}行数据错误".format(count))
        print("导入" + columns + "列" + rows + "行数据到pgsql数据库！！！")
        return "{}行数据错误".format(count)
    print(error_list[:20])
    return error_list[:20]


if __name__ == '__main__':
    check_data()