Python List from Database Table into a Dictionary

How do I turn this data from an SQL database table into a dict?

You can use defaultdict
and iterate on the cursor directly

d = defaultdict(list)
for row in c:
d[row[0]].append(row[1])

To make it more readable change the loop to

d = defaultdict(list)
for guild, word in c:
d[guild].append(word)

Python add items into dictionary from database table (sqlite3)

Something like this:

for row in data:
name, weight = row
fruitweight[name] = weight

fetchall() returns a list of tuples of values from the database according to your query. The above unpacks the tuples into two variables.

If you want to get fancy, you can use a dictionary comprehension with tuple unpacking:

fruitweight = {name: weight for name, weight in data}

And finally, execute() actually returns a cursor that you can iterate over, so I think your code can be reduced to the following:

import sqlite3

conn = sqlite3.connect("database.db")

sql = """SELECT fruit,weight FROM fruits"""
fruitweight = {name: weight for name, weight in conn.execute(sql)}

When trying to convert a list of python dictionaries into a SQLite table I keep getting an error?

Python's sqlite package doesn't use %s style formatting. It uses ? for positional placeholders or :name for keyword placeholders.

So your query might look like

c.execute(
"INSERT INTO AAPL1M2021 (time,open,high,low,close,volume) VALUES (:t, :o, :h, :l, :c, :v)",
data
)

Note that executemany is used for lists of arguments for executing mulitple queries. For a single dict use execute; use executemany for a list of dicts.

SQLite Database to Python Dictionary using SQL SELECT statement

For this you need to group by WorkList only and use conditional aggregation:

SELECT WorkList, 
SUM(RecordStatus = 'Open') AS Open,
SUM(RecordStatus = 'Closed') AS Closed,
SUM(RecordStatus = 'Hold') AS Hold
FROM worklist_reports
WHERE WorkList IS NOT NULL
GROUP BY WorkList;

Python convert table to dictionary

from itertools import dropwhile, takewhile
with open("test.txt") as f:
dp = dropwhile(lambda x: not x.startswith("-"), f)
next(dp) # skip ----
names = next(dp).split() # get headers names
next(f) # skip -----
out = []
for line in takewhile(lambda x: not x.startswith("-"), f):
a, b = line.rsplit(None, 1)
out.append(dict(zip(names, a.split(None, 7) + [b])))]

Output:

from pprint import  pprint as pp

pp(out)
[{'Access': 'RW',
'Cache': 'RWTD',
'Consist': 'No',
'DG/VD': '0/0',
'Name': 'one',
'Size': '1.818 TB',
'State': 'Optl',
'TYPE': 'RAID1',
'sCC': '-'},
{'Access': 'RW',
'Cache': 'RWTD',
'Consist': 'No',
'DG/VD': '1/1',
'Name': 'two',
'Size': '1.818 TB',
'State': 'Optl',
'TYPE': 'RAID1',
'sCC': '-'},
{'Access': 'RW',
'Cache': 'RWTD',
'Consist': 'No',
'DG/VD': '2/2',
'Name': 'three',
'Size': '1.818 TB',
'State': 'Optl',
'TYPE': 'RAID1',
'sCC': '-'},
{'Access': 'RW',
'Cache': 'RWTD',
'Consist': 'No',
'DG/VD': '3/3',
'Name': 'four',
'Size': '1.818 TB',
'State': 'Optl',
'TYPE': 'RAID1',
'sCC': '-'}]

If you want to maintain order use an OrderedDict

out = [OrderedDict(zip(names, line.split()))
for line in takewhile(lambda x: not x.startswith("-"), f)]

For missing Name values as per your edit:

from itertools import dropwhile, takewhile

with open("test.txt") as f:
dp = dropwhile(lambda x: not x.startswith("-"), f)
next(dp) # skip ----
names = next(dp).split() # get headers names
next(f) # skip -----
out = []
for line in takewhile(lambda x: not x.startswith("-"), f):
a, b = line.rsplit(" ", 1)
out.append(dict(zip(names, a.rstrip().split(None, 7) + [b.rstrip()])))

Output:

[{'Access': 'RW',
'Cache': 'RWTD',
'Consist': 'No',
'DG/VD': '0/0',
'Name': 'one',
'Size': '1.818 TB',
'State': 'Optl',
'TYPE': 'RAID1',
'sCC': '-'},
{'Access': 'RW',
'Cache': 'RWTD',
'Consist': 'No',
'DG/VD': '1/1',
'Name': 'two',
'Size': '1.818 TB',
'State': 'Optl',
'TYPE': 'RAID1',
'sCC': '-'},
{'Access': 'RW',
'Cache': 'RWTD',
'Consist': 'No',
'DG/VD': '2/2',
'Name': 'three',
'Size': '1.818 TB',
'State': 'Optl',
'TYPE': 'RAID1',
'sCC': '-'},
{'Access': 'RW',
'Cache': 'RWTD',
'Consist': 'No',
'DG/VD': '3/3',
'Name': 'four',
'Size': '1.818 TB',
'State': 'Optl',
'TYPE': 'RAID1',
'sCC': '-'},
{'Access': 'RW',
'Cache': 'RWTD',
'Consist': 'No',
'DG/VD': '4/4',
'Name': '',
'Size': '4.681 TB',
'State': 'Reblg',
'TYPE': 'RAID10',
'sCC': '-'}]

Which will also handle lines with multiple spaces between TB and the Name column value 1.818 TB one

Python easiest way to insert a dictionary into a database table?

If you're using pyodbc then this might work:

columns = {row.column_name for row in cursor.columns(table='TABLEabc')}

safe_dict = {key: val for key, val in my_dict.items() if key in columns}

# generate a parameterised query for the keys in our dict
query = "INSERT INTO TABLEabc ({columns}) VALUES ({value_placeholders})".format(
columns=", ".join(safe_dict.keys()),
value_placeholders=", ".join(["?"] * len(safe_dict)),
)

cursor.execute(query, list(safe_dict.values()))

It is intended to be safe from SQL injection because:

  • we filter for only keys which are actual column names in the db
  • we use pyodbc cursor execute params, so the values will be escaped properly

Where it possibly won't work:

  • if any of the column names need to be quoted and escaped, this won't happen automatically so it will fail

Quoting/escaping is db-specific so we would have to check the rules for our actual db and apply that to the dict keys that we format into the query. (or find some way to get pyodbc to do that for us, not sure if possible)

If you trust your my_dict not to contain malicious code then you can simplify to just:

query = "INSERT INTO TABLEabc ({columns}) VALUES ({value_placeholders})".format(
columns=", ".join(my_dict.keys()),
value_placeholders=", ".join(["?"] * len(my_dict)),
)

cursor.execute(query, list(my_dict.values()))


Related Topics



Leave a reply



Submit