Python - Using replace to remove Double Quotes in SQL Query

Question

I want to remove the double quotation marks from a database that I'm exporting information via Python but when I run my query I encounter the following error:

I've used the replace that should be the correct one in the process but I'm not succeeding ...

# Libraries
import csv 
import logging
import os
import gcloud
from gcloud import storage
from google.cloud import bigquery
from oauth2client.client import GoogleCredentials
import json
import pyodbc
from datetime import datetime

try:
    script_path = os.path.dirname(os.path.abspath(__file__)) + "/"
except:
    script_path = "key.json"

#Bigquery Credentials and settings
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = script_path 

database='xxx'
uid = 'xxx'
pwd = 'xxx'
server = '0.0.0.0'
driver = "DRIVER={SQL Server};server=" + server + ";database=" + database + ";uid=" + uid + ";pwd=" + pwd
print(driver) 
# connecting to the DB 
db = pyodbc.connect(driver)
cursor = db.cursor()
tabela = 'test'
SQLview = "select replace(replace(col0,';','|'),'"','') as col0, \
replace(replace(col1,';','|'),'"','') as col1, \
replace(replace(col2,';','|'),'"','') as col2, \
replace(replace(col3,';','|'),'"','') as col3, \
replace(replace(col4,';','|'),'"','') as col4
from test"
data = datetime.today().strftime('%Y%m%d%H%M%S')
filename = tabela + '_' + data + '.csv'
folder = "C:\\Users\\me\\Documents\\"


# Creating CVS file
cursor.execute(SQLview)
with open(folder + filename, 'w', newline= '', encoding = 'utf-8') as f:
    writer = csv.writer(f, delimiter=';')
    writer.writerow([ i[0] for i in cursor.description ])
    writer.writerows(cursor.fetchall())

File "", line 64 from operacoes_b2w" ^ SyntaxError: EOL while scanning string literal

Matthew Barlowe · Accepted Answer · 2019-06-06 15:31:14Z

2

You need to wrap your SQL query in triple quotes instead of single because it's mistaking the " inside of it as ending the string. Instead of this:

SQLview = "select replace(replace(col0,';','|'),'"','') as col0, \
replace(replace(col1,';','|'),'"','') as col1, \
replace(replace(col2,';','|'),'"','') as col2, \
replace(replace(col3,';','|'),'"','') as col3, \
replace(replace(col4,';','|'),'"','') as col4
from test"

Do this:

SQLview = """select replace(replace(col0,';','|'),'"','') as col0, \
replace(replace(col1,';','|'),'"','') as col1, \
replace(replace(col2,';','|'),'"','') as col2, \
replace(replace(col3,';','|'),'"','') as col3, \
replace(replace(col4,';','|'),'"','') as col4
from test"""

answered Jun 6, 2019 at 15:31

Matthew Barlowe

2,3281 gold badge16 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Python - Using replace to remove Double Quotes in SQL Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related