#!/usr/bin/env python3

import matplotlib.pyplot as plt

plt.style.use('ggplot')                     # 使用ggplot样式表来模拟ggplot2风格的图形

# 为条形图准备数据
customers = ['ABC', 'DEF', 'GHI', 'JKL', 'MNO']
customers_index = range(len(customers))
sale_amounts = [127, 90, 201, 111, 232]

# 绘图
fig = plt.figure()                          # 创建了一个基础图
ax1 = fig.add_subplot(1, 1, 1)              # 在基础图中创建1行1列的子图，并使用第1个也是唯一的一个子图
ax1.bar(customers_index, sale_amounts, align='center', color='darkblue')    # 创建条形图
ax1.xaxis.set_ticks_position('bottom')      # x刻度线位置
ax1.yaxis.set_ticks_position('left')        # y刻度线位置
plt.xticks(customers_index, customers, rotation=0, fontsize='small')        # 将刻度线标签更改为实际的客户名称
plt.xlabel('Customer Name')                 # 添加x轴标签
plt.ylabel('Sale Amount')                   # 添加y轴标签
plt.title('Sale Amount per Customer')       # 添加图形标题
plt.savefig('bar_plot.png', dpi=400, bbox_inches='tight')       # 保存统计图到当前文件夹
plt.show()

#!/usr/bin/env python3

import numpy as np
import matplotlib.pyplot as plt

plt.style.use('ggplot')

# 为直方图准备数据
mu1, mu2, sigma = 100, 130, 15
x1 = mu1 + sigma * np.random.randn(10000)        # 使用随机数生成器创建两个正态分布变量
x2 = mu2 + sigma * np.random.randn(10000)

# 绘图
fig = plt.figure()
ax1 = fig.add_subplot(1, 1, 1)
n, bins, patches = ax1.hist(x1, bins=50, density=False, color='darkgreen')          # 创建概率分布图
n, bins, patches = ax1.hist(x2, bins=50, density=False, color='orange', alpha=0.5)
ax1.xaxis.set_ticks_position('bottom')
ax1.yaxis.set_ticks_position('left')
plt.xlabel('Bins')
plt.ylabel('Number of Values in Bin')
fig.suptitle('Histograms', fontsize=14, fontweight='bold')      # 为基础图添加一个居中的标题
ax1.set_title('Two Frequency Distributions')    # 为子图添加一个居中的标题，位于基础图标题下面
plt.savefig('histogram.png', dpi=400, bbox_inches='tight')
plt.show()

#!/usr/bin/env python3

from numpy.random import randn
import matplotlib.pyplot as plt

plt.style.use('ggplot')

# 为折线图准备数据
plot_data1 = randn(50).cumsum()         # 使用randn创建绘图所用的随机数据
plot_data2 = randn(50).cumsum()
plot_data3 = randn(50).cumsum()
plot_data4 = randn(50).cumsum()

# 绘图
fig = plt.figure()
ax1 = fig.add_subplot(1, 1, 1)
ax1.plot(plot_data1, marker=r'o', color=u'blue', linestyle='-', label='Blue Solid')     # 创建4条折线
ax1.plot(plot_data2, marker=r'+', color=u'red', linestyle='--', label='Red Dashed')
ax1.plot(plot_data3, marker=r'*', color=u'green', linestyle='-.', label='Green Dash Dot')
ax1.plot(plot_data4, marker=r's', color=u'orange', linestyle=':', label='Orange Dotted')
ax1.xaxis.set_ticks_position('bottom')
ax1.yaxis.set_ticks_position('left')
ax1.set_title('Line Plots: Markers, Colors, and Linestyles')
plt.xlabel('Draw')
plt.ylabel('Random Number')
plt.legend(loc='best')      # 创建图例，根据图中的空白部分将图例放在最合适的位置
plt.savefig('line_plot.png', dpi=400, bbox_inches='tight')
plt.show()

#!/usr/bin/env python3

import numpy as np
import matplotlib.pyplot as plt

plt.style.use('ggplot')

