def first(self):
cursor = db.get_cursor()
sql = self.query_sql
db.execute_sql(cursor, sql)
rows = cursor.fetchall()
if not rows:
return None
r = rows[0]
ob = self._r2ob(r)
return ob
def all(self):
cursor = db.get_cursor()
sql = self.query_sql
db.execute_sql(cursor, sql)
rows = cursor.fetchall()
obs = []
for item in rows:
ob = self._r2ob(item)
obs.append(ob)
return obs
def __update(self):
cursor = db.get_cursor()
name_value = []
for name, value in zip(self.field_names, self.field_values):
name_value.append(f"{name}={value}")
name_value_sql = ", ".join(name_value)
sql = f"update `{self.table_name}` set {name_value_sql} where id = {self.id}"
db.execute_sql(cursor, sql)
db.db_commit()
def __insert(self):
cursor = db.get_cursor()
field_names_sql = ", ".join(self.field_names)
field_values_sql = ", ".join(self.field_values)
sql = f"insert into `{self.table_name}`({ field_names_sql}) values({field_values_sql})"
db.execute_sql(cursor, sql)
db.db_commit()
sql = f"select id from `{self.table_name}` order by id desc;"
db.execute_sql(cursor, sql)
self.id = cursor.fetchone()[0]
def update(self, **kwargs):
cursor = db.get_cursor()
update_sql = ''
for name, value in kwargs.items():
if f"`{name}`" not in self.field_names + ["`id`"]:
raise exception.ModelFieldNameException(
model_name=self.model_class.__name__,
field_name=name,
)
if isinstance(value, Model):
value = value.id
if isinstance(value, str):
value = value.replace("'", "''")
update_sql += f"`{name}` = '{value}',"
if update_sql:
update_sql = update_sql[:-1]
sql = f"update `{self.table_name}` set {update_sql} where {self.where_sql}"
db.execute_sql(cursor, sql)
db.db_commit()
def try_create_table(cls):
table_name = cls.__name__.lower()
cursor = db.get_cursor()
sql = f"select * from sqlite_master where type='table' AND name='{table_name}';"
db.execute_sql(cursor, sql)
if not cursor.fetchall():
sql = f"drop table if exists `{table_name}`;"
db.execute_sql(cursor, sql)
fields_sql = ""
for name in dir(cls):
var = getattr(cls, name.replace("`", ""))
if isinstance(var, Field):
field = var
field_sql = field.field_sql(name)
fields_sql += ", " + field_sql
sql = f'create table `{table_name}` ("id" integer not null primary key {fields_sql});'
db.execute_sql(cursor, sql)
db.db_commit()
def delete(self):
cursor = db.get_cursor()
sql = f"delete from `{self.table_name}` where {self.where_sql}"
db.execute_sql(cursor, sql)
db.db_commit()