Python SQLite: reading in data from multiple files into a TABLE

Question

I have a table in sqlite

CREATE TABLE nutritional_values
         (item,
         calories,
         total_fat_g,
         saturated_fat_g,
         polyunsaturated_g,
         monosaturated_g,)''')

I create my TABLE using SQLite in a python, by running from the command line the file

c:\users\John\diet\nutrition.py

then i have hundreds of individual files saved in

c:\users\John\diet\food_items

which look like

# item
'avocado',
# calories
160,
# total_fat_g
14.7,
# saturated_fat_g
2.1,
# polyunsaturated_g
1.8,
# monosaturated_g
9.8,

I could save these as txt files, or anything else suitable. The hash mark line is just a comment to help me when i create these files.

How can I read in all these files into my SQLite nutritional_values table?

So each file creates one line in the table.

andopr · Accepted Answer · 2019-09-12 09:26:05Z

1

Have you heard of dictionaries in Python? If your files always have the same structure, you can read that file in Python and add each value to a python dict. Then, you should use a SQL insert to insert them.

Your dictionary could have this format:

py_dict = {'item' : 'avocado', 'calories': '160', ...}

Then, you use the insert:

insert = "INSERT INTO nutritional_values (item, calories, total_fat_g, saturated_fat_g,...) VALUES ('{item}','{calories}', '{total_fat_g}', ...)".format(**py_dict)

So, step by step you need to:

Read all files
Store info in dictionaries
Insert to table

This is just an overview of what you have to do, not the full code. You can let me know if you need future instructions! :)

answered Sep 12, 2019 at 9:26

andopr

3163 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python SQLite: reading in data from multiple files into a TABLE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related