Python操作MySQL数据库的三种方法

 数据库编程

从前面我们知道数据库概念包含
数据库文件、服务器和数据库客户端
客户端我们之前已经用过的有navicat/mysql-client等程序。

问题:
如何使用客户端将100000行数据插入到数据库?

大家会发现如果用之前客户端几乎是不可能完全这个任务的,
因为我们不可能去构造出那个插入10w行数据的SQL。可是,如果我们有一个功能能够插入一行数据,借助程序强大的特点-重复,就可以轻而易举的就将10w行数据收入麾下。这种通过使用程序代码的方式去连接数据库服务器,通过和服务器进行交互完成对数据库的增删改查的方式,就称为数据库编程。

而此刻学习的pymysql就是一种客户端。

 
  #sql = “INSERT INTO Student(ID, Name, Grade) \
 
  #    VALUES (‘%s’, ‘%s’, ‘%d’)” % \
 
  #    (‘001’, ‘HP’, 60)
 
  try:
 
      # 执行sql语句
 
      cursor.execute(sql)
 
      # 提交到数据库执行
 
      db.commit()
 
  except:
 
      # Rollback in case there is any error
 
      print ‘插入数据失败!’
 
      db.rollback()

第一步   安装引入模块

1.安装mysql模块(python3)

pip install pymysql

2.在文件中引入

import pymysql

Connection 对象

  • 用于建立与数据库的连接
    调用pymysql模块中的connect()方法

conn=connect(参数列表)

* 参数host:连接的mysql主机,如果本机是'localhost'
* 参数port:连接的mysql主机的端口,默认是3306
* 参数database:数据库的名称
* 参数user:连接的用户名
* 参数password:连接的密码
* 参数charset:通信采用的编码方式,推荐使用utf8
  • 关闭连接 conn.close()

  • 提交数据 conn.commit()

  • 撤销数据 conn.rollback()

  • 通过连接获取游标
    cur = conn.cursor()返回Cursor对象,用于执行sql语句并获得结果

 
  try:
 
     # 执行SQL语句
 
     cursor.execute(sql)
 
     # 提交修改
 
     db.commit()
 
  except:
 
      print ‘删除数据失败!’
 
      # 发生错误时回滚
 
      db.rollback()

(详解)

pymysql完成对数据库的增删改

import pymysql

conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='mysql',db='python_test_1', charset='utf8')

# 通过连接获取游标
cursor = conn.cursor()


# sql = "delete from hero where id = 5;"
# sql = insert into hero (name) values ('西部大坏蛋孙悟空');
sql = "update hero set kongfuid=444 where id = 4;"

row_count = cursor.execute(sql)
print("SQL语句执行影响的行数%d" % row_count)

# 提交数据到数据库
# conn.commit()

# 回滚数据到什么都不做的状态 即撤销刚刚的修改
conn.rollback()

# 关闭游标和连接
cursor.close()
conn.close()

关于提交commit
commit将修改提交到数据库,保存修改

style=”font-size: 14px”>注意pymysql中数据需要手动提交commit才能保存到数据库中

cursor用来执行命令的方法:
callproc(self,
procname,
args):用来执行存储过程,接收的参数为存储过程名和参数列表,返回值为受影响的行数
execute(self,
query,
args):执行单条sql语句,接收的参数为sql语句本身和使用的参数列表,返回值为受影响的行数
executemany(self,
query,
args):执行单挑sql语句,但是重复执行参数列表里的参数,返回值为受影响的行数
nextset(self):移动到下一个结果集

完整步骤(举例)

# 1 . 引包

import pymysql

# 2. 创建连接

conn = pymysql.connect(host=’127.0.0.1′, port=3306, user=’用户名’,
passwd=’密码’, db=’数据库名’,charset=”utf8″)

# 3 . 创建对象

cur = conn.cursor()

# 4 . 执行语句

cur.execute(“delete from user where id=1”)

# 5 . 提交

conn.commit() 提交(除查询,需要提交数据。)

# 6 . 关闭连接

cur.close()

conn.close()


Cursor游标对象

  • 获取Cursor对象

      # 调用Connection对象的cursor()方法    
      cur =conn.cursor()
    

目的:
执行sql语句(使用频度最高的语句为select、insert、update、delete)

  • 使用游标执行SQL语句

