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

k is the variable with the data is from the other table. but if i try this i get the error: there are too few parameters, it is to expected number: 1 what i want is to get data from one database to another database. I figured out that this is not working because i m using sql in the script and there is no k in the database i m using now only one database but in the future it has to be two different ones. — waywer
– waywer, Commented Mar 19, 2015 at 14:21
You are getting the new error because you are clobbering the cursor object inside the loop. If you are iterating over the rows in a cursor object named cursor you'll need to use a different cursor object (e.g., one named cursor2) to perform the insert. — Gord Thompson
– Gord Thompson, Commented Mar 20, 2015 at 11:39
thanks, error is now gone. but still not working edit - the problem is solved — waywer
– waywer, Commented Mar 20, 2015 at 13:47

mhawke · Accepted Answer · 2015-03-20 13:15:48Z

1

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

edited Mar 20, 2015 at 13:15

answered Mar 19, 2015 at 14:26

mhawke

87.5k10 gold badges122 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

mhawke Over a year ago

? is the placeholder for pyodbc. Updated answer.

waywer Over a year ago

i was typing the answer why i m doing this, and realise that i want to do something what is not needed at all. So i m feeling really stupid. So thanks for the answer and took your time but i was just being really stupid.... Thanks anyway

waywer Over a year ago

and yes, i tried ? already. It still not working. Got the same error this time but the parameter is now expected 2. error: there are too few parameters, it is to expected number: 2

mhawke Over a year ago

That's very surprising. Can you post the full and complete traceback in your question (not as a comment - it's too hard to read)?

waywer Over a year ago

i put the traceback into the question.

|

Collectives™ on Stack Overflow

How to import access table to another access table using Python

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related