Pythonのpymysqlの使用の結び目

17805 ワード
Python pymysql 使用
python 3.xでは、pymysqlを使ってMySQLデータベースに接続し、データベースの各種操作を実現します。今回のブログでは、主にpymysqlのインストールと使用方法を紹介します。
 PyMySQLのインストール
一.windows上の設置方法：
python 3.6にはpip 3を持参していますので、python 3で直接pip 3を使って必要なモジュールをインストールできます。

pip3 install pymysql -i https://pypi.douban.com/simple
二.linuxでのインストール方法：
1.tarパッケージのダウンロードと解凍

  tar 
wget https://pypi.python.org/packages/29/f8/919a28976bf0557b7819fd6935bfd839118aff913407ca58346e14fa6c86/PyMySQL-0.7.11.tar.gz#md5=167f28514f4c20cbc6b1ddf831ade772
     tar 
tar xf PyMySQL-0.7.11.tar.gz
2.据え付け

[root@localhost PyMySQL-0.7.11]# python36 setup.py install
データベースの接続
今回のテストで作成したデータとテーブル:

#       ，      
mysql> create database dbforpymysql;
mysql> create table userinfo(id int not null auto_increment primary key,username varchar(10),passwd varchar(10))engine=innodb default charset=utf8;
mysql> insert into userinfo(username,passwd) values('frank','123'),('rose','321'),('jeff',666);

#     
mysql> select * from userinfo;
+----+----------+--------+
| id | username | passwd |
+----+----------+--------+
| 1 | frank  | 123  |
| 2 | rose   | 321  |
| 3 | jeff   | 666  |
+----+----------+--------+
3 rows in set (0.00 sec)
データベースを接続:

import pymysql

#     
db = pymysql.connect("localhost","root","LBLB1212@@","dbforpymysql")

#  cursor()          
cursor = db.cursor()

#  execute()    SQL  
cursor.execute("SELECT * FROM userinfo")

#  fetall()      
data = cursor.fetchall()

#        
print(data)

#           
cursor.close()
db.close()

#    
((1, 'frank', '123'), (2, 'rose', '321'), (3, 'jeff', '666'))
MySQLデータの接続を完了するには、connectで以下のパラメータが使えます。

def __init__(self, host=None, user=None, password="",
       database=None, port=0, unix_socket=None,
       charset='', sql_mode=None,
       read_default_file=None, conv=None, use_unicode=None,
       client_flag=0, cursorclass=Cursor, init_command=None,
       connect_timeout=10, ssl=None, read_default_group=None,
       compress=None, named_pipe=None, no_delay=None,
       autocommit=False, db=None, passwd=None, local_infile=False,
       max_allowed_packet=16*1024*1024, defer_connect=False,
       auth_plugin_map={}, read_timeout=None, write_timeout=None,
       bind_address=None):
    ：
host: Host where the database server is located  #         
user: Username to log in as  #   
password: Password to use.  #  
database: Database to use, None to not use a particular one.  #      
port: MySQL port to use, default is usually OK. (default: 3306)  #  ，   3306
bind_address: When the client has multiple network interfaces, specify
  the interface from which to connect to the host. Argument can be
  a hostname or an IP address.  #              ，           ，          ip  
unix_socket: Optionally, you can use a unix socket rather than TCP/IP.
charset: Charset you want to use.  #      
sql_mode: Default SQL_MODE to use. 
read_default_file:
  Specifies my.cnf file to read these parameters from under the [client] section.
conv:
  Conversion dictionary to use instead of the default one.
  This is used to provide custom marshalling and unmarshaling of types.
  See converters.
use_unicode:
  Whether or not to default to unicode strings.
  This option defaults to true for Py3k.
client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.
cursorclass: Custom cursor class to use.
init_command: Initial SQL statement to run when connection is established.
connect_timeout: Timeout before throwing an exception when connecting.
  (default: 10, min: 1, max: 31536000)
ssl:
  A dict of arguments similar to mysql_ssl_set()'s parameters.
  For now the capath and cipher arguments are not supported.
read_default_group: Group to read from in the configuration file.
compress; Not supported
named_pipe: Not supported
autocommit: Autocommit mode. None means use server default. (default: False)
local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB)
  Only used to limit size of "LOAD LOCAL INFILE" data packet smaller than default (16KB).
defer_connect: Don't explicitly connect on contruction - wait for connect call.
  (default: False)
auth_plugin_map: A dict of plugin names to a class that processes that plugin.
  The class will take the Connection object as the argument to the constructor.
  The class needs an authenticate method taking an authentication packet as
  an argument. For the dialog plugin, a prompt(echo, prompt) method can be used
  (if no authenticate method) for returning a string from the user. (experimental)
