Python连接MySQL数据库-昊天SEO

文章目录

Python标准数据库接口为Python DB-API, Python DB-API为开发人员提供了数据库应用编程接口。

Python 数据库接口支持非常多的数据库,你可以选择适合你项目的数据库:

GadFly
mSQL
MySQL
PostgreSQL
Microsoft SQL Server 2000 Informix
Interbase Oracle Sybase

你可以访问Python数据库接口及API查看详细的支持数据库列表。

不同的数据库你需要下载不同的DB API模块,例如你需要访问Oracle数据库和Mysql数据,你需要下载Oracle和MySQL数据库模块。

DB-API是一个规范。它定义了一系列必须的对象和数据库存取方式, 以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口。

Python的DB-API,为大多数的数据库实现了接口,使用它连接各数据库后,就可以用相同的方式操作各数据库。

Python DB-API使用流程:

引入API模块。
获取与数据库的连接。
执行SQL语句和存储过程。
关闭数据库连接。

1. MySQLdb

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

安装

直接使用pip进行安装，在此之前需要安装一些系统依赖包。

CentOS

yum install gcc <a href="http://www.168seo.cn/python" title="python">python</a>-devel mysql-devel zlib-devel openssl-devel

1

yum install gcc python-devel mysql-devel zlib-devel openssl-devel
Ubuntu

sudo apt-get install libmysqlclient-dev libmysqld-dev <a href="http://www.168seo.cn/python" title="python">python</a>-dev python-setuptools

1

sudo apt-get install libmysqlclient-dev libmysqld-dev python-dev python-setuptools

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

pip install MySQL-python

1	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条.

实例

<span class="hljs-comment">#!/usr/bin/env python</span>
<span class="hljs-comment"># -*- coding: utf-8 -*-</span>
<span class="hljs-keyword">import</span> MySQLdb <span class="hljs-keyword">as</span> mdb

<span class="hljs-comment"># 连接数据库</span>
conn = mdb.connect(<span class="hljs-string">'localhost'</span>, <span class="hljs-string">'root'</span>, <span class="hljs-string">'root'</span>)

<span class="hljs-comment"># 也可以使用关键字参数</span>
conn = mdb.connect(host=<span class="hljs-string">'127.0.0.1'</span>, port=<span class="hljs-number">3306</span>, user=<span class="hljs-string">'root'</span>, passwd=<span class="hljs-string">'root'</span>, db=<span class="hljs-string">'test'</span>, charset=<span class="hljs-string">'utf8'</span>)

<span class="hljs-comment"># 也可以使用字典进行连接参数的管理</span>
config = {
    <span class="hljs-string">'host'</span>: <span class="hljs-string">'127.0.0.1'</span>,
    <span class="hljs-string">'port'</span>: <span class="hljs-number">3306</span>,
    <span class="hljs-string">'user'</span>: <span class="hljs-string">'root'</span>,
    <span class="hljs-string">'passwd'</span>: <span class="hljs-string">'root'</span>,
    <span class="hljs-string">'db'</span>: <span class="hljs-string">'test'</span>,
    <span class="hljs-string">'charset'</span>: <span class="hljs-string">'utf8'</span>
}
conn = mdb.connect(**config)

<span class="hljs-comment"># 如果使用事务引擎，可以设置自动提交事务，或者在每次操作完成后手动提交事务conn.commit()</span>
conn.autocommit(<span class="hljs-number">1</span>)    <span class="hljs-comment"># conn.autocommit(True) </span>

<span class="hljs-comment"># 使用cursor()方法获取操作游标</span>
cursor = conn.cursor()
<span class="hljs-comment"># 因该模块底层其实是调用CAPI的，所以，需要先得到当前指向数据库的指针。</span>

