def save2db(items, table, schema, connect, batch_size):
"""
保存数据到mysql, hive
:param items: 保存的数据，list
:param table: 表名
:param schema: 表shema
:param connect: 数据库连接信息
:return:
"""
session = load_sqlalchemy(connect)
names = [column['name'] for column in schema]
ctypes = [column['ctype'] for column in schema]
# 构造数据格式，字符串需要加上单引号
formats = ["'%s'" if ctype in STR_TYPES else "%s" for ctype in ctypes]
names_format = u"(" + u",".join(formats) + u")"
batches = [items[i:i + batch_size] for i in range(0, len(items), batch_size)]
column_names = ','.join(names)
i = 0
for batch in batches:
batch_value = []
for row in batch:
batch_value.append(names_format % tuple(row))
sql = u"insert into {table} ({column_names}) values {values}".format(
table=table, column_names=column_names, values=u','.join([item for item in batch_value]))
session.execute(sql)
i += batch_size
session.commit()
session.close()
def init(self):
self.session = load_sqlalchemy(self.args.connect)
def construct_self_rows(self):
session = load_sqlalchemy(self.args.connect)
sql = 'desc %s' % self.args.table
rows = session.execute(sql)
rows = [row for row in rows]
return rows
def construct_self_rows(self):
session = load_sqlalchemy(self.args.connect)
sql = 'show full columns from %s' % self.args.table
rows = session.execute(sql)
return rows