def multi_insert_mysql_data(self, engine, data_dict, logger):
# 连接引擎
# conn = engine.connect()
# 传递参数并执行语句
try:
# 绑定引擎
metadata = MetaData(engine)
# 连接数据表
# print("all_insert_data_dict:",data_dict)
new_dict = {}
for data_list in data_dict:
for table, data in data_list.items():
tb_bdm_employee = Table(table, metadata, autoload=True)
ins = tb_bdm_employee.insert()
new_dict[ins] = data
print('new_dict', new_dict)
with engine.begin() as conn:
# conn = engine.begin()
for ins, data in new_dict.items():
result = conn.execute(ins, **data)
return result.lastrowid
except Exception as e:
file_path = os.path.join(
sys.path[0], "logs", "%s.log" % datetime.datetime.strftime(
datetime.datetime.now(), "%Y_%m_%d"))
log = logger(filename=file_path)
log.removeHandler(log.handlers)
log.info(e.__str__())
traceback.print_exc()
return False
def insert_mysql_data(self, engine, table_name, data_dict, logger):
# sql_comment = "insert into {table_name} {filed_list} values {data_tuple}".format(
# table_name=table_name,filed_list=filed_list, data_tuple=data_tuple)
# sql_comment = "insert into tb_bdm_employee 'EmployeeID' values 5"
# # print(sql_comment)
# cursor = session.execute(sql_comment)
# session.commit()
# result = cursor.lastrowid
# print(cursor.lastrowid)
try:
# 绑定引擎
metadata = MetaData(engine)
# 连接数据表
tb_bdm_employee = Table(table_name, metadata, autoload=True)
# 连接引擎
conn = engine.connect()
ins = tb_bdm_employee.insert()
# 传递参数并执行语句
result = conn.execute(ins, **data_dict)
return result.lastrowid
except Exception as e:
traceback.print_exc()
file_path = os.path.join(
sys.path[0], "logs", "%s.log" % datetime.datetime.strftime(
datetime.datetime.now(), "%Y_%m_%d"))
log = logger(filename=file_path)
log.removeHandler(log.handlers)
log.info(e.__str__())
traceback.print_exc()
return None
def update_mysql_data(self, engine, schema, table_name, data_dict, where,
logger):
# sql_comment = 'UPDATE %s SET ' % table_name + ','.join(['%s=%r' % (k, data_dict[k]) for k in data_dict]) + ' WHERE %s=%r;' % (where[0], where[1])
# cursor = session.execute(sql_comment)
# session.commit()
# result = cursor.lastrowid
# print("cursor",cursor)
# print("result",result)
# print(cursor.lastrowid)
try:
# 绑定引擎
metadata = MetaData(engine)
# 连接数据表
tb_bdm_employee = Table(table_name, metadata, autoload=True)
# 连接引擎
conn = engine.connect()
ins = tb_bdm_employee.update().where(
schema.Column(where[0]) == where[1]).values(
**data_dict
) # table.update().where(table.c.id==7).values(name='foo')
# 传递参数并执行语句
result = conn.execute(ins)
return result
except Exception as e:
print("error_update_dict:", data_dict)
file_path = os.path.join(
sys.path[0], "logs", "%s.log" % datetime.datetime.strftime(
datetime.datetime.now(), "%Y_%m_%d"))
log = logger(filename=file_path)
log.removeHandler(log.handlers)
log.info(e.__str__())
traceback.print_exc()
return None
def insert_sqlserver_data(self, engine, table_name, data_dict):
# 绑定引擎
metadata = MetaData(engine)
# 连接数据表
tb_bdm_employee = Table(table_name, metadata, autoload=True)
# 连接引擎
conn = engine.connect()
ins = tb_bdm_employee.insert()
# 传递参数并执行语句
result = conn.execute(ins, **data_dict)
return result
def update_sqlserver_data(self, engine, table_name, data_dict, where):
metadata = MetaData(engine)
# 连接数据表
tb_bdm_employee = Table(table_name, metadata, autoload=True)
# 连接引擎
conn = engine.connect()
ins = tb_bdm_employee.update().where(
schema.Column(where[0]) == where[1]).values(
**data_dict
) # table.update().where(table.c.id==7).values(name='foo')
# 传递参数并执行语句
result = conn.execute(ins)
return result
def get_metadata(filepath):
with open(filepath, "r", encoding="utf8") as fp:
for line_number, line in enumerate(fp.readlines()):
trimmed_line = line.lstrip()
indent = len(line) - len(trimmed_line)
if len(trimmed_line) == 0:
continue
if trimmed_line[0] == '#':
continue
metadata = MetaData(indent, line_number, filepath, trimmed_line)
yield metadata
def seed_tracks():
with open(TRACK_DATA_PATH, 'rb') as csv_file:
reader = csv.DictReader(csv_file, delimiter=';')
tracks = []
meta_data = []
for data in reader:
attrs = normalize_attrs(data)
track_attrs = slice_attrs(attrs, TRACK_ATTRIBUTES)
meta_attrs = slice_attrs(attrs, META_ATTRIBUTES)
meta_attrs['artist_hotness'] = meta_attrs.pop('artist_hotttnesss')
meta_attrs['song_hotness'] = meta_attrs.pop('song_hotttnesss')
tracks.append(Track(**track_attrs))
meta_data.append(MetaData(**meta_attrs))
db.session.add_all(tracks)
db.session.commit()
for i, track in enumerate(tracks):
meta_data[i].track_id = track.id
db.session.add_all(meta_data)
db.session.commit()
def update_mysql_data(self, engine, schema, table_name, data_dict, where, logger):
try:
# 绑定引擎
metadata = MetaData(engine)
# 连接数据表
tb_bdm_employee = Table(table_name, metadata, autoload=True)
# address_table = Table('address', metadata, autoload=True)
# 连接引擎
conn = engine.connect()
# print('where', where)
# print('data_dict', data_dict)
ins = tb_bdm_employee.update().where(schema.Column(where[0]) == where[1]).values(
**data_dict) # table.update().where(table.c.id==7).values(name='foo')
# 传递参数并执行语句
result = conn.execute(ins)
return result
except Exception as e:
file_path = os.path.join(sys.path[0], "logs",
"%s.log" % datetime.datetime.strftime(datetime.datetime.now(), "%Y_%m_%d"))
log = logger(filename=file_path)
log.removeHandler(log.handlers)
log.info(e.__str__())
traceback.print_exc()
return None