def send_index(ret): with open('./templates/index.html', encoding='utf8') as f: """"open打开的路径是相对web_server来确定的""" content = f.read() custom_msg = '' conn = Connect(host='localhost', port=3306, user='******', password='******', database='stock_db', charset='utf8') cursor = conn.cursor() cursor.execute('select * from info;') data = cursor.fetchall() cursor.close() conn.close() html_template = """ <tr> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td> <input type="button" value="添加" id="toAdd" name="toAdd" systemIdVaule="%s"> </td> </tr>""" for item in data: custom_msg += html_template % (item + (item[1], )) return re.sub(r'\{%content%\}', custom_msg, content)
def send_center(ret): with open('./templates/center.html', encoding='utf8') as f: content = f.read() custom_msg = '' conn = Connect(host='localhost', port=3306, user='******', password='******', database='stock_db', charset='utf8') cursor = conn.cursor() cursor.execute('select f.id, i.code, i.short, i.chg, i.turnover, i.price, i.highs, f.note_info from info as i inner join focus as f on i.id = f.info_id;') data = cursor.fetchall() cursor.close() conn.close() html_template = """ <tr> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td> <a type="button" class="btn btn-default btn-xs" href="/update/%s.html"> <span class="glyphicon glyphicon-star" aria-hidden="true"></span> 修改 </a> </td> <td> <input type="button" value="删除" id="toDel" name="toDel" systemIdVaule="%s"> </td> </tr> """ for item in data: custom_msg += html_template % (item[1:] + (item[0], item[0])) return re.sub(r'\{%content%\}', custom_msg, content)
class mysqlPipeLine(object): conn = None cursor = None def open_spider(self, spider): print('开始爬虫并连接数据库') self.conn = Connect(host='127.0.0.1', port=3306, user='******', password='******', db='my_django_project_2', charset='utf8') self.cursor = self.conn.cursor() def process_item(self, item, spider): img_src = item['img_src'] img_path = img_src.split('/')[-1] sql = 'insert into pic_4k(img_name,img_size,img_path,img_cls) values("%s","%s","%s","%s")' % ( item['img_name'], item['img_size'], img_path, '4k动物') try: self.cursor.execute(sql) self.conn.commit() except Exception as e: print('向数据库中插入数据异常:', e) self.conn.rollback() return item def close_spider(self, spider): print('结束爬虫并关闭数据库连接') self.cursor.close() self.conn.close()
def stock(t, c, v=400, z=0.0): stk = PluginStock() stk.set_param(t, c, v, z) results = stk.anyl_dadan_zhanbi() connection = Connect(host='103.235.232.114', port=3306, user='******', password='******', db='stock', charset='utf8mb4', cursorclass=cursors.DictCursor) cursor = connection.cursor() sql = "INSERT INTO rizhanbi(code,zhanbi,timedate) values(%s,%s,%s)" cursor.executemany(sql, results) connection.commit() connection.close() # if result and (not len(result)==0): # r = redis.Redis(host='103.235.232.114', port=6379, decode_responses=True,password='******') # r.lpush(t,c) # @app.task(base=CallbackTask) # def multiply(x,y): # return x * y
def add(match): code = match.group(1) print('code:', code) conn = Connect(host='localhost', port=3306, database='stock_db', user='******', password='******', charset='utf8') cur = conn.cursor() sql_str = ''' select * from focus where info_id in (select id from info where code = %s); ''' cur.execute(sql_str, (code, )) ret = cur.fetchone() print('ret:', ret) if ret: body = '添加过' else: sql_str = '''insert into focus (info_id) select id from info where code = %s; ''' cur.execute(sql_str, (code, )) conn.commit() body = '成功' cur.connection conn.close() return body
def insert_whmcs_ipv4(insert: [(IPv4Address, str, IPv4Address, int)], interface: str, db: Connect, debug: bool = False, verbose: bool = False): """ This function insert given IPs and MACs to WHMCS :param insert: The list of IPs, MACs to insert :type insert: [(IPv4Address, str, IPv4Address, int)] :param interface: The interface of IPs :type interface: str :param db: The database connection of WHMCS :type db: pymysql.Connect :param debug: Disable commit on database :type debug: bool :param verbose: Print actions on database :type verbose: bool """ cursor = db.cursor() # Get gateway gateway = insert[0][0] del insert[0] # Get vlan if given if interface[:4] == "vlan": try: vlan = int(interface[4:]) except ValueError: raise ValueError("Invalid vlan !") else: vlan = "null" # For every IP to insert for i in insert: if i[1]: cmd = f"INSERT INTO mg_proxmox_addon_ip (ip, type, mac_address, subnet_mask, cidr, sid, gateway, tag) " \ f"VALUES ('{i[0]}', 'IPv4', '{i[1]}', '{i[2]}', {i[3]}, 0, '{gateway}', {vlan})" try: cursor.execute(cmd) except Exception as e: print(cmd, file=stderr) raise e if debug or verbose: print(cmd) cursor.close() # Commit to the DB if not debug: try: print("Commit to DB...") db.commit() except Exception as e: raise e else: print("Commited to DB")
def center(match): # 接着模板文件地址 path = './templates/center.html' # 读取模板文件 with open(path, 'r') as f: content = f.read() # 设置显示的数据,将原来的固定数据替换成占位符 row_str = """ <tr> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td> <a type="button" class="btn btn-default btn-xs" href="/update/%s.html"> <span class="glyphicon glyphicon-star" aria-hidden="true"></span> 修改 </a> </td> <td> <input type="button" value="删除" id="toDel" name="toDel" systemidvaule="%s"> </td> </tr> """ # 连接数据库 # 1.连接数据 # 创建Connection连接 conn = Connect(host='localhost', port=3306, database='stock_db', user='******', password='******', charset='utf8') # 获得Cursor对象 cur = conn.cursor() # 2 准备执行的 sql 语句字符串 sql_str = """ select info.code,info.short,info.chg,info.turnover,info.price,info.highs,focus.note_info from info inner join focus where info.id = focus.info_id;""" # 执行sql cur.execute(sql_str) # 获取所有的结果 sql_relust = cur.fetchall() # 遍历结果并拼接数据 all_data = "" for t in sql_relust: #根据格式字符串中的每项空白,将数据从元组中取出并添加到格式字符串中 all_data += row_str % (t[0], t[1], t[2], t[3], t[4], t[5], t[6], t[0], t[0]) # 3. 关闭游标和数据库 cur.close() conn.close() # 替换模板中的占位符 content = re.sub(r'\{%content%\}', all_data, content) return content
def index(match): # 接着模板文件地址 path = './templates/index.html' # 读取模板文件 with open(path, 'r') as f: content = f.read() # 设置显示的数据,将原来的固定数据替换成占位符 row_str = """ <tr> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td> <input type="button" value="添加" id="toAdd" name="toAdd" systemidvaule="%s"> </td> </tr> """ # 连接数据库 # 1.连接数据 # 创建Connection连接 conn = Connect(host='localhost', port=3306, database='stock_db', user='******', password='******', charset='utf8') # 获得Cursor对象 cur = conn.cursor() # 2 准备执行的 sql 语句字符串 sql_str = """ select * from info;""" # 执行sql cur.execute(sql_str) # 获取所有的结果 sql_relust = cur.fetchall() # 遍历结果并拼接数据 all_data = "" for t in sql_relust: #根据格式字符串中的每项空白,将数据从元组中取出并添加到格式字符串中 all_data += row_str % (t[0], t[1], t[2], t[3], t[4], t[5], t[6], t[7], t[1]) # 3. 关闭游标和数据库 cur.close() conn.close() # 替换模板中的占位符 content = re.sub(r'\{%content%\}', all_data, content) return content
def db_cursor(commit=False): con = None try: con = Connect(host=DBHOST, user=DBUSER, password=DBPASS, db=DB) with con.cursor() as cur: yield cur if commit: con.commit() finally: if con: con.close()
def getData(sql): conn = Connect(host="localhost", port=3306, user="******", password="******", database="visual_db_2017", charset="utf8") cur = conn.cursor() cur.execute(sql) data = cur.fetchall() conn.close() return data
def index(): # 读取模板文件 with open('templates/index.html', 'r', encoding='utf8') as file: content = file.read() # 下面的开发思想使用的是 前后端不分离思想 # 首页固定数据 # 先将 标签中的固定数据变成占位符 row_str = """ <tr> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td> <input type="button" value="添加" id="toAdd" name="toAdd" systemidvaule="%s"> </td> </tr> """ # 连接数据库 db_connect = Connect(host='localhost', port=3306, user='******', password='******', database='stock_db', charset='utf8') cur = db_connect.cursor() sql_str = ''' select * from info ''' cur.execute(sql_str) result = cur.fetchall() cur.close() db_connect.close() # 遍历结果,并将结果添加到格式字符串中 all_data = '' for t in result: all_data += row_str % (t[0], t[1], t[2], t[3], t[4], t[5], t[6], t[7], t[1]) # 字符串是一个不可变对象,所以要接收一下替换后的数据并返回 content = content.replace('{%content%}', all_data) # 将模板文件中的内容返回 return content
def send_update(ret): with open('./templates/update.html', encoding='utf8') as f: """"open打开的路径是相对web_server来确定的""" stock_id = int(ret.group(1)) content = f.read() conn = Connect(host='localhost', port=3306, user='******', password='******', database='stock_db', charset='utf8') cursor = conn.cursor() cursor.execute('select * from focus where id = %d;' % stock_id) data = cursor.fetchone() cursor.close() conn.close() content = re.sub(r'\{%code%\}', str(data[0]), content) # sub中用作替换的必须是字符串,否则会报错 content = re.sub(r'\{%note_info%\}', str(data[1]), content) return content
def del_foucs(ret): """添加关注 """ focus_id = ret.group(1) # 关注id conn = Connect(host='localhost', port=3306, user='******', password='******', database='stock_db', charset='utf8') cursor = conn.cursor() res = cursor.execute('delete from focus where id = %d' % int(focus_id)) # execute 只能对字符串进行拼接 if res: msg = '删除成功!' else: msg = '删除失败!' conn.commit() cursor.close() conn.close() return msg
def get_whmcs_mac(db: Connect) -> [str]: """ Get all MACs of WHMCS :param db: The database connection of WHMCS :type db: pymysql.Connect :return: The list of WHMCS MAC :rtype: [str] """ cursor = db.cursor() cursor.execute("SELECT mac_address FROM mg_proxmox_addon_ip") macs = [i[0] for i in cursor.fetchall()] cursor.close() return macs
def delete(match): code = match.group(1) conn = Connect(host='localhost', port=3306, database='stock_db', user='******', password='******', charset='utf8') cur = conn.cursor() sql_str = ''' delete from focus where info_id = (select id from info where code = %s)''' cur.execute(sql_str, (code, )) conn.commit() cur.close() conn.close() return 'OK'
def get_whmcs_ipv4(db: Connect) -> [IPv4Address]: """ Get all IPs of WHMCS :param db: The database connection of WHMCS :type db: pymysql.Connect :return: The list of WHMCS IP :rtype: [IPv4Address] """ cursor = db.cursor() cursor.execute("SELECT ip FROM mg_proxmox_addon_ip") ips = [IPv4Address(i[0]) for i in cursor.fetchall()] cursor.close() return ips
def connection(): from helpers import abort cnx = None try: cnx = Connect(host='timbess.net', user='******', password='******', database='test') curr = cnx.cursor(cursor=DictCursor) return cnx, curr except MySQLError as e: if cnx is not None: cnx.close() current_app.logger.exception(e) abort(500, message='Failed to connect to database')
class QcwyMysqlPipeline(object): def __init__(self, host, database, port, user, password): self.host = host self.database = database self.table_name = '51job' self.port = port self.user = user self.password = password @classmethod def from_crawler(cls, crawler): return cls( host=crawler.settings.get('MYSQL_HOST'), database=crawler.settings.get('MYSQL_DATABASE'), port=crawler.settings.get('MYSQL_PORT'), user=crawler.settings.get('MYSQL_USER'), password=crawler.settings.get('MYSQL_PASSWORD'), ) def open_spider(self, spider): # print(self.table_name) self.db = Connect( host=self.host, database=self.database, port=self.port, user=self.user, password=self.password, charset='utf8', ) self.cursor = self.db.cursor() def process_item(self, item, spider): data = dict(item) keys = ','.join(data.keys()) values = ','.join(['\"%s\"' % i for i in data.values()]) sql = 'insert into %s(%s) values (%s)' % (self.table_name, keys, values) try: self.cursor.execute(sql) self.db.commit() except Exception as e: self.db.rollback() print('异常错误是:', e) def close_spider(self, spider): self.cursor.close() self.db.close()
def update(ret): stock_id = ret.group(1) """由于浏览器会将url中的特殊字符进行url编码,因此在解析时需要对其进行解码码""" note = urllib.parse.unquote(ret.group(2), encoding='utf8') conn = Connect(host='localhost', port=3306, user='******', password='******', database='stock_db', charset='utf8') cursor = conn.cursor() sql = 'update focus set note_info = "%s" where id = %s;' % (note, stock_id) print(sql) res = cursor.execute(sql) if res: msg = '修改成功!' else: msg = '修改失败!' cursor.close() conn.commit() conn.close() return msg
class db(object): def __init__(self): self.conn = Connect(host='mysql', user='******', password='******', database='visual_db_2017', port=3306) def get_cur(self, sql): """ 向数据库查询sql语句 :param sql: 需要查询的sql语法 :string :return: 返回结果游标 """ cur = self.conn.cursor() cur.execute(sql) self.conn.close() return cur
class ConnMysql(object): # ===================数据库连接=================== def __init__(self, host='localhost', port=3306, user='******', password='******', database="allip", charset='utf8'): self.lock = Lock() try: self.conn = Connect(host=host, port=port, user=user, password=password, database=database, charset=charset) self.cursor = self.conn.cursor() except Exception as e: print("数据库连接异常:{}".format(e)) print("请检查您是否使用了默认的数据库参数...") else: pass finally: pass # ===================保存数据=================== def exe(self, sql_language): try: # 数据库插入数据 self.lock.acquire() # 获取锁,多线程应用. self.cursor.execute(sql_language) self.conn.commit() except Exception as e: self.conn.rollback() print("执行sql语句失败...{}".format(e)) else: pass finally: self.lock.release() # 释放锁 def close(self): self.cursor.close() self.conn.close()
def add_foucs(ret): """添加关注 """ stock_code = ret.group(1) # 股票代号 conn = Connect(host='localhost', port=3306, user='******', password='******', database='stock_db', charset='utf8') cursor = conn.cursor() # 判断是否存在此股票 cursor.execute('select 1 from info where code = %s limit 1;', (stock_code,)) if not cursor.fetchone(): return '不存在对应的股票信息!' # 判断是否已添加关注 cursor.execute('select * from focus inner join info on focus.info_id = info.id having code = %s;', (stock_code,)) if cursor.fetchone(): return '此股票已在关注列表,请勿重复添加!' # 若未关注,则添加关注 cursor.execute('insert into focus (info_id) select id from info where code = %s;', (stock_code,)) conn.commit() cursor.close() conn.close() return '添加成功!'
def update(match): path = './templates/update.html' code = match.group(1) with open(path, 'r') as f: file_content = f.read() conn = Connect(host='localhost', port=3306, database='stock_db', user='******', password='******', charset='utf8') cur = conn.cursor() sql_str = '''select note_info from focus where info_id = (select id from info where code = %s); ''' cur.execute(sql_str, (code, )) sql_result = cur.fetchone() file_content = re.sub(r'\{%code%\}', code, file_content) file_content = re.sub(r'\{%note_info%\}', sql_result[0], file_content) return file_content
def update_commit(match): code = match.group(1) note_info = match.group(2) note_info = unquote(note_info) conn = Connect(host='localhost', port=3306, database='stock_db', user='******', password='******', charset='utf8') cur = conn.cursor() sql_str = ''' update focus set note_info = %s where info_id = (select id from info where code = %s);''' cur.execute(sql_str, (note_info, code)) conn.commit() cur.close() conn.close() return 'OK'
class PythonJobMySqlPipeline(object): def open_spider(self, spider): print("爬虫开始了mysql------------------------------------------") self.client = Connect(host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PASSWORD, database=MYSQL_DBNAME, port=MYSQL_PORT, charset='utf8') self.sursor = self.client.cursor() def close_spider(self, spider): #关闭数据库,释放资源 self.sursor.close() self.client.close() def process_item(self, item, spider): s = dict(item) parms = [ s["url"], s["title"], s["location"], s["company_name"], s["salary"], s["company_info"], s["experience"], s["job_info"], s["address"], s["crawled"], s["spider"], ] sql = "INSERT INTO job_items(url,title,location,company_name,salary,company_info,experience,job_info,address,crawled,spider) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" self.sursor.execute(sql, parms) #事务提交 self.client.commit() #字典 return item
class SaveSqlPipeline(object): def process_item(self, item, spider): self.save_pics_info(item) self.save_pics_detail(item) self.conn.commit() return item def save_pics_info(self, item): url = item['image_path'][0] title = item['title'] tags = item['tags'] date = item['date'] params = [url, title, tags, date] sql = 'insert into pics_info (url,title,tags,date) values (%s,%s,%s,%s)' logging.info(sql % tuple(params)) self.cursor.execute(sql, params) def save_pics_detail(self, item): id = self.cursor.lastrowid image_urls = item['image_path'][1:] for url in image_urls: params = [id, url] sql = 'insert into pic_detail (pic_info_id,url) values (%s,%s)' logging.info(sql % tuple(params)) self.cursor.execute(sql, params) def open_spider(self, spider): self.conn = Connect( host=spider.settings.get('MYSQL_HOST'), port=spider.settings.get('MYSQL_PORT'), database=spider.settings.get('MYSQL_DATABASE'), user=spider.settings.get('MYSQL_USER'), password=spider.settings.get('MYSQL_PASSWORD'), charset=spider.settings.get('MYSQL_CHARSET'), ) self.cursor = self.conn.cursor() # spider (Spider 对象) – 被开启的spider # 可选实现,当spider被开启时,这个方法被调用。 def close_spider(self, spider): self.cursor.close() self.conn.close()
def do_sql(self, query, flag=1): '''query:查询语句 flag:标记,1:查询第一条数据,2:查询获取多条的数据''' # 读取配置文件的数据库信息,连接数据库, db_config = ReadConfig().get_data('DB', 'db_config') # 数据库基本信息 # cnn=connector.connect(**db_config) #建立连接 cnn1 = Connect(**db_config) # 获取游标 # cursor=cnn.cursor() cursor = cnn1.cursor() # 操作数据表, 执行查询语句 cursor.execute(query) # 判断flag,要获取哪些数据 if flag == 1: res = cursor.fetchone() #查询第一条数据,返回的是元祖类型 else: res = cursor.fetchall() #查询所有的数据,返回的是列表嵌套元祖类型 cnn1.close() cursor.close() return res
def remove_whmcs_ipv4(remove: [(IPv4Address, str, IPv4Address, int)], db: Connect, debug: bool = False, verbose: bool = False): """ This function remove IPv6 on the router :param remove: The list of IPs, MACs to remove :type remove: [(IPv4Address, str, IPv4Address, int)] :param db: The database connection of WHMCS :type db: pymysql.Connect :param debug: Disable commit on database :type debug: bool :param verbose: Print each command on router :type verbose: bool """ cursor = db.cursor() for i in remove: cmd = f"DELETE FROM mg_proxmox_addon_ip WHERE ip = '{i[0]}'" try: cursor.execute(cmd) except Exception as e: print(cmd, file=stderr) raise e if debug or verbose: print(cmd) cursor.close() # Commit to the DB if not debug: try: print("Commit to DB...") db.commit() except Exception as e: raise e else: print("Commited to DB")
from pymysql import Connect import pymysql import random import time db = Connect(host="localhost", port=3306, user="******", password="******", database="RECOMMENDATION_SYSTEM_DB", charset='utf8') cursor = db.cursor(pymysql.cursors.DictCursor) sql = """ SELECT rpbi.id as product_id, rpbi.name as product_name, rpbi.image_url as product_image, rpbi.status as product_status, rpbi.price as price, rpsi.comment_count as comment_count, rpsi.average_score as average_score, rpsi.good_count as good_count, rpsi.general_count as general_count, rpsi.poor_count as poor_count, rpsi.good_rate as good_rate, rpsi.inventory as inventory, rsi.id as shop_id, rsi.name as shop_name, rai.area_code as area_code, rai.name as area_name FROM rs_product_base_info as rpbi,
class BhavDB: def __init__(self, mysqldb='bhavdata'): try: self.__connection = Connect(host='localhost', user='******', password='', db=mysqldb, charset='utf8mb4', cursorclass=cursors.DictCursor) with self.__connection.cursor() as cursor: tables_sql = "SELECT table_name FROM information_schema.tables where table_schema = 'bhavdata';" cursor.execute(tables_sql) self._bhavcopy_tables = [ t['table_name'] for t in cursor.fetchall() ] if not path.exists("./logs"): os.mkdir("./logs") self._logger = open("./logs/bhav_db.log", "w") self._month_dict = { v.upper(): k for k, v in enumerate(calendar.month_abbr) } except OperationalError as op_err: raise DBConnectionError(op_err) def __del__(self): try: self.__connection.commit() self.__connection.close() self._logger.close() except Exception as err: pass # get the first nse bhav date in recorded history # return: date @property def the_first_date(self) -> date: with self.__connection.cursor() as cursor: sql_statement = "SELECT min(table_name) first_year_table FROM information_schema.tables where table_name like 'bhavcopy_%';" cursor.execute(sql_statement) result = cursor.fetchone() sql_statement = "select min(timestamp) the_first_date from {}".format( result['first_year_table']) cursor.execute(sql_statement) result = cursor.fetchone() return result['the_first_date'] @property def last_saved_date(self) -> date: with self.__connection.cursor() as cursor: sql_statement = "SELECT max(table_name) first_year_table FROM information_schema.tables where table_name like 'bhavcopy_%';" cursor.execute(sql_statement) result = cursor.fetchone() sql_statement = "select max(timestamp) last_saved_date from {}".format( result['first_year_table']) cursor.execute(sql_statement) result = cursor.fetchone() return result['last_saved_date'] @property def unsaved_dates_till_today(self) -> list: unsaved_dates = [] unsaved_date: date = self.last_saved_date while (unsaved_date <= date.today()): if unsaved_date.strftime("%a").upper() not in ['SAT', 'SUN']: print(unsaved_date.strftime("%d-%m-%Y")) unsaved_dates.append(unsaved_date) unsaved_date += timedelta(1) unsaved_date += timedelta(1) def _log_err(self, row: dict, err: Exception, zip_file_name: str): self._logger.write("Error: {}\n".format(str(err))) self._logger.write( "For: (Symbol: {}; Series: {}; Timestamp: {})\n".format( row['SYMBOL'], row['SERIES'], row['TIMESTAMP'])) self._logger.write("In: ({})\n".format(path.basename(zip_file_name))) self._logger.write("+" * max([ len("Record: Symbol: {}; Series: {}; Timestamp: {}".format( row['SYMBOL'], row['SERIES'], row['TIMESTAMP'])), len(str(err)), len(os.path.basename(zip_file_name)) ]) + "\n") # we will say that data by year # this is for github purposes not for production # so that we don't have to upload one humongous file every time we push updates to github # only the most recent year table is effected. # previous year tables are completely historic data so they won't every change once that year is done # PRIMARY KEY: This is on the symbol, series and timestamp fields since this combination is unique def _create_year_table(self, year: int): table_name = "bhavcopy_{}".format(year) create_table_sql = "CREATE table " + " if not exists " + table_name + "(" create_table_sql += "symbol varchar(15)," create_table_sql += "series char(2)," create_table_sql += "open DECIMAL(8,2)," create_table_sql += "high DECIMAL(8,2)," create_table_sql += "low DECIMAL(8,2)," create_table_sql += "close DECIMAL(8,2)," create_table_sql += "last DECIMAL(8,2)," create_table_sql += "prevclose DECIMAL(8,2)," create_table_sql += "tottrdqty int unsigned," create_table_sql += "tottrdval bigint unsigned," if year >= 2011: create_table_sql += "totaltrades mediumint unsigned default null," create_table_sql += "isin char(12) default null," create_table_sql += "timestamp date," create_table_sql += "primary key(symbol, series, timestamp)" create_table_sql += ");" with self.__connection.cursor() as cursor: cursor.execute(create_table_sql) def keep_only_eq_data(self): sql = "select column_name, table_name from information_schema.columns where table_schema = 'bhavdata';" with self.__connection.cursor() as cursor: cursor.execute(sql) tables = {} for row in cursor.fetchall(): if tables.get(row['table_name'], 0) == 0: columns = [] else: columns = tables[row['table_name']] if row['column_name'] != 'series': columns.append(row['column_name']) tables[row['table_name']] = columns for table in tables: print("doing {}".format(table)) cursor.execute( "CREATE TEMPORARY TABLE {table}_temp select {columns} from {table} where series = 'EQ';" .format(table=table, columns=",".join(tables[table]))) cursor.execute("drop table {};".format(table)) cursor.execute( "CREATE TABLE {table} select {columns} from {table}_temp;". format(table=table, columns=",".join(tables[table]))) cursor.execute( "ALTER TABLE {} ADD PRIMARY KEY(symbol,timestamp);".format( table)) cursor.execute("drop table {}_temp;".format(table)) print("done {}!!".format(table)) def _get_row_date(self, timestamp) -> date: time_parts = timestamp.split("-") year = int(time_parts[2]) month = int(self._month_dict[time_parts[1]]) day = int(time_parts[0]) return date(year, month, day) @property def bhav_count_by_year(self): for bhavcopy_table in self._bhavcopy_tables: with self.__connection.cursor() as cursor: cursor.execute( "select count(*) bhavcount from {}".format(bhavcopy_table)) count_result = cursor.fetchone() result = search("\d{4}", bhavcopy_table) yield result.group(), count_result['bhavcount'] def insert_bhav_row(self, row: dict, zip_file_name: str): row_date: date = self._get_row_date(row['TIMESTAMP']) bhavcopy_table = "bhavcopy_{}".format(row_date.year) field_sql_list: str = "" value_sql_list: str = "" for field_name, value in row.items(): if field_name.strip() != "": field_sql_list += field_name + ", " if field_name == 'TIMESTAMP': value_sql_list += "'" + str(row_date) + "', " else: value_sql_list += "'" + value + "', " field_sql_list = field_sql_list.strip(", ") value_sql_list = value_sql_list.strip(", ") sql_insert = "insert into {table_name} ({field_list}) values({value_list})".format( table_name=bhavcopy_table, field_list=field_sql_list, value_list=value_sql_list) with self.__connection.cursor() as cursor: try: if bhavcopy_table not in self._bhavcopy_tables: self._create_year_table(row_date.year) self._bhavcopy_tables.append(bhavcopy_table) cursor.execute(sql_insert) except IntegrityError as integrity_err: # IMPORTANT: Rather than check, we're just going to let the db fail on duplicates # and then not log the duplicate error if "(1062, \"Duplicate entry '" + row['SYMBOL'] + "-" + row[ 'SERIES'] + "-" + row_date.strftime( "%Y-%m-%d") + "\' for key 'PRIMARY'\")" in str( integrity_err): pass else: self._log_err(row, integrity_err, zip_file_name) except BadTimestampYearError as bad_timestamp_year_err: self._log_err(row, bad_timestamp_year_err, zip_file_name) except DataError as data_err: self._log_err(row, data_err, zip_file_name) except InternalError as interal_err: self._log_err(row, interal_err, zip_file_name) def insert_holiday_data(self, row: dict): try: with self.__connection.cursor() as cursor: cursor.execute( "insert into nse_holidays_temp (`holiday`, `timestamp`) values('{}', '{}')" .format(row['reason'], row['timestamp'])) except IntegrityError as integrity_err: # IMPORTANT: In the off chance that two holidays fall on the same day, # we'll just take the first and let the second fall thru if "(1062, \"Duplicate entry '{}\' for key 'PRIMARY'\")".format( row['timestamp']) in str(integrity_err): pass def holidays_by_year(self, year): with self.__connection.cursor() as cursor: cursor.execute( "select * from nse_holidays where year(timestamp) = {};". format(year)) return cursor.fetchall() @staticmethod def _extract_sql_to_bhavdata_dir(zip_file_name) -> ZipInfo: bhavcopy_sql_zip = ZipFile(zip_file_name) file_zip_info: ZipInfo = bhavcopy_sql_zip.filelist[0] print(file_zip_info.filename) bhavcopy_sql_zip.extract(file_zip_info.filename, "mariadb.bhav/data/bhavdata") bhavcopy_sql_zip.close() return file_zip_info def prepare_data_for_git(self): with self.__connection.cursor() as cursor: bhavcopy_zip_dict = { search("(bhavcopy_\d{4})", bhavcopy_sql_zip_file).groups()[0]: bhavcopy_sql_zip_file for bhavcopy_sql_zip_file in glob.glob( "bhavcopy.sql.zip/*.sql.zip") } # let's create two temp year tables - one for pre-2011 and one for post (after total trades was added) self._create_year_table(1000) self._create_year_table( 3000 ) # of course, we're assuming this prog will not be used in year 3000 :) for bhavcopy_table in self._bhavcopy_tables: print("Doing: {}".format(bhavcopy_table)) try: # create a temp 1000 year table temp_year_table = None result = search("\d{4}", bhavcopy_table) if int(result.group()) >= 2011: temp_year_table = "bhavcopy_{}".format(3000) else: temp_year_table = "bhavcopy_{}".format(1000) bhavcopy_zip = bhavcopy_zip_dict.get(bhavcopy_table, None) make_zip = False if bhavcopy_zip: file_zip_info: ZipInfo = self._extract_sql_to_bhavdata_dir( bhavcopy_zip_dict[bhavcopy_table]) cursor.execute( "truncate table {};".format(temp_year_table)) cursor.execute( "load data infile '{}' into table {};".format( file_zip_info.filename, temp_year_table)) cursor.execute("SELECT symbol, timestamp FROM " \ "(SELECT symbol, timestamp FROM {} UNION ALL SELECT symbol, timestamp FROM {}) tbl " \ "GROUP BY symbol, timestamp HAVING count(*) = 1 ORDER BY symbol, timestamp;".format(bhavcopy_table, temp_year_table)) table_diff = cursor.fetchone() if table_diff: make_zip = True else: make_zip = True if make_zip: cursor.execute( "select * into outfile '../../bhavcopy.sql.zip/{0}.sql' from {0};" .format(bhavcopy_table)) with ZipFile( "bhavcopy.sql.zip/{0}.sql.zip".format( bhavcopy_table), 'w', ZIP_DEFLATED) as sql_zip: sql_zip.write( "bhavcopy.sql.zip/{0}.sql".format( bhavcopy_table), "{}.sql".format(bhavcopy_table)) os.remove( "bhavcopy.sql.zip/{0}.sql".format(bhavcopy_table)) except InternalError as int_err: print(int_err) # drop the temp year tables cursor.execute("drop table bhavcopy_{};".format(1000)) cursor.execute("drop table bhavcopy_{};".format(3000)) # zip back-up of nse_holidays table # cursor.execute( # "select * into outfile '../../bhavcopy.sql.zip/nse_holidays.sql' from nse_holidays;") def get_data_of_git(self): with self.__connection.cursor() as cursor: try: cursor.execute("create database bhavdata;") cursor.execute("use bhavdata;") bhavcopy_sql_zip_files = glob.glob("bhavcopy.sql.zip/*.zip") for bhavcopy_sql_zip_file in bhavcopy_sql_zip_files: file_zip_info: ZipInfo = BhavDB._extract_sql_to_bhavdata_dir( bhavcopy_sql_zip_file) (table_name, year) = search("(bhavcopy_(\d{4}))", file_zip_info.filename).groups() self._create_year_table(int(year)) cursor.execute( "load data infile '{}' into table {};".format( file_zip_info.filename, table_name)) os.remove("mariadb.bhav/data/bhavdata/{}".format( file_zip_info.filename)) except ProgrammingError as prog_err: print(prog_err) @staticmethod def _is_holiday(day, cursor) -> bool: if day.strftime("%a").upper() in ['SAT', 'SUN']: return True else: cursor.execute( "select count(timestamp) holiday_count from nse_holidays_temp where timestamp = '{}'" .format(day)) # x = cursor.fetchone() return int(cursor.fetchone()['holiday_count']) == 1 @property def no_bhav_days(self): count = 0 day = self.the_first_date with self.__connection.cursor() as cursor: bhav_days = [] for bhavcopy_table in self._bhavcopy_tables: result = search("\d{4}", bhavcopy_table) cursor.execute( "select distinct(timestamp) as bhavdate from {};".format( bhavcopy_table)) for row in cursor.fetchall(): bhav_days.append(row['bhavdate']) while self.the_first_date <= day <= self.last_saved_date: print(day) if day not in bhav_days and not BhavDB._is_holiday( day, cursor): print("a real holiday") self._logger.write( day.strftime("%a-%d-%b-%Y").upper() + "\n") day = day + timedelta(1) print("x" * 30) print(count)