Ошибка в INSERT

297 просмотра
0
0 Комментариев

Привожу код программы

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();
SqlAccess.execute(sql.sql_count_records_clients);
CountOfRecords=SqlAccess.fetchone()[0];
print CountOfRecords
id=0
while id<CountOfRecords:
    id+=1
    SqlAccess.execute(sql.sql_allfields_clients, (id,))
    clientdict=dict(ident=id, organization=SqlAccess.fetchone()[1]);
    print clientdict
    s='INSERT INTO CLIENTS (ID, COMPANY) VALUES'
    s+='('+str(clientdict['ident'])+', "' +clientdict['organization']+'")';
    print s
    cur.execute(s)
conn.commit()
#print s
#cur.execute(s)
#conn.commit()
#cur.execute(s1)
#conn.commit()
#cur=con.cursor()
con.close()
conn.close()
conAcc.close();
#print "Connect succesfully"

В результате выполнения выдает ошибку

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,
   CONSTRAINT PK_CLIENTS_ID PRIMARY KEY(ID)
);
 
create ascending index IDX_CLIENTS_CITY on CLIENTS (CITY);
 
create ascending index IDX_CLIENTS_COMPANY on CLIENTS (COMPANY);
 
create ascending index IDX_CLIENTS_FIRST_NAME on CLIENTS ("FIRST NAME");
 
create ascending index IDX_CLIENTS_LAST_NAME on CLIENTS ("LAST NAME");
 
create ascending index IDX_CLIENTS_POSTAL_CODE on CLIENTS ("ZIP/POSTAL CODE");
 
create ascending index IDX_CLIENTS_PRIMARYKEY on CLIENTS (ID);
 
create ascending index "IDX_CLIENTS_STATE/PROVINCE" on CLIENTS ("STATE/PROVINCE");
 
create table EMPLOYEES
(
   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,
   CONSTRAINT PK_EMPLOYEES_ID PRIMARY KEY(ID)
);
 
create ascending index IDX_EMPLOYEES_CITY on EMPLOYEES (CITY);
 
create ascending index IDX_EMPLOYEES_COMPANY on EMPLOYEES (COMPANY);
 
create ascending index IDX_EMPLOYEES_FIRST_NAME on EMPLOYEES ("FIRST NAME");
 
create ascending index IDX_EMPLOYEES_LAST_NAME on EMPLOYEES ("LAST NAME");
 
create ascending index IDX_EMPLOYEES_POSTAL_CODE on EMPLOYEES ("ZIP/POSTAL CODE");
 
create ascending index IDX_EMPLOYEES_PRIMARYKEY on EMPLOYEES (ID);
 
create ascending index "IDX_EMPLOYEES_STATE/PROVINCE" on EMPLOYEES ("STATE/PROVINCE");
 
create table ORDERS
(
   ORDERID    INTEGER NOT NULL,
   EMPLOYEEID   INTEGER,
   CLIENTID   INTEGER,
   "PLACEMENT DATE"   DATE,
   "SHIPMENT DATE"   DATE,
   "CONSIGNOR ID"   INTEGER,
   RECEIVER   VARCHAR(50),
   "RECEIVER ADDRESS"   BLOB SUB_TYPE 1,
   "RECEIVER CITY"   VARCHAR(50),
   "RECEIVER STATE"   VARCHAR(50),
   "DELIVERY INDEX"   VARCHAR(50),
   "DELIVERY COUNTRY/REGION"   VARCHAR(40),
   "DELIVERY COST"   NUMERIC(18,2),
   TAXES   NUMERIC(18,2),
   "PAYMENT TYPE"   VARCHAR(30),
   "PAYMENT DATE"   DATE,
   NOTES   BLOB SUB_TYPE 1,
   "TAX RATE"   FLOAT,
   "TAX STATUS"   INTEGER,
   "ID STATUS"   INTEGER,
   CONSTRAINT PK_ORDERS_ID PRIMARY KEY(ORDERID)
);
 
create ascending index IDX_ORDERS_CUSTOMERID on ORDERS (CLIENTID);
 
create ascending index IDX_ORDERS_CUSTOMERONORDERS on ORDERS (CLIENTID);
 
create ascending index IDX_ORDERS_EMPLOYEEID on ORDERS (EMPLOYEEID);
 
