Python-MySQL数据库

wyqwilliam

2020-06-13

什么是MySQLdb?

MySQLdb 是用于Python链接Mysql数据库的接口，它实现了 Python 数据库 API 规范 V2.0，基于 MySQL C API 上建立的。

如何安装MySQLdb?

为了用DB-API编写MySQL脚本，必须确保已经安装了MySQL。复制以下代码，并执行：

import MySQLdb

如果执行后的输出结果如下所示，意味着你没有安装 MySQLdb 模块：

Traceback (most recent call last):
  File "test.py", line 3, in <module>
    import MySQLdb
ImportError: No module named MySQLdb

安装MySQLdb，请访问 http://sourceforge.net/projects/mysql-python ，(Linux平台可以访问：https://pypi.python.org/pypi/MySQL-python)从这里可选择适合您的平台的安装包，分为预编译的二进制文件和源代码安装包。

如果您选择二进制文件发行版本的话，安装过程基本安装提示即可完成。如果从源代码进行安装的话，则需要切换到MySQLdb发行版本的顶级目录，并键入下列命令:

$ gunzip MySQL-python-1.2.2.tar.gz
$ tar -xvf MySQL-python-1.2.2.tar
$ cd MySQL-python-1.2.2
$ python setup.py build
$ python setup.py install

注意：请确保您有root权限来安装上述模块。

数据库连接

import MySQLdb

mysql = MySQLdb.connect("localhost","root",‘sutaoyu01‘) #连接数据库

此时，执行程序，如果不报错，则Mysql连接成功，前提是后台必须打开Mysql的进程。

创建数据库

import MySQLdb

mysql = MySQLdb.connect("localhost","root",‘sutaoyu01‘) #连接数据库

mysql_curson = mysql.cursor()
mysql_curson.execute("CREATE DATABASE TESTDB")   #创建 TESTDB 库

如果程序不报错，代表数据库创建成功

查看数据库

import MySQLdb

mysql = MySQLdb.connect("localhost","root",‘sutaoyu01‘) #连接数据库

mysql_curson = mysql.cursor()
# mysql_curson.execute("CREATE DATABASE TESTDB")   #创建 TESTDB 库
mysql_curson.execute("SHOW DATABASES") # 查看数据库

for x in mysql_curson:
    print(x)
    
# (‘information_schema‘,)
# (‘mysql‘,)
# (‘performance_schema‘,)
# (‘python‘,)
# (‘sakila‘,)
# (‘test‘,)
# (‘testdb‘,)
# (‘testtdb‘,)
# (‘world‘,)

进入数据库

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库

创建数据表

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库

mysql_cursor = mysql.cursor()
mysql_cursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

查看表

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库

mysql_cursor = mysql.cursor()
# mysql_cursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
mysql_cursor.execute("SHOW TABLES")

for x in mysql_cursor:
    print(x)
    
# (‘customers‘,)

主键

在创建表时，应该创建一个主键字段。主键唯一地标识一行记录。

可以使用语句“INT AUTO_INCREMENT PRIMARY KEY”创建主键，它将创建一个自增ID（从1开始，每条记录增加1）作为主键。

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库

mysql_cursor = mysql.cursor()
mysql_cursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

如果表已经存在，可使用ALTER TABLE关键字创建主键:

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库

mysql_cursor = mysql.cursor()
mysql_cursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

插入表

要把记录插入到MySQL中的表中，使用“INSERT INTO”语句。

示例

在“customers”表中插入一条记录:

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")

mysql_cursor.execute(sql,val)
mysql.commit()
print(mysql_cursor.rowcount, "条记录已插入")

# 1 条记录已插入

注意: 调用mysql_cursor.commit()语句提交修改，否则修改不会生效。

插入多行

要将多行插入到表中，使用executemany()方法。

executemany()方法的第二个参数是一个元组列表，包含了要插入的数据:

示例

