Trouble connecting to SQL server with python

Question

I have recently installed Microsoft SQL Server 2014 on my PC as I want to create a database for a web application I am building (I have been learning Python for a year and have very basic experience with SQLite).

After installing SQL Server 2014 and creating a database called Users, I am just trying to run some very basic commands to my database but I am falling at the first hurdle over and over!

I have installed pymssql and pyodbc and tried running commands directly with these but have failed. (e.g. pymssql gives me a TypeError: argument of type 'NoneType' is not iterable when I set the variable conn = pymssql.connect(server, user, password, "tempdb")

My latest attempt is to use SQLalchemy to achieve my long awaited connection with SQL database. However, after installing this, it is failing on the following error: "sqlalchemy.exc.OperationalError: (pymssql.OperationalError) (20009, 'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist\nNet-Lib error during Unknown error (10035)\n')"

The question I need answering is, how do I start talking to my database using SQLalchemy?

The code I am using is as follows: from sqlalchemy import *

    engine = create_engine('mssql+pymssql://Han & Lew:@SlugarPlum:1433/Users')

    m = MetaData()

    t = Table('t', m,
            Column('id', Integer, primary_key=True),
            Column('x', Integer))

    m.create_all(engine)

Yes, my PC is called SlugarPlum. User is Han & Lew. And my server is called THELROYSERVER. DSN = 1433. No password. (I don't know if it is wise that I am giving this information online but the data I have is not sensitive so I guess it's worth a shot.)

Also, if anyone can direct me to an ultra-beginners resource for Python-SQL server that would be awesome as I am getting beaten up by how complex this seems to be!

Answer 1

Here's a connect function and example for connecting via pyodbc. Connecting via pymssql should be as easy as formatting the connecting string for pymssql. I've provided Windows and Linux options, but only tested on Linux. I hope it helps.

def sqlalchemy_connect(connect_string): 
    """ Connect to the database via ODBC, start SQL Alchemy engine. """

    def connect():
        return pyodbc.connect(connect_string, autocommit=True)

    db = create_engine('mssql://', creator=connect)
    db.echo = False

    return db

def main():
    global DBCONN

    # Linux with FreeTDS
    connect_string = "DRIVER={FreeTDS};SERVER=<server name>;PORT=<port num>;DATABASE=<db>;UID=<user>;PWD=<password>;TDS_Version=<version num>;"

    # Windows with SQL Server
    connect_string = "DRIVER={SQL Server};SERVER=<server name>;PORT=<port num>;DATABASE=<db>;UID=<user>;PWD=<password>;"

    DBCONN = sqlalchemy_connect(connect_string)


if __name__ == "__main__":
    main()