Metadata-Version: 2.4
Name: psql_sqltool
Version: 0.1.3
Summary: Sql Tool (PostgreSQL version)
Home-page: https://github.com/0716gzs/psql_sqltool
Author: attr: psql_sqltool.__author__
Author-email: 0716gzs@gmail.com
License: BSD-3-Clause
Project-URL: Source, https://github.com/0716gzs/psql_sqltool
Classifier: Development Status :: 5 - Production/Stable
Classifier: Environment :: Web Environment
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: BSD License
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: 3.6
Classifier: Programming Language :: Python :: 3.7
Classifier: Programming Language :: Python :: 3.8
Classifier: Topic :: Database
Requires-Python: >=3.6
Description-Content-Type: text/x-rst
License-File: LICENSE
Requires-Dist: psycopg2-binary
Dynamic: license-file

psql_sqltool
============

PostgreSQL 版本的 psql_sqltool。本项目由 `007gzs/psql_sqltool <https://github.com/007gzs/psql_sqltool>`_ (基于 pymysql) 改写而来,
全部适配为 PostgreSQL 语法, 底层使用 `psycopg2 <https://www.psycopg.org/>`_。

作者: 0716gzs (https://github.com/0716gzs)
项目地址: https://github.com/0716gzs/psql_sqltool

特性
----

* PostgreSQL 连接池 (fork 安全, 自动归还连接)
* 链式 SQL 生成器: ``INSERT`` / ``SELECT`` / ``UPDATE`` / ``DELETE``
* 批量 SQL 生成, 适合把数据落盘成导入脚本
* ``ON CONFLICT ... DO UPDATE`` (对应 MySQL 的 ``ON DUPLICATE KEY UPDATE``)
* 自增主键 / 唯一冲突的内存去重

安装
----

.. code-block:: bash

    pip install psycopg2-binary
    python setup.py install

快速上手
--------

.. code-block:: python

    from psql_sqltool.postgres_client import PostgresClient

    client = PostgresClient(
        host='127.0.0.1',
        port=5432,
        user='postgres',
        password='postgres',
        dbname='demo',
    )

    # 查询
    rows = client.select(table_name='user', wheres={'id': 1})

    # 插入 (参数化)
    client.insert(
        [{'id': 1, 'name': 'alice'}, {'id': 2, 'name': 'bob'}],
        table_name='user',
        field_list=('id', 'name'),
    )

    # 插入并冲突更新 (upsert)
    client.insert(
        [{'id': 1, 'name': 'alice2'}],
        table_name='user',
        field_list=('id', 'name'),
        on_conflict_fields=('id',),
        on_duplicate_key_update_fields=('name',),
    )

    # 更新 / 删除
    client.update(table_name='user', update_columns={'name': 'alice'}, wheres={'id': 1})
    client.delete(table_name='user', wheres={'id': 1})

批量生成 SQL (导出脚本)
-----------------------

.. code-block:: python

    from psql_sqltool.sql_gen import GenSqlAutoId

    class GenUser(GenSqlAutoId):
        TABLE_NAME = 'user'
        FIELD_LIST = ('id', 'name')

    gen = GenUser()
    gen.add_item(name='alice')
    gen.add_item(name='bob')
    for sql in gen.gen_sql():
        print(sql)

与原 MySQL 版的主要差异
------------------------

* 标识符引用: 反引号 ````` -> 双引号 ``"``
* ``ON DUPLICATE KEY UPDATE ... AS __new_data__`` -> ``ON CONFLICT (...) DO UPDATE SET ... EXCLUDED...``,
  且 PostgreSQL 必须显式指定冲突目标 ``on_conflict_fields``
* 分页: ``LIMIT start, size`` -> ``LIMIT size OFFSET start``
* ``INFORMATION_SCHEMA ... AUTO_INCREMENT`` -> 通过 ``pg_get_serial_sequence`` 读取序列
* 底层驱动: ``pymysql`` -> ``psycopg2``

License
-------

BSD-3-Clause
