pypy mysql_Python连接MySQL数据库

常用函数

Python DB API 2.0 对事务提供了两个方法：

commit() 提交

rollback() 回滚

cursor用来执行命令的方法:

callproc(self, procname, args) 用来执行存储过程,接收的参数为存储过程名和参数列表,返回值为受影响的行数

execute(self, query, args) 执行单条sql语句,接收的参数为sql语句本身和使用的参数列表,返回值为受影响的行数

executemany(self, query, args) 执行单挑sql语句,但是重复执行参数列表里的参数,返回值为受影响的行数

nextset(self) 移动到下一个结果集

cursor用来接收返回值的方法:

fetchall(self) 接收全部的返回结果行.

fetchmany(self, size=None) 接收size条返回结果行.如果size的值大于返回的结果行的数量,则会返回cursor.arraysize条数据.

fetchone(self) 返回一条结果行.

rowcount 这是一个只读属性,并返回执行execute() 方法后影响的行数。

scroll(self, value, mode='relative') 移动指针到某一行; 如果mode='relative',则表示从当前所在行移动value条,如果 mode='absolute',则表示从结果集的第一行移动value条.

实例

#!/usr/bin/env python

# -*- coding: utf-8 -*-

import MySQLdb as mdb

# 连接数据库

conn = mdb.connect('localhost', 'root', 'root')

# 也可以使用关键字参数

conn = mdb.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='test', charset='utf8')

# 也可以使用字典进行连接参数的管理

config = {

'host': '127.0.0.1',

'port': 3306,

'user': 'root',

'passwd': 'root',

'db': 'test',

'charset': 'utf8'

}

conn = mdb.connect(**config)

# 如果使用事务引擎，可以设置自动提交事务，或者在每次操作完成后手动提交事务conn.commit()

conn.autocommit(1) # conn.autocommit(True)

# 使用cursor()方法获取操作游标

cursor = conn.cursor()

# 因该模块底层其实是调用CAPI的，所以，需要先得到当前指向数据库的指针。

try:

# 创建数据库

DB_NAME = 'test'

cursor.execute('DROP DATABASE IF EXISTS %s' %DB_NAME)

cursor.execute('CREATE DATABASE IF NOT EXISTS %s' %DB_NAME)

conn.select_db(DB_NAME)

#创建表

TABLE_NAME = 'user'

cursor.execute('CREATE TABLE %s(id int primary key,name varchar(30))' %TABLE_NAME)

# 插入单条数据

sql = 'INSERT INTO user values("%d","%s")' %(1,"jack")

# 不建议直接拼接sql，占位符方面可能会出问题，execute提供了直接传值

value = [2,'John']

cursor.execute('INSERT INTO test values(%s,%s)',value)

# 批量插入数据

values = []

for i in range(3, 20):

values.append((i,'kk'+str(i)))

cursor.executemany('INSERT INTO user values(%s,%s)',values)

# 查询数据条目

count = cursor.execute('SELECT * FROM %s' %TABLE_NAME)

print 'total records: %d' %count

print 'total records:', cursor.rowcount

# 获取表名信息

desc = cursor.description

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

# 查询一条记录

print 'fetch one record:'

result = cursor.fetchone()

print result

print 'id: %s,name: %s' %(result[0],result[1])

# 查询多条记录

print 'fetch five record:'

results = cursor.fetchmany(5)

for r in results:

print r

# 查询所有记录# 重置游标位置，偏移量:大于0向后移动;小于0向前移动，mode默认是relative# relative:表示从当前所在的行开始移动; absolute:表示从第一行开始移动

cursor.scroll(0,mode='absolute')

results = cursor.fetchall()

for r in results:

print r

cursor.scroll(-2)

results = cursor.fetchall()

for r in results:

print r

# 更新记录

cursor.execute('UPDATE %s SET name = "%s" WHERE id = %s' %(TABLE_NAME,'Jack',1))

# 删除记录

cursor.execute('DELETE FROM %s WHERE id = %s' %(TABLE_NAME,2))

# 如果没有设置自动提交事务，则这里需要手动提交一次

conn.commit()

except:

import traceback

traceback.print_exc()

# 发生错误时会滚

conn.rollback()

finally:

# 关闭游标连接

cursor.close()

# 关闭数据库连接

conn.close()

查询时返回字典结构

MySQLdb默认查询结果都是返回tuple，通过使用不同的游标可以改变输出格式，这里传递一个cursors.DictCursor参数。

import MySQLdb.cursors

conn = MySQLdb.connect(host='localhost', user='root', passwd='root', db='test', cursorclass=MySQLdb.cursors.DictCursor)

cursor = conn.cursor()

cursor.execute('select * from user')

