sqlexecx


Namesqlexecx JSON
Version 0.8.0 PyPI version JSON
download
home_pagehttps://gitee.com/summry/sqlexecx
SummaryA thread safe sql executor for Python with connection pool. It helps you automatically manage connections and transactions. It blocked the differences in various databases. Support MySQL, PostgreSQL, SQLite etc.
upload_time2024-05-11 09:47:42
maintainerNone
docs_urlNone
authorsummy
requires_python>=3.6
licenseNone
keywords sql mysql postgresql sqlite database python rdb
VCS
bugtrack_url
requirements No requirements were recorded.
Travis-CI No Travis.
coveralls test coverage No coveralls. 
            Usage Sample
''''''''''''

.. code:: python

       import sqlexec as db

       if __name__ == '__main__':
           db.init('test.db', driver='sqlite3', show_sql=True, debug=True)

           # or
           db.init("postgres://user:password@127.0.0.1:5432/testdb", driver='psycopg2', pool_size=5, show_sql=True, debug=True)

           # or
           db.init(host='127.0.0.1', port='5432', user='xxx', password='xxx', database='testdb', show_sql=True, driver='psycopg2')

           effected_rowcount = db.insert(table='person', name='zhangsan', age=15)

           # if driver is 'pymysql' or 'mysql.connector' of MySQL, the select_key is 'SELECT LAST_INSERT_ID()'
           select_key = "SELECT currval('person_id_seq')"

           id = db.save(select_key=select_key, table='person', name='lisi', age=26)

           id = db.save_sql(select_key, 'INSERT INTO person(name, age) VALUES(?,?)', 'wangwu', 38)

           id = db.save_sql(select_key, 'INSERT INTO person(name, age) VALUES(:name, :age)', name='zhaoliu', age=45)

           count = db.get('select count(1) from person')
           # result: 4

           count = db.sql('select count(1) from person').get()
           # result: 4

           persons = db.select('select id, name, age from person')
           # result:
           # (3, 'zhangsan', 15)
           # (4, 'lisi', 26)
           # (5, 'wangwu', 38)
           # (6, 'zhaoliu', 45)

           persons = db.sql('select id, name, age from person').select()
           # result:
           # (3, 'zhangsan', 15)
           # (4, 'lisi', 26)
           # (5, 'wangwu', 38)
           # (6, 'zhaoliu', 45)

           persons = db.table('person').select('id', 'name', 'age')
           # result:
           # (3, 'zhangsan', 15)
           # (4, 'lisi', 26)
           # (5, 'wangwu', 38)
           # (6, 'zhaoliu', 45)

           persons = db.select_one('select id, name, age from person where name = ?', 'zhangsan')
           # result:
           # (3, 'zhangsan', 15)

           persons = db.sql('select id, name, age from person where name = ?').select_one('zhangsan')
           # result:
           # (3, 'zhangsan', 15)

           persons = db.select('select id, name, age from person where name = :name', name='zhangsan')
           # result:
           # [(3, 'zhangsan', 15)]

           persons = db.sql('select id, name, age from person where name = :name').select(name='zhangsan')
           # result:
           # [(3, 'zhangsan', 15)]

           persons = db.sql('select id, name, age from person where name = :name').param(name='zhangsan').select()
           # result:
           # [(3, 'zhangsan', 15)]

           persons = db.table('person').where(name__eq='zhangsan').select('id', 'name', 'age')
           # result:
           # [(3, 'zhangsan', 15)]

           persons = db.query('select id, name, age from person')
           # result:
           # {'id': 3, 'name': 'zhangsan', 'age': 15}
           # {'id': 4, 'name': 'lisi', 'age': 26}
           # {'id': 5, 'name': 'wangwu', 'age': 38}
           # {'id': 6, 'name': 'zhaoliu', 'age': 45}

           persons = db.sql('select id, name, age from person').query()
           # result:
           # {'id': 3, 'name': 'zhangsan', 'age': 15}
           # {'id': 4, 'name': 'lisi', 'age': 26}
           # {'id': 5, 'name': 'wangwu', 'age': 38}
           # {'id': 6, 'name': 'zhaoliu', 'age': 45}

           persons = db.query_one('select id, name, age from person where name = ?', 'zhangsan')
           # result:
           # {'id': 3, 'name': 'zhangsan', 'age': 15}

           persons = db.sql('select id, name, age from person where name = ?').query_one('zhangsan')
           # result:
           # {'id': 3, 'name': 'zhangsan', 'age': 15}

           persons = db.query('select id, name, age from person where name = :name', name='zhangsan')
           # result:
           # [{'id': 3, 'name': 'zhangsan', 'age': 15}]

           persons = db.sql('select id, name, age from person where name = :name').query(name='zhangsan')
           # result:
           # [{'id': 3, 'name': 'zhangsan', 'age': 15}]

           persons = db.sql('select id, name, age from person where name = :name').param(name='zhangsan').query()
           # result:
           # [{'id': 3, 'name': 'zhangsan', 'age': 15}]

           persons = db.table('person').columns('id', 'name', 'age').where(name='zhangsan').query()
           # result:
           # [{'id': 3, 'name': 'zhangsan', 'age': 15}]

           effected_rowcount = db.table('person').where(name='zhangsan').update(name='xxx', age=45)

           effected_rowcount = db.table('person').where(id=6).delete()
           count = db.table('person').count())
           # result: 3

           effected_rowcount = db.execute('delete from person where id = :id', id=5)
           count = db.get('select count(1) from person')
           # result: 2

           effected_rowcount = db.sql('delete from person where id = ?').execute(4)
           count = db.sql('select count(1) from person').get()
           # result: 1

           effected_rowcount = db.sql('delete from person where id = :id').execute(id=3)
           count = db.sql('select count(1) from person').get()
           # result: 0

           # select data save as csv
           db.sql('select name, age from person WHERE name = ?').load('张三').to_csv('test.csv')

           db.sql('select name, age from person WHERE name = ?').param('张三').to_csv('test.csv')

           # insert from csv
           db.table('person').insert_from_csv('test.csv')

           # select data transform to DataFrame of pandas
           df = db.sql('select name, age from person WHERE name = :name').load(name='张三').to_df()

           df = db.sql('select name, age from person WHERE name = :name').param(name='张三').to_df()

           # insert from DataFrame of pandas
           db.table('person').insert_from_df(dataframe)

           # select data save as json
           db.sql('select name, age from person WHERE name = ?').load('张三').to_json('test.json')

           db.sql('select name, age from person WHERE name = ?').param('张三').to_json('test.json')

           # insert from json
           db.table('person').insert_from_json('test.json')

           db.close()