create ascending index IDX_ORDERS_EMPLOYEESONORDERS on ORDERS (EMPLOYEEID);
 
create ascending index IDX_ORDERS_ORDERSID on ORDERS (ORDERID);
 
create ascending index IDX_ORDERS_ORDERSTATUS on ORDERS ("ID STATUS");
 
create ascending index IDX_ORDERS_PRIMARYKEY on ORDERS (ORDERID);
 
create ascending index IDX_ORDERS_SHIPPERID on ORDERS ("CONSIGNOR ID");
 
create ascending index IDX_ORDERS_SHIPPERONORDER on ORDERS ("CONSIGNOR ID");
 
create ascending index IDX_ORDERS_STATUS_ID on ORDERS ("ID STATUS");
 
create ascending index IDX_ORDERS_TAXSTATUSONORDERS on ORDERS ("TAX STATUS");
 
create ascending index IDX_ORDERS_ZIP_POSTALCODE on ORDERS ("DELIVERY INDEX");
 
create table INFORMATION_ABOUT_ORDER
(
   ID    INTEGER NOT NULL,
   ORDERID   INTEGER,
   PRODUCTID   INTEGER,
   QUANTITY   FLOAT,
   PRICEFORUNIT   NUMERIC(18,2),
   DISCOUNT   FLOAT,
   "ID STATUS"   INTEGER,
   "PLACEMENT DATE"   DATE,
   "ORDERID FOR ACQUISITION"   INTEGER,
   "ACCESSION NUMBER"   INTEGER,
   CONSTRAINT PK_INFORMATION_ABOUT_ORDER_ID PRIMARY KEY(ID)
);
 
create ascending index DX_ID on INFORMATION_ABOUT_ORDER (ID);
 
create ascending index IDX_INVENTORY_ID on INFORMATION_ABOUT_ORDER ("ACCESSION NUMBER");
 
create ascending index IDX_ORDERDETAILS on INFORMATION_ABOUT_ORDER (ORDERID);
 
create ascending index DX_ORDERID on INFORMATION_ABOUT_ORDER (ORDERID);
 
create ascending index DX_ORDERSTATUSLOOKUP on INFORMATION_ABOUT_ORDER ("ID STATUS");
 
create ascending index DX_PRIMARYKEY on INFORMATION_ABOUT_ORDER (ID);
 
create ascending index DX_PRODUCTID on INFORMATION_ABOUT_ORDER (PRODUCTID);
 
create ascending index DX_PRODUCTONORDERS on INFORMATION_ABOUT_ORDER (PRODUCTID);
 
create ascending index DX_PURCHASE_ORDER_ID on INFORMATION_ABOUT_ORDER ("ORDERID FOR ACQUISITION");
 
create ascending index DX_STATUS_ID on INFORMATION_ABOUT_ORDER ("ID STATUS");
 
create table PRODUCTS
(
   "IDS SUPPLIERS"   INTEGER,
   ID    INTEGER NOT NULL,
   "PRODUCT CODE"   VARCHAR(25),
   NAME   VARCHAR(50),
   DESCRIPTION   BLOB SUB_TYPE 1,
   "STANDARD COST"   NUMERIC(18,2),
   LIST_PRICE   NUMERIC(18,2),
   "MINIMUM STOCK"   INTEGER,
   "DESIRABLE STOCK"   INTEGER,
   "QUANTITY IN A POSITION"   VARCHAR(50),
   "DELIVERIES ARE STOPPED"   INTEGER,
   MINIMUM_QUANTITY   INTEGER,
   CATEGORY   VARCHAR(50),
   INCLUDING   BLOB,
   CONSTRAINT PK_PRODUCTS_ID PRIMARY KEY(ID)
);
 
create ascending index IDX_PRODUCTS_PRIMARYKEY on PRODUCTS (ID);
 
create ascending index IDX_PRODUCTS_PRODUCT_CODE on PRODUCTS ("PRODUCT CODE");
 
create table EMPLOYEES_ROLES
(
   EMPLOYEEID    INTEGER NOT NULL,
   ROLEID    INTEGER NOT NULL,
   CONSTRAINT PK_EMPLOYEES_ROLES_ID PRIMARY KEY(EMPLOYEEID,ROLEID)
);
 
