databases

[coral wasp]

and are they being used? Did you notice a performance difference?

[covert bane]

They are used in the select query. But not in the count

[coral wasp]

The thing is; your sql had ‘upper’ which renders the index useless (im quite sure)

[covert bane]

The query was autogenerated by the ORM. Let me check without UPPER

I'd still have to use ILIKe though

[coral wasp]

Try with a case with a simple like, just to compare performance

Like, start with something the index works cleanly with: column and simple like and everything already in right case

** I don’t know specifically whether trigrams are case sensitive or insensitive, worth looking up. many things in most dbs are case insensitive, but it varies when you get to text indexing

[covert bane]

The query is obviously faster if I remove UPPER and use LIKE. But it is still using full sequential scan

[coral wasp]

The full sequential may not be avoidable, given the low selectivity of your id joins.

[covert bane]

So I found an SO thread that says to index on UPPER value of the column

Let me try that and see

[coral wasp]

Yah, do you understand why doing upper in the query itself is a bad idea?

(With regards to indexing?)

[covert bane]

Yup. The index was case sensitive or wasn't in upper case

[coral wasp]

No…

Well that

And, it prevents an index from being used. Like, x is indexed, f(x) isn’t indexed

Make sense?

[covert bane]

So i'd have to index f(x)

[coral wasp]

Yah, more or less. There’s exceptions because optimizers can deal with simple cases (like x+1)

[covert bane]

Did not work. Still using sequential scan. still took 12 seconds to execute

CREATE INDEX store_name_ln_gin_idx
    ON stores_store USING gin (UPPER(name::TEXT) gin_trgm_ops)

[grim vault]

a ... LIKE '%foo%' can't use a normal index and will result in an sequential scan of the table.

[covert bane]

What's the soluntion to this?

[grim vault]

I never uses postgres but I would guess you have other operators to use a trigram index (not LIKE).

[coral wasp]

I would decompose the problem first to figure out the right indexing strategy for the single table such that the index is leveraged. Experiment with gist or gin trigrams, on a single table

Like and Ilike support the trigram indices in postgres

[covert bane]

The actual table with the indices isn't big enough

like 1000 rows

[coral wasp]

I’m saying: decompose the problem to a simple case, then expand to the full case.

[covert bane]

there is another table that has 12 million rows that the indexed table is joined to

[coral wasp]

Don’t worry about that part yet; start with; will a simple query use my index?

And try both gin and gist indices

(All I’m saying is: scientific method)

[covert bane]

So I tried with a single join and it used a bitmap index scan

SELECT COUNT(1)
FROM a
         LEFT OUTER JOIN b ON (a.b_id = b.id)
WHERE UPPER(b.name::TEXT) LIKE UPPER('%{{str1}}%')
  AND UPPER(b.name::TEXT) LIKE UPPER('%{{str2}}%')

Completed in 30 ms

Joined another table to it and it used full scan for table b and c.

and took 8 seconds

SELECT COUNT(1)
FROM a
         LEFT OUTER JOIN b ON (a.b_id = b.id)
         LEFT OUTER JOIN c
                         ON (b.c_id = c.id)
WHERE (UPPER(b.name::TEXT) LIKE UPPER('%{{str1}}%') OR
       UPPER(c.name::TEXT) LIKE UPPER('%{{str1}}%'))
  AND (UPPER(b.name::TEXT) LIKE UPPER('%{{str2}}%') OR
       UPPER(c.name::TEXT) LIKE UPPER('%{{str2}}%'));

[coral wasp]

Get rid of the uppers, do it with a simple b.name, without the cast, without the upper, with a index on b.name

[covert bane]

But it needs to be case insensitive

[coral wasp]

Just start simple, let’s measure that

[grim vault]

This are left outer joins and the count will always count all rows of table a no matter what?

[coral wasp]

Get the simple case to work first with a single table select.

[covert bane]

It's faster without upper. But still took 1.6 seconds

[coral wasp]

It’s not about timing, it’s about it using the index

So, what we’re trying to do is construct an index and filter such that the indices are useful/used

[covert bane]

The index use is the same as before. Index only scan was used for table a. but full scan for b and c

[coral wasp]

Is a the big table?

[covert bane]

Yup

a = 12 million+ rows
b and c = 1000+ each

[coral wasp]

And how long with no joins ?

[covert bane]

400ms. Index only scan

[coral wasp]

Which index?

[covert bane]

Probably the normal index on id.

[coral wasp]

Secondly, the other tables may be too small for postgres to "decide" to use the index... there's a cost tradeoff, and sometimes a full table scan is good

[covert bane]

I understand that

[coral wasp]

what's the selectivity of the query on a? Are you expecting a few hundred rows out of 12M?

[covert bane]

Nope. < 20

[coral wasp]

Ok, so let's start there: Will a gin trigram index speed up a single table query?

[covert bane]

There's nothing to index with gin index on table A

[coral wasp]

Oh, I thought a was also being filtered, gotcha

[covert bane]

We're not filtering by any column on table A

[coral wasp]

OR UPPER(a.name::TEXT) LIKE UPPER('%foo%')

Is what you posted

[covert bane]

That was just an example. I was looking for something generic that works for all count queries

Like how would one optimize a count query generally speaking

[coral wasp]

The same as any other query

You make sure you have good indices, make sure they're used, sometimes give some hints or materialize where necessary, etc

Like, you might be able to use a materialized cte to, say, get the ids you're interested in.

And, of course, minimizing or avoiding operations (functions) that are computed for each row.

[covert bane]

Isn't UPPER(a.name::TEXT) LIKE UPPER('%foo%') same as a.name::TEXT ILIKE '%foo%'

And probably a.name::TEXT ICONTAINS 'foo'

[coral wasp]

Depends what you're asking. Are you asking if they return the same result? They're certainly executed differently

Secondly, try to remove the use of any functions like upper() and casts like ::text. Use the right data types in the tables.

I'm just speaking from a performance optimization perspective: the KISS principle.

[covert bane]

Comparision with UPPER is faster than ILIKE though

[coral wasp]

Can you show the current query and table schema (particularly the indices)?

(upper is probably faster than ilike with no index, that's not terribly surprising... but if the index is selected, then I'd expect ilike with a trigram index)

But, my point about removing upper is about starting from simple. Start with data / foo that already match... I'm not saying go from upper to ilike, I was saying go from upper to a simple like. If you can reduce the problem to a simple measurable query: select count(*) from a join b where a.id=b.id and b.name like '%foo%', you can then use that as a baseline as you expand the query to meet your objective.

Like, start with a basic case like this, measure it, and then expand to your case: ```create table a as
select * from generate_series(1, 12000000) t(id);

create table b as
select id, id::text as name from generate_series(1, 1000) t(id);

select * from a join b on a.id=b.id
where b.name like '%1%'```

[queen rose]

what are some good databases for storing geographic data (addresses)?

[coral wasp]

I use https://duckdb.org/2023/04/28/spatial.html, more info: https://tech.marksblogg.com/duckdb-geospatial-gis.html

(there's also clickhouse, postgis, etc, but I haven't used... clickhouse is pretty common tho)

[queen rose]

I'm not too familiar with duckdb, is it an in-memory format like Apache Arrow that reads and writes CSV, paruqet, etc.
or is it a physical database that stores the data on the HDD like PostgreSQL, MySQL, etc. ?

[coral wasp]

duckdb is more similar to sqlite: it can be fully in memory or using a file store.

it's more something you'd use with arrow data, as opposed to "instead of"

[queen rose]

how would you use it with arrow data?

[queen rose]

so something hybrid I guess

[coral wasp]

Edit: See example here: https://duckdb.org/docs/guides/python/sql_on_arrow.html. I dunno, let's say you have arrow tables t1 and t2, you could write: select * from t1 join t2 on t1.id=t2.id. Obviously you'd write something more complex, but anything you'd want a sql transformation on... particularly vectorizable operations.

Just out that blog on my first reply, they walk through the usage.

But, you have lots of options... but if I'm just doing something on-host / in-memory, I use duckdb. For bigger stuff, I use snowflake.

[queen rose]

I see, its basically SQLite but it can read and write from CSV, Parquet better

[coral wasp]

The pedant in me is compelled to say: it's a columnar vectorizable engine (both words are very important when talking about olap), with similar usage as SQLite.

So, you wouldn't use it for transactional stuff (where sqlite is more appropriate). But, this is a general statement about any OLAP / columnar database vs more traditional RDBMS's that are row-oriented.

So, as a developer, it looks similar... but what it does / how it works is very diff.

[queen rose]

arent all the big databases like Postgres and MySQL vectorized as well? and SQLite3?

I wonder how it differs in terms of performance

[coral wasp]

This is probably a long lecture about the new breed of olap-oriented engines, but: there's huge differences for certain workloads.

Snowflake/clickhouse/duckdb are going to crush the others for certain things, and vice versa. (sweeping, useless generalization, I know)

So, I didn't actually ask what your use case was/etc. You'll have to form your own opinion. Just something worth researching.

[queen rose]

But what are the main strong points of these newer and more modern databases, what kind of queries do they excel at compared to your classical DBMS?

[queen rose]

right now I dont know, I'm just brainstorming

[coral wasp]

Are you familiar with numpy and vectorized operations?

[queen rose]

yeah, more or less

[coral wasp]

Yah, so think of them as being really good at that stuff

There's some great academic papers on this topic, if you're so inclined.

[queen rose]

If there are some you can send I'd apprecciate it

[coral wasp]

Disclaimer: I have no affiliation with the project, but I use it professionally, so it sounds like I'm selling but I'm just a huge fan.

There's a bunch of (non-duckdb) papers at https://duckdb.org/why_duckdb.html#standing-on-the-shoulders-of-giants. MonetDB, the first link, I think was the first database to use this approach.... which many databases have since adopted.

[hollow oar]

1. address like "10 Downing St, London SW1A 2AA" is not really geographic data. are you looking to store address or lat/lng or other more complicated geometries?
2. please provide more context, storing for what purpose? what's the access pattern? what's the wirte pattern?

[queen rose]

I get it bro, there are quite a few people here that like duckdb, I guess it's kind of like Rust, everybody talking about the new kid in the block. I personally think its great because this way people get introduced to these great technologies (that's how I started learning Rust and even Pyarrow, from this server!)

[queen rose]

the data contains both the adresses as strings, and lng/lat data.
as for what queries I'm going to do? I have no idea. at the moment I just need to create the DB to have the data more organized, and then I'll see how were going to use it.
whats for sure is that I'm going to query it by lng/lat values, and also other properties of the address (City, Country, etc.)

[hollow oar]

what's the wirte pattern?
what about this? is this known atm?

[queen rose]

about the write? I'm gonna dump the data all ot once, I doubt there are gonna be any more INSERTs or UPDATEs in the near future

[hollow oar]

are you doing data analysis or something else?
the generic answer is just to stick it into postgres+postgis extension without knowing anything else.

[last atlas]

Hey guys i am writing an API and i have 3 different types of users -> user, manager, admin
But i am storing themm all in the same Users table in the database

I have a route /user/create to register a new user
i was wondering if i should make a new route like /manager/create to register a user as a manager
or just pass in the role as a role query parameter
oh wait that what i am doing rn
i forgot
damn i gotta sleep

anyways the users with manager role can have other users under them, i was wonder if i should add the CRUD operations for managing the users under manager type users too ..? coz there is no way to set them rn

[river echo]

A small DuckDB project based on a LinkedIn post from a fellow Analyst.

I tried to paste the pdf, but that's not allowed here 😅. I'd rather post the .html nbconvert, but that's out, too. Here's my LinkedIn post with the .pdf preview:

https://www.linkedin.com/posts/justin-cribbs-816a8334_trading-calendar-days-activity-7076106645063282688-3IrJ?utm_source=share&utm_medium=member_android

[fading patrol]

!rules 4

[delicate field]

Rules

4. Use English to the best of your ability. Be polite if someone speaks English imperfectly.

[queen rose]

you brobaly want to ask at #software-architecture

