python-sql-query-builder - pypi Package Compare versions

Comparing version 0.1.2 to 0.1.3

PKG-INFO

 Metadata-Version: 2.4
 Name: python-sql-query-builder
-Version: 0.1.2
+Version: 0.1.3
 Summary: A fluent Python SQL query builder with support for joins, where clauses, inserts, updates, and more
@@ -5,0 +5,0 @@ Author-email: Omer Menashe <[e-mail]>

pyproject.toml

@@ -7,3 +7,3 @@ [build-system]
 name = "python-sql-query-builder"
-version = "0.1.2"
+version = "0.1.3"
 description = "A fluent Python SQL query builder with support for joins, where clauses, inserts, updates, and more"
@@ -10,0 +10,0 @@ readme = "README.md"

python_sql_query_builder.egg-info/PKG-INFO

 Metadata-Version: 2.4
 Name: python-sql-query-builder
-Version: 0.1.2
+Version: 0.1.3
 Summary: A fluent Python SQL query builder with support for joins, where clauses, inserts, updates, and more
@@ -5,0 +5,0 @@ Author-email: Omer Menashe <[e-mail]>

sql_query_builder.py

@@ -11,3 +11,4 @@ from typing import List
 
-    # Basic Query Clauses
+    #  Basic Query Clauses  #
+
     def select(self, *columns: str, distinct: bool = False) -> 'SQLQueryBuilder':
@@ -23,6 +24,2 @@ columns_str = ', '.join(columns) if columns else '*'
 
-    def where(self, condition: str) -> 'SQLQueryBuilder':
-        self.__query_segments.append(f'WHERE {condition}')
-        return self
-
     def group_by(self, *columns: str) -> 'SQLQueryBuilder':
@@ -46,7 +43,16 @@ columns_str = ', '.join(columns)
 
-    def in_clause(self, column: str, *values: str) -> 'SQLQueryBuilder':
-        values_str = ', '.join(str(v) for v in values)
-        self.__query_segments.append(f'WHERE {column} IN ({values_str})')
+    def having(self, condition: str) -> 'SQLQueryBuilder':
+        self.__query_segments.append(f'HAVING {condition}')
         return self
 
+    def on_conflict_do_nothing(self) -> 'SQLQueryBuilder':
+        self.__query_segments.append('ON CONFLICT DO NOTHING')
+        return self
+
+    #  Conditions / Filtering  #
+
+    def where(self, condition: str) -> 'SQLQueryBuilder':
+        self.__query_segments.append(f'WHERE {condition}')
+        return self
+
     def and_where(self, condition: str) -> 'SQLQueryBuilder':
@@ -60,11 +66,30 @@ self.__query_segments.append(f'AND {condition}')
 
-    def having(self, condition: str) -> 'SQLQueryBuilder':
-        self.__query_segments.append(f'HAVING {condition}')
+    def not_where(self, condition: str) -> 'SQLQueryBuilder':
+        self.__query_segments.append(f'WHERE NOT ({condition})')
         return self
 
-    def on_conflict_do_nothing(self) -> 'SQLQueryBuilder':
-        self.__query_segments.append('ON CONFLICT DO NOTHING')
+    def and_not(self, condition: str) -> 'SQLQueryBuilder':
+        self.__query_segments.append(f'AND NOT ({condition})')
         return self
 
-    # Joins
+    def or_not(self, condition: str) -> 'SQLQueryBuilder':
+        self.__query_segments.append(f'OR NOT ({condition})')
+        return self
+
+    def in_clause(self, column: str, *values: str) -> 'SQLQueryBuilder':
+        values_str = ', '.join(str(v) for v in values)
+        self.__query_segments.append(f'WHERE {column} IN ({values_str})')
+        return self
+
+    def not_in_clause(self, column: str, *values: str) -> 'SQLQueryBuilder':
+        values_str = ', '.join(f"'{v}'" for v in values)
+        self.__query_segments.append(f'WHERE {column} NOT IN ({values_str})')
+        return self
+
+    def not_exists(self, subquery: str) -> 'SQLQueryBuilder':
+        self.__query_segments.append(f'WHERE NOT EXISTS ({subquery})')
+        return self
+
+    #  Joins  #
+
     def left_join(self, table: str, on_condition: str) -> 'SQLQueryBuilder':
@@ -86,3 +111,4 @@ self.__query_segments.append(f'LEFT JOIN {table} ON {on_condition}')
 
-    # Data Modification
+    #  Data Manipulation (DML)  #
+
     def insert_into(self, table: str, **columns: str) -> 'SQLQueryBuilder':
@@ -94,2 +120,7 @@ column_names = ', '.join(columns.keys())
 
+    def values(self, *rows: List[str]) -> 'SQLQueryBuilder':
+        rows_str = ', '.join(f"({', '.join(row)})" for row in rows)
+        self.__query_segments.append(f'VALUES {rows_str}')
+        return self
+
     def update(self, table: str, **columns: str) -> 'SQLQueryBuilder':
@@ -104,8 +135,4 @@ set_clause = ', '.join(f"{k} = '{v}'" for k, v in columns.items())
 
-    def values(self, *rows: List[str]) -> 'SQLQueryBuilder':
-        rows_str = ', '.join(f"({', '.join(row)})" for row in rows)
-        self.__query_segments.append(f'VALUES {rows_str}')
-        return self
+    #  Transactions  #
 
-    # Transactions
     def commit(self) -> 'SQLQueryBuilder':
@@ -123,7 +150,3 @@ self.__query_segments.append('COMMIT')
 
