[](https://www.codacy.com/gh/kawansoft/aceql-http-client-python?utm_source=github.com&utm_medium=referral&utm_content=kawansoft/aceql-http-client-python&utm_campaign=Badge_Grade)
# AceQL HTTP
## Python Client SDK v5.6.1 - User Guide
## December 12, 2022
<img src="https://docs.aceql.com/img/AceQL-Schema-min.jpg" alt="AceQL Draw"/>
* [Fundamentals](#fundamentals)
* [License](#license)
* [Python Versions & DB-API 2.0](#python-versions--db-api-20)
* [AceQL Server side compatiblity](#aceql-server-side-compatiblity)
* [Installation](#installation)
* [Data transport](#data-transport)
* [Best practices for fast response time](#best-practices-for-fast-response-time)
* [Datatypes](#datatypes)
* [Usage](#usage)
* [Quickstart](#quickstart)
* [Handling Exceptions](#handling-exceptions)
* [The error type](#the-error-type)
* [Most common AceQL server messages](#most-common-aceql-server-messages)
* [HTTP Status Codes](#http-status-codes)
* [Advanced Usage](#advanced-usage)
* [Managing NULL values](#managing-null-values)
* [Setting NULL values](#setting-null-values)
* [Reading NULL values](#reading-null-values)
* [Transactions](#transactions)
* [Proxies](#proxies)
* [Timeouts](#timeouts)
* [BLOB management](#blob-management)
* [BLOB creation](#blob-creation)
* [BLOB reading](#blob-reading)
* [Managing BLOB upload progress](#managing-blob-upload-progress)
* [Calling AceQL Java stored procedures](#calling-aceql-java-stored-procedures)
* [Using outer authentication without a password and with an AceQL Session ID](#using-outer-authentication-without-a-password-and-with-an-aceql-session-id)
* [Using the Metadata Query API](#using-the-metadata-query-api)
* [Downloading database schema into a file](#downloading-database-schema-into-a-file)
* [Accessing remote database main properties](#accessing-remote-database-main-properties)
* [Getting Details of Tables and Columns](#getting-details-of-tables-and-columns)
# Fundamentals
This document describes how to use the AceQL SDK / module and gives some details about how it operates with the AceQL Server side.
The AceQL SDK / module allows you to wrap the [AceQL HTTP APIs](https://github.com/kawansoft/aceql-http/blob/master/aceql-http-user-guide-api.md) to access remote SQL databases and/or SQL databases in the cloud by simply including standard Python SQL calls in your code, just like you would do for any local database. There is zero learning curve and usage is straightforward.
The AceQL Server operation is described in [AceQL HTTP Server Installation and Configuration Guide](https://github.com/kawansoft/aceql-http/blob/master/README.md), whose content is sometimes referred to in his User Guide.
On the remote side, like the AceQL Server access to the SQL database using Java JDBC, we will sometimes use the JDBC terminology (ResultSet, etc.) in this document. Nevertheless, knowledge of Java or JDBC is *not* a requirement.
## License
The SDK is licensed with the liberal [Apache 2.0](https://www.apache.org/licenses/LICENSE-2.0) license.
## Python Versions & DB-API 2.0
The module supports Python 3.7–3.11.
The module provides a SQL interface compliant with the DB-API 2.0 specification described by [**PEP 249**](https://www.python.org/dev/peps/pep-0249).
## AceQL Server side compatiblity
This SDK version is compatible with AceQL HTTP server side v6.2+. It requires version 8.0+ in order to use batch commands.
## Installation
`pip install aceql`
## Data transport
HTTP requests parameters are transported in UTF-8 format and JSON format is used for data and class transport
All requests are streamed:
- Output requests (from the client side) are streamed directly from the socket to the server to avoid buffering any content body
- Input responses (for the client side) are streamed directly from the socket to the server to efficiently read the response body
Large content (query results, Blobs/Clobs, etc.) is transferred using files. It is never loaded in memory. Streaming techniques are always used to read and write this content.
## Best practices for fast response time
Every HTTP exchange between the client and server side is time-consuming, because the HTTP call is synchronous and waits for the server's response
Try to avoid coding SQL calls inside loops, as this can reduce execution speed. Each SQL call will send an http request and wait for the response from the server.
Note that AceQL is optimized as much as possible. A SELECT call returning a huge data volume will not consume memory on the server or client side: AceQL uses input stream and output stream I/O for data transfer.
Server JDBC ResultSet retrieval is as fast as possible :
- The ResultSet creation is done once on the server by the executeQuery.
- The rows are all dumped at once on the servlet output stream by the server
- The client side gets the ResultSet content as a file.
All data reading commands are executed locally on the client side with forward-only reading.
**It is highly recommended to always use `Cursor.executemany` with autocommit off when you have many rows to INSERT or UPDATE.**
## Datatypes
The main server side JDBC data types for columns are supported:
`Boolean`, `Blob/Clob`, `Integer`, `Short`, `Double`, `Float`, `BigDecimal`, `Long`, `String`, `Date`, `Time`, and `Timestamp`.
Note that the AceQL module does not allow you to specify data types to use; data types are implicitly chosen by the module.
Parameter values are automatically converted to their SQL equivalent. The following Python types can thus be sent to remote server without any problem:
| Python type / class | SQL type |
| ------------------------------------------- | -------------------------------------------- |
| Tuple (None, SqlNullType.<SQL_TYPE>) | NULL |
| str | CHAR, VARCHAR |
| int | INTEGER, or BIGINT, <br />depending on size |
| bool | BIT, BOOL |
| float | REAL |
| date | DATE |
| datetime | TIMESTAMP |
| time | TIME |
| File Object | BLOB |
NULL and BLOB types are explained in Advanced Usage.
This is how SQL types are converted to Python types by default:
| SQL type | Python type / class |
| ----------------------------------------------- | ------------------- |
| NULL | None |
| CHAR, VARCHAR | str |
| TINYINT, INTEGER | int |
| BIGINT | int |
| BOOL, BIT | bool |
| DATE | date |
| DECIMAL, DOUBLE_PRECISION, FLOAT, NUMERIC, REAL | float |
| TIMESTAMP | datetime |
| TIME | time |
| BLOB | Response stream |
# Usage
## Quickstart
To use the module, just create a `Connection` object that represents the database:
```python
import aceql
# URL of the AceQL server, Remote SQL database name & authentication info
url = "https://www.acme.com:9443/aceql"
database = "sampledb"
username = "user1"
password = "password1"
connection = aceql.connect(url=url, username=username, password=password, database=database)
```
The alternate syntax with parameters in the URL is supported:
```python
url="https://www.acme.com:9443/aceql?username=user1&password=password1&database=sampledb"
connection = aceql.connect(url=url)
```
The schema of the database is here: [sampledb](https://docs.aceql.com/rest/soft/7.0/src/sampledb_other_databases.txt)
Once you have a `Connection`, you can create a `Cursor` object and call its `execute()` method to perform SQL commands.
Following sample shows how to insert 3 new customers using prepared statements:
```python
sql = "insert into customer values (?, ?, ?, ?, ?, ?, ?, ?)"
params = (1, 'Sir', 'John', 'Smith I', '1 Madison Ave', 'New York',
'NY 10010', '+1 212-586-7001')
cursor.execute(sql, params)
rows_inserted = cursor.rowcount
sql = "insert into customer values (?, ?, ?, ?, ?, ?, ?, ?)"
params = (2, 'Sir', 'William', 'Smith II', '1 Madison Ave', 'New York',
'NY 10010', '+1 212-586-7002')
cursor.execute(sql, params)
rows_inserted += cursor.rowcount
sql = "insert into customer values (?, ?, ?, ?, ?, ?, ?, ?)"
params = (3, 'Sir', 'William', 'Smith III', '1 Madison Ave', 'New York',
'NY 10010', '+1 212-586-7003')
cursor.execute(sql, params)
rows_inserted += cursor.rowcount
print("rows inserted: " + str(rows_inserted))
```
which returns:
```bash
rows inserted: 3
```
The `cursor.execute()` sends the SQL order and the parameters to the server who executes it on.
We view the first inserted customer:
```python
sql = "select * from customer where customer_id = ?"
params = (1,)
cursor.execute(sql, params)
row = cursor.fetchone()
print (row)
```
which returns:
```bash
(1, 'Sir ', 'John', 'Smith', '1 Madison Ave', 'New York', 'NY 10010 ', '+1 212-586-7000')
```
The remote result set is downloaded into a file that is read line per line at each `Cursor.fetchone()` call.
We have access to the name and type of each column:
```python
for desc in cursor.description:
print(desc[0] + ", " + desc[1])
```
Which returns:
```
customer_id, INTEGER
customer_title, CHAR
fname, VARCHAR
lname, VARCHAR
addressline, VARCHAR
town, VARCHAR
zipcode, CHAR
phone, VARCHAR
```
It's recommended to close the `Cursor` at end of SELECT usage in order to release the underlying file stream and delete the associated temp file:
```python
cursor.close()
```
We view now all the customers and apply good practice to force the closing of `Cursor`:
```python
with closing(connection.cursor()) as cursor:
sql = "select * from customer where customer_id >= ? order by customer_id"
params = (1,)
cursor.execute(sql, params)
print("rows: " + str(cursor.rowcount))
rows = cursor.fetchall()
for row in rows:
print(row)
```
Which returns:
```bash
(1, 'Sir ', 'John', 'Smith', '1 Madison Ave', 'New York', 'NY 10010 ', '+1 212-586-7001')
(2, 'Sir ', 'William', 'Smith II', '1 Madison Ave', 'New York', 'NY 10010 ', '+1 212-586-7002')
(3, 'Sir ', 'William', 'Smith III', '1 Madison Ave', 'New York', 'NY 10010 ', '+1 212-586-7003')
rows: 3
```
At end of our session, it is highly recommended to close the `Connection`:
```python
# Make sure connection is always closed in order to close and release
# server connection into the pool:
connection.close()
```
## Handling Exceptions
Except for `TypeError`, Exceptions thrown are always an instance of `Error`
The `Error` contains 5 pieces of information:
| Info | Description |
| :--------------- | :--------------------------------------- |
| Reason | The error message. Retrieved with `reason` property. |
| Error Type | See below for description. Retrieved with `error_type` property. |
| Exception | The original `Exception` that is the cause, if any. Retrieved with `cause` property. |
| Http Status Code | See below for description. Retrieved with `http_status_code` property. |
| Server Exception | The Java Exception Stack Trace thrown on server side, if any. Retrieved with `remote_stack_trace` property. |
### The error type
The error type allows you to get the type of error, and where the error occurred. It is retrieved with the `Error.error_type` property.
| Error Type Value | Description |
| :---------------: | :--------------------------------------- |
| 0 | The error occurred locally on the client side. See `http_status_code` property for more info. Typical cases: no Internet connection, proxy authentication required. |
| 1 | The error is due to a JDBC Exception. It was raised by the remote JDBC Driver and is rerouted by AceQL as is. The JDBC error message is accessible via `reason` property. Typical case: an error in the SQL statement. <br />Examples: wrong table or column name. |
| 2 | The error was raised by the AceQL Server. This means that the AceQL Server expected a value or parameter that was not sent by the client side. Typical cases: misspelling in URL parameter, missing required request parameters, JDBC Connection expiration, etc. The detailed error message is accessible via `reason` property. See below for most common AceQL Server error messages. |
| 3 | The AceQL Server forbade the execution of the SQL statement for a security reason. For security reasons, `reason` property gives access to voluntarily vague details. |
| 4 | The AceQL Server is on failure and raised an unexpected Java Exception. The stack track is included and accessible via `remote_stack_trace` property. |
### Most common AceQL server messages
| AceQL Sever Error Messages (Error.error_type = 2) |
| ------------------------------------------------------------ |
| AceQL main servlet not found in path |
| An error occurred during Blob download |
| An error occurred during Blob upload |
| Blob directory defined in DatabaseConfigurator.getBlobDirectory() does not exist |
| Connection is invalidated (probably expired) |
| Database does not exist |
| Invalid blob_id. Cannot be used to create a file |
| Invalid blob_id. No Blob corresponding to blob_id |
| Invalid session_id |
| Invalid username or password |
| No action found in request |
| Unable to get a Connection |
| Unknown SQL action or not supported by software |
### HTTP Status Codes
The HTTP Status Code is accessible with the `Error.http_status_code` property. The HTTP Status Code is 200 (OK) on successful completion calls.
When an error occurs:
- If error type is 0, the HTTP Status Code is returned by the client side and may take all possible values in a malformed HTTP call.
- If error type is > 0, the HTTP Status Code can take one the following values returned by the server side:
| HTTP Status Code | Description |
| ---------------------------- | ---------------------------------------- |
| 400 (BAD REQUEST) | Missing element in URL path<br />Missing request parameters<br />All JDBC errors raised by the remote JDBC Driver |
| 401 (UNAUTHORIZED) | Invalid username or password in connect<br />Invalid session_id<br />The AceQL Server forbade the execution of the SQL statement for security reasons |
| 404 (NOT_FOUND) | BLOB directory does not exist on server<br />BLOB file not found on server |
| 500 (INTERNAL_SERVER_ERROR) | The AceQL Server is on failure and raised an unexpected Java Exception |
## Advanced Usage
### Managing NULL values
#### Setting NULL values
`NULL` values are handled in a specific way, because the remote server must know the type of the `NULL` value.
To create a `NULL` value parameter, create a tuple of 2 elements:
- First value is `None`.
- Second value is a one of the `SqlNullType` constants that defines the type of the parameter.
This 2 elements tuple is then inserted in the tuple of the prepared statement parameters:
```python
sql = "insert into customer values (?, ?, ?, ?, ?, ?, ?, ?)"
params = (4, 'Sir', 'William', 'Smith IV', '1 Madison Ave',
'New York', 'NY 10010', (None, SqlNullType.VARCHAR))
cursor.execute(sql, params)
```
#### Reading NULL values
A `NULL` column value is returned as `None`:
```python
sql = "select * from customer_3 where customer_id = ? order by customer_id"
params = (4,)
cursor.execute(sql, params)
row = cursor.fetchone()
print (row)
```
Execution will return:
```python
(4, 'Sir ', 'William', 'Smith IV', '1 Madison Ave', 'New York', 'NY 10010 ', None)
```
In this AceQL module version: there is no difference for string columns between a real NULL in the database and the ''NULL" string.
### Transactions
Transactions are supported by the module. Because the remote server executes JDBC code, client code must follow the JDBC requirement to set the auto commit mode to false prior executing a transaction.
This is done with `Cursor.set_auto_commit(False)`. It is good practice to always reset auto commit mode to true at end of your transactions. Not that it auto commit mode state is undefined when a `Connection` is created with `aceql.connect()` call.
Transaction example:
```python
# To do prior transaction
self.connection.set_auto_commit(False)
cursor = self.connection.cursor()
try:
# Create a Customer
sql = "insert into customer values (?, ?, ?, ?, ?, ?, ?, ?)"
params = (customer_id, 'Sir', 'John', 'Smith', '1 Madison Ave',
'New York', 'NY 10010', '+1 212-586-7000')
cursor.execute(sql, params)
# Create an Order for this Customer
sql = "insert into orderlog values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )"
the_datetime = datetime.now()
the_date = the_datetime.date()
# (None, SqlNullType.BLOB) means to set the jpeg_image BLOB
# column to NULL on server:
params = (customer_id, item_id, "Item Description", 9999,
the_date, the_datetime, (None, SqlNullType.BLOB), 1, 2)
cursor.execute(sql, params)
self.connection.commit()
except Error as e:
print(e)
self.connection.rollback()
raise e
finally:
self.connection.set_auto_commit(True) # Good practice
cursor.close()
```
### Proxies
The AceQL module support proxies, using the [proxy](https://requests.readthedocs.io/en/master/user/advanced/#proxies) syntax of [Requests](https://requests.readthedocs.io/en/master/) The aceql module uses Requests for HTTP communications with the remote server. All options of a new AceQL connection are passed with the `ConnectionOptions` wrapper.
```python
import aceql
from aceql import ConnectionOptions
# URL of the AceQL server, Remote SQL database name & authentication info
url = "https://www.acme.com:9443/aceql"
database = "sampledb"
username = "user1"
password = "password1"
proxies = {
'http': 'http://10.10.1.10:3128',
'https': 'http://10.10.1.10:1080',
}
# We use the ConnectionOptions wrapper class to pass the proxies
connection_options = ConnectionOptions(proxies=proxies)
connection = aceql.connect(url=url, username=username, password=password, database=database,
connection_options=connection_options)
```
Authenticated proxies are supported. Just create an `aceql.ProxyAuth` instance and pass it to `aceql.connect()` with the `ConnectionOptions` wrapper.
```python
import aceql
from aceql import ConnectionOptions
from aceql import ProxyAuth
from samples import my_proxy
# URL of the AceQL server, Remote SQL database name & authentication info
url = "https://www.acme.com:9443/aceql"
database = "sampledb"
username = "user1"
password = "password1"
proxies = {
"http": "http://localhost:8081",
}
# Get the proxy credentials with our own application methods
proxy_username = my_proxy.get_username()
proxy_password = my_proxy.get_password()
# The AceQL ProxyAuth class allows to define the proxy credentials
auth = ProxyAuth(proxy_username, proxy_password)
# We use the ConnectionOptions wrapper class to pass both the proxies & the auth
connection_options = ConnectionOptions(proxies=proxies, auth=auth)
connection = aceql.connect(url=url, username=username, password=password, database=database,
connection_options=connection_options)
```
The AceQL module uses [requests-toolbelt](https://pypi.python.org/pypi/requests-toolbelt) for authenticated proxy management.
### Timeouts
Use the `timeout` parameter of `ConnectionOptions` to pass a timeout value in the [Requests Timeouts](https://requests.readthedocs.io/en/master/user/advanced/#timeouts) format.
If no timeout is specified explicitly, requests do not time out.
```python
connection_options = ConnectionOptions(timeout=10)
connection = aceql.connect(url=url, username=username, password=password, database=database,
connection_options=connection_options)
```
### BLOB management
The AceQL module supports BLOB creation and reading. Methods are implemented using streaming techniques to keep low memory consumption. CLOBs are not supported in this version.
#### BLOB creation
BLOB creation is supported by passing a tuple with a File Object as parameter of a prepared statement:
```python
sql = "insert into orderlog values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )"
filename = os.getcwd() + sep + "item_1_image.png"
fd = open(filename, "rb") # File will be closed by AceQL
blob_tuple = (fd, )
params = (1, 1, "Item 1 Description", 9999,
datetime.now() , datetime.now().date(), blob_tuple, 1, 2)
cursor.execute(sql, params)
```
#### BLOB reading
BLOB reading is supported through `Cursor.get_blob_stream(column_index`). The stream can then be read with a `for` loop that iterates on the `respsonse`, using syntax provided by [Requests](https://requests.readthedocs.io/en/master/user/quickstart/#raw-response-content):
```python
sql = "select customer_id, item_id, jpeg_image from orderlog " \
"where customer_id = ? and item_id = ?"
params = (1, 1)
cursor.execute(sql, params)
row = cursor.fetchone()
# You can get BLOB length if you want to use a progress indicator
blob_length = cursor.get_blob_length(2)
print("blob length: " + str(blob_length))
# Get the stream to the remote BLOB
response = cursor.get_blob_stream(2)
# Download is streamed and writen into filename
filename = os.path.expanduser("~") + sep + "jpeg_image.jpg"
with open(filename, 'wb') as fd:
for chunk in response.iter_content(chunk_size=2048):
fd.write(chunk)
stat_info = os.stat(filename)
print("file length: " + str(stat_info.st_size))
```
#### Managing BLOB upload progress
You may want to give your users a progress bar when uploading BLOBs.
The `ProgressIndicator.Percent` property allows you to get the current percent of upload. Value will be incremented automatically during upload.
To activate the update mechanism:
1/ Set the long BLOB length along the File Object in the tuple of the BLOB prepared statement parameter:
```python
file_length = os.stat(filename).st_size
fd = open(filename, "rb")
blob_tuple = (fd, file_length)
```
2/ Create your `ProgressIndicator` instance and enter it to the `Connection` instance before the `Cursor.execute(sql, params)` call :
```python
progress_indicator = ProgressIndicator()
connection.set_progress_indicator(progress_indicator)
```
You then can read `ProgressIndicator.percent` property in your watching thread.
Code sample:
```python
with closing(connection.cursor()) as cursor:
filename = os.getcwd() + sep + "item_1_image.jpg"
file_length = os.stat(filename).st_size
fd = open(filename, "rb")
blob_tuple = (fd, file_length)
progress_indicator = ProgressIndicator()
connection.set_progress_indicator(progress_indicator)
sql = "insert into orderlog values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )"
params = (1, 1, "Item 1 Description", 9999,
datetime.now() , datetime.now().date(),
blob_tuple, 1, 2)
# cursor.execute() uploads BLOB by chunks and increments
# ProgressIndicator.percent property
cursor.execute(sql, params)
```
### Calling AceQL Java stored procedures
The AceQL client SDK allows executing a remote server Java class that implements the AceQL Server
`org.kawanfw.sql.api.server.executor.ServerQueryExecutor` interface and that returns the rows of the SELECT.
See the `org.kawanfw.sql.api.server.executor.ServerQueryExecutor` [Javadoc](https://docs.aceql.com/rest/soft/11.0/javadoc/org/kawanfw/sql/api/server/executor/ServerQueryExecutor.html).
The usage on the client side is straightforward with the `Cursor.ExecuteServerQueryAsync()` method:
```python
server_query_executor_class_name = "com.mycompany.MyServerQueryExecutor"
my_parameters = [1]
cursor.execute_server_query(server_query_executor_class_name, my_parameters)
print("cursor.rowcount : " + str(cursor.rowcount))
rows = cursor.fetchall()
print("fetchall:")
for row in rows:
print(row)
cursor.close()
```
### Using outer authentication without a password and with an AceQL Session ID
Some working environments (Intranet, etc.) require that the client user authenticates himself without a password. Thus, it is not possible for this users to authenticate though the AceQL client SDK.
In this case, you may use directly the native HTTP [login](https://github.com/kawansoft/aceql-http/blob/master/aceql-http-user-guide-api.md#login) API to authenticate the users and retrieve the `session_id` returned by the API. Just pass the value of the `session_id` to the `ConnectionOptions` instance, along with a `None` `password` to `connect`.
```python
import aceql
from aceql import ConnectionOptions
from samples import my_session_id_builder
# URL of the AceQL server, Remote SQL database name & authentication info without password
url = "https://www.acme.com:9443/aceql"
database = "sampledb"
username = "user1"
# Our application will get the session_id to use
session_id = my_session_id_builder.get_session_id_from_login_api()
# We use the ConnectionOptions wrapper to tell to use session_id instead of a password
connection_options = ConnectionOptions(session_id=session_id)
connection = aceql.connect(url=url, username=username, password=None, database=database,
connection_options=connection_options)
```
## Using the Metadata Query API
The metadata API allows:
- downloading a remote database schema
in HTML or text format,
- to get a remote database main properties,
- to get the list of tables,
- to get the details of each table.
It also allows wrapping remote tables, columns, indexes, etc. into
easy to use provided Python classes: Table, Index, Column, etc.
First step is to get an instance of `RemoteDatabaseMetaData`:
```python
remote_database_meta_data = RemoteDatabaseMetaData(connection)
```
### Downloading database schema into a file
Downloading a schema into a `File` is done through the `db_schema_download(filename)` method. See the `RemoteDatabaseMetaData` documentation:
```python
filename = os.path.expanduser("~") + os.sep + "db_schema.html"
remote_database_meta_data.db_schema_download(filename)
```
See an example of the built HTML schema: [db_schema.out.html](https://docs.aceql.com/rest/soft/11.0/src/db_schema.out.html)
### Accessing remote database main properties
The `JdbcDatabaseMetaData` class wraps instance the main value retrieved by a remote server JDBC call to `java.sql.Connection.getMetaData()`:
```python
jdbc_meta_data = remote_database_meta_data.get_jdbc_database_meta_data()
print("Major Version: " + str(jdbc_meta_data.getJDBCMajorVersion))
print("Minor Version: " + str(jdbc_meta_data.getJDBCMinorVersion))
print("IsReadOnly : " + str(jdbc_meta_data.isReadOnly))
```
### Getting Details of Tables and Columns
See the `RemoteDatabaseMetaData` documentation:
```python
print("Print the column details of each table:")
for table_name in table_names:
table = remote_database_meta_data.get_table(table_name)
print()
print("Columns of table: " + table_name)
for column in table.columns:
print(column)
```
____________
Raw data
{
"_id": null,
"home_page": "https://github.com/kawansoft/aceql-py",
"name": "aceql",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "DATABASE SQL HTTP CLIENT-SERVER",
"author": "KawanSoft",
"author_email": "contact@kawansoft.com",
"download_url": "https://files.pythonhosted.org/packages/64/3a/cef3ab3a06a44f26b01cc80ba5538c4b6fa1cb32cfbc666111baff340847/aceql-5.6.1.tar.gz",
"platform": null,
"description": " \r\n \r\n\r\n\r\n \r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n \r\n[](https://www.codacy.com/gh/kawansoft/aceql-http-client-python?utm_source=github.com&utm_medium=referral&utm_content=kawansoft/aceql-http-client-python&utm_campaign=Badge_Grade)\r\n\r\n\r\n# AceQL HTTP \r\n\r\n## Python Client SDK v5.6.1 - User Guide \r\n\r\n## December 12, 2022\r\n\r\n<img src=\"https://docs.aceql.com/img/AceQL-Schema-min.jpg\" alt=\"AceQL Draw\"/>\r\n\r\n * [Fundamentals](#fundamentals)\r\n * [License](#license)\r\n * [Python Versions & DB-API 2.0](#python-versions--db-api-20)\r\n * [AceQL Server side compatiblity](#aceql-server-side-compatiblity)\r\n * [Installation](#installation)\r\n * [Data transport](#data-transport)\r\n * [Best practices for fast response time](#best-practices-for-fast-response-time)\r\n * [Datatypes](#datatypes)\r\n * [Usage](#usage)\r\n * [Quickstart](#quickstart)\r\n * [Handling Exceptions](#handling-exceptions)\r\n * [The error type](#the-error-type)\r\n * [Most common AceQL server messages](#most-common-aceql-server-messages)\r\n * [HTTP Status Codes](#http-status-codes)\r\n * [Advanced Usage](#advanced-usage)\r\n * [Managing NULL values](#managing-null-values)\r\n * [Setting NULL values](#setting-null-values)\r\n * [Reading NULL values](#reading-null-values)\r\n * [Transactions](#transactions)\r\n * [Proxies](#proxies)\r\n * [Timeouts](#timeouts)\r\n * [BLOB management](#blob-management)\r\n * [BLOB creation](#blob-creation)\r\n * [BLOB reading](#blob-reading)\r\n * [Managing BLOB upload progress](#managing-blob-upload-progress)\r\n * [Calling AceQL Java stored procedures](#calling-aceql-java-stored-procedures)\r\n * [Using outer authentication without a password and with an AceQL Session ID](#using-outer-authentication-without-a-password-and-with-an-aceql-session-id)\r\n * [Using the Metadata Query API](#using-the-metadata-query-api)\r\n * [Downloading database schema into a file](#downloading-database-schema-into-a-file)\r\n * [Accessing remote database main properties](#accessing-remote-database-main-properties)\r\n * [Getting Details of Tables and Columns](#getting-details-of-tables-and-columns)\r\n\r\n\r\n# Fundamentals\r\n\r\nThis document describes how to use the AceQL SDK / module and gives some details about how it operates with the AceQL Server side.\r\n\r\nThe AceQL SDK / module allows you to wrap the [AceQL HTTP APIs](https://github.com/kawansoft/aceql-http/blob/master/aceql-http-user-guide-api.md) to access remote SQL databases and/or SQL databases in the cloud by simply including standard Python SQL calls in your code, just like you would do for any local database. There is zero learning curve and usage is straightforward.\r\n\r\nThe AceQL Server operation is described in [AceQL HTTP Server Installation and Configuration Guide](https://github.com/kawansoft/aceql-http/blob/master/README.md), whose content is sometimes referred to in his User Guide.\r\n\r\nOn the remote side, like the AceQL Server access to the SQL database using Java JDBC, we will sometimes use the JDBC terminology (ResultSet, etc.) in this document. Nevertheless, knowledge of Java or JDBC is *not* a requirement.\r\n\r\n## License\r\n\r\nThe SDK is licensed with the liberal [Apache 2.0](https://www.apache.org/licenses/LICENSE-2.0) license.\r\n\r\n## Python Versions & DB-API 2.0\r\n\r\nThe module supports Python 3.7\u20133.11.\r\n\r\nThe module provides a SQL interface compliant with the DB-API 2.0 specification described by [**PEP 249**](https://www.python.org/dev/peps/pep-0249).\r\n\r\n## AceQL Server side compatiblity\r\n\r\nThis SDK version is compatible with AceQL HTTP server side v6.2+. It requires version 8.0+ in order to use batch commands.\r\n\r\n## Installation\r\n\r\n`pip install aceql`\r\n\r\n## Data transport\r\n\r\nHTTP requests parameters are transported in UTF-8 format and JSON format is used for data and class transport\r\n\r\nAll requests are streamed:\r\n\r\n- Output requests (from the client side) are streamed directly from the socket to the server to avoid buffering any content body\r\n- Input responses (for the client side) are streamed directly from the socket to the server to efficiently read the response body\r\n\r\nLarge content (query results, Blobs/Clobs, etc.) is transferred using files. It is never loaded in memory. Streaming techniques are always used to read and write this content.\r\n\r\n## Best practices for fast response time\r\n\r\nEvery HTTP exchange between the client and server side is time-consuming, because the HTTP call is synchronous and waits for the server's response\r\n\r\nTry to avoid coding SQL calls inside loops, as this can reduce execution speed. Each SQL call will send an http request and wait for the response from the server.\r\n\r\nNote that AceQL is optimized as much as possible. A SELECT call returning a huge data volume will not consume memory on the server or client side: AceQL uses input stream and output stream I/O for data transfer.\r\n\r\nServer JDBC ResultSet retrieval is as fast as possible :\r\n\r\n- The ResultSet creation is done once on the server by the executeQuery.\r\n\r\n\r\n- The rows are all dumped at once on the servlet output stream by the server\r\n\r\n\r\n- The client side gets the ResultSet content as a file.\r\n\r\nAll data reading commands are executed locally on the client side with forward-only reading.\r\n\r\n**It is highly recommended to always use `Cursor.executemany` with autocommit off when you have many rows to INSERT or UPDATE.**\r\n\r\n## Datatypes\r\n\r\nThe main server side JDBC data types for columns are supported:\r\n\r\n`Boolean`, `Blob/Clob`, `Integer`, `Short`, `Double`, `Float`, `BigDecimal`, `Long`, `String`, `Date`, `Time`, and `Timestamp`.\r\n\r\nNote that the AceQL module does not allow you to specify data types to use; data types are implicitly chosen by the module.\r\n\r\nParameter values are automatically converted to their SQL equivalent. The following Python types can thus be sent to remote server without any problem:\r\n\r\n| Python type / class | SQL type |\r\n| ------------------------------------------- | -------------------------------------------- |\r\n| Tuple (None, SqlNullType.<SQL_TYPE>) | NULL |\r\n| str | CHAR, VARCHAR |\r\n| int | INTEGER, or BIGINT, <br />depending on size |\r\n| bool | BIT, BOOL |\r\n| float | REAL |\r\n| date | DATE |\r\n| datetime | TIMESTAMP |\r\n| time | TIME |\r\n| File Object | BLOB |\r\n\r\nNULL and BLOB types are explained in Advanced Usage.\r\n\r\nThis is how SQL types are converted to Python types by default:\r\n\r\n| SQL type | Python type / class |\r\n| ----------------------------------------------- | ------------------- |\r\n| NULL | None |\r\n| CHAR, VARCHAR | str |\r\n| TINYINT, INTEGER | int |\r\n| BIGINT | int |\r\n| BOOL, BIT | bool |\r\n| DATE | date |\r\n| DECIMAL, DOUBLE_PRECISION, FLOAT, NUMERIC, REAL | float |\r\n| TIMESTAMP | datetime |\r\n| TIME | time |\r\n| BLOB | Response stream |\r\n\r\n# Usage\r\n\r\n## Quickstart\r\n\r\nTo use the module, just create a `Connection` object that represents the database:\r\n\r\n```python\r\nimport aceql\r\n\r\n# URL of the AceQL server, Remote SQL database name & authentication info\r\nurl = \"https://www.acme.com:9443/aceql\"\r\ndatabase = \"sampledb\"\r\nusername = \"user1\"\r\npassword = \"password1\"\r\n\r\nconnection = aceql.connect(url=url, username=username, password=password, database=database)\r\n```\r\n\r\nThe alternate syntax with parameters in the URL is supported:\r\n\r\n```python\r\nurl=\"https://www.acme.com:9443/aceql?username=user1&password=password1&database=sampledb\"\r\nconnection = aceql.connect(url=url)\r\n```\r\n\r\nThe schema of the database is here: [sampledb](https://docs.aceql.com/rest/soft/7.0/src/sampledb_other_databases.txt)\r\n\r\nOnce you have a `Connection`, you can create a `Cursor` object and call its `execute()` method to perform SQL commands.\r\n\r\nFollowing sample shows how to insert 3 new customers using prepared statements:\r\n\r\n```python\r\nsql = \"insert into customer values (?, ?, ?, ?, ?, ?, ?, ?)\"\r\nparams = (1, 'Sir', 'John', 'Smith I', '1 Madison Ave', 'New York',\r\n 'NY 10010', '+1 212-586-7001')\r\ncursor.execute(sql, params)\r\nrows_inserted = cursor.rowcount\r\n\r\nsql = \"insert into customer values (?, ?, ?, ?, ?, ?, ?, ?)\"\r\nparams = (2, 'Sir', 'William', 'Smith II', '1 Madison Ave', 'New York',\r\n 'NY 10010', '+1 212-586-7002')\r\ncursor.execute(sql, params)\r\nrows_inserted += cursor.rowcount\r\n\r\nsql = \"insert into customer values (?, ?, ?, ?, ?, ?, ?, ?)\"\r\nparams = (3, 'Sir', 'William', 'Smith III', '1 Madison Ave', 'New York',\r\n 'NY 10010', '+1 212-586-7003')\r\ncursor.execute(sql, params)\r\nrows_inserted += cursor.rowcount\r\n\r\nprint(\"rows inserted: \" + str(rows_inserted))\r\n```\r\n\r\nwhich returns:\r\n\r\n```bash\r\nrows inserted: 3\r\n```\r\n\r\nThe `cursor.execute()` sends the SQL order and the parameters to the server who executes it on.\r\n\r\nWe view the first inserted customer:\r\n\r\n```python\r\nsql = \"select * from customer where customer_id = ?\"\r\nparams = (1,)\r\ncursor.execute(sql, params)\r\nrow = cursor.fetchone()\r\nprint (row)\r\n```\r\n\r\nwhich returns:\r\n\r\n```bash\r\n(1, 'Sir ', 'John', 'Smith', '1 Madison Ave', 'New York', 'NY 10010 ', '+1 212-586-7000')\r\n```\r\n\r\nThe remote result set is downloaded into a file that is read line per line at each `Cursor.fetchone()` call.\r\n\r\nWe have access to the name and type of each column:\r\n\r\n```python\r\nfor desc in cursor.description:\r\n print(desc[0] + \", \" + desc[1])\r\n```\r\n\r\nWhich returns:\r\n\r\n```\r\ncustomer_id, INTEGER\r\ncustomer_title, CHAR\r\nfname, VARCHAR\r\nlname, VARCHAR\r\naddressline, VARCHAR\r\ntown, VARCHAR\r\nzipcode, CHAR\r\nphone, VARCHAR\r\n```\r\n\r\nIt's recommended to close the `Cursor` at end of SELECT usage in order to release the underlying file stream and delete the associated temp file:\r\n\r\n```python\r\ncursor.close()\r\n```\r\n\r\nWe view now all the customers and apply good practice to force the closing of `Cursor`:\r\n\r\n```python\r\nwith closing(connection.cursor()) as cursor:\r\n sql = \"select * from customer where customer_id >= ? order by customer_id\"\r\n params = (1,)\r\n cursor.execute(sql, params)\r\n print(\"rows: \" + str(cursor.rowcount))\r\n\r\n rows = cursor.fetchall()\r\n\r\n for row in rows:\r\n print(row)\r\n```\r\n\r\nWhich returns:\r\n\r\n```bash\r\n(1, 'Sir ', 'John', 'Smith', '1 Madison Ave', 'New York', 'NY 10010 ', '+1 212-586-7001')\r\n(2, 'Sir ', 'William', 'Smith II', '1 Madison Ave', 'New York', 'NY 10010 ', '+1 212-586-7002')\r\n(3, 'Sir ', 'William', 'Smith III', '1 Madison Ave', 'New York', 'NY 10010 ', '+1 212-586-7003')\r\nrows: 3\r\n```\r\n\r\nAt end of our session, it is highly recommended to close the `Connection`:\r\n\r\n```python\r\n# Make sure connection is always closed in order to close and release\r\n# server connection into the pool:\r\nconnection.close()\r\n```\r\n\r\n## Handling Exceptions\r\n\r\nExcept for `TypeError`, Exceptions thrown are always an instance of `Error`\r\n\r\nThe `Error` contains 5 pieces of information:\r\n\r\n| Info | Description |\r\n| :--------------- | :--------------------------------------- |\r\n| Reason | The error message. Retrieved with `reason` property. |\r\n| Error Type | See below for description. Retrieved with `error_type` property. |\r\n| Exception | The original `Exception` that is the cause, if any. Retrieved with `cause` property. |\r\n| Http Status Code | See below for description. Retrieved with `http_status_code` property. |\r\n| Server Exception | The Java Exception Stack Trace thrown on server side, if any. Retrieved with `remote_stack_trace` property. |\r\n\r\n### The error type\r\n\r\nThe error type allows you to get the type of error, and where the error occurred. It is retrieved with the `Error.error_type` property.\r\n\r\n| Error Type Value | Description |\r\n| :---------------: | :--------------------------------------- |\r\n| 0 | The error occurred locally on the client side. See `http_status_code` property for more info. Typical cases: no Internet connection, proxy authentication required. |\r\n| 1 | The error is due to a JDBC Exception. It was raised by the remote JDBC Driver and is rerouted by AceQL as is. The JDBC error message is accessible via `reason` property. Typical case: an error in the SQL statement. <br />Examples: wrong table or column name. |\r\n| 2 | The error was raised by the AceQL Server. This means that the AceQL Server expected a value or parameter that was not sent by the client side. Typical cases: misspelling in URL parameter, missing required request parameters, JDBC Connection expiration, etc. The detailed error message is accessible via `reason` property. See below for most common AceQL Server error messages. |\r\n| 3 | The AceQL Server forbade the execution of the SQL statement for a security reason. For security reasons, `reason` property gives access to voluntarily vague details. |\r\n| 4 | The AceQL Server is on failure and raised an unexpected Java Exception. The stack track is included and accessible via `remote_stack_trace` property. |\r\n\r\n### Most common AceQL server messages\r\n\r\n| AceQL Sever Error Messages (Error.error_type = 2) |\r\n| ------------------------------------------------------------ |\r\n| AceQL main servlet not found in path |\r\n| An error occurred during Blob download |\r\n| An error occurred during Blob upload |\r\n| Blob directory defined in DatabaseConfigurator.getBlobDirectory() does not exist |\r\n| Connection is invalidated (probably expired) |\r\n| Database does not exist |\r\n| Invalid blob_id. Cannot be used to create a file |\r\n| Invalid blob_id. No Blob corresponding to blob_id |\r\n| Invalid session_id |\r\n| Invalid username or password |\r\n| No action found in request |\r\n| Unable to get a Connection |\r\n| Unknown SQL action or not supported by software |\r\n\r\n### HTTP Status Codes\r\n\r\nThe HTTP Status Code is accessible with the `Error.http_status_code` property. The HTTP Status Code is 200 (OK) on successful completion calls.\r\n\r\nWhen an error occurs:\r\n\r\n- If error type is 0, the HTTP Status Code is returned by the client side and may take all possible values in a malformed HTTP call.\r\n\r\n- If error type is > 0, the HTTP Status Code can take one the following values returned by the server side:\r\n\r\n\r\n\r\n| HTTP Status Code | Description |\r\n| ---------------------------- | ---------------------------------------- |\r\n| 400 (BAD REQUEST) | Missing element in URL path<br />Missing request parameters<br />All JDBC errors raised by the remote JDBC Driver |\r\n| 401 (UNAUTHORIZED) | Invalid username or password in connect<br />Invalid session_id<br />The AceQL Server forbade the execution of the SQL statement for security reasons |\r\n| 404 (NOT_FOUND) | BLOB directory does not exist on server<br />BLOB file not found on server |\r\n| 500 (INTERNAL_SERVER_ERROR) | The AceQL Server is on failure and raised an unexpected Java Exception |\r\n\r\n## Advanced Usage\r\n\r\n### Managing NULL values\r\n\r\n#### Setting NULL values\r\n\r\n`NULL` values are handled in a specific way, because the remote server must know the type of the `NULL` value.\r\n\r\nTo create a `NULL` value parameter, create a tuple of 2 elements:\r\n\r\n- First value is `None`.\r\n- Second value is a one of the `SqlNullType` constants that defines the type of the parameter.\r\n\r\nThis 2 elements tuple is then inserted in the tuple of the prepared statement parameters:\r\n\r\n```python\r\nsql = \"insert into customer values (?, ?, ?, ?, ?, ?, ?, ?)\"\r\nparams = (4, 'Sir', 'William', 'Smith IV', '1 Madison Ave',\r\n 'New York', 'NY 10010', (None, SqlNullType.VARCHAR))\r\ncursor.execute(sql, params)\r\n```\r\n\r\n#### Reading NULL values\r\n\r\nA `NULL` column value is returned as `None`:\r\n\r\n```python\r\nsql = \"select * from customer_3 where customer_id = ? order by customer_id\"\r\nparams = (4,)\r\ncursor.execute(sql, params)\r\nrow = cursor.fetchone()\r\nprint (row)\r\n```\r\n\r\nExecution will return:\r\n\r\n```python\r\n(4, 'Sir ', 'William', 'Smith IV', '1 Madison Ave', 'New York', 'NY 10010 ', None)\r\n```\r\n\r\nIn this AceQL module version: there is no difference for string columns between a real NULL in the database and the ''NULL\" string.\r\n\r\n### Transactions\r\n\r\nTransactions are supported by the module. Because the remote server executes JDBC code, client code must follow the JDBC requirement to set the auto commit mode to false prior executing a transaction.\r\n\r\nThis is done with `Cursor.set_auto_commit(False)`. It is good practice to always reset auto commit mode to true at end of your transactions. Not that it auto commit mode state is undefined when a `Connection` is created with `aceql.connect()` call.\r\n\r\nTransaction example:\r\n\r\n```python\r\n# To do prior transaction\r\nself.connection.set_auto_commit(False)\r\n\r\ncursor = self.connection.cursor()\r\n\r\ntry:\r\n # Create a Customer\r\n sql = \"insert into customer values (?, ?, ?, ?, ?, ?, ?, ?)\"\r\n params = (customer_id, 'Sir', 'John', 'Smith', '1 Madison Ave',\r\n 'New York', 'NY 10010', '+1 212-586-7000')\r\n cursor.execute(sql, params)\r\n\r\n # Create an Order for this Customer\r\n sql = \"insert into orderlog values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )\"\r\n\r\n the_datetime = datetime.now()\r\n the_date = the_datetime.date()\r\n\r\n # (None, SqlNullType.BLOB) means to set the jpeg_image BLOB\r\n # column to NULL on server:\r\n params = (customer_id, item_id, \"Item Description\", 9999,\r\n the_date, the_datetime, (None, SqlNullType.BLOB), 1, 2)\r\n cursor.execute(sql, params)\r\n\r\n self.connection.commit()\r\nexcept Error as e:\r\n print(e)\r\n self.connection.rollback()\r\n raise e\r\nfinally:\r\n self.connection.set_auto_commit(True) # Good practice\r\n cursor.close()\r\n```\r\n\r\n### Proxies\r\n\r\nThe AceQL module support proxies, using the [proxy](https://requests.readthedocs.io/en/master/user/advanced/#proxies) syntax of [Requests](https://requests.readthedocs.io/en/master/) The aceql module uses Requests for HTTP communications with the remote server. All options of a new AceQL connection are passed with the `ConnectionOptions` wrapper.\r\n\r\n```python\r\nimport aceql\r\nfrom aceql import ConnectionOptions\r\n\r\n# URL of the AceQL server, Remote SQL database name & authentication info\r\nurl = \"https://www.acme.com:9443/aceql\"\r\ndatabase = \"sampledb\"\r\nusername = \"user1\"\r\npassword = \"password1\"\r\n\r\nproxies = {\r\n 'http': 'http://10.10.1.10:3128',\r\n 'https': 'http://10.10.1.10:1080',\r\n}\r\n\r\n# We use the ConnectionOptions wrapper class to pass the proxies\r\nconnection_options = ConnectionOptions(proxies=proxies)\r\nconnection = aceql.connect(url=url, username=username, password=password, database=database,\r\n connection_options=connection_options)\r\n```\r\n\r\nAuthenticated proxies are supported. Just create an `aceql.ProxyAuth` instance and pass it to `aceql.connect()` with the `ConnectionOptions` wrapper.\r\n\r\n```python\r\nimport aceql\r\nfrom aceql import ConnectionOptions\r\nfrom aceql import ProxyAuth\r\nfrom samples import my_proxy\r\n\r\n# URL of the AceQL server, Remote SQL database name & authentication info\r\nurl = \"https://www.acme.com:9443/aceql\"\r\ndatabase = \"sampledb\"\r\nusername = \"user1\"\r\npassword = \"password1\"\r\n\r\nproxies = {\r\n \"http\": \"http://localhost:8081\",\r\n}\r\n\r\n# Get the proxy credentials with our own application methods\r\nproxy_username = my_proxy.get_username()\r\nproxy_password = my_proxy.get_password()\r\n\r\n# The AceQL ProxyAuth class allows to define the proxy credentials\r\nauth = ProxyAuth(proxy_username, proxy_password)\r\n\r\n# We use the ConnectionOptions wrapper class to pass both the proxies & the auth\r\nconnection_options = ConnectionOptions(proxies=proxies, auth=auth)\r\nconnection = aceql.connect(url=url, username=username, password=password, database=database,\r\n connection_options=connection_options)\r\n```\r\n\r\nThe AceQL module uses [requests-toolbelt](https://pypi.python.org/pypi/requests-toolbelt) for authenticated proxy management.\r\n\r\n### Timeouts\r\n\r\nUse the `timeout` parameter of `ConnectionOptions` to pass a timeout value in the [Requests Timeouts](https://requests.readthedocs.io/en/master/user/advanced/#timeouts) format.\r\n\r\nIf no timeout is specified explicitly, requests do not time out. \r\n\r\n```python\r\nconnection_options = ConnectionOptions(timeout=10)\r\nconnection = aceql.connect(url=url, username=username, password=password, database=database,\r\n connection_options=connection_options)\r\n```\r\n\r\n### BLOB management\r\n\r\nThe AceQL module supports BLOB creation and reading. Methods are implemented using streaming techniques to keep low memory consumption. CLOBs are not supported in this version.\r\n\r\n#### BLOB creation\r\n\r\nBLOB creation is supported by passing a tuple with a File Object as parameter of a prepared statement:\r\n\r\n```python\r\nsql = \"insert into orderlog values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )\"\r\n\r\nfilename = os.getcwd() + sep + \"item_1_image.png\"\r\nfd = open(filename, \"rb\") # File will be closed by AceQL\r\nblob_tuple = (fd, )\r\n\r\nparams = (1, 1, \"Item 1 Description\", 9999,\r\n datetime.now() , datetime.now().date(), blob_tuple, 1, 2)\r\ncursor.execute(sql, params)\r\n```\r\n\r\n#### BLOB reading\r\n\r\nBLOB reading is supported through `Cursor.get_blob_stream(column_index`). The stream can then be read with a `for` loop that iterates on the `respsonse`, using syntax provided by [Requests](https://requests.readthedocs.io/en/master/user/quickstart/#raw-response-content):\r\n\r\n```python\r\nsql = \"select customer_id, item_id, jpeg_image from orderlog \" \\\r\n \"where customer_id = ? and item_id = ?\"\r\nparams = (1, 1)\r\ncursor.execute(sql, params)\r\nrow = cursor.fetchone()\r\n\r\n# You can get BLOB length if you want to use a progress indicator\r\nblob_length = cursor.get_blob_length(2)\r\nprint(\"blob length: \" + str(blob_length))\r\n\r\n# Get the stream to the remote BLOB\r\nresponse = cursor.get_blob_stream(2)\r\n\r\n# Download is streamed and writen into filename\r\nfilename = os.path.expanduser(\"~\") + sep + \"jpeg_image.jpg\"\r\nwith open(filename, 'wb') as fd:\r\n for chunk in response.iter_content(chunk_size=2048):\r\n fd.write(chunk)\r\n\r\nstat_info = os.stat(filename)\r\nprint(\"file length: \" + str(stat_info.st_size))\r\n```\r\n\r\n#### Managing BLOB upload progress\r\n\r\nYou may want to give your users a progress bar when uploading BLOBs.\r\n\r\nThe `ProgressIndicator.Percent` property allows you to get the current percent of upload. Value will be incremented automatically during upload.\r\n\r\nTo activate the update mechanism:\r\n\r\n1/ Set the long BLOB length along the File Object in the tuple of the BLOB prepared statement parameter:\r\n\r\n```python\r\nfile_length = os.stat(filename).st_size\r\n\r\nfd = open(filename, \"rb\")\r\nblob_tuple = (fd, file_length)\r\n```\r\n\r\n2/ Create your `ProgressIndicator` instance and enter it to the `Connection` instance before the `Cursor.execute(sql, params)` call :\r\n\r\n```python\r\nprogress_indicator = ProgressIndicator()\r\nconnection.set_progress_indicator(progress_indicator)\r\n```\r\n\r\nYou then can read `ProgressIndicator.percent` property in your watching thread.\r\n\r\nCode sample:\r\n\r\n```python\r\nwith closing(connection.cursor()) as cursor:\r\n filename = os.getcwd() + sep + \"item_1_image.jpg\"\r\n file_length = os.stat(filename).st_size\r\n\r\n fd = open(filename, \"rb\")\r\n blob_tuple = (fd, file_length)\r\n\r\n progress_indicator = ProgressIndicator()\r\n connection.set_progress_indicator(progress_indicator)\r\n\r\n sql = \"insert into orderlog values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )\"\r\n\r\n params = (1, 1, \"Item 1 Description\", 9999,\r\n datetime.now() , datetime.now().date(),\r\n blob_tuple, 1, 2)\r\n \r\n # cursor.execute() uploads BLOB by chunks and increments \r\n # ProgressIndicator.percent property\r\n cursor.execute(sql, params)\r\n```\r\n\r\n### Calling AceQL Java stored procedures\r\n\r\nThe AceQL client SDK allows executing a remote server Java class that implements the AceQL Server\r\n\r\n`org.kawanfw.sql.api.server.executor.ServerQueryExecutor` interface and that returns the rows of the SELECT.\r\n\r\nSee the `org.kawanfw.sql.api.server.executor.ServerQueryExecutor` [Javadoc](https://docs.aceql.com/rest/soft/11.0/javadoc/org/kawanfw/sql/api/server/executor/ServerQueryExecutor.html).\r\n\r\nThe usage on the client side is straightforward with the `Cursor.ExecuteServerQueryAsync()` method:\r\n\r\n```python\r\nserver_query_executor_class_name = \"com.mycompany.MyServerQueryExecutor\"\r\nmy_parameters = [1]\r\n\r\ncursor.execute_server_query(server_query_executor_class_name, my_parameters)\r\nprint(\"cursor.rowcount : \" + str(cursor.rowcount))\r\nrows = cursor.fetchall()\r\n\r\nprint(\"fetchall:\")\r\nfor row in rows:\r\n print(row)\r\n\r\ncursor.close()\r\n```\r\n\r\n### Using outer authentication without a password and with an AceQL Session ID\r\n\r\nSome working environments (Intranet, etc.) require that the client user authenticates himself without a password. Thus, it is not possible for this users to authenticate though the AceQL client SDK.\r\n\r\nIn this case, you may use directly the native HTTP [login](https://github.com/kawansoft/aceql-http/blob/master/aceql-http-user-guide-api.md#login) API to authenticate the users and retrieve the `session_id` returned by the API. Just pass the value of the `session_id` to the `ConnectionOptions` instance, along with a `None` `password` to `connect`.\r\n\r\n```python\r\nimport aceql\r\nfrom aceql import ConnectionOptions\r\nfrom samples import my_session_id_builder\r\n\r\n# URL of the AceQL server, Remote SQL database name & authentication info without password\r\nurl = \"https://www.acme.com:9443/aceql\"\r\ndatabase = \"sampledb\"\r\nusername = \"user1\"\r\n\r\n# Our application will get the session_id to use\r\nsession_id = my_session_id_builder.get_session_id_from_login_api()\r\n\r\n# We use the ConnectionOptions wrapper to tell to use session_id instead of a password\r\nconnection_options = ConnectionOptions(session_id=session_id)\r\nconnection = aceql.connect(url=url, username=username, password=None, database=database,\r\n connection_options=connection_options)\r\n```\r\n\r\n## Using the Metadata Query API \r\n\r\nThe metadata API allows:\r\n\r\n- downloading a remote database schema\r\n in HTML or text format,\r\n- to get a remote database main properties,\r\n- to get the list of tables, \r\n- to get the details of each table. \r\n\r\nIt also allows wrapping remote tables, columns, indexes, etc. into\r\neasy to use provided Python classes: Table, Index, Column, etc.\r\n\r\nFirst step is to get an instance of `RemoteDatabaseMetaData`:\r\n\r\n```python\r\nremote_database_meta_data = RemoteDatabaseMetaData(connection)\r\n```\r\n\r\n### Downloading database schema into a file\r\n\r\nDownloading a schema into a `File` is done through the `db_schema_download(filename)` method. See the `RemoteDatabaseMetaData` documentation:\r\n\r\n```python\r\nfilename = os.path.expanduser(\"~\") + os.sep + \"db_schema.html\"\r\nremote_database_meta_data.db_schema_download(filename)\r\n```\r\n\r\nSee an example of the built HTML schema: [db_schema.out.html](https://docs.aceql.com/rest/soft/11.0/src/db_schema.out.html)\r\n\r\n### Accessing remote database main properties\r\n\r\nThe `JdbcDatabaseMetaData` class wraps instance the main value retrieved by a remote server JDBC call to `java.sql.Connection.getMetaData()`:\r\n\r\n```python\r\njdbc_meta_data = remote_database_meta_data.get_jdbc_database_meta_data()\r\nprint(\"Major Version: \" + str(jdbc_meta_data.getJDBCMajorVersion))\r\nprint(\"Minor Version: \" + str(jdbc_meta_data.getJDBCMinorVersion))\r\nprint(\"IsReadOnly : \" + str(jdbc_meta_data.isReadOnly))\r\n```\r\n\r\n### Getting Details of Tables and Columns\r\n\r\nSee the `RemoteDatabaseMetaData` documentation:\r\n\r\n```python\r\nprint(\"Print the column details of each table:\")\r\nfor table_name in table_names:\r\n table = remote_database_meta_data.get_table(table_name)\r\n\r\n print()\r\n print(\"Columns of table: \" + table_name)\r\n for column in table.columns:\r\n print(column)\r\n```\r\n\r\n____________\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n",
"bugtrack_url": null,
"license": "Apache 2.0",
"summary": "Python 3 Client toolkit for easy access of remote SQL databases managed with AceQL HTTP.",
"version": "5.6.1",
"split_keywords": [
"database",
"sql",
"http",
"client-server"
],
"urls": [
{
"comment_text": "",
"digests": {
"md5": "8c97307b7d27197799e3ab005dc74c08",
"sha256": "8c37a78128ba6392a350c31eb06377ba26ebd04036ca680e9112d53381962c92"
},
"downloads": -1,
"filename": "aceql-5.6.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "8c97307b7d27197799e3ab005dc74c08",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 91159,
"upload_time": "2022-12-12T12:59:21",
"upload_time_iso_8601": "2022-12-12T12:59:21.699595Z",
"url": "https://files.pythonhosted.org/packages/09/12/25cbf59ac1445709ff2e4a7d87c28aa2b8ee97e53a9ba65ec7c108612fa6/aceql-5.6.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"md5": "8989ab969f7d7d27948d6d20a6375ad5",
"sha256": "9ee0527e07b0d16663a12e7aff6e1db65126f1f0fb55a622c42d301c37dd2b22"
},
"downloads": -1,
"filename": "aceql-5.6.1.tar.gz",
"has_sig": false,
"md5_digest": "8989ab969f7d7d27948d6d20a6375ad5",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 57839,
"upload_time": "2022-12-12T12:59:24",
"upload_time_iso_8601": "2022-12-12T12:59:24.422783Z",
"url": "https://files.pythonhosted.org/packages/64/3a/cef3ab3a06a44f26b01cc80ba5538c4b6fa1cb32cfbc666111baff340847/aceql-5.6.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2022-12-12 12:59:24",
"github": true,
"gitlab": false,
"bitbucket": false,
"github_user": "kawansoft",
"github_project": "aceql-py",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"lcname": "aceql"
}
JSON
