python操作mysql、excel、pdf的示例

#!/usr/bin/python
# -*- coding: UTF-8 -*-

# 1. 定義Person類
class Person:

  def __init__(self, name, age):
    self.name = name
    self.age = age

  def watch_tv(self):
    print(f'{self.name} 看電視')


# 2. 定義loop函數
# 打印 1-max 中的奇數
def test_person():
  person = Person('Jake', 20)
  print(f'打印person的地址：', person)
  print(f'person.name:{person.name}')
  print(f'person.age:{person.age}')
  person.watch_tv()

  person = Person('Koko', 18)
  print(f'打印person的地址：', person)
  print(f'person.name:{person.name}')
  print(f'person.age:{person.age}')
  person.watch_tv()


# 3. 執行calculate方法
# 計算 當前值小於1，當前值：0
# 計算 1 >= 1: True
# 計算 2 >= 1: True
# 計算 10 >= 1: True
test_person()

#!/usr/bin/python
# -*- coding: UTF-8 -*-

# pip3 install pymysql

import pymysql

from getpass import getpass

# from mysql.connector import connect, Error
#
host = 'xxxxxxx'
port = 3306
username = 'db_account_member'
password = 'db_account_password'
database = 'some_database'


def connect_db():
  return pymysql.connect(host=host,
              port=port,
              user=username,
              password=password,
              database=database,
              charset='utf8')


def print_error(e):
  print(f'錯誤類型:{type(e)}')
  print(f'錯誤內容:{e}')


def close_gracefully(cursor, conn):
  if cursor:
    cursor.close()
  if conn:
    conn.close()


# 查詢數據庫，可以寫任意查詢語句
def query(sql):
  try:
    conn = connect_db() # 創建連接
    cursor = conn.cursor() # 建立遊標
    cursor.execute(sql) # 執行sql語句
    return cursor.fetchall()
  except pymysql.Error as e:
    print_error(e)
  finally:
    close_gracefully(cursor, conn)



query_sql = 'select * from category where id = 1'
rows = query(query_sql)
print('category表中的數據如下:')
print(rows)

# -*- coding: UTF-8 -*-

# 1. 導入csv庫
import csv

file_name = '../resources/test.csv'

# 2. 定義headers和rows
headers = ['index', 'name', 'sex', 'height', 'year']

rows = [
  [1, 'Jake', 'male', 177, 20],
  [2, 'Koko', 'female', 165, 18],
  [3, 'Mother', 'female', 163, 45],
  [4, 'Father', 'male', 172, 48]
]


# 3. 定義write_csv函數
# 寫入csv
def write_csv():
  print(f'文件[{file_name}]準備寫入')
  with open(f'{file_name}', 'w')as f:
    f_csv = csv.writer(f)
    f_csv.writerow(headers)
    f_csv.writerows(rows)
    print(f'文件[{file_name}]寫入完畢')


# 讀取csv
def read_csv():
  print(f'文件[{file_name}]準備讀取')
  with open(f'{file_name}')as f:
    f_csv = csv.reader(f)
    for row in f_csv:
      print(row)
  print(f'文件[{file_name}]讀取完畢')


# 4. 執行write_csv函數
write_csv()
print('------')
read_csv()

# -*- coding: UTF-8 -*-

# 導引
# 安裝相關依賴
# pip3 install xlrd

# 引入xlrd去支持讀取xls相關的文件
import xlrd

# 定義文件名
file_name = '../resources/sku.xls'

# 1. 讀取xls文件
# 預計輸出
# sku.xls該文檔有 3 個tab頁
sku_file = xlrd.open_workbook(file_name)
print("{0}該文檔有 {1} 個tab頁".format(file_name, sku_file.nsheets))
print("每個tab頁，頁名分別為: {0}".format(sku_file.sheet_names()))

# 2. 讀取xls文件第1頁
# 預計輸出
# tab頁名：Sheet1，該tab頁共有59行，3列
# A6方格的值：1908165140370878
current_sheet_index = 0 # 下標0為第一頁tab
current_sheet = sku_file.sheet_by_index(current_sheet_index)
print("tab頁名：{0}，該tab頁共有{1}行，{2}列".format(current_sheet.name, current_sheet.nrows, current_sheet.ncols))
print("A6方格的值：{0}".format(current_sheet.cell_value(rowx=5, colx=0)))

# 3. 打印每頁的數據，每一行的數據為一個數組
# 預計輸出
# [text:'1908154975415329', text:'鞋面是織物 鞋底是聚氨酯底的哦', text:'鞋底是5厘米 內增是3厘米 總高度是8厘米左右哦']
# [text:'1908040228021948', text:'鞋面是飛織 鞋底是聚氨酯底的哦', text:'鞋底高度是3厘米左右哦']
# ...以下省略後續打印
for rx in range(current_sheet.nrows):
  print(current_sheet.row(rx))

import platform
import pdfkit

# 這裡根據自己的系統修改對應的wkhtmltopdf安裝路徑，修改其中一個就行瞭
win_path = 'D:/tools/wkhtmltopdf'
non_win_path = '/usr/local/bin/wkhtmltopdf'


def wkhtmltopdf_path():
  system = platform.system()
  if system == 'Darwin':
    print('蘋果系統，可以生成pdf')
    path = non_win_path
  elif system == 'Windows':
    print('Windows系統，可以生成pdf')
    path = win_path
  elif system == 'Linux系統':
    print('Linux系統，可以生成pdf')
    path = non_win_path
  else:
    print('其他系統，暫不支持生成pdf')
    raise Exception('其他系統，暫不支持生成pdf')
  return path


def pre_config():
  return pdfkit.configuration(wkhtmltopdf=wkhtmltopdf_path())


# 從鏈接地址生成pdf
def generate_pdf_from_url(url, output_file_path):
  config = pre_config()
  pdfkit.from_url(url, output_file_path)


# 從字符串生成pdf
def generate_pdf_from_string(str, output_file_path):
  config = pre_config()
  pdfkit.from_string(str, output_file_path)


generate_pdf_from_url('https://baidu.com', '../temp/baidu_test.pdf')

generate_pdf_from_string('hello', '../temp/hello.pdf')