python连接并操作数据库+excel+txt文件

操作数据库

import pymysql

def ocr(phoneNo,uuid):
    """
    OCR认证
    :param phoneNo: 
    :param uuid: 
    """
    connect = pymysql.Connect(
        host="xx.xx.x.x",
        port = 3306,
        user = "root",
        passwd = "pw",
        db = "test",
        charset = "utf8"
    )

    cursor = connect.cursor()

    sql = "INSERT INTO `table_a`(`login_name`, `user_source`, `user_id`, `app_id`, `auth_status`) VALUES ('%s', 'OCR', '%s', '1', 1);"
    data = (phoneNo,uuid)
    cursor.execute(sql % data)
    connect.commit()
    print(namespace + "环境" + phoneNo + "手机号OCR认证完成")

    cursor.close()
    connect.close()

# 调用
ocr(phoneNo,uuid)

操作excel

import pandas as pd
import sys,os

# 读写excel的三种方式：
# 用xlrd和xlwt进行excel读写；
# 用openpyxl进行excel读写；
# 用pandas进行excel读写；pandas最好用，我们就用它!用它!用它!

# 读一个sheet
df = pd.read_excel(r'./data/demotest.xlsx',sheet_name='Sheet1')

# # 读多个sheet
# a = ['Sheet1','Sheet2']
# df = pd.read_excel(r'./data/demotest.xlsx',sheet_name=a)
print(df)

# 写入sheet
data = pd.DataFrame(
    {
        'name':['zhangsan','lisi','wangwu'],
        'age':[15,18,21],
        'scroe':[99,98,97]
    }
)

path_pj = sys.path[0]
path_file = './data/'
path_a = os.path.join(path_pj,path_file)

if not os.path.exists(path_a):
    os.mkdir(path_a)
data.to_excel(path_a + 'demo1.xlsx')

操作txt

• 读取文件的操作
  1.打开文件，获取文件描述符
  2.操作文件描述符 读or写
  3.关闭文件

• 函数
  open函数

  
  图片.png
• 其他
  常用with语句块，可以在文件打开操作完毕之后，自动关闭文件，所以不需要再写关闭语句

# 读
file = './data/demo_txt.txt'
with open(file,'r') as f:
    lines = f.readlines()
    print(lines)

# 写，会覆盖源文件
file1 = './data/demo2_txt.txt'
with open(file1,'w') as f:
    f.write('测试测试啊')

常见最优写法

with open('data.txt') as f:
    while True:
        line = f.readline()
        f.readable() # 判断文件是否可读
        if line:
            print(line)
        else:
            break