def __init__(self, databasename):
global connection, command
connection = Dispatch("ADODB.Connection")
command = Dispatch("ADODB.Command")
connection.Open(ADOdb.dsource % databasename)
command.ActiveConnection = connection
def acc2orcl(mdb,collectDate):
oConn = Dispatch('ADODB.Connection')
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+mdb+"SpiderResult.mdb;"
oConn.Open()
conn=cx_Oracle.connect('naiawii/naiawii@cdwii01')
cur=conn.cursor()
table='NETDATANEW'
td=datetime.date.today()
if oConn.State == adStateOpen:
print "Connected to the database: "+mdb[-4:-1]
oRS = Dispatch('ADODB.RecordSet')
oRS.ActiveConnection = oConn
sql="""select ID,Mid(用户ID,1,200),Mid(城市,1,600),Mid(标题,1,600),出处,主题内容
,Mid(出行天数,1,25),Mid(出发日期,1,10),Mid(目的地,1,600),Mid(发起人,1,200)
,Mid(发起人城市,1,10),Mid(主题发表时间,1,10),Mid(发起人Mail,1,50),Mid(发起人QQ,1,50)
,Mid(发起人联系方式,1,100),PageUrl,采集日期 from content where 采集日期>'"""+collectDate+" 16:00:00'"+\
" and 采集日期<'"+td.isoformat()+" 16:00:00'"
oRS.Open(sql,oConn)
param=[]
while not oRS.EOF:
content=oRS.Fields(5).Value.encode('utf8')
email=oRS.Fields(12).Value.encode('utf8')
qqnum=oRS.Fields(13).Value.encode('utf8')
phone=oRS.Fields(14).Value.encode('utf8')
if str(email)=='' or '@' not in str(email):
email=getRe(content,'gemail')
if str(qqnum)=='':
qqnum=getRe(content,'qqnum')
if str(phone)=='':
phone=getRe(content,'cphone')
if str(email)=='' and str(qqnum)!='':
email=qqnum+'@qq.com'
#print 'email: '+email+',qqnum: '+qqnum+',phone: '+phone
param.append({'id':oRS.Fields(0).Value,'userid':oRS.Fields(1).Value.encode('utf8')\
,'city':oRS.Fields(2).Value.encode('utf8'),'title':oRS.Fields(3).Value.encode('utf8')\
,'souloc':oRS.Fields(4).Value.encode('utf8'),'postcontent':content\
,'days':oRS.Fields(6).Value.encode('utf8'),'departdate':oRS.Fields(7).Value.encode('utf8')\
,'destination':oRS.Fields(8).Value.encode('utf8'),'postman':oRS.Fields(9).Value.encode('utf8')\
,'postcity':oRS.Fields(10).Value.encode('utf8'),'postdate':oRS.Fields(11).Value.encode('utf8')\
,'postemail':email,'qqnum':qqnum,'postcontact':phone,'pageurl':oRS.Fields(15).Value.encode('utf8')\
,'inputdate':oRS.Fields(16).Value.encode('utf8')})
oRS.MoveNext()
print len(param)
cur.setinputsizes(postcontent=cx_Oracle.CLOB)
cur.executemany('insert into netdatanew (id, userid, city,title, souloc,postcontent, days, departdate,destination, postman\
, postcity, postdate, postemail, qqnum, postcontact,pageurl,inputdate) values (:id, :userid, :city,:title\
, :souloc,:postcontent, :days, :departdate,:destination, :postman, :postcity, :postdate, :postemail, :qqnum, :postcontact\
, :pageurl, :inputdate)',param)
cur.close()
conn.commit()
conn.close()
oRS.Close()
oRS=None
else:
print "Failed to connect to the database."
def execute(conn, sql):
cmd = Dispatch("ADODB.Command")
try:
conn.Open()
cmd.ActiveConnection = conn
cmd.CommandText = sql
cmd.execute()
except Exception as ex:
print(ex)
for err in conn.Errors:
error_handler.print_error(err)
finally:
if conn.State == adoconstants.adStateOpen:
conn.Close()
def getCollectDate(mdb):
oConn = Dispatch('ADODB.Connection')
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+mdb+"SpiderResult.mdb;"
oConn.Open()
if oConn.State == adStateOpen:
oRS = Dispatch('ADODB.RecordSet')
oRS.ActiveConnection = oConn
getDate='select top 1 采集日期 from content order by 采集日期 desc'
oRS.Open(getDate,oConn)
while not oRS.EOF:
coll=oRS.Fields(0).Value.encode('utf8')
oRS.MoveNext()
print "采集日期："+coll
oRS.Close()
oRS=None
return coll[0:11]
else:
return '';
def query(query_string, **command_properties):
"""Auxiliary function to serve as a quick-and-dirty
wrapper round an ADO query
"""
command = Dispatch("ADODB.Command")
command.ActiveConnection = connection()
#
# Add any client-specified ADO command properties.
# NB underscores in the keyword are replaced by spaces.
#
# Examples:
# "Cache_results" = False => Don't cache large result sets
# "Page_size" = 500 => Return batches of this size
# "Time Limit" = 30 => How many seconds should the search continue
#
for k, v in command_properties.items():
command.Properties(k.replace("_", " ")).Value = v
command.CommandText = query_string
recordset, result = command.Execute()
while not recordset.EOF:
yield ADO_record(recordset)
recordset.MoveNext()
def query (query_string, **command_properties):
"""Auxiliary function to serve as a quick-and-dirty
wrapper round an ADO query
"""
command = Dispatch ("ADODB.Command")
command.ActiveConnection = connection ()
#
# Add any client-specified ADO command properties.
# NB underscores in the keyword are replaced by spaces.
#
# Examples:
# "Cache_results" = False => Don't cache large result sets
# "Page_size" = 500 => Return batches of this size
# "Time Limit" = 30 => How many seconds should the search continue
#
for k, v in command_properties.items ():
command.Properties (k.replace ("_", " ")).Value = v
command.CommandText = query_string
recordset, result = command.Execute ()
while not recordset.EOF:
yield ADO_record (recordset)
recordset.MoveNext ()
