databases

[wise goblet]

Column wide, yeah

[harsh pulsar]

i wonder how redis compares with newer options like rocksdb for "persist key-value data as fast as possible"

[wise goblet]

Not knowing enough to answer at this point, but it is one of three DBs one dB expert recommended for huuuge amount of dumped data

[harsh pulsar]

interesting

i wonder what makes it good. maybe it's optimized for that

i think mongo can hit really high write speeds, but the meme is always that writing to /dev/null is fast too and just as safe. never needed to push mongo that far so i never benchmarked

[wise goblet]

I ll get back to you after learning them xD

[harsh pulsar]

is there something like the benchmark game for databases?

i also wonder about couchdb, i know it's meant for easily distributing/scaling but idk about write throughput

[wise goblet]

All I know now
Than Cassandra is resilient to take down.
If master server is down, any slave automatically becomes new master

And it can scale in size by inclusion of new servers easily
Or it can work in replica mode, automatically replicating same data across all servers, making it HA available

[harsh pulsar]

maybe the best solution here is "invent a benchmark that is realistic to your application, and try them all"

https://db-engines.com/en/ this site looks interesting

[harsh pulsar]

i just looked this up, i didn't realize it was a distinct type

so each "column" is actually a variant/object thing

i see

ok that does make sense as supporting fast writes

how does that differ functionally from a "document" database? just in terms of the query semantics?

https://db-engines.com/en/ranking this is awesome, lots of stuff to read and click through

i wish they had a column to indicate which ones were open source

[brazen charm]

I was including that as part of Scylla. There's basically no reason to use Cassandra now over Scylla unless you have a pre existing setup with the dB.

It's faster, quicker release cycle, compatible with dynamoDB drivers, Cassandra drivers or via REST, more predictable performance and less setup hassle.

Wide columns and basically most newer DBs are build on LSMs (log structure merge trees) which make all writes sequential rather than random like a traditional B+Tree in something like postgres, the downside is they produce read and disk amplification as the trees are immutable, read performance is generally better than SQL DBs on average though with a clever setup of caches and bloom filters.

[brazen charm]

Cassandra is leaderless just as an FYI. It's based on the dynamoDB white paper which I'd really recommend reading if you're into that kind of thing.

The consensus method for cluster membership is an anti-entropy algorithm called scuttlebut and then replication is built on top of CRDTs which basically underpin any eventually consistent database.

CRDTs ar Conflict Free Replicated Datatypes and essentially are just structures which are idempotent i.e. an operation can be applied multiple times and not affect the outcome of the state, and that state is always deterministic, sets tend to be the biggest example of them.

https://youtu.be/B5NULPSiOGw

Is a really good talk about distributed consistency.

[wise goblet]

but can we get with your Scylla as easily replicated databases as Cassandra? and horizontally scalble in total size too

[brazen charm]

Yes, they are the same thing

Scylla is Cassandra but re implemented in C++ and fixes a lot of issues within Cassandra itself.

[wise goblet]

✍️ Yet another thing to learn then

[brazen charm]

The general difference between your SQL DBs and databases like Cassandra etc... Is the trade off between consistency and availability.

The general rule is you can have only 2 of: strong consistency, performamce, availability

Databases like Cassandra choose to drop strong consistency in order to get performance and high availability, while things like postgres take strong consistency and performance and drop availablity.

[wise goblet]

can they be having some good data integrity? only certain fields allowed in table / certain data types to fields? data migration of tables?

[brazen charm]

Mmm it doesn't really limit data types or anything like that, it's more of an internal thing within how the database works out what operation should be applied in what order if two nodes both try to modify data.

The biggest thing that you'll get is the fact that because it's eventually consistent, it does not guarantee that the changes will instantly be reflected across all nodes.

So if you had something like postgres, once that operation completes you know that everything is updated and sees the updated result. But in eventually consistent databases you don't get that, because the only guarantee is the changes will eventually be reflected across all nodes but not instantly.

There are some exceptions to this though, so there's an algorithm called Raft which implements replication via consensus which you can think of like a write ahead log (WAL) but for distributed systems.

This gives strong consistency and availability but can be weaker on performance. It's also a leader based system so all writes for the data controlled by Raft which you want to replicate must go to the leader node (the leader can change though if a node goes down, this is done by an election which basically just works out what node is the most up to date)

Recently Scylla have actually started to use Raft with schema updates because they're fairly infrequent but often want stronger consistency than what you would get otherwise.

😅 I have been successfully nerd sniped but I must sleep now lol

[wise goblet]

Sounds like a great database for view-output having eventually consistency, probably

[brazen charm]

Just remembered but technically yes this does limit some data types like counters, counting things sequentially are a big problem because they are often not idempotent.

My final thing before I go to sleep, but you can check out https://www.yugabyte.com/ for a distributed version of postgres, which is built on top of raft and they have some some major hoop jumping

[wise goblet]

ScyllaDB's mascot makes me suspected it being written in Golang xD despite it being in C++

[brazen charm]

Scylla is a beast of a database

[wise goblet]

Omg. distributed easily replicated postgres. 🤤

[brazen charm]

If you're in a situation where you want postgres replicas, ye I highly recommend yugabyte.

It's also a bit more forgiving towards very large columns or rows as well

[wise goblet]

Too many databases exist in the world
https://media.tenor.com/mC_lgI6TUlYAAAAd/cat-hide.gif

Feels like here alone i'll get infinity learning curve

i'll be consistent. Finsihing Postgresql first to medium level, then ElasticSearch, then MongoDB, then RabbitMQ, then Kafka, and only after that ScyllaDB, lets postpone other perversions for later. xD

[brazen charm]

Realistically the ones that are probably good to know are:

• Postgres
• Cassandra/Scylla

Everything else is largely similar design and behaviour.

I guess if you look at a lot of time series stuff then ClickHouse is a good one because it's again a different design for analytics and things.

[wise goblet]

ElasticSearch is very often encountered i think. As Search Engine db

[keen minnow]

imho, I would stick with Apache Cassandra rather than scilladb

[brazen charm]

Personally I have a pet peeve against Mongo and elastic just because in Mongo's case, 99.99% of things have a fixed schema, and I think trying to throw that idea out the window is a bad idea. And then elastic just because it's a search engine, not a database in the way that most people think of databases. Search engines are a different beast entirely.

[wise goblet]

U can have enforced scheme of tables in NoSQL? Count me in xD

[brazen charm]

In other words just learn CQL, the difference between the DBs themselves are fairly irrelevant.

[keen minnow]

yeah. I wouldn't be willing to trade the maintenance aspects and velocity of the project for just a c++ clone.
They would need more than that to make the switch worth it to me.

[wise goblet]

for popularity alone, i will prefer choosing stable mature solutions 🙂

[keen minnow]

and it's OSS

[brazen charm]

I mean most of the time if you have an existing cluster of Cassandra people won't switch, most of the time if people do switch is because it reduces the cost of operations by orders of magnitudes because of the increased capacity of each node.

The tooling is the same between the two so you don't loose things like developer experience.

But if you're not having issues there's not a huge reason to bother changing and migrating, because realistically who enjoys migrating a huge amount of data and the under taking that involves

But if you start a new project and are looking at Cassandra I would just go with Scylla, because of the reasons I mentioned earlier, unless your logic depends on some very very specific Cassandra behaviour which isn't the same.

[dark kayak]

Hi , i want to use Django and it has backed ability too like database .So i don't know which database to use and which database fits with django

[dark kayak]

precisly which large database engine will suite python the best

[wise goblet]

If u a in doubts, then use Postgresql

Best default choice

[dark kayak]

yes sir

postgresql difficulty in comparison

[wise goblet]

In comparison to what?

[dark kayak]

to sql,oracle,mongodb

[wise goblet]

Postgresql is SQL. Plus features on top

Oracle is proprietary pain in the ass

MongoDB is kind of incomparable due to being no SQL dB. Some stuff is easier, some stuff is worse in it

[dark kayak]

ok

resolved

[frigid swan]

does anyone wanna test my chat bot

[harsh pulsar]

interesting. i don't know much about how these various algorithms work, but it's good to know what the current state of the art is

i occasionally end up in a position where my opinion becomes somewhat relevant on technical decisions about databases etc. and i like to at least kind of know what i'm looking at

[velvet knot]

Sure

[keen minnow]

Some good resources:

• https://dataintensive.net/
• https://www.databass.dev/

[harsh pulsar]

thanks, these both look great!

[brave bridge]

I'm pretty sure P stands for Partition Tolerance

[crystal inlet]

Is there a preferred way to store IP addresses in a database for small scale projects? (sub 100k rows) gotten some mixed feedback, about converting to unsigned int vs just keeping it as a string.

[junior robin]

I am wondering if someone has stumbled upon a similar scenario, I am using asyncpg and I am trying to fetch rows based on a SELECT, however I need to have the column names in the SELECT read from a variable. Representing as an f-string for illustration would be f"SELECT {variables} from mytable", the problem is this is prone to SQL Injection, in psycopg2, one can construct such a query safely with the help of psycopg2.sql, I am wondering what people use for similar situations when using asyncpg?

[graceful widget]

import asyncpg
import discord
from discord.ext import commands

bot = commands.Bot(command_prefix="...", intents=discord.Intents.default())

@bot.event
async def setup_hook() -> None:
    # this override's Bot.setup_hook and is triggered before the bot starts.

    # creating a database pool
    bot.db_pool: asyncpg.Pool = await asyncpg.create_pool("PGSQL SERVER URL HERE.")

    # example to making a query to the database anywhere in your code ->
    async with bot.db_pool.acquire() as connection:
        await connection.execute(
            """SQL QUERY HERE"""
        )
        # creating a connection cursor if needed
        async with connection.cursor() as cursor:
            await cursor.fetch("FETCH QUERY...")
```i have this
but idk what to write when i want to get a data inside the database with my discord bot

[graceful widget]

before this i use replit so its like

item = db[f'{userid}item']```

[pale granite]

Hi guys, I have bunch of csv & excel files with storage order of 10G and i may do some ETL on them later, what are some best practice cloud storage for my purpose?

[naive sandal]

might want to look into some sql tutorials like https://sqlbolt.com

[graceful widget]

i learnt this

i just dont know how to do the async connection thing

[graceful widget]

i learnt all the query

[naive sandal]

what async connection thing?

[graceful widget]

async with connection.cursor() as cursor:
            await cursor.fetch("FETCH QUERY...")
```all these

i know the query but idk how to set things up

[graceful widget]

someone sent me this but idk how to use it ;-;

[naive sandal]

bot.db_pool: asyncpg.Pool = await asyncpg.create_pool("PGSQL SERVER URL HERE.")
creates a connection pool

async with bot.db_pool.acquire() as connection:
acquire a connection from the pool

await connection.execute("QUERY HERE")
execute sql query

[graceful widget]

@bot.command()
async def inv(ctx):
  item = await connection.execute(f"SELECT inv FROM table WHERE id = {ctx.author.id}")
  await ctx.send(item)
```i dont think things work that way 💀

[torn sphinx]

you gotta create the tables first

then in your SELECT queries use a proper condition to what rows you want the cursor to pick

[graceful widget]

i have a table already

[torn sphinx]

well then item should be the RowFactory, a tuple or a dict depending on the driver, idk what asyncpg returns

[graceful widget]

@bot.command()
async def inv(ctx):
  item = await connection.execute(f"SELECT inv FROM table WHERE id = {ctx.author.id}")
  await ctx.send(item)
```alr even tho i make it this way connection is still not defined

[naive sandal]

you're supposed to acquire a connection from the pool...

[torn sphinx]

!sql-fstrings

[delicate field]

SQL & f-strings
Don't use f-strings (f"") or other forms of "string interpolation" (%, +, .format) to inject data into a SQL query. It is an endless source of bugs and syntax errors. Additionally, in user-facing applications, it presents a major security risk via SQL injection.

Your database library should support "query parameters". A query parameter is a placeholder that you put in the SQL query. When the query is executed, you provide data to the database library, and the library inserts the data into the query for you, safely.

For example, the sqlite3 package supports using ? as a placeholder:

query = "SELECT * FROM stocks WHERE symbol = ?;"
params = ("RHAT",)
db.execute(query, params)

Note: Different database libraries support different placeholder styles, e.g. %s and $1. Consult your library's documentation for details.

See Also
• Extended Example with SQLite (search for "Instead, use the DB-API's parameter substitution")
• PEP-249 - A specification of how database libraries in Python should work

[graceful widget]

"SELECT inv FROM table WHERE id = ?"
params = ('ctx.author.id',)
```so its this?

