python操作mysql数据库需要先安装pymysql模块,在之前博客中可翻看如何安装,最简单的就是pip install pymysql
1.导入pymysql模块 import pymysql
2.连接数据库,需要ip,账号,密码,端口号,数据库等信息
3.建立游标
4.执行sql语句
5.获取结果
6.关闭游标
7.关闭连接
import pymysql
coon = pymysql.connect(host='192.168.21.129',user='root',passwd='',port=,db='test',charset='utf8')
cur = coon.cursor()
sql = 'select * from user'
cur.execute(sql)
res = cur.fetchall()
print(res)
cur.close()
coon.close()
import pymysql
coon = pymysql.connect(host='192.168.21.129',user='root',passwd='',port=,db='test',charset='utf8')
cur = coon.cursor() #建立游标
cur.execute('insert into user(username,passwd) VALUE ("2222","4444");') #执行sql
coon.commit() #delete update insert必须得coomit
cur.close() #关闭游标
coon.close() #关闭连接
在执行select语句和delete update insert 语句不一样,那我们可以定义一个函数,在函数中用条件来判断sql语句
def my_db(host,user,passwd,db,sql,port=,charset='utf8'):
import pymysql
coon = pymysql.connect(user=user,
host=host,
port=port,
passwd=passwd,
db=db,
charset=charset
)
cur = coon.cursor() #建立游标
cur.execute(sql)#执行sql
if sql.strip()[:].upper()=='SELECT': #sql语句切片取前6位,然后转换为大写字母,再进行比较
res = cur.fetchall()
else:
coon.commit()
res = 'ok'
cur.close()
coon.close()
return res