I wrote the following code by data transfer from Access to Firebird
def FirebirdDatetime(dt):
return '\'%s.%s.%s\'' % (str(dt.day).rjust(2,'0'),
str(dt.month).rjust(2,'0'),
str(dt.year).rjust(4,'0'))
def SelectFromAccessTable(tablename):
return 'select * from [' + tablename+']'
def InsertToFirebirdTable(tablename, row):
values=''
i=0
for elem in row:
i+=1
#print type(elem)
if type(elem) == int:
temp = str(elem)
elif (type(elem) == str) or (type(elem)==unicode):
temp = '\'%s\'' % (elem,)
elif type(elem) == datetime.datetime:
temp =FirebirdDatetime(elem)
elif type(elem) == decimal.Decimal:
temp = str(elem)
elif elem==None:
temp='null'
if (i<len(row)):
values+=temp+', '
else:
values+=temp
return 'insert into '+tablename+' values ('+values+')'
def AccessToFirebird(accesstablename, firebirdtablename, accesscursor, firebirdcursor):
SelectSql=SelectFromAccessTable(accesstablename)
for row in accesscursor.execute(SelectSql):
InsertSql=InsertToFirebirdTable(firebirdtablename, row)
InsertSql=InsertSql
print InsertSql
firebirdcursor.execute(InsertSql)
In the main module there is an AccessToFirebird function call
conAcc = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:\ThirdTask\Northwind.accdb')
SqlAccess=conAcc.cursor();
conn.begin()
cur=conn.cursor()
sql.AccessToFirebird('Customers', 'CLIENTS', SqlAccess, cur)
conn.commit()
conn.begin()
cur=conn.cursor()
sql.AccessToFirebird('??????????', 'EMPLOYEES', SqlAccess, cur)
sql.AccessToFirebird('????', 'ROLES', SqlAccess, cur)
sql.AccessToFirebird('???? ???????????', 'EMPLOYEES_ROLES', SqlAccess, cur)
sql.AccessToFirebird('????????', 'DELIVERY', SqlAccess, cur)
sql.AccessToFirebird('??????????', 'SUPPLIERS', SqlAccess, cur)
sql.AccessToFirebird('????????? ?????? ???????', 'TAX_STATUS_OF_ORDERS', SqlAccess, cur)
sql.AccessToFirebird('????????? ???????? ? ??????', 'STATE_ORDER_DETAILS', SqlAccess, cur)
sql.AccessToFirebird('????????? ???????', 'CONDITION_OF_ORDERS', SqlAccess, cur)
sql.AccessToFirebird('??????', 'ORDERS', SqlAccess, cur)
sql.AccessToFirebird('?????', 'BILLS', SqlAccess, cur)
sql.AccessToFirebird('????????? ?????? ?? ????????????', 'STATUS_PURCHASE_ORDER', SqlAccess, cur)
sql.AccessToFirebird('?????? ?? ????????????', 'ORDERS_FOR_ACQUISITION', SqlAccess, cur)
sql.AccessToFirebird('???????? ? ?????? ?? ????????????', 'INFORMPURCHASEORDER', SqlAccess, cur)
sql.AccessToFirebird('??????', 'PRODUCTS', SqlAccess, cur)
conn.commit()
conAcc.commit()
conn.close()
conAcc.close()
But as a result, not all records have been inserted into the table Products (Table
Goods - Northwind database), for example, does not work request
insert into PRODUCTS values ('4', 1, 'NWTB-1', '?????????? ???', null, 13.5000, 18.0000, 10, 40, '10 ??????? ?? 20 ?????????', '10 ??????? ?? 20 ?????????', 10, '???????', '')
In ibexpert to this request message issued
can't format message 13:587 -- message file C:\Windows\firebird.msg not found.
conversion error from string "10 ?????????±???? ???? 20 ???°???µ?‚????????".
Worked only requests
insert into PRODUCTS values ('1', 82, 'NWTC-82', '???????', null, 2.0000, 4.0000, 20, 100, null, null, null, '????', '')
insert into PRODUCTS values ('9', 83, 'NWTCS-83', '???????????? ?????', null, 0.5000, 1.8000, 30, 200, null, null, null, '????? ? ???????', '')
insert into PRODUCTS values ('1', 97, 'NWTC-82', '???????', null, 3.0000, 5.0000, 50, 200, null, null, null, '????', '')
insert into PRODUCTS values ('6', 98, 'NWTSO-98', '??????? ???', null, 1.0000, 1.8900, 100, 200, null, null, null, '????', '')
insert into PRODUCTS values ('6', 99, 'NWTSO-99', '??????? ??????', null, 1.0000, 1.9500, 100, 200, null, null, null, '????', '')
other records were not inserted.