db: Alias for database. (for compatibility to MySQLdb)
passwd: Alias for password. (for compatibility to MySQLdb)
cursorは実はcursorsモジュールの下のCursorのクラスを呼び出したのです。このモジュールの主な役割はデータベースと対話するためのものです。対象を具体化した場合、対象の下の様々なバインディング方法を呼び出すことができます。

class Cursor(object):
  """
  This is the object you use to interact with the database.
  """
  def close(self):
    """
    Closing a cursor just exhausts all remaining data.
    """
  def setinputsizes(self, *args):
    """Does nothing, required by DB API."""

  def setoutputsizes(self, *args):
    """Does nothing, required by DB API."""    
  def execute(self, query, args=None):
    """Execute a query

    :param str query: Query to execute.

    :param args: parameters used with query. (optional)
    :type args: tuple, list or dict

    :return: Number of affected rows
    :rtype: int

    If args is a list or tuple, %s can be used as a placeholder in the query.
    If args is a dict, %(name)s can be used as a placeholder in the query.
    """
  def executemany(self, query, args):
    # type: (str, list) -> int
    """Run several data against one query

    :param query: query to execute on server
    :param args: Sequence of sequences or mappings. It is used as parameter.
    :return: Number of rows affected, if any.

    This method improves performance on multiple-row INSERT and
    REPLACE. Otherwise it is equivalent to looping over args with
    execute().
    """
  def fetchone(self):
    """Fetch the next row"""
  def fetchmany(self, size=None):
    """Fetch several rows"""
  def fetchall(self):
    """Fetch all the rows"""
  ......
データベース操作
一、データベースの添削操作
comit()方法：データベースの中で増加して、削除して、改正する時、必ず提出しなければならなくて、さもなくば挿入するデータは効力がありません。

import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "INSERT INTO userinfo(username,passwd) VALUES('jack','123')"
cursor.execute(sql)
db.commit() #    
cursor.close()
db.close()
   execute       
import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "INSERT INTO userinfo(username,passwd) VALUES(%s,%s)"
cursor.execute(sql,("bob","123")) 
db.commit() #    
cursor.close()
db.close()
小知識点、mysqlの注入問題：

 mysql   "--"    ，                 ：
          userinfo ，     ：
mysql> select * from userinfo;
+----+----------+--------+
| id | username | passwd |
+----+----------+--------+
| 1 | frank  | 123  |
| 2 | rose   | 321  |
| 3 | jeff   | 666  |
+----+----------+--------+
3 rows in set (0.00 sec)
       ：
import pymysql
user = input("username:")
pwd = input("password:")
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
sql = "select * from userinfo where username='%s' and passwd='%s'" %(user,pwd)
result=cursor.execute(sql)
cursor.close()
db.close()
if result:
  print('    ')
else:
  print('    ')
#         
username:frank
password:123
result: 1
    
#         
username:frank
password:1231231
result: 0
    
         ，          
----------------------------------------------
username:' or 1=1 -- 
password:123
result: 3
    
----------------------------------------------
 ~      .
    ？           sql  ：
select * from userinfo where username='' or 1=1 -- ' and passwd='123'
  --       ，  where     ，             ，       0，        。
                 execute   ：
result=cursor.execute(sql,(user,pwd))
     ' or 1=1 --            。
executemeny()：複数のデータを同時に挿入するために使用します。

import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "INSERT INTO userinfo(username,passwd) VALUES(%s,%s)"
cursor.executemany(sql,[("tom","123"),("alex",'321')])
db.commit() #    
cursor.close()
db.close()
execute()とexecutemeny()は影響を受ける行数を返します。

sql = "delete from userinfo where username=%s"
res = cursor.executemany(sql,("jack",))
print("res=",res)
#    
res= 1
表に自己増加のメインキーがある場合、最後の自己増加IDはlastrowidを使って取得できます。

import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "INSERT INTO userinfo(username,passwd) VALUES(%s,%s)"
cursor.execute(sql,("zed","123"))
print("the last rowid is ",cursor.lastrowid)
db.commit() #    
cursor.close()
db.close()

#    
the last rowid is 10
二、データベースのクエリー操作
ここでは主に三つの結合方法を紹介します。
  • fetch()：次の行のデータを取得するのは初めてです。
  • fetch all()：すべての行のデータソースを取得する
  • fettmanny(4)：次の4行のデータを取得する
    • まず、表の内容を確認します。
    