# 为散点图准备数据
x = np.arange(start=1, stop=15, step=1)
y_linear = x + 5 * np.random.randn(14)
y_quadratic = x**2 + 10 * np.random.randn(14)
fn_linear = np.poly1d(np.polyfit(x, y_linear, deg=1))
fn_quadratic = np.poly1d(np.polyfit(x, y_quadratic, deg=2))

# 绘图
fig = plt.figure()
ax1 = fig.add_subplot(1, 1, 1)
ax1.plot(x, y_linear, 'bo', x, y_quadratic, 'go', x, fn_linear(x), 'b-',
         x, fn_quadratic(x), 'g-', linewidth=2)                # 创建带有两条回归曲线的散点图
ax1.xaxis.set_ticks_position('bottom')
ax1.yaxis.set_ticks_position('left')
ax1.set_title('Scatter Plots Regression Lines')
plt.xlabel('x')
plt.ylabel('f(x)')
plt.xlim((min(x) - 1, max(x) + 1))        # 设置x轴范围
plt.ylim((min(y_quadratic) - 10, max(y_quadratic) + 10))      # 设置y轴范围
plt.savefig('scatter_plot.png', dpi=400, bbox_inches='tight')
plt.show()

#!/usr/bin/env python3

import numpy as np
import matplotlib.pyplot as plt

plt.style.use('ggplot')

# 为箱线图准备数据
N = 500
normal = np.random.normal(loc=0.0, scale=1.0, size=N)
lognormal = np.random.lognormal(mean=0.0, sigma=1.0, size=N)
index_value = np.random.random_integers(low=0, high=N-1, size=N)
normal_sample = normal[index_value]
lognormal_sample = lognormal[index_value]
box_plot_data = [normal, normal_sample, lognormal, lognormal_sample]

# 绘图
fig = plt.figure()
ax1 = fig.add_subplot(1, 1, 1)
box_labels = ['normal', 'normal_sample', 'lognormal', 'lognormal_sample']       # 保存每个箱线图的标签
ax1.boxplot(box_plot_data, notch=False, sym='.', vert=True, whis=1.5, showmeans=True,
            labels=box_labels)          # 创建4个箱线图
ax1.xaxis.set_ticks_position('bottom')
ax1.yaxis.set_ticks_position('left')
ax1.set_title('Box Plots: Resampling of Two Distributions')
plt.xlabel('Distribution')
plt.ylabel('Value')
plt.savefig('box_plot.png', dpi=400, bbox_inches='tight')
plt.show()

#!/usr/bin/env python3

import csv
import glob
import os
import sys
from datetime import date
from xlrd import open_workbook, xldate_as_tuple

item_numbers_file = sys.argv[1]
path_to_folder = sys.argv[2]
output_file = sys.argv[3]

item_numbers_to_find = []
with open(item_numbers_file, 'r', newline='') as item_numbers_csv_file:
    filereader = csv.reader(item_numbers_csv_file)
    for row in filereader:
        item_numbers_to_find.append(row[0])
print(item_numbers_to_find)

filewriter = csv.writer(open(output_file, 'a', newline=''))
file_counter = 0
line_counter = 0
count_of_item_numbers = 0
for input_file in glob.glob(os.path.join(path_to_folder, '*.*')):
    file_counter += 1
    if input_file.split('.')[1] == 'csv':
        with open(input_file, 'r', newline='') as csv_in_file:
            filereader = csv.reader(csv_in_file)
            header = next(filereader)
            for row in filereader:
                row_of_output = []
                for column in range(len(header)):
                    if column == 3:
                        cell_value = str(row[column]).lstrip('$').replace(',', '').strip()
                        row_of_output.append(cell_value)
                    else:
                        cell_value = str(row[column]).strip()
                        row_of_output.append(cell_value)
                row_of_output.append(os.path.basename(input_file))
                if row[0] in item_numbers_to_find:
                    filewriter.writerow(row_of_output)
                    count_of_item_numbers += 1
                line_counter += 1
    elif input_file.split('.')[1] == 'xls' or input_file.split('.')[1] == 'xlsx':
        workbook = open_workbook(input_file)
        for worksheet in workbook.sheets():
            try:
                header = worksheet.row_values(0)
            except IndexError:
                pass
            for row in range(1, worksheet.nrows):
                row_of_output = []
                for column in range(len(header)):
                    if worksheet.cell_type(row, column) == 3:
                        cell_value = xldate_as_tuple(worksheet.cell(row, column).value, workbook.datemode)
                        cell_value = str(date(*cell_value[0:3])).strip()
                        row_of_output.append(cell_value)
                    else:
                        cell_value = str(worksheet.cell_value(row, column)).strip()
                        row_of_output.append(cell_value)
                row_of_output.append(os.path.basename(input_file))
                row_of_output.append(worksheet.name)
                if str(worksheet.cell(row, 0).value).split('.')[0].strip() in item_numbers_to_find:
                    filewriter.writerow(row_of_output)
                    count_of_item_numbers += 1
                line_counter += 1

