当前位置：极客教程 > MySQL > MySQL 教程 > MySQL Python 教程

MySQL Python 教程

这是 MySQL Python 编程教程，它涵盖了使用 Python 进行 MySQL 编程的基础。 它使用MySQLdb模块。 这些示例是在 Ubuntu Linux 上创建和测试的。

MySQLdb 是 MySQL 的 Python 2 旧版数据库模块。 对于现代 Python 3 MySQL 编程，请使用 PyMySQL 模块。 参见 PyMySQL 教程。

关于 MySQL 数据库

MySQL 是领先的开源数据库管理系统。 它是一个多用户，多线程的数据库管理系统。 MySQL 在网络上特别流行。 它是非常流行的 _LAMP_ 平台的一部分，该平台由 Linux，Apache，MySQL 和 PHP 组成。 目前，MySQL 由 Oracle 拥有。

MySQL 数据库在最重要的 OS 平台上可用。 它可以在 BSD Unix，Linux，Windows 或 Mac OS 上运行。 维基百科和 YouTube 使用 MySQL。 这些站点每天管理数百万个查询。 MySQL 有两个版本：MySQL 服务器系统和 MySQL 嵌入式系统。

MySQLdb 安装

$ apt-cache search MySQLdb
python-mysqldb - A Python interface to MySQL
python-mysqldb-dbg - A Python interface to MySQL (debug extension)
bibus - bibliographic database
eikazo - graphical frontend for SANE designed for mass-scanning

我们在包名称中搜索 MySQLdb 模块。 我们使用apt-cache命令找出答案。

$ sudo apt-get install python-mysqldb

在这里，我们将 Python 接口安装到 MySQL 数据库。 _mysql和mysql模块。

MySQL 安装

如果您尚未安装 MySQL，则必须安装它。

$ sudo apt-get install mysql-server

此命令将安装 MySQL 服务器和其他各种软件包。 在安装软件包时，提示我们输入 MySQL 根帐户的密码。

接下来，我们将创建一个新的数据库用户和一个新的数据库。 我们使用mysql客户端。

$ mysql -u root -p
Enter password: 

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
+--------------------+
2 rows in set (0.00 sec)

我们使用根帐户连接到数据库。 我们用SHOW DATABASES语句显示所有可用的数据库。

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.02 sec)

我们创建一个新的testdb数据库。 在整个教程中，我们将使用此数据库。

mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test623';
Query OK, 0 rows affected (0.00 sec)

mysql> USE testdb;
Database changed

mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

我们创建一个新的数据库用户。 我们授予该用户testdb数据库所有表的所有特权。

_mysql 模块

_mysql模块直接实现 MySQL C API。 它与 Python DB API 接口不兼容。 通常，程序员更喜欢面向对象的MySQLdb模块。 我们将关注后一个模块。 在这里，我们仅介绍_mysql模块的一个小示例。

version_capi.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import _mysql
import sys

try:
    con = _mysql.connect('localhost', 'testuser', 'test623', 'testdb')

    con.query("SELECT VERSION()")
    result = con.use_result()

    print "MySQL version: %s" % \
        result.fetch_row()[0]

except _mysql.Error, e:

    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exit(1)

finally:

    if con:
        con.close()

该示例将获取并打印 MySQL 数据库的版本。 为此，我们使用SELECT VERSION() SQL 语句。

MySQLdb 模块

MySQLdb是围绕_mysql的薄型 Python 包装器。 它与 Python DB API 兼容，这使代码更易于移植。 使用此模型是使用 MySQL 的首选方式。

MySQLdb 版本示例

在第一个示例中，我们将获取 MySQL 数据库的版本。

version.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

try:
    con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

    cur = con.cursor()
    cur.execute("SELECT VERSION()")

    ver = cur.fetchone()

    print "Database version : %s " % ver

except mdb.Error, e:

    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

finally:    

    if con:    
        con.close()

在此脚本中，我们连接到 testdb 数据库并执行SELECT VERSION()语句。 这将返回 MySQL 数据库的当前版本。 我们将其打印到控制台。

import MySQLdb as mdb

我们导入MySQLdb模块。

con = mdb.connect('localhost', 'testuser', 
    'test623', 'testdb');

我们使用connect()方法连接到数据库。 我们传递四个参数：主机名，数据库用户名，密码和数据库名。

cur = con.cursor()
cur.execute("SELECT VERSION()")

