How to import access table to another access table using Python
Question: 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


Answer: 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()
    

