�yFirebird�z�X�֔ԍ���DB�̍쐬(2) ���p���W���[�� mire.fb (original) (raw)

�@���Ɉꕔ�̊֐��͂����ꂩ�̍e�ŏЉ�ς݂̃��W���[���Q�����A����̗X�֔ԍ���DB�̎�g�݂ŁA�P��SQL���̎��s�p�֐��uexec_sql()�v��uquery()�v��V���ɒlj������̂ʼn��߂čŐV�ł��f�ڂ��Ă����B

�@�����A�K�v�ɉ����Đ��s�ō쐬���Ă���̂ŁA���ꐫ���m�ۂ���ׂɂƂ��ǂ��݊����̂Ȃ��d�l�ύX������s�Ȃ����Ƃ����邵�A�����܂����������Ƃ��Ă���͂�閘�͌��\�ύX������Ǝv���̂ŁA���̕ӁA���ي肢�����B

�@���č���̑傫�Ȓlj��́Aexec_sql()��query()�֐��ł���B

�@���Ƀf�[�^�x�[�X�쐬�����l�A�������UTF8�Ńf�[�^�i�[���邱�Ƃ�����Ƃ��Ă��邪�A���݂̂Ƃ���s��͋N���Ă��Ȃ��B�܂��Aconnect��cursor���֐��ɂɈ����p�����ƂŁA�֐����s����connect�I�u�W�F�N�g�̐����Acommit�����Ă���close�ɂ�镉�ׂ𖳂����A�A���������̃`���[�j���O���”\�ɂ����B�O�e�̗X�֔ԍ�����x�ł�conn��cur�Ɉ��p���ł��Ȃ��Ɛ��{�ȏ㏈���Ɏ��Ԃ��������Ă��܂��B

�@�ڍׂ̐����ɑウ�āAhlpy mire.fb �Ƃ��ďo����help()�֐��̏o�͂������āA�����Ƀ\�[�X���f�ڂ��Ă����B���A�O�e�̃X�N���v�g�ł́A����ȊO��mire.std�ɓo�^���Ă���INI�n�ݒ�t�@�C���̉�͊֐��uconf_section_items(conf_file, section, debug=debug)�v���g���Ă���B����͎��e��mire.std�Ƌ��Ɍf�ڂ���B

�@�G���Ȃ���A�����DB�֌W�I�u�W�F�N�g��1.���ۊm�F�A2.�쐬�A3.���O���o�Ɏ��ۂ�SQL����������������Ƃň�ʂ��RDBMS�����̗��ꂪ���������󂾁B
�@����Ō�A�v���‚��g���Ƃ��ẮA�e�L�X�g�C���|�[�g���̊֐���W�������邱�ƁASQL���ƃf�[�^�捞�݂𓯂��e�L�X�g�ɋL�������s������X�N���v�g�p�̊֐��ł�����ƕ֗����Ǝv���Ă���B���̓Ǝ��g���X�N���v�g�͈ȑOC++Builder�ō쐬�������Ƃ�����̂ʼn�������Python�ŕ��������悤�Ǝv���B�܂��A�Ɨ��A�v���ɂ͂Ȃ邪�A�Ǘ��c�[����N�G���[��e�[�u���ҏW��e�Ղɍs�Ȃ����Ƃ��o����Web�t�H�[���ō���Ƃ����ȂƂ͎v���Ă���B�e�[�u���̕ҏW�t�H�[���͈�x����Ă���̂łP�N��ʂɂ͏o���邩���ł��B���Ԃ�����΁B

     [root@l22dm ~]# hlpy mire.fb  

Help on module mire.fb in mire:

NAME
mire.fb

FILE
/usr/local/lib/python2.6/site-packages/mire/fb.py

DESCRIPTION
###########################################################################
# 1. exists_db() : DB�̑��݂��m�F�B �A�l: ����=1,�s����=0
# 2. exists_tbl() : �w�薼�̂̃e�[�u���̑��݂��m�F�B�A�l: ����=1,�s����=0
# 3. exists_trg() : �w�薼�̂�TRIGGER�̑��݂��m�F�B �A�l: ����=1,�s����=0
# 4. exists_seq() : �w�薼�̂�SEQUENCE�̑��݂��m�F�B�A�l: ����=1,�s����=0
# 5. exists_idx() : �w�薼�̂�INDX�̑��݂��m�F�B �A�l: ����=1,�s����=0
# 6. tbl_name() : SQL������e�[�u�����𒊏o���Ԃ��B
# 7. trg_name() : SQL������TRIGGER���𒊏o���Ԃ��B
# 8. seq_name() : SQL������SEQUENCE���𒊏o���Ԃ��B
# 9. create_db() : DB���쐬
#10. create_tbl() : �w��sql�ɂ��e�[�u���쐬
#11. create_idx() : index����table���fields���X�g����L�[�Ƃ���INDEX���쐬
#11. query() : �l��Ԃ��N�G���������s
#12. exec_sql() : �P�Ƃ�SQL�������s
#13. exec_sqls() : sql�̃��X�g�̘A�����s�B�����̃e�[�u����SEQUENCE�쐬�ւ�
# : ���p��z��BDB���g�����A�v���ŁA�v���W�F�N�g�P�ʂ�DB��
# : �쐬����ꍇ��SQL�𕡐��ꊇ�w�肵�čς܂��邱�Ƃ��”\��
# : �l�ɒ����@�\�������BVIEW,TRIGGER,PROCEDURE�������Ή��B
# : ���̊֐��ɂ́ASQL������A���̍쐬�Ώۖ��𒊏o����֐���
# : DB���Ɋ����̓����̂��̂��Ȃ������ׂ�֐����K�v�ƂȂ�B
# : ��L��exists_tbl,exists_seq,tbl_name,seq_name�����̗��
# : ���č쐬�\��(2010-02-24)�Btrigger�͒lj��ς������e�X�g�B
# : �������ǂ��g���̂͂��̂��炢�Ȃ̂Ō�͌���B
# : ���A���̊֐��ɋ@�\���܂��ꂽcreate_tbls()�͔p�~�����B
#14. count_value(): �w��DB��TABLE��FIELD���Ɏw��l�̐���Ԃ�
##
# 2010-04-13 create_db�ɑ��ݎ������p�X��page size, charset�w���lj�
# 2010-04-28 exec_sql, query ����lj����ADOC�𒲐�
# 2010-04-29 exists_idx, create_idx ��lj�

FUNCTIONS
count_value(value, field, table, dsn, user, password, debug=0)
�w��DB��TABLE��FIELD���Ɏw��l�����݂��邩�ǂ�����Ԃ�

create_db(dsn='localhost:DUMMY', user='SYSDBA', password='masterkey', page_size=None, charset=None, debug=0)  
    DB�̑��݂��m�F�B  
    ����: dsn, user, password, page_size:1024;2048;4096;8192;16384(�V�X�e�������4096), charset  
    �A�l: ����=1,�s����=0  
    ���ݎ��ɂ̓p�X (2010-04-13�lj�)

create_idx(index, fields=[], table='', conn=None, cur=None, dsn='', user='SYSDBA', password='manterkey', charset='UTF8', debug=0)  
    index�̖��O��table���fields���X�g��  
    ����L�[�Ƃ���C���f�b�N�X�𐶐�  
    2010-04-29 �lj�

create_tbl(sql='', dsn='localhost:DUMMY', user='SYSDBA', password='masterkey', debug=0)  
    �e�[�u���쐬  
    sql:          �쐬����SQL�����w��  
    ���̑��̈���: dsn, user, password  
    2010-04-13 ���ۃ`�F�b�N�t��

exec_sql(sql, params=[], conn=None, cur=None, ip='127.0.0.1', db_file='', user='SYSDBA', password='masterkey', charset='UTF8', debug=0)  
        SQL�����s�֐�  
        Database�Ƃ�Connection�̈��p������ꍇ�ɂ́A����𗬗p��  
        ���ACommit��Com  
    nection��‚���������Ȃ��B  
        �� �A�����[�v���ł̕p�ɂ�Connection�̐����ƕ��A������  
           Commit��CPU���ׂ��傫���Ȃ�B���̏ꍇ�̈��p�����ɗL��  
           �A��CPU���ׂ����ɂȂ�Ȃ��ꍇ�ɂ́A�P���SQL���s����  
           Connection�̐����ƕ�������Commit�����{���������悢�B  
           ������s����SQL��Commit����O�������Ŕ��f����Ȃ��”\��  
           �����邩��ł���B���̉”\�������������ꍇ���ɂ́Aconn��  
           None�����āA��O�������Ȃ����̂�L���ɂ��邱�Ƃ��o����B  
           �ʓr�A�֐����N�����ׂ������A�Ⴆ�΁A���������e�[�u����`  
           �𒴂����ꍇ�̉��}�Ώ��Ƃ��Ă��L�����낤�B  
        2010-04-28�lj�

exec_sqls(sqls=[], dsn='localhost:DUMMY', user='SYSDBA', password='masterkey', debug=0)

exists_db(dsn='', user='SYSDBA', password='masterkey', debug=0)  
    #### �ڑ����Ď��āADB�̑��݂��m�F ####  
    ���݂����� 1��Ԃ��A�łȂ���΁A0��Ԃ�

exists_idx(index, conn=None, cur=None, dsn='', user='SYSDBA', password='masterkey', charset='UTF8', debug=0)  
    �w�薼�̂�INDEX�̑��݂��m�F  
    2010-04-28 �lj�  
    2010-04-29 �C��

exists_seq(seq, conn=None, cur=None, dsn='', user='SYSDBA', password='masterkey')  
    �V�X�e���e�[�u���uRDB$GENERATORS�v�ɐڑ����쐬�\���SEQUENCE����  
    �����̂��̂ƃ_�u���ĂȂ�������

exists_tbl(table, conn=None, cur=None, dsn='', user='SYSDBA', password='masterkey', debug=0)  
    �V�X�e���e�[�u���uRDB$RELATIONS�v�ɐڑ���  
    �쐬�\��̃e�[�u�����������e�[�u����  
    �_�u���ĂȂ����̒���  
     1:����  
     0:���݂��Ȃ�  
    -1:��O����  
    2010-04-13 Added debug mode

exists_trg(trg, conn=None, cur=None, dsn='', user='SYSDBA', password='masterkey')  
    �V�X�e���e�[�u���uRDB$TRIGGERS�v�ɐڑ����쐬�\��̃g���K�[����  
    �����̂��̂ƃ_�u���ĂȂ�������

query(sql, params=[], conn=None, cur=None, ip='127.0.0.1', db_file='', user='SYSDBA', password='masterkey', charset='UTF8', mode='all', size=None, debug=0)  
    �N�G���nSQL�����s�֐�  
    exec_sql()���l��Connection�̈��p���”\  
    sql�ɂ�SELECT�Ŏn�܂�N�G�����݂̂̎w���z��  
    mode�ɂ��Afetchone(), fetchmany(), fetchall()��  
    ���ʂ̂����ꂩ��Ԃ����Ƃ��o����Bfetchall()�ȊO  
    �̖��擾���ɂ‚��ẮAconn��cur�����p�����ꍇ��  
    �̂݁A���̊֐��̊O�Ŏ擾�”\�ƂȂ�B  
    2010-04-28�lj�

seq_name(sql, debug=0)  
    CREATE SEQUENCE ��SQL������TABLE���𒊏o���A���X�g�ŕԂ�  
    �� ������ CREATE GENERATOR �͔񐄏��ɕt�A�Ή������B

tbl_name(sql, debug=0)  
    CREATE TABLE ��SQL������TABLE���𒊏o���A���X�g�ŕԂ�

trg_name(sql, debug=0)  
    CREATE TRIGGER ��SQL������TRIGGER���𒊏o���A���X�g�ŕԂ�

DATA
author = 'Mire in Japan'
copyright = 'Copyright (c) 2010 Mire'
license = 'GPL'
url = 'http://pythonlife.seesaa.net/article/146561690.html'
version = '0.0.3'

VERSION
0.0.3

AUTHOR
Mire in Japan

(END)

/usr/local/lib/python2.6/site-packages/mire/fb.py

#!/usr/local/bin/Python26

-- coding: UTF-8 --

"""
###########################################################################

1. exists_db() : DB�̑��݂��m�F�B �A�l: ����=1,�s����=0

2. exists_tbl() : �w�薼�̂̃e�[�u���̑��݂��m�F�B�A�l: ����=1,�s����=0

3. exists_trg() : �w�薼�̂�TRIGGER�̑��݂��m�F�B �A�l: ����=1,�s����=0

4. exists_seq() : �w�薼�̂�SEQUENCE�̑��݂��m�F�B�A�l: ����=1,�s����=0

5. exists_idx() : �w�薼�̂�INDX�̑��݂��m�F�B �A�l: ����=1,�s����=0

6. tbl_name() : SQL������e�[�u�����𒊏o���Ԃ��B

7. trg_name() : SQL������TRIGGER���𒊏o���Ԃ��B

8. seq_name() : SQL������SEQUENCE���𒊏o���Ԃ��B

9. create_db() : DB���쐬

#10. create_tbl() : �w��sql�ɂ��e�[�u���쐬
#11. create_idx() : index����table���fields���X�g����L�[�Ƃ���INDEX���쐬
#11. query() : �l��Ԃ��N�G���������s
#12. exec_sql() : �P�Ƃ�SQL�������s
#13. exec_sqls() : sql�̃��X�g�̘A�����s�B�����̃e�[�u����SEQUENCE�쐬�ւ�

: ���p��z��BDB���g�����A�v���ŁA�v���W�F�N�g�P�ʂ�DB��

: �쐬����ꍇ��SQL�𕡐��ꊇ�w�肵�čς܂��邱�Ƃ��”\��

: �l�ɒ����@�\�������BVIEW,TRIGGER,PROCEDURE�������Ή��B

: ���̊֐��ɂ́ASQL������A���̍쐬�Ώۖ��𒊏o����֐���

: DB���Ɋ����̓����̂��̂��Ȃ������ׂ�֐����K�v�ƂȂ�B

: ��L��exists_tbl,exists_seq,tbl_name,seq_name�����̗��

: ���č쐬�\��(2010-02-24)�Btrigger�͒lj��ς������e�X�g�B

: �������ǂ��g���̂͂��̂��炢�Ȃ̂Ō�͌���B

: ���A���̊֐��ɋ@�\���܂��ꂽcreate_tbls()�͔p�~�����B

#14. count_value(): �w��DB��TABLE��FIELD���Ɏw��l�̐���Ԃ�

2010-04-13 create_db�ɑ��ݎ������p�X��page size, charset�w���lj�

2010-04-28 exec_sql, query ����lj����ADOC�𒲐�

2010-04-29 exists_idx, create_idx ��lj�

"""
author = "Mire in Japan"
version = '0.0.3'
copyright = 'Copyright (c) 2010 Mire'
license = 'GPL'
url = 'http://pythonlife.seesaa.net/article/146561690.html'

import gettext #�ȉ���i18n���ۉ��̗��p�������R�[�h(������)���܂�
def exists_db(dsn='', user='SYSDBA', password='masterkey', debug=0):
"""
#### �ڑ����Ď��āADB�̑��݂��m�F ####
���݂����� 1��Ԃ��A�łȂ���΁A0��Ԃ�

"""  
from kinterbasdb import connect  
try:  
    conn=connect(dsn=dsn,user=user,password=password)  
    conn.close()  
    if not debug==0: print _('EXISTS_DB')  
    return 1  
except:  
    if not debug==0: print _('NOT EXISTS_DB')  
    return 0

def exists_trg(trg,conn=None,cur=None,dsn='', user='SYSDBA', password='masterkey'):
"""
�V�X�e���e�[�u���uRDB$TRIGGERS�v�ɐڑ����쐬�\��̃g���K�[����
�����̂��̂ƃ_�u���ĂȂ�������
"""
from kinterbasdb import connect
sql_exists="""SELECT RDB$TRIGGERS_ID
FROM RDB$TRIGGERS
WHERE RDB$TRIGGER_NAME='%s';""" % (seq)

if conn==None:  
    try:  
        conn=connect(dsn=dsn,user=user,password=password)  
        cur=conn.cursor()  
        cur.execute(sql_exists)  
        if len(cur.fetchall())==0:  # ���݂��Ȃ�������  
            return 0  
        else:  
            return 1  
    except:  
        return -1  
    finally:  
        conn.close()                # �ڑ���‚���B  
else:  
    try:  
        cur.execute(sql_exists)  
        if len(cur.fetchall())==0:  # ���݂��Ȃ�������  
            return 0  
        else:  
            return 1  
    except:  
        return -1

def exists_seq(seq,conn=None,cur=None,dsn='', user='SYSDBA', password='masterkey'):
"""
�V�X�e���e�[�u���uRDB$GENERATORS�v�ɐڑ����쐬�\���SEQUENCE����
�����̂��̂ƃ_�u���ĂȂ�������
"""
from kinterbasdb import connect
sql_exists="""SELECT RDB$GENERATORS_ID
FROM RDB$GENERATORS
WHERE RDB$GENERATORS_NAME='%s';""" % (seq)

if conn==None:  
    try:  
        conn=connect(dsn=dsn,user=user,password=password)  
        cur=conn.cursor()  
        cur.execute(sql_exists)  
        if len(cur.fetchall())==0:  # ���݂��Ȃ�������  
            return 0  
        else:  
            return 1  
    except:  
        return -1  
    finally:  
        conn.close()                # �ڑ���‚���B  
else:  
    try:  
        cur.execute(sql_exists)  
        if len(cur.fetchall())==0:  # ���݂��Ȃ�������  
            return 0  
        else:  
            return 1  
    except:  
        return -1

def exists_tbl(table,conn=None,cur=None,dsn='', user='SYSDBA', password='masterkey', debug=0):
"""
�V�X�e���e�[�u���uRDB$RELATIONS�v�ɐڑ���
�쐬�\��̃e�[�u�����������e�[�u����
�_�u���ĂȂ����̒���
1:����
0:���݂��Ȃ�
-1:��O����
2010-04-13 Added debug mode
"""
from kinterbasdb import connect
sql_exists = """SELECT RDB$RELATION_ID
FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME='%s';""" % (table)
if conn==None:
try:
conn=connect(dsn=dsn,user=user,password=password)
cur=conn.cursor()
cur.execute(sql_exists)
if len(cur.fetchall())==0: # ���݂��Ȃ�������
if debug>0: print '%s Not Exists' % (table)
return 0
else:
if debug>0: print '%s Exists' % (table)
return 1
except:
if debug>0:
from mire.htm import display_err
print 'Exception Error (coon=None)'
print sql_exists
conn=connect(dsn=dsn,user=user,password=password)
cur=conn.cursor()
cur.execute(sql_exists)
return -1
finally:
conn.close() # �ڑ���‚���B
else:
try:
cur.execute(sql_exists)
if len(cur.fetchall())==0: # ���݂��Ȃ�������
if debug>0: print '%s Not Exists' % (table)
return 0
else:
if debug>0: print '%s Exists' % (table)
return 1
except:
if debug>0:
print 'Exception Error'
print sql_exists
execute(sql_exist)
return -1

def exists_idx(index, conn=None, cur=None, dsn=''
, user='SYSDBA', password='masterkey', charset='UTF8', debug=0):
"""
�w�薼�̂�INDEX�̑��݂��m�F
2010-04-28 �lj�
2010-04-29 �C��
"""
try:
if conn==None or cur==None:
from kinterbasdb import connect
conn = connect(dsn=dsn, user=user, password=password, charset=charset)
cur = conn.cursor()
not_connected = 1
else:
not_connected = 0

    sql = "SELECT RDB$INDEX_NAME FROM RDB$INDEX_SEGMENTS WHERE RDB$INDEX_NAME = ? "  
    cur.execute(sql, [(index)])  
    datas = cur.fetchall()  
    if datas==[]:  
        return 0  
    else:  
        return 1  
except:  
    if debug>0:  
        from mire.htm import display_err  
        display_err(locals(), title='Error! mire.fb.py exists_idx()')  
    else:  
        print 'Please, Contact Your System Administrator.'  
    return -1  
finally:  
    if not_connected == 1:  
        conn.commit()  
        conn.close()

def trg_name(sql, debug=0):
"""
CREATE TRIGGER ��SQL������TRIGGER���𒊏o���A���X�g�ŕԂ�
"""
import re
s='%s' % (sql)
s=sql.replace('\n', ' ')
ss=re.findall(r'(CREATE\sTRIGGER\s[a-z,A-Z,]*|create\trigger\s[a-z,A-Z,]*)',s)
nms=[]
for sq in ss:
nm=sq.split()
for n in nm:
if not re.search(r'(CREATE|create|Crate|TRIGGER|trigger|Trigger)',n):
nms.append(n)
if debug>0: print 'nms=', nms
return nms

def seq_name(sql, debug=0):
"""
CREATE SEQUENCE ��SQL������TABLE���𒊏o���A���X�g�ŕԂ�
�� ������ CREATE GENERATOR �͔񐄏��ɕt�A�Ή������B
"""
import re
s='%s' % (sql)
s=sql.replace('\n', ' ')
ss=re.findall(r'(CREATE\sSEQUENCE\s[a-z,A-Z,]*|create\sequence\s[a-z,A-Z,]*)',s)
nms=[]
for sq in ss:
nm=sq.split()
for n in nm:
if not re.search(r'(CREATE|create|Crate|SEQUENCE|sequence|Sequence)',n):
if n[-1]==';':
nms.append(n[:-1])
else:
nms.append(n)
if debug>0: print 'nms=', nms
return nms

def tbl_name(sql, debug=0):
"""
CREATE TABLE ��SQL������TABLE���𒊏o���A���X�g�ŕԂ�
"""
import re
s='%s' % (sql)
s=sql.replace('\n', ' ')
ss=re.findall(r'(CREATE\sTABLE\s[a-z,A-Z,]*(|create\stable\s[a-z,A-Z,]*()',s)
nms=[]
for sq in ss:
nm=sq.split()
for n in nm:
if not re.search(r'(CREATE|create|Crate|TABLE|table|Table)',n):
if n[-1]=='(':
nms.append(n[:-1])
else:
nms.append(n)
if debug>0: print 'nms=', nms
return nms

def create_db(dsn='localhost:DUMMY', user='SYSDBA',
password='masterkey', page_size=None, charset=None, debug=0):
"""
DB�̑��݂��m�F�B
����: dsn, user, password, page_size:1024;2048;4096;8192;16384(�V�X�e�������4096), charset
�A�l: ����=1,�s����=0
���ݎ��ɂ̓p�X (2010-04-13�lj�)
"""
if exists_db(dsn=dsn, user=user, password=password):
return 1 #���ɑ��݂���Ƃ��̓p�X���� Added 2010-04-13

from kinterbasdb import create_database  
ds=dsn[len(dsn.split(':')[0]):]  
sql="CREATE DATABASE '%s' USER '%s' PASSWORD '%s'" % (ds,user,password)  
if not page_size is None:  
    sql=sql+' PAGE_SIZE=%d' % (page_size)  
# Added 2010-04-13  
if not charset is None:  
    sql=sql+' DEFAULT CHARACTER SET %s' % (charset)  
sql=sql+';'  
if debug>0: print sql  
conn=create_database(sql)  
conn.commit()  
conn.close()

def create_tbl(sql='', dsn='localhost:DUMMY', user='SYSDBA', password='masterkey',debug=0):
"""
�e�[�u���쐬
sql: �쐬����SQL�����w��
���̑��̈���: dsn, user, password
2010-04-13 ���ۃ`�F�b�N�t��
"""
from types import ListType
from kinterbasdb import connect
if debug>0: print dsn,'\n', sql
table = tbl_name(sql)
if type(table) is ListType: table=table[0]
if exists_tbl(table=table, dsn=dsn, user=user, password=password, debug=debug)==1 : #���ɑ���
if debug>0: print table #����Ȃ�
return 1 #�p�X
conn=connect(dsn=dsn,user=user,password=password)
cur=conn.cursor()
cur.execute(sql)
conn.commit()
conn.close()
return 0

def create_idx(index, fields=[], table='', conn=None, cur=None, dsn='',
user='SYSDBA', password='manterkey', charset='UTF8', debug=0):
"""
index�̖��O��table���fields���X�g��
����L�[�Ƃ���C���f�b�N�X�𐶐�
2010-04-29 �lj�
"""
from mire.fb import exists_idx
if conn==None or cur==None: # Connection�̈��p
from kinterbasdb import connect # ���Ȃ��Ƃ���
dsn = '%s:%s' % (ip, db_file) # connect �I�u�W�F
conn = connect(dsn=dsn, user=user, password=password,charset=charset) # �N�g���쐬��
cur = conn.cursor() # cursor ���N����
not_connected = 1 # �ڑ����Ȃ�����1
else:
not_connected = 0 # �ڑ��������̂�0
try:
if exists_idx(index=index, conn=conn, cur=cur, dsn=dsn,
user=user, password=password, debug=debug) == 0:
sql = 'CREATE INDEX %s ON %s (%s);' % (index, table, ','.join(fields))
cur.execute(sql)
conn.commit()
return 0
else:
return 1
except:
if debug>0:
from mire.htm import display_err
display_err(locals(),title='Error! [search.py create_idx()]')
else:
pass # index�͖����Ă����삷��̂Ń��[�U�ɂ͉����m�点�Ȃ�
return -1
finally: # ��O�����̔@���ɍS�炸���s
if not_connected == 1: # Connection���Ȃ������Ƃ���
conn.commit() # �֐��I������commit��
conn.close() # close ���s�Ȃ�

def exec_sqls(sqls=[], dsn='localhost:DUMMY',user='SYSDBA',password='masterkey', debug=0):
import re
from kinterbasdb import connect
#�ȉ���i18n���ۉ��̗��p�������R�[�h(������)���܂�
gettext.bindtextdomain('mire', './language/i18n')
gettext.textdomain('mire')
_ = gettext.gettext
conn=connect(dsn=dsn,user=user,password=password, debug=0)
cur=conn.cursor()
for sql in sqls:
if re.search(r'(CREATE\sTABLE\s|create\stable\s)',sql):
#�e�[�u���쐬SQL�Ȃ��
tables = tbl_name(sql) #TABLE�����擾
if len(tables)==1: #TABLE��������o������
table=tables[0]
ex_tbl=exists_tbl(table=table, dsn=dsn,
conn=conn,cur=cur, user=user, password=password)
if debug>0: print _('ex_tbl='), ex_tbl
if ex_tbl==0:
#�e�[�u�������݂��Ȃ��̂�
try:
cur.execute(sql) #����SQL�����s������
except:
if debug>0:
print _('failed in SQL execution.') #SQL���s�Ɏ��s���܂����B
print u''
else:
if debug>0:
print _('Already, table '%s' exists in this DB.' % (table))
#���ɁA�e�[�u��'%s' �͂���DB���ɑ��݂��܂��B
print _('Then, The SQL for CREATE TABLE was not executed.')
#����ŁA�e�[�u���쐬��SQL�͎��s����܂���ł����B
else:
if debug>0:
print _('table name was not specified.')
print 'tables=', tables
print 'sql='
print sql
elif re.search(r'(CREATE\sSEQUENCE\s|create\ssequence\s)',sql):
#SEQUENCE�쐬�Ȃ�
sequences = seq_name(sql)
if len(sequences)==1:
sequence = sequences[0]
ex_seq = exists_seq(seq=sequence, dsn=dsn,
conn=conn,cur=cur, user=user, password=password)
if debug>0: print _('ex_seq='), ex_seq
if ex_seq==0:
#�A�Ԃ����݂��Ȃ��̂�
try:
cur.execute(sql) #����SQL�����s������
except:
if debug>0:
print _('failed in SQL execution.') #SQL���s�Ɏ��s���܂����B
print u''
else:
if debug>0:
print _('Already, sequence '%s' exists in this DB.' % (sequence))
#���ɁASEQUENCE '%s' �͂���DB���ɑ��݂��܂��B
print _('Then, The SQL for CREATE SEQUENCE was not executed.')
#����ŁASEQUENCE�쐬��SQL�͎��s����܂���ł����B
else:
if debug>0:
print _('sequence name was not specified.')
# sequence��������o���܂���ł����B
print 'sequences=', sequences
print 'sql='
print sql
else:
try:
cur.execute(sql)
except:
if debug>0:
print _('SQL can not be executed.')
#SQL�͎��s�o���܂���B
print sql
conn.commit()
conn.close()

def count_value(value, field, table, dsn, user, password, debug=0):
"""
�w��DB��TABLE��FIELD���Ɏw��l�����݂��邩�ǂ�����Ԃ�
"""
from kinterbasdb import connect
try:
sql = "SELECT %s FROM %s WHERE $s=?" % (field, table, field)
conn = connect(dsn=dsn,user=user,password=password, debug=0)
cur = conn.cursor()
cur.execute(sql,value)
datas = cur.fetchall()
return len(datas)
except:
if debug>0: # Debug���̓G���[����
from mire.htm import display_err
display_err(locals(),title='Error in count_value()')
else: # ���^�p�ł́A�Z�L�����e�B�セ����B��
print 'Please, Contact Your Administrator'
finally:
try:
conn.close()
except:
pass

�f�[�^�̍X�V�o�^�}���p�֐�

def exec_sql(sql, params=[], conn=None, cur=None, ip='127.0.0.1', db_file='',
user='SYSDBA', password='masterkey', charset='UTF8', debug=0):
"""
SQL�����s�֐�
Database�Ƃ�Connection�̈��p������ꍇ�ɂ́A����𗬗p��
���ACommit��Com\nnection��‚���������Ȃ��B
�� �A�����[�v���ł̕p�ɂ�Connection�̐����ƕ��A������
Commit��CPU���ׂ��傫���Ȃ�B���̏ꍇ�̈��p�����ɗL��
�A��CPU���ׂ����ɂȂ�Ȃ��ꍇ�ɂ́A�P���SQL���s����
Connection�̐����ƕ�������Commit�����{���������悢�B
������s����SQL��Commit����O�������Ŕ��f����Ȃ��”\��
�����邩��ł���B���̉”\�������������ꍇ���ɂ́Aconn��
None�����āA��O�������Ȃ����̂�L���ɂ��邱�Ƃ��o����B
�ʓr�A�֐����N�����ׂ������A�Ⴆ�΁A���������e�[�u����`
�𒴂����ꍇ�̉��}�Ώ��Ƃ��Ă��L�����낤�B
2010-04-28�lj�
"""
if conn==None or cur==None: # Connection�̈��p
from kinterbasdb import connect # ���Ȃ��Ƃ���
dsn='%s:%s' % (ip, db_file) # connect �I�u�W�F
conn = connect(dsn=dsn, user=user, password=password,charset=charset) # �N�g���쐬��
cur = conn.cursor() # cursor ���N����
not_connected = 1 # �ڑ����Ȃ�����1
else:
not_connected = 0 # �ڑ��������̂�0
try:
if params == []:
cur.execute(sql)
else:
cur.execute(sql,tuple(params))
#conn.commit()
except:
if debug>0:
from mire.std import display_err
display_err(locals(),title='Error! [ZipCodeJp.py insert()]')
else:
print 'Please, Contact Your System Administrator.'
raise
finally:
if not_connected == 1:
conn.commit()
conn.close()

def query(sql, params=[], conn=None, cur=None, ip='127.0.0.1', db_file='',
user='SYSDBA', password='masterkey', charset='UTF8', mode='all', size=None, debug=0):
"""
�N�G���nSQL�����s�֐�
exec_sql()���l��Connection�̈��p���”\
sql�ɂ�SELECT�Ŏn�܂�N�G�����݂̂̎w���z��
mode�ɂ��Afetchone(), fetchmany(), fetchall()��
���ʂ̂����ꂩ��Ԃ����Ƃ��o����Bfetchall()�ȊO
�̖��擾���ɂ‚��ẮAconn��cur�����p�����ꍇ��
�̂݁A���̊֐��̊O�Ŏ擾�”\�ƂȂ�B
2010-04-28�lj�
"""
if conn==None or cur==None: # Connection�̈��p
from kinterbasdb import connect # ���Ȃ��Ƃ���
dsn = '%s:%s' % (ip, db_file) # connect �I�u�W�F
conn = connect(dsn=dsn, user=user, password=password,charset=charset) # �N�g���쐬��
cur = conn.cursor() # cursor ���N����
not_connected = 1 # �ڑ����Ȃ�����1
else:
not_connected = 0 # �ڑ��������̂�0

try:  
    if params == []:                                                       # �������Ȃ��ꍇ��  
        cur.execute(sql)                                                   # ������SQL�����s  
    else:  
        cur.execute(sql,tuple(params))                                     # �����L�Ȃ�����t

    if mode == 'one':  
        datas = cur.fetchone()                 # count sum ���ł͂���  
    elif mode == 'many':  
        if size==None:  
            datas = cur.fetchmany()            # �g�������ƂȂ�����Ȃ�  
        else:  
            datas = cur.fetchmany(size=size)   # �@�\����̂��m��Ȃ���!  
    else:  
        datas = cur.fetchall()                 # ���ʂ͂���ł��傤

except:  
    if debug>0:  
        from mire.std import display_err  
        display_err(locals(),title='Error! [ZipCodeJp.py insert()]')  
    else:  
        print 'Please, Contact Your System Administrator.'  
        raise  
finally:  
    if not_connected == 1:                                       # Connection���Ȃ������Ƃ���  
        conn.commit()                                            # �֐��I������commit��  
        conn.close()                                             #             close ���s�Ȃ�  
return datas                                                     # �N�G������Ԃ�

##########################################

���̃��W���[�������s�����Help��\��

�����less�Ɠ���

�Ői��:f �Ŗ߂�:b �̗��p��������

##########################################
if name == 'main': # ���ڎ��s���݈̂ȉ������s
import mire.fb
help(mire.fb)

�y���@�@���z
2010-04-28 �V�K������
2010-04-29 index�n�̏C���ƒlj�

| | | | ------------------------------------------------------------------------------------- |

�yFIREBIRD �֘A���e�z