[](https://github.com/segasai/sqlutilpy/actions)
[](http://sqlutilpy.readthedocs.io/en/latest/?badge=latest)
[](https://coveralls.io/github/segasai/sqlutilpy?branch=master)
[](https://doi.org/10.5281/zenodo.6867957)
# sqlutilpy
Python module to query SQL databases and return numpy arrays, upload
tables and run join queries involving local arrays and the tables in the DB.
This module is optimized to be able to deal efficiently with query results with millions of rows.
The module only works with PostgreSQL, SQLite and DuckDB databases.
The full documentation is available [here](http://sqlutilpy.readthedocs.io/en/latest/)
Author: Sergey Koposov (Uni of Cambridge/CMU/Uni of Edinburgh)
## Installation
To install the package you just need to do pip install.
```
pip install sqlutilpy
```
## Authentication
Throughout this readme, I will assume that the .pgpass file ( https://www.postgresql.org/docs/11/libpq-pgpass.html )
has been created with your login/password details for Postgresql. If that is not the case, all of the
commands given below will also need user='....' and password='...' options
## Connection information
Most of the sqlutilpy commands require hostname, database name, user etc.
If you don't want to always type it, you can use standard PostgreSQL environment variables
like PGPORT, PGDATABASE, PGUSER, PGHOST for the port, database name, user name and hostname
of the connection.
## Querying the database and retrieving the results
This command will run the query and put the columns into variables ra,dec
```python
import sqlutilpy
ra,dec = squtilpy.get('select ra,dec from mytable',
host='HOST_NAME_OF_MY_PG_SERVER',
db='THE_NAME_OF_MY_DB')
```
By default sqlutilpy.get executes the query and returns the tuple with
results. You can return the results as dictionary using asDict option.
## Uploading your arrays as column in a table
```python
x = np.arange(10)
y = x**.5
sqlutilpy.upload('mytable',(x,y),('xcol','ycol'))
```
This will create a table called mytable with columns xcol and ycol
## Join query involving your local data and the database table
Imagine you have arrays myid and y and you want to to extract all the
information from somebigtable for objects with id=myid. In principle
you could upload the arrays in the DB and run a query, but local_join function does that for you.
```python
myid = np.arange(10)
y = np.random.uniform(size=10)
R=sqlutilpy.local_join('''select * from mytmptable as m,
somebigtable as s where s.id=m.myid order by m.myid''',
'mytmptable',(myid, y),('myid','ycol'))
```
It executes a query as if you arrays were in mytmptable. (behind the scenes
it uploads the data to the db and runs a query)
## Keeping the connection open.
Often it is beneficial to preserve an open connection to the database. You can do that if you first
obtain the connection using sqlutilpy.getConnection() and then provide it directly
to sqlutil.get() and friends using conn=conn argument
```python
conn = sqlutilpy.getConnection(db='mydb', user='meuser', password='something', host='hostname')
R= sqlutilpy.get('select 1', conn=conn)
R1= sqlutilpy.get('select 1', conn=conn)
```
# How to cite the software
If you use this package, please cite it through zenodo https://doi.org/10.5281/zenodo.6867957
Raw data
{
"_id": null,
"home_page": null,
"name": "sqlutilpy",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": null,
"keywords": "numpy postgresql query sql sqlite array",
"author": null,
"author_email": "\"Sergey E. Koposov\" <skoposov@ed.ac.uk>",
"download_url": "https://files.pythonhosted.org/packages/fe/80/5f92885a86b69bbb90ede58f6e330d9970da58ae0e811d90fcf1b4929013/sqlutilpy-0.23.0.tar.gz",
"platform": null,
"description": "[](https://github.com/segasai/sqlutilpy/actions)\n[](http://sqlutilpy.readthedocs.io/en/latest/?badge=latest)\n[](https://coveralls.io/github/segasai/sqlutilpy?branch=master)\n[](https://doi.org/10.5281/zenodo.6867957)\n\n# sqlutilpy\nPython module to query SQL databases and return numpy arrays, upload\ntables and run join queries involving local arrays and the tables in the DB.\nThis module is optimized to be able to deal efficiently with query results with millions of rows.\nThe module only works with PostgreSQL, SQLite and DuckDB databases.\n\nThe full documentation is available [here](http://sqlutilpy.readthedocs.io/en/latest/)\n\nAuthor: Sergey Koposov (Uni of Cambridge/CMU/Uni of Edinburgh)\n\n## Installation\nTo install the package you just need to do pip install. \n\n```\npip install sqlutilpy\n```\n## Authentication\n\nThroughout this readme, I will assume that the .pgpass file ( https://www.postgresql.org/docs/11/libpq-pgpass.html ) \nhas been created with your login/password details for Postgresql. If that is not the case, all of the \ncommands given below will also need user='....' and password='...' options\n\n## Connection information\n\nMost of the sqlutilpy commands require hostname, database name, user etc. \nIf you don't want to always type it, you can use standard PostgreSQL environment variables\nlike PGPORT, PGDATABASE, PGUSER, PGHOST for the port, database name, user name and hostname\nof the connection. \n\n\n## Querying the database and retrieving the results\n\nThis command will run the query and put the columns into variables ra,dec\n```python\nimport sqlutilpy\nra,dec = squtilpy.get('select ra,dec from mytable', \n host='HOST_NAME_OF_MY_PG_SERVER', \n db='THE_NAME_OF_MY_DB')\n```\n\nBy default sqlutilpy.get executes the query and returns the tuple with \nresults. You can return the results as dictionary using asDict option.\n\n## Uploading your arrays as column in a table\n\n```python\nx = np.arange(10) \ny = x**.5 \nsqlutilpy.upload('mytable',(x,y),('xcol','ycol')) \n``` \nThis will create a table called mytable with columns xcol and ycol \n\n## Join query involving your local data and the database table\n\nImagine you have arrays myid and y and you want to to extract all the \ninformation from somebigtable for objects with id=myid. In principle\nyou could upload the arrays in the DB and run a query, but local_join function does that for you.\n\n```python\nmyid = np.arange(10)\ny = np.random.uniform(size=10)\n\nR=sqlutilpy.local_join('''select * from mytmptable as m, \n somebigtable as s where s.id=m.myid order by m.myid''', \n 'mytmptable',(myid, y),('myid','ycol'))\n```\n\nIt executes a query as if you arrays were in mytmptable. (behind the scenes\nit uploads the data to the db and runs a query)\n\n## Keeping the connection open. \n\nOften it is beneficial to preserve an open connection to the database. You can do that if you first \nobtain the connection using sqlutilpy.getConnection() and then provide it directly\nto sqlutil.get() and friends using conn=conn argument\n```python\nconn = sqlutilpy.getConnection(db='mydb', user='meuser', password='something', host='hostname')\nR= sqlutilpy.get('select 1', conn=conn)\nR1= sqlutilpy.get('select 1', conn=conn)\n```\n\n# How to cite the software\n\nIf you use this package, please cite it through zenodo https://doi.org/10.5281/zenodo.6867957\n\n",
"bugtrack_url": null,
"license": "BSD",
"summary": "Database query code returning numpy arrays",
"version": "0.23.0",
"project_urls": {
"Homepage": "https://github.com/segasai/sqlutilpy"
},
"split_keywords": [
"numpy",
"postgresql",
"query",
"sql",
"sqlite",
"array"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "93d52f3e4cc8146bb161fd7ee84c1ac335e08ab30625461772ab85f355908a9a",
"md5": "31e0b71bb022dd7e5e374ddf78cddf22",
"sha256": "612592d3e224378395c7527a63ee91fa4445555950d6465135304d268d4cba62"
},
"downloads": -1,
"filename": "sqlutilpy-0.23.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "31e0b71bb022dd7e5e374ddf78cddf22",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 11182,
"upload_time": "2024-08-29T11:14:13",
"upload_time_iso_8601": "2024-08-29T11:14:13.190874Z",
"url": "https://files.pythonhosted.org/packages/93/d5/2f3e4cc8146bb161fd7ee84c1ac335e08ab30625461772ab85f355908a9a/sqlutilpy-0.23.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "fe805f92885a86b69bbb90ede58f6e330d9970da58ae0e811d90fcf1b4929013",
"md5": "1dcc036f725f558f90bc6f50f95e9506",
"sha256": "775633049cc5895a7807d3409c760f37a38791cf0d406115c4ef55ca0d77ab74"
},
"downloads": -1,
"filename": "sqlutilpy-0.23.0.tar.gz",
"has_sig": false,
"md5_digest": "1dcc036f725f558f90bc6f50f95e9506",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 15796,
"upload_time": "2024-08-29T11:14:14",
"upload_time_iso_8601": "2024-08-29T11:14:14.664211Z",
"url": "https://files.pythonhosted.org/packages/fe/80/5f92885a86b69bbb90ede58f6e330d9970da58ae0e811d90fcf1b4929013/sqlutilpy-0.23.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-08-29 11:14:14",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "segasai",
"github_project": "sqlutilpy",
"travis_ci": false,
"coveralls": true,
"github_actions": true,
"lcname": "sqlutilpy"
}
JSON
