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()); } } }