azure 如何在python中读取.mdb access数据库文件

snz8szmq  于 2023-11-21  发布在  Python
关注(0)|答案(2)|浏览(132)

我需要在python中读取.mdb文件,该文件在azure blob存储中,并将其导出为csv,我可以读取csv,但我无法读取.mdb文件。是否有其他方法可以做到这一点,请随时给予建议,而不是python。
我试过:

from azure.storage.blob import BlockBlobService
import pandas as pd
import tables

STORAGEACCOUNTNAME= <storage_account_name>
STORAGEACCOUNTKEY= <storage_account_key>
LOCALFILENAME= <local_file_name>
CONTAINERNAME= <container_name>
BLOBNAME= <blob_name>

blob_service=BlockBlobService(account_name=STORAGEACCOUNTNAME,account_key=STORAGEACCOUNTKEY)
blob_service.get_blob_to_path(CONTAINERNAME,BLOBNAME,test.mdb)

# LOCALFILE is the file path
dataframe_blobdata = pd.read_csv(test.mdb)

字符串

pgx2nnw8

pgx2nnw81#

要从数据库中读取.mdb文件,需要第三方应用程序pyodbc,下面是从python中阅读.mdb文件的示例代码。

import csv
import pyodbc

MDB = 'c:/path/to/my.mdb'
DRV = '{Microsoft Access Driver (*.mdb)}'
PWD = 'mypassword'

conn = pyodbc.connect('DRIVER=%s;DBQ=%s;PWD=%s' % (DRV,MDB,PWD))
curs = conn.cursor()

SQL = 'SELECT * FROM mytable;' # insert your query here
curs.execute(SQL)

rows = curs.fetchall()

curs.close()
conn.close()

# you could change the 'w' to 'a' for subsequent queries
csv_writer = csv.writer(open('mytable.csv', 'w'), lineterminator='\n')

for row in rows:
    csv_writer.writerow(row)

字符串
有关详细信息,请查找相关的SO1SO2

owfi6suc

owfi6suc2#

最简单的方法是安装mdbtools(在MacOS上是brew install mdbtools),使用它们将数据库转换为sqlite数据库,通过import sqlite3从Python使用sqlite数据库要容易得多--你不会遇到试图使用pyodbc并意识到你没有MDB驱动程序,然后试图找到一个,付费,安装并使用它的问题。
我遇到了这个问题,写了一个小脚本来 Package mdbtools并将mdb数据库转换为sqlite。重要的部分如下:

def list_tables(filename):
    delimiter = ", "
    u = run_command(["mdb-tables", "-d", delimiter, filename])
    tables = u.split(delimiter)
    return [stripped for t in tables if (stripped := t.strip()) != ""]

def export_sqlite(dbname, tablenames, filename):
    print(f"creating {filename}")
    con = sqlite3.connect(filename)
    cur = con.cursor()

    # Populate it.
    create = "mdb-schema --indexes --relations --default-values --not-null".split(" ")
    for table in tablenames:
        print(f"creating table {table}")
        table_create = run_command(create + [dbname, "-T", table, "sqlite"])
        cur.execute(table_create)

        sql = run_command(["mdb-export", "-I", "sqlite", "-S", "1", dbname, table])
        for i, ins in enumerate(sql.split(";\n")):
            cur.execute(ins)

        print(f"inserted {i} records into {table}")
        con.commit()
    con.close()

def main(filename):
    tables = list_tables(filename)
    export_sqlite(filename, tables, filename + '.sqlite')

字符串
分享完整的代码here的情况下,它可以帮助别人。

相关问题