0

I am new to both python and SQLite.

I have used python to extract data from xlsx workbooks. Each workbook is one series of several sheets and is its own database, but I would also like a merged database of every series together. The structure is the same for all.

The structure of my database is: *Table A with autoincrement primary key id, logical variable and 1 other variable.
*Table B autoincrement primary key id, logical variable and 4 other variables *Table C is joined by table A id and table B id, together the primary key, and also has 4 other variables specific to this instance of intersection between table A and table B.

I tried using the answer at Sqlite merging databases into one, with unique values, preserving foregin key relation

along with various other ATTACH solutions, but each time I got an error message ("cannot ATTACH database within transaction").

Can anyone suggest why I can't get ATTACH to work?

I also tried a ToMerge like the one at How can I merge many SQLite databases? and it couldn't do ToMerge in the transaction either.

I also initially tried connecting to the existing SQLite db, making dictionaries from the existing tables in python, then adding the information in the dictionaries into a new 'merged' db, but this actually seemed to be far slower than the original process of extracting everything from the xlsx files.

I realize I can easily just run my xlsx to SQL python script again and again for each series directing it all into the one big SQL database and that is my backup plan, but I want to learn how to do it the best, fastest way.

So, what is the best way for me to merge identical structured SQLite databases into one, maintaining my foreign keys.

TIA for any suggestions :-)L

Community
  • 1
  • 1
LeanneBD
  • 13
  • 5
  • 1
    It'd be preferable if you could tweak your question title to be a question. Less people will assume this is a "give me solutions" question. – byxor Dec 01 '16 at 20:04

1 Answers1

0

You cannot execute the ATTACH statement from inside a transaction.

You did not start a transaction, but Python tried to be clever, got the type of your statement wrong, and automatically started a transaction for you.

Set connection.isolation_level = None.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • It didn't seem to fix the problem by itself but I have added it and hopefully when I figure out what else I did wrong it will work. Thank for your help :-) – LeanneBD Dec 09 '16 at 20:10