-    # DDL Statements (Create, Alter, Drop)
-    def drop(self, object_type: str, object_name: str, if_exists: bool = False) -> 'SQLQueryBuilder':
-        exists_clause = 'IF EXISTS' if if_exists else None
-        self.__query_segments.append(f'DROP {exists_clause + ' ' if exists_clause else ''}{object_type} {object_name}')
-        return self
+    #  DDL (Data Definition)  #
 
@@ -135,7 +158,11 @@ def create_table(self, table: str, **columns: str) -> 'SQLQueryBuilder':
 
-    def create_index(self, index_name: str, table: str, *columns: str) -> 'SQLQueryBuilder':
-        columns_str = ', '.join(columns)
-        self.__query_segments.append(f'CREATE INDEX {index_name} ON {table} ({columns_str})')
+    def drop(self, object_type: str, object_name: str, if_exists: bool = False) -> 'SQLQueryBuilder':
+        exists_clause = 'IF EXISTS' if if_exists else ''
+        self.__query_segments.append(f'DROP {exists_clause + " " if exists_clause else ""}{object_type} {object_name}')
         return self
 
+    def alter_table(self, table: str) -> 'SQLQueryBuilder':
+        self.__query_segments.append(f'ALTER TABLE {table}')
+        return self
+
     def add_column(self, column_name: str, column_definition: str) -> 'SQLQueryBuilder':
@@ -149,6 +176,2 @@ self.__query_segments.append(f'ADD COLUMN {column_name} {column_definition}')
 
-    def alter_table(self, table: str) -> 'SQLQueryBuilder':
-        self.__query_segments.append(f'ALTER TABLE {table}')
-        return self
-
     def rename_table(self, old_name: str, new_name: str) -> 'SQLQueryBuilder':
@@ -162,3 +185,9 @@ self.__query_segments.append(f'RENAME TABLE {old_name} TO {new_name}')
 
-    # Permissions
+    def create_index(self, index_name: str, table: str, *columns: str) -> 'SQLQueryBuilder':
+        columns_str = ', '.join(columns)
+        self.__query_segments.append(f'CREATE INDEX {index_name} ON {table} ({columns_str})')
+        return self
+
+    #  Permissions  #
+
     def grant(self, permission: str, table: str, user: str) -> 'SQLQueryBuilder':
@@ -172,18 +201,21 @@ self.__query_segments.append(f'GRANT {permission} ON {table} TO {user}')
 
-    # Set Operations
+    #  Set Operations  #
+
     def union(self, query: str, union_all: bool = False) -> 'SQLQueryBuilder':
-        all_clause = 'ALL' if union_all else None
-        self.__query_segments.append(f'UNION {all_clause + ' ' if all_clause else ''}{query}')
+        all_clause = 'ALL' if union_all else ''
+        self.__query_segments.append(f'UNION {all_clause + " " if all_clause else ""}{query}')
         return self
 
     def intersect(self, query: str, intersect_all: bool = False) -> 'SQLQueryBuilder':
-        all_clause = 'ALL' if intersect_all else None
-        self.__query_segments.append(f'INTERSECT {all_clause + ' ' if all_clause else ''}{query}')
+        all_clause = 'ALL' if intersect_all else ''
+        self.__query_segments.append(f'INTERSECT {all_clause + " " if all_clause else ""}{query}')
         return self
 
     def except_clause(self, query: str, except_all: bool = False) -> 'SQLQueryBuilder':
-        all_clause = 'ALL' if except_all else None
-        self.__query_segments.append(f'EXCEPT {all_clause + ' ' if all_clause else ''}{query}')
+        all_clause = 'ALL' if except_all else ''
+        self.__query_segments.append(f'EXCEPT {all_clause + " " if all_clause else ""}{query}')
         return self
 
+    #  Utility  #
+
     def __str__(self) -> str:
@@ -190,0 +222,0 @@ return self.build()

tests/test_sql_query_builder.py

 import unittest
+
 from sql_query_builder import SQLQueryBuilder
@@ -117,3 +118,24 @@
 
+    def test_not_where_query(self):
+        query = SQLQueryBuilder().select('*').from_table('users').not_where("age < 18").build()
+        self.assertEqual(query, 'SELECT * FROM users WHERE NOT (age < 18)')
+
+    def test_not_in_clause_query(self):
+        query = SQLQueryBuilder().select('*').from_table('users').not_in_clause('age', 15, 16).build()
+        self.assertEqual(query, "SELECT * FROM users WHERE age NOT IN ('15', '16')")
+
+    def test_not_exists_query(self):
+        query = SQLQueryBuilder().select('*').from_table('users').not_exists('SELECT 1 FROM banned WHERE banned.id = users.id').build()
+        self.assertEqual(query, 'SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM banned WHERE banned.id = users.id)')
+
+    def test_and_not_query(self):
+        query = SQLQueryBuilder().select('*').from_table('users').where("age > 18").and_not("verified = false").build()
+        self.assertEqual(query, 'SELECT * FROM users WHERE age > 18 AND NOT (verified = false)')
+
+    def test_or_not_query(self):
+        query = SQLQueryBuilder().select('*').from_table('users').where("age > 18").or_not("verified = false").build()
+        self.assertEqual(query, 'SELECT * FROM users WHERE age > 18 OR NOT (verified = false)')
+
+
 if __name__ == '__main__':
     unittest.main()

Alert delta unavailable

Currently unable to show alert delta for PyPI packages.

Improved metrics

Total package byte prevSize

23006

10.2%

Lines of code

260

14.54%