Transaction
'''''''''''

.. code:: python

       from sqlexec import with_transaction, transaction

       @with_transaction
       def test_transaction():
           insert_func(....)
           update_func(....)


       def test_transaction2():
           with transaction():
               insert_func(....)
               update_func(....)


If you want to operate MySQL database like Mybatis, may be you need MySqlx: https://pypi.org/project/mysqlx

If you want to operate PostgreSQL database like Mybatis, may be you need PgSqlx: https://pypi.org/project/pgsqlx

If you want to execute SQL like Mybatis, may be you need sqlx-batis: https://pypi.org/project/sqlx-batis

Raw data

            {
    "_id": null,
    "home_page": "https://gitee.com/summry/sqlexecx",
    "name": "sqlexecx",
    "maintainer": null,
    "docs_url": null,
    "requires_python": ">=3.6",
    "maintainer_email": null,
    "keywords": "SQL, MySQL, PostgreSQL, SQLite, Database, Python, RDB",
    "author": "summy",
    "author_email": "xiazhongbiao@126.com",
    "download_url": "https://files.pythonhosted.org/packages/eb/c9/ad53ef1ba835452bf73cd33ea4e96c7a680ebd4df4ae4276beb39749d38e/sqlexecx-0.8.0.tar.gz",
    "platform": null,
    "description": "Usage Sample\n''''''''''''\n\n.. code:: python\n\n       import sqlexec as db\n\n       if __name__ == '__main__':\n           db.init('test.db', driver='sqlite3', show_sql=True, debug=True)\n\n           # or\n           db.init(\"postgres://user:password@127.0.0.1:5432/testdb\", driver='psycopg2', pool_size=5, show_sql=True, debug=True)\n\n           # or\n           db.init(host='127.0.0.1', port='5432', user='xxx', password='xxx', database='testdb', show_sql=True, driver='psycopg2')\n\n           effected_rowcount = db.insert(table='person', name='zhangsan', age=15)\n\n           # if driver is 'pymysql' or 'mysql.connector' of MySQL, the select_key is 'SELECT LAST_INSERT_ID()'\n           select_key = \"SELECT currval('person_id_seq')\"\n\n           id = db.save(select_key=select_key, table='person', name='lisi', age=26)\n\n           id = db.save_sql(select_key, 'INSERT INTO person(name, age) VALUES(?,?)', 'wangwu', 38)\n\n           id = db.save_sql(select_key, 'INSERT INTO person(name, age) VALUES(:name, :age)', name='zhaoliu', age=45)\n\n           count = db.get('select count(1) from person')\n           # result: 4\n\n           count = db.sql('select count(1) from person').get()\n           # result: 4\n\n           persons = db.select('select id, name, age from person')\n           # result:\n           # (3, 'zhangsan', 15)\n           # (4, 'lisi', 26)\n           # (5, 'wangwu', 38)\n           # (6, 'zhaoliu', 45)\n\n           persons = db.sql('select id, name, age from person').select()\n           # result:\n           # (3, 'zhangsan', 15)\n           # (4, 'lisi', 26)\n           # (5, 'wangwu', 38)\n           # (6, 'zhaoliu', 45)\n\n           persons = db.table('person').select('id', 'name', 'age')\n           # result:\n           # (3, 'zhangsan', 15)\n           # (4, 'lisi', 26)\n           # (5, 'wangwu', 38)\n           # (6, 'zhaoliu', 45)\n\n           persons = db.select_one('select id, name, age from person where name = ?', 'zhangsan')\n           # result:\n           # (3, 'zhangsan', 15)\n\n           persons = db.sql('select id, name, age from person where name = ?').select_one('zhangsan')\n           # result:\n           # (3, 'zhangsan', 15)\n\n           persons = db.select('select id, name, age from person where name = :name', name='zhangsan')\n           # result:\n           # [(3, 'zhangsan', 15)]\n\n           persons = db.sql('select id, name, age from person where name = :name').select(name='zhangsan')\n           # result:\n           # [(3, 'zhangsan', 15)]\n\n           persons = db.sql('select id, name, age from person where name = :name').param(name='zhangsan').select()\n           # result:\n           # [(3, 'zhangsan', 15)]\n\n           persons = db.table('person').where(name__eq='zhangsan').select('id', 'name', 'age')\n           # result:\n           # [(3, 'zhangsan', 15)]\n\n           persons = db.query('select id, name, age from person')\n           # result:\n           # {'id': 3, 'name': 'zhangsan', 'age': 15}\n           # {'id': 4, 'name': 'lisi', 'age': 26}\n           # {'id': 5, 'name': 'wangwu', 'age': 38}\n           # {'id': 6, 'name': 'zhaoliu', 'age': 45}\n\n           persons = db.sql('select id, name, age from person').query()\n           # result:\n           # {'id': 3, 'name': 'zhangsan', 'age': 15}\n           # {'id': 4, 'name': 'lisi', 'age': 26}\n           # {'id': 5, 'name': 'wangwu', 'age': 38}\n           # {'id': 6, 'name': 'zhaoliu', 'age': 45}\n\n           persons = db.query_one('select id, name, age from person where name = ?', 'zhangsan')\n           # result:\n           # {'id': 3, 'name': 'zhangsan', 'age': 15}\n\n           persons = db.sql('select id, name, age from person where name = ?').query_one('zhangsan')\n           # result:\n           # {'id': 3, 'name': 'zhangsan', 'age': 15}\n\n           persons = db.query('select id, name, age from person where name = :name', name='zhangsan')\n           # result:\n           # [{'id': 3, 'name': 'zhangsan', 'age': 15}]\n\n           persons = db.sql('select id, name, age from person where name = :name').query(name='zhangsan')\n           # result:\n           # [{'id': 3, 'name': 'zhangsan', 'age': 15}]\n\n           persons = db.sql('select id, name, age from person where name = :name').param(name='zhangsan').query()\n           # result:\n           # [{'id': 3, 'name': 'zhangsan', 'age': 15}]\n\n           persons = db.table('person').columns('id', 'name', 'age').where(name='zhangsan').query()\n           # result:\n           # [{'id': 3, 'name': 'zhangsan', 'age': 15}]\n\n           effected_rowcount = db.table('person').where(name='zhangsan').update(name='xxx', age=45)\n\n           effected_rowcount = db.table('person').where(id=6).delete()\n           count = db.table('person').count())\n           # result: 3\n\n           effected_rowcount = db.execute('delete from person where id = :id', id=5)\n           count = db.get('select count(1) from person')\n           # result: 2\n\n           effected_rowcount = db.sql('delete from person where id = ?').execute(4)\n           count = db.sql('select count(1) from person').get()\n           # result: 1\n\n           effected_rowcount = db.sql('delete from person where id = :id').execute(id=3)\n           count = db.sql('select count(1) from person').get()\n           # result: 0\n\n           # select data save as csv\n           db.sql('select name, age from person WHERE name = ?').load('\u5f20\u4e09').to_csv('test.csv')\n\n           db.sql('select name, age from person WHERE name = ?').param('\u5f20\u4e09').to_csv('test.csv')\n\n           # insert from csv\n           db.table('person').insert_from_csv('test.csv')\n\n           # select data transform to DataFrame of pandas\n           df = db.sql('select name, age from person WHERE name = :name').load(name='\u5f20\u4e09').to_df()\n\n           df = db.sql('select name, age from person WHERE name = :name').param(name='\u5f20\u4e09').to_df()\n\n           # insert from DataFrame of pandas\n           db.table('person').insert_from_df(dataframe)\n\n           # select data save as json\n           db.sql('select name, age from person WHERE name = ?').load('\u5f20\u4e09').to_json('test.json')\n\n           db.sql('select name, age from person WHERE name = ?').param('\u5f20\u4e09').to_json('test.json')\n\n           # insert from json\n           db.table('person').insert_from_json('test.json')\n\n           db.close()\n\nTransaction\n'''''''''''\n\n.. code:: python\n\n       from sqlexec import with_transaction, transaction\n\n       @with_transaction\n       def test_transaction():\n           insert_func(....)\n           update_func(....)\n\n\n       def test_transaction2():\n           with transaction():\n               insert_func(....)\n               update_func(....)\n\n\nIf you want to operate MySQL database like Mybatis, may be you need MySqlx: https://pypi.org/project/mysqlx\n\nIf you want to operate PostgreSQL database like Mybatis, may be you need PgSqlx: https://pypi.org/project/pgsqlx\n\nIf you want to execute SQL like Mybatis, may be you need sqlx-batis: https://pypi.org/project/sqlx-batis\n\n\n",
    "bugtrack_url": null,
    "license": null,
    "summary": "A thread safe sql executor for Python with connection pool. It helps you automatically manage connections and transactions.     It blocked the differences in various databases. Support MySQL, PostgreSQL, SQLite etc.",
    "version": "0.8.0",
    "project_urls": {
        "Homepage": "https://gitee.com/summry/sqlexecx"
    },
    "split_keywords": [
        "sql",
        " mysql",
        " postgresql",
        " sqlite",
        " database",
        " python",
        " rdb"
    ],
    "urls": [
        {
            "comment_text": "",
            "digests": {
                "blake2b_256": "ebc9ad53ef1ba835452bf73cd33ea4e96c7a680ebd4df4ae4276beb39749d38e",
                "md5": "8e34ef7d2163c9ffcda7480ed14bfc84",
                "sha256": "4fd4b992f8b48f84d71757a3ad57df736e395ad8b201a4a28eef6e08e58d8f4f"
            },
            "downloads": -1,
            "filename": "sqlexecx-0.8.0.tar.gz",
            "has_sig": false,
            "md5_digest": "8e34ef7d2163c9ffcda7480ed14bfc84",
            "packagetype": "sdist",
            "python_version": "source",
            "requires_python": ">=3.6",
            "size": 20262,
            "upload_time": "2024-05-11T09:47:42",
            "upload_time_iso_8601": "2024-05-11T09:47:42.733459Z",
            "url": "https://files.pythonhosted.org/packages/eb/c9/ad53ef1ba835452bf73cd33ea4e96c7a680ebd4df4ae4276beb39749d38e/sqlexecx-0.8.0.tar.gz",
            "yanked": false,
            "yanked_reason": null
        }
    ],
    "upload_time": "2024-05-11 09:47:42",
    "github": false,
    "gitlab": false,
    "bitbucket": false,
    "codeberg": false,
    "lcname": "sqlexecx"
}
summy
Elapsed time: 0.25211s