blocks|key|1744510|text|下面是我为我的烧瓶应用程序编写的片段：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1744511|import+sqlite3
from+io+import+StringIO
++++
def+init_sqlite_db(app):
++++#+Read+database+to+tempfile
++++con+=+sqlite3.connect(app.config['SQLITE_DATABASE'])
++++tempfile+=+StringIO()
++++for+line+in+con.iterdump():
++++++++tempfile.write('%25s\n'+%25+line)
++++con.close()
++++tempfile.seek(0)

++++#+Create+a+database+in+memory+and+import+from+tempfile
++++app.sqlite+=+sqlite3.connect(":memory:")
++++app.sqlite.cursor().executescript(tempfile.read())
++++app.sqlite.commit()
++++app.sqlite.row_factory+=+sqlite3.Row|code-block|syntax|javascript|1744512|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is the snippet that I wrote for my flask application:
<pre class="lang-python prettyprint-override"><code>import sqlite3
from io import StringIO
 
def init_sqlite_db(app):
 # Read database to tempfile
 con = sqlite3.connect(app.config['SQLITE_DATABASE'])
 tempfile = StringIO()
 for line in con.iterdump():
 tempfile.write('%s\n' % line)
 con.close()
 tempfile.seek(0)

 # Create a database in memory and import from tempfile
 app.sqlite = sqlite3.connect(&quot;:memory:&quot;)
 app.sqlite.cursor().executescript(tempfile.read())
 app.sqlite.commit()
 app.sqlite.row_factory = sqlite3.Row
</code></pre>

blocks|key|546674|text|那sqlite3.Connection.backup(...)呢？“这种方法即使在被其他客户端访问时，或者通过相同的连接同时进行SQLite数据库的备份。”可用性:+SQLite+3.6.11或更高版本。新版本3.7。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|546675|import+sqlite3

source+=+sqlite3.connect('existing_db.db')
dest+=+sqlite3.connect(':memory:')
source.backup(dest)|code-block|syntax|javascript|546676|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.backup^0|1|U|1|U|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

What about <a href="https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.backup" rel="noreferrer"><code>sqlite3.Connection.backup(...)</code></a>? "This method makes a backup of a SQLite database even while it’s being accessed by other clients, or concurrently by the same connection." Availability: SQLite 3.6.11 or higher. New in version 3.7.

<pre><code>import sqlite3

source = sqlite3.connect('existing_db.db')
dest = sqlite3.connect(':memory:')
source.backup(dest)
</code></pre>

blocks|key|1744447|text|sqlite3.Connection.iterdump“返回一个迭代器以SQL文本格式转储数据库。在保存内存中的数据库以便以后恢复时非常有用。此函数提供与sqlite3+shell中的.dump命令相同的功能。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1744448|获取这样一个迭代器，并将基于磁盘的数据库转储到基于内存的数据库中，您就可以进行计算了。当计算完成后，只需将相反的方式转储回磁盘。|1744449|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.python.org/library/sqlite3.html#sqlite3.Connection.iterdump^0|0|R|2K|5|0|R|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]|$9|S|A|T|B|C]]|D|@$9|U|A|V|1|W]]|E|$]]|$1|F|3|G|5|6|7|X|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]]]

<a href="http://docs.python.org/library/sqlite3.html#sqlite3.Connection.iterdump" rel="noreferrer"><code>sqlite3.Connection.iterdump</code></a> "[r]eturns an iterator to dump the database in an SQL text format. Useful when saving an in-memory database for later restoration. This function provides the same capabilities as the <code>.dump</code> command in the sqlite3 shell."

Get such an iterator and dump the disk-based database into a memory-based one, and you're ready to compute. When the computation is done, just dump the other way around back to disk.