create ascending index IDX_PRIVILIGES on EMPLOYEES_ROLES (EMPLOYEEID);
 
create ascending index IDX_PRIVILIGESLOOKUP on EMPLOYEES_ROLES (ROLEID);
 
create ascending index IDX_EMPLOYEES_ROLES_PRIMARYKEY on EMPLOYEES_ROLES (EMPLOYEEID, ROLEID);
 
create ascending index IDX_PRIVILEGE_ID on EMPLOYEES_ROLES (ROLEID);
 
create table ROLES
(
   ROLEID    INTEGER NOT NULL,
   "ROLE NAME"   VARCHAR(50),
   CONSTRAINT PK_ROLES_ID PRIMARY KEY(ROLEID)
);
 
create ascending index IDX_ROLES_PRIMARYKEY on ROLES (ROLEID);
 
create table ORDERS_FOR_ACQUISITION
(
   "ORDERID FOR ACQUISITION"    INTEGER NOT NULL,
   SUPPLIERID   INTEGER,
   CREATED   INTEGER,
   "SENDING DATE"   DATE,
   "CREATION DATE"   DATE,
   "ID STATUS"   INTEGER,
   "SETTLEMENT DATE"   DATE,
   "DELIVERY COST"   NUMERIC(18,2),
   TAXES   NUMERIC(18,2),
   "MATURITY DATE"   DATE,
   AMOUNT   NUMERIC(18,2),
   "PAYMENT FORM"   VARCHAR(30),
   NOTES   BLOB SUB_TYPE 1,
   APPROVED   INTEGER,
   "STATEMENT DATE"   DATE,
   SENDED   INTEGER,
   CONSTRAINT PK_ORDERS_FOR_ACQUISITION PRIMARY KEY("ORDERID FOR ACQUISITION")
);
 
create ascending index IDX_EMPLOYEESONPURCHASEORDER on ORDERS_FOR_ACQUISITION (CREATED);
 
create ascending index IDX_ORDERS_FOR_ACQUISITION_ID on ORDERS_FOR_ACQUISITION ("ORDERID FOR ACQUISITION");
 
create ascending index IDX_PURCHASE_ORDERS_PRIMARYKEY on ORDERS_FOR_ACQUISITION ("ORDERID FOR ACQUISITION");
 
create ascending index IDX_PURCHASEORDERSTATUSLOOKUP on ORDERS_FOR_ACQUISITION ("ID STATUS");
 
create ascending index IDX_STATUS_ID on ORDERS_FOR_ACQUISITION ("ID STATUS");
 
create ascending index IDX_SUPPLIER_ID on ORDERS_FOR_ACQUISITION (SUPPLIERID);
 
create ascending index IDX_SUPPLIERSONPURCHASEORDER on ORDERS_FOR_ACQUISITION (SUPPLIERID);
 
create table OPERATIONS_WITH_STOCKS
(
   OPERATIONID    INTEGER NOT NULL,
   "OPERATION TYPE"   INTEGER,
   "CREATION DATE OF OPERATION"   DATE,
   "CHANGE DATE OF OPERATION"   DATE,
   PRODUCTID   INTEGER,
   AMOUNT   INTEGER,
   "ORDERID FOR ACQUISITION"   INTEGER,
   "ORDERID OF CLIENT"   INTEGER,
   NOTES   VARCHAR(255),
   CONSTRAINT PK_OPERATIONS_WITH_STOCKS PRIMARY KEY(OPERATIONID)
);
 
create ascending index IDX_CUSTOMER_ORDER_ID on OPERATIONS_WITH_STOCKS ("ORDERID OF CLIENT");
 
create ascending index IDX_ONINVENTORYTRANSACTIONS on OPERATIONS_WITH_STOCKS ("ORDERID OF CLIENT");
 
create ascending index IDX_RESERVE_PRIMARYKEY on OPERATIONS_WITH_STOCKS (OPERATIONID);
 
create ascending index IDX_RESERVE_PRODUCTID on OPERATIONS_WITH_STOCKS (PRODUCTID);
 
create ascending index IDX_INVENTORYTRANSACTION on OPERATIONS_WITH_STOCKS (PRODUCTID);
 
create ascending index IDX_PUCHASEORDERSTRANSACTIONS on OPERATIONS_WITH_STOCKS ("ORDERID FOR ACQUISITION");
 
