Convert SQL Result to List Python

Convert sql result to list python

If you have an iterable in Python, to make a list, one can simply call the list() built-in:

list(cursor.fetchall())

Note that an iterable is often just as useful as a list, and potentially more efficient as it can be lazy.

Your original code fails as it doesn't make too much sense. You loop over the rows and enumerate them, so you get (0, first_row), (1, second_row), etc... - this means you are building up a list of the nth item of each nth row, which isn't what you wanted at all.

This code shows some problems - firstly, list() without any arguments is generally better replaced with an empty list literal ([]), as it's easier to read.

Next, you are trying to loop by index, this is a bad idea in Python. Loop over values, themselves, not indices you then use to get values.

Also note that when you do need to build a list of values like this, a list comprehension is the best way to do it, rather than creating a list, then appending to it.

How to convert an SQL Output to a python list

It is minimal example which create database with two elements and later it uses data from database to fill Treeview

You have to use create() only once.

import tkinter as tk
from tkinter import ttk
import sqlite3

def create(con):
    cur = con.cursor()

    cur.execute('''CREATE TABLE IF NOT EXISTS  user (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age TEXT NOT NULL,
        email TEXT NOT NULL UNIQUE,
        town TEXT NOT NULL
    );''')

    cur.execute('INSERT INTO user (name, age, email, town) values("James Bond", 45, "bond@mi6.uk", "London")')
    cur.execute('INSERT INTO user (name, age, email, town) values("Rambo", 45, "rambo@usa.com", "Washington")')

    con.commit()

# --- main ---

con = sqlite3.connect("user_info.db")
cur = con.cursor()

create(con)

root = tk.Tk()

tv = ttk.Treeview(root)
tv.pack(fill='both', expand=True)
tv.config(columns= ("name", 'age', 'email', 'town'))

tv.heading('#0', text='ID')
tv.column('#0', width = 100)

tv.heading('1', text = "Age")
tv.column('1', width = 100)

tv.heading('name', text = 'Name')
tv.column('name', width = 100)

tv.heading('2', text = "email")
tv.column("2", width = 100)

tv.heading('3', text = "Town")
tv.column("3", width = 100)

cur.execute('SELECT * FROM user')
for number, row in enumerate(cur.fetchall()):
    print(row)
    item = 'item{}'.format(number)
    tv.insert("", 'end', item, text=row[0])
    tv.set(item, 'name', row[1])
    tv.set(item, 'age', row[2])
    tv.set(item, 'email', row[3])
    tv.set(item, 'town', row[4])

root.mainloop()

How to convert sql query to list?

You can try

print(["{} {}".format(i[0], i[1]) for i in list(c)])

That will print you

['Id id', 'RelationId reference', 'EventId reference']

Convert result from mysql to list in python

You just have to listify the entire result set from the cursor object and you should be good

con = mdb.connect('localhost', 'root', 'root', 'sample_db');
with con:
cur = con.cursor()
cur.execute("SELECT site_id FROM positive_outcomes")

result_set = list(cursor.fetchall())

for result in result_set:
    # do something here

Convert sql output to string in python

If you only need a single row, use mycursor.fetchone() instead of mycursor.fetchall().

The result you're currently getting is a list containing 1 tuple containining 1 string. It can be accessed as firstname = mycursor.fetchall()[0][0]

The better solution is firstname = mycursor.fetchone()[0]

---

Related Topics