Автор: dvs
Дата сообщения: 24.11.2005 11:44
можно использовать похожий скрипт на Питоне:
(использует ODBC для работы и с Paradox и с DB2)
Код: # -*- coding: windows-1251 -*-
import mx.ODBC.Windows as odbc
import time, traceback
import sys
import psyco; psyco.full()
import ordereddict
pdx_PARADOX_DB_NAME = 'pdxdd'
conn22 = None
cursor22 = None
pdx_data = {}
def get_pk_columns(tab):
'''primary keys'''
global pdx_data
pk_columns = []
if len(pk_columns)==0: #уже заполнено
pk_columns = [
col[0]
for col in pdx_data[tab]['columns']
if col[4]==True
]
return tuple(pk_columns)
def insert_data(tab, cols, cur):
try:
sql = 'select %s from %s' % (','.join(cols), tab)
cur.execute(sql)
tab_data = cur.fetchall()
except:
pprint (traceback.print_exc())
pprint (sql)
sys.exit(444)
if len(tab_data)==0:
print "No data in source table"
return
pprint ("COLS", cols)
sql_ins = ( "Insert into pdx."+ tab.replace('-','_') + # шаблон SQLя на вставку
" (%s)" % ','.join(cols) +
' values (%s)' % ','.join('?'* len(cols) )
)
print sql_ins
try:
#db2cur.executemany(sql_ins, tab_data);
for_exc = []
for i,dat in enumerate(tab_data): #tmp, uncomment EXECUTEMANY
for_exc =dat
rstripped_dat=[]
for d in dat:
rstripped_dat.append(isinstance(d,str) and d.rstrip() or d)
db2cur.execute(sql_ins, tuple(rstripped_dat))
conn22.commit()
print "inserted %d rows \n" % len(tab_data)
except Warning:
pass
except:
print "--------------------------"
print traceback.print_exc(),'\n',for_exc
print 'for_exc:'
for i,fe in enumerate(for_exc):
print cols[i],"\t'"+str(fe)+"'"#,
conn22.close()
sys.exit(333)
def get_db2_cursor():
global cursor22
global conn22
if cursor22 is None:
conn22=get_db2_conn()
cursor22=conn22.cursor()
return cursor22
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
def get_db2_conn():
global conn22
if conn22 is None:
conn22=odbc.connect(db2_DB2_DB_NAME, db2_DB2_DB_UID, db2_DB2_DB_PASS)
return conn22
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
def get_pdx_tab_data():
global pdx_data
conn = odbc.connect(pdx_PARADOX_DB_NAME)
cur = conn.cursor()
cur.tables()
tables=cur.fetchall()
for tab in tables:
tabname = tab[2]
print "\n","="*10, tabname,"="*10
try:
pdx_data[ tabname ] = {'comment': (tab[4] is None) and ' ' or tab[4]}
pdx_data[ tabname ]['columns']=[]
cur.columns(table=tabname)
cols_ds = cur.fetchall() # 'ds' mean DataSet
cold = {}
for col in cols_ds:
cold[col[3]]=col
cols_ds2 = []
for col in cols_ds:
if col in cold.values():
cols_ds2.append(col)
cols_ds = cols_ds2
cur.primarykeys(table=tabname)
pk_ds = cur.fetchall()
cur.foreignkeys(primary_table=tabname)
fk_ds = cur.fetchall()
for col in cols_ds:
col_name=col[3]
fk_tuple=[]
for fk in fk_ds:
if fk[3]==col_name:
fk_tuple.append( (fk[5]+'.'+fk[6], fk[7]) )
pdx_data[tabname]['columns'].append((
#name
col_name,
col[11] is None and ' ' or col[11], #comment
col[5], col[6], # type len
#pk?
col_name in [pk[3] for pk in pk_ds],
#fk
tuple(fk_tuple)
))
#26.05.2004
pdx_data[ tabname ]['columns']=tuple( pdx_data[ tabname ]['columns'] )
except:
print traceback.print_exc()
sys.exit(555)
print cols_ds
print >> del_from_all, "Delete from pdx.%s;" % tabname.replace('-','_')
return pdx_data
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
def main():
global pdx_data
db2cur = get_db2_cursor()
create = ""
crea_file = open("create_pdx_table.sql","w")
global del_from_all
del_from_all = open("del_from_all_pdx_table.sql","w")
inserts = ""
pdx_data = get_pdx_tab_data()
for k,v in pdx_data.items():
print "#"*10,k,"#"*10
for kk,vv in v.items():
print kk,vv
print
create += "Create table pdx.%s (\n" % k.replace('-','_')
cols = []
pk_cols = get_pk_columns(k)
for col in v['columns']:
crea = " %s " % col[0]
sql_type = col[2]
#sql types
if sql_type in ('LONG INTEGER','AUTOINCREMENT'):
crea += "INTEGER"
elif sql_type=='ALPHA': crea += "varCHAR (%d)" %col[3]
elif sql_type=='SHORT': crea += "SMALLINT"
elif sql_type=='NUMBER': crea += "FLOAT"
elif sql_type=='DATE': crea += "DATE"
else:
print sql_type, col[5]
sys.exit()
if col[0] in pk_cols:
crea += " NOT NULL"
cols.append(crea)
create +=",\n".join(cols)
if len(pk_cols)>0:
create += "\n, primary key (%s)" % (",".join(pk_cols))
create += "); \n\n"
# PK !!
print "\n get_pk_columns(k)",get_pk_columns(k)
print "\n*************** INSERT ****************"
if len(sys.argv)>1 and sys.argv[1]=="ins" :
insert_data(k,[col[0] for col in v['columns']], cur)
db2cur.commit()
else:
print "Usage:\n python pdx.py [ins]"
print >> crea_file, create
if __name__=='__main__':
main()
