Python操作MySQL

用Python代码连接MySQL并发送命令

1.添加数据

import pymysql

# 1.连接 MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="123123", charset="utf8", db="unicom")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2.发送指令
cursor.execute("insert into admin(username, password, mobile) values('abc', '123123', '12341235821')")  # 里边写数据库指令
conn.commit()

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

• 注意
  在发送指令时，不能用format去做SQL的拼接，会导致SQL注入，有安全隐患，应使用内置的excute方法

  # 1.用列表传
  sql = "insert into admin(username, password, mobile) values(%s, %s, %s)"  # 里边写数据库指令
  cursor.execute(sql, ["xyz", "qwe123", "122222222"])
  conn.commit()
  
  # 2.用字典传
  sql = "insert into admin(username, password, mobile) values(%(n1)s, %(n2)s, %(n3)s)"  # 里边写数据库指令
  cursor.execute(sql, {"n1": "abc", "n2": "qwe123", "n3": "123123123"})
  conn.commit()
  

2.获取数据

import pymysql


# 1.连接MySQL
conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="401025", charset="utf8", db="unicom")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 2.发送指令
cursor.execute("select * from admin")  # 里边写数据库指令
data_list = cursor.fetchall()
for row_list in data_list:
    print(row_list)

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