Python操作MySQL

12939 ワード

Python操作MySQL
一.python操作データベースの紹介
Python標準データベースインタフェースはPython DB-APIであり、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文とストアド・プロシージャを実行します.データベース接続を閉じます.
二.python操作MySQLモジュール
Python操作MySQLは主に2つの方法を使用します.

DBモジュール(オリジナルSQL)

PyMySQL(python 2.x/3.x対応)

MySQLdb(現在python 2.xのみサポート)

ORMフレームワーク

SQLAchemy

2.1 PyMySQLモジュール
本文は主にPyMySQLモジュールを紹介して、MySQLdbの使用方式は類似しています
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)

⚠️ fetchデータの場合は順番に行う、cursorを用いることができる.scroll(num,mode)は、次のようなカーソル位置を移動します.

cursor.scroll(1,mode='llative')#現在位置に対して

移動

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が持参したsqlite 3モジュールのプレースホルダは疑問符(?)のようです.そのため、他のデータベースを使用するときは、ドキュメントをよく読みましょう.Welcome to PyMySQL’s documentation
三.データベース接続プール
上記の方法では、単一スレッドの場合に満たすことができ、プログラムは頻繁に接続を解放してデータベースの操作を完了する必要があるという問題があります.では、私たちのプログラム/スクリプトは、マルチスレッドの場合にどのような問題を引き起こすのでしょうか.この場合、データベース接続プールを使用してこの問題を解決する必要があります.
3.1 DBUtilsモジュール
DBUtilsはPythonのデータベース接続プールを実現するためのモジュールです.
この接続プールには2つの接続モードがあります.

スレッドごとに接続を作成します.スレッドはcloseメソッドを呼び出しても閉じません.接続プールに接続を再配置し、自分のスレッドを再使用するだけです.スレッドが終了すると、接続は自動的に

を閉じる.

すべてのスレッドを共有するための接続プール(推奨)

を作成します.
3.2モード一

#! /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, '\r
')

    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()

データベースで接続状況を表示できます:show status like'Threads%';
四.データベース接続プール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:静的な方法で一つのクラスにカプセル化でき、使いやすい

mysql Pythonによるclickhouse行数の導入による完了速度への影響(データファイル圧縮なし)

ウィザード形式の登録フォームでバリデーション