Python：读取 csv 文件并插入到 PostgreSQL 数据库

目录

一、读取 csv 文件

二、创建数据库链接

三、读取 csv 数据并插入到数据库

---

一、读取 csv 文件

Python：使用 csv 模块读写 csv 文件

二、创建数据库链接

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

import sqlalchemy
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Record(Base):
    __tablename__ = 'files'

    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)
    name = sqlalchemy.Column(sqlalchemy.String(256), nullable=True)
    size = sqlalchemy.Column(sqlalchemy.BigInteger, nullable=True)
    path = sqlalchemy.Column(sqlalchemy.String(256), nullable=True)
    mtime = sqlalchemy.Column(sqlalchemy.DateTime, nullable=True)

    def __init__(self, filename, filesize, filepath, filemt):
        '''
        "名称","大小","路径","修改时间"
        '''
        self.name = filename
        self.size = int(filesize)
        self.path = filepath
        self.mtime = filemt

def get_session():
    engine = sqlalchemy.create_engine ("postgresql://postgres:064f0901-c561-44b9-a4bb-a893d23cc505@127.0.0.1/dogdb")
    metadata = sqlalchemy.schema.MetaData(bind=engine)
    Base.metadata.create_all(engine)
    Sess = orm.sessionmaker(bind=engine)
    session = Sess()

    return session

三、读取 csv 数据并插入到数据库

def store_to_db(filename):
    session = get_session() # 使用 get_session() 函数获取 session

    with open(filename, 'r') as f:
        reader = csv.reader(f)
        header_row = next(reader)
        cnt = 0;
        for line in reader:
            record = Record(line[0], line[1], line[2], line[3])
            try:
                session.add(record)
                cnt += 1
                if cnt % 1000 == 0:
                    session.commit()
            except sqlalchemy.exc.PendingRollbackError:
                session.rollback()
                session.add(record)
            except Exception as e:
                print(e)
                break
    session.commit()
    session.close()


if "__main__" == __name__:
    filename = sys.argv[1]
    store_to_db(filename)

运行脚本：

$ python insert-files.py files.csv

查看数据库中的数据：

[select id,name,mtime from files order by id]
 id |       name        |        mtime        
----+-------------------+---------------------
  1 | Security.evtx     | 2021-02-24 07:23:48
  2 | MRT.exe           | 2017-12-24 21:45:48
  3 | MRT-KB890830.exe  | 2017-12-24 21:45:50
  4 | NvCplSetupInt.exe | 2016-09-12 21:15:30
  5 | SOFTWARE          | 2021-02-20 09:31:27
  6 | SOFTWARE          | 2021-02-21 01:32:16
  7 | SRUDB.dat         | 2021-02-24 21:20:00
  8 | RCORES64.dat      | 2020-04-08 04:44:38
  9 | RCORES64.dat      | 2015-08-04 00:21:58
 10 | BootCKCL.etl      | 2021-02-20 09:33:51
(10 rows)

---