最後更新: 2022-09-02

目錄

• python3-pymysql
• mysqldb
• mysql-connector-python (建議)
• sqlalchemy

python3-pymysql

Pure-Python MySQL Driver

Homepage: https://github.com/PyMySQL/PyMySQL

drop-in replacement for MySQLdb and work on CPython, PyPy, IronPython and Jython.

mysqldb

https://github.com/farcepest/MySQLdb1

Install

yum install python-pip; pip install mysqldb       # C6

yum install MySQL-python                               # C7

Testing

#!/bin/env python

import MySQLdb

# Settings
dbhost = "localhost"
dbuser = "username"
dbpass = "password"
mydb   = "mydb"

# Code
db = MySQLdb.connect(dbhost, dbuser , dbpass, mydb)
cursor = db.cursor()
cursor.execute("SELECT VERSION()")
data = cursor.fetchone()    
print "Database version : %s " % data    
db.close()

Usage

import MySQLdb

conn = MySQLdb.connect(host="localhost", user="db_user", passwd="db_pass", db="db_name", charset='utf8')

cursor = conn.cursor()

cursor.execute("SELECT * FROM db_table")

result = cursor.fetchall()

for row in result:
    id = row[0]
    city = row[1]

cursor.execute('''INSERT into db_table(id, city)
                  values (%s, %s)''',
                  (id, city))

conn.commit()
cursor.close()
conn.close()

Notes

Doc: http://mysql-python.sourceforge.net/MySQLdb.html

# By default Connector turns autocommit off

# InnoDB tables is necessary to commit your changes using the connection's commit() method.

 * 不 commit 就相當於自動行了 rollback()

結論: You must commit the data after a sequence of INSERT, DELETE, and UPDATE statements.

enable or disable the autocommit feature of MySQL.

>>> cnx.autocommit

False

>>> cnx.autocommit = True

>>> cnx.autocommit

True

Other Function:

cursor.fetchall()                  # fetches all rows of a query result set and returns a list of tuples.

len(cursor.fetchall())           # 0, 1, 2 ....

cursor.fetchone()                # returned tuple consists of data returned by the MySQL server

mysql_insert_id()                # conn.insert_id()

mysql_num_fields()            # result.num_fields()

mysql_num_rows()             # result.num_rows()

mysql-connector-python (Official MysQL Python Connector)

MySQL 官方出的 python library.

(https://dev.mysql.com/doc/connector-python/en/)

Written in Python which does not depend on MySQL C client libraries.

Version

Lib    MySQL
8.0    8.0, 5.7, 5.6, 5.5
2.1    5.7, 5.6, 5.5
2.0    5.7, 5.6, 5.5
1.2    5.7, 5.6, 5.5 (5.1, 5.0, 4.1)

安裝:

# Window

easy_install.exe mysql-connector-python

# Centos6 - epel

yum install mysql-connector-python

Test Code:

import mysql.connector

config = {
  'user': 'pytest',
  'password': '?????????',
  'host': 'localhost',
  'database': 'pytest',
  'raise_on_warnings': True,
}
# cnx 未連線, 所以不用 try
cnx = mysql.connector.connect(**config)
try:
  if cnx.is_connected():
    db_Info = cnx.get_server_info()
    print db_Info
    cursor = cnx.cursor()
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exists")
  else:
    print(err)
finally:
  if (cnx.is_connected()):
    cursor.close()
    cnx.close()

Opts:

raise_on_warnings  (False)
# Whether to raise an exception on warnings.

buffered (False)
Whether cursor objects fetch the results immediately after executing queries.

autocommit (off)

Select:

import datetime
import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()

query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")

hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(1999, 12, 31)

cursor.execute(query, (hire_start, hire_end))

# cursor 會成為一個 tuple
# 內容是: 
#     str u''
#     1
#     None
#     datetime.datetime(2015, 12, 15, 11, 40, 26)

for (first_name, last_name, hire_date) in cursor:
  print("{}, {} was hired on {:%d %b %Y}".format(
    last_name, first_name, hire_date))

cursor.close()
cnx.close()

Insert:

cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()

add_employee = ("INSERT INTO employees "
               "(first_name, last_name, hire_date, gender, birth_date) "
               "VALUES (%s, %s, %s, %s, %s)")

data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))

# Insert new employee
cursor.execute(add_employee, data_employee)
emp_no = cursor.lastrowid

# By default Connector/Python turns autocommit off,
# and MySQL 5.5 and later uses transactional InnoDB tables by default
# Make sure data is committed to the database
# 沒有 commit(), 相當於 cnx.rollback()
# commit() 是在 connector 上做的
cnx.commit()

cursor.close()
cnx.close()