Solved

Python SQLObject connection to MS SQL Server

Posted on 2014-10-03
3
521 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 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sequence is something that used to store data in it in very simple words. Let us just create a list first. To create a list first of all we need to give a name to our list which I have taken as “COURSE” followed by equals sign and finally enclosed …
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 strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now