通过pymysql操作mysql数据库

1增

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='students', charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 默认查询结果是元组，DictCursor设置查询的结果为字典
cursor.execute("insert into students (name) value(%s)",[data])
conn.commit()
cursor.close()
conn.close()
#一次增加多个值
cursor.executemany("insert into students (name) value(%s)",data)
其中data为一个元组列表，如[(1,2),(1,3),(3,4)]

2删

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='students', charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 默认查询结果是元组，DictCursor设置查询的结果为字典
cursor.execute("delete from students where id=%s",[nid,])
conn.commit()
cursor.close()
conn.close()

3改

　import pysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='students', charset='utf8')

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 默认查询结果是元组，DictCursor设置查询的结果为字典
cursor.execute("update students set name=%s where id=%s", [name,nid])
conn.commit()
cursor.close()
conn.close()

4查

（查询表的所有数据）

 

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='students',charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #默认查询结果是元组，DictCursor设置查询的结果为字典
cursor.execute("select *  from students")
result = cursor.fetchall()
cursor.close()
conn.close()

 

（带条件查询标的数据）

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='students', charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 默认查询结果是元组，DictCursor设置查询的结果为字典
cursor.execute("select id,name  from students where id=%s", [nid, ])
result = cursor.fetchone()
cursor.close()
conn.close()

获取最新数据的自增ID

new_id = cursor.lastrowid

 

---

一对多连表操作

 

 

 

---

多对多连表操作

 

 老师和班级之间的多对多的关系需要额外建立一张关系表。

连表查询命令为

select tearch2class.id, tearchs.id ,tearchs.tname,classes.title from tearch2class

left join tearchs on tearch2class.tearch_id=tearchs.id

left join classes on tearch2class.class_id=classes.id