Converting columns in SQLite database to cell values [closed]

Question

Closed. This question needs to be more focused. It is not currently accepting answers.

Want to improve this question? Guide the asker to update the question so it focuses on a single, specific problem. Narrowing the question will help others answer the question concisely. You may edit the question if you feel you can improve it yourself. If edited, the question will be reviewed and might be reopened.

Closed 3 years ago.

Improve this question

Let's say I have the following database structure:

fruits	vegetables	grains
banana	carrot	wheat
apple	broccoli	barley
watermelon	cabbage	malt
strawberry	tomato	semolina

And I want it to be a bit easier to work with, transforming it into the following structure instead:

name	type
banana	fruit
apple	fruit
broccoli	vegetable
barley	grain
tomato	vegatable
wheat	grain

How would I go about writing a python program that could do this for me?

I found this solution to be workable - but maybe it's not as pythonic/readable as it should be:

import sqlite3

def transform():
    con = sqlite3.connect('database.db')
    cur = con.cursor()
    cur2 = con.cursor()
    cur3 = con.cursor()
    data = cur.execute('''SELECT * FROM table_1''')
    columns = []
    for column in data.description:
        columns.append(column[0])
    for columnx in columns:
        cur2.execute(f'''SELECT {columnx} FROM table_1''')
        content = cur2.fetchall()
        for word in content:
            word = str(word)
            word = word.strip("(),' ")
            if word != "None":
               cur3.execute(f'''INSERT INTO table_1_V2 (column1, column2) VALUES (?,?);''',(str(word),str(columnx)))
    con.commit()

I wrote a working program myself. See reply. Could be useful to others who want to solve a similar issue without getting too bogged down in code they will never work with again. — LarsLill
– LarsLill, Commented Jan 26, 2022 at 14:55
Didn't see that you answered your own question. I'll delete my comment. You should accept your answer if it's suitable for you. Consider editing it to generalise a bit. And use sqlfiddle.com or db-fiddle.com to provide future readers with a working schema and a table with data. — aneroid
– aneroid, Commented Jan 26, 2022 at 15:32
Voting to reopen, since OP is the Answerer, so has code (ie shown effort). — aneroid
– aneroid, Commented Jan 26, 2022 at 16:55

LarsLill · Accepted Answer · 2022-01-26 12:28:26Z

The following program does the job - although it could probably be a bit more pythonic/readable:

import sqlite3

def transform():
    con = sqlite3.connect('database.db')
    cur = con.cursor()
    cur2 = con.cursor()
    cur3 = con.cursor()
    data = cur.execute('''SELECT * FROM table_1''')
    columns = []
    for column in data.description:
        columns.append(column[0])
    for columnx in columns:
        cur2.execute(f'''SELECT {columnx} FROM table_1''')
        content = cur2.fetchall()
        for word in content:
            word = str(word)
            word = word.strip("(),' ")
            if word != "None":
               cur3.execute(f'''INSERT INTO table_1_V2 (column1, column2) VALUES (?,?);''',(str(word),str(columnx)))
    con.commit()

In the INSERT statement here, column1, column2 can be extended with however many columns you want to fill in your new table, and the values that are to be inserted in the new column are found at the end of the statement (word and columnx here). Note that you have to actually create the new table with the desired contents for this code to work.

Collectives™ on Stack Overflow

Converting columns in SQLite database to cell values [closed]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related