5

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!

Improve this question
2
  • 1
    Gave you confirm your credentials work using SQL server studio manager? Commented Jun 13, 2015 at 22:25
  • Thanks for getting back to me. I have logged in and used the management console and have a Microsoft login etc. Commented Jun 13, 2015 at 23:12

1 Answer 1

Reset to default
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()
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.