python2.7:Access数据库表和CSV文件比较

2024-03-29 09:05:16 发布

您现在位置:Python中文网/ 问答频道 /正文

我需要比较.accdb文件和.csv文件。你知道吗

具有相同类型记录且计数不匹配的两个文件。所以我想显示那些不常见的记录,并将其存储到新的文本文件中,用;和列标题分隔。你知道吗

.accdb

ID  Name
1   Mak
2   Smith
3   Jack

.csv文件

ID  Name
1   Mak
2   Smith
3   Jack
4   Johnson
5   Mike

需要.txt文件

ID;  Name
4;   Johnson
5;  Mike

我的尝试

import pyodbc

CSVfile = 'E:\Python\AccessCSVFiles\EMP.csv'
TEXTfile = 'E:\Python\AccessCSVFiles\EMP_UPDATES.txt'

conn_string = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=E:\Python\AccessCSVFiles\EMP.accdb;'
con = pyodbc.connect(conn_string)
cur = con.cursor()

SQLQuery = 'SELECT * FROM EMP;'
rows = cur.execute(SQLQuery).fetchall()
records = [tuple(map(str,record)) for record in rows]

accessfile = set(records)

with open(CSVfile) as a:
    first_line = a.readline()
    with open(TEXTfile, 'w') as result:
        result.write(first_line)
        for line in a:
            if line not in accessfile:
                print line

但是在文本文件EMP_UPDATES.txt中用,分隔所有5条记录。你知道吗


Tags: 文件csvnameintxtid记录line
1条回答
网友
1楼 · 发布于 2024-03-29 09:05:16

使用DictReader读取CSV

import csv
with open(CSVfile) as csvfile:
    reader = csv.DictReader(csvfile)
    csv_data = {(row['ID'], row['Name']) for row in reader}  # generates set

如果预期只有CSV包含缺少的数据:

diff = sorted(csv_data.difference(accessfile))  # Generated Sorted list with values missing
                                                # in accessfile

要写入新文件:

with open(TEXTfile, 'w') as result:
    for missing in diff:
        result.write(";".join(missing))

相关问题 更多 >