def create_offers(cursor: Cursor, product_id: int, offers: list):
"""
Creates the new found offers from api
:param cursor: connection cursor to DB
:param product_id: product id from DB
:param offers: list of offers that will be created
"""
o_fields = Offer.fields()
values = []
for offer in offers:
offer["product_id"] = product_id
values.append(tuple(int(offer[key]) for key in o_fields))
placeholder = ["%s" for _ in o_fields]
cursor.executemany(
"INSERT INTO `{}` ({}) VALUES ({})".format(Offer.__tablename__,
", ".join(o_fields),
", ".join(placeholder)),
values)
def update_offers(cursor: Cursor, product_id: int, offers: list):
"""
Creates query to update data in DB
:param cursor: connection cursor to DB
:param product_id: product id
:param offers: list of offers that will be updated
"""
values = []
for offer in offers:
sets = []
for key in offer:
sets.append(offer[key])
values.append(sets)
# because product_id is specific for DB it needs to be removed
placeholder = [
"{} = %s".format(field) for field in Offer.fields(("product_id", ))
]
query = """
UPDATE `{}` SET {} WHERE id = {}
""".format(Offer.__tablename__, ", ".join(placeholder),
product_id).strip()
cursor.executemany(query, values)
def setup_schema_data(cursor: pc.Cursor, queries: t.List[t.List]):
for query, params in queries:
cursor.executemany(query, params)
fCodes = soup.find("table", id="oTable").tbody.find_all("td", "bzdm") # 基金编码
result = ()
for fCode in fCodes:
result += ({"fcode": fCode.get_text()
, "fname": fCode.next_sibling.find("a").get_text()
, "NAV": fCode.next_sibling.next_sibling.get_text()
, "ACCNAV": fCode.next_sibling.next_sibling.next_sibling.get_text(),
"updatetime": datetime.now().isoformat(sep=' ', timespec="seconds")}
,)
print(result)
import pymysql
from pymysql.cursors import Cursor, SSCursor
from common.config import dbconfig
connection = pymysql.connect(**dbconfig)
cursor = Cursor(connection)
# cursor.execute("sql")
# result = cursor.fetchall()
cursor.executemany("""
insert into myfund(fcode, fname, NAV, ACCNAV, updatetime)
values(%(fcode)s,%(fname)s,%(NAV)s,%(ACCNAV)s,%(updatetime)s)
ON duplicate KEY UPDATE `updatetime`=%(updatetime)s, NAV=%(NAV)s, ACCNAV=%(ACCNAV)s
""", result)
connection.commit()
connection.close()
with open('./files/2.txt', 'rb') as f:
html = f.read().decode('utf8')
f.close()
soup = BeautifulSoup(html, 'html.parser')
f_codes = soup.find('table', id='oTable').tbody.find_all('td', 'bzdm') # 基金代码
ret = ()
for f_code in f_codes:
ret += ({
'f_code': f_code.get_text()
, 'f_name': f_code.next_sibling.find('a').get_text()
, 'nav': f_code.next_sibling.next_sibling.get_text()
, 'accnav': f_code.next_sibling.next_sibling.next_sibling.get_text()
, 'updated': datetime.now().isoformat(' ', 'seconds')
},)
print(ret)
import pymysql
from pymysql.cursors import Cursor, SSCursor
from common.config import db_config
connection = pymysql.connect(**db_config)
cursor = Cursor(connection)
cursor.executemany("""insert into fund(f_code,f_name,nav,accnav,updated)
values(%(f_code)s,%(f_name)s,%(nav)s,%(accnav)s,%(updated)s)
ON duplicate KEY UPDATE updated=%(updated)s,nav=%(nav)s,accnav=%(accnav)s""", ret)
connection.commit()
connection.close()