[queen rose]

nah, just data engineer stuff, ETL; load the data to the DB, and what they do with it is none of my business 🙂

[hollow oar]

Well my advice is to start asking what are they gonna do with it.
Working in this siloed fashion is just going to lower your org's velocity of delivering value, just my two cents as someone who has worked as data engineer and data scientist, feel free to ignore.

[untold dust]

is it possible to make SQL match as many OR operators as possible?
e.g i have a query

SELECT * FROM cars
WHERE color='white' OR price < 10000 OR wheels = 4;

and if theres a white car under 10k with 4 wheels then it will return the appropriate data, but if there isnt it tries to match the closest car with those statements

[obtuse magnet]

closest meaning?

[untold dust]

e.g. it finds a car thats white and has 4 wheels in this case

[obtuse magnet]

yeah it will

thats what OR means

oh

u mean u want to set the minimum how many of the OR statements u want to match?

[untold dust]

no but i want it to match the most amount of or statements

[obtuse magnet]

so no just white cars?

[untold dust]

yeah

[obtuse magnet]

not sure if thats possible hahah

maybe an accompanying case statement, sum that up and order by?

[untold dust]

ah ok tysm

[hexed estuary]

do an order by <some measure of distance> limit 1.

e.g. ORDER BY ABS(price-10000) + ABS(wheels-4)*1000 + (color != "white")*10000

[mellow pumice]

How can I take a table that I made and decompose it to other tables in PHPmyadmin?

[hardy gust]

Not so sure which DC group/channel should I post this question... Has anyone tried with Bitnami PostgreSQL-HA helm chart? I tried to use repmgrPassfile instead of password but got the following error

postgresql-repmgr 01:37:37.71 INFO  ==> ** Starting repmgrd **
[2023-06-16 01:37:37] [NOTICE] repmgrd (repmgrd 5.3.3) starting up
[2023-06-16 01:37:37] [INFO] connecting to database "user=repmgr passfile=/etc/secrets/.pgpass host=postgres-postgresql-ha-postgresql-0.postgres-postgresql-ha-postgresql-headless.test.svc.cluster.local dbname=repmgr port=5432 connect_timeout=5"
[2023-06-16 01:37:37] [DEBUG] connecting to: "user=repmgr passfile=/etc/secrets/.pgpass connect_timeout=5 dbname=repmgr host=postgres-postgresql-ha-postgresql-0.postgres-postgresql-ha-postgresql-headless.test.svc.cluster.local port=5432 fallback_application_name=repmgr options=-csearch_path="
[2023-06-16 01:37:37] [ERROR] repmgr extension not found on this node
[2023-06-16 01:37:37] [DETAIL] repmgr extension is available but not installed in database "repmgr"
[2023-06-16 01:37:37] [HINT] check that this node is part of a repmgr cluster

[unkempt prism]

I've found simular issues:

https://github.com/bitnami/charts/issues/1880

https://github.com/EnterpriseDB/repmgr/issues/554

They might be different circumstanes though can't tell from this info. Best to share in whatever of these repo's you believe is best.

[queen rose]

what do you guys do with date that only have the year and month (YYYY-MM), I tend to separate the year and month in two different columns, I guess the other option is to add a "fake" day and convert the string YYYY-MM-DD to a datetime (for populating the DB).
what approach do you guys have for this problem?

[unkempt prism]

Depends. Though if the need to calculate seamlessly storing it as a daterange type that covers the period makes it easy to model accurately.

[queen rose]

how do you store it as a daterange?

is that a data type in SQL databases?

oe even NoSQL DBs

[unkempt prism]

postgres does have range types. I'm unsure if they are sql standard.

[paper flower]

I usually just store a date when I only need a year and a month

e.g. 2023-06-01

[shy dragon]

How to avoid Object is already attached to session errors in fastapi + sqlalchemy.
For example:
async def attendance_of_last_seven_days(user: User = Depends(get_current_user), db: AsyncSession = Depends(get_db)):

in fastapi db sessions can be used by using it as dependency while defining the route, and here my 'get_current_user' dependency also need a db session to to retrieve user.
That means the user object is already associated with a session and in my view function i am using another db session. thus if for some operation if i want to use the user object with other objects which was retrieved by the db session from the dependency it throws exceptions.

i know that in my 'get_current_user' dependency i could separate the session from user object and return it and add new session with it inside the view function. i dont want this cause it seems ugly to me .

or should i use same session object which is associated with user in every place of the view function, instead of defining another session as dependency in the route?

what is the recomended way ?

[stable sentinel]

Hi all, can I get assistance on any free platform to learn DBMS? Using postgre

[paper flower]

Use single session per request

[gaunt kernel]

why does batching make more sense vs single calls to a database when you are doing a large sql database migration?

[paper flower]

What do you mean by migration here?

Usually sending large query would be better

[shy dragon]

yes i know that, but i dont know if i am doing it in right way.
i cant use just send a db session in get_current_user dependecy.

i was doing this instead.

async def details_of_department(department_id: uuid.UUID, user_id: uuid.UUID = Depends(get_current_user), db: async_session = Depends(get_db)): user = await get_user_from_user_id(user_id, db) if not user: return JSONResponse(status_code=status.HTTP_403_FORBIDDEN, content={'errors': 'Not Authenticated'})

but here these two lines are getting repeated for every view funvtions which i dont like
if not user: return JSONResponse(status_code=status.HTTP_403_FORBIDDEN, content={'errors': 'Not Authenticated'})

how do you handle such things

[paper flower]

Use a dependency to authenticate the user

[shy dragon]

can you please show some code example

[shy dragon]

i was doing exactly that

[paper flower]

You're usign a dependency already to get user_id, you could return user instead of it's id

So you won't have code duplication

[shy dragon]

but if return user from dependency it will be have session associated with it, and it will get mixed with the session of the views dependency , that was my issue

[paper flower]

Your app should use one session per request

Can you share how you're using your session?

[shy dragon]

of ocourse wait i am showing codes

i this as dependency to get a db session
`from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlalchemy.orm import declarative_base

from .config import configs

SQLALCHEMY_DATABASE_URL = configs['DATABASE_URL']

engine = create_async_engine(SQLALCHEMY_DATABASE_URL, future=True)
async_session = async_sessionmaker(engine, class_=AsyncSession, expire_on_commit=False, future=True)

Base = declarative_base()

async def get_db():
async with async_session() as session:
try:
yield session
finally:
await session.close()`

for my another code
i was getting he user object and returning object like you said . like this
async def get_or_create_user(user_id: UUID) -> User: async with async_session() as db:

here i was using another session to get user object. cause i cant pass db session in a dependeny as far as i know. so i had to create new session any way

[paper flower]

You can

async def get_or_create_user(user_id: UUID, session: Annotated[AsyncSession, Depends(get_db)]) -> User:
    ...

[shy dragon]

but doesnt it create just another session?

[paper flower]

no

[shy dragon]

and my view function also has has get_db dependency

[paper flower]

All dependies are cached, so everything gets the same session

[shy dragon]

wow

mind blown

i am so stupid

i didnt know

i was writting messy codes 😦

@paper flower idk how to thank you !! i am so grateful

do you have(know) any open source projects that uses fastapi which i can use as guide, idk i feel terrible now , maybe i am doing others things wrong too

[paper flower]

Maybe https://gitlab.com/ThirVondukr/fastapi-cookiecutter
And https://gitlab.com/manga-microservices/identity
But first one is just a template that I use, it doesn't have authentication 🤔

[shy dragon]

okie thank you , so much

[little girder]

async def db():
    async with aiosqlite.connect("mydb.db") as db:
        data = await db.execute("SELECT list FROM members").fetchone()

AttributeError: 'Result' object has no attribute 'fetchone'

help?

[shy dragon]

according to this
https://stackoverflow.com/questions/71729956/aiosqlite-result-object-has-no-attribue-execute

this should help :
cursor = await db.execute("SELECT list FROM members") data = await cursor.fetchone()

[little girder]

i don't get it but thanks it works now

[shy dragon]

me neither 😆 , yw

[pastel wren]

just realized that i made a mistake using the sql alchemy orm queries but i don't exactly know why. Is the cardDb class being ignored and sqlalchemy trusting me to know what i'm doing when i call magicInfo class methods in the body of the query?

      result = cardSession.query(cardDb) \
      .with_entities(magicInfo.id,magicInfo.images,magicInfo.name,magicInfo.types,magicInfo.color_id,magicInfo.rarity) \
      .filter(magicInfo.name.like('%'+name+'%')).all() 

[paper flower]

It's essentially a query builder, it won't validate what you're doing 🤔

[sharp crystal]

Hi all, this may me an extremally vague question but I can't find any objective data about this. I'm now approaching my next simple personal project in Python: a scraper from a website stores data iun a database and api to share it. However I'm wondering what databse to choose and here comes my question:

I saw multiple people meme about MongoDB being a choice mistake. For example, recently ThePrimeagen gave a similar comment. Why is/isn't this true?

[coral wasp]

Mongo might be appropriate for storing HTML blobs/etc. Kinda depends on what kind of data you're storing.

Like, are you parsing the data out (into tables), or storing blocks of json/html

[sharp crystal]

storing scraped data already processed into json files

[coral wasp]

So, in a nutshell, you have a bunch of JSON you want to put in a database?

[fiery thunder]

i have a simple question:

if im trying to put a .csv spreadsheet into a pandas table:

filepath = Path("top10.csv")

scoreboard = pandas.read_csv(filepath)
print(scoreboard)

(the top10.csv is in the same directory as this code)

why is it that it is telling me: FileNotFoundError: [Errno 2] No such file or directory: 'top10.csv'?

[obtuse magnet]

Try passing in a normal path string

[paper flower]

Are you running it from the same directory?

[paper flower]

I'd look into sqlite or postgres, if you want to host a mongodb postgres could be better 🤔
I'd say IMO databases withoug a well defined schema are hard to work with

[fiery thunder]

yeah I figured it out that was exactly correct just cd'd into the relavant directory 🤦‍♂️

thank you everyone!

[lost lily]

Hi, I was wondering if anyone could help my friend and I with our project set up. We are using django and then mysql for our database. This is our first time doing a project so we are a little confused on the set up. Right now in our settings.py file we have DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'fooName',
'USER': 'root',
'PASSWORD': 'hisPassword',
'HOST': 'localhost',
'PORT': '3306',
}
} And this lets his server run, however I am unsure if I need to connect mysql to his db and if so how? or if we could add myself as a user in settings.py, I guess I am just confused on how to access the database.

[little forge]

Hey @lost lily , I didn't understand the part of "connecting mysql to his db". Since the server is running, it means that Django is able to establish a connection with the database.