mysql> select * from userinfo;
+----+----------+--------+
| id | username | passwd |
+----+----------+--------+
| 1 | frank  | 123  |
| 2 | rose   | 321  |
| 3 | jeff   | 666  |
| 5 | bob   | 123  |
| 8 | jack   | 123  |
| 10 | zed   | 123  |
+----+----------+--------+
6 rows in set (0.00 sec)
    fetch():
    
import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "SELECT * FROM userinfo"
cursor.execute(sql)
res = cursor.fetchone() #     
print(res)
res = cursor.fetchone() #     
print(res)
cursor.close()
db.close()

#    
(1, 'frank', '123')
(2, 'rose', '321')
    fetch():
    
import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "SELECT * FROM userinfo"
cursor.execute(sql)
res = cursor.fetchall() #     
print(res)
res = cursor.fetchall() #     
print(res)
cursor.close()
db.close()
#    
((1, 'frank', '123'), (2, 'rose', '321'), (3, 'jeff', '666'), (5, 'bob', '123'), (8, 'jack', '123'), (10, 'zed', '123'))
()
    二回目の取得時には、何のデータも入手できませんでした。これはファイルの読み込み操作と似ています。
    デフォルトでは、私たちが取得した戻り値は元のグループであり、各行のデータしか見られませんが、各列の代表は何か分かりません。この時は以下のように辞書に戻ります。各行のデータは辞書を作成します。
    
cursor = db.cursor(cursor=pymysql.cursors.DictCursor) #       ，   cursor   pymysql.cursors.DictCursor
    fetch allを使ってすべての行のデータを取得し、各行に1つの辞書を作成してリストに入れます。
    
import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
sql = "SELECT * FROM userinfo"
cursor.execute(sql)
res = cursor.fetchall()
print(res)
cursor.close()
db.close()
#    
[{'id': 1, 'username': 'frank', 'passwd': '123'}, {'id': 2, 'username': 'rose', 'passwd': '321'}, {'id': 3, 'username': 'jeff', 'passwd': '666'}, {'id': 5, 'username': 'bob', 'passwd': '123'}, {'id': 8, 'username': 'jack', 'passwd': '123'}, {'id': 10, 'username': 'zed', 'passwd': '123'}]
    このようにして得られた内容は理解されやすく使用されます。
    行のデータを取得するとき、最初の行の上を指して1行を取得し、次の行に移動します。だから、行の指針が最後の行になると、行の内容が得られなくなりますので、次のような方法で行の指針を移動します。
    
cursor.scroll(1,mode='relative') #         
cursor.scroll(2,mode='absolute') #
    最初の値は移動の行数、整数は下に移動し、負は上に移動し、modeは現在の位置に対して移動するか、それとも最初の行に対して移動するかを指定します。
    たとえば:
    
sql = "SELECT * FROM userinfo"
cursor.execute(sql)
res = cursor.fetchall()
print(res)
cursor.scroll(0,mode='absolute') #       0，            
res = cursor.fetchall() #         
print(res)

#    
[{'id': 1, 'username': 'frank', 'passwd': '123'}, {'id': 2, 'username': 'rose', 'passwd': '321'}, {'id': 3, 'username': 'jeff', 'passwd': '666'}, {'id': 5, 'username': 'bob', 'passwd': '123'}, {'id': 8, 'username': 'jack', 'passwd': '123'}, {'id': 10, 'username': 'zed', 'passwd': '123'}]
[{'id': 1, 'username': 'frank', 'passwd': '123'}, {'id': 2, 'username': 'rose', 'passwd': '321'}, {'id': 3, 'username': 'jeff', 'passwd': '666'}, {'id': 5, 'username': 'bob', 'passwd': '123'}, {'id': 8, 'username': 'jack', 'passwd': '123'}, {'id': 10, 'username': 'zed', 'passwd': '123'}]
    コンテキストマネージャ
    pythonのファイル操作でコンテキストマネージャをサポートし、データベースを操作する時にも使用できます。
    
import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
with db.cursor(cursor=pymysql.cursors.DictCursor) as cursor: #          
  sql = "SELECT * FROM userinfo"  
  cursor.execute(sql)
  res = cursor.fetchone()
  print(res)
  cursor.scroll(2,mode='relative')
  res = cursor.fetchone()
  print(res)
  cursor.close()
db.close()

#    
{'id': 1, 'username': 'frank', 'passwd': '123'}
{'id': 5, 'username': 'bob', 'passwd': '123'}
    コンテキストマネージャは、コードの読み取り可能性をより強くすることができる。
    以上が本文の全部です。皆さんの勉強に役に立つように、私たちを応援してください。
    Javaファイルの実行順序、mainプログラムの入り口の理解を話します。
    Javaのwaitとsleepの違いを詳しく説明します。