python对mysql数据库的操作

python操作mysql数据库

1、安装pymysql

%pip3 install pymysql

2、python操作mysql数据库

# -*- coding: UTF-8 -*-
import pymysql
# ------------连接mysql数据库-----------------------
db = pymysql.connect(host="localhost",port=3306,user="root",password="123456",db= "gas",charset="utf8")
# ------------显示mysql版本-----------------------
cursor = db.cursor()
cursor.execute("SELECT VERSION()")
data = cursor.fetchone()
print("Database version : %s " % data)
db.close()
#---------------显示表中全部数据------------------------
cursor = db.cursor()
cursor.execute("SELECT * FROM tusigas")
result = cursor.fetchall()  # fetchall() 获取所有记录
for x in result:
    print(x)
db.close()
# --------------表中插入数据--------------------------
cursor = db.cursor()
sql = "INSERT INTO tusigas (name, url) VALUES (%s, %s)"
val = ("RUNOOB", "www.runoob.com")
try:
   # 执行sql语句
   cursor.execute(sql, val)
   # 提交到数据库执行
   db.commit()
except:
   # 如果发生错误则回滚
   db.rollback()
# 关闭数据库连接
db.close()
# -------------------------------------------------