7 Star 0 Fork 5

openGauss / openGauss-sqlalchemy

Create your Gitee Account
Explore and code with more than 8 million developers,Free private repositories !:)
Sign up
Clone or Download
contribute
Sync branch
Cancel
Notice: Creating folder will generate an empty file .keep, because not support in Git
Loading...
README.en.md

openGauss-sqlalchemy

OpenGauss dialect for SQLAlchemy.

This project has been tested with test suites of SQLAlchemy.

Dependency for opengauss

Installation

# From pypi
>>> python -m pip install opengauss-sqlalchemy

# From source code
>>> python setup.py install

Usage

Create an DSN(Data Source Name) that points to your OpenGauss database.

>>> import sqlalchemy as sa
# With centralized mode
>>> sa.create_engine('opengauss://username:password@host:port/database_name')
# Or
>>> sa.create_engine('opengauss+psycopg2://username:password@host:port/database_name')
# With distributed mode
>>> sa.create_engine('opengauss+dc_psycopg2://username:password@host:port/database_name')
# Or
>>> sa.create_engine('opengauss+dc_psycopg2://username:password@/database_name?host=hostA:portA&host=hostB:portB')

See the OpenGauss DeveloperGuide for more infomation.

Features For Centralized and Distributed OpenGauss

Index

  • Index with USING method
tbl = Table("testtbl", m, Column("data", String))
Index("test_idx1", tbl.c.data, opengauss_using="btree")
  • Index with column expression
tbl = Table(
    "testtbl",
    m,
    Column("data", String),
    Column("data2", Integer, key="d2"),
)

Index(
    "test_idx1",
    tbl.c.data,
    tbl.c.d2,
    opengauss_ops={"data": "text_pattern_ops", "d2": "int4_ops"},
)
  • Index with LOCAL, only available for index on a partitioned table
tbl = Table(
    "testtbl",
    m,
    Column("data", Integer),
    opengauss_partition_by="RANGE (data) ..."
)
Index("test_idx1", tbl.c.data, opengauss_local=[""])

Index(
    "test_idx2",
    tbl.c.data,
    opengauss_local=[
        "PARTITION data_index1",
        "PARTITION data_index2 TABLESPACE example3",
    ]
)
  • Index with WITH
tbl = Table("testtbl", m, Column("data", String))
Index("test_idx1", tbl.c.data, opengauss_with={"fillfactor": 50})
  • Index with TABLESPACE
tbl = Table("testtbl", m, Column("data", String))
Index("test_idx1", tbl.c.data, opengauss_tablespace="sometablespace")
  • Index with WHERE, unsupported for index on a partitioned table
tbl = Table("testtbl", m, Column("data", Integer))
Index(
    "test_idx1",
    tbl.c.data,
    opengauss_where=and_(tbl.c.data > 5, tbl.c.data < 10),
)

Table

  • Table with WITH ({storage_parameter = value})
Table("some_table", ..., opengauss_with={"storage_parameter": "value"})
  • Table with ON COMMIT
Table("some_talbe", ..., prefixes=["TEMPORARY"], opengauss_on_commit="PRESERVE ROWS")
  • Table with COMPRESS
Table("some_talbe", ..., opengauss_with={"ORIENTATION": "COLUMN"}, opengauss_compress=True)
  • Table with TABLESPACE tablespace_name
Table("some_talbe", ..., opengauss_tablespace="tablespace_name")
  • Table with PARTITION BY
Table("some_talbe", ..., opengauss_partition_by="RANGE(column_name) "
        "(PARTITION P1 VALUES LESS THAN(10), "
        "PARTITION P2 VALUES LESS THAN(MAXVALUE))")
  • Table with ENABLE ROW MOVEMENT
Table("some_talbe", ..., opengauss_partition_by="RANGE(column_name) ...",
    opengauss_enable_row_movement=True)

Features For Centralized OpenGauss

Index

  • Index with CONCURRENTLY
tbl = Table("testtbl", m, Column("data", Integer))
Index("test_idx1", tbl.c.data, opengauss_concurrently=True)

Features For Distributed OpenGauss

TABLE

  • Table with DISTRIBUTE BY
Table("some_table", ..., opengauss_distribute_by="HASH(column_name)")

NOTE: table without distributable columns will be set with "DISTRIBUTE BY REPLICATION"

  • Table with TO GROUP
Table("some_table", ..., opengauss_to="GROUP group_name")

Releasing

Build python wheel

>>> python -m pip install wheel
>>> python setup.py bdist_wheel

Testing

  1. Set environment with export LD_LIBRARY_PATH= and export PYTHONPATH= to your path of package psycopg2.
  2. Install opengauss and update configuration, see "Steps to install and config opengauss for testing".
  3. Execute tox -e py38.

Steps to install and config centralized opengauss for testing

  1. Add OS user for opengauss >>> useradd omm -g dbgrp
  2. Change owner of opengauss dir >>> chown omm:dbgrp ${db_dir} -R
  3. Switch to user omm >>> su - omm
  4. Install opengauss >>> sh install.sh -w ${db_password} -p 37200
  5. Start opengauss >>> gs_ctl start -D ${db_dir}/data/single_node/
  6. Login opengauss >>> gsql -d postgres -p 37200
  7. Create database user and create database & schema for testing
openGauss=# create user scott identified by 'Tiger123';
openGauss=# create database test with owner=scott encoding='utf8' template=template0;
openGauss=# GRANT ALL PRIVILEGES TO scott;
openGauss=# ALTER DATABASE test SET default_text_search_config = 'pg_catalog.english';
openGauss=# \c test
test=# create schema test_schema AUTHORIZATION scott;
test=# create schema test_schema_2 AUTHORIZATION scott;
test=# \q
  1. Config opengauss
>>> gs_guc set -D ${db_dir}/data/single_node/ -c "ssl=off"
>>> gs_guc set -D ${db_dir}/data/single_node/ -c "max_prepared_transactions = 100"
>>> gs_guc reload -D ${db_dir}/data/single_node/ -h "local all scott sha256"
>>> gs_guc reload -D ${db_dir}/data/single_node/ -h "host all scott 127.0.0.1/32 sha256"
>>> gs_guc reload -D ${db_dir}/data/single_node/ -h "host all scott 0.0.0.0/0 sha256"
>>> gs_ctl stop -D ${db_dir}/data/single_node/
>>> gs_tl start -D ${db_dir}/data/single_node/
  1. Optional: enable log_statement
>>> gs_guc set -D ${db_dir}/data/single_node/ -c "log_min_error_statement = error"
>>> gs_guc set -D ${db_dir}/data/single_node/ -c "log_statement = 'all'"

About

No description expand collapse
Cancel

Releases (2)

All

Contributors

All

Activities

Load More
can not load any more
1
https://gitee.com/opengauss/openGauss-sqlalchemy.git
git@gitee.com:opengauss/openGauss-sqlalchemy.git
opengauss
openGauss-sqlalchemy
openGauss-sqlalchemy
master

Search

E71a60c3 8189591 Df7b7c6b 8189591