《Super Auto Pets》六本食物一览，

「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.代码

#导入pyodbcimport pyodbcmdb_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()#导入pypyodbcimport pypyodbcmdb = '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文件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表并对空值填充并删除重复的并重置indeximport pandas as pddf=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 Accesssql = "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,df.loc,df.loc.replace("'",""),df.loc,df.loc,df.loc,df.loc,df.loc,df.loc)    print(sql)    cur.execute(sql)conn.commit()   

#全部代码def f_driver():    return 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 curdef di_table(cur):    tables=<>    for table_name in cur.tables(tableType="TABLE"):        #print(table_name.table_name)        tables.append(table_name.table_name)    return tablesdef 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,df.loc,df.loc.replace("'",""),df.loc,df.loc,df.loc,df.loc,df.loc,df.loc)        #print(sql)        cur.execute(sql)    conn.commit()    return dfdef print_cut(cur):    sql = "SELECT * FROM Customer"    print(sql)    cur.execute(sql)    alldata = cur.fetchall()    print(alldata)