0
def rmv_dupes_in_psumsdb(setoffilestoprocess, config):
    setoffilestoprocess_fnames = [file.name for file in setoffilestoprocess]
    constring = config['db_string']['db_string']
    cnxn = pyodbc.connect(constring)
    FilesToBeCrunched1000 = list(chunks(list(setoffilestoprocess_fnames), 2))
    for FilesChunks1000 in FilesToBeCrunched1000:
        sqlstring = 'DELETE FROM {0} WHERE [LOG] IN ('.format(config['db_string']['bd_psums_meta_table'])
        # print((FilesChunks1000))
        values_string = (', '.join("'" + item + "'" for item in FilesChunks1000))
        sqlstring+=values_string
        sqlstring+=')'
        cnxn.execute(sqlstring)

The script calls this function but nothing happens on the database side. I wrote a function similar to this that does a Select statement and it works. But this one doesn't. I printed out (sqlstring) and it correctly gave me the following output:

DELETE FROM [NSGWSAINLINE].[dbo].[bd_psums_meta] WHERE [LOG] IN ('0517312.002.7312-08.FRP.00.S25D._Yo9EXAOaDK6asQ2_.0.zip', '0503302.002.3302-20.FRP.00.S26A._obBBQu5GUT1pnKO_.0.zip')
DELETE FROM [NSGWSAINLINE].[dbo].[bd_psums_meta] WHERE [LOG] IN ('0524222.002.4222-08.FRP.00.S25D._cH03BJws2g1pnKO_.0.zip', '0532722.002.2722-15.92FIP.00.S26A._hR10vpeCvpsonKO_.0.zip')
DELETE FROM [NSGWSAINLINE].[dbo].[bd_psums_meta] WHERE [LOG] IN ('0524282.002.4282-25.FRP.00.S25D._0sOzWcCcEptonKO_.0.zip')

I actually went ahead and copied the outputs above and they ran in SQL Server and did the delete statements. So why isn't this working from within Python?

Dale K
  • 25,246
  • 15
  • 42
  • 71
edo101
  • 629
  • 6
  • 17

1 Answers1

3

You should commit your changes after execute with cnxn.commit() if there is no autocommit option in your connection enabled.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Etoneja
  • 1,023
  • 1
  • 8
  • 15
  • Yes, definitely want to check what commit/transactions are up to. Might be something else, but this an excellent first step. – JL Peyret Aug 05 '20 at 01:06
  • What do you mean by transactions? Is there a way to check without looking at sql server if the query was successfully executed/transferred? @JL Peyret? – edo101 Aug 05 '20 at 01:08
  • 1
    Each connection to db is a transation that shoud be commited or rolled back. More info here: https://www.python.org/dev/peps/pep-0249/ – Etoneja Aug 05 '20 at 01:16
  • Not sure. Depends on your RDBMS, its install settings and your user/session stuff. But it boils to your problems being easily caused by an uncommitted transaction, esp if your exact SQL (which I hope you didnt assemble by string concat) works in a query utility. So if *but it works in MS SQlL Studio...* take a look at transactions. – JL Peyret Aug 05 '20 at 01:16
  • LOL I assembled it from a string concat. What is wrong with this? @JLPeyret – edo101 Aug 05 '20 at 01:18
  • Sql security 101. Never do that. Called sql injection - 1 of biggest causes of hacks. And an instant death sentence on a job application. https://xkcd.com/327/ No worries if just learning but really never do it once you know about it. Use parametrized queries always if dealing with external inputs. – JL Peyret Aug 05 '20 at 01:21
  • So I am just learning and this is actually for my company. And my mentor was the one that suggested this string concatenation. So the person that manages my tasks was the one that suggested this for a big name company... This is awkward @JLPeyret What are these parameterized queries you speak of? – edo101 Aug 05 '20 at 01:25
  • To be fair, in your case, if the stuff comes *only* from file names, risk is limited. And parametrized queries are hard to do with IN/NOT IN criteria. But still... try to avoid string concat as much as possible. – JL Peyret Aug 05 '20 at 01:29
  • https://stackoverflow.com/questions/1633332/how-to-put-parameterized-sql-query-into-variable-and-then-execute-in-python Each RDBMS has its own slightly different variant syntax you need to look up MS SQL Servers. None of them are good with lists of values. You can always engage carefully w your mentor - they’re either unaware or, correctly, figure risk is low w file names coming from your OS or SQL Server. – JL Peyret Aug 05 '20 at 01:34
  • Are so parameterized queries are not very good with lists? I will have to make a note of this technique. DO you have an example of instances where parameterized queries are applicable to? @JLPeyret And yes, these are just file names in the list – edo101 Aug 05 '20 at 01:37
  • Err... they are really the ONLY way to go if you are using user-supplied values like running w criteria from a web form. Lookup “sql injection” that will get you started. – JL Peyret Aug 05 '20 at 01:39
  • Just to be clear, you can 'commit' with the cursor like: cnxt = pypyodbc.connect("connectionstring") cursor = cnxt.cursor() then: cursor.commit() – Mattman85208 Dec 21 '20 at 22:31