python2连接mysql_mysql-2：python操作mysql

1、python操作mysql

安装MySQL-Python的包：

pip install pymysql——import pymysql

# pip install MySQLClient——import MySQLdb

# 注意：MySQLdb和pymysql区别

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

MySQLdb只支持Python2.，还不支持3.可以用PyMySQL代替。安装方法：pip install PyMySQL

然后在需要的项目中，把 init.py中添加两行：

import pymysql

pymysql.install_as_MySQLdb()

就可以用 import MySQLdb了。

python数据库接口支持非常多的数据库,可以选择适合你项目的数据库:

MySQL,PostgreSQL,Microsoft SQL Server 2000,Oracle,Sybase

具体查看:https://wiki.python.org/moin/DatabaseInterfaces

python安装插件Database Navigator插件查看数据库，

windows版本，改时区，python连接成功：

show variables like '%time_zone%';

set global time_zone='+8:00';

MySQLdb._exceptions.IntegrityError: (1062, "Duplicate entry '1' for key 'PRIMARY'")

是因为对于键xxxxx已经存在一个值为“xx”了，所以当插入重复的值时就会提示该错误。一般是由于字段设置了unique导致的。我自己更改：在客户端右键-设计表，主键，再次执行代码后，刷新表就OK。

mysql中插入数据时Duplicate entry '' for key 'PRIMARY'的解决方案：https://blog.csdn.net/zhangyr_student/article/details/80119238

REPLACE INTO Table_name() VALUES(1,1),(2,2),(3,3)

2、方法说明

(1)Connection支持的方法：

方法名 说明

cursor() 创建并且返回游标

commit() 提交当前事物

rollback() 回滚当前事物

close() 关闭Connection

# 1、cursor用来执行命令的方法:

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

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

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

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

(2)获取Cursor游标对象，用于执行查询和获取结果，它支持的方法如下：

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

方法名 说明

execute() 用于执行一个数据库的查询命令, 将一个结果从数据库获取到客户端

fetch*()，移动rownumber，返回数据。

fetchall() 获取结果集中剩下的所有行

fetchone() 返回一条结果行

fetchmany(size)获取结果集中的下(size)行

rowcount 最近一次execute返回数据/影响的行数

close() 关闭游标

cursor.scroll(2,mode='relative')移动指针到某一行。

# 如果mode='relative',则表示从当前所在行移动value条,

# 如果 mode='absolute',则表示从结果集的第一行移动value条.

3、具体操作

说明：

创建Connection，创建了Python客户端与数据库之间的网络通路。类型string，db是数据库名，charset连接字符集

(0)格式

import pymysql

from pymysql.cursors import DictCursor

1、建立数据库连接对象 conn

conn = pymysql.connect(host='192.168.199.128',port=3306,user='root',passwd='123456',db='2019-1229',charset='utf8')

2、获取游标：通过coon.cursor() 创建操作sql的 游标对象

# 注：游标不设置参数，查询的结果就是“数据元组”，数据没有标识性；

# 设置pymysql.cursors.DictCursor，查询的结果是“字典”，key是表的字段

cursor=coon.cursor()

cursor=coon.cursor(DictCursor) # 建立游标,指定游标类型，返回字典

cursor=conn.cursor(pymysql.cursors.DictCursor)

3、编写sql交给 cursor 执行；——————更改3 该部分就可以

# 如果是查询，通过 cursor对象 获取结果

# sql = 'create table t1(id int, x int, y int)' # 使用sql语句创建表

sql='select * from ch0;'

# sql='select * from ch0 limit 2;' # 操作语句，只查询前两行

rows=cursor.execute(sql) # 返回结果是受影响的行数

res = cursor.fetchall() # 获取查询的所有结果

print(res) # 打印结果

4、

conn.commit() # 提交

cursor.close() # 关闭游标

conn.close() # 关闭连接

# 判断是否连接成功

if rows >= 0:

print('连接数据库成功')

else:

print('连接数据库失败')

(1)插入数据：单条，多条，大批量

1、增加数据：单条和多条

# 注意都可以定义成一个变量：cursor.execute(sql, value)

sql='insert into test(id,name) values(%s,%s)'

rows=cursor.execute(sql, ('4', 'qzcsbj4')) # 插入单条

rows=cursor.executemany(sql,[('5','qzcsbj5'),('6','qzcsbj6'),('7','qzcsbj7')]) # 插入多条

2、大批量新增，执行sql语句

#cursor=conn.cursor(pymysql.cursors.DictCursor)

values=[]

for i in range(100, 110):

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

sql='insert into ch0(id,name) values(%s,%s)'

rows=cursor.executemany(sql,values)