从连接中，我们得到游标对象。 游标用于遍历结果集中的记录。 我们调用游标的execute()方法并执行 SQL 语句。

ver = cur.fetchone()

我们获取数据。 由于只检索一条记录，因此我们称为fetchone()方法。

print "Database version : %s " % ver

我们将检索到的数据打印到控制台。

except mdb.Error, e:

    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

我们检查错误。

finally:    

    if con:    
        con.close()

在最后一步，我们释放资源。

$ ./version.py
Database version : 5.7.23-0ubuntu0.16.04.1

这是一个示例输出。

MySQLdb 创建并填充表

我们创建一个表，并用一些数据填充它。

create_table.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con:

    cur = con.cursor()
    cur.execute("DROP TABLE IF EXISTS Writers")
    cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
                 Name VARCHAR(25))")
    cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")

我们创建一个Writers表，并向其中添加五个作者。

with con:

使用 with 关键字，Python 解释器会自动释放资源。 它还提供错误处理。

cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
                Name VARCHAR(25))")

该 SQL 语句创建一个名为Writers的新数据库表。 它具有两列：Id和Name。

cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
...

我们使用INSERT语句在表中插入作者。 在这里，我们添加两行。

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
+----+-------------------+
5 rows in set (0.00 sec)

执行脚本后，我们使用mysql客户端工具从 Writers 表中选择所有数据。

MySQLdb Fetchall

fetchall()方法获取查询结果集的所有（或所有剩余）行，并返回一个元组列表。

fetch_all.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con: 

    cur = con.cursor()
    cur.execute("SELECT * FROM Writers")

    rows = cur.fetchall()

    for row in rows:
        print row

在此示例中，我们从Writers表中检索所有数据。

cur.execute("SELECT * FROM Writers")

该 SQL 语句从Writers表中选择所有数据。

rows = cur.fetchall()

fetchall()方法获取所有记录。 它返回一个结果集。 从技术上讲，它是一个元组的元组。 每个内部元组代表表中的一行。

for row in rows:
    print row

我们将数据逐行打印到控制台。

$ ./fetch_all.py
(1L, 'Jack London')
(2L, 'Honore de Balzac')
(3L, 'Lion Feuchtwanger')
(4L, 'Emile Zola')
(5L, 'Truman Capote')

这是示例的输出。

一次返回所有数据可能不可行。 我们可以一一读取行。

fetch_onebyone.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con:

    cur = con.cursor()
    cur.execute("SELECT * FROM Writers")

    for i in range(cur.rowcount):

        row = cur.fetchone()
        print row[0], row[1]

我们再次将数据从Writers表打印到控制台。 这次，我们一张一行地获取行。

for i in range(cur.rowcount):

    row = cur.fetchone()
    print row[0], row[1]

我们使用fetchone()方法一张一行地获取行。 rowcount 属性给出了 SQL 语句返回的行数。

$ ./fetch_onebyone.py
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

这是示例的输出。

MySQLdb 字典游标

MySQLdb模块中有多种游标类型。 默认游标以元组的元组返回数据。 当我们使用字典游标时，数据以 Python 字典的形式发送。 这样，我们可以通过列名称来引用数据。

dictionary_cursor.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con:

    cur = con.cursor(mdb.cursors.DictCursor)
    cur.execute("SELECT * FROM Writers LIMIT 4")

    rows = cur.fetchall()

    for row in rows:
        print row["Id"], row["Name"]

在此示例中，我们使用字典游标获取Writers表的前四行。

cur = con.cursor(mdb.cursors.DictCursor)

我们使用DictCursor字典游标。

cur.execute("SELECT * FROM Writers LIMIT 4")

我们从Writers表中获取四行。

for row in rows:
    print row["Id"], row["Name"]

我们通过Writers表的列名引用数据。

$ ./dictcur.py
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola

示例输出。

MySQLdb 列标题

接下来，我们将展示如何使用数据库表中的数据打印列标题。

column_headers.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con:

    cur = con.cursor()
    cur.execute("SELECT * FROM Writers LIMIT 5")

    rows = cur.fetchall()

    desc = cur.description

    print "%s %3s" % (desc[0][0], desc[1][0])

    for row in rows:    
        print "%2s %3s" % row

同样，我们将Writers表的内容打印到控制台。 现在，我们也包括列的名称。 列名被认为是“元数据”。 它是从游标对象获得的。

desc = cur.description

游标的描述属性返回有关查询的每个结果列的信息。

