[SOLVED] loadtxt to structured array and adding one column with value from filename

loadtxt to structured array and adding one column with value from filename

i`m new to python and nupmy.

I have to import some Data from txt-File and insert it to a postgresql database.

I read the data this way:

type_definitions = ([('StationID', 'S4'), ('East', np.float), ('North', np.float), ('Height', np.float)])
filename = os.path.join(directory, file)
day = file.split('_')[2]
day = DoW.split('.')[0]
DataSet[day] = np.loadtxt(fname=filename, usecols=[0, 1, 2, 3], dtype=type_definitions)

This works ok for me. (It`s part of a loop over a couple of files. Each "day" is one file) Next step is to join the DataSet values to a string for the sql query:

values = ', '.join(map(str, DataSet[day]))
sql = "INSERT INTO tm_utm (day, station, east, north, height) VALUES {}".format(values)

The 'day' i have to add to the sql-query is not part of the text-file, but it is in the name of the file. So i splitted the filname for the 'day'.

Unfortunately i fail to write the 'day' in the first column of values. I tried a couple of different things like adding an array to the DataSet:

day2 = (np.ones(len(DataSet[Dow]['Stations'])) * int(day))

But i fail to add the day2-array to the DataSet structure.

Any suggestion, how to add the 'day' value? Thank you for help!

Edit: Here is, what the sql query lookslike. The Values for the day are missing.

INSERT INTO tm_utm (day, station, east, north, height) VALUES (b'2547', 32394691.312, 5693210.5467, 264.5246), (b'6578', 32362171.6427, 5702679.4317, 217.2954) ...

Solution

Ok,i did it. Not really elegant,but it works.

weekday = int(day)
for i in range(0, len(DataSet[day]['StationID'])-1):
    StationID = DataSet[day]['StationID'][i].decode('UTF-8')
    East = DataSet[day]['East'][i]
    North = DataSet[day]['North'][i]
    Height = DataSet[day]['Height'][i]
    sql_query = "INSERT INTO tm_utm (day, station, east, north, height) VALUES ({}, \'{}\', {}, {}, {})".format(weekday, StationID, East, North, Height)
    print(sql_query)

INSERT INTO tm_utm (day, station, east, north, height) VALUES (21160, 'ABFH', 32319923.5979, 5656881.7123, 156.0891)