public static void InsertQuandlDatasets(QuandlDatasetGroup datasetGroup)
{
// Make query before oppening the connection to make sure it doesn't time out.
string queryFilePath = datasetGroup.MakeInsertQueryFile();
FileInfo file = new FileInfo(queryFilePath);
string query = file.OpenText().ReadToEnd();
using (var conn = new NpgsqlConnection(Utils.Constants.CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand())
{
// Open connection
// ===============================================================
conn.Open();
cmd.Connection = conn;
cmd.CommandText = query;
try { cmd.ExecuteNonQuery(); }
catch (PostgresException ex)
{
conn.Close();
Helpers.ExitWithError(ex.Message);
}
// Close connection
// ===============================================================
conn.Close();
}
}
}
private static async Task DownloadDatasetsDataAsync(QuandlDatasetGroup datasetGroup, int to)
{
await Task.WhenAll(datasetGroup.Datasets.Select(d => DownloadDatasetDataAsync(d, to)));
Console.WriteLine();
// Reset datasets fetched count for next group
datasetsFetched = 0;
}
// Where all the datasets data will go
public static void CreateQuandlDatasetDataTable(QuandlDatasetGroup datasetGroup)
{
// Do not make data tables without date colum
if (!datasetGroup.HasColumnDate())
{
return;
}
using (var conn = new NpgsqlConnection(Constants.CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand())
{
// Open connection
// ===============================================================
conn.Open();
// Query
string query = @"CREATE TABLE quandl." + datasetGroup.DatabaseCode + @"(
DatasetCode TEXT,
DatabaseCode TEXT,
Name TEXT NOT NULL,
Transform TEXT,
DatabaseId BIGINT,
date_insert timestamptz," +
// Column names [specific data]
//datasetGroup.MakeDatasetsExtraColumnsWithDataType() + @",
datasetGroup.MakePrimaryKeysForCreate() + @",
PRIMARY KEY(" + string.Join(", ", datasetGroup.PrimaryKeys()) + @")
);";
cmd.Connection = conn;
cmd.CommandText = query;
try { cmd.ExecuteNonQuery(); }
catch (PostgresException ex)
{
//Console.WriteLine(ex.Message);
if (ex.SqlState == "42P07")
{
createDateInsertColumnIfNeeded("quandl." + datasetGroup.DatabaseCode);
ConsoleInformer.Inform("Table model [" + datasetGroup.DatabaseCode + "] already exists. Using it");
}
else
{
conn.Close(); Helpers.ExitWithError(ex.Message);
}
}
ConsoleInformer.PrintProgress("2C", "[" + datasetGroup.DatabaseCode + "] Creating table model: ", "100%");
// Close connection
// ===============================================================
conn.Close();
}
}
}
// Detects the most recent data date
// Returns a list of pairs:
// [date, datasetcode]
// Ex. ['2016-10-21', 'AAPL']
// ['2016-10-26', 'FB']
// ...
public static List <Tuple <DateTime, string> > GetNewestImportedData(QuandlDatasetGroup datasetGroup)
{
// Query
string query = @"SELECT max(" + datasetGroup.GetColumnDate() + @") as date, datasetcode
FROM quandl." + datasetGroup.DatabaseCode + @"
GROUP BY datasetcode";
List <Tuple <DateTime, string> > datasetNewestDateList = new List <Tuple <DateTime, string> >();
using (var conn = new NpgsqlConnection(Constants.CONNECTION_STRING))
{
using (var cmd = new NpgsqlCommand(query))
{
// Open connection
// ===============================================================
conn.Open();
cmd.Connection = conn;
try
{
// Execute the query and obtain a result set
NpgsqlDataReader dr = cmd.ExecuteReader();
// Make tuple for each row
// Add tuple to list
while (dr.Read())
{
datasetNewestDateList.Add(new Tuple <DateTime, string>(dr.GetDateTime(dr.GetOrdinal("date")), (string)dr["datasetcode"]));
}
}
catch (PostgresException ex)
{
if (ex.SqlState == "42P01")
{
// The table does not exists.
// That means this is the first time importing this dataset group
datasetNewestDateList.Add(new Tuple <DateTime, string>(DateTime.MinValue, datasetGroup.Datasets[0].DatasetCode));
}
else
{
conn.Close();
Helpers.ExitWithError(ex.Message);
}
}
//ConsoleInformer.PrintProgress("0C", "Querying imported datasets: ", "100%");
// Close connection
// ===============================================================
conn.Close();
}
}
return(datasetNewestDateList);
}
public static void InsertQuandlDatasetGroup(QuandlDatasetGroup datasetGroup)
{
try
{
datasetGroup.MakeInsertQuery();
ConsoleInformer.PrintProgress("3B", "Inserting datasets for [" + datasetGroup.DatabaseCode + "]");
}
catch (Exception ex)
{
// Write
Utils.ConsoleInformer.Inform("Some unexpected stuff happened when inserting [" + datasetGroup.DatabaseCode + "]. See the log for more info");
Utils.Helpers.Log("Something worng happened when trying to insert [" + datasetGroup.DatabaseCode + "] datasets. Check log", ex.Message);
}
}
private static DatasetsResponse DownloadDataset(int page, QuandlDatabase database)
{
using (PatientWebClient client = new PatientWebClient())
{
try
{
var json = client.DownloadString("https://www.quandl.com/api/v3/datasets.json?database_code=" + database.DatabaseCode + "&sort_by=id&page=" + page + "&api_key=" + Utils.Constants.API_KEY);
DatasetsResponse response =
JsonConvert.DeserializeObject <DatasetsResponse>(json, new JsonSerializerSettings {
ContractResolver = Utils.Converters.MakeUnderscoreContract()
});
pagesSum++;
Utils.ConsoleInformer.PrintProgress("1B", "Fetching datasets [" + database.DatabaseCode + "]: ", Utils.Helpers.GetPercent(pagesSum, response.Meta.TotalPages).ToString() + "%");
// Add it to its own group
datasetsGroups.Find(d => d.DatabaseCode == database.DatabaseCode).Datasets.AddRange(response.Datasets);
// Insert datasets page directly
QuandlDatasetGroup datasetGroup = new QuandlDatasetGroup()
{
DatabaseCode = database.DatabaseCode, Datasets = response.Datasets
};
PostgresHelpers.QuandlDatasetActions.InsertQuandlDatasetGroup(datasetGroup);
return(response);
}
catch (Exception e)
{
if (e.Message.Contains("(429)") && !blocked)
{
Utils.ConsoleInformer.Inform("Looks like quandl just blocked you");
blocked = true;
}
// Log
Utils.Helpers.Log("Failed to fetch page: " + page + " from Database: [" + database.DatabaseCode + "]", "Ex: " + e.Message);
// Add error to inform and log later
errors.Add(new Tuple <string, string> ("Failed to fetch page: " + page + " from Database: [" + database.DatabaseCode + "]", "Ex: " + e.Message));
return(new DatasetsResponse());
}
}
}
