| Name | zodbc JSON |
| Version |
0.9.1
JSON |
| download |
| home_page | None |
| Summary | DBAPI2-like ODBC client with Apache Arrow support |
| upload_time | 2025-10-18 20:37:40 |
| maintainer | None |
| docs_url | None |
| author | Felix Graßl |
| requires_python | >=3.10 |
| license | None |
| keywords |
odbc
arrow
mssql
sql server
|
| VCS |
 |
| bugtrack_url |
|
| requirements |
No requirements were recorded.
|
| Travis-CI |
No Travis.
|
| coveralls test coverage |
No coveralls.
|
# zodbc
This odbc client tries to be compatible with the DB API 2 Specification, while also treating pyarrow compatibility as a first class citizen. At the moment it is tested with the msodbc driver. Compilation and installation is made easy by ziglang being a simple build dependency. The only system dependencies for compilation should be development headers for unixodbc (if on linux) and python.
# Installation
```bash
pip install zodbc
```
# Features
## DB API 2
```py
import zodbc
import os
con = zodbc.connect(os.environ["ODBC_CONSTR"]) # Connect with odbc connection string or DSN
cur = con.cursor() # Cursor, which translates to an odbc statement handle
# python parameters & return values, data types like str, int, bool, UUID, decimal, date, time, datetime
cur.execute("select ? a", [42])
assert cur.fetchone()[0] == 42
# Short hand alternative for getting column names from cur.description
assert [c[0] for c in cur.description] == cur.column_names == ["a"]
con.commit() # Transactions, autocommit is disabled by default
```
## Additional fetch methods
The DB API 2 fetch methods `fetchone`, `fetchmany` and `fetchall` return (lists of) tuples. Additionally, there is `fetch_named` which returns a list of named tuples, which allows accessing fields by attribute name like `row.a` as well as `fetch_dict` which returns a list of dicts. `fetch_dict` requires unique column names in the query. These methods are implemented directly in Zig and should therefore be more efficient than fetching tuples and converting them in python.
## Apache Arrow
A query can be executed for every row in an Arrow RecordBatch.
```py
import pyarrow as pa
cur.execute("drop table if exists t1")
cur.execute("create table t1(a int)")
cur.executemany_arrow(
"insert into t1(a) values(?)",
pa.RecordBatch.from_arrays([pa.array([1, 2, 3])], ["a"])
)
cur.execute("select * from t1").fetchall() # [(1,), (2,), (3,)]
```
Arrow RecordBatches can be fetched from a query result.
```py
cur.execute("select 1 a").fetch_arrow()
```
Arrow RecordBatches can be used as a table valued parameter for MS SQL.
```py
cur.execute("drop type if exists test_tabletype")
cur.execute("create type test_tabletype as table(a int)")
con.commit()
assert cur.execute(
"select sum(a) from ? where a <= ?",
[
zodbc.ArrowTVP(
zodbc.ArrowTVPType.from_name("test_tabletype"),
pa.RecordBatch.from_arrays([pa.array([1, 2, 3])], ["a"]),
),
2,
]
).fetchone()[0] == 3
```
# Differences with pyodbc
- UUID is always returned as a python UUID object (no pyodbc.native_uuid global variable)
- No output conversion (add_output_converter connection method)
- More explicit:
- No `execute(query, param1, param2)` => params must be passed as sequence
- No Cursor.commit, instead use Cursor.connection.commit
- Connection.getinfo takes a string instead of the odbc enum value
Raw data
{
"_id": null,
"home_page": null,
"name": "zodbc",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.10",
"maintainer_email": null,
"keywords": "odbc, arrow, mssql, sql server",
"author": "Felix Gra\u00dfl",
"author_email": null,
"download_url": "https://files.pythonhosted.org/packages/a3/36/07f7be5aa16e35103737d7830db1c5332917e8692b20a010d506eb73985b/zodbc-0.9.1.tar.gz",
"platform": null,
"description": "# zodbc\n\nThis odbc client tries to be compatible with the DB API 2 Specification, while also treating pyarrow compatibility as a first class citizen. At the moment it is tested with the msodbc driver. Compilation and installation is made easy by ziglang being a simple build dependency. The only system dependencies for compilation should be development headers for unixodbc (if on linux) and python.\n\n\n# Installation\n\n```bash\npip install zodbc\n```\n\n\n# Features\n\n\n## DB API 2\n\n```py\nimport zodbc\nimport os\n\ncon = zodbc.connect(os.environ[\"ODBC_CONSTR\"]) # Connect with odbc connection string or DSN\ncur = con.cursor() # Cursor, which translates to an odbc statement handle\n\n# python parameters & return values, data types like str, int, bool, UUID, decimal, date, time, datetime\ncur.execute(\"select ? a\", [42])\nassert cur.fetchone()[0] == 42\n\n# Short hand alternative for getting column names from cur.description\nassert [c[0] for c in cur.description] == cur.column_names == [\"a\"]\n\ncon.commit() # Transactions, autocommit is disabled by default\n```\n\n\n## Additional fetch methods\n\nThe DB API 2 fetch methods `fetchone`, `fetchmany` and `fetchall` return (lists of) tuples. Additionally, there is `fetch_named` which returns a list of named tuples, which allows accessing fields by attribute name like `row.a` as well as `fetch_dict` which returns a list of dicts. `fetch_dict` requires unique column names in the query. These methods are implemented directly in Zig and should therefore be more efficient than fetching tuples and converting them in python.\n\n\n## Apache Arrow\n\nA query can be executed for every row in an Arrow RecordBatch.\n\n```py\nimport pyarrow as pa\n\ncur.execute(\"drop table if exists t1\")\ncur.execute(\"create table t1(a int)\")\ncur.executemany_arrow(\n \"insert into t1(a) values(?)\",\n pa.RecordBatch.from_arrays([pa.array([1, 2, 3])], [\"a\"])\n)\ncur.execute(\"select * from t1\").fetchall() # [(1,), (2,), (3,)]\n```\n\nArrow RecordBatches can be fetched from a query result.\n\n```py\ncur.execute(\"select 1 a\").fetch_arrow()\n```\n\nArrow RecordBatches can be used as a table valued parameter for MS SQL.\n\n```py\ncur.execute(\"drop type if exists test_tabletype\")\ncur.execute(\"create type test_tabletype as table(a int)\")\ncon.commit()\n\nassert cur.execute(\n \"select sum(a) from ? where a <= ?\",\n [\n zodbc.ArrowTVP(\n zodbc.ArrowTVPType.from_name(\"test_tabletype\"),\n pa.RecordBatch.from_arrays([pa.array([1, 2, 3])], [\"a\"]),\n ),\n 2,\n ]\n).fetchone()[0] == 3\n```\n\n\n# Differences with pyodbc\n\n- UUID is always returned as a python UUID object (no pyodbc.native_uuid global variable)\n- No output conversion (add_output_converter connection method)\n- More explicit:\n - No `execute(query, param1, param2)` => params must be passed as sequence\n - No Cursor.commit, instead use Cursor.connection.commit\n- Connection.getinfo takes a string instead of the odbc enum value\n",
"bugtrack_url": null,
"license": null,
"summary": "DBAPI2-like ODBC client with Apache Arrow support",
"version": "0.9.1",
"project_urls": {
"Homepage": "https://github.com/ffelixg/zodbc_py"
},
"split_keywords": [
"odbc",
" arrow",
" mssql",
" sql server"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "c95bc22c335688c460bf9c2dbcbf2d84ebcf19cf1820ed468cc5e73f6be24bd1",
"md5": "211af98be6272a7f192a5c516f7f15a3",
"sha256": "937654848744cb5f7c2cfe3938a830a93d25f741b9f4e37122402172dc7a0353"
},
"downloads": -1,
"filename": "zodbc-0.9.1-cp311-cp311-manylinux_2_38_x86_64.whl",
"has_sig": false,
"md5_digest": "211af98be6272a7f192a5c516f7f15a3",
"packagetype": "bdist_wheel",
"python_version": "cp311",
"requires_python": ">=3.10",
"size": 1397461,
"upload_time": "2025-10-18T20:37:35",
"upload_time_iso_8601": "2025-10-18T20:37:35.955631Z",
"url": "https://files.pythonhosted.org/packages/c9/5b/c22c335688c460bf9c2dbcbf2d84ebcf19cf1820ed468cc5e73f6be24bd1/zodbc-0.9.1-cp311-cp311-manylinux_2_38_x86_64.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "75450f4e40fcdd65999e6cf1de09cf882b84fb39abdb4fc010bd77c53842b1dc",
"md5": "227713b422c5adaaacc05c8a46b2ed17",
"sha256": "1cfa5bd37f7a0f36f33336b945165277d422112e7218f19394fba51eb2190534"
},
"downloads": -1,
"filename": "zodbc-0.9.1-cp312-cp312-manylinux_2_38_x86_64.whl",
"has_sig": false,
"md5_digest": "227713b422c5adaaacc05c8a46b2ed17",
"packagetype": "bdist_wheel",
"python_version": "cp312",
"requires_python": ">=3.10",
"size": 1412305,
"upload_time": "2025-10-18T20:37:37",
"upload_time_iso_8601": "2025-10-18T20:37:37.544413Z",
"url": "https://files.pythonhosted.org/packages/75/45/0f4e40fcdd65999e6cf1de09cf882b84fb39abdb4fc010bd77c53842b1dc/zodbc-0.9.1-cp312-cp312-manylinux_2_38_x86_64.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "fb712f53b32c387af5a35c1a018bd024338683f3637012522e15a4b246b3ffa7",
"md5": "53b49d19367fe17949ea450a52982323",
"sha256": "7ec2baa40376f2afd46bf7aed467e1807271f42bc76800c893a2e0e3a9e2b50a"
},
"downloads": -1,
"filename": "zodbc-0.9.1-cp313-cp313-manylinux_2_38_x86_64.whl",
"has_sig": false,
"md5_digest": "53b49d19367fe17949ea450a52982323",
"packagetype": "bdist_wheel",
"python_version": "cp313",
"requires_python": ">=3.10",
"size": 1412231,
"upload_time": "2025-10-18T20:37:39",
"upload_time_iso_8601": "2025-10-18T20:37:39.261842Z",
"url": "https://files.pythonhosted.org/packages/fb/71/2f53b32c387af5a35c1a018bd024338683f3637012522e15a4b246b3ffa7/zodbc-0.9.1-cp313-cp313-manylinux_2_38_x86_64.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "a33607f7be5aa16e35103737d7830db1c5332917e8692b20a010d506eb73985b",
"md5": "088fddbbbfe718d5f936fa64acca0811",
"sha256": "ee6d601aa706e7a6d7ba754fecd392da4bb414f6672e7f8233672f16aac07791"
},
"downloads": -1,
"filename": "zodbc-0.9.1.tar.gz",
"has_sig": false,
"md5_digest": "088fddbbbfe718d5f936fa64acca0811",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.10",
"size": 29009,
"upload_time": "2025-10-18T20:37:40",
"upload_time_iso_8601": "2025-10-18T20:37:40.370064Z",
"url": "https://files.pythonhosted.org/packages/a3/36/07f7be5aa16e35103737d7830db1c5332917e8692b20a010d506eb73985b/zodbc-0.9.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-10-18 20:37:40",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "ffelixg",
"github_project": "zodbc_py",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"lcname": "zodbc"
}
