Python操作MySQL
创建一个user表
CREATE TABLE user
(id
int(11) DEFAULT NULL,name
varchar(255) DEFAULT NULL,password
varchar(255) DEFAULT NULL,birthday
date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Python DB-API
Python标准数据接口为Python DB-API,其提供了数据库应用编程接口.
Python DB-API使用流程:
引用API模块
获取与数据库的连接
执行sql语句与存储过程
关闭数据库连接
安装MySQLdb
MySQLdb是用于Python链接mysql数据库的接口,它实现了Python数据库API规范V2.0,基于 MySQL C API 上建立的。
连接数据库
连接数据库前,必须确保以下事项:
- 已经安装了mysql服务端;
- 已经创建了数据库、数据表、表中添加了数据;
- 连接数据库使用的用户名、密码,以及数据库服务所在机器的ip,端口号、
用户拥有访问数据的权限; - 你的机器上已经安装了Python MySQLdb 模块
MySQLdb.Connect() 连接数据库函数
conn = MySQLdb.connect(
host = ip,
user = username,
passwd = password,
db = database
charset=encoding
)
host:mysql服务所在机器的ip;
port:表示连接所使用的端口号;
user:连接数据库时所用的用户名;
passwd:连接数据库时所使用的用户的密码;
db:要连接数据库的库名;
charset:连接数据时使用的编码;
示例:
#coding=utf-8
import MySQLdb
conn = MySQLdb.connect(
host = "127.0.0.1",
user = "root",
passwd = "123456",
db = "python",
charset="utf8"
)
print conn
print type(conn)
conn.cusor()获取游标
要想操作数据库,光连接数据库是不够,必须拿到操作数据库的游标,才能进行后续的操作,比如读取数据,添加数据等。通过获取到的数据库连接实例conn下的cursor()方法来创建游标。游标用来接收返回结果
#coding=utf-8
import MySQLdb
打开数据库连接
conn = MySQLdb.connect(
host = "localhost",
port = 3306,
user = "root",
passwd = "123456",
db = "python",
charset = "utf8")
使用cursor()方法获取数据库的操作游标
cursor = conn.cursor()
print cursor
print type(cursor)
注意:
Cursor返回一个游标实例对象,其中内置了很多操作数据的方法,比如执行sql语句;
执行sql语句方法execute和executemany。
execute(query, args=None)
execute执行单条sql语句,成功后返回受影响的行数,长整型;
参数说明:
• query:要执行的sql语句,字符串类型。
• args:可选的序列或映射,用于query的参数值。如果args为序列,query中必须使用%s做占位符;如果args为映射,query中必须使用%(key)s做占位符
executemany (query, args)
该方法用于批量执行sql语句,比如批量插入数据,返回受影响的行数,长整型。
参数说明:
• query:要执行的sql语句,字符串类型。
• args:嵌套的序列或映射,用于query的参数值。
数据库性能瓶颈很大一部份就在于网络IO和磁盘IO,将多个sql语句放在一起,只执行一次IO,可以有效的提升数据库性能。推荐此方法
注意:
用executemany()方法一次性批量执行sql语句,固然很好,但是当数据一次传入过多到
server端,可能造成server端的buffer溢出,也可能产生一些意想不到的麻烦。所以,合理、分批次使用executemany是个合理的办法
建库:
#encoding=utf-8
import MySQLdb
try:
conn = MySQLdb.connect(#此处需要创建数据库,此函数不需要指定数据库
host='127.0.0.1',
user='root',
passwd="123456",
port=3306)
cur = conn.cursor()
cur.execute('CREATE DATABASE IF NOT EXISTS pythonDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')
cur.close()
conn.close()
print u"创建数据库pythonDB成功! "
except MySQLdb.Error, e:
print "Mysql Error %d: %s" % (e.args[0], e.args[1])
建表
import MySQLdb
try:
conn = MySQLdb.connect(
host = '127.0.0.1',
user = 'root',
passwd = "123456",
port = 3306)
conn.select_db('python')
此处选择数据库python,connect函数就不需要指定db参数
cur = conn.cursor()# 获取游标
如果所建表已存在,删除重建
cur.execute("drop table if exists User;")
#执行建表sql语句
cur.execute('''CREATE TABLE User
(id
int(11) DEFAULT NULL,name
varchar(255) DEFAULT NULL,password
varchar(255) DEFAULT NULL,birthday
date DEFAULT NULL
)ENGINE=innodb DEFAULT CHARSET=utf8;''')
cur.close()#先关闭游标
conn.close()#再关闭数据库连接
print u"创建数据表成功"
except MySQLdb.Error, e:
print "Mysql Error %d: %s" %(e.args[0], e.args[1])
插入单条数据:
import MySQLdb
try:
conn = MySQLdb.connect(
host = '127.0.0.1',
user = 'root',
passwd = "123456",
port = 3306)
conn.select_db('python')#
cur = conn.cursor()# 获取游标
insert = cur.execute("insert into user values(1,'Tom','123','1990-01-01')")
print u"添加语句受影响的行数:", insert
#另一种插入数据方法,通过格式字符串传入值
sql = "insert into user values(%s, %s, %s, %s)"
cur.execute(sql, (3,'lucy','efg','1993-02-01'))#这里需要传入参数组成的元组
cur.close()
conn.commit()
conn.close()
print u"sql执行成功"
except Exception, e:
print e
批量插入数据
import MySQLdb
try:
conn = MySQLdb.connect(
host = '127.0.0.1',
user = 'root',
passwd = "123456",
port = 3306)
conn.select_db('python')
cur = conn.cursor()# 获取游标
sql = "insert into user values(%s, %s, %s, %s)"
#executemany的参数可以传入列表,返回受影响的行数
insert = cur.executemany(sql, [
(5,'tom','tom','1989-03-17'), #元组
(6,'amy','test','1898-12-01'),
(7,'lily','linux','1994-06-23')])
print u"批量插入返回受影响的行数:",insert
cur.close()
conn.commit()
conn.close()
print u"sql执行成功"
except Exception, e:
print e
批量执行sql语句使用的是executemany(sql, args)函数,返回受影响的行数。Args参数是一个包含多个元组的列表,每个元组对应mysql中的一条数据。注意,这里的%s不需要加引号,否则插入数据的数据会类型错误
小练习:
插入100条数据到数据库
方式1:
import MySQLdb
import string
import random
try:
conn = MySQLdb.connect(
host="127.0.0.1",
user="root",
passwd="123456",
port=3306
)
conn.select_db("pythonTest")
cursor = conn.cursor()
sql = "insert into user values(%s,%s,%s,%s)"
words=list(string.letters)
for i in xrange(100):
random.shuffle(words)#打乱列表
cursor.execute(sql,(100+i,"".join(words[0:5])+str(i),"".join(words[5:10])+str(i),str(random.randint(1900,2018))+ "-" +str(random.randint(1,12)) + "-" + str(random.randint(1,28))))
cursor.close()
conn.commit()
conn.close()
except MySQLdb.Error,e:
print e
方式2:
import MySQLdb
try:
conn = MySQLdb.connect(
host = '127.0.0.1',
user = 'root',
passwd = "123456",
port = 3306)
conn.select_db('python')#
cur = conn.cursor()# 获取游标
sql = "insert into user values(%s, %s, %s, %s)"
insert = cur.executemany(sql,
[(i,'tom'+str(i),'tom'+str(i),'1989-03-17') for i in xrange(100)])
print u"批量插入返回受影响的行数:",insert
cur.close()
conn.commit()
conn.close()
except Exception, e:
print e
转载于:https://blog.51cto.com/13496943/2147517