python之oracle数据库增删改查

import cx_Oracle
复制

conn = cx_Oracle.connect('xzt/xzt@localhost/testdb')#这里的顺序是用户名/密码@oracleserver的ip地址/数据库名字
  cur = conn.cursor()
  sql = "SELECT * FROM DUAL"
  cur.execute(sql)
  cur.close()
  conn.commit()


  conn.close()
复制

import cx_Oracle  
  
conn = cx_Oracle.connect('xzt/xzt@localhost/testdb')    
cursor = conn.cursor ()  
  
cursor.execute ("SELECT * FROM STUDENT_TB")  
rows = cursor.fetchall() #得到所有数据集 
for row in rows:  
    print("%d, %s, %s, %s" % (row[0], row[1], row[2], row[3]))#python3以上版本中print()要加括号用了
  
print("Number of rows returned: %d" % cursor.rowcount)  
  
cursor.execute ("SELECT * FROM STUDENT_TB")  
while (True):  
    row = cursor.fetchone() #逐行得到数据集
    if row == None:  
        break  
    print("%d, %s, %s, %s" % (row[0], row[1], row[2], row[3])) 
      
print("Number of rows returned: %d" % cursor.rowcount)
  
cursor.close ()  
conn.close ()
复制

import cx_Oracle  
  
conn = cx_Oracle.connect('xzp/xzp@localhost/testdb')    
cursor = conn.cursor()  
  
cursor.execute ("CREATE TABLE INSERTTEST(ID INT, C1 VARCHAR(50), C2 VARCHAR(50), C3 VARCHAR(50))")  
  
cursor.execute ("INSERT INTO INSERTTEST (ID, COL1, COL2, COL3)VALUES(1213412, 'asdfa', 'ewewe', 'sfjgsfg')")  
cursor.execute ("INSERT INTO INSERTTEST (ID, COL1, COL2, COL3)VALUES(12341, 'ashdfh', 'shhsdfh', 'sghs')")  
cursor.execute ("INSERT INTO INSERTTEST (ID, COL1, COL2, COL3)VALUES(123451235, 'werwerw', 'asdfaf', 'awew')")  
conn.commit() #这里一定要commit才行，要不然数据是不会插入的 
  
cursor.close()  
conn.close()
复制

import re
import urllib
import cx_Oracle
import urllib.request


def getHtml(url):
  page = urllib.request.urlopen(url)
  html= page.read()
  return html
def getNumber(html):
  reg = r'<li class="ball_red">(\d{2})</li>'
  reg2 = r'<li class="ball_blue">(\d{2})</li>'
  regqnumber = r'第 <font class="cfont2"><strong>(\d*)</strong></font>'
  number = re.compile(reg)
  numberblue = re.compile(reg2)
  qnumber = re.compile(regqnumber)
  numberlist = re.findall(number,html.decode('gbk'))
  numberblue = re.findall(numberblue,html.decode('gbk'))
  qnum = re.findall(qnumber,html.decode('gbk'))
  for number in numberblue:
    numberlist.append(number)
  for n in qnum:
    numberlist.append(n)
  print(numberlist)
  return numberlist


  #将查询到的号码入库
def RecodeToOracle(list):
  conn = cx_Oracle.connect('xzp/xzp@localhost/testdb.domain')
  cur = conn.cursor()
  sql = "INSERT INTO SSQ (REDNUM1,REDNUM2,REDNUM3,REDNUM4,REDNUM5,REDNUM6,BLUENUM,QNUMBER) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s')"%(list[0],list[1],list[2],list[3],list[4],list[5],list[6],list[7])
  cur.execute(sql)
  cur.close()
  conn.commit()#这里一定要提交，要不然是没有办法将数据入库的
  conn.close()#记得要关闭会话
def Geturl(html):
  reg = r'上一期：<a href="(.*)" target="_blank">'
  url = re.compile(reg)
  urllist = re.findall(url,html.decode('gbk'))
  if len(urllist)!=0:
    print(urllist[0])
    if urllist[0].index('http:')<0:
      urllist[0]='http:'+urllist[0]
    htmlbefore = getHtml(urllist[0])
    # print(htmlbefore)
    numberlist = getNumber(htmlbefore)
    print(len(numberlist))
    RecodeToOracle(numberlist)
    print(numberlist)
    Geturl(htmlbefore)
  else:  
    return


str1 = '网站地址'
html1 = getHtml(str1)
RecodeToOracle(getNumber(html1))
Geturl(html1)
复制