�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 0def 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 -1def 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 -1def 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
- SQL RDBMS FIREBIRD��b ������ (��)
- SQL RDBMS FIREBIRD�ƃf�[�g ���ʗ�����
- SQL RDBMS FIREBIRD��2.5�Ŏn�߂悤 +�ڑ���Q�ؕ���
- Firebird-2.5.0.RC2 ��Install
- �yCentOS�zFirebird-2.5.0.RC2 ��Superclassic�N����
- �yFirebird�zDB/TABLE�쐬�pPython���W���[��
- �yFirebird�zDB/TABLE�쐬�pPython���W���[��2
- �yFirebird�z�X�֔ԍ���DB�̍쐬 ./dl_ZipCodeJp.py
- �yFirebird�z�X�֔ԍ���DB�̍쐬(2) ���p���W���[�� mire.fb
- �yFirebird�z�X�֔ԍ���DB�̍쐬(3) conf_section_items in mire.std
- �yFirebird�z�X�֔ԍ���DB�̍쐬(4) �����t�H�[���Ŋm�F
- �yFirebird�z�X�֔ԍ���DB�̍쐬(5) mire.htm �� str_input_form()