python使用数据库

发布网友发布时间：2022-04-21 02:40

共1个回答

热心网友时间：2022-04-07 23:29

增加一条数据

import pymysql

#返回Connection对象
#host="localhost"
con = pymysql.connect(host="192.168.31.28",
                  port=3306,user="atguigu",
                  password="atguigu",
                  db="atguigudb",
                  charset="utf8")
#返回cursor对象
cursor = con.cursor()
#SQL语言-SQL语句
sql = "insert into students(name) value('李四')"
#插入数据
cursor.execute(sql)
#提交数据,没有提交就没有数据
con.commit()
#关闭释放资源
cursor.close()
#关闭资源
con.close()

修改数据

import pymysql
#修改任意一条数据

#返回Connection对象
conn = pymysql.connect(
    host="192.168.31.28",
    db="atguigudb",
    port=3306,
    user="atguigu",
    password="atguigu",
    charset="utf8"
)
cursor = conn.cursor()
sql = "update students set name='郭靖' where id = 1"
count = cursor.execute(sql)
print("count=",count)
#提交正常数据物理上修改了
conn.commit()
cursor.close()
conn.close()

删除数据

import pymysql
#修改任意一条数据

#返回Connection对象
conn = pymysql.connect(
    host="192.168.31.28",
    db="atguigudb",
    port=3306,
    user="atguigu",
    password="atguigu",
    charset="utf8"
)
cursor = conn.cursor()
sql = "delete from students where id =20"
count = cursor.execute(sql)
print("count=",count)
conn.commit()
cursor.close()
conn.close()

查询一条数据

import pymysql
try:
    conn=pymysql.connect(
       host='192.168.31.28',
       port=3306,
       db='atguigudb',
       user='atguigu',
       passwd='atguigu',
       charset='utf8'
    )
    cursor=conn.cursor()
    cursor.execute('select * from students where id = 3')
    #返回满足这个条件的这个数据,如果有多条返回第一条,并且封装元组中
    result = cursor.fetchone()
    print(result)
    for i in result:
       print(i)
    cursor.close()
    conn.close()
except Exception as e:
    print(e.message)

查询多条数据

import pymysql
try:
    conn=pymysql.connect(
       host='192.168.31.28',
       port=3306,
       db='atguigudb',
       user='atguigu',
       passwd='atguigu',
       charset='utf8'
    )
    cursor=conn.cursor()
    cursor.execute('select * from students')
    #返回元组,如果多条数据,元组里面嵌套元组
    result = cursor.fetchall()
    print(result)
    for i in result:
       print(i)
    conn.commit()
    cursor.close()
    conn.close()
except Exception as e:
    print(e.message)

读取mysql数据，填写数据到excel

from pyexcel_xls import save_data
from pyexcel_xls import get_data
from collections import OrderedDict
import mysql.connector

#和数据库建立连接
cnx =mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='test')
#查询语句
sql = "select my_name,my_value from tbl_members "
#执行查询
cursor.execute(sql)
#获得查询结果
result = cursor.fetchall()

cursor.close()
cnx.close()

#打开预定义表头文件
xls_header= get_data("d:/xh.xls")

#获得表头数据
xh = xls_header.pop("Sheet1")

#拼接整表数据
xd = OrderedDict()
xd.update({"Sheet 1":xh+result})

#保存到另一个文件中
save_data("d:/xd.xls",xd)