Python execute sql query using sqlcmd

Question

Hi I want to execute query using sqlcmd so I am calling it using subprocess.call() . This process sometimes its working but in loop it does not work. It only the execute the last argument. Please help below is the sample code I am trying-

import subprocess
host = 'hostname'
db = 'SQLTest'
sqlcmd = "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
query = "INSERT INTO [dbo].[Test](type,ident,lat,long,y_proj,x_proj,new_seg,display,color,altitude,depth,temp,time,model,filename,ltime) VALUES ('TRACK','ACTIVE LOG','40.79015493','-77.85914183','4627311.94501541','1779470.5827101','False','False','255','351.979858398438','0','0','2008/06/11-14:33:33','eTrex Venture','','2008/06/11 09:33:33')"
for x in range (0,5):
    subprocess.call([sqlcmd,'-S' ,host, '-d', db, '-Q', query])

Or is there any other method. I even tried pymysql module. But it shows authentication error.

Ankky · Accepted Answer · 2016-11-12 20:22:51Z

1

I got the error. It was related to the query I was passing. The query was reading from a text file. So it had spaces in them except the last query. and for single testing I was using the last query. After fixing that it worked.

answered Nov 12, 2016 at 20:22

Ankky

471 gold badge2 silver badges12 bronze badges

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

Comments

Chris Curvey · Accepted Answer · 2016-11-12 12:58:55Z

That's a very creative solution!

When you say " in loop it does not work", could you tell us what's happening? Is there an error message? Does it run, but no data is inserted in the table? Can you get this to work properly outside a loop?

The first thing I notice is

sqlcmd = "c:\program files\...."

You might want to make that a raw string, by putting an "r" in front of the quotes, like so:

sqlcmd = r"c:\program files\...."

That will prevent Python from trying to interpret the backslash as a special characters.

It looks like you're trying to talk to a SQL Server, so pymysql is not going to help (that's for talking to a MySQL Server). I would suggest looking into pyodbc or pymssql as an alternative.

Collectives™ on Stack Overflow

Python execute sql query using sqlcmd

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related