print('Number of files: ', file_counter)
print('Number of lines: ', line_counter)
print('Number of item numbers: ', count_of_item_numbers)

#!/usr/bin/env python3

import csv
import sys
from datetime import date, datetime


def date_diff(date1, date2):
    try:
        diff = str(datetime.strptime(date1, '%m/%d/%Y') - datetime.strptime(date2, '%m/%d/%Y')).split()[0]
    except:
        diff = 0
    if diff == '0:00:00':
        diff = 0
    return diff


input_file = sys.argv[1]
output_file = sys.argv[2]

packages = {}
previous_name = 'N/A'
previous_package = 'N/A'
previous_package_date = 'N/A'
first_row = True
today = date.today().strftime('%m/%d/%Y')
with open(input_file, 'r', newline='') as input_csv_file:
    filereader = csv.reader(input_csv_file)
    header = next(filereader)
    for row in filereader:
        current_name = row[0]
        current_package = row[1]
        current_package_date = row[3]
        if current_name not in packages:
            packages[current_name] = {}
        if current_package not in packages[current_name]:
            packages[current_name][current_package] = 0
        if current_name != previous_name:
            if first_row:
                first_row = False
            else:
                diff = date_diff(today, previous_package_date)
                if previous_package not in packages[previous_name]:
                    packages[previous_name][previous_package] = int(diff)
                else:
                    packages[previous_name][previous_package] += int(diff)
        else:
            diff = date_diff(current_package_date, previous_package_date)
            packages[previous_name][previous_package] += int(diff)
        previous_name = current_name
        previous_package = current_package
        previous_package_date = current_package_date

header = ['Customer Name', 'Category', 'Total Time (in Days)']
with open(output_file, 'w', newline='') as output_csv_file:
    filewriter = csv.writer(output_csv_file)
    filewriter.writerow(header)
    for customer_name, customer_name_value in packages.items():
        for package_category, package_category_value in packages[customer_name].items():
            row_of_output = []
            print(customer_name, package_category, package_category_value)
            row_of_output.append(customer_name)
            row_of_output.append(package_category)
            row_of_output.append(package_category_value)
            filewriter.writerow(row_of_output)

#!/usr/bin/env python3

import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

messages = {}
notes = []
with open(input_file, 'r', newline='') as text_file:
    for row in text_file:
        if '[Note]' in row:
            row_list = row.split(' ', 4)
            day = row_list[0].strip()
            note = row_list[4].strip('\n').strip()
            if note not in notes:
                notes.append(note)
            if day not in messages:
                messages[day] = {}
            if note not in messages[day]:
                messages[day][note] = 1
            else:
                messages[day][note] += 1

filewriter = open(output_file, 'w', newline='')
header = ['Date']
header.extend(notes)
header = ','.join(map(str, header)) + '\n'
print(header)
filewriter.write(header)
for day, day_value in messages.items():
    row_of_output = []
    row_of_output.append(day)
    for index in range(len(notes)):
        if notes[index] in day_value.keys():
            row_of_output.append(day_value[notes[index]])
        else:
            row_of_output.append(0)
    output = ','.join(map(str, row_of_output)) + '\n'
    print(output)
    filewriter.write(output)
filewriter.close()