create ascending index IDX_PURCHASE_ORDER_ID on OPERATIONS_WITH_STOCKS ("ORDERID FOR ACQUISITION");
 
create ascending index IDX_RESERVE_TRANSACTIONTYPES on OPERATIONS_WITH_STOCKS ("OPERATION TYPE");
 
create table INFORMPURCHASEORDER
(
   ID    INTEGER NOT NULL,
   "ORDERID FOR ACQUISITION"   INTEGER,
   PRODUCTID   INTEGER,
   AMOUNT   FLOAT,
   "UNIT COST"   NUMERIC(18,2),
   "DATE OF RECEIPT"   DATE,
   "SENT ON WAREHOUSE"   INTEGER,
   "ACCESSION NUMBER"   INTEGER,
   CONSTRAINT PK_DATA_ON_THE_ORDER PRIMARY KEY(ID)
);
 
create ascending index IDX_INFORMPURCHASEORDER_ID on INFORMPURCHASEORDER (ID);
 
create ascending index IDX__INVENTORY_ID on INFORMPURCHASEORDER ("ACCESSION NUMBER");
 
create ascending index IDX_TRANSACTIONSONORDERS on INFORMPURCHASEORDER ("ACCESSION NUMBER");
 
create ascending index IDX_ORDERID on INFORMPURCHASEORDER ("ORDERID FOR ACQUISITION");
 
create ascending index IDX_INFORMABOUTORDER_PRIMARYKEY on INFORMPURCHASEORDER (ID);
 
create ascending index IDX_INFORMABOUTORDER_PRODUCTID on INFORMPURCHASEORDER (PRODUCTID);
 
create ascending index IDX_PRODUCTONORDERDETAILS on INFORMPURCHASEORDER (PRODUCTID);
 
create ascending index IDX_ORDERDEATILSONPURCHASEORDER on INFORMPURCHASEORDER ("ORDERID FOR ACQUISITION");
 
create table BILLS
(
   BILLID    INTEGER NOT NULL,
   ORDERID   INTEGER,
   "BILL DATE"   DATE,
   PERIOD   DATE,
   TAX   NUMERIC(18,2),
   DELIVERY   NUMERIC(18,2),
   RESTS   NUMERIC(18,2),
   CONSTRAINT PK_BILLS_BILLID PRIMARY KEY(BILLID)
);
 
create ascending index IDX_BILLS_ORDERID on BILLS (ORDERID);
 
create ascending index IDX_BILLS_ORDERINVOICE on BILLS (ORDERID);
 
create ascending index IDX_BILLS_PRIMARYKEY on BILLS (BILLID);
 
create table CONDITION_OF_ORDERS
(
   STATUSID    INTEGER NOT NULL,
   STATUSNAME   VARCHAR(50),
   CONSTRAINT PK_CONDITION_OF_ORDERS_ID PRIMARY KEY(STATUSID)
);
 
create ascending index IDX_ORDER_STATUS_PRIMARYKEY on CONDITION_OF_ORDERS (STATUSID);
 
create table STATE_ORDER_DETAILS
(
   STATUSID    INTEGER NOT NULL,
   STATUSNAME   VARCHAR(50),
   CONSTRAINT PK_STATUS_ORDER_DETAILS PRIMARY KEY(STATUSID)
);
 
create ascending index IDX_STATUS_ORDER_DETAILS_PK on STATE_ORDER_DETAILS (STATUSID);
 
create table STATUS_PURCHASE_ORDER
(
   STATUSID    INTEGER NOT NULL,
   STATUS   VARCHAR(50),
   CONSTRAINT PK_STATUS_PURCHASE_ORDER PRIMARY KEY(STATUSID)
);
 
create ascending index IDX_STATUS_PURCHASE_ORDER_PK on STATUS_PURCHASE_ORDER (STATUSID);
 
create table SUPPLIERS
(
   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(35),
   NOTES   BLOB SUB_TYPE 1,
   INCLUDING   BLOB,
   CONSTRAINT PK_SUPPLIERS_ID PRIMARY KEY(ID)
);
 
create ascending index IDX_SUPPLIERS_CITY on SUPPLIERS (CITY);
 
