python 游标对象方法 pymysql 游标

1、Python连接数据库

要想操作数据库，仅连接数据是不够的，必须拿到操作数据库的游标才能进行后续操作，比如读取数据、添加数据。通过获取到的数据库连接示例conn下的cursor()方法来创建游标。游标用来返回结果。

import pymysql
conn = pymysql.connect('locahost',user='root',passwd='123456',db='testdb',charset='utf-8')
cusor = conn.cursor

备注：cursor返回一个游标实例对象，其中包含很多操作数据的方法，比如执行SQL语句。

执行SQL语句execute和executemany：

execute(query,args=None)：执行单条SQL语句，执行成功后返回受影响的行数。query为要执行的sql语句，且为字符串类型。args为可选的序列或映射，用于query的参数值，如果args为序列，query中必须使用%s做占位符；如果args为映射，query中必须使用%(key)s做占位符。

 

executemany(query,args=None)：批量执行SQL语句，比如批量插入数据，执行成功后返回受影响的行数，query为要执行的sql语句，且为字符串类型。args为嵌套的序列或映射，用于query的参数值。

 

示例：

创建数据库：

import pymysql
#打开数据库连接，不需要指定数据库，因为需要创建数据库
conn = pymysql.connect('locahost',user='root',passwd='123456',charset='utf-8')
#获取游标
cusor = conn.cursor
#创建数据库
cursor.execute('CREATE DATABASE IF NOT EXISTS pythonDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')
#先关闭游标
cursor.close()
#再关闭数据库
con.close()
print('数据库创建成功')

备注：创建后可使用show databases查询确认

创建表：

