databases

[harsh pulsar]

not sure about exponential fit though

[summer pier]

but no function analysis of the regression curve

[harsh pulsar]

what exactly are you looking for? you might indeed want to start using something like R

we also have #data-science-and-ml which i think is a more appropriate place for questions about data analysis, in the future

[summer pier]

I need a tool, that gives me a regression of a data sheed, and then also does the calculus with the Regression Curve

i mean i could do it by myself, but why waste time?

[harsh pulsar]

what calculus? you need be more specific. the calculus is usually required to fit the model but not usually required after

[summer pier]

maxima, minima, the roots, the turning point, all the basic stuff of curve discussion

[harsh pulsar]

im not an engineer so i have no idea what people do with such curves, so i had to ask

[summer pier]

no problem haha

[harsh pulsar]

you can definitely get pretty far with excel of course. another option is a symbolic math tool like maxima, sympy, or mathematica

https://www.sympy.org/en/index.html
https://maxima.sourceforge.io/

sympy is written in python and uses python syntax, so it might be a good excuse to get started learning python

[summer pier]

Yeah, i should. I already got Spyder. But literally it would be enough for me, if i just had a program, where i put my data in and it puts out all the Stuff i wanted. I dont really have to understand what it excatly does haha

[harsh pulsar]

I think what you want is rather specific, so no such program exists

This is why programmable tools exist, so that you can do tasks like this

If this is something you need to do repeatedly, you can write a program that does it once and then you can reuse that program

[summer pier]

Yes Yes, i thought that i would not be the first Person with this Problem, so anyone here have written such a program befor and could share it with me haha

[brave bridge]

libreoffice lambdas when

[rustic ginkgo]

How can I input sql commands for sqlite in vscode? I want just to configure db without writing commands with python

[late tinsel]

Would anyone have a good tutorial or recommended pattern for storing data provided by the end user. For example if I gave the option to create custom fields/lists, tables etc. Ive looked into eav, or even just storing json (currently on SQL server, may switch to MySQL at some point (planetscale).

[fading patrol]

I haven't tried this but maybe what you're looking for? https://github.com/AlexCovizzi/vscode-sqlite

[brave bridge]

You could talk to SQLite via the command line, if you have it installed separately

Run sqlite3 in the command line

[livid coyote]

Does SQLAlchemy deduplicate records based on pkey when calling all() as opposed to count()?

Edit: Yep! It sure does: https://groups.google.com/g/sqlalchemy/c/iqkl3PB10kM

[paper ledge]

how do i connect to a sql data bases (mysql)

[harsh pulsar]

huh, libreoffice actually does support javascript! i didn't realize. i knew it supported basic (its own dialect) and python. idk what beanshell is but it sounds like a terrifying java abomination. https://help.libreoffice.org/6.2/en-US/text/shared/guide/scripting.html?DbPAR=SHARED

[fading patrol]

You have many options. DBeaver is a good free tool to start with. If you are asking how to do it in Python specifically, this is a good intro: https://realpython.com/python-mysql/

[low rain]

CREATE UNIQUE INDEX idx_contacts_email
ON contacts (email);

what is this actually creating? why is the index named differently than the column?

[harsh pulsar]

an index is a lot like an index in a book. it's a separate data structure that the db engine can use to look up rows when filtering on that column, instead of just scanning all rows

often they use a data structure called a "b-tree" but several different kinds of indexes are available in different databases

it has a name because it's a separate thing in the database and you need a name to refer to when modifying or deleting it

the postgres docs also serve as a good general introduction https://www.postgresql.org/docs/current/indexes-intro.html

[coarse silo]

hey
i was isnstalling mongo db on my system

but after i added it to path

windows powsershell is not opening it

[low rain]

If you query data from the contacts table based on a specific email, SQLite will use the index to locate the data. ----Am i to understand that the index will be called when the column is queried? even if the index created isn't specified?

[torn sphinx]

Are there ways to revert the update and delete commands?

does anyone know how we can expire a record in postgres without coding a cron job? i am trying to make a premium system in my bot, where i want the activated licenses to expire after a while (1 month, 1 year and so on) but i am unable to understand how to expire a record in postgres and i dont wanna use a cron job

[civic cargo]

you can store the expiration date with your records and treat them as expired once past (& remove them by sending a query from your app if desired)

[civic cargo]

if you execute them in a transaction, you can rollback the transaction

[civic cargo]

the index is a data structure that the database builds on top of your data to allow faster queries

let's think in terms of Python for now - if you have a list that contains all registered users of your application, let's say 10_000 users, and you search for a user by their email, you have to check every user one-by-one until you find a match (or you don't). this is a linear search that your database may perform if in a similar situation.

however, if you know that you need to search for users by their email often, you can pre-compute a dictionary that maps from email->user record. once pre-computed, you can use it to significantly speed up your user lookups as long as you know their email, from linear to better-than-linear time. this allows your queries to still be fast, despite a growing amount of users, but you need to make sure that the dict is updated whenever something changes in the underlying data (i.e. user's email changes, user is removed, new user is added, etc)

a database index is similar to this dictionary - and importantnly, you don't need to maintain it once created, as the database will do that for you. by creating an index on the email column, you're saying that you will be doing lookups on it often, and that the database should pre-compute a data structure that will optimise such operations. as long as the index exists, the database will be smart enough to know to use it!

[low rain]

thanks 🙂

[torn sphinx]

well if i bind that part in my discord bot to check for expired license i need to use some event which will always keep running and keeping a track of time, and i dont know any event which runs all the time

thats why i am confused on how do i set this license expire stuff

[civic cargo]

you can use just a standard asyncio task to run the expiry logic periodically, but it is not needed
if you store the expiry date with your license record, you can easily filter out expired licenses in queries, e.g.

SELECT * FROM license WHERE user_id = $1 AND expire_at > NOW()

you need to adapt this to your schema, database dialect, etc - just demonstrating the approach

this way, if there is a license but it is expired, it will be filtered out

[torn sphinx]

so if the license is expired like if the statement expire_at > NOW() is false, then i wont get any record at all right?

[civic cargo]

yes, you can do < and > comparisons on date types

so the condition says, give me a license for some user that expires later than now

// is not yet expired

[torn sphinx]

ok but will this still work let's say that, a user activated their license in a guild and that license is just for 1 month, and after 1 month the license will expire

[civic cargo]

then the expire_at will be set to the activation time + 1 month (you can calculate this in Python)

and the query will stop returning it after 1 month

[torn sphinx]

ah ok, i will give this idea a shot tysm

[civic cargo]

if you want to periodically clean expired licenses, you can do e.g.

DELETE FROM license WHERE expire_at < NOW()

[torn sphinx]

just one last question the expire_At is a timestamp right

[civic cargo]

it should be some date type supported by your database

[torn sphinx]

ok

[civic cargo]

that will allow you to compare it using < and >

in postgres, you can use DATE (only date, no time) or TIMESTAMP (include time)

depends on what you need

[torn sphinx]

k

[tight stream]

Should I learn mongoDB or sql

Or just use json files for everything

[mental cargo]

Say I have the following database

X           |          Y
1           |          2
1           |          4
1           |          3
1           |          2
2           |          1
2           |          1
3           |          2
3           |          1

I want to filter out all that contains Y = 1, meaning i only get left with

X           |          Y
1           |          2
1           |          4
1           |          3
1           |          2

[paper flower]

You can simply use where

select x, y
from your_table
where y != 1;

[mental cargo]

but will tat return 3,2

because i want to delete 3,2 as well

since there was 3, 1

@paper flower

[paper flower]

I want to filter out all that contains Y = 1 😐

Can you explain what you want to do?

[ashen mason]

Hi there, anyone has any experience on synchronising relational db to Neo4j? Our sync takes way too long.

[fading patrol]

It really depends on what you're trying to accomplish but SQL is well worth learning at least the very basics. SQLite is the easiest way to get started.

Mongo is stupid simple if you just need to cache a bunch of data and not think about it

[torn sphinx]

how do i can make a python app with register/login system with website database

#help-honey

[grim vault]

You mean like:

select x, y
  from your_table
 where x not in (select x from your_table where y = 1);

[spice swallow]

Does anyone know why this isn't working? I literally cannot tell

its mysql

[mental cargo]

If I have

X    |    Y
1    |    5
2    |    
     |    4
4    |    
5    |    1
     |    6
7    |    8

and i want to do a z column, where x is the priority but if theres no x value then it goes to the y value.
So the final result will be

Z   
1
2
4
4
5
6
7

[waxen finch]

i think what you want is the coalesce() function to select the first non-null column

[mental cargo]

maybe tats the one

thanks

ill try soon

[torn sphinx]

Very basic question. I'm hitting an API that returns json data. I want this data to be transformed and loaded into my mysql database. What's the industry best practice for doing this?

Should I build custom python logic to create sql queries that store the data?

Is there some kind of tool that makes this easier, like an orm? I know django orm made it simple.

Just want to know what the general best practice is. I can make it work, I'm just not sure if my first idea is the best way

[harsh pulsar]

your application should have some internal representation of each "thing" that you are eventually writing to the database. this is very often a class of some kind. it can be as simple as a dataclass or as complicated as a django model.

a standard practice is to do this in "layers" or "phases": 1) download the data, 2) transform the raw response data into your internal representation, 3) transform the internal representation into a plain tuple or dict that can be written to the database, 4) write the data to the database.

often people group 1-2 and 3-4 together, but for the sake of illustration i wanted to make them explicit.

you can also skip the "internal representation" part if you are literally just writing stuff to the database and not doing anything else. but then basically your internal representation becomes "whatever the api returns", if that makes sense

let's say the api returns something like a list of widgets:

[
  {"color": "red", "size": 10},
  {"color": "blue", "size": 14},
  ...
]

then you might define a Widget class:

import enum
import attrs  # pip install attrs

class Color(str, enum.Enum):
    red = "red"
    blue = "blue"

@attrs.define
class Widget:
    color: Color
    size: int
    @size.validator
    def check_size_positive(self, attr: attrs.Attribute, val: int) -> None:
        if val <= 0:
            raise ValueError('Size must be positive.')

and then you can do something like this:

resp = requests.get('https://api.example.net/widgets')
resp_data = resp.json()

widgets = [Widget(**item) for item in resp_data]

conn.executemany(
    'INSERT INTO widgets (color, size) VALUES (?, ?)',
    [(w.color, w.size) for w in widgets],
)

[tranquil shoal]

#web-development message

[uncut moss]

hey can anyone tell me how to mark a composite key in an ER Diagram?
do i just have two PKs or do i put 'PK, FK' for both columns i want to use?

like this? or in another way?

[azure walrus]

hey people! I have a .csv dataset and i want to pass that data to a postgresql table using a python script. i am not finding any tutorial 😅 . How can i do that??

[fading patrol]

https://stackoverflow.com/questions/69860284/copying-csv-to-postgresql-database-using-psycopg3-in-python

[shy forum]

For sqlalchemy users:
I want to define a member class, that I will manipulate with my code, then create, delete or update the member in a database. I'm just starting with sqlalchemy and OOP, and I'd like to know if I should create that class member inheriting from Base class?

So does this example looks well written?:

class Member(Base):    
    def __init__(self, id, nickname):
        self.id = id
        self.nickname = nickname
    
    __tablename__="member"
    id = Column('id', Integer, primary_key=True)
    nickname = Column('nickname', String)

    def get_name():
        print(nickname)

[paper flower]

sqlalchemy generates __init__ for you, you don't need to manually write it

You don't need to specify column names when using or,, they're inferred from attribute names

Also primarykey should be primary_key if I'm not mistaken

class Member(Base):
    __tablename__ = "member"
  
    id = Column(Integer, primary_key=True)
    nickname = Column(String, nullable=False) # You also probably need nullable=False

Something like this should work

member = Member(nickname="Doctor")
session.add(member)
session.flush()
session.refresh(member)
print(member.id)

[shy forum]

alright I see. and I can then add methods to this class and manipulate the attributes after loading from database or before adding to database like it was any other class? pretty interresting thank you !

[paper flower]

I'd generally avoid adding any "heavy" logic to your models, especially something like database calls

[shy forum]

I see... There are so many ways to code a thing and I'm looking to improve my code...
I'll see what I can do, thank you for your help :)

[delicate field]

Hey @torn sphinx!

It looks like you tried to attach file type(s) that we do not allow (.zip). We currently allow the following file types: .gif, .jpg, .jpeg, .mov, .mp4, .mpg, .png, .mp3, .wav, .ogg, .webm, .webp, .flac, .m4a, .csv, .json.

Feel free to ask in #community-meta if you think this is a mistake.

[idle stone]

I know my question is subjective, but I just want some criteria that might help me to know what database is better.

what is better NoSQL or SQL database?

[fading patrol]

As a general rule, go with SQL if you don't have a specific reason not to

[torn sphinx]

Hi, i wanna make a database and i need to store timestamps, should i use the datetime format provided by sql or should i instead save the timestamp as epoch bla bla 8 bit integer Bigint which is more storage efficient?

[torn sphinx]

Agreed

[harsh pulsar]

use the datetime format provided by your database

[torn sphinx]

Any reason?

[harsh pulsar]

yes. let the database designers figure out the technical details.