blocks|key|2099471|text|如果我们必须使用python包装器，那么就没有比这两个pass、read和we解决方案更好的解决方案了。但是从3.7.17版本开始，SQLite可以选择直接使用内存映射的I/O访问磁盘内容。方形mmap|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2099472|如果您想使用mmap，您必须使用C接口，因为没有包装器提供它。|2099473|还有另一种硬件解决方案，即内存disk.then，您有方便的文件IO和内存速度。|2099474|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlite.org/mmap.html^0|2N|6|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

if we must use a python wrapper,then there are no better solution than the two pass, read and write solution. 
but beginning with version 3.7.17, SQLite has the option of accessing disk content directly using memory-mapped I/O.<a href="http://www.sqlite.org/mmap.html" rel="noreferrer">sqlite mmap</a>

if you want to use mmap,you have to use the C interface since no wrapper provide it.

and there is another hardware solution,the memory disk.then you have the convenient file IO and the speed of memory.

blocks|key|2099409|text|以前已经回答过这个问题，包括在python中，如何在连接到sqlite之前将sqlite完全加载到内存中？中的代码示例。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2099410|您没有提到操作系统，但是Windows的一个优点是它默认为一个10+is的文件缓存，不管您有多少内存。(这在64+in的系统出现的日子里是有意义的)。此消息有几个链接：|2099411|http://marc.info/?l=sqlite-users&m=116743785223905&w=2|2099412|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/3826552|1^0|E|13|0|0|0|0|1I|1|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|V|8|@]|9|@$A|W|B|X|1|Y]]|C|$]]|$1|H|3|-4|5|6|7|Z|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]|P|$5|K|L|M|C|$N|G]]]]

This has already been answered before, including code examples at <a href="https://stackoverflow.com/questions/3826552">In python, how can I load a sqlite db completely to memory before connecting to it?</a>

You do not mention operating system, but one gotcha of Windows XP is that it defaults to a 10MB file cache, no matter how much memory you have. (This made sense in the days when systems came with 64MB etc). This message has several links:

<a href="http://marc.info/?l=sqlite-users&amp;m=116743785223905&amp;w=2" rel="nofollow noreferrer">http://marc.info/?l=sqlite-users&amp;m=116743785223905&amp;w=2</a>

blocks|key|1744523|text|下面是一种将SQLite数据库读入内存的相对简单的方法。根据您在操作数据方面的首选项，可以使用Pandas，也可以将表写入内存中的sqlite3数据库。类似地，在操作数据之后，可以使用相同的df.to_sqlite方法将结果存储回db表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1744524|import+sqlite3+as+lite
from+pandas.io.sql+import+read_sql
from+sqlalchemy+import+create_engine

engine+=+create_engine('sqlite://')
c+=+engine.connect()
conmem+=+c.connection
con+=+lite.connect('ait.sqlite',+isolation_level=None)+#Here+is+the+connection+to+<ait.sqlite>+residing+on+disk
cur+=+con.cursor()
sqlx+=+'SELECT+*+FROM+Table'
df+=+read_sql(sqlx,+con,+coerce_float=True,+params=None)+

#Read+SQLite+table+into+a+panda+dataframe
df.to_sql(con=conmem,+name='Table',+if_exists='replace',+flavor='sqlite')|code-block|syntax|javascript|1744525|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is a relatively simple way to read a SQLite db into memory. Depending upon your preferences with regard to manipulating data, you either use Pandas dataframe or write your table to a in-memory sqlite3 database. Similarly after manipulating your data you use the same df.to_sqlite approach to store your results back into a db table.

<pre><code>import sqlite3 as lite
from pandas.io.sql import read_sql
from sqlalchemy import create_engine

engine = create_engine('sqlite://')
c = engine.connect()
conmem = c.connection
con = lite.connect('ait.sqlite', isolation_level=None) #Here is the connection to &lt;ait.sqlite&gt; residing on disk
cur = con.cursor()
sqlx = 'SELECT * FROM Table'
df = read_sql(sqlx, con, coerce_float=True, params=None) 

#Read SQLite table into a panda dataframe
df.to_sql(con=conmem, name='Table', if_exists='replace', flavor='sqlite')
</code></pre>