You can start creating models on your project, these should be populated into your database after writing them and running the migrations (https://docs.djangoproject.com/en/4.2/intro/tutorial02/#activating-models)

If you want for some reason to manually access the database, you can run mysql -h localhost -P 3306 -u root -p

[odd zodiac]

Using SQLite3 module in Python, how do I select all the information in a specified row number? So for example, I want to select all of row 1, assign its values, then move on to row 2

[little forge]

for row in cur.execute("SELECT t FROM data"):
    # assign values here
    print(row)

https://docs.python.org/3/library/sqlite3.html#cursor-objects

I believe row will be a dict like object, so you can iterate and get the values you selected on the query

[ebon plover]

Hi, all
I'm loosing my mind trying to make working a PostGres database in docker from my app docker project. The builds is made and the container running but i cannot access to it with my python file. I've try all day, anyone has an idea or already made it work ? thank you

[little forge]

Hey @ebon plover , can you provide the docker/docker-compose files?

Have you mapped the port from the docker file to your computer?

[ebon plover]

Hello @little forge , I think i did map the ports but seems to not follow the proper way . Here's you can have a look at the docker compose file
`version: '3'

services:
source-postgres:
image: postgres:latest
hostname: source-postgres
restart: always
healthcheck:
test: ["CMD", "pg_isready", "-q", "-d", "postgres", "-U", "root"]
timeout: 45s
interval: 10s
retries: 10
environment:
- POSTGRES_USER=root
- POSTGRES_PASSWORD=password
- POSTGRES_HOST_AUTH_METHOD=trust
volumes:
- ./db/postgres:/docker-entrypoint-initdb.d/
ports:
- 5432:5432
networks:
- my-network

bridge-minio:
image: minio/minio:latest
restart: always
command: server --console-address ":9001" /data/minio/
healthcheck:
test: ["CMD", "curl", "-f", "http://localhost:9000/minio/health/live"]
interval: 30s
timeout: 20s
retries: 3
environment:
MINIO_ROOT_USER: minioadmin
MINIO_ROOT_PASSWORD: minioadmin
ports:
- 9002:9002
- 9001:9001
networks:
- my-network

create-minio-bucket:
image: minio/mc
depends_on:
- bridge-minio
entrypoint: >
/bin/sh -c "
/usr/bin/mc config host add myminio http://bridge-minio:9000 minioadmin minioadmin;
/usr/bin/mc rm -r --force myminio/replibyte-test;
/usr/bin/mc mb myminio/replibyte-test;
/usr/bin/mc policy download myminio/replibyte-test;
exit 0;
"
networks:
- my-network

adminer:
image: adminer:4.8.1-standalone
hostname: adminer
restart: unless-stopped
ports:
- "8080:8080"
environment:
ADMINER_DEFAULT_SERVER: source-postgres
networks:
- my-network

networks:
my-network:
driver: bridge
`

and the docker file:

`FROM mcr.microsoft.com/devcontainers/base:debian
ARG DEBIAN_FRONTEND=noninteractive
ARG USER=vscode
RUN DEBIAN_FRONTEND=noninteractive
&& apt-get update \
&& apt-get install -y build-essential --no-install-recommends make
ca-certificates
git
libssl-dev
zlib1g-dev
libbz2-dev
libreadline-dev
wget
curl
llvm
libncurses5-dev
xz-utils
libsqlite3-dev
tk-dev
libxml2-dev
libxmlsec1-dev
libffi-dev
liblzma-dev

Python and poetry installation

USER $USER
ARG HOME="/home/$USER"
ARG PYTHON_VERSION=3.11

ARG PYTHON_VERSION=3.11

ENV PYENV_ROOT="${HOME}/.pyenv"
ENV PATH="${PYENV_ROOT}/shims:${PYENV_ROOT}/bin:${HOME}/.local/bin:$PATH"
RUN echo "done 0"
&& curl https://pyenv.run | bash
&& echo "done 1"
&& pyenv install ${PYTHON_VERSION}
&& echo "done 2"
&& pyenv global ${PYTHON_VERSION}
&& echo "done 3"
&& curl -sSL https://install.python-poetry.org | python3 -
&& poetry config virtualenvs.in-project true`

Thank you

I've also a devcontainer.json but it's not relevant i guess

[little forge]

everything looks right to me @ebon plover , what happens when you run this? docker-compose exec source-postgres psql -U root does it connect to the database?

Maybe the problem can be related to the connection string, how are you connecting to this database on your application?

[ebon plover]

@little forge Good things is, when I execute the command, I get in the container :). Bad things when i execute the python script i get :
conn = _connect(dsn, connection_factory=connection_factory, **kwasync) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ psycopg2.OperationalError: connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused Is the server running on that host and accepting TCP/IP connections? connection to server at "localhost" (::1), port 5432 failed: Cannot assign requested address Is the server running on that host and accepting TCP/IP connections?

`import psycopg2

PostgreSQL connection details

host = "localhost"
port = 5432
database = "postgres"
user = "root"
password = "password"

Establish a connection

conn = psycopg2.connect(
host=host,
port=port,
database=database,
user=user,
password=password
)

Create a cursor object to execute queries

cursor = conn.cursor()

Execute a sample query

cursor.execute("SELECT * FROM test")

Fetch all rows from the result

rows = cursor.fetchall()
for row in rows:
print(row)

Close the cursor and connection

cursor.close()
conn.close()
print("Connection successful. Cursor and connection closed.")
`

[little forge]

is the container running when you are trying to run this python script? You can run with docker-compose up -d

[ebon plover]

Are we on the same page that i'm supposed able to run the python script in my container ? Btw the docker-compose up -d does not change the error on the python script :/

[pastel wren]

thank you!

[little forge]

try changing the host on your file @ebon plover

host = "source-postgres"
port = 5432
database = "postgres"
user = "root"
password = "password"

to match the host on your db config

[ebon plover]

New change means new Error Ahhhh: 🙂

conn = _connect(dsn, connection_factory=connection_factory, **kwasync) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ psycopg2.OperationalError: could not translate host name "source-postgres" to address: Name or service not known

[little forge]

weird, it should be able to resolve the hostname, I wasn't able to understand in your docker/docker-compose, where this is script is being called, how are you running it?

Maybe it should be added on the docker-compose and adding a dependency with the database

[ebon plover]

This script is called in my poetry env

[little forge]

hmmm I would try adding it to the docker-compose file, something like this:

services:
  myapp:
    build: 
      context: .
      dockerfile: Dockerfile
    volumes:
      - .:/app
    working_dir: /app
    command: >
      bash -c "poetry install && poetry config virtualenvs.in-project true && poetry run python <YOUR SCRIPT HERE>"
    depends_on:
      - source-postgres
    networks:
      - my-network

[ebon plover]

I'll try thanks

[upper epoch]

looks a lil simple folder seperation but it works for small projects!

[silver robin]

Hello, anyone have a good easy method for basic SQL queries to an access database on macos?

I have tried the JayDeBeAPI way with UCanAccess but having loads of issues getting it to fire

[misty hound]

Hello, I want to make a program that could take/fetch data from firebase database should it in a GUI. I am a beginner so I don't know much about python and firebase. I'd be grateful if someone could show me how to do it? or guide me in right direction.

[torn sphinx]

any free service online to host a db?

• how do i know when do i have to store things on a local file or on a data base?

rn im using a json, is small, and works, but wanna try db 🙂

[crisp ravine]

something like Dbeaver?

[void perch]

need help pls

[wise goblet]

start with Sqlite3.

it is still localfile, but it is already database

the difference is... static typed structure that is easy to migrate between application versions

it is code scalable

[wise goblet]

if you are going to make more than one version of your application, if you are going to upgrade it and maintain, using for a lot of users.
best to start using database.

if you wish to preserve data, which you migrate between application versions -> best to use relational database

Sqlite3 is a small step forward that is local file, and at the same time relational db

of course there exist non sql dbs.. but better not using them until u fully understood merits of sql and all its usage cases. sql db is nice default for everything

[torn sphinx]

but do i need to run sqlite on my pc?

i studied sql on my university but rlly poor

just the classic select from where

and something about foreign keys i dont remember well but which links 2 different tables, iirc

right now my json is a key and a list of strings as values for each key

which are paths on my local disk

[wise goblet]

u can use Sqlite3 without having it as a process
it is just a localfile + library interacting with it, that imitates interacting with fully fledged relational database

it has its limitations, but still wonderful thing

and yes it will involve foreign keys stuff

[wise goblet]

https://sqlbolt.com/ start with this refreshing interactive course

[wise goblet]

Or start with this, it will work too

it explains related stuff for dummies

[torn sphinx]

tyvm 😄

[silver robin]

Looking for something that can be scripted in Python to import queries as data frames. I have DBeaver but not looking to do these manually.

[somber dirge]

are there any nosql db that are really similar to redis but not in memory? im trying mongodb now but the document searching is a little weird compared to just all keys in redis

[wise goblet]

Lets check the list... hmm... there is some stuff 🤔

https://etcd.io/
btw, this key value db is used as one of main things for Kubernetes functionality

https://github.com/kragniz/python-etcd3
oh yeah, python lib is available

looks very redis like but with more features

[somber dirge]

looks like scylla and ignite are both key value

[somber dirge]

etcd does look great

nesting using a directory format is interesting

in the "pip install python-etcd" version

[signal dome]

hey

[fading patrol]

Never done it but my guess is that you want this: https://pypi.org/project/pyodbc/

[rapid flare]

How to create database

And from where

[fleet sandal]

anyone help me please

it is showing an error while connecting with MYSQL

ERROR: 103 no route to host

[silver robin]

Thanks but unfortunately there are no native ODBC drivers for Microsoft Access on MacOS. There is an MDB-tables module which is reasonably ok but doesn't quite get it done.

[wraith crown]

You can try with DBeaver https://dbeaver.io/

It says that mongo connection is closed - how do you manage the opening and closing?

[wraith crown]

cool, so I can suggest some things:

• look in the mongodb server is your IP whitelisted;
• is the server online etc;
• try using the client as context manager as I showed, this ensures closing the connection

[maiden light]

Lemme try this method
And + how to see if server is online or not?

[wraith crown]

@maiden light also check here https://pymongo.readthedocs.io/en/stable/api/pymongo/errors.html#pymongo.errors.AutoReconnect

exception pymongo.errors.AutoReconnect(message: str = '', errors: Mapping[str, Any] | Sequence | None = None)
Raised when a connection to the database is lost and an attempt to auto-reconnect will be made.

In order to auto-reconnect you must handle this exception, recognizing that the operation which caused it has not necessarily succeeded. Future operations will attempt to open a new connection to the database (and will continue to raise this exception until the first successful connection is made).

I suppose something is wrong with the server

[maiden light]

I'll look

[wraith crown]

I suppose you can try connecting to the server with MongoDBCompass with your credentials
also if you can - use the debugger and see what is the uri that you are getting from os.environ["ans"] it can be wrong

[maiden light]

I double checked url

[wraith crown]

@maiden light can you test this - here the autoreconnect is handled and if connection is successful the loop breaks
these are the imports

import os
from pymongo import MongoClient
from pymongo.errors import AutoReconnect

[fleet sandal]

No one helped me

@formal cosmos

@soft fog

@half gyro

Anyone help please

[half gyro]

Please don't ping random people, it's rude.

[fleet sandal]

I pinged cuz u have helper role.. Sorry ;-; help me

[wraith crown]

@fleet sandal can you try connecting to the database with something different from python

[fleet sandal]

What do you mean? Can you briefly explain it please?

[fleet sandal]

No I didn't tried by any other programm, I only tried in python

I only know python

And i bought a server for python which provided me that database..

[wraith crown]

@fleet sandal so you have a server that has your app and a database installed on it?

[fleet sandal]

So i have a server which gives me one panel for python codes to run which i bought for discord.py bots and it gave me one database in mysql server I set up that database by their guide now when I connecting it with my code then it is giving that error

[wraith crown]

@fleet sandal No route to host error means that either the port or the server address is not correct, can you show your code that is responsible for the conenction

[fleet sandal]

😅

I don't know which is important to hide so I hided all

I just did this.. And it showing the error

[fleet sandal]

Do u know phpadmin?

[wraith crown]

@fleet sandal yes, can you connect through it?

[fleet sandal]

No

[fleet sandal]

So the host is in the right top line before via right?

[fleet sandal]

Connect through phpadmin yes, but in python code, no

[wraith crown]

@fleet sandal this means your server is working, I've had problems with mysql.connector before, so I used pymysql like so
pip install pymysql

import pymysql
con = pymysql.connect(host="", port=3306, user="", password="")

@fleet sandal you can try like this and fill your credentials

[fleet sandal]

Thankyou i will try it soon

[wraith crown]

@fleet sandal you can try also if you run the app on the same server to use localhost:3306 as the server host

[fleet sandal]

What?

[wraith crown]

if your python code is on the same server, instead of db-lon-01.sparkedhost.us use localhost:3306

[fleet sandal]

Ok let me try

[fleet sandal]

When I write localhost instead of db.lon.....

[wraith crown]

try with pymysql

[fleet sandal]

And when I write localhost:3306

[fleet sandal]

Ok

[wraith crown]

@fleet sandal did it work?

[fleet sandal]

I am out of house.. Will let you know when I will reach back

[pearl adder]

