Python与MySQL数据库进行连接,并对数据库进行操作
本文中涉及到的开发环境:Ubuntu16.04 + MySQL5.7.21 and Windows下使用Navicat对Ubuntu下的MySQL进行可视化操作,MySQL的相关安装步骤见以前发表的文章,Python与数据库MySQL进行连接需要使用到MySQLdb下载地址click me,MySQLdb先支持Python2.,暂不支持Python3.。
基本开发环境的搭建以及软件的安装
MySQLdb的安装
MySQLdb的安装可以使用pip
进行安装:
pip install MySQL-python==1.2.5
Windows下Navicat与MySQL(Ubuntu)的连接
在进行连接前需要对MySQL进行相关的设置,因为MySQL默认是与本机127.0.0.1
进行连接绑定,其他的主机是没有办法与数据库进行数据的访问和修改的,相关的设置如下:
在目录/etc/mysql/mysql.conf.d
下的mysqld.cnf
进行修改。
lee@lee-PC:/etc/mysql/mysql.conf.d$ tree
.
├── mysqld.cnf
└── mysqld_safe_syslog.cnf
mysqld.cnf
的部分代码如下,将 bind-address = 127.0.0.1
注释掉,就可以通过其他的电脑对MySQL进行访问了。
...
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 127.0.0.1
#
...
设置数据数据库的访问的权限,进入数据库,通过下面的命令进行设置,username
可以在任何的IP上对数据库进行访问和操作。
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
直接通过Navicat进行连接即可。
Python对MySQL进行操作
Python与MySQL进行连接-connection
- 创建连接的对象,建立Python客户端与数据库网络连接
- 创建的基本方法MySQLdb.connect(相关的参数),其中的主要参数有(host, user, passwd, port, db, charset),在这些参数中,只有port是数字类型,其他的参数都是字符串类型。
对数据库进行执行,获取执行的结构-cursor
- 游标对象:用于执行查询和获取结果
- cursor对象包含的方法如下:
参数名 | 说明 |
---|---|
execute(command) | 执行一个数据库查询和命令 |
fetchone() | 获取结果集中的下一行 |
fetchmany() | 获取结果集中的下几行 |
fetchall() | 获取结果集中的全部信息 |
rowcount | 最近的一次execute返回数据或影响的行数 |
close() | 关闭游标对象 |
举一个例子
import MySQLdb
conn = MySQLdb.Connect(
host = "127.0.0.1",
port = 3306,
user = "lee",
passwd = "123",
db = "lee",
charset = "utf8",
)
cursor = conn.cursor()
sql_command = 'select * from user'
cursor.execute(sql_command)
rs = cursor.fetchall()
for ele in rs:
print 'userid : %d , username : %s' %ele
cursor.close()
conn.close()
commit()和rollback()操作
当我们使用cursor.execute(SQL)执行一条Insert/Update/Delete语句后,我们需要使用conn.commit()提交事务,否则,语句不会生效。
其实我们常用的commit(),就是包含了一种事务处理的概念,如果你在commit()之前,执行了多条语句,只有当commit()之后,才会全部生效。
那么如果像文章开始提到的那样,执行两条插入语句,我们可以把conn.commit()语句放在两条插入语句执行之后,并放在try语句块内,如果差生异常,我们可以使用:
conn.rollback()
这个语句可以使事务回滚,直到上一条conn.commit()执行之后的位置。
下面是一个简单的例子,对commit()和rollback()做一个大致应用。
import MySQLdb
print MySQLdb
conn = MySQLdb.Connect(
host = "127.0.0.1",
port = 3306,
user = "lee",
passwd = "123",
db = "lee",
charset = "utf8"
)
cursor = conn.cursor()
sql_commond = 'select * from user '
cursor.execute(sql_commond)
for ele in cursor.fetchall():
print "userid : %d, username : %s" %ele
print "*" * 8
# exe insert update delete
sql_insert = "insert into user(username) values('Lily')"
sql_update = "update user set username='Sweet' where userid=1"
sql_delete = "delete from user where userid=3"
# sql_delete = "delete from user where username='Lily'"
sql_insert = "insert into user(userame) values('Lily')"
try:
cursor.execute(sql_insert)
print cursor.rowcount
cursor.execute(sql_update)
print cursor.rowcount
cursor.execute(sql_delete)
print cursor.rowcount
conn.commit()
except Exception as e:
print e
conn.rollback()
cursor.close()
conn.close()
下面是一个实力,对上述的方法进行一个综合的应用-银行取钱。
# coding:utf8
import sys
import MySQLdb
class TransferMoney(object):
def __init__(self, conn):
self.conn = conn
def transfer(self, source_accid, target_accid, money):
try:
self.check_acct_available(source_accid)
self.check_acct_available(target_accid)
self.has_enough_money(source_accid, money)
self.reduce_money(source_accid, money)
self.add_money(target_accid, money)
self.conn.commit()
except Exception as e:
self.conn.rollback()
raise e
def check_acct_available(self, accid):
try:
cursor = self.conn.cursor()
sql_command = 'select * from bankaccount where accid=%s' %accid
print "check_acct_available" + sql_command
cursor.execute(sql_command)
rs = cursor.fetchall()
if len(rs) != 1:
raise Exception("账号%s 不错在" %accid)
finally:
cursor.close()
def has_enough_money(self, accid, money):
try:
cursor = self.conn.cursor()
sql_command = 'select * from bankaccount where accid=%s and money > %s' %(accid, money)
print "has_enough_money" + sql_command
cursor.execute(sql_command)
rs = cursor.fetchall()
if len(rs) != 1:
raise Exception("账号%s 的金额不足" %accid)
finally:
cursor.close()
def reduce_money(self, accid, money):
try:
cursor = self.conn.cursor()
sql_command = 'update bankaccount set money=money-%s where accid=%s' %(money, accid)
print "reduce_money" + sql_command
cursor.execute(sql_command)
rs = cursor.fetchall()
if cursor.rowcount != 1:
raise Exception("账号%s 扣款失败" %accid)
finally:
cursor.close()
def add_money(self, accid, money):
try:
cursor = self.conn.cursor()
sql_command = 'update bankaccount set money=money+%s where accid=%s' %(money, accid)
print "add_money" + sql_command
cursor.execute(sql_command)
rs = cursor.fetchall()
if cursor.rowcount != 1:
raise Exception("账号%s 入款失败" %accid)
finally:
cursor.close()
if __name__ == "__main__":
source_accid = sys.argv[1]
target_accid = sys.argv[2]
money = sys.argv[3]
conn = MySQLdb.connect(
host = '127.0.0.1',
user = 'lee',
passwd = '123',
port = 3306,
db = 'lee'
)
tr_money = TransferMoney(conn)
try:
tr_money.transfer(source_accid, target_accid, money)
except Exception as e:
print "出现问题 :" + str(e)
finally:
conn.close()
在编写上面程序中遇到的问题,以及解决的方法。
提示:
`TypeError:%d format:a number is required, not str.
解决的方法,传入sql的变量的格式进行修改就可以了,sql中并不需要对相应的变量写作%d
,只用写%s
即可。
以上。