Python-MySQL CRUD操作

安装PyMySQL

pip install pymysql

对象类比

访问数据库流程

connection对象

con = pymysql.connect(host = '',                      port = '',                      user = '',                      passwd = '',                      db = '',                      charset = 'utf-8' )     

游标对象cursor

one = cur.fetchone()print(one)#获取剩余的3条记录three = cur.fetchmany(3)print(three)# 获取剩余所有记录列表rows = cur.fetchall()print("表中剩余所有记录:", rows)print("number of records:", len(rows))结果如下(1, 'aaa')((2, 'bbb'), (3, 'ccc'), (4, 'ddd'))表中所有记录: ((5, 'eee'), (6, 'fff'))number of records: 2

事务回滚

try:    cur.execute();      db.commit()    print("Success")except Exception as e:    print(e)    db.rollback()

execute执行流程

CRUD操作
增加(Create)、读取查询(Retrieve)、更新(Update)和删除(Delete)

import pymysqlconn = pymysql.connect("IP或者域名","mysql账户名","mysql密码","数据库名")cur = conn.cursor()#如果表存在,删除表cur.execute("DROP TABLE IF EXISTS demo")#建立表 (列名 类型)cur.execute('CREATE TABLE demo(num int ,str varchar(20));')#插入#两种方式均可,但必须用双引号cur.execute("INSERT INTO demo VALUES(%d, '%s')" % (1,'aaa'))cur.execute("INSERT INTO demo VALUES(2,'bbb')")#更新cur.execute("UPDATE demo SET str ='ccc' where num = 1")#查询cur.execute("SELECT * FROM demo")# 获取所有记录列表rows = cur.fetchall()print("表中所有记录:",rows)print("number of records:",len(rows))for i in rows:    print("num=%s,str=%s" % i)#提交事务,使上述操作生效conn.commit()#关闭游标对象cur.close()#关闭数据库连接conn.close()