Python SQLObject connection to MS SQL Server

Hi there,

I am trying to get started with a small util using Python and SQLObject where I want to use it with MS SQL server.

It is difficult to find a Python example 'out there' on how to code the connection for SQLObject for a SQL server.

Can someone with the expertise help me with this?

Many thanks in advance, for some help on this.

Thx Scott
srbarker8Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

clockwatcherCommented:
The following connect uri worked for me:
connection_string = 'mssql://username:password@server/database'

Open in new window

Here's a full example.
from sqlobject import *

class Person(SQLObject):
    firstName = StringCol()
    lastName = StringCol()

connection_string = "mssql://sa:password@server_name/exampledb"
connection = connectionForURI(connection_string)
sqlhub.processConnection = connection

Person.createTable()
print "Created Table"
Person(firstName='John', lastName='Doe')
print "Created Record"

connection.transaction().commit()

Open in new window

In the extremely limited amount of testing that I did with it, the pymssql library doesn't seem to play well with the sqlobject autocommit.  I had to explicitly commit the transaction.

I also didn't see a nice way to get the pymssql library to connect to a sql server named instance.  I didn't do a whole lot of messing with it but the only way that I could get that working was to force the instance to a specific port and then specify the port number in the connect.
mssql://sa:password@server_name:port_number/exampledb

Open in new window

I'm much more of a SQLAlchemy user (first I've ever even heard of SQLObject) and I typically go with pyodbc rather than pymssql when connecting to MS SQL, but the above worked for me.  Client was debian.  The server was SQLExpress 2014.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
srbarker8Author Commented:
Hi there,

Thanks so much for responding.   Your connection string did the trick!  

However, I tried to map it to the MS SQL database in question, and it just blows up on anything that has an underscore in the column-name.  

As I am trying to make something that has to use this existing MS SQL database (without changing its structure) I am going to have to dump this SQLObject idea.

The reason I was looking at it was more interested in dealing with a library that basically functioned from an "active record" perspective and then just dealt with all the CRUD operations.

Given what I am dealing with ...with this legacy system, I simply wanted to have the CRUD operations taken care of without worrying about implied key fields, foreign key relations.  These I was willing to handle on my own.

So, as a result, I had chosen to look at SQLObject and not SQLAlchemy...as it appeared more simplistic.

Now that I know that it won't work....I'd ask you this:

Will SQLAlchemy work for me  - since you are an expert in that product.

Scott
0
clockwatcherCommented:
Underscores in your database are represented by mixed case python variable names in SQLObject:
class Person(SQLObject):
    firstName = StringCol()
    lastName = StringCol()
    anotherExampleField = StringCol()

Open in new window

Translates to a SQL table with field names: first_name, last_name, and another_example_field.

But I would suggest giving SQLAlchemy a try.  It's a pretty solid ORM and should handle pretty much anything you want to throw at it.   It's declarative feature (http://sqlalchemy.readthedocs.org/en/doc_nav/orm/extensions/declarative.html) combines the features of the Table, mapper, and object class-- appearing to give you pretty much the exact same thiing that SQLObject gives you.  But with a lot more options if you need it.

The tutorial is a pretty good starting point -- http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html

MS SQL specific details are here:  http://docs.sqlalchemy.org/en/rel_0_6/dialects/mssql.html

Connecting using pymssql:  http://docs.sqlalchemy.org/en/rel_0_6/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pymssql
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Python

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.