PostgreSQL数据导入/导出实用程序
pgmerge的Python项目详细描述
pgmerge-PostgreSQL数据导入和合并实用程序
此实用程序的主要目的是管理一组csv文件,这些文件与postgresql数据库中的表相对应。然后,这些csv文件中的每一个都可以被合并到它们的表中,这意味着将发生以下过程(也称为upsert操作):
- 表中尚未存在主键的行将被导入。
- 将忽略丢失或未更改的行。
然后,pgmerge还可以以导入所需的相同格式导出数据。
这些功能允许您在具有相同架构的数据库之间移动数据,以使它们保持最新和同步,尽管它不包括处理已删除的数据。
$ pgmerge --help
Usage: pgmerge [OPTIONS] COMMAND [ARGS]...
Merges data in CSV files into a Postgresql database.
Options:
--version Show the version and exit.
--help Show this message and exit.
Commands:
export Export each table to a CSV file.
import Import/merge each CSV file into a table.
inspect Inspect database schema in various ways.
导入
$ pgmerge import --help
Usage: pgmerge import [OPTIONS] DIRECTORY [TABLES]...
Import/merge each CSV file into a table.
All CSV files need the same name as their matching table and have to be located
in the given directory. If one or more tables are specified then only they will
be used, otherwise all tables found will be selected.
Options:
-d, --dbname TEXT Database name to connect to. [required]
-h, --host TEXT Database server host or socket directory.
[default: localhost]
-p, --port TEXT Database server port. [default: 5432]
-U, --username TEXT Database user name. [default: postgres]
-s, --schema TEXT Database schema to use. [default: public]
-w, --no-password Never prompt for password (e.g. peer
authentication).
-W, --password TEXT Database password (default is to prompt for
password or read config).
-L, --uri TEXT Connection URI can be used instead of specifying
parameters separately (also sets --no-password).
-f, --ignore-cycles Don't stop import when cycles are detected in
schema (will still fail if there are cycles in
data)
-F, --disable-foreign-keys Disable foreign key constraint checking during
import (necessary if you have cycles, but requires
superuser rights).
-c, --config PATH Config file for customizing how tables are
imported/exported.
-i, --include-dependent-tables When selecting specific tables, also include all
tables on which they depend due to foreign key
constraints.
--help Show this message and exit.
安装
WARNING: the reliability of this utility is not guaranteed and loss or corruption of data is always a possibility.
从pypi安装
在系统上安装Python 3
后,您可以运行:
pip install pgmerge
(您的pip --version
必须是9.0或更高版本)。要测试安装是否有效,请运行:
pgmerge --help
您可以随时使用以下命令卸载:
pip uninstall pgmerge
从github安装
直接从github安装最新代码:
pip install git+https://github.com/samuller/pgmerge
问题
如果您在安装和运行一个基于debian的linux时遇到问题,该linux使用Python 2
作为其系统默认值,那么您可能需要运行:
sudo apt install libpq-dev python3-pip python3-setuptools
sudo -H pip3 install pgmerge