print "%s %3s" % (desc[0][0], desc[1][0])

在这里，我们打印并格式化表的列名。

for row in rows:    
    print "%2s %3s" % row

在这里，我们遍历并打印数据。

$ ./columnheaders.py
Id Name
 1 Jack London
 2 Honore de Balzac
 3 Lion Feuchtwanger
 4 Emile Zola
 5 Truman Capote

这是输出。

MySQLdb 预备语句

在编写预备语句时，我们使用占位符，而不是直接将值写入语句中。 预准备的语句可提高安全性和性能。 Python DB API 规范建议了五种不同的方式来构建预备语句。 MySQLdb模块支持其中之一，即 ANSI printf格式代码。

prepared_statement.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con:    

    cur = con.cursor()

    cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
        ("Guy de Maupasant", "4"))        

    print "Number of rows updated:",  cur.rowcount

我们在第四行更改作者的姓名。

cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
    ("Guy de Maupasant", "4"))

我们使用由%s标记标识的两个占位符。 在执行 SQL 语句之前，将值绑定到它们的占位符。

$ ./prepared.py
Number of rows updated: 1

我们更新了一行。

mysql> SELECT Name FROM Writers WHERE Id=4;
+------------------+
| Name             |
+------------------+
| Guy de Maupasant |
+------------------+
1 row in set (0.00 sec)

第四行的作者已成功更改。

MySQLdb 插入图像

人们经常寻找将图像插入数据库的方法。 我们将展示如何在 SQLite 和 Python 中完成它。 请注意，有些人不建议将图像放入数据库。 图像是二进制数据。 MySQL 数据库具有一种特殊的数据类型来存储称为BLOB（二进制大对象）的二进制数据。 TINYBLOB，BLOB，MEDIUMBLOB和LONGBLOB是二进制对象类型的变体。

mysql> CREATE TABLE Images(Id INT PRIMARY KEY, Data MEDIUMBLOB);
Query OK, 0 rows affected (0.08 sec)

对于此示例，我们创建一个名为Images的新表。

insert_image.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb

def read_image():

    with open("sid.jpg") as f:

        img = f.read()
        return img

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con:

    cur = con.cursor()
    data = read_image()
    cur.execute("INSERT INTO Images VALUES(1, %s)", (data, ))

在上面的脚本中，我们从磁盘读取 JPG 图像并将其插入到Images表中。

def read_image():

    with open("sid.jpg") as f:

        img = f.read()
        return img

read_image()方法从位于当前工作目录中的 JPG 文件读取二进制数据。

cur.execute("INSERT INTO Images VALUES(1, %s)", (data, ))

我们将图像数据插入Images表。

MySQLdb 读取图像

在前面的示例中，我们已将图像插入数据库表中。 现在，我们将从表中读取图像。

read_image.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb 

def writeImage(data):

    fout = open('sid2.jpg', 'wb')

    with fout:

        fout.write(data)

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con:

    cur = con.cursor()

    cur.execute("SELECT Data FROM Images WHERE Id=1")
    data = cur.fetchone()[0]
    writeImage(data)

我们从Images表中读取了一张图像。

cur.execute("SELECT Data FROM Images WHERE Id=1")

我们从表中选择一条记录。

fout = open('sid2.jpg', 'wb')

我们打开一个可写的二进制文件。

fout.write(data)

我们将数据写入磁盘。

现在我们在当前目录中应该有一个名为woman2.jpg的映像。 我们可以检查它是否与我们插入表中的图像相同。

MySQLdb 事务支持

事务 是针对一个或多个数据库中数据的数据库操作的基本单位。 事务中所有 SQL 语句的影响可以全部提交给数据库，也可以全部回滚。

对于支持事务的数据库，在创建游标时，Python 接口会静默启动事务。 commit()方法将提交使用该游标进行的更新，而rollback()方法将丢弃这些更新。 每种方法都会启动一个新事务。

MySQL 数据库具有不同类型的存储引擎。 最常见的是 MyISAM 和 InnoDB 引擎。 从 MySQL 5.5 开始，InnoDB 成为默认的存储引擎。 在数据安全性和数据库速度之间需要权衡。 MyISAM 表的处理速度更快，并且不支持事务。 commit()和rollback()方法未实现。 他们什么都不做。 另一方面，InnoDB 表可以更安全地防止数据丢失。 他们支持事务。 它们处理较慢。

transaction.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