填写“客户”表格:

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  (‘Peter‘, ‘Lowstreet 4‘),
  (‘Amy‘, ‘Apple st 652‘),
  (‘Hannah‘, ‘Mountain 21‘),
  (‘Michael‘, ‘Valley 345‘),
  (‘Sandy‘, ‘Ocean blvd 2‘),
  (‘Betty‘, ‘Green Grass 1‘),
  (‘Richard‘, ‘Sky st 331‘),
  (‘Susan‘, ‘One way 98‘),
  (‘Vicky‘, ‘Yellow Garden 2‘),
  (‘Ben‘, ‘Park Lane 38‘),
  (‘William‘, ‘Central st 954‘),
  (‘Chuck‘, ‘Main Road 989‘),
  (‘Viola‘, ‘Sideway 1633‘)
]

mysql_cursor.executemany(sql,val)
mysql.commit()
print(mysql_cursor.rowcount, "条记录已插入")

# 13 条记录已插入

获取插入行的ID

可以通过查询cursor对象，获得刚才插入行的id（如果插入多行，则返回最后插入的id）。

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  (‘Peter‘, ‘Lowstreet 4‘),
  (‘Amy‘, ‘Apple st 652‘),
  (‘Hannah‘, ‘Mountain 21‘),
  (‘Michael‘, ‘Valley 345‘),
  (‘Sandy‘, ‘Ocean blvd 2‘),
  (‘Betty‘, ‘Green Grass 1‘),
  (‘Richard‘, ‘Sky st 331‘),
  (‘Susan‘, ‘One way 98‘),
  (‘Vicky‘, ‘Yellow Garden 2‘),
  (‘Ben‘, ‘Park Lane 38‘),
  (‘William‘, ‘Central st 954‘),
  (‘Chuck‘, ‘Main Road 989‘),
  (‘Viola‘, ‘Sideway 1633‘)
]

mysql_cursor.executemany(sql,val)
mysql.commit()
print("1 条记录插入, ID:", mysql_cursor.lastrowid)

# 1 条记录插入, ID: 15

查询数据

从MySQL表中选取(SELECT)数据，使用“SELECT”语句:

示例：

从“customers”表中选取(SELECT)所有记录，并显示结果:

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()

mysql_cursor.execute("SELECT * FROM customers")

result = mysql_cursor.fetchall()
for x in result:
    print(x)
    
# (‘John‘, ‘Highway 21‘, 1)
# (‘Peter‘, ‘Lowstreet 4‘, 2)
# (‘Amy‘, ‘Apple st 652‘, 3)
# (‘Hannah‘, ‘Mountain 21‘, 4)
# (‘Michael‘, ‘Valley 345‘, 5)
# (‘Sandy‘, ‘Ocean blvd 2‘, 6)
# (‘Betty‘, ‘Green Grass 1‘, 7)
# (‘Richard‘, ‘Sky st 331‘, 8)
# (‘Susan‘, ‘One way 98‘, 9)
# (‘Vicky‘, ‘Yellow Garden 2‘, 10)
# (‘Ben‘, ‘Park Lane 38‘, 11)
# (‘William‘, ‘Central st 954‘, 12)
# (‘Chuck‘, ‘Main Road 989‘, 13)
# (‘Viola‘, ‘Sideway 1633‘, 14)

注意: 我们使用了fetchall()方法，它从最后所执行语句的结果中，获取所有行。

选取（SELECT）部分字段

如果要选取表中的部分字段，使用“SELECT 字段1, 字段2 …”语句:

示例

选择name和address字段:

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()

mysql_cursor.execute("SELECT name,address FROM customers")

result = mysql_cursor.fetchall()
for x in result:
    print(x)

# (‘John‘, ‘Highway 21‘)
# (‘Peter‘, ‘Lowstreet 4‘)
# (‘Amy‘, ‘Apple st 652‘)
# (‘Hannah‘, ‘Mountain 21‘)
# (‘Michael‘, ‘Valley 345‘)
# (‘Sandy‘, ‘Ocean blvd 2‘)
# (‘Betty‘, ‘Green Grass 1‘)
# (‘Richard‘, ‘Sky st 331‘)
# (‘Susan‘, ‘One way 98‘)
# (‘Vicky‘, ‘Yellow Garden 2‘)
# (‘Ben‘, ‘Park Lane 38‘)
# (‘William‘, ‘Central st 954‘)
# (‘Chuck‘, ‘Main Road 989‘)
# (‘Viola‘, ‘Sideway 1633‘)

使用fetchone()方法