execute(operation [, parameters ])
执行SQL语句,返回受影响的行数,主要用于执行insert、update、delete语句,也可以执行create、alter、drop等语句

  • 关闭游标 cur.close()

  • 获取结果集中的一条

    cur.fetchone()返回一个元组 形如
    (1,’妲己’,18)

  • 获取结果集中的所有

    cur.fetchall()执行查询时,获取结果集的所有行,一行构成一个元组,再将这些元组装入一个元组返回
    形如((1,’公孙离’,20),(2,’妲己’,18))

 
  # SQL 更新语句
 
  sql = “UPDATE Student SET Grade = Grade + 3 WHERE ID = ‘%s'” %
(‘003’)

第二步   Connection对象

1.用于建立与数据库的连接

2.创建对象:调用connect()方法

● 参数host:连接的mysql主机,如果本机是’localhost’

● 参数port:连接的mysql主机的端口,默认是3306

● 参数db:数据库的名称

● 参数user:连接的用户名

● 参数password:连接的密码

● 参数charset:通信采用的编码方式,默认是’gb2312’,要求与数据库创建时指定的编码一致,否则中文会乱码

例如:conn = pymysql.connect(host=’127.0.0.1′, port=3306, user=’用户名’,
passwd=’密码’, db=’数据库名’,charset=”utf8″)

pymysql完成数据查询

import pymysql

# 创建和数据库服务器的连接 服务器地址   端口    用户名     密码  数据库名 通信使用字符和数据库字符集一致
conn = pymysql.connect(host='localhost', port=3306, user='root', password='mysql',database='python_test_1', charset='utf8')

# 获取游标
cursor = conn.cursor()

# 执行SQL语句 返回值就是SQL语句在执行过程中影响的行数
sql = """select * from hero;"""

row_count = cursor.execute(sql)
print("SQL语句执行影响的行数%d" % row_count)

# 取出结果集中一行  返回的结果是一行 (1, '妲己', 2)
# print(cursor.fetchone())

# 取出结果集中的所有数据  返回 ((一行数据),(),())
# ((1, '妲己', 2), (2, '李白', 1), (3, '程咬金', 3), (4, '亚瑟', 5), (5, '荆轲', 99))
for line in cursor.fetchall():
    print(line)

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

(3)
使用 PyMySQL:

第三步   cursor对象

1.执行sql语句

2.创建对象:调用Connection对象的cursor()方法

例如:cur=conn.cursor()

 


def
insertdb(db):
 
  # 使用cursor()方法获取操作游标 
 
  cursor = db.cursor()
 
  # SQL 插入语句
 
  sql = “””INSERT INTO Student
 
       VALUES (‘001’, ‘CZQ’, 70),
 
              (‘002’, ‘LHQ’, 80),
 
              (‘003’, ‘MQ’, 90),
 
              (‘004’, ‘WH’, 80),
 
              (‘005’, ‘HP’, 70),
 
              (‘006’, ‘YF’, 66),
 
              (‘007’, ‘TEST’, 100)”””

第四部   对数据进行操作

cur.execute(“执行语句”)

pymysql的使用

引入模块

from pymysql import connect

import
pymysql  
  
#
打开数据库连接(ip/数据库用户名/登录密码/数据库名)  
db = pymysql.connect(“localhost”, “root”,
“root”, “test”)  
#
使用 cursor() 方法创建一个游标对象 cursor  
cursor = db.cursor()  
  
#
使用 execute()  方法执行 SQL 查询  
cursor.execute(“SELECT
VERSION()”)  
#
使用 fetchone() 方法获取单条数据.  
data = cursor.fetchone()  
print(“Database
version : %s ” % data)  
  
#
关闭数据库连接  
db.close()
  

对象的方法

● close()关闭

● execute(operation [, parameters ])执行语句,返回受影响的行数

● fetchone()执行查询语句时,获取查询结果集的第一个行数据,返回一个元组

● next()执行查询语句时,获取当前行的下一行

● fetchall()执行查询时,获取结果集的所有行,一行构成一个元组,再将这些元组装入一个元组返回

● scroll(value[,mode])将行指针移动到某个位置

                ○ mode表示移动的方式

                ○ mode的默认值为relative,表示基于当前行移动到value,value为正则向下移动,value为负则向上移动

               ○ mode的值为absolute,表示基于第一条数据的位置,第一条数据的位置为0

 Python 中操作 MySQL 步骤

如何理解连接 connection 和 游标 cursor
connection就像是连接出发地和目的地的 高速公路
cursor就像是在高速公路上的货车-拉货 我们使用游标就可以完成对数据的操作
当我们完成操作完成后就可以停下货车,然后公路再停止使用。

import
pymysql  
  
#
打开数据库连接(ip/数据库用户名/登录密码/数据库名)  
db
= pymysql.connect(“localhost”, “root”, “root”, “test”)  
#
使用 cursor() 方法创建一个游标对象 cursor  
cursor
= db.cursor()  
  