import pymysql
#打开数据库连接，指定数据库
conn = pymysql.connect('locahost',user='root',passwd='123456',db='testdb',charset='utf-8')
#获取游标
cusor = conn.cursor
#创建user表
cursor.execute('drop table if exists user')
sql="""CREATE TABLE IF NOT EXISTS `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NOT NULL,
      `age` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""
cursor.execute(sql)
#先关闭游标
cursor.close()
#再关闭数据库
con.close()
print('user表创建成功')

插入单条数据：

import pymysql
#打开数据库连接，不指定数据库
conn = pymysql.connect('locahost',user='root',passwd='123456',charset='utf-8')
#选择需要连接的数据库
conn.select_db('testdb')    #获取游标
cusor = conn.cursor
insert = cur.execute("insert into user values(1,'tom',18)")
print("添加语句受影响的行数：",insert)

#第二种插入数据的方式，通过字符串传入值
sql = "insert into user values(%s,%s,%s)"
cur.execute(sql,(3,'kongsh',20))
#先关闭游标
cursor.close()
#再关闭数据库
con.close()
print('SQL执行成功')

批量插入多条数据：

import pymysql
#打开数据库连接，不指定数据库
conn = pymysql.connect('locahost',user='root',passwd='123456',charset='utf-8')
#选择需要连接的数据库
conn.select_db('testdb')   
cusor = conn.cursor      #获取游标
sql = "insert into user values(%s,%s,%s)"
cur.executemany(sql,[(4,'wen',20),(5,'tom',10),(6,'test',30)])
#先关闭游标
cursor.close()
#再关闭数据库
con.close()
print('SQL执行成功')

备注：批量插入多条SQL语句采用的是executemany(sql,args)函数，返回受影响的行数。
args参数是一个包含多个元组的列表，每个元组对应一条mysql中的一条数据。这里的%s不需要加引号，否则插入数据的数据类型错误

查询数据：

使用execute()函数得到的只是受影响的行数，并不能真正拿到查询的内容。cursor对象还提供了3中提取数据的方法：fetchone、fetchmany、fetchall。每个方法都会导致游标移动。

cursor.fetchone()：获取游标所在处的一行数据，返回元组，没有返回None；

cursor.fetchmany(size)：接受size行返回结果行。如果size大于返回的结果行的数量，则会返回cursor.aruuaysize条数据。

cursor.fetchall()：接收全部的返回结果行。

示例：

fetchone：

import pymysql
#打开数据库连接，不指定数据库
conn = pymysql.connect('locahost',user='root',passwd='123456',charset='utf-8')
#选择需要连接的数据库
conn.select_db('testdb')   
cusor = conn.cursor      #获取游标
cur.execute("select * from user")
while 1:
    res = cur.fetchone()
    if res is None:
        break
    print(res)
#先关闭游标
cursor.close()
#再关闭数据库
con.close()
print('SQL执行成功')

#输出结果：
(1,'tom',18)
(3,'kongsh',20)
(4,'wen',20)
(5,'tom',10)
(6,'test',30)

备注：fetchone()函数必须跟execute()函数结合使用，并且在execute()函数之后使用。

fetchmany：

import pymysql
#打开数据库连接，不指定数据库
conn = pymysql.connect('locahost',user='root',passwd='123456',charset='utf-8')
#选择需要连接的数据库
conn.select_db('testdb')   
cusor = conn.cursor      #获取游标
cur.execute("select * from user")
resTuple = cur.fetchmany(3)
for res in resTuple:
    print(res)
#先关闭游标
cursor.close()
#再关闭数据库
con.close()
print('SQL执行成功')

#输出结果：
(1,'tom',18)
(3,'kongsh',20)
(4,'wen',20)

备注：从exceute()函数结果中获取游标所在处的size条数据，并以元组的形式返回，
元组的每一个元素都也是一个由一行数据组成的元组，如果size大于有效的结果行数，
将会返回cursor.arraysize条数据，但如果游标所在处没有数据，将返回空元组。
查询几条数据，游标将会向下移动几个位置。fetmany()函数必须跟exceute()函数结合使用，
并且在exceute()函数之后使用

fetchall：

import pymysql
#打开数据库连接，不指定数据库
conn = pymysql.connect('locahost',user='root',passwd='123456',charset='utf-8')
#选择需要连接的数据库
conn.select_db('testdb')   
cusor = conn.cursor      #获取游标
cur.execute("select * from user")
resTuple = cur.fetchall()
print("共%d条数据"%len(resTuple))
#先关闭游标
cursor.close()
#再关闭数据库
con.close()
print('SQL执行成功')

更新数据：

更新单条数据：

import pymysql
#打开数据库连接，不指定数据库
conn = pymysql.connect('locahost',user='root',passwd='123456',charset='utf-8')
#选择需要连接的数据库
conn.select_db('testdb')   
cusor = conn.cursor      #获取游标
#更新一条数据
update = cur.execute("update user set age=100 where name='kongsh'")
print ('修改后受影响的行数为：',update)
#查询一条数据
cur.execute('select * from user where name="kongsh";')
print(cur.fetchone())
#先关闭游标
cursor.close()
#再关闭数据库
con.close()
print('SQL执行成功')

更新多条数据：

import pymysql

conn = pymysql.connect("localhost",'root','123456',charset='utf-8')
conn.select_db("testdb")
cursor = conn.cursor()
#更新前查询所有数据
cur.execute("select * from user where name in ('kongsh','wen');")
for res in cur.fetchall():
    print(res)

#更新两条数据
sql = update user set age=%s where name=%s
update = cur.executemany(sql,[(15,'kongsh'),(18,'wen')])
cur.execute()
#更新2条数据后查询所有数据
cur.execute("select * from user where name in ('kongsh','wen');")
print('更新后的数据为：')
for res in cur.fetchall():
      print (res)


cur.close()
conn.commit()
conn.close()
print('sql执行成功')

删除数据：

删除单条数据：

import pymysql

conn = pymysql.connect("localhost",'root','123456',charset='utf-8')
conn.select_db("testdb")
cursor = conn.cursor()
#更新前查询所有数据
cur.execute("select * from user;")
print("删除前的数据为：")
for res in cur.fetchall():
    print(res)

#删除一条数据
sql = 'delete from user where id=1'
cur.execute()
#查询删除后的所有数据
cur.execute("select * from user")
print('更新后的数据为：')
for res in cur.fetchall():
      print (res)


cur.close()
conn.commit()
conn.close()
print('sql执行成功')

 

删除多条数据：

import pymysql

conn = pymysql.connect("localhost",'root','123456',charset='utf-8')
conn.select_db("testdb")
cursor = conn.cursor()
#更新前查询所有数据
cur.execute("select * from user;")
print("删除前的数据为：")
for res in cur.fetchall():
    print(res)

#删除两条数据
sql = 'delete from user where id=%s'
cur.executemany(sql,[(3),(4)])
#查询删除后的所有数据
cur.execute("select * from user")
print('更新后的数据为：')
for res in cur.fetchall():
      print (res)


cur.close()
conn.commit()
conn.close()
print('sql执行成功')

事务回滚：

import pymysql
#打开数据库连接
conn=pymysql.connect('localhost','root','123456')
conn.select_db('pythondb')
#获取游标
cur=conn.cursor()

#修改前查询所有数据
cur.execute("select * from user;")
print('修改前的数据为：')
for res in cur.fetchall():
      print (res)

print ('*'*40)      
#更新表中第1条数据
cur.execute("update user set name='xiaoxiaoxiaoxiaoren' where id=5")

#修改后查询所有数据
cur.execute("select * from user;")
print('修改后的数据为：')
for res in cur.fetchall():
      print (res)
print ('*'*40)
#回滚事务
conn.rollback()
cur.execute("select * from user;")
print('回滚事务后的数据为：')
for res in cur.fetchall():
      print (res)

cur.close()
conn.commit()
conn.close()
print('sql执行成功')

插入100条数据到数据库：

'''插入100条数据到数据库(每次插入一条)'''
import pymysql
import string,random
#打开数据库连接
conn=pymysql.connect('localhost','root','123456')
conn.select_db('testdb')
#获取游标
cur=conn.cursor()

#创建user表
cur.execute('drop table if exists user')
sql="""CREATE TABLE IF NOT EXISTS `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NOT NULL,
      `age` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""

cur.execute(sql)

#修改前查询所有数据
cur.execute("select * from user;")
print('修改前的数据为：')
for res in cur.fetchall():
      print (res)

print ('*'*40)      
#循环插入数据
words=list(string.ascii_letters)
sql="insert into user values(%s,%s,%s)"
for i in range(100):
      random.shuffle(words)#打乱顺序
      cur.execute(sql,(i+1,"".join(words[:5]),random.randint(0,80)))

#插入100条后查询所有数据
cur.execute("select * from user;")
print('修改后的数据为：')
for res in cur.fetchall():
      print (res)
print ('*'*40)

cur.close()
conn.commit()
conn.close()
print('sql执行成功')

一次插入多条：

'''插入100条数据到数据库(一次插入多条)'''
import pymysql
import string,random
#打开数据库连接
conn=pymysql.connect('localhost','root','123456')
conn.select_db('testdb')
#获取游标
cur=conn.cursor()

#创建user表
cur.execute('drop table if exists user')
sql="""CREATE TABLE IF NOT EXISTS `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NOT NULL,
      `age` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""

