python连接mysql
一. 安装pymysql
sudo pip install pymysql
设置编码格式, 引入MySQL接口module
#!/usr/bin/python
# 设置编码格式为utf-8
# -*-coding=utf-8-*-
# 或者
# _*_ coding:utf-8 _*_
# 引入MySQL操作接口
import pymysql
Mysql.connect(参数);用于连接MySQLAIP
host MySQL服务区地址
port 端口号
user 用户名
passwd 密码
db 数据库
charset 连接编码
connection对象支持的方法;建立数据库的连接
cursor(); 使用连接创建并返回游标
commit(); 提交当前事务
autocommit(); 自动提交事务
rollback(); 回滚当前事务
close(): 关闭连接
cursor对象用于查询和获取结果,支持的对象:
excute(); 支持一个MySQL语句,返回受影响行
excutemany(); 支持多个MySQL插入语句,返回受影响行
fetchone(); 获取结果集的下一行
fetchmany(); 获取结果集的下几行
fetchall(); 获取结果集中剩下的所有行
rowcount; 最近一次excute返回的受影响行数
lastrowid(); 获取自增ID
close(); 关闭游标对象
注释:
fetch方法,每次的运行都是在上一次的结果的最后一行开始获取.
如:
第一次fetchone 得到uid1,
第二次fetchone 得到uid2,
而如果放在fetchall后面, 得到是None
事务
事务: 访问和更新数据库的一个程序执行单元
-原子性: 事务中包含的诸多操作,要么都做,要么都不做.
-一致性: 事务必须让数据库从一致性状态,编导另一个一致性状态.
-隔离行: 一个事务的执行不能被其他事务干扰
-持久性: 事务一旦提交, 对数据库的改变是永久性的.
开发中开启事务:
- 关闭自动提交conmmit: 设置conn.autocommit(false)
- 正常提交: conn.commit();
- 异常回滚: conn.rooback();
实例:
- 连接数据库并且进行基础测试
# -*-coding=utf-8-*-
# pymysql 和Mysqldb的操作方式是一样的,据传pymysql是未来趋势
import pymysql
# import Mysqldb
# 数据库句柄
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='',
db='test',
charset='utf8'
# cursorclass = pymysql.cursors.DictCursor #此处可使返回结果为字典dict
)
# 激活游标
cursor = conn.cursor()
# 查询数据库
sql = 'select * from USER '
ec = cursor.execute(sql) # 此处ec获取的也是受影响行数,print 结果4096
# 获得受影响行数 print 结果4096
rowcount = cursor.rowcount
# 查询一条
one = cursor.fetchone()
# 再查一条
one1 = cursor.fetchone()
# 查询多条
many = cursor.fetchmany(3)
# 查询所有数据
all = cursor.fetchall()
print(rowcount)
print(one)
print(one1)
print(many)
print(all)
'''
经过以上测试得出:
fetch方式每次得到的结果都是在,前一个fetch的最后一行开始计算的.
如:
第一次fetchone 得到的是uid1
第二次fetchone 得到的是uid2
假如将fetchone 放在fetchall后面,得到的将是None
'''
- 使用select和fetchmany查询语句
# -*-coding=utf-8-*-
import pymysql
'''
执行一次真正的select操作
'''
# 打开资源
conn = pymysql.connect(
host = '127.0.0.1',
user = 'root',
passwd = '',
db = 'test',
charset = 'utf8'
# cursorclass = pymysql.cursors.DictCursor #此处可使返回结果为字典dict
)
# 激活游标
cursor = conn.cursor();
# 执行sql操作
sql = 'select * from user '
cursor.execute(sql)
# 获取指定个数的结果集
result = cursor.fetchmany(100)
# 遍历结果集
for row in result:
# 格式化数据输出
print "uid=%d,name=%s,sex=%d,username=%s" %row
# 关闭资源和游标
cursor.close()
conn.close()
- insert/update/delete操作数据库
# -*-coding=utf-8-*-
import pymysql
# 打开资源
conn = pymysql.connect(
host = '127.0.0.1',
user = 'root',
passwd = '',
db = 'test',
charset = 'utf8'
# cursorclass = pymysql.cursors.DictCursor #此处可使返回结果为字典dict
)
# 激活游标
cursor = conn.cursor()
# sql
sql_insert = 'insert into user(name,username,cny) values("wangwu","wag","1000")'
sql_update = "update user set username='laowu' where uid=4"
sql_delete = "delete from user where uid>100"
# 使用try catch 判断是否成功
try:
# 执行插入
cursor.execute(sql_insert)
# 更新
cursor.execute(sql_update)
# 删除
cursor.execute(sql_delete)
# 提交数据
conn.commit()
except Exception as e:
# 回滚数据
conn.rollback()
print e
finally: # finaly 无论结果如何都将执行
# 关闭资源
conn.close()
cursor.close()
- 在execute做update/insert操作时利用元组tuple一次做多个赋值
# coding:utf-8
import pymysql
import sys
import json
# 连接 mysql,获取连接的对象
conn = pymysql.connect('localhost', 'root', '', 'test');
cursor = conn.cursor()
sql = "UPDATE user SET name = %s WHERE uid = %s"
try:
# 如果某个数据库支持事务,会自动开启
cursor.execute(sql, ('aa','15'))
cursor.execute(sql, ('ss','20'))
conn.commit()
if cursor.rowcount >= 1:
print '修改成功'
except Exception as e:
print(e)
conn.rollback()
finally:
cursor.close()
conn.close()
- executemany做insert/update操作一次插入多个值
# _*_ coding:utf-8 _*_
import pymysql
# 连接MySQL
conn = pymysql.connect(
host='127.0.0.1',
user='root',
password='',
database='python',
charset='utf8',
port=3306
)
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
# 此处的""""""不单单是注释符号,
# 同时需要注意%s,如果下面的exec中输入的是字符串,就不用加'或"了
# 如果没有在execute中格式化,而是单独用%格式化的,需要注意输入的字符串问题
sql_insert = """insert into zufang(id,title,money) values(null,%s,%s)"""
sql_update = """update zufang set title=%s where id=%s"""
try:
# 返回受影响行,注意,这里后面的多个参数用的的list包着元组
rowCount = cursor.executemany(sql_insert, [('a', '100'), ('b', '200'), ('3', '300')])
rowCount = cursor.executemany(sql_update, [('aaa', 1), ('bbb', 2)])
# 提交
conn.commit()
# 输出受影响行
print(rowCount)
# 或者
print(cursor.rowcount())
except Exception as e:
# 错误回滚
conn.rollback()
# 输出错误信息
print(e)
finally:
conn.close()
- 模拟sql注入
# _*_ coding:utf-8 _*_
import pymysql
# 连接MySQL
conn = pymysql.connect(
host='127.0.0.1',
user='root',
password='',
database='python',
charset='utf8',
port=3306
)
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
# 此处的"数字--"可以查询出所有,其实不加斜线也是可以查所有的.
# 原理在于, select * from user where uid=1 or 2
# 此处uid='..or ...'没有引号就会, 有引号的话以第一个值为准.
id = '126 or 125 --'
title = "'liu'"
sql_select = "select * from zufang where id=%s or title=%s" % (id, title)
print(sql_select)
try:
# 返回受影响行
rowCount = cursor.execute(sql_select)
# 提交
conn.commit()
# 输出受影响行
print(rowCount)
# 或者
print(cursor.rowcount())
# 输入所有结果集
print(cursor.fetchall())
except Exception as e:
# 错误回滚
conn.rollback()
# 输出错误信息
print(e)
finally:
conn.close()
- 防止SQL注入
# 主要方式就是使用pymysql的,参数化查询,
# 不要在excute外面进行格式化后,再执行
# 当然,也可将SQL准备好%格式,赋值一个变量,然后放在excute中进行格式化
# _*_ coding:utf-8 _*_
import pymysql
# 连接MySQL
conn = pymysql.connect(
host='127.0.0.1',
user='root',
password='',
database='python',
charset='utf8',
port=3306
)
# 使用cursor()方法获取操作游标
cursor = conn.cursor()
# 此处的"数字--"可以查询出所有
# id = '126 or 125 --'
# title = "'liu'"
#
sql_select = "select * from zufang where id=%s or title=%s"
print(sql_select)
try:
# #执行参数化查询,返回受影响行
rowCount = cursor.execute(sql_select, ('126 or 125 --', 'liu'))
# 提交
conn.commit()
# 输出受影响行
print(rowCount)
# 或者
print(cursor.rowcount())
# 输入所有结果集
print(cursor.fetchall())
except Exception as e:
# 错误回滚
conn.rollback()
# 输出错误信息
print(e)
finally:
conn.close()
- 使用with简化连接过程
# 每次都连接关闭很麻烦,使用上下文管理,简化连接过程
# 百度到的方式
#! /usr/bin/env python
# -*- coding:utf-8 -*-
# __author__ = "TKQ"
import pymysql
import contextlib
#定义上下文管理器,连接后自动关闭连接,必须在一个函数上面.
@contextlib.contextmanager
def mysql(host='127.0.0.1', port=3306, user='root', passwd='', db='tkq1',charset='utf8'):
conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset=charset)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
try:
yield cursor
finally:
conn.commit()
cursor.close()
conn.close()
# 执行sql
with mysql() as cursor:
print(cursor)
row_count = cursor.execute("select * from tb7")
row_1 = cursor.fetchone()
print row_count, row_1
- 终结篇 利用事务机制进行银行转账的操作