Skip to content

Instantly share code, notes, and snippets.

@sdshlanta
Last active June 9, 2017 16:58
Show Gist options
  • Save sdshlanta/c0966298427f7a1b22a8fa400bcdc8bb to your computer and use it in GitHub Desktop.
Save sdshlanta/c0966298427f7a1b22a8fa400bcdc8bb to your computer and use it in GitHub Desktop.
I didn't have access to a copy of Microsoft Access but I still needed to get some data out of a mde so I put together this quick SQL console for it.
import pyodbc
import sys
import argparse
import os
import subprocess as sp
if not bool(filter(lambda x: 'Microsoft Access Driver' in x, pyodbc.drivers())): #empyt list == False
print("Please install the x64 version of the Microsoft Access ODBC which can be found here: https://www.microsoft.com/en-US/download/details.aspx?id=13255")
sys.exit()
def connectToDB(databaseFile):
connectionString = (
r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
r'DBQ=%s;' % databaseFile
)
try:
dbConn = pyodbc.connect(connectionString)
except pyodbc.Error as e:
if "Unable to open registry key" in str(e):
print("%s\n\nTry running as administrator.")
elif "Not a valid file name." in str(e):
try:
connectionString = (
r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
r'DBQ=%s;' % os.path.abspath(databaseFile)
)
dbConn = pyodbc.connect(connectionString)
except Exception as e:
print("Unhandled excption:\n")
raise e
else:
raise e
return dbConn
def dumpTables(dbConn):
dbCursor = dbConn.cursor()
dbCursor2 = dbConn.cursor()
for table_info in dbCursor.tables(tableType='TABLE'):
tableName = table_info.table_name
query = 'SELECT * FROM %s' % table_info.table_name
dbCursor2.execute(query)
columns = str([column[0] for column in dbCursor2.description])[1:-1]
print('%s, %s' % (tableName, columns))
dbCursor.close()
dbCursor2.close()
def main():
# pp = PrettyPrinter(indent = 4)
sessionLog = open('session.log', 'w')
dbConn = connectToDB(args.f)
if dbConn:
print("Connected to DB")
dbCursor = dbConn.cursor()
try:
inputText = input('=> ').strip()
except EOFError:
inputText = 'exit'
while inputText != 'exit':
try:
if inputText == '':
pass
elif inputText == 'dumpTables':
dumpTables(dbConn)
elif inputText == 'clear':
sp.call('cls',shell=True)
else:
if '|' in inputText:
splitInput = inputText.split('|')
inputText = splitInput[0].strip()
countToReturn = int(splitInput[1].strip())
else:
countToReturn = 1
dbCursor.execute(inputText)
sessionLog.write('%s\n' % inputText)
for row in dbCursor.fetchmany(int(countToReturn)):
print(row)
sessionLog.write('%s\n' % str(row))
sys.stdout.write('\n')
except Exception as e:
print(str(e) + '\n')
try:
inputText = input('=> ').strip()
except EOFError:
inputText = 'exit'
dbCursor.close()
dbConn.close()
sessionLog.close()
if __name__ == '__main__':
parser = argparse.ArgumentParser(description="SQL console for access databases.")
parser.add_argument('-f', type=str, default='database.mde', help='The name of the database file..')
args = parser.parse_args()
if os.path.exists(args.f):
main()
else:
print("File does not Exist.")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment