def createTables(CONN_STRING):
con = mydb.getCon(CONN_STRING)
queries = list()
queries.append('create table users(user_id varchar(50), name varchar(100), review_count int, primary key(user_id))')
queries.append('create table business(business_id varchar(50), name varchar(100), full_address varchar(100), city varchar(20), state varchar(20), review_count int, categories varchar(20), primary key(business_id))')
queries.append('create table review(id int, business_id varchar(50), user_id varchar(50), review_text text, review_date date, review_clauses text, primary key(id))')
mydb.executeManyQuery(con, queries, True)
def dropTables(CONN_STRING):
con = mydb.getCon(CONN_STRING)
queries = list()
queries.append('drop table if exists review')
queries.append('drop table if exists users')
queries.append('drop table if exists business')
mydb.executeManyQuery(con, queries, True)
def clearTables(CONN_STRING):
con = mydb.getCon(CONN_STRING)
queries = list()
queries.append('delete from review')
queries.append('delete from users')
queries.append('delete from business')
mydb.executeManyQuery(con, queries, True)
def create_table(con, table_name):
querys = []
query = 'drop table if exists __table_name__;'
query = query.replace('__table_name__', table_name)
querys.append(query)
query = 'create table __table_name__(rel text, s text, e text);'
query = query.replace('__table_name__', table_name)
querys.append(query)
mydb.executeManyQuery(con, querys, True)
def create_table(con,table_name):
querys = []
query = 'drop table if exists __table_name__;'
query = query.replace('__table_name__',table_name)
querys.append(query)
query = 'create table __table_name__(rel text, s text, e text);'
query = query.replace('__table_name__',table_name)
querys.append(query)
mydb.executeManyQuery(con,querys,True)
def main():
from optparse import OptionParser
# option
usage="usage: %prog [options]"
parser = OptionParser(usage=usage)
parser.add_option("-t","--tfile", dest='type_filePath',help="Type file path")
parser.add_option("-p","--pfile", dest='property_filePath',help="Property file path")
parser.add_option("-l","--lang", dest='lang',help="language:EN,ES,RU,FA")
parser.add_option("-d","--debug",dest = "debug" , action = "store_true",
help="output debug info, default is false", default = False)
(options,args) = parser.parse_args()
lang = options.lang
tfilePath = options.type_filePath
pfilePath = options.property_filePath
debug = options.debug
indexs={"EN":0,"ES":1,"RU":2,"FA":3}
type_table_names=["type_en","type_es","type_ru","type_fa"]
property_table_names=["property_en","property_es","property_ru","property_fa"]
index=indexs[lang]
type_table_name = type_table_names[index]
property_table_name = property_table_names[index]
CONN_STRING=mydb.get_CONN()
con = mydb.getCon(CONN_STRING)
# create table
querys=[]
querys.append( 'DROP TABLE IF EXISTS '+type_table_name+';' )
querys.append('DROP TABLE IF EXISTS '+property_table_name+';')
querys.append('CREATE TABLE '+type_table_name+'(entity varchar,type varchar);')
querys.append('CREATE TABLE '+property_table_name+'(entity varchar,property varchar,value varchar);')
mydb.executeManyQuery(con,querys,debug)
# process the data
tfile = open(tfilePath,'r')
pfile = open(pfilePath,'r')
# for the type file
query_t='insert into __table_name__(entity,type) VALUES(%s, %s)'
query_t=query_t.replace('__table_name__',type_table_name)
processFile(con,query_t,tfile,True)
# for property table
query_p='insert into __table_name__(entity,property,value) VALUES(%s, %s, %s)'
query_p=query_p.replace('__table_name__',property_table_name)
processFile(con,query_p,pfile,False)
# build index
querys=[]
query = 'create index __indexName__ on __index__;'
querys.append(query.replace('__indexName__',type_table_name+'_entity').replace('__index__',type_table_name+'(entity)'))
querys.append(query.replace('__indexName__',type_table_name+'_type').replace('__index__',type_table_name+'(type)'))
querys.append(query.replace('__indexName__',property_table_name+'_entity').replace('__index__',property_table_name+'(entity)'))
querys.append(query.replace('__indexName__',property_table_name+'_property').replace('__index__',property_table_name+'(property)'))
mydb.executeManyQuery(con,querys,debug)
# close db
mydb.closeCon(con)
def main():
from optparse import OptionParser
# option
usage = "usage: %prog [options]"
parser = OptionParser(usage=usage)
parser.add_option("-t",
"--tfile",
dest='type_filePath',
help="Type file path")
parser.add_option("-p",
"--pfile",
dest='property_filePath',
help="Property file path")
parser.add_option("-l",
"--lang",
dest='lang',
help="language: EN, ES, RU, FA")
parser.add_option("-d",
"--debug",
dest="debug",
action="store_true",
help="output debug info, default is false",
default=False)
(options, args) = parser.parse_args()
lang = options.lang
tfilePath = options.type_filePath
pfilePath = options.property_filePath
debug = options.debug
indexes = {"EN": 0, "ES": 1, "RU": 2, "FA": 3}
type_table_names = ["type_en", "type_es", "type_ru", "type_fa"]
property_table_names = [
"property_en", "property_es", "property_ru", "property_fa"
]
index = indexes[lang]
type_table_name = type_table_names[index]
property_table_name = property_table_names[index]
CONN_STRING = mydb.get_CONN()
con = mydb.getCon(CONN_STRING)
# create table
queries = []
queries.append('DROP TABLE IF EXISTS ' + type_table_name + ';')
queries.append('DROP TABLE IF EXISTS ' + property_table_name + ';')
queries.append('CREATE TABLE ' + type_table_name +
'(entity varchar,type varchar);')
queries.append('CREATE TABLE ' + property_table_name +
'(entity varchar,property varchar,value varchar);')
mydb.executeManyQuery(con, queries, debug)
# process the data
tfile = open(tfilePath, 'r')
pfile = open(pfilePath, 'r')
# for the type file
query_t = 'insert into __table_name__(entity,type) VALUES(%s, %s)'
query_t = query_t.replace('__table_name__', type_table_name)
processFile(con, query_t, tfile, True)
# for property table
query_p = 'insert into __table_name__(entity,property,value) VALUES(%s, %s, %s)'
query_p = query_p.replace('__table_name__', property_table_name)
processFile(con, query_p, pfile, False)
# build index
queries = []
query = 'create index __indexName__ on __index__;'
queries.append(
query.replace('__indexName__', type_table_name + '_entity').replace(
'__index__', type_table_name + '(entity)'))
queries.append(
query.replace('__indexName__', type_table_name + '_type').replace(
'__index__', type_table_name + '(type)'))
queries.append(
query.replace('__indexName__',
property_table_name + '_entity').replace(
'__index__', property_table_name + '(entity)'))
queries.append(
query.replace('__indexName__',
property_table_name + '_property').replace(
'__index__', property_table_name + '(property)'))
mydb.executeManyQuery(con, queries, debug)
# close db
mydb.closeCon(con)