Arranging SQL Extract - Printable Version

Arranging SQL Extract - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Arranging SQL Extract (/thread-23455.html)

Arranging SQL Extract - Ranjirock - Dec-31-2019

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')

RE: Arranging SQL Extract - ndc85430 - Dec-31-2019

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.

RE: Arranging SQL Extract - Ranjirock - Dec-31-2019

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.

RE: Arranging SQL Extract - Ranjirock - Jan-02-2020

any response please

RE: Arranging SQL Extract - sandeep_ganga - Jan-02-2020

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