SQL – Check out columns and datatypes of an SQLite database table and PRAGMA

In SQLite, there is no direct equivalent of the describe() and info() functions in pandas for generating summary statistics. However, you can use various SQL queries to calculate similar statistics on an SQLite table. Here are a few examples:

1.
-- Count of rows
SELECT COUNT(*) FROM tablename;

2.
-- Summary statistics for a specific column
SELECT
MIN(longitude) AS longitude_minimum,
MAX(longitude) AS longitude_maximum,
AVG(longitude) AS longitude_mean,
MEDIAN(longitude) AS longitude_median,
SUM(longitude) AS longitude_sum,
COUNT(longitude) AS longitude_count
FROM tablename;

You can then construct separate queries for each column to calculate the desired statistics.

3.
-- Summary statistics for all columns
PRAGMA table_info(tablename);

This query will return information about the table's columns, including their names and data types.

You may need to write custom SQL queries or use external libraries to obtain more advanced statistics such as quartiles or mode.

In SQLite, PRAGMA is a special command that allows you to query or modify various aspects of the database engine's behavior and settings. The term "PRAGMA" stands for "PRogmAtic General-purpose Attribute" and it is used to manage database-specific settings and options.

Here are a few key points about PRAGMA in SQLite:

PRAGMA statements are used for querying and modifying SQLite-specific settings, behaviors, and metadata.

PRAGMA statements are executed using SQL syntax.

PRAGMA statements do not follow the traditional SQL pattern of returning result sets. Instead, they typically return a single row with a single column that contains the requested information.

PRAGMA statements can be used to retrieve information about tables, columns, indexes, and other database objects. They can also be used to change settings such as journaling mode, foreign key enforcement, and case sensitivity.

PRAGMA statements are database-specific and may not be supported by other database management systems (DBMS). They are designed specifically for SQLite and its unique features and behaviors.

Overall, PRAGMA statements in SQLite provide a way to access and modify database-specific settings and properties that are not covered by standard SQL statements. They are a powerful tool for managing and fine-tuning SQLite databases.