<span class="hljs-keyword">try</span>:
    <span class="hljs-comment"># 创建数据库</span>
    DB_NAME = <span class="hljs-string">'test'</span>
    cursor.execute(<span class="hljs-string">'DROP DATABASE IF EXISTS %s'</span> %DB_NAME)
    cursor.execute(<span class="hljs-string">'CREATE DATABASE IF NOT EXISTS %s'</span> %DB_NAME)
    conn.select_db(DB_NAME)

    <span class="hljs-comment">#创建表</span>
    TABLE_NAME = <span class="hljs-string">'user'</span>
    cursor.execute(<span class="hljs-string">'CREATE TABLE %s(id int primary key,name varchar(30))'</span> %TABLE_NAME)

    <span class="hljs-comment"># 插入单条数据</span>
    sql = <span class="hljs-string">'INSERT INTO user values("%d","%s")'</span> %(<span class="hljs-number">1</span>,<span class="hljs-string">"jack"</span>)

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

    <span class="hljs-comment"># 批量插入数据</span>
    values = []
    <span class="hljs-keyword">for</span> i <span class="hljs-keyword">in</span> range(<span class="hljs-number">3</span>, <span class="hljs-number">20</span>):
        values.append((i,<span class="hljs-string">'kk'</span>+str(i)))
    cursor.executemany(<span class="hljs-string">'INSERT INTO user values(%s,%s)'</span>,values)

    <span class="hljs-comment"># 查询数据条目</span>
    count = cursor.execute(<span class="hljs-string">'SELECT * FROM %s'</span> %TABLE_NAME)
    <span class="hljs-keyword">print</span> <span class="hljs-string">'total records: %d'</span> %count
    <span class="hljs-keyword">print</span> <span class="hljs-string">'total records:'</span>, cursor.rowcount

    <span class="hljs-comment"># 获取表名信息</span>
    desc = cursor.description
    <span class="hljs-keyword">print</span> <span class="hljs-string">"%s %3s"</span> % (desc[<span class="hljs-number">0</span>][<span class="hljs-number">0</span>], desc[<span class="hljs-number">1</span>][<span class="hljs-number">0</span>])

    <span class="hljs-comment"># 查询一条记录</span>
    <span class="hljs-keyword">print</span> <span class="hljs-string">'fetch one record:'</span>
    result = cursor.fetchone()
    <span class="hljs-keyword">print</span> result
    <span class="hljs-keyword">print</span> <span class="hljs-string">'id: %s,name: %s'</span> %(result[<span class="hljs-number">0</span>],result[<span class="hljs-number">1</span>])

    <span class="hljs-comment"># 查询多条记录</span>
    <span class="hljs-keyword">print</span> <span class="hljs-string">'fetch five record:'</span>
    results = cursor.fetchmany(<span class="hljs-number">5</span>)
    <span class="hljs-keyword">for</span> r <span class="hljs-keyword">in</span> results:
        <span class="hljs-keyword">print</span> r

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

    cursor.scroll(<span class="hljs-number">-2</span>)
    results = cursor.fetchall()
    <span class="hljs-keyword">for</span> r <span class="hljs-keyword">in</span> results:
        <span class="hljs-keyword">print</span> r

    <span class="hljs-comment"># 更新记录</span>
    cursor.execute(<span class="hljs-string">'UPDATE %s SET name = "%s" WHERE id = %s'</span> %(TABLE_NAME,<span class="hljs-string">'Jack'</span>,<span class="hljs-number">1</span>))
    <span class="hljs-comment"># 删除记录</span>
    cursor.execute(<span class="hljs-string">'DELETE FROM %s WHERE id = %s'</span> %(TABLE_NAME,<span class="hljs-number">2</span>))

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

100

101

102

103

#!/usr/bin/env python

# -*- coding: utf-8 -*-

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.commit()

conn.autocommit(1) # conn.autocommit(True)

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

cursor = conn.cursor()

# 因该模块底层其实是调用CAPI的，所以，需要先得到当前指向数据库的指针。

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()

查询时返回字典结构

MySQLdb默认查询结果都是返回tuple，通过使用不同的游标可以改变输出格式，这里传递一个cursors.DictCursor参数。

<span class="hljs-keyword">import</span> MySQLdb.cursors

conn = MySQLdb.connect(host=<span class="hljs-string">'localhost'</span>, user=<span class="hljs-string">'root'</span>, passwd=<span class="hljs-string">'root'</span>, db=<span class="hljs-string">'test'</span>, cursorclass=MySQLdb.cursors.DictCursor)
cursor = conn.cursor()

cursor.execute(<span class="hljs-string">'select * from user'</span>)
r = cursor.fetchall()
<span class="hljs-keyword">print</span> r
<span class="hljs-comment"># 当使用位置参数或字典管理参数时，必须导入MySQLdb.cursors模块</span>

<span class="hljs-comment"># 也可以用下面的写法</span>
<span class="hljs-keyword">import</span> MySQLdb <span class="hljs-keyword">as</span> mdb
conn  = mdb.connect(<span class="hljs-string">'localhost'</span>, <span class="hljs-string">'root'</span>, <span class="hljs-string">'root'</span>, <span class="hljs-string">'test'</span>)
cursor = conn.cursor(cursorclass=mdb.cursors.DictCursor)

