7
votes
Accepted
Insert data to PostgreSQL
Do not use string formatting to pass parameters to SQL queries.
This way you are not only getting type conversion and quotes balancing/escaping issues, but also making your code vulnerable to SQL ...
- 17.3k
7
votes
Accepted
Cryptocurrency notifier - let's get rich (or at least let's try)
I know I don't have docstrings and that my API keys shouldn't be
stored in my program (the same for DB credentials), so try to avoid
these aspects when reviewing my code.
Just mentioning even ...
- 8,726
6
votes
Accepted
A schema for awallet system that allows transfers between users
redundant wallets
CREATE TABLE wallets (
id UUID PRIMARY KEY ...,
user_id UUID REFERENCES users(id), ...
currency VARCHAR(3) ...
This isn't ...
- 32.4k
5
votes
Accepted
Correct using of try/catch clause on database execution
Some quick remarks:
Avoid writing ADO.NET code by hand; instead use an ORM like Dapper.
Why not use MAX() -- which I think is supported by PostgreSQL -- instead of ...
- 11.2k
5
votes
Correct using of try/catch clause on database execution
Just one short note...
catch (NullReferenceException e)
You never want to catch this exception explicitly. 99.999% of the time its occurance means there is a ...
- 44.3k
5
votes
Postgres query to extract a JSON sub-object from a JSON field
I'd recommend (if it isn't already) converting to the type from json to jsonb and adding a GIN index. The documentation below lays it out pretty clear. Good luck!
https://www.postgresql.org/docs/9.4/...
- 51
5
votes
A schema for awallet system that allows transfers between users
Time zone
Instead of using naive datetimes, I would strongly recommend time zone-aware types eg TIMESTAMP WITH TIME ZONE. See relevant doc for available options. A ...
- 7,338
4
votes
Accepted
Translating code numbers to item descriptions in PostgreSQL query
You should put all the mapping between itemId values and whatever constant strings in a separate table (2 columns) and do a join with it.
This allows you to update the mapping (by changing the table ...
- 222
4
votes
Accepted
Update SQL database, similar-but-different queries for each month
There are two main problems with this:
Firstly, as you've identified, your approach will involve a lot of repeated code. The only thing that changes for each of your SQL commands is the monthid field ...
- 211
4
votes
Iterate between dates and INSERT values in a performant way
Instead of a loop which inserts one row after the other you should use generate_series to create all timestamps at once.
Untested:
...
- 1,172
4
votes
Accepted
Historical Funding Rate Miner for ftx.com
Code should be correct, maintainable, robust, reasonably efficient, and, most importantly, readable. Code should be useful and have real value.
I found your code hard to read. Your code does not ...
- 3,531
4
votes
Accepted
Postgres DB design
What probably stands out most is the foreign key in sh_staff_members which also functions as a PK. While inheritance seems like a better tool to represent the is-a relationship between sh_staff_member ...
- 63.1k
3
votes
Update SQL database, similar-but-different queries for each month
What is con? con is a reference to my database connection which is public.
I have to disappoint you beceause it is not public. Class members are ...
- 44.3k
3
votes
Accepted
3
votes
Migrate files from MySQL BLOBs to PostgreSQL largeobjects
You don't have any performance metrics, so there is no telling whether this will have a significant effect, but one of the things you could do is combine your queries.
Take this one:
...
- 22.3k
3
votes
Accepted
Asynchronous database query system using futures-rs in Rust
Here are a few shallow comments on the code.
This code
...
- 9,613
3
votes
Accepted
Splitting a list of numbers into multiple lists
Calculating ranges
I need split the list I get from the select into multiple list so I can use Parallel.For() to generate the next query in different threads.
Nope. You don't need it. Use ...
- 44.3k
3
votes
Splitting a list of numbers into multiple lists
Pretty minor but you don't need the if (row_id == 0)
...
- 6,026
3
votes
Accepted
Importing database of 4 million rows into Pandas DataFrame
Your code
def import_db_table(chunk_size, offset):
It doesn't look like you need to pass offset to this function. All it does ...
- 243
3
votes
Count buffered streets that intersect buffered points using PostGIS
adding this CTE decreased the time from 1 hr 11 minutes to 2 minutes 62 seconds:
cte_dumppoly as (select (st_dump(geom)).geom as geom, dccode from public."MTC15")
...
- 141
3
votes
Accepted
3
votes
Consume etherscan.io API, store in DB, serve from web app
I must say it a very interesting assignment to do:
what I see missing from your assignment is not much bigger things in development aspects but it all have a huge impact on Code maintainability and ...
- 131
3
votes
Accepted
Python script to find employees who are in one PostgreSQL table but not in another
LEFT JOIN from test_test to test_table selecting only rows where test_table.test_emp_id is <...
- 268
3
votes
3
votes
Accepted
OOP Python Blackjack game with player accounts in PostgreSQL
Here are a few things that caught my eye while quickly looking through your code:
Imports
As per the official Style Guide for Python Code (aka PEP8), imports ...
- 7,270
3
votes
Accepted
Enforcing a constraint that a user may vote on each post at most once
which one is more efficient
Almost surely the unique index will be. Querying back and forth between the server and database is very expensive, and the query that you would have to write to check the ...
- 63.1k
3
votes
Accepted
Discord.py Pokemon Bot performance problems
Almost certainly the largest slow-down is that you are getting each individual value on its own. Instead get all information at once:
...
- 41k
3
votes
Accepted
Postgres Shopping Database using Python
Before anything, read on SQL parameterization and avoid concatenating or interpolating variables directly in SQL statements. Otherwise you open your application to SQL injection and inefficiencies. ...
- 960
3
votes
Fetching all nodes of a directed graph by an id of any node
I am not well qualified to answer this, as I know nothing of PostgreSQL or the RECURSIVE extension you are using, so unfortunately I cannot very well comment on your solution.
However perhaps it can ...
- 1,246
3
votes
Selecting changes from a temporal table
Externalize your DML
Given the length of the query written in GetArticleChangesAsync, I would expect that either
it be moved to a stored procedure (common but IMO ...
- 63.1k
Only top scored, non community-wiki answers of a minimum length are eligible