18 Jogging 102271561469000 -13.53 16.89 -6.4
18 Jogging 102271641608000 -5.75 16.89 -0.46
18 Jogging 102271681617000 -2.18 16.32 11.07
18 Jogging 3.36
18 Downstairs 103260201636000 -4.44 7.06 1.95
18 Downstairs 103260241614000 -3.87 7.55 3.3
18 Downstairs 103260321693000 -4.06 8.08 4.79
18 Downstairs 103260365577000 -6.32 8.66 4.94
18 Downstairs 103260403083000 -5.37 11.22 3.06
18 Downstairs 103260443305000 -5.79 9.92 2.53
6 Walking 0 0 0 3.214402

18,Jogging,102271561469000,-13.53,16.89,-6.4
18,Jogging,102271641608000,-5.75,16.89,-0.46
18,Jogging,102271681617000,-2.18,16.32,11.07
18,Downstairs,103260201636000,-4.44,7.06,1.95
18,Downstairs,103260241614000,-3.87,7.55,3.3
18,Downstairs,103260321693000,-4.06,8.08,4.79
18,Downstairs,103260365577000,-6.32,8.66,4.94
18,Downstairs,103260403083000,-5.37,11.22,3.06
18,Downstairs,103260443305000,-5.79,9.92,2.53

#!/usr/bin/env python3

import csv
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

with open(input_file, 'r', newline='') as csv_in_file:
    with open(output_file, 'w', newline='') as csv_out_file:
        filereader = csv.reader(csv_in_file, delimiter=' ')
        filewriter = csv.writer(csv_out_file)
        for row_list in filereader:
            if len(row_list) == 6 and row_list[2] != '0':
                filewriter.writerow(row_list)

#!/usr/bin/env python3

import csv
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

movementsDict = {}
inputData = []
with open(input_file, 'r', newline='') as csv_in_file:
    filereader = csv.reader(csv_in_file)
    movementsList = []
    for row_list in filereader:
        movementsList.append(row_list[1])
    for movement in movementsList:
        if movement not in movementsDict:
            movementsDict[movement] = 1
        else:
            movementsDict[movement] += 1
    for movement, movementsDict[movement] in movementsDict.items():
        print('Movement: %-15s' % movement, end='')
        print('Amount: ' + str(movementsDict[movement]))
        movementsDict[movement] = movementsDict[movement] // 100 * 100
        inputData.append(movementsDict[movement])
    csv_in_file.seek(0, 0)
    with open(output_file, 'w', newline='') as csv_out_file:
        filewriter = csv.writer(csv_out_file)
        countWalking = 0
        countJogging = 0
        countUpstairs = 0
        countDownstairs = 0
        countStanding = 0
        countSitting = 0
        for row_list in filereader:
            if row_list[1] == 'Walking':
                if countWalking < inputData[0]:
                    filewriter.writerow(row_list)
                    countWalking += 1
            elif row_list[1] == 'Jogging':
                if countJogging < inputData[1]:
                    filewriter.writerow(row_list)
                    countJogging += 1
            elif row_list[1] == 'Upstairs':
                if countUpstairs < inputData[2]:
                    filewriter.writerow(row_list)
                    countUpstairs += 1
            elif row_list[1] == 'Downstairs':
                if countDownstairs < inputData[3]:
                    filewriter.writerow(row_list)
                    countDownstairs += 1
            elif row_list[1] == 'Standing':
                if countStanding < inputData[4]:
                    filewriter.writerow(row_list)
                    countStanding += 1
            else:
                if countSitting < inputData[5]:
                    filewriter.writerow(row_list)
                    countSitting += 1

-0.72 9.62 0.14982383
-4.02 11.03 3.445948
0.95 14.71 3.636633

#!/usr/bin/env python3

import csv
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

my_columns = [3, 4, 5]
with open(input_file, 'r', newline='') as csv_in_file:
    with open(output_file, 'w', newline='') as csv_out_file:
        filereader = csv.reader(csv_in_file)
        filewriter = csv.writer(csv_out_file, delimiter=' ')
        for row_list in filereader:
            row_list_output = []
            for index_value in my_columns:
                row_list_output.append(row_list[index_value])
            filewriter.writerow(row_list_output)