SQL: How do I write an SQL Select query where it checks it returns all the values from a given month e.g 2023-06-XX Any day in that month?

[fading patrol]

I googled it for you. Details may vary by database engine. https://stackoverflow.com/questions/851236/how-to-write-a-where-clause-to-find-all-records-in-a-specific-month

[pearl adder]

omg I saw the Month function but used it wrong, thank you.

[maiden light]

Issue is resolved now thanks for all the help!

[wraith crown]

what resolved it?

[maiden light]

With method

You gave first

It started working fine now

[maiden light]

Category  |  Type
      1                 a
      1                 x
      1                 w
      1                 b
      2                 a
      2                 x

I have SQLite table looks like this.
How can i check if 1 has value of x in it or not in python?

[unreal hemlock]

what's your issue

[maiden light]

Category IDs can be duplicated
But Type per category is unique

[unreal hemlock]

how to do it in python or which query to use

[maiden light]

I want to check if that value exists in this category or not

[unreal hemlock]

yeah but

can you show what you've tried

[maiden light]

cursor.execute("SELECT * FROM table WHERE category = ? AND type = ?" (1, 'x'))
exists = cursor.fetchone()

if exists is None:
   print('value does not exist')
else:
    print('value exists')

[unreal hemlock]

2 things

is x a variable?

[maiden light]

No

Typo

[unreal hemlock]

ok you updated it 👌

whats your issue with it?

[obtuse magnet]

Why this weird mix of python and SQL

[maiden light]

Not answering

[obtuse magnet]

U can do what u want directly in SQL

[unreal hemlock]

wdyn by that

[maiden light]

I'm using database in my python application

[unreal hemlock]

because sql is never used alone

@maiden light

print exists

[obtuse magnet]

I mean the result he wants can be from a single SQL query

[unreal hemlock]

and check it's value

[unreal hemlock]

?

[obtuse magnet]

And then just take whatever result into python

[maiden light]

It printed does not exists

[obtuse magnet]

Rather than splitting the logic across SQL and python

[unreal hemlock]

man just don't

[unreal hemlock]

what's iots value?

[maiden light]

I want answer inside my application for users of application
Not every user can have access to db

[maiden light]

iots?

[unreal hemlock]

its*

[obtuse magnet]

Idt u are getting what I'm saying but ok

[unreal hemlock]

yeah but makes no sense

[maiden light]

It printed first statement

if exists is None

[unreal hemlock]

try to print exists

[maiden light]

Never worked on real project?

[maiden light]

Good idea thanks

[obtuse magnet]

Wew

[wraith crown]

maybe this needs to be a dynamic query - different each time

@maiden light exists has to be some list with values from all columns, this is because you execute SELECT *

[maiden light]

So can i use "SELECT 1" instead of "SELECT *"

[wraith crown]

if you need all of the data, then no, if you need something specific from the table then SELECT column FROM table will get you just the value from the column

[maiden light]

i need specific value

[wraith crown]

good, let's say you need the category - "SELECT category FROM table WHERE category = ? AND type = ?"

[maiden light]

But i have repeated values in column

[maiden light]

Oh

Okay

It works ig

Thanks

[fleet sandal]

@wraith crown

[wraith crown]

is this with pymysql

[fleet sandal]

yesh

[wraith crown]

show me your code, blank the user and password again

did you enter the port?

[fleet sandal]

okey

[wraith crown]

try with this

[fleet sandal]

why this happening to me

[fleet sandal]

ok

[fleet sandal]

same

[wraith crown]

hmm this is strange, do you have access to the server where database is running?

[fleet sandal]

how to do this?

[fleet sandal]

obviously i bought it

[wraith crown]

from this machine do ping db-lon-01.sparkedhost.us

I mean the server that has your python code

I think that this server is not able to find the database server

[fleet sandal]

in cmd prompt?

[wraith crown]

yes

[fleet sandal]

yeah that i get by buying it

[wraith crown]

good, run the ping command from it

[fleet sandal]

now>?

[wraith crown]

what did it print?

now this is strange

you have connection

[fleet sandal]

hmmm

[fleet sandal]

tell me how to get username , port , host and password via php admin

i will recheck it

i filled it right but...

it is not working ;-;

[wraith crown]

can you execute queries with phpmyadmin, if yes then the credentials you use for it are the database credentials

[fleet sandal]

yes i made tables and columnns too

[wraith crown]

so there is something wrong with the python code, can you post the functions you use for database connection

and how you run the code?

[fleet sandal]

this username and pass will be use for db right?

[wraith crown]

I think so

[fleet sandal]

by simply tapping start

[wraith crown]

what is this - some code server?

[fleet sandal]

yeah thats what i bought

a code server for make bot 24 7 online with a database

seems like i must contact their support

[wraith crown]

maybe yes, but I still don't understand, this code is run on the same machine where the database is, correct?

[fleet sandal]

yes

[wraith crown]

good, when you made the ping you've seen the IP of the server, add it instead of the db-lon-01.sparkedhost.us in the pymysql snippet

[fleet sandal]

okey

[fleet sandal]

same 113 no route to host

[heady violet]

Is it just me or I find sql is very limited in some situations. Whenver you want to add some simple functionalties over ur existing query, you have to write a nested query which in return slows down your run time. For example, in the query that results in this table below:
https://cdn.discordapp.com/attachments/342318764227821568/1122902175169904670/image.png
If I want to add header names for row 1 as 24hr, row2 as 48hr, row3 as 1month, chatgpt is telling me to use join to do this:
which is:

SELECT
header_name,
"YIELD",
"AVG_SPEED",
"TURNINGS",
"SHRED"
FROM (
SELECT '24hr' AS header_name FROM dual
UNION ALL
SELECT '48hr' AS header_name FROM dual
UNION ALL
SELECT '1month' AS header_name FROM dual
) header_table
JOIN your_table ON 1=1;

Now imagine if my table is really big, that means just adding column header names -> a very simple functionality results my query to be 10 times slower for example

In conclusion, I feel like sql is limited in defining header names, in other words you would have to have to think about ur header names before setting up your database tables so you don't have to go through this tedious process. If i want to build a table with both column headers and row headers, sql is not the way to go?

[wraith crown]

I have no more ideas, this is totally not right - on the server console can you run mysql -u root -p - this is to connect to the database from console

[fleet sandal]

their is no console in it

💀

[wraith crown]

can you ssh into it

It will be best to contact their support to resolve your issue, the services are not communicating with each other

I really don't have more ideas

[fleet sandal]

Yeah I am on their support ticket.. And waiting for response

[hollow oar]

cursor.execute(create_table) on the left
cursor.execute(config) on the right
what's happening here? did you confirm your changes are saved?

[maiden light]

I just changed variable name when tried

It's same code

It has same motive so doesn't matter

[hollow oar]

please post your code as text instead of screenshot 🙏
also please include the error you are encountering again (again in text form)