r = cursor.fetchall()

print r

# 当使用位置参数或字典管理参数时，必须导入MySQLdb.cursors模块# 也可以用下面的写法

import MySQLdb as mdb

conn = mdb.connect('localhost', 'root', 'root', 'test')

cursor = conn.cursor(cursorclass=mdb.cursors.DictCursor)

cursor.execute('select * from user')

r = cursor.fetchall()

print r

MySQLdb取回大结果集的技巧

普通的操作无论是fetchall()还是fetchone()都是先将数据载入到本地再进行计算，大量的数据会导致内存资源消耗光。解决办法是使用SSCurosr光标来处理。

然而，在python3下，MySQLdb模块不再提供支持，此时可以使用另一个模块PyMySQL，它支持python2和python3。

2. PyMySQL

PyMySQL是一个纯Python写的MySQL客户端，它的目标是替代MySQLdb，可以在CPython、PyPy、IronPython和Jython环境下运行。PyMySQL在MIT许可下发布。

PyMySQL的性能和MySQLdb几乎相当，如果对性能要求

不是特别的强，使用PyMySQL将更加方便。

PyMySQL的使用方法和MySQLdb几乎一样。

安装

pip install pymysql

实例

#!/usr/bin/env python

# -*- coding: utf-8 -*-

import pymysql

config = {

'host': '127.0.0.1',

'port': 3306,

'user': 'root',

'passwd': 'root',

'charset':'utf8mb4',

'cursorclass':pymysql.cursors.DictCursor

}

conn = pymysql.connect(**config)

conn.autocommit(1)

cursor = conn.cursor()

try:

# 创建数据库

DB_NAME = 'test'

cursor.execute('DROP DATABASE IF EXISTS %s' %DB_NAME)

cursor.execute('CREATE DATABASE IF NOT EXISTS %s' %DB_NAME)

conn.select_db(DB_NAME)

#创建表

TABLE_NAME = 'user'

cursor.execute('CREATE TABLE %s(id int primary key,name varchar(30))' %TABLE_NAME)

# 批量插入纪录

values = []

for i in range(20):

values.append((i,'kk'+str(i)))

cursor.executemany('INSERT INTO user values(%s,%s)',values)

# 查询数据条目

count = cursor.execute('SELECT * FROM %s' %TABLE_NAME)

print 'total records:', cursor.rowcount

# 获取表名信息

desc = cursor.description

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

cursor.scroll(10,mode='absolute')

results = cursor.fetchall()

for result in results:

print result

except:

import traceback

traceback.print_exc()

# 发生错误时回滚

conn.rollback()

finally:

# 关闭游标连接

cursor.close()

# 关闭数据库连接

conn.close()

#coding = utf-8#3.6版本使用pymysql，2.7版本使用MySQLdb

import pymysql

#连接数据库使用关键字参数的方式

conn = pymysql.connect(host='localhost', port = 3306, user = 'root', passwd = 'password', db = 'py_test',)

#连接数据库，使用字典进行连接参数的管理

conn = pymysql.connect (

host = 'localhost',

port = 3306,

user = 'root',

passwd = 'password',

db = 'py_test',

)

#通过获取到的数据连接conn下的cursor()方法来创建游标

cur = conn.cursor()

#通过游标cur操作execute()方法写入纯sql语句。通过execute()方法中写入sql语句来对数据库进行操作。

#创建数据库

cur.execute("create database py_test")

#创建数据库表

cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))"

#添加数据

cur.execute("insert into student values('3','Xue','2 year 1 class','10')")

#删除数据

cur.execute("delete from student where age='9'")

#修改查询条件的数据

cur.execute("update student set class='3 year 1 class' where name = 'Amy'")

#查询表中有多少条数据

aa = cur.execute("select * from student")

print(aa)

#打印表中的数据

info = cur.fetchmany(aa)

for ii in info:

print(ii)

info = cur.fetchmany(cur.execute("select * from student"))

for ii in info:

print(ii)

#插入数据的优化方法

sqli="insert into student values(%s,%s,%s,%s)"

cur.execute(sqli,('3','Huhu','2 year 1 class','7'))

cur.execute(sqli,('2','Fufu','4 year 5 class','6'))

cur.executemany(sqli, [('3','Qiqi','2 year 1 class','7'),

('2','Bobo','4 year 5 class','6'),

('4','Anan','5 year 2 class','2'),

])

#异常

try:

cur.execute(sqli)

conn.commit()

except:

conn.rollback() #发生错误时回滚

#关闭游标

cur.close()

#conn.commit()提交数据库执行，再向数据库插入一条数据时必须要有这个方法，否则数据不会被真正的插入

conn.commit()

#关闭数据库连接

conn.close()