[graceful widget]

.

[naive sandal]

postgres placeholders are different

[graceful widget]

im using postgres

[naive sandal]

https://magicstack.github.io/asyncpg/current/usage.html

see the example there

[graceful widget]

thanks ❤️

[ebon skiff]

I highly recommend just skimming trough the docs there is a lot of useful info.

[graceful widget]

okok

wait have u saw me before

ur name look familiar

[ebon skiff]

I've been active in #discord-bots and several help channels over the span of 2 years.

[graceful widget]

ye i think i met u in there

[torn sphinx]

panda is ancient

[ebon skiff]

[graceful widget]

first met - january 2022 lol

[ebon skiff]

!u

[delicate field]

You are not allowed to use that command here. Please use the #bot-commands channel instead.

[brazen charm]

In CAP theorem yes, it's part of the distributed consensus problem.

In practice in databases you get strong consistency, performance, availability as a result of implementing the consensus algorithms to achieve distributed replication as an additional set of rules that just appear as a additional task to the above.

Performance comes into play because picking strong consistency and availability results in either needing to perform more round trips between nodes to decide on a operation's result (Paxos or CASPaxos are examples of consensus algorithms that rely on this principle), or it limits the throughput of operations because there must be a single leader in order to maintain consensus and consistency (Multi-paxos, Raft, ZAB).

As a result a lot of distributed databases like Cassandra do the same thing as they do for CAP which is they drop strong consistency, mostly, in practice they still use Paxos and Raft for things which do need strong consistency, but as a result they impose less performance guarantees (Think LWT (light weight transactions)).

[junior robin]

In practice, network partitions are a huge deal and a common occurrence, the results proven (that there has to be a tradeoff) apply to (Consistency, Availability, and Partition Tolerance).

[brazen charm]

yes

[junior robin]

So, if someone else encounters this, what I did for now is to just issue a call to the DB executing SELECT quote_ident ($1) for the identifiers I want sanitized, similar to how psycopg2.sql does sanitization for psycopg2.sql.Identifier.

[ionic pecan]

quote_ident is the way to go

[wheat coyote]

which sql i can use with desktop python application for local usage
i want something simple to get a project done in 2 weeks

[atomic cliff]

!sql

[delicate field]

SQL & f-strings
Don't use f-strings (f"") or other forms of "string interpolation" (%, +, .format) to inject data into a SQL query. It is an endless source of bugs and syntax errors. Additionally, in user-facing applications, it presents a major security risk via SQL injection.

Your database library should support "query parameters". A query parameter is a placeholder that you put in the SQL query. When the query is executed, you provide data to the database library, and the library inserts the data into the query for you, safely.

For example, the sqlite3 package supports using ? as a placeholder:

query = "SELECT * FROM stocks WHERE symbol = ?;"
params = ("RHAT",)
db.execute(query, params)

Note: Different database libraries support different placeholder styles, e.g. %s and $1. Consult your library's documentation for details.

See Also
• Extended Example with SQLite (search for "Instead, use the DB-API's parameter substitution")
• PEP-249 - A specification of how database libraries in Python should work

[tender sapphire]

i am trying to store/sort alot of data at once, i tried using json first then moved to mongoDB (using the free tier on their site) but both are too slow for the amount of data i got. any tricks, tips, or databases i can use to speed it up

[storm mauve]

which kind of data?

you can try just setting up an Index or two on the MongoDB

[tender sapphire]

im making my own have i been pwned database for a network sec class

so its just user, password/s

[wise goblet]

Is it possible having strong consistent and highly available?xD are the any databases that have it?

https://www.quora.com/What-are-the-best-strongly-consistent-distributed-databases
Hbase has it

[brazen charm]

If I remember correctly Hbase is CP of CAP. So consistency and partition tolerance.

Kind of hard to really say with HBase though because it orientated around HDFS and Hadoop which add a bunch of other moving parts to the puzzle

[brazen charm]

You could in theory, but part of being a distributed system means you must tolerate network partions, so the P part of CAP isn't really very optional for consensus algorithms

This is fairly theoretical though as database tend to combine systems to give you the potential for either availability or strong consistency.

Yugabyte have some decent blogs about it since strong consistency (or emulating the behaviour) is a huge part of the PostgreSQL compatibility

[storm anchor]

can someone help me to display an image storage as blob in mysql on flask, pls

[hot orchid]

https://tenor.com/view/christian-bale-american-psycho-walk-jam-gif-16732505

[unkempt prism]

Not knowing the mysql python library you are using I'd start with https://mysqlcode.com/python-mysql-blob/ and https://flask.palletsprojects.com/en/2.2.x/api/?highlight=send_file#flask.send_file

[tiny eagle]

If I were to store messages sent in an rdbms, would it be better to store attachments in a separate table that references the message table, or create a type and have it as an array in the message table? Feel like the former is best for an rdbms however working with this won't be ideal

[lusty creek]

you can ask discord py questions here

[fading patrol]

however working with this won't be ideal

Why not?

[heady gale]

Seems cool

[torn sphinx]

if cockroachdb serverless is hosted by them

why do i need postgresql shit

[keen minnow]

You don't.
And postgres is far from shit

As long as you don't need any extension which is incompatible

[torn sphinx]

didnt say it was

[keen minnow]

sounds like it

[torn sphinx]

no

its like

generalizing it

[keen minnow]

Whether you intended or not, it did sound like that to me

[brazen charm]

Personally I don't see much point in using cockroach over postgres' itself or yugabyte.

They have considerably more tooling available, more battle tested and more utility out of the box.

If you want a DBAAS then sure, just use a cloud offering of some DB most do so because that tends to be their business model, but yeah

[keen minnow]

I am still waiting a bit more on yugabyte.

So far, nothing that could resist a sharded/replicated set of mysql/postgres

[brazen charm]

I think most of the time I still use postgres yes.

But I'd take yugabyte over cockroach as a general selection.

Postgres in general though is pretty hard to beat other than HA which is fairly awkward

I think what would be very good if postgres ever added in the future, is raft for managing their replication and failover handling. It would simplify the process a lot IMO.

[torn sphinx]

wtf is "yugabyte"

[brazen charm]

Ah, looks like EDB have made an equivalent of this for commercial use.

[brazen charm]

It's cockroach's twin sister that decided do things a bit differently but the two still bicker over who is the better postgres alternative.

[torn sphinx]

if its free and i dont need to install anything except for the package to use it im in

which fits the criteria?

[brazen charm]

What do you actually want and what do you actually need.

[keen minnow]

yugabyte/cockroach and more generally distributed/sharded databases are used in case of extreme load or constraints. You are unlikely to need them

[torn sphinx]

i want something atleast decently fast and thatI wont have to store myself

[brazen charm]

Use some DB as a service then

Most cloud providers offer them

They're not cheap though, typically they're aimed at companies which have to pay for developers to set things up etc... So they're more than happy to pay a lot of money for hosting because it's still cheap than what their developers cost.

But if you're doing it for some hobby project then they're generally not the best idea in the world with bang for your buck

[keen minnow]

if you have a vps, spinning up a container is pretty easy

[brazen charm]

Yeah, for a hobby project it's easier to buy a £5 vps and spin up a docker container

Hell most hosting providers, provide images with docker already installed.

Incidentally the £5 vps will probably give you more specs than a £50/month hosted server, probably more tbf

[clear stirrup]

setting up a postgres database on railway is really cheap

for my hobby project it was like a dollar for a month

fly.io is also great

[silver crow]

Hey,
I have created a Django application and connected Mysql database
but when I use Modelclass.objects.all() in views.py
It gives me error of unknown column A.id, and I am unable to fetch the data from database

MySql database have 1 table named xyz
2columns A_ID and A_name
A_ID : Primary key, auto increment, not null, int
A_name: Varchar(50)

Can anyone tell me whats the issue and how can i solve this error

Note: I have already use makemigrations and migrate.

[torn sphinx]

im broke

i cant pay 5 dollars an hour

[harsh pulsar]

if the class is A, wouldnt the table be a and the column be id by default in django? did you customize the table and field names?

@torn sphinx a VPS costs more like $5 /month

[silver crow]

yes because database created before django application create

If nothing works then i have to change the field name in database, that will be the last option

[harsh pulsar]

normally django makemigrations is supposed to create the tables for you. i'm not sure this is even a supported case in django. at minimum, you'll probably need to set custom names for the table and fields. you'll have to consult the docs for how to work with pre-existing tables.

[torn sphinx]

me no has credit card

[silver crow]

as django by default search for .id in tables

[harsh pulsar]

if you're young and don't have a way to pay, maybe you have a family member that would be willing to fund your project. at any rate, while you are developing your application, you can run postgres locally on your own computer. you don't need to pay for hosting just to start learning.

[torn sphinx]

i know my way aroound most of what i wanna do

and i have a form of somewhat good free hosting

the missing piece is a db

[harsh pulsar]

what kind of hosting? a database is just a program, all it needs is a persistent filesystem really

[torn sphinx]

hosting as in

running the program 24/7

but not 365

i have yet to run into a service that has uptime 365

a year

[harsh pulsar]

yes but how? on something specific like repl.it? or a general-purpose host like digitalocean?

[torn sphinx]

general purpose

but

u can only run code

[sour belfry]

Help me with this issue they say there is no such table as tiket ini this code

import sqlite3 as sq
import streamlit as st
from streamlit_lottie import st_lottie
import requests as req
import pandas as pd

confirm = "n"
while confirm == "n" or confirm == "N":
    
    conn = sq.connect("film.db")
    cur = conn.cursor()
    book = cur.execute("SELECT * FROM tiket")
    data = cur.execute("INSERT INTO tiket (Seat) ")

    nama = str(input("nama anda "))
    jekel = str(input("jenis kelamin (l/p): "))
    tiket = int(input("pilih kursi 1 sampai 240 dari website"))

    if jekel == "l" or "L":
        jekel = "Laki_Laki"
    elif jekel == "p" or "P":
        jekel = "Perempuan"

    for i in len(1, 4):
        print("   ")

    print(nama, jekel, tiket)
    confirm = str(input("apakah ini benar (Y/N)? "))
    
    if confirm == "y" or  confirm == "Y":
        break

while tiket == book:
    print("tiket anda sudah di ambil ")
    tiket = int(input("pilih kursi 1 sampai 240 dari website"))
    if tiket != book:
        entity = (nama, jekel, tiket)
        put = data.execute(entity)
        print(put)
        break



def load_animation(url):
    r =  req.get(url)
    if r.status_code != 200:
        return None
    return r.json()

# assets
gambar1 = load_animation("https://assets10.lottiefiles.com/packages/lf20_i7dxj8qw.json")



st.set_page_config(page_title="Testing Ground", layout="wide")

with st.container():
    st.header("Hello world")
    st.subheader("i was here")
    st.write("contact infomation goes here:")
    st.write("email")

with st.container():
    st.write('---')
    left_column, middle_column, right_column = st.columns(3)
    with left_column:
        st.button
    with middle_column:
        st_lottie(gambar1)
    with right_column:
        st.write('i was testing')

with st.sidebar:
    add_radio = st.radio(
        "Choose a shipping method",
        ("Standard (5-15 days)", "Express (2-5 days)")
    )

What can you do to help

I used phpadmin

And sqlite 3

And it keep saying no such table as tiket

Im very confused when i found out this

So

Oh

Is there a link to download that

Alr

[ancient obsidian]

talus how are u

[sour belfry]

What

Im still learning databases

[ancient obsidian]

u know sql

[sour belfry]

Not really

:/

[ancient obsidian]

basics?

[sour belfry]

Well kinda

Like i said im still learning

[ancient obsidian]

hmm

[sour belfry]

I have

Like alot of error

But i need to know which one is which plus

Wdym by this

[white elm]

how do you go through your collections in a pymongo code?

i get the error that says that i cannot apply _id to the cursor instances

when im trying to browse the collections that is under the object id

of mongodb

i tried to use this "data["_id"]["name"]

but it wont work go to the data under the object id

[white elm]

Oh okay ill check that out

Thanks

Yeah

Yeah, im trying to get access to a specific part of the data that is {players:0},which is under the object id in and a another id that put there

And I could not get through the firat part which was the object id

So i could not access any data

First*

Yeah

I tried stuff like data["_id"][ID]["players"] but i just got errors from the first part

Im gonna try that one

[somber oracle]

🌿 Hi, guys!

How do I insert record in sqlite only if the record doesn't exist?

