百度360必应搜狗淘宝本站头条
tomcat 乱码无法与服务器建立安全连接是什么意思sysv-rc-confdocker映射容器目录到宿主机internet explorer 增强的安全配置已启用
当前位置：网站首页 > 技术文章 > 正文

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

nanshan 2025-03-12 18:53 11 浏览 0 评论

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)



相关推荐

ubuntu24.04下kubernetes1.30环境搭建

设置root用户密码#在Ubuntu系统中，默认情况下root用户是被禁用的（没有设置密码）#而是通过sudo命令让普通用户临时获取管理员权限,#如果需要启用或修改root密...

Canonical 在 Ubuntu 24.10 发布之前对 Snap 进行了更多改进

作为Ubuntu桌面临时工程总监，OliverSmith介绍了Ubuntu24.10的最新进展。在Ubuntu24.10功能冻结之前，GNOME47测试版已经登陆Ubuntu...

Ubuntu Touch OTA-5手机系统发布:细化电源配置等

IT之家8月2日消息，UBports基金会于7月30日发布UbuntuTouch20.04OTA-5版本更新，距离上次OTA-4更新发布相隔6个月时间。Ubuntu...

Ubuntu更契合英特尔酷睿Ultra，综合性能比Win11高15%

IT之家12月23日消息，英特尔本月推出酷睿UltraMeteorLake处理器，那么Win11和Ubuntu发行版两者时间，谁能更好地发挥其性能呢？国外科技媒体phoron...

针对英特尔酷睿CPU优化，Canonical发布Ubuntu实时内核

IT之家7月27日消息，Canonical今天宣布针对支持时序协调运算（TCC）和时间敏感网络（IEEETSN）的英特尔酷睿处理器，推出优化版实时Ubuntu内核。Canonical...

在Ubuntu/Debian上设置永久DNS域名服务器

在Linux上设置自定义DNS服务器可以提高性能和安全性，甚至可以通过DNS阻止一些使用地理屏蔽的网站。有几种方法可以做到这一点，包括在许多Linux发行版中包括的NetworkManagerGUI...

宣布延期:Ubuntu 24.04 LTS第一个版本发布推迟两周

Ubuntu开发团队原计划于8月19日星期四发布Ubuntu24.04.1LTS。然而，由于发现几个重大升级错误，发布被推迟。Ubuntu24.04.1LTS的新发布日期现定为...

Ubuntu系统已经十岁了 10月新推14.10版

|责编：李鑫比较非主流的Ubuntu系统刚刚推出了14.10版，同时大家也可能不知道其实它已经十岁了！它的第一次公布时间为2004年的10月呢。在这次十周年更新中，Ubuntu为用...

wsl2在休眠后的时间偏差问题的修复

笔记本电脑在日常使用中，常常会有进入休眠状态的情况。休眠对于wsl2而言，却造成了时间偏差的问题，休眠期间wsl2的时间停止了。这个问题的根治，需要等微软。本文提供的是一种简单的修复办法。ntp是网络...

基于Ubuntu22.04源码安装配置RabbitVCS过程记录

基于Ubuntu22.04源码安装配置RabbitVCS过程记录安装开始时间开始时间:2025年7月18日17:09(北京时间)系统:Ubuntu22.04用户:itgather时区:A...

GNOME 46桌面环境发布，Ubuntu 24.04 LTS和Fedora 40率先预装

IT之家3月21日消息，GNOME团队今天发布公告，正式推出代号为“Kathmandu”的GNOME46桌面环境，并已经开放下载。Fedora40发行版将于4月发布，率先预装...

如果大家同意的话 Ubuntu可能很快就会有一个新的垃圾桶图标

Ubuntu贡献者目前正在构思一个新的垃圾桶图标，该图标最早可能在10月份Ubuntu25.10发布时出现在Dock栏中。关于Ubuntu垃圾桶图标外观的讨论在2019年持续进...

Ubuntu 25.10 通过更安全地获取时间来提供进一步的安全性提升

Canonical宣布将从Ubuntu25.10开始使用一款名为chrony的软件，以实现更安全的时间管理。最终用户无需过于担心这一变化，但它将增强系统安全性，尤其是在加密操作和证书验证方...

Linux 修改系统时间的两种方式

一：更新系统时间的方式1、手动修改通过相关工具来手动修改系统的时间。2、自动同步使用NTP自动同步系统时间。二：手动修改系统时间1、date工具作用：显示和设置系统时间选项：-d<字符串&g...

Ubuntu计划下版本为RISC-V设置RVA23基线，大量硬件无法升级

IT之家7月14日消息，主要Linux发行版之一的Ubuntu计划在其接下来的一个大版本25.10中将对RISC-V处理器的准入门槛设置从此前的RVA20配置文件更新至最新...

取消回复欢迎 你 发表评论:

一周热门
最近发表
标签列表