百度360必应搜狗淘宝本站头条
外键约束 oraclephp简单源码宏程序代码一览表唯一索引 oraclelinux有没有pe工具
当前位置：网站首页 > 技术资源 > 正文

python散装笔记——161: 数据库访问

moboyou 2025-03-23 18:50 14 浏览

1: SQLite

SQLite是一种轻量级的、基于磁盘的数据库。由于它不需要单独的数据库服务器，因此通常用于原型设计或经常由单个用户或在给定时间内由一个用户使用的较小应用程序。

import sqlite3

conn = sqlite3.connect("users.db")
c = conn.cursor()

c.execute("CREATE TABLE user (name text, age integer)")

c.execute("INSERT INTO user VALUES ('User A', 42)")
c.execute("INSERT INTO user VALUES ('User B', 43)")

conn.commit()

c.execute("SELECT * FROM user")
print(c.fetchall())

conn.close()

上述代码连接到存储在名为users.db的文件中的数据库，如果该文件尚不存在，则会首先创建该文件。你可以通过SQL语句与数据库进行交互。

此示例的结果应为：

[(u'User A', 42), (u'User B', 43)]

SQLite语法：深入分析

开始使用

  1. 使用以下命令导入sqlite模块：
>>> import sqlite3
  1. 要使用该模块，你必须首先创建一个Connection对象，该对象代表数据库。这里的数据将存储在example.db文件中：
>>> conn = sqlite3.connect('users.db')

或者，你也可以提供特殊名称:memory:来创建一个临时的内存数据库，如下所示：

>>> conn = sqlite3.connect(':memory:')
  1. 一旦有了Connection，你就可以创建一个Cursor对象，并调用其execute()方法来执行SQL命令：
c = conn.cursor()

