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)