如果只想获取一行记录，可以使用fetchone()方法。

fetchone()方法将返回结果的第一行:

示例

只取一行:

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()

mysql_cursor.execute("SELECT * FROM customers")

result = mysql_cursor.fetchone()
print(result)

# (‘John‘, ‘Highway 21‘, 1)

筛选数据

从表中选取记录时，可以使用“WHERE”语句筛选:

示例

选取地址为“Park Lane 38”的记录:

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()
sql = "SELECT * FROM customers WHERE address =‘Park Lane 38‘"
mysql_cursor.execute(sql)

result = mysql_cursor.fetchall()
for x in result:
    print(x)
    
# (‘Ben‘, ‘Park Lane 38‘, 11)

通配符

WHERE语句中可以使用通配符%。关于SQL中，WHERE子句使用通配符

示例

选取地址中包含单词“way”的记录:

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()
sql = "SELECT * FROM customers WHERE address LIKE ‘%way%‘"
mysql_cursor.execute(sql)

result = mysql_cursor.fetchall()
for x in result:
    print(x)

# (‘John‘, ‘Highway 21‘, 1)
# (‘Susan‘, ‘One way 98‘, 9)
# (‘Viola‘, ‘Sideway 1633‘, 14)

防止SQL注入

当用户提供查询值时，为了防止SQL注入，应该转义这些值。

SQL注入是一种常见的web黑客技术，用于破坏或误用数据库。

mysql.connector 模块有方法可以转义查询值:

示例

使用占位符%s方法转义查询值:

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()
sql = "SELECT * FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )
mysql_cursor.execute(sql,adr)

result = mysql_cursor.fetchall()
for x in result:
    print(x)

# (‘Vicky‘, ‘Yellow Garden 2‘, 10)

对结果排序

可以使用ORDER BY语句，按升序或降序对结果排序。

默认情况下，ORDER BY关键字按升序排列结果。要按降序排列，可使用DESC关键字。

示例

按name的字母顺序排列结果:

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()
sql = "SELECT * FROM customers ORDER BY name"

mysql_cursor.execute(sql)

result = mysql_cursor.fetchall()
for x in result:
    print(x)
    
# (‘Amy‘, ‘Apple st 652‘, 3)
# (‘Ben‘, ‘Park Lane 38‘, 11)
# (‘Betty‘, ‘Green Grass 1‘, 7)
# (‘Chuck‘, ‘Main Road 989‘, 13)
# (‘Hannah‘, ‘Mountain 21‘, 4)
# (‘John‘, ‘Highway 21‘, 1)
# (‘Michael‘, ‘Valley 345‘, 5)
# (‘Peter‘, ‘Lowstreet 4‘, 2)
# (‘Richard‘, ‘Sky st 331‘, 8)
# (‘Sandy‘, ‘Ocean blvd 2‘, 6)
# (‘Susan‘, ‘One way 98‘, 9)
# (‘Vicky‘, ‘Yellow Garden 2‘, 10)
# (‘Viola‘, ‘Sideway 1633‘, 14)
# (‘William‘, ‘Central st 954‘, 12)

ORDER BY DESC

使用DESC关键字，可按降序对结果排序。

示例

按name的字母降序，对结果进行排序:

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()
sql = "SELECT * FROM customers ORDER BY name DESC"

mysql_cursor.execute(sql)

result = mysql_cursor.fetchall()
for x in result:
    print(x)

# (‘William‘, ‘Central st 954‘, 12)
# (‘Viola‘, ‘Sideway 1633‘, 14)
# (‘Vicky‘, ‘Yellow Garden 2‘, 10)
# (‘Susan‘, ‘One way 98‘, 9)
# (‘Sandy‘, ‘Ocean blvd 2‘, 6)
# (‘Richard‘, ‘Sky st 331‘, 8)
# (‘Peter‘, ‘Lowstreet 4‘, 2)
# (‘Michael‘, ‘Valley 345‘, 5)
# (‘John‘, ‘Highway 21‘, 1)
# (‘Hannah‘, ‘Mountain 21‘, 4)
# (‘Chuck‘, ‘Main Road 989‘, 13)
# (‘Betty‘, ‘Green Grass 1‘, 7)
# (‘Ben‘, ‘Park Lane 38‘, 24)
# (‘Amy‘, ‘Apple st 652‘, 16)

