Error in running Pymssql.Cursor.Execute due to Strange Characters Added

Question

I need help fixing an error that occurs when I run an sql query using pymssql, below is the code I am using.

config.py:

import pymssql

class devConfig():
    server = 'servername'
    usr = 'user'
    pwd = 'password'
    db_name = 'database'

init.py:

from flask import Flask 
from sqlalchemy import create_engine
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import sessionmaker, scoped_session
from config import devConfig
import pymssql

app = Flask(__name__)

app.config.from_object('config')

Session = scoped_session(sessionmaker())

_sql_engine = None
_current_session = None

def session():
    global _current_session

    if _current_session is None:
        session = Session.configure(bind=sql_engine())
        _current_session = Session()

    return _current_session


def sql_engine():
    global _sql_engine

    if _sql_engine:
        return _sql_engine
    else:
        server = devConfig.server
        db_name = devConfig.db_name
        usr = devConfig.usr
        pwd = devConfig.pwd

        _sql_engine = create_engine("mssql+pymssql://filex.com\{}:{}@{}/{}".format(usr, pwd, server, db_name)) #create_engine("mssql+pymssql://{0}:{1}@{2}/{3}".format(username, password, dns, db))
        return _sql_engine

from app import views

views.py:

from flask import render_template, flash, redirect

from app import app
from .forms import LoginForm
from . import session
from sqlalchemy.sql import text
#app.config.from_object('config.DevConfig')
#attributes = inspect.getmembers('config.DevConfig')

@app.route('/')
@app.route('/index')
def index():
    select_sql = 'Select top 5 * from vendor'
    result = session().execute(text(select_sql))

    return render_template('index.html',
                           title='Home',
                           data=result)
    session().close()

When I run this, I get this error:

File "pymssql.pyx", line 464, in pymssql.Cursor.execute (pymssql.c:7491)
sqlalchemy.exc.ProgrammingError: (pymssql.ProgrammingError) (102, b"Incorrect syntax near 'from\xef\xbb\xbfvendor'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n") [SQL: 'Select top 5 * from\ufeffvendor']

I don't understand why my query becomes 'from\xef\xbb\xbfvendor' when those characters don't exist on my query. What is causing this and how do I fix this?

Answer 1

You are using the wrong syntax for your SQL-Query. The top keyword is not available in myssql. Use limit 0,5 instead:

select_sql = 'Select * from vendor LIMIT 0,5'

Check this from W3C schools too.

By the way: please tell me this is not your real password in the config.py

Edit: I misread the import statement, please ignore this answer, it is wrong and not helping.

Next try:

When you check the Unicode character that is inserted there, it seems that it is a "Zero with no Break Space" character, not a usual space. You can see this here: fileformat.info Can you check your sourcecode and insert an Space at this position? Make sure that the statement

select_sql 'Select top 5 * from vendor'

holds only proper Spaces. This may have happened by Copy&Pasting Code.