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数据库的方法,简单的介绍了数据库基本的操作,后续如有相关内容将持续更新。