Python操作MySQL数据库

来自CloudWiki
跳转至： 导航， 搜索

操作MySQL数据库

安装PyMySQL

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。 在命令行中使用： 

pip3 install pymysql

建立数据库

C:\Users\maxin>mysql -uroot -p 

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

CREATE DATABASE `notebook` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 

Query OK, 1 row affected (0.00 sec)

数据库连接

Python连接数据库代码： 

import pymysql

host = '127.0.0.1'    # 主机名
user = 'root'         # 数据库用户名
password = '123456'     # 数据库密码
database = 'notebook' # 数据库名称
db = pymysql.connect(host=host,user=user,password=password,db=database) # 建立连接
# 打开数据库连接

#这4个参数依次是：主机名，用户名，密码和数据库名
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# 使用 execute()  方法执行 SQL 查询 
cursor.execute("SELECT VERSION()")
 
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
 
print ("Database version : %s " % data)
 
# 关闭数据库连接
db.close()

执行以上脚本输出结果如下： 

Database version : 5.0.22-community-nt

创建数据库表

import pymysql

host = '127.0.0.1'    # 主机名
user = 'root'         # 数据库用户名
password = '000000'     # 数据库密码
database = 'notebook' # 数据库名称
db = pymysql.connect(host=host,user=user,password=password,db=database) # 建立连接
# 打开数据库连接

#这4个参数依次是：主机名，用户名，密码和数据库名
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS users")
 
# 使用预处理语句创建表
sql = """CREATE TABLE `users` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
 
cursor.execute(sql)
 
# 关闭数据库连接
db.close()

终端验证

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| notebook           |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use notebook
Database changed
mysql> show tables
    -> ;
+--------------------+
| Tables_in_notebook |
+--------------------+
| users              |
+--------------------+
1 row in set (0.00 sec)

数据库插入操作

示例1

import pymysql

host = '127.0.0.1'    # 主机名
user = 'root'         # 数据库用户名
password = '000000'     # 数据库密码
database = 'notebook' # 数据库名称
db = pymysql.connect(host=host,user=user,password=password,db=database) # 建立连接
# 打开数据库连接

#这4个参数依次是：主机名，用户名，密码和数据库名
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
email = 'maxin5@163.com'
username = 'maxin5'
password ='123456'
# 使用预处理语句创建表

sql = "INSERT INTO users(username,email,password) VALUES ('%s', '%s', '%s')" % (email,username,password)
 
try:
   # 执行sql语句
   cursor.execute(sql)
   # 执行sql语句
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()
 
 
# 关闭数据库连接
db.close()

示例2

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","root","000000","cloud" )
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# SQL 插入语句
sql = "INSERT INTO EMPLOYER(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
#这里使用的是预编译用法，%s,%d,%c分别指代字符串、数字和字符
try:
   # 执行sql语句
   cursor.execute(sql)
   # 执行sql语句
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()
 
# 关闭数据库连接
db.close()

数据库查询操作

Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

  • fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
  • fetchall(): 接收全部的返回结果行.
  • rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。

实例1

import pymysql

host = '127.0.0.1'    # 主机名
user = 'root'         # 数据库用户名
password = '000000'     # 数据库密码
database = 'store' # 数据库名称
db = pymysql.connect(host=host,user=user,password=password,db=database) # 建立连接
# 打开数据库连接

#这4个参数依次是：主机名，用户名，密码和数据库名
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# SQL 查询语句
sql = "SELECT * FROM users"
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 获取所有记录列表
   results = cursor.fetchall()
   for row in results:
      name=row[2]
      email=row[1]
      password=row[3]
       # 打印结果
      print ("name=%s,email=%s,password=%s" % \
             (name, email,password))
except:
   print ("Error: unable to fetch data")
 
# 关闭数据库连接
db.close()

实例2

查询EMPLOYEE表中salary（工资）字段大于1000的所有数据： 

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","root","000000","cloud" )
 
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
 
# SQL 查询语句
sql = "SELECT * FROM EMPLOYER \
       WHERE INCOME > '%d'" % (1000)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 获取所有记录列表
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
       # 打印结果
      print ("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
             (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")
 
# 关闭数据库连接
db.close()

数据库更新操作

数据库删除操作

执行事务

返回 文件和数据格式化（输入与输出）

取自“http://www.openbrains.net/mediawiki/index.php?title=Python操作MySQL数据库&oldid=29235”