(2)修改数据，更新操作

# 删除上面大批量？？？？

delete from test where id>=100;

sql='update test set name = %s where id = %s'

# 单条

rows=cursor.execute(sql,('qzcsbj','7'))

# 多条

rows=cursor.executemany(sql,[('全栈测试笔记5','5'),('全栈测试笔记6','6')])

(3)删除数据????

sql='delete from test where id = %s'

# 单条

rows=cursor.execute(sql,('1',))

？？？对比测试，cursor.execute(sql,1)

# 多条

rows=cursor.executemany(sql,[('2'),('3')])

(4)查询数据？？？

fetchone，有点像从管道中取一个，如果再来一个fetchone，会又取下一个，如果取完了再取，就返回None。

每条记录为元组格式

fetchmany

一、查看t1表里的所有数据

sql = 'select * from t1'

row = cursor.execute(sql) # 返回值是受影响的行

print(row)

二、如果是查询，通过 cursor对象 获取结果

# 1.fetchone() 偏移一条取出

sql = 'select * from t1'

row = cursor.execute(sql)

r1 = cursor.fetchone()

print(r1)

1、fetchone

# 执行sql语句，每条记录为字典

rows=cursor.execute('select * from test;')

print(cursor.fetchone()) # 运行结果：(7, 'qzcsbj')

print(cursor.fetchone()) # 若是最后结束，None

# 每条记录为字典格式

cursor=conn.cursor(pymysql.cursors.DictCursor)

rows=cursor.execute('select * from test;')

print(cursor.fetchone()) # 运行结果：{'id': 7, 'name': 'qzcsbj'}

# 2.fetchmany(n) 偏移n条取出

2、fetchmany

print(cursor.fetchmany(2))

# 运行结果：[{'id': 4, 'name': 'qzcsbj4'}, {'id': 5, 'name': '全栈测试笔记5'}]

# 3.fetchall() 偏移剩余全部

3、fetchall

print(cursor.fetchall())

print(cursor.fetchall())

# 运行结果：[{'id': 4, 'name': 'qzcsbj4'}, {'id': 5, 'name': '全栈测试笔记5'}, {'id': 6, 'name': '全栈测试笔记6'}, {'id': 7, 'name': 'qzcsbj'}]

#[] ，因为后面没有记录了，所以取出来是空的列表

(5)游标操作

(1)相对绝对位置移动，从头开始跳过n个

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

print(cursor.fetchone())

# 运行结果：{'id': 7, 'name': 'qzcsbj'}

(2)相对当前位置移动，

rows=cursor.execute('select * from test;')

print(cursor.fetchone())

cursor.scroll(2,mode='relative')

print(cursor.fetchone())

# 运行结果：{'id': 4, 'name': 'qzcsbj4'}

# {'id': 7, 'name': 'qzcsbj'}

row = cursor.execute(sql)

if row:

r1 = cursor.fetchmany(2)

print(r1)

# 操作游标

# cursor.scroll(0, 'absolute') # absolute绝对偏移，游标重置，从头开始偏移

cursor.scroll(-2, 'relative') # relative相对偏移，游标在当前位置进行左右偏移

r2 = cursor.fetchone()

print(r2)

4、实践：建立数据库，进行一些简单操作

1、Select操作：简单的创建一个ch2表，并且插入一些数据，只有两个字段：userid和username。

2、数据的更新，即：insert、update、delete操作。

注意：是否数据发生异常，如果数据没有发生异常，我们便可以直接使用commit()进行提交(注：如没有使用commit，则数据库不会发生任何变化)。但是如果出现了异常，那么就需要使用rollback()进行回滚。

import pymysql

coon = pymysql.connect(host='192.168.199.128',port=3306,user='root',passwd='123456',db='2019-1229',charset='utf8')

cur = coon.cursor()

cur.execute("""

create table if not EXISTS ch2

(

userid int(11) PRIMARY KEY,

username VARCHAR(20)

)

""")

# 0、用Navicat打开数据库，查看一下结果，成功创建表，并且插入了十个数据。每次执行该句都会增10条记录

for i in range(1,10):

cur.execute("REPLACE into ch2(userid,username) values('%d','%s')" %(int(i),'name'+str(i)))

# 1、Select操作，操作一下Cursor里面的一些方法

sql='select * from ch2;'

cur.execute(sql)

print(cur.rowcount) # 或其他方法，cur.fetchone()，cur.fetchmany(3)，

rs=cur.fetchone()

print(rs)

res=cur.fetchall()

for row in res:

print('userid=%s,userna=%s' %row)

# 2、insert、update、delete：在写增删改查操作时，最好把操作放入一个try控制块中，来避免一些不必要的错误。

