文件操作-访问数据库

1.创建连接

print("====创建数据库test=====")
import pymysql
# 创建数据库并打开
db = pymysql.connect(host='localhost',port =3306,user='root',passwd='12345',db='test',charset='utf8')
cursor = db.cursor()
# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS 51job")
# 使用预处理语句创建表
sql = """CREATE TABLE 51job (
         jobName varchar(50) NOT NULL,
         company varchar(50) not null,
         location varchar(50) not null,
         salary varchar(20),
         postDate varchar(50) not null)"""
cursor.execute(sql)
# 关闭数据库连接
db.close()```


## 2.添加数据

```python
print("============添加表51job.xls到51job数据表中===========")
import xlrd, pymysql
# 创建数据库连接
conn = pymysql.connect(host='localhost', user="root", password='12345'
                       , database='test')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = "insert into 51job (jobName,company,location,salary,postDate) values (%s,%s,%s,%s,%s)"
# 打开文件
file = xlrd.open_workbook("E:/python/Python资料/51job.xls")
sheet_1 = file.sheet_by_index(0)  # 根据sheet页的排序选取sheet
row_content = sheet_1.row_values(0)  # 获取指定行的数据，返回列表，排序自0开始
row_number = sheet_1.nrows  # 获取有数据的最大行数
for i in range(2, row_number):
    jobName = sheet_1.cell(i, 0).value
    company = sheet_1.cell(i, 1).value
    location = sheet_1.cell(i, 2).value
    salary = sheet_1.cell(i, 3).value
    postDate = sheet_1.cell(i, 4).value
    values = (jobName, company, location, salary, postDate)
    # 执行sql语句插入数据
    cursor.execute(sql, values)
    conn.commit()
cursor.close()
conn.close()

3.解析数据

print("===========读取51job.xls文件==========")
import xlrd
# 使用xlrd的函数打开本地文件，并使用变量存储
workbook=xlrd.open_workbook('E:\\python\\Python资料\\51job.xls')
sheetNames=workbook.sheet_names()
# 通过表名称获取指定表
sheetOne=workbook.sheet_by_name(sheetNames[0])
# 获取行数
row=sheetOne.nrows
for i in range(row):
    each_row=sheetOne.row_values(i)
    print(each_row)```

![在这里插入图片描述](https://img-blog.csdnimg.cn/20200917205328522.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4MzYxNzcy,size_16,color_FFFFFF,t_70#pic_center)


## 4.查询数据

```python
from config import get_connect
import os
import csv
import xlrd
from xlutils.copy import copy
import xlwt
from openpyxl import Workbook
connect = get_connect('test')
# 获取连接对象
print("数据库链接成功")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = connect.cursor()
# 查询操作
# select * from 51job where location like '上海-浦东新区'
def select():
    sql = "select * from 51job where location like '%s'"
    data = ('上海-浦东新区',)
    strSql = sql % data
    print(strSql)
    cursor.execute(strSql)
    for row in cursor.fetchall():
        print(row)
    print('共查找出', cursor.rowcount, '条数据')
select()