def change_phone_password(phone, password, code):
if db.exist('select id from password_code where phone = %s and code = %s' % (phone, code)):
salt = util.generate_salt()
db.execute('update user set password = "******", salt = "%s" where phone = "%s"'
% (util.bcrypt_password(password + salt), salt, phone))
return True
return False
def run(self):
gags = self.ng.get_latest_hot()[::-1]
for gag in gags:
match = db.select('SELECT * FROM gags WHERE gag="{}"'.format(gag.id))
if not match:
# New gag!
db.execute('INSERT INTO gags(gag, kind) VALUES("{}", "{}")'.format(gag.id, gag.kind))
if gag.kind == 'image':
self.engine.telegram.sendPhoto(
chat_id=settings.CHAT_ID,
photo=gag.img['src'],
caption=gag.name
)
elif gag.kind == 'gif':
import urllib2
from random import random
data = urllib2.urlopen(gag.animated['data-mp4']).read()
tmp = open('/tmp/{}.mp4'.format(str(int(random() * 1000000))), 'w+b')
tmp.write(data)
tmp.seek(0)
self.engine.telegram.sendVideo(
chat_id=settings.CHAT_ID,
video=tmp,
caption=gag.name
)
elif gag.kind == 'article':
self.engine.telegram.sendPhoto(
chat_id=settings.CHAT_ID,
photo=gag.img['href'],
caption=u'Full version: {}'.format(gag.name)
)
# print 'NineGagPoster.run()'
self.start_later(3)
def get_orders(offset, limit):
try:
result = db.execute(
"select customers.name, customers.address, customers.cid, " \
"customerorders1.oid, customerorders1.date, customerorders1.status" \
" from (select * from customerorders left outer join staff on customerorders.eid = staff.eid) " \
"as customerorders1 join customers on customerorders1.cid = customers.cid " \
"ORDER BY customerorders1.oid DESC LIMIT {} OFFSET {}".format(int(limit), int(offset)))
ret = []
for row in result:
r = dict(row.items())
sub_result = db.execute("""
SELECT c.quantity, s.name, s.cost FROM customersuborders AS c, snacks AS s
WHERE c.oid = {} AND c.sid = s.sid
""".format(int(row['oid'])))
r['suborders'] = []
for sub_row in sub_result:
r['suborders'].append(dict(sub_row.items()))
ret.append(r)
return jsonify(ret)
except ValueError as e:
return jsonify(msg=e.args), 400
else:
abort(404)
def submit_score(self, username, good):
results = db.select(
'SELECT * '
'FROM carma WHERE username="******"'.format(
username
)
)
if not results:
db.execute(
'INSERT INTO carma(username, commends, reports) '
'VALUES("{}", 0, 0)'.format(username)
)
commends = 0
reports = 0
else:
_, _, commends, reports = results[0]
if good:
commends += 1
else:
reports += 1
db.execute(
'UPDATE carma SET commends={}, reports={} '
'WHERE username="******"'.format(
commends,
reports,
username
)
)
def new(self, **kwargs):
_dirty_fields = []
_values = []
for k, v in kwargs.iteritems():
_dirty_fields.append(k)
if k in self.__time_fields and v:
v = datetime.fromtimestamp(v)
kwargs[k] = v
_values.append(v)
self.__dict__.update(kwargs)
_sql = 'INSERT INTO %s (' % self.table + ','.join(_dirty_fields) + ') VALUES (' \
+ ','.join(['%s'] * len(_values)) + ')'
res = yield db.insert(_sql, _values)
if self.table == 'tb_character':
if res > 1000000:
db.execute('DELETE FROM {0} WHERE id={1};'.format(
self.table, res))
log.error('insert error. res:{0}, _values:{1}.'.format(
res, _values))
raise AttributeError(
'Attribute: insert new character id error')
new_res = kwargs.get('sid', 1001) * 1000000 + res
db.execute('UPDATE {0} SET id={1} WHERE id={2};'.format(
self.table, new_res, res))
self.__uid = new_res
log.error('For Test. table:{0}, res:{1}, new_res:{2}.'.format(
self.table, res, new_res))
else:
self.__uid = res
defer.returnValue(self.__uid)
def is_appropriate(self, report):
db.execute("SELECT patterns FROM triggerpatterns WHERE user_id=?;",
(self.uid, ))
patterns = db.cur.fetchone()[0]
for pattern in patterns.splitlines():
if pattern.lower() in report.text.lower():
break
else:
# no pattern matched
return False
default_badwords = """
bitch
w***e
hitler
s**t
hure
jude
schwuchtel
schlampe
f*g
f****t
nigger
neger
schlitz
"""
db.execute("SELECT words FROM badwords WHERE user_id=?;", (self.uid, ))
badwords = db.cur.fetchone()
for word in report.text.lower().splitlines():
if word in badwords:
return False
for word in default_badwords.splitlines():
if word in badwords:
return False
return True
def main():
with open("./books.csv", "r") as books_csv:
# read the csv file in csv_reader variable via csv.reader method
csv_reader = csv.reader(books_csv)
# jumps to second row as first row is the column name
next(csv_reader)
# truncate the table before re-executing
db.execute('TRUNCATE TABLE books CASCADE')
data = []
# insert the data from csv to 'books' table on postgresql by bulk insert
for isbn, title, author, year in csv_reader:
title = title.replace("'", "''")
author = author.replace("'", "''")
data.append(f"('{isbn}', '{title}', '{author}', '{year}')")
# print(data)
db.execute(f'''
INSERT INTO books(isbn, title, author, year)
VALUES {', '.join(data)}
''')
print('Data stored on Postgre!')
db.commit()
def collect_job(user_id, job_id):
sql = 'select id from collect_job where user = %s and job = %s' % (user_id, job_id)
if _db.query_one(sql) is None:
_db.insert('collect_job', {'user': user_id, 'job': job_id})
_db.execute('update job set collect = collect + 1 where id = %s' % job_id)
return True
return False
def syncdb(self):
if self.__dirty:
_dirty_fields = self.__dirty_fields[:]
if len(_dirty_fields) == 0 and False == self.__del:
log.info(
'no dirty_fields! table name:{0}, attrib_id:{1}.'.format(
self.table, self.__uid))
return
_sql = ''
try:
if self.__del:
db.execute('DELETE FROM {0} WHERE id={1};'.format(
self.table, self.__uid))
else:
_sql, _v = self.__gen_update_value(_dirty_fields)
if _v:
db.execute(_sql, _v)
else:
log.warn(
'Update error. table: {0}, cid: {1}, sql: {2}, dirty: {3}.'
.format(self.table, self.__uid, _sql,
self.__dirty_fields))
except:
log.exception(
'[ SQLERROR ]table:{0}, id:{1}, dirty:{2}, new:{3}, dirty_fields:{4}, sql:{5}'
.format(self.table, self.__uid, self.__dirty, self.__new,
self.__dirty_fields, _sql))
else:
self.clean()
def get_telegram_subscribers(self):
db.execute(
"""SELECT subscriber_id
FROM telegram_subscribers
WHERE user_id = ?;""", (self.uid, ))
rows = db.cur.fetchall()
return rows
def new(self, **kwargs):
_dirty_fields = []
_values = []
for k, v in kwargs.iteritems():
_dirty_fields.append(k)
if k in self.__time_fields and v:
v = datetime.fromtimestamp( v )
kwargs[k] = v
_values.append(v)
self.__dict__.update(kwargs)
_sql = 'INSERT INTO %s (' % self.table + ','.join(_dirty_fields) + ') VALUES (' \
+ ','.join(['%s'] * len(_values)) + ')'
res = yield db.insert(_sql, _values)
if self.table == 'tb_character':
if res > 1000000:
db.execute('DELETE FROM {0} WHERE id={1};'.format(self.table, res))
log.error('insert error. res:{0}, _values:{1}.'.format( res, _values ))
raise AttributeError('Attribute: insert new character id error')
new_res = kwargs.get('sid', 1001)*1000000 + res
db.execute('UPDATE {0} SET id={1} WHERE id={2};'.format(self.table, new_res, res))
self.__uid = new_res
log.error('For Test. table:{0}, res:{1}, new_res:{2}.'.format( self.table, res, new_res ))
else:
self.__uid = res
defer.returnValue(self.__uid)
def insert_url(url):
db.execute("SELECT id from url_short where url='%s'" %url)
if db.rowcount():
return db.fetchone()[0]
db.execute("INSERT INTO url_short(url, count) VALUES('%s',0)" %url)
db.con.commit()
return db.cursor.lastrowid
def initial_version():
db.execute("""
CREATE TABLE Person (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT
)
""")
db.execute("""
CREATE TABLE Role (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE NOT NULL
)
""")
db.execute("""
CREATE TABLE Assignment (
id INTEGER PRIMARY KEY AUTOINCREMENT,
person INTEGER NOT NULL REFERENCES Person (id),
role INTEGER NOT NULL REFERENCES Role (id),
year INTEGER NOT NULL,
semester INTEGER NOT NULL
)
""")
db.execute("""
CREATE INDEX idx_assignment__person ON Assignment (person)
""")
db.execute("""
CREATE INDEX idx_assignment__role ON Assignment (role)
""")
def fetch_coins():
now = int(time.time())
sql = 'delete from otc_origin'
db.execute(sql)
coins = {
'BTC': '1',
'ETH': '3',
'USDT': '2',
'LTC': '8',
'HT': '4',
'HUSD': '6',
'EOS': '5',
'XRP': '7'
}
trade_types = ['sell', 'buy']
# threads = []
for trade_type in trade_types:
for coin_name, coin_id in coins.items():
fetch_coin(trade_type, coin_id, coin_name, 1)
'''
t = myThread(trade_type, coin_id, coin_name, 1)
threads.append(t)
'''
'''
for t in threads:
t.start()
for t in threads:
t.join()
'''
end = int(time.time())
print('花费耗时:', end - now)
def get_telegram_credentials(self):
db.execute(
"""SELECT apikey
FROM telegram_accounts
WHERE user_id = ? AND active = 1;""", (self.uid, ))
row = db.cur.fetchone()
return row[0]
def save_twitter_token(self, access_token, access_token_secret):
db.execute(
"""INSERT INTO twitter_accounts(
user_id, client_id, client_secret
) VALUES(?, ?, ?);""",
(self.uid, access_token, access_token_secret))
db.commit()
def remove_telegram_subscribers(self, subscriber_id):
db.execute(
"""DELETE
FROM telegram_subscribers
WHERE user_id = ?
AND subscriber_id = ?;""", (self.uid, subscriber_id))
db.commit()
def post(self):
newUD = request.get_json()
avatar_path = path.join(current_dir, f"../twooter-app/public/avatars/{session['hashed_id']}.jpg")
if( len(newUD['avatar_input']) > 3):
with open(avatar_path, "wb") as fh:
fh.write(base64.decodebytes(newUD['avatar_input'].encode('ascii')))
try:
db.execute("UPDATE users \
SET displayname=:displayname, email=:email, dob=:dob, bio=:bio, avatar=:avatar\
WHERE user_id=:user_id",
user_id=session['user_id'],
displayname=newUD['name_input'],
email=newUD['email_input'],
dob=newUD['dob_input'],
bio=newUD['bio_input'],
avatar=session['hashed_id'])
except Exception as e:
print(e)
return 'userData update failed', 500
else:
return 'userData updated in DB', 200
return 'something went wrong :(',501
def save_request_token(self, token):
db.execute(
"""INSERT INTO
twitter_request_tokens(
user_id, request_token, request_token_secret
) VALUES(?, ?, ?);""",
(self.uid, token["oauth_token"], token["oauth_token_secret"]))
db.commit()
def register_user(phone, password):
token = util.generate_access_token(password)
salt = util.generate_salt()
db.execute('update user set token = "%s", password = "******", salt = "%s", pass = 1 where phone = "%s"'
% (token, util.bcrypt_password(password + salt), salt, phone))
uid = db.query_one('select id from user where phone = %s' % phone)['id']
db.insert('user_settings', {'user': uid})
db.insert('user_info', {'user': uid, 'nick': phone})
def delete_email(self, email):
db.execute("SELECT COUNT(*) FROM email WHERE user_id=?", (self.uid, ))
if db.cur.fetchone()[0] == 1:
return False # don't allow to delete last email
db.execute("DELETE FROM email WHERE user_id=? AND email=?;",
(self.uid, email))
db.commit()
return True
def on_follow_short_link(request, short_id):
db.execute("SELECT * from url_short where id=%s" %short_id)
link_target = db.fetchone()
if link_target is None:
raise NotFound()
db.execute("UPDATE url_short SET count=%d where id=%d "%(link_target[2]+1, link_target[0]))
db.con.commit()
return redirect(link_target[1])
def post(self):
def formCompletion (formList):
for field in formList:
if field is '' or None:
return False
return True
def validateEmail (email):
"""
checs if email is legit
"""
return True if match(".{1,}@[^.]{1,}", email) is not None else False
def checkUsername (username):
"""
checks if username is available
"""
q = db.execute("SELECT * FROM users WHERE username=:username", username=username)
q = query_to_dict(q)
return True if len(q) < 1 else False
def validatePasswordMatch (password, confirmation):
"""
checks if password and confirmation match
"""
return True if password == confirmation else False
user_info_json = request.get_json()
print(user_info_json)
#constructs a new user model with username and password supplied from front-end in JSON format
new_user = User(user_info_json['username'], user_info_json['password'], user_info_json['email'])
#input validation
if not formCompletion([user_info_json['username'], user_info_json['password'], user_info_json['passwordConfirm'], user_info_json['email']]):
return 'form incomplete',459
if not validateEmail(new_user.email):
return 'invalid email', 460
if not validatePasswordMatch(new_user.password, user_info_json['passwordConfirm']):
return 'passwords do not match', 461
if not checkUsername(new_user.username):
return 'username is taken', 462
# - - -
#create a new user
db.execute("INSERT INTO users (username, displayname, password, email) VALUES (:username, :displayname, :password, :email)",
username=new_user.username,
displayname=new_user.displayname,
password=generate_password_hash(new_user.password),
email=new_user.email)
q = db.execute("SELECT * FROM users WHERE username=:username", username=new_user.username)
if q is None:
return 'user-creation-failed', 500
session['user_id'] = query_to_dict(db.execute('SELECT * FROM users WHERE username=:username', username=new_user.username))[0]['user_id']
#we need a hashed id so we can send it to the front end without worries (for avatar path)
session['hashed_id'] = hash(session['user_id']) #<== very intricate hash function :)
return 'user-created', 200 #return 201 if valid POST request.
def get_token(phone, password):
r = db.query_one('select password, salt from user where phone = %s and pass=1' % phone)
if r is not None and util.bcrypt_check(password=password + r['salt'], hashed=r['password']):
new_token = util.generate_access_token(password)
db.execute('update user set token = "%s" where phone = "%s"' % (new_token, phone))
return db.query_one(
'select user.id, user.phone, user.token, user_settings.city, user_settings.cityId, user_settings.appliable '
'from user join user_settings on user_settings.user = user.id where user.phone = %s' % phone)
return None
def apply_job(user_id, job_id):
sql = 'select id from apply_job where user = %s and job = %s' % (user_id, job_id)
if _db.query_one(sql) is None:
_db.insert('apply_job', {'user': user_id, 'job': job_id})
_db.execute('update job set apply = apply + 1 where id = %s' % job_id)
r = _db.query_one('select company from job where id = %s' % job_id)
_db.execute('update company_info set apply = apply + 1 where company = %s' % r['company'])
return True
return False
def add_or_update_code(self, code, code_type='SH'):
if self.is_valid_code(code, code_type):
sql = "insert into stock (code, code_type) values('%s','%s') on duplicate key update code_type='%s'" % (
code,
code_type,
code_type,
)
self.logger.info(sql)
db.execute(sql)
def save_masto_token(self, access_token, instance):
db.execute(
"""SELECT id
FROM mastodon_instances
WHERE instance = ?;""", (instance, ))
instance_id = db.cur.fetchone()[0]
db.execute(
"INSERT INTO mastodon_accounts(user_id, access_token, instance_id, active) "
"VALUES(?, ?, ?, ?);", (self.uid, access_token, instance_id, 1))
db.commit()
def remove(self,obj):
ref = self.reference
if not isinstance(obj,ref.child): raise Exception
if ref.through:
sql = 'delete from "%s" where "%s" = ? and "%s" = ?' % (ref.through,ref.parent_column,ref.child_column)
values = [getattr(self.model,self.model._pk),getattr(obj,obj._pk)]
db.execute(sql,values)
else:
setattr(obj,ref.child_column,None)
obj.save()
super(self.__class__,self).remove(obj)
def toggle_complete():
req = request.get_json()
completion = req['status']
oid = req['oid']
db.execute(
"""
UPDATE customerorders SET status = %s WHERE oid = %s
""", completion, oid)
return jsonify(msg="successs"), 200
def get(self):
q = db.execute("SELECT * FROM users WHERE user_id=:user_id", user_id=session['user_id'])
q = query_to_dict(q)
#query for follower/following counts and then append them to our main query
q[0].update(query_to_dict(db.execute("SELECT COUNT(other_id) as following FROM follows WHERE self_id=:user_id", user_id=session['user_id']))[0])
q[0].update(query_to_dict(db.execute("SELECT COUNT(self_id) as followers FROM follows WHERE other_id=:user_id", user_id=session['user_id']))[0])
#append an avatar element to the dictionary if the user has an avatar in the directory
return jsonify(q[0])
def init_lifts(resort_id):
parsed_json = parse(resort_id)
lifts = parsed_json['Lifts']
lift_names = list(map(lambda l: l['Name'], lifts))
#save lift names in db
for lift in lift_names:
sql = 'INSERT INTO lifts (resort_id, name) VALUES (%s, %s)'
val = (resort_id, lift)
db.execute(sql, val)
db.commit()
db.close_db_connection()
def update_name(self, stock_list):
for stock in stock_list:
column = stock.split(COMMA)
code = column[0]
name = column[1]
print name
sql = self.UPDATE_NAME % (
name,
code,
)
db.execute(sql)
def get_masto_credentials(self):
db.execute(
"""SELECT access_token, instance_id
FROM mastodon_accounts
WHERE user_id = ? AND active = 1;""", (self.uid, ))
row = db.cur.fetchone()
db.execute(
"""SELECT instance, client_id, client_secret
FROM mastodon_instances
WHERE id = ?;""", (row[1], ))
instance = db.cur.fetchone()
return instance[1], instance[2], row[0], instance[0]
def post(self):
other_id = request.get_json();
other_id = query_to_dict(db.execute("SELECT user_id FROM users WHERE username=:username",username=other_id))[0]['user_id']
q = db.execute("SELECT post_id, message, image, username, displayname, verified, avatar \
FROM posts JOIN users on posts.user_id=users.user_id \
WHERE posts.user_id=:user_id AND image!=''", user_id=other_id)
q = query_to_dict(q)
q = append_twoot_stats(q)
twoots = {}
for d in q:
twoots[d['post_id']] = d
return jsonify(twoots)
def create(self,*args,**kwargs):
ref = self.reference
obj = ref.child(*args,**kwargs)
if ref.through:
obj.save()
sql = 'insert into "%s" ("%s","%s") values (?,?)' % (ref.through,ref.parent_column,ref.child_column)
values = [getattr(self.model,self.model._pk),getattr(obj,obj._pk)]
db.execute(sql,values)
else:
setattr(obj,ref.child_column,getattr(self.model,self.model._pk))
obj.save()
self.append(obj)
def removeUserVote(vid: int, uid: int, choice: int = None):
if choice is None:
sel = db.executeFAll(
"SELECT Choice FROM UserVote WHERE UserID = %s AND VoteID = %s;",
uid, vid)
db.execute("DELETE FROM UserVote WHERE UserID = %s AND VoteID = %s;",
uid, vid)
return [s[0] for s in sel]
else:
db.execute(
"DELETE FROM UserVote WHERE UserID = %s AND VoteID = %s AND Choice = %s;",
uid, vid, choice)
def init_lifts(resort_id):
lifts_dom = parse(resort_id)
lifts = lifts_dom.find_all('div','lift')
lift_names = list(map(lambda l: l.find_all('div','cell')[0].text, lifts))
#save lift names in db
for lift in lift_names:
sql = 'INSERT INTO lifts (resort_id, name) VALUES (%s, %s)'
val = (resort_id, lift)
db.execute(sql, val)
db.commit()
db.close_db_connection()
def add(args):
name = args.get("name")
model = args.get("model", "normal")
userid = args.get("userid")
if not userid:
raise InvalidUser
id = maxid() + 1
if not name:
name = str(id)
sql = "INSERT INTO node(id, name, model, userid)VALUES(?, ?, ?, ?)"
db.execute(sql, (id, name, model, userid))
db.commit()
def get_weather_for_resort(resort_id):
apikey = config['appid']
res = db.query('SELECT geo_lat, geo_lon FROM resorts WHERE id='+str(resort_id))
latitude = res[0]['geo_lat']
longitude = res[0]['geo_lon']
url = 'http://api.openweathermap.org/data/2.5/weather?lat={}&lon={}&APPID={}'.format(latitude, longitude, apikey)
report = requests.get(url)
weather_data = json.loads(report.content)
data_map = {
'source': 'openweathermap',
'updated_at': time.strftime('%Y-%m-%d %H:%M:%S'),
'resort_id': resort_id
}
if len(weather_data) > 0:
if len(weather_data['weather']) > 0:
weather = weather_data['weather'][0]
if 'id' in weather: data_map['label_id'] = weather['id']
if 'main' in weather: data_map['label'] = weather['main']
if 'description' in weather: data_map['description'] = weather['description']
if 'main' in weather_data:
main = weather_data['main']
if 'temp' in main: data_map['temperature'] = (main['temp'] - 273.15) #kelvin->centigrade
if 'pressure' in main: data_map['pressure'] = main['pressure']
if 'humidity' in main: data_map['humidity'] = main['humidity']
if 'temp_min' in main: data_map['temperature_min'] = (main['temp_min'] - 273.15) #kelvin->centigrade
if 'temp_max' in main: data_map['temperature_max'] = (main['temp_max'] - 273.15) #kelvin->centigrade
if 'visibility' in weather_data: data_map['visibility'] = weather_data['visibility']
if 'wind' in weather_data:
wind = weather_data['wind']
if 'speed' in wind: data_map['wind_speed'] = wind['speed']
if 'deg' in wind: data_map['wind_dir'] = wind['deg']
if 'clouds' in weather_data and 'all' in weather_data['clouds']:
data_map['cloudiness'] = weather_data['clouds']['all']
if 'rain' in weather_data:
rain = weather_data['rain']
if '1h' in rain: data_map['rain_last_1h'] = rain['1h']
if '3h' in rain: data_map['rain_last_3h'] = rain['3h']
if 'snow' in weather_data:
snow = weather_data['snow']
if '1h' in snow: data_map['snow_last_1h'] = snow['1h']
if '3h' in snow: data_map['snow_last_3h'] = snow['3h']
if 'dt' in weather_data: data_map['data_calculated_at'] = datetime.utcfromtimestamp(weather_data['dt']).strftime('%Y-%m-%d %H:%M:%S')
cols = ','.join(data_map.keys())
vals = data_map.values()
vals = tuple(vals)
tmp = ','.join(['%s'] * len(vals))
sql = 'INSERT INTO weather_reports ({}) VALUES ({})'.format(cols,tmp)
db.execute(sql, vals)
db.commit()
db.close_db_connection()
def on_short_link_details(request, short_id):
query = "SELECT * from url_short where id=%d" %int(short_id)
# pdb.set_trace()
db.execute("SELECT * from url_short where id=%d" %int(short_id))
link_target = db.fetchone()
if link_target is None:
raise NotFound()
click_count = int(link_target[2])
return render_template('short_link_details.html',
link_target=link_target[1],
short_id=link_target[0],
click_count=click_count+1
)
def add(self,obj):
ref = self.reference
if not isinstance(obj,ref.child): raise Exception
if ref.through:
if not getattr(self.model,self.model._pk):
self.model.save()
sql = 'insert into "%s" ("%s","%s") values (?,?)' % (ref.through,ref.parent_column,ref.child_column)
values = [getattr(self.model,self.model._pk),getattr(obj,obj._pk)]
db.execute(sql,values)
else:
setattr(obj,ref.child_column,self.model.id)
obj.save()
self.append(obj)
def get_request_token(self):
db.execute(
"""SELECT request_token, request_token_secret
FROM twitter_request_tokens
WHERE user_id = ?;""", (self.uid, ))
request_token = db.cur.fetchone()
db.execute(
"""DELETE FROM twitter_request_tokens
WHERE user_id = ?;""", (self.uid, ))
db.commit()
return {
"oauth_token": request_token[0],
"oauth_token_secret": request_token[1]
}
def add(args):
username = args.get("username", None)
password = args.get("password", None)
if username and len(username) > 0 and password and len(password) > 0:
if checkExist(username):
raise AccountExist
maxId = fetchMaxId() + 1
sql = "INSERT INTO account (id, username, password)VALUES(?, ?, ?)"
db.execute(sql, (maxId, username, password))
db.commit()
return Account(maxId)
else:
raise InvalidArgument
def save(self):
fields = []
values = []
for k in self._columns:
fields.append(k)
values.append(self.__dict__[k])
if getattr(self,self._pk):
sql = 'update "%s" set %s where "%s" = ?' % (self._table, ','.join(['"%s"=?' % f for f in fields]),self._pk)
db.execute(sql,values + [getattr(self,self._pk)])
else:
sql = 'insert into "%s" ("%s") values (%s)' % (self._table, '","'.join(fields), ','.join(['?']*len(fields)))
db.execute(sql,values)
setattr(self,self._pk,db.lastrowid)
return self
def add_primary_key(table):
df = query(f'''SELECT * FROM {table} LIMIT 1''')
existing_cols = df.columns
if 'table_id' in existing_cols:
print('primary key already detected')
return
db.execute(f'''
ALTER TABLE
{table}
ADD
table_id INT
PRIMARY KEY
AUTO_INCREMENT
;
''')
def get(cls, id):
sql = u'select "%s" from "%s" where "%s" = ?' % ('","'.join(cls._columns),cls._table,cls._pk)
data = {}
for row in db.execute(sql,[id]):
for num in xrange(len(cls._columns)):
data[cls._columns[num]] = row[num]
return cls(**data) if data else None # raise an exception ?
def select(cls, query="", context=None, count=False, left_join=False, values=[]):
join = 'left join' if left_join else 'join'
collections = re.findall(r'([\w\.]+)\.\w+',query) # find collections
collections = normalize(collections)
query = re.sub(r'([\w\.]+)\.(\w+)','"\\1"."\\2"',query) # quote collection aliases
references = {} # find reference for each collection
for collection in collections:
if '.' in collection:
prefix,attr = collection.rsplit('.',1)
references[collection] = model_cache[references[prefix].model]._references[attr]
else:
references[collection] = cls._references[collection]
query = parse(query,context)
if count: sql = 'select count(distinct "%s"."%s")' % (cls.__name__,cls._pk)
else:
columns = []
for column in cls._columns:
columns.append('"%s"."%s"' % (cls.__name__,column))
sql = 'select distinct ' + ','.join(columns)
sql += ' from "%s" as "%s"' % (cls._table,cls.__name__)
for collection in sorted(references.keys()): # add joins for each collection
prefix = collection.rsplit('.',1)[0] if '.' in collection else cls.__name__
ref = references[collection]
if isinstance(ref,HasOne):
sql += ' %s "%s" as "%s" on ("%s"."%s" = "%s"."%s")' % \
(join,ref.child._table,collection,collection,ref.child_column,prefix,ref.parent_column)
elif isinstance(ref,HasMany):
if ref.through:
prefix_pk = model_cache[references[prefix].model]._pk if prefix in references else cls._pk
sql += ' %s "%s" as "%s.map" on ("%s"."%s" = "%s.map"."%s")' % \
(join,ref.through,collection,prefix,prefix_pk,collection,ref.parent_column)
sql += ' %s "%s" as "%s" on ("%s"."%s" = "%s.map"."%s")' % \
(join,ref.child._table,collection,collection,ref.child._pk,collection,ref.child_column)
else:
sql += ' %s "%s" as "%s" on ("%s"."%s" = "%s"."%s")' % \
(join,ref.child._table,collection,collection,ref.child_column,prefix,ref.parent_column)
sql += " %s" % query
if count: return db.execute(sql,values)[0][0]
else:
objects = [] # TODO: use dictionary, instead of distinct, to provide uniqueness ???
for row in db.execute(sql,values):
data = {}
for num in xrange(len(cls._columns)):
data[cls._columns[num]] = row[num]
objects.append(cls(**data))
return objects
def __new__(cls, name, bases, attrs):
if name == 'Model': return type.__new__(cls, name, bases, attrs)
references = {}
for key,val in attrs.items():
if isinstance(val,HasOne) or isinstance(val,HasMany):
val._parent = name
references[key] = val
del attrs[key]
new_class = type.__new__(cls, name, bases, attrs)
new_class._table = attrs.get('_table',re.sub(r'([A-Z])',r'_\1',name).lower().lstrip('_'))
new_class._pk = attrs.get('_pk','id')
if not attrs.get('_columns',None):
sql = 'select * from "%s" limit 1' % new_class._table
db.execute(sql)
new_class._columns = [col[0] for col in db.cursor.description]
new_class._references = references
model_cache[new_class.__name__] = new_class
return new_class
def syncdb(self):
if self.__dirty:
_dirty_fields = self.__dirty_fields[:]
if len(_dirty_fields) == 0 and False == self.__del:
log.info('no dirty_fields! table name:{0}, attrib_id:{1}.'.format( self.table, self.__uid ))
return
_sql = ''
try:
if self.__del:
db.execute('DELETE FROM {0} WHERE id={1};'.format(self.table, self.__uid))
else:
_sql, _v = self.__gen_update_value(_dirty_fields)
if _v:
db.execute(_sql, _v)
else:
log.warn('Update error. table: {0}, cid: {1}, sql: {2}, dirty: {3}.'.format(self.table, self.__uid, _sql, self.__dirty_fields))
except:
log.exception('[ SQLERROR ]table:{0}, id:{1}, dirty:{2}, new:{3}, dirty_fields:{4}, sql:{5}'.format(
self.table, self.__uid, self.__dirty, self.__new, self.__dirty_fields, _sql))
else:
self.clean()
def save_phone_code(user, phone, code):
if db.exist('select id from phone_code where phone = %s' % phone):
db.execute('delete from phone_code where phone = %s' % phone)
db.insert(table='phone_code', values={'user': user, 'phone': phone, 'code': code})
def discollect(user_id, enter_id):
sql = 'delete from collect_enterprise where user = %s and company = %s' % (user_id, enter_id)
_db.execute(sql)
return True
def store(self,session):
data = base64.encodestring(pickle.dumps(session))
if self.exists(session.key):
db.execute("""update "%s" set data = ? where key = ?""" % self.table,[data,session.key])
else:
db.execute("""insert into "%s" (key,data) values (?,?)""" % self.table,[session.key,data])
def upload_resume(user_id, file_name):
if _db.query_one('select id from resume where user = %s' % user_id) is None:
_db.insert('resume', {'user': user_id})
_db.execute('update resume set file = "%s" where user = %s' % (file_name, user_id))
def change_nick(user_id, nick):
sql = 'update user_info set nick = "%s" where user = %s' % (nick, user_id)
_db.execute(sql)
def change_signature(user_id, signature):
sql = 'update user_info set signature = "%s" where user = %s' % (signature, user_id)
_db.execute(sql)
def discollect(user_id, job_id):
sql = 'delete from collect_job where user = %s and job = %s' % (user_id, job_id)
_db.execute('update job set collect = collect - 1 where id = %s' % job_id)
_db.execute(sql)
return True
def update_resume(user_id, field, value):
if _db.query_one('select id from resume where user = %s' % user_id) is None:
_db.insert('resume', {'user': user_id})
_db.execute('update resume set %s = "%s" where user = %s' % (field, value, user_id))
def delete_notifications(user_id, n_id):
sql = 'delete from notification_user where id = %s and user = %s' % (n_id, user_id)
_db.execute(sql)
return True
def save_new_user(phone):
if db.exist('select id from user where phone = %s' % phone):
db.execute('delete from user where phone = %s' % phone)
db.insert(table='user', values={'phone': phone})
return db.query('select id from user where phone = %s' % phone)[0]