Python Flask, SQLIte, and CSV: How To Perform Transformation Function on Data In Column of CSV When-CodePudding

I am trying to import data from CSV in comma delimited file type in Python (Flask, SQLite env) to be inserted into my DB table.

However, once the password column is read in the CSV I want to apply a hash function to that data then insert it into the DB table. How it is now I can only read all inputs and insert them raw with no modifications.

How would I go about implementing this onto the password column?

cursor.execute('CREATE TABLE IF NOT EXISTS Users(email TEXT NOT NULL PRIMARY KEY, password TEXT NOT NULL);')
file = open('data/Users.csv')
contents = csv.reader(file)
next(contents) # skip header row
insert_records = "INSERT INTO Users(email, password) VALUES(?, ?)"
cursor.executemany(insert_records, contents)

This is the desired effect on the column

password = hashlib.md5(password.encode())

CVS Format

Email, Password
[email protected], hackme!

to ->

[email protected], $@RN_SOMEHASH_JW#W

Thank you!

CodePudding user response：

Try building a new iterable with password assignment via list comprehension prior to parameterized SQL. Be sure to use with context manager to close automatically after reading text file.

insert_records = "INSERT INTO Users(email, password) VALUES(?, ?)"

with open('data/Users.csv') as file:
    contents = csv.reader(file)
    next(contents) # skip header row

    # LIST OF TUPLES WITH HASH CONVERSION ON SECOND ITEM
    params = [(row[0], hashlib.md5(row[1].encode())) for row in contents]
    cursor.executemany(insert_records, params)