Pythona, create 2D Numpy array and append data vertically-CodePudding

I have an SQL query that creates an array with 9 entries, I want to create a table with Numpy and append data as rows

The following code gives me an error

ValueError: all the input arrays must have same number of dimensions, but the array at index 0 has 2 dimension(s) and the array at index 1 has 1 dimension(s)

How can initialize the numpy array correctly, and append the array as a row to the table,

sql_query = "select top 100 Passed, f.ID, Yld, Line, Location, Type, Name, ErrorID, Site from dw.table1 f join dw.table2 d on f.ID = d.ID where Type like '%test%'"
table_array = numpy.empty((0, 9))  
cursor.execute(sql_query)
row = cursor.fetchone()
while row:
    table_array = numpy.append(table_array, row, axis=0)
    row = cursor.fetchone()

CodePudding user response：

Turns out I cannot simply append SQL row as a numpy array, had to fix it this way:

table_array = numpy.append(
    table_array, numpy.array([[row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8]]]), axis=0)

CodePudding user response：

Don't repeatedly append to a numpy array in a loop. Since numpy arrays are contiguous blocks of memory, that requires the entire array to be copied over to new memory. This operation can slow down your loop significantly. Instead, pre-allocate the amount of memory you will need, and assign values when you get them from your query. When you do this, you can simply set one row of your array to the row you get from your query (assuming the type of all elements of your query is compatible with your numpy array)

table_array = numpy.empty((100, 9))  # You know you're getting the top 100
row_num = 0
cursor.execute(sql_query)
row = cursor.fetchone()
while row and row_num < table_array.shape[0]:
    table_array[row_num, :] = row
    row = cursor.fetchone()
    row_num  = 1

# If you want, you can then slice away the unfilled rows.
table_array = table_array[:row_num, :]