Ошибочный sql запрос

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

Я написал программу, которая по описателю (заполненной базе, которая представляет собой структуру базы данных «Борей») генерирует эту базу данных в Firebird

import kinterbasdb
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()
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='?')
        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:/ThirdTask/test.fdb')
con=kinterbasdb.create_database("create database 'D:/ThirdTask/test.fdb' user 'sysdba' password 'masterkey'")
conn=kinterbasdb.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(' ')!=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:
                    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:
                            s+=namefield+','
                else:
                        if (namefield.count(' ')!=0) or (namefield.count('-')!=0) or (namefield.count('/')!=0):
                            s+='"'+namefield+'"'+')'+'\n'
                        else:
                            s+=namefield+')'+'\n'
    s+=');'+'\n'
    for nameindex in nametable['indices']:
        #print 'nameidex[props] ', nameindex['props']
        for prop in nameindex['props']:
            if prop==0:
                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:
                            s+=nameitem+', '
                else:
                        if (nameitem.count(' ')!=0) or (nameitem.count('-')!=0) or (nameitem.count('/')!=0):
                            s+='"'+nameitem+'"'+');'+'\n'
                        else:
                            s+=nameitem+');'+'\n'
    print s
    cur.execute(s)
    conn.commit()
#print s
#cur.execute(s)
#conn.commit()
#cur=con.cursor()
con.close()
conn.close()
print "Connect succesfully"

Модуль sql.py

sqltables='''SELECT id, name, description, can_add, can_edit, can_delete FROM tables'''
sqlfields = '''SELECT fields.position,fields.name,fields.description,
data_types.type_id,domains.char_length,
fields.can_input,fields.can_edit,
fields.show_in_grid,fields.is_mean, fields.autocalculated,
fields.required
FROM fields
JOIN domains ON fields.domain_id = <a href="http://domains.id">domains.id</a>
JOIN data_types ON domains.data_type_id = <a href="http://data_types.id">data_types.id</a>
WHERE fields.table_id = ?
ORDER BY fields.position
'''
sqlconstraints='''SELECT <a href="http://constraints.id">constraints.id</a>,
constraints.name, constraint_type,
tables.name
FROM constraints
LEFT JOIN tables ON constraints.reference = <a href="http://tables.id">tables.id</a>
WHERE table_id = ? '''
sql_constr_det = '''SELECT fields.name, tables.name
FROM constraint_details
JOIN fields ON constraint_details.field_id = <a href="http://fields.id">fields.id</a>
JOIN tables ON <a href="http://tables.id">tables.id</a>=fields.table_id
WHERE constraint_id = ?
AND <a href="http://tables.id">tables.id</a>= ? '''
sqlindices = '''SELECT <a href="http://indices.id">indices.id</a>,indices.name
FROM indices
WHERE indices.table_id = ? '''
sql_indices_fields = '''SELECT fields.name,
index_details.position
FROM indices
JOIN index_details ON index_id = <a href="http://indices.id">indices.id</a>
JOIN fields ON field_id = <a href="http://fields.id">fields.id</a>
WHERE indices.table_id = ?
AND <a href="http://indices.id">indices.id</a>=?'''
sql_indices_descend='''SELECT index_details.descend
FROM index_details
JOIN indices ON index_details.index_id = <a href="http://indices.id">indices.id</a>
JOIN fields ON field_id = <a href="http://fields.id">fields.id</a>
WHERE indices.table_id = ?
AND <a href="http://indices.id">indices.id</a> = ?
AND fields.name = ?'''

но при запуске выдает ошибку

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");
 
Traceback (most recent call last):
  File "D:\ThirdTask\<a href="http://connect.py">connect.py</a>", line 135, in <module>
    cur.execute(s)
ProgrammingError: (-104, 'isc_dsql_prepare: \n  Dynamic SQL Error\n  SQL error code = -104\n  Token unknown - line 23, column 1\n  create')

По-моему sql-запрос, выведенный для отладки правильный. Не понимаю в чем ошибка.


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

1 Ответы

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

Необходимо было каждый sql запрос оформлять в одну строку и передавать команде cur.execute(s)- в таком случае работает.

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