0

How can I convert the account value into a string that my SQL query accepts.

import pandas as pd
import os
from xlwings import Workbook, Range
from sqlalchemy import create_engine

account = Range('G2').options(numbers=int).value #account number #value is 3001001000

sql = 'SELECT top 30 oraclenum,pcat,ConLenses, sum(dollars) as dollars FROM ' \
          'ConData WHERE oraclenum = {} group by oraclenum,pcat, ConLenses'.format(account)

How can I include the value between ' '. Kinda like WHERE oraclenum = '3001001000' in my SQL query. It throws an error if its not formatted as a string.

Cesar
  • 617
  • 3
  • 8
  • 17
  • Consider [parameterized queries](http://docs.sqlalchemy.org/en/latest/core/tutorial.html#specifying-bound-parameter-behaviors). – Lunaweaver Nov 11 '16 at 20:55

1 Answers1

2

You could put escaped quotes around your format expression (\'{}\'), though the best way is to swap quotes for double quotes and put simple quotes around your format string

sql = "SELECT top 30 oraclenum,pcat,ConLenses, sum(dollars) as dollars FROM " \
          "ConData WHERE oraclenum = '{}' group by oraclenum,pcat, ConLenses".format(account)
Jean-François Fabre
  • 137,073
  • 23
  • 153
  • 219
  • 1
    You could also use `"""` (triple quotes) for multi-line strings. See http://stackoverflow.com/questions/10660435/pythonic-way-to-create-a-long-multi-line-string – Lunaweaver Nov 11 '16 at 20:52
  • you're right, but in that case, it's just 2 strings on a single line, concatenated and using backslash to avoid creating a too long line. – Jean-François Fabre Nov 11 '16 at 20:54