Solved

Python SQLObject connection to MS SQL Server

Posted on 2014-10-03
3
654 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Plenty of writing has gone on the web trying to compare Python with other competitive programming languages and vice versa. However, not much has been put into a wholistic perspective. This article should help you decide whether to adopt Python as a…
Less strange, but still introduction This introduction was added (1st August, 2011) to reflect some reactions.  Firstly, the term basics in the title of the article...  As any other word, it is a symbol with meaning attached to the word by some a…
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 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…
Suggested Courses

630 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