-0.72 9.62 0.14982383,-4.02 11.03 3.445948,0.95 14.71 3.636633,-3.57 5.75 -5.407278,-5.28 8.85 -9.615966,-1.14 15.02 -3.8681788,7.86 11.22 -1.879608,6.28 4.9 -2.3018389,0.95 7.06 -3.445948,-1.61 9.7 0.23154591,6.44 12.18 -0.7627395,5.83 12.07 -0.53119355,7.21 12.41 0.3405087,6.17 12.53 -6.701211,-1.08 17.54 -6.701211,-1.69 16.78 3.214402,-2.3 8.12 -3.486809,-2.91 0 -4.7535014,-2.91 0 -4.7535014,-4.44 1.84 -2.8330324

#!/usr/bin/env python3

import csv
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

with open(input_file, 'r', newline='') as csv_in_file:
    with open(output_file, 'w', newline='') as csv_out_file:
        filereader = csv.reader(csv_in_file)
        filewriter = csv.writer(csv_out_file)
        total = []
        for row_list in filereader:
            total.append(row_list[0])
        outputList = []
        a_list = []
        a = 0
        for data in total:
            if a < 19:
                a += 1
                a_list.append(data)
            else:
                a_list.append(data)
                outputList.append(a_list)
                a = 0
                a_list = []
        for output_line in outputList:
            filewriter.writerow(output_line)

#include <stdio.h>

int main(void) {
	int i, sum = 0;
	for (i = 1; i <= 100; i++) {
		sum += i;
	}
	printf("%d\n", sum);
	return 0;
}

#include <stdio.h>

int main(void) {
	int n = 100, sum = 0;
	sum = (1 + n) * n / 2;
	printf("%d\n", sum);
	return 0;
}

// 算法1：循环求和
int i, sum = 0;                 // 执行1次
for (i = 1; i <= 100; i++) {    // 执行(n+1)次
	sum += i;                   // 执行n次
}
printf("%d\n", sum);            // 执行1次

// 算法2：高斯求和
int n = 100, sum = 0;           // 执行1次
sum = (1 + n) * n / 2;          // 执行1次
printf("%d\n", sum);            // 执行1次

int n = 100, sum = 0;           // 执行1次
sum = (1 + n) * n / 2;          // 执行1次
printf("%d\n", sum);            // 执行1次

int i;
for (i = 0; i < n; i++) {
    /* 时间复杂度为O(1)的程序步骤序列 */
}

int count = 1;
while (count > n) {
    count = count * 2;
    /* 时间复杂度为O(1)的程序步骤序列 */
}

int i, j;
for (i = 0; i < n; i++) {
    for (j = 0; j < n; j++) {
        /* 时间复杂度为O(1)的程序步骤序列 */
    }
}

int i, j;
for (i = 0; i < m; i++) {
    for (j = 0; j < n; j++) {
        /* 时间复杂度为O(1)的程序步骤序列 */
    }
}

int i, j;
for (i = 0; i < n; i++) {
    for (j = i; j < n; j++) {
        /* 时间复杂度为O(1)的程序步骤序列 */
    }
}

void function(int count) {
    int j;
    for (j = count; j < n; j++) {
        /* 时间复杂度为O(1)的程序步骤序列 */
    }
}

n++;
function(n);
int i, j;
for (i = 0; i < n; i++) {
    function(i);
}
for (i = 0; i < n; i++) {
    for (j = i; j < n; j++) {
        /* 时间复杂度为O(1)的程序步骤序列 */
    }
}

n++;                              // 执行次数为1
function(n);                      // 执行次数为n
int i, j;
for (i = 0; i < n; i++) {         // 执行次数为n×n
    function(i);
}
for (i = 0; i < n; i++) {         // 执行次数为n(n+1)/2
    for (j = i; j < n; j++) {
        /* 时间复杂度为O(1)的程序步骤序列 */
    }
}

ADT 抽象数据类型名
Data
    数据元素之间逻辑关系的定义
Operation
    操作1
        初始条件
        操作结果描述
    操作2
        ……
    操作n
        ……
endADT

#!/usr/bin/env python3

import csv
import MySQLdb
import sys

