.. role:: bash(code)
:language: bash
===================================
Python connector for SQream DB
===================================
* **Supported SQream DB versions:** >= 2.13, 2019.2 or newer recommended
The Python connector for SQream DB is a Python DB API 2.0-compliant interface for developing Python applications with SQream DB.
The SQream Python connector provides an interface for creating and running Python applications that can connect to a SQream DB database. It provides a lighter-weight alternative to working through native C++ or Java bindings, including JDBC and ODBC drivers.
pysqream conforms to Python DB-API specifications `PEP-249 <https://www.python.org/dev/peps/pep-0249/>`_
``pysqream`` is native and pure Python, with minimal requirements. It can be installed with ``pip`` on any operating system, including Linux, Windows, and macOS.
For more information and a full API reference, see `SQream documentation's pysqream guide <https://sqream-docs.readthedocs.io/en/latest/guides/client_drivers/python/index.html>`_ .
Requirements
====================
* Python 3.9+
* Cython (Optional, faster performance) - `pip3 install --upgrade cython`
Installing the Python connector
==================================
Prerequisites
----------------
1. Python
^^^^^^^^^^^^
The connector requires Python 3.9 or newer. To verify your version of Python:
.. code-block:: console
$ python --version
Python 3.9
Note: If both Python 2.x and 3.x are installed, you can run ``python3`` and ``pip3`` instead of ``python`` and ``pip`` respectively for the rest of this guide
2. PIP
^^^^^^^^^^^^
The Python connector is installed via ``pip``, the Python package manager and installer.
We recommend upgrading to the latest version of ``pip`` before installing. To verify that you are on the latest version, run the following command:
.. code-block:: console
$ python3.9 -m pip install --upgrade pip
Collecting pip
Downloading https://files.pythonhosted.org/packages/00/b6/9cfa56b4081ad13874b0c6f96af8ce16cfbc1cb06bedf8e9164ce5551ec1/pip-19.3.1-py2.py3-none-any.whl (1.4MB)
|████████████████████████████████| 1.4MB 1.6MB/s
Installing collected packages: pip
Found existing installation: pip 19.1.1
Uninstalling pip-19.1.1:
Successfully uninstalled pip-19.1.1
Successfully installed pip-19.3.1
.. note::
* On macOS, you may want to use virtualenv to install Python and the connector, to ensure compatibility with the built-in Python environment
* If you encounter an error including ``SSLError`` or ``WARNING: pip is configured with locations that require TLS/SSL, however the ssl module in Python is not available.`` - please be sure to reinstall Python with SSL enabled, or use virtualenv or Anaconda.
3. OpenSSL for Linux
^^^^^^^^^^^^^^^^^^^^^^^^^^
Some distributions of Python do not include OpenSSL. The Python connector relies on OpenSSL for secure connections to SQream DB.
* To install OpenSSL on RHEL/CentOS
.. code-block:: console
$ sudo yum install -y libffi-devel openssl-devel
* To install OpenSSL on Ubuntu
.. code-block:: console
$ sudo apt-get install libssl-dev libffi-dev -y
Install via pip
-----------------
The Python connector is available via `PyPi <https://pypi.org/project/pysqream/>`_.
Install the connector with ``pip``:
.. code-block:: console
$ pip3.9 install pysqream
``pip`` will automatically installs all necessary libraries and modules.
Validate the installation
-----------------------------
Create a file called ``test.py`` (make sure to replace the parameters in the connection with the respective parameters for your SQream DB installation):
.. code-block:: python
#!/usr/bin/env python
import pysqream
"""
Connection parameters include:
* IP/Hostname
* Port
* database name
* username
* password
* Connect through load balancer, or direct to worker (Default: false - direct to worker)
* use SSL connection (default: false)
* Optional service queue (default: 'sqream')
"""
# Create a connection object
con = pysqream.connect(host='127.0.0.1', port=5000, database='master'
, username='sqream', password='sqream'
, clustered=False)
# Create a new cursor
cur = con.cursor()
# Prepare and execute a query
cur.execute('select show_version()')
result = cur.fetchall() # `fetchall` gets the entire data set
print (f"Version: {result[0][0]}")
# This should print the SQream DB version. For example ``Version: v2020.1``.
# Finally, close the connection
con.close()
Run the test file to verify that you can connect to SQream DB:
.. code-block:: console
$ python test.py
Version: v2020.1
If all went well, you are now ready to build an application using the SQream DB Python connector!
If any connection error appears, verify that you have access to a running SQream DB and that the connection parameters are correct.
Logging
-------
To enable logging, pass a path to a log file in the connection string as follows:
.. code-block:: python
con = pysqream.connect('127.0.0.1', 5000, 'master', 'sqream', 'sqream', False, False, log = '/path/to/logfile.xx')
Or pass True to save to `'/tmp/sqream_dbapi.log'`:
.. code-block:: python
con = pysqream.connect('127.0.0.1', 5000, 'master', 'sqream', 'sqream', False, False, log =True)
Further examples
====================
Data load example
-------------------
This example loads 10,000 rows of dummy data to a SQream DB instance
.. code-block:: python
import pysqream
from datetime import date, datetime
from time import time
con = pysqream.connect(host='127.0.0.1', port=3108, database='master'
, username='rhendricks', password='Tr0ub4dor&3'
, clustered=True)
# Create a table for loading
create = 'create or replace table perf (b bool, t tinyint, sm smallint, i int, bi bigint, f real, d double, s varchar(12), ss nvarchar(20), dt date, dtt datetime)'
con.execute(create)
# After creating the table, we can load data into it with the INSERT command
# Create dummy data which matches the table we created
data = (False, 2, 12, 145, 84124234, 3.141, -4.3, "Marty McFly" , u"キウイは楽しい鳥です" , date(2019, 12, 17), datetime(1955, 11, 4, 1, 23, 0, 0))
row_count = 10**4
# Get a new cursor
cur = con.cursor()
insert = 'insert into perf values (?,?,?,?,?,?,?,?,?,?,?)'
start = time()
cur.executemany(insert, [data] * row_count)
print (f"Total insert time for {row_count} rows: {time() - start} seconds")
# Close this cursor
cur.close()
# Verify that the data was inserted correctly
# Get a new cursor
cur = con.cursor()
cur.execute('select count(*) from perf')
result = cur.fetchall() # `fetchall` collects the entire data set
print (f"Count of inserted rows: {result[0][0]}")
# When done, close the cursor
cur.close()
# Close the connection
con.close()
Example of data retrieval methods
-----------------------------------------
.. code-block:: python
# Assume a table structure:
# "CREATE TABLE table_name (int_column int, varchar_column varchar(10))"
# The select statement:
statement = 'SELECT int_column, varchar_column FROM table_name'
con.execute(statement)
first_row = con.fetchone() # Fetch one row at a time (first row)
second_row = con.fetchone() # Fetch one row at a time (second row)
# executing `fetchone` twice is equivalent to this form:
third_and_fourth_rows = con.fetchmany(2)
# To get all rows at once, use `fetchall`
remaining_rows = con.fetchall()
con.close()
Example of a SET data loop for data loading
-----------------------------------------------------
.. code-block:: python
# Assume a table structure:
# "CREATE TABLE table_name (int_column int, varchar_column varchar(10))"
# Each `?` placeholder represents a column value that will be inserted
statement = 'INSERT INTO table_name(int_column, varchar_column) VALUES(?, ?)'
# To insert data, we execute the statement with `executemany`, and pass an array of values alongside it
data_rows = [(1, 's1'), (2, 's2'), (3, 's3')] # Sample data
con.executemany(statement, data_rows)
con.close()
Example inserting data from a CSV
-----------------------------------------
.. code-block:: python
def insert_from_csv(con, table_name, csv_filename, field_delimiter = ',', null_markers = []):
# We will first ask SQream DB for some table information.
# This is important for understanding the number of columns, and will help
# to create an INSERT statement
column_info = con.execute(f"select * from {table_name} limit 0").description
def parse_datetime(v):
try:
return datetime.datetime.strptime(row[i], '%Y-%m-%d %H:%M:%S.%f')
except ValueError:
try:
return datetime.datetime.strptime(row[i], '%Y-%m-%d %H:%M:%S')
except ValueError:
return datetime.datetime.strptime(row[i], '%Y-%m-%d')
# Create enough placeholders (`?`) for the INSERT query string
qstring = ','.join(['?'] * len(column_info))
insert_statement = f"insert into {table_name} values ({qstring})"
# Open the CSV file
with open(csv_filename, mode='r') as csv_file:
csv_reader = csv.reader(csv_file, delimiter=field_delimiter)
# Execute the INSERT statement with the CSV data
con.executemany(insert_statement, [row for row in csv_reader]):
Example saving the results of a query to a csv file
-------------------------------------------------------------
.. code-block:: python
def save_query(con, query, csv_filename, field_delimiter, null_marker):
# The query string has been passed from the outside, so we will now execute it:
column_info = con.execute(query).description
# With the query information, we will write a new CSV file
with open(csv_filename, 'x', newline='') as csvfile:
wr = csv.writer(csvfile, delimiter=field_delimiter,quoting=csv.QUOTE_MINIMAL)
# For each result row in a query, write the data out
for result_row in con:
csv_row = []
wr.writerow(result_row)
Raw data
{
"_id": null,
"home_page": "https://github.com/SQream/pysqream",
"name": "pysqream",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.9",
"maintainer_email": null,
"keywords": "database db-api sqream sqreamdb",
"author": "SQream",
"author_email": "info@sqream.com",
"download_url": "https://files.pythonhosted.org/packages/9a/f9/2eabd71a49d7d53fb2d4aba003fd7f75b9b059760974eed4ba70ee871672/pysqream-5.3.0.tar.gz",
"platform": null,
"description": ".. role:: bash(code)\n :language: bash\n \n===================================\nPython connector for SQream DB\n===================================\n\n* **Supported SQream DB versions:** >= 2.13, 2019.2 or newer recommended\n\nThe Python connector for SQream DB is a Python DB API 2.0-compliant interface for developing Python applications with SQream DB.\n\nThe SQream Python connector provides an interface for creating and running Python applications that can connect to a SQream DB database. It provides a lighter-weight alternative to working through native C++ or Java bindings, including JDBC and ODBC drivers.\n\npysqream conforms to Python DB-API specifications `PEP-249 <https://www.python.org/dev/peps/pep-0249/>`_\n\n``pysqream`` is native and pure Python, with minimal requirements. It can be installed with ``pip`` on any operating system, including Linux, Windows, and macOS.\n\nFor more information and a full API reference, see `SQream documentation's pysqream guide <https://sqream-docs.readthedocs.io/en/latest/guides/client_drivers/python/index.html>`_ .\n\nRequirements\n====================\n\n* Python 3.9+\n\n* Cython (Optional, faster performance) - `pip3 install --upgrade cython`\n\nInstalling the Python connector\n==================================\n\nPrerequisites\n----------------\n\n1. Python\n^^^^^^^^^^^^\n\nThe connector requires Python 3.9 or newer. To verify your version of Python:\n\n.. code-block:: console\n\n $ python --version\n Python 3.9\n \n\nNote: If both Python 2.x and 3.x are installed, you can run ``python3`` and ``pip3`` instead of ``python`` and ``pip`` respectively for the rest of this guide\n\n2. PIP\n^^^^^^^^^^^^\nThe Python connector is installed via ``pip``, the Python package manager and installer.\n\nWe recommend upgrading to the latest version of ``pip`` before installing. To verify that you are on the latest version, run the following command:\n\n.. code-block:: console\n\n $ python3.9 -m pip install --upgrade pip\n Collecting pip\n Downloading https://files.pythonhosted.org/packages/00/b6/9cfa56b4081ad13874b0c6f96af8ce16cfbc1cb06bedf8e9164ce5551ec1/pip-19.3.1-py2.py3-none-any.whl (1.4MB)\n |\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588| 1.4MB 1.6MB/s\n Installing collected packages: pip\n Found existing installation: pip 19.1.1\n Uninstalling pip-19.1.1:\n Successfully uninstalled pip-19.1.1\n Successfully installed pip-19.3.1\n\n.. note:: \n * On macOS, you may want to use virtualenv to install Python and the connector, to ensure compatibility with the built-in Python environment\n * If you encounter an error including ``SSLError`` or ``WARNING: pip is configured with locations that require TLS/SSL, however the ssl module in Python is not available.`` - please be sure to reinstall Python with SSL enabled, or use virtualenv or Anaconda.\n\n3. OpenSSL for Linux\n^^^^^^^^^^^^^^^^^^^^^^^^^^\n\nSome distributions of Python do not include OpenSSL. The Python connector relies on OpenSSL for secure connections to SQream DB.\n\n* To install OpenSSL on RHEL/CentOS\n\n .. code-block:: console\n \n $ sudo yum install -y libffi-devel openssl-devel\n\n* To install OpenSSL on Ubuntu\n\n .. code-block:: console\n \n $ sudo apt-get install libssl-dev libffi-dev -y\n\n\n\nInstall via pip\n-----------------\n\nThe Python connector is available via `PyPi <https://pypi.org/project/pysqream/>`_.\n\nInstall the connector with ``pip``:\n\n.. code-block:: console\n \n $ pip3.9 install pysqream\n\n``pip`` will automatically installs all necessary libraries and modules.\n\nValidate the installation\n-----------------------------\n\nCreate a file called ``test.py`` (make sure to replace the parameters in the connection with the respective parameters for your SQream DB installation):\n\n.. code-block:: python\n \n #!/usr/bin/env python\n\n import pysqream\n\n \"\"\"\n Connection parameters include:\n * IP/Hostname\n * Port\n * database name\n * username\n * password \n * Connect through load balancer, or direct to worker (Default: false - direct to worker)\n * use SSL connection (default: false)\n * Optional service queue (default: 'sqream')\n \"\"\"\n\n # Create a connection object\n\n con = pysqream.connect(host='127.0.0.1', port=5000, database='master'\n , username='sqream', password='sqream'\n , clustered=False)\n\n # Create a new cursor\n cur = con.cursor()\n\n # Prepare and execute a query\n cur.execute('select show_version()')\n\n result = cur.fetchall() # `fetchall` gets the entire data set\n\n print (f\"Version: {result[0][0]}\")\n\n # This should print the SQream DB version. For example ``Version: v2020.1``.\n\n # Finally, close the connection\n\n con.close()\n\nRun the test file to verify that you can connect to SQream DB:\n\n.. code-block:: console\n \n $ python test.py\n Version: v2020.1\n\nIf all went well, you are now ready to build an application using the SQream DB Python connector!\n\nIf any connection error appears, verify that you have access to a running SQream DB and that the connection parameters are correct.\n\nLogging\n-------\n\nTo enable logging, pass a path to a log file in the connection string as follows:\n\n.. code-block:: python\n \n con = pysqream.connect('127.0.0.1', 5000, 'master', 'sqream', 'sqream', False, False, log = '/path/to/logfile.xx')\n\nOr pass True to save to `'/tmp/sqream_dbapi.log'`:\n\n.. code-block:: python\n \n con = pysqream.connect('127.0.0.1', 5000, 'master', 'sqream', 'sqream', False, False, log =True)\n \n\n\nFurther examples\n====================\n\nData load example\n-------------------\n\nThis example loads 10,000 rows of dummy data to a SQream DB instance\n\n.. code-block:: python\n \n import pysqream\n from datetime import date, datetime\n from time import time\n\n con = pysqream.connect(host='127.0.0.1', port=3108, database='master'\n , username='rhendricks', password='Tr0ub4dor&3'\n , clustered=True)\n \n # Create a table for loading\n create = 'create or replace table perf (b bool, t tinyint, sm smallint, i int, bi bigint, f real, d double, s varchar(12), ss nvarchar(20), dt date, dtt datetime)'\n con.execute(create)\n\n # After creating the table, we can load data into it with the INSERT command\n\n # Create dummy data which matches the table we created\n data = (False, 2, 12, 145, 84124234, 3.141, -4.3, \"Marty McFly\" , u\"\u30ad\u30a6\u30a4\u306f\u697d\u3057\u3044\u9ce5\u3067\u3059\" , date(2019, 12, 17), datetime(1955, 11, 4, 1, 23, 0, 0))\n \n \n row_count = 10**4\n\n # Get a new cursor\n cur = con.cursor()\n insert = 'insert into perf values (?,?,?,?,?,?,?,?,?,?,?)'\n start = time()\n cur.executemany(insert, [data] * row_count)\n print (f\"Total insert time for {row_count} rows: {time() - start} seconds\")\n\n # Close this cursor\n cur.close()\n \n # Verify that the data was inserted correctly\n # Get a new cursor\n cur = con.cursor()\n cur.execute('select count(*) from perf')\n result = cur.fetchall() # `fetchall` collects the entire data set\n print (f\"Count of inserted rows: {result[0][0]}\")\n\n # When done, close the cursor\n cur.close()\n \n # Close the connection\n con.close()\n\n\nExample of data retrieval methods\n-----------------------------------------\n\n.. code-block:: python\n\n # Assume a table structure:\n # \"CREATE TABLE table_name (int_column int, varchar_column varchar(10))\"\n\n # The select statement:\n statement = 'SELECT int_column, varchar_column FROM table_name'\n con.execute(statement)\n\n first_row = con.fetchone() # Fetch one row at a time (first row)\n second_row = con.fetchone() # Fetch one row at a time (second row)\n \n # executing `fetchone` twice is equivalent to this form:\n third_and_fourth_rows = con.fetchmany(2)\n \n # To get all rows at once, use `fetchall`\n remaining_rows = con.fetchall() \n\n con.close()\n\n\nExample of a SET data loop for data loading\n-----------------------------------------------------\n\n.. code-block:: python\n\n # Assume a table structure:\n # \"CREATE TABLE table_name (int_column int, varchar_column varchar(10))\"\n \n # Each `?` placeholder represents a column value that will be inserted\n statement = 'INSERT INTO table_name(int_column, varchar_column) VALUES(?, ?)'\n \n # To insert data, we execute the statement with `executemany`, and pass an array of values alongside it\n data_rows = [(1, 's1'), (2, 's2'), (3, 's3')] # Sample data\n con.executemany(statement, data_rows)\n \n con.close()\n \n\nExample inserting data from a CSV\n-----------------------------------------\n\n.. code-block:: python\n\n def insert_from_csv(con, table_name, csv_filename, field_delimiter = ',', null_markers = []):\n \n # We will first ask SQream DB for some table information.\n # This is important for understanding the number of columns, and will help\n # to create an INSERT statement\n \n column_info = con.execute(f\"select * from {table_name} limit 0\").description\n\n \n def parse_datetime(v):\n try:\n return datetime.datetime.strptime(row[i], '%Y-%m-%d %H:%M:%S.%f')\n except ValueError:\n try: \n return datetime.datetime.strptime(row[i], '%Y-%m-%d %H:%M:%S')\n except ValueError:\n return datetime.datetime.strptime(row[i], '%Y-%m-%d')\n \n # Create enough placeholders (`?`) for the INSERT query string\n qstring = ','.join(['?'] * len(column_info))\n insert_statement = f\"insert into {table_name} values ({qstring})\"\n \n # Open the CSV file\n with open(csv_filename, mode='r') as csv_file:\n csv_reader = csv.reader(csv_file, delimiter=field_delimiter)\n \n # Execute the INSERT statement with the CSV data\n con.executemany(insert_statement, [row for row in csv_reader]):\n \n \nExample saving the results of a query to a csv file\n-------------------------------------------------------------\n\n.. code-block:: python\n\n def save_query(con, query, csv_filename, field_delimiter, null_marker):\n # The query string has been passed from the outside, so we will now execute it:\n column_info = con.execute(query).description\n \n # With the query information, we will write a new CSV file\n with open(csv_filename, 'x', newline='') as csvfile:\n wr = csv.writer(csvfile, delimiter=field_delimiter,quoting=csv.QUOTE_MINIMAL)\n # For each result row in a query, write the data out\n for result_row in con:\n csv_row = []\n wr.writerow(result_row)\n \n\n",
"bugtrack_url": null,
"license": null,
"summary": "DB-API connector for SQream DB",
"version": "5.3.0",
"project_urls": {
"Homepage": "https://github.com/SQream/pysqream"
},
"split_keywords": [
"database",
"db-api",
"sqream",
"sqreamdb"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "4f0252acd8ebed364718222998c9df290e3161228fd29b908dfdc112725f0828",
"md5": "9e6cbc433dad43f1644b21d66b4fde91",
"sha256": "f0e5b42631c641035472493af445ba38c4b0ed1fb3022d94c0438f22d390fba6"
},
"downloads": -1,
"filename": "pysqream-5.3.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "9e6cbc433dad43f1644b21d66b4fde91",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.9",
"size": 50713,
"upload_time": "2024-10-29T08:26:15",
"upload_time_iso_8601": "2024-10-29T08:26:15.773647Z",
"url": "https://files.pythonhosted.org/packages/4f/02/52acd8ebed364718222998c9df290e3161228fd29b908dfdc112725f0828/pysqream-5.3.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "9af92eabd71a49d7d53fb2d4aba003fd7f75b9b059760974eed4ba70ee871672",
"md5": "f9e3cdebcf94549077fdb297ebffff83",
"sha256": "8894d9e3c033e7f5091eee759b05ab632760b9c2dfa6fdcb9854017ac90525a5"
},
"downloads": -1,
"filename": "pysqream-5.3.0.tar.gz",
"has_sig": false,
"md5_digest": "f9e3cdebcf94549077fdb297ebffff83",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.9",
"size": 47283,
"upload_time": "2024-10-29T08:26:17",
"upload_time_iso_8601": "2024-10-29T08:26:17.220110Z",
"url": "https://files.pythonhosted.org/packages/9a/f9/2eabd71a49d7d53fb2d4aba003fd7f75b9b059760974eed4ba70ee871672/pysqream-5.3.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-10-29 08:26:17",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "SQream",
"github_project": "pysqream",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"requirements": [],
"lcname": "pysqream"
}
JSON