create ascending index IDX_SUPPLIERS_COMPANY on SUPPLIERS (COMPANY);
 
create ascending index IDX_SUPPLIERS_FIRST_NAME on SUPPLIERS ("FIRST NAME");
 
create ascending index IDX_SUPPLIERS_LAST_NAME on SUPPLIERS ("LAST NAME");
 
create ascending index IDX_SUPPLIERS_POSTAL_CODE on SUPPLIERS ("ZIP/POSTAL CODE");
 
create ascending index IDX_SUPPLIERS_PRIMARYKEY on SUPPLIERS (ID);
 
create ascending index "IDX_SUPPLIERS_STATE/PROVINCE" on SUPPLIERS ("STATE/PROVINCE");
 
create table DELIVERY
(
   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(35),
   NOTES   BLOB SUB_TYPE 1,
   INCLUDING   BLOB,
   CONSTRAINT PK_DELIVERY_ID PRIMARY KEY(ID)
);
 
create ascending index IDX_DELIVERY_CITY on DELIVERY (CITY);
 
create ascending index IDX_DELIVERY_COMPANY on DELIVERY (COMPANY);
 
create ascending index IDX_DELIVERY_FIRST_NAME on DELIVERY ("FIRST NAME");
 
create ascending index IDX_DELIVERY_LAST_NAME on DELIVERY ("LAST NAME");
 
create ascending index IDX_DELIVERY_POSTAL_CODE on DELIVERY ("ZIP/POSTAL CODE");
 
create ascending index IDX_DELIVERY_PRIMARYKEY on DELIVERY (ID);
 
create ascending index "IDX_DELIVERY_STATE/PROVINCE" on DELIVERY ("STATE/PROVINCE");
 
create table TAX_STATUS_OF_ORDERS
(
   ID    INTEGER NOT NULL,
   "NAME TAX STATUS"   VARCHAR(50),
   CONSTRAINT PK_TAX_STATUS_OF_ORDERS PRIMARY KEY(ID)
);
 
create ascending index IDX_TAX_STATUS_OF_ORDERS_PK on TAX_STATUS_OF_ORDERS (ID);
 
create table TYPES_RESERVE_OPERATIONS
(
   ID    INTEGER NOT NULL,
   "TYPE NAME"   VARCHAR(50),
   CONSTRAINT PK_TYPES_RESERVE_OPERATIONS PRIMARY KEY(ID)
);
 
create ascending index IDX_TYPES_RESERVE_OPERATIONS_PK on TYPES_RESERVE_OPERATIONS (ID);
 
ALTER TABLE ORDERS
  ADD CONSTRAINT FK_ORDERS_CLIENTID FOREIGN KEY (CLIENTID)
      REFERENCES CLIENTS (ID);
 
ALTER TABLE ORDERS
  ADD CONSTRAINT FK_ORDERS_EMPLOYEEID FOREIGN KEY (EMPLOYEEID)
      REFERENCES EMPLOYEES (ID);
 
ALTER TABLE ORDERS
  ADD CONSTRAINT FK_ORDERS_ID_STATUS FOREIGN KEY ("ID STATUS")
      REFERENCES CONDITION_OF_ORDERS (STATUSID);
 
ALTER TABLE INFORMATION_ABOUT_ORDER
  ADD CONSTRAINT FK_INFORM_ABOUT_ORDER_ID_STATUS FOREIGN KEY ("ID STATUS")
      REFERENCES CONDITION_OF_ORDERS (STATUSID);
 
ALTER TABLE INFORMATION_ABOUT_ORDER
  ADD CONSTRAINT FK_INFORM_ABOUT_ORDER_ORDERID FOREIGN KEY (ORDERID)
      REFERENCES ORDERS (ORDERID);
 
ALTER TABLE INFORMATION_ABOUT_ORDER
  ADD CONSTRAINT FK_INFORM_ORDERID_FOR_ACQUISIT FOREIGN KEY ("ORDERID FOR ACQUISITION")
      REFERENCES ORDERS_FOR_ACQUISITION ("ORDERID FOR ACQUISITION");
 
ALTER TABLE INFORMATION_ABOUT_ORDER
  ADD CONSTRAINT FK_INFORM_PRODUCTID FOREIGN KEY (PRODUCTID)
      REFERENCES PRODUCTS (ID);
 
