python 连接数据库

 

#### 远程oracle

import cx_Oracle

conn = cx_Oracle.connect('userid/key@ip:端口号/service_name') 

cursor = conn.cursor()

result = cursor.execute('select * from table_name') # 在这里写sql

data = cursor.fetchone() #get one row

data_all = cursor.fetchall() # get all data

data_some = cursor.fetchmany(999) # get 999 rows

conn.close()

 

from hdfs import InsecureClient

from impala.dbapi import connect

conn = connect(host='99.999.999.99',port=1234,database='basename',auth_mechanism='auth_mechanism',user='username',password='password')

cur = conn.cursor()

cur.execute('select count(1) from tablename where skill is not null')

print(cur.fetchall())

cur.execute('select nideid,name,age,skill,afds,fesdc from tablename where skill=1')

data = cur.fetchall()

conn.close()

data = DataFrame(data)

data.columns = ['iiddddddd','nnnnnnnname','aggggge','skill','afds','fesdc']

 

 

 

### 本地mysql

import pymysql.cursors
conn = pymysql.connect(host = '127.0.0.1',port=3306,user='root',password='root',db='dbname')
cursor = conn.cursor()

sql1='select * from tablename'
cursor.execute(sql1)
# result = cursor.fetchone()
# data = cursor.fetchall()
result = cursor.fetchmany(3)
print(result)

import pandas as pd
sp_myd = pd.DataFrame(result)

sql2="select * from table2 where (ywlx='1' or ywlx='2');"
cursor.execute(sql2)
result = cursor.fetchmany(3)
print(result)
sp_kfgd = pd.DataFrame(result)

del sql1,sql2,result

cursor.close()
conn.close()