系统环境:windows 64 位
python版本:2.7
Maxcomputer(原ODPS)
----- 1、安装python2.7(略过,百度一下就可以)
----- 2、安装MySQLdb模块
windows下个人习惯pip 安装 whl文件,虽然不是所有都能顺利安装,但是大部分还是可以的。
下载地址:
https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysql-python
下载到本地后,cmd进入dos环境,可以直接切换到下载目录,直接使用命令 pip install mysqlclient-1.3.13-cp27-cp27m-win_amd64.whl
或者pip-script.py install mysqlclient-1.3.13-cp27-cp27m-win_amd64.whl
编译方法:
1、创建个xxx.py
的文件,进入dos,切换到py文件路径:python xxx.py
2、使用编译工具:PyCharm、Maxcomputer Studio等工具执行
#!/usr/bin/python
# -*- coding: UTF-8 -*-
# author:zhy
import MySQLdb
##### mysql数据库配置
def exec_sql(sql):
conn=MySQLdb.connect(host='数据库地址',user='用户',passwd='密码',db=TABLE_SCHEMA,charset='utf8')
cursor = conn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
cursor.close()
conn.close()
return rows
#### schema
TABLE_SCHEMA='mysql' # mysql数据库元数据库
sys_id='s01' # ods入edw规范:表名+前缀
target_schema='odps_project' #odps对应项目名称
##### 获取mysql表名
sql='''
select table_name
from information_schema.`TABLES`
where TABLE_SCHEMA='%s'
and table_type='BASE TABLE'
'''%TABLE_SCHEMA
exec_relusts=exec_sql(sql) # sql执行结果为二维
out_comment='' # 定义输出参数
###以下并不包含所有mysql字段类型的转换
for i in exec_relusts:
TABLE_NAME = i[0]
print TABLE_NAME
sql='''
select
TABLE_NAME
,COLUMN_NAME
,case when DATA_TYPE in ('varchar','char','date','longtext','text') then 'string'
when DATA_TYPE in ('float') then 'double'
when DATA_TYPE = 'tinyint' then 'int'
when DATA_TYPE = 'timestamp' then 'datetime'
else DATA_TYPE
end as DATA_TYPE
,COLUMN_COMMENT
from information_schema.`COLUMNS`
where TABLE_SCHEMA = '%s'
and TABLE_NAME = '%s'
order by TABLE_NAME,ORDINAL_POSITION
'''%(TABLE_SCHEMA,TABLE_NAME)
rows=exec_sql(sql)
sql='''
show create table %s.%s
'''%(TABLE_SCHEMA,TABLE_NAME)
sql_exec_result= exec_sql(sql)
ddl_info0= sql_exec_result[0][1]
if ddl_info0.find('COMMENT=')>1:
ddl_table_common = ddl_info0[ddl_info0.index("COMMENT="):]
ddl_table_common= ddl_table_common.replace('=',' ')
else:
ddl_table_common="COMMENT ''"
ddl_info='CREATE TABLE IF NOT EXISTS %s.%s_%s (\n'%(target_schema,sys_id,TABLE_NAME)
for row in rows:
row3=row[3].replace('\r\n','\\r\\n')
row3=row3.replace('\n','\\n')
ddl_info=ddl_info+ str(row[1]).upper()+' '+row[2].upper()+' '+"COMMENT'"+ row3+"'\n,"
ddl_info=ddl_info+')'
ddl_info=ddl_info.replace(')',')')
ddl_info=ddl_info+'\n'+ddl_table_common+"\nPARTITIONED BY (PT STRING COMMENT '数据日期')".decode('utf-8') #如不需分区,需修改
out_comment= out_comment+'-- '+TABLE_NAME +'\n'+ddl_info +'\n;\n'
# print out_comment
# 将DDL语句输出到文件
Output_File=r'D:\data\output'+'\\'+sys_id+'_'+TABLE_SCHEMA+'.sql'
file_w=file(Output_File,"w")
file_w.write(out_comment.encode("utf-8"))
file_w.close()