Python 读取txt，数据库连接，批量存入数据库

• Python 第一次用，做个记录

import textwrap
import pymssql
from datetime import datetime, timedelta


class Data:
    # 数据库连接
    server = ""
    user = ""
    password = ''
    database = ''
    list = []  # 定义列表用来存放SQL语句

    def __init__(self, LogFileName):

        self.LogFileName = LogFileName

    # 格林时间转北京时间
    def UTCtoBJS(self, UTC):
        # UTC_format = "%Y-%m-%dT%H:%M:%S.%fZ"
        BJS_format = "%Y-%m-%d %H:%M:%S"
        UTC = datetime.strptime(UTC, BJS_format)
        # 格林威治时间+8小时变为北京时间
        BJS = UTC + timedelta(hours=8)
        BJS = BJS.strftime(BJS_format)
        return BJS

    # 批量导入数据库
    def StoreToDatabase(self):
        try:
            connect = pymssql.connect(self.server, self.user, self.password, self.database)  # 建立连接
            if connect:
                print("连接成功!")

                cursor = connect.cursor()  # 创建一个游标对象,python里的sql语句都要通过cursor来执行

                sql = ''  # 循环拼接SQL
                for items in self.list:
                    sql += textwrap.dedent(items)

                cursor.execute(sql)  # 执行sql语句
                connect.commit()  # 提交
                self.list.clear()  # 清空 list
                cursor.close()
                connect.close()

            else:
                print("连接失败!")

        except BaseException as ex:
            print('抛出异常', ex)

    def main(self):
        f = open(self.LogFileName, 'r', encoding='UTF-8', errors='replace')
        lines = f.readlines()
        self.list.clear()  # list全局变量清空

      

        print('开始执行，打印文件名', self.LogFileName)

        for line in lines:
            if SourceSite in line:
                array = line.split(' ', 5)

                AccessDate = self.UTCtoBJS('%s %s' % (array[0], array[1]))  # 时间
                SystemPage = array[4][1:len(array[4])]  

              
                if 'App Store' in SystemPage:
                    # txt记录一份 可直接删除
                    # with open('lucky.txt', "a", encoding='UTF-8') as file:  # ”w"代表着每次运行都覆盖内容
                    #     file.write(
                    #         '%s %s %s %s \n' % (time, array[2], array[3], myPageURL))

                    sql = "INSERT dbo.ChemicalbookAccessData (AccessDate,SourceSite,SystemPage,CreateDate)VALUES(\'%s\',\'%s\',\'%s\',GETDATE());" % (
                        AccessDate, SourceSite, SystemPage)
                    self.list.append(sql)  # list 存储 SQL 每20条执行一次

                    if len(self.list) == 20:
                        self.StoreToDatabase()
                        print('每次导入20条数据,导入成功')
        # 再来一个导入
        if len(self.list) > 0:
            print('再来一个导入%s条数据' % (len(self.list)))
            self.StoreToDatabase()
            print('最后一次导入成功')

        print('------------程序结束执行---------------')


if __name__ == '__main__':
    dateNow = datetime.now().strftime('u_ex%C%m%d.log')  # 生成文件名
    mm = ChemicalbookAccessData('D:/Projects/' + dateNow)  # 直接传路径
    mm.main()
 

• Python简单例子 类 函数 类方法 property装饰器方法

class Student:

    # 构造函数
    def __init__(self, name: str, age: int, sex: str):
        self.Name = name
        self.Age = age
        self.Sex = sex
        print("__init__ 构造函数调用")

    def __del__(self):
        print("__del__ 我是一个析构函数")

    # 普通函数， self 起到一个占位的作用，是Student类的实例
    def speak(self):
        print("my name" + self.Name)
        self.__speak2()

    # 加两个下划线为私有函数
    def __speak2(self):
        print("私有函数只能在类的内部访问")

    def speak1(self):
        print("我是一个被类方法调用的普通函数")

    # 类方法
    @classmethod
    def sayClassMethod(cls, data_string):
        print("say hello" + data_string)
        cls.speak1(cls)

    # property装饰器
    @property
    def sayPropertyMethod(self):
        print("property装饰器方法" + str(self.Age))


# 实例化 输出Name
obj = Student('林则徐', 80, "男")
print(obj.Name)

# 实例化调用函数
obj.speak()
# 实例化调用类方法
obj.sayClassMethod("123")
# 调用 property装饰器 方法
obj.sayPropertyMethod

obj.Age = 88
obj.sayPropertyMethod

# 类方法直接调用
Student.sayClassMethod("类方法")

• Python 时间格式化 json序列化

import time
import json

ticks = time.time()

print(ticks)
print(time.localtime(time.time()))
# 优化格式化化版本
print(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))

# str = input("请输入：")
# print("你输入的内容是: ", str)

# json 序列化反序列化
data = [{'a': 1, 'b': 2, 'c': 3, 'd': 4, 'e': 5}]
data2 = json.dumps(data)
print(data2)
print(json.loads(data2))

• Flask 安装和简单使用

pip install Flask

from flask import Flask

app = Flask(__name__)


@app.route("/")
def index():
    return "<h1 style='color:red'>Hello World</h1>"


if __name__ == "__main__":
    app.run()

• pymssql 简单使用

import pymssql

connect = pymssql.connect(host='localhost',
                          user='sa',
                          password='123456',
                          database='safecamera',
                          charset="utf8")  # 建立连接

if connect:
    print("连接成功!")

cursor = connect.cursor()  # 创建一个游标对象,python里的sql语句都要通过cursor来执行
sql = "SELECT * FROM trade;"
cursor.execute(sql)  # 执行sql语句
row = cursor.fetchone()  # 读取查询结果,

# 关闭连接
cursor.close()
connect.close()

# 打印下查询结果
print(row)

• pymysql 简单使用

# 连接数据库
import pymysql

connect = pymysql.Connect(host='localhost',
                          port=3306,
                          user='root',
                          passwd='123456',
                          db='safecamera',
                          charset='utf8')

# 获取游标
cursor = connect.cursor()

# 插入数据
# sql = "INSERT INTO trade (name, account, saving) VALUES ( '%s', '%s', %.2f )"
# data = ('雷军', '13512345678', 10000)

# try:
#     cursor.execute(sql % data)
#     connect.commit()

#     # 提交之后，获取刚插入的数据的ID
#     last_id = cursor.lastrowid
#     print('成功插入', cursor.rowcount, '条数据 last_id：', last_id)

# except Exception as ex:
#     connect.rollback()

# 查询数据的SQL语句
sql = "SELECT * FROM trade;"
# 执行SQL语句
cursor.execute(sql)
# 获取多条查询数据
ret = cursor.fetchall()

# 关闭连接
cursor.close()
connect.close()

# 打印下查询结果
print(ret)