try:
    con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

    cur = con.cursor()
    cur.execute("DROP TABLE IF EXISTS Writers")
    cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
                 Name VARCHAR(25)) ENGINE=INNODB")
    cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Terry Pratchett')")

    con.commit()

except mdb.MySQLError, e:

    if con:
        con.rollback()

    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

finally:    

    if con:    
        con.close()

我们重新创建Writers表。 我们明确地处理事务。

cur = con.cursor()

在 Python DB API 中，我们不调用 BEGIN 语句来启动事务。 创建游标后，将启动一个事务。

cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
                Name VARCHAR(25)) ENGINE=INNODB")

我们正在处理一个 InnoDB 表类型。 对于较早的 MySQL 版本（< 5.5），我们需要使用ENGINE=INNODB选项指定引擎类型。

con.commit()

我们必须使用commit()或rollback()方法结束事务。 如果我们在这行中添加注释，则会创建表，但不会将数据写入表中。

在本教程中，我们一直在处理事务而未明确说明它们。 我们使用了上下文管理器。 上下文管理器处理所需的运行时上下文的入口和出口，以执行代码块。 通常使用with语句来调用上下文管理器。

MySQLdb模块中的连接对象可用作上下文管理器。 他们自动提交或回滚事务。 连接上下文管理器通过分解try，except和finally语句来清理代码。

transaction2.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')

with con:

    cur = con.cursor()
    cur.execute("DROP TABLE IF EXISTS Writers")
    cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
                 Name VARCHAR(25))")
    cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Terry Pratchett')")

在我们的代码示例中，上下文管理器处理错误处理所需的所有工作。 它会自动提交或回滚事务。

这是 MySQL Python 教程。 它使用旧版 MySQLdb 模块与 MySQL 一起使用。 请参考 PyMySQL 教程以使用现代的 PyMySQL 模块。

您可能也对 MySQL Python 教程， MySQL Visual Basic 教程或 MySQL PHP 教程，MySQL Java 教程，MySQL Ruby 教程，MySQL C# 教程感兴趣。

Python教程

Python 教程

Python 教程
Tkinter 教程

Tkinter 教程
Pandas 教程

Pandas 教程
NumPy 教程

NumPy 教程
Flask 教程

Flask 教程
Django 教程

Django 教程
PySpark 教程

PySpark 教程
wxPython 教程

wxPython 教程
SymPy 教程

SymPy 教程
Seaborn 教程

Seaborn 教程
SciPy 教程

SciPy 教程
RxPY 教程

RxPY 教程
Pycharm 教程

Pycharm 教程
Pygame 教程

Pygame 教程
PyGTK 教程

PyGTK 教程
PyQt 教程

PyQt 教程
PyQt5 教程

PyQt5 教程
PyTorch 教程

PyTorch 教程
Matplotlib 教程

Matplotlib 教程
Web2py 教程

Web2py 教程
BeautifulSoup 教程

BeautifulSoup 教程

Java教程

Java 教程

Java 教程

Web教程

HTML 教程

HTML 教程
CSS 教程

CSS 教程
CSS3 教程

CSS3 教程
jQuery 教程

jQuery 教程
Ajax 教程

Ajax 教程
AngularJS 教程

AngularJS 教程
TypeScript 教程

TypeScript 教程
WordPress 教程

WordPress 教程
Laravel 教程

Laravel 教程
Next.js 教程

Next.js 教程
PhantomJS 教程

PhantomJS 教程
Three.js 教程

Three.js 教程
Underscore.JS 教程

Underscore.JS 教程
WebGL 教程

WebGL 教程
WebRTC 教程

WebRTC 教程
VueJS 教程

VueJS 教程

数据库教程

SQL 教程

SQL 教程
MySQL 教程

MySQL 教程
MongoDB 教程

MongoDB 教程
PostgreSQL 教程

PostgreSQL 教程
SQLite 教程

SQLite 教程
Redis 教程

Redis 教程
MariaDB 教程

MariaDB 教程

图形图像教程

Vulkan 教程

Vulkan 教程
OpenCV 教程

OpenCV 教程

大数据教程

R语言 教程

R语言 教程

开发工具教程

Git 教程

Git 教程
VSCode 教程

VSCode 教程
Docker 教程

Docker 教程
Gerrit 教程

Gerrit 教程
Excel 教程

Excel 教程

计算机教程

Go语言 教程

Go语言 教程
C++ 教程

C++ 教程

MySQL 教程