jaydebeapi | JayDeBeApi module allows you to connect from Python code | Database library

So I am having some performance issues with my program. When I run it, it crashes nearly every time. Most likely, the program cannot process over a million rows at once. This occurs when I run cursor.fetchall() in the code below:

I'm connecting to a Oracle database using the following code:

Using the Keyword Query from the Robot Framework DatabaseLibrary JayDeBeApi in conjunction with DB2 like this: ${results}= Query CREATE TABLE SCHEMANAME.TEST_TEMP (id BIGINT, name VARCHAR(25)) is being executed (table exists afterwards).

But nevertheless RobotFramework throws a FAIL and ${results} contains the Message DatabaseError: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-601, SQLSTATE=42710, SQLERRMC=SCHEMANAME.TEST_TEMP;TABLE, DRIVER=4.14.122 and often even a very simple Message Error after running the same statement.

Running the query above (copy/paste) directly within a database SQL window doesn't return any errors.

How is it possible, in RobotFramework the query is executed successfully but nevertheless an error is thrown?

Task I am writing a Python script that makes some changes in a HSQL-DB and then rexecutes a java-programm via the command line, which works with the data in the DB. I am using jaydebeapi for altering the data in Python.

Problem The java program fails because it cannot obtain a DB lock. This is despite me having closed the cursor and connection as stated in the documentation.

I am trying to connect and test a H2 database stored inside a local file with path: /home/me/mydb.db with RobotFramework, where user is sa without password.

I would like to know the proper string for connection. I've tried to use jaydebeapi with a downloaded h2 driver .jar file but without success. Thank you for the help!

I am using jpype, jaydebeapi to connect to an Oracle database which works fine but the sql returns tuples instead of strings with brackets and a comma after each character.

e.g. instead of dog it returns (d,o,g,)

Running the same sql in DBeaver or Toad returns the string without alteration as you would expect.

Any idea on how to return the string without brackets and comma please? (I can still use the data but it looks weird in a chart and makes the chart and other outputs harder to read).

I have already searched Google and stackoverflow for similar issues (link1,link2,link3) but non of these solutions have worked for me. I need to find a way to loop through all columns to find those with dtype string and then join all tuples in those.

Here is a screenshot of how the data is being returned: df_screenshot

Here is a screenshot of a pivot (showing the data can be used without using brackets and commas): pivot_screenshot

Here is a screenshot of a chart (again showing string with brackets and commas): chart_screenshot

The code I'm using for the connection is as follows (no issues with this as far as I can see?):

I'm fetching data from a db with the use of jaydebeapi pkg. I want to serialize the data (it's in list of tuples format) using pickle.

The data may contain different java class types. For a certain data type () I get the following error message:

I am trying to connect to Apache Drill from python using jaydebeapi library.

I have turned on drill in embedded mode via drill-embedded, and the web ui runs correctly in port 8047. Then, I am trying to connect via JDBC through a python script:

I am currently looking for optimal way how to obtain a random data sample from the table (for instance in HIVE). I know that PRESTO provides either RANDOM() function or TABLESAMPLE BERNOULLI/SYSTEM. Problem is when querying table with significant number of records, it takes a lot of time, which is not suitable with cooperation with JayDeBeApi which might close the connection waiting too long for the response.

I would prefer to use TABLESAMPLE BERNOULLI/SYSTEM which takes as an argument percentage of the records to be fetched. To compare with ORACLE, SAP or MSSQL databases which enable to pass the precise percentage, i.e. 0.003123412%, the PRESTO does not allows you, despite that the function are quite similar, and everything is converted in the range 1-100%.

Does anyone know some workaround how to solve this? I would prefer to avoid the limit clause in cooperation with TABLESAMPLE BERNOULLI/SYSTEM which might not work as expected.