#
SQL 更新语句  
sql
= “UPDATE user SET name = ‘Bob’ WHERE id = 1”  
try:
 
 
  # 执行SQL语句  
 
  cursor.execute(sql)  
 
  # 提交到数据库执行  
 
  db.commit()  
except:
 
 
  # 发生错误时回滚  
 
  db.rollback()  
 
   
#
关闭数据库连接  
db.close()
 

对象的方法

● close()关闭连接

● commit()事务,所以需要提交才会生效

● rollback()事务,放弃之前的操作

● cursor()返回Cursor对象,用于执行sql语句并获得结果

参数化列表防止SQL注入

什么是SQL注入 产生原因:
后台将用户提交的带有恶意的数据和SQL进行字符串方式的拼接,从而影响了SQL语句的语义,最终产生数据泄露的现象。
如果防止: sql语句的参数化,
将SQL语句的所有数据参数存在一个列表中传递给execute函数的第二个参数

注意

* 此处不同于python的字符串格式化,必须全部使用%s占位
* 所有参数所需占位符外不需要加引号
from pymysql import connect

def main():

    find_name = input("请输入物品名称:")

    # 创建Connection连接
    conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
    # 获得Cursor对象
    cs1 = conn.cursor()


    # # 非安全的方式
    # # 输入 " or 1=1 or "   (双引号也要输入)
    # sql = 'select * from goods where name="%s"' % find_name
    # print("""sql===>%s<====""" % sql)
    # # 执行select语句,并返回受影响的行数:查询所有数据
    # count = cs1.execute(sql)

    # 安全的方式
    # 构造参数列表 
    params = [find_name]
    # 执行select语句,并返回受影响的行数:查询所有数据
    count = cs1.execute('select * from goods where name=%s', params)
    # 注意:
    # 如果要是有多个参数,需要进行参数化
    # 那么params = [数值1, 数值2....],此时sql语句中有多个%s即可 

    # 打印受影响的行数
    print(count)
    # 获取查询的结果
    # result = cs1.fetchone()
    result = cs1.fetchall()
    # 打印查询的结果
    print(result)
    # 关闭Cursor对象
    cs1.close()
    # 关闭Connection对象
    conn.close()

if __name__ == '__main__':
    main()

import
MySQLdb

对象的属性

● rowcount只读属性,表示最近一次execute()执行后受影响的行数

● connection获得当前连接对象

import
MySQLdb  
    
try:
 
 
  conn =MySQLdb.Connect(host=’192.168.8.40′,user=’root’,passwd=’root’,db=’mysql’,port=3306,charset=’utf8′)  
 
    
 
  cur = conn.cursor()  
 
    
 
  conn.select_db(‘python’)  
 
    
 
  count = cur.execute(‘select * from test’)   
 
    
 
  print ‘there has %s rows record’ % count  
 
    
 
  result = cur.fetchone()  
 
  print result  
 
  print ‘id: %s info %s’ % result  
 
    
 
  result2 = cur.fetchmany(3)  
 
  for record in result2:  
 
      print record  
 
        
 
  print ‘==’*10  
 
  cur.scroll(0,mode=’absolute’)  
 
    
 
  result3 = cur.fetchall()  
 
  for record in result3:  
 
      print record[0] ,’—‘,record[1]  
 
    
 
  conn.commit()  
 
  cur.close()  
 
  conn.close()  
except
MySQLdb.Error,e:  
 
  print ‘mysql error msg: %d,%s’ % (e.args[0],e.args[1]) 

 
  try:
 
     # 执行SQL语句
 
     cursor.execute(sql)
 
     # 提交修改
 
     db.commit()
 
  except:
 
      print ‘删除数据失败!’
 
      # 发生错误时回滚
 
      db.rollback()

def
deletedb(db):
 
  # 使用cursor()方法获取操作游标 
 
  cursor = db.cursor()


由于 MySQL 服务器以独立的进程运行,并通过网络对外服务,所以,需要支持
Python 的 MySQL 驱动来连接到 MySQL 服务器。

 
  createtable(db)     # 创建表
 
  insertdb(db)        # 插入数据
 
  print ‘\n插入数据后:’
 
  querydb(db) 
 
  deletedb(db)        # 删除数据
 
  print ‘\n删除数据后:’
 
  querydb(db)
 
  updatedb(db)        # 更新数据
 
  print ‘\n更新数据后:’
 
  querydb(db)

