python文件存储过程_python-gitlab自动同步mysql存储过程以及本地文件上传gitlab

**运行环境: Python 2.7.5 , linux **

参考文档

python-gitlab简介

pip install python-gitlab

#官方建议使用配置文件

vi .python-gitlab.cfg

[global]

default = gitlab

ssh_verify = False

timeout = 8

[gitlab]

url = http://xxxxxxxxx

private_token = xxxxxxxxx

gitlab中private_token的获取

gitlab.jpg

gitlab demo 简单使用，主要是理解api

# coding:utf-8

'''

用于上传存储过程以及本地文件到gitlab中

'''

import gitlab

def allprojects():

#######获取gitlab的所有projects###

projects = gl.projects.list(all=True)

for project in projects:

print project.name, project.id

def allgroups():

#######获取gitlab的所有group名称以及ID###

all_groups = gl.groups.list(all=True)

for group in all_groups:

print group.name, group.id

def allusers():

#######获取gitlab的所有user名称以及ID###

users = gl.users.list(all=True)

for user in users:

print user.username, user.id, user.name, user.state

def assgroup():

#######获取gitlab指定组内所有user以及project名称以及ID信息，本例中组ID为58###

gid = int(raw_input('Input the group ID: '))

group = gl.groups.get(gid)

print group.name

# members = group.members.list(all=True)

# for me in members:

# print me.username,me.id

projects = group.projects.list(all=True)

for project in projects:

print group.name, project.name

#######################################

def projectinfo():

pid = int(raw_input('Input the pid: '))

projects = gl.projects.get(pid)

return projects

def projectid():

gid = int(raw_input('Input the group ID: '))

group = gl.groups.get(gid)

repo = str(raw_input('Input your repo name: '))

project = gl.projects.get(group.name + '/' + repo)

print project.id

def assuser():

#######获取gitlab指定user###

uid = int(raw_input('Input the user ID: '))

user = gl.users.get(uid)

print user.name

if __name__ == '__main__':

## login

gl = gitlab.Gitlab.from_config('gitlab', ['.python-gitlab.cfg'])

info = {1: 'allprojects()', 2: 'allgroups()', 3: 'allusers()', 4: 'projectinfo()', 5: 'projectid()', 6: 'assuser()',

7: 'assgroup()'}

serp = '-' * 20

print '''%s

1. 列出所有的projects

2. 列出所有的groups

3. 列出所有的users

4. 根据project的ID列出project的所有信息

5. 列出指定的project ID

6. 列出指定的user

7. 列出指定的组内的信息

%s''' % (serp, serp)

num = int(raw_input('Input yout choice: '))

exec info[num]

下面进入mysql存储过程的同步，多的话不说，直接上代码; 【这个是参考别人的文档所写】

将同步后的文件写入到 /home/hadoop/shell/$db 目录，用于后续用python-gitlab上传

vi export.sh

#!/bin/bash

#set -x

#将mysql中存储过程代码直接导出为文件

dbcn="mysql -hrm-2zera5z4rl,com -udb_user -p*";

db=db_channel;

ii=0;

# 查询当前数据库中的存储过程

ct=`$dbcn -N -e " select count(1) from mysql.proc as p where 1=1 and p.db='$db' and p.type like 'P%';"`;

# 先删除目录，然后新建存放目录

rm -rf /home/hadoop/shell/$db

mkdir -p /home/hadoop/shell/$db

while true;

do

if [ $ii -lt $ct ]

then

p=$ii;

let ii++;

# 通过序号获取每个存储过程的名称

spname=`$dbcn -N -e " select p.name from mysql.proc as p where 1=1 and p.db='$db' and p.type like 'P%' limit $p,1;"`;

sleep 0;

ss=`$dbcn -N -e "

SELECT

CONCAT(

'

CREATE DEFINER=',CHAR(96),

LEFT(DEFINER, -1+LOCATE('@',DEFINER)),

CHAR(96),'@',CHAR(96),

RIGHT(DEFINER,LENGTH(DEFINER)-LOCATE('@',DEFINER)),

CHAR(96)

,' PROCEDURE ',CHAR(96),p.name ,CHAR(96),'(',p.param_list,')

', p.body ,REPEAT(CHAR(36),2)) AS sql_create

FROM mysql.proc AS p

WHERE 1=1

and p.db='$db'

and p.type LIKE 'P%'

AND p.name ='$spname'

;"

`

echo -e "$ss" > /home/hadoop/shell/$db/$spname.sql

if [ $? -ne 0 ]; then

echo 'fail'

exit 0;

else

echo 'success'

fi

# 循环结束

else

echo '_while finished';

exit 0;

fi

done

用于将存储过程以及python脚本&shell脚本上传到服务器

# coding:utf-8

'''

用于上传存储过程以及本地文件到gitlab中

'''

import gitlab

import subprocess

import json

import requests

import time

import os

def projectinfo(pid):

projects = gl.projects.get(pid)

return projects

# print projects.name, projects.http_url_to_repo

# 获得project下单个文件 README.md

def getContent(pid):