cursor.execute(<span class="hljs-string">'select * from user'</span>)
r = cursor.fetchall()
<span class="hljs-keyword">print</span> r

import MySQLdb.cursors

conn = MySQLdb.connect(host='localhost', user='root', passwd='root', db='test', cursorclass=MySQLdb.cursors.DictCursor)

cursor = conn.cursor()

cursor.execute('select * from user')

r = cursor.fetchall()

print r

# 当使用位置参数或字典管理参数时，必须导入MySQLdb.cursors模块

# 也可以用下面的写法

import MySQLdb as mdb

conn = mdb.connect('localhost', 'root', 'root', 'test')

cursor = conn.cursor(cursorclass=mdb.cursors.DictCursor)

cursor.execute('select * from user')

r = cursor.fetchall()

print r

MySQLdb取回大结果集的技巧

普通的操作无论是fetchall()还是fetchone()都是先将数据载入到本地再进行计算，大量的数据会导致内存资源消耗光。解决办法是使用SSCurosr光标来处理。

然而，在python3下，MySQLdb模块不再提供支持，此时可以使用另一个模块PyMySQL，它支持python2和python3。

2. PyMySQL

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

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

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

安装

pip <span class="hljs-keyword">install</span> pymysql

1	pip <span class="hljs-keyword">install</span> pymysql

实例

<span class="hljs-comment">#!/usr/bin/env python</span>
<span class="hljs-comment"># -*- coding: utf-8 -*-</span>
<span class="hljs-keyword">import</span> pymysql

config = {
    <span class="hljs-string">'host'</span>: <span class="hljs-string">'127.0.0.1'</span>,
    <span class="hljs-string">'port'</span>: <span class="hljs-number">3306</span>,
    <span class="hljs-string">'user'</span>: <span class="hljs-string">'root'</span>,
    <span class="hljs-string">'passwd'</span>: <span class="hljs-string">'root'</span>,
    <span class="hljs-string">'charset'</span>:<span class="hljs-string">'utf8mb4'</span>,
    <span class="hljs-string">'cursorclass'</span>:pymysql.cursors.DictCursor
    }
conn = pymysql.connect(**config)
conn.autocommit(<span class="hljs-number">1</span>)
cursor = conn.cursor()

<span class="hljs-keyword">try</span>:
    <span class="hljs-comment"># 创建数据库</span>
    DB_NAME = <span class="hljs-string">'test'</span>
    cursor.execute(<span class="hljs-string">'DROP DATABASE IF EXISTS %s'</span> %DB_NAME)
    cursor.execute(<span class="hljs-string">'CREATE DATABASE IF NOT EXISTS %s'</span> %DB_NAME)
    conn.select_db(DB_NAME)

    <span class="hljs-comment">#创建表</span>
    TABLE_NAME = <span class="hljs-string">'user'</span>
    cursor.execute(<span class="hljs-string">'CREATE TABLE %s(id int primary key,name varchar(30))'</span> %TABLE_NAME)

    <span class="hljs-comment"># 批量插入纪录</span>
    values = []
    <span class="hljs-keyword">for</span> i <span class="hljs-keyword">in</span> range(<span class="hljs-number">20</span>):
        values.append((i,<span class="hljs-string">'kk'</span>+str(i)))
    cursor.executemany(<span class="hljs-string">'INSERT INTO user values(%s,%s)'</span>,values)

    <span class="hljs-comment"># 查询数据条目</span>
    count = cursor.execute(<span class="hljs-string">'SELECT * FROM %s'</span> %TABLE_NAME)
    <span class="hljs-keyword">print</span> <span class="hljs-string">'total records:'</span>, cursor.rowcount

    <span class="hljs-comment"># 获取表名信息</span>
    desc = cursor.description
    <span class="hljs-keyword">print</span> <span class="hljs-string">"%s %3s"</span> % (desc[<span class="hljs-number">0</span>][<span class="hljs-number">0</span>], desc[<span class="hljs-number">1</span>][<span class="hljs-number">0</span>])

    cursor.scroll(<span class="hljs-number">10</span>,mode=<span class="hljs-string">'absolute'</span>)
    results = cursor.fetchall()
    <span class="hljs-keyword">for</span> result <span class="hljs-keyword">in</span> results:
        <span class="hljs-keyword">print</span> result

<span class="hljs-keyword">except</span>:
    <span class="hljs-keyword">import</span> traceback
    traceback.print_exc()
    <span class="hljs-comment"># 发生错误时会滚</span>
    conn.rollback()