def
createtable(db):
 
  # 使用cursor()方法获取操作游标 
 
  cursor = db.cursor()

 
  # SQL 插入语句
 
  sql = “””INSERT INTO Student
 
       VALUES (‘001’, ‘CZQ’, 70),
 
              (‘002’, ‘LHQ’, 80),
 
              (‘003’, ‘MQ’, 90),
 
              (‘004’, ‘WH’, 80),
 
              (‘005’, ‘HP’, 70),
 
              (‘006’, ‘YF’, 66),
 
              (‘007’, ‘TEST’, 100)”””

$
tar zxvf MySQL-python-*.tar.gz
$
cd MySQL-python-*
$
python setup.py build
$
python setup.py install 

import
pymysql  
  
#
打开数据库连接(ip/数据库用户名/登录密码/数据库名)  
db = pymysql.connect(“localhost”, “root”,
“root”, “test”)  
#
使用 cursor() 方法创建一个游标对象 cursor  
cursor = db.cursor()  
  
#
SQL 插入语句  
sql
= “””INSERT INTO user(name) 
 
       VALUES (‘Mac’)”””  
try:
 
 
 # 执行sql语句  
 
 cursor.execute(sql)  
 
 # 提交到数据库执行  
 
 db.commit()  
except:
 
 
 # 如果发生错误则回滚  
 
 db.rollback()  
  
#
关闭数据库连接  
db.close()
  

import
pymysql  
  
#
打开数据库连接(ip/数据库用户名/登录密码/数据库名)  
db
= pymysql.connect(“localhost”, “root”, “root”, “test”)  
#
使用 cursor() 方法创建一个游标对象 cursor  
cursor
= db.cursor()  
  
#
SQL 查询语句  
sql
= “SELECT * FROM user”  
  
try:
 
 
  # 执行SQL语句  
 
  cursor.execute(sql)  
 
  # 获取所有记录列表  
 
  results =
cursor.fetchall()  
 
  for row in results:  
 
      id = row[0]  
 
      name = row[1]  
 
      # 打印结果  
 
      print(“id=%s,name=%s” % \  
 
            (id, name))  
except:
 
 
  print(“Error: unable to fecth data”)  
  
#
关闭数据库连接  
db.close()
 

(3)
使用 mysql.connector:

 
  # SQL 插入语句
 
  sql = “””INSERT INTO Student
 
       VALUES (‘001’, ‘CZQ’, 70),
 
              (‘002’, ‘LHQ’, 80),
 
              (‘003’, ‘MQ’, 90),
 
              (‘004’, ‘WH’, 80),
 
              (‘005’, ‘HP’, 70),
 
              (‘006’, ‘YF’, 66),
 
              (‘007’, ‘TEST’, 100)”””

 
  createtable(db)     # 创建表
 
  insertdb(db)        # 插入数据
 
  print ‘\n插入数据后:’
 
  querydb(db) 
 
  deletedb(db)        # 删除数据
 
  print ‘\n删除数据后:’
 
  querydb(db)
 
  updatedb(db)        # 更新数据
 
  print ‘\n更新数据后:’
 
  querydb(db)

(2)
安装 PyMysql:

 
  try:
 
     # 执行SQL语句
 
     cursor.execute(sql)
 
     # 提交修改
 
     db.commit()
 
  except:
 
      print ‘删除数据失败!’
 
      # 发生错误时回滚
 
      db.rollback()

#!/usr/bin/env
python
import
MySQLdb  
 
 
try:
 
 
 conn=MySQLdb.connect(host=’localhost’,user=’root’,passwd=’root’,db=’test’,port=3306)  
 
  cur=conn.cursor()  
 
  cur.execute(‘select user,password from
user’)  
 
  cur.close()  
 
  conn.close()  
except
MySQLdb.Error,e:  
 
   print “Mysql Error %d: %s” % (e.args[0], e.args[1])   

import
pymysql


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

def
updatedb(db):
 
  # 使用cursor()方法获取操作游标 
 
  cursor = db.cursor()

def
connectdb():
 
  print(‘连接到mysql服务器…’)
 
  # 打开数据库连接
 
  # 用户名:p,
密码:12345.,用户名和密码需要改成你自己的mysql用户名和密码,并且要创建数据库TESTDB,并在TESTDB数据库中创建好表Student
 
  db = mysql.connector.connect(user=”p”,
passwd=”12345.”, database=”TESTDB”,use_unicode=True)
 
  print(‘连接上了!’)
 
  return db

#!/usr/bin/env
python
#
coding=utf-8

 
  try:
 
      # 执行SQL语句
 
      cursor.execute(sql)
 
      # 提交到数据库执行
 
      db.commit()
 
  except:
 
      print ‘更新数据失败!’
 
      # 发生错误时回滚
 
      db.rollback()