Merge data on in a SQLite database through Python

Question

I have managed to import multiple csv files (contained in one folder) into an SQLite database - thanks to the very useful feedback I received on my previous question on this forum.

A16_B1_T5 A16_B1_T6 contain data from the same sensor, measuring temperature and humidity. However, they have been collected at different times of the year, therefore they always have significant overlap (i.e. T5 might indicate data collected from April->October 2015, while T6 July->December 2015).

I am now trying to merge two or more tables (originally corresponding to separate csv files) into one. For the reference example, A16_B1_T5 and A16_B1_T6 should be merged into A16_B1_T(or A16_B1_TT). This would mean append as well as overwrite/delete duplicate data.

Any tips on how to do it? Original working code for batch importing csv into sqlite is the following:

import csv
import sqlite3
import glob
import os

def do_directory(dirname, db):
    for filename in glob.glob(os.path.join(dirname, '*.csv')):
        do_file(filename, db)

def do_file(filename, db):
        with open(filename) as f:
            with db:
                data = csv.DictReader(f)
                cols = data.fieldnames
                table=os.path.splitext(os.path.basename(filename))[0]

                sql = 'drop table if exists "{}"'.format(table)
                db.execute(sql)

                sql = 'create table "{table}" ( {cols} )'.format(
                    table=table,
                    cols=','.join('"{}"'.format(col) for col in cols))
                db.execute(sql)

                sql = 'insert into "{table}" values ( {vals} )'.format(
                    table=table,
                    vals=','.join('?' for col in cols))
                db.executemany(sql, (list(map(row.get, cols)) for row in data))

    if __name__ == '__main__':
        connection = sqlite3.connect('C:/ROAST/3_ANALYSIS/03_SQL-PY/primo.db')
do_directory('C:/ROAST/3_ANALYSIS/03_SQL-PY\A08_csv',connection)

jsbueno · Accepted Answer · 2016-06-08 23:50:47Z

0

You just have to create the new table and use two INSERT INTO <newtable> SEÇECT <collunmns_wanted> FROM <old_table_1> to the sqlite engine.

Full documentation to the INSERT is here: https://www.sqlite.org/lang_insert.html

edited Jun 8, 2016 at 23:50

answered Jun 8, 2016 at 15:52

jsbueno

113k11 gold badges159 silver badges239 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Andreuccio Over a year ago

jsbueno, thanks for your answer. Can that be implemented into the Python code, rather than used within the SQLite environment?

jsbueno Over a year ago

You just have to issue the SQL statement from within Python - as a parameter to db.execute

Andreuccio Over a year ago

Thanks again. However, it seems from your reply that this would create additional columns to the new table, which is not what I'd want. I'd like to have the same number of clumns and append/overwrite rows instead.

jsbueno Over a year ago

An insert statement can't modify a table's columns. The context of you rquestion suggests you are trying to achieve your results without having a full understanding of what is going on. I'd suggest you'd settle for some trial and error so you can learn some Python and some SQL in the process. Otherwise anyone answering you will have to either write the exact solution you have to type there, or write a full book-size answer covering the several subjects involved in your task. Just make a copy of your .db file before trying, and play along with it.

Andreuccio Over a year ago

Having said that, I have used your command and adjusted as follows: DROP TABLE IF EXISTS A16_B1_TT; CREATE TABLE IF NOT EXISTS A16_B1_TT (dataora TIMESTAMP, temp FLOAT, rh FLOAT); INSERT INTO A16_B1_TT SELECT * FROM A16_B1_T5; INSERT INTO A16_B1_TT SELECT * FROM A16_B1_T6; then I included the line: delete from A16_B1_TT where rowid not in (select max(rowid) from A16_B1_TT group by dataora); so that worked @jsbueno. Thank you for that!

|

Collectives™ on Stack Overflow

Merge data on in a SQLite database through Python

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related