cur.execute(sql)

#修改前查询所有数据
cur.execute("select * from user;")
print('修改前的数据为：')
for res in cur.fetchall():
      print (res)

print ('*'*40)      
#循环插入数据
words=list(string.ascii_letters)
sql="insert into user values(%s,%s,%s)"
random.shuffle(words)#打乱顺序
cur.executemany(sql,[(i+1,"".join(words[:5]),random.randint(0,80)) for i in range(100) ])

#插入100条后查询所有数据
cur.execute("select * from user;")
print('修改后的数据为：')
for res in cur.fetchall():
      print (res)
print ('*'*40)

cur.close()
conn.commit()
conn.close()
print('sql执行成功')

 

Python调用mysql存储过程：

# encoding: utf-8

# sql导出
# mysqldump -u root -p db_name > test_db.sql

import MySQLdb


class MySqlOp(object):
    def __init__(self, login_msg):
        self.host = login_msg[0]
        self.port = login_msg[1]
        self.db_name = login_msg[2]
        self.user = login_msg[3]
        self.passwd = login_msg[4]
        self.charset = 'utf8'

    # 更新
    def operate(self, sql):
        db = MySQLdb.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db_name, charset=self.charset)
        cur = db.cursor()
        try:
            # 执行sql语句
            cur.execute(sql)
            cur.close()
            # 提交到数据库执行
            db.commit()
        except Exception as e:
            print(e)
            cur.close()
            # Rollback in case there is any error
            db.rollback()
        # 关闭数据库连接
        db.close()

    # 插入
    def select(self, sql):
        db = MySQLdb.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db_name, charset=self.charset)
        cur = db.cursor()
        results = None
        try:
            # 执行sql语句
            cur.execute(sql)
            # 获取所有记录列表
            results = cur.fetchall()
            # print(results)
        except Exception as e:
            print(e)
            # 关闭数据库连接
        db.close()
        return results

    # 调用存储过程
    def callproc(self, sql, args=[]):
        db = MySQLdb.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db_name, charset=self.charset)
        cur = db.cursor()
        results = None
        try:
            # 执行sql语句
            cur.callproc(sql, args)
            # 获取所有记录列表
            results = cur.fetchall()
            cur.close()
            # 提交到数据库执行
            db.commit()
            # print(results)
        except Exception as e:
            print(e)
            # 关闭数据库连接
            cur.close()
            # Rollback in case there is any error
            db.rollback()
        db.close()
        return results

    @classmethod
    def int_c(cls, int_):
        '''
        整数处理
        '''
        return int_ if int_ else 'null'

    @classmethod
    def str_c(cls, str_):
        '''
        字符串处理
        '''
        return "'{}'".format(str_) if str_ else 'null'

 

 调用：

if __name__ == '__main__':
    # 连接信息 [ip， 端口， 数据库名， 用户名， 密码]
    login_msg = ['192.168.0.1', 3306, 'student_db', 'paisen', 'abcd1234']
    msq = MySqlOp(login_msg)

    # 查询
    book = msq.select('''show full columns from data;''')
    print(book)

    # 更新
    msq.operate('''insert into student (no, name) values (1, "刘华强");''')

    # 调用存储过程
    cover = msq.callproc('getDataCoverPath', [100,])
    print(cover)