sqlalchemy 批量插入

def batch_insert_test():

    from sqlalchemy import create_engine, Table, Column, func, String, MetaData

    import pandas as pd

    import logging

    engine = create_engine("oracle://user:pass@x.x.x.x:xxxx/?service_name=xxx.xxx.xxx", echo=True)

    # Temp list

    user_list = [{'mingzi': 'jalen1', 'nianling': '11', 'tizhong': '123'},

                 {'mingzi': 'jalen2', 'nianling': '12', 'tizhong': '124'},

                 {'mingzi': 'jalen3', 'nianling': '13', 'tizhong': '125'},

                 {'mingzi': 'jalen4', 'nianling': '14', 'tizhong': None}]

    conn = engine.connect()

    trans = conn.begin()

    try:

        metadata = MetaData()

        tb_user = Table('tb_user', metadata,

                        Column('username', String(32), default=func.sys_guid(), primary_key=True),

                        Column('age', String(64)),

                        Column('height', String(64))

                        )

        # Rename <list key> to <db column name>

        df_users = pd.DataFrame(user_list)

        rename_columns = {'mingzi': 'username', 'nianling': 'age', 'tizhong': 'height'}

        df_users = df_users.rename(index=str, columns=rename_columns).fillna('')

        users = df_users.to_dict('records')

        # Batch insert

        conn.execute(tb_user.insert(), users)

        trans.commit()

    except Exception as e:

        trans.rollback()

        logging.info('Method Exception: ' + e.message)

        logging.info(traceback.format_exc())

    finally:

        conn.close()