ibmstreams/pypi.streamsx.database

This is a step in allowing natural use of Streams for a Python developer. A project that will be registered with PyPi to allow 'pip install' of Python packages that support Python developers interacting with IBM Streams.

pypi.streamsx.database's Issues

Support external connection (CP4D)

Extend configure_connection and run_statement for external connection (CP4D)

    db_external_connection = icpd_util.get_connection('Db2-Cloud',conn_class='external')
    res = db.run_statement(query, credentials=db_external_connection, schema=sample_schema)

map and select

hello,

I am new in streams, just want to ask a question about select in database. can i use select with "where" for each tuple value.

i implement a small topo, receive tuple and use the value in tuple to query the other related data from DB.

 # include 3 columns device_id, weatherStationId and rainStationId
table_name = "joinTest"
sql_select = 'SELECT device_id, weatherStationId, rainStationId FROM '+ table_name +' where device_id=\'?\''
tuple_schema = StreamSchema("tuple<rstring device_id>")
trdata = st.map(lambda tpl: (tpl["device_id"],), schema=tuple_schema)

stResults = db.run_statement(name="select", schema='tuple<rstring device_id, rstring weatherStationId, rstring rainStationId>', stream=trdata, sql=sql_select, sql_params="device_id" ,credentials = dbcredentials, jdbc_driver_lib="/opt/jdbc/mssql-jdbc.jar", jdbc_driver_class="com.microsoft.sqlserver.jdbc.SQLServerDriver")

but there is nothing response and i also have no idea to debug. i can't check the sql is correct or not. or i ca't use sql_params for select?

do you have any idea?

thank you!

Support default schema for JDBCStatement

/package/streamsx/database/_database.py", line 645, in populate
    return _op.outputs[0]
IndexError: list index out of range

        sql_drop = 'DROP TABLE RUN_SAMPLE'
        s = topo.source([sql_drop]).as_string()
        res_sql = s.map(db.JDBCStatement(credentials))
        res_sql.print()

if no schema in map is set, then set output schema to input schema

Provide composite class

uses streamsx feature:
IBMStreams/streamsx.topology#2322

Add download_toolkit function

calls common function from streamsx.toolkits with parameter for jdbc toolkit

update documentation of configure_connection

replace from streamsx.rest import Instance with from streamsx.rest_primitives import Instance

change download location of client jar

currently downloaded from master branch of streamsx.jdbc repo,
change to develop branch

map should return error tuples

JDBCRun returns an error port, but the python wrapper has no way to access this port.
Requesting an enhancement to implement a workaround for map to return error tuples only and not insert results.

Documentation examples need improvement to use a stream of data

Including code examples in the documentation is definitely very useful. To make it even more easy to use and ready for someone to modify, it should address the most common scenario which is how to use the package with a stream of data.

For example, the first sample:

sql_insert = 'INSERT INTO RUN_SAMPLE (A, B) VALUES ('hello', 'world')'
sql_drop = 'DROP TABLE RUN_SAMPLE'
s = topo.source([sql_create, sql_insert, sql_drop]).as_string()
res_sql = s.map(db.JDBCStatement(credentials))
res_sql.print()

uses hard coded data, which is not the most common scenario.

The second issue is, further down in the doc there are examples for inserting but it is missing the important step of showing that the stream of data needs to be converted to a structured stream.
For a beginner who is new to the Python API and the programming model, it was confusing.

I would recommend updating the examples to just show some generated data as the sample stream you want to insert, and show that it has to be converted to a sample schema so that the sample is complete and someone can copy and use it without missing a step.

Lastly, using 'A,B' for both the parameter name and tuple attribute name is confusing:

sample_schema = StreamSchema('tuple<rstring A, rstring B>')
...
stmt.sql = 'INSERT INTO RUN_SAMPLE (A, B) VALUES (?, ?)'
stmt.sql_params = 'A, B'