I am using the following code to import database table into a DataFrame: ``` def import_db_table(chunk_size, offset): dfs_ct = [] j = 0 start = dt.datetime.now() df = pd.DataFrame() while True: sql_ct = "SELECT * FROM my_table limit %d offset %d" % (chunk_size, offset) dfs_ct.append(psql.read_sql_query(sql_ct, connection)) offset += chunk_size if len(dfs_ct[-1]) < chunk_size: break df = pd.concat(dfs_ct) # Convert columns to datetime columns = ['col1', 'col2', 'col3','col4', 'col5', 'col6', 'col7', 'col8', 'col9', 'col10', 'col11', 'col12', 'col13', 'col14', 'col15'] for column in columns: df[column] = pd.to_datetime(df[column], errors='coerce') # Remove the uninteresting columns columns_remove = ['col42', 'col43', 'col67','col52', 'col39', 'col48','col49', 'col50', 'col60', 'col61', 'col62', 'col63', 'col64','col75', 'col80'] for c in df.columns: if c not in columns_remove: df = df.drop(c, axis=1) j+=1 print('{} seconds: completed {} rows'.format((dt.datetime.now() - start).seconds, j*chunk_size)) return df ``` I am calling it with: ``` df = import_db_table(100000, 0) ``` This seems to be very slow - it starts with importing 100000 rows in 7 seconds but later after 1 million rows the number of seconds needed grows to 40-50 and more. Could this be improved somehow? I am using PostgreSQL, Python 3.5. `7 seconds: completed 100000 rows 17 seconds: completed 200000 rows 30 seconds: completed 300000 rows 47 seconds: completed 400000 rows 69 seconds: completed 500000 rows 92 seconds: completed 600000 rows 121 seconds: completed 700000 rows 153 seconds: completed 800000 rows 188 seconds: completed 900000 rows 228 seconds: completed 1000000 rows 271 seconds: completed 1100000 rows 318 seconds: completed 1200000 rows 368 seconds: completed 13000

The goal this method was to emulate the way EF or Dapper return to you concrete POCO classes instead of recordsets when querying your db. Our current code is riddled with iterations over recordsets and I want to consolidate to an extension method that can convert the recordset into an `Enumerable`. Review I'm concerned about how efficient this will be. When querying the db for a single or handful of records I'm not concerned but we sometimes query for an entire table (i.e. ~100,000 records) and in that case will the iterations in this start to cause performance issues? Is there a better way to resolve the recordsets to classes without iterating? Any other constructive remarks appreciated. ``` public static IEnumerable AsList(this Recordset rs) where T : new() { IEnumerable props = new List(typeof(T).GetProperties()).Where(p => p.CanWrite); List results = new List(); while (!rs.Eof) { T obj = new T(); foreach (PropertyInfo property in props) { var val = rs[property.Name].Value; if (val != null) property.SetValue(obj, Convert.ChangeType(val, property.PropertyType)); } rs.MoveNext(); results.Add(obj); } return results.AsEnumerable(); } ``` used as... ``` Recordset rs = new Recordset(); rs.Open("select * from Users", connection) IEnumerable results = rs.AsList(); ``` Post Review Update After great feedback from Heslacher and t3chb0t the following code has been finalized. Note there was a change in the requirements and sister method was created `As()` to create a single class instance from a recordset. The feedback from the review has been applied to it as well. ``` public static IList AsList(this Recordset rs) where T : class, new() { var results = new List(); if (rs == null) throw new ArgumentNullException("rs"); if (rs.Eof) return results; IList properties = GetProperties(); while (!rs.Eof) { results.Add(CreateInstance(propertie

I work at a small English school in Japan and I'm trying to create a database to handle student attendance and class scheduling. I'm using PostgreSQL for the database and I'll use PHP for interacting with it. I've looked at some examples of similar designs here and elsewhere for guidance, but since I'm still an amateur, I'd like to see if I've made any major mistakes before I take this into production. First, a diagram (full resolution): Next, the SQL code (generated by GenMyModel.com): ``` -- Create schemas CREATE SCHEMA IF NOT EXISTS System; CREATE SCHEMA IF NOT EXISTS Attendance; CREATE SCHEMA IF NOT EXISTS People; CREATE SCHEMA IF NOT EXISTS Classes; -- Create tables -- Users of the database, aka staff members who are allowed to enter attendance data CREATE TABLE IF NOT EXISTS System.users ( user_id SERIAL NOT NULL, person_id SERIAL NOT NULL, -- Links a user to their staff information user_type SERIAL NOT NULL, username VARCHAR(100) NOT NULL, password VARCHAR(500) NOT NULL, salt VARCHAR(500) NOT NULL, last_login TIMESTAMP, created TIMESTAMP NOT NULL, lastmod TIMESTAMP NOT NULL, PRIMARY KEY(user_id) ); CREATE TABLE IF NOT EXISTS System.user_types ( utype_id SERIAL NOT NULL, utype_name VARCHAR(100) NOT NULL, -- Admin vs. standard user utype_desc VARCHAR(500), created TIMESTAMP NOT NULL, lastmod TIMESTAMP NOT NULL, PRIMARY KEY(utype_id) ); CREATE TABLE IF NOT EXISTS Attendance.makeup ( makeup_id SERIAL NOT NULL, student_id SERIAL NOT NULL, original_cinstance_id SERIAL NOT NULL, makeup_cinstance_id SERIAL NOT NULL, notes VARCHAR(1000), created TIMESTAMP NOT NULL, lastmod TIMESTAMP NOT NULL, PRIMARY KEY(makeup_id) ); CREATE TABLE IF NOT EXISTS Attendance.attendance ( attendance_id SERIAL NOT NULL, cinstance_id SERIAL NOT NULL, teacher_id SERIAL NOT NULL, student_id SERIAL NOT NULL, present BOOLEAN NOT NULL, notes VARCHAR(1000), create

I have a table full of player names: I also have a table full of player penalties: I have to list all of the players names, player numbers, and the number of penalties that they've incurred. To do that, I'm using this code: ``` select players.playerno, name, ifnull(`Penalty count`, 0) as `Penalty count` from players left join (select players.playerno, count(players.playerno) as `Penalty count` from players, penalties where players.playerno = penalties.playerno group by players.playerno) as `query1` on players.playerno = query1.playerno order by playerno ``` The code works. Here's the output table: It seems like a lot of code just to merge two tables together. Is there a better way to do it? I've also read online that keeping the NULL is generally a better practice than changing it to zero, but would that apply in this case, since it's supposed to be the total number of penalties? It seems like NULL would give the impression of "no data" or "unknown data", but in this case the number is known to be zero.

I used the following Stack Overflow questions as references for writing this code: - Select columns from result set of stored procedure - What is the equivalent of String.Join on TSQL? The idea behind my stored procedure is to give an explanation of why a particular worker is unsuitable to care for a particular patient under specific circumstances. For example, you obviously can't assign an RN to do physical therapy visits. I have a Disciplines table, which has a structure that's kind of like this: ``` -------------------------------------------- | Discipline | Discipline ID | Other Field | -------------------------------------------- | RN | 1 | 2 | -------------------------------------------- | PT | 2 | 5 | -------------------------------------------- | CNA | 3 | 7 | -------------------------------------------- | MD | 4 | 23 | -------------------------------------------- ``` Obviously this is hypothetical data and I've modified the column names a bit (this is mostly for illustration). What I want is to retrieve the list of Disciplines as a single string and concatenate it with an existing string. Workers might have several disciplines. (This is determined in a table that's not shown). An example of one possible output would be `The worker does not have the appropriate disciplines. They have the following discipline(s): RN, PT` (if that employee is both a RN and a PT - not too likely, obviously, but let's assume that they are). I haven't included the entire stored procedure, but this is pretty representative and is the main part that I'm looking for a critique of: ``` -- @IsAppropriate is a bit type SELECT @IsAppropriate = dbo.fnCheckIfWorkerHasDisciplineForSvc(@BusinessUnitID, @WorkerID, @ServiceTypeID, GetDate(), GetDate()) IF @IsAppropriate = 0 BEGIN SET @output = 'The worker does not have the appropriate disciplines. They have th

I'm currently enrolled in a further education with the topic database administration. Among other chapters it contains how databases can be accessed from application-software. Because it lacks programming exercises I have programmed a bit myself. Just for getting familiar with the techniques (mainly Java JDBC) shown. I finally got the idea for a MySQL wrapper-class which hides away (at least partly) the actual SQL. Here's what I got so far: ``` package jdbc; import java.sql.*; import static java.lang.System.*; public class MySqlDb { private Connection connection; private Statement statement; public MySqlDb(String url, String dbName, String username, String password) throws SQLException { connection = DriverManager.getConnection( "jdbc:mysql://" + url + ":3306/" + dbName, username, password); } public String select(String tableName, String fields[]) { return select(tableName, fields, "1 = 1"); } public String select(String tableName, String fields[], String crits) { String selectStatement = "SELECT * " + "FROM " + tableName + " " + "WHERE " + crits; String ret = ""; try { statement = connection.createStatement(); ResultSet result = statement.executeQuery(selectStatement); while (result.next()) { for (String field : fields) { String currentFieldValue = result.getString(field); if (currentFieldValue != null) { ret += result.getString(field) + "\t"; } } ret = ret.substring(0, ret.length() - 1) + "\n"; } } catch (SQLException e) { err.println(createSqlExceptionInfo(e)); } finally { resetStatem

Build a project which can allow user to post and search for job listings, I have written some code and divided into different modules. My motivation is to understand how much over-engineering is needed or not for a system like this which is basically a CRUD system. db.js ``` var pg = require('pg'); var config = { host: 'localhost', user: 'v', password: 'a', database: 'j', }; var pool = new pg.Pool(config); pool.connect(function(err, client, done) { if(err) { return console.error('error fetching client from pool', err); } client.query('SELECT $1::int AS number', ['1'], function(err, result) { done(err); if(err) { return console.error('error running query', err); } console.log(result.rows[0].number); //output: 1 }); }); pool.on('error', function (err, client) { // if an error is encountered by a client while it sits idle in the pool // the pool itself will emit an error event with both the error and // the client which emitted the original error // this is a rare occurrence but can happen if there is a network partition // between your application and the database, the database restarts, etc. // and so you might want to handle it and at least log it out console.error('idle client error', err.message, err.stack) }); module.exports = pool; ``` index.js ``` 'use strict'; var express = require('express'); var Promise = require('promise'); var router = express.Router(); var app = express(); var pool = require('./db.js')(); var User = require('./models'); var PORT = 3000; app.use(function (req, res, next) { pool.connect(function(error, client, done) { // Handle connection errors if (error) { done(error); console.log(error.message); return res.status(500) .json({success: false, data: error}); } req.client = client; req.done = done; next(); }); }); **index.js** 'use strict'; var express = require('express'); var Promise = require('promise'); var

I've been interested in writing something meaningful in C++ for a long time, yet I had a hard time not picking Java or C# for a new project... Now I've found something for which C++ seems to be the right tool: Writing a Database Management System. I'm going to show you the code for the first step: Storing databases. The code is written in Microsoft Visual C++ 17 and I'm trying to have code that is as modern as possible. I also didn't intend to build a serialization library, it just happened. This library exports its methods through a DLL. stdafx.h ``` #pragma once #include "targetver.h" #define WIN32_LEAN_AND_MEAN // Exclude rarely-used stuff from Windows headers // Windows Header Files: #include #include #include #include #include #include #ifdef DBMS_EXPORTS #define DBMS_EXPORTS_API __declspec(dllexport) #else #define DBMS_EXPORTS_API __declspec(dllimport) #endif ``` targetver.h ``` #pragma once // Including SDKDDKVer.h defines the highest available Windows platform. // If you wish to build your application for a previous Windows platform, include WinSDKVer.h and // set the _WIN32_WINNT macro to the platform you wish to support before including SDKDDKVer.h. #include ``` DBMS.h ``` #pragma once #include "stdafx.h" #include "Database.h" namespace DBMS { class DBMS { public: DBMS_EXPORTS_API DBMS(const std::string file); DBMS_EXPORTS_API Database& CreateDatabase(const std::string file, const std::string name); DBMS_EXPORTS_API std::optional> GetDatabaseByFile(const std::string file) const; DBMS_EXPORTS_API std::optional> GetDatabaseByName(const std::string name) const; DBMS_EXPORTS_API void DeleteDatabase(const Database& database); private: const std::string file; std::vector databases; void SaveToDisk() const; }; } ``` Database.h ``` #pragma once #include "stdafx.h" namespace DBMS { class Database { public: DBMS_EX

I have written this simple `DBHelper` class to access a MySQL database. I am rethrowing a more generic exception so that the client side can take some action like showing a friendly message on the screen. Is it ok, or can something else be done? ``` using System; using System.Collections.Generic; using System.Linq; using System.Text; using MySql.Data.MySqlClient; namespace DBLibrary { public class DBHelper { private MySqlConnection _connection; private string _server; private string _database; private string _uid; private string _password; private MySqlCommand _command; public DBHelper(string server, string database, string uid, string password) { _connection = new MySqlConnection(); _server = server; _database = database; _uid = uid; _password = password; _connection.ConnectionString = String.Format("server={0};database={1};uid={2};password={3}", _server, _database, _uid, _password); } public MySqlParameterCollection Parameters { get { return _command.Parameters; } set { foreach (MySqlParameter param in value) _command.Parameters.Add(param); } } public bool OpenConnection() { try { _connection.Open(); _command = new MySqlCommand() { Connection = _connection }; } catch (MySqlException ex) { //When handling errors, you can your application's response based on the error number. //The two most common error numbers when connecting are as follows: //0: Cannot connect to server. //1045: Invalid user name and/or password. switch (ex.Number) { case 0: throw new Excep

``` package com.company; import java.io.*; import java.util.ArrayList; import java.util.List; import java.util.Scanner; import static com.company.Person.id; public class Main { private static Scanner in = new Scanner(System.in); private static File file = new File("Addresses.txt"); static List people = new ArrayList<>(); public static void main(String[] args) throws IOException { readPeopleFromFile(); showMainMenu(); } private static void findPerson() throws IOException { System.out.println("1. Find with name"); System.out.println("2. Find with surname"); String choice; do { choice = in.nextLine(); switch (choice) { case "1": findByName(); break; case "2": findBySurname(); break; default: System.out.print("Choose 1 or 2: "); } } while (!choice.equals("1") && !choice.equals("2")); System.out.println(); showMainMenu(); } private static void findBySurname() { System.out.print("Enter surname: "); String surnameToFind = in.nextLine(); int matches = 0; for(Person person : people) { if(person.getSurname().equals(surnameToFind)) { System.out.println(person); matches++; } } if(matches<=0) { System.out.println("There is no person with this surname"); } } private static void findByName() { System.out.print("Enter name: "); String nameToFind = in.nextLine(); int matches = 0; for(Person person : people) { if(person.getName().equals(nameToFind)) { System.out.println(person); matches++; } } if(matches<=0) { System.out.println("T