Python连接多种数据库的方式

       本次主要介绍3种数据库（MySQL、PostgreSQL、oracle）如何用Python连接。

一、MySQL

       MySQL这里介绍三种Python的库来连接，分别是sqlalchemy、MySQLdb、PyMySQL。

1、sqlalchemy

       sqlalchemy是python中著名的orm框架，通过这个框架可以不用关心sql语句，就能操作数据库。大大的提高开发效率。当然通过orm来操作数据库会执行很多的数据库冗余操作，降低程序的运行效率。

安装

       直接使用pip安装。

pip install sqlalchemy或者easy install sqlalchemy

       在Python3.6之后似乎使用MySQLdb不是很方便，索性就用pymysql了。

示例

from sqlalchemy import create_engine
## 地址为127.0.0.1，数据库名称为tipdm
engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/tipdm?charset=utf8')
data = pd.read_sql('select * from all_gzdata',con=engine)

2、MySQLdb

       MySQLdb是用于Python链接Mysql数据库的接口，它实现了Python 数据库API规范V2.0，基于MySQL C API上建立的。

安装

       直接使用pip安装，MySQLdb模块的名字在pip上叫MySQL-python。

pip install MySQL-python

常用函数

       Python DB API 2.0 对事务提供了两个方法：

• commit() 提交
• rollback() 回滚

       cursor用来执行命令的方法:

• callproc(self, procname, args) 用来执行存储过程,接收的参数为存储过程名和参数列表，返回值为受影响的行数
• execute(self, query, args) 执行单条sql语句，接收的参数为sql语句本身和使用的参数列表，返回值为受影响的行数
• executemany(self, query, args) 执行单挑sql语句，但是重复执行参数列表里的参数,返回值为受影响的行数
• nextset(self) 移动到下一个结果集

       cursor用来接收返回值的方法:

• fetchall(self) 接收全部的返回结果行
• fetchmany(self, size=None) 接收size条返回结果行。如果size的值大于返回的结果行的数量，则会返回cursor.arraysize条数据
• fetchone(self) 返回一条结果行
• rowcount 这是一个只读属性，并返回执行execute() 方法后影响的行数
• scroll(self, value, mode='relative') 移动指针到某一行；如果mode='relative'，则表示从当前所在行移动value条，如果 mode='absolute'，则表示从结果集的第一行移动value条

示例

import MySQLdb as mdb
# 连接数据库
conn = mdb.connect('localhost', 'root', 'root')

conn = mdb.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='test', charset='utf8')# 可以使用关键字参数

config = {'host': '127.0.0.1','port': 3306,'user': 'root','passwd': 'root','db': 'test','charset': 'utf8'}# 可以使用字典进行连接参数的管理
conn = mdb.connect(**config)

# 如果使用事务引擎，可以设置自动提交事务，或者在每次操作完成后手动提交事务
conn.autocommit(1)    # conn.autocommit(True)

# 使用cursor()方法获取操作游标
cursor = conn.cursor()

try:
    # 创建数据库
    DB_NAME = 'test'
    cursor.execute('DROP DATABASE IF EXISTS %s' %DB_NAME)
    cursor.execute('CREATE DATABASE IF NOT EXISTS %s' %DB_NAME)
    conn.select_db(DB_NAME)

    #创建表
    TABLE_NAME = 'user'
    cursor.execute('CREATE TABLE %s(id int primary key,name varchar(30))' %TABLE_NAME)

    # 插入单条数据
    sql = 'INSERT INTO user values("%d","%s")' %(1,"jack")

    # 不建议直接拼接sql，占位符方面可能会出问题，execute提供了直接传值
    value = [2,'John']
    cursor.execute('INSERT INTO test values(%s,%s)',value)

    # 批量插入数据
    values = []
    for i in range(3, 20):
        values.append((i,'kk'+str(i)))
    cursor.executemany('INSERT INTO user values(%s,%s)',values)

    # 查询数据条目
    count = cursor.execute('SELECT * FROM %s' %TABLE_NAME)
    print('total records: %d' %count)
    print('total records:', cursor.rowcount)

    # 获取表名信息
    desc = cursor.description
    print("%s %3s" % (desc[0][0], desc[1][0]))

    # 查询一条记录
    print('fetch one record:')
    result = cursor.fetchone()
    print(result)
    print('id: %s,name: %s' %(result[0],result[1]))

    # 查询多条记录
    print('fetch five record:')
    results = cursor.fetchmany(5)
    for r in results:
        print(r)

    # 查询所有记录
    # 重置游标位置，偏移量:大于0向后移动;小于0向前移动，mode默认是relative
    # relative:表示从当前所在的行开始移动; absolute:表示从第一行开始移动
    cursor.scroll(0,mode='absolute')
    results = cursor.fetchall()
    for r in results:
        print(r)

    cursor.scroll(-2)
    results = cursor.fetchall()
    for r in results:
        print(r)

    # 更新记录
    cursor.execute('UPDATE %s SET name = "%s" WHERE id = %s' %(TABLE_NAME,'Jack',1))
    # 删除记录
    cursor.execute('DELETE FROM %s WHERE id = %s' %(TABLE_NAME,2))

    # 如果没有设置自动提交事务，则这里需要手动提交一次
    conn.commit()