# CSV输出文件的路径和文件名
output_file = sys.argv[1]

# 连接MySQL数据库
con = MySQLdb.connect(host='localhost', port=3306, db='my_suppliers', user='root', passwd='## your password ##')
c = con.cursor()

# 创建写文件的对象，并写入标题行
filewriter = csv.writer(open(output_file, 'w', newline=''), delimiter=',')
header = ['Supplier Name', 'Invoice Number', 'Part Number', 'Cost', 'Purchase Date']
filewriter.writerow(header)

# 查询Suppliers表，并将结果写入CSV输出文件
c.execute("""SELECT *
             FROM Suppliers
             WHERE Cost > 700.0;""")
rows = c.fetchall()
for row in rows:
    filewriter.writerow(row)

#!/usr/bin/env python3

import csv
import MySQLdb
import sys

# CSV输入文件的路径和文件名
input_file = sys.argv[1]

# 连接MySQL数据库
con = MySQLdb.connect(host='localhost', port=3306, db='my_suppliers', user='root', passwd='## your password ##')
c = con.cursor()

# 读取CSV文件并更新特定的行
file_reader = csv.reader(open(input_file, 'r', newline=''), delimiter=',')
header = next(file_reader, None)
for row in file_reader:
    data = []
    for column_index in range(len(header)):
        data.append(str(row[column_index]).strip())
    print(data)
    c.execute("""UPDATE Suppliers SET Cost=%s, Purchase_Date=%s WHERE Supplier_Name=%s;""", data)
con.commit()

# 查询Suppliers表
c.execute("SELECT * FROM Suppliers")
rows = c.fetchall()
for row in rows:
    output = []
    for column_index in range(len(row)):
        output.append(str(row[column_index]))
    print(output)

CREATE TABLE IF NOT EXISTS Suppliers
(Supplier_Name VARCHAR(20),
Invoice_Number VARCHAR(20),
Part_Number VARCHAR(20),
Cost FLOAT,
Purchase_Date DATE);

GRANT ALL PRIVILEGES ON my_suppliers.* TO 'username'@'localhost';
FLUSH PRIVILEGES;

#!/usr/bin/env python3

import csv
import MySQLdb
import sys
from datetime import datetime, date

# CSV输入文件的路径和文件名
input_file = sys.argv[1]

# 连接MySQL数据库
con = MySQLdb.connect(host='localhost', port=3306, db='my_suppliers', user='root', passwd='## your password ##')
c = con.cursor()

# 向Suppliers表中插入数据
file_reader = csv.reader(open(input_file, 'r', newline=''))
header = next(file_reader)
for row in file_reader:
    data = []
    for column_index in range(len(header)):
        if column_index < 4:
            data.append(str(row[column_index]).lstrip('$').replace(',', '').strip())
        else:
            a_date = datetime.date(datetime.strptime(str(row[column_index]), '%m/%d/%Y'))
            a_date = a_date.strftime('%Y-%m-%d')
            data.append(a_date)
    print(data)
    c.execute("""INSERT INTO Suppliers VALUES (%s, %s, %s, %s, %s);""", data)
con.commit()
print("")

# 查询Suppliers表
c.execute("SELECT * FROM Suppliers")
rows = c.fetchall()
for row in rows:
    row_list_output = []
    for column_index in range(len(row)):
        row_list_output.append(str(row[column_index]))
    print(row_list_output)

#!/usr/bin/env python3

import sqlite3

# 创建SQLite3内存数据库
# 创建带有4个属性的sales表
con = sqlite3.connect(':memory:')
query = """CREATE TABLE sales
           (customer VARCHAR(20),
           product VARCHAR(40),
           amount FLOAT,
           date DATE);"""
con.execute(query)
con.commit()

# 在表中插入几行数据
data = [('Richard Lucas', 'Notepad', 2.50, '2014-01-02'),
        ('Jenny Kim', 'Binder', 4.15, '2014-01-15'),
        ('Svetlana Crow', 'Printer', 155.75, '2014-02-03'),
        ('Stephen Randolph', 'Computer', 679.40, '2014-02-20')]
