Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 792
  • Last Modified:

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
0
srbarker8
Asked:
srbarker8
  • 2
1 Solution
 
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
 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now