except:
    import traceback
    traceback.print_exc()
    # 发生错误时会滚
    conn.rollback()
finally:
    # 关闭游标连接
    cursor.close()
    # 关闭数据库连接
    conn.close()

3、PyMySQL

       PyMySQL是一个纯Python写的MySQL客户端，它的目标是替代MySQLdb，可以在CPython、PyPy、IronPython和Jython环境下运行。

       PyMySQL的性能和MySQLdb几乎相当，如果对性能要求不是特别的强，使用PyMySQL将更加方便。

       PyMySQL的使用方法和MySQLdb几乎一样。

安装

pip install pymysql

示例

import os
import pymysql
import pandas as pd
# 读取数据1
os.getcwd()
os.chdir('E:/')
db = pymysql.connect('localhost','root','123456','tipdm',charset='gbk')
# host: 这个是ip地址，因为我这里是本地的，所以填127.0.0.1，也可以填localhost。
# user：用户名，如果你也是本地的，就填root好了
# passwd：这个是密码
# db：这个是数据库名
# port：这个是端口，本地的一般都是3306
# charset：这个是编码方式，要和你数据库的编码方式一致，要不会连接失败
data = pd.read_sql('select * from all_gzdata',con=db)
db.close()           #关闭连接
# 读取数据2
try:
    db = pymysql.connect('localhost','root','123456','tipdm',charset='gbk')
    cursor = db.cursor() # 光标
# 这个是执行sql语句，返回的是影响的条数
    data = cursor.execute('SELECT * FROM `all_gzdata`')
    print(cursor.fetchone())
    print(data)
    cursor.close() # 关闭光标
except pymysql.Error as e:
    print(e)
    print('操作数据库失败')
finally:# 如果连接成功就要关闭数据库
    if db:
        db.close()

print(cursor.description)
new = dict(zip([x[0] for x in cursor.description],[x for x in cursor.fetchone()]))
print(new)
print(new['title'])
# 上面的只是一条数据的，如果是多条，就需要用到map函数
def new2dict(new):
    return dict(zip([x[0] for x in cursor.description],[x for x in new]))
news_list = list(map(new2dict,cursor.fetchall()))
print(news_list)

二、PostgreSQL

       PostgreSQL可以用Python psycopg2模块集成。 sycopg2是Python编程语言的PostgreSQL数据库的适配器。 其程序代码少，速度快，稳定。

安装

pip install psycopg2

示例

import psycopg2
import pandas as pd
con = psycopg2.connect(database="969368", user="postgres", password="root", host="10.16.15.18", port="5432")

data = pd.read_sql('select * from mm_bill',con=con)
data.to_csv('11.csv')
data.ix[1,:]
data.columns

三、oracle

       工作中使用的是Oracle数据库，Python有一个模块cx_Oracle可以与Oracle相连。

安装

       要使用cx_Oracle，就要先下载。

pip install cx_Oracle

示例

import pandas as pd
import cx_Oracle                                          #引用模块cx_Oracle
# select value from v$parameter where name like '%service_name%' ，查看服务名称
conn=cx_Oracle.connect('dbcustadm/test928@10.16.9.28:1521/crmdb30')    #连接数据库
data = pd.read_sql('select * from cparambase',con = conn) # 获取数据
conn.close()                                                 #关闭cursor

文章未经博主同意，禁止转载！