0

i have a sqlite3 database that has multiple (six) tables and i need it to be imported to csv, but when i try to import it, i get a duplicated value if a column (in a table) is larger than another (in another table).

ie: this is how my sqlite3 database file looks like:

column on table1     column on table2   column on table3
25                   30                 20
30

this is the result on the .csv file (using this script as example)

25,30,20
30,30,20

and this is the result i need it to show:

25,30,20
30

EDIT: Ok, this is how i add the values to each table, based on the python documentation example (executed each time a value entry is used):

import sqlite3
conn = sqlite3.connect('database.db')

c = conn.cursor()

# Create table
c.execute('''CREATE TABLE table
            (column int)''')

# Insert a row of data
c.execute("INSERT INTO table VALUES (value)")

# Save (commit) the changes
conn.commit()

# We can also close the cursor if we are done with it
c.close()

any help?

-Regards...

Community
  • 1
  • 1
Hairo
  • 2,062
  • 5
  • 27
  • 33

1 Answers1

1

This is how you could do this. 

import sqlite3

con = sqlite3.connect('database')
cur = con.Cursor()

cur.execute('select table1.column, table2.column, table3.column from table1, table2, table3')
# result should look like ((25, 30, 20), (30,)), if I remember correctly
results = cur.fetchall()

output = '\n'.join(','.join(str(i) for i in line) for line in results)

Note: this code is untested, written out of my head, but I hope you get the idea.

UPDATE: apparently I made some mistakes in the script and somehow sql 'magically' pads the result (you might have guessed now that I'm not a sql guru :D). Another way to do it would be:

import sqlite3
conn = sqlite3.connect('database.db')

cur = conn.cursor()

tables = ('table1', 'table2', 'table3')
results = [list(cur.execute('select column from %s' % table)) for table in tables]

def try_list(lst, i):
    try:
        return lst[i]
    except IndexError:
        return ('',)

maxlen = max(len(i) for i in results)
results2 = [[try_list(line, i) for line in results] for i in xrange(maxlen)]

output = '\n'.join(','.join(str(i[0]) for i in line) for line in results2)
print output

which produces

25,30,20
30,,

This is probably an overcomplicated way to do it, but it is 0:30 right now for me, so I'm not on my best...
At least it gets the desired result.

BrtH
  • 2,610
  • 16
  • 27
  • Can you edit your question with the values you use with the insert query and can you also give the query you use to get the data from the db (`from ... select ...`)? Right now, I suspect that something is wrong with your input data in the first place, or that you are using an overcomplicated script that places the values where you don't want them. Could you place a larger extract of your actual code? – BrtH Jul 03 '12 at 10:09
  • as i said in the OP i took it from here: http://stackoverflow.com/questions/4264379/it-is-possible-export-table-sqlite3-table-to-csv-or-similiar also, i tried your code in the console and the result of output was the same i got with the said script – Hairo Jul 03 '12 at 14:55
  • 1
    I updated the question, and it should work now, although I'm sure there is a more elegant way... – BrtH Jul 03 '12 at 22:41
  • thanks, it really worked, there should be a better way using the python csv module i'll try an see... thank you so much... – Hairo Jul 04 '12 at 02:33