any reason not to? the answer in general is "no".

[torn sphinx]

I kinda wanna make my own database

[harsh pulsar]

that's a different story. then you want to learn about how databases are designed.

[torn sphinx]

Well i use postgrssql now

[delicate field]

@little ingot Per Rule 6, your invite link has been removed. If you believe this was a mistake, please let staff know!

Our server rules can be found here: https://pythondiscord.com/pages/rules

[dusk urchin]

How do I remove a item from a json file if I have the key?

[fading patrol]

With Python? Off topic to this channel but I think you have to read the file to a dictionary, delete the item and then overwrite the file.

[west cobalt]

Query the Name of any student in STUDENTS who scored higher than Marks. Order your output by the last three characters of each name. If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.

Input Format

The STUDENTS table is described as follows:

Does anyone know where I can find a learning resource that will teach me how to answer this question in MYSQL?

I'm hard stuck on this question

No idea how to solve it whatsoever

[crimson tangle]

in DESC why none is in top

???????

how can i get none values at buttom ?

[waxen finch]

^ context
#help-orange message

[civic cargo]

you can usually specify null behaviour in addition to asc/desc sort

eg in postgres you would do DESC NULLS LAST

[raw perch]

how do i slove this problem

        Name= input("Enter Student Name")
        Adminssion_no = int(input("Enter Adminssion No."))
        Std = int(input("Enter Student standard "))
        B_id = int(input("Enter Book ID"))
        a = datetime.datetime.strptime('my date', "%b %d %Y %H:%M") 
        Date = a.strftime('%Y-%m-%d %H:%M:%S')
        data = (Name,Adminssion_no,Std,B_id,Date,)
        sql = 'insert into Student (Name,Adminssion_no,std,B_id,Date) Values(%s,%s,%s,%s,%s)'
        print(data)
        Cursor.execute(sql,data)
        mydb.commint()

[tidal bone]

I have app which mainly is used to create documentation and whole profiling to database, after importing db I have to test correctness of import function for all the metadata.
By that if I understand it correctly, I have to check if nothing is missing in structure/data/relations etc.

I'm right?

[civic cargo]

As the error says, your are trying to parse 'my date' into a datetime object, but it doesn't fit the needed format (it's not really a date at all)

it happens on this line:

a = datetime.datetime.strptime('my date', "%b %d %Y %H:%M")

you need to provide a proper date, such as:

a = datetime.datetime.strptime('Oct 15 2022 13:01', "%b %d %Y %H:%M")

[indigo lily]

i've never used databases before and i'm trying now for a project of mine but i can't even manage to connect to my db to create a table

i created a MySQL db on planetscale and installed mysql on my pc, now i'm using the SQLTools extension for vscode to create a connection, and it says the connection works fine, but when i try to run a simple query to create a table it gives me an ungodly error that i can't figure out

the code:

the error:

[indigo lily]

ER_ACCESS_DENIED_ERROR: target: olitracker.-.primary: vttablet: rpc error: code = PermissionDenied desc = DDL command denied to user 'planetscale-writer' for table 'Tasks' (ACL check error) (CallerID: planetscale-writer)

(olitracker is the name of my database)

[indigo lily]

ok solved... i need Admin permissions to create tables, not just read/write, now it works

[lament sphinx]

I'm trying to use sqlite to queue jobs for a subprocess execution

How would I get this data into a subprocess call?

so when job 1 finishes rendering the cube it moves onto rendering the redcube, the green cube and yellowcube in that order

[wicked flax]

I need to create a composite primary key in a sqlite table.

In other tables, I created a dictionary which indicated the primary key. If possible, just to keep things consistent, I'd like to keep using a dictionary-type format. Here's what I had before:

pk = {'meter': 'TEXT PRIMARY KEY'}    # 'meter' is the field name
table = 'tblMeters'
db = 'myDB.db'
cn = sqlite3.connect(db)
df.to_sql(table, con=cn, if_exists="replace", dtype=pk)

How would I change pk to create a composite primary key? I tried pk = {'meter': 'TEXT PRIMARY KEY', 'date': 'TEXT PRIMARY KEY'} but when I attempted to create the table I got the error table "tablename" has more than one primary key

[wicked flax]

I tried this: pk = {'meter, date': 'TEXT PRIMARY KEY'} and it seemed to work, but when I executed PRAGMA table_info('{table}') it didn't look like there were any primary keys in my table.

[harsh pulsar]

maybe try setting a multiindex on the dataframe

df.set_index(['meter','date']).to_sql(...)

!d pandas.DataFrame.to_sql

[delicate field]

pandas.DataFrame.to\_sql

DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)```
Write records stored in a DataFrame to a SQL database.

Databases supported by SQLAlchemy [[1]](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html#r689dfd12abe5-1) are supported. Tables can be newly created, appended to, or overwritten.

[harsh pulsar]

interesting, the doc says nothing about it

[raw perch]

Cursor.execute(f"update Books set B_status=not_available WHERE B_id=1") 

how do i fix this

[fading patrol]

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

I think you need to use %s

[tawdry finch]

Hi! I have some problem with my SqLite database... :

I think the problem is that the IDs are two digit IDs, but I don't know how to fix it

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

[civic cargo]

I think you need to give a tuple

cursor.execute('QUERY HERE', (id,))

[tawdry finch]

It works!!

[harsh pulsar]

the reason you got this specific error is that id is probably a string of length 2, so it was treating each character in the list as a separate parameter

[torn sphinx]

anyone here experienced with mongodb?

As I already said

Please just ask your question. Don't ask to ask, ask for topic experts or DMs. Skip the formalities and ask away

Asking if someone has experience in something is mostly wasting time as they will reply "Yes I do" and then wait for you to ask your question. Ask your question directly.

[halcyon jolt]

With Flask squalachemy 2.5 I want to be able to query the latest id column so i can add 1 to the id so ids aren't overwritten. Can I have help?

[paper flower]

Why not use autogenerated ids?

[halcyon jolt]

Is it possible to do that without giving out the same id

[paper flower]

Yes, sequential ids can be generated automatically by the database

[halcyon jolt]

Im sorry to bother, But how do I do that with flask squalchemy?

[paper flower]

Can you share your current model? Also what database are you using?

[halcyon jolt]

Thank you I figured it out.

[halcyon jolt]

I Am using postgres and i actually did not figure it out here is my model ```

from flask_sqlalchemy import SQLAlchemy
import sqlalchemy

db = SQLAlchemy()

class User(db.Model):

name = db.Column(db.String, nullable=False)
user_id = db.Column(db.Integer, nullable=False)

[paper flower]

This should do

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, nullable=False)

[halcyon jolt]

sorry, now how in my code when I commit name how can i add a new id one digit greater than the previous id?

[paper flower]

Db would do that automatically

you don't need to touch the id at all

[halcyon jolt]

Tysm

[lament sphinx]

