Python读取Access数据库基本操作

接上篇《python操作Access .mdb数据库环境配置》配置好的环境，进行Access数据库基本的操作。 

---

#导入模块

import pypyodbc
import win32com.client
path=r'C:\Users\Administrator\Desktop\test.mdb'# 数据库文件

#获取单个表结构信息

con = win32com.client.Dispatch(r'ADODB.Connection')
DSN = 'PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=' + path + ';'
con.Open(DSN)
rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs.Cursorlocation = 3
rs.Open('SELECT TOP 1 * FROM table1', con)
for i in range(0, rs.Fields.Count):
    print(rs.Fields[i].Name + ' - ' + str(rs.Fields[i].Type) + ' - ' + str(rs.Fields[i].DefinedSize))

#获取所有表名称 

conn = pypyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + path + ";Uid=;Pwd=;")
cursor = conn.cursor()
SQL = "SELECT name FROM MSYSOBJECTS WHERE TYPE=1 AND NAME NOT LIKE 'Msys*'"#
for row in cursor.execute(SQL):
    print(row)

 #获取某个表数据内容

SQL_value = 'select * from admin'#
for row in cursor.execute(SQL_value):
    print(row)

#获取统计表数据 

SQL_value = 'select count(*) from admin'#
cursor.execute(SQL_value)
SUM=cursor.fetchall()[0][0]
print(SUM)

#修改表中数 

edit_SQL='update table1 set name=\'逍遥子\''
cursor.execute(edit_SQL)
cursor.commit()

#清空一列数据 

clear_SQL='update table2 set name = null'
cursor.execute(clear_SQL)
cursor.commit()

#删除表字段 

delete_field_SQL='alter table table3 drop column name'
cursor.execute(delete_field_SQL)
cursor.commit()

#增加表字段 

add_field_SQL='alter table table4 add column AAA text '
cursor.execute(add_field_SQL)
cursor.commit()

#清空表 

qingkong_SQL='delete * from table5'
cursor.execute(qingkong_SQL)
cursor.commit()
conn.close()