Use parameter for limit and offset

cedk · cedk · commit b98c950bab8d · 2024-05-27T18:17:13.000+02:00
Closes #92
diff --git a/CHANGELOG b/CHANGELOG
@@ -1,3 +1,5 @@
+* Use parameter for limit and offset
+
 Version 1.5.0 - 2024-05-13
 * Skip alias on INSERT without ON CONFLICT or RETURNING
 * Add MERGE
diff --git a/sql/__init__.py b/sql/__init__.py
@@ -380,13 +380,14 @@ def offset(self, value):
 
     @property
     def _limit_offset_str(self):
+        param = Flavor.get().param
         if Flavor.get().limitstyle == 'limit':
             offset = ''
             if self.offset:
-                offset = ' OFFSET %s' % self.offset
+                offset = ' OFFSET %s' % param
             limit = ''
             if self.limit is not None:
-                limit = ' LIMIT %s' % self.limit
+                limit = ' LIMIT %s' % param
             elif self.offset:
                 max_limit = Flavor.get().max_limit
                 if max_limit:
@@ -395,12 +396,27 @@ def _limit_offset_str(self):
         else:
             offset = ''
             if self.offset:
-                offset = ' OFFSET (%s) ROWS' % self.offset
+                offset = ' OFFSET (%s) ROWS' % param
             fetch = ''
             if self.limit is not None:
-                fetch = ' FETCH FIRST (%s) ROWS ONLY' % self.limit
+                fetch = ' FETCH FIRST (%s) ROWS ONLY' % param
             return offset + fetch
 
+    @property
+    def _limit_offset_params(self):
+        p = []
+        if Flavor.get().limitstyle == 'limit':
+            if self.limit is not None:
+                p.append(self.limit)
+            if self.offset:
+                p.append(self.offset)
+        else:
+            if self.offset:
+                p.append(self.offset)
+            if self.limit is not None:
+                p.append(self.limit)
+        return tuple(p)
+
     def as_(self, output_name):
         return As(self, output_name)
 
@@ -663,6 +679,7 @@ def params(self):
                 p.extend(self.having.params)
             for window in self.windows:
                 p.extend(window.params)
+            p.extend(self._limit_offset_params)
         return tuple(p)
 
 
diff --git a/sql/tests/test_select.py b/sql/tests/test_select.py
@@ -253,13 +253,13 @@ def test_select_limit_offset(self):
             Flavor.set(Flavor(limitstyle='limit'))
             query = self.table.select(limit=50, offset=10)
             self.assertEqual(str(query),
-                'SELECT * FROM "t" AS "a" LIMIT 50 OFFSET 10')
-            self.assertEqual(tuple(query.params), ())
+                'SELECT * FROM "t" AS "a" LIMIT %s OFFSET %s')
+            self.assertEqual(tuple(query.params), (50, 10))
 
             query.limit = None
             self.assertEqual(str(query),
-                'SELECT * FROM "t" AS "a" OFFSET 10')
-            self.assertEqual(tuple(query.params), ())
+                'SELECT * FROM "t" AS "a" OFFSET %s')
+            self.assertEqual(tuple(query.params), (10,))
 
             query.offset = 0
             self.assertEqual(str(query),
@@ -280,8 +280,8 @@ def test_select_limit_offset(self):
 
             query.offset = 10
             self.assertEqual(str(query),
-                'SELECT * FROM "t" AS "a" LIMIT -1 OFFSET 10')
-            self.assertEqual(tuple(query.params), ())
+                'SELECT * FROM "t" AS "a" LIMIT -1 OFFSET %s')
+            self.assertEqual(tuple(query.params), (10,))
         finally:
             Flavor.set(Flavor())
 
@@ -291,13 +291,13 @@ def test_select_offset_fetch(self):
             query = self.table.select(limit=50, offset=10)
             self.assertEqual(str(query),
                 'SELECT * FROM "t" AS "a" '
-                'OFFSET (10) ROWS FETCH FIRST (50) ROWS ONLY')
-            self.assertEqual(tuple(query.params), ())
+                'OFFSET (%s) ROWS FETCH FIRST (%s) ROWS ONLY')
+            self.assertEqual(tuple(query.params), (10, 50))
 
             query.limit = None
             self.assertEqual(str(query),
-                'SELECT * FROM "t" AS "a" OFFSET (10) ROWS')
-            self.assertEqual(tuple(query.params), ())
+                'SELECT * FROM "t" AS "a" OFFSET (%s) ROWS')
+            self.assertEqual(tuple(query.params), (10,))
 
             query.offset = 0
             self.assertEqual(str(query),

Original file line number	Diff line number	Diff line change
`@@ -1,3 +1,5 @@`
	`1`	`+* Use parameter for limit and offset`
	`2`	`+`
`1`	`3`	`Version 1.5.0 - 2024-05-13`
`2`	`4`	`* Skip alias on INSERT without ON CONFLICT or RETURNING`
`3`	`5`	`* Add MERGE`