CODEWITHSUNDEEP
pythonms-access-2007pyodbc
waywer
waywer

Reputation: 21

How to import access table to another access table using Python

Good Morning.

I'm new to Python and I'm doing a internship at the moment. One part of the script that they want me to make is to import a table from Access database 1 to Access database 2.

I was trying to do something with the 2 following libaries: pyodbc and prettytable. Where I wanted to make a temporary table with prettytable from database 1 and get the values from that and put it in database 2. Where I was hoping that I can put a variable in the SQL. But obvious, that never work. So I'm stuck right now.

Has somebody an idea?

You can read my beautiful code that I use below here:

import pyodbc

DBfile = 'C:/Users/stage1/Documents/test.accdb'
conn = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:/Users/stage1/Documents/test.accdb;")
cursor = conn.cursor()

DBfile = 'C:/Users/stage1/Documents/test.accdb'
conn2 = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:/Users/stage1/Documents/test.accdb;")
cursor2 = conn2.cursor()

SQL = """select naam
FROM naam;"""
for row in cursor.execute(SQL):
    k = row.naam
    print k
    cursor2.execute("""insert into testtable(naam) values (?)""", (k))
    conn2.commit()

cursor.close()
conn.close()
cursor2.close()
conn2.close()

THE PROBLEM IS SOLVED. Thanks to mhawke

Upvotes: 0

Views: 1578

Answers (1)

mhawke
mhawke

Reputation: 87134

No comment on what you are doing and why. I don't grok the pretty table stuff, but you need to pass the value of k to the execute statement. Change:

cursor.execute("""insert into Tableimport(name) values (k)""")

to

cursor.execute("""insert into Tableimport(name) values (?)""", (k,))

The latter is a parameterised query where the ? placeholder will be replaced with the value of k.

Too much back and forth with comments, and the information provided in the question is not clear enough. So, you can try the following code which checks that the required tables and column exist before attempting any queries. It will display a list of tables and/or columns if anything is not as expected. This is intended to help diagnose the problem, it's not production code.\

import pyodbc

DBfile = 'C:/Users/stage1/Documents/test.accdb'
conn = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb,    *.accdb)};DBQ=C:/Users/stage1/Documents/test.accdb;")
cursor = conn.cursor()

source_table = 'naam'
dest_table = 'testtable'
column_name = 'naam'

if (cursor.columns(table=source_table, column=column_name).fetchone() and
    cursor.columns(table=dest_table, column=column_name).fetchone()):
    result = cursor.execute("SELECT naam FROM naam")
    for row in result:
        k = row.naam
        print 'Got k: {!r}'.format(k)
        cursor.execute("insert into testtable (naam) values (?)", (k,))
    conn.commit()
else:
    # tables and/or columns missing
    show_tables = show_source_columns = show_dest_columns = False

    if not cursor.tables(table=source_table).fetchone():
        print 'No source table named "{}"'.format(source_table)
        show_tables = True
    elif not cursor.columns(table=source_table, column=column_name).fetchone():
        show_source_columns = True

    if not cursor.tables(table=dest_table).fetchone():
        print 'No destination table named "{}"'.format(source_table)
        show_tables = True
    elif not cursor.columns(table=dest_table, column=column_name).fetchone():
        show_dest_columns = True

    if show_tables:
        print '\n\nAvailable tables are:'
        for row in cursor.tables():
            print row

    if show_source_columns:
        print '\n\nSource table "{}" missing column "{}". Available columns:'.format(source_table, column_name)
        for row in cursor.columns(table=source_table):
            print '{} : {}'.format(row.column_name, row.data_type)

    if show_dest_columns:
        print '\n\nDestination "{}" table missing column "{}". Available columns:'.format(dest_table, column_name)
        for row in cursor.columns(table=dest_table):
            print '{} : {}'.format(row.column_name, row.data_type)

cursor.close()
conn.close()

Upvotes: 1

Related Questions