使用sqlalchemy往已有的数据库表插入数据

网上使用sqlalchemy进行数据库操作的，大部分都是新建表进行增删改查，
而在工作中，经常遇到的是，项目已经运行很久了，表也都建立了，现在产品经理需要你插入一些别的地方获取的数据，而数据一般是保存在ｅｘｃｅｌ中，需要将ｅｘｅｃｌ中的每行记录，保存到数据中，并自动执行某个接口的调用，此代码是执行了generatebettle()，
下面主要介绍如何将数据通过ＯＲＭ保存到已有的数据库中，且是同时保存两个相关联的表，第二张表的apply_id取决于第一张表的id。

# -*- coding:utf-8 -*-
import xlrd
import json
from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import sessionmaker
import requests
import time
import pymysql

class ExcelData():
'''
这个类是将ｅｘｃｅｌ中的数据取出来保存为列表套字典的格式，字典的ｋｅｙ就是ｅｘｃｅｌ每列第一行的字段
'''
    def __init__(self,data_path,sheetname):
        self.data_path = data_path
        self.sheetname = sheetname
        self.data = xlrd.open_workbook(self.data_path)
        self.table = self.data.sheet_by_name(self.sheetname)
        self.keys = self.table.row_values(0)
        self.rowNum = self.table.nrows
        self.colNum = self.table.ncols

    def readExcel(self):
        datas = []
        for i in range(1,self.rowNum):
            sheet_data = {}
            for j in range(self.colNum):
                sheet_data[self.keys[j]] = self.table.row_values(i)[j]
            datas.append(sheet_data)
        return datas


def get_apply_id(resource_name):
    db = pymysql.connect("127.0.0.1", "root", "123456", "rc1")
    cursor1 = db.cursor()
    try:
        cursor1.execute("select id from apply_rescdetail where resource_type='Job' and resource_name='%s';"%(resource_name))
        value = cursor1.fetchone()
        apply_id = value[0]
        db.commit()
        return apply_id
    except Exception as e:
        print('error:', e)
        db.rollback()
    cursor1.close()
    db.close()

def get_approved_id(resource_name):
    db = pymysql.connect("127.0.0.1", "root", "123456", "rc1")
    cursor2 = db.cursor()
    try:
        cursor2.execute("select id from approvedresc_rescdetail where resource_type='Job' and resource_name='%s';"%(resource_name))
        value = cursor2.fetchone()
        approved_id = value[0]
        db.commit()
        return approved_id
    except Exception as e:
        print('error:', e)
        db.rollback()
    cursor2.close()
    db.close()

mysql_conn_str = "mysql+pymysql://root:123456@127.0.0.1:3306/rc1"
engine = create_engine(mysql_conn_str)
Base = automap_base()
Base.prepare(engine, reflect=True)
Base.classes.keys()
Session = sessionmaker(bind=engine)
session = Session()

apply_rescdetail = Base.classes.apply_rescdetail
approvedresc_rescdetail = Base.classes.approvedresc_rescdetail

class insertDataToMysql():
    def __init__(self,data):
        self.apply_type = 'new'
        self.resource_type = data['resource_type']
        self.resource_name = data['resource_name']
        self.unique_token = assemble_unique_token(data)
        self.resc_status = 'Approved'
        self.apply_by = data['apply_by']
        self.current_approver = 'NA'
        self.next_approver = 'NA'
        self.create_time = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
        self.update_time = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
        self.resc_metadata = json.loads(data['resc_metadata'])

    def insert_data_to_apply(self):
        session.add(apply_rescdetail(apply_type=self.apply_type, resource_type=self.resource_type, resource_name=self.resource_name, unique_token=self.unique_token, resc_status='Approved',
                                     apply_by=self.apply_by,current_approver='NA', next_approver='NA',create_time=self.create_time,
                                     update_time=self.update_time,resc_metadata=self.resc_metadata)
                        )
        session.commit()

    def insert_data_to_approve(self):
        Session = sessionmaker(bind=engine)
        session = Session()
        applyid = get_apply_id(self.resource_name)
        session.add(approvedresc_rescdetail(bettle_id='-1000', apply_by=self.apply_by, apply_id=applyid,
                                             resource_type=self.resource_type, resource_name=self.resource_name,
                                             unique_token=self.unique_token,
                                             create_time=self.create_time, update_time=self.update_time,
                                             resc_metadata=self.resc_metadata)
                    )
        session.commit()


def generatebettle(resc_ids,resc_types,apply_by):
    url = 'http://127.0.0.1:8000/lts/rc/approvedresc/'
    headers = {
    'Accept': 'application/json, text/plain, */*',
    'Accept-Encoding': 'gzip, deflate, br',
    'Accept-Language': 'zh-CN,zh;q=0.9',
    'Connection': 'keep-alive',
    'Content-Length': '55',
    'Content-Type': 'application/x-www-form-urlencoded',
    'Cookie': 'VQCUPl9Wvw',
    'Host': '127.0.0.1:8000',
    'Origin': 'http://127.0.0.1:8000',
    'Referer': 'http://127.0.0.1:8000/view/call-bettle',
    'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36'
    }

    params = {'resc_id':resc_ids,'resc_type':resc_types,'apply_by':apply_by}

    requests.post(url,data = params,headers=headers)

def get_bettle_ids(datas):
    bettle_group = {}
    for data in datas:
        wms = insertDataToMysql(data)
        wms.insert_data_to_apply()
        wms.insert_data_to_approve()
       
        resc_metadata = json.loads(data['resc_metadata'])
        job_group = resc_metadata['job_group']
        job_name = resc_metadata['job_name']
        job_id = get_approved_id(job_name)
        if job_group in bettle_group:
            bettle_group[job_group].append(job_id)
        else:
            bettle_group[job_group] = [job_id]
    with open('/home/xingguo/bettle_group.txt', 'a') as f:
        bettle_group_txt = json.dumps(bettle_group)
        f.write(bettle_group_txt)
    return bettle_group


if __name__ == '__main__':
    data_path = "/home/xingguo/wmc_job.xlsx"
    sheetname = "Sheet1"
    get_data = ExcelData(data_path,sheetname)
    datas = get_data.readExcel()
    print(datas)
　　 #　　　datas = [{
    #     'resource_type': 'Job',
    #     'resource_name': 'wms10',
    #     'apply_by': 'zhang001',
    #     'resc_metadata':'{"job_name": "wms10",}
    bettle_group = get_bettle_ids(datas)
    for job_group,job_id in bettle_group.items():
        generatebettle(job_id, 'Job', 'zhangxing')