Python连接MySQL、Oracle数据库

1. Python链接MySQL数据库

• MySQLdb是用于Python链接Mysql数据库的接口，它实现了Python 数据库API规范V2.0，基于MySQL C API上建立的。`
• PyMySQL是一个纯Python写的MySQL客户端，它的目标是替代MySQLdb，可以在CPython、PyPy、IronPython和Jython环境下运行.
  下面主要介绍这两种方式链接MySQL数据库：

1.1 MySQLdb

链接MySQL的test数据库实例：

# 导入模块
import MySQLdb
# 打开数据库连接（1）：使用关键字参数
conn = MySQLdb.connect(host='127.0.0.1', port=3306,
                       user='root', passwd='root', db='test', charset='utf8')
# 打开数据库连接（2）：也可以使用字典进行连接参数的管理
config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'passwd': 'root',
    'db': 'test',
    'charset': 'utf8'
}
conn = MySQLdb.connect(**config)
# 以上两种方式根据需求选择
# 使用cursor()方法获取操作游标 
cursor = conn.cursor()

# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取一条数据
data1 = cursor.fetchone()
print "Database version : %s " % data1

# 使用 fetchall() 方法获取所有数据
data2 = cursor.fetchall()

# 如果没有设置自动提交事务，则这里需要手动提交一次
conn.commit()

# 关闭游标和数据库连接
cursor.close()
conn.close()

# 常用数据库操作语句：
# 注：MySQLdb默认查询结果都是返回tuple
desc = cursor.description     # 获取表名信息
result = cursor.fetchone()    # 查询一条记录
results = cursor.fetchmany(5) # 查询多条记录
results = cursor.fetchall()   # 查询所有记录
conn.commit()                 # 手动提交事务集
conn.rollback()               # 回滚当前游标的所有操作 

# MySQLdb一系列数据库相关操作
import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1', port=3306,
                       user='root', passwd='root', db='test', charset='utf8')
cursor = conn.cursor()

# （1）创建数据库表操作
sql1="""create table test_01(
		user_name char(20) not null,
		ident char(20) not null,
		sex char(20),
		age int,
		income float)"""
cursor.excute(sql1)

# (2)数据库插入操作
sql2="""insert into test_01(user_name,ident,sex,age,income) 	
		values('%s', '%s', '%c', '%d', '%d' )" % 
		('mac','44444','男'，12,2000))"""
try:
   cursor.execute(sql2)
   conn.commit() # 提交到数据库执行
except:
   conn.rollback() # 发生错误时会滚
   
# (3)数据库查询操作
sql3 = "select * from test_01 where income>'%d'" % (1000)
try:
   cursor.execute(sql3)
   results = cursor.fetchall()
   for row in results:
      user_name= row[0]
      ident= row[1]
      sex= row[2]
      age= row[3]
      income = row[4]
      print "user_naem=%s,ident=%s,sex=%s,age=%d,income=%d" % 
      (user_name, ident, sex, age, income )
except:
   print "Error: unable to fecth data"

cursor.close()
conn.close()

#（4）数据库更新、删除操作
# 只需将需要执行的sql放入cursor.execute(sql)中即可

1.2 PyMySQL

# 导入模块
import pymysql
# 链接MySQL数据库
def testMysql1():
    conn = pymysql.connect(host='127.0.0.1', port=3306,
                           user='root', passwd='root', db='test', charset='utf8')
    cursor = conn.cursor()
    cursor.execute("select * from user")
    result1 = cursor.fetchone() # result2 = cursor.fetchall()
    print(result1[0])
    cursor.close()
    conn.close() 
# 数据库操作语句与MySQLdb基本一致

import pymysql
# 封装成类
class MysqlConnect(object):
    # 魔术方法, 初始化, 构造函数
    def __init__(self, host, user, password, database):
        '''
        连接Mysql数据库
        :param host: IP
        :param user: 用户名
        :param password: 密码
        :param port: 端口号
        :param database: 数据库名
        :param charset: 编码格式
        '''
        self.db = pymysql.connect(host=host, user=user, password=password,port=3306, database=database, charset='utf8')
        self.cursor = self.db.cursor()

    # 将要插入的数据写成元组传入
    def exec_data(self, sql, data=None):
        # 执行SQL语句
        self.cursor.execute(sql, data)
        # 提交到数据库执行
        self.db.commit()

    # sql拼接时使用repr()，将字符串原样输出
    def exec(self, sql):
        self.cursor.execute(sql)
        # 提交到数据库执行
        self.db.commit()

    def select(self,sql):
        self.cursor.execute(sql)
        # 获取所有记录列表
        results = self.cursor.fetchall()
        for row in results:
            print(row)

    # 魔术方法, 析构化 ,析构函数
    def __del__(self):
        self.cursor.close()
        self.db.close()

if __name__ == '__main__':
    mc = MysqlConnect('127.0.0.1', 'root', '123456', 'homework')
    # mc.exec('insert into test(id, text) values(%s, %s)' % (1, repr('哈送到附近')))
    mc.exec_data('insert into test(id, text) values(%s, %s)' % (1, repr('哈送到附近')))
    # mc.exec_data('insert into test(id, text) values(%s, %s)',(13, '哈送到附近'))
    mc.select('select * from test')

import pymysql
import pandas as pd
import numpy as np
import time
import requests
import geohash

# 封装类改进
class MysqlConnect(object):
	# 魔术方法, 初始化, 构造函数
	def __init__(self, host, user, password, port, database, charset):
		"""
		连接Mysql数据库
		:param host: IP
		:param user: 用户名
		:param password: 密码
		:param port: 端口号
		:param database: 数据库名
		:param charset: 编码格式
		"""
		self.db = pymysql.connect(host=host, user=user, password=password, port=port, database=database,
								  charset=charset)
		self.cursor = self.db.cursor()

	# execute循环单条插入
	def exec_one(self, sql, param):
		try:
			self.cursor.execute(sql, param)
			self.db.commit()
		except Exception as e:
			print(e)
			self.db.rollback()  # 发生错误时会滚

	# executemany批量插入
	def exec_all(self, sql, param):
		try:
			self.cursor.executemany(sql, param)
			self.db.commit()
		except Exception as e:
			print(e)
			self.db.rollback()  # 发生错误时会滚

	# sql查询操作
	def select(self, sql):
		try:
			self.cursor.execute(sql)
			results = self.cursor.fetchall()
			return results
		# for row in results:
		# 	print(row)
		except Exception as e:
			print(e)

	# 数据库删除操作
	def delete(self, sql):
		try:
			self.cursor.execute(sql)
		except Exception as e:
			print(e)

	# 魔术方法, 析构化 ,析构函数
	def __del__(self):
		self.cursor.close()
		self.db.close()


if __name__ == '__main__':
	conn1 = MysqlConnect('127.0.0.1', 'root', '123456', 3306, 'homework1','utf8')
	conn2 = MysqlConnect('127.0.0.1', 'root', '123456', 3306, 'homework2','utf8')
	sql1 = """select * from test1 """
	sql2 = """delete from test2 """
	sql3 = "insert into test2 values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"

	start = time.process_time()
	result = conn1.select(sql1)

	param = []
	for i in range(len(result)):
		grid = (gaodecode(result[i][3], result[i][4]))
		param.append(
			(result[i][0], result[i][1], result[i][2], result[i][3], result[i][4], result[i][5], grid['province'],
			 grid['city'], grid['district'], grid['grid'], result[i][6], result[i][7],
			 result[i][8], result[i][9], result[i][10]))

	conn2.delete(sql2)
	conn2.exec_all(sql3, param) # sql模板字符串,param是一个列表，列表中的每一个元素必须是元组！！！

	end = time.process_time()
	print('[insert_by_many executemany] Time Usage:', end - start)

    # 单条插入
	# start = time.process_time()
	# result = conn1.select(sql1)
	# conn2.delete(sql2)
	# param = []
	# for i in range(len(result)):
	# 	grid = (gaodecode(result[i][3], result[i][4]))
	# 	param = (result[i][0], result[i][1], result[i][2], result[i][3], result[i][4], result[i][5], grid['province'],
	# 			 grid['city'], grid['district'], grid['grid'], result[i][6], result[i][7], result[i][8], result[i][9],
	# 			 result[i][10])
	# 	conn2.exec_one(sql3, param)
	# end = time.process_time()
	# print('[insert_by_many executemany] Time Usage:', end - start)

---

2. Python链接Oracle数据库

# 导入模块
import cx_Oracle
# 数据库连接函数
def testOracle():  
    # python安装的是多少位oracle客户端(instantclient_11_2)就是多少位  
    # conn = cx_Oracle.connect('用户名/密码@数据库ip:端口号/数据库名')  
    conn = cx_Oracle.connect('root/123456@127.0.0.1:1521/testDB')  
    cursor = conn.cursor()  
    cursor.execute('select sysdate from dual')  
    result = cursor.fetchone()  
    print('Database time:%s' % result) 
    cursor.close()  
    conn.close()
    
# 封装起来
import cx_Oracle 
# 读取需要执行的sql，返回执行函数
def oraclesql(cursor):
    fp = open(r'/home/oracle/scripts/tablespace.sql')
    fp_sql = fp.read()
    cursor.execute(fp_sql)
    data = cursor.fetchall()
    return data
    
if __name__ == '__main__':
    ipaddr = "192.168.223.138"
    username = "system"
    password = "redhat"
    oracle_port = "1521"
    oracle_service = "oracle.test"
    try:
        conn = cx_Oracle.connect(username + "/" + password + "@" +
                            ipaddr + ":" + oracle_port + "/" + oracle_service)
      # 将异常捕捉，然后错误就是抛异常的具体内容
    except Exception as e:
        print(e)
    else:
        cursor = conn.cursor()
        data = oraclesql(cursor)
        for i in data:
            print(i)
        cursor.close()
        conn.close()

到此主要介绍了python连接mysql及oracle数据库的方法，简单的介绍了数据库基本的操作，后续如有相关内容将持续更新。