Showing posts with label written. Show all posts
Showing posts with label written. Show all posts

Wednesday, March 7, 2012

i need to connect python to sql2005

hi
i need to connect
a small program written in Python
to a SqlServer 2005 database
how con i do it ?
can anyone link me to a resource ?
thanks
You should be able to connect with ODBC jsut like any other language. I
would start by looking at the Python web site:
http://www.python.org/
but a simple google search for python odbc gives a lot of hits. It's amazing
what a few keystrokes can buy you these days.
Andrew J. Kelly SQL MVP
"keyser soze" <bajopalabra@.hotmail.com> wrote in message
news:O93RJ1YbHHA.4656@.TK2MSFTNGP03.phx.gbl...
> hi
> i need to connect
> a small program written in Python
> to a SqlServer 2005 database
> how con i do it ?
> can anyone link me to a resource ?
> thanks
>
|||of course, Andrew
i googled that...
( does exist anybody
that don't find answers on internet first ?! :-)
i promisse i did it,
what i found is an old adodbapi
developed near 2003, that is
before sql2005 being released
i wonder if there is other ways
thanks & regards!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
news:OrwyjTabHHA.4544@.TK2MSFTNGP03.phx.gbl...
> You should be able to connect with ODBC jsut like any other language. I
> would start by looking at the Python web site:
> http://www.python.org/
> but a simple google search for python odbc gives a lot of hits. It's
amazing
> what a few keystrokes can buy you these days.
> --
> Andrew J. Kelly SQL MVP
> "keyser soze" <bajopalabra@.hotmail.com> wrote in message
> news:O93RJ1YbHHA.4656@.TK2MSFTNGP03.phx.gbl...
>
|||If you google for "python odbc sql 2005" the very first hit is a 3rd party
that has 2005 drivers. There are several other links that appear useful as
well. Hopefully one of those will do the trick.
Andrew J. Kelly SQL MVP
"keyser soze" <bajopalabra@.hotmail.com> wrote in message
news:un3HEf6bHHA.260@.TK2MSFTNGP02.phx.gbl...
> of course, Andrew
> i googled that...
> ( does exist anybody
> that don't find answers on internet first ?! :-)
> i promisse i did it,
> what i found is an old adodbapi
> developed near 2003, that is
> before sql2005 being released
>
> i wonder if there is other ways
> thanks & regards!
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
> news:OrwyjTabHHA.4544@.TK2MSFTNGP03.phx.gbl...
> amazing
>
|||well, i'm looking for something """free"""
but you are right,
i'll search in depth
thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
news:%23G2y$I9bHHA.2316@.TK2MSFTNGP04.phx.gbl...
> If you google for "python odbc sql 2005" the very first hit is a 3rd party
> that has 2005 drivers. There are several other links that appear useful as
> well. Hopefully one of those will do the trick.
> --
> Andrew J. Kelly SQL MVP
> "keyser soze" <bajopalabra@.hotmail.com> wrote in message
> news:un3HEf6bHHA.260@.TK2MSFTNGP02.phx.gbl...
>
|||OK well that may narrow the search down a bit.
Andrew J. Kelly SQL MVP
"keyser soze" <bajopalabra@.hotmail.com> wrote in message
news:Oz$N3X9bHHA.1216@.TK2MSFTNGP03.phx.gbl...
> well, i'm looking for something """free"""
> but you are right,
> i'll search in depth
> thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
> news:%23G2y$I9bHHA.2316@.TK2MSFTNGP04.phx.gbl...
>
|||I just googled it and found a free one on the first hit - a
download from sourceforge:
About pymssql
This module provides access to Microsoft SQL Servers from
Python scripts. It's the most efficient method of accessing
MS SQL Server's data from Python scripts.
http://pymssql.sourceforge.net/
And in the first few hits there was this:
Microsoft SQL Server Express / Python HOWTO
http://www.time-travellers.org/shane/howtos/MS-SQL-Express-Python-HOWTO.html
-Sue
On Mon, 26 Mar 2007 15:50:43 -0300, "keyser soze"
<bajopalabra@.hotmail.com> wrote:

>well, i'm looking for something """free"""
>but you are right,
>i'll search in depth
>thanks
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
>news:%23G2y$I9bHHA.2316@.TK2MSFTNGP04.phx.gbl...
>
|||come on, hit me...
anyway, thanks both
for reply with respect
you are very kind
|<aiser Soze
"Sue Hoegemeier" <Sue_H@.nomail.please> escribi en el mensaje
news:jfmg03toi97nkhslost93k5ir0s5ina8jc@.4ax.com...
> I just googled it and found a free one on the first hit - a
> download from sourceforge:
> About pymssql
> This module provides access to Microsoft SQL Servers from
> Python scripts. It's the most efficient method of accessing
> MS SQL Server's data from Python scripts.
> http://pymssql.sourceforge.net/
> And in the first few hits there was this:
> Microsoft SQL Server Express / Python HOWTO
>
http://www.time-travellers.org/shane/howtos/MS-SQL-Express-Python-HOWTO.html[vbcol=seagreen]
> -Sue
> On Mon, 26 Mar 2007 15:50:43 -0300, "keyser soze"
> <bajopalabra@.hotmail.com> wrote:
party[vbcol=seagreen]
as[vbcol=seagreen]
mensaje[vbcol=seagreen]
language. I
>

i need to connect python to sql2005

hi
i need to connect
a small program written in Python
to a SqlServer 2005 database
how con i do it ?
can anyone link me to a resource ?
thanksYou should be able to connect with ODBC jsut like any other language. I
would start by looking at the Python web site:
http://www.python.org/
but a simple google search for Python odbc gives a lot of hits. It's amazing
what a few keystrokes can buy you these days.
Andrew J. Kelly SQL MVP
"keyser soze" <bajopalabra@.hotmail.com> wrote in message
news:O93RJ1YbHHA.4656@.TK2MSFTNGP03.phx.gbl...
> hi
> i need to connect
> a small program written in Python
> to a SqlServer 2005 database
> how con i do it ?
> can anyone link me to a resource ?
> thanks
>|||of course, Andrew
i googled that...
( does exist anybody
that don't find answers on internet first ?! :-)
i promisse i did it,
what i found is an old adodbapi
developed near 2003, that is
before sql2005 being released
i wonder if there is other ways
thanks & regards!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
news:OrwyjTabHHA.4544@.TK2MSFTNGP03.phx.gbl...
> You should be able to connect with ODBC jsut like any other language. I
> would start by looking at the Python web site:
> http://www.python.org/
> but a simple google search for Python odbc gives a lot of hits. It's
amazing
> what a few keystrokes can buy you these days.
> --
> Andrew J. Kelly SQL MVP
> "keyser soze" <bajopalabra@.hotmail.com> wrote in message
> news:O93RJ1YbHHA.4656@.TK2MSFTNGP03.phx.gbl...
>|||If you google for "python odbc sql 2005" the very first hit is a 3rd party
that has 2005 drivers. There are several other links that appear useful as
well. Hopefully one of those will do the trick.
Andrew J. Kelly SQL MVP
"keyser soze" <bajopalabra@.hotmail.com> wrote in message
news:un3HEf6bHHA.260@.TK2MSFTNGP02.phx.gbl...
> of course, Andrew
> i googled that...
> ( does exist anybody
> that don't find answers on internet first ?! :-)
> i promisse i did it,
> what i found is an old adodbapi
> developed near 2003, that is
> before sql2005 being released
>
> i wonder if there is other ways
> thanks & regards!
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
> news:OrwyjTabHHA.4544@.TK2MSFTNGP03.phx.gbl...
> amazing
>|||well, i'm looking for something """free"""
but you are right,
i'll search in depth
thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
news:%23G2y$I9bHHA.2316@.TK2MSFTNGP04.phx.gbl...
> If you google for "python odbc sql 2005" the very first hit is a 3rd party
> that has 2005 drivers. There are several other links that appear useful as
> well. Hopefully one of those will do the trick.
> --
> Andrew J. Kelly SQL MVP
> "keyser soze" <bajopalabra@.hotmail.com> wrote in message
> news:un3HEf6bHHA.260@.TK2MSFTNGP02.phx.gbl...
>|||OK well that may narrow the search down a bit.
Andrew J. Kelly SQL MVP
"keyser soze" <bajopalabra@.hotmail.com> wrote in message
news:Oz$N3X9bHHA.1216@.TK2MSFTNGP03.phx.gbl...
> well, i'm looking for something """free"""
> but you are right,
> i'll search in depth
> thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
> news:%23G2y$I9bHHA.2316@.TK2MSFTNGP04.phx.gbl...
>|||I just googled it and found a free one on the first hit - a
download from sourceforge:
About pymssql
This module provides access to Microsoft SQL Servers from
Python scripts. It's the most efficient method of accessing
MS SQL Server's data from Python scripts.
http://pymssql.sourceforge.net/
And in the first few hits there was this:
Microsoft SQL Server Express / Python HOWTO
http://www.time-travellers.org/shan...thon-HOWTO.html
-Sue
On Mon, 26 Mar 2007 15:50:43 -0300, "keyser soze"
<bajopalabra@.hotmail.com> wrote:

>well, i'm looking for something """free"""
>but you are right,
>i'll search in depth
>thanks
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escribi en el mensaje
>news:%23G2y$I9bHHA.2316@.TK2MSFTNGP04.phx.gbl...
>|||Lines: 102
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1807
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1896
NNTP-Posting-Host: line40.comsat.net.ar 200.47.78.40
Xref: leafnode.mcse.ms microsoft.public.sqlserver.connect:1884
come on, hit me...
anyway, thanks both
for reply with respect
you are very kind
|<aiser Soze
"Sue Hoegemeier" <Sue_H@.nomail.please> escribi en el mensaje
news:jfmg03toi97nkhslost93k5ir0s5ina8jc@.
4ax.com...
> I just googled it and found a free one on the first hit - a
> download from sourceforge:
> About pymssql
> This module provides access to Microsoft SQL Servers from
> Python scripts. It's the most efficient method of accessing
> MS SQL Server's data from Python scripts.
> http://pymssql.sourceforge.net/
> And in the first few hits there was this:
> Microsoft SQL Server Express / Python HOWTO
>
http://www.time-travellers.org/shan...thon-HOWTO.html
> -Sue
> On Mon, 26 Mar 2007 15:50:43 -0300, "keyser soze"
> <bajopalabra@.hotmail.com> wrote:
>
party[vbcol=seagreen]
as[vbcol=seagreen]
mensaje[vbcol=seagreen]
language. I[vbcol=seagreen]
>

I need some help with a complex query

I've written a lot of queries in the past, but I'm having a lot of trouble with this one.

I have 3 tables: Thread, Reply, User

Thread has these relevant fields: ThreadID, UserID, DTStamp, Subject

Reply has these relevant fields: ThreadID, UserID, DTStamp

User has these relavent fields: UserID, Name

A few details:
- Thread and Reply connect with ThreadID
- Thread and Reply both connect to Person with UserID
- Thread and Reply share a 1 to many relationship (1 Thread with many Replies)
- It is also possible there are no replies

What I need is a query that looks at thedata and returns a set of records with the following data fields:

Subject: The Subject of the thread
CreationDate: The Date that Subject thread was created
Author: The Name of the UserID that created that thread
Replies: The Number of Replies to the Subject thread
LastPost: The Date of the Last Reply
LastPostAuthor: The Name of the UserID of the Last Reply

And I need this all sorted by Date of Last Reply

Is this even doable? Are there any suggestions on the best way to get started?


Thanks in advance,

Chris

Hi Chris my friend, I will help you on this.

First run the following SQL to create a function: -

CREATE FUNCTION fn_GetLastPostUserNameByThreadID
(
@.ThreadID AS INT
)

RETURNS varchar(30)

AS

BEGIN

DECLARE @.Author AS VARCHAR(30)

SET @.Author = (
SELECT Name FROM User WHERE UserID =
(SELECT TOP 1 USERID FROM Reply WHERE ThreadID = @.ThreadID
ORDER BY dtstamp DESC)
)

RETURN @.Author

END

Now for the SQL to get you the results: -

SELECT thread.Subject,
thread.dtstamp,
user.name,
COUNT(reply.threadid) AS Replies,
MAX(reply.dtstamp) AS LastPost,
dbo.fn_GetLastPostUserNameByThreadID(thread.threadid) AS LastPostAuthor

FROM thread

INNER JOIN user on thread.userid = user.userid
LEFT OUTER JOIN reply ON thread.threadid = reply.threadid

GROUP BY Subject,
thread.dtstamp,
user.name,
dbo.fn_GetLastPostUserNameByThreadID(thread.threadid)

ORDER BY reply.dtstamp DESC

|||

Thank you so much! I can't wait to try this out.

I have created stored procedures before, but not functions, so I have a couple follow-up questions.

When I look at my database through SQL Server Management Studio Express, I see funtions and under that 4 categories:

Table-valued Functions|||

Hi Chris,

A Table-valued function returns a table. To use one, you do "SELECT * FROM dbo.MyFunction()". The following example takes a string and turns it into a table.

create FUNCTION dbo.StringArrayIntoTable
(
@.String VARCHAR(8000),
@.Separator VARCHAR(1)
)
RETURNS @.tblStrings TABLE(Item VARCHAR(8000))

AS

BEGIN

DECLARE @.pos INT,
@.SubStr VARCHAR(10)


SET @.pos = CHARINDEX(@.Separator, @.String)

WHILE @.pos > 0
BEGIN
SET @.SubStr = SUBSTRING(@.String, 0, @.pos)

INSERT INTO @.tblStrings (Item) VALUES (@.SubStr)

SET @.String = SUBSTRING(@.String, LEN(@.SubStr) + 2, LEN(@.String) - LEN(@.SubStr) + 1)
SET @.pos = CHARINDEX(@.Separator, @.String)
END

INSERT INTO @.tblStrings (Item) VALUES (@.String)
RETURN
END

The first parameter is the string. The second is the separator. Test it with the following: -

select * from dbo.StringArrayIntoTable('red,blue,yellow', ',')
select * from dbo.StringArrayIntoTable('USA|Germany|Russia|UK', '|')

This is useful if you need to pass an array of values into a stored procedure. Just pass in a string that you can separate!

Scalar valued functions only return one value and when using them, you don't use "SELECT * FROM", just "SELECT FunctionName()". You would create one of these if you wanted a function that only returned one value, like the one I gave to you that returns 1 varchar; the author's name.

Aggregate functions are built-in scalar valued functions. For example, SUM and AVG; select SUM(SaleValue) AS Total, AVG(SaleValue) AS AverageSale FROM tblSales.

As for System functions, some are more useful than others. For example, if you want to return 0 for SaleValue if the field value is null, you can use SELECT IsNull(SaleValue, 0) AS SaleValue. It returns whatever the SalesValue is, but 0 if it is NULL.

As for your second question, always use a stored procedure. When a stored procedure cannot give you directly what you need without calling a function, as in the problem you posted, have the procedure call a function. Stored procedures have pre-compiled execution plans and execute more efficiently. On the other hand, performance hits are associated with functions so use them only when necessary.

By the way. I noticed that within the SQL I gave to you I used "user" to refer to the author table. This cannot be right because user is not a valid table name. I did not realize this at first because I did it in Notepad. Please substitute this with the correct table name and the SQL should work.

In return for all of this help, I only ask that you mark me as the answerer of your question in this forum.

Kind regards

Scotty

|||

Scotty,

Wow! I can't begin to thank you enough for your help. Your code and explanations are just what I needed.

FYI, my table is called User. I probably should change the name, but for now I just refer to it like this [User] and it works okay.

Also, I made one other change to the code, I changed the last line to "Order By LastPost Desc" as it had a problem with "reply.dtstamp" not being part of the result set.

Thanks again,

Chris

p.s. If you want to see your code in action, feel free to check out my site MoviePoet.com early next week.

Sunday, February 19, 2012

I need help please! My Exception Handling is not working.

I need help please...

I am using exception handling and Transaction handling combined.
The code is written to delete a record from 2 different table.
The purpose of using exception and transaction handling combined is tomake sure that the record is deleted in BOTH table. If there is errorwhile deleting a record from one of the tables, it will rollback thedatabase to the original and will stop deleting any record.

However, The Transaction handling is not working. When error occurswhile deleting a record on table A, the code still delete a record fromone table B. The transaction.RollBack() doesn't work. This is my code:

Try
Conn= NewSqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Conn.Open()
Trans = sConn.BeginTransaction

For Each row In GridView1.Rows
Dim sql As String = "SELECT * FROM Member
Using Cmd As New SqlCommand(sql, Conn, Trans)
SqlDataSource1.DeleteParameters("MemberID").DefaultValue =DropDownListMember.SelectedValue
SqlDataSource1.Delete()
SqlDataSource2.DeleteParameters("CarID").DefaultValue =DropDownListCar.SelectedValue
SqlDataSource2.Delete()
End Using
End If
Next
sTrans.Commit()
Label1.Text = "deleted."
Catch ex As Exception
If sTrans IsNot Nothing Then
sTrans.Rollback()
End If
Label1.Text = "error"
Return
Finally
If sConn IsNot Nothing Then
sConn.Close()
End If
End Try

soo much thanks in advanced!!!got my question answered!

I found it myself by trying change the codes. thanks all! (?)