<span class="hljs-keyword">finally</span>:
    <span class="hljs-comment"># 关闭游标连接</span>
    cursor.close()
    <span class="hljs-comment"># 关闭数据库连接</span>
    conn.close()

#!/usr/bin/env python

# -*- coding: utf-8 -*-

import pymysql

config = {

'host': '127.0.0.1',

'port': 3306,

'user': 'root',

'passwd': 'root',

'charset':'utf8mb4',

'cursorclass':pymysql.cursors.DictCursor

}

conn = pymysql.connect(**config)

conn.autocommit(1)

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)

# 批量插入纪录

values = []

for i in range(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:', cursor.rowcount

# 获取表名信息

desc = cursor.description

cursor.scroll(10,mode='absolute')

results = cursor.fetchall()

for result in results:

print result

except:

import traceback

traceback.print_exc()

# 发生错误时会滚

conn.rollback()

finally:

# 关闭游标连接

cursor.close()

# 关闭数据库连接

conn.close()

输出结果：

total records: <span class="hljs-number">20</span>
id name
{<span class="hljs-string">u'id'</span>: <span class="hljs-number">10</span>, <span class="hljs-string">u'name'</span>: <span class="hljs-string">u'kk10'</span>}
{<span class="hljs-string">u'id'</span>: <span class="hljs-number">11</span>, <span class="hljs-string">u'name'</span>: <span class="hljs-string">u'kk11'</span>}
{<span class="hljs-string">u'id'</span>: <span class="hljs-number">12</span>, <span class="hljs-string">u'name'</span>: <span class="hljs-string">u'kk12'</span>}
{<span class="hljs-string">u'id'</span>: <span class="hljs-number">13</span>, <span class="hljs-string">u'name'</span>: <span class="hljs-string">u'kk13'</span>}
{<span class="hljs-string">u'id'</span>: <span class="hljs-number">14</span>, <span class="hljs-string">u'name'</span>: <span class="hljs-string">u'kk14'</span>}
{<span class="hljs-string">u'id'</span>: <span class="hljs-number">15</span>, <span class="hljs-string">u'name'</span>: <span class="hljs-string">u'kk15'</span>}
{<span class="hljs-string">u'id'</span>: <span class="hljs-number">16</span>, <span class="hljs-string">u'name'</span>: <span class="hljs-string">u'kk16'</span>}
{<span class="hljs-string">u'id'</span>: <span class="hljs-number">17</span>, <span class="hljs-string">u'name'</span>: <span class="hljs-string">u'kk17'</span>}
{<span class="hljs-string">u'id'</span>: <span class="hljs-number">18</span>, <span class="hljs-string">u'name'</span>: <span class="hljs-string">u'kk18'</span>}
{<span class="hljs-string">u'id'</span>: <span class="hljs-number">19</span>, <span class="hljs-string">u'name'</span>: <span class="hljs-string">u'kk19'</span>}

total records: 20

id name

{u'id': 10, u'name': u'kk10'}

{u'id': 11, u'name': u'kk11'}

{u'id': 12, u'name': u'kk12'}

{u'id': 13, u'name': u'kk13'}

{u'id': 14, u'name': u'kk14'}

{u'id': 15, u'name': u'kk15'}

{u'id': 16, u'name': u'kk16'}

{u'id': 17, u'name': u'kk17'}

{u'id': 18, u'name': u'kk18'}

{u'id': 19, u'name': u'kk19'}

HTTPX 基础教程-新乡seo|网站优化,网站建设_微信公众号:zeropython—昊天博客

Python连接MySQL数据库

1. MySQLdb

安装

实例

查询时返回字典结构

MySQLdb取回大结果集的技巧

2. PyMySQL

安装

实例

linux删除大文件的前n行

搭建xunsearch 的一些坑

Python连接MySQL数据库

1. MySQLdb

安装

实例

查询时返回字典结构

MySQLdb取回大结果集的技巧

2. PyMySQL

安装

实例

linux删除大文件的前n行

搭建xunsearch 的一些坑

猜你喜欢

python 正则函数 re.sub 替换不完全的问题以及解决方法 – zlsgmail – 博客园

TinyProxy 搭建代理 爬虫

用 Python 发送 短信

Pytest AttributeError: ‘Function’ object has no attribute ‘get_marker’

爬虫 进程、线程和协程的结合使用

Arm 架构 Linux 安装编译 Python环境

关注我们的公众号

TinyProxy 搭建代理爬虫

用 Python 发送短信

爬虫进程、线程和协程的结合使用