sqlite tips

@misc{
  borui2024,
  author = {borui},
  title = {sqlite tips},
  year = {2024},
  publisher = {borui's blog},
  journal = {borui's blog},
  url={https://borui/blog/2024-03-24-en-sqlite-tips}
}

Accessing data stored in SQLite using Python and Pandas

Using pandas, we can import results of a SQLite query into a dataframe. Note that you can use the same SQL commands / syntax that we used in the SQLite lesson. An example of using pandas together with sqlite is below:

import pandas as pd
import sqlite3

# Read sqlite query results into a pandas DataFrame
con = sqlite3.connect("data/portal_mammals.sqlite")
df = pd.read_sql_query("SELECT * from surveys", con)

# Verify that result of SQL query is stored in the dataframe
print(df.head())

con.close()

Accessing SQLite Databases Using Python and Pandas Last updated on 2023-05-18 | Edit this page https://datacarpentry.org/python-ecology-lesson/instructor/09-working-with-sql.html

Using SQL with Pandas DataFrames David Fagbuyiro Oct 17, 2023 https://medium.com/@davidfagb/using-sql-with-pandas-dataframes-1c36f57ea65d

The issue comes with Sqlite's employs a one database per file method

A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.

• About sqlite. (n.d.). sqlite. Retrieved April 2, 2024, from https://www.sqlite.org/about.html

• thameera. (Feb 23, 2012). Afaik, SQLite stores a single database in a single file. Since this would decrease the performance when working with large databases, is it possible to explicitly tell SQLite not to store the whole DB in a single file and store different tables in different files instead?. [Question]. stackoverflow. https://stackoverflow.com/questions/9410011/multiple-files-for-a-single-sqlite-database

Maximum Database Size

Every database consists of one or more "pages". Within a single database, every page is the same size, but different databases can have page sizes that are powers of two between 512 and 65536, inclusive. The maximum size of a database file is 4294967294 pages. At the maximum page size of 65536 bytes, this translates into a maximum database size of approximately 1.4e+14 bytes (281 terabytes, or 256 tebibytes, or 281474 gigabytes or 256,000 gibibytes).

This particular upper bound is untested since the developers do not have access to hardware capable of reaching this limit. However, tests do verify that SQLite behaves correctly and sanely when a database reaches the maximum file size of the underlying filesystem (which is usually much less than the maximum theoretical database size) and when a database is unable to grow due to disk space exhaustion.

📓 Note: Other databases such as mysql has this theoretical limitations for db files of similiar range .

• Limits in sqlite. (n.d.). sqlite. Retrieved April 2, 2024, from https://www.sqlite.org/limits.html

How do you join tables from different databases?

If ATTACH is activated in your build of Sqlite (it should be in most builds), you can attach another database file to the current connection using the ATTACH keyword. The limit on the number of db's that can be attached is a compile time setting(SQLITE_MAX_ATTACHED), currently defaults to 10, but this too may vary by the build you have. The global limit is 125.

attach 'database1.db' as db1;
attach 'database2.db' as db2;

You can see all connected databases with keyword

sqlite> .tables.databases

Then you should be able to do the following.

select
  *
from
  db1.SomeTable a
    inner join 
  db2.SomeTable b on b.SomeColumn = a.SomeColumn;
Note that "[t]he database names main and temp are reserved for the primary database and database to hold temporary tables and other temporary data objects. Both of these database names exist for every database connection and should not be used for attachment".

• Brian Gideon. (Jul 26, 2011). If ATTACH is activated in your build of Sqlite (it should be in most builds), you can attach another database file to the current connection using the ATTACH keyword. The limit on the number of db's that can be attached is a compile time setting. [Answer]. stackoverflow. https://stackoverflow.com/questions/6824717/sqlite-how-do-you-join-tables-from-different-databases/6824831#6824831

Maximum Number Of Attached Databases

The ATTACH statement is an SQLite extension that allows two or more databases to be associated to the same database connection and to operate as if they were a single database. The number of simultaneously attached databases is limited to SQLITE_MAX_ATTACHED which is set to 10 by default. The maximum number of attached databases cannot be increased above 125.

The maximum number of attached databases can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_ATTACHED,size) interface.