ALTER TABLE EMPLOYEES_ROLES
  ADD CONSTRAINT FK_EMPLOYEES_ROLES_EMPLOYEEID FOREIGN KEY (EMPLOYEEID)
      REFERENCES EMPLOYEES (ID);
 
ALTER TABLE EMPLOYEES_ROLES
  ADD CONSTRAINT FK_EMPLOYEES_ROLES_ROLEID FOREIGN KEY (ROLEID)
      REFERENCES ROLES (ROLEID);
 
ALTER TABLE ORDERS_FOR_ACQUISITION
  ADD CONSTRAINT FK_ORDERS_FOR_ACQUIS_ID_STATUS FOREIGN KEY ("ID STATUS")
      REFERENCES STATUS_PURCHASE_ORDER (STATUSID);
 
ALTER TABLE ORDERS_FOR_ACQUISITION
  ADD CONSTRAINT FK_ORDERS_FOR_ACQUIS_SUPPLIERID FOREIGN KEY (SUPPLIERID)
      REFERENCES SUPPLIERS (ID);
 
ALTER TABLE OPERATIONS_WITH_STOCKS
  ADD CONSTRAINT FK_OPERATIONS_ORDERID_ACQUISIT FOREIGN KEY ("ORDERID FOR ACQUISITION")
      REFERENCES ORDERS_FOR_ACQUISITION ("ORDERID FOR ACQUISITION");
 
ALTER TABLE OPERATIONS_WITH_STOCKS
  ADD CONSTRAINT FK_OPERATIONS_ORDERID_OF_CLIENT FOREIGN KEY ("ORDERID OF CLIENT")
      REFERENCES ORDERS (ORDERID);
 
ALTER TABLE OPERATIONS_WITH_STOCKS
  ADD CONSTRAINT FK_OPERATIONS_PRODUCTID FOREIGN KEY (PRODUCTID)
      REFERENCES PRODUCTS (ID);
 
ALTER TABLE INFORMPURCHASEORDER
  ADD CONSTRAINT FK_DATA_ON_THE_ORDER_ACQUISIT FOREIGN KEY ("ORDERID FOR ACQUISITION")
      REFERENCES ORDERS_FOR_ACQUISITION ("ORDERID FOR ACQUISITION");
 
ALTER TABLE BILLS
  ADD CONSTRAINT FK_BILLS_ORDERID FOREIGN KEY (ORDERID)
      REFERENCES ORDERS (ORDERID);
 
29
{'organization': u'\u041e\u0440\u0433\u0430\u043d\u0438\u0437\u0430\u0446\u0438\u044f \u0410', 'ident': 1}
INSERT INTO CLIENTS (ID, COMPANY) VALUES(1, "Организация А")
 
Traceback (most recent call last):
  File "D:\ThirdTask\connect.py", line 262, in <module>
    cur.execute(s)
  File "C:\Python27\lib\site-packages\firebirdsql\fbcore.py", line 495, in execute
    stmt_type, stmt_handle = self._execute(query, params)
  File "C:\Python27\lib\site-packages\firebirdsql\fbcore.py", line 457, in _execute
    (h, oid, buf) = self.transaction.connection._op_response()
  File "C:\Python27\lib\site-packages\firebirdsql\wireprotocol.py", line 764, in _op_response
    return self._parse_op_response()
  File "C:\Python27\lib\site-packages\firebirdsql\wireprotocol.py", line 308, in _parse_op_response
    raise OperationalError(message, gds_codes, sql_code)
OperationalError: Dynamic SQL Error
SQL error code = -206
Column unknown
Организация А
At line 1, column 35

Специально вывел на печать сформированный запрос вставки

INSERT INTO CLIENTS (ID, COMPANY) VALUES(1, "Организация А")

и при его выполнении появляется ошибка (см. выше). Как исправить ошибку?


Добавить комментарий

2 Answers

Python Опубликовано 10.12.2018
0

А так?

INSERT INTO CLIENTS (ID, COMPANY) VALUES(1, 'Организация А')

Добавить комментарий
0

Вставку необходимо было осуществлять во второй транзакции способом, указанным в ответе на AttributeError: Transaction instance has no attribute 'trans_handle' .

Добавить комментарий
Напишите свой ответ на данный вопрос.
Scroll Up