CODEWITHSUNDEEP
pythonpython-2.7numpypyodbc
Dan
Dan

Reputation: 45752

Correct way to fill a list from a SQL query using pyodbc

Is this the correct way to get a list from a SQL query in Python 2.7? Using a loop just seems somehow spurious. Is there a neater better way?

import numpy as np
import pyodbc as SQL
from datetime import datetime

con = SQL.connect('Driver={SQL Server};Server=MyServer; Database=MyDB; UID=MyUser; PWD=MyPassword')
cursor = con.cursor()

#Function to convert the unicode dates returned by SQL Server into Python datetime objects
ConvertToDate = lambda s:datetime.strptime(s,"%Y-%m-%d")

#Parameters
Code = 'GBPZAR'

date_query = '''
             SELECT DISTINCT TradeDate 
             FROM MTM 
             WHERE Code = ? 
               and TradeDate > '2009-04-08' 
             ORDER BY TradeDate
            '''

#Get a list of dates from SQL
cursor.execute(date_query, [Code])
rows = cursor.fetchall()
Dates = [None]*len(rows) #Initialize array
r = 0
for row in rows:
    Dates[r] = ConvertToDate(row[0])
    r += 1

Edit:

What about when I want to put a query into a structured array? At the moment I do something like this:

#Initialize the structured array
AllData = np.zeros(num_rows, dtype=[('TradeDate', datetime),
                                    ('Expiry', datetime), 
                                    ('Moneyness', float),
                                    ('Volatility', float)])

#Iterate through the record set using the cursor and populate the structure array
r = 0
for row in cursor.execute(single_date_and_expiry_query, [TradeDate, Code, Expiry]):
    AllData[r] = (ConvertToDate(row[0]), ConvertToDate(row[1])) + row[2:] #Convert th0e date columns and concatenate the numeric columns
    r += 1

Upvotes: 0

Views: 7303

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1123520

There is no need to pre-create a list, you could use list.append() instead. This also avoids having to keep a counter to index into Dates.

I'd use a list comprehension here, looping directly over the cursor to fetch rows:

cursor.execute(date_query, [Code])
Dates = [datetime.strptime(r[0], "%Y-%m-%d") for r in cursor]

You may want to add .date() to the datetime.strptime() result to get datetime.date objects instead.

Iterating over the cursor is preferable as it avoids loading all rows as a list into memory, only to replace that list with another, processed list of dates. See the cursor.fetchall() documentation:

Since this reads all rows into memory, it should not be used if there are a lot of rows. Consider iterating over the rows instead.

To produce your numpy.array, don't prepopulate. Instead use numpy.asarray() to turn the cursor items into an array, with the help of a generator:

dtype=[('TradeDate', datetime), ('Expiry', datetime), 
       ('Moneyness', float), ('Volatility', float)]
dt = lambda v: datetime.strptime(v, "%Y-%m-%d")
filtered_rows = ((dt(r[0]), dt(r[1]) + r[2:]) for r in cursor)
all_values = np.asarray(filtered_rows, dtype=dtype)

For future reference, you can use enumerate() to produce a counter with a loop:

for r, row in enumerate(rows):
    # r starts at 0 and counts along

Upvotes: 1

Related Questions