projects = projectinfo(pid)

# 获得文件

f = projects.files.get(file_path='code/shell/export.sh', ref='master')

# 第一次decode获得bytes格式的内容

content = f.decode()

# # 存到本地

with open('export.sh', 'wb') as code:

code.write(content)

def uploadeFile(pid):

projects = projectinfo(pid)

# projects.upload("export.sh", filepath="xxxxxx")

# 这个方法上传文件限制很大 ， file_path 文件名 f.content 用于生成文件内容 ;一定要用 base64

f = projects.files.create({'file_path': 'export.sh',

'branch': 'master',

'content': 'test',

'author_email': 'xxx',

'author_name': 'xxx',

'encoding': 'base64',

'commit_message': 'Create export.sh'})

# 将数据写入

f.content = open('insert_channel_basic.sql').read()

f.save(branch='master', commit_message='Update testfile')

# 【实际中用到这个方法】多文件提交参考 : https://docs.gitlab.com/ce/api/commits.html#create-a-commit-with-multiple-files-and-actions

def commit_file(projects, data):

projects.commits.create(data)

def judgeType(projects, file_path):

try:

projects.files.get(file_path=file_path, ref='master')

return True

except Exception as e:

return False

# jMap 用于生成最后的对象; path 文件路径; jsonList用于生成中间对象

def getData(path, projects, gitlab_source_dir, message):

jsonList = []

jMap = {}

jMap["branch"] = 'master'

jMap["commit_message"] = message

for file in os.listdir(path):

file_dir = os.path.join(path, file)

# 判断当前路径是文件还是目录 ,排除 pyc造成的编译问题

if os.path.isfile(file_dir) and 'pyc' not in file:

gitlab_dir = '%s%s' % (gitlab_source_dir, file)

aMap = {}

# 存在就更新， 不存在就创建

if judgeType(projects, gitlab_dir):

aMap["action"] = "update"

else:

aMap["action"] = "create"

aMap["file_path"] = gitlab_dir

aMap["content"] = open(file_dir).read()

jsonList.append(aMap)

jMap["actions"] = jsonList

return jMap

def run_it(cmd):

'''

通过python执行shell命令

:param cmd: sh /home/hadoop/shell/export.sh

'''

p = subprocess.Popen(cmd, stdout=subprocess.PIPE, shell=True,

stderr=subprocess.PIPE)

# print ('running:%s' % cmd)

out, err = p.communicate()

if p.returncode != 0:

print("Non zero exit code:%s executing: %s \nerr course ---> %s" % (p.returncode, cmd, err))

# 如果出错直接告警

raise RuntimeError('导出存储过程失败')

if __name__ == '__main__':

# 用于钉钉告警

url = "xxxx"

header = {

"Content-Type": "application/json",

"charset": "utf-8"

}

## login

gl = gitlab.Gitlab.from_config('gitlab', ['.python-gitlab.cfg'])

# 用于将gitlab上面文件写入本地

# getContent(87)

# 1. 将mysql存储过程通过shell脚本写入到指定目录

try:

cmd = 'sh /home/hadoop/shell/export.sh'

path = '/home/hadoop/shell/db_channel'

pid = 92

run_it(cmd)

# 获取对应project 对象

projects = projectinfo(pid)

# 将目录下的文件循环读取，然后批量提交到gitlab中

# dir /home/hadoop/shell/db_channel

# 用于存放拼接好的对象, 如果文件最开始不存在，用update 会报错

jsonList = []

jMap = {}

jMap["branch"] = 'master'

jMap["commit_message"] = 'update production sql'

for file in os.listdir(path):

file_dir = os.path.join(path, file)

gitlab_dir = '%s%s' % ("code/production/", file)

aMap = {}

# 存在就更新， 不存在就创建

if judgeType(projects, gitlab_dir):

aMap["action"] = "update"

else:

aMap["action"] = "create"

aMap["file_path"] = gitlab_dir

aMap["content"] = open(file_dir).read()

jsonList.append(aMap)

jMap["actions"] = jsonList

# 用于存储过程的同步

commit_file(projects, jMap)

# 2. 用于shell脚本的同步

pathShell = "/home/hadoop/shell"

gitlab_source_dirShell = "code/shell/"

message_shell = "update shell file"

shell_data = getData(pathShell, projects, gitlab_source_dirShell, message_shell)

commit_file(projects, shell_data)

# 3. 用于上传python 脚本， 如果之后有新的路径，只需要添加进list中

pythonList = ['/home/hadoop/py']

for pathPython in pythonList:

gitlab_source_dirPython = "code/Python/"

message_python = "update python file"

python_data = getData(pathPython, projects, gitlab_source_dirPython, message_python)

commit_file(projects, python_data)

except Exception as e:

nowTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))

data = {

"msgtype": "text",

"text": {

"content": "【Fail:gitlab自动同步失败】:【" + nowTime + "】\n" + "【错误原因】:\n" + str(e)

}

}

sendData = json.dumps(data).encode("utf-8")

requests.post(url=url, data=sendData, headers=header)