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
Python Format Size Application (Converting B to Kb, Mb, Gb, Tb)
How to Verify If a Button Is Enabled and Disabled in Webdriver Python
Importing Modules from Parent Folder
How to Remove Square Brackets from List in Python
How to Remove Strings Present in a List from a Column in Pandas
How to Clear Only Last One Line in Python Output Console
Removing Backslashes from a String in Python
How to Limit the User Input to Only Integers in Python
How to Kill a While Loop With a Keystroke
How to Determine If My Python Shell Is Executing in 32Bit or 64Bit
How to Make a Function Change Variables While in a While Loop
How to Restart a Program Based on User Input
Discord Bot Messaging a User With a Specific User Id
How to Delete Blank Lines from CSV File
Using Condition to Split Pandas Column of Lists into Multiple Columns.