how do I remove the [ from the db entry so subprocess can use it ?

using sqlite btw

[drowsy flame]

Hi Guys! I'm trying to learn databases

I'm getting "NoSuchTableError"

could someone please help me?

import sqlalchemy as db

engine = db.create_engine('sqlite:///Database1.accdb')
connection = engine.connect()

metadata = db.MetaData()
punishments = db.Table('Punishments', metadata, autoload=True, autoload_with=engine)

[paper flower]

Well, that table does not exist

[drowsy flame]

it does though

[paper flower]

Maybe you need to call metadata.reflect(engine) first?

[drowsy flame]

im getting same error

[paper flower]

Where did you call reflect?

[drowsy flame]

import sqlalchemy as db

engine = db.create_engine('sqlite:///Database1.accdb')
connection = engine.connect()

metadata = db.MetaData()
metadata.reflect(engine)
punishments = db.Table('Punishments', metadata, autoload=True, autoload_with=engine)

[torn sphinx]

what are some good cloud databases for discord bots

[paper flower]

What do you mean by "cloud" db?

[torn sphinx]

basically a db that doesn’t use a file

[paper flower]

postgres/mysql

[torn sphinx]

besides that

besides mongodb too

[paper flower]

What's wrong with postgres?

[torn sphinx]

i’m currently using mongodb

[paper flower]

@drowsy flame Can you dm me your db?

[torn sphinx]

& i’ve already tried learning postgres

any others though?

[paper flower]

SQL databases are pretty much most popular and versatile

[drowsy flame]

sure

[torn sphinx]

do you know mongodb?

[paper flower]

I don't find it that useful to I don't use it

[torn sphinx]

you know anyone who do

[paper flower]

Just ask your question, don't look for <insert technology name> experts

[drowsy flame]

import sqlalchemy as db

engine = db.create_engine('access+pyodbc:///Database1')
connection = engine.connect()

metadata = db.MetaData()
metadata.reflect(engine)
punishments = db.Table('Punishments', metadata, autoload=True, autoload_with=engine)

InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

I'm getting this error with the above code, can someone please help me?

[drowsy flame]

can someone please help? if anyone is around

[drowsy flame]

helooo?

[fair niche]

how to create a database?

[stable ibex]

Can anyone help?

[keen minnow]

Can you expand on your question?

[keen minnow]

That depends on the question

[fair niche]

what is a database

[keen minnow]

Alright. That may be beyond the scope of discord.
For questions like that, you will get more thorough answers on google.
Discord is more appropriate for discussions or clearing out specific problems or doubts

[stable ibex]

@keen minnow

[keen minnow]

Not sure which problem you are trying to solve, but numbers don't need to be between quotes

[stable ibex]

The instruction says Task 5: Write and execute the command to retrieve the item ID, description, invoice number, and quoted price for all invoices in the ITEM_INVOICE VIEW for items with a quoted prices that exceed $100.

It wants the results to be what it says on the expected results.

But instead it displays whatever is on the bottom.

[keen minnow]

so what do you get when you are running your query?

[stable ibex]

The display is on the bottom of the screen.

[keen minnow]

and what were you expecting in its place?

[stable ibex]

It's the one in the left part of the screen.

the one that says Expected results

Please help, I'm running out of time.

[keen minnow]

oh that's a test. Then we don't help cheating.
Happy to get back to it in 2h

[stable ibex]

@keen minnow It's not a test.

[keen minnow]

So what's the urgency?

[stable ibex]

@keen minnowIt's an assignment.

[keen minnow]

Then what's the problem to delay the help then

?

[stable ibex]

I have 15 minutes left.

[keen minnow]

So it is a graded work

[stable ibex]

Yes.

[keen minnow]

So we still don't help with cheating

[stable ibex]

@keen minnow It's not a test though.

[keen minnow]

but it's graded

isn't it?

don't you think that would not reflect your performance if we were to help you?

[wicked flax]

Depends on the level of help tho, no?

[keen minnow]

no. If you are in a graded environment, the work should solely reflect your own

[wicked flax]

I don't disagree. I do wonder tho if help can be offered in general, like "hey, since it's a graded assignment, just go google select queries" or something in general and not specific.

fwiw I thought the query he entered should have worked lol. I didn't look at it really closely tho.

[fair niche]

is this normal?

[keen minnow]

Then the result wouldn't reflect their own achievement.

[fair niche]

is this normal

[keen minnow]

This is not normal and does not look database related.
It looks like you have a syntax error. You may want to check #❓｜how-to-get-help for a more appropriate channel

[keen minnow]

also fyi, the error messages point you at specific lines and character numbers. It's worth checking them out 😉

[wicked flax]

Good luck Hunter!

(You're in the wrong channel my friend...)

[fair niche]

no i tryed downloading the SDK / .NET Framework for C# And when i do dotnet run $fileName it shows that error

[keen minnow]

These are not mutually exclusive

[wicked flax]

Look in the channel list, click on #🤡help-banana and type up your question

Add as many details and code as you can so folks can see what's going on.

[fair niche]

i posted it in #help-candy

[wicked flax]

Good.

If you want to solicit help go post a message in #python-discussion and link to your occupied help channel and ask if there's anyone that can help out. Some folks do click around in the help channels tho. Someone may be there eventually.

@keen minnow Are you familiar with using python as a front end to MS Access?

[keen minnow]

nope. Is there a specific question about it?

[wicked flax]

Not really. I'm a bit of a noob when it comes to using python to talk to databases. I have a bit of knowledge of setting up a database properly (in MS Access at least). Not knowing any better I'm using sqlalchemy to talk to a sql server and grab data, and then using sqlite3 to create a local file to work from. I saw a post earlier today about connecting to an accdb file and thought it might be nice to be able to look at the data. I don't know if there's a user interface that can open and look at a sqlite database and see the tables. I know how to get a list of fields and indexes from a sqlite table; it's just nice to be able to see the data in the database for a n00b like myself 🙂

[keen minnow]

That may sound old school, but I am used to use the repl for these things

like in the case of sqlite, there is a command line tool to open the files

[wicked flax]

I recently saw something about what repl is, and of course I've slept since then so I've forgotten...

I'm not working with a large set of data so it's easy enough to spit it out to Excel for a peek.

[keen minnow]

https://www.sqlite.org/cli.html

it's just an interactive terminal where you can write your select statements (among other things)

[wicked flax]

I'm so lazy... I just set the thing up in Access and create the queries there and copy the SQL over to python. Works perfect! Please don't hate me lol'

[keen minnow]

lol whatever floats your boat.
There is no wrong way

[mellow delta]

Is putting the two different teams breaking first normal form?

[wicked flax]

Based on what I can read what the first normal form is... I would say no. I would wait for a second opinion tho. There's no repeating data unless a team could somehow play itself?

What is 'Outcome' vs 'Winner'?

[mellow delta]

outcome is like played, abandoned.. winner is winning team

[wicked flax]

I tend to think in "final" form, so what I'm seeing is a many-to-many relationship between teams and matches. I'm pretty sure that's beyond the first form tho.

I was looking at this: https://www.tutorialspoint.com/sql/first-normal-form.htm

[mellow delta]

what im unsure about is repeated groups

1NF

[wicked flax]

Well from that example I linked to, I would still say no. Because there's no specific details about the teams that are stored in that table. Just the name of the team, which could link back to a tblTeams where you have info about the teams.

I think a repeating group would be like if you had the teams' hometown in that table or something.

[mellow delta]

This for example breaks 1NF

so theres not a massive difference between course titles and my teams

[wicked flax]

Yeah. Because the marital status is a detail about the person.

I think you have it correct.

[mellow delta]

heh?

it breaks 1NF because of repeating groups, which are the course titles

[wicked flax]

Well, like I said, I'm not used to thinking in terms of the normal forms. I'm used to thinking first about subjects. So that table there, I'm thinking it's the classes someone would be taking. I see a limit of three classes, so what happens if they take four? So that's a many to many relationship between courses and people. And a person's marital status is a detail about people so there would be no reason to mix that into that table.

I still think your Table 3 is good.

[mellow delta]

yeah ok thanks

[small iris]

Hello guys 🤠 i have SQLite Data which i took them from a old Huawei phone, i would like to read them, does anyone have an idea how i can read them outside a phone⁉️🤔

[paper flower]

If you just need some kind of ui you could use https://sqlitebrowser.org/

[strange pulsar]

yes very

[nimble sun]

Servus! When I have 2 db models in my app that means I have two separate databases?
or does it mean that two tables are stores in the same db?

[paper flower]

Two tables in same db 👍

In most cases your application only needs to use single sql database

Model is a mapping from python class to a table

[slender atlas]

From tkinter.tix's docs:

Deprecated since version 3.6: This Tk extension is unmaintained and should not be used in new code. Use tkinter.ttk instead.

[quartz island]

Hello, I'm learning about MySQL and Im really a beginner. Im following a crash course video on YT by mike dane and doing leet code questions along the way. In a recent lecture mike had us set up a simple company database that looks something like this.

QUESTION: I was wondering if I can try to figure out how much is spent by the clients on each branch i.e (in the works_with table) each employee makes some sales, and each employee works for some branch. So in total how much does each branch make?

I just managed to group the total_sales by employee, so I know how much each employee makes, but I don't know how to link this with the branch table.

SELECT emp_id, SUM(total_sales) AS 'Total Sales' FROM works_with
GROUP BY emp_id
ORDER BY total_sales DESC;

Appreciate any help. Thanks.

[vapid hawk]

anyone had any experience with pyodbc and microsoft access? im trying to access data inside an old database, but i get this error

my code

import pyodbc

conn_str = r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\work\python\random\data.mdb'
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

cursor.execute("SELECT * FROM user")

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

pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Access database engine cannot find the input table or query 'user'. Make sure it exists and that its name is spelled correctly. (-1305) (SQLExecDirectW)")

[nimble sun]

You right! I always thought that a db means 1 table. Finding now that it can actually store tables changes my perception of what you can actually do with one. (P.S. never worked with databases)

[woven dragon]

Does SQLAlchemy safely open & close DB connections?

[civic cargo]

it tries to

[woven dragon]

God I hate it

[civic cargo]

it probably works fine in sync apps, but using their AsyncSession has given me so much trouble that I refuse to use it anymore

sqlalchemy works well as a query builder (+ alembic for migrations is ok), but I strongly prefer to interface with e.g. asyncpg directly and pass it queries built with sqlalchemy

it's fairly simple to build an uow/transaction abstraction that binds them together and returns custom dataclasses/attrs/pydantic instances for domain representation

using AsyncSession, I'd sometimes have the app run fine for an hour and then crash because a connection with an open transaction was returned to the conn pool and reused by another task, and those problems are beyond annoying to debug, because no traceback can help you

eventually I just gave up; I wouldn't dare claim that these issues are caused by bugs in the library, but I feel like the orm's interface was conceived before async was a concern, and it had to be bent a lot to make async an option

as a result, it's too easy to write code that causes nightmare issues with connection pooling & transaction management

[paper flower]

Never had any problems with async sessions

[civic cargo]

maybe u are just a better programmer

[wicked flax]

I'm working on a production forecast. The way the forecast is developed is we take the current monthly average daily production value for a flow meter and multiply it by a standard percentage value depending on how long that meter has been in service. So for example, if the meter has been in service for 24 months, the decline curve would be applied against current production number starting at month 25. The forecast always extends out 48 months. The decline curve goes out 10+ years. The issue I'm having is how to create some kind of offset to grab the next value out of a joined table, if that makes sense?

The declineRate is cumulative so the value reflects how much the volume would have declined by month 'X'.

I'm wondering if this can be done via SQL? I currently have it working in a python script that involves creating two dataframes, very manually, and merging those dataframes into a final table. It's really messy and actually very slow, and I'm trying to recreate it in a much more clean and maintainable format.

Here's how the tables/queries are setup:

qryMeterList: meter(pk, string), monthly_vol(decimal), meterAge(date) <- the date it's first online
This is a list of the meters with the meterAge being a calculated value in that query. I'm just whittling things down to the bare minimum for the question here.

**tblDecline **: ID(pk, autonumber), monthNum(integer), declineRate(percentage)

The problem I'm running across is how to offset the meterAge by 1 to get the next declineRate value for the subsequent month?

Current query:

SELECT qryMeterList.meter, qryMeterList.meterAge,
[cumulative]*[monthly_vol] AS m1_vol,
[cumulative]*[monthly_vol] AS m2_vol, *how do I offset cumulative by one month?
[cumulative]*[monthly_vol] AS m3_vol, *how do I offset cumulative by two months? And so on, and so on...
FROM qryMeterList LEFT JOIN tblDecline ON qryMeterList.meterAge = tblDecline.monthNum;

Between typing that up I think I realized some other things to search for, and I think what I need to do is to add LIMIT and OFFSET, I think? MS Access doesn't have those functions available, however.

[drowsy flame]

Hi, can someone help me with sqlalchemy?

stmt = db.select([punishments]).where(punishments.c.Discord_Name == "CoCaptainBlue")
A = connection.execute(stmt)

I want to get and display one of the records from the database. I can run this code without errors, but I don't know how to print the strings etc.

[drowsy flame]

I literally give up lol, getting help with databases is not easy and they are so difficult to learn

ima literally use a text file instead, it's so much more easier

[clear stirrup]

ok

[paper flower]

https://docs.sqlalchemy.org/en/14/orm/queryguide.html#select-statements

[fluid zinc]

How can one query partitioned table using sqlalchemy from Postgres SQL

[tardy portal]

wat the hek is a database

[wicked flax]

I'm a bit confused with something; please forgive my beginner questions!

I am sending a query to a Microsoft SQL Server. I have this working right now but I'm curious if the "system" I've implemented can be simplified a bit. My understanding is that first a connection is made from python to the 'Server. I'm using SQLAlchemy to do this. My question is, can SQLite3 create a connection with a Microsoft SQL Server?

The reason I'm asking is I want to settle on one "platform", if possible. What I'm doing is I'm pulling data from that Server and from Excel and creating a local db file for processing/queries/etc. I'm using SQLite3 to create/manage/query that local file.

Thanks for your help!

[wise goblet]

SQLite3 is a database driver/type, like Microsoft SQL Server or like Posgresql or like MySQL
MySQL can't communicate with Postgresql xD

SQLite3 is a database stored locally in your filesystem. Has some limitations but kind of useful

[wise goblet]

In order to make connection with Microsoft SQL Server u need to find library that uses Microsoft SQL Server driver for connection
(Check if SQLAlchemy supports working with such drive also)

https://learn.microsoft.com/en-us/sql/connect/python/python-driver-for-sql-server?view=sql-server-ver16
microsoft promises support of pyodbc at least library

According to SQLALchemy docs, MS-SQL is supported
https://www.sqlalchemy.org/features.html#:~:text=Supported Databases,of which support multiple DBAPIs.

[wise goblet]

What do u mean, u want to settle on one platform if possible?

[wise goblet]

🪄 🪄 🪄 Whooosh! 🪄 🪄 🪄
Magical fairy from anime explains it

P.S. If to say shortly, databases provide centralized storage of data in some comfortable way. Usually they provide it accessable over network way, so we could have data being separate from applications.
Usually under databases we assume SQL databases first, because they are the most amazing and ensuring data integrity. We are promised to have data structured in the way we defined, and it will lead to errors if someone will try to break our rules.
Really good for data scaling

Key piece technology for implementation of server side applications / web infrastructures / web sites/blogs / game servers and etc. Anything that is online and multi person, that has data needed to be kept at server side

[wise goblet]

🤣

[wise goblet]

https://github.com/darklab8/darklab_darkbot/blob/d87da80630bae47624b51f7cf296675b73f0198b/scrappy/bases/storage.py#L79
try just iterarting result in order to see what u get

for row in A.all():
  print(row)

U can get any first result with A.first() i think

[delicate field]

scrappy/bases/storage.py line 79

bases = BaseQuerySet.from_many_rows_to_schemas(db_rows)```

[wise goblet]

grabbing offset rows feature is avaiable in at least PostgreSQL syntax
https://www.postgresqltutorial.com/postgresql-window-function/postgresql-lead-function/
https://www.postgresqltutorial.com/postgresql-window-function/postgresql-lag-function/
as functions out of windows functions set
https://www.postgresql.org/docs/current/functions-window.html

Not promising that it exists in other database types

[light crescent]

code to add data to the next empty line in a dataframe in pandas?

please

[wise goblet]

panda is not database. It is #data-science-and-ml instrument, and just tool for.... C performance optimized calculations... with usually math
Technically at some stretch it is #algos-and-data-structs too

[light crescent]

oh my bad sorry

[wise goblet]

Never had a problem with it too yet.
i just wrote comfortable context manager that does it for me automatically
https://github.com/darklab8/darklab_darkbot/blob/d87da80630bae47624b51f7cf296675b73f0198b/configurator/channels/storage.py#L42

async with self.db.get_async_session() as session:
  # my any code

https://github.com/darklab8/darklab_darkbot/blob/d87da80630bae47624b51f7cf296675b73f0198b/utils/database/sql.py#L86
No matter what happens, my good old written finally should ensure it will happen

    @asynccontextmanager
    async def get_async_session(self) -> AsyncGenerator[AsyncSession, None]:
        try:
            connection = AsyncSession(self.async_engine)
            yield connection
        finally:
            await connection.close()

Same as for sync connections

    @contextmanager
    def get_core_session(self) -> Generator[Session, None, None]:
        with Session(self.engine, future=True) as session:
            yield session

Coming to thing of it, i should check if AsyncSession is already having implemented with syntax too And to fix a bit incorrect typing perhaps

[woven dragon]

Yep, I decided to do that same thing!

[glossy cradle]

I just wanted to know more on 2 phase commit implementation in python (sqlalchemy or psycopg) but couldn't found any reference links. Any reference links/github repo would be super helpful.

I have looked into documentation but found method names with not much extra info.

[paper flower]

async with async_sessionmaker() as session:
    ...

[wise goblet]

🙂

[paper flower]

You can even automatically begin transaction if you want:

async with async_sessionmaker.begin() as session:
    ...

[quick chasm]

hi there,

lower_education = df[~(df.education == 'Bachelors' ) ] & df[~(df.education== 'Masters')] & df[~(df.education== 'Doctorate')]  

i want to filter some stuff in pandas, i want lower education to show all rows which do not own one of the above strings (bachelors,...)

[storm mauve]

you've probably figured by now but just in case, with that approach it would bepy df[(df.education != 'Bachelors') & (df.education != 'Masters') & (df.education != 'Doctorate')] but I think that you can just ```py
df[~df.education.isin(('Bachelors', 'Masters', 'Doctorate'))]

side note: you might want to use df['col'] over df.col, it's clearer that you are referring to a column (not a method/attribute) and supports column names that would break when accessing via .

!d pandas.Series.isin

[delicate field]

pandas.Series.isin

Series.isin(values)```
Whether elements in Series are contained in values.

Return a boolean Series showing whether each element in the Series matches an element in the passed sequence of values exactly.

[storm mauve]

oh, also that's more of #data-science-and-ml than #databases

[quick chasm]

thanks mate

[nimble sun]

How do you guys manage tinyint with MySQL and flask SQL alchemy. I know that by assigning a boolean i get to write tinyint in db.
Is there another way I can create a tinyint in SQL alchemy?

[wise goblet]

There is page for SQLALchemy dialects with other databases
https://docs.sqlalchemy.org/en/14/dialects/mysql.html
TinyInt is mentioned there
https://docs.sqlalchemy.org/en/14/dialects/mysql.html#mysql-data-types

from sqlalchemy.dialects.mysql import (
    ...
    TINYINT,
    ...
)

[uneven geyser]

Hey, hoping someone could help me out here. I wanted to know if this could be achive much more easily with python since my other option just don't look too straight forward. I looked into a few languages tried them out and lately I did some html css to understand those. I then wanted to look towards making my first application. But then problems came up how do I save my data recall my tables when I reload that application (defiently should work on a tablet). Here's a reddit I post I posted and the html/css roughly showing the design :
https://www.reddit.com/r/learnprogramming/comments/y72mjd/comment/ista8zx/?context=3
https://rainbow-alpaca-1f2f72.netlify.app/index.html
Thanks for the help already.

[green pier]

This isn't really a #databases question, but have a look at https://anvil.works Its datatables service is a wrapper around the postgresql dbms and has a nice Python api.

[uneven geyser]

Thanks, will have a look 👍

[tardy portal]

ok

[ionic pecan]

I think you can do this using Window functions, notably with a ROWS frame https://www.sqltutorial.org/sql-window-functions/. That allows you to run aggregates on a row and preceding / following rows

and I think what I need to do is to add LIMIT and OFFSET, I think? MS Access doesn't have those functions available, however.
i smell trouble in regards to window function availability

[wicked flax]

Thanks! I will check this out soon!

[bold copper]

Anybody know if there's a way to reconnect to mariadb on error? About once a day I get disconnected from my database and need to restart my program

The error I get is that it got disconnected during query, could be due to my internet going offline. However, the program and database are on the same VM so I don't see why that would cause an issue

[fading patrol]

Proper error handling with try / except should take care of that from the sound of it

[bold copper]

To reconnect is it as simple as mysql.connector.reconnect()?

Or do I have to close and connect again

[graceful oxide]

def config(server,value,mode):
    default = {"config":"detest"}
    data = shelve.open("bot.shlf")
    if mode == "r":
        try:
            return data["config"][server][value]
        except:
            return default[value]
    elif mode == "w":
        temp = data["config"]
        temp[server] = value
        data["config"] = temp
        return("success")
    else:
        print("error")
    data.close()
dict = {"token":"test"}
print(config("token",dict,"w"))

throws this

  File "/usr/lib/python3.8/shelve.py", line 111, in __getitem__
    value = self.cache[key]
KeyError: 'config'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "text.py", line 19, in <module>
    print(config("token",dict,"w"))
  File "text.py", line 11, in config
    temp = data["config"]
  File "/usr/lib/python3.8/shelve.py", line 113, in __getitem__
    f = BytesIO(self.dict[key.encode(self.keyencoding)])
KeyError: b'config'

[harsh pulsar]

you might want to use the with shelve.open() form instead: https://docs.python.org/3/library/shelve.html#shelve.open

[harsh pulsar]

the problem is that 'config' isn't a valid key in data. that's what KeyError means

[graceful oxide]

so i just need to create that first

that makes sense

because it was working earlier

and then i cleaned stuff up

and i removed the test list i was using

k thx

[marsh pier]

def get_stats(user_id):
      con = sqlite3.connect('db.db')
      cur = con.execute("SELECT Constitution,Max_Constitution,Dexterity,Intelligence,mana,Strength,floor,class FROM Character WHERE user_id = ?", (user_id,))
      stats = cur.fetchone()
      cur = con.execute("SELECT floor_number,Floor_Boss_Damage,Floor_Boss_Health FROM Floor where floor_number = ?", (stats[5],))
      floor = cur.fetchone()
      return stats,floor

I have this fuction
and on another class I do ```
def init(self,author) -> None:
super().init()
self.value = None
self.author = author
self.player,self.boss = get_stats(author)
etc....
self.boss[2] = self.boss[2] - self.damage

self.boss[2] = self.boss[2] - self.damage
TypeError: 'int' object is not subscriptable
and I get problem with tuple conversion... how could I fix this?

[harsh pulsar]

self.boss appears to be an int, not a tuple. this it's unclear why, because get_stats does return a tuple for the 2nd argument. apparently you messed with it in the etc part.

that said, you cannot modify a tuple. if you need to do so, consider converting it to a list. or in this case, consider using a dict instead, with keys being the column names

[marsh pier]

if I show the etc part

it might be better

I assume

[harsh pulsar]

def get_stats(user_id):
      con = sqlite3.connect('db.db')

      cur = con.execute("SELECT Constitution, Max_Constitution, Dexterity, Intelligence, mana, Strength, floor, class FROM Character WHERE user_id = ?", (user_id,))
      stats = cur.fetchone()
      cols = [c[0] for c in cur.description]
      stats = dict(zip(cols, stats))

      cur = con.execute("SELECT floor_number, Floor_Boss_Damage, Floor_Boss_Health FROM Floor where floor_number = ?", (stats['mana'],))
      floor = cur.fetchone()
      cols = [c[0] for c in cur.description]
      floor = dict(zip(cols, floor))

      return stats, floor

if you do this, at least you will get two dicts instead of two tuples

[harsh pulsar]

!paste of course.

[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.

[harsh pulsar]

read above if the code is too big to share in a code block

[marsh pier]

https://paste.pythondiscord.com/kodemaqaxa

code continues but the other part is irrelevant

since its other buttons etc

[harsh pulsar]

the code you posted doesn't correspond to that file

however i see self.boss[2][0]

[marsh pier]

ye I was trying different

methods sry about that

but I tried both ways

[harsh pulsar]

self.boss is a tuple. self.boss[2] is a number. so what do you expect self.boss[2][0] to be?

(hint: nothing, it's an error to use [] on a number)

[marsh pier]

TypeError: 'tuple' object does not support item assignment```

[harsh pulsar]

right, that's the error i expected

i explained this already. you cannot assign to a tuple. that's why i suggested using a dict instead

[marsh pier]

and where should I pass it

inside class

I assume?

sry for dumb questions btw

Found it

tysm

[trail arch]

hey how to convert this single datafeame into three columns

for some reson after group by it shows like this

[minor zodiac]

Pandas dataframe?

[trail arch]

yup

[minor zodiac]

I might be able to help

Show the output and the desired output with a sample data

[trail arch]

this is dataframe it comes under one column wanna slpit it in three

@minor zodiac

[minor zodiac]

What is coming under one column tho?

[trail arch]

this whole dataframe as shown

[minor zodiac]

That's the original dataframe?

[trail arch]

nope result after group by

[minor zodiac]

Seems like a grouped one to me

[minor zodiac]

Ah okay

First show the original dataframe

Just share like the first 20 rows or so

These are three columns btw

[trail arch]

yup they just come under one column

somehow

afte grouping

like if i try to use df[:work_year"]

it gives me akey error

[minor zodiac]

How many total columns do you have?

[trail arch]

11

why I just need these three

[minor zodiac]

Okay so just make a temporary dataframe with those three columns and share the first 20 rows as a code here

That way I'll be able to copy it and use

[trail arch]

ah never mind got it

[minor zodiac]

Ah wait

Thsoe two columns are as index

Just while grouping pass "as_index = False"

@trail arch

[trail arch]

thanks man

[rain pike]

can you tell me what isolation level to choose for transactions? Maxim transfers money to Ivan.

BEGIN
UPDATE balance SET money = money - 100 WHERE name = 'max'
UPDATE balance SET money = money + 100 WHERE name = 'ivan'
END

[bold copper]

I've been looking into using a temporary table in mariadb, the docs say that they're only available until the end of session. So is end of session when I disconnect the cursor or close the connection?

[proven arrow]

Session is created and destroyed when you connect and disconnect.

[bold copper]

Hmm alright thanks

[tall iris]

hi folks! I just published a blog post showcasing how to use DuckDB to analyze 4.6+ million mentions of climate change on Reddit, hope you like it 😊 https://www.orchest.io/blog/sql-on-python-part-1-the-simplicity-of-duckdb (You can upvote it on Reddit in you happen to have an account there, posted it to r/Python)

[gusty tapir]

I code sql on notepad , in college

[harsh jackal]

hey

[green pier]

does your subquery run by itself ok?

[harsh jackal]

what

[green pier]

if you take the code for your subquery and execute it, does that work ok?

[harsh jackal]

yeah

it does

i used the sqlite studio

to run it

and it works perfecttly

[green pier]

then I can't see what's wrong with your query either

[grim vault]

How about:

SELECT * 
  FROM bookjobs
 WHERE (SELECT COUNT(DISTINCT vendor_id) FROM pos WHERE pos.job_id = bookjobs.job_id) > 1

That's without group by.

[harsh jackal]

Thanks I’ll try that

[sudden berry]

I went in RAW to understand the under hood of things on SQLAlchemy and I found how remarkable it is to work with even at its basis.

my journey to SQLAlchemy

https://dev.to/spaceofmiah/database-interaction-with-sqlalchemy-raw-dml-dql-4bc8

[tawdry finch]

Hi! Im creating a video game and Im working on a save system.
Im trying to write all the username in the line of a specific account name here Papitaku_RL
But here's the problem, thats return just the first one...

This is my program :

[grim vault]

.fetchone() only returns the first row, use .fetchall() to get all of them.

[tawdry finch]

Ok that was that but it wrote it like that :

[grim vault]

You'll also need to use name[0] or for (name,) in ...

[tawdry finch]

Perfect 👍🏻

[grim vault]

There are also f-strings in python, eg print(f"{num}. {name}")

You are also setting num = 1 inside the for loop, you need to do that outside, or all the names are number one.

[harsh jackal]

Thank you so much

it worked

[bold copper]

whats the best way to frequently open and close a mariadb connection? i currently have mysql.connector.connect(...) at the beginning of my code but in order to utilize temp tables i need to close the connection when im done with the temp table. i just tried to copy/paste the connect and cursor connection at the beginning of all my execute statements and conn.close() at the end of them, but then my database wasnt updating like it was before so i changed it back

[harsh pulsar]

don't! create a connection pool at the beginning of your application, and acquire connections from the pool as needed.

but at minimum you will want to use a context manager instead of calling .close explicitly

[bold copper]

whats the best way to accomplish this

[harsh pulsar]

!d contextlib.closing

[delicate field]

contextlib.closing

contextlib.closing(thing)```
Return a context manager that closes *thing* upon completion of the block. This is basically equivalent to:

```py
from contextlib import contextmanager

@contextmanager
def closing(thing):
    try:
        yield thing
    finally:
        thing.close()
```...

[harsh pulsar]

what mysql library are you using? a lot of database libraries support connection pooling out of the box

[bold copper]

mysql.connector

[harsh pulsar]

(i haven't used mysql in years so i am honestly going to just check the docs)

[bold copper]

ive tried i just dont know the right terminology to get the answers im looking for lol

[harsh pulsar]

"connection pool" or "connection pooling" is your search term

https://dev.mysql.com/doc/connector-python/en/connector-python-connection-pooling.html

[bold copper]

yea i have the page pulled up rn lol

[harsh pulsar]

from contextlib import closing

pool = mysql.connector.pooling.MySQLConnectionPool(
    pool_name = "mypool",
    pool_size = 3
)

def fetch_data():
    with closing(pool.get_connection()) as conn:
        ...  # query here

[bold copper]

so the pool size is how many connections i can have running around the code at once?

[bold copper]

closing just closes the connection for you at the end instead of conn.close()?

[harsh pulsar]

!d contextlib.closing

[delicate field]

contextlib.closing

contextlib.closing(thing)```
Return a context manager that closes *thing* upon completion of the block. This is basically equivalent to:

```py
from contextlib import contextmanager

@contextmanager
def closing(thing):
    try:
        yield thing
    finally:
        thing.close()
```...

[harsh pulsar]

indeed

[bold copper]

sweet

[harsh pulsar]

yes, although you'll have to check the library docs to see what happens if you exceed that limit

"good" async libraries will block until a connection becomes available

mysql connector/python is sometimes weird, and for all i know they raise an exception. you'll have to dig around in the docs

[bold copper]

yea ill check it out. thanks for the help

[bold copper]

If a pool is exhausted, a PoolError is raised. good call lol

[harsh pulsar]

yuck, you'll need to poll for a connection to become available 🤢

you could also use the "implicit" pool style, or just create a single connection at the top of your application and re-use that

[bold copper]

orrrrr i just set the pool number high enough to avoid that lmao

[nocturne cloud]

For Discord Account ID, should I use integer datatype or text? I know that integer does not account for leading 0's, so I'm wondering if that's going to be a problem

[paper flower]

bigint

[rugged field]

Hey guys! i got some warning really annoying me in my notebook but i cant seem to fix it :
no matter what i try it always pops up when i try to add a new column:

like it does create the column as i told him to but this warning keeps poping up

Even in pandas doc that's how they create new columns

nvm i found the answer!

[bold copper]

Can I use conn.commit() somehow with mariadb connection pools? Can't find any resources online to give me an answer and for some reason I can't use autocommit

[bold copper]

I think I have the pooling working as it should aside from any updates I do aren't committed to the database and It tells me I can't use commit() and I've tried just about everything to try and turn on autocommit with no results

[harsh pulsar]

i would've expected that to work. try just using a single shared connection for now?

if you can construct a minimal reproducible example, it would be a good stackoverflow question

[bold copper]

I'll work on that when I can get to my computer, working from my phone at the moment lol

If I just use the mysql.connector with no pooling it works as expected but then I go through all the pooling stuff and I can't commit anything it seems. Idk. I'll dig for more errors in a bit I just wasn't sure if someone else has had this issue or what

[bold copper]

I changed from mysql.connector to mariadb and I can now use .commit() inside pool connections and everything is back to working order.

[harsh pulsar]

yeah like i said, that mysql connector/python library is a little weird

you'd think that it'd be better because it's "official"

but it's actually kind of bare-bones

[bold copper]

Yea the mariadb one seems to be better from what I'm reading

[harsh pulsar]

maybe it's faster than the alternatives, but i haven't benchmarked it and i don't use mysql/mariadb often enough to worry about it

there might also be some subtle incompatibility between the mysql client and the mariadb server

[bold copper]

That's what my thought was and why I switched it to test

Because technically mariadb ≠ mysql

[prisma drift]

ignore the fact that this is supposed to be done in a programming language. how do i get the creation date from the deleted object?

DROP TABLE IF EXISTS accounts_table;

CREATE TABLE accounts_table(id INTEGER PRIMARY KEY, creation_date STRING, username STRING, info INTEGER, leave_date);

CREATE TRIGGER account_leave AFTER DELETE ON accounts_table
    BEGIN
        INSERT INTO accounts_table(creation_date, username, info, leave_date) VALUES(OLD.creation_date ,"OGSneakybot", 1, DATETIME('now'));
    END;

INSERT INTO accounts_table(username) VALUES("OGSneakybot");

SELECT * FROM accounts_table;

DELETE FROM accounts_table
WHERE username = "OGSneakybot";

SELECT * FROM accounts_table;

[bold copper]

I think you'd have to log the datetime upon creation the same way you did for leave date

[prisma drift]

DROP TABLE IF EXISTS accounts_table;

CREATE TABLE accounts_table(id INTEGER PRIMARY KEY, creation_date STRING, username STRING, info INTEGER, leave_date);

/*info is to check if the account is in the server*/

CREATE TRIGGER new_account AFTER INSERT ON accounts_table
    BEGIN
        UPDATE accounts_table SET creation_date = DATETIME('now') WHERE username = "OGSneakybot" AND info != 1;
    END;

/* This assumes that the variable name is passed in.*/

CREATE TRIGGER account_leave AFTER DELETE ON accounts_table
    BEGIN
        INSERT INTO accounts_table(creation_date, username, info, leave_date) VALUES(OLD.creation_date ,"OGSneakybot", 1, DATETIME('now'));
    END;

INSERT INTO accounts_table(username) VALUES("OGSneakybot");

SELECT * FROM accounts_table;

DELETE FROM accounts_table
WHERE username = "OGSneakybot";

SELECT * FROM accounts_table;

[bold copper]

Does it work?

[prisma drift]

no. the OLD.creation_date is just a null value

[bold copper]

Well the old creation date is already added above in the new_account trigger so there's no need to insert it again when they leave

[prisma drift]

yeah but is there a way to?

[bold copper]

INSERT INTO accounts_table(username, info, leave_date) VALUES("name", 1, DATETIME('now'))

[prisma drift]

this doesnt work because the account is being deleted

I'm trying to figure out if i can get values in A TRIGGER AFTER DELETE statement

[bold copper]

Oh I see. You won't be able to pass in account info if the account is no longer there is what you're saying

[prisma drift]

Right

[bold copper]

So when they join add their account info to the table then when they leave just add datetime

And change the info to 0

[grim vault]

Well, a DELETE TRIGGER should have a valid OLD reference.

[prisma drift]

Do you know how to pull info from an OLD obj?

[prisma drift]

Right but this is more so for learning how to pull an object

oh oops sorry for ping

[bold copper]

Ah ok. Past my knowledge lol

[prisma drift]

all g

[grim vault]

DROP TABLE IF EXISTS accounts_table;

CREATE TABLE accounts_table(
  id INTEGER PRIMARY KEY,
  creation_date TEXT DEFAULT CURRENT_TIMESTAMP,
  username TEXT,
  info INTEGER,
  leave_date TEXT
);

/* This assumes that the variable name is passed in.*/
CREATE TRIGGER account_leave
  AFTER DELETE ON accounts_table
  BEGIN
    INSERT INTO accounts_table(creation_date, username, info, leave_date)
    VALUES(OLD.creation_date , OLD.username, 1, DATETIME('now'));
  END;

INSERT INTO accounts_table(username) VALUES("OGSneakybot");
INSERT INTO accounts_table(username) VALUES("Berndulas");

SELECT * FROM accounts_table;

DELETE FROM accounts_table
 WHERE username = "OGSneakybot";

SELECT * FROM accounts_table;

id    creation_date    username    info    leave_date
2    2022-10-21 12:58:57    Berndulas        
3    2022-10-21 12:58:57    OGSneakybot    1    2022-10-21 12:58:57

OGSneakybot had id 1 got deleted and reinserted with new id and leave_date.

I added DEFAULT CURRENT_TIMESTAMP to the creation date so that it will have a value if you only do a INSERT INTO accounts_table(username) VALUES("OGSneakybot"); otherwise it would be NULL because that's the default value if the column is not specified in an INSERT.

If you want to keep the old id you can add it in the trigger:

CREATE TRIGGER account_leave
  AFTER DELETE ON accounts_table
  BEGIN
    INSERT INTO accounts_table(id, creation_date, username, info, leave_date)
    VALUES(OLD.id, OLD.creation_date , OLD.username, 1, DATETIME('now'));
  END;

[prisma drift]

Wow thanks. this is really helpful!

[grim vault]

You can also not delete the row but just update it, like:

/* This assumes that the variable name is passed in.*/
CREATE TRIGGER account_leave
  BEFORE DELETE ON accounts_table
  BEGIN
    UPDATE accounts_table
       SET info = 1, leave_date = DATETIME('now')
     WHERE accounts_table.id = OLD.id;
    SELECT RAISE(IGNORE);
  END;

[prisma drift]

Yeah ik. I was really just trying to learn to use the OLD statement properly. Thanks again.

[soft patio]

I've never really worked with SQL databases. Only NoSQL. How would one go ahead and recreate a structure like that in SQL DBs?

[fading patrol]

You could have an ingredients table with those three fields, it should be very simple

Ah, but is that a recipe?

[soft patio]

I should send the entire document

[fading patrol]

That helps, yes...

Read about "many to many" relationships. You'd have an intermediate table linking recipes with ingredients. Not very intuitive at first but simple once you get the concept

[soft patio]

My only SQL experience is what I had in school where we were told "many to many bad". Is that just an approach to not overwhelm beginners or a mistake?

oh I haven't read your reply properly

[fading patrol]

I have no idea why someone would say "many to many bad". But if you don't want to normalize you can store json inside SQL

[soft patio]

What we did was what I think it is you are saying. Getting rid of the many to many relationship by having a table that links both tables therefore creating multiple 1 to many scenarios

Is that what you meant?

basically I have a table "ingredients-of-recipes" which contains the foreign key of the recipe and of the used ingredient in combination with the amount and measurement

[fading patrol]

I think I understand... This is still many to many but the correct way to handle it, yes

[fading patrol]

Yes, you get it

[soft patio]

What about the instructions? How do I store an array of strings? Using an extra table with a foreign keys seems overkill. Or is that the correct approach?

[fading patrol]

I believe it's considered the correct approach if you're going to fully normalize, but it can be ok to store an array in a JSONB field if you want to

Not sure which DBs support that, Postgres does

[soft patio]

Yea I read about Postgres doing that. I think I should fully normalize though. Good practice for my exam.

Thanks for your help. It was all in front of me but I didn't think of it the SQL way xD

NoSQL is just too convenient...

[paper flower]

Most nosql databases are quite limited compared to sql, the fact that you don't need to have a defined schema is a downside in most cases

[brazen charm]

Don't let mongo give NoSQl databases a bad name!

Most NoSQL DBs are schema based most of the time although they often do not provide things like relations because relations and joins cause a lot of problems at scale and people tend to over use them.

[paper flower]

Well, document oriented dbs

[brazen charm]

Triggers, relations, joins, all lovely until you hit large scale or high throughput and then they're silent footguns

[paper flower]

Thing is - document oriented dbs have it's use cases (for example if you have fully self-contained groups of entities/documents), but most of your data is relational

Also you don't reach such scale in 90% of the cases

[brazen charm]

Most people are probably fine with SQL and postgres yes

And most people are also probably fine with mongo even though imo it's not really a great choice

[paper flower]

People are using nosql dbs/mongo for small-medium size projects where sql db would make more sense
If I didn't use sql for my current project my life would be a pain since I run a lot of analytical queries

[brazen charm]

Man for analytics I definitely would not use traditional SQL DBs 😅

Systems like clickhouse and OLAP DBs just dominate over that. Although clickhouse does do SQL I wouldn't really say it's recommended to be used the same way people use postgres

I.e. JOINs are bad, don't use them in clickhouse 😅

[paper flower]

If you don't have that much data then SQL is fine

[brazen charm]

Sure but analytical loads now days tend to be very time series based or related to a constant ingestion source

[paper flower]

I could export some data for analytics into another system/db if I need to, but postgres does fine for now

Did you try any redis alternatives like keydb or dragonfly? 🤔

[brazen charm]

I've used them yeah, but we generally don't really touch those sorts of DBs anymore. Unless it's unbelievable necessary

Like you would be looking at extreme cases where it'd be used ig.

Mostly because ScyllaDB is a monster and you gain the advantage of your cache + durable disk writes all in one

So not having that additional layer for caching is amazing. Because caching things correctly sucks to do correctly.

If I was to say my top 3 databases to cover basically every use case:

• postgres
• clickhouse
• scylla

[stable ibex]

Does anyone know any free SQL software for beginners?

[waxen finch]

SQLite is a pretty easy database to start with since its file-based, no server setup required

to create and interact with one you can use the GUI program SQLiteStudio https://www.sqlitestudio.pl/, or if you want a command-line shell there's prebuilt binaries on SQLite's official site https://sqlite.org/download.html
(CLI documentation: https://sqlite.org/cli.html)

oh and python has a built-in sqlite3 module as well

there are some significant quirks to know about like dynamic typing and foreign keys being unenforced by default, you can read more about those here https://sqlite.org/quirks.html

[eternal sundial]

I am just to start a project in which we will have lots of time series, we will need to compare data based on week number across last 3 years, but it is no year week number but production week number. There will be around 2000 different variables that will model several processes, which type of database can help better? Time series like AWS timestream or relational database or a NoSQL?

[harsh pulsar]

how often do you need to make these comparisons? how fast do they need to be? 2000 variables seems like a lot, what kinds of comparisons and computations are you actually doing?

i assume you have 2000 individual time series? you might want to just put them into a big relational db table with a column indicating the variable + put an index on it for faster lookups, then do your computations in python. depends on the task though.

if this is weekly data, i assume each time series can't be that big

[eternal sundial]

I think we will record daily measures, then a weekly record, an accumulated record can also be required.

[eternal sundial]

We are going to processed this records from raw data, and graph processed data

[harsh pulsar]

appending to relational table seems fine, since appending a row is cheap in a traditional relational db

[eternal sundial]

Do you recommend use AWS Glue to do our transformations

[harsh pulsar]

not sure about the large number if variables, i think things get weird in really big tables

[eternal sundial]

Thanks

[harsh pulsar]

honestly i am not qualified to answer that

[eternal sundial]

I know we can do all of that using just Python, but it looks as we may need to queue some tasks

[harsh pulsar]

fwiw my current company uses kinesis and timescaledb

but we get data much more frequently than daily

[keen minnow]

Given your use case, you will want to benchmark it.
Timescaledb is an option for timeseries data too. Beyond python, databases can help with indexing and making access faster and more efficient

[nocturne cloud]

Hello! Got a question about DB. I don't understand why after executing, we need to commit. When you execute, does it not execute the command?

[paper flower]

It does, but you need to commit current transaction, it's done this way because you can have multiple operations in single transaction

[nocturne cloud]

Multiple operations in a single transaction? So a commit is considered a database transaction. A single database transaction can have multiple 'actions'? Is that what you're saying? Such as, one update data + insertion new data + deletion of old data all in one commit?

[paper flower]

A classic example is transferring money from one account to another, you would not want such operation to complete partially, right?

[nocturne cloud]

Ah I see. So it's to prevent error in case the operation where the money is taken is completed, but the money to be deposited failed because, say, loss of power

So if it can't do something, it won't do anything..

Is that it?

[paper flower]

Or just any other error, yep

[nocturne cloud]

Ok, now that makes sense

By the way, I only recently learned about normalization, and on my personal discord bot project, I want to try to apply what I learned. Do you think this database structure is normalized?

[paper flower]

For example from your application standpoint you can have one operation, such as creating a user, but it may involve making multiple database queries, you want them to either complete or not affect your db at all

https://en.wikipedia.org/wiki/Atomicity_(database_systems)

[nocturne cloud]

fixed ITEM_ID from INT to TEXT

[nocturne cloud]

Yeah, I understand. Some operations will consist of only doing one thing (e.g. inserting new data), but some others may involve several steps.

[paper flower]

It's not really good to use text for ids in most cases

[nocturne cloud]

I was thinking originally of doing INT, but in the instance where ID may start with leading 0's, won't it affect the result?

[paper flower]

Where are you getting these ids from?

[nocturne cloud]

ACCOUNT_ID? It'd be for Discord Account Id

Like that

331396649228435456

That's my ID

[paper flower]

discord ids are int64 (bigint in sql)

[nocturne cloud]

Right, but I read or watched somewhere a long time ago that they said INT won't take leading 0's. So in the case an ID has a leading 0's, won't that make it impossible to look?

[paper flower]

But why would you need leading zeros?

[nocturne cloud]

If Discord assigns an ID with leading 0's?

[paper flower]

If you need to send it somewhere in that format you could just transform it into a string

[paper flower]

Does it?

[nocturne cloud]

Not sure, but in the case that it happens?

[paper flower]

discord ids are integers, they don't have leading zeros

You don't need that

[nocturne cloud]

I see. Now, do you have any possible reason why using INT is favored over TEXT? If all the thing we do with ID is just match case?

I've changed mine, so don't worry. But I just want to know the concept

For learning

[paper flower]

Generally it's better to use right data type for your columns, ints also would be smaller than string of the same size

[nocturne cloud]

Ah, I see

Ok, aside from that one, which I have fixed, do you see any other improvements that can be made or suggestions?

The structure and stuff

[paper flower]

Seems good to me, maybe use varchar(LENGTH) so your column size is constrained

[nocturne cloud]

Oh, for ITEM_NAME?

[paper flower]

If you know that your strings would be generally shorter than some length you can use varchar

[paper flower]

INVENTORY.ITEM_ID should be bigint

[nocturne cloud]

Is it fine to keep ITEM_DESCIRPTION TEXT because sometimes it can be a bit lengthy?

[paper flower]

Also you should add foreign keys

[nocturne cloud]

Oh yeah, forgot to switch that one

Right, about foreign key, I did learn about that... but implementing it live seems a little bit odd. For example in this case, I cannot see the need for foreign key.

[paper flower]

Also maybe that's just my taste/opinion but you don't have to prefix column names with table name

[paper flower]

Why not?

[nocturne cloud]

Do they, in normal industry, do it my way or your way? Or is it really just preference?

[paper flower]

I think it's just a preference

[nocturne cloud]

For example. If I were to set ITEM_ID as a foreign key to the ITEM table, that can work. However, I set that as primary key since QUANTITY depends both on ACCOUNT_ID and ITEM_ID. If I let that be as normal attribute, that would be a violation to third normal form which prohibits transitive relationship among non-primary key. Isn't that right?

[paper flower]

Some of my coworkers prefix every column with c_

[nocturne cloud]

Ah, ok. Nothing to worry too much then.

Aside from ITEM_ID potentially be a candidate for foreign key, I don't see any other attributes that I can set as foreign key that's not already assigned as primary key.

[paper flower]

What do you mean by "normal attribute"?

[nocturne cloud]

Non primary key attributes

[paper flower]

Well, it should not be a primary key...

[nocturne cloud]

ITEM_ID should not be a primary key?

[paper flower]

quantity should not 😅

[nocturne cloud]

Oh, not quantity. I was talking about ITEM_ID

[paper flower]

Quantity depends on both item and account ids, I don't see why there's a transitive dependency

[nocturne cloud]

Yeah, I probably jumped the explanation. You suggested I use some foreign key. My understanding of the tables I have right now is that there simply is no attribute I can assign as a foreign key. The only one that 'might' be a candidate is ITEM_ID. However, as I explained before, if I were to set that as foreign key, that would make QUANTITY be dependent on an attribute that is not a primary key.

[paper flower]

create table account_items(
  account_id bigint references account(id),
  item_id int references item(id),
  quantity int not null,
  primary key (account_id, item_id)
);

[nocturne cloud]

Therefore, my conclusion right now is that there is no need for foreign key in my tables. Of course, if my understanding is wrong, I am open to learn something

Wait wait, you can set a primary key as a foreign key too?

[paper flower]

Yep

[nocturne cloud]

Huh? I was never taught that

[paper flower]

You can make composite primary key so combination of account_id and item_id is unique within this table, which makes sense

And quantity of an item depends fully on pk

[nocturne cloud]

Right, that part I understand

[paper flower]

If you didn't have foreign keys here I could use account or item id that does not exist

[nocturne cloud]

Right..

[paper flower]

Same applies to other tables

[nocturne cloud]

I see

So even if it's primary key, it needs to reference to original table

Now the next question is this.. ACCOUNT_ID would ideally be in a table like ACCOUNT_INFORMATION where it consists of:

ACCOUNT_ID (PK)
ACCOUNT_NAME
ACCOUNT_AGE
ACCOUNT_NITRO_STATUS
etc.

Basically a table that has all general information about an account. But since I obviously won't collect that, which one should I treat as the 'host' for ACCOUNT_ID?

For ITEM_ID, I know that ITEM_ID in ACCOUNT_INVENTORY will reference back to ITEM table. But for ACCOUNT_ID, with my structure, I am not sure where it should reference back to.

[paper flower]

Yep, that's how you usually implement many to many relationships

You could some general info about account in separate table, also you don't have to create separate tables for, say, economy and level at this moment

It would be harder to work with

It's just more joins on database side and more None checks on client side 🙂

[nocturne cloud]

Ah, the most complicated relationship that I still sometimes struggle

And I am even more confused with junction table (intersection table sometimes)

[paper flower]

You could try learning sqlalchemy if you want to be even more confused

[nocturne cloud]

Oh, no thank you

and thank you for your help @paper flower

[hollow ivy]

Hi, class DynamicArray:
def init(self):

Remarks: you must have variables inside the constructor method, with n=0 and capacity=1 (their default values) first. When n==capacity you should always update to capacity *= 2.
Methods:
len
getitem
settem
appendix
insert: this is my task but I don't understand how to do it

[hollow ivy]

hello I really need help can you help me

[nocturne torrent]

hello! I need to have a table with some fields like: names, descriptions, conditions, additional_info etc, where each of those is an array of strings
my question is: should I create an associative table for each of these fields or a single table and add an additional field that specifies the type (name, description etc.)?
hopefully it makes sense, thanks in advance!

[bold copper]

can i disable mariadb wait_timeout?

or is there a way to re-open a timed out connection? not sure how to handle that error

nvm the max value is like 1 year so ill just increase the value lol

[torn sphinx]

Lol

[tight junco]

how can i exclude specific fields
find_one({"_id": ...}, {"field_1": 0, "field_2": 0})
like this?

[fair niche]

How do i use my database?

Because I bought my own website (.com) and i decided to just add a database and some more things to it but i do not know how to use i tryed python requests as in request.post(value) but it did not work

[paper flower]

You need to use a mysql driver or ORM (which would use that driver underneath)

[fair niche]

I do not know

what you mean

[paper flower]

Are you familiar with sql?

[fair niche]

What do i do?

@paper flower

[paper flower]

?

[fair niche]

no

heard of it

but no

[paper flower]

Learn sql 🙂

[fair niche]

wait..

ima delete that database and show you

@paper flower do not leave me

i will be back

when i go to

databases

[paper flower]

You just should learn sql first

[fair niche]

look

i get a choice

@paper flower

[paper flower]

I does not matter that much

[fair niche]

ok

what do i do

doctoe

doctor

[paper flower]

^

[fair niche]

ok :(

I hope doctors can fix my heart and mind @paper flower

can you do that

[torn sphinx]

hey, i'm currently learning postgres with asyncpg and am using some public utils

    async def execute(self, statement, *params, one_row=False, one_value=False, as_list=False):
        if await self.wait_for_pool():
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cur:
                    await cur.execute(statement, params)
                    data = await cur.fetchall()
            if data is None:
                return ()
            if data:
                if one_value:
                    return data[0][0]
                if one_row:
                    return data[0]
                if as_list:
                    return [row[0] for row in data]
                return data
            return ()
        print("Could not connect to the local asyncpg instance!")

When I try to use execute with something simple like, ,jsk py await bot.db.execute("SHOW TABLES;") I recieve ``` async with conn.cursor() as cur:
File "C:\Users\sorrow\AppData\Local\Programs\Python\Python310\lib\site-packages\asyncpg\pool.py", line 61, in call_con_method
return meth(self._con, *args, **kwargs)
TypeError: Connection.cursor() missing 1 required positional argument: 'query'

A bit lost

[grim vault]

asyncpg isn't python DB-API 2.0 (PEP 249) conform and uses it's own implementation. You would execute directly on the connection object, the .cursor() method would need a sql statement. There is also no .fetchall() it's .fetch() and so on.

[serene flicker]

yo whatsup guyssss big big things going wrong here so uhm lets begin

Code:

Input output things:

Error:

My database:

more error info

pls help

[grim vault]

Your insert is missing the second question mark ... VALUES(?, ?)

[covert wind]

Hai does anyone know SQL here, I would really need some help :/

[harsh pulsar]

don't "ask to ask", state your question in detail and someone will answer if they know the answer & have time

[serene flicker]

aah ok thanku

[serene flicker]

MATE THANKU SO MUCH IT IS FIXED NOW

[covert wind]

Sorry, alright.

Anyone know why I keep getting this SQL error: Error Code: 1146. Table 'new_store.ingredientssold' doesn't exist? ```CREATE VIEW IngredientsSold AS
SELECT ItemsInTransactions.name,
amount_in_stock - COUNT(ItemsInTransactions.name) AS amount_left
FROM ItemsInTransactions,
Items
WHERE ItemsInTransactions.name = Items.name
GROUP BY ItemsInTransactions.name;

CREATE VIEW ItemsLeft1 AS
SELECT Items.name,
Items.type,
amount_left
FROM Items,
IngredientsSold
WHERE Items.name = IngredientsSold.name
AND Items.type IN (1, 2)
ORDER BY Items.type ASC,
Items.name ASC;```

clearly im referencing the view table in the FROM?

[paper flower]

FROM Items,
    IngredientsSold

[covert wind]

yeah :/

[covert wind]

Its what I did

[paper flower]

Well, second table doesn't exist

Maybe you made a typo?

[covert wind]

my ingredients one if the first one

[paper flower]

I'm not sure what might cause such issue

[paper flower]

Looks like mysql

[wild pelican]

Using aiosqlite, how could I add a column to my database and set everything to 0? For example, I store user ids and balance, if I were to add net_worth to the database's column, I need everyone to have 0 automatically.

[paper flower]

You want to migrate existing table?

[wild pelican]

Would that allow me to add net_worth and for 0 and 503641822141349888, they would have 0 in the net_worth column?

[paper flower]

If your table doesn't hold any important data it would be easier to just recreate it 😉
Otherwise you could add nullable column, set all values to 0 and make it non nullable, alternatively you could add a column with default value

[wild pelican]

My table will be holding very important information, would you mind linking me how to add a column with a default value?

[paper flower]

alter table <tablename> add column <column> <column parameters>

[wild pelican]

                await db.execute('ALTER TABLE balance ADD COLUMN net_worth 0')
                await db.commit()
                await db.close()``` just like so?

[paper flower]

default 0

[wild pelican]

Perfect, thank you very much!

[nocturne cloud]

This is account_id. A BIGINT datatype for Discord Account ID. I want to look up this ID from a table. However, the system considers each number to be an 'independent' element, so it thinks I am giving it 18 instead of 1.

[paper flower]

Why are you trying to pass a string when it's declared as int in your db?

[nocturne cloud]

Because it gave me ValueError before.

[paper flower]

Also seconds parameter accepts a tuple, so, execute("...", (account_id, ))

[nocturne cloud]

Yes, I just found out about this literally seconds before you commented XD

[nocturne cloud]

Ok, now that the tuple problem is fixed, for some reason, it doesn't give me ValueError error.

It did give me that at #discord-bots

Anyway, thanks for your help again Doc

[paper flower]

Because it was expecting a tuple

Read the errors

[nocturne cloud]

Yeah, but that was a misunderstanding. When I gave it this, it gave me ValueError. My first guess was that it was expecting a string. So when I converted it into a string, it gave me different error, saying that I gave it 18 instead of 1.

So I thought my first fix was correct, but there was another issue.

[paper flower]

Probably because string is iterable, so it's kind of the same as tuple with 18 elements

[nocturne cloud]

Yeah, now I realize that in python, string is just iterable of char

So probably that's why

Anyway, thank you

[trail cosmos]

Hello

[serene flicker]

guys how can i print the data from a database ?

sqlite database

[serene flicker]

HELP

Code:

@bot.command()
async def getall(ctx):
    db = sqlite3.connect("main.sqlite")

    cursor = db.cursor()

    cursor.execute("SELECT * FROM main")

    result = db.fetchall()

    for row in result:
        print(row)
        print("\n")```


**Error:**
```error
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'sqlite3.Connection' object has no attribute 'fetchall'```

[serene flicker]

ok

[mellow delta]

When shouldn’t I use cascade on foreign keys?

[stark gust]

when you don't need to?

[mellow delta]

Yeah but when wouldn’t you need to

if it’s dependent on another table then i don’t see why you’d ever not use cascade

[sick lion]

Hey ppl, I'm new to mysql and I was wondering how I can check if a ID is in a Table. When it is, it will add a value to it's existing one, if not, it should set it to 0.

[torn sphinx]

i am using asyncpg to work with a postgres db, my one issue is that how do we convert asyncpg record lists:-

[<Record license_key='OLAM1-58QNQ-SIRQY'>, <Record license_key='CKM5F-ERZ7W-CGT70'>, <Record license_key='RDHA3-WEU17-SSOPZ'>]

to regular python lists?

these are all from the same column

import asyncpg
import asyncio
postgres_url = ''

async def run_query(query):
    """
    run a query to postgresql via asyncpg
    """
    conn = await asyncpg.connect(postgres_url)
    return await conn.fetch(query)

stuff = asyncio.run(run_query("select license_key from unactivated_licenses"))
print(stuff)

this is the code

i dont understand how do you convert these asyncpg record lists to regular lists

[grim vault]

Why do you need to? You can access the fields like a tuple or a dict:

new_stuff = [rec['license_key'] for rec in stuff]
# or
new_stuff = [rec[0] for rec in stuff]

[marsh pier]

in SQLite is it possible to store multiple values on one column? like a list? and if yes how?

[fading patrol]

You're probably doing something wrong if you are trying to do that, but here are some workarounds if you're sure you want to: https://stackoverflow.com/questions/9755741/vectors-lists-in-sqlite

[marsh pier]

my idea is for like lets say recipes

soup-recipe, has id name and igredients list

the only way to make it would be with a list no?

[fading patrol]

No... That's called a many to many relationship and you use an intermediate table

[marsh pier]

hmmm

so I should make recipes, igrendients and recipes_ingredients

with FK keys

on recipes_ingredients

[fading patrol]

Right, I think you have the idea

Recent discussion, also related to recipes and ingredients oddly enough: #databases message

[marsh pier]

lmaooo tysm

[wild pelican]

await db.execute('ALTER TABLE balance ADD COLUMN max_bank DEFAULT 0') How could I make it say that the column max_bank is an INTEGER?

Right now, it is blank.

[waxen finch]

it uses the same format as a column definition for CREATE TABLE, so you'd write INTEGER after max_bank

[wild pelican]

await db.execute('ALTER TABLE balance ADD COLUMN max_bank INTEGER DEFAULT 0') would work?

[mighty glen]

if i have INSERT INTO levels (id, xp) VALUES (?, ?) ON DUPLICATE KEY UPDATE xp=xp+?, is it possible to get the value of xp from this query?

[fading patrol]

I'm not sure I understand what this question could mean without more context. It's an INSERT statement so the value is whatever you make it

[mighty glen]

yeah- but it could also update it, and if it does i want to know what it updated it to

Is that even possible without a second query?

[fading patrol]

Ah, I see what you mean now but I don't know. What library is this, sqlite3?

[mighty glen]

this is mysql with the mysql connector

I tried stackoverflow, but it seems this is just a little bit too obscure

[fading patrol]

There might be another way but I would just use a select before inserting

[mighty glen]

after you mean

okay, cool

[fading patrol]

No, before. Select to find out if the key exists, then insert accordingly

[mighty glen]

Why?

[fading patrol]

what else would you do?

[mighty glen]

What i'm doing already

Then just select the current value

[fading patrol]

I see, yeah that's fine

I've literally never used MySQL so someone else may know better

[paper flower]

No, mysql doesn't support returning

I'd consider using mariadb or postgres

[mighty glen]

If only.

[delicate field]

Hey @marsh pier!

You either uploaded a .txt file or entered a message that was too long. Please use our paste bin instead.

[marsh pier]

async def on_submit(self, interaction: discord.Interaction):
        if int(self.quantity.value) < 0:
            await interaction.response.send_message("You can't craft negative items", ephemeral=True)
            return
        async with aiosqlite.connect('db.db') as con:
           cursor = await con.execute("SELECT item_quantity, FK_item_ID FROM item_crafting WHERE item_ID = (SELECT ID FROM items WHERE item_name = ?)",(self.selected_item[0],))
           quantity_item = await cursor.fetchall()
           print("teste2")
           for i in quantity_item:
              cursor = await con.execute("SELECT item_quantity FROM character_items WHERE item_ID = ?",(quantity_item[i][i+1]))
              player_quantity = await cursor.fetchall()
              print("teste3")
           for i in player_quantity:
              if player_quantity[i] < quantity_item[i][i] * int(self.quantity.value):
                 print("teste4")
                 await interaction.response.send_message("You don't have enough items to craft this", ephemeral=True)
                 return
                 
           for i in quantity_item:
              print("teste5")
              await con.execute("UPDATE character_items SET item_quantity = item_quantity - ? WHERE item_ID = ?",(quantity_item[i][i] * int(self.quantity.value),quantity_item[i][i+1]))
              await con.execute("INSERT INTO character_items (character_id,item_ID,item_quantity) VALUES (?,?,?) ON CONFLICT (character_ID,item_ID) DO UPDATE SET item_quantity = item_quantity + ?",(interaction.user.id,quantity_item[i][i+1],quantity_item[i][i] * int(self.quantity.value),quantity_item[i][i] * int(self.quantity.value)))
              await con.commit()
              await interaction.response.send_message(f"You crafted {self.quantity.value} {self.selected_item[0]}", ephemeral=True)

Error: list indices must be integers or slices, not tuple

[paper flower]

It pretty much tells you what the issue is

[marsh pier]

ye but how could I fix

I have been trying everything

I do personally think logic might be wrong

so I posted here to get another person point of view

[paper flower]

You're trying to index (str[something]) string using a tuple, that tuple probably comes from your db

since cursors return tuples

[marsh pier]

mhm

but I use for example quantity_item[i] to change index etc

can it be a problem on "for i in quantity_item"?

forgot to mention ignore the prints I was trynna figure out where problem was

[wild pelican]

How could I alter every single value for a specific column?

(aiosqlite)

[paper flower]

update <table>
set column = value

[wild pelican]

Great, thank you!

[graceful widget]

how do i make database for discord bot

[silent lynx]

Hi all, i am using postgres for making a table and i have a field called application_type. Now the thing is this application_table column can only have a value in the range 1 to 5 (both inclusive). How can i specify this constraint while creating the table?

[paper flower]

https://www.postgresql.org/docs/current/ddl-constraints.html

Probably something like CHECK (price >= 1 and price <= 5)

[silent lynx]

thanks

[whole portal]

Hello 👋
I am making a texting platform using ReactJS for the front end
for the backend what database can I use to update chat with realtime?

Planning to use Django but which Database do I use?

[shut trellis]

Hi, is it possible to use a db file instead of an sql server ?

[wise goblet]

In general for most of your program, postgresql is great, but if it is live message platform, make sure to check Django channels which use in addition Redis as message broker for rapid asynchronous communications

SQL databases should be always chosen first for majority of data for the sake of data integrity, and querying language capabilities

[whole portal]

So I use postgresql

Is it live

[wise goblet]

What do u mean by that

[wise goblet]

Yes if u wish to avoid making not maintainable code and data

[whole portal]

Like there is no delay in msgs

[graceful widget]

can anyone recommend me some tutorial about connecting discord bot to online database

[wise goblet]

Surely there is. All communications over network with databases have. No exceptions

Difference in possible amount of write/read requests per second

[whole portal]

So I trying to create a discord clone for example

With soo many user

I don't want delays and also store msg history

I wanted to know what Is the right way to go

[wise goblet]

Well, that is another kind of task. Very expensive one. Let me drop u answer

[wise goblet]

https://github.com/donnemartin/system-design-primer
This page contains answers

At least part of them

[whole portal]

Hmm thank you I'll go through them

So postegresql

[wise goblet]

U a welcome. To get full picture u will need also learning at least one of big cloud providers like AWS, GCP or Azure

[whole portal]

Hmm alr ty

[wise goblet]

Best possible starting option. Postgresql is very well scalable

[whole portal]

And it's free to start off with right?

[wise goblet]

Postgresql is fully free and open source

U pay only for server hardware resources if necessary

Or for any cloud provider managed versions, taken care by cloud provider to provision and scale. If u want to delegate who is maintaining it

[whole portal]

Oh ok thanks I'll check it out

[wise goblet]

Django ORM is almost out of box meant to interact with it

at least if u install psycopg-binary driver

[whole portal]

Ah ok

[signal rune]

When using the shelve module, when should I close the shelf, and what happens if I don't close the shelf ?
example:

import shelve

database = shelve.open('shelf.db')

def fun(number):
  database[variable] = number
  number += 2
  database[2nd_variable] = number
  database[3rd_variable] = 5
  database[3rd_variable] += 3

[fading patrol]

In your case I would use with inside fun. Example in the docs: https://docs.python.org/3/library/shelve.html

[pulsar hazel]

how do I obtain index (row position) value of a particular name in data a data frame?

been stuck on this for a while

[fading patrol]

"data frame" meaning Pandas? If so, get_loc. https://pandas.pydata.org/docs/reference/api/pandas.Index.get_loc.html

[serene flicker]

HELP

Code:

@bot.command()
async def channel(ctx, text: str):
    if ctx.message.author.guild_permissions.manage_messages:
        db = sqlite3.connect("password.sqlite")
        cursor = db.cursor()
        cursor.execute(f"SELECT password FROM main WHERE name = {ctx.author.id}")
        result = cursor.fetchone()
        if result is None:
            sql = ("INSERT INTO main(name, password) VALUES(?, ?)")
            val = (ctx.author.name, text)
            await ctx.send(f"Pass is set to {text}")
        elif result is not None:
            sql = ("UPDATE main SET channel_id = ? WHERE guild_id = ?")
            val = (ctx.author.name, text)
            await ctx.send(f"Pass is updated to {text}")
        cursor.execute(sql, val)
        db.commit()
        cursor.close()
        db.close()```

**Error:**
```error
, line 119, in channel
    cursor.execute(sql, val)
sqlite3.OperationalError: database is locked```

i fixed this but another error occurred

HELP

Code:

@bot.command()
async def channel(ctx, text: str):
    if ctx.message.author.guild_permissions.manage_messages:
        db = sqlite3.connect("password.sqlite")
        cursor = db.cursor()
        cursor.execute(f"SELECT password FROM main WHERE name = {ctx.author.id}")
        result = cursor.fetchone()
        if result is None:
            sql = ("INSERT INTO main(name, password) VALUES(?, ?)")
            val = (ctx.author.id, text)
            await ctx.send(f"Pass is set to {text}")
        elif result is not None:
            sql = ("UPDATE main SET name = ? WHERE password = ?")
            val = (ctx.author.id, text)
            await ctx.send(f"Pass is updated to {text}")
        cursor.execute(sql, val)
        db.commit()
        cursor.close()
        db.close()```
The update thing shows a message that it is updated but in the database it doesnt update

[ivory turtle]

django querying question:

lets say I have this model setup ```py
class Player(Model):
username = TextField(...)

class SaveSlot(Model):
player = ForeignKey(Player)

And I have a list like this ```py
save_slots = [save_slot_a, save_slot_b, save_slot_c, save_slot_d, save_slot_e]
``` and I would like to access the player for each of these save slot ```py
for save_slot in save_slots:
  print(save_slot.player.name)```
but this is an N+1 query, and requires fetching each player as an individual query - what's the simplest way to optimize this?
`select_related` will not work for me since the save slots have already been baked into a list.

[clear stirrup]

can you do anything before you turn it into a list?

select_related is the way, but you ofc need to do that before the initial query is executed

you could do an extra query afterwards

qs = Player.objects.filter(id__in=[x.player_id for x in save_slots])

and then you'll have to match up each player in this query with the save_slot player_ids

option 2:

• redo the query completely with the join

qs = SaveSlot.objects.filter(id__in=[x.id for x in save_slots]).select_related('player')

@ivory turtle

[grim vault]

This must be the other way around:

        ...
        elif result is not None:
            sql = ("UPDATE main SET password = ? WHERE name = ?")
            val = (text, ctx.author.id)
        ...

You want to set the password for the user.

And as a side note: saving passwords in plain text is a bad idea.

[torn sphinx]

Cam someone plz help my with mysql

[fading patrol]

#❓｜how-to-get-help

[eternal coral]

Hi, im using sqlite3 for my project and im having a problem fetching some data from the database, when i use fetchall() i get empty arrays, any help?

This is my code, i want that for each stored hour checks the matching channels to that hour but when i fetchall i get []

cursor.execute(f"SELECT hour FROM alarm")
hour_result = cursor.fetchall()
for hour_row in hour_result:
  hour = re.sub('[^0-9]', '', str(hour_row))
  cursor.execute(f"SELECT channel_id FROM alarm WHERE hour = {hour}")
  channel_id_result = cursor.fetchall()
  print(channel_id_result)```

the second

srry

i deleted that

i deleted prints and i mistaken that line

@tasks.loop(minutes=1)
async def time_check(self, ctx: commands.Context):
    rn = re.sub('[^0-9]', '', str(datetime.datetime.now().time()))
    time = str(rn[:4])
    print("")
    print("Actual time: " + time)
    BASE_DIR = os.path.dirname(os.path.abspath(__file__))
    db_path = os.path.join(BASE_DIR, "kakapo_database.db")
    with sqlite3.connect(db_path) as db:
        cursor = db.cursor()
        cursor.execute(f"SELECT hour FROM alarm")
        hour_result = cursor.fetchall()
        for hour_row in hour_result:
            print(hour_row)
            print("")
            print("Step 1")
            hour = re.sub('[^0-9]', '', str(hour_row))
            print(hour)

            #if time == hour:
            cursor.execute(f"SELECT channel_id FROM alarm WHERE hour = {hour}")
            channel_id_result = cursor.fetchall()
            print(channel_id_result)
            for channel_id_row in channel_id_result:
                print(channel_id_row)
                channel_id = re.sub('[^0-9]', '', str(channel_id_row))
                print("")
                print("Step 2")
                print(channel_id)

                cursor.execute(f"SELECT message FROM alarm WHERE channel_id = {channel_id}")
                message = cursor.fetchone()
                print("")
                print(message[0])
                print(channel_id)
                #print(guild_id)
                print("Exit")

                channel = self.kakapo.get_channel(int(channel_id))
                await channel.send(message[0])
    db.commit()
    cursor.close()
    db.close()```

('0123',)

that is one stored hour i did for testing

okay

im still getting the empty array

also my db looks like this

ooh

that worked

Tyvm ^^

Should i also remove the regex from channel_id?

Okay ^^

[kindred nova]

help me transfer data from one db to other
python
SQLite to PostgreSQL

[grim vault]

Don't use f-strings, use parameter like: cursor.execute("SELECT channel_id FROM alarm WHERE hour = ?", (hour,))

You may also want to select the hour as distinct value, so you only get each one once. Right now you will get the same value multiple times.

Something like:

cursor.execute("SELECT DISTINCT hour FROM alarm")
hour_result = cursor.fetchall()
for hour_row in hour_result:
  cursor.execute("SELECT channel_id FROM alarm WHERE hour = ?", (hour_row[0],))
  channel_id_result = [row[0] for row in cursor.fetchall()]
  print(f"hour: {hour_row[0]}, channels:{channel_id_result}")

[eternal coral]

Oh tyvm ^^ i was just about to start on that problem

[fading patrol]

What method have you tried? Here's one way, you can search the web for others: https://pgloader.readthedocs.io/en/latest/ref/sqlite.html

[kindred nova]

ok

[hollow notch]

I have some functions which operate on my MySQL database. Each of these functions takes in a cursor object as an argument so it can do its work.

Right now, I first connect to a database in that main.py, create a cursor object and then pass that object as an argument into my functions. Are there any benefits to passing a connection object into the function instead and creating the cursor within that function?

A minimal implementation of both cases:

import mysql.connector
my_conn = mysql.connector.connect(user="root", password="test")
my_cursor = my_conn.cursor()

func1(my_cursor)

def func1(cursor):
  cursor.execute("SHOW DATABASE")
  ...

import mysql.connector
my_conn = mysql.connector.connect(user="root", password="test")

func1(my_conn)

def func1(connection):
  cursor = connection.cursor()
  cursor.execute("SHOW DATABASE")
  ...

[fading patrol]

I'm not super experienced with this but my understanding is that you want to leave connections open (because they're expensive) but can close and make new cursors (because they are cheap). So your first scenario looks better

[hollow notch]

Don't I keep the connection in both cases? The difference is that I create one cursor in the first case which is reused for each subsequent function and that I create a new cursor each time a function is called. Unless I misunderstand my own code. 😅

[fading patrol]

Ah, I see, you're right. Should have looked more carefully. I actually prefer the second one in that case but not sure it really matters

[deep maple]

Hi, is someone know how to regroup this query?

SELECT premium, "char"
FROM premium
WHERE guild_id = 913766363791757353;

SELECT user_id
FROM blacklist_member
WHERE guild_id = 913766363791757353
AND user_id = 640518207257444374

SELECT enable, auto_delete, mode
FROM flag_translation
WHERE guild_id = 913766363791757353 AND channel_id = 913766363791757356```
I have try FULL JOIN without succees, and LEFT JOIN also

[fading patrol]

I don't really follow what your question is. Perhaps you can share what else you tried, what you expected to happen, and what happened instead

[deep maple]

yes sure

I try join

SELECT *
FROM flag_translation
FULL OUTER JOIN premium USING(guild_id)
FULL OUTER JOIN blacklist_member USING(guild_id)
WHERE guild_id=913766363791757353 ```

and I want get one result

exemple

not premium guild, no flag translation set but one user banned

so

[deep maple]

but with that, I see all content

and If I had condition

SELECT enable, auto_delete, mode, user_id, premium, "char"
FROM flag_translation
FULL OUTER JOIN premium ON premium.guild_id=flag_translation.guild_id AND premium.guild_id=913766363791757353
FULL OUTER JOIN blacklist_member ON blacklist_member.guild_id=flag_translation.guild_id AND blacklist_member.user_id=640518207257444374```

If a table is empty, I have no result or strange result

I want the row with the user_id at 640518207257444374

[hollow notch]

I previously had cursor.execute(f"SHOW TABLES FROM {schema}") and rewrote it as cursor.execute("SHOW TABLES FROM %s", (schema,)) but when I run that, I get an 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''database1'' at line 1. I am using mysql.connector. What am I doing wrong? My usage of the prepared statement seems to be in line with pretty much any resource I saw.

[brave bridge]

You can only really substitute "data", not column or table names

Why do you want this?

[hollow notch]

It's not something user-facing but I thought that while I make a habit out of using prepared statements, to just always use them

[brave bridge]

Just check that schema is contained in some whitelist. Although, why do you want to generate such a query dynamically?

[hollow notch]

For printing out for which table the program did its thing

Long term, it will probably not be needed since I will be providing the relevant schema and tables via some configuration file and could just get that info from there

While I figure out the other parts of my program, I just did it like that

Right now, that query is in a function where I input a list of schemas, get prompted to choose some tables and then get a list of table names out of it

That list of table names is then used to create a csv file with the table name + the current maximal value of my primary key (which are datetimes)

[quaint bloom]

Hi friends! I have a problem using the .apply() to a pandas dataframe. What I'm trying to do is something in the lines of:

df.groupby['A','B'].apply(value_counts().value1/(value_counts().value1 + value_counts().value2)

That is, I wan't to get the ratio of value1 when grouping by A and B. My problem is, df.value_counts() works fine, but when I have to put value_counts() in the apply, it does not work. I've just tried ```py
df.groupby['A','B'].apply(lambda x: x.value_counts().value1/(x.value_counts().value1 + x.value_counts().value2)

and it also does not work because some groups don't have value1 or value2.

 What I want is tranforming df1 into df2, following the rule above, where SR is value1 and SL is value2:
```py
df = pd.DataFrame({'Agent':['A','A','B','B','A','A','A','B'],
                   'Month':[1,1,1,1,2,2,2,2],
                   'Value':['SR','SR','LR','SR','SR','LR','LR','LR']})

df2 = pd.DataFrame({'Agent':['A','A','B','B','A','A','A','B'],
                   'Month':[1,1,1,1,2,2,2,2],
                   'Value':['SR','SR','LR','SR','SR','LR','LR','LR'],
                   'Grouping': [1,1,0.5,0.5,1/3,1/3,1/3,0]})

[vocal parrot]

Hey guys! I don't know a lot of servers where I can ask this, so asking here

So i have this super weird bug. I am using tortoise orm (just like django but async) for my sqlite db

This is the giveaway model for my discord bot

class Giveaway(Model):

    msgid = fields.BigIntField(unique=True, null=True)
    guild: fields.ForeignKeyRelation[GuildDB] = fields.ForeignKeyField(
        "models.GuildDB", related_name="giveaways", on_delete=fields.CASCADE
    )
    channelid = fields.BigIntField()
    hostid = fields.BigIntField()
    prize = fields.CharField(max_length=128)
    winner_count = fields.IntField()
    duration = fields.IntField()  # in seconds
    end_timestamp = fields.BigIntField()  # in seconds
    ended = fields.BooleanField(default=False)
    amari_level = fields.IntField(default=0)
    amari_weekly = fields.IntField(default=0)
    required_roles: fields.ManyToManyRelation[RoleDB] = fields.ManyToManyField(
        "models.RoleDB", related_name="required_giveaways"
    )
    bypass_roles: fields.ManyToManyRelation[RoleDB] = fields.ManyToManyField(
        "models.RoleDB", related_name="bypass_giveaways"
    )
    blacklist_roles: fields.ManyToManyRelation[RoleDB] = fields.ManyToManyField(
        "models.RoleDB", related_name="blacklisted_giveaways"
    )
    users: fields.ManyToManyRelation[UserDB] = fields.ManyToManyField(
        "models.UserDB", related_name="giveaways_joined"
    )

when i create a giveaway, if i have required roles, bypass roles, blacklist roles, etc ; I create an entry for them in the db and then add them to the respective relation. For eg-

        if required_roles:
            required_roles_list = await self.parse_roles(
                required_roles, ctx.guild, as_db=True
            ) # this is a function i made that creates and returns me the db objects 

            await gaw.required_roles.add(*required_roles_list)

The issue is,** the roles get added to bypass roles and blacklist roles too** for some reason. This is breaking my code logic. Help appreciated

[harsh pulsar]

(this question is on-topic here, but i don't know the answer)

[vocal parrot]

Oh nice. It's fine you don't know lol I'll wait for someone

[serene flicker]

aah ok

[sullen crystal]

I want to use Sqlalchemy Core to make my database interactions more pythonic, I've been using text mostly up until now. But it is different....

I want to do a CASE WHEN col in (list of string values) THEN True END but not sure how to do it.

I have the list of strings as a list variable defined, but it's not actually working for me.

[night hemlock]

Hi all,

I have a small, user-managed SQLite database where users will be writing SQL queries to retrieve data from several tables. One table contains test configuration metadata and the other contains test results for each test unique test run. Often times, we want to find the test results for repeat configurations (eg multiple runs performed with same config). These configurations usually share the same value across columns in a given row. An example query might look like this:

SELECT * FROM Configurations AS cfg INNER JOIN Results USING(RunNumber) WHERE cfg.a = 0 AND cfg.b = 0 AND cfg.c = 0 AND cfg.d = 0;

Sometimes there are many columns whose value should be zero to identify the desired configuration. Is there a way to more succinctly write these repetitive AND statements to assert that all values in a set of columns should equal zero?

Apologies for any poor formatting… I’m writing this from mobile.

[grim vault]

Some databases support: ... WHERE (cfg.a, cfg.b, cfg.c, cfg.d) = (0, 0, 0, 0);
but I'm not sure if that's more readable.

[torn sphinx]

has anyone professionally used casandra db?

[night hemlock]

Thanks. I’ve seen the row value comparison option before but wasn’t happy since you end up repeating yourself anyway. Was hoping for a way to say something like ALL( (cfg.a, cfg.b, cfg.c, cfg.d) = 0 ) where the inner statement would return (true,true,true,true) and the ALL() function would return true if all elements in the row value were true.

This does not appear to be a feature in SQL but I was hoping someone may have a clever solution.

[torn sphinx]

Would it be faster to do joins in the sql statement or would it be faster to do it in the application?

Is writing the if statement logic in SQL faster than checking it in the application code?

[grim vault]

If they are positive integers you can just add them up and compare it to 0.

[grim vault]

You can also write a custom function and add it to your sqlite connection.

[grim vault]

!e example of custom function:

import sqlite3

def compare_all(check, *args):
    return all(x == check for x in args)

con = sqlite3.connect(":memory:")
con.create_function("compare_all", -1, compare_all, deterministic=True)

con.execute("create table test(id, a int, b int, c int, d int)")
con.execute("insert into test values(1, 0, 0, 0, 0)")
con.execute("insert into test values(2, 1, 1, 1, 1)")
con.execute("insert into test values(3, 2, 2, 2, 2)")
con.execute("insert into test values(4, 1, 1, 1, 1)")
con.execute("insert into test values(5, 2, 2, 2, 2)")
con.execute("insert into test values(6, 0, 0, 0, 0)")

for row in con.execute("select * from test where compare_all(0, a, b, c, d)"):
    print(row)

for row in con.execute("select * from test where compare_all(1, a, b, c, d)"):
    print(row)

for row in con.execute("select * from test where compare_all(2, a, b, c, d)"):
    print(row)

[delicate field]

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

001 | (1, 0, 0, 0, 0)
002 | (6, 0, 0, 0, 0)
003 | (2, 1, 1, 1, 1)
004 | (4, 1, 1, 1, 1)
005 | (3, 2, 2, 2, 2)
006 | (5, 2, 2, 2, 2)

[night hemlock]

This might be just the feature I’m looking for. I did not know it would be so simple to create a custom function. At the very least, you’ve given me a solid lead. Thank you very much!

[bold copper]

how do you handle an exception for list index out of range

Right now I have it written like if results[1] != None: which doesn't work I've also tried 'null' and '' with no different results. Now that I'm not on my computer though I have an idea, would it work if I just did try and except instead of if

[fading patrol]

Off topic in this channel, but if len(results) > 0: is one solution