「Python」「Access」「Excel」客户信息收集程序

2022-11-10 客户需求如下：

实施步骤

1.按照pyodbc库

2.如果没有装过微软的access数据库的话，那么需要先安装access的驱动程序

32位：http://download.microsoft.com/download/E/4/2/E4220252-5FAE-4F0A-B1B9-0B48B5FBCCF9/AccessDatabaseEngine.exe

64位：http://download.microsoft.com/download/E/4/2/E4220252-5FAE-4F0A-B1B9-0B48B5FBCCF9/AccessDatabaseEngine_X64.exe

3.Excel表格

4.代码

#导入pyodbc
import pyodbc
mdb_file=r'C:\\Users\\Administrator\\jupyter\\access\\Database1.accdb'
driver = '{Microsoft Access Driver (*.mdb,*.accdb)}'
conn = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + DBfile + ";Uid=;Pwd=;")
cur = conn.cursor()
sql = "SELECT * FROM 表1"
print(sql)
cur.execute(sql)
alldata = cur.fetchall()

#导入pypyodbc
import pypyodbc
mdb = 'Driver={Microsoft Access Driver (*.mdb,*.accdb)};DBQ=C:\\Users\\Administrator\\jupyter\\access\\Database1.accdb'
conn = pypyodbc.win_connect_mdb(mdb)
cur = conn.cursor()
sql = "SELECT * FROM 表1"
print(sql)
cur.execute(sql)
alldata = cur.fetchall()
print(alldata)

#读取access文件
[x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]
mdb_file=r'C:\\Users\\Administrator\\jupyter\\access\\Database1.accdb'
driver = '{Microsoft Access Driver (*.mdb,*.accdb)}'
conn = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + mdb_file + ";Uid=;Pwd=;")
cur = conn.cursor()
#cur.execute('DROP TABLE CustomerInfo ')
#遍历ACCDB文件中的表名
tables=[]
for table_name in cur.tables(tableType="TABLE"):
    print(table_name.table_name)
    tables.append(table_name.table_name)
print(tables)
   

# 创建access表的sql语句
sql = """
create table CustomerInfo
(
CustomerID AUTOINCREMENT PRIMARY KEY,
FName varchar(255),
LName varchar(255),
StateName varchar(255),
Gender varchar(255),
HHI INTEGER,
Marital varchar(255),
Children INTEGER,
HasPets varchar(255)
);"""

if "CustomerInfo" not in tables:
    print(sql)
    cur.execute(sql)

sql = "SELECT * FROM CustomerInfo"
print(sql)
cur.execute(sql)
alldata = cur.fetchall()
print(alldata)
conn.commit()

#使用pandas读取excel表并对空值填充并删除重复的并重置index
import pandas as pd
df=pd.read_excel('Major Programming HW Dataset.xlsx')
df['Gender'].fillna(" ",inplace=True) 
df['HHI'].fillna(0,inplace=True) 
df['Marital'].fillna(" ",inplace=True) 
df['Children'].fillna(0,inplace=True) 
df['HasPets'].fillna(" ",inplace=True) 
df.drop_duplicates(subset=['CustomerID'],keep='first',inplace=True)
df.reset_index(inplace=True,drop=True)
df

#遍历dataframe表insert Access
sql = "delete  FROM CustomerInfo"
print(sql)
cur.execute(sql)
for i in range(len(df)):    
    sql=f"INSERT INTO CustomerInfo(CustomerID, FName,LName,StateName,Gender,HHI,Marital,Children,HasPets) VALUES (%d,'%s' ,'%s','%s','%s',%d,'%s',%d,'%s')"%(df.loc[i,'CustomerID'],df.loc[i,'FName'],df.loc[i,'LName'].replace("'",""),df.loc[i,'StateName'],df.loc[i,'Gender'],df.loc[i,'HHI'],df.loc[i,'Marital'],df.loc[i,'Children'],df.loc[i,'HasPets'])
    print(sql)
    cur.execute(sql)
conn.commit()   

#全部代码
def f_driver():
    return [x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]
def d_driver():
    mdb_file=r'C:\\Users\\Administrator\\jupyter\\access\\Database1.accdb'
    driver = '{Microsoft Access Driver (*.mdb,*.accdb)}'
    conn = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + mdb_file + ";Uid=;Pwd=;")
    cur = conn.cursor()
    return cur
def di_table(cur):
    tables=[]
    for table_name in cur.tables(tableType="TABLE"):
        #print(table_name.table_name)
        tables.append(table_name.table_name)
    return tables

def cre_table(cur,tables):
    # 创建表的sql语句
    sql = """
    create table CustomerInfo
    (
    CustomerID AUTOINCREMENT PRIMARY KEY,
    FName varchar(255),
    LName varchar(255),
    StateName varchar(255),
    Gender varchar(255),
    HHI INTEGER,
    Marital varchar(255),
    Children INTEGER,
    HasPets varchar(255)
    );"""

    if "CustomerInfo" not in tables:
        #print(sql)
        cur.execute(sql)

    sql1 = """
    create table Customer
    (
    CustID AUTOINCREMENT PRIMARY KEY,
    FName varchar(255),
    LName varchar(255),
    StateName varchar(255),
    HHI INTEGER,
    Marital varchar(255),
    Children INTEGER,
    HasPets varchar(255)
    );"""

    if "Customer" not in tables:
        #print(sql1)
        cur.execute(sql1)

def dao_excel(cur):
    #使用pandas读取excel表并对空值填充并删除重复的并重置index
    #import pandas as pd
    df=pd.read_excel('Major Programming HW Dataset.xlsx')
    df['Gender'].fillna(" ",inplace=True) 
    df['HHI'].fillna(0,inplace=True) 
    df['Marital'].fillna(" ",inplace=True) 
    df['Children'].fillna(0,inplace=True) 
    df['HasPets'].fillna(" ",inplace=True) 
    df.drop_duplicates(subset=['CustomerID'],keep='first',inplace=True)
    df.reset_index(inplace=True,drop=True)
    #遍历dataframe表insert Access
    sql = "delete  FROM CustomerInfo"
    #print(sql)
    cur.execute(sql)
    for i in range(len(df)):    
        sql=f"INSERT INTO CustomerInfo(CustomerID, FName,LName,StateName,Gender,HHI,Marital,Children,HasPets) VALUES (%d,'%s' ,'%s','%s','%s',%d,'%s',%d,'%s')"%(df.loc[i,'CustomerID'],df.loc[i,'FName'],df.loc[i,'LName'].replace("'",""),df.loc[i,'StateName'],df.loc[i,'Gender'],df.loc[i,'HHI'],df.loc[i,'Marital'],df.loc[i,'Children'],df.loc[i,'HasPets'])
        #print(sql)
        cur.execute(sql)
    conn.commit()
    return df
def print_cut(cur):
    sql = "SELECT * FROM Customer"
    print(sql)
    cur.execute(sql)
    alldata = cur.fetchall()
    print(alldata)