Python连接Mysql、Oracle、MongDB实例及批处理

用于个人备忘录，Myql，Oracle, Mongodb相关操作

•  Oracle

import cx_Oracle as cx

host = "10.0.0.0"
user = "usr"
port = "1521"
password = "password"
service_name = "orcl"

conn = cx.connect(user, password, "{}:{}/{}".format(host, port, service_name))
sql = """select * from table_name """

with conn.cursor() as a:
    a.execute(sql)
    lines = a.fetchone()
    for line in lines:
        print(line)

• Mysql

import pymysql

host = "10.0.0.0"
user = "root"
password = "password"
db = "dbname"

conn = pymysql.connect(host=host, user=user, password=password, db=db, charset='utf8')
sql = """select * from table_name """
with conn.cursor() as a:
    a.execute(sql)
    line = a.fetchall()

• MongoDB

import pymongo

host = "10.0.0.0"
port = "27017"
db = "db_name"
collection = "collections"

# 连接Mongo数据库服务
client = pymongo.MongoClient(host, port)
# 连接指定数据库
mydb = client[db]
# 连接指定表
mycol = mydb[collection]
# 查询数据
data = mycol.find_one()

• 注意

  • mysql通过指定数据库名来连接指定数据库，oracle通过指定service_name、sid来指定链接数据库-----(=-=  我理解的 写到这才理解的) 
  • mysql、oracle执行sql时可以使用fetchall(),fetchone(),fetchmany()
  • oracle还可以执行sql批处理（昨天听同事用，今天就看到了 ！ 下面开始学习）

Oracle 批处理

import cx_Oracle as cx

host = "10.0.0.0"
user = "usr"
port = "1521"
password = "password"
service_name = "orcl"

path = r"a.txt"
conn = cx.connect(user, password, "{}:{}/{}".format(host, port, service_name))
sql = """insert into table_name(nbr, date, complate_date, scode, name)  values(:1, :2,:3, :4, :5)"""
rows = []
with open(path, "r") as f:
    data = f.readlines()
    for item in data:
        rows.append(tuple(item.split("|")))
with conn.cursor() as a:
    a.executemany(sql, rows)
    lines = a.fetchone()
    for line in lines:
        print(line)