statement = "INSERT INTO sales VALUES(?, ?, ?, ?)"
con.executemany(statement, data)
con.commit()

# 查询sales表
cursor = con.execute("SELECT * FROM sales")
rows = cursor.fetchall()

# 计算查询结果中行的数量
row_counter = 0
for row in rows:
    print(row)
    row_counter += 1
print('Number of rows: %d' % (row_counter))

('Richard Lucas', 'Notepad', 2.5, '2014-01-02')
('Jenny Kim', 'Binder', 4.15, '2014-01-15')
('Svetlana Crow', 'Printer', 155.75, '2014-02-03')
('Stephen Randolph', 'Computer', 679.4, '2014-02-20')
Number of rows: 4

#!/usr/bin/env python3

import csv
import sqlite3
import sys

# CSV输入文件的路径和文件名
input_file = sys.argv[1]

# 创建SQLite3内存数据库
# 创建带有5个属性的Suppliers表
con = sqlite3.connect('E:\\python_pycharm\\Python数据分析基础\\第4章 数据库\\Suppliers.db')
c = con.cursor()
create_table = """CREATE TABLE IF NOT EXISTS Suppliers
                  (Supplier_Name VARCHAR(20),
                  Invoice_Number VARCHAR(20),
                  Part_Number VARCHAR(20),
                  Cost FLOAT,
                  Purchase_Date DATE);"""
c.execute(create_table)
con.commit()

# 读取CSV文件
# 向Suppliers表中插入数据
file_reader = csv.reader(open(input_file, 'r'), delimiter=',')
header = next(file_reader, None)
for row in file_reader:
    data = []
    for column_index in range(len(header)):
        data.append(row[column_index])
    print(data)
    c.execute("INSERT INTO Suppliers VALUES (?, ?, ?, ?, ?);", data)
con.commit()
print('')

# 查询Suppliers表
output = c.execute("SELECT * FROM Suppliers")
rows = output.fetchall()
for row in rows:
    output = []
    for column_index in range(len(row)):
        output.append(str(row[column_index]))
    print(output)

#!/usr/bin/env python3

import csv
import sqlite3
import sys

# CSV输入文件的路径和文件名
input_file = sys.argv[1]

# 创建SQLite3内存数据库
# 创建带有4个属性的sales表
con = sqlite3.connect(':memory:')
query = """CREATE TABLE IF NOT EXISTS sales
           (customer VARCHAR(20),
           product VARCHAR(40),
           amount FLOAT,
           date DATE);"""
con.execute(query)
con.commit()

# 向表中插入几行数据
data = [('Richard Lucas', 'Notepad', 2.50, '2014-01-02'),
        ('Jenny Kim', 'Binder', 4.15, '2014-01-15'),
        ('Svetlana Crow', 'Printer', 155.75, '2014-02-03'),
        ('Stephen Randolph', 'Computer', 679.40, '2014-02-20')]
for tuple in data:
    print(tuple)
statement = "INSERT INTO sales VALUES(?, ?, ?, ?)"
con.executemany(statement, data)
con.commit()

# 读取CSV文件并更新特定的行
file_reader = csv.reader(open(input_file, 'r'), delimiter=',')
header = next(file_reader, None)
for row in file_reader:
    data = []
    for column_index in range(len(header)):
        data.append(row[column_index])
    print(data)
    con.execute("UPDATE sales SET amount=?, date=? WHERE customer=?;", data)
con.commit()

# 查询sales表
cursor = con.execute("SELECT * FROM sales")
rows = cursor.fetchall()
for row in rows:
    output = []
    for column_index in range(len(row)):
        output.append(str(row[column_index]))
    print(output)

#!/usr/bin/env python3

import glob
import os
import sys
from xlrd import open_workbook

input_directory = sys.argv[1]

workbook_counter = 0
for input_file in glob.glob(os.path.join(input_directory, '*.xlsx')):
    workbook = open_workbook(input_file)
    print('Workbook: %s' % os.path.basename(input_file))
    print('Number of worksheets: %d' % workbook.nsheets)
    for worksheet in workbook.sheets():
        print('Worksheet name: ', worksheet.name, '\tRows: ', worksheet.nrows, '\tColumns: ', worksheet.ncols)
    workbook_counter += 1
