Python SQLite3 – Horizontal Partitioning by String Column

pythonsqlite

I have a 30 GB SQLite 3 database with a table containing 25 million rows. I want to group the table by a string column and create a new table for every group of records, named after the string and containing all columns.

Is this possible with Python and SQLite 3? I could process chunks in Python, but is there a SQL command?

Start table

all_entries

a   b   c   d
hi  4   234 lala
bye 7   342 lulu
bye 5   433 abc
bye 4   234 abd
hi  56  435 abc
hi  3   455 a

Result Tables

bye

a   b   c   d
bye 7   342 lulu
bye 5   433 abc
bye 4   234 abd

hi

a   b   c   d
hi  4   234 lala
hi  56  435 abc
hi  3   455 a

Best Answer

This can be achieved as follows according to the official SQLite documentation.

For the bye table you could use the following:

CREATE TABLE bye AS 
SELECT a, 
       b,
       c,
       d,
FROM all_entries
WHERE a = 'bye'

...and for the hi table:

CREATE TABLE bye AS 
SELECT a, 
       b,
       c,
       d,
FROM all_entries
WHERE a = 'hi'

Reference: CREATE TABLE (SQL As Understood By SQLite)

If this is just a temporary solution for something you wish to achieve then add the TEMPORARY keyword:

CREATE TEMPORARY TABLE bye AS...

There are some limitations with this approach which are described in the referenced article.

A table created using CREATE TABLE AS has no PRIMARY KEY and no constraints of any kind. The default value of each column is NULL. The default collation sequence for each column of the new table is BINARY.

Tables created using CREATE TABLE AS are initially populated with the rows of data returned by the SELECT statement. Rows are assigned contiguously ascending rowid values, starting with 1, in the order that they are returned by the SELECT statement.

And you would possibly have to post a question on Stack Overflow for a Python solution.

Related Question