Python을 사용하여 MySQL에서 사용자 정의 저장 프로시저 및 함수를 작성하는 방법

CREATE DATABASE testdb;

USE testdb;

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255),
  age INT,
  email VARCHAR(255)
);

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="testdb"
)

mycursor = mydb.cursor()

def create_procedure():
  sql = """
CREATE PROCEDURE get_users_by_age(
  IN min_age INT,
  IN max_age INT
)
BEGIN
  SELECT * FROM users WHERE age >= min_age AND age <= max_age;
END
  """
  mycursor.execute(sql)
  mydb.commit()

def call_procedure():
  mycursor.callproc("get_users_by_age", (18, 30))
  for result in mycursor.stored_results():
    for row in result.fetchall():
      print(row)

def create_function():
  sql = """
CREATE FUNCTION count_users_by_age(
  min_age INT,
  max_age INT
)
RETURNS INT
BEGIN
  DECLARE user_count INT;
  SELECT COUNT(*) INTO user_count FROM users WHERE age >= min_age AND age <= max_age;
  RETURN user_count;
END
  """
  mycursor.execute(sql)
  mydb.commit()

def call_function():
  mycursor.callfunc("count_users_by_age", int, (18, 30))

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="testdb"
)

mycursor = mydb.cursor()

def create_procedure():
  sql = """
CREATE PROCEDURE get_users_by_age(
  IN min_age INT,
  IN max_age INT
)
BEGIN
  SELECT * FROM users WHERE age >= min_age AND age <= max_age;
END
  """
  mycursor.execute(sql)
  mydb.commit()

def call_procedure():
  mycursor.callproc("get_users_by_age", (18, 30))
  for result in mycursor.stored_results():
    for row in result.fetchall():
      print(row)

def create_function():
  sql = """
CREATE FUNCTION count_users_by_age(
  min_age INT,
  max_age INT
)
RETURNS INT
BEGIN
  DECLARE user_count INT;
  SELECT COUNT(*) INTO user_count FROM users WHERE age >= min_age AND age <= max_age;
  RETURN user_count;
END
  """
  mycursor.execute(sql)
  mydb.commit()

def call_function():
  mycursor.callfunc("count_users_by_age", int, (18, 30))

create_procedure()
call_procedure()
create_function()
call_function()