print('Number of Excel workbooks: %d' % (workbook_counter))

#!/usr/bin/env python3

import glob
import os
import sys
from datetime import date
from xlrd import open_workbook, xldate_as_tuple
from xlwt import Workbook

input_folder = sys.argv[1]
output_file = sys.argv[2]

output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('all_data_all_workbooks', cell_overwrite_ok=True)
data = []
first_worksheet = True
for input_file in glob.glob(os.path.join(input_folder, '*.xlsx')):
    print(os.path.basename(input_file))
    with open_workbook(input_file) as workbook:
        for worksheet in workbook.sheets():
            if first_worksheet:
                header_row = worksheet.row_values(0)
                data.append(header_row)
                first_worksheet = False
            for row_index in range(1, worksheet.nrows):
                row_list = []
                for column_index in range(worksheet.ncols):
                    cell_value = worksheet.cell_value(row_index, column_index)
                    cell_type = worksheet.cell_type(row_index, column_index)
                    if cell_type == 3:
                        date_cell = xldate_as_tuple(cell_value, workbook.datemode)
                        date_cell = date(*date_cell[0:3]).strftime('%m/%d/%Y')
                        row_list.append(date_cell)
                    else:
                        row_list.append(cell_value)
                data.append(row_list)
    for list_index, output_list in enumerate(data):
        for element_index, element in enumerate(output_list):
            output_worksheet.write(list_index, element_index, element)
output_workbook.save(output_file)

#!/usr/bin/env python3

import pandas as pd
import glob
import os
import sys

input_path = sys.argv[1]
output_file = sys.argv[2]

all_workbooks = glob.glob(os.path.join(input_path, '*.xlsx'))
data_frames = []
for workbook in all_workbooks:
    all_worksheets = pd.read_excel(workbook, sheet_name=None, index_col=None)
    for worksheet_name, data in all_worksheets.items():
        data_frames.append(data)
all_data_concatenated = pd.concat(data_frames, axis=0, ignore_index=True)
writer = pd.ExcelWriter(output_file)
all_data_concatenated.to_excel(writer, sheet_name='all_data_all_workbooks', index=False)
writer.save()

#!/usr/bin/env python3

import glob
import os
import sys
from datetime import date
from xlrd import open_workbook, xldate_as_tuple
from xlwt import Workbook

input_folder = sys.argv[1]
output_file = sys.argv[2]

output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('sums_and_averages')
all_data = []
sales_column_index = 3
header = ['workbook', 'worksheet', 'worksheet_total', 'worksheet_average', 'workbook_total', 'workbook_average']
all_data.append(header)
for input_file in glob.glob(os.path.join(input_folder, '*.xlsx')):
    with open_workbook(input_file) as workbook:
        list_of_totals = []
        list_of_numbers = []
        workbook_output = []
        for worksheet in workbook.sheets():
            total_sales = 0
            number_of_sales = 0
            worksheet_list = []
            worksheet_list.append(os.path.basename(input_file))
            worksheet_list.append(worksheet.name)
            for row_index in range(1, worksheet.nrows):
                try:
                    total_sales += float(str(worksheet.cell_value(row_index, sales_column_index))
                                         .strip('$').replace(',', ''))
                    number_of_sales += 1
                except:
                    total_sales += 0
                    number_of_sales += 0
            average_sales = '%.2f' % (total_sales / number_of_sales)
            worksheet_list.append(total_sales)
            worksheet_list.append(float(average_sales))
            list_of_totals.append(total_sales)
            list_of_numbers.append(float(number_of_sales))
            workbook_output.append(worksheet_list)
        workbook_total = sum(list_of_totals)
        workbook_average = sum(list_of_totals) / sum(list_of_numbers)
        for list_element in workbook_output:
            list_element.append(workbook_total)
            list_element.append(workbook_average)
        all_data.extend(workbook_output)

for list_index, output_list in enumerate(all_data):
    for element_index, element in enumerate(output_list):
        output_worksheet.write(list_index, element_index, element)
output_workbook.save(output_file)