# Python crate using multiple cursors parallel

**URL:** <https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788>\
**Category:** Client Libraries\
**Created:** [June 21, 2024, 2:15pm UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788 "2024-06-21T14:15:05Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![SchabiDesigns](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/schabidesigns/32/1255_2.png) [@SchabiDesigns](https://community.cratedb.com/u/SchabiDesigns)\
**Post date:** [June 21, 2024, 2:15pm UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/1 "2024-06-21T14:15:05Z")

</div>

Hello guys

The [documentation](https://cratedb.com/docs/python/en/latest/query.html) says to initialize a connection and create cursors and then use them to execute queries and fetch data. Can multiple cursors been created and do jobs parallel? Or is there another approach to parallelize executing queries?

Thanks for your help!  
Regards Schabi

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [June 21, 2024, 4:12pm UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/2 "2024-06-21T16:12:31Z")

</div>

Hi @SchabiDesigns

The `crate-python` library is thread-safe only at the module level, not at the connection or cursor level. Additionally, since the DB API lacks an asynchronous specification, it does not yet support executing queries asynchronously. To use `crate-python` in parallel, you must open multiple connections.

If you need async execution, you might want to look into using [asyncpg](https://magicstack.github.io/asyncpg/current/) or [psycopg3](https://www.psycopg.org/) using the pg wire protocol port (5432)

---

<div class="post-metadata">

**Author:** ![SchabiDesigns](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/schabidesigns/32/1255_2.png) [@SchabiDesigns](https://community.cratedb.com/u/SchabiDesigns)\
**Post date:** [June 24, 2024, 8:16am UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/3 "2024-06-24T08:16:22Z")

</div>

Thanks for your help @proddata

The [documentation](https://cratedb.com/docs/crate/clients-tools/en/latest/connect/python.html#asyncpg) helped me to fetch some data from the database.

How would you establish a [pool feature](https://magicstack.github.io/asyncpg/current/usage.html#connection-pools) from the _asyncpg_ package?

**Some Background** : I use [streamlit](https://docs.streamlit.io/) in combination with crateDB. streamlit provide some [drivers for different database solutions](https://docs.streamlit.io/develop/tutorials/databases). I would implement one for crateDB, but not sure how it has to be done properly.

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [June 25, 2024, 11:20am UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/4 "2024-06-25T11:20:51Z")

</div>

Dear Schabi,

> [@SchabiDesigns](#):
>
> The [documentation](https://cratedb.com/docs/crate/clients-tools/en/latest/connect/python.html#asyncpg) helped me to fetch some data from the database.

Excellent. Thanks. Please let us know any time where details can be improved.

> [@SchabiDesigns](#):
>
> How would you establish a [pool feature](https://magicstack.github.io/asyncpg/current/usage.html#connection-pools) from the _asyncpg_ package?

Sorry for not displaying a verbatim example demonstrating this use case. Are you able to make it work by combining relevant details appropriately, or do you observe any errors when doing so?

> [@SchabiDesigns](#):
>
> **Background** : I use [streamlit](https://docs.streamlit.io/) in combination with crateDB. streamlit provide some [drivers for different database solutions](https://docs.streamlit.io/develop/tutorials/databases). I would implement one for crateDB, but not sure how it has to be done properly.

We also would like to see CrateDB working well together with Streamlit, so we appreciate you sharing more details about your use case with us. Did you already try to make it work using Streamlit’s [PostgreSQL database adapter](https://docs.streamlit.io/develop/tutorials/databases/postgresql)?

With kind regards,  
Andreas.

---

<div class="post-metadata">

**Author:** ![SchabiDesigns](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/schabidesigns/32/1255_2.png) [@SchabiDesigns](https://community.cratedb.com/u/SchabiDesigns)\
**Post date:** [June 26, 2024, 3:54pm UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/5 "2024-06-26T15:54:54Z")

</div>

Thanks @amotl

- Documentation is good like it is 🙂
- I also managed to use the pool feature from the _asyncpg_ package, which wasn’t a big deal.
- Nice to hear that you would like to have crateDB working with streamlit. 🤩

I tried the [PostgreSQL database adapter](https://docs.streamlit.io/develop/tutorials/databases/postgresql) you mentioned before, but faced some errors.

```auto
sqlalchemy.exc.InternalError: (psycopg2.errors.InternalError_) line 1:1: mismatched input 'ROLLBACK' expecting {'SELECT', 'DEALLOCATE', 'FETCH', 'END', 'WITH', 'CREATE', 'ALTER', 'KILL', 'CLOSE', 'BEGIN', 'START', 'COMMIT', 'ANALYZE', 'DISCARD', 'EXPLAIN', 'SHOW', 'OPTIMIZE', 'REFRESH', 'RESTORE', 'DROP', 'INSERT', 'VALUES', 'DELETE', 'UPDATE', 'SET', 'RESET', 'COPY', 'GRANT', 'DENY', 'REVOKE', 'DECLARE'}
CONTEXT: io.crate.exceptions.SQLExceptions.esToCrateException(SQLExceptions.java:211)
io.crate.exceptions.SQLExceptions.prepareForClientTransmission(SQLExceptions.java:200)
io.crate.protocols.postgres.Messages.sendErrorResponse(Messages.java:189)
io.crate.protocols.postgres.PostgresWireProtocol.handleSimpleQuery(PostgresWireProtocol.java:788)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.dispatchMessage(PostgresWireProtocol.java:340)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.dispatchState(PostgresWireProtocol.java:330)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.channelRead0(PostgresWireProtocol.java:298)
io.crate.protocols.postgres.PostgresWireProtocol$MessageHandler.channelRead0(PostgresWireProtocol.java:282)
io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:346)
io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:318)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:440)
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)

(Background on this error at: https://sqlalche.me/e/20/2j85)

```

I also took a look into [st.connection](https://docs.streamlit.io/develop/api-reference/connections/st.connection) and there is also a youtube-video how to add a connection to any database, but not sure how it has to be done 🫣

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [June 26, 2024, 4:11pm UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/6 "2024-06-26T16:11:58Z")

</div>

Hi again,

> [@SchabiDesigns](#):
>
> I tried the [PostgreSQL database adapter](https://docs.streamlit.io/develop/tutorials/databases/postgresql) you mentioned before, but faced some errors, [effectively conveying back]:
> 
> ```auto
> mismatched input 'ROLLBACK'
> 
> ```

Thank you for sharing your outcome. This error message usually indicates some other error happened where CrateDB does not exactly behave like PostgreSQL, the error is propagated to the application, and it issues a ROLLBACK, which also does not work but finally bubbles up the stack to be presented to the user.

In other words, another query fails, and the error report is getting masked by the exception you’ve shared.

With kind regards,  
Andreas.

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [June 26, 2024, 4:13pm UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/7 "2024-06-26T16:13:23Z")

</div>

@proddata was so kind to whip up a quick example at [GitHub - proddata/streamlit-cratedb](https://github.com/proddata/streamlit-cratedb). It looks like it validates that CrateDB is able to work successfully with Streamlit. You may want to exercise this application and maybe start from there when applicable?

However, I also admit it does not exactly satisfy your specific questions yet, about database connection pool concerns, about support for efficient and safe parallel access, or support for [Python’s asyncio](https://docs.python.org/3/library/asyncio.html). On the Python DB API driver which is using the HTTP transport, we are adjusting the thread safety advertisement with [Configure DB API interface attribute `threadsafety = 1`: Threads may share the module, but not connections by amotl · Pull Request #635 · crate/crate-python · GitHub](https://github.com/crate/crate-python/pull/635). So, when following relevant constraints, by maintaining a dedicated connection per thread or process, parallel access should work well.

On the side of support for `asyncio`, because Streamlit uses SQLAlchemy to connect to SQL databases, that other patch may well be able to make a difference if async operations on the database level are supported by Streamlit: [Dialect: Add support for `asyncpg` and `psycopg3` drivers by amotl · Pull Request #11 · crate/sqlalchemy-cratedb · GitHub](https://github.com/crate/sqlalchemy-cratedb/pull/11). It hasn’t been mainlined yet, but the PR description includes instructions how to install the improvement directly from the feature branch. Documentation is missing yet, but the change log item conveys the ingredients of the patch:

> Added support for `psycopg` and `asyncpg` drivers, by introducing the `crate+psycopg://`, `crate+asyncpg://`, and `crate+urllib3://` dialect identifiers. The asynchronous variant of `psycopg` is also supported.

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [June 26, 2024, 7:29pm UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/8 "2024-06-26T19:29:16Z")

</div>

> [@SchabiDesigns](#):
>
> I also took a look into [st.connection](https://docs.streamlit.io/develop/api-reference/connections/st.connection) and there is also a youtube-video how to add a connection to any database, but not sure how it has to be done 🫣

Actually it “simply works” when adding `sqlalchemy-cratedb` to your `requirements.txt` and specifying secrets like

```auto
sqlalchemy
sqlalchemy-cratedb
streamlit

```

```toml
# .streamlit/secrets.toml

[connections.cratedb]
dialect = "crate"
host = "localhost"
port = "4200"
username = "crate"
password = ""

```

then you can just

```auto
conn = st.connection("cratedb", type="sql")
query_result = conn.query(query, ttl="10m")

```

I updated [GitHub - proddata/streamlit-cratedb](https://github.com/proddata/streamlit-cratedb/tree/main) … did not know it was that simple 😉

---

<div class="post-metadata">

**Author:** ![SchabiDesigns](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/schabidesigns/32/1255_2.png) [@SchabiDesigns](https://community.cratedb.com/u/SchabiDesigns)\
**Post date:** [June 27, 2024, 5:57am UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/9 "2024-06-27T05:57:08Z")

</div>

Thanks for your help Georg and Andreas!  
Not sure if I should split this topic and if how it should be done… There are kind of 2 problems and 2 solutions 🙂

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [June 27, 2024, 12:20pm UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/10 "2024-06-27T12:20:35Z")

</div>

Hi again.

> [@SchabiDesigns](#):
>
> Thanks for your help Georg and Andreas!

You are welcome. Please ask further questions any time. Based on your suggestions, and @proddata’s investigations, we added those two resources to our documentation and code snippets.

- [Using Programming Frameworks with CrateDB — CrateDB: Clients and Tools](https://crate-clients-tools--132.org.readthedocs.build/en/132/integrate/framework.html)
- [cratedb-examples/framework/streamlit at main · crate/cratedb-examples · GitHub](https://github.com/crate/cratedb-examples/tree/main/framework/streamlit)

> [@SchabiDesigns](#):
>
> Not sure if I should split this topic and if how it should be done… There are kind of 2 problems and 2 solutions 🙂

No worries about splitting this. While I admit the conversation mixes different topics, I think it will be too difficult to split in this case. If you want, you may want to rephrase the title and fragments of your original post, at your disposal.

Cheers,  
Andreas.

---

<div class="post-metadata">

**Author:** ![SchabiDesigns](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/schabidesigns/32/1255_2.png) [@SchabiDesigns](https://community.cratedb.com/u/SchabiDesigns)\
**Post date:** [July 1, 2024, 2:27pm UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/11 "2024-07-01T14:27:20Z")

</div>

> [@proddata](#):
>
> ```auto
> # .streamlit/secrets.toml
> 
> [connections.cratedb]
> dialect = "crate"
> host = "localhost"
> port = "4200"
> username = "crate"
> password = ""
> 
> ```

Hi Georg,

do you know how to add multiple hosts so that a kind of load balancer is in place?  
Or do I need to setup a load balancer manually on the cluster and use that access point?

Regards Pascal

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [July 1, 2024, 2:48pm UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/12 "2024-07-01T14:48:53Z")

</div>

I think `connection()` takes `kwargs**` and sqlalchemy-cratedb supports a `servers` argument, but I am not sure how they need to be provided. Maybe @amotl knows 😬

> <https://github.com/crate/sqlalchemy-cratedb/blob/6db47020b59b3a07257a487e9fe3a8a4e4b9e76a/src/sqlalchemy_cratedb/dialect.py#L212>

---

<div class="post-metadata">

**Author:** ![SchabiDesigns](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/schabidesigns/32/1255_2.png) [@SchabiDesigns](https://community.cratedb.com/u/SchabiDesigns)\
**Post date:** [July 1, 2024, 3:31pm UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/13 "2024-07-01T15:31:04Z")

</div>

Thanks Georg 🙂

I just added the servers list to the toml file but host have to be included as well. Due to that I have no information if any load balancing will be happening or not.  
@amotl Andreas is there any way to get that information? I found that method active\_servers() within the source code of the [http client](https://github.com/crate/crate-python/blob/master/src/crate/client/http.py) but I think I have no access from within streamlit:

 ![streamlit conn-object](https://us1.discourse-cdn.com/flex020/uploads/crate/original/2X/8/8db3b44546b2d9e85e3531a242415f7f16e039d8.png)

Thanks for your help guys!

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [July 1, 2024, 4:28pm UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/14 "2024-07-01T16:28:03Z")

</div>

Dear Pascal,

> [@SchabiDesigns](#):
>
> I found that method active\_servers() within the source code of the [http client](https://github.com/crate/crate-python/blob/master/src/crate/client/http.py) but I think I have no access from within streamlit:

Based on this modified [synopsis example code](https://cratedb.com/docs/sqlalchemy-cratedb/#synopsis) at [Access list of active server connections to CrateDB when using SQLAlchemy · GitHub](https://gist.github.com/amotl/e39d8e6ac93c0c0c37746fdb7ec66784), you can observe it’s there, but the requested attribute is a bit nested.

So, effectively, just use `connection.connection.dbapi_connection.client.active_servers`, where the first `connection` symbol is a reference to an `sa.engine.Connection` object, like outlined in the example above.

With kind regards,  
Andreas.

---

<div class="post-metadata">

**Author:** ![SchabiDesigns](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/schabidesigns/32/1255_2.png) [@SchabiDesigns](https://community.cratedb.com/u/SchabiDesigns)\
**Post date:** [July 2, 2024, 6:20am UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/15 "2024-07-02T06:20:39Z")

</div>

> [@amotl](#):
>
> connection.dbapi\_connection.client.active\_servers

Thanks Andreas @amotl  
That helped a lot!  
I used following to check the active servers:

> conn = st.connection(“crateDB”, type=“sql”)  
> conn\_alchemy\_engine = conn.connect()  
> conn\_alchemy\_engine.connection.dbapi\_connection.client.active\_servers()

This results in **only one element** , which I specified in the _toml_ file under host.  
The servers property will be ignored and if I try to add multiple hosts this also fails from streamlit side. I also tried a stupid approach to add servers with the append method:

> conn\_alchemy\_engine.connection.dbapi\_connection.client.active\_servers.append([list\_of\_servers])

which had no effect.

Thanks for your help  
Regards

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [July 3, 2024, 8:08am UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/16 "2024-07-03T08:08:06Z")

</div>

Hi Pascal,

I’ve created a corresponding issue at [Using multiple servers/endpoints · Issue #146 · crate/sqlalchemy-cratedb · GitHub](https://github.com/crate/sqlalchemy-cratedb/issues/146), in order to investigate the problem. Please let us know about the urgency of this matter on your side \[1\], so we can optimally plan accordingly.

With kind regards,  
Andreas.

* * *

1. NB/JFYI: Because those servers would be used in a round-robin way, just configuring multiple of them definitively does not increase concurrency.

---

<div class="post-metadata">

**Author:** ![SchabiDesigns](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/schabidesigns/32/1255_2.png) [@SchabiDesigns](https://community.cratedb.com/u/SchabiDesigns)\
**Post date:** [July 3, 2024, 12:25pm UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/17 "2024-07-03T12:25:44Z")

</div>

Hi Andreas  
There is a work around so it is not that important. 😉  
 → I think I will go for a load balancer and use that as entry point for the cluster. Do you have a recommendation?  
Regards

---

<div class="post-metadata">

**Author:** ![surister](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/surister/32/1087_2.png) [@surister](https://community.cratedb.com/u/surister)\
**Post date:** [September 12, 2024, 7:40am UTC](https://community.cratedb.com/t/python-crate-using-multiple-cursors-parallel/1788/18 "2024-09-12T07:40:33Z")

</div>

> [@What do you recommend as a load balancer?](https://community.cratedb.com/t/what-do-you-recommend-as-a-load-balancer/805):
>
> Hello, In your documentation at [Clustering — CrateDB: Reference](https://crate.io/docs/crate/reference/en/4.6/concepts/clustering.html), I see the following architecture: I have two questions: Do you recommend a particular load balancer, e.g. [http://www.haproxy.org](http://www.haproxy.org) ? Unlike Figure 5, and similar to Figure 4 in the documentation above, would it be OK to put the Load Balancer between an “Application” instance and CrateDB nodes? I assume that should be OK, because if I understand correctly, any CrateDB node can access any part of the data thanks to repli…