Python으로 MySQL을 작동하는 방법

1. Python 데이터베이스 작업 소개

Python의 표준 데이터베이스 인터페이스는 개발자에게 데이터베이스 응용 프로그래밍 인터페이스를 제공하는 Python DB-API입니다. Python은 다양한 데이터베이스 선택 요구 사항을 충족하기 위해 광범위한 데이터베이스 인터페이스를 제공합니다. 프로젝트

• GadFly
  

• mSQL
  

• MySQL
  

• PostgreSQL
  

• Microsoft SQL Server 2000
  

• Informix
  

• Interbase
  

• Oracle
  

• Sybase…
  

Python 데이터베이스 인터페이스를 방문할 수 있습니다. 세부 지원 데이터베이스 목록을 볼 수 있는 API입니다.

다른 데이터베이스에 대해 다른 DB API 모듈을 다운로드해야 합니다. 예를 들어 Oracle 데이터베이스 및 Mysql 데이터에 액세스해야 하는 경우 Oracle 및 MySQL 데이터베이스 모듈을 다운로드해야 합니다.

DB-API는 다양한 기본 데이터베이스 시스템 및 다양한 데이터베이스 인터페이스 프로그램에 대한 일관된 액세스 인터페이스를 제공하기 위해 일련의 필수 객체 및 데이터베이스 액세스 방법을 정의합니다.

Python의 DB-API는 대부분의 데이터베이스에 대한 인터페이스를 구현합니다. 이를 사용하여 각 데이터베이스에 연결한 후 동일한 방식으로 각 데이터베이스를 작동할 수 있습니다.

Python DB-API 사용 프로세스:

• API 모듈을 소개합니다.
  

• 데이터베이스에 연결합니다.
  

• SQL 문과 저장 프로시저를 실행합니다.
  

• 데이터베이스 연결을 닫습니다.
  

2. Python은 MySQL 모듈을 작동합니다.

Python은 주로 MySQL을 작동하는 데 두 가지 방법을 사용합니다.

DB 모듈(네이티브 SQL)

• PyMySQL(python2.x/3.x 지원)
  