**UNIQUE **doesn't work unfortunately...

Code: https://codeshare.io/dwVYnR

*The problem is that several people use the application and so that they do not add the same client to the database, sqlite needs to ignore the input if, for example, a user entered an existing one in the RegNom field in the database. *

THANKS IN ADVANCE!

¯_(ツ)_/¯

OK

unfortunately it doesn't help

no

yes

The general syntax of defining the UNIQUE constraint on a column is:

CREATE TABLE TABLE_NAME (column1 datatype UNIQUE, column2 datatype);

No

everything works correctly

[paper flower]

Unique should work, but sqlite ignores all constraints by default 😐

[queen rose]

is NoSQL just sql without writing queries ? so like using OOP to interact with the database?

So SqlAlchemy and Django both qualify as 'NoSQL' since you are interacting with the DB without writing any queries, right?

thats what I was wondering as well, if its about how the data is stored, or how you interact with the DB

im it justs writes the queries automatically... so yeah in the end its just regular SQL queries

[graceful widget]

how do i save list in sql

like
[1,2,1,3,12,45]

[fading patrol]

No, to me these are ORMs that rely on a relational/SQL database... The term NoSQL refers to non-relational databases

[fading patrol]

That's rarely the right approach, you probably want a table or a column for that data. But if you really do want to do it that way, it depends on the DB. If SQLite a string (you might see why this is a bad idea), with Postgres as BJSON.

[graceful widget]

should i create a table, add a foreign key in there and put the whole list key by key there?

[fading patrol]

Quite likely yes, if that makes sense based on your use case

[graceful widget]

im making a monopoly bot

i want to save the list of the player's id

is this a 'good case'?

[fading patrol]

Then you probably do want a players table, yes

[graceful widget]

aight thanks

[wary vine]

anyone who worked on tiny db?

[meager stirrup]

I have a table in postgresql into which I want to add additional columns is there any way to do this with pandas dataframe without losing data or dropping table

[fading patrol]

I'm not sure I follow the question but I don't see why adding a column to a table in Postgres should cause you to lose data or require you to drop a table

you might need to share code to clarify what the problem is

[meager stirrup]

basically

I first used alter table command to add columns

but i am chekcing the database no columns have been added

[brazen charm]

oh boy

[brazen charm]

2 things:

1: do not format sql queries like this
2: "" are used for identifiers in postgres, for strings it's ''. but this shouldn't matter because you should be using placeholders instead

!sql

[delicate field]

SQL & f-strings
Don't use f-strings (f"") or other forms of "string interpolation" (%, +, .format) to inject data into a SQL query. It is an endless source of bugs and syntax errors. Additionally, in user-facing applications, it presents a major security risk via SQL injection.

Your database library should support "query parameters". A query parameter is a placeholder that you put in the SQL query. When the query is executed, you provide data to the database library, and the library inserts the data into the query for you, safely.

For example, the sqlite3 package supports using ? as a placeholder:

query = "SELECT * FROM stocks WHERE symbol = ?;"
params = ("RHAT",)
db.execute(query, params)

Note: Different database libraries support different placeholder styles, e.g. %s and $1. Consult your library's documentation for details.

See Also
• Extended Example with SQLite (search for "Instead, use the DB-API's parameter substitution")
• PEP-249 - A specification of how database libraries in Python should work

[meager stirrup]

okay

and what about in psycopg2

[brazen charm]

it's the same

it doesn't change with database driver

[meager stirrup]

okay

thanks I'll try and tell

[stark gust]

Hi. I'm using MongoDB, trying to get the lowest birth year value that exists in a collection of personal data, but this doesn't work:

min_year = db.people.aggregate( [ { "$group": { "_id": "$dates.birth.year", "value": { "$min": "$dates.birth.year" } } } ] )

it says pymongo.errors.OperationFailure: Unrecognized pipeline stage name: '$min'
Can someone help me resolve this? Thanks.

there are many dates, so they're stored in a dates object
they are separated by the kind of date (birth), and because they're stored as numbers, not as timestamps

why should it be {}?

I don't think so. I'm trying to follow the first code example on this documentation page: https://www.mongodb.com/docs/manual/reference/operator/aggregation/min/

db.sales.aggregate(
   [
     {
       $group:
         {
           _id: "$item",
           minQuantity: { $min: "$quantity" }
         }
     }
   ]
)

I think we should let someone else help

someone who knows Mongo

you don't know what you're referring to

okay, I see what you mean. but it doesn't work with {} either, so that's not the solution

5.0

[meager stirrup]

Thanks for your help man btw the solution was just converting double quotes to single quotes

but thanks for telling me about placeholders

[quick linden]

Got an app running which is connected to my postgresql database. Sometimes it doesn't do much for a while and then I get "could not receive data from client: Connection reset by peer" in my logs and an exception on python. Anything I can do about it/what are the best practices here. Am I not supposed to keep connections about?

[keen minnow]

do you use a connection pool?

[quick linden]

Nope didn't really think about looking into that. I started using sqlalchemy and then just used that

[keen minnow]

yeah, if you have a long lived connection inactive for a while, something in between might think it's dead.

Connection pools have some advantage as they can maintain multiple connection for higher throughput, amortize the cost of establishing/maintaining connection and validate them periodically, on borrow or/and on release

[quick linden]

So I should look into pools instead and start using that?

[keen minnow]

That is the implication, yes

[quick linden]

Is it safe to use with threads?

[keen minnow]

I haven't used sqlalchemy in years. So I can't speak to that.
But I would expect it to be safe.
See also https://docs.sqlalchemy.org/en/14/core/pooling.html

[quick linden]

Alright awesome. Thank you!

[potent halo]

is it possible to input an sql insert where you define the columns and values at the same time? ie;
INSERT INTO testing(items = 'test')
rather than INSERT INTO testing(items) VALUES('test')

the reason for this is if i am updating a lot (but not all) of my columns i dont want to have 20-30 column names and values that i need to ensure match up perfectly

if i mis-order one or add a column to the func and misplace its matching value, im kinda screwed till i find the bug later

[harsh pulsar]

unfortunately that's what we are stuck with in sql. one possibility is to generate an insert query from python code:

record = {"x": ..., "y": ...}
fields = ", ".join(record.keys())
placeholders = ", ".join(["?"] * len(record))
query = f"INSERT INTO testing ({fields}) VALUES ({placeholders})"
conn.execute(query, tuple(record.values()))

note that i'm still using query parameters to insert data into the query

and when you do this, you need to be very careful not to pass user-provided data into the field names

[potent halo]

dang it took me a while to translate that one lol

[potent halo]

ur basically dissembling a dict and resembling it in a format compatible with a query

interesting way to do it

[harsh pulsar]

kind of, i'm disassembling the dict and building a query & a list of parameters from it

you can do this without a dict of course, e.g. if you have one list of field names and another list of values

[potent halo]

then i have the same issue with keeping em lined up

a dict keeps em together tidier

[harsh pulsar]

@potent halo it depends on your code of course

if you have to write them all by hand, then yes i think a dict is safer

[potent halo]

alot of them are 1s and 0s for true false checks

so yeah lining them up would be a pita lol

[potent halo]

yup

at least i only have to do it once...

[harsh pulsar]

this is why we have functions!

[potent halo]

fr

[potent halo]

the one thing im sure on, is how does the ["?"] work? ie in the placeholder def, how are you getting the tuple in record.values tuple in there

[harsh pulsar]

!e ```python
q = ["?"]
print( q * 10 )

[delicate field]

@harsh pulsar :white_check_mark: Your 3.11 eval job has completed with return code 0.

['?', '?', '?', '?', '?', '?', '?', '?', '?', '?']

[potent halo]

it seems that you are parsing the tuple as a para, tho im not sure if its just the syntax for queries

[harsh pulsar]

this is how all database libraries in python (and most other languages) work. you don't want to pass data in as a plain string, you end up with lots of problems that way. so they provide a mechanism for binding parameters to placeholders in the query

[potent halo]

i see

[harsh pulsar]

@potent halo https://docs.python.org/3/library/sqlite3.html#how-to-use-placeholders-to-bind-values-in-sql-queries

[potent halo]

interesting

[potent halo]

also whats the diff between cursor.execute() and sqlite3.connect().execute()?

[harsh pulsar]

the former is required by the dbapi standard in pep 249, the latter is a convenience

the connection execute method creates a cursor and returns it

[potent halo]

ah

[harsh pulsar]

that's documented somewhere in the sqlite3 docs

!pep 249

[delicate field]

**PEP 249 - Python Database API Specification v2.0**

Link

Status

Final

Created

12-Apr-1999

Type

Informational

[harsh pulsar]

and here's the database api specification, worth at least skimming

[potent halo]

so using the cursor is a universal standard rather than just sql specific?

[harsh pulsar]

this specification is for python libraries that interact with sql databases

[torn sphinx]

Hello

i need help with something small, anyone with pandas experience could help?

[harsh pulsar]

don't "ask to ask", just state your question. note however that pandas questions might get better answers in #data-science-and-ml , since pandas data frames aren't really "databases" in the sense of this channel

[torn sphinx]

i gont some help, thank you though!

[graceful widget]

why are there no foreign key

[grim vault]

Why is there no mention which software and database that is?

[graceful widget]

railway.app pgsql

mb

[torn sphinx]

is there an easy way to create an autoincrement column for a query

like right now i have to create a new table entirely with an autoincrement column

CREATE TABLE fam_atk(
  row_id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  attack INTEGER
);

INSERT INTO fam_atk(name, attack)
SELECT name, attack
FROM familiar ORDER BY attack DESC;```

[fading patrol]

ALTER TABLE

[rugged stirrup]

hey i have a json file and im trying to print a part of it within some index

with open('workout.json', "rb") as f:
    exercises = json.load(f)

for exercise in exercises:
    print(f"{exercise.index} {exercise}")

I want to have them in order so my output should be like:
1 Abs
But im not sure how to do that

[graceful widget]

can anyone help with my normalisation
this is what i have done so far
and its for a monopoly bot

[rugged stirrup]

right

thank you <333

[rugged stirrup]

thank you!

do you know if there's a way to go with index? for example if i want to print the values only of the first exercise

