Skip to content

[PG DRIVER] params are not working for cypher (and probably others languages others than SQL) #2119

Closed

Description

@ExtReMLapin

Title, test code :

import psycopg

db_string_test = 'TEST'

with psycopg.connect(user="root", password="rootroot",
                    host='localhost',
                    port='5432',
                    dbname=db_string_test,
                    sslmode='disable'
                    ) as connection:
    
    connection.autocommit = True

    with connection.cursor(row_factory=psycopg.rows.dict_row) as cursor:
        params = {'select_value': 4}
        query = "SELECT %(select_value)s"
        cursor.execute(query, params)
        result = cursor.fetchall()
        print(result)
    print('sql param works')


    with connection.cursor(row_factory=psycopg.rows.dict_row) as cursor:
        
        query = '{cypher}return $ret_value'
        cursor.execute(query, {'ret_value':1})
        ret = cursor.fetchall()
        print(ret)

    print('cypher param works')

Activity

  1. ExtReMLapin commented on Apr 2, 2025

    @ExtReMLapin
    ContributorAuthor

    From my understanding it's because the client is asking the backend to parse and cypher params are not detected as parsable commands, so it never asks to pass arguments.

  2. robfrank commented on Apr 2, 2025

    @robfrank
    Collaborator

    "probably" we should give some love to the docs :) That said, the way to user parametrized queries should be (as per https://docs.arcadedb.com/#_10-minute-tutorial) :

    select something from aType where id= :id
    
  3. ExtReMLapin commented on Apr 2, 2025

    @ExtReMLapin
    ContributorAuthor

    Unless I misundertood your message, that's how things works with direct API but not how it can work with PG driver.

    From my understanding, the PG client (here psycopg) will either tell the backend (here arcadedb's implementation) to either directly execute the query (if there is no param syntax detected in the string) or to parse it before (if there is a param detected in the string).

    In this code :

    import psycopg
    
    db_string_test = 'ORANO_HEAVY_PSY2'
    
    with psycopg.connect(user="root", password="rootroot",
                        host='localhost',
                        port='5432',
                        dbname=db_string_test,
                        sslmode='disable'
                        ) as connection:
        
        connection.autocommit = True
    
        with connection.cursor(row_factory=psycopg.rows.dict_row) as cursor:
            params = {'select_value': 4}
            query = "SELECT %(select_value)s"
            cursor.execute(query, params)
            result = cursor.fetchone()
            print(result)
        print('sql param works')
    
    
        with connection.cursor(row_factory=psycopg.rows.dict_row) as cursor:
            params = {'select_value': 4}
            query = "{sqlscript}SELECT %(select_value)s"
            cursor.execute(query, params)
            result = cursor.fetchone()
            print(result)
        print('sqlscript param works')
    
        with connection.cursor(row_factory=psycopg.rows.dict_row) as cursor:
            
            query = '{cypher}return $ret_value'
            cursor.execute(query, {'ret_value':1})
            ret = cursor.fetchone()
            print(ret)
    
        print('cypher param works')

    "SELECT %(select_value)s" gets turned into SELECT $1 ( before being sent to the backend)
    Same for sqlscript as it uses the same syntax.


    SQLSCRIPT output when using PG syntax :

    2025-04-02 12:43:18.838 INFO  [PostgresNetworkExecutor] PSQL:-> request for password (R - 8b) (thread=102)
    2025-04-02 12:43:18.880 INFO  [PostgresNetworkExecutor] PSQL:-> authentication ok (R - 8b) (thread=102)
    2025-04-02 12:43:18.881 INFO  [PostgresNetworkExecutor] PSQL:-> backend key data (K - 12b) (thread=102)
    2025-04-02 12:43:18.881 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 24b) (thread=102)
    2025-04-02 12:43:18.881 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=102)
    2025-04-02 12:43:18.881 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=102)
    2025-04-02 12:43:18.882 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=102)
    Received message of type: P
    
    2025-04-02 12:43:18.882 INFO  [PostgresNetworkExecutor] PSQL: parse (portal=) -> {sqlscript}SELECT $1 (params=1) (errorInTransaction=false thread=102)
    2025-04-02 12:43:18.921 INFO  [PostgresNetworkExecutor] PSQL:-> parse complete (1 - 4b) (thread=102)
    Received message of type: B
    paramValuesCount: 1
    Parameter Value: 4
    
    2025-04-02 12:43:18.921 INFO  [PostgresNetworkExecutor] PSQL: bind (portal=) ->  (query={sqlscript}SELECT $1) (thread=102)
    2025-04-02 12:43:18.921 INFO  [PostgresNetworkExecutor] PSQL:-> bind complete (2 - 4b) (thread=102)
    Received message of type: D
    
    2025-04-02 12:43:18.922 INFO  [PostgresNetworkExecutor] PSQL: describe '' type=P (errorInTransaction=false thread=102)
    2025-04-02 12:43:18.922 INFO  [PostgresNetworkExecutor] PSQL:-> row description (T - 27b) (thread=102)
    Received message of type: E
    
    2025-04-02 12:43:18.922 INFO  [PostgresNetworkExecutor] PSQL: execute (portal=) (limit=0)-> {sqlscript}SELECT $1 (thread=102)
    2025-04-02 12:43:18.922 INFO  [PostgresNetworkExecutor] PSQL:-> command complete (C - 5b) (thread=102)
    Received message of type: S
    
    2025-04-02 12:43:18.922 INFO  [PostgresNetworkExecutor] PSQL: sync (thread=102)
    2025-04-02 12:43:18.923 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=102)
    Received message of type: X
    

    Please note the Parse call


    Here is the cypher query when using the ArcadeDB (opencypher) syntax :

    2025-04-02 12:42:38.485 INFO  [PostgresNetworkExecutor] PSQL:-> request for password (R - 8b) (thread=102)
    2025-04-02 12:42:38.532 INFO  [PostgresNetworkExecutor] PSQL:-> authentication ok (R - 8b) (thread=102)
    2025-04-02 12:42:38.533 INFO  [PostgresNetworkExecutor] PSQL:-> backend key data (K - 12b) (thread=102)
    2025-04-02 12:42:38.533 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 24b) (thread=102)
    2025-04-02 12:42:38.534 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=102)
    2025-04-02 12:42:38.534 INFO  [PostgresNetworkExecutor] PSQL:-> parameter status (S - 25b) (thread=102)
    2025-04-02 12:42:38.534 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=102)
    Received message of type: Q
    
    2025-04-02 12:42:38.536 INFO  [PostgresNetworkExecutor] PSQL: query -> Query[language=cypher, query=return $ret_value] javax.script.ScriptException: javax.script.ScriptException: groovy.lang.MissingPropertyException: No such property: ret_value for class: Script1
            at org.apache.tinkerpop.gremlin.groovy.jsr223.GremlinGroovyScriptEngine.eval(GremlinGroovyScriptEngine.java:383)
            at java.scripting/javax.script.AbstractScriptEngine.eval(AbstractScriptEngine.java:231)
            at com.arcadedb.gremlin.ArcadeGremlin.executeStatement(ArcadeGremlin.java:236)
            at com.arcadedb.gremlin.ArcadeGremlin.executeStatement(ArcadeGremlin.java:213)
            at com.arcadedb.gremlin.ArcadeGremlin.execute(ArcadeGremlin.java:73)
            at com.arcadedb.cypher.ArcadeCypher.execute(ArcadeCypher.java:66)
            at com.arcadedb.cypher.query.CypherQueryEngine.command(CypherQueryEngine.java:77)
            at com.arcadedb.cypher.query.CypherQueryEngine.command(CypherQueryEngine.java:159)
            at com.arcadedb.database.LocalDatabase.command(LocalDatabase.java:1334)
            at com.arcadedb.server.ServerDatabase.command(ServerDatabase.java:461)
            at com.arcadedb.postgres.PostgresNetworkExecutor.queryCommand(PostgresNetworkExecutor.java:391)
            at com.arcadedb.postgres.PostgresNetworkExecutor.lambda$run$4(PostgresNetworkExecutor.java:173)
            at com.arcadedb.postgres.PostgresNetworkExecutor.readMessage(PostgresNetworkExecutor.java:1111)
            at com.arcadedb.postgres.PostgresNetworkExecutor.run(PostgresNetworkExecutor.java:165)
    Caused by: javax.script.ScriptException: groovy.lang.MissingPropertyException: No such property: ret_value for class: Script1
            at org.apache.tinkerpop.gremlin.groovy.jsr223.GremlinGroovyScriptEngine.eval(GremlinGroovyScriptEngine.java:702)
            at org.apache.tinkerpop.gremlin.groovy.jsr223.GremlinGroovyScriptEngine.eval(GremlinGroovyScriptEngine.java:381)
            ... 13 more
    Caused by: groovy.lang.MissingPropertyException: No such property: ret_value for class: Script1
            at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:67)
            at org.codehaus.groovy.vmplugin.v8.IndyGuardsFiltersAndSignatures.unwrap(IndyGuardsFiltersAndSignatures.java:163)
            at org.codehaus.groovy.vmplugin.v8.IndyInterface.fromCache(IndyInterface.java:321)
            at Script1.run(Script1.groovy:1)
            at org.apache.tinkerpop.gremlin.groovy.jsr223.GremlinGroovyScriptEngine.eval(GremlinGroovyScriptEngine.java:679)
            ... 14 more
    
    2025-04-02 12:42:39.478 INFO  [PostgresNetworkExecutor] PSQL:-> ready for query (Z - 5b) (thread=102)
    Received message of type: X
    

    Please note instead the Q (execute) call instead .

    I should have tested with the psycopg like sql and sql script and maybe it could have worked (but i'm out of the office now).

    My point is, from my understanding we cannot use the PG driver without rewriting it because it doesn't correctly support params.

    It expects it's own syntax instead of the ArcadeDB syntax.

  4. self-assigned this
    on Apr 6, 2025
  5. added this to the 25.4.1 milestone on Apr 6, 2025
  6. ExtReMLapin commented on Apr 9, 2025

    @ExtReMLapin
    ContributorAuthor

    I took a look at it this weekend.
    IMO it would be better to add a translation layer to https://github.com/extremlapin/pyarcade

    Detecting variables for each languages, SQL with :variable Cyher with $variable

    While we can easily do this with cypher using py2neo.cypher doing it with SQL sounds harder because arcade got it's own SQL syntax and replacing each : followed by a word sounds really like a bad idea.

  7. added 3 commits that reference this issue on Apr 10, 2025
  8. robfrank commented on Apr 10, 2025

    @robfrank
    Collaborator

    I added a couple of tests:

    def test_psycopg2_with_named_parameterized_query():

    Tests are green.

    reading the doc https://www.psycopg.org/psycopg3/docs/basic/params.html and the one related to Java JDBC preparedStatement, I discovered that the replacement of params is performed on client side.

  9. ExtReMLapin commented on Apr 10, 2025

    @ExtReMLapin
    ContributorAuthor

    Yes thats why the translation layer should be implemented on the client (here pyarcade imo)

  10. robfrank commented on Apr 11, 2025

    @robfrank
    Collaborator

    It expects it's own syntax instead of the ArcadeDB syntax.

    Yes, exactly, and every driver has its own syntax and semantic to manage params.
    On Python you can us %s with an array or %(name_of_param)s and a map.

    In Java/JDBC there is a single way, with question marks for parameters in the query:

      void preparedStatement() throws Exception {
    
        try (final PreparedStatement st = conn.prepareStatement("SELECT * FROM Beer where name = ?")) {
          st.setString(1, "Stout");
          try (final ResultSet rs = st.executeQuery()) {
            while (rs.next()) {
              assertThat(rs.getString("name")).isEqualTo("Stout");
            }
          }
        }
      }

    And this is the only way for EVERY JDBC driver, not only the PG one.

    BTW, I'll add more tests on python and Js e2e with the help of some AI :)

  11. added a commit that references this issue on Apr 18, 2025
  12. modified the milestones: 25.4.1, 25.5.1 on Apr 22, 2025
  13. robfrank commented on May 13, 2025

    @robfrank
    Collaborator

    #2146 should solve this issue

  14. ExtReMLapin commented on May 13, 2025

    @ExtReMLapin
    ContributorAuthor

    ooray

  15. robfrank commented on May 13, 2025

    @robfrank
    Collaborator

    no, sorry, this one #2169 (that is still in WIP)

  16. ExtReMLapin commented on May 13, 2025

    @ExtReMLapin
    ContributorAuthor

    :'(

  17. added a commit that references this issue on May 13, 2025
  18. robfrank commented on May 13, 2025

    @robfrank
    Collaborator

    merged, now it works with java JDBC with prepared Statemets

  19. added 2 commits that reference this issue on Jun 28, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

No type

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions