nikkiy nikkiy - 29 days ago 14
Python Question

Track use of a function using SQlite3

I'm new to programming, and am using Python to write a function that transfers files created or modified in the last 24 hours. I now need to figure out how to use a SQlite3 database to keep track of the time and date of last file transfer. I know how to create a database and table in SQlite3, but am at a loss when it comes to tying it to the file transfer function. This is the code for my file transfer:

import os,time
import datetime
import shutil
import datetime as dt
conn = sqlite3.connect('file_check.db')
c = conn.cursor()


now = dt.datetime.now()
ago = now-dt.timedelta(hours=24)
strftime = "%H:%M %m/%d/%Y"
created = ('C:\\Users\\Jacqueline\\Desktop\\created')
dest = ('C:\\Users\\Jacqueline\\Desktop\\dest')

def file_trans(created, dest):
for root, dirs,files in os.walk(created):
for fname in files:
path = os.path.join(root, fname)
st = os.stat(path)
mtime = dt.datetime.fromtimestamp(st.st_mtime)
if mtime > ago:
print("True: ", fname, " at ", mtime.strftime("%H:%M %m/%d/%Y"))
shutil.move(path, dest)
c.execute("INSERT INTO FileCheck (unix, datestamp, timestamp) VALUES (?,?,?)", (unix, datestamp, timestamp))
conn.commit
conn.close


def main(source, destination):
# parameters passed into file_trans
source = created
destination = dest
#call file_trans
file_trans(source, destination)


if __name__=='__main__':
main()


Any ideas on how to link this somehow to an SQlite3 table so I can track when the file transfers have taken place? My code for creating the database is:

import sqlite3
import time
import datetime #creates datestamp
import random #creates value

conn = sqlite3.connect("file_check.db") #defines connection
c = conn.cursor()


def create_table():
c.execute("CREATE TABLE IF NOT EXISTS FileCheck(unix REAL, datestamp TEXT, timestamp TEXT)")


def dynamic_data_entry():
unix = time.time() #timestamp
datestamp = str(datetime.datetime.fromtimestamp(unix).strftime('%m/%d/%Y')) #formats datestamp
timestamp = str(datetime.datetime.fromtimestamp(unix).strftime('%H:%M:%S'))
c.execute("INSERT INTO FileCheck (unix, datestamp, timestamp) VALUES (?,?,?)",
(unix, datestamp, timestamp))
conn.commit()

create_table()
c.close()
conn.close()


The table is fine-- I just need to link it to the function.

Answer

Looks like you just need to define the variables you are passing in to your execute statement. If you are still getting errors, post the current code you are using, plus the full stack trace (error message) you are getting. It is surprising how much that can tell people about what is going wrong.

I've also moved the connection creation into the function, so you don't have issues with the connection closing before you call the function.

import os,time
import datetime
import shutil
import datetime as dt
import sqlite3

now = dt.datetime.now()
ago = now-dt.timedelta(hours=24)
strftime = "%H:%M %m/%d/%Y"
created = ('C:\\Users\\Jacqueline\\Desktop\\created')
dest = ('C:\\Users\\Jacqueline\\Desktop\\dest')

def file_trans(created, dest):
    conn = sqlite3.connect('file_check.db')
    c = conn.cursor()
    for root, dirs,files in os.walk(created):  
        for fname in files:
            path = os.path.join(root, fname)
            st = os.stat(path)    
            mtime = dt.datetime.fromtimestamp(st.st_mtime)
            if mtime > ago:
                print("True:  ", fname, " at ", mtime.strftime("%H:%M %m/%d/%Y"))
                shutil.move(path, dest)
                unix = time.time()
                datestamp = str(datetime.datetime.fromtimestamp(unix).strftime('%m/%d/%Y'))
                timestamp = str(datetime.datetime.fromtimestamp(unix).strftime('%H:%M:%S')) 
                c.execute("INSERT INTO FileCheck (unix, datestamp, timestamp) VALUES (?,?,?)", (unix, datestamp, timestamp))
                conn.commit()
    conn.close()