-1

i am very new to python.. i have a json object. below is the code.

jsondata = json.loads(data)

jsondata looks like this

{u'approvalId': u'0', u'size-1': 202, u'indRate': u'0.003', u'orderNo': u'ROMA2-20200508-00001', u'brokerSymbol': u'', u'requestedFor': u'r101115', u'aggUnit': u'', u'repId': u'O70', u'noGoodShares': 0, u'requestedBy': u'r101115', u'status': 4, u'timestamp': u'May 08, 2020 02:29:52', u'symbol': u'IBM', u'broker': u'APOC RT', u'usedShares': 3, u'pubKey': u'O70.ROMA2-20200508-00001', u'locateBrokerId': u'APOC', u'goodShares': 500, u'rtLeafId': 900059, u'availableShares': 497, u'requestedShares': 500, u'brokerOrderNo': u'', u'indicativeRate': 0.0030000000000000001, u'pendingShares': 0}

i want to insert this in sybase database not all few of the details. below insert statement i tried.

cur = conn.cursor()

sql = "INSERT into RT24.dbo.SLOrd(status, rtLeafId, orderNo, repId, symbol, broker, timestamp, modifiedTime) VALUES (d["status"], d["rtLeafId"], d["orderNo"], d["repId"], d["symbol"\
], d["broker"], d["timestamp"], d["timestamp"])"

cur.execute(sql)
conn.commit()

Can someone please help me how can i do this..

Regards, Prashant.

Barmar
  • 741,623
  • 53
  • 500
  • 612
Prashant Naik
  • 105
  • 1
  • 8

1 Answers1

0

You can't stick variables in the middle of a string like that. You should put placeholders in the SQL string, and then provide a parameters dictionary as the second argument to cur.execute().

sql = """INSERT into RT24.dbo.SLOrd(status, rtLeafId, orderNo, repId, symbol, broker, timestamp, modifiedTime) 
        VALUES (@status, @rtLeafId, @orderNo, @repId, @symbol, @broker, @timestamp, @timestamp)"""
cur.execute(sql, {"@status": d["status"], "@rtLeafId": d["rtLeafId"], "@orderNo": d["orderNo"], "@repId": d["repId"], "@symbol": d["symbol"], "@broker": d["broker"], "@timestamp": d["timestamp"], "@timestamp": d["timestamp"]})

See the description of the execute() method in the python-sybase manual

If this isn't working, you can use string formatting, but then you have the possibility of SQL-injection if you don't sanitize the data.

sql = f"""INSERT into RT24.dbo.SLOrd(status, rtLeafId, orderNo, repId, symbol, broker, timestamp, modifiedTime) 
        VALUES ({d["status"]}, {d["rtLeafId"]}, '{d["orderNo"]}', '{d["repId"]}', '{d["symbol"]}', '{d["broker"]}', '{d["timestamp"]}', '{d["timestamp"]}')"""
cur.execute(sql)
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thank you for your help.. But still i am getting the error. ProgrammingError: ("Must declare variable '@status'.\n", 137). – Prashant Naik May 09 '20 at 06:24
  • i tried the below code and that gives me ProgrammingError: ("Incorrect syntax near '%'.\n", 102) cur.execute("INSERT into RT24.dbo.SLOrd(rtLeafId,status,orderNo,repId,symbol,broker,timestamp)VALUES(%s,%s,%s,%s,%s,%s,%s)",(str(d["rtLeafId"]),str(d["status"]),d["orderNo"],d["repId"], d["symbol"],d["broker"],d["timestamp"])) – Prashant Naik May 09 '20 at 14:52
  • Sounds like you have a newline in your query string. If you want to split a Python string over multiple lines, you must use triple quotes. – Barmar May 09 '20 at 16:12
  • Can you please give example how can I use triple quotes – Prashant Naik May 09 '20 at 16:16
  • i am still getting the same error (Pdb) > /home/naikp/lib.linux-x86_64-2.6/rcv.py(109)on_msg() -> sql = """INSERT into RT24.dbo.SLOrd(rtLeafId, status, orderNo, repId, symbol, broker, timestamp) VALUES(%s, %s, %s, %s, %s, %s, %s)""" (Pdb) > /home/naikp/lib.linux-x86_64-2.6/rcv.py(112)on_msg() -> cur.execute(sql, (d["rtLeafId"],d["status"], d["orderNo"], d["repId"], d["symbol"], d["broker"], d["timestamp"])) (Pdb) 0755 12:32:35> STAT w oqsize:0, sent:2, part:0, eagain:0, full:1, forceq:5 natq:0 ffq:2 pfq:0 bytes:171 ProgrammingError: ("Incorrect syntax near '%'.\n", 102) – Prashant Naik May 09 '20 at 16:33
  • new code looks like this sql = """INSERT into RT24.dbo.SLOrd(rtLeafId, status, orderNo, repId, symbol, broker, timestamp) VALUES(%s, %s, %s, %s, %s, %s, %s)""" cur.execute(sql, (d["rtLeafId"],d["status"], d["orderNo"], d["repId"], d["symbol"], d["broker"], d["timestamp"])) – Prashant Naik May 09 '20 at 16:36
  • That looks like the syntax for the `pymysql` module, not the `python-sybase` library I linked to in my answer. – Barmar May 09 '20 at 16:46
  • sorry i didn't get you.. any solution you gave ? – Prashant Naik May 09 '20 at 16:50
  • The only thing I know about using Sybase from Python is what I read in the manual I linked to, I've never done it myself. If that doesn't work, I have no other solution. – Barmar May 09 '20 at 16:52
  • ok. but anyways thanks a lot for your help.. i learned something .. i appreciate you for helping me. – Prashant Naik May 09 '20 at 16:55
  • just for your info. when i try below by hardcoding the values it works for me.. sql = "INSERT into RT24.dbo.SLOrd(status, rtLeafId, orderNo, repId, symbol, broker, timestamp) VALUES (4, 900059, 'ROMA2-20200508-00001', 'O70', 'IBM', 'APOC RT', '2020-05-04 02:29:52')" cur.execute(sql); – Prashant Naik May 09 '20 at 16:58
  • I've added a solution that uses string formatting instead of query parameters. – Barmar May 09 '20 at 17:04
  • its throwing invalid syntax, File "rcv.py", line 116 sql = f"""INSERT into RT24.dbo.SLOrd(status, rtLeafId, orderNo, repId, symbol, broker, timestamp)VALUES ('{d["status"]}', '{d["rtLeafId"]}', '{d["orderNo"]}', '{d["repId"]}', '{d["symbol"]}', '{d["broker"]}', '{d["timestamp"]}')""" ^ SyntaxError: invalid syntax – Prashant Naik May 09 '20 at 17:34
  • F-strings require python 3.6 or higher. If you have an older version, use `%` or `.format()` – Barmar May 09 '20 at 23:49
  • See https://stackoverflow.com/questions/42126794/python-3-returns-invalid-syntax-when-trying-to-perform-string-interpolation – Barmar May 09 '20 at 23:50
  • Hi Barmar, finally it worked. Just for you info. below is the correct query. sql = """INSERT into RT24.dbo.SLOrd(status, rtLeafId, orderNo, repId, symbol, broker, timestamp) VALUES (%d,%d,'%s','%s','\ %s','%s','%s')""" %(d["status"],d["rtLeafId"],d["orderNo"],d["repId"],d["symbol"],d["broker"],d["timestamp"]) thank you very much. i learnt a lot – Prashant Naik May 11 '20 at 10:23