从 sqlite3 远程数据库读取 [英] Reading from sqlite3 remote databases
问题描述
在我的服务器中,我试图从一堆 sqlite3 数据库(从 Web 客户端发送)中读取数据并处理它们的数据.db 文件位于 S3 存储桶中,我有它们的 url,我可以在内存中打开它们.
In my server I'm trying to read from a bunch of sqlite3 databases (sent from web clients) and process their data. The db files are in an S3 bucket and I have their url and I can open them in memory.
现在的问题是 sqlite3.connect
只接受一个绝对路径字符串,我无法将内存中的文件传递给它.
Now the problem is sqlite3.connect
only takes an absolute path string and I can't pass to it a file in memory.
conn=sqlite3.connect() #how to pass file in memory or url
c=conn.cursor()
c.execute('''select * from data;''')
res=c.fetchall()
# other processing with res
推荐答案
SQLite 要求 数据库文件存储在磁盘上(它使用各种锁和分页技术).内存文件是不够的.
SQLite requires database files to be stored on disk (it uses various locks and paging techniques). An in-memory file will not suffice.
我会创建一个临时目录来保存数据库文件,将其写入该目录,然后连接到它.该目录也为 SQLite 提供了写入提交日志的空间.
I'd create a temporary directory to hold the database file, write it to that directory, then connect to it. The directory gives SQLite the space to write commit logs as well.
为了处理这一切,上下文管理器可能会有所帮助:
To handle all this, a context manager might be helpful:
import os.path
import shutil
import sqlite3
import sys
import tempfile
from contextlib import contextmanager
@contextmanager
def sqlite_database(inmemory_data):
path = tempfile.mkdtemp()
with open(os.path.join(path, 'sqlite.db'), 'wb') as dbfile:
dbfile.write(inmemory_data)
conn = None
try:
conn = sqlite3.connect(os.path.join(path, 'sqlite.db'))
yield conn
finally:
if conn is not None:
conn.close()
try:
shutil.rmtree(path)
except IOError:
sys.stderr.write('Failed to clean up temp dir {}'.format(path))
并将其用作:
with sqlite_database(yourdata) as connection:
# query the database
这会将内存中的数据写入磁盘,打开一个连接,让您使用该连接,然后在您之后进行清理.
This writes in-memory data to disk, opens a connection, lets you use that connection, and afterwards cleans up after you.
这篇关于从 sqlite3 远程数据库读取的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!