def querystrings(self) -> t.Sequence[QueryString]: if self._drop_table is not None: return [self._drop_table.querystring] query = f"ALTER TABLE {self.table._meta.tablename}" alterations = [ i.querystring for i in itertools.chain( self._add, self._rename_columns, self._rename_table, self._drop, self._drop_default, self._set_column_type, self._set_unique, self._set_null, self._set_length, self._set_default, self._set_digits, ) ] if self.engine_type == "sqlite": # Can only perform one alter statement at a time. query += " {}" return [QueryString(query, i) for i in alterations] # Postgres can perform them all at once: query += ",".join([" {}" for i in alterations]) return [QueryString(query, *alterations)]
def querystring(self) -> QueryString: if self.boolean: return QueryString( f"ALTER COLUMN {self.column_name} DROP NOT NULL" ) else: return QueryString(f"ALTER COLUMN {self.column_name} SET NOT NULL")
def get_querystring( self, column_name: str, operator: str, value: t.Union[int, float, Integer], reverse=False, ) -> QueryString: if isinstance(value, Integer): column: Integer = value if len(column._meta.call_chain) > 0: raise ValueError( "Adding values across joins isn't currently supported." ) column_name = column._meta.name if reverse: return QueryString(f"{column_name} {operator} {column_name}") else: return QueryString(f"{column_name} {operator} {column_name}") elif isinstance(value, (int, float)): if reverse: return QueryString(f"{{}} {operator} {column_name}", value) else: return QueryString(f"{column_name} {operator} {{}}", value) else: raise ValueError( "Only integers, floats, and other Integer columns can be " "added." )
def default_querystrings(self) -> t.Sequence[QueryString]: self.validate() columns_str = ", ".join( [ f"{col._meta.name} = {{}}" for col, _ in self.values_delegate._values.items() ] ) query = f"UPDATE {self.table._meta.tablename} SET " + columns_str querystring = QueryString( query, *self.values_delegate._values.values() ) if self.where_delegate._where: where_querystring = QueryString( "{} WHERE {}", querystring, self.where_delegate._where.querystring, ) return [where_querystring] else: return [querystring]
def default_querystrings(self) -> t.Sequence[QueryString]: query = f"DELETE FROM {self.table._meta.tablename}" if self.where_delegate._where: query += " WHERE {}" return [QueryString(query, self.where_delegate._where.querystring)] else: return [QueryString(query)]
def querystring(self) -> QueryString: if self.digits is not None: precision = self.digits[0] scale = self.digits[1] return QueryString(f"ALTER COLUMN {self.column_name} TYPE " f"{self.column_type}({precision}, {scale})") else: return QueryString( f"ALTER COLUMN {self.column_name} TYPE {self.column_type}", )
def querystring(self) -> QueryString: if self.boolean: return QueryString(f"ADD UNIQUE ({self.column_name})") else: if isinstance(self.column, str): raise ValueError( "Removing a unique constraint requires a Column instance " "to be passed as the column arg instead of a string.") tablename = self.column._meta.table._meta.tablename column_name = self.column_name key = f"{tablename}_{column_name}_key" return QueryString(f'DROP CONSTRAINT "{key}"')
def __init__(self, sql: str, *args: t.Any) -> None: """ Execute raw SQL queries in your where clause. Use with caution! await Band.where( WhereRaw("name = 'Pythonistas'") ) Or passing in parameters: await Band.where( WhereRaw("name = {}", 'Pythonistas') ) """ self.querystring = QueryString(sql, *args)
def querystring(self) -> QueryString: """ Used when creating tables. """ query = f'"{self._meta.name}" {self.column_type}' if self._meta.primary: query += " PRIMARY" if self._meta.key: query += " KEY" if self._meta.unique: query += " UNIQUE" if not self._meta.null: query += " NOT NULL" foreign_key_meta: t.Optional[ForeignKeyMeta] = getattr( self, "_foreign_key_meta", None) if foreign_key_meta: tablename = foreign_key_meta.resolved_references._meta.tablename on_delete = foreign_key_meta.on_delete.value on_update = foreign_key_meta.on_update.value query += (f" REFERENCES {tablename} (id)" f" ON DELETE {on_delete}" f" ON UPDATE {on_update}") if not self._meta.primary: default = self.get_default_value() sql_value = self.get_sql_value(value=default) # Escape the value if it contains a pair of curly braces, otherwise # an empty value will appear in the compiled querystring. sql_value = (sql_value.replace("{}", "{{}}") if isinstance( sql_value, str) else sql_value) query += f" DEFAULT {sql_value}" return QueryString(query)
def postgres_querystrings(self) -> t.Sequence[QueryString]: return [ QueryString( "SELECT EXISTS(SELECT * FROM information_schema.tables WHERE " f"table_name = '{self.table._meta.tablename}')" ) ]
def sqlite_querystrings(self) -> t.Sequence[QueryString]: return [ QueryString( "SELECT EXISTS(SELECT * FROM sqlite_master WHERE " f"name = '{self.table._meta.tablename}') AS 'exists'" ) ]
def querystrings(self) -> t.Sequence[QueryString]: select = Select(table=self.table) select.where_delegate._where = self.where_delegate._where return [ QueryString('SELECT EXISTS({}) AS "exists"', select.querystrings[0]) ]
def default_querystrings(self) -> t.Sequence[QueryString]: column_names = self.column_names index_name = self.table._get_index_name(column_names) query = "DROP INDEX" if self.if_exists: query += " IF EXISTS" return [QueryString(f"{query} {index_name}")]
def querystring(self) -> QueryString: """ Used when inserting rows. """ args_dict = { col._meta.name: self[col._meta.name] for col in self._meta.columns } def is_unquoted(arg): return type(arg) == Unquoted # Strip out any args which are unquoted. # TODO Not the cleanest place to have it (would rather have it handled # in the QueryString bundle logic) - might need refactoring. filtered_args = [i for i in args_dict.values() if not is_unquoted(i)] # If unquoted, dump it straight into the query. query = ",".join( [ args_dict[column._meta.name].value if is_unquoted(args_dict[column._meta.name]) else "{}" for column in self._meta.columns ] ) return QueryString(f"({query})", *filtered_args)
def querystrings(self) -> t.Sequence[QueryString]: prefix = "CREATE TABLE" if self.if_not_exists: prefix += " IF NOT EXISTS" if self.only_default_columns: columns = self.table._meta.non_default_columns else: columns = self.table._meta.columns base = f"{prefix} {self.table._meta.tablename}" columns_sql = ", ".join(["{}" for i in columns]) query = f"{base} ({columns_sql})" create_table = QueryString(query, *[i.querystring for i in columns]) create_indexes: t.List[QueryString] = [] for column in columns: if column._meta.index is True: create_indexes.extend( CreateIndex( table=self.table, columns=[column], method=column._meta.index_method, if_not_exists=self.if_not_exists, ).querystrings) return [create_table] + create_indexes
def __init__( self, table: t.Type[Table], querystring: QueryString = QueryString(""), **kwargs, ): super().__init__(table, **kwargs) self.querystring = querystring
def postgres_querystrings(self) -> t.Sequence[QueryString]: return [ QueryString( "SELECT indexname AS name FROM pg_indexes " "WHERE tablename = {}", self.table._meta.tablename, ) ]
def values_querystring(self) -> QueryString: values = self.values if isinstance(values, Undefined): raise ValueError("values is undefined") template = ", ".join("{}" for _ in values) return QueryString(template, *values)
def querystrings(self) -> t.Sequence[QueryString]: select = Select(self.table) select.where_delegate._where = self.where_delegate._where return [ QueryString( 'SELECT COUNT(*) AS "count" FROM ({}) AS "subquery"', select.querystrings[0], ) ]
def querystring(self) -> QueryString: query = ( f"ADD CONSTRAINT {self.constraint_name} FOREIGN KEY " f"({self.foreign_key_column_name}) REFERENCES " f"{self.referenced_table_name} ({self.referenced_column_name})") if self.on_delete: query += f" ON DELETE {self.on_delete.value}" if self.on_update: query += f" ON UPDATE {self.on_update.value}" return QueryString(query)
def postgres_querystrings(self) -> t.Sequence[QueryString]: column_names = self.column_names index_name = self.table._get_index_name(column_names) tablename = self.table._meta.tablename method_name = self.method.value column_names_str = ", ".join(column_names) return [ QueryString(f"{self.prefix} {index_name} ON {tablename} USING " f"{method_name} ({column_names_str})") ]
def default_querystrings(self) -> t.Sequence[QueryString]: columns_str = ", ".join( f'"{col._meta.db_column_name}" = {{}}' for col, _ in self.values_delegate._values.items()) query = f"UPDATE {self.table._meta.tablename} SET " + columns_str querystring = QueryString(query, *self.values_delegate.get_sql_values()) if not self.where_delegate._where: return [querystring] where_querystring = QueryString( "{} WHERE {}", querystring, self.where_delegate._where.querystring, ) return [where_querystring]
def querystring(self) -> QueryString: if self.new_column._meta._table is None: self.new_column._meta._table = self.old_column._meta.table column_name = self.old_column._meta.name query = ( f"ALTER COLUMN {column_name} TYPE {self.new_column.column_type}") if self.using_expression is not None: query += f" USING {self.using_expression}" return QueryString(query)
def raw(cls, sql: str, *args: t.Any) -> Raw: """ Execute raw SQL queries on the underlying engine - use with caution! await Band.raw('select * from band').run() Or passing in parameters: await Band.raw("select * from band where name = {}", 'Pythonistas') """ return Raw(table=cls, querystring=QueryString(sql, *args))
class TestQueryString(TestCase): qs = QueryString("SELECT id FROM band {}", QueryString("WHERE name = {}", "Pythonistas")) def test_compile_string(self): compiled_string, args = self.qs.compile_string() self.assertEqual(compiled_string, "SELECT id FROM band WHERE name = $1") self.assertEqual(args, ["Pythonistas"]) def test_string(self): string = self.qs.__str__() self.assertEqual(string, "SELECT id FROM band WHERE name = 'Pythonistas'") def test_querystring_with_no_args(self): qs = QueryString("SELECT name FROM band") self.assertEqual(qs.compile_string(), ("SELECT name FROM band", []))
def sqlite_querystrings(self) -> t.Sequence[QueryString]: base = f"INSERT INTO {self.table._meta.tablename}" columns = ",".join([i._meta.name for i in self.table._meta.columns]) values = ",".join(["{}" for i in self.add_delegate._add]) query = f"{base} ({columns}) VALUES {values}" return [ QueryString( query, *[i.querystring for i in self.add_delegate._add], query_type="insert", ) ]
def querystring(self) -> QueryString: query = "DROP TABLE" if self.if_exists: query += " IF EXISTS" query += f" {self.tablename}" if self.cascade: query += " CASCADE" return QueryString(query)
async def run_querystring(self, querystring: QueryString, in_pool: bool = True): query, query_args = querystring.compile_string( engine_type=self.engine_type) # If running inside a transaction: connection = self.transaction_connection.get() if connection: return await connection.fetch(query, *query_args) elif in_pool and self.pool: return await self._run_in_pool(query, query_args) else: return await self._run_in_new_connection(query, query_args)
def sqlite_querystrings(self) -> t.Sequence[QueryString]: column_names = self.column_names index_name = self.table._get_index_name(column_names) tablename = self.table._meta.tablename method_name = self.method.value if method_name != "btree": raise ValueError("SQLite only support btree indexes.") column_names_str = ", ".join(column_names) return [ QueryString(f"{self.prefix} {index_name} ON {tablename} " f"({column_names_str})") ]
def querystring(self) -> QueryString: args: t.List[t.Any] = [] if self.value != UNDEFINED: args.append(self.value) if self.values != UNDEFINED: args.append(self.values_querystring) template = self.operator.template.format( name=self.column.get_where_string( engine_type=self.column._meta.engine_type), value="{}", values="{}", ) return QueryString(template, *args)