一、连接
前提已安装依赖MySQL-python==1.2.5(我这里的版本),可以按需安装
import MySQLdb conn=MySQLdb.connect(host="127.0.0.1", port=3306,user="myusername",passwd="mypasswd",db="mydbname",charset="utf8") cursor=conn.cursor() ''' 操作db ''' cursor.close()
二、读数据
import MySQLdb conn=MySQLdb.connect(host="127.0.0.1", port=3306,user="myusername",passwd="mypasswd",db="mydbname",charset="utf8") cursor=conn.cursor() ''' 操作db ''' sql = "select * from user" cursor.execute(sql) # 取结果,可以使用其他方法,如fetchone(),可查看文档或网上搜索 result = cursor.fetchall() print(result) cursor.close()
三、写数据
3.1 直接拼sql方式
import MySQLdb conn=MySQLdb.connect(host="127.0.0.1", port=3306,user="myusername",passwd="mypasswd",db="mydbname",charset="utf8") cursor=conn.cursor() ''' 操作db ''' try: sql = "update user set age='%d' where id='%d';" % (age, id) print(sql) cursor.execute(sql) conn.commit() # 提交到数据库,一定要记得提交 except Exception as e: print(str(e)) db.rollback() # 发送错误时回滚 cursor.close()
3.2 使用参数方式
import MySQLdb conn=MySQLdb.connect(host="127.0.0.1", port=3306,user="myusername",passwd="mypasswd",db="mydbname",charset="utf8") cursor=conn.cursor() ''' 操作db ''' try: sql = "update user set age='%d' where id='%d';" param = (age, id) cursor.execute(sql, param) conn.commit() # 提交到数据库,一定要记得提交 except Exception as e: print(str(e)) db.rollback() # 发送错误时回滚 cursor.close()
在该种形式下,若某个参数的值为空,直接在变量param中的相应位置的参数用None替换即可,即
param = (None, id)