Python and Access

Question

Please help, my class does not connect in Access at all.. I’m using: Pycharm, win10 (x64), office 2007 and 2016, python 3.6(x86). Thank you for your attention. follows example of the class:

class Banco(object):
def __init__(self):
    conStr = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=D:\BDFone\AgMesaIB.mdb"
    conxn = pyodbc.connect(conStr)
    self.cursor = conxn.cursor()


class Cadastro(object):

def __init__(self, controle="", nome="", nome2="", email="", telefone=""):
    self.info = {}
    self.cod = controle
    self.nome = nome
    self.nome2 = nome2
    self.email = email
    self.telefone = telefone

def selectCad(self, pnome):

    banco = Banco()
    try:
        c = banco.cursor()
        c.execute("select * from Geral WHERE Primeiro_nome='" + pnome + "'")

        for linha in c:
            self.cod = linha[0]
            self.nome = linha[1]
            self.nome2 = linha[2]
            self.email = linha[3]
            self.telefone = linha[4]
        c.close()

        return "Busca feita com sucesso!"
    except:
        return "Ocorreu um erro na busca do Cadastro"

Answer 1

Start by checking if you have the Access driver installed:

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""Verificando se o driver está instalado"""
import pyodbc

print([x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')])

The above print should return something similar to:

['Microsoft Access Driver (*.mdb, *.accdb)']

If an empty list is returned [] you will have to install Diver:

Driver Access 2010:

https://www.microsoft.com/en-US/download/details.aspx?id=13255

Driver Access 2016:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

Remembering that:

• *.accdb: Format used by Access 2007 onwards.
• *.mdb: Format used by Access 97, Access 2000, Access 2002 or Access 2003.

Archives *.mdb may have limited resources and may not function properly.

The code below has been tested with a file *.accdb and driver 2016:

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""Conectando no banco do MS Access"""
import pyodbc


class ConectarDB:
    def __init__(self):
        # Criando conexão.
        self.con = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
                                  r'DBQ=C:\Caminho\para\o\banco.accdb;')
        # Criando cursor.
        self.cur = self.con.cursor()

    def exibir_tabelas(self):
        for tabela in self.cur.tables(tableType='TABLE'):
            print(tabela.table_name)

    def consultar_registros(self):
        return self.cur.execute('SELECT * FROM NomeDaTabela').fetchall()


if __name__ == '__main__':
    banco = ConectarDB()
    banco.exibir_tabelas()
    # print(banco.consultar_registros())

Answer 2

I don’t know much about Acces or python, but I managed to get my code (simple) to connect to Sqlite3, creating its tables and columns. I don’t know if it’ll help, but just in case... Code is down:

import sqlite3

import time import datetime

Connection = sqlite3.connect('IMDC_DB.db') c = Connection.cursor()

def create_table(): c.execute('CREATE TABLE IF NOT EXISTS data_students (name text, integer age, text,materia text, Cpf text, email text, data text)')

create_table()

def dataentry():

c.execute('INSERT INTO dados_alunos (name, idade, numero, materia, cpf, email, data)VALUES \
          (?,?,?,?,?,?,?)', (nome_aluno, idade_aluno, numero_aluno,\
                             materia_aluno, cpf_aluno, email_aluno, date))

connection.commit()

print("Welcome(a) to the first IMDC System test using Database!") op_menu_student = 1 while op_menu_student != 0: print("==========") print("IMDC MENU") print("==========") print("[1] REGISTER STUDENT") print("[0] EXIT") op_menu_student = int(input())

if op_menu_aluno == 1:
    nome_aluno = input("Digite o nome do aluno(a): ")
    idade_aluno = int(input("Digite a idade do aluno(a): "))
    numero_aluno = input("Digite o telefone do aluno(a): ")
    materia_aluno = input("Digite a(s) matérias(s) que o aluno quer se matricular: ")
    cpf_aluno = input("Digite o CPF do aluno(a): ")
    email_aluno = input("Digite o Email do aluno(a): ")
    date = str(datetime.datetime.fromtimestamp(int(time.time())).strftime('%Y-%m-%d %H:%M:%S'))
    dataentry()
elif op_menu_aluno == 0:
    print("Tchau")
else:
    print("Opção inválida")