创建userinfo表并插入数据
MariaDB [sqlexample]> create table userinfo(nid int not null auto_increment primary key,username varchar(16) not null,password varchar(16) not null) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.03 sec)
MariaDB [sqlexample]> show tables;
+----------------------+
| Tables_in_sqlexample |
+----------------------+
| class |
| course |
| score |
| student |
| teacher |
| userinfo |
+----------------------+
6 rows in set (0.00 sec)
MariaDB [sqlexample]> insert into userinfo(nid,username,password) values(1,'smoke','smoke520');
Query OK, 1 row affected (0.00 sec)
MariaDB [sqlexample]> insert into userinfo(username,password) values('smoke1','smoke520');
Query OK, 1 row affected (0.01 sec)
MariaDB [sqlexample]> insert into userinfo(username,password) values('smoke2','smoke520');
Query OK, 1 row affected (0.00 sec)
MariaDB [sqlexample]> select * from userinfo;
+-----+----------+----------+
| nid | username | password |
+-----+----------+----------+
| 1 | smoke | smoke520 |
| 2 | smoke1 | smoke520 |
| 3 | smoke2 | smoke520 |
+-----+----------+----------+
3 rows in set (0.00 sec)
sql注入测试代码
#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: use_pymysql
# time: 2021/09/04
import pymysql
# 创建连接
conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor()
cursor.execute('select username,password from userinfo where username=%s and password=%s',('smoke','smoke520'))
result = cursor.fetchone()
print(result)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/join/use_pymysql.py
('smoke', 'smoke520')
Process finished with exit code 0
#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: use_pymysql
# time: 2021/09/04
import pymysql
# 创建连接
conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor()
cursor.execute('select username,password from userinfo where username=%s and password=%s',('smoke','smoke123'))
result = cursor.fetchone()
print(result)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/join/use_pymysql.py
None
Process finished with exit code 0
#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: use_pymysql
# time: 2021/09/04
import pymysql
# 创建连接
conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor()
sql = 'select username,password from userinfo where username="%s" and password="%s"'
sql = sql %('smoke','smoke520')
cursor.execute(sql)
result = cursor.fetchone()
print(result)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: use_pymysql
# time: 2021/09/04
import pymysql
# 创建连接
conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor()
sql = 'select username,password from userinfo where username="%s" and password="%s"'
sql = sql %('smoke','smoke123')
cursor.execute(sql)
result = cursor.fetchone()
print(result)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/join/use_pymysql.py
None
Process finished with exit code 0
#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: use_pymysql
# time: 2021/09/04
import pymysql
# 创建连接
conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor()
sql = 'select username,password from userinfo where username="%s" and password="%s"'
sql = sql %('smoke" or 1=1 -- ','smoke123') #密码错误也能获取数据
cursor.execute(sql)
result = cursor.fetchone()
print(result)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/join/use_pymysql.py
('smoke', 'smoke520')
Process finished with exit code 0
#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: use_pymysql
# time: 2021/09/04
import pymysql
# 创建连接
conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor()
sql = 'select username,password from userinfo where username="%s" and password="%s"'
sql = sql %('smoke" -- ','smoke123')
cursor.execute(sql)
result = cursor.fetchone()
print(result)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/join/use_pymysql.py
('smoke', 'smoke520')
Process finished with exit code 0
#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: use_pymysql
# time: 2021/09/04
import pymysql
# 创建连接
conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor()
sql = 'select username,password from userinfo where username="%s" and password="%s"'
sql = sql %('smoke111" or 1=1 -- ','smoke123')
cursor.execute(sql)
result = cursor.fetchone()
print(result)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/join/use_pymysql.py
('smoke', 'smoke520')
Process finished with exit code 0
#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: use_pymysql
# time: 2021/09/04
import pymysql
# 创建连接
conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor()
cursor.execute('select username,password from userinfo where username=%s and password=%s',('smoke" or 1=1 -- ','smoke123'))
# sql = 'select username,password from userinfo where username="%s" and password="%s"'
# sql = sql %('smoke111" or 1=1 -- ','smoke123')
# cursor.execute(sql)
result = cursor.fetchone()
print(result)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/join/use_pymysql.py
None
Process finished with exit code 0
游标设置为字典类型代码
#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: use_pymysql
# time: 2021/09/04
import pymysql
# 创建连接
conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor()
r = cursor.execute('select * from student')
print(cursor.fetchall())
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/join/use_pymysql.py
((1, '男', 1, '马大狗'), (2, '女', 1, '钢蛋'), (3, '男', 1, '张三'), (4, '男', 1, '张一'), (5, '女', 1, '张二'), (6, '男', 1, '张四'), (7, '女', 2, '铁锤'), (8, '男', 2, '李三'), (9, '男', 2, '李一'), (10, '女', 2, '李二'),
(11, '男', 2, '李四'), (12, '女', 3, '如花'), (13, '男', 3, '刘三'), (14, '男', 3, '刘一'), (15, '女', 3, '刘二'), (16, '男', 3, '刘四'), (17, '女', 1, '鸭蛋'), (18, '女', 1, '鸭蛋1'), (19, '女', 1, '鸭蛋2'), (20, '女', 1,
'鸭蛋3'))
Process finished with exit code 0
#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: use_pymysql
# time: 2021/09/04
import pymysql
# 创建连接
conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute('select * from student')
print(cursor.fetchall())
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/join/use_pymysql.py
[{'sid': 1, 'gender': '男', 'class_id': 1, 'sname': '马大狗'}, {'sid': 2, 'gender': '女', 'class_id': 1, 'sname': '钢蛋'}, {'sid': 3, 'gender': '男', 'class_id': 1, 'sname': '张三'}, {'sid': 4, 'gender': '男', 'class_id
': 1, 'sname': '张一'}, {'sid': 5, 'gender': '女', 'class_id': 1, 'sname': '张二'}, {'sid': 6, 'gender': '男', 'class_id': 1, 'sname': '张四'}, {'sid': 7, 'gender': '女', 'class_id': 2, 'sname': '铁锤'}, {'sid': 8, '
gender': '男', 'class_id': 2, 'sname': '李三'}, {'sid': 9, 'gender': '男', 'class_id': 2, 'sname': '李一'}, {'sid': 10, 'gender': '女', 'class_id': 2, 'sname': '李二'}, {'sid': 11, 'gender': '男', 'class_id': 2, 'sname
': '李四'}, {'sid': 12, 'gender': '女', 'class_id': 3, 'sname': '如花'}, {'sid': 13, 'gender': '男', 'class_id': 3, 'sname': '刘三'}, {'sid': 14, 'gender': '男', 'class_id': 3, 'sname': '刘一'}, {'sid': 15, 'gender': '
女', 'class_id': 3, 'sname': '刘二'}, {'sid': 16, 'gender': '男', 'class_id': 3, 'sname': '刘四'}, {'sid': 17, 'gender': '女', 'class_id': 1, 'sname': '鸭蛋'}, {'sid': 18, 'gender': '女', 'class_id': 1, 'sname': '鸭蛋1
'}, {'sid': 19, 'gender': '女', 'class_id': 1, 'sname': '鸭蛋2'}, {'sid': 20, 'gender': '女', 'class_id': 1, 'sname': '鸭蛋3'}]
Process finished with exit code 0
#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: use_pymysql
# time: 2021/09/04
import pymysql
# 创建连接
conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute('select sname,sid,gender from student')
print(cursor.fetchall())
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/join/use_pymysql.py
[{'sname': '马大狗', 'sid': 1, 'gender': '男'}, {'sname': '钢蛋', 'sid': 2, 'gender': '女'}, {'sname': '张三', 'sid': 3, 'gender': '男'}, {'sname': '张一', 'sid': 4, 'gender': '男'}, {'sname': '张二', 'sid': 5, 'gender
': '女'}, {'sname': '张四', 'sid': 6, 'gender': '男'}, {'sname': '铁锤', 'sid': 7, 'gender': '女'}, {'sname': '李三', 'sid': 8, 'gender': '男'}, {'sname': '李一', 'sid': 9, 'gender': '男'}, {'sname': '李二', 'sid': 10,
'gender': '女'}, {'sname': '李四', 'sid': 11, 'gender': '男'}, {'sname': '如花', 'sid': 12, 'gender': '女'}, {'sname': '刘三', 'sid': 13, 'gender': '男'}, {'sname': '刘一', 'sid': 14, 'gender': '男'}, {'sname': '刘二'
, 'sid': 15, 'gender': '女'}, {'sname': '刘四', 'sid': 16, 'gender': '男'}, {'sname': '鸭蛋', 'sid': 17, 'gender': '女'}, {'sname': '鸭蛋1', 'sid': 18, 'gender': '女'}, {'sname': '鸭蛋2', 'sid': 19, 'gender': '女'}, {
'sname': '鸭蛋3', 'sid': 20, 'gender': '女'}]
Process finished with exit code 0
#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: use_pymysql
# time: 2021/09/04
import pymysql
# 创建连接
conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute('select sname as name,sid,gender from student')
print(cursor.fetchall())
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/join/use_pymysql.py
[{'name': '马大狗', 'sid': 1, 'gender': '男'}, {'name': '钢蛋', 'sid': 2, 'gender': '女'}, {'name': '张三', 'sid': 3, 'gender': '男'}, {'name': '张一', 'sid': 4, 'gender': '男'}, {'name': '张二', 'sid': 5, 'gender'
: '女'}, {'name': '张四', 'sid': 6, 'gender': '男'}, {'name': '铁锤', 'sid': 7, 'gender': '女'}, {'name': '李三', 'sid': 8, 'gender': '男'}, {'name': '李一', 'sid': 9, 'gender': '男'}, {'name': '李二', 'sid': 10, '
gender': '女'}, {'name': '李四', 'sid': 11, 'gender': '男'}, {'name': '如花', 'sid': 12, 'gender': '女'}, {'name': '刘三', 'sid': 13, 'gender': '男'}, {'name': '刘一', 'sid': 14, 'gender': '男'}, {'name': '刘二', '
sid': 15, 'gender': '女'}, {'name': '刘四', 'sid': 16, 'gender': '男'}, {'name': '鸭蛋', 'sid': 17, 'gender': '女'}, {'name': '鸭蛋1', 'sid': 18, 'gender': '女'}, {'name': '鸭蛋2', 'sid': 19, 'gender': '女'}, {'
name': '鸭蛋3', 'sid': 20, 'gender': '女'}]
Process finished with exit code 0
#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: use_pymysql
# time: 2021/09/04
import pymysql
# 创建连接
conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute('select sname as f,sid,gender from student')
print(cursor.fetchall())
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/join/use_pymysql.py
[{'f': '马大狗', 'sid': 1, 'gender': '男'}, {'f': '钢蛋', 'sid': 2, 'gender': '女'}, {'f': '张三', 'sid': 3, 'gender': '男'}, {'f': '张一', 'sid': 4, 'gender': '男'}, {'f': '张二', 'sid': 5, 'gender': '女'}, {'f':
'张四', 'sid': 6, 'gender': '男'}, {'f': '铁锤', 'sid': 7, 'gender': '女'}, {'f': '李三', 'sid': 8, 'gender': '男'}, {'f': '李一', 'sid': 9, 'gender': '男'}, {'f': '李二', 'sid': 10, 'gender': '女'}, {'f': '李四',
'sid': 11, 'gender': '男'}, {'f': '如花', 'sid': 12, 'gender': '女'}, {'f': '刘三', 'sid': 13, 'gender': '男'}, {'f': '刘一', 'sid': 14, 'gender': '男'}, {'f': '刘二', 'sid': 15, 'gender': '女'}, {'f': '刘四', '
sid': 16, 'gender': '男'}, {'f': '鸭蛋', 'sid': 17, 'gender': '女'}, {'f': '鸭蛋1', 'sid': 18, 'gender': '女'}, {'f': '鸭蛋2', 'sid': 19, 'gender': '女'}, {'f': '鸭蛋3', 'sid': 20, 'gender': '女'}]
Process finished with exit code 0
获取新创建数据自增ID
#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: use_pymysql
# time: 2021/09/04
import pymysql
# 创建连接
conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute('insert into class(caption) values(%s)',('ooo'))
conn.commit()
nid = cursor.lastrowid
print(nid)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/join/use_pymysql.py
10
Process finished with exit code 0
查询class表
MariaDB [sqlexample]> select * from class; +-----+--------------+ | cid | caption | +-----+--------------+ | 1 | 三年二班 | | 2 | 三年三班 | | 3 | 一年二班 | | 4 | 二年九班 | | 5 | 全栈二班 | | 6 | 全栈二班 | | 7 | uuu | | 8 | 'nnn' | | 9 | op | | 10 | ooo | +-----+--------------+ 10 rows in set (0.00 sec)
修改代码
#!/usr/bin/env python3.8
# -*- coding: UTF-8 -*-
# __author: smoke
# file: use_pymysql
# time: 2021/09/04
import pymysql
# 创建连接
conn = pymysql.connect(host='172.16.100.67', port=3306, user='root', passwd='smoke520', db='sqlexample',charset='utf8')
# 创建游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.executemany('insert into class(caption) values(%s)',[('ooo'),('ooo1'),('ooo2')])
conn.commit()
nid = cursor.lastrowid
print(nid)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
/home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/venv/bin/python /home/smoke/文档/DocumentFile/PycharmProjects/pythonProject/join/use_pymysql.py
13
Process finished with exit code 0
查询class表
MariaDB [sqlexample]> select * from class; +-----+--------------+ | cid | caption | +-----+--------------+ | 1 | 三年二班 | | 2 | 三年三班 | | 3 | 一年二班 | | 4 | 二年九班 | | 5 | 全栈二班 | | 6 | 全栈二班 | | 7 | uuu | | 8 | 'nnn' | | 9 | op | | 10 | ooo | | 11 | ooo | | 12 | ooo1 | | 13 | ooo2 | +-----+--------------+ 13 rows in set (0.00 sec)
浙公网安备 33010602011771号