Python操作MySQL
用Python代码连接MySQL并发送指令。
pip install pymysql
1.创建数据
import pymysql # 1.连接MySQL conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='unicom') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 2.发送指令 cursor.execute("insert into admin(username,password,mobile) values('wupeiqi','qwe123','15155555555')") conn.commit() # 3.关闭 cursor.close() conn.close()
import pymysql # 1.连接MySQL conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='unicom') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 2.发送指令(千万不要用字符串格式化去做SQL的拼接,安全隐患SQL注入) # sql = "insert into admin(username,password,mobile) values(%s,%s,%s)" # cursor.execute(sql, ["韩超", "qwe123", "1999999999"]) # sql = "insert into admin(username,password,mobile) values( %(n1)s, %(n2)s, %(n3)s)" # cursor.execute(sql, {"n1": "集宁", "n2": "qwe123", "n3": "1999999999"}) conn.commit() # 3.关闭 cursor.close() conn.close()
import pymysql while True: user = input("用户名:") if user.upper() == 'Q': break pwd = input("密码:") mobile = input("手机号:") # 1.连接MySQL conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='unicom') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 2.发送指令(千万不要用字符串格式化去做SQL的拼接,安全隐患SQL注入) sql = "insert into admin(username,password,mobile) values(%s,%s,%s)" cursor.execute(sql, [user, pwd, mobile]) conn.commit() # 3.关闭 cursor.close() conn.close()
2.查询数据
import pymysql # 1.连接MySQL conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='unicom') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 2.发送指令( *** 千万不要用字符串格式化去做SQL的拼接,安全隐患SQL注入***) cursor.execute("select * from admin where id > %s", [2, ]) # 获取符合条件的所有数据,得到的是 [ 字典,字典, ] 空列表 data_list = cursor.fetchall() for row_dict in data_list: print(row_dict) # 3.关闭连接 cursor.close() conn.close()
import pymysql # 1.连接MySQL conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='unicom') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 2.发送指令( *** 千万不要用字符串格式化去做SQL的拼接,安全隐患SQL注入***) cursor.execute("select * from admin where id > %s", [2, ]) # 获取符合条件的第一条数据,字典 None res = cursor.fetchone() print(res) # {'id': 3, 'username': '集宁', 'password': 'qwe123', 'mobile': '1999999999'} # 3.关闭连接 cursor.close() conn.close()
3.删除数据
import pymysql # 1.连接MySQL conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='unicom') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 2.发送指令( *** 千万不要用字符串格式化去做SQL的拼接,安全隐患SQL注入***) cursor.execute("delete from admin where id=%s", [3, ]) conn.commit() # 3.关闭 cursor.close() conn.close()
4.修改数据
import pymysql # 1.连接MySQL conn = pymysql.connect(host="127.0.0.1", port=3306, user='root', passwd="root123", charset='utf8', db='unicom') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 2.发送指令( *** 千万不要用字符串格式化去做SQL的拼接,安全隐患SQL注入***) cursor.execute("update admin set mobile=%s where id=%s", ["1888888888", 4, ]) conn.commit() # 3.关闭 cursor.close() conn.close()
强调:
-
在进行 新增、删除、修改时,一定要记得commit,不然数据库么有数据。
cursor.execute("..") conn.commit()
-
在查询时,不需要commit,执行fetchall / fetchone
cursor.execute("...") # 第一条数据,字典,无数据时是空列表 v1 = cursor.fetchone() # 所有数据,列表套字典,无数据时是None v1 = cursor.fetchall()
-
对于SQL语句不要用Python的字符串格式化进行拼接(会被SQL注入),一定要用execute+参数
cursor.execute(".%s..... %s", ["xx","xx"])