首页 > 数据库 >Python直连MySQL数据库

Python直连MySQL数据库

时间：2023-05-21 23:32:49浏览次数：44  
标签：直连 execute NAME Python cursor MySQL print id conn


       

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使用流程:

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

1. MySQLdb

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

安装

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

  • CentOS 
yum install gcc python-devel mysql-devel zlib-devel openssl-devel
yum install gcc python-devel mysql-devel zlib-devel openssl-devel
  • Ubuntu 
sudo apt-get install libmysqlclient-dev libmysqld-dev python-dev python-setuptools

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

pip install 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条.

实例

#!/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()
#!/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参数。

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
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 install pymysql
pip install pymysql

实例

#!/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
    print "%s %3s" % (desc[0][0], desc[1][0])

    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()
#!/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
    print "%s %3s" % (desc[0][0], desc[1][0])

    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: 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'}
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'}

参考：



标签：直连,execute,NAME,Python,cursor,MySQL,print,id,conn
From： https://blog.51cto.com/u_6186189/6320686

相关文章

  • Oracle与DB2、MySQL取前10条记录的对比
    Oralce的示例：1.最佳选择：利用分析函数row_number()over(partitionbycol1orderbycol2)比如想取出100-150条记录,按照tname排序selecttname,tabtypefrom(selecttname,tabtype,row_number()over(orderbytname)rnfromtab)whe......
  • python多进程踩过的坑
    转载：python多进程踩过的坑-简书(jianshu.com)背景算法离线测试的上线后，随着业务的增长，算法的构建越来越频繁，数量也越来越多，最近一个任务中就包含28个算法。随着压力的增大，算法离线测试需要算法并行测试来解决效率问题。成果#28个算法单进程执行时间Task1111417runs......
  • Python跨进程共享数据/对象
    转载：(14条消息)Python跨进程共享数据/对象_python多进程共享对象_alpha.5的博客-CSDN博客1.跨进程共享方式在multiprocess库中，跨进程对象共享有三种方式：（1）第一种仅适用于原生机器类型，即python.ctypes当中的类型，这种在mp库的文档当中称为sharedmemory方式，即通过共享内存共享对......
  • Python字符串的encode与decode
    首先要搞清楚，字符串在Python内部的表示是unicode编码.因此，在做编码转换时，通常需要以unicode作为中间编码，即先将其他编码的字符串解码（decode）成unicode，再从unicode编码（encode）成另一种编码。decode的作用是将其他编码的字符串转换成unicode编码，如str1.decode('gb2312')，表示将gb2312编......
  • mysql联合索引详解
    所有的MySQL列类型能被索引。在相关的列上的使用索引是改进SELECT操作性能的最好方法。一、前缀索引对于CHAR和VARCHAR列，你可以索引列的前缀。这更快并且比索引整个列需要较少的磁盘空间。在CREATETABLE语句中索引列前缀的语法看起来像这样：KEYindex_name(col_name(length))下面......
  • python解析XML
    xml简介XML全称ExtensibleMarkupLanguage，中文译为可扩展标记语言。XML之前有两个先行者：SGML和HTML，率先登场的是SGML，尽管它功能强大，但文档结构复杂，既不容易学也不易于使用，因此几个主要的浏览器厂商均拒绝支持SGML，这些因素限制了SGML在网上的传播性；1989年HTML登场，它继......
  • mysql与lightdb中的insert on duplicate/replace
    最近看pg中insert的实现源码，看到onconflict的excluded优点疑惑，顺带总结下mysql和pg中已存在更新、不存在插入的差异（注：oracle是mergeinto实现）。在mysql中的insertonduplicate和lightdb的onconflict是等价的。逻辑都是基于唯一约束进行已存在则更新，否则插入。insertrecordIF......
  • python datetime时区转换
    比如把格林威治时间转换为上海时间：fromdatetimeimportdatetimeimportpytzprint('格林威治时间:',datetime.now().strftime("%Y-%m-%d_%H-%M-%S"))print('上海时间:',datetime.now().astimezone(pytz.timezone("Asia/Shanghai")).strftime("......
  • Python 多进程之间共享变量
    转载：Python多进程之间共享变量-知乎(zhihu.com)Python多线程之间共享变量很简单，直接定义全局global变量即可。而多进程之间是相互独立的执行单元，这种方法就不可行了。不过Python标准库已经给我们提供了这样的能力，使用起来也很简单。但要分两种情况来看，一种是Process......
  • python类中调用类方法时，报错self参数未填
    转载：(14条消息)python类中调用类方法时，报错self参数未填。_追天一方的博客-CSDN博客又碰到了一个小错误比如一个类如下：classprint_number(object):def__init__(self,string="数字是"):self.string=stringdefprint_(self,ss=3):print("{}:{}".......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99