通过Python使用MySQL

使用pymysql包实现，可以使用pip进行安装

pip install pymysql

示例

create table admin(
	id int auto_increment primary key,
	username varchar(64) not null,
    password varchar(64) not null,
    mobile char(11) not null
) default charset=utf8;

mysql> create table admin(
    -> id int auto_increment primary key,
    -> username varchar(64) not null,
    ->     password varchar(64) not null,
    ->     mobile char(11) not null
    -> ) default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_unicom |
+------------------+
| admin            |
+------------------+
1 row in set (0.00 sec)

mysql> desc admin;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(64) | NO   |     | NULL    |                |
| password | varchar(64) | NO   |     | NULL    |                |
| mobile   | char(11)    | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

1.创建数据

import pymysql

#1.连接MySQL
##创建连接
conn = pymysql.connect(host="127.0.0.1",port=3306,user='root',password="123456",charset='utf8',db='unicom')
##使用游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

#2.发送指令
##执行
cursor.execute("insert into admin(username,password,mobile) values('xiaoming','123','13556456575')")
##提交
conn.commit()
###!!!!!!千万不要使用字符串格式化去做SQL语句的拼接，会被SQL注入，具有安全隐患
###需要格式化输入
sql = "insert into admin(username,password,mobile) values(%s,%s,%s)"
cursor.execute(sql,["二狗","666","15684966379"])
conn.commit()
###或者
sql = "insert into admin(username,password,mobile) values(%(n1)s,%(n2)s,%(n3)s)"
cursor.execute(sql,{"n1":"王大壮","n2":"457","n3":"18756236607"})
conn.commit()

###写一个自定义连续输入
while True:
    user = input("用户名：")
    if user == "Q":
        break
    pwd = input("密码：")
    num = input("手机号：")
    sql = "insert into admin(username,password,mobile) values(%s,%s,%s)"
    cursor.execute(sql, [user,pwd,num])
    conn.commit()


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

2. 查询数据

import pymysql

#1.连接MySQL
##创建连接
conn = pymysql.connect(host="127.0.0.1",port=3306,user='root',password="123456",charset='utf8',db='unicom')
##使用游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

#2.发送指令
sql = "select * from admin where id>%s"
id = [4]
cursor.execute(sql,id)
data_list = cursor.fetchall()#以字典列表的形式返回全部数据,没有返回空列表
for row_dict in data_list:
    print(row_dict)

cursor.execute(sql,id)
first_data = cursor.fetchone()#fetchone：以字典形式返回第一条数据，没有返回None
print('\n',first_data)

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

3.删除数据

import pymysql

#1.连接MySQL
##创建连接
conn = pymysql.connect(host="127.0.0.1",port=3306,user='root',password="123456",charset='utf8',db='unicom')
##使用游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

#2.发送指令
sql = "select * from admin where id>%s"
id = [4]
cursor.execute(sql,id)
data_list = cursor.fetchall()#以字典列表的形式返回全部数据,没有返回空列表
for row_dict in data_list:
    print(row_dict)

cursor.execute(sql,id)
first_data = cursor.fetchone()#fetchone：以字典形式返回第一条数据，没有返回None
print('\n',first_data)

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

4.创建数据

import pymysql

#1.连接MySQL
##创建连接
conn = pymysql.connect(host="127.0.0.1",port=3306,user='root',password="123456",charset='utf8',db='unicom')
##使用游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

#2.发送指令
sql = "select * from admin where id>%s"
id = [4]
cursor.execute(sql,id)
data_list = cursor.fetchall()#以字典列表的形式返回全部数据,没有返回空列表
for row_dict in data_list:
    print(row_dict)

cursor.execute(sql,id)
first_data = cursor.fetchone()#fetchone：以字典形式返回第一条数据，没有返回None
print('\n',first_data)

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

注意

• 在使用新增、删除和修改数据时，记得commit
• 查询使用fetchall和fetchone