数据库——pymysql模块的使用（13）

1.基本用法——建立链接，获取游标，执行sql语句，关闭

建立远程链接账号和权限

mysql> grant all on *.* to 'root'@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.40 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.23 sec)

#-*- coding:utf-8 -*-
import pymysql
user = input("用户名：").strip()
pwd = input("密码：").strip()
#建立链接
conn = pymysql.connect(
    host = "192.168.110.1",
    port = 3306,
    user = "root",
    password = "123",
    db = "db1",
    charset = "utf8"
)

#拿游标
cursor = conn.cursor()

#执行sql
sql = 'select * from user_info where name = %s and pwd = %s'
print(sql)
rows = cursor.execute(sql,(user,pwd))
#关闭
cursor.close()
conn.close()

if rows:
    print("登录成功")
else:
    print("登录失败")

示例

2.增删改

#-*- coding:utf-8 -*-
import pymysql
#建立链接
conn = pymysql.connect(
    host = "192.168.110.1",
    port = 3306,
    user = "root",
    password = "123",
    db = "db1",
    charset = "utf8"
)

#拿游标
cursor = conn.cursor()

#执行sql
###########增############
# sql = 'insert into user_info(name,pwd) values (%s,%s)'
# #插入一条记录
# rows = cursor.execute(sql,('xxx',123))
# conn.commit()

# #插入多条记录
# rows = cursor.executemany(sql,[('xyy','ba'),('yxy','abc'),('yyy','dhdf')])
# conn.commit()
# print(rows)

###########删############
# sql = "delete from user_info where id = %s ;"
# rows = cursor.execute(sql,(3,))
# conn.commit()
# print(rows)

###########改############

sql = 'update user_info set pwd = %s where name = "egon4" '
rows = cursor.execute(sql,'aaa')
conn.commit()
print(rows)
#关闭
cursor.close()
conn.close()

示例

3.查

#-*- coding:utf-8 -*-
import pymysql
#建立链接
conn = pymysql.connect(
    host = "192.168.110.1",
    port = 3306,
    user = "root",
    password = "123",
    db = "db1",
    charset = "utf8"
)

#拿游标
# cursor = conn.cursor()
cursor = conn.cursor(pymysql.cursors.DictCursor)#以字典形式显示
#执行sql
###########查############
sql = 'select * from user_info;'
rows = cursor.execute(sql)
# print(rows)
#一次取一个
# print(cursor.fetchone())  #打印一条记录
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchone())

# print(cursor.fetchmany(2))#一次取多个
# print(cursor.fetchall())#取所有

cursor.scroll(3,mode='absolute') # 相对绝对位置移动
# cursor.scroll(3,mode='relative') # 相对当前位置移动
print(cursor.fetchone())
cursor.scroll(1,mode='relative') # 相对当前位置移动
print(cursor.fetchone())
#关闭
cursor.close()
conn.close()

示例——查

4.获取插入的最后一条数据的自增ID

#-*- coding:utf-8 -*-
import pymysql
#建立链接
conn = pymysql.connect(
    host = "192.168.110.1",
    port = 3306,
    user = "root",
    password = "123",
    db = "db1",
    charset = "utf8"
)

#拿游标
cursor = conn.cursor()

#执行sql
###########增############
sql = 'insert into user_info(name,pwd) values (%s,%s)'
rows = cursor.executemany(sql,[('xyyx','ba'),('yxyx','abc'),('yyyx','dhdf')])
print(cursor.lastrowid)#在插入语句后查询
conn.commit()

#关闭
cursor.close()
conn.close()

View Code