35834

Maximum number of bind parameters in SQLAlchemy

Question:

I have a question about the maximum number of bind parameters that can be passed to a query I wish to execute using SQLAlchemy.

My query is a set of SELECT statements all grouped together via UNION (about 100+) and each select statement will be passed a different value. The reason why I need to do this via a UNION is that I am using the trigram matching operator % in Postgres (<a href="https://www.postgresql.org/docs/9.3/static/pgtrgm.html" rel="nofollow">https://www.postgresql.org/docs/9.3/static/pgtrgm.html</a>) so I can't combine the statements into one using IN.

SELECT col1, col2, col3 FROM TABLE where col3=:p1 AND col2 % :q1 UNION SELECT col1, col2, col3 FROM TABLE where col3=:p2 AND col2 % :q2 UNION SELECT col1, col2, col3 FROM TABLE where col3=:p3 AND col2 % :q3 UNION ...

etc. Is there a limit that would cause it to fail ? where the params are (p1,q1, p2,q2, p3, q3) etc.

Answer1:

I was able to achieve my goal by using a VALUES list in Postgresql:

SELECT col1, col2, col3 FROM TABLE a, VALUES(('x1','x2','x3'), ('y1','y2','y3'), ('z1','z2','z3')) AS t(col1, col2, col3) WHERE a.col1=t.col1 AND lower(a.col2) % t.name

Recommend

  • Using countVectorizer to compute word occurrence for my own vocabulary in python
  • PostgreSQL Full Text Search and Trigram Confusion
  • Trigram For Heaven entity not working for Windows Phone
  • Django Migrations fail during django initialization
  • PostgreSQL 9.x - pg_read_binary_file & inserting files into bytea
  • Regex for Specific Tag
  • .NET video play library which allows to change the playback rate?
  • How to skip require in ruby?
  • How to 'create temp table as select' in Slick?
  • Trying to get the char code of ENTER key
  • Using Sax parsing to edit and write XML in VB6
  • RxJava debounce by arbitrary value
  • How VBA declared Volatility works
  • Not able to aggregate on nested fields in elasticsearch
  • MySQL Order by column = x, column asc?
  • Debug.DrawLine not showing in the GameView
  • Parsing a CSV string while ignoring commas inside the individual columns
  • debug library loaded with ctypes using gdb
  • Swift: Switch statement fallthrough behavior
  • MVC3 Razor - ListBox pre-select not working
  • Yii2: Config params vs. const/define
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • how to adjust image in a panel in Java swing?
  • Spring Data JPA custom method causing PropertyReferenceException
  • req.body is undefined - nodejs
  • RectangularRangeIndicator format like triangular using dojo
  • Cross-Platform Protobuf Serialization
  • sending/ receiving email in Java
  • How to model a transition system with SPIN
  • Weird JavaScript statement, what does it mean?
  • Do I've to free mysql result after storing it?
  • How to delete a row from a dynamic generate table using jquery?
  • Python: how to group similar lists together in a list of lists?
  • Revoking OAuth Access Token Results in 404 Not Found
  • Proper way to use connect-multiparty with express.js?
  • What are the advantages and disadvantages of reading an entire file into a single String as opposed
  • Turn off referential integrity in Derby? is it possible?
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • Checking variable from a different class in C#
  • Django query for large number of relationships