def update_jobs_type(self, _id, name):
with Connect(self.dbpool) as conn:
cur = conn.cursor(DICT_CUR)
sql = 'update job_types set name="{}" where id={}'.format(
name, _id)
cur.execute(sql)
conn.commit()
def delete_gmtype(self, group, _id):
with Connect(self.dbpool) as conn:
cur = conn.cursor(DICT_CUR)
sql = 'delete from gmtype where id = {} and groups = "{}"'.format(
_id, group)
cur.execute(sql)
conn.commit()
def create_gmtype(self, group, name):
with Connect(self.dbpool) as conn:
cur = conn.cursor(DICT_CUR)
sql = 'insert into gmtype (groups, name) values("{}", "{}")'.format(
group, name)
cur.execute(sql)
conn.commit()
def update_gmtype(self, _id, group, name):
with Connect(self.dbpool) as conn:
cur = conn.cursor(DICT_CUR)
sql = 'update gmtype set name="{}" where id={} and groups="{}"'.format(
name, _id, group)
cur.execute(sql)
conn.commit()
def delete_job(self, _id):
'''
delete special message
'''
with Connect(self.dbpool) as conn:
cur = conn.cursor(DICT_CUR)
sql = 'delete from jobs where id = "{}"'.format(_id)
cur.execute(sql)
conn.commit()
def update(self, table, filters, **kwargs):
'''
update records's value (**kwargs) where matched filters
'''
with Connect(self.dbpool) as conn:
cur = conn.cursor(DICT_CUR)
filters = self.combine_kwargs(filters)
modify_str = self.combine_kwargs(', ', **kwargs)
sql = 'update {} set {} where {}'.format(table, modify_str, filters)
cur.execute(sql)
conn.commit()
def update_job(self, _id, **kwargs):
'''
modify job info
'''
with Connect(self.dbpool) as conn:
cur = conn.cursor(DICT_CUR)
modify_str = ', '.join([
"{} = '{}'".format(key, value)
for key, value in kwargs.iteritems()
])
sql = 'update jobs set {} where id = "{}"'.format(modify_str, _id)
cur.execute(sql)
conn.commit()
def query2(self, table, **kwargs):
'''
query special records in table
first commit connection, refresh records
'''
with Connect(self.dbpool) as conn:
conn.commit()
cur = conn.cursor(DICT_CUR)
query_str = self.combine_kwargs(**kwargs)
sql = 'select * from {} where {}'.format(table, query_str)
cur.execute(sql)
return cur.fetchall()
def create_ap_msg_tuple(self, msg_id, groups, ap_groups):
'''
create new msg ap tuple
'''
with Connect(self.dbpool) as conn:
cur = conn.cursor(DICT_CUR)
for ap_group in ap_groups:
if ap_group:
sql = '''insert into ap_msg (ap_group, msg_id, _location)
values("{}", "{}", "{}")'''.format(ap_group, msg_id,
groups)
cur.execute(sql)
conn.commit()
def create_job(self, **kwargs):
'''
create new job reocrds
{id:'', name:'', type:'', mask:'', address_id:'', remand:'',
duty:'', department_id:'', ctime:'', expire:''}
'''
with Connect(self.dbpool) as conn:
cur = conn.cursor(DICT_CUR)
key_str = ', '.join(kwargs.keys())
value_str = ', '.join(
["'{}'".format(item) for item in kwargs.values()])
sql = 'insert into jobs ({}) values({})'.format(key_str, value_str)
cur.execute(sql)
conn.commit()
def delete(self, table, filters):
'''
delete records where matched filters
'''
with Connect(self.dbpool) as conn:
if not filters:
return
cur = conn.cursor(DICT_CUR)
filters = self.combine_kwargs(filters)
sql = 'delete from {} where {}'.format(table, filters)
cur.execute(sql)
conn.commit()
def insert(self, table, **kwargs):
'''
insert new records
'''
with Connect(self.dbpool) as conn:
cur = conn.cursor(DICT_CUR)
keys,values = kwargs.keys(),kwargs.values()
keys = ['"{}"'.format(key) for key in keys]
values = ['"{}"'.format(value) for value in values]
keys, values = ', '.join(keys), ', '.join(values)
sql = 'insert into {} ({}) values({})'.format(table, keys, values)
cur.execute(sql)
conn.commit()
def create_message(self, **kwargs):
'''
create new message
each message distinguished by id [md5(groups, title, subtitle, content)]
'''
with Connect(self.dbpool) as conn:
cur = conn.cursor(DICT_CUR)
key_str = ', '.join(kwargs.keys())
value_str = ', '.join(
["'{}'".format(item) for item in kwargs.values()])
sql = 'insert into message ({}) values({})'.format(
key_str, value_str)
cur.execute(sql)
conn.commit()
def create_jobs_type(self, name):
with Connect(self.dbpool) as conn:
cur = conn.cursor(DICT_CUR)
sql = 'insert into job_types (name) values ("{}")'.format(name)
cur.execute(sql)
conn.commit()