删除记录

可以使用“DELETE FROM”语句，从现有表中删除记录:

示例

删除地址为“Mountain 21”的记录:

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()

sql = "DELETE FROM customers WHERE address = ‘Mountain 21‘"
mysql_cursor.execute(sql)
mysql.commit()

print(mysql_cursor.rowcount, " 条记录删除")

# 1  条记录删除

注意： 数据库修改后，需要使用mysql_cursor.commit()语句提交，不提交，修改不会生效。注意DELETE语句中的WHERE子句: WHERE子句指定应该删除哪些记录。如果省略WHERE子句，将删除所有记录!

防止SQL注入

在delete语句中，为了防止SQL注入，通常应该转义查询值。

SQL注入是一种常见的web黑客技术，用于破坏或误用数据库。

mysql.connector 模块有方法可以转义查询值:

示例

使用占位符%s方法转义查询值：

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()

sql = "DELETE FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )
mysql_cursor.execute(sql,adr)
mysql.commit()

print(mysql_cursor.rowcount, " 条记录删除")

# 1  条记录删除

删除表

可以使用“DROP table”语句，删除现有的表:

示例

删除表“customers”:

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库

mysql_cursor = mysql.cursor()
mysql_cursor.execute("DROP TABLE customers")

仅当表存在时才删除

如果要删除的表不存在，会报错，可以使用If EXISTS关键字判断表是否存在，避免报错。

示例

删除存在的表“customers”:

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库

mysql_cursor = mysql.cursor()
mysql_cursor.execute("DROP TABLE IF EXISTS customers")

更新表

可以使用“UPDATE”语句，更新表格内的现有记录:

示例

将地址栏由“Valley 345”改写为“Canyoun 123”

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()
sql = "UPDATE customers SET address = ‘Canyon 123‘ WHERE address = ‘Valley 345‘"
mysql_cursor.execute(sql)
mysql.commit()
print(mysql_cursor.rowcount, " 条记录已更新")

# 1  条记录已更新

防止SQL注入

在update语句中，为了防止SQL注入，通常应该转义查询值。

SQL注入是一种常见的web黑客技术，用于破坏或误用数据库。

mysql.connector 模块有方法可以转义查询值:

示例

使用占位符%s方法转义查询值：

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()
sql = "UPDATE customers SET address = %s WHERE address = %s"
val = ("Valley 345", "Canyon 123")
mysql_cursor.execute(sql,val)
mysql.commit()
print(mysql_cursor.rowcount, " 条记录已更新")

# 1  条记录已更新

限制结果数量

可以使用“LIMIT”语句，限制查询返回的记录数量:

示例

在“customers”表中，选择前5条记录:

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()
sql = ‘SELECT * FROM customers LIMIT 5‘
mysql_cursor.execute(sql)

result = mysql_cursor.fetchall()
for x in result:
    print(x)
    
# (‘Peter‘, ‘Lowstreet 4‘)
# (‘Amy‘, ‘Apple st 652‘)
# (‘Hannah‘, ‘Mountain 21‘)
# (‘Michael‘, ‘Valley 345‘)
# (‘Sandy‘, ‘Ocean blvd 2‘)

从指定位置开始

如果想返回，从第3条记录开始的5条记录，可以使用“OFFSET”关键字:

示例

从位置3开始，返回5条记录:

import MySQLdb

mysql = MySQLdb.connect(host = "localhost",
                        user = "root",
                        passwd = ‘sutaoyu01‘,
                        database = "testdb") #进入testdb数据库
mysql_cursor = mysql.cursor()
sql = ‘SELECT * FROM customers LIMIT 5 OFFSET 2‘
mysql_cursor.execute(sql)

result = mysql_cursor.fetchall()
for x in result:
    print(x)

# (‘Hannah‘, ‘Mountain 21‘)
# (‘Michael‘, ‘Valley 345‘)
# (‘Sandy‘, ‘Ocean blvd 2‘)
# (‘Betty‘, ‘Green Grass 1‘)
# (‘Richard‘, ‘Sky st 331‘)

mysql python连接mysql mysql创建数据库数据库 python