Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Arranging SQL Extract
#1
Hello Everyone,

I'm trying to extract the data content from SQL DB to Python and process and send an email (everything to be automated). However, i'm unable to do it as I am new to this and finding it difficult.

I have a DB columns with following fields and need to extract the data like below:
User Ticket TimeSpent
Raju T97234 30
Ram T23422 60
John T23423 45
Raju T97231 50
Raju T97234 25
Sam T07074 45
Raju T63482 60
Ram T72934 20
John T19384 15
Sam T97744 90

I am unable to get the data like this, it just comes with the extract without header, I am using pyodbc to do it.
Q1: What is the best library to work with SQL?
Q2: How can I organize data and groupby the User Column?
Q3: The output I get is only data in the list, not the column header ('User','Ticket','TimeSpent')

import pyodbc
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=MSSQLSRV0001\MSSQLINS100;DATABASE=TicketTracker;UID=SUser;PWD=password') 

cursor = conn.cursor()
cursor.execute('SELECT * FROM TicketTracker.dbo.TicketRecords')
myrow = cursor.execute('SELECT * FROM TicketTracker.dbo.TicketRecords')
result = myrow.fetchall()
result
Output:
('Raju','T97234','30') ('Ram','T23422','60') ('John','T23423','45')
Reply
#2
Regarding question 3: of course you don't get the column names from the query, since they aren't part of the data but only part of the schema. You can of course print them yourself (and possibly SQL Server has a way to obtain them, but I don't know).

2. Does an SQL "GROUP BY" not do what you want? If not, perhaps explain in more detail what you want.
Reply
#3
Thank you for your response
I would like to do it in Python rather than SQL.
I want to group my 'User' Column because i want to find the count of each users to find their efforts.
Reply
#4
any response please
Reply
#5
pandas dataframe groupby would help,

import pandas as pd
df=pd.read_csv("Book11.csv",names=['Users','Ticket','TimeSpent'], header=1,index_col=None)
blankIndex=[''] * len(df)
df.index=blankIndex
print(df)  #complete data
pd.to_numeric(df['TimeSpent']) #convert timespent data to numeric
print(df.groupby('Users').count())
print(df.groupby('Users').sum())
Output:
C:\Users\testuser\Downloads\DL>python test.py Users Ticket TimeSpent Ram T23422 60 John T23423 45 Raju T97231 50 Raju T97234 25 Sam T07074 45 Raju T63482 60 Ram T72934 20 John T19384 15 Sam T97744 90 Ticket TimeSpent Users John 2 2 Raju 3 3 Ram 2 2 Sam 2 2 TimeSpent Users John 60 Raju 135 Ram 80 Sam 135
Best Regards,
Sandeep

GANGA SANDEEP KUMAR
Reply


Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020