• MySQLdb (현재 python2만 지원합니다. 방법은
  

2.1과 유사합니다. .1 PyMySQL 설치

PyMySQL은 Python으로 작성된 MySQL 드라이버로, Python 언어를 사용하여 MySQL 데이터베이스를 운영할 수 있습니다.

• pip install PyMySQL

  
  2.2 기본 사용법

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
import pymysql
# 创建连接
conn = pymysql.connect(host="127.0.0.1", port=3306, user='zff', passwd='zff123', db='zff', charset='utf8mb4')
# 创建游标(查询数据返回为元组格式)
# cursor = conn.cursor()
# 创建游标(查询数据返回为字典格式)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 1. 执行SQL,返回受影响的行数
effect_row1 = cursor.execute("select * from USER")
# 2. 执行SQL,返回受影响的行数,一次插入多行数据
effect_row2 = cursor.executemany("insert into USER (NAME) values(%s)", [("jack"), ("boom"), ("lucy")])# 3
# 查询所有数据,返回数据为元组格式
result = cursor.fetchall()
# 增/删/改均需要进行commit提交,进行保存
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
print(result)
"""
[{'id': 6, 'name': 'boom'}, {'id': 5, 'name': 'jack'}, {'id': 7, 'name': 'lucy'}, {'id': 4, 'name': 'tome'}, {'id': 3, 'name': 'zff'}, {'id': 1, 'name': 'zhaofengfeng'}, {'id': 2, 'name': 'zhaofengfeng02'}]
"""

2.3 자동 증가 ID로 최신 생성된 데이터 가져오기

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
import pymysql
# 创建连接
conn = pymysql.connect(host="127.0.0.1", port=3306, user='zff', passwd='zff123', db='zff', charset='utf8mb4')
# 创建游标(查询数据返回为元组格式)
cursor = conn.cursor()
# 获取新创建数据自增ID
effect_row = cursor.executemany("insert into USER (NAME)values(%s)", [("eric")])
# 增删改均需要进行commit提交
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
new_id = cursor.lastrowid
print(new_id)
"""
8
"""

2.4 쿼리 작업

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
import pymysql
# 创建连接
conn = pymysql.connect(host="127.0.0.1", port=3306, user='zff', passwd='zff123', db='zff', charset='utf8mb4')
# 创建游标
cursor = conn.cursor()
cursor.execute("select * from USER")
# 获取第一行数据
row_1 = cursor.fetchone()
# 获取前n行数据
row_2 = cursor.fetchmany(3)
#
# # 获取所有数据
row_3 = cursor.fetchall()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
print(row_1)
print(row_2)
print(row_3)

⚠️ 데이터를 가져올 때 커서.scroll(num, mode)을 사용할 수 있습니다. 다음과 같이 커서 위치를 이동합니다:

cursor.scroll(1,mode='relative') # 현재 위치를 기준으로 이동

cursor.scroll(2, mode='absolute') # 상대 절대 위치 모바일

2.5는 SQL 주입을 방지합니다

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
import pymysql
# 创建连接
conn = pymysql.connect(host="127.0.0.1", port=3306, user='zff', passwd='zff123', db='zff', charset='utf8mb4')
# 创建游标
cursor = conn.cursor()
# 存在sql注入情况(不要用格式化字符串的方式拼接SQL)
sql = "insert into USER (NAME) values('%s')" % ('zhangsan',)
effect_row = cursor.execute(sql)
# 正确方式一
# execute函数接受一个元组/列表作为SQL参数,元素个数只能有1个
sql = "insert into USER (NAME) values(%s)"
effect_row1 = cursor.execute(sql, ['wang6'])
effect_row2 = cursor.execute(sql, ('wang7',))
# 正确方式二
sql = "insert into USER (NAME) values(%(name)s)"
effect_row1 = cursor.execute(sql, {'name': 'wudalang'})
# 写入插入多行数据
effect_row2 = cursor.executemany("insert into USER (NAME) values(%s)", [('ermazi'), ('dianxiaoer')])
# 提交
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

이렇게 하면 SQL 작업이 더 안전해집니다. 더 자세한 문서가 필요하면 PyMySQL 문서를 참조하세요. 그러나 이러한 SQL 데이터베이스의 구현은 동일하지 않은 것으로 보입니다. PyMySQL의 매개변수 자리 표시자는 %s와 같은 C 포맷터를 사용하는 반면, Python 자체 sqlite3 모듈의 자리 표시자는 물음표(?)인 것으로 보입니다. 따라서 다른 데이터베이스를 사용할 때에는 설명서를 주의 깊게 읽어보는 것이 좋습니다.

• 3. 데이터베이스 연결 풀

  
  

  위 방법에는 단일 스레드 상황에서 만족할 수 있는 문제가 있습니다. 그러면 데이터베이스 작업을 완료하려면 프로그램이 자주 연결을 생성하고 해제해야 합니다. 우리 프로그램/스크립트는 여러 스레드에서 사용될 수 있습니다. 이 경우 어떤 문제가 발생합니까? 이때 이 문제를 해결하려면 데이터베이스 연결 풀을 사용해야 합니다.

• 3.1 DBUtils 모듈
  

DBUtils는 Python의 모듈입니다. 데이터베이스 연결 풀을 구현하는 데 사용됩니다.

이 연결 풀에는 두 가지 연결 모드가 있습니다.

스레드가 close 메소드를 호출하더라도 연결을 다시 연결 풀에 넣기만 하면 됩니다. 다시 사용할 자신의 스레드. 스레드가 종료되면 연결이 자동으로 닫힙니다

모든 스레드가 공유할 수 있도록 연결 풀에 일괄 연결 만들기(권장)

3.2 모드 1

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
from DBUtils.PersistentDB import PersistentDB
import pymysql
POOL = PersistentDB(
 creator=pymysql,# 使用链接数据库的模块
 maxusage=None,# 一个链接最多被重复使用的次数，None表示无限制
 setsession=[],# 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
 ping=0,
 # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
 closeable=False,
 # 如果为False时， conn.close() 实际上被忽略，供下次使用，再线程关闭时，才会自动关闭链接。如果为True时， conn.close()则关闭链接，那么再次调用pool.connection时就会报错，因为已经真的关闭了连接（pool.steady_connection()可以获取一个新的链接）
 threadlocal=None,# 本线程独享值得对象，用于保存链接对象，如果链接对象被重置
 host='127.0.0.1',
 port=3306,
 user='zff',
 password='zff123',
 database='zff',
 charset='utf8',
)
def func():
 conn = POOL.connection(shareable=False)
 cursor = conn.cursor()
 cursor.execute('select * from USER')
 result = cursor.fetchall()
 cursor.close()
 conn.close()
 return result
result = func()
print(result)

3.2 모드 2

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "shuke"
# Date: 2018/5/13
import time
import pymysql
import threading
from DBUtils.PooledDB import PooledDB, SharedDBConnection
POOL = PooledDB(
 creator=pymysql,# 使用链接数据库的模块
 maxconnections=6,# 连接池允许的最大连接数，0和None表示不限制连接数
 mincached=2,# 初始化时，链接池中至少创建的空闲的链接，0表示不创建
 maxcached=5,# 链接池中最多闲置的链接，0和None不限制
 maxshared=3,
 # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
 blocking=True,# 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
 maxusage=None,# 一个链接最多被重复使用的次数，None表示无限制
 setsession=[],# 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
 ping=0,
 # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
 host='127.0.0.1',
 port=3306,
 user='zff',
 password='zff123',
 database='zff',
 charset='utf8'
)
def func():
 # 检测当前正在运行连接数的是否小于最大链接数，如果不小于则：等待或报raise TooManyConnections异常
 # 否则
 # 则优先去初始化时创建的链接中获取链接 SteadyDBConnection。
 # 然后将SteadyDBConnection对象封装到PooledDedicatedDBConnection中并返回。
 # 如果最开始创建的链接没有链接，则去创建一个SteadyDBConnection对象，再封装到PooledDedicatedDBConnection中并返回。
 # 一旦关闭链接后，连接就返回到连接池让后续线程继续使用。
 conn = POOL.connection()
 # print('连接被拿走了', conn._con)
 # print('池子里目前有', POOL._idle_cache, 'rn')
 cursor = conn.cursor()
 cursor.execute('select * from USER')
 result = cursor.fetchall()
 conn.close()
 return result
result = func()
print(result)

• ⚠️ pymysql, MySQLdb 등의 threadsafety 값이 1이므로 이 모드에서 연결 풀의 스레드는 모든 스레드에서 공유되므로 스레드로부터 안전합니다. 연결 풀이 없으면 pymysql을 사용하여 데이터베이스에 연결할 때 단일 스레드 응용 프로그램에는 문제가 없습니다. 그러나 다중 스레드 응용 프로그램이 관련되면 잠금이 필요합니다. 일단 잠기면 연결이 필연적으로 대기열에 추가됩니다. .요청이 많으면 성능이 저하됩니다.

  
  

  3.3 잠금

• #! /usr/bin/env python
  # -*- coding: utf-8 -*-
  # __author__ = "shuke"
  # Date: 2018/5/13
  import pymysql
  import threading
  from threading import RLock
  LOCK = RLock()
  CONN = pymysql.connect(host='127.0.0.1',
  port=3306,
  user='zff',
  password='zff123',
  database='zff',
  charset='utf8')
  def task(arg):
   with LOCK:
   cursor = CONN.cursor()
   cursor.execute('select * from USER ')
   result = cursor.fetchall()
   cursor.close()
   print(result)
  for i in range(10):
   t = threading.Thread(target=task, args=(i,))
   t.start()

  3.4 잠금 없음(오류 보고)
  

  #! /usr/bin/env python
  # -*- coding: utf-8 -*-
  # __author__ = "shuke"
  # Date: 2018/5/13
  import pymysql
  import threading
  CONN = pymysql.connect(host='127.0.0.1',
  port=3306,
  user='zff',
  password='zff123',
  database='zff',
  charset='utf8')
  def task(arg):
   cursor = CONN.cursor()
   cursor.execute('select * from USER ')
   # cursor.execute('select sleep(10)')
   result = cursor.fetchall()
   cursor.close()
   print(result)
  for i in range(10):
   t = threading.Thread(target=task, args=(i,))
   t.start()

이때 데이터베이스에서 연결 상태를 확인할 수 있습니다. 'Threads%'와 같은 상태를 표시합니다.

4. pymsql

과 함께 사용됩니다.

# cat sql_helper.py
import pymysql
import threading
from DBUtils.PooledDB import PooledDB, SharedDBConnection
POOL = PooledDB(
 creator=pymysql,# 使用链接数据库的模块
 maxconnections=20,# 连接池允许的最大连接数，0和None表示不限制连接数
 mincached=2,# 初始化时，链接池中至少创建的空闲的链接，0表示不创建
 maxcached=5,# 链接池中最多闲置的链接，0和None不限制
 #maxshared=3,# 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
 blocking=True,# 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
 maxusage=None,# 一个链接最多被重复使用的次数，None表示无限制
 setsession=[],# 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
 ping=0,
 # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
 host='192.168.11.38',
 port=3306,
 user='root',
 passwd='apNXgF6RDitFtDQx',
 db='m2day03db',
 charset='utf8'
)
def connect():
 # 创建连接
 # conn = pymysql.connect(host='192.168.11.38', port=3306, user='root', passwd='apNXgF6RDitFtDQx', db='m2day03db')
 conn = POOL.connection()
 # 创建游标
 cursor = conn.cursor(pymysql.cursors.DictCursor)
 return conn,cursor
def close(conn,cursor):
 # 关闭游标
 cursor.close()
 # 关闭连接
 conn.close()
def fetch_one(sql,args):
 conn,cursor = connect()
 # 执行SQL，并返回收影响行数
 effect_row = cursor.execute(sql,args)
 result = cursor.fetchone()
 close(conn,cursor)
 return result
def fetch_all(sql,args):
 conn, cursor = connect()
 # 执行SQL，并返回收影响行数
 cursor.execute(sql,args)
 result = cursor.fetchall()
 close(conn, cursor)
 return result
def insert(sql,args):
 """
 创建数据
 :param sql: 含有占位符的SQL
 :return:
 """
 conn, cursor = connect()
 # 执行SQL，并返回收影响行数
 effect_row = cursor.execute(sql,args)
 conn.commit()
 close(conn, cursor)
def delete(sql,args):
 """
 创建数据
 :param sql: 含有占位符的SQL
 :return:
 """
 conn, cursor = connect()
 # 执行SQL，并返回收影响行数
 effect_row = cursor.execute(sql,args)
 conn.commit()
 close(conn, cursor)
 return effect_row
def update(sql,args):
 conn, cursor = connect()
 # 执行SQL，并返回收影响行数
 effect_row = cursor.execute(sql, args)
 conn.commit()
 close(conn, cursor)
 return effect_row

PS: 정적 메서드를 사용하여 쉽게 사용할 수 있도록 클래스로 캡슐화할 수 있습니다

위 내용은 Python으로 MySQL을 작동하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!