更多方法参考原博。
1.安装pymysql模块 pip install pymysql
2.连接数据库:账号、密码、ip、端口号、数据库
3.建立游标
4.执行sql
5.获取结果
6.关闭游标
7.关闭连接
import pymysql
coon = pymysql.connect(
host = '127.0.0.1',user = 'root',passwd = '123456',
port = 3306,db = 'mydb',charset = 'utf8'
#port必须写int类型
#charset必须写utf8,不能写utf-8
)
cur = coon.cursor() #建立游标
cur.execute("select * from stu") #查询数据
res = cur.fetchall() #获取结果
print(res)
cur.close() #关闭游标
coon.close() #关闭连接
---------------------------------------------------------------------------
#如果是插入数据,则要commit一下,把第9行换成以下两行
cur.execute('insert into stu(name,sex) VALUE ("pzp","man");')
coon.commit()
封装成一个函数
def my_db(host,user,passwd,db,sql,port= 3306,charset = 'utf8'):
import pymysql
coon = pymysql.connect(host=host,
port=port,
user=user,
passwd = passwd,
db = db,
charset = charset
)
cur = coon.cursor()
cur.execute(sql)
if sql.strip()[:6].upper() == 'SELECT':
res = cur.fetchall()
else:
coon.commit()
res = 'ok'
cur.close()
coon.close()
return res