python sqlite3 multiple-databases

Question

I have the same data data spread out over many identical databases. I want to combine all this data into one database ( well over a million records)

I have connection objects and cursor objects from the donating database and the receiving database and am keeping careful track of them. Then I am doing a [SELECT * …] from the donating database and In the fetchall , trying to INSERT INTO the archive. This seems to work, but will never take variables: […. VALUES (557)] will nicely add 557. But [….VALUES (?)” (var_name))] never works.

import sqlite3

conn = sqlite3.connect ('small_no_sort_2')
c = conn.cursor()
conn_rec = sqlite3.connect ('may_22_2019_int_db')  # new database,,,table is first_table
c_rec = conn_rec.cursor()

def data_entry_col_three():
    c_rec.execute ("INSERT INTO first_table (column_five) VALUES   (?)", (var))
    conn_rec.commit()

c.execute ("SELECT column_five FROM first_table WHERE column_five > 1809200 and column_five < 1822000 ")
all_rows = c.fetchmany(15)
for row in all_rows:
    if (row[0]) > 1819700 and row[0]< 1822799:
        print (row[0])
        var = row[0]
        var = str(var)
        var = (var[0:7])
        var = int(var)
        print (type(var))
        print (".....")
        print (var)
        new_var = 2000
        data_entry_col_three()

Notice that I have fussed with the data types at some length. [data_entry_col_three] this function will take a number, but not a variable. The ……………….VALUES (33445) will work, but not …………………VALUES (?),”(var)).

Shawn · Accepted Answer · 2019-05-23 14:33:21Z

3

It's easier and more efficient to do it all in SQL.

Once you've opened the destination database, something like

ATTACH DATABASE 'source.db' AS source;
INSERT INTO main.first_table(column_five)
  SELECT column_five
  FROM source.first_table
  WHERE column_five BETWEEN 1819701 AND 1822798;
DETACH source;

will insert values from the source table in the given range into the destination table.

answered May 23, 2019 at 14:33

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

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

Comments

Serge Ballesta · Accepted Answer · 2019-05-23 15:00:20Z

Your code contains 2 problems.

First, execute expects a sequence as its second parameter. Here you pass (var) which is not a tuple according to Python internal parsing but simply parentheses around a value, so you are executing c_rec.execute ("INSERT INTO first_table (column_five) VALUES (?)", var)

You should change it to force the value to be seen as a tuple with a comma:

c_rec.execute ("INSERT INTO first_table (column_five) VALUES   (?)", (var,))

Next, you only process 15 rows (because of all_rows = c.fetchmany(15)). It is likely that none of them match the following if (row[0]) > 1819700 and row[0]< 1822799: and that code just does nothing. Immediate fix here:

all_rows = c.fetchall()

After those 2 fixes, the second database should have some values...

That being said, if all you want to do is copy value from one database to another one without any more processing, the solution from Shawn is certainly easier to maintain...

Collectives™ on Stack Overflow

python sqlite3 multiple-databases

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related