Copy all records from one table to another

I wrote a program that, using a handle (a database containing the structure of the Northwind database - in separate tables, containing a list of tables, indexes, restrictions, domains) of the Northwind database builds it in Firebird. Then I connect to the Access database and want to copy the contents of the Access Database tables into the tables of the identical Firebird database using the sql query.

import pyodbc #import kinterbasdb import firebirdsql import os import json,sqlite3,sql con = sqlite3.connect('borey.db') sampling_tables=con.cursor() sampling_fields=con.cursor() sampling_constraints=con.cursor() sampling_constraints_fields=con.cursor() sampling_indices=con.cursor() sampling_indices_fields=con.cursor() sampling_indices_props=con.cursor() sampling_fields_from_constraints=con.cursor() js=[] sampling_tables.execute(sql.sqltables) for table in sampling_tables: tabledict=dict(inform_about_table='?', fields='?', constraints='?', indices='?') information_about_table_dict=dict(name=table[1], description=table[2], can_add=table[3], can_edit=table[4], can_delete=table[5]) #print(js_information_about_table) tabledict['inform_about_table']=information_about_table_dict #print(tabledict) table_id=table[0] sampling_fields.execute(sql.sqlfields,(table_id,)) listfields=[] for field in sampling_fields: fielddict=dict(position=field[0], name=field[1], description=field[2], datatype=field[3], char_length=field[4], can_input=field[5], can_edit=field[6], show_in_grid=field[7], is_mean=field[8], autocalculated=field[9], required=field[10]) listfields.append(fielddict) tabledict['fields']=listfields #print(tabledict) sampling_constraints.execute(sql.sqlconstraints,(table_id,)) listconstraints=[] for constraint in sampling_constraints: constraintdict=dict(name=constraint[1], constrtype=constraint[2], items='?', reference=constraint[3]) sampling_constraints_fields.execute(sql.sql_constr_det,(constraint[0],table_id)) listitems=[] for constr_det in sampling_constraints_fields: listitems.append(constr_det[0]) constraintdict['items']=listitems listconstraints.append(constraintdict) tabledict['constraints']=listconstraints #print(tabledict) sampling_indices.execute(sql.sqlindices,(table_id,)) listindices=[] for index in sampling_indices: indexdict=dict(name=index[1], items='?', position='?', props='?') index_id=index[0] sampling_indices_fields.execute(sql.sql_indices_fields,(table_id,index_id)) #sampling_indices_props.execute(sql.sql_indices_descend,(table_id,index_id, )) listfields=[] listpositions=[] listprops=[] for index_det in sampling_indices_fields: listfields.append(index_det[0]) listpositions.append(index_det[1]) fieldinindex=index_det[0] sampling_indices_props.execute(sql.sql_indices_descend,(table_id,index_id,fieldinindex)) for index_desc in sampling_indices_props: listprops.append(index_desc[0]) indexdict['items']=listfields indexdict['position']=listpositions indexdict['props']=listprops listindices.append(indexdict) tabledict['indices']=listindices #print(tabledict) js.append(tabledict) os.remove('D:/boreas.fdb') conn=firebirdsql.create_database(host='localhost', database='D:/boreas.fdb', user='sysdba', password='masterkey') #conn=firebirdsql.connect(host='localhost',database='D:/ThirdTask/test.fdb', user='sysdba', password='masterkey') cur=conn.cursor() for nametable in js: s='' s+='create table '+nametable['inform_about_table']['name']+'\n' s+='('+'\n' countfield=0 for namefield in nametable['fields']: s+=' ' if namefield['name']=='COUNT': s+='QUANTITY' else: if namefield['name']=='MINIMUM QUANTITY FOR ORDER REPETITION': s+='MINIMUM_QUANTITY' else: if namefield['name']=='PRICE ACCORDING TO THE PRICE-LIST': s+='LIST_PRICE' else: if (namefield['name'].count(' ')!=0) or (namefield['name'].count('-')!=0) or (namefield['name'].count('/')!=0): s+='"'+namefield['name']+'"' else: s+=namefield['name'] if namefield['datatype']=='FMTBCD': s+=' '+' INTEGER NOT NULL' else: if namefield['datatype']=='STRING': s+=' '+'VARCHAR' else: if namefield['datatype']=='MEMO': s+=' '+'BLOB SUB_TYPE 1' else: if namefield['datatype']=='LARGEINT': s+=' '+'INTEGER' else: if namefield['datatype']=='SMALLINT': s+=' '+'INTEGER' else: if namefield['datatype']=='CURRENCY': s+=' '+'NUMERIC(18,2)' else: if namefield['datatype']=='BOOLEAN': s+=' '+'INTEGER' else: s+=' '+namefield['datatype'] if namefield['datatype']=='STRING': s+='('+str(namefield['char_length'])+')' countfield+=1 #if countfield!=len(nametable['fields']): s+=','+'\n' #else: #s+='\n' #s+='\n' for nameconstraint in nametable['constraints']: if nameconstraint['constrtype']=='PRIMARY': s+=' CONSTRAINT '+nameconstraint['name']+ ' PRIMARY KEY(' countfieldinconstr=0 for namefield in nameconstraint['items']: countfieldinconstr+=1 if countfieldinconstr!=len(nameconstraint['items']): if (namefield.count(' ')!=0) or (namefield.count('-')!=0) or (namefield.count('/')!=0): s+='"'+namefield+'"'+',' else: if namefield=='COUNT': s+='QUANTITY'+',' else: s+=namefield+',' else: if (namefield.count(' ')!=0) or (namefield.count('-')!=0) or (namefield.count('/')!=0): s+='"'+namefield+'"'+')'+'\n' else: if namefield=='COUNT': s+='QUANTITY'+')'+'\n' else: s+=namefield+')'+'\n' s+=');'+'\n' print s cur.execute(s) #conn.commit() #conn.begin() for nameindex in nametable['indices']: #print 'nameidex[props] ', nameindex['props'] s='' for prop in nameindex['props']: if prop==0: if (nameindex['name'].count(' ')!=0) or (nameindex['name'].count('-')!=0) or (nameindex['name'].count('/')!=0): s+='create ascending index "'+nameindex['name'] + '" on '+nametable['inform_about_table']['name']+' (' else: if nameindex['name']=='IDX_INFORMATION_ABOUT_ORDER_INVENTORY_ID': s+='create ascending index ' + 'IDX_INVENTORY_ID' + ' on '+nametable['inform_about_table']['name']+' (' else: if nameindex['name']=='IDX_INFORMATION_ABOUT_ORDER_ORDERDETAILS': s+='create ascending index ' + 'IDX_ORDERDETAILS' + ' on '+nametable['inform_about_table']['name']+' (' else: if nameindex['name'].count('INFORMATION_ABOUT_ORDER_')!=0: s+='create ascending index ' + nameindex['name'][1:4]+nameindex['name'][-len(nameindex['name'])+28:] + ' on '+nametable['inform_about_table']['name']+' (' else: s+='create ascending index '+nameindex['name'] + ' on '+nametable['inform_about_table']['name']+' (' countitem=0 for nameitem in nameindex['items']: if countitem!=len(nameindex['items'])-1: if (nameitem.count(' ')!=0) or (nameitem.count('-')!=0) or (nameitem.count('/')!=0): s+='"'+nameitem+'"'+', ' else: if nameitem=='COUNT': s+='QUANTITY'+', ' else: s+=nameitem+', ' else: if (nameitem.count(' ')!=0) or (nameitem.count('-')!=0) or (nameitem.count('/')!=0): s+='"'+nameitem+'"'+');'+'\n' else: if nameitem=='COUNT': s+='QUANTITY'+');'+'\n' else: s+=nameitem+');'+'\n' countitem+=1 print s cur.execute(s) #conn.commit() for nametable in js: for nameconstraint in nametable['constraints']: if nameconstraint['constrtype']=='FOREIGN': s='' s+='ALTER TABLE '+nametable['inform_about_table']['name']+'\n' s+=' '+'ADD CONSTRAINT '+nameconstraint['name']+' FOREIGN KEY (' for namefield in nameconstraint['items']: if (namefield.count(' ')!=0) or (namefield.count('-')!=0) or (namefield.count('/')!=0): s+='"'+namefield+'")'+'\n' else: s+=namefield+')'+'\n' s+=' '+'REFERENCES '+nameconstraint['reference'] sampling_fields_from_constraints.execute(sql.sql_fields, (nameconstraint['reference'],)) for fields in sampling_fields_from_constraints: field=fields[0] if (field.count(' ')!=0) or (field.count('-')!=0) or (field.count('/')!=0): s+=' ("'+field+'");'+'\n' else: s+=' ('+field+');'+'\n' print s cur.execute(s) #conn.commit() conn.commit() s=""" create table CLIENTS ( ID INTEGER NOT NULL, COMPANY VARCHAR(50), "LAST NAME" VARCHAR(50), "FIRST NAME" VARCHAR(50), "E-MAIL ADDRESS" VARCHAR(50), "JOB TITLE" VARCHAR(50), "BUSINESS PHONE" VARCHAR(25), "HOME PHONE" VARCHAR(25), "MOBILE PHONE" VARCHAR(25), "FAX NUMBER" VARCHAR(25), ADDRESS BLOB SUB_TYPE 1, CITY VARCHAR(50), "STATE/PROVINCE" VARCHAR(50), "ZIP/POSTAL CODE" VARCHAR(15), "COUNTRY/REGION" VARCHAR(50), "WEB-SITE" VARCHAR(25), NOTES BLOB SUB_TYPE 1, INCLUDING BLOB ); """ s1='create ascending index IDX_CLIENTS_CITY on CLIENTS (CITY);' """db = 'D:/ThirdTask/Northwind.accdb' connaccess = win32com.client.Dispatch(r'ADODB.Connection') DSN = ('PROVIDER = Microsoft.Jet.OLEDB.4.0;DATA SOURCE = ' + db + ';') connaccess.Open(DSN) rs = win32com.client.Dispatch(r'ADODB.Recordset') strsql = "select * from deer" rs.Open(strsql, conn, 1, 3) t = rs.GetRows() connaccess.Close() """ conAcc = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:\ThirdTask\Northwind.accdb') SqlAccess=conAcc.cursor(); #print s #cur.execute(s) #conn.commit() #cur.execute(s1) #conn.commit() #cur=con.cursor() con.close() conn.close() conAcc.close(); #print "Connect succesfully" 

How, for example, copy the contents of the CLIENTS table from Northwind.accdb to the CLIENTS table from boreas.fdb?