Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Python SQLObject connection to MS SQL Server

Posted on 2014-10-03
3
Medium Priority
?
743 Views
Last Modified: 2014-10-07
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
Comment
Question by:srbarker8
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
clockwatcher earned 2000 total points
ID: 40360939
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
 

Author Comment

by:srbarker8
ID: 40364729
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
 
LVL 25

Expert Comment

by:clockwatcher
ID: 40365231
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Installing Python 2.7.3 version on Windows operating system For installing Python first we need to download Python's latest version from URL" www.python.org " You can also get information on Python scripting language from the above mentioned we…
Dictionaries contain key:value pairs. Which means a collection of tuples with an attribute name and an assigned value to it. The semicolon present in between each key and values and attribute with values are delimited with a comma.  In python we can…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question