Apr-29-2022, 06:57 PM
Hello,
I am using Python3, PyCharm and DB Browser for SQLite. I am new to this domain and those are what I have learned on so far.
I am collecting daily data that goes to an excel/csv file. I am hoping to send the response to a database on SQLite so I can then use python to analyze and report.
Im hoping this is the right method but am open to suggestions. Excel -> CSV -> Python -> SQLite, then Python to retrieve data when needed.
I am having trouble creating the tables to store the data in SQLite. Im trying to avoid duplicates by using "ids" instead of having the data all in one table. The code I am using is below.
I would repeat 70 to 74 for each table created.
Any suggestion is appreciated, even it is a different method to achieve this goal.
Thank you
I am using Python3, PyCharm and DB Browser for SQLite. I am new to this domain and those are what I have learned on so far.
I am collecting daily data that goes to an excel/csv file. I am hoping to send the response to a database on SQLite so I can then use python to analyze and report.
Im hoping this is the right method but am open to suggestions. Excel -> CSV -> Python -> SQLite, then Python to retrieve data when needed.
I am having trouble creating the tables to store the data in SQLite. Im trying to avoid duplicates by using "ids" instead of having the data all in one table. The code I am using is below.
import pandas as pd import sqlite3 df = pd.read_csv ('PyCharmData.csv') # column headers # 'Start', 'Completion', 'Email', 'Name', 'First Name', 'Last Name', # 'RPE', 'Duration', 'Sleep', 'Fatigue', 'Stress', 'Soreness', 'Mood', # 'Diet', 'Injuries', 'Full Name' N = df['Full Name'] D = df['Completion'] I = df['Injuries'] R = df[['RPE', 'Duration', 'Sleep', 'Fatigue', 'Stress', 'Soreness', 'Mood', 'Diet']] conn = sqlite3.connect('testSQL') cur = conn.cursor() cur.executescript(''' DROP TABLE IF EXISTS AthName; DROP TABLE IF EXISTS Date; DROP TABLE IF EXISTS Injury; DROP TABLE IF EXISTS Metrics; CREATE TABLE AthName ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, name TEXT UNIQUE, Date_id INTEGER, Injury_id INTEGER, Metrics_id INTEGER ); CREATE TABLE Date ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, day TEXT, AthName_id INTEGER, Injury_id INTEGER, Metrics_id INTEGER ); CREATE TABLE Injury ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, info TEXT, AthName_id INTEGER, Date_id INTEGER, Injury_id INTEGER ); CREATE TABLE Metrics ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, AthName_id INTEGER, Injury_id INTEGER, Date_id INTEGER, sleep TEXT, mood TEXT, soreness INTEGER, rpe TEXT, fatigue INTEGER, diet TEXT, duration INTEGER, stress, INTEGER ); ''') fname = input('Enter file name: ') for entry in df: cur.execute('''INSERT OR IGNORE INTO AthName (name) VALUES ( ? )''', (N, ) ) cur.execute('SELECT id FROM AthName WHERE name = ? ', (N, )) AthName_id = cur.fetchone()[0] conn.commit()
I would repeat 70 to 74 for each table created.
Error:Traceback (most recent call last):
"...pyToSQL/main.py", line 71, in <module>
cur.execute('''INSERT OR IGNORE INTO AthName (name)
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.
I could be way off on this, it is patch work from some courses and other articles. Any suggestion is appreciated, even it is a different method to achieve this goal.
Thank you