安装
pip3 install pymysql |
常用函数:
execute() 执行语句并返回收影响的行数
import pymysql
# 创建连接
conn = pymysql.connect(
host=
'127.0.0.1',
port=
3306,
user=
'root',
passwd=
'123456',
db=
'test',
charset=
"utf8" )
# 创建游标
cursor = conn.cursor()
# 执行SQL,并返回收影响行数
effect_row = cursor.execute(
"insert into t2 VALUES(1,'ray')")
# 执行SQL,并返回受影响行数
# effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
# 执行SQL,并返回受影响行数
# effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
# 提交,不然无法保存新建或者修改的数据
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
|
import pymysql
# 创建连接
conn = pymysql.connect(
host=
'127.0.0.1',
port=
3306,
user=
'root',
passwd=
'123456',
db=
'test',
charset=
"utf8" )
# 创建游标
cursor = conn.cursor()
# 执行SQL,并返回收影响行数
truple_str=
'ray'
effect_row = cursor.execute(
"insert into t2 VALUES(3,%s)",truple_str)
print(effect_row)
truple_str=
'suen'
effect_row = cursor.execute(
"insert into t2 VALUES(4,%s)",truple_str)
print(effect_row)
# 提交,不然无法保存新建或者修改的数据
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
|
import pymysql
# 创建连接
conn = pymysql.connect(
host=
'127.0.0.1',
port=
3306,
user=
'root',
passwd=
'123456',
db=
'test',
charset=
"utf8" )
# 创建游标
cursor = conn.cursor()
# 执行SQL,并返回收影响行数
truple_str=(
'lalala',
1)
cursor.execute(
'update t2 set t_name=%s where t_id=%s',truple_str)
# 提交,不然无法保存新建或者修改的数据
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
|
import pymysql
# 创建连接
conn = pymysql.connect(
host=
'127.0.0.1',
port=
3306,
user=
'root',
passwd=
'123456',
db=
'test',
charset=
"utf8" )
# 创建游标
cursor = conn.cursor()
# 执行SQL,并返回收影响行数
# truple_str=('lalala',1)
tid=
1
cursor.execute(
'delete from t2 where t_id=%s',tid)
# 提交,不然无法保存新建或者修改的数据
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
|
executemany()执行条语句,以元组的形式传入
import pymysql
# 创建连接
conn = pymysql.connect(
host=
'127.0.0.1',
port=
3306,
user=
'root',
passwd=
'123456',
db=
'test',
charset=
"utf8" )
# 创建游标
cursor = conn.cursor()
# 执行SQL,并返回收影响行数
truple_str=[
(
5,
'aaa'),
(
6,
'bbb'),
(
7,
'ccc'),
(
8,
'ddd')
]
cursor.executemany(
"insert into t2 VALUES(%s,%s)",truple_str)
# 提交,不然无法保存新建或者修改的数据
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
|
fecheone()
fechemany()
fecheall()
注:在fetch数据时按照顺序进行,可以使用cursor.scroll(num,mode)来移动游标位置,如:
- cursor.scroll(1,mode='relative') # 相对当前位置移动,正数为向下移动,负数为向上移动
- cursor.scroll(2,mode='absolute') # 相对绝对位置移动
import
pymysql
# 创建连接
conn = pymysql.connect(
host
=
'127.0.0.1'
,
port
=
3306
,
user
=
'root'
,
passwd
=
'123456'
,
db
=
'test'
,
charset
=
"utf8"
)
# 创建游标
cursor = conn.cursor()
# 执行SQL,并返回收影响行数
# truple_str=('lalala',1)
cursor.execute(
'select
*
from t2'
)
#fech 是去内存中获取
res = cursor.fetchone()
print
(res)
res = cursor.fetchmany(
7
)
#指定获取的条目数
print
(res)
res = cursor.fetchall()
print
(res)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
|
import
pymysql
# 创建连接
conn = pymysql.connect(
host
=
'127.0.0.1'
,
port
=
3306
,
user
=
'root'
,
passwd
=
'123456'
,
db
=
'test'
,
charset
=
"utf8"
)
# 创建游标
cursor = conn.cursor()
# 执行SQL,并返回收影响行数
# truple_str=('lalala',1)
cursor.execute(
'select
*
from t2'
)
#fech 是去内存中获取
res = cursor.fetchone()
print
(res)
res = cursor.fetchone()
print
(res)
cursor.scroll(
0
,
mode
=
'absolute'
)
res = cursor.fetchall()
print
(res)
cursor.scroll(-
1
,
mode
=
'relative'
)
res = cursor.fetchall()
print
(res)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
|
import
pymysql
# 创建连接
conn = pymysql.connect(
host
=
'127.0.0.1'
,
port
=
3306
,
user
=
'root'
,
passwd
=
'123456'
,
db
=
'test'
,
charset
=
"utf8"
)
# 创建游标
cursor = conn.cursor()
# 执行SQL,并返回收影响行数
# truple_str=('lalala',1)
cursor.execute(
'select
*
from t2'
)
#fech 是去内存中获取
while
1
:
res = cursor.fetchone()
if
res ==
None
:
break
print
(res)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
|
cursor修改,改变获取后的结果为字典集合
import pymysql
# 创建连接
conn = pymysql.connect(
host=
'127.0.0.1',
port=
3306,
user=
'root',
passwd=
'123456',
db=
'test',
charset=
"utf8" )
# 创建游标
cursor = conn.cursor(
cursor=pymysql.cursors.DictCursor)
# 执行SQL,并返回收影响行数
# truple_str=('lalala',1)
cursor.execute(
'select
*
from t2')
#fech 是去内存中获取
while
1:
res = cursor.fetchone()
if res ==
None:
break
print(res)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
|
lastrowid 获取最后的自增序列号
import pymysql
# 创建连接
conn = pymysql.connect(
host=
'127.0.0.1',
port=
3306,
user=
'root',
passwd=
'123456',
db=
'test',
charset=
"utf8" )
# 创建游标
cursor = conn.cursor()
cursor.executemany(
'insert into t3(t_name) VALUES (%s)',[(
'aaa07'),(
'aaa08'),(
'aaa09')])
tid=cursor.lastrowid
print(tid)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
|
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28572479/viewspace-2152559/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28572479/viewspace-2152559/