something like ```
muscle_group = int(input("Choose a muscle group: "))
print(exercises[muscle_group])

no also sorry bad variable name out of case

i have this

and for example if someone gives number 1

in input

i want to display only the abs part

if he gives 2 it will display test

but it should work with integers so if i change a name the code would still work

oh ok i see

alright thanks anyway!

will try that to see how it works ty <33

[thorny tusk]

What data basr do I use to make a casino bot(economy bot)

[brazen charm]

I'd recommend using postgres

[thorny tusk]

Thanks

[thorny tusk]

Whats the specific link to their docs so I can learn it?

[brazen charm]

https://www.postgresql.org/

[thorny tusk]

Ty

[thorny tusk]

Is this what im supposed to click and read?

To learn it

[unkempt prism]

The first 2 tables look good. For the second 2 tables you want to turn columns into rows. Instead of having 4 columns of player. It will just be matchid, playerid, position. If you have 2 players you will have 2 rows for that natchid when they start a game. If 6 people play you have 6 rows with that matchid etc. Same idea for property.

[civic gorge]

Guys, Could you please evaluate this simple database design that i've made?

does it contain any issues?

[torn sphinx]

Could you be more specific?

[wise night]

Here, anyone aware of RDBMS?

[keen minnow]

It's the topic of this channel

[white scarab]

why the error occurs and how to fix it

[torn sphinx]

global the connection in your next function too, where you printing it.

[ruby sand]

How do you make the text like that

[sudden peak]

Use the key underneath escape 3 times to start and then 3 times to close

Write the language that you want it to syntax in

[torn sphinx]

put `text`
Output:
text

Like dat

If you want code block
Then
```
text
```
Output:

Text

And for Python code block:-
```py
text
```
Output:

text = "text"

[mild rivet]

does anyone use motor here? (async driver for mongodb)

I need some help concerning this error...

i had all dependencies installed p sure

py 3.10.4 windows and motor 3.1.1

i ran this

which should install all dependencies

[mint mauve]

can someone help me with flask database migration in #🤡help-banana ?

[real timber]

Are there any known issues with alembic and adding indices to existing sqla models?

Migrations don't seem to be picking them up, i had issues having CHECK constraints detected for existing models - so am wondering if there's anything similar here

[real timber]

when using op.execute with an alembic migration - is each call a transaction? or is the whole upgrade() a single transaction ?

eg:

def upgrade():
    op.execute( ... ) 
    ... 
    op.execute( ... )

I'm not sure whether there would have been two transactions carried out there or one

[last summit]

when does it make sense to use a database vs. using classes/objects for a series of data files (csv) and iterating into a GUI?

[brazen charm]

It's nearly always easier to use a database unless your files are immutable and can fit into memory.

SQLite will can be embedded, gives you correct durability guarantees, good performance, and easy way to query and modify data.

[last summit]

Ok. So then i will start with interpreting the data and registering it into a database. Thank you

[wise night]

oKI I'm writing Relational Query please let me the output
Here i've used relational operator called union.
Tell me the ouput?
which row will display

[torn sphinx]

what does this mean

[severe rain]

So I installed python-mysql connector but it says-

installed both mysql-connector and mysql-connector-python

(Idk much about this stuff, need for school project)

[frozen grotto]

Those two often conflict, try uninstalling both, and then re-installing mysql-connector-python

[grave ruin]

Isn't there a whitespace in your import?

[severe rain]

I did

alright so the problem I am getting now is this

[torpid heart]

guys is it considered a good practice to work with sqlalchemy's model objects inside business logic or they should be converted to something like pydantic dtos?

[hardy drum]

bhaio help me out

i am getting this error when trying to connect python to sql

already installed all the required stuff i.e. Mysql connector

Since I am new to this, please tell all the possible things I might be doing wrong?

[severe rain]

bhenchod answer mil nahi rahe question aur puchhlo aake

[frozen grotto]

Version mismatch https://tableplus.com/blog/2019/09/bad-handshake-when-trying-to-connect-mysql.html

[real timber]

I'm currently working on something that uses SQLA, Alembic, and postgres. Tables are defined as models in SQLA, then the db is generated via migration files in alembic.

I'm not really enjoying SQLA (or alembic), and would rather be able to just focus on written raw ddl / sql instead of handling the (often leaky) abstraction of these two.

At the moment I guess it's sqla -> alembic -> database, so sqla models are the source. I would like to have database -> sqla, so the sqla models are based on the database DDL instead of the other way around.

it's not something I've done before though - so am wondering if it's something typically done, or if there are reasons one would avoid trying something like this

[velvet walrus]

ok so i have been using sqlite for my project since i just started learnind database, at what point should i upgrade from sqlite 🤔

[storm mauve]

you might want to upgrade if you need of multiple active connections at the same time

but if your project will only run in one machine at a time, even more so if without parallelism (threading/asyncio/multiprocessing), SQLite is probably fine

[paper flower]

IMO it's way better to keep your migrations in source control, alembic is just a tool that can integrate with sqlalchemy, but you can use any other tool for your migrations

sqla also provides sql-like query syntax:

stmt = (
    select(Model)
    .where(Model.column == something)
    .order_by(Model.created_at.desc())
)

[real timber]

I like the idea of source control etc - and assume it's useful to do as people have spent time writing tools which permit it. But all the information is on the database - the DDL can be extracted from pg_dump etc - all the transactions are (i think?) stored in the WAL logs and so on.

SQLA and alembic feel like leaky abstractions and I find myself having to hand-edit migration files a lot.

[paper flower]

What do you edit specifically?

Also sqlalchemy tries to expose all sql features, compared to, say, django it's a lot better

[real timber]

alembic won't detect things such as column constraints added to existing tables - they need to be added in using op.execute, it also struggles with enums when they're removed - the upgrade / downgrade need to be removed there. Often when editing sqla models alembic will struggle to detect changes

[paper flower]

It should detect constraints, you probably didn't configure that 🤔

[real timber]

Hrm - it didn't seem to :S

Also didn't detect when i changed model datetimes to timezone aware

[paper flower]

You didn't configure that as I said 😅

[real timber]

maybe - I'm not sure what to search for here though ?

is there a way to tell it to detect everything ?

I've no idea why i would want to change the models and not have that detected

[paper flower]

https://alembic.sqlalchemy.org/en/latest/autogenerate.html
Search for "Autogenerate can not detect:"

But there's compare_type and compare_server_default
https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.compare_type

[real timber]

I didn't see anything fo rthat search

[paper flower]

without the quotation marks?

[real timber]

ack i'm on the wrong webpage sorry 🤦‍♂️

[real timber]

right - seems it can't detect check constraints then ? Like a regex constraint on a column etc

[paper flower]

Yep 🤔

[real timber]

yea - which is annoying

I feel as though I'd be happier to have sqla based on the database, rather than dictating the database, but idk if that's a weird use of it

[paper flower]

It would be really annoying for other people to work with imo

[real timber]

what would it break ? I've not got enough experience with it to know

[paper flower]

1. It's easy for the general state(excluding constraints) to get out of sync
2. Developers would not be able to easily create db for local development or testing

Previous developers at my company decided to migrate some things in our db by hand

[real timber]

couldn't a local db be creating using the raw ddl instead ?

[paper flower]

Now I can't set up local db easily

Yes, but migrations are much easier 😉

[real timber]

I'm not sold on migrations being easier i guess

I'd like to be, as it's what's currently there ha

[paper flower]

You also have to, well, evolve your schema somehow

That's what migrations are for

[real timber]

sure - but I could ALTER TABLE ... and have the same effect couldn't I ?

[paper flower]

Yeah, now you have undocumented change and all developers have to pull that change somehow

[real timber]

the change is on the database - they can get the ddl from pg_dump with schema only

the change is documented on the database

[paper flower]

Much harder compared to typing alembic upgrade head imo

[real timber]

idk - i don't think so, but i feel i must be missing something

cynically it seems that a lot of sqla usage is because people don't know sql

[paper flower]

I use sqlalchemy to dynamically build queries and map sqlalchemy tables/queries to object

That's what it's for 😅

[real timber]

right, i've no doubt there are valid uses, I really don't have experience but am just trying to work things out.

It seems that the database isn't a first class citizen in this sort of work flow though ?

one just looks at / concerns oneself with sqla, which is (partly) the point of it i guess 😅

[paper flower]

isn't a first class citizen in this sort of work flow Why not? 🤔

[real timber]

hm, maybe that doesn't make sense... I was thinking that - as the code is written in sqla and the migrations are in alembic there's no need to consider some of the things that are already on the db... but I don't see why a migration file is really any better than point in time recovery and transaction history on the database.

[paper flower]

What migrations have to do with transaction history? I don't quite understand you

Also what kind of application are you making?

[real timber]

the database is supporting a few things - but the main app i'm exposed to is a rest api

[paper flower]

Are you working solo? 🤔

[real timber]

no not working solo

[paper flower]

I'd strongly consider using some kind of migration tool, either alembic or https://ollycope.com/software/yoyo/latest/

[real timber]

thanks i'll have a gander... the use of sqla as a source of truth vs being based on the database (and that being the source of truth) isn't so clear to me - it seems that the latter would be atypical?

[paper flower]

When working with sqlalchemy - in most cases yes 🤔
I personally just check migration files that alembic generates, if something isn't right I correct it 🤷‍♂️

If you have a lot of check constraints that might be a pain 😅

[real timber]

: ' )

Yeah - i'm not too sure what it will do with triggers, views, materialized views and so on either

[paper flower]

Sqlalchemy/alembic doesn't support that afaik 🤔 I personally don't use triggers, trying to move as much logic as possible to my application instead

Materialized views are useful sometimes though

[real timber]

hrm, yeah so if you have some query that's v expensive (for an api), and depends on some data - you might want to create a table of the aggregates and have a trigger to re-generate it when data is added to the table

[paper flower]

You can as well trigger view refresh in app logic but that's up to you 🤔

You can reflect view into a table object if you need to though

In case you need to dynamically construct your queries

[real timber]

hm... yeah i've not done this in sqla before... which annoys me bc i don't want to say i don't like something when in fact i just don't know it ha

[paper flower]

There are just no better orms, and sqlalchemy allows you to do the most things without using raw SQL, so I'd stick with it

[real timber]

we also have some queries written in raw sql - and increasingly so as sqla didn't/doesn't play nice with the database we're using

[paper flower]

It's much easier to work with typed python objects than with random tuples after all

[paper flower]

Why is that?

[real timber]

but they're not random tuples if they're from a database - they have types, and can have relations and constraints

[real timber]

think it was the hypertables in timescale

[paper flower]

I mean tuples that you get from your queries😅

[paper flower]

Can you share an example?

[real timber]

yea - but data in sql feels like it's a lot nicer than something like pandas a lot of the time, as far as being well formed / constrained etc... i know there's panderas but still

[real timber]

no actually, sorry i don't have one 😦 but i think it was a thing

[paper flower]

You can use all SQL functions via sqlalchemy.func if than was the problem 🤔

By objects I mean python classes (sqlalchemy models or you can also use dataclasses)

[real timber]

yea, with pydantic / pandera and stuff python can have some nicer things as well it's true

[paper flower]

Sqlalchemy 2.0 should also increase orm bulk insert performance, since you use timescaledb it seems important 👀

Did you consider any alternatives?

[real timber]

alternatives to sqla ?

[paper flower]

Yep

[real timber]

no, wasn't my design decision (i came in after), and i'm not saying it's a bad one either - i don't know enough to say that ha... just expressing my bias 😄

[paper flower]

Coming from active record orms like Django orm sqlalchemy allows you to do much more

[real timber]

i guess i'd rather have more sql than django or something

[paper flower]

If you're doing any complex queries - yes

I'd say in there cases Django is counterproductive

Also you should ask your team how they want to work on that project, do you need migrations, etc

[real timber]

yea, the sql / db experience is pretty low on the team i think - the need for migrations is something i'd be interested in though i think

[paper flower]

Imo using pg_dump or something similar won't be really productive, you'd have to post it somewhere or ask for it 🙃

[real timber]

oh - you could automate the posting of it though no ?

[paper flower]

You can, but I'd rather just stick to migrations, that's my opinion

When someone makes a change you can apply their migration

[real timber]

yea all good - i don't have enough experience so am just trying to work out where they're particularly useful / when they might not be etc

[paper flower]

Yeah, that's interesting question 🤔

Imagine we have two environments with some "live" data, e.g. production and staging

You want to apply some changes from your dev branch to staging environment, currently I do that via alembic, so I can just apply them before deploying my application. If migration fails deployment fails also

You can do that by hand, but that's

1. Not automated
2. You'd have to sync changes with your production environment

[real timber]

staging is something i'd like to get 😅

[real timber]

right - so currently the work flow is to sort of 'step test' by hand - upgrade / downgrade / upgrade with alembic to make sure that it's working with the last migration

[paper flower]

You can run your migrations in automated tests too 👀

[real timber]

i could have some upgrade head / downgrade -1 / upgrade head thing in it i guess

[paper flower]

Downgrade -1? What if there are multiple migrations?

[real timber]

then that wouldn't work ha

[paper flower]

How do you currently deploy btw?

[real timber]

when updating the prod database ?

that's done using alembic upgrade

[paper flower]

Just how do you generally deploy your application?

[real timber]

this is something that's a little clunky maybe - the api is in a separate repo to where the sqla models are defined, so the api repo depends on the repo with the sqla models... so it's possible to break the api by changing things to the sqla models without catching it 😬

the api is containerised and deployed somewhere from ci, i've not actually gone through that yet though

[paper flower]

As of lately I deploy apps via helm + k8s in CI, but I didn't come up with anything to rollback my migrations if I need to rollback a particular release 🤔

I run migration as a pre-install hook

[real timber]

hm, yeah i- there's nooo way we're going to roll back to some of the migration scripts that are in the alembic/versions dir, so i do wonder what the point is at that stage

[paper flower]

Also some migrations are just destructive 🙃

[real timber]

yea - we have 6 day rolling backups i think

so , hopefully caught in that time 😅

[torn sphinx]

How I can connect my python code to database?

[torn sphinx]

Is anyone here to help me?

[unkempt prism]

#❓｜how-to-get-help

[unkempt prism]

For example to connect to python to an sqlite db you can use the sqlite driver built in https://docs.python.org/3/library/sqlite3.html.

If you have any other type of database you can likely pip install an 3rd party often open source driver maintained by a community.

[grave yacht]

Any idea? (sqlite3)
edit** changed query edit2 got it, switched last 2 lines```sql
CREATE TABLE IF NOT EXISTS users (
uid TEXT PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS preKeys (
user TEXT UNIQUE,
FOREIGN KEY(user) REFERENCES users(uid),
keyBytes BLOB UNIQUE
);

[tranquil salmon]

hey guys not sure if this is correct discord

but im trying to use a github free source supply chain software (GreaterWMS)

i have deployed a live server via lightsail postgresql db

and connected it to my django python app

[versed robin]

Sorry for the noob question:
"Is there a easy way to 'time travel' in data in a postgresDB and show what the date from e.g. a table was 6 month ago and highlight changes like:
Items added
Items deleted
Items changed
Hints are very welcome.... This is a example of the table strucutre:

[tranquil salmon]

however I am getting a network exception error and have no idea why I am getting a network exception error

like I would except my static ip django app to update the login info into the postgreaql database but i keeep getting a network exception error

would this be the right topic chat?

like the migration worked

and the datebase had all the tables

however the following error occurs when I try to log a admin user so I can actually use the app

this has been driving me nuts I dont even know where to look to start

I can confirm in my setting.py

my allowed hosts includes my static IP

my end point for the postgresql db

and can confirm when I run daphne

that it is receivign the requests when I go throught the application

but the only thing that is not working is when I try to register an admin account I get the network exception

i dont have a django-admin app

but dont know how that plays into this

and assume that is the key I am missing?

if anyone could point me in the right direction would greatly appreciate

[grim vault]

Table constraints must be done after all columns:

        CREATE TABLE IF NOT EXISTS preKeys (
            user TEXT UNIQUE,
            keyBytes BLOB UNIQUE,
            FOREIGN KEY(user) REFERENCES users(uid)
        );

or define the reference directly at column level:

        CREATE TABLE IF NOT EXISTS preKeys (
            user TEXT UNIQUE REFERENCES users(uid),
            keyBytes BLOB UNIQUE
        );

Looking at the table definition you could just add the keyBytes column directly to the users table.

This: user TEXT UNIQUE REFERENCES users(uid), makes it a 1:1 reference between the two tables.

[white current]

how do i do an if else in sqlite

[torn sphinx]

pls what's ur theme vs code

@white scarab

[white scarab]

Andromeda

[fair girder]

if you are given data in batches in xls format (once a day), what is the recommended way to upload them to bigquery + do some pre-processing to make another bq table

[grave yacht]

Ahh I should remove the UNIQUE I want it to be user 1--* preKey

[grave yacht]

Ok I think this works sql CREATE TABLE IF NOT EXISTS users ( uid TEXT PRIMARY KEY ); CREATE TABLE IF NOT EXISTS preKeys ( fromUser TEXT NOT NULL, keyBytes BLOB UNIQUE NOT NULL, FOREIGN KEY (fromUser) REFERENCES users(uid) ); If I want preKeys to be indexed on fromUser do I need to add a INDEX statement or is it already indexed because it references a foreign key?

Just gonna go with writing it to be safe I think

[grim vault]

No, I don't think so (maybe database dependent). SQLite does not index a FK constraint.

[kindred nacelle]

Hello folks! I could use some assistance figuring out how to use WITH DELETE RETURNING properly. The query on the screenshot returned an error column "deleted_id" doesn't exist. So could anyone please explain to me what have I done wrong there?

[wise goblet]

SELECT
    pe.post_id as post_id,
    pe.tags as tags,
    MAX(pe.created_at) as created
FROM post_edition pe
GROUP BY pe.post_id

I am trying to select any first record containing pe.post_id in each group, that has maximum pe.created_at attribute (among same pe.post), how to achieve it?
(+I need tags of this record selected too)

[cobalt mirage]

Connection is not defined. Please define it

[kindred nacelle]

The task is to remove a row from College_groups table with the group_name of "CS21". The trick is that there are rows in the table Students which include a foreign key referencing the row from College_groups which is to be deleted, and at first I thought that it would be smart to use RETURNING on the first DELETE to construct this recursive deletion into one query, but now I'm struggling :^(. The task is solved really simply through hard-code (e.g. DELETE FROM Students WHERE group_id = 2; DELETE FROM College_groups WHERE group_name = 'CS21'), but I'd really like the smart solution here

[wise goblet]

SELECT
  pe.post_id as post_id,
  pe.tags as tags,
  MAX(pe.created_at) as created
FROM post_edition pe
GROUP BY pe.post_id, pe.tags

not sure if correct, but oh well, it works

[harsh pulsar]

https://stackoverflow.com/a/38854846/2954547 here is the canonical solution to this problem

[wise goblet]

makes sense.

will GROUP BY with MAX version yield similar results?

[harsh pulsar]

your groupby version won't give you the first (created earliest?) row for which that condition is true

[wise goblet]

okay, partition it is then 🙂

or denormalizing xD

[harsh pulsar]

SELECT
  t.post_id,
  t.tags,
  t.created_at
FROM (
  SELECT
    *, row_number() OVER (
      PARTITION BY pe.post_id
      ORDER BY pe.created_at DESC
    ) AS created_recency
  FROM post_edition AS pe
) AS t
WHERE t.created_recency = 1

something like that

although i'd usually want to rewrite it as a CTE

[wise goblet]

thanks. i'll try it

[harsh pulsar]

WITH t AS (
  SELECT
    *, row_number() OVER (
      PARTITION BY pe.post_id
      ORDER BY pe.created_at DESC
    ) AS created_recency
  FROM post_edition AS pe
)

SELECT
  t.post_id,
  t.tags,
  t.created_at
FROM t
WHERE t.created_recency = 1

i think you can also do this with HAVING but you end up with the same issue i think

[wise goblet]

looks cleaner to me

[harsh pulsar]

yeah CTEs usually are

[wise goblet]

thanks

finally getting close to finish this postgresql training xD

[harsh pulsar]

i think in postgresql, CTEs used to be an "optimization fence" while nested queries were not

that limitation was removed in recent versions, but you might want to consult the changelogs to see which versions

i think other databases operate that way still. we used them in snowflake all the time and i don't think they are optimization fences there

https://www.reddit.com/r/SQL/comments/hgd10h/cte_optimization_fence/ @wise goblet

[paper flower]

If you want to select first/last element from group you could use distinct

[hexed torrent]

Is any body here how can help me to connect python code with database?

[harsh pulsar]

good idea

[glacial smelt]

Hello does anyone has a couple of minutes to help me for a school project with SQL ?

[robust sequoia]

best thing to do is use one of the help channels

[forest raptor]

Hello, I get this error. I think this belongs in databases, as it errors at the database.

Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Python310\lib\tkinter\__init__.py", line 1921, in __call__
    return self.func(*args)
  File "C:\Python310\lib\site-packages\customtkinter\widgets\ctk_button.py", line 377, in clicked
    self.command()
  File "C:\Python310\lib\site-packages\pymongo\message.py", line 370, in get_message
    request_id, msg, size, _ = _op_msg(
  File "C:\Python310\lib\site-packages\pymongo\message.py", line 673, in _op_msg
    return _op_msg_uncompressed(flags, command, identifier, docs, check_keys, opts)

(There's more)

While running the below code:

https://pastebin.com/gv7nSPdv (Admin class part)
https://pastebin.com/qXpKuAHi (Tkinter Part)

[bitter hare]

How wold I go about building an api to acsess a database?

[pure sleet]

build a webserver that interacts with a database

[pure sleet]

doesn't a WHERE clause work for this

[torn sphinx]

like where col1,col2 in '(a,b),(b,c)'?

[merry chasm]

Can someone help me on this? Using the two buttons on the cpx board. Your function will record whatever pattern the user presses the buttons in. So if press the two buttons in the order right, left, left, right you function would output the string "RLLR"

[fresh swift]

Hey, I guess it is to do with databases but not directly. It is about saving the right content in openpyxl workbook. I am desperately trying to preserve new lines (\r\n) when I save or load an xlsx file, but somehow it skips those chars

does anyone know how can I fix this problem? I need these new lines chars inside the xlsx file when I save and load it

[unkempt prism]

What isn't working for you? Is it the same issue as https://stackoverflow.com/questions/15370432/writing-multi-line-strings-into-cells-using-openpyxl
Lets take this to another channel as its not database related.

[fresh swift]

not when I try to read it back

it seems that the character \r is translated to _x000D_ and I am not sure if there is an elegant solution

[fresh swift]

What channel will it fit to then?

[unkempt prism]

Honestly its a bit obscure so I don't know? Perhaps the most relevant issue on the projects page: https://foss.heptapod.net/openpyxl/openpyxl/-/issues/263

[opaque wave]

is there an equivalent to np.product in bigquery? i.e. i want to groupby a col and find the product of another col

[keen spoke]

can i ask about ERDs here?

[grim vault]

!e That's why you should not use f-strings. Your user.id is None, so your f-string becomes:

class User():
    pass

user = User()
user.id = None
print(f"SELECT * FROM sometable WHERE user_id = {user.id}")

[delicate field]

@grim vault :white_check_mark: Your 3.11 eval job has completed with return code 0.

SELECT * FROM sometable WHERE user_id = None

[grim vault]

No, use placeholder parameters.

!sql-f-string

[delicate field]

SQL & f-strings
Don't use f-strings (f"") or other forms of "string interpolation" (%, +, .format) to inject data into a SQL query. It is an endless source of bugs and syntax errors. Additionally, in user-facing applications, it presents a major security risk via SQL injection.

Your database library should support "query parameters". A query parameter is a placeholder that you put in the SQL query. When the query is executed, you provide data to the database library, and the library inserts the data into the query for you, safely.

For example, the sqlite3 package supports using ? as a placeholder:

query = "SELECT * FROM stocks WHERE symbol = ?;"
params = ("RHAT",)
db.execute(query, params)

Note: Different database libraries support different placeholder styles, e.g. %s and $1. Consult your library's documentation for details.

See Also
• Python sqlite3 docs - How to use placeholders to bind values in SQL queries
• PEP-249 - A specification of how database libraries in Python should work

[grim vault]

The params should be the variable, not the literal string of the variable name, so just:params = (user.id,)

[pure sleet]

delete from table where cond1 and cond2

That is possible in sql

If you have certain conditions you don't want to delete, just remove them from the list etc

[loud scarab]

is this the place to ask help with pandas?

[orchid gyro]

My task is to build a pc builder.. basically when a person choose cpu , only the motherboard that supports the cpu needs to be shown. Similarly, other components like graphic card and stuffs too. How do I store data for this and what is the best algorithm to achieve this sort of filtering?

[vast tiger]

lol'

[brave tree]

@eager tide what's the groupby here for?

group by
  t_as.grn_id
  and t.po_id != '0'
  and grn_type != 'post_grn'
  and t.app_status in(0, 1, 2)

[eager tide]

group by for t_as.grn_id which is similar ids for po number and I aggregate their values based grn_id

@brave tree

[brave tree]

what about the ands

are they meant to be part of the where

[eager tide]

@brave tree yes

[brave tree]

does that fix the issue

[eager tide]

if I remove sum function and group by it works fine

but I want this values

[brave tree]

mm not too sure what sql engine you're using but maybe its possible that it doesnt allow for aggregate functions (groupby, sum, etc) alongside normal columns?

[eager tide]

2014 version

[brave tree]

mysql, sqlite, ?

[eager tide]

MSSQL

[brave tree]

mm yep

seems like they dont allow a column not used in aggregate/groupby

is it a syntax error or runtime

[eager tide]

I used in other and works fine

[eager tide]

syntax error
SQLServerException: Incorrect syntax near the keyword 'where'.

[opaque wave]

hey people can anyone help me with the following?

I'm uploading a csv to cloud storage and then loading that into a table in bq. I'm trying to set a schema but I run into an issue with the timestamp columns. They are written in this format '2022-11-24 00:00:00 UTC -04:00", is it impossible to load it as timestamp?

[pale niche]

hi

[queen nebula]

Where exactly would the performance degrade with deferrable constraints in postgres? https://www.postgresql.org/docs/current/sql-createtable.html#id-1.9.3.85.9.4 mentions it potentially being significantly slower but I'm not sure how much of an impact it'd have compared to the alternatives

[brazen charm]

My immediate assumption would be that performing the check on bulk operations would require an additional scan over the table and validating all the rows the previous operation mutated as part of the check. Vs checking as it goes. That being said, the actual performance impact of that would depend on the operation itself, indexes, etc...

[queen nebula]

I assume it'd only show in situations where it's also required for it to be deferrable? It's for a table where there's a unique constraint on a priority column that can be reordered, so I think the only way to go around that would be to reset all the relevant rows to null in the column, save it, and then assign the new values

[brazen charm]

Yeah, I don't it's really used a whole lot in reality.

[neon plank]

Hello, what are the correct ones?

[brazen charm]

this looks like an exam

[neon plank]

Its a self-study program on epam. I think my answers are correct but it doesn't work. I wonder what other correct scripts are here

[brazen charm]

Why don't you try running them in a local database

[grim vault]

Maybe it's interaction.user.id instead of user.id but this seems to be a question for #discord-bots

[cedar sage]

can someone help me understand how yo set up a database in flask

[unkempt prism]

I don’t think psycopg2 is async compatible. It’s my understanding you need psycopg3 for async.

[real forum]

Yes, I've found the problem

The Message ID was not the same, so None

[unkempt prism]

I’m obviously wrong about needing v3 for async.

[real forum]

yes

[primal wigeon]

I’m about to work on my first project involving a database. Trying to decide between MongoDB and a relational db. Currently I’m storing Spotify streaming history in JSON format and doing analysis/viz with Pandas/Seaborn. Down the road, I’d like to turn it into a web app where users can upload their data (scrubbed of PII with client-side field/cell-level encryption) to receive preset tables / visualizations, with the option to create an account for persistent storage. Further in the future, I’d like to provide an interactive dashboard and possibly options to compare streaming history with other users.

Typically an individual’s streaming history will be <500,000 rows, and likely <200,000. No fields need to be updated. There are entries for song metadata and the timestamp of the stream. “Audio features” like tempo, key, etc can be retrieved using the Spotify API.

Any help would be appreciated. I can give more info, just wanted to avoid writing an even bigger wall of text.

[keen minnow]

I recommend to start with relational databases.
There isn't much benefit to mongodb

[leaden nacelle]

I have a table called publisher which has the publisher id number, publisher name, and the id of the books

CREATE TABLE Publisher (
PublisherID INT NOT NULL,
PublisherName varchar (50) NOT NULL,
PRIMARY KEY (PublisherID));

ALTER TABLE library.publisher
ADD COLUMN BookID INT,
ADD FOREIGN KEY BookID(BookID) REFERENCES books(BookID) ON DELETE CASCADE;

How can I alter the table so that I can have a one to many relationship between the PublisherName with the book ids?
This would allow me to have it so each publisher has multiple books.

Right now I can only have 1 book id for each publisher and can't add more than one. Or would it be possible to have multiple book ids for each publisher id? How could I go about doing that?

[pure sleet]

create another table for the relationship, i.e PublisherBooks for example, then take publisher id and book id as foreign and primary composite keys. for one to many relationship, you basically want the bookid to be unique in this table. so that one publisher id can have different books if that makes sense

[mint wharf]

I need to set a Datetime field to CURRENT_TIMESTAMP + 3 Days.
Using SQLite on a Flask web app. Any help?

[leaden nacelle]

Ok thank you @pure sleet

[pure sleet]

unless a book can have multiple publishers, then the book id need not be unique but you'd need to take care of duplicate entries

[pure sleet]

python has a datetime module. you can achieve this with it easily

[wary vine]

I need a help where to ask for help?

[keen minnow]

#❓｜how-to-get-help

[worthy vale]

question on spotify: is it possible to download jre podcast from spotify to my hard drive throught python or something

[primal wigeon]

No, and I don’t think we are supposed to discuss piracy here. Also listen to Behind the Bastards or something instead 🙃

[modest fjord]

I think I need something other than a traditional database. Or maybe some sort of mixed solution, but I'm not sure.
What I want to do is have different shapes of part, and depending on which shape is selected, I want to have different dimension fields which may have different units or different required precisions.
I guess I could have a different table for each possible shape. But that would require api changes every time a new shape is added I think?
I'm not familiar with anything other than bog standard SQL, are there any sugestions?

[tribal light]

@modest fjord, this sounds like 2-3 linked tables in an E-R model. In Python you can work with SQLAlchemy on similar problems and there is a package called alembic that helps reverse engineering existing ER model to SQLAlchemy objects.
SQLAlchemy is an ORM framework that maps Entities to Objects, so to speak.

[mint wharf]

Hey, I'm making a Flask web app and I'm using Sqlite3. I need to run an UPDATE query and also retrieve the rows that were updated, or at least their Primary Keys. Any advice?

[brazen charm]

You can add RETURNING my_id; to the end of your query to get the rows that were updated, replacing my_id with what ever your primary key is, or you can just return the whole row, depends on what you need.

[mint wharf]

Thank you!

[rugged crane]

The SQL is invalid because the foreign key constraint (Comarcas_fk) for the Temperaturas table does not specify which columns should be referenced in the Comarcas table. It should be written as:

CONSTRAINT Comarcas_fk FOREIGN KEY (estacion) REFERENCES Comarcas (estacion)

[topaz walrus]

Anyone know how to use subqueries with sqlalchemy 2.0 syntax with (session.execute(query))?

eg. pseudocode:

sub_query = select(MyTable).where(MyTable.someField > 
1).subquery()
query = select(MyTable).where(MyTable.author_id in sub_query)
...

^ The issue I hit with that is
argument of type 'Subquery' is not iterable

[rugged crane]

I believe you need to use the .select() method on the subquery for it to work. Try this instead:

sub_query = select(MyTable).where(MyTable.someField >
1).subquery()
query = select(MyTable).where(MyTable.author_id.in_(sub_query.select()))
...

[topaz walrus]

Thanks! I think that worked, with in_ from

from sqlalchemy.sql.expression import ColumnOperators

[torn sphinx]

What types of web apps or services do noSql and Sql databases benefit?

I want to write a raw upsert query in mysql. where 
if (id , category and type) is equal to the payload :
        then Update
else : 
      insert the payload 
Have Already Tried Replace and Insert on duplicate Key

PS : category and type cannot be unique

[keen minnow]

Anything that needs to store or query data

[distant furnace]

Hey guys! I want to ask (cuz am very new to python): Is it possible to take specific data from a pdf into a table in word using python? I have this pdf with all exam dates but its for all subjects in the world. But, I wanna only extract my subject data, the date, and then the type of paper we'll write (AS/A level cambridge examinations)

[upbeat pilot]

hey guys, i'm looking for help with python in pycharm

i am new to coding and i'm having trouble with a tutorial

[young tree]

show the error

[upbeat pilot]

so for me, everything following the cur.execute turns into a string, but in the tutorial it doesn't

i'm written it exactly like the tutorial

[upbeat pilot]

@young tree

[young tree]

put a semicolon

after the password paranthesis

[grim vault]

? Everything after the """ is a string until the next """, an SQL command which must be send as string to the database server. That's ok like it is.

[upbeat pilot]

the tutorial shows like this

is it fine

[grim vault]

Well, that depends on your IDE/editor if it recognises SQL inside a string.

[young tree]

just run the command of table if it exist or not to check

[upbeat pilot]

sorry i am very new to coding. i have imported sqlite3 and i'm using pycharm

[grim vault]

The code is ok like it is.

[upbeat pilot]

ok thanks

[grim vault]

SQL highlight:

create table tabname
(
  id_col integer primary key,
  text_col char(100)
)```
python highlight:
```py
curs.execute("""
create table tabname
(
  id_col integer primary key,
  text_col char(100)
)
""")

[upbeat pilot]

do you know why it shows as string and not as sql?

sorry i'm just trying to understand

[grim vault]

Because for python it is a string. I don't use pycharm, maybe it does have a plugin / setting to highlight inline SQL syntax.

[upbeat pilot]

do you use vs code?

[grim vault]

No, I use an text editor called TextPad.

[brazen charm]

For DB linting you need PyCharm Professional unfortunately

[upbeat pilot]

oh okay

thanks for the help guys

!!

[green pier]

I've had this on my todo list for a while. I'm a neovim user and I have this YT video bookmarked to get into at some point: https://www.youtube.com/watch?v=v3o9YaHBM4Q&t=850s

[torn sphinx]

I still dont understand why
Why instead of leaving action attribute empty or putting the filename itself why do we use this

[modest fjord]

Is there any advantage to using an async library like aiosqlite when I'm just doing a simple crud api with FastAPI?
If I understand correctly, I only start getting any benefit if I have long running tasks that don't hit the database. Like sending emails or reading files.
Or is there some sort of under the hood optimization?

[wise goblet]

xD found SQL roadmap. Sounds like useful thing to make

[left igloo]

hi. I am having a problem while inserting some multiple row strings at a bigquery db

can someone help me?

the error is illegal escape newline at [13:178]

I am trying to insert a long string, which has a bunch of rows, that as I said they should belong to the same string. The thing is that when I enter a new line it is not taking it as the same string

[silent quest]

Hey. I am starting new project (mostly in educational purposes) that involve database usage. Can someone clearify a few things for me in PM? 🤝

[wise goblet]

better read how to ask questions in a good way https://pythondiscord.com/pages/resources/guides/asking-good-questions/ and ask your questions here in public
asking in DM is very rude thing to ask, because you don't value other people's time then / and preventing sharing-spreading of knowledge / preventing more quality input from multiple people.
When it is asked in public, your question is answered by any asynchronously available person, while asking in dm you are synchronously bothering specific person without regards if he is busy or not.
Some people answer questions here just for the sake of being corrected by other people and learning more useful knowledge in the process of answering to others
Some people just like to read answers to questions and finding useful bits for themselves. Everything of it will not happen in DM.

[modest fjord]

I'm attempting to switch to SQLModel (which wraps SQLAlchemy & Pydantic so you don't have to declare schema separate from model)
Here you can see I'm trying to define a Category table that can form a tree structure.

class CategoryBase(SQLModel):
    name: str = Field(index=True, nullable=False)
    child_prefix: str = Field(nullable=False, max_length=20)
    parent_id: Union[int, None] = Field(default=None, foreign_key="category.id")

class Category(CategoryBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    parent: Union[Category, None] = Relationship(back_populates="children")
    children: List[Category] = Relationship(back_populates="parent")

But clearly I'm misunderstanding something from the docs, because I'm getting an error when I actually try to do anything with these models:

sqlalchemy.exc.InvalidRequestError: When initializing mapper mapped class Category->category, expression "Union['Category', 
None]" failed to locate a name ("Union['Category', None]"). If this is a class name, consider adding this relationship() to 
the <class 'backend.slices.category.Category'> class after both dependent classes have been defined.

The docs I'm trying to go off https://sqlmodel.tiangolo.com/tutorial/relationship-attributes/define-relationships-attributes/

[winter sparrow]

I know this is not related to python but

What's the difference between these two? (Other than one of them has unique that is)

[unkempt prism]

If I was going to guess the first one shows each field with its own index. and the second shows a multicolumn index as per https://www.postgresql.org/docs/current/indexes-multicolumn.html

[winter sparrow]

Ah thank you

[light ice]

I need help 🙂
ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 139644041131840 and this is thread id 139644009989888.
I can share the code via DM if anyone is interested in helping me!
Solved it 🙂 Thanks

[rare monolith]

Can someone help me with a small SQL code I am trying to write

I am just stuck. It seems like I get no error but I also don’t get any results

I am a beginner in SQL and I have not grasped nested queries fully yet. I need to write a query where I can choose a person(Jack), then list all the friends Jack has. Then I want to list all the friends Jack’s friends has.

So I wrote this query

Sorry for taking picture with phone. No discord on laptop

These are the tables

[rare monolith]

So I have changed the query a bit and actually got some result now.

Well the nested quarry is working but the number-of-friends is showing 24 for each person.

When it should be 2, 5 and 4

[shrewd basalt]

await is not working in psycopg flask how to fix it

[unkempt prism]

Async examples on psycopg3 docs use context managers. Might be worth trying to change it to match. https://www.psycopg.org/psycopg3/docs/advanced/async.html#asynchronous-operations

[wise goblet]

.bm

[viral hill]

Click the button to be sent your very own bookmark to [this message](#databases message).

[teal sparrow]

Any recommendations for getting better in databases? So far I've failed a few interviews because my database skills are not good.

First interview I failed because I could not design a database for a search engine. Second I failed because I didn't know about consistent locking. Don't want to fail the third

[viral hill]

Please don't do that.

@teal sparrow, please enable your DMs to receive the bookmark.

[teal sparrow]

Please ping me on reply

[silver crow]

Hey,
I have data in document's array field in firestore database
how to retrieve that data using javascript

[torn sphinx]

What job were you applying for and at what level ?

[teal sparrow]

Backend developer. I have 2 years of exp so junior

[torn sphinx]

I would not expect an interview for someone with 2 years experience to ask questions about locking. Especially for a backend developer.

Are you familiar with the normal forms and how normalisation works for databases?

[teal sparrow]

No. Which is why I came here for advise. Let's say my skills are at the level of creating models for my app data

I know a lit bit about normalisation but only theory

[queen rose]

is this normal??

with db.conn as cur:
    cur.execute('CREATE VIEW test1 AS SELECT year FROM forest_area LIMIT 5')
    out = cur.execute('SELECT * FROM test1')
print(out.fetchall())
[(2016,), (2016,), (2016,), (2016,), (2016,)]

with db.conn as cur:
    out = cur.execute('SELECT COUNT(*) FROM test1')
print(out.fetchall())
[(5,)]

with db.conn as cur:
    out = cur.execute('SELECT * FROM test1 ORDER BY year')
print(out.fetchall())
[(1990,), (1990,), (1990,), (1990,), (1990,)]

with db.conn as cur:
    out = cur.execute('SELECT * FROM test1 ORDER BY year DESC')
print(out.fetchall())
[(2016,), (2016,), (2016,), (2016,), (2016,)]

this is with SQLite3 btw

[torn sphinx]

What exactly did you find difficult about the search engine question. I assume the requirement was to build a simple search facility to search and query data from the database ?

For that and with 2 years experience I would expect you to be confident with knowing what models/tables you should create and in which of those tables to store data. Obviously the design of the database does not need to be perfect but enough to get the job done.

[queen rose]

Im disappointed with sqlite, I wanted to do a function that returns a slice of a table/column using the LIMIT statement, but as you can see the data changes when you sort the table/column in the opposite direction.
from my (limited) knowldge this isnt supposed to happen as the VIEW is created first, and then the SELECT statement should run only on the output of the VIEW. if thats not the case than we should be able to SELECT * from a nested view hundreds of times and it should still perform similarly, right?

anyways I've found that doing WHERE _rowid_ <= 5 works better in this case

[wise goblet]

i think you mistake what for view perhaps. may be u were looking for CTE then (for temporal result to reuse between queries)
https://www.sqlite.org/lang_with.html

or u wish materialized view

[waxen finch]

im not sure of how views are expected to behave, but it appears that the view's results will be consistent if you define the order inside the view: sql SQLite version 3.39.4 2022-09-29 15:55:41 sqlite> CREATE TABLE forest_area (year INTEGER); sqlite> INSERT INTO forest_area VALUES (1990), (1990), (1990), (1990), (1990), (2016), (2016), (2016), (2016), (2016); sqlite> CREATE VIEW test1 AS SELECT year FROM forest_area ORDER BY year LIMIT 5; sqlite> SELECT * FROM test1; 1990 (repeated 5 times) sqlite> SELECT * FROM test1 ORDER BY year DESC; 1990 (repeated 5 times)

[wise goblet]

😆 even better.

[queen rose]

ok thanks, the only issues is that I dont actually want to sort the column, I just want to maintain the same order (and I dont know it)

[queen rose]

I thought CTE's and VIEW's worked in the same exact way (like you would save the output to a variable in python and then filter it further)

materialize'd view sounds more like what I'm after

but I really dont know what im talking about tbh lmao

it seems that for performance alone, the regular VIEW is better. but I guess I'll just use the _rowid_ column to get the first n rows

[teal sparrow]

Actually the question was to design an auto complete system

So if a user types in a word we should know what the next word should be using only the database. Again, what type of database would be right for an auto complete system and how would one model relationships to give the right data.

For this question I went with an SQL dB but looking back I think a no SQL would have been better. Again, if this question is given to me right now I can't answer.

So the question is what do I need to learn to improve?

[torn sphinx]

Well for an interview, this kind of problem can be solved with a SQL database so not sure why you were trying to look elsewhere such as nosql. It’s just going to make things a little more complicated especially if you aren’t familiar with it. Instead you should familiarise yourself with basic query operators and learn how to query data out of the data store for different kinds of scenarios.

For the autocomplete you could’ve just used the SQL “LIKE” operator to match strings based on a pattern.

[teal sparrow]

Any projects, books to familiarise myself?
Still don't know what to do to get better 😅😅

[torn sphinx]

https://sqlbolt.com

Probably not what you need but has good problems http://www.artfulsoftware.com/infotree/queries.php?w=378

[teal sparrow]

Thank you. You showed me where my problems were and gave me resources 😀

[teal sparrow]

.bm

[viral hill]

Click the button to be sent your very own bookmark to [this message](#databases message).

[grim vault]

How about:

CREATE VIEW test1 AS SELECT year FROM (SELECT year FROM forest_area LIMIT 5);```

[little quartz]

should i change all incomplete data to avg of each column in 4th and 5th

or it would be better to delete the first half of 2nd and 3rd column

?

[fleet pebble]

Are X_London and X_paris the same thing (E.g. Population), but in two different cities?

[little quartz]

yes

[fleet pebble]

Why don't you do something like:
Date | X_value | Y_Value | City

[little quartz]

because there only 2 cities

[fleet pebble]

What's the end goal?

[little quartz]

i have to make some analysis on it like parametral and nonparametral tests corelation and regression and analysis of time series

[fleet pebble]

The advantage of having the data frame in
Date| X_value | Y_Value | City
format is you can easily filter with:
London_data = df[df['City'] == 'London']
Or you can just specify two different data frames:
London_data = df[['date', 'X_London', 'Y_London']]

In the sense of your correlation tests and time series analysis, either one should work.

You don't really want to fill in the average as this doesn't make sense in time series. And, you can't really delete the first/2nd half of your missing columns as they are tied to your first and second column.

[little quartz]

ok thx

[queen rose]

wrong channel sorry

[soft gorge]

CREATE TABLE IF NOT EXISTS members(member_name TEXT, member_id INTEGER, member_sales INTEGER DEFAULT 0);

delimiter $$
create trigger my_insert
after insert on members
for each row
begin
    update members set member_sales = member_sales + 1 where member_id = new.member_id;
end$$
delimiter ;

I'm trying to create this trigger for my table and when I try to run this with aiosqlite using the .executescript method I am getting an "error near "$$": syntax error" Is this something to do with aiosqlite / mysqlite?

[inner sentinel]

databases are a pain

(I'm using sqlalchemy.orm and postgres.)

I'm trying to constrain a column to have only one row be set to true out of all columns that have the same value in a different column

so if I have three rows

package_id | default 
13         | true
13         | true
9          | true
``` the database would complain that the second row up there violates the constraint because two rows are true for package_id 13.

[pure sleet]

idk, this doesn't sound like something you would try to achieve with constraints but rather a function or procedure

just my personal take

[inner sentinel]

ah

how would I use those, then?

[pure sleet]

define a function/procedure that checks whether a true value already exists in the database.

before adding a new value that is

or to basically just do what you want above. I'm not really sure what you want to achieve but yeah. hopefully you get the idea

[inner sentinel]

I can define that in the database?

[pure sleet]

yes, postgres supports procedures and functions

[torn sphinx]

\

[inner sentinel]

okay i went looking and looking and still have no idea what i'm looking for

i'll try to explain my problem better this time

i have a list of sources in one table, that correspond to specific packages, which are in a separate table

there is a many-to-one relationship from the sources to the package

each package can have one default source

[grim vault]

You could make a unique index with a where clause:

CREATE UNIQUE INDEX check_default_source ON sourcetablename(source_id, package_id) WHERE default;

[inner sentinel]

what is "default" at the end of the query?

[grim vault]

The columnname of your true/false column in the source table.

WHERE default is just a shortcut for WHERE default = true because default is allready true or false.

[inner sentinel]

ah so multiple can be false but only one can be true?

[grim vault]

That's the idea, yes.

[inner sentinel]

my current solution was a unique constraint with both package_id and default and I made default nullable

I'm using sqlalchemy.orm btw

do you know how I convert what you provided into the orm, what the important keywords are?

[grim vault]

No sorry, never used an ORM myself.

[inner sentinel]

I think I got it but I'll have to wait until tomorrow to test

[compact grove]

Hi, im using pandas and i have a dataset whit NaN and normal numeric values, but i only want to drop the rows that are full with the numeric values and leave the rows with NaN
Any ideas?

[fleet pebble]

You can use a negating statement. ~ takes the negation of the given condition.
e.g .new_df = df[~df['col'].isna()]

[compact grove]

thank you

[paper flower]

Yeah, it's one of the solutions as far as I can tell, not sure if SQL provides any other way to do that though 🤔
It's not really related to sqlalchemy though
As an alternative solution separate table could be created to represent that "true" record and any related data

[inner sentinel]

the issue became I figured out a way but couldn't get sqlalchemy to make the constraint

I finally found a constraint tho

[paper flower]

You can add __table_args__ field to your model and put UniqueConstraint in it

[flat marsh]

whys this not work

pair_genderf_q29 = (data['q29'] == 2) and (data['gender'] == 1).sum()

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

[inner sentinel]

yeah i finally figured it out

    __table_args__ = (
        Index(
            "ix_doc_source_defaults",
            "package_id",
            "default",
            text("default = true"),
            unique=True,
            postgresql_where=Column("default"),
        ),
    )

[vapid tangle]

Does anyone know how I would implement the sleep time? Error currently thrown is: "Dataframe" object is not callable

`letters = list(string.ascii_lowercase)
playerlist_2023 = []
for letter in letters:
playerlist = pd.read_html("https://www.basketball-reference.com/players/{}".format(letter))[0]
time.sleep(5)

playerlist_2023.append(playerlist)`

[flat marsh]

I figured it out it was the ‘and’ I just literally replaced it with & and it worked

[inner sentinel]

oh sorry ;-;

i replied to the wrong message with that

[flat marsh]

Oh lol

[inner sentinel]

what are you trying to do?

[flat marsh]

Project for a class

had to take this survey that the cdc does called the YBRSS
(Youth Behavior Risk Surveillance Survey). We had to select a few questions and compare and plot the data from those questions

[formal crater]

Hi everyone,

I am sorry if this is the wrong thread but after 1 day python bot hasn't been that useful on this purpose.
I developed a library that acts as mapper between python and GraphQL languages for clients (it transforms python classes in GraphQL objects and operations and viceversa).
Please let me know what you think about it:

repo: https://github.com/dapalex/py-graphql-mapper/

[opaque wave]

hey can someone explain the purpose of this query?

SELECT user, country, territory from table1
UNION distinct
SELECT user, country, territory from table1

does it simply remove duplicates?

[thin smelt]

some help please ?

[eager tide]

in query fetching last row value each time. why ?

rs = stmt.executeQuery("select category,max_amount from pr_po_category_master where cat_status = 1 order by category");

in query two values passing to save page, One passsing from name and other value pass from variable through name

[brazen charm]

well the letter D is semantically larger than A so when you order by category you're going to get rows in the order of: D, D, C, B, A

by default order by is descending so biggest value first.

[tribal light]

I thought that the default order is ascending which gives the results shown in the picture...

[tribal light]

From a quick look your statement looks ok, can you use a MariaDB GUI, something like phpmyadmin? IMO there are always small details that might go wrong and the GUI saves you some time from those small mistakes, by performing those operations semi-automatically

[eager tide]

for any entry it getting last value with cat_status = 1

[tribal light]

with last value you mean that with the largest cat_id?

[rare monolith]

Does anyone know a website for quick generating a hundred name, email, age, phone numbers etc for an SQL table

So we don’t have to push our imagination into limits to generate 100 fake person

[burnt turret]

!pypi Faker

[delicate field]

Faker v15.3.4

Faker is a Python package that generates fake data for you.

[burnt turret]

not a website but does the job

[torn sphinx]

Yes only the count value, unless you specify that you want to overwrite the primary key value.

[torn sphinx]

I just asked ChatGPT to help me convert my 20GB sqlite database to Postgres, and not only it was basically spot on but now my query times are 2 orders of magnitude shorter lel

Simply amazing

[fleet pebble]

Wait until it starts to take your job.

[true beacon]

!e ```py
class A:
def str(self):
return 'b'

print(A.str())```

[delicate field]

@true beacon :white_check_mark: Your 3.11 eval job has completed with return code 0.

<class '__main__.A'>

[true beacon]

!e ```py
class A:
def str(self):
return 'b'

print(A.str(A()))```

[delicate field]

@true beacon :white_check_mark: Your 3.11 eval job has completed with return code 0.

b

[lean plover]

I am getting this error: sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied. ...from this code: ```py
data = sqlite_conn.execute(f"SELECT * FROM {tablename} WHERE archivist_id = ?", str(10))

[waxen finch]

for positional placeholders the execute method expects a sequence of parameters, e.g. a list/tuple py conn.execute('INSERT INTO table VALUES (?)', ("10",))

if you dont put your string inside a sequence, it treats the string as if it were a sequence of characters, hence why it thought "10" was 2 bindings

[torn sphinx]

from utils.database import Database anyone know why this isnt being imported

[pure sleet]

what is the error saying

[torn sphinx]

@delicate field

[chrome loom]

hi

I need to pass a list of data in a POST request.

My API is getting
{ "documents":"888888", "type":1, "code":2 }

I want to send a list of data:

[ { "documents":"888888", "type":1, "code":2 }, { "documents":"1111111", "type":2, "code":5 }, { "documents":"9999999", "type":1, "code":4 }, ]

Each "block" of this data is a list in the database.

I'm using SQL Alchemy and FastAPI

Does anyone know what I need to change to do this?

[tepid night]

#1035199133436354600

[brazen blaze]

hiii

could someone help me with this please

[mystic pendant]

Do I need to keep the connection persistent or every time I query I reconnect the database?

class DB:

    def __init__(self):
        self.con = mysql.connector.connect(host=os.getenv("HOST"),
                                           user=os.getenv("USER"),
                                           password=os.getenv("PASSWORD"),
                                           database=os.getenv("DATABASE"))

    def select(self, sql: str, val: list):
        cc = self.con.cursor()
        cc.execute(sql, val)
        result = cc.fetchone()
        return list(result) if bool(result) else None

[solid tendon]

is there any sql server script that exports table to a flat file?

[clear spade]

CREATE TABLE dbo.tblUsers
(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
UserID AS 'UID' + RIGHT('00000000' + CAST(ID AS VARCHAR(8)), 8) PERSISTED,
.... your other columns here....
)

how to achieve this using sqlalchemy or django orm?

UserID is computed value something like UID000000001 if ID is 1

[drowsy agate]

Is it necessary to use the with statement when using Pandas read_sql (with a str connection) for the connection to automatically close after the query is done?

[real timber]

When alembic if i have several op.execute calls, is each of those a transaction - or are they all considered part of the same transaction ?

[fringe trench]

I have problem with creating db file for flask. Can someone DM me for information please...

[compact marlin]

 async def on_ready(self):
        print("Bot is up and ready to go!")
        setattr(client, "db", aiosqlite.connect("level.db"))
        async with client.db.cursor() as cursor:
          await cursor.execute("CREATE TABLE IF NOT EXISTS levels (level INTEGER, xp INTEGER, user INTEGER, guild INTEGER)")
``` I get this error: ```  File "/home/runner/level-bot/venv/lib/python3.8/site-packages/aiosqlite/core.py", line 67, in _conn
    raise ValueError("no active connection")
ValueError: no active connection```

[grim vault]

I guess you need to await the connect call, it's async after all. And why not do it like:

    client.db = await aiosqlite.connect("level.db")

[alpine cairn]

it this a right place to ask for help?

what does this mean? {:02d}:{:02d}

[thin python]

hello can anyone help me with an erd and normalisation?

[abstract flower]

hi , can someone help me with this problem ?

[jagged swallow]

@compact marlinasync def on_ready(self):
print("Bot is up and ready to go!")
setattr(self, "db", aiosqlite.connect("level.db"))
async with self.db.cursor() as cursor:
await cursor.execute("CREATE TABLE IF NOT EXISTS levels (level INTEGER, xp INTEGER, user INTEGER, guild INTEGER)")

There were a few errors in the original code. First, the on_ready method is a member of a class, so it should be using self instead of client to access instance attributes. Second, the aiosqlite.connect method should be called asynchronously using await, not directly. Finally, the CREATE TABLE statement should be indented correctly so that it is executed within the async with block.

[civic quiver]

are database tables similar to hash maps/dicts

[crimson tangle]

?

[stark gust]

if I have a db table where I want to set two boolean values which are mutually exclusive (never both True but both can be False), does it make more sense to have two separate columns for these values, or would it make more sense to have a single column that takes on an enum.ONE or enum.TWO or None value?

in terms of query speed would either be considerably faster?

[torn sphinx]

@bot.event
async def on_member_join(member):
  async with aiosqlite.connect("main.db") as db:
    async with db.cursor() as cursor:
      await cursor.execute(f"SELECT welcomechannel FROM users WHERE guild = {member.guild.id}")
      result = await cursor.fetchone()
      if result == None:
        return
      else:
        await cursor.execute(f"SELECT welcomemessage FROM users WHERE guild = {member.guild.id}")
        result1 = await cursor.fetchone()
        members = len(list(member.guild.members))
        mention = member.mention
        user = member.name
        guild = member.guild
        embed = discord.Embed(description=str(result1)[0].format(members=members, mention=mention, user=user, guild=guild))
        embed.set_author(name=f"{member.name}")
        channel = bot.get_channel(int(result[0]))
        await channel.send(embed=embed)

anyone know why this isnt working?

[fading patrol]

Pretty sure you want two separate bool columns. It's just so much cleaner, and I have to imagine that any gain in query speed (if any) will be negligible in most circumstances.

[stark gust]

cleaner in what way?

[fading patrol]

Everyone knows what "True" means, nobody knows what "enum.ONE" if they haven't studied your code/documentation.

I'm not that experienced, but I've never see things done this way

[stark gust]

I was only using ONE and TWO as examples, they are actually VERIFIED and REMOVED, which would be perfectly clear to someone who works with this data

[grim vault]

Looks like this: str(result1)[0] should be str(result1[0]) but I don't think you need the str() at all.
And please don't use f-strings for sql statements.

[stark gust]

why are f-strings bad for sql statements?

[torn sphinx]

raises errors

and can delete the db

[delicate field]

Did you mean ...

» f-strings
» fstrings

[stark gust]

can you explain how? I don't see why that would happen

[grim vault]

!sql

[delicate field]

SQL & f-strings
Don't use f-strings (f"") or other forms of "string interpolation" (%, +, .format) to inject data into a SQL query. It is an endless source of bugs and syntax errors. Additionally, in user-facing applications, it presents a major security risk via SQL injection.

Your database library should support "query parameters". A query parameter is a placeholder that you put in the SQL query. When the query is executed, you provide data to the database library, and the library inserts the data into the query for you, safely.

For example, the sqlite3 package supports using ? as a placeholder:

query = "SELECT * FROM stocks WHERE symbol = ?;"
params = ("RHAT",)
db.execute(query, params)

Note: Different database libraries support different placeholder styles, e.g. %s and $1. Consult your library's documentation for details.

See Also
• Python sqlite3 docs - How to use placeholders to bind values in SQL queries
• PEP-249 - A specification of how database libraries in Python should work

[stark gust]

makes sense. thanks for the info

[grim vault]

Also, one select is enough:

@bot.event
async def on_member_join(member):
  async with aiosqlite.connect("main.db") as db:
    async with db.cursor() as cursor:
      await cursor.execute("SELECT welcomechannel, welcomemessage FROM users WHERE guild = ?", (member.guild.id,))
      result = await cursor.fetchone()
      if result is None:
        return
      welcome_channel, welcome_message = result
      ...

[sharp token]

{'_id': 'Kingdom of Mospelia', 'cities': {'rich': 'Newvault', 'capital': 'Oxbrookward', 'trade_port': 'Stillows', 'religion': 'Shagon', 'generic1': 'Dustpoint', 'generic2': 'Clifshiel', 'generic3': 'Cloudhol'}}

I have this dictionary (mongoDB) and i want to get the value 'Newvault' but nothing i do seems to work, .get() doesn't do the trick, and trying to debug things using .values() just confuses me more
any help?

[stark gust]

you want that value from inside the dictionary or the document that has this value from inside mongodb?

mydict["cities"]["rich"]

this returns the value if the dict is in a variable called mydict

[sharp token]

documetn

document*

[stark gust]

then

mydb.mycollection.find({"cities.rich": "Newvault"})

[sharp token]

yeah but it should return the value of rich no matter the value

[stark gust]

so you want all the documents' values of rich where they exist?

[sharp token]

no like it's one document, and i want to get the value of rich

but we don't know the value yet, that's why we are getting it

[stark gust]

that was what I gave you the first time

[sharp token]

oh

[stark gust]

mydict = {'_id': 'Kingdom of Mospelia', 'cities': {'rich': 'Newvault', 'capital': 'Oxbrookward', 'trade_port': 'Stillows', 'religion': 'Shagon', 'generic1': 'Dustpoint', 'generic2': 'Clifshiel', 'generic3': 'Cloudhol'}}
print(mydict["cities"]["rich"])
>>> 'Newvault'

[sharp token]

        locations_db = client.get_database("RPG")
        locations_coll = locations_db.get_collection("locations")
        rpg_locations = locations_coll.find_one({"_id": "Kingdom of Mospelia"})
        print(rpg_locations)
        print(rpg_locations.get('rich'))

this is the code i made
i want the last print to output "Newvault"

I'm a little tired, so i'm sorry if i come off a little vague

[stark gust]

you only need to use get() if you're unsure whether the key exists or not, otherwise you can just reference it with brackets [] like I did

[mint wharf]

Need some help quick. I just set up a PostgreSQL server on my machine, but not matter what I do, it doesn't seem to take the password that I gave it when I first set it up. It worked when accessing it on pgAdmin4, but it doesn't work in psql on the command prompt and it doesn't work when I'm trying to connect to it in a Python script
I keep getting "password authentication failed for user"

[stark gust]

you don't seem to understand the structure of your document, let me help you here, give me a bit

[sharp token]

okidoki

[stark gust]

{
   '_id': 'Kingdom of Mospelia',
   'cities': {
      'rich': 'Newvault',
      'capital': 'Oxbrookward',
      'trade_port': 'Stillows',
      'religion': 'Shagon',
      'generic1': 'Dustpoint',
      'generic2': 'Clifshiel',
      'generic3': 'Cloudhol'
   }
}

if you look at it, you'll see that "rich" is inside the "cities" variable as a nested dictionary

so you have to first reference the first (outer) key, then "rich", the inner key

[sharp token]

ah yeah

that makes sense

[stark gust]

how are you trying to give it the password?

as far as I understand you gave a password during setup to access the postgres stuff through pgAdmin, and it asks for that password when you open that program

that password is bound to a user called postgres

so unless you're trying to connect as the user postgres it won't work

how does one store a BC date in SQL?
is there a setting in the Date() type or something?

[sharp token]

thx for the help!

[stark gust]

https://www.postgresql.org/docs/current/functions-formatting.html
someone please tell me an example of a proper formatted value of type Date that has a BC indicator in it... I can't wrap my head around this and the damn thing doesn't have a freaking example written out

like where do I put it? for example, that would be good to know

or how do I save a BC year/month/day date otherwise?

[grim vault]

Just use a negative year.

[mint wharf]

I tried psql -U postgre and tried the password I used when I first set postgresql up. It didn't work

[stark gust]

if I pass a date as "1800-01-30" then I can't do "-1800-01-30", it throws an error
that's why I would like to know how is it formatted properly... can you help out with that?

[grim vault]

Hm, select to_timestamp('1800-01-30 BC', 'YYYY-MM-DD BC') -> -001799-01-30T00:00:00.000Z

[stark gust]

1800 BC becomes -1799... interesting

[stark gust]

thank you!

[mint wharf]

I was able to get psql running inside of pgAdmin. Is it possible to change the passwords of postgresql users?

MOTHER F$2837:72&&:7;7373);772$37:83jehduwu see idaijddj

It's spelled postgres, not postgre, as I've been trying.

[stark gust]

I thought you only made that typo here in the chat 😄

[grim vault]

That's because there is no year zero, so 1 BC becomes 0.

[stark gust]

if I only know the year, how do I pass it?
to_timestamp('620 BC', 'YYYY BC') is apparently not it

setting it to '620-00-00 BC' doesn't work either

oh I get the issue

dammit

the missing month and day wasn't the problem, it was just 3 digit long year instead of 4 digit 🤦‍♂️

[agile flame]

I'm trying to graphically organize/document my database ahead of time before I start winging it.
I was going to do something with dictionaries of dictionaries and cartesian products to get all the possible (but not plausible) combinations of inventory

Is this entity relationship stuff the way to go?

I was winging it with something like this

[stark gust]

you're creating your own custom database?

[agile flame]

I'm not looking to be hand-held but just pointed in the right direction

[agile flame]

We currently do not have one, and I am creating a web-based app via streamlit to query inventory quantity of components and use a backend algo I developed to decide which components to use

if there is boilerplate or available choices out there, I'm all for not reinventing the wheel