[grim vault]

    v9 INTEGER,
);'''

This shoud be a syntax error. Trailing , is not allowed in SQL.

[maiden light]

Error is same even if i remove that 💀

import sqlite3

conn = sqlite3.connect('test.db')
cursor = conn.cursor()

create_table = '''CREATE TABLE antinuke (
    guild INTEGER,
    v1 INTEGER,
    v2 INTEGER,
    s3 INTEGER,
    v4 INTEGER,
    v5 INTEGER,
    v6 INTEGER,
    v7 INTEGER,
    v8 INTEGER,
    v9 INTEGER
);'''

cursor.execute(create_table)

This is code in text format

[grim vault]

Works fine.

[maiden light]

Throwing error

[grim vault]

!e

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

create_table = '''CREATE TABLE antinuke (
    guild INTEGER,
    v1 INTEGER,
    v2 INTEGER,
    s3 INTEGER,
    v4 INTEGER,
    v5 INTEGER,
    v6 INTEGER,
    v7 INTEGER,
    v8 INTEGER,
    v9 INTEGER
);'''

cursor.execute(create_table)

Hm

[hexed estuary]

it might be some reserved name

[grim vault]

!e

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

create_table = '''CREATE TABLE antinuke (
    guild INTEGER,
    v1 INTEGER,
    v2 INTEGER,
    s3 INTEGER,
    v4 INTEGER,
    v5 INTEGER,
    v6 INTEGER,
    v7 INTEGER,
    v8 INTEGER,
    v9 INTEGER
);'''

cursor.execute(create_table)

[delicate field]

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

[No output]

[maiden light]

Oh

[grim vault]

Ah, mixed nobreakspace/space~~/tab~~ in the front of the names.

[maiden light]

Lemme try this

[hexed estuary]

why does that cause a duplicate column name error of all things?

[grim vault]

No idea. You need some debug output of the sql parser.

Ah, it's 0xA0 the no brake space then a normal space, so sqlite tries to make a column named with three nobrakspace twice.

[maiden light]

It worked

Thanks

I'm learning so errors make me little confuse

[grim vault]

Maybe your editor inserts nobreakspace chars if you use <shift>-<space> or so. Such errors are hard to find.

[burnt fog]

im not able to connect my code to mongo db

i have like a 50 line error

https://paste.pythondiscord.com/tidasojexu

[wraith crown]

pymongo.errors.OperationFailure: bad auth : authentication failed

Check your credentials

[fleet sandal]

They said they created an another database with different region.. and now it is working

It means it will work for mysql.connector too

Cuz all of my code was in mysql that's why I don't want to shift..

[maiden light]

My data id not staying in sqlite3

I turn on my application settings and after few minutes they disable automatically (means data disappears)

[little girder]

` a = ["6567", "9766", "2232"]
for x in a:
await db.execute("INSERT INTO mytable (mycol) VALUES (?)", x)

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 4 supplied.`

help?

[maiden light]

Oh

values = [('6567',),
          ('9766',),
          ('2232',)]

# Execute the INSERT INTO statement
cursor.executemany("INSERT INTO mytable (mycol) VALUES (?)", values)

@little girder try this method

[little girder]

I'll try

[maiden light]

Wait

I'll edit it for you

[maiden light]

@little girder take this

Check if it works

[little girder]

still incorrect binding

[wraith crown]

try with:

await db.execute("INSERT INTO mytable (mycol) VALUES (?)", (x, ))
await db.commit() # maybe you will need this too

I think that the parameters have to be a tuple

[fleet sandal]

@wraith crownone last help

when i make my bot 24x7

then after some time the mysql functions stop working

every time this happens

i guess some timeout but why that is not why i bought it restarting it every half hour

[wraith crown]

what error you get

as a suggestion make sure you close your database connections

[fleet sandal]

How to close it? Db.close() ?

[wraith crown]

generally yes, I prefer however to use context manager

[fleet sandal]

Ok

[fleet sandal]

Do i have write this in all function

Cuz after closing it is not working again

[wraith crown]

this is why I prefer context managers - if you go with the .close() you have to start the conenction before each query and close it after that, in each function for each query

[fleet sandal]

Is context manager is a module?

[wraith crown]

read about them here https://www.learndatasci.com/solutions/python-context-managers/

[real timber]

why does everything seem to use psycopg2 instead of psycopg3 ? I'd have expected the sqla 2 update to use 3 for some reason, but it's still on 2.

[maiden light]

What code writer are you using?

[fleet sandal]

Pycharm

[maiden light]

On phone?

I need this thing

[fleet sandal]

Nah... I use pycharm on PC
This is server that i bought to run discord.py bot I just copy pasted my pycharm code
Also this is not convenient i do all stuff at pycharm and paste it here..

[maiden light]

Oh okay

Nvm

[fleet sandal]

[drowsy quest]

hello everyone

I run into a weird issue

"ValueError: cannot reshape array of size 2499 into shape (2500,1)"

what doe this mean

actually figured it out-

[steady saffron]

Does anyone have any good resources for someone completely new to databases (JSON as a DB my love), in particular relational databases? I'd like to use sqlite, but some reading is probably in order first before I do that 😅

[lean olive]

plug incoming

[green pier]

I have a tutorial I wrote a while back
https://owencampbell.me.uk/sql_python_tutorial/

[steady saffron]

lovely, thank you!

[upper otter]

Weird question what would be the pip install for compliance?

[wise goblet]

#databases message and #databases message

[hot dock]

First of all I hope this is the right chat

Hello guys,
I have a problem which I don’t really know how to solve properly.
So there I’ve got a table stored in a csv file, it’s made up of seven columns and each column contains a number of rows (each column contains a different number of names, so I want to put „None” in empty space). The essence of the problem is to sort this table in such a way, that rows from all of the columns are aligned in one row eg.:

before sorting:
france; germany; france; italy
norway; italy; germany; norway
germany; None; norway; france
italy; None; None; germany

after sorting:
france; None; france; france
norway; None; norway; norway
germany; germany; germany; germany
italy; italy; None; italy

Also additional problem is that names of the countries may differ slightly eg.: France, Republic of France etc.

Does anyone know a good/elegant approach for such a problem?

[storm mauve]

that is not sorting at all and it sounds like your data should not have been in a CSV file to begin with in first place

csv & saying that you are sorting a table implies that you are working with a table in which the values inside of each your rows are related to each other

what you have, or at least what you are trying to do, sounds like just a mess.

best case scenario: xy problem, you actually meant to do something like group by
worst case scenario: that data is either completely unusable or just unsuited for what you're trying to do

!xy

[delicate field]

xy-problem

The XY problem can be summarised as asking about your attempted solution, rather than your actual problem.

Often programmers will get distracted with a potential solution they've come up with, and will try asking for help getting it to work. However, it's possible this solution either wouldn't work as they expect, or there's a much better solution instead.

For more information and examples, see http://xyproblem.info/.

[hot dock]

well thanks for response, the example I gave is extremally simplified. In reality I have this data stored in excel, so this is the way I wanted to grab this data into python. I know it's mess as I've tried to combat this problem for 5 days now and i don't really know what to do

[storm mauve]

what do you actually have, and what are you trying to get out of it?

[hot dock]

the file looks like this - these are the names of sport teams

and i need to somehow sort them by rows as desribed

btw sorry for being so messy with this, but this is a part of my project and I'm not a professional coder (yet I hope)

[storm mauve]

ngl looks unusable to me

what even are the columns?

[hot dock]

the colums are the names of participants and the rows are the names of teams

I somehow need to match team names in rows if possible at first place

this is what it looks like in excel (I send you like 700th row), because I want to demonstrate that some of the cells are actualy empty

[true glacier]

ok so what you need to do?

oh i see

[hot dock]

any idea? xd

[true glacier]

so you need to sort into rows based on countries and insert None into emptys to make it like a solid rectangle?

[hot dock]

essentially yes

[true glacier]

can you send the files that you're working with?

[hot dock]

the problem with instering Nones is already fixed

yeah sure

[true glacier]

you can send in dm

[true glacier]

ok i cant even understand what cell is supposed to correspond to the country of the teams

[hot dock]

so my previous example was simplified, but in program I'm working with names of teams and not only names of countries

sorry for the mess btw

there are 7 colums and they contain names of teams

I need to group them by rows so they match xd

[true glacier]

yeah but how does one get the country from the team in this sheet

it seems like the country names appear in random places

[hot dock]

i will try to explain the issue once again on the different dataset

[true glacier]

ok but this has 2 columns and the other one has 7, who tf gathered this dataset

[hot dock]

:)))))

let me explaing man XD

[hot dock]

this data set is f***** for real and not intuitial at all so i will only use the 2nd one ever since now

@true glacier let me dm you so I can explain it with a friend of mine (he perhaps has a better explantion of the issue)

[fleet sandal]

my pip stopped working suddenly

[maiden light]

Json isn't for db purpose

Checkout PostgreSQL, mongo, SQLite, MySQL

[lean olive]

http://www.interdb.jp/pg/
interesting find 👀

[lean olive]

not sure, I just got to know this randomly

[blazing nexus]

Hi guys, has anybody worked with SQL Alchemy and RDS proxy? I can see that session pinning is occuring and causing lambdas to timeout.

[coral wasp]

You’re looking for a melt / unpivot operation, to convert column orientation to row orientation.

[hot dock]

edit: @true glacier has solved our problem

[coral wasp]

Great, what technique did they use?

[true glacier]

not really a technique, 1 sec, ill send the source

!paste

[delicate field]

Pasting large amounts of code

If your code is too long to fit in a codeblock in Discord, you can paste your code here:
https://paste.pythondiscord.com/

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[true glacier]

https://paste.pythondiscord.com/ewaduhomik

basically bruteforce lmao

[coral wasp]

Oh, melt would've been better, imo

unless I'm misunderstanding it, which I might be

generally, columns -> rows = melt (the pandas term for it, other systems call it unpivot or uncrosstab), rows -> columns = pivot (or crosstab)

[hot dock]

maybe it would and there is definitely a room for optimisation, but @true glacier program is working super well and I think I will stick to it. The time is not really an issue considering my application of it

[coral wasp]

Yup, was just an fyi for future reference!

[rapid flare]

how to create database

.

[unreal hemlock]

!d sqlite3

[delicate field]

sqlite3

Source code: Lib/sqlite3/

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

The sqlite3 module was written by Gerhard Häring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249, and requires SQLite 3.7.15 or newer.

This document includes four main sections:

[paper flower]

I work with sqlalchemy, what's the problem? 🤔

[vast cradle]

Hey all, I'm looking to find people who are trying to use Python for Data Engineering who might want to jam sometime

[leaden talon]

What do you mean by jam? 🤔

[vast cradle]

Tag team a project, work through a concept

[rapid flare]

I have postgresql

[vast cradle]

None atm

[torn sphinx]

QQ for sqlalchemy users; is there a way to define an attribute based on another column on the model?

class Email(Base):
  isRead: Mapped[bool]

class Inbox(Base):
  emails: Mapped[List[Email]]

class UnreadInbox(Inbox):
  unreadEmails = Inbox.emails.where(Email.isRead == False)

UnreadInbox is an example of what I would like to achieve - ideally it would be like a bi-directional relationship in that changes to the emails field would be reflected in unreadEmails and vice versa, but that's not 100% necessary :)

[paper flower]

Kind of, I think you can create a relationship with custom join condition? 🤔

Do you need that filter condition?

[steady saffron]

for sqlite3, there are pragma options. how/when am I supposed to set them?

do I run PRAGMA at the start of every connection to the database?

ah, SO says that it depends on the pragma option

that's great

[brazen charm]

For the most common, i.e. WAL, etc... then when you create the connection normally

[waxen finch]

judging from https://sqlite.org/pragma.html only a couple pragma statements are persistent such as journal_mode specifically with WAL, although there's probably no harm in repeating those pragmas

[torn sphinx]

Thank you! That's a great idea, i'll look into it 👍
I do need the where on there, yeah.

[clear stirrup]

you can use and more than once

[deft ridge]

timestamptz (postgres)doesnt allow to store varying timezones according to input , do i need to create a seperate char column to store timesones in IANA format, i have a requirement where i want to store the users timezone data , and users are from different timezone. In postgres docs its saying that it will always store them in UTC and we need to convert it to required timezone while retreiving ,????

[spare pendant]

i have this sql query:

"SELECT username, password FROM users WHERE username=%s, password=%s", (username, password))"

where the username and password variables are given in python with these exact values (these are dummy credentials):

username = "rudi"
password = "rudi456"

sql kept coming back saying i have the wrong syntax:

...for the right syntax to use near ', password='rudi456'' at line 1

i am so confused what did i do wrong

[grim vault]

You can't use a comma , for multible clauses, I guess you meant:

... WHERE username=%s AND password=%s

[spare pendant]

i didn't know that

thanks! it works as expected now

[grim vault]

Just a note: You shouldn't save passwords in plain text.

[spare pendant]

noted
is saving it in a .env a bit better? so its never revealed?

[grim vault]

It depends on the use case. I just saw the users table, which means multible users (to me) and in that table the passwords whould not be stored in plain text. I'm sure there are many guides on How to save passwords.

[spare pendant]

thats true
i can't really modify it much though since this is part of a bootcamp assignment, and this is what was given to me

certainly not best practices, or the appropriate way to do this, for that matter

im also limited by my own capability, still new to all of the database side of backend dev

[light pendant]

Hey everyone, I'm trying to use SQLAlchemy mapped dataclasses, specifically defining reusable column declarations using Annotated as described in the documentation here: https://docs.sqlalchemy.org/en/20/orm/dataclasses.html#integration-with-annotated

However, even the example code shown there (repasted below) does not work, instead throwing a TypeError: __init__() missing 1 required positional argument: 'id':

from typing import Annotated

from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import registry

# typing tools will ignore init=False here
intpk = Annotated[int, mapped_column(init=False, primary_key=True)]

reg = registry()


@reg.mapped_as_dataclass
class User:
    __tablename__ = "user_account"
    id: Mapped[intpk]


# typing error: Argument missing for parameter "id"
u1 = User()

Using the latest SQLAlchemy version (2.0.17) and Python 3.10. Have been trying to sift through the issue tracker to see if it's a bug but am having trouble finding anything (my github-fu is not good it seems).

[deft ridge]

anyone ?

[torn sphinx]

await self.execute("INSERT INTO inventory (userID, itemID, quantity) VALUES ($1, $2, $3);", int(id), str(item), amount)``` how do i like insert into the inventory with the itemID being on the items table and the itemid on the inventory is a fk referencing the itemSerial pk on the items table

[fading patrol]

I haven't dealt with this, but see a lot of relevant info here: https://stackoverflow.com/questions/13837258/what-is-an-appropriate-data-type-to-store-a-timezone

[tribal lake]

import aiosqlite

class Database:
    def __init__(self, filename):
        self.filename = filename
        self.connection = None

    async def connect(self):
        self.connection = await aiosqlite.connect("database.investments")

    async def close(self):
        if self.connection:
            await self.connection.commit()
            await self.connection.close()

    async def execute(self, query, *args):
        async with self.connection.execute(query, args) as cursor:
            return await cursor.fetchall()

    async def execute_and_commit(self, query, *args):
        async with self.connection.execute(query, args) as cursor:
            await self.connection.commit()
            return await cursor.fetchall()

class Bot(commands.Bot):
    def __init__(self) -> None:
        super().__init__(intents=Intents, command_prefix='-')
        self.database: Database | None = None

    async def setup_hook(self) -> None:
        self.database = Database("database.sqlite3")
        await self.database.connect()
        await self.database.execute_and_commit("""
           CREATE TABLE IF NOT EXISTS items (
            Discord_ID INT,
            Roblox_ID INT,
            Amount_Invested INT
            );
        """)

    async def close(self) -> None:
        await super().close()
        if self.database:
            await self.database.close()

database not creating, no errors

[torn sphinx]

UPDATE inventory SET quantity=$1 FROM items WHERE inventory.userID=$2 AND inventory.itemID=$3 AND inventory.itemID = items.itemSerial;``` i think that its trying to insert a string into the itemID in the inventory which is an integer 
simple idea of my structure: itemID in inventory is a string of the item name, itemID on inventory is integer which is the serial number from the items table

discord.errors.ApplicationCommandInvokeError: Application Command raised an exception: DataError: invalid input for query argument $3: 'item1' ('str' object cannot be interpreted as an integer)```

[hexed estuary]

Sounds like you need to convert the third argument to an int, if you're comparing it to an int column.

[torn sphinx]

yeah well the itemID in items, is a string, which i want to query, and i want to update the itemID in inventory which is the serial number of the itemID in items thats a string i dont know if this makes much sense i can visualise if needed

ah nevermind, i am stupid

[coral wasp]

You good now?

[torn sphinx]

yeah

[torn sphinx]

I think you missed part of the link that you sent - that code is a sample of something that does not work with static analysis. Sadly the mapped_column call also needs to be present in your model. Have a look in the section that you linked for more info 👍

Many thanks for sharing this, very cool feature that I didn't know about!

[light pendant]

I'm aware it does not work with static analysis, that is not the issue I am referring to. Despite the claims in the docs it does not work even at runtime.

[torn sphinx]

Oop, I'm sorry, now I'm the one not reading 😄 your question was not about static analysis, my bad!

[light pendant]

No worries, the way it's worded does make it sound ambiguous

I'm trying to figure out if there's something missing from that example or it it's a bug. I don't want to open a GH issue if it's already known, but I'm having trouble finding anything related. Either I'm not using the right search terms or something 🤷‍♂️

[paper flower]

It doesn't seem to work with Annotated, you probably should create a GH issue for that 👀

= mapped_column(...) works as expected

[thorny marsh]

how do i do this

nvm

i was told its built in

[maiden light]

Will conn.commit work for both or I'll have to separate them?

[cold grotto]

What async odm do you guys use??

[south gust]

How do I resolve Errno 2? I keep getting file does not exist on Colab.

I put both the colab and the file in the same folder in my gdrive

[paper flower]

I personally prefer SQL, but I think beanie and odmantic are the most popular ones 🤔

Also beanie seems to be more active

[cold grotto]

i have been using motor mongodb driver

in FastApi projects

[paper flower]

beaine uses motor under the hood

Also pymongo isn't an odm really 🤔

[cold grotto]

when i started the project beanie was not stable so i went with motor itself

[cold grotto]

umm okay

why do you personally prefer SQL???

[paper flower]

Yep!

[paper flower]

pymongo is a driver, it doesn't map anything to objects 🤔 So it's not an ODM

[cold grotto]

yes agreed.

[tribal lake]

Can anyone help me on how to input values into a datbase table that's a variable. The database code is in a whole separate file

[deft ridge]

i am working on making a monitoring dashboard which is quite complex , data is huge too like 10,00,00,000 rows , and i need to write many analytical queries on it , i can use partitions on tables and and write views to avoid running huge queries on run time and try to make it work using postgres, i am not aware of non relational databases, would it be beneficial if i use them for this case

[deft ridge]

can u elaborate a little ?

[tribal lake]

it doesn't matter

[fading patrol]

No you absolutely do not want a non-relational DB in your use case. You want a data warehouse, which can be built on Postgres and/or you can use solutions like Redshift, BigQuery or Snowflake

[brazen charm]

If it's analytics specifically with big aggregations, Clickhouse is probably a very high up contender

[coral wasp]

Im a big fan of duckdb, if it works for your data set. We’re processing large datasets that we would’ve normally used snowflake for

[deft ridge]

at first glance its looking good , will check it out in depth

[deft ridge]

any specific reasons to avoid non relational db's

[coral wasp]

Analytical databases are good for analytical queries (vectorized / columnar stuff). If your queries are aggregates over partitions/windows, for instance, it’s going to be hard to beat the OLAP databases

A lot of it just has to do with vectorized operations.

[deft ridge]

oh i see

[coral wasp]

Duckdb has a pretty active discord too

[light pendant]

Appreciate having a another data point! That gives me some more confidence to go make an issue

[fading patrol]

Because what you described (analytical queries etc.) is exactly why relational DBs exist.

[quartz nest]

Any good resource to learn dbms

Books, video tutorial or anything is ok

[trim mica]

I have a problem with SQLite, I want to insert some IP address into my sqlite table
I have a fucntion Which takes an ip address as only parameter and insert this IP into thé SQLite Table
It works with "192", "192.168"
But with "192.168.10" I have a OperationalError near ".10"

I don't know why I have this issue if my IP is 3 bytes or more

[coral wasp]

You column type is probably a numeric, not string type.

So, 192 and 192.168 are numeric. 192.168.10 is not.

Oh, you said SQLIte, I dunno then. Sqlite's data typing is weird to me.

What's the full error message?

[trim mica]

Yes SQLite, "192" is string, 192 IS numeric

Can I texte you in Mp to show photo ?

[coral wasp]

Oh, I get it. Show me the insert statement please.

Just paste the code here.

[trim mica]

Yes thanks you, I do that in 20 minutes

[coral wasp]

Ok, I suspect you're either not using a parameterized query (with a ?) or inserting an unquoted 192.168.1.10. Either use a parameterized query, or quote the string: '192.168.1.10'

[torn sphinx]

If you have multiple database operations that you want to group together as a single transaction, you can perform all the operations and then call commit once at the end to commit all the changes together.

@maiden light

[maiden light]

Thanks

Helpful

[hollow wedge]

  host='redis-11943.c56.east-us.azure.cloud.redislabs.com',
  port=11943,
  password='pass')```
 
im using redis but its not working for example

```py
@client.tree.command(name="anti-discord-servers-on", description="blocks discord servers from your server")
@app_commands.default_permissions(manage_guild=True)
async def antiservers_on(interaction: discord.Interaction):
    if f'{interaction.guild.id}' in r:
        pass
        await interaction.response.send_message("Anti discord servers is already on")
    else:
        r.set(f'{str(interaction.guild.id)}', 'on')
  
        await interaction.response.send_message("Anti discord servers is on")```
        
 this doesnt work like it stores it but doesnt get it

[torn sphinx]

is there any serverless hosting platform that is recommended?

i'm currently using cockroachdb and it works fine

[hollow wedge]

Does cockroach have redis?

[torn sphinx]

idk

[proven mica]

what kind of mattery is this?
i even didn't know if theres a await?!

[vital widget]

It throws error code 1064. Can someone tell what's wrong in this.
I wanna update multiple rows as null from table employee

[trim mica]

Hi, I have an issue with my sqlite code, can someone help me ?

active_users ='''CREATE TABLE IF NOT EXISTS Active_users(
                        IP TEXT,
                        PORT INT
                        )'''
cursor.execute(active_users)

def write_database(param, ip, port):
    if param == 'insert_user':
        a = f'''
            INSERT INTO active_users(IP,PORT)
            VALUES({ip}, {port})
            '''
        pass
    elif param == 'delete_user':
        a = f"DELETE from active_users where IP = {ip}"
        pass
    cursor.execute(a)

write_database('insert_user', '192.168.10.43', 5000)```

But it doesn't work, that is the error

sqlite3.OperationalError: near ".10": syntax error```

And it works with "192.14" as IP but if I had more than 2 bytes, it fails

[paper flower]

!sql-fstring

[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

[trim mica]

Hi, sorry I didn't understand

[paper flower]

Don't use f-strings when writing sql queries

[paper flower]

I think that explains it pretty well^

[trim mica]

ok thanks you !

[grim vault]

The list must be in parenthesis:

update employees set d_name = null where emp_id in (12,20,21,22,23,24,25)

[velvet perch]

Hey, do you have a website to find opensource data to train and exploit them ?

[vital widget]

Oh that was about this, i forgot. Thanks sir

[coral wasp]

In addition (use parameters, not f strings), the reason your query failed was: set ip = 192.168.1.10 is invalid: the ip address string needed to be quoted, since 192.168.1.10 is not a valid literal.

[karmic spade]

hey guys iam trying to connect to a postgres server but my problem is how can i connect to a specific schema in my database ?

[karmic spade]

Anybody help pls ?

[digital coral]

um

isnt it just

postgres://username:password@host/database```

[karmic spade]

ik but if i want to connect to a specific schema in my database which has more than one schema, how can i do that

[queen rose]

Whats a schema? Perhaps you mean a Database?

[karmic spade]

check out this screenshot

how can i connect specifically to the shop-manage schema

[karmic spade]

the screenshot just finished uploaded my internet sucks

[paper flower]

IIRC you can't connect to specific schema, but you can reference objects in it via .:

select * from public.table;

You can also modify search_path: https://www.postgresql.org/docs/current/ddl-schemas.html

[karmic spade]

thanks alot ❤️ btw this is my first time asking someone online about something that i want to do in programming

iam not used to ask people in programming communities because usually i have to wait for a long period of time to get an answer so i just try to do it on my own

[bold copper]

Hey guys, started up a fresh VM to play with docker on. Downloaded LAMPP to tinker with that as well and I'm trying to connect to the mysql database that's included with it. I can access it fine from myphpadmin but when attempting to connect via python code with mariadb package, it is looking for the socket in the wrong place. I've tried creating a symbolic link between the 2 locations and that does not work. Is there a way to specify where my socket location is inside the python code? It's looking in /run/mysqld/ but the actual location is /opt/lampp/var/mysql

If I'm asking in the wrong channel please direct me to the correct one

[torn sphinx]

sorry to post over you cereal, but I'm hoping this will be a quick and easy one - I have a SQLAlchemy one-to-one relationship. Can I have my attribute automatically fetch a single field from the related record, instead of referencing the whole record?

E.g currently I have:

class A(Base):
  b: Mapped["B"]

class B(Base):
  name: Mapped[str]

But I would like something along the lines of:

class A(Base):
  bName: Mapped["B"].name

class B(Base):
  name: Mapped[str]

It's looking like I'll have to use a hybrid property but that doesn't quite meet my use case 😰

[paper flower]

You're looking for association proxy (it's a sqlalchemy thing) I may be mistaken but I think you can use it with one-one and many-one relationships

[torn sphinx]

Yep this is perfect, thank you so much 😁

[solemn imp]

so cooool

[silent cypress]

Please can someone tell me why I keep getting this very annoying error.

[stark oasis]

How do I tell SQL what data type I’m passing it for each column (using pandas)

[coral wasp]

What database?

[torn sphinx]

your current working directory may not match, make sure you're launching python from within the Flask Project 2 folder?

[silent cypress]

Yeah I had to set config directly from my flask app.

[river matrix]

Hey guys,

I'm trying to use infornation_schema.columns in Jupyter through the SQL extention. Do you guys know why it keeps returning an error?

[river matrix]

Also tried using CHARINDEX() but it doesn't seem to exist.

[lucid bolt]

How do i solve this in postgresql?

[ionic pecan]

How would you convert an integer to a UUID?

[lucid bolt]

vvid to bigint

[ionic pecan]

You are converting a BIGINT column to UUID. How would you convert an integer, say, 3, to a UUID?

[lucid bolt]

idk i messed everything up

i created a django model posts with id column as vvid but deleted the model but still getting such thing

[ionic pecan]

the thing I'm trying to say is that there's no clear conversion from an integer to an UUID as opposed to, say, converting an integer to text

and if id is your primary key and referenced anywhere, then you won't be able to convert the column as easily either

[torn sphinx]

Just learned how to use and setup SQLite3 LOL

[steady saffron]

congrats! I also recently learned the basics of SQL and SQLite

[vital widget]

Why does it gives me 3 results?

[forest ice]

Which databases are important to learn for a data analyst?

Is MySQL and posgresql enough?

[brazen charm]

Probably because you have 3 courses that the student isn't apart of, and you select a new row for every student, for every course they're not apart of.

[vital widget]

I didn't understand

[grim vault]

Your join doesn't have any condition, so every student is joinded with every course anbd only studends with the null condition are displayed with every course there is.
I would say you have one student (with id 6 where its course column is null) and three courses (1, 2 and 3).

[ionic pecan]

anything with SQL will do you great, great favours. PostgreSQL is a solid database server. SQLite is a solid data base library. MySQL I haven't worked with too much myself, but i'm sure it's solid too. But I think solid knowledge of SQL itself is better than focussing on a specific database.

[earnest trail]

Hey. Any one can help me in my project

[ionic pecan]

yes. just ask your question

[coral wasp]

I’d strong suggest an OLAP database (I’m a duckdb fan since it’s like SQLite for OLAP): you’ll likely see snowflake or something similar in a DE or DS role

[brazen charm]

Honestly if you touch anything close to power BI, MSSQL as well

[humble wasp]

I am getting an error "sqlite3.OperationalError: near "?": syntax error" when passing in a string of the table to this function. Is there an error in my code? (using sqlite3)

    delete_query = "DROP TABLE ?"
    cursor.execute(delete_query, (table,)) ```

[hexed estuary]

I don't think it's possible to interpolate a table name; placeholders only support values and not stuff like table/column names.

[humble wasp]

is it possible to have a placeholder for a table name?

[storm mauve]

not really

you'd have to do something like py allowed_values = {'games', 'books', 'movies'} if table not in allowed_values: raise ValueError() cursor.execute(f'DROP TABLE {table}') or ```py
if table == 'games':
cursor.execute('DROP TABLE games')
elif table == ...:
...
else:
raise ValueError()

[humble wasp]

ok thanks!

[wise goblet]

SQL injections man. very bad advice. @humble wasp don't listen this stuff 😆

[storm mauve]

what do you suggest instead?

that is literally just as bad

[wise goblet]

oh right. i have read wrong section of docs 😆

[humble wasp]

idk if you can use %s in sqlite

[wise goblet]

let me debug options locally what works i guess.

[storm mauve]

in the code snippet they posted and then deleted, that was an old way of doing string interpolation in python, which is literally as bad as f-strings when it comes to SQL Injection

some databases may use other symbols instead of ?, but the right way to do it with SQLite is with ?
(iirc MySQL uses %, but still execute(query, params) instead of query % params, and Postgres uses $)

[storm mauve]

the issue is, the 'right' way (? placeholder for sqlite) does not works when specifying tables/columns

[wise goblet]

https://blog.devgenius.io/til-python-sqlite3-using-placeholders-to-bind-values-in-sql-statement-62d10338cb6b
yeah

not found either anywhere. except for explicit telling it does not work with tables :/

troublesome

https://pa1.aminoapps.com/5825/7070b6ba0c43cde82a8ea2942438c9070f463447_hq.gif

[wise goblet]

all right. then as the only substitution we can do. if we are going to use it with user input. we should restrict it to VERY strict regex for example some_prefix_[a-z]+_some_suffix. At least prefix or suffix should be present. + additional validations if user has rights for such action

then user will not escape our bindings

[humble wasp]

is it possible to make a function that you just pass in the table that you want to drop, and it executes a query that will drop the variable name passed in?

[wise goblet]

Sure. use ALTER TABLE tablename DROP COLUMN column_name query

[wise goblet]

that is if u wish to drop entire column

if u wish to drop specific Row, that is another query

[humble wasp]

like the entire table

[wise goblet]

Drop column it is then

[coral wasp]

That’s correct. You can use dynamic procedural language extensions to do such things, but yes: dynamically selecting a table via a parameter is not possible in (most) databases

[humble wasp]

Ah I figured something out. You can create a seperate string for the query and use the format method on that, then pass that string into the execute method.

[coral wasp]

Are you familiar with little Bobby tables?

!xkcd 327

[devout seal]

non visible do a screenshot

why are all database tutorials so boring they always succeed to make me fall asleep no matter how hard I try am scared of purchasing a course

[vital widget]

Why would you purchase a course?when all are available on YT

[tender osprey]

Hello chat 💬, I am using planetscale, with sqlalchemy. PlanetScale recommends using the
relational model and associate
tables by "pointing" rows from one
table to another, without enforcing
the relationships at the database Level. I am using the declarative mapping style in sqlalchemy but I keep getting errors I have one table lets call it Parent and a one-to-many on Child_1 and a one-to-one on the rest, does anyone use planetscale with sqlalchemy?

[devout seal]

I haven't managed to go far in any YT course I just want a place with exams and challenging stuff a better and fast way to or have you come across anyone who won't cause me to fall asleep while they are teaching?

[vital widget]

So yt lectures are boring?

[devout seal]

They just make me fall asleep the best one i have is from Giraffe academy but still the same it feels like Database is not my thing

[tender osprey]

what database?

Postegresql?

[vital widget]

Maybe You fall asleep because you don't get to pay for them

[vital widget]

What do you think is best?

[tender osprey]

if your leaning than probably sqllite3

its a testing database

Postgresql or mariadb are great production database, im old school thought so im still using mysql

[devout seal]

Is anyone familiar with Oracle? how would you compare it to SQL

[tender osprey]

??

[devout seal]

which is better easier and etc to learn

[tender osprey]

Sql is a language used by databases oracle is a company

so i guess it depends if you want to work for oracle

[devout seal]

the tutorial guys explained as though they where tools of the same level and functionality thanks for the explanation though

i now understand allot thanks to you

[tender osprey]

oracle has its own database which uses sql , thats what you probably mean

[uneven gulch]

How to use match against in sqlalchemy?
I need to use it on title and venue in combination with ilike - other two.

query=query.where(models.Publication.title.ilike("%{}%".format(search_query)) | models.Publication.abstract.ilike("%{}%".format(search_query)) | models.Publication.venue.ilike("%{}%".format(search_query)) | models.Publication.authors.ilike("%{}%".format(search_query)))

[stoic finch]

I’m creating a inventory management web-app as I think it’ll have a lot for me to learn.

I’m currently using supabase authentication and their DB, would this be more beneficial or to just host my own PostgreSQL db? Or doesn’t it really matter and it’s just down to price?

[humble wasp]

I'm using sqlite3, and getting this error: sqlite3.OperationalError: no such column: Billy when using this function:

def update_rows(table: str, column: str, value: str, condition: str) -> None:
        # update rows function
        update_query = """UPDATE {tbl} 
                        SET {column} = {value} 
                        WHERE {row}""".format(tbl=table, column=column, value=value,                         row=condition)
        cursor.execute(update_query)```
when passing in these values:
```py
Tables.update_rows(table="employees", column="first_name", value="Billy", condition="last_name = 'Bob'")
 ```I think the error is the value parameter is somehow pointing to a column when it explicitly is not.

[coral wasp]

👀

[coral wasp]

What sql statement do you think is being evaluated here?

print the update_query, and I'll show you the mistake. then i'll lecture you about not parameterizing the query (don't use f-strings)

[humble wasp]

printed: UPDATE employees SET first_name = Billy WHERE last_name = 'Bob'

[coral wasp]

So, you're setting column first_name to column billy

[humble wasp]

?

[coral wasp]

That's what that line does.

[humble wasp]

ok then how do I fix it?

to update the row

[coral wasp]

What is the difference between your firstname and lastname check?

[humble wasp]

none

[coral wasp]

Honestly, just look again then. There's a distinct difference between the two parts of your SQL query that is causing the error.

[humble wasp]

ok i fixed it

[harsh torrent]

Does anyone know any Python libraries for importing data from excel to Python?

[coral wasp]

Pandas is the place to start. It can read and write Excel spreadsheets

[harsh torrent]

Thank you for the quick reply!

[halcyon zealot]

Hi everyone, sorry for my bad english (I live in Austria, german my mother tongue), I started recently with python, but I'm a developer since nearly 40 years... so I developed scoriet (https://www.scoriet.com) and now I want to implement a REST api. So basic, socriet reads the structure of a existing database, in this case mysql, creates a duplicate of the structure (in his own database) and then start a generation of a template based project. In this case a Python REST api with Flask, where can I start for a CRUD REST api? I want of course the project in Python3, database maybe mysql, can of course later changed to PoastgreSQL

[clear wraith]

Hi everyone, I don't quite know what to ask or what to Google for, I have no prior experiences with databases and an open source discord bot I'm trying to run is giving this error.

Could anyone help me with this? If you can't help in-depth, maybe some resources online that I could read to help me with this?

[storm mauve]

postgres documentation / how to setup postgres

you have to setup an entire program external to python

or use an online one provided by some hosting provider, but in which case you'll likely have to pay some $ to whatever's hosting it

[scenic olive]

Hello Database Wizards. Im still in the learning phase of SQL and have been at this on and off for way more days than id like to admin. Can anyone explain or point me to where i go wrong since the unblurred entry appears twice even tho i have group by name? all other columns are unique names

[scenic olive]

(no whitespace before or after)

[covert radish]

how would i load the yelp dataset onto a sqlite database? https://www.yelp.com/dataset

[coral wasp]

I don't really get it from the screenshot. What database? What's the full SQL (text plz)?

[fading patrol]

Do you know how to load the data from JSON files? Start there. Then learn the basics of SQLite and the rest should be easy. This may help. https://www.freecodecamp.org/news/sqlite-python-beginners-tutorial/

[coral wasp]

I'm a broken record... for a OLAP type query of JSON, try duckdb. I've linked this a few times: https://duckdb.org/2023/03/03/json.html

[covert radish]

sorry i didnt see it before, the issue is the yelp database is a text file with a bunch of jsons per business. does that still work here? or does it have to be a list

[coral wasp]

From what I read, the yelp dataset is a set of 6 JSON files. Is that right?

I'm reading: https://towardsdatascience.com/converting-yelp-dataset-to-csv-using-pandas-2a4c8f03bd88

If so, you should be able to directly query the JSON files in duckdb, if that's what you want. ie: duckdb.execute("select count(*) from 'review.json'")

[covert radish]

i believe that article is a bit old, i looked at a similar one too. they use a dataset from the link https://www.yelp.com/dataset/challenge which no longer works. my dataset is a... ~10 gig text file with a bunch of json files

[coral wasp]

The yelp docs agree with that link: https://www.yelp.com/dataset/documentation/main

And I got to those docs from https://www.yelp.com/dataset

[covert radish]

yeah im reading that rn

idk, i downloaded it and it gave me a txt file that looks like this

and if i scroll down theres reviews, and users

[coral wasp]

That's json.

What's the filename of the txt file?

[covert radish]

yelp_dataset

[coral wasp]

What extension?

[covert radish]

yes but its not split across multiple files

txt

[coral wasp]

When you downloaded it, what was the original filename? This isn't at all what the https://www.yelp.com/dataset/download page says. You should have gotten a .tgz file.

That contains a pdf and multiple .json files. I think you have a .tar file.

[covert radish]

should i try redownloading?

[coral wasp]

When you downloaded it, how did yo uuncompress it? Did you rename it at all?

[covert radish]

nope, i used winrar to unzip

[coral wasp]

The "download photos" option says it gives you a text file. Maybe you downloaded the wrong link?

There's a "download JSON" and a "download photos", and only photos says anything about text.

[covert radish]

yeah but this is one of the entries in the text file i have

it doesnt contain anything about photos

[humble wasp]

Do the constraint, modify and reference sql keywords work in sqlite3? Because I am getting errors when executing statements with them.

[coral wasp]

Yah, I dunno. I don't have time to download right now, but something isn't making sense to me.

[covert radish]

well from the data i have, is there anything i can do with it?

[coral wasp]

Yes, certainly, I just don't know what you have. If you have JSON files, you can query it with a number of tools (of which duckdb is one). It's a large dataset, so the regular "import json" might not be performant enough... there's also "orjson"

I don't understand that text file you have tho.

[covert radish]

im pretty sure i dont have json file, however my txt file is in the format of many jsons just stacked on top of each other

[coral wasp]

That's why I think you have a .tar file

a .tgz file is a .tar.gz file: uncompress once, and you get a .tar file

And a .tar file basically looks like (in a text editor) a text file with the files concatenated

What's the first line of the text file?

[covert radish]

i edited the text file a bit because the beginning of the text file contained something talking about the usage of the data

[coral wasp]

How big is the file?

[covert radish]

around 9 gigs

[coral wasp]

Yah, I'm sticking with my .tar guess.

[covert radish]

with (from a rough estimate) 200-250k businesses registered, which is a bit more than what the docs stated

[coral wasp]

Also, I'm guessing you're looking at this dataset: https://www.kaggle.com/datasets/yelp-dataset/yelp-dataset

(my guess is that the yelp link = the kaggle link)

[covert radish]

i can try downloading that one and see

it also says 5 json files

[coral wasp]

Yes, you should end up with multiple .json files.

The kaggle link also has some starter code to load it.

[covert radish]

what does this mean? py  data

[coral wasp]

nbsp is just an HTML space

non breaking space

I’m logging out for the evening, feel free to tag me and I’ll look tomorrow

[covert radish]

alright thanks so much

ill update you if anything changes

oo yep this is the one

[humble wasp]

Do the constraint, modify and reference sql keywords work in sqlite3? Because I am getting errors saying that the keywords are syntax errors when executing statements with them.

[coral wasp]

What statements are you executing, can you share full statement and error?

[covert radish]

can sqlite3 on python connect to the same database on sqlite shell?

[neat basin]

Hi guys I am having trouble installing this python package. Does anyone know how to resolve this? pip install coinglass-api

[neat basin]

ERROR: Could not find a version that satisfies the requirement coinglass-api
ERROR: No matching distribution found for coinglass-api

[neat basin]

This is the error message that it is giving me, never encountered it before

[fading patrol]

Yes but not at the same time

[fading patrol]

Or maybe I'm wrong, why not give it a try? (Back up your DB first)

[waxen finch]

from a bit of testing with the default rollback journal, PRAGMA locking_mode=[NORMAL|EXCLUSIVE], and BEGIN EXCLUSIVE, you can always create multiple connections to the same database file regardless of lock state and only after you attempt a read/write might it return an SQLITE_BUSY error code

also as suggested in https://sqlite.org/lockingv3.html, connections start in an UNLOCKED state rather than being disconnected, even if a PENDING/EXCLUSIVE lock forbids that connection from reading or writing

[neat basin]

Update, this is my error, I found it on stack overflow but nobody answered it, maybe someone will here https://stackoverflow.com/questions/61751422/pip-error-unable-to-create-process-using-file-path

[peak mesa]

!e

import pandas as pd
import datetime
now = datetime.datetime.now()
dmy = now.day,now.month,now.year

items = {'added':['000.023.223'], 'removed':[]}

addedCount = len(items['added'])
removedCount = len(items['removed'])

while addedCount != removedCount:
    if addedCount > removedCount:
        items['removed'].append(' ')
    elif addedCount < removedCount:
        items['added'].append(' ')

f=pd.DataFrame(items)
print(f)

[delicate field]

@peak mesa :warning: Your 3.11 eval job timed out or ran out of memory.

[No output]

[peak mesa]

help

why

?

[coral wasp]

Why what?

It runs forever because the counts are never updated

[coral wasp]

Make sense?

[peak mesa]

ah yes i got that figured out

i have another problem though

rooms = ['kitchen','child room','quota stuff']

roomSelection = input(f'Welcome. Which room?\nAvailable rooms:{rooms}')
roomAcceptedAnswers = ['1','2','3','k','c','child','q','quota',rooms]

while roomSelection not in roomAcceptedAnswers:
    roomSelection = input(f'Unrecognized input.\nAvailable rooms:{rooms}')
if roomSelection in ('1','k','kitchen'):
    room = rooms[0]
elif roomSelection in ('2','c','child','child room'):
    room = rooms[1]
elif roomSelection in ('3','q','quota','quota stuff'):
    room = rooms[2]

def add():
    while True:
        x=input('type items to add, press enter when done:')
        if x == '':
            break
        items['added'].append(x)
        print(items)

def remove():
    while True:
        x=input('type items to remove, press enter when done:')
        if x == '':
            break
        items['removed'].append(x)
        print(items)

items = {'added':[], 'removed':[]}

while True:
    course=input('--- ITEMS MENU ---\n "add", "remove" or "exit"?')
    while course not in ('add','remove','exit','1','2','3'):
        course = input('no. type "add", "remove", or "exit".\n"add" - add items to a room.\n"remove" - remove items from a room.\n"exit" - save changes and exit the program.')

    if course in ('add','1'):
        add()

    if course == ('remove','2'):
        remove()

    elif course == ('exit','3'):
        break

why does the loop keep looping if i type anything except 1

when getting the definition for course ?

[coral wasp]

You should probably open a help thread here, or ask in py-gen. Not really a db question (and I need to go eat 🙂

[covert radish]

connection = sqlite3.connect('./datasets/yelp_business')
c = connection.cursor()

c.execute('select categories from restaurants where categories like "%Restaurants"')
restaurants = map(lambda x: x[0].split(', '), c.fetchall())
unique_categories = set([item for sublist in restaurants for item in sublist])

unique_categories

why is it that there are things in unique_categories but if i do

for row in restaurants:
  print(row)

it doesnt print anything, or if i do list(restaurants)

[waxen finch]

map() returns an iterator (in python 3 that is) so you can only go through its elements once, if you want to use it multiple times you should store it in a container like tuple/list

[covert radish]

ah i see, thanks

[vital widget]

What's wrong i did? Do i need to put double quotes and why

[deft ridge]

'simaru' instead of 'simaru''

.

when there is no orm and migration system , how do we keep track of table created and updated , should we store it in .sql files in my project or is there a better way to keep track of things. for e.g clickhouse

[vital widget]

..

@deft ridge

Can you help me in this

Why it shows this error

[deft ridge]

f"select * from students where name = '{u}';"

[obtuse magnet]

F string...

[vital widget]

I didn't understand

[deft ridge]

this is not a good practice though , prone to sql injection

[trim mica]

Hi guys

I have a programm which is called 'server programm' running and this program is linked with my database of active users, I would like that when I shut 'server_program' it delete every row of my table active_users, like a kind of reset of my table of active users

I know how to delete but i don't konw how to how to make my server program understand that it must delete when i shut it down

[vital widget]

Why do we put {u} in quotes '{u}'? That's what i don't get

[grim vault]

e = con.execute('select * from students where name = ?', (u,))

!sql-fstring

[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

[vital widget]

Thanks sir

[deft ridge]

Anyone ?

[deft ridge]

What is this chat gpt ?

[noble lintel]

can anyone help me with connecting python to mysql?

[crimson cargo]

Good day every1!11
I'm working with pydantic / sqlalchemy ORM and create engine like this:

create_engine(url=env("DB_ENGINE_URI"), json_serializer=pydantic_encoder)

However this encoder fails on None(NoneType) fields, as well as dict fields (normally JSONB in my postgres db)

File "/home//.venv/lib/python3.11/site-packages/psycopg/types/json.py", line 140, in dump
    return dumps(obj).encode()
           ^^^^^^^^^^
  File "pydantic/json.py", line 90, in pydantic.json.pydantic_encoder
TypeError: Object of type 'dict' is not JSON serializable

What am I doing wrong?

[hollow oar]

what pydantic version are you using?
could you provide more context?
could you tell us what is obj here?

[crimson cargo]

The obj is from psycopg sources here:

class _JsonDumper(Dumper):
    # The globally used JSON dumps() function. It can be changed globally (by
    # set_json_dumps) or by a subclass.
    _dumps: JsonDumpsFunction = json.dumps

    def __init__(self, cls: type, context: Optional[abc.AdaptContext] = None):
        super().__init__(cls, context)
        self.dumps = self.__class__._dumps

    def dump(self, obj: Any) -> bytes:
        if isinstance(obj, _JsonWrapper):
            dumps = obj.dumps or self.dumps
            obj = obj.obj
        else:
            dumps = self.dumps

        return dumps(obj).encode()

Pydantic is of version 1.10.10

[hollow oar]

could you tell us what is obj here?
sorry i wasn't clear before, what is the concrete value of obj when it crashed?

have you consulted this example? https://gist.github.com/imankulov/4051b7805ad737ace7d8de3d3f934d6b

[crimson cargo]

It crashes on None, for example

and on dict like {'foo': 'bar'}

I am upgrading pydantic to 2.0.2 and psycopg to 3.1.9-binary

Also I'm trying to adopt the sample you provided, thanks!

[trim mica]

Hi guys
I have a programm which is called 'server programm' running and this program is linked with my database of active users, I would like that when I shut 'server_program' it delete every row of my table active_users, like a kind of reset of my table of active users
I know how to delete but i don't konw how to how to make my server program understand that it must delete when i shut it down

[vestal ravine]

So I am writing a discord bot with discord.py and I am too the point where I think I need a database to handle some of the information. Here is the problem I have never worked with a db before, but I have it all up and running (the containers atleast lol). Here's my setup: Host is a raspberry pi running docker where I have 2 containers 1 running discord.py and hosting my bot, and the other is a postgres database. I can connect to the database and run queries from both containers so it is working. I decided to start with something simple... 1 table with 8 columns so I manually created the table. What is the best way to connect to the DB from my bot? Do I write functions for save/delete/update/insert and call the function anytime I need to write to the DB?

[harsh pulsar]

Generally yes, this is a good place to start. Writing your own functions to save, delete, etc. is a great exercise and learning to work with the database directly, as well as writing SQL code. You should make sure that you are following best practices using bound parameters and transactions where necessary. Also, consider using Docker Compose to manage your containers, it's meant for exactly this use case of a single "application" consisting of multiple "services"

You might also want to consider looking into a migration tool, to manage database schema changes

For now you don't have to worry much about a security configuration on the database side because it's all running on the same machine as the Python application and should not be exposed to the outside world at all. But it can't hurt to look into how Postgres roles and permission grants work, it's still good practice to create a separate user for your application with limited access to only what you need, just to avoid accidentally truncating a table or doing something else destructive when you didn't intend to

[vestal ravine]

Thank you for your response. I am using docker compose and hosting the application and the DB in separate containers utilizing the least privilege methodology along the way to hopefully thwart any badness lol. No ports are open on either of the containers to the outside world.

[harsh pulsar]

Sounds like you're off to a good start

[fading patrol]

An ORM like SQLAlchemy takes a little getting used to if you've never done it, but is generally worth learning (as opposed to embedding raw SQL queries in Python)

[harsh pulsar]

My feeling is that you should start with the raw queries, and then "graduate" to sqlalchemy core and/or ORM once you have a good grasp of how it works

[pure mortar]

lol someone recommended this paper to me https://arxiv.org/pdf/2306.08766.pdf

anyway

what i got from it was a call to action for dbms academic folks

[wise goblet]

concurrent execution of "GRANT pg_monitor TO %s" command gets me error

"pq: duplicate key value violates unique constraint \"pg_auth_members...

i could add mutex to solve this problem

but may be i can solve it insome another way

So grant operation is not very concurrent safe i assume

hmmm. i'll start with small, confirming that mutex can solve it, then seeking another solution

Okay. mutex onto all GRANT operations helped 😎

Question araises. can we do it without mutex

[torn sphinx]

yyooo

[wise goblet]

answer. looks like GRANT role is global per cluster

all other grants are fine and unique to db

answer is: running GRANT role only once, or adding mutex only to grant role 🙂

[vital widget]

3. st_name input("Enter the student Name: - ")

4. data conn.execute("SELECT * FROM student where st_name = '"+st_name+"'")

5. for n in data:

6 print(n[0]," ",n[1]," ",[2],[3])```
Can someone pls explain me why do we put those many quote in line 4  where name =

[slender atlas]

It's string concatenation. There are three strings there whose characters are:
SELECT * FROM student where st_name = '
The student name from st_name
'

Alternatively, and perhaps more intuitively and definitely more securely, you could use query parameters: data = conn.execute(f"SELECT * FROM students where st_name = ?", [st_name])

[vital widget]

Why can't we just do this ('select * from student where name =' +st_name)

[slender atlas]

This will replace ? with whatever value st_name

[slender atlas]

Because st_name is a string and needs to be treated that way in the query

Imagine if someone gave you a name like hello, buddy, the query would be SELECT * FROM students WHERE st_name = hello, buddy

There need to be quotation marks around the value so it's treated as a string, a proper value, by the query

Going into security practices, using query parameters does the job of converting Python objects to corresponding SQL values properly and eliminates the problem of SQL injections

[vital widget]

I get it thanks roie

[slender atlas]

Is it not possible to use wait_for in slash commands

You could use that to wait for a message right after somebody used /author ping

Oh it has nothing to do with it I see it's just for showing records

[naive sandal]

maybe your select statement didn't return any results?

you could add a debug log to check that case

[naive sandal]

you could just use a print statement although I would recommend setting up some proper logging for your project

[slow rivet]

I have such an odd problem and I cant even properly understand it and I have been trying to solve it for such a long time now. Any help please? It is related to displaying an image from a database to my flask app

[fading patrol]

Looks like you already hijacked the web dev channel but for the future see #❓｜how-to-get-help