From: arne@vajhoej.dk   
      
   On 7/9/2025 8:04 PM, Arne Vajhøj wrote:   
   > I would write your code a little simpler:   
   >   
   > $ type dyn.py   
   > import sqlite3   
   >   
   > def esc(s):   
   >      return "''".join(s.split("'"))   
   >   
   > def q(s):   
   >      return f"'{s}'"   
   >   
   > def dump_range(con, vals):   
   >      jvals = ",".join(q(esc(val)) for val in vals)   
   >      c = con.cursor()   
   >      c.execute(f"SELECT f1,f2 FROM t1 WHERE f2 IN ({jvals})")   
   >      for row in c.fetchall():   
   >          print('%d %s' % (row[0], row[1]))   
   >   
   > with sqlite3.connect('test.db') as con:   
   >      dump_range(con, ['A', 'CCC', 'EEEEE'])   
   >      dump_range(con, ['BB', 'DDDD'])   
   > $ python dyn.py   
   > 1 A   
   > 3 CCC   
   > 5 EEEEE   
   > 2 BB   
   > 4 DDDD   
   >   
   > But it is both simpler and safer to use dynamic SQL   
   > with just dynamic non-data:   
   >   
   > $ type sta.py   
   > import sqlite3   
   >   
   > def dump_range(con, vals):   
   >      jparm = ",".join('?' for val in vals)   
   >      c = con.cursor()   
   >      c.execute(f"SELECT f1,f2 FROM t1 WHERE f2 IN ({jparm})", vals)   
   >      for row in c.fetchall():   
   >          print('%d %s' % (row[0], row[1]))   
   >   
   > with sqlite3.connect('test.db') as con:   
   >      dump_range(con, ['A', 'CCC', 'EEEEE'])   
   >      dump_range(con, ['BB', 'DDDD'])   
   > $ python sta.py   
   > 1 A   
   > 3 CCC   
   > 5 EEEEE   
   > 2 BB   
   > 4 DDDD   
      
   But this is fun:   
      
   $ define/nolog jython_libs "/javalib/zxjdbc.jar:/javalib/h2-2_2_220.jar"   
   $ type dyn2.py   
   from com.ziclix.python.sql import zxJDBC   
      
   def esc(s):   
        return "''".join(s.split("'"))   
      
   def q(s):   
        return "'" + s + "'"   
      
   def dump_range(con, vals):   
        jvals = ",".join(q(esc(val)) for val in vals)   
        c = con.cursor()   
        c.execute("SELECT f1,f2 FROM t1 WHERE f2 IN (" + jvals + ")")   
        for row in c.fetchall():   
            print('%d %s' % (row[0], row[1]))   
      
   with zxJDBC.connect('jdbc:h2:./test;FILE_LOCK=FS;ALLOW_LITERALS=NONE',   
   'sa', 'hemmeligt', 'org.h2.Driver') as con:   
        dump_range(con, ['A', 'CCC', 'EEEEE'])   
        dump_range(con, ['BB', 'DDDD'])   
   $ jython dyn2.py   
   Traceback (most recent call last):   
      File "DYN2.PY", line 17, in    
        dump_range(con, ['A', 'CCC', 'EEEEE'])   
      File "DYN2.PY", line 12, in dump_range   
        c.execute("SELECT f1,f2 FROM t1 WHERE f2 IN (" + jvals + ")")   
   zxJDBC.Error: Literals of this kind are not allowed; SQL statement:   
   SELECT f1,f2 FROM t1 WHERE f2 IN ('A','CCC','EEEEE') [90116-220]   
   [SQLCode: 90116], [SQLState: 90116]   
   $ type sta2.py   
   from com.ziclix.python.sql import zxJDBC   
      
   def dump_range(con, vals):   
        jparm = ",".join('?' for val in vals)   
        c = con.cursor()   
        c.execute("SELECT f1,f2 FROM t1 WHERE f2 IN (" + jparm +")", vals)   
        for row in c.fetchall():   
            print('%d %s' % (row[0], row[1]))   
      
   with zxJDBC.connect('jdbc:h2:./test;FILE_LOCK=FS;ALLOW_LITERALS=NONE',   
   'sa', 'hemmeligt', 'org.h2.Driver') as con:   
        dump_range(con, ['A', 'CCC', 'EEEEE'])   
        dump_range(con, ['BB', 'DDDD'])   
   $ jython sta2.py   
   1 A   
   3 CCC   
   5 EEEEE   
   2 BB   
   4 DDDD   
      
   :-)   
      
   Arne   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)