day38python中导入mysql数据库

1.安装第三方库：

pip install -i https://pypi.doubanio.com/simple pymysql 安装mysql第三方库镜像文件
pip install -i https://pypi.doubanio.com/simple -U pip 更新pip方法一；非必须
python -m pip install -i https://pypi.doubanio.com/simple -U pip 更新pip方法二；非必须
pip install -i https://pypi.doubanio.com/simple pylint #安装pylint（用于检查代码的规范度）
python中读入excel数据：xlrd
python中写入excel数据：xlwt
ctl+alt+L：代码美化快捷键

2.添加语句

2.1添加流程

1.创建数据库连接对象
2.通过连接对象获取游标；游标对象cursor能够发出sql语句
3.通过游标执行sql并获得执行结果
4.操作成功提交事务
5.关闭连接，释放资源
import pymysql
pymysql.connect(主机地址，端口，数据库名字，编码，用户名，密码) 在最后若写上autocommit=True则在执行完后会自动提交
cursor = con.cursor()
result = cursor.execute('insert into tb_dept values (%s,%s,%s)',(no,name,loc)) # %s：安全占位符
if result == 1:
con.commit()
print('添加成功')

import pymysql
no=int(input('部门编号:'))
name=input('部门名字:')
loc=input('部门所在地:')
con = pymysql.connect(host='112.74.61.160', port=3306,
                      database='hrs', charset='utf8',
                      user='root', password='123456')
try:
    cursor = con.cursor()
    result = cursor.execute('insert into tb_dept values (%s,%s,%s)',(no,name,loc))
    if result == 1:
        con.commit()
        print('添加成功')
    except pymysql.MySQLError as error:
        print(error)
        con.rollback() #操作失败回滚（撤销）事务
    finally:
        con.close()

3删除语句

no=int(input('部门编号:'))
con = pymysql.connect(host='112.74.61.160', port=3306,
                      database='hrs', charset='utf8',
                      user='root', password='123456')
try:
    with con.cursor() as cursor: 
    意义同cursor = con.cursor()，但在操作执行完成后会自动关闭游标cursor
        result = cursor.execute('delete from tb_dept where dno=%s',(no,))
    if result == 1:
        con.commit()
        print('删除成功')
finally:
    con.close()

4更新语句

pip install -i https://pypi.doubanio.com/simple pylint #安装pylint（用于检查代码的规范度）

no = int(input('部门编号:'))
name = input('部门名字:')
loc = input('部门所在地:')
con = pymysql.connect(host='112.74.61.160', port=3306,
                      database='hrs', charset='utf8',
                      user='root', password='123456')
try:
    with con.cursor() as cursor:
    result = cursor.execute('update tb_dept set dname=%s,dloc=%s where       dno=%s', (name, loc, no)) 
   if result == 1:
       con.commit()
       print('更新成功')
finally:
    con.close()

4查询语句

import pymysql
from pymysql.cursors import DictCursor
con = pymysql.connect(host='112.74.61.160', port=3306,
                      database='hrs', charset='utf8',
                      user='root', password='123456')
try:
   --- 将cursor设置成DictCursor，以字典的形式返回，所有字典组成一个列表；
            若不设置，则返回元祖---
     with con.cursor(cursor=DictCursor) as cursor:
       cursor.execute('select dno as no, dname as name, dloc as loc from tb_dept')
       ----- 通过游标抓取获取到的数据---
       results = cursor.fetchall()
       print(results)
       print('编号\t名称\t所在地')
    for dept in results:
       print(dept['no'], end='\t')
       print(dept['name'], end='\t')
       print(dept['loc'])
    finally:
       con.close()

5对象查询

import pymysql
from pymysql.cursors import DictCursor
class Dept():
    def __init__(self,no,name,loc):
        self.no=no
        self.name=name
        self.loc=loc
    def __str__(self):
        return f'{self.no}\t{self.name}\t{self.loc}' # 格式字符串
con = pymysql.connect(host='112.74.61.160', port=3306,
                      database='hrs', charset='utf8',
                      user='root', password='123456')
try:
    with con.cursor(cursor=DictCursor) as cursor:
        cursor.execute('select dno as no, dname as name, dloc as loc from tb_dept')
        results = cursor.fetchall()
        print(results)
        print('编号\t名称\t所在地')
        for result in results:
            dept=Dept(**result) # **result:字典解包
            print(dept)
finally:
    con.close()