9TMrKrVT

1# ref: https://stackoverflow.com/q/64247899/2144390
2
3import sys
4
5import pymysql
6import sqlalchemy as sa
7from sqlalchemy import Column, Integer, String
8from sqlalchemy.ext.declarative import declarative_base
9from sqlalchemy.orm import sessionmaker
10
11print(
12    sys.version
13)  # 3.7.5 (tags/v3.7.5:5c02a39a0b, Oct 15 2019, 00:11:34) [MSC v.1916 64 bit (AMD64)]
14print(sa.__version__)  # 1.3.19
15print(pymysql.__version__)  # 0.10.1
16
17engine = sa.create_engine("mysql+pymysql://scott:tiger@localhost:3307/mydb")
18Session = sessionmaker(bind=engine)
19session = Session()
20
21
22def call_procedure(function_name, params):
23    connection = engine.raw_connection()
24    try:
25        cursor = connection.cursor()
26        cursor.callproc(function_name, params)
27        results = list(cursor.fetchall())
28        cursor.close()
29        connection.commit()
30        return results
31    finally:
32        connection.close()
33
34
35with engine.begin() as conn:
36    sql = "DROP PROCEDURE IF EXISTS add_employee"
37    conn.execute(sa.text(sql))
38    sql = "DROP TABLE IF EXISTS employee"
39    conn.execute(sa.text(sql))
40    sql = """\
41    CREATE TABLE `employee` (
42     `id` int(11) NOT NULL AUTO_INCREMENT,
43     `name` varchar(30) DEFAULT NULL,
44     `last_name` varchar(30) DEFAULT NULL,
45     PRIMARY KEY (`id`)
46    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
47    """
48    conn.execute(sa.text(sql))
49    sql = """\
50    CREATE DEFINER=`root`@`localhost` PROCEDURE `add_employee`(IN `new_name` VARCHAR(30), IN `new_last_name` VARCHAR(30))
51       MODIFIES SQL DATA
52       SQL SECURITY INVOKER
53    BEGIN
54    INSERT INTO employee (`name`, `last_name`) VALUES (new_name, new_last_name);
55    SELECT LAST_INSERT_ID() AS new_id;
56    END
57    """
58    conn.execute(sa.text(sql))
59
60
61class Employee(declarative_base()):
62    __tablename__ = "EMPLOYEE"
63
64    id = Column(Integer, primary_key=True)
65    name = Column(String(30))
66    last_name = Column(String(30))
67
68
69result = call_procedure("add_employee", ("Bob", "Loblaw"))
70new_employee_id = result[0][0]
71print(new_employee_id)  # 1
72new_employee_object = session.query(Employee).get(new_employee_id)
73print(new_employee_object)  # <__main__.Employee object at 0x00000081DAA40A08>
74