示例#1
0
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
示例#2
0
    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)
示例#3
0
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)
示例#4
0
    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
            )
        )
示例#5
0
    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)
示例#6
0
文件: user.py 项目: patcon/ticketfrei
    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
示例#7
0
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()
示例#8
0
文件: job.py 项目: coocol/server_app
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
示例#9
0
    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()
示例#10
0
文件: user.py 项目: patcon/ticketfrei
 def get_telegram_subscribers(self):
     db.execute(
         """SELECT subscriber_id 
                       FROM telegram_subscribers 
                       WHERE user_id = ?;""", (self.uid, ))
     rows = db.cur.fetchall()
     return rows
示例#11
0
    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)
示例#12
0
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
示例#13
0
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)
    """)
示例#14
0
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)
示例#15
0
文件: user.py 项目: patcon/ticketfrei
 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]
示例#16
0
文件: user.py 项目: patcon/ticketfrei
 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()
示例#17
0
文件: user.py 项目: patcon/ticketfrei
 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()
示例#18
0
    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
示例#19
0
文件: user.py 项目: patcon/ticketfrei
 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()
示例#20
0
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})
示例#21
0
文件: user.py 项目: patcon/ticketfrei
 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
示例#22
0
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])
示例#23
0
    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.
示例#24
0
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
示例#25
0
文件: job.py 项目: coocol/server_app
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
示例#26
0
 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)
示例#27
0
文件: user.py 项目: patcon/ticketfrei
 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()
示例#28
0
 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)
示例#29
0
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
示例#30
0
 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])
示例#31
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()
示例#32
0
 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)
示例#33
0
文件: user.py 项目: patcon/ticketfrei
 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]
示例#34
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)        
示例#35
0
 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)
示例#36
0
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)
示例#37
0
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()
示例#38
0
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()
示例#39
0
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()
示例#40
0
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
    )
示例#41
0
 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)
示例#42
0
文件: user.py 项目: patcon/ticketfrei
 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]
     }
示例#43
0
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
示例#44
0
 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
示例#45
0
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
            ;
    ''')
示例#46
0
 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 ?
示例#47
0
 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
示例#48
0
 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
示例#49
0
    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()
示例#50
0
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})
示例#51
0
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
示例#52
0
 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])
示例#53
0
文件: user.py 项目: coocol/server_app
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))
示例#54
0
文件: user.py 项目: coocol/server_app
def change_nick(user_id, nick):
    sql = 'update user_info set nick = "%s" where user = %s' % (nick, user_id)
    _db.execute(sql)
示例#55
0
文件: user.py 项目: coocol/server_app
def change_signature(user_id, signature):
    sql = 'update user_info set signature = "%s" where user = %s' % (signature, user_id)
    _db.execute(sql)
示例#56
0
文件: job.py 项目: coocol/server_app
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
示例#57
0
文件: user.py 项目: coocol/server_app
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))
示例#58
0
文件: user.py 项目: coocol/server_app
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
示例#59
0
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]