# Create table
c.execute('''CREATE TABLE stocks
(date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
conn.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
conn.close()

Connection的重要属性和函数

  1. isolation_level

这是一个用于获取或设置当前隔离级别的属性。可以是None（自动提交模式），或者是DEFERRED、IMMEDIATE或EXCLUSIVE之一。

  1. cursor

cursor对象用于执行SQL命令和查询。

  1. commit()

提交当前事务。

  1. rollback()

回滚自上次调用commit()以来所做的任何更改。

  1. close()

关闭数据库连接。它不会自动调用commit()。如果在未调用commit()的情况下（假设你不在自动提交模式下）关闭了连接，那么所有更改都将丢失。

  1. total_changes

一个记录自数据库打开以来修改、删除或插入的总行数的属性。

  1. execute、executemany和executescript

这些函数的执行方式与cursor对象的相同。这其实是一个快捷方式，因为通过Connection对象调用这些函数会导致创建一个中间的cursor对象，并调用该cursor对象的相应方法。

  1. row_factory

你可以将此属性更改为一个可调用对象，该对象接受cursor和原始行作为元组，并将返回实际的结果行。

def dict_factory(cursor, row):
  d = {}
  for i, col in enumerate(cursor.description):
    d[col[0]] = row[i]
  return d

conn = sqlite3.connect(":memory:")
conn.row_factory = dict_factory

Cursor的重要函数

  1. execute(sql[, parameters])

执行单个SQL语句。SQL语句可以参数化（即使用占位符而不是SQL字面量）。sqlite3模块支持两种占位符：问号?（“qmark风格”）和命名占位符:name（“命名风格”）。

import sqlite3
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("create table people (name, age)")

who = "Sophia"
age = 37
# This IS the qmark STYLE:
cur.execute("insert into people values (?, ?)", (who, age))

# AND this IS the named STYLE:
cur.execute("select * from people where name=:who and age=:age", {"who": who, "age": age}) 
# the KEYS correspond TO the placeholders IN SQL

print(cur.fetchone())

注意：不要使用%s将字符串插入SQL命令中，因为这会使你的程序容易受到SQL注入攻击（参见SQL注入）。

  1. executemany(sql, seq_of_parameters)

对序列sql中的所有参数序列或映射执行SQL命令。

sqlite3模块还允许使用返回参数的迭代器而不是序列。

L = [(1, 'abcd', 'dfj', 300), # A list OF tuples TO be inserted INTO the DATABASE
     (2, 'cfgd', 'dyfj', 400),
     (3, 'sdd', 'dfjh', 300.50)]

conn = sqlite3.connect("test1.db")
conn.execute("create table if not exists book (id int, name text, author text, price real)")
conn.executemany("insert into book values (?, ?, ?, ?)", L)

for row in conn.execute("select * from book"):
  print(ROW)

你也可以将迭代器对象作为executemany的参数传递，该函数将迭代迭代器返回的每个值元组。迭代器必须返回一个值元组。

import sqlite3

class IterChars:
  def __init__(self):
    self.count = ord('a')

  def __iter__(self):
    return self

  def __next__(self): # (USE NEXT(SELF) FOR Python 2)
    if self.count > ord('z'):
      raise StopIteration
    self.count += 1
    return (chr(SELF.count - 1),)

conn = sqlite3.connect("abc.db")
cur = conn.cursor()
cur.execute("create table characters(c)")

theIter = IterChars()
cur.executemany("insert into characters(c) values (?)", theIter)

rows = cur.execute("select c from characters")
for row in rows:
  print(row[0]),
  1. executescript(sql_script)

这是一个非标准的便捷方法，用于一次性执行多个SQL语句。它首先发出一个COMMIT语句，然后执行作为参数传递的SQL脚本。

sql_script可以是str或bytes的实例。

import sqlite3
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.executescript(
  """
  create table person(firstname, lastname, age);
  create table book(title, author, published);
  
  insert into book(title, author, published) 
  values (
  'Dirk Gently''s Holistic Detective Agency',
  'Douglas Adams',
  1987
  );
  """)

以下函数与SQL中的SELECT语句一起使用。执行SELECT语句后，你可以将cursor视为迭代器，调用cursor的fetchone()方法来检索单个匹配行，或者调用fetchall()来获取匹配行的列表。

迭代器形式的示例：

import sqlite3
stocks = [('2006-01-05', 'BUY', 'RHAT', 100, 35.14),
          ('2006-03-28', 'BUY', 'IBM', 1000, 45.0),
          ('2006-04-06', 'SELL', 'IBM', 500, 53.0),
          ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)]
conn = sqlite3.connect(":memory:")
conn.execute("create table stocks (date text, buysell text, symb text, amount int, price real)")
conn.executemany("insert into stocks values (?, ?, ?, ?, ?)", stocks)
cur = conn.cursor()

for row in cur.execute('SELECT * FROM stocks ORDER BY price'):
  print(row)

# Output:
# ('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
# ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
# ('2006-04-06', 'SELL', 'IBM', 500, 53.0)
# ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)
  1. fetchone()

检索查询结果集的下一行，返回一个单一序列，如果没有更多数据可用则返回None。

cur.execute('SELECT * FROM stocks ORDER BY price')
i = cur.fetchone()
while(i):
  print(i)
  i = cur.fetchone()

# Output:
# ('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
# ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
# ('2006-04-06', 'SELL', 'IBM', 500, 53.0)
# ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)
  1. fetchmany(size=cursor.arraysize)

检索查询结果的下一批行（由size指定），返回一个列表。如果省略size，fetchmany返回单行。如果没有更多行可用，则返回空列表。

cur.execute('SELECT * FROM stocks ORDER BY price')
print(cur.fetchmany(2))

# Output:
# [('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)]
  1. fetchall()

检索查询结果的所有（剩余）行，返回一个列表。

cur.execute('SELECT * FROM stocks ORDER BY price')
print(cur.fetchall())

# Output:
# [('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.0), ('2006-04-06', 'SELL', 'IBM', 500, 53.0), ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)]

SQLite和Python数据类型

SQLite原生支持以下数据类型：NULL、INTEGER、REAL、TEXT、BLOB。

当从SQL移动到Python或反之亦然时，数据类型之间的转换如下：

None <-> NULL
int <-> INTEGER/INT
float <-> REAL/FLOAT
str <-> TEXT/VARCHAR(n)
bytes <-> BLOB

2: 使用MySQLdb访问MySQL数据库

首先，你需要使用connect方法创建一个与数据库的连接。之后，你需要一个游标，它将与该连接一起操作。

使用游标的execute方法与数据库交互，并不时地使用连接对象的commit方法提交更改。

完成所有操作后，不要忘记关闭游标和连接。

以下是一个包含你需要的一切的Dbconnect类。

import MySQLdb

class Dbconnect(object):
  
  def __init__(self):
    self.dbconection = MySQLdb.connect(host='host_example',
                                       port=int('port_example'),
                                       user='user_example',
                                       passwd='pass_example',
                                       db='schema_example')

    self.dbcursor = self.dbconection.cursor()

  def commit_db(self):
     elf.dbconection.commit()

  def close_db(self):
    self.dbcursor.close()
    self.dbconection.close()

与数据库交互非常简单。创建对象后，只需使用execute方法即可。

db = Dbconnect()
db.dbcursor.execute('SELECT * FROM %s' % 'table_example')

如果你想调用存储过程，请使用以下语法。注意，参数列表是可选的。

db = Dbconnect()
db.callproc('stored_procedure_name', [parameters] )

查询完成后，你可以通过多种方式访问结果。游标对象是一个生成器，可以检索所有结果或进行循环。

results = db.dbcursor.fetchall()
for individual_row in results:
  first_field = individual_row[0]

如果你想直接使用生成器进行循环：

for individual_row in db.dbcursor:
  first_field = individual_row[0]

如果你想将更改提交到数据库：

db.commit_db()

如果你想关闭游标和连接：

db.close_db()

3: 连接

创建连接

根据PEP 249，应使用connect()构造函数建立与数据库的连接，该函数返回一个Connection对象。此构造函数的参数取决于数据库。请参阅数据库特定的主题以了解相关的参数。

import MyDBAPI

con = MyDBAPI.connect(*database_dependent_args)

此连接对象具有四个方法：

1: close

con.close()

立即关闭连接。注意，如果调用了Connection.__del__方法，则连接将自动关闭。任何挂起的事务都将隐式回滚。

2: commit

con.commit()

将任何挂起的事务提交到数据库。

3: rollback

con.rollback()

回滚到任何挂起事务的开始。换句话说：这会取消对数据库的所有非提交事务。

4: cursor

cur = con.cursor()

返回一个Cursor对象。这用于在数据库上执行事务。

4: 使用psycopg2访问PostgreSQL数据库

psycopg2是最受欢迎的PostgreSQL数据库适配器，它既轻量级又高效。它是当前的PostgreSQL适配器实现。

它的主要特点是完全实现了Python DB API 2.0规范，并且线程安全（多个线程可以共享同一个连接）。

建立与数据库的连接并创建表

import psycopg2

# 建立与数据库的连接。
# 将参数值替换为数据库凭证。
conn = psycopg2.connect(database="testpython",
                        user="postgres",
                        host="localhost",
                        password="abc123",
                        port="5432")

# 创建一个游标。游标允许你执行数据库查询。
cur = conn.cursor()

# 创建一个表。初始化表名、列名和数据类型。
cur.execute(
  """CREATE TABLE FRUITS (
  id INT ,
  fruit_name TEXT,
  color TEXT,
  price REAL
  )""")

conn.commit()
conn.close()

向表中插入数据：

# 按照上述方式创建表后，向其中插入值。
cur.execute(
  """INSERT INTO FRUITS (id, fruit_name, color, price) 
  VALUES (1, 'Apples', 'green', 1.00)
  """)
cur.execute(
  """INSERT INTO FRUITS (id, fruit_name, color, price) 
  VALUES (1, 'Bananas', 'yellow', 0.80)
  """)

检索表数据：

# 设置查询并执行
cur.execute("""SELECT id, fruit_name, color, price FROM fruits""")

# 检索数据
rows = cur.fetchall()

# 对数据进行操作
for row in rows:
  print("ID = {} ".format(row[0]))
  print("FRUIT NAME = {}".format(row[1]))
  print("COLOR = {}".format(row[2]))
  print("PRICE = {}".format(row[3]))

上述代码的输出为：

ID = 1
NAME = Apples
COLOR = green
PRICE = 1.0

ID = 2
NAME = Bananas
COLOR = yellow
PRICE = 0.8

就这样，你已经掌握了psycopg2所需了解的一半内容！：）

5: Oracle数据库

先决条件：

  • cx_Oracle包 - 点击此处查看所有版本
  • Oracle即时客户端 - Windows x64，Linux x64

设置：

  • 安装cx_Oracle包：
sudo rpm -i
  • 解压Oracle即时客户端并设置环境变量：
ORACLE_HOME=
PATH=$ORACLE_HOME:$PATH
LD_LIBRARY_PATH=:$LD_LIBRARY_PATH

创建连接：

import cx_Oracle
class OraExec(object):
_db_connection = None
_db_cur = None
def __init__(self):
self._db_connection =
cx_Oracle.connect('/@:/')
self._db_cur = self._db_connection.cursor()

获取数据库版本：

ver = con.version.split(".")
print ver

示例：

# Output: ['12', '1', '0', '2', '0']

执行查询：SELECT

_db_cur.execute("select * from employees order by emp_id")
for result in _db_cur:
  print result

输出将以Python元组的形式呈现：

(10, 'SYSADMIN', 'IT-INFRA', 7)
(23, 'HR ASSOCIATE', 'HUMAN RESOURCES', 6)

执行查询：INSERT

_db_cur.execute("insert into employees(emp_id, title, dept, grade) values (31, 'MTS', 'ENGINEERING', 7)
_db_connection.commit()

在Oracle数据库中执行插入/更新/删除操作时，更改仅在你的会话中可用，直到发出提交命令。当更新的数据提交到数据库后，它才对其他用户和会话可用。

使用绑定变量执行查询：INSERT

参考

绑定变量使你能够使用新值重新执行语句，而无需重新解析语句的开销。绑定变量提高了代码的可重用性，并可以降低SQL注入攻击的风险。

rows = [ (1, "First" ),
        (2, "Second" ),
        (3, "Third" ) ]
_db_cur.bindarraysize = 3
_db_cur.setinputsizes(int, 10)
_db_cur.executemany("insert into mytab(id, data) values (:1, :2)", rows)
_db_connection.commit()

关闭连接：

_db_connection.close()

close()方法关闭连接。任何未明确关闭的连接将在脚本结束时自动释放。

6: 使用sqlalchemy

要使用sqlalchemy访问数据库：

from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL

url = URL(drivername='mysql',
          username='user',
          password='passwd',
          host='host',database='db')

engine = create_engine(url) # sqlalchemy engine

现在可以使用这个引擎了，例如与pandas一起使用，直接从mysql获取数据框：

import pandas as pd

con = engine.connect()
dataframe = pd.read_sql(sql=query, con=con)



相关推荐

Excel技巧:SHEETSNA函数一键提取所有工作表名称批量生产目录

首先介绍一下此函数：SHEETSNAME函数用于获取工作表的名称，有三个可选参数。语法：=SHEETSNAME([参照区域],[结果方向],[工作表范围])（参照区域，可选。给出参照，只返回参照单元格...

Excel HOUR函数:“小时”提取器_excel+hour函数提取器怎么用

一、函数概述HOUR函数是Excel中用于提取时间值小时部分的日期时间函数，返回0（12:00AM）到23（11:00PM）之间的整数。该函数在时间数据分析、考勤统计、日程安排等场景中应用广泛。语...

Filter+Search信息管理不再难|多条件|模糊查找|Excel函数应用

原创版权所有介绍一个信息管理系统，要求可以实现:多条件、模糊查找，手动输入的内容能去空格。先看效果，如下图动画演示这样的一个效果要怎样实现呢？本文所用函数有Filter和Search。先用filter...

FILTER函数介绍及经典用法12:FILTER+切片器的应用

EXCEL函数技巧：FILTER经典用法12。FILTER+切片器制作筛选按钮。FILTER的函数的经典用法12是用FILTER的函数和切片器制作一个筛选按钮。像左边的原始数据，右边想要制作一...

office办公应用网站推荐_office办公软件大全

以下是针对Office办公应用（Word/Excel/PPT等）的免费学习网站推荐，涵盖官方教程、综合平台及垂直领域资源，适合不同学习需求：一、官方权威资源1.微软Office官方培训...

WPS/Excel职场办公最常用的60个函数大全(含卡片)，效率翻倍!

办公最常用的60个函数大全：从入门到精通，效率翻倍！在职场中，WPS/Excel几乎是每个人都离不开的工具，而函数则是其灵魂。掌握常用的函数，不仅能大幅提升工作效率，还能让你在数据处理、报表分析、自动...

收藏|查找神器Xlookup全集|一篇就够|Excel函数|图解教程

原创版权所有全程图解，方便阅读，内容比较多，请先收藏！Xlookup是Vlookup的升级函数，解决了Vlookup的所有缺点，可以完全取代Vlookup，学完本文后你将可以应对所有的查找难题，内容...

批量查询快递总耗时?用Excel这个公式，自动计算揽收到签收天数

批量查询快递总耗时？用Excel这个公式，自动计算揽收到签收天数在电商运营、物流对账等工作中，经常需要统计快递“揽收到签收”的耗时——比如判断某快递公司是否符合“3天内送达”的服务承...

Excel函数公式教程(490个实例详解)

Excel函数公式教程（490个实例详解）管理层的财务人员为什么那么厉害？就是因为他们精通excel技能！财务人员在日常工作中，经常会用到Excel财务函数公式，比如财务报表分析、工资核算、库存管理等...

Excel(WPS表格)Tocol函数应用技巧案例解读，建议收藏备用!

工作中，经常需要从多个单元格区域中提取唯一值，如体育赛事报名信息中提取唯一的参赛者信息等，此时如果复制粘贴然后去重，效率就会很低。如果能合理利用Tocol函数，将会极大地提高工作效率。一、功能及语法结...

Excel中的SCAN函数公式，把计算过程理清，你就会了

Excel新版本里面，除了出现非常好用的xlookup，Filter公式之外，还更新一批自定义函数，可以像写代码一样写公式其中SCAN函数公式，也非常强大，它是一个循环函数，今天来了解这个函数公式的计...

Excel(WPS表格)中多列去重就用Tocol+Unique组合函数，简单高效

在数据的分析和处理中，“去重”一直是绕不开的话题，如果单列去重，可以使用Unique函数完成，如果多列去重，如下图：从数据信息中可以看到，每位参赛者参加了多项运动，如果想知道去重后的参赛者有多少人，该...

Excel(WPS表格)函数Groupby，聚合统计，快速提高效率!

在前期的内容中，我们讲了很多的统计函数，如Sum系列、Average系列、Count系列、Rank系列等等……但如果用一个函数实现类似数据透视表的功能，就必须用Groupby函数，按指定字段进行聚合汇...

Excel新版本，IFS函数公式，太强大了!

我们举一个工作实例，现在需要计算业务员的奖励数据，右边是公司的奖励标准：在新版本的函数公式出来之前，我们需要使用IF函数公式来解决1、IF函数公式IF函数公式由三个参数组成，IF(判断条件，对的时候返...

Excel不用函数公式数据透视表，1秒完成多列项目汇总统计

如何将这里的多组数据进行汇总统计？每组数据当中一列是不同菜品，另一列就是该菜品的销售数量。如何进行汇总统计得到所有的菜品销售数量的求和、技术、平均、最大、最小值等数据？不用函数公式和数据透视表，一秒就...

一周热门
最近发表
标签列表
蜀ICP备2024111239号-28