blocks|key|2099515|text|对于solution的解决方案，当进程达到500+of时，我总是有一个Python3.7的MemoryError。只有使用sqlite3的备份功能(由Thinw友克提到)，我才能够加载和保存更大的SQLite数据库。同样，你也可以用3行代码来做同样的事情，这两种方法都可以。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2099516|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

With the solution of Cenk Alti, I always had a MemoryError with Python 3.7, when the process reached 500MB. Only with the use of the backup functionality of sqlite3 (mentioned by thinwybk), I was able to to load and save bigger SQLite databases. Also you can do the same with just 3 lines of code, both ways.

blocks|key|2099577|text|@thinwybk和吟唱者的答案都很好。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2099578|当您有多个连接到:memory:+sqlite数据库时，例如当使用SQLAlchemy和source.backup(dest)函数时，您可能最终不会将备份放置到“右”内存DB中。|style|CODE|2099579|这可以使用适当的连接字符串：https://stackoverflow.com/a/65429612/1617295修复，不涉及任何黑客攻击，不使用无文档的特性。|2099580|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/53253110/1617295|1|https://stackoverflow.com/a/37164336/1617295|2|https://stackoverflow.com/a/65429612/1617295^0|0|9|0|A|3|1|0|8|8|18|J|0|E|18|2|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@$A|W|B|X|1|Y]|$A|Z|B|10|1|11]]|C|$]]|$1|D|3|E|5|6|7|12|8|@$A|13|B|14|F|G]|$A|15|B|16|F|G]]|9|@]|C|$]]|$1|H|3|I|5|6|7|17|8|@]|9|@$A|18|B|19|1|1A]]|C|$]]|$1|J|3|-4|5|6|7|1B|8|@]|9|@]|C|$]]]|K|$L|$5|M|N|O|C|$P|Q]]|R|$5|M|N|O|C|$P|S]]|T|$5|M|N|O|C|$P|U]]]]

The answers of <a href="https://stackoverflow.com/a/53253110/1617295">@thinwybk</a> and <a href="https://stackoverflow.com/a/37164336/1617295">Crooner</a> are both excellent.
When you have multiple connections to <code>:memory:</code> sqlite databases, for instance when using SQLAlchemy together with the <code>source.backup(dest)</code> function, then you may end up in not placing the backup into the &quot;right&quot; memory DB.
This can be fixed using a proper connection string: <a href="https://stackoverflow.com/a/65429612/1617295">https://stackoverflow.com/a/65429612/1617295</a>
and does not involve any hack not use of undocumented features.

blocks|key|546474|text|sqlite支持内存数据库。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|546475|在python中，您将使用:memory:数据库名。|offset|length|style|BOLD|546476|也许您可以打开两个数据库(一个从文件中打开，一个是内存中的空数据库)，将所有内容从文件数据库迁移到内存中，然后进一步使用内存数据库进行计算。|546477|entityMap^0|0|D|8|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

sqlite supports in-memory databases.

In python, you would use a :memory: database name for that.

Perhaps you could open two databases (one from the file, an empty one in-memory), migrate everything from the file database into memory, then use the in-memory database further to do calculations.

I have an existing <code>sqlite3</code> db file, on which I need to make some extensive calculations. Doing the calculations from the file is painfully slow, and as the file is not large (~<code>10 MB</code>), so there should be no problem to load it into memory.

Is there a Pythonic way to load the existing file into memory in order to speed up the calculations?

How to load existing db file to memory in Python sqlite3?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个现有的sqlite3 db文件，需要对其进行一些广泛的计算。从文件中进行计算非常缓慢，而且由于文件不太大(~10 MB)，因此将其加载到内存中应该没有问题。是否有一种Pythonic方式将现有文件加载到内存中以加快计算速度？

问如何将现有db文件加载到Python sqlite3中的内存中？
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将现有db文件加载到Python sqlite3中的内存中？EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将现有db文件加载到Python sqlite3中的内存中？
EN