通过pymysql操作mysql数据库

1增

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

2删

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

3改

1 　import pysql
2 
3 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='students', charset='utf8')
4 
5 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 默认查询结果是元组，DictCursor设置查询的结果为字典
6 cursor.execute("update students set name=%s where id=%s", [name,nid])
7 conn.commit()
8 cursor.close()
9 conn.close()

4查

（查询表的所有数据）

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


（带条件查询标的数据）

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


获取最新数据的自增ID

1 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

人生苦短，我用cnblog