sql_insert = 'insert into ch3(userid,username) values(10,"name10")'

sql_update = 'update ch3 set username="name91" where userid=9'

sql_delete = 'delete from ch3 where userid=3'

try:

cur.execute(sql_insert)

print(cur.rowcount)

cur.execute(sql_update)

print(cur.rowcount)

cur.execute(sql_delete)

print(cur.rowcount)

except Exception as e:

print(e)

coon.rollback() # rollback()的作用数据没有任何改变。

coon.commit()

cur.close()

coon.close()

5、银行转账的实例

#-*-encoding:utf-8 -*-

coon = pymysql.connect(host='192.168.199.128',port=3306,user='root',passwd='123456',db='2019-1229',charset='utf8')

cur = coon.cursor()

# 创建数据表

cur.execute("""

create table if not EXISTS account(

accid int(10) PRIMARY KEY ,

money int(10)

)

""")

# 插入两行数据

cur.execute('insert into account(accid,money) VALUES (1,110)')

cur.execute('insert into account(accid,money) VALUES (2,10)')

coon.commit()

cur.close()

coon.close()

import sys

import MySQLdb

class TransferMoney(object):

def __init__(self,conn):

self.conn=conn

def check_acct_available(self,accid):

cursor=self.conn.cursor() # 游标，self

try:

sql='select * from account where accid=%s' %accid

cursor.execute(sql)

print('check_acct_available'+sql)

rs=cursor.fetchall()

if len(rs)!=1:

raise Exception('账号%s 不存在' %accid)

finally:

cursor.close()

def has_enough_money(self,accid,money):

cursor=self.conn.cursor()

try:

sql='select * from account where accid=%s and money>%s' %(accid,money)

cursor.execute(sql)

print('check_money_available'+sql)

rs=cursor.fetchall()

if len(rs)!=1:

raise Exception('账号%s 没有足够钱' %accid)

finally:

cursor.close()

def reduce_money(self,accid,money):

cursor=self.conn.cursor()

try:

sql='update account set money=money-%s where accid=%s' %(money,accid)

cursor.execute(sql)

print('reduce money'+sql)

rs=cursor.fetchall()

if cursor.rowcount!=1:

raise Exception('账号%s 减款失败' %accid)

finally:

cursor.close()

def add_money(self,accid,money):

cursor=self.conn.cursor()

try:

sql='update account set money=money+%s where accid=%s' %(money,accid)

cursor.execute(sql)

print('reduce money'+sql)

rs=cursor.fetchall()

if cursor.rowcount!=1:

raise Exception('账号%s 加款失败' %accid)

finally:

cursor.close()

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

if __name__=='__main__':

source_accid=sys.argv[1]

target_accid=sys.argv[2]

money=sys.argv[3]

conn = pymysql.connect(host='192.168.199.128',port=3306,user='root',passwd='123456',db='2019-1229',charset='utf8')

tr_money=TransferMoney(conn)

try:

tr_money.transfer(source_accid,target_accid,money)

except Exception as e:

print('出现问题'+str(e))

finally:

conn.close()

6、

try:

# cur.execute('create database if not exists python')

# conn.select_db('python')

sql = 'create table t2(id int, name char(4), money int)'

row = cursor.execute(sql)

print(row)

except:

print('表已创建')

pass

# 空表才插入

row = cursor.execute('select * from t2')

if not row:

sql = 'insert into t2 values(%s,%s,%s)'

row = cursor.executemany(sql, [(1, 'tom', 10), (2, 'Bob', 10)])

conn.commit()

1、pymysql事务

# 可能会出现异常的sql

try:

sql1 = 'update t2 set money=money-1 where name="tom"'

r1 = cursor.execute(sql1)

sql2 = 'update t2 set money=money+1 where name="ruakei"' # 转入的人不存在

r2 = cursor.execute(sql2)

except:

print('转账执行异常')

conn.rollback()

else:

print('转账没有异常')

if r1 == 1 and r2 == 1:

print('转账成功')

conn.commit()

else:

conn.rollback()

2、SQL注入问题

# 用户登录

usr = input('usr: ')

pwd = input('pwd: ')

# 自己拼接参数一定有sql注入，将数据的占位填充交给pymysql

sql = 'select * from user where name=%s and password=%s'

row = cursor.execute(sql, (usr, pwd))

if row:

print('登录成功')

else:

print('登录失败')

# 知道用户名时

# 输入用户时：

# tom => select * from user where name="tom" and password="%s"

# tom" # => select * from user where name="tom" #" and password="%s"

# 不自定义用户名时

# " or 1=1 # => select * from user where name="" or 1=1 #" and password="%s"