Read a filename and insert into database (using python and MSSQL)

Question

Using python, need to look into a dir and grab a filename, then insert the name into a MSSQL database with date/time for tracking purposes.

Web Results I keep coming across are for loading a whole CSV which we have in place and is already working for us. This need is just simply to pull the filename and add to a tracking table in MSSQL.

Here is the code that simply reads the files:

# read file
path = "Y:\\python\\working"
dirs = os.listdir( path )
# This prints all the files and directories (in our case it will be one file)
for file in dirs:
   print (file)

How can I take the result (filename) and insert filename and current date into a MSSQL DB with say a table name of "tracking" and two cols, file_name & date? There will be only 1 file at a time.

Possible duplicate of insert data into MSSQL server using python — philshem
– philshem, Commented Mar 28, 2019 at 19:42
@philshem that post is for adding the contents of a CSV to the DB. All I need to is to pull the filename as we already have the CSV loading processes in place. — pthfndr2007
– pthfndr2007, Commented Mar 28, 2019 at 19:56

philshem · Accepted Answer · 2019-03-28 20:00:27Z

2

assuming you have a table called tablename with a single char column called filename

import pyodbc
con_string='UID=%s;PWD=%s;DATABASE=%s;PORT=%s;TDS=%s;SERVER=%s;driver=%s' % (user,password, database,port,TDS_Version,server,driver)
....
cnxn=pyodbc.connect(con_string)
cursor=cnxn.cursor()
for f in dirs:
    cursor.execute("INSERT INTO tablename(filename) VALUES(?) ", (f))

cnxn.commit()

answered Mar 28, 2019 at 20:00

philshem

25.5k8 gold badges66 silver badges136 bronze badges

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

3 Comments

pthfndr2007 Over a year ago

@ philshem - this gets me to a point I can continue on, thank you.

pthfndr2007 Over a year ago

For current date I added a col to MSSQL that auto adds date/time upon insert of the "filename".

WinEunuuchs2Unix Over a year ago

Upvoted but the VALUES(?) practice I see all over the net is mystifying. There should be a way of simply inserting a string without the whole-dancing-around-the-issue. It will be a nightmare when I'm inserting a python list with thousands of entries.

Collectives™ on Stack Overflow

Read a filename and insert into database (using python and MSSQL)

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related