def add(name, question=None, answer=None, tag_num=None, title=None, content=None, file_name=None): conn = None cur = None if name == 'other': conn = get_connection() cur = conn.cursor() sql = 'insert into article(title,url_address,content,tag_id)VALUES (%s,%s,%s,%s)' try: cur.execute(sql, (title, file_name, content, tag_num)) conn.commit() finally: conn.close() cur.close() elif name == 'QA': conn = get_connection() cur = conn.cursor() sql = 'insert into qa(question,answer,tag_id)VALUES (%s,%s,%s)' try: cur.execute(sql, (question, answer, tag_num)) conn.commit() except Exception as e: conn.rollback() finally: conn.close() cur.close()
def __create_transformed_sales_table(table_name: str): postgres.create_table(get_connection(), table_name, [ "id serial PRIMARY KEY", "app_id varchar(36) NOT NULL UNIQUE", "date date", "address varchar(255)", "postcode varchar(20)", "county varchar(20)", "price decimal", "full_price boolean", "vat_exclusive boolean", "new boolean", "size varchar(20)" ])
def persist_raw_sales(property_sales: List[RawPropertySale]): table_name = config['storage']['table']['raw'] postgres.drop_table(get_connection(), table_name) logging.debug("Dropped table '{}'".format(table_name)) __create_raw_sale_table(table_name) logging.debug("Table '{}' is available".format(table_name)) __insert_raw_property_sales(table_name, property_sales)
def __create_raw_sale_table(table_name: str): postgres.create_table(get_connection(), table_name, [ "id serial PRIMARY KEY", "app_id varchar(36) NOT NULL UNIQUE", "date varchar(20)", "address varchar(255)", "postcode varchar(20)", "county varchar(20)", "price varchar(20)", "not_full_price varchar (20)", "vat_exclusive varchar (20)", "property_description varchar(255)", "size_description varchar(255)" ])
def persist_transformed_sales( transformed_sales: List[TransformedPropertySale]): table_name = config['storage']['table']['transformed'] postgres.drop_table(get_connection(), table_name) logging.debug("Dropped table '{}'".format(table_name)) __create_transformed_sales_table(table_name) logging.debug("Table '{}' is available".format(table_name)) __insert_transformed_sales(table_name, transformed_sales)
def __insert_transformed_sales(table_name: str, property_sales: List[TransformedPropertySale]): postgres.bulk_insert(get_connection(), table_name, [ "app_id", "date", "address", "postcode", "county", "price", "full_price", "vat_exclusive", "new", "size" ], ((sale.app_id, sale.date, sale.address, sale.postcode, sale.county, sale.price, sale.full_price, sale.vat_exclusive, sale.new, sale.size) for sale in property_sales))
def get_done(risk_num): conn = get_connection() cur = conn.cursor() sql = 'update user set risk_tendency={} where name="dp"'.format(risk_num) cur.execute(sql) conn.commit() cur.close() conn.close() return
def __insert_raw_property_sales(table_name: str, property_sales: List[RawPropertySale]): postgres.bulk_insert(get_connection(), table_name, [ "app_id", "date", "address", "postcode", "county", "price", "not_full_price", "vat_exclusive", "property_description", "size_description" ], ((sale.app_id, sale.date, sale.address, sale.postcode, sale.county, sale.price, sale.not_full_price, sale.vat_exclusive, sale.property_description, sale.size_description) for sale in property_sales))
def get_top10_funplay(): conn = get_connection() cur = conn.cursor() sql = 'select id,rank,name,pic_url from amusement_project order by rank DESC ' cur.execute(sql) res = cur.fetchall() data = [] for i in res: content = {"id": i[0], "rank": i[1], "name": i[2], "pic_url": i[3]} data.append(content) return data
def create_post_record(post): if check_post_exists_by_slug(post["slug"]): return connection = get_connection() with connection.cursor() as cursor: sql = """INSERT INTO kodilan_posts(slug, position, location, created_at, company, tags, description)VALUES(%s,%s,%s,now(),%s,%s,%s)""" cursor.execute( sql, (post.get('slug'), post.get('position'), post.get('location'), post.get('company'), post.get('tags'), post.get('description'))) connection.commit()
def run_query( query, binds=None, cursor_type=pymysql.cursors.DictCursor) -> pymysql.cursors.Cursor: connection = get_connection() cursor = connection.cursor(cursor_type) if (binds is None): cursor.execute(query) else: cursor.execute(query, binds) connection.commit() return cursor
def open(self, message_type, destination): from app import get_connection """ example: open(message_type='room', destination='room1') or open(message_type='user' destination='fred')""" if not destination or not message_type: self.write_message({'error': 1, 'text': 'Error: No room or user specified'}) self.close() self.connection = get_connection() self.destination = self._format_destination(message_type, destination) self.message_type = message_type yield tornado.gen.Task(self.connection.subscribe, self.destination) self.connection.listen(self.on_messages_published)
def get_front_end_tech_stats(startDate, endDate, order="desc"): connection = get_connection() langs = [ "react", "vue", "jquery", "bootstrap", "angular", "redux", "vuex", "figma", "photoshop" ] with connection.cursor() as cursor: res = [] for lang in langs: where = f'where (tags like %s or tags like %s) && `created_at` >= %s && `created_at` <= %s' sql = f'select %s as tech, count(*) as total from kodilan_posts {where}' cursor.execute( sql, (lang, f'%{lang}', f'%{lang},%', startDate, endDate)) res.append(cursor.fetchone()) res.sort(key=operator.itemgetter('total'), reverse=True) return res
def get_tech_stats(startDate, endDate, order="desc"): connection = get_connection() langs = [ "spring", "django", "nodejs", "react", "vue", "react-native", "flutter", "rails", "dotnet", "laravel" ] with connection.cursor() as cursor: res = [] for lang in langs: where = f'where (tags like %s or tags like %s) && `created_at` >= %s && `created_at` <= %s' sql = f'select %s as tech, count(*) as total from kodilan_posts {where}' cursor.execute( sql, (lang, f'%{lang}', f'%{lang},%', startDate, endDate)) res.append(cursor.fetchone()) res.sort(key=operator.itemgetter('total'), reverse=True) return res
def get_lang_stats(startDate, endDate, order="desc"): connection = get_connection() langs = [ "java", "c#", "python", "javascript", "go", "dart", "php", "ruby", "c", "c++", "typescript" ] with connection.cursor() as cursor: res = [] for lang in langs: where = f'where (tags like %s or tags like %s) && `created_at` >= %s && `created_at` <= %s' sql = f'select %s as lang,count(*) as total from kodilan_posts {where}' cursor.execute( sql, (lang, f'%{lang}', f'%{lang},%', startDate, endDate)) res.append(cursor.fetchone()) res.sort(key=operator.itemgetter('total'), reverse=True) return res
def get_new_by_type(type): conn = get_connection() cur = conn.cursor() sql = 'select * from article where tag_id={}'.format(type) cur.execute(sql) res = cur.fetchall() data = [] for i in res: content = { "id": i[0], "title": i[1], "url_address": i[2], "content": i[3], "collection": i[5] } data.append(content) return data
def get_order(name): conn = get_connection() cur = conn.cursor() sql = 'select * from amusement_project where name=%s' time_list = [] cur.execute(sql,name) res = cur.fetchall() time=res[2] max_man=res[6] for i in range(1,5): wait_people=random.randint(1,max_man) content={"back_time":back_time(time,i)} time_list.append(content) conn.close() cur.close() this_data={"id":res[0],"name":res[1],"back_time":time_list} return this_data
def login2(): APPID = 'wxe9f8ea2b5c9b1f26' SECRET = 'bd9151ef9d8d4baeed692e67cec9b16b' # 获取appid和secret data = request.get_json() # 拿到微信小程序发送code信息 JSCODE = data["code"] url = 'https://api.weixin.qq.com/sns/jscode2session?appid=' + APPID + '&secret=' + SECRET + '&js_code=' + JSCODE + '&grant_type=authorization_code' # 腾讯的ap print(url) res = requests.get(url).json() print(type(res)) if 'openid' in res.keys(): openid = res["openid"] con = None cur = None try: con = get_connection() cur = con.cursor() sql = 'INSERT INTO `user` (id) VALUES (%s)' cur.execute(sql, openid) con.commit() session_key = res["session_key"] # 访问腾讯给的接口用code换取openid和session—— sessionid = uuid.uuid1() # 随机生产sessionid作为key value = '"openid":"{}","session_key":"{}"'.format( openid, session_key) # openid和session_key作为value conn = redis.StrictRedis(host='120.79.154.232', port=6379) # 连接redis数据库ps这里我是试用我自己的来测试 conn.hset("3rd_sessionid", sessionid, value) # 数据传入数据库 return jsonify({'code': 1, 'msg': '获取成功', 'data': sessionid}) except Exception as e: con.rollback() raise e finally: if con is not None: cur.close() con.close() else: return jsonify({'code': 0, 'msg': '获取失败', 'data': None})
def get(tag): conn = get_connection() cur = conn.cursor() sql = 'select url,content,title,collection_num from for_something where tag_id=%s' cur.execute(sql, tag) res = cur.fetchall() print(res) data = [] for i in res: list = { "url": i[0], "content": i[1], "title": i[2], "collection_num": i[3] } data.append(list) cur.close() conn.close() return data
def get_all_news(): conn = None cur = None conn = get_connection() cur = conn.cursor() sql = 'select * from article' data=[] content={} try: cur.execute(sql) res=cur.fetchall() for i in res: print(i) content={"id":i[0],"title":i[1],"url_address":i[2],"content":i[3]} data.append(content) except Exception as e: conn.rollback() finally: conn.close() cur.close() return data
import app, time while True: try: app.get_connection() app.query_db("SELECT 1") print("successfully connected to DB") break except: print("couldn't connect to DB, waiting...") time.sleep(1) time.sleep(1)
def read_transformed_sales() -> List[TransformedPropertySale]: table_name = config['storage']['table']['transformed'] logging.debug( "Reading all transformed property sales from '{}'".format(table_name)) sales = postgres.read_all(get_connection(), table_name) return [__create_transformed_sale_from_db(db_tuple) for db_tuple in sales]