databases

[tawdry kelp]

This is the table details

[grim vault]

I don't think these columns should be unique in this table (Customer_Orders) because the customer might want to use the same payment for different orders?

[tawdry kelp]

In customer order table
Customer payment methods id and order status code are given as foreign key

Should we create foreign key for that

[grim vault]

Looking at the schema it seems like there are some tables missing. A foreign key allways neeeds to reference an existing unique constrain.

And to be forthright I have no interest in solving this problem right now.

[tawdry kelp]

Oh no problem

I am really sorry for bothering you

[grim vault]

I just wanted to give you a general direction so you might be able to solve it yourself.

[tawdry kelp]

Iam trying to figure

[grim vault]

Good luck.

[river matrix]

Hey, question. When using Wildcards in Jupyter notebook is the query lookup case sensitive? For instance is it necessary for me to LOWER my records in a name field? Say I'm looking for records which have J/j

Will a Look up LIKE '%J% find both 'j' and 'J'?

[grim vault]

It's database dependent. SQLite does support a NOCASE collation (for ASCII chars).

[river matrix]

i see, thank you!

[spiral mason]

i have been using mongodb and it's slow in responding what do you suggest i use now

[compact marlin]

i need help with my economy discord bots pay command. this is the code:
https://pastebin.com/RJe4mafJ

and this is the error i get: Traceback (most recent call last): File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 939, in invoke await ctx.command.invoke(ctx) File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 863, in invoke await injected(*ctx.args, **ctx.kwargs) File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 94, in wrapped raise CommandInvokeError(exc) from exc discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: no such column: getter_id

[rugged parrot]

Hi there, I’m making a discord game bot at the moment, so I’m using the repl.it database however I have no idea how to split everyone’s progress into different profiles so when a cool down starts for one person it’s also for all other players

I wasn’t sure which server to put this in as it’s to do with databases and discord bots but thought it was more relevant here

[river matrix]

Hey, for those who understand english well. I don't Please help me understand what it is that I'm supposed to figure out.

I have a DB of many domestic carriers. And I'm supposed to figure out which of the carriers have the 'best OPT' now apparently OTP is defined as the 'Rate of On-time flights with a 15min buffer on departure and arrival.'

I don't know how to what that last statement means nor how to figure out what the best OTP is.

This is my ER Diagram

[proven arrow]

With the data you have you need to calculate for each flight how often they are delayed. Group that by the carrier to then which you can sort by the best .

[river matrix]

Oh, I think I understand when you put it that way. So the 'Best' in this case be the carrier which returns the carrier with the least 'delays'?

or lowest rather

[proven arrow]

Of course. No delay means happy customer right 🙂

[river matrix]

Thank you!!

[tawdry kelp]

One question

There is a schema with tables names and column name and they are connected
In one of a table's column is marked as foreign key but there is no same colomn in other tables
How to define it as foreign key

[rugged parrot]

Hi there, I’m making a discord game bot at the moment, so I’m using the repl.it database however I have no idea how to split everyone’s progress into different profiles so when a cool down starts for one person it’s also for all other players

I wasn’t sure which server to put this in as it’s to do with databases and discord bots but thought it was more relevant here

[proven arrow]

It doesn’t have to have the same name to reference a foreign key.

[tawdry kelp]

I'll explain

Tab1
Id int ;

Tab2
Foreign key(name) references tab1(name)

Will that works?

[proven arrow]

No, because you don’t have a column called name in the table 1. It should reference id. And also the column of table 1 you want to reference should be either the primary key or a unique key.

[tawdry kelp]

Constraints

[proven arrow]

?

[tawdry kelp]

Yes there is a primary key

[tawdry kelp]

There are two types of foreign key
1 connects other table directly
And another with reference connects

Am i right

[proven arrow]

There’s only one type.

[tawdry kelp]

To connect two tables the column in tab1 must be primary key ?

[proven arrow]

Already answered.

[tawdry kelp]

Primary or unique

Got it

From this schema

In customer order table
The customer payment method id is mentioned as foreign key

But there's no column in other tables

How should I create foreign key

[proven arrow]

Going off naming conventions and looking at the FK, I would expect a payment methods table. I don’t see one, or any payment/transactions related tables so you would need to add them in.

[tawdry kelp]

This is the question from assignment

Ok thanks
so just create column without mentioning foreign key

Thanks a lot sir

[proven arrow]

No i did not say that. But ok.

[tawdry kelp]

Any other solution there?

[proven arrow]

I told you what to do.

You want to store payment data so create payment related tables.

Or tell your teacher to give more detail.

[tawdry kelp]

We have to create table from the given schema only so this is good

Just leaving column without foreign key

[rocky arch]

What is the difference between "joins" and "Foreigns Keys". I have a hard time understanding how to insert data into my Database with a foreign key.
But It seems, I can get everything I need without a Foreign Key and with only Joins.

[tawdry kelp]

Joins is used to display the tables
Foreign keys are connect with other table so that we can join two tables

[rocky arch]

Yeah I think I know what Foreign Keys do, but I dont understand how to use them correctly. And I think the "Join" method can do similar things.

Even if this solution is not the best

[tawdry kelp]

Joins are 4 types they are distributed into sets method
Left outer join
Inner join
Full join
Right outer join

[rocky arch]

yes

[tawdry kelp]

We can create Foreign key only if
Other table's column is either primary key or unique

[tawdry kelp]

Join method is just to display tables of both with the methods

[rocky arch]

Yes. I know. I dont understand how I can "INSERT" data.
I am reading the MYSql doc. right now

problem solved 🙈

[proven arrow]

Yes you can get everything without the foreign key. But you would use a foreign key to enforce integrity and consistency over the data. When making relationships between tables this is quite useful. Most importantly when you assign a foreign key it helps to ensure the parent/child relation actually exists (i.e, you can’t reference a value that doesn’t exist). Also for actions like cascading it is very useful (for example, deleting a parent should delete child rows).

[rocky arch]

Yeah I just found this out. So I got Foreign Key kinda wrong.
But one question I have right now.

I have my Tabel one with lets say

User:
id, name, age

and

Table2:

id, user_id INT, FOREIGN KEY (user_id) REFERENCES User(id), text

I now want to print every user where "text = "hello"".

I thought I could do it with

SELECT * FROM User, Table2 WHERE User.text = 'hello';

This kinda works, it shows the right Users, but every user very often. Basically every id and user_id get assigned to every user.

So
User id user_id
User1 1 1
User1 2 2
User1 3 3
User1 4 4
User1 5 5

I thought the Foreign Key would assign the exact user_id to the id from the first table.

It shows every possible combination and I dont know why.

[nova cove]

The foreign key generally relates to the primary key in another table

[rocky arch]

yes. So I probably created the second table wrong?

[nova cove]

with the information you gave, I assume that id is the primary key in table one

[rocky arch]

Do I create a Foreign Key like this

CREATE TABLE Data_used (id int PRIMARY KEY AUTO_INCREMENT, user_id INT, FOREIGN KEY (user_id) REFERENCES User(id) ,test boolean DEFAULT false, server_id BIGINT NOT NULL)

(The Tabel User was created before with id as Primary Key)

[nova cove]

With that, it would reference a column in another table with a different primary key. In your example, it would reference user_id in table 2. So for each unique user in the first table there will be an entry in the second table with the users unique id

[rocky arch]

yes

[nova cove]

I would put foreign key at the bottom

[proven arrow]

@rocky arch but you have not specified in the query what columns to join on

[rocky arch]

ahhh so I probably have to change the Tables? So that my User Table has the Foreign key.

[nova cove]

ah yeah he didn’t

[proven arrow]

And no idea what data you are expecting. Not sure I understand what the requirement is

[nova cove]

I was confused at your select query there

[rocky arch]

I want to see every user where "test = false/true".

I changed some stuff from the original code to make it easier to understand, but its probably harder now...

[nova cove]

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

this is an example of what the table should look like. It’s mostly preference to it at the bottom but I generally see FK at the bottom like this

[rocky arch]

Oh Okay I thought on the top.

[proven arrow]

You should always show the code you have rather than replace with dummy names. Also personal preference but INNER JOIN reads better.

[rocky arch]

Okay I will send a screen of the real data. I didnt want to confuse you, sorry.

So its a game bot and I just selected the important stuff.

As you can see, every Data.id gets a lot of Data_used.id's.
(If I scroll down the same will show up with Data.id = 2,3....)

[tawdry kelp]

You have to join tables

[rocky arch]

So Foreign is really just to secure your data?

I thought I "connected" the Data.id with my Data_used.id

[proven arrow]

I thought I already mentioned this earlier, but you didn’t specify the columns to join on @rocky arch

So it’s resulting in a cross join which is what you described with the result your currently getting

[rocky arch]

Yes that makes sense

[rocky arch]

But what is the difference between these two?

Only the last line is different

once with INNER JOIN and once with WHERE. The result is the same

no it isnt. oh no

I am sorry guys, sql just confuses me 😬

[torn sphinx]

if anyone knows how to work with postgresql, please ping me

[somber iron]

What specifically are you looking for? Just asking with help for a variant of SQL doesn't really tell us if I know/don't know this one off feature of Postgres @torn sphinx

[torn sphinx]

well im trying to set it up

i want to create tables and im currently trying to do it through 3rd party platforms and nothing's working

i tried pgAdmin

now im trying Forest Admin and i cant seem to get it going

so i want someone to help me set up things

@somber iron

[somber iron]

So are you trying to do it through like a GUI outside of python, that's a little outside of my wheelhouse. I would recommend the python package like sql alchemy for sql syntax if you're willing to do it through python

I typically use mssql/sqlite3, so anything specifically for postgres that's a gui I am going to be of little use.

[torn sphinx]

well the only reason im trying to use guis is because idk sql

like i dont need a lot of help

all i need to know is how to create tables and how to insert values in them

@somber iron

[proven arrow]

@torn sphinx https://tableplus.com is easy to use with a simple UI.

[torn sphinx]

ok ill try ty

@proven arrow oh dude, its already working

i already connected it

this seems to be an amazing program

tysm

@somber iron what are lists in sql

i cant find arrays

these are the options for data types

[iron gust]

Why is my input from python not getting reflected in my database in MySQL

I can share my code

The code is working perfectly fine except for the fact that it’s not doing its job

[compact marlin]

@celest sleet so how do i do it?

[somber iron]

What I've seen is like some sort of delimited list, then you call split on the result.

[somber iron]

does the package you're using require commit() to be called on the data to save?

[iron gust]

Nope

I can share my code

Do I just share it here?

[celest fractal]

Yes, lets take a look

[iron gust]

import mysql.connector #this is used to connect our mysql database
from datetime import date # this is used to get date and time

fine_per_day =5.0 #global variable

def clear():
for _ in range(1):
print

#This allows us to add Details regarding books, such as the Name, Author, Publisher
def add_book():
conn = mysql.connector.connect(
host='localhost', database='library', user='root', password='password')
cursor = conn.cursor()

title = input('Enter the Name of the Book :')
author = input('Enter the Author of the Book : ')
publisher = input('Enter the Publisher of the Book : ')
pages = input('Enter Number of Pages in Book : ')
price = input('Enter MRP of the Book : ')
edition = input('Enter Edition of the book ')
copies = int(input('Enter number of copies : '))
sql = 'insert into book(title,author,price,pages,publisher,edition,status) values ( "' +
title + '","' + author+'",'+price+','+pages+',"'+publisher+'","'+edition+'","available");'

for _ in range(0,copies):
cursor.execute(sql)
conn.close()
print('\n\nCongratulations!!! A New Book has been added successfully')
wait = input('\n\n\nPress X to proceed')

This is just a part of it.

[torn sphinx]

not sure what u meant there but what should i select from that list?

[iron gust]

Small chunk

[celest fractal]

by default auto commit is off, and I don't see you turning it on.

[somber iron]

you can do text, then insert a delimited list to that one field, then when you retrieve it you can call split on the data to get the list.

[celest fractal]

if you have cursor.commit() after cursor.execute() does it work?

[iron gust]

One sec

Lemme try

[somber iron]

also, it's recommended not to directly concat values for sql calls (Sql injection), most packages let you do substitutions. Not sure about the mysql package though.

[torn sphinx]

whats a delimited list?

[celest fractal]

something like this

deleted

[iron gust]

Nope (but I think I might be doing it wrong)

[somber iron]

!e

var_a = "a|b|c|d|EF"
print(var_a.split("|"))

[delicate field]

@somber iron :white_check_mark: Your eval job has completed with return code 0.

['a', 'b', 'c', 'd', 'EF']

[somber iron]

@torn sphinx ^

[iron gust]

I will try to make auto commit true and see if things change

[torn sphinx]

so just a normal list?

[somber iron]

It's delimited with a pipe. see that var_a input

[torn sphinx]

so which one is that delimited list

1. var_a = "a|b|c|d|EF"
2. ['a', 'b', 'c', 'd', 'EF']

[somber iron]

var_a is the delimited list, that would be the value in the DB.

[torn sphinx]

could i just do repr(mylist)?

instead of adding this |

i could store as repr(mylist) and then when i pull it back do list(mylist), no?

@somber iron

[celest fractal]

https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html

This might help

[rocky arch]

cursor.execute('''SELECT link FROM Data WHERE id ='''+str(number))


for x in cursor:
  print(x)

This should get me a Link. In the MYSQL terminal it gives me exactly what I want, but in python it looks like this

[('Link',)]

(I removed the Link, its alright) Why is there a "[", a "(" and why is there a ","? I can understand a "(" and a "[" but why a comma? And is there any way to get a clean output?

In "MYSQL" this

SELECT link FROM Data WHERE id =6

gives me

link

without anything else.

[sly pivot]

Could not commit table structure. Error message: UNIQUE constraint failed
i have all data filled in
but i cant seem to link tables by using forin keys

[honest jetty]

every time a worker makes a transaction on my app, I am recording the moment this happens
each transaction has a field that contains the moment the transaction happened
should I save the moment the transaction happens as timestamp, e.g., 1647371394, or should I use datetime.datetime?

[somber iron]

@rocky arch Because the row returned from the cursor is a tuple. so you would need to reference by the index of the tuple, or my the value name if your package supports that.

[somber iron]

It could be in either, if you're doing a datetime.datetime though I would recommend storing as UTC personally. Depends what is going to be using that date - human or machine?

[snow niche]

how do i set primary key when creating a table? (postgresql)

("""CREATE TABLE IF NOT EXISTS modlogs 
                    (guild_id BIGINT, channel_id BIGINT)""")
``` this is my code

i want to make guild_id the primary key

so should i change it to:
(guild_id BIGINT PRIMARY KEY) ?

pls ping on reply

[torn sphinx]

hi this is my first time trying mongodb i worked with sql before how can you do that if you search for a field and its empty how can you do that with an if statement?

[somber iron]

@torn sphinx that depends, what is the value the package returning? Is it the keyword None?

[somber iron]

Yes that is correct.

[torn sphinx]

what do you mean by that? didnt understand it correctly

[somber iron]

When you query the database, and you have the result - what is the result?

[torn sphinx]

i printed it, it came up a list so i can basically do if an field is none

[somber iron]

yes that should work, if the value is being returned as None, and not like an empty string, or a 0 (depending on datatype)

[torn sphinx]

ye it works now

[somber iron]

Sounds good, glad you figured it out!

[torn sphinx]

:)

[rough kettle]

are there any good tutorials on sqlite?

[proven arrow]

Tutorial for what? To use the api with python ? Or learning sql ?

[rough kettle]

learning sql

[proven arrow]

There is some links in the pinned message of this channel.

#databases message

[honest jetty]

human

[spiral mason]

i'm using SQLite3 with python and it's giving me this error when i try to insert some data

[torn sphinx]

Can i not do this?? ```sql
CREATE DATABASE FamilyMembers;

USE FamilyMembers;

CREATE TABLE People
(
PersonID int identity (1, 1) not null,
PersonInitials char(5) not null,
PersonAge int not null,
PersonWeight_KG float not null,
PersonHight_FT float not null,
PersonDescription varchar(100) null
);```

[worn flame]

SQLAlchemy:
To store my salt and encoded password (both of type byte) - I should create a "Unicode" field or "UnicodeText" field?
(ref: https://docs.sqlalchemy.org/en/14/core/type_basics.html)

[lunar pier]

Is there an easy way to copy column into another column in sqlite3+python?

I remember i did it somehow only with sqlite3 query, but don't remember how exactly

[snow niche]

how do i get the modlogs channel id from this? (postgresql)

    async def get_logs_channel(self, guild_id, channel_id):
        data = self.bot.db.execute("SELECT channel_id FROM modlogs WHERE guild_id=$1, channel_id=$2", guild_id, channel_id)

[proven arrow]

@lunar pier you can do UPDATE table_name SET new_column = old_column

[lunar pier]

Oh, thanks 😅

[proven arrow]

Which library for postgres ?

[snow niche]

asyncpg

[proven arrow]

Shouldn’t you be awaiting

I don’t think asyncpg has execute

[snow niche]

oh yes

[proven arrow]

It has fetch or something like that

[snow niche]

its for my bot.. i think it has

[snow niche]

fetchrow ?

[proven arrow]

https://magicstack.github.io/asyncpg/current/search.html?q=Fetch&check_keywords=yes&area=default

[proven arrow]

Yeah that would work. But if you want just one value then it has fetchval as well.

[snow niche]

im confortable with fetchrow, as i have used it b4

how do i get channel id tho?

[proven arrow]

Fetchval would give you that value

Fetchrow gives a record object back. So you have to go through that to get the value then otherwise

[snow niche]

        data = await self.bot.db.fetchval("SELECT channel_id FROM modlogs WHERE guild_id=$1, channel_id=$2", guild_id, channel_id)

``` ?

[proven arrow]

Looks fine as long as you know that query will return one row.

Otherwise it’ll just get the first row probably

[snow niche]

hmm

[proven arrow]

How many records are you expecting back?

[snow niche]

uhh idk just a channel id and guild id

im trying to make a modlog

i have everything ready, now the only part is to get the channel id which the user saves

and then send the msg to that channel

[proven arrow]

So how many channels can the user save?

[snow niche]

1

[proven arrow]

Then fetchval would work

[snow niche]

okay

[proven arrow]

As you only need the value of the first matching row

[snow niche]

yeah

[snow niche]

if they want to change it, i made it so like it updates the old one

    @set.command(aliases=['log'])
    @commands.has_guild_permissions(manage_guild=True)
    @commands.bot_has_permissions(manage_guild=True)
    async def logs(self, ctx, channel: discord.TextChannel):
        """
        Set a mod-log channel.
        To replace a log channel, simply run this command again.
        """
        try:
            await self.bot.db.execute("INSERT INTO modlogs (guild_id, channel_id) VALUES ($1, $2) ON CONFLICT (guild_id) DO UPDATE SET channel_id=$2 WHERE modlogs.guild_id=$1", ctx.guild.id, channel.id)
            await ctx.send(f"{self.bot.yes} Mod-logs channel set to {channel}")
        except Exception as e:
            print(e)

[snow niche]

@proven arrow

[proven arrow]

Didn’t we agree already to use fetchval to get the id you want?

[snow niche]

yes

so i dont need to use stuff like data[0] ?

[proven arrow]

No

I don’t think so anyways looking at the docs

Try it and you’ll see

[snow niche]

ok ty

[grim vault]

I don't think this is valid SQL. What's with the , channel_id = $2 part? You want to select the channel_id it doesn't belong in the where clause (with an wrong syntax).

[snow niche]

what should I do here then?

[ionic latch]

https://gyazo.com/8cf48515eeaf1e5d67cd781ddbec19f1

i dont know what to do here, i wrote this

def author_exist(lastname, initials):
    cursor = conn.cursor()
    
    SQL = '''SELECT Lastname, Initials, AuthorID
             FROM Author
             WHERE Lastname = ?
             AND Initials = ?'''
    
    temp = cursor.execute(SQL, lastname, initials)```

[proven arrow]

You can fetch the data, and then use the result to see if anything was returned.

[ionic latch]

so i create a py result = temp.fetchall()

and then do py if result == None: return 0 else: return result

or am i doing it wrong

[proven arrow]

If none is return then yes, but I’m not sure what the return type is for no match found. You can inspect the returned type to see what you get back.

[ionic latch]

but will that function then return the AuthorID? since thats what the exercise says

[proven arrow]

Well if you inspected the value you would see what you get back. It will be some sort of list. So you need to access the value from there.

[torn sphinx]

how can i fetch data from my postgreSQL db in a form of a dict
i dont want to rely on indexing

[sturdy lily]

I am using atlas search and i want to search a user with the text whether it's included in the name, username or email, and also want to use partial text search. So when I am using the autocomplete for partial text search then i am not able to use wildcard so that i can use search from any field. Can someone help me with this?

[eternal folio]

Do i need to have a database to make commands for my app

[nova cove]

if you are storing a lot of relational data, yes

but if its just to learn and make something small, you don't really need to have a db

[eternal folio]

ok

[celest dove]

Would appreciate anyone who's even slightly proficient at interacting with mongo collections / documents with pymongo to help me over at #help-chili 🙏 - thanks peoples

[maiden wigeon]

Hi, is there a certain datatype for gender in MySQL?

[nova cove]

?

why would that be a thing?

just make a TEXT column

[maiden wigeon]

oh ok

[nova cove]

and the column name is gender

[maiden wigeon]

alr thx : )

[celest dove]

There's enum data standards it seems if you were to go via that route :

The four codes specified in ISO/IEC 5218 are:
    0 = Not known;
    1 = Male;
    2 = Female;
    9 = Not applicable.```

[maiden wigeon]

so then to create a table which has the column GENDER in a database, there is no certain datatype. is that what you meant? sry if i'm being dumb😶

[celest dove]

No sorry - I'm saying if you were to store them as enum values, rather than a string e.g. "Male" - there seems to be data standards - your choice on how you do it but there isn't a specific data type for GENDER per se

[maiden wigeon]

Ohk. Understood better now. Thanks for the help!

[bleak berry]

#help-chestnut 🛐

[grizzled rapids]

How do I send api data to json?

[grizzled rapids]

Does this work?

[lucid lintel]

Hi everyone, I posted this question in stack overflow: https://stackoverflow.com/questions/71503023/how-can-i-select-the-largest-value-when-an-ungrouped-column-is-dependent-on-a-gr, if anyone can help I would appreciated. I posted the link since I thought it would be to long to re-ask here.😫

[compact marlin]

i need help

why doesnt this work?: ```py
await cursor.execute("UPDATE money_data SET balance = balance + ? WHERE user_id=?", (amount, getter_id))
await db.commit()

    await cursor.execute("UPDATE money_data SET balance = balance - ? WHERE user_id=?", (amount, USER_ID))
await db.commit()```

i get no error when i run it but it simply doesnt do what i want it to do

[brave bridge]

This sounds like it should work.

The only reason it might not do anything is if the user IDs are incorrect

maybe you can add RETURNING user_id at the end, and then print the query results?

[compact marlin]

this is where i define the ids: py @client.command(name="pay") async def pay(ctx, member: discord.Member, amount): db = client.database_connection cursor = await db.cursor() USER_ID = ctx.message.author.id USER_NAME = str(ctx.message.author) getter_id = str(member.id) getter_name = str(member)

[compact marlin]

i think i defined them correctly

[snow niche]

no, it's showing an error

[brave bridge]

So what exactly is not working?

[grizzled rapids]

Where?

[brave bridge]

And how do you know that the rows with these IDs already exist when making the payment?

If the rows don't exist, there's nothing to update.

[compact marlin]

i dont know bro. the whole command is working. it sends what it should be sending. but then it doesnt update their bal

[compact marlin]

it checks if it exists

and if it doesnt exist it creates one

[brave bridge]

Can you do ```py
await cursor.execute("UPDATE money_data SET balance = balance - ? WHERE user_id=? RETURNING user_id", (amount, USER_ID))

and then print `await cursor.fetchall()`?

(and the same for +)

[snow niche]

there's no unique exclusion or constraint matching the ON CONFLICT specification

[grizzled rapids]

I’ve no idea, what that is sorry 🙈🙈

[snow niche]

lol ok

[grim vault]

It means the guild_id has no unique constrain.

[snow niche]

it's a primary key

so what should I do? I'm going to sleep now.. reply ping

thanks

[grim vault]

show the create table command.

[compact marlin]

this changed nothing

the same thing happens

[brave bridge]

This is not supposed to change anything. It's supposed to print the changed IDs for debugging

also, can you show the whole command maybe?

!paste

[delicate field]

Pasting large amounts of code

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

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

[compact marlin]

ok i will wait

[compact marlin]

https://pastebin.com/nJRT4NuX

[brave bridge]

What I asked you to do is: ```py
await cursor.execute("UPDATE money_data SET balance = balance - ? WHERE user_id=? RETURNING user_id", (amount, USER_ID))
print(await cursor.fetchall())
await cursor.commit()

then you should get some extra output in the terminal

[compact marlin]

bruh

alright

[brave bridge]

RETURNING lets you output something from an INSERT or UPDATE statement

Oh, wait

this code isn't even executing

It only executes if amount < 0

[compact marlin]

wait

[compact marlin]

no

its amount > 0

[brave bridge]

right but your UPDATE is in the else: branch

[compact marlin]

omg

im so stupid

thank u lol

[brave bridge]

btw, you should not commit after the first UPDATE

otherwise, if the second UPDATE fails (because of some I/O error, because the bot program stops etc.), you will add to one user's balance but not subtract from the other's

[compact marlin]

ok

so i should only commit after the second one?

[brave bridge]

yes

[compact marlin]

ok it should be working now imma test it thank u

[compact marlin]

everything works now thank u

[torn sphinx]

I'm trying to put a string in my db with sqlite3 and it's not going too well. Here is my code:

import sqlite3

name = "this is a test"

conn = sqlite3.connect()
c = conn.cursor()

c.execute("""CREATE TABLE hello (
            name text
            )""")

c.execute("INSERT INTO hello VALUES (?)", name)

c.execute("SELECT * FROM hello WHERE name=?", (name))

print(c.fetchall())

conn.commit()

conn.close()

[storm mauve]

you must use (name,) in both cases, instead of name / (name) ```py
c.execute(query, (name, ))

!e ```py
import sqlite3

name = "this is a test"

conn = sqlite3.connect(":memory:")
c = conn.cursor()

c.execute("""CREATE TABLE hello (
name text
)""")

c.execute("INSERT INTO hello VALUES (?)", (name,))

c.execute("SELECT * FROM hello WHERE name=?", (name,))

print(c.fetchall())

[delicate field]

@storm mauve :white_check_mark: Your eval job has completed with return code 0.

[('this is a test',)]

[torn sphinx]

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

and i changed name to name,

[storm mauve]

That error happens because it's trying to assign each letter from name to a different ?, but you only supplied one ?

[torn sphinx]

oh

[rich topaz]

ah the one valued tuple back at it again

[torn sphinx]

so how would i assign every letter from name to the same ?

[storm mauve]

create a tuple that only contains name, (name, )

note that this is different from just (name) and from name,

#does not works here:
function(name)  # it tries to decompose name into each letter from name
function(name,)  # passes the argument name, with a trailing comma
function((name))  # same as just function(name)
# would work:
function((name,))  # passes a tuple of length 1, which contains name

[rich topaz]

tuple syntax is the best paren reuse I have ever seen, but it's just as confusing

[storm mauve]

wait until you learn about comprehensions

[rich topaz]

yo mean [book for book in book_shelf for book_shelf in library]?

[storm mauve]

yes, but when you use () it is a generator comprehension (lazy evaluated, yields one at once, will be exhausted after you go through it once) instead of a tuple
they work for sets and dictionaries the same way they work for lists though ```py
{x for x in range(10)}
{x: x**2 for x in range(10)}

[rich topaz]

oh ill show show you my amazing code, guess what it does. ```py
_, node, i = min([(costs[node], node, i) for i, node in enumerate(frontier)])
frontier.pop(i)

@storm mauve

[rich topaz]

this is actually considered good practice by some random utuber that was calculating sum of matrix with comprehension instead of loops

[storm mauve]

I'm pretty sure that you could just remove the [] by the way

min((costs[node], node, i) for i, node in enumerate(frontier))

[rich topaz]

rly?

[storm mauve]

yep

[rich topaz]

nice

[storm mauve]

that will make a generator instead of a list

which should end up a fair bit cheaper

[rich topaz]

so for prefixed with expression is generator

[storm mauve]

inside of ()

[rich topaz]

epic

[rich topaz]

rust is also full of surprises, did you know @ is used in pattern matching

[storm mauve]

we are getting kinda offtopic for this channel at this point

[rich topaz]

so what channel do you suggest? #community-meta maybe

[storm mauve]

!ot

[delicate field]

Off-topic channel: #ot2-the-original-pubsta

Please read our off-topic etiquette before participating in conversations.

[stray moss]

In MySQL,
I have a table named Channel

id    guildId    position

id is primary key unique
guildId and position are both unsigned int, and together they form a unique constraint

How can I do something that doesn't break this unique constraint such that:
update a channel current's position field value to x (assuming x is bigger than current.position)
update all channels between current.position and position x and decrement their position field by 1
update current.position to position x

[rigid yacht]

hello guys good morning

[snow niche]

https://github.com/DTS-11/PizzaHat/blob/main/PizzaHat/cogs_hidden/events.py#L16-L17

[delicate field]

PizzaHat/cogs_hidden/events.py lines 16 to 17

await self.bot.db.execute("""CREATE TABLE IF NOT EXISTS modlogs 
            (guild_id BIGINT PRIMARY KEY, channel_id BIGINT)""")```

[snow niche]

I also get this error from this code

    @set.command(aliases=['log'])
    @commands.has_guild_permissions(manage_guild=True)
    @commands.bot_has_permissions(manage_guild=True)
    async def logs(self, ctx, channel: discord.TextChannel):
        """
        Set a mod-log channel.
        To replace a log channel, simply run this command again.
        """
        try:
            await self.bot.db.execute("INSERT INTO modlogs (guild_id, channel_id) VALUES ($1, $2) ON CONFLICT (guild_id) DO UPDATE SET channel_id=$2 WHERE modlogs.guild_id=$1", ctx.guild.id, channel.id)
            await ctx.send(f"{self.bot.yes} Mod-logs channel set to {channel}")
        except Exception as e:
            print(e)
``` error: there is no unique or exclusion constraint matching the ON CONFLICT specification

[grim vault]

It should work: https://www.db-fiddle.com/f/mjuJQQtriUVTZDSEGFt3ch/0
And the WHERE modlogs.guild_id=$1 is not needed, an ON CONFLICT does only update the row t where the conflict occured.

[snow niche]

I first tried:
INSERT OR REPLACE INTO modlogs

[grim vault]

Did you add the primary key after the table existed? It won't recreate it. Can you check with an db utility to see if it realy is the PK?

[snow niche]

but then someone suggested me this, same thing as I was trying to do

[snow niche]

one sec

pretty sure it is primary key

[grim vault]

If it is, it would work.

[snow niche]

there's no problem with the code?

[grim vault]

No, but as I said, the where clause is not needed.

[snow niche]

ok so channel_id=$2

till there it's needed?

[grim vault]

yes

[snow niche]

okay I will try it, thanks

[grim vault]

did you look at my link?

[snow niche]

no, Im going to now

it's like online SQL?

[grim vault]

But it will not change the error, because the DB says that there is no unique constraint on guild_id, so it can't be a PK.

Yes, you can try different things with an database.

[snow niche]

hmm, I will delete the table and recreate it.. that should work

[snow niche]

ok it seems to store the channel id.. but the bot is not sending msgs to that channel, code given below

    async def get_logs_channel(self, guild_id):
        data = await self.bot.db.execute("SELECT channel_id FROM modlogs WHERE guild_id=$1", guild_id)
        id = data[0]
        channel = self.bot.get_channel(id)
        return channel

    @Cog.listener()
    async def on_message_edit(self, before, after):
        try:
            em = discord.Embed(
                title=f"Message edited in {before.message.channel}",
                color=self.bot.success,
                timestamp=before.created_at
            )
            em.add_field(name="-Before", value=before.content, inline=False)
            em.add_field(name="+After", value=after.content, inline=False)
            em.set_author(name=before.author, icon_url=before.author.avatar.url)
            em.set_footer(text=before.author.id)

            channel = await self.get_logs_channel(before.guild.id)
            await channel.send(embed=em)
        except:
            pass

should i make it print the error?

[grim vault]

An execute doesn't fetch data, so I'm pretty sure id = data[0] doesn't work.

You need .fetchone() or (is it fetchrow() for pg?) something first.

[snow niche]

hmm

[snow niche]

fetchrow gets a row

which is record obj

[grim vault]

you only have one column, so you can use fetchval()

    async def get_logs_channel(self, guild_id):
        channel_id = await self.bot.db.fetchval("SELECT channel_id FROM modlogs WHERE guild_id=$1", guild_id)
        # check for None maybe?
        channel = self.bot.get_channel(channel_id)
        # and some error checking here?
        return channel

[snow niche]

checks are needed?

like: if channel_id return []

[grim vault]

Not needed, but easier to debug if something fails.

and you can't use a None channel anyway

[snow niche]

yeah

so no need of that id var

[snow niche]

    async def get_logs_channel(self, guild_id):
        data = await self.bot.db.fetchval("SELECT channel_id FROM modlogs WHERE guild_id=$1", guild_id)
        if data:
            return []
        else:
            print("No data")
        channel = self.bot.get_channel(data)
        return channel
``` this might work?

[grim vault]

No

Think what your code is doing.

• fetchdata
• if there is no data return an empty list, if not print "no data"
• get the channel (but this is only done if there is NO data)!
• return a channel which will never be found

[snow niche]

okay

what exactly should i change here? the if data condition ig?

[grim vault]

You only can do get_channel() if there is data, so yes, you need to rethink your if statement.

[snow niche]

    async def get_logs_channel(self, guild_id):
        data = await self.bot.db.fetchval("SELECT channel_id FROM modlogs WHERE guild_id=$1", guild_id)
        if data:
            channel = self.bot.get_channel(data)
            return channel
        else:
            print("No data")
```  this makes sense to me

[grim vault]

looks better, yes. You may want to add a return None after the if (which id done atomaticly by python if there is no return statement) to be clear about it. And maybe a better error message.

    async def get_logs_channel(self, guild_id):
        channel_id = await self.bot.db.fetchval("SELECT channel_id FROM modlogs WHERE guild_id=$1", guild_id)
        if channel_id:
            return self.bot.get_channel(channel_id)
        print(f"No modlog channel for {guild_id}")
        return None

[snow niche]

hmm k

[snow niche]

so this will work?

[tough kettle]

can anybody here help with SQLAlchemy

[grim vault]

Yes, that should work.

[snow niche]

ok tysm

[grim vault]

but be aware that:

            ...
            channel = await self.get_logs_channel(before.guild.id)
            await channel.send(embed=em)

this can still fail if the channel is None (not found in the function).

[snow niche]

ok

this event is confusing me bcz it takes in only b4 and after

but others take in msg, guild, user so that way im able to get the guild id

[tough kettle]

I have a TodoList Class That Has a Relation with ListItem Class
So a TodoList Object has a one to many relation with the ListItems

The ListItems have a column called completed

It can either Be NULL/False or True

[grim vault]

The _edit part suggest that there is a message before and after the edit.

[tough kettle]

So here's what i need help in
I want To see If All of the completed are True, For a List

And Return True/False depending on it

[snow niche]

yes thats how the event works.. i thought for a while on getting the guild id

[tough kettle]

can you ppl help pls?

[grim vault]

I'm not familiar with SQLAlchemy, sorry. Maybe someone with knowledge will jump in.

[tough kettle]

do you know SQL?

[grim vault]

Yes

[tough kettle]

so what would the SQL roughly look like?

for this

[grim vault]

something like:

select
  listtable.id,
  not exists (select 1 from itemtable
               where itemtable.list_id = listtable.id
                 and not itemtable.completed) as "all_done"
  from listtable

maybe

[tough kettle]

ok Thanks

[compact marlin]

hi i made this command trying to sort values in my sqlite database but it doesnt work. this is my code: py @client.command(name="lb") async def top10(ctx): db = client.database_connection cursor = await db.cursor() await cursor.execute(f"SELECT user_id, balance from money_data WHERE user_id = {ctx.guild.id} ORDER BY balance DESC LIMIT 3 ") result = await cursor.fetchall() embed = discord.Embed(title="Leaderboards", colour=discord.Colour(0x6790a7)) for i, x in enumerate(result, 1): embed.add_field(name=f"#{i}", value=f"<@{str(x[0])}> on Level {str(x[1])} with {str(x[2])} Total XP", inline=False) await ctx.send(embed=embed) print(result)

pls help me

[sturdy lily]

i have there text index for title, body and info in atlas search and i want to do a partial text search from all these three at once, but when i am using autocomplete then i can't use the wildcard so how can achieve that? can someone help with this?

[grim vault]

don't use f-strings

  await cursor.execute(f"SELECT user_id, balance from money_data WHERE user_id = {ctx.guild.id} ORDER BY balance DESC LIMIT 3 ")

use bind parameters (and in this case you might want guild_id as column not the user_id?):

  await cursor.execute("SELECT user_id, balance FROM money_data WHERE guild_id = ? ORDER BY balance DESC LIMIT 3", (ctx.guild.id,))

  for i, x in enumerate(result, 1):
    embed.add_field(name=f"#{i}", value=f"<@{str(x[0])}> on Level {str(x[1])} with {str(x[2])} Total XP", inline=False)
    ...

You only select two columns SELECT user_id, balance FROM ... so you can't use x[2] that would need a third column to be selected.

[torn sphinx]

cursor.execute('INSERT INTO banwords VALUES(%s, %s)', (message.author.id,1))2022-03-17T11:31:55.477270+00:00 app[worker.1]: psycopg2.errors.NumericValueOutOfRange: integer out of range

postgresql

[grim vault]

For guild and user IDs you need a BIGINT datatype, not INTEGER.

[torn sphinx]

(userid BIGINT, count INT)

what?

[torn sphinx]

(userid BIGINT, count INT) BIGINT uses

[grim vault]

Do you mean that your table banwords already uses an BIGINT for the userid?

[torn sphinx]

Used INT, then I changed to BIGINT

[grim vault]

You'll need to recreate the table to get the change or execute an alter table command.

[torn sphinx]

okay

[grim vault]

ALTER TABLE banwords ALTER COLUMN userid TYPE BIGINT;

[torn sphinx]

okay, thank you

[torn sphinx]

warning = cursor.execute('SELECT * FROM banwords WHERE userid = %s', (message.author.id,))
        cursor.fetchone()
        

        if warning == None:
            cursor.execute('INSERT INTO banwords VALUES(%s, %s)', (message.author.id,1))
            base.commit()
            emb=discord.Embed(title="Предупреждение", description=f"Участник **{message.author.name}** получил предупреждение за грубое оскорбление, случай 1.",color=discord.Color.from_rgb(128, 0, 255), timestamp=message.created_at)
            emb.set_thumbnail(url = message.author.avatar_url)
            emb.set_footer(text="© Автоматические предупреждение")
            
            await message.channel.send(embed=emb)
        
        elif warning[1] == 1:
            cursor.execute('UPDATE banwords SET count = %s WHERE userid = %s', (2,message.author.id))
            base.commit()
            emb=discord.Embed(title="Предупреждение", description=f"Участник **{message.author.name}** получил предупреждение за грубое оскорбление, случай 2.",color=discord.Color.from_rgb(128, 0, 255), timestamp=message.created_at)
            emb.set_thumbnail(url = message.author.avatar_url)
            emb.set_footer(text="© Автоматические предупреждение")
            
            await message.channel.send(embed=emb)
 ```

elif warning[1] == 2:
            cursor.execute('UPDATE banwords SET count = %s WHERE userid = %s',(3,message.author.id))
            base.commit()
            role = message.guild.get_role(921806885831716875)
            emb=discord.Embed(title="Предупреждение", description=f"Участник **{message.author.name}** получил предупреждение за грубое оскорбление, случай 3. Участнику выдан мьют, за грубые оскорбления, предупреждения сбросились.\n||Обращайтесь к админам, чтобы они сняли мьют||",color=discord.Color.from_rgb(128, 0, 255), timestamp=message.created_at)
            emb.set_thumbnail(url = message.author.avatar_url)
            emb.set_footer(text="© Автоматические предупреждение")
            
            await message.channel.send(embed=emb)
            await message.author.add_roles(role)
            cursor.execute('DELETE FROM banwords WHERE count AND userid') 
            base.commit() 

always Value 1, does not change, why?

[grim vault]

This: cursor.execute('DELETE FROM banwords WHERE count AND userid') will delete all entries as soon as one reaches count 2.
You want most likely:

    cursor.execute('DELETE FROM banwords WHERE userid = %s', (message.author.id,))

And are this two separate code lines?

        warning = cursor.execute('SELECT * FROM banwords WHERE userid = %s', (message.author.id,))
        cursor.fetchone()

It would need to be the other way around:

        cursor.execute('SELECT * FROM banwords WHERE userid = %s', (message.author.id,))
        warning = cursor.fetchone()

And you don't need to set the count to 3 if you are going to delete the entry anyway.

[tropic dagger]

@pure mortar from #career-advice , what do cloud dbs require? Clustering? The closest I've seen was with Mongo Atlas and its trial cluster.

[pure mortar]

many cloud dbs are columnar DBs, so many traditional SQL queries are inefficient on these dbs. Atlas is good for document DBs. pick up some cloud and/or tooling like snowflake or dbt, etc. and you should be competitive enough for entry-level DE

[pure mortar]

many relational schemas dont fit for cloud data warehouses (obv. depends on which one), but i think you should understand DE from less of a relational DB approach

otherwise you might as well go and be a DBA

[tropic dagger]

What do you recommend between snowflake and dbt?

At least something that costs 0€ for practice

[pure mortar]

check out seattle data guy for DE resources https://youtu.be/SpaFPPByOhM

skip the stuff you already know

focus on the other stuff you dont

best approach

he also has ton of other good DE videos too

[tropic dagger]

Oh wait he also proposes some DE projects. Interesting.

[pure mortar]

yep yep

better for your resume tbh

than normal projects

that might be too general

[tawdry kelp]

Brother one question

I created a table with a column as a data type bigint

I want to change to varchar
How to change

[compact marlin]

hi i have a question

i use an sqlite database for my discord bot and i wonder if i can make a command which will register everysingle member in a discord server and save their information (id and balance) in my db file? would i have to make a loop which registers them all or can i just get the member list and get them all?

[torn sphinx]

you'll need a loop to create a table for each member

in the server

[celest sleet]

or make so that they have no balance

untill post a msg

and register discordID into db and set default balance on their first msg

if no record exists

[compact marlin]

thats actually exactly what i did

[celest sleet]

(just think its more api friendly)

[compact marlin]

maybe thats good enough idk

nah honestly thats good enough

i already made that so i dont need to change anything then

[celest sleet]

i think its a good way u also dont care about idlers

but yeah if u still anyway want check n put all users into db

u will need like a loop "task"

[compact marlin]

i will have to loop it yes

[celest sleet]

check every hour or so

[compact marlin]

i will have to make a command and give everyone money manually then

[celest sleet]

no if u give balance when created if not existed

[compact marlin]

cus i already have the bot going with a json file as a database

[celest sleet]

if they exists their balance is same

[compact marlin]

and i want everyone to have as much money as they had before

so i will have to manually fix that

[celest sleet]

u can just write a function if u have that stored

and just !copydb

call command let it read ur old json or shit

[compact marlin]

its a json file tho

will that fr work

[celest sleet]

n insert it to the discordID

[compact marlin]

oh damn

how would that work?

[celest sleet]

yes u only need to do it once

like u read ur json

go throu all

check if its in db then insert balance to them

[compact marlin]

no i just realised i dont think that will work

[celest sleet]

if u wanna copy ur json to db it will work

u just ned to run it one time

[compact marlin]

cus in my sqlite file i have user_name, user_id and user_bal

on my json file i only have user_id and user_bal

[celest sleet]

if u scrabble all ur users now 1 time n make rows of em

yeah

lets try explain it again

[compact marlin]

lol

[celest sleet]

Json -> read loop
database -> check if exists insert json.user_bal to db and same with user_name then

but first run a code once that grabs all users u have atm to db

all u need is discordid for that rest columns can be empty

cause u will update it with json

[compact marlin]

ooooooooh

alright

[celest sleet]

and then code so that any one type a msg if not exist in db insert it with default balance

[compact marlin]

alright i get it

[celest sleet]

then its on automaticly

ye its easy peasy

@compact marlinfeel free to ask if anything showsup

[sly pivot]

#databases can anyone get voice chat and help mw with SQLite studio not rushing anyone

[gleaming herald]

is anyone in here experienced with AWS?

[keen minnow]

note the lack of wings of the inner plane

[severe coral]

That used to be XML in an SQL DB

[pure mortar]

you guys are absolutely killing me

💀

[severe coral]

Nah too much coffee will do that to you .... watch coffee intake...

[pure mortar]

i only had two today

[weak mist]

is this the right channel to ask a question about sqlalchemy and marshmallow?

[sly pivot]

can anyone get voice chat and help mw with SQLite studio not rushing anyone

my prokect is due toady

[harsh pulsar]

seems reasonable

[harsh pulsar]

rushing?

[sly pivot]

nvm what i said

[weak mist]

@harsh pulsar where to post sample code? git? or paste here?

[burnt turret]

if it's not too long feel free to paste it here. otherwise you can post it on https://paste.pythondiscord.com and share the link here instead

[harsh pulsar]

and if you want to post shorter code snippets, you can use code formatting

!code

[delicate field]

Here's how to format Python code on Discord:

```py
print('Hello world!')
```

These are backticks, not quotes. Check this out if you can't find the backtick key.

[weak mist]

@burnt turret @harsh pulsar Thanks both for help.

[random bobcat]

i have a replit sample database

i made some economy commands

in that

i want to make a list so that the person with the highest currecy

to cone at the top

[random bobcat]

ik ik ima peasant

[gloomy spindle]

Isnt the replit "database" just a json file

you can just search "how to sort a dictionary"

[undone bloom]

Hellooo. I have a scatter plot with probabilities that change on the y axis and constant positions on the x axis. Do you know how to animate it or a good tutorial?

[random bobcat]

hmm okk

[oak oyster]

get all items -> sort the dictionary using the sorted method

[celest sleet]

@random bobcat

oof = {"guy1":10,"guy2":20,"guy3":30}

x = sorted(oof.items(), key=lambda x: x[1], reverse=True)

for o in x:
    print(o)
# ('guy3', 30)
# ('guy2', 20)
# ('guy1', 10)

!d sorted

[delicate field]

sorted

sorted(iterable, /, *, key=None, reverse=False)```
Return a new sorted list from the items in *iterable*.

Has two optional arguments which must be specified as keyword arguments.

*key* specifies a function of one argument that is used to extract a comparison key from each element in *iterable* (for example, `key=str.lower`). The default value is `None` (compare the elements directly).

*reverse* is a boolean value. If set to `True`, then the list elements are sorted as if each comparison were reversed.

Use [`functools.cmp_to_key()`](https://docs.python.org/3/library/functools.html#functools.cmp_to_key "functools.cmp_to_key") to convert an old-style *cmp* function to a *key* function.

[random bobcat]

ohh

[stray moss]

MySQL v8 is giving me this error

[harsh pulsar]

single quotes are for strings. use double quotes for quoting a column name

ROW_NUMBER() OVER w AS "p"

[stray moss]

k

[harsh pulsar]

(also you probably don't need to quote this name at all)

[stray moss]

same error

[harsh pulsar]

also can you post your code a text in the future, using a code block?

[stray moss]

position 35 is the char before w

[harsh pulsar]

some people cannot read images

!code

[delicate field]

Here's how to format Python code on Discord:

```py
print('Hello world!')
```

These are backticks, not quotes. Check this out if you can't find the backtick key.

[stray moss]

SELECT c.id,
  ROW_NUMBER() OVER w AS p
FROM Channel c
WINDOW w AS (ORDER BY position);

[harsh pulsar]

thanks

[stray moss]

CREATE TABLE IF NOT EXISTS `Channel` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `guildId` INTEGER UNSIGNED NOT NULL,
    `name` VARCHAR(64) NOT NULL,
    `description` TEXT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `position` FLOAT NOT NULL,
    `parentId` INTEGER NULL,
    `ratelimit` INTEGER NOT NULL DEFAULT 0,
    `type` ENUM('textChannel', 'categoryChannel') NOT NULL,

    INDEX `Channel_guildId_idx`(`guildId`),
    UNIQUE INDEX `Channel_guildId_name_key`(`guildId`, `name`),
    UNIQUE INDEX `Channel_guildId_position_key`(`guildId`, `position`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Here's my entire table

INSERT INTO Channel (id, guildId, name, position, type) VALUES (1, 1, 'ch1', 1, 'textChannel');
INSERT INTO Channel (id, guildId, name, position, type) VALUES (2, 1, 'ch2', 2, 'textChannel');
INSERT INTO Channel (id, guildId, name, position, type) VALUES (3, 1, 'ch3', 3, 'textChannel');

3 test rows

[harsh pulsar]

thanks!

that makes this so much easier

wait, try backticks instead of double quotes...

i don't use mysql much, maybe it doesn't use double quotes

[stray moss]

it still doesn't work

SELECT c.id,
  ROW_NUMBER() OVER `w` AS `p`
FROM `Channel` c
WINDOW `w` AS (ORDER BY `position`);

[harsh pulsar]

https://www.db-fiddle.com/f/whr4PZzxRqNsVDcTECSEV7/1 works fine for me

[stray moss]

hmm

that's really weird

[harsh pulsar]

must be a typo elsewhere in your code 🤷‍♂️

or a bug in whatever console you are using

https://www.db-fiddle.com/f/whr4PZzxRqNsVDcTECSEV7/3

that's with everything quoted using backticks

[stray moss]

what..

Im trying to replicate the thing without ROW_NUMBER()

it works but it doesn't actually work

rank field is completely empty

I think my console is bugged in some way...

@harsh pulsar u there?

[harsh pulsar]

possibly, i'm not sure. maybe restart the console?

[brave bridge]

@stray moss What is your MySQL version?

[stray moss]

v8

[brave bridge]

Strange. The DB-fiddle salt rock lamp linked gives the same error you have if you switch MySQL version to v5.7.

What software are you using to make the queries?

[stray moss]

it's a cloud provider

I'm using their console interface

I sent a support letter already

[brave bridge]

Could you run SHOW VARIABLES LIKE ‘%version%’; maybe?

[stray moss]

oh it's using InnoDB

[brave bridge]

idk what it is but it seems like something 🙂

[stray moss]

it's also using Vitess

[brave bridge]

hmm wait @stray moss , it says here
https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html

InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 8.0, InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE clause creates an InnoDB table.
se seems like InnoDB is expected?

[stray moss]

yeah

the tables are InnoDB tables

[brave bridge]

hmm, it's showing 8.0.23 but 8.0.21 on the other screenshot 🤔

oh wait, it's innodb version vs the "version" version?

[stray moss]

I dont even know it just shows that

[brave bridge]

me neither, I have no idea about MySQL to be honest

maybe you should bump your question 🙂

[stray moss]

same I wanted to use Postgres but this provider said free 10 GB MySQL so I got hooked in

[harsh pulsar]

innodb is the "storage engine", but that shouldn't have anything to do with query parsing

[stray moss]

yeah

oh

it's actually not MySQL

it's Vitess MySQL

[harsh pulsar]

https://vitess.io/ who knows then

maybe it doesn't support WINDOW for all we know

i never used this

[stray moss]

sad

[harsh pulsar]

unfortunate, but there's the answer

[stray moss]

i can implement ROW_NUMBER on my own

but wth @row_number variables doesn't even work

[torn sphinx]

Hello I'm currently designing the structure of a database and I have the following table that I would like to transform it to 3FN. The following table contains time ranges from start to end showing availabilities of a referee on a weekday.

CREATE TABLE AVAILABILITIES(
   ref_num int8 NOT NULL,
   weekday varchar(50) NOT NULL,
   start_time time NOT NULL,
   end_time time NOT NULL,
   FOREIGN KEY (ref_num)
   REFERENCES REFEREES(ref_id)
   ON DELETE CASCADE
   ON UPDATE CASCADE
);

Notice that each referee can have different time ranges for the same weekday

[grim vault]

if position is unique you can do it like:

select id,
  (select count(*) from Channel b
   where b.position <= a.position) as "row_number"
from Channel a
order by position

[stray moss]

Let me try

[paper flower]

So you want to extract that time range into separate table?

[stray moss]

YOO IT WORKS

@grim vault thanks

[torn sphinx]

I'm not sure, I feel like there is a transitive dependency between start and end time with ref_num

[paper flower]

Hm, is there? 🤔

[torn sphinx]

Both start and end time are fully dependant on weekday

But I think that weekday do not depend on ref_num...

[paper flower]

How so? I don't quite understand how they could be dependant on a weekday here, they're logically related but there's no dependency

[torn sphinx]

I might have similar rows with the same ref_num and weekday and only start_time and end_time will change from one row to another

But i feel like I would have the same issue moving it to another table since I need to keep the relation with the referee

[paper flower]

Makes sense, but 3nf doesn't give you much in this situation imo

Times are quite individual, these are not concrete entities like country which is used in some examples

[torn sphinx]

I agree

Thank you so much

[stray moss]

UPDATE `Channel` AS `ch`, (
  SELECT `id`,
  (SELECT COUNT(*) FROM `Channel` AS `b`
   WHERE `b`.`position` <= `a`.`position`) AS `p`
   FROM `Channel` AS `a` WHERE `guildId` = 1
   ORDER BY `position` ASC
) AS `td` SET `ch`.`position` = `td`.`p` WHERE `ch`.`id` = `td`.`id`;

Error:

target: gnc.-.primary: vttablet: rpc error: code = InvalidArgument desc = Incorrect usage of UPDATE and LIMIT (errno 1221) (sqlstate HY000) (CallerID: planetscale-admin): Sql: "update `Channel` as ch, (select id, (select COUNT(*) from `Channel` as b where b.position <= a.position) as p from `Channel` as a where guildId = :vtg1 order by position asc) as td set ch.position = td.p where ch.id = td.id", BindVars: {#maxLimit: "type:INT64 value:\"100001\""vtg1: "type:INT64 value:\"1\""}

[grim vault]

if the timeslots are the same for each referee you can make a timeslot table and reference that.

[paper flower]

Just doesn't make a lot of sense in this context imo

If you change start or end time it would be different availability entry, so i wouldn't extract it into separate table

[torn sphinx]

Exactly

[stray moss]

anyone see any errors?

[torn sphinx]

I will leave it as it is

[celest fractal]

I am not the most knowledgeable but I use it for basic needs. What's up?

[stray moss]

#help-lemon

[devout yacht]

Hi, just a question can we solve integrityError

in pymysql

[faint blade]

What was this called?

Vileet..? or something?

[stray moss]

Vitess

it's the database system YouTube uses

[rocky arch]

Hey guys,
I have a database with stats from Discord users.
A table is basically

Discord_id, wins, coins

I want to get ever Discord_id exactly once. Exactly where "wins" is max. And I want to show all 3 values.

[stray moss]

@faint blade this is the update query

UPDATE `Channel` `ch`
INNER JOIN ( 
  SELECT `id` `id2`,
  ( SELECT COUNT(*)
    FROM (SELECT * FROM `Channel`) `b`
    WHERE `b`.`position` <= `a`.`position`
  ) AS `p`
  FROM `Channel` `a` WHERE `guildId` = 1
) `td` ON `ch`.`id` = `td`.`id2`
SET `ch`.`position` = `td`.`p`;
SELECT * FROM Channel;

[faint blade]

UPDATE `Channel` `ch`
INNER JOIN ( 
  SELECT `id` `id2`,
  ( SELECT COUNT(*)
    FROM (SELECT * FROM `Channel` ORDER BY `id`) `b`
    WHERE `b`.`position` <= `a`.`position`
  ) AS `p`
  FROM `Channel` `a` WHERE `guildId` = 1
) `td` ON `ch`.`id` = `td`.`id2`
SET `ch`.`position` = `td`.`p`;
SELECT * FROM Channel;

Have you tried something like this?

I added an ORDER BY

[stray moss]

just ran, doesn't work either

[faint blade]

Will you be running this query often? If you're violating a constraint, then you're probably not doing something you're meant to

[stray moss]

[stray moss]

No

What I am doing is

[faint blade]

Try flipping it to be descending: ```sql
UPDATE Channel ch
INNER JOIN (
SELECT id id2,
( SELECT COUNT(*)
FROM (SELECT * FROM Channel ORDER BY id DESC) b
WHERE b.position <= a.position
) AS p
FROM Channel a WHERE guildId = 1
) td ON ch.id = td.id2
SET ch.position = td.p;
SELECT * FROM Channel;

[vague nimbus]

I have a quick question on pymongo

[stray moss]

well still doesn't work

[faint blade]

Can you continue this thought?

[vague nimbus]

mycol = db["customers"]

If i just do this ^ will the collection be created if I don't insert any data?

[stray moss]

Ok but I have to go eat
Here:
I want to change the table from:

id    name    position
1     ch1     1
2     ch2     2
3     ch3     3

and move ch3 to position 1 but I need to update ch1 and ch2 because their positions need to move back by 1

id    name    position
3     ch3     1
1     ch1     2
2     ch2     3

Basically like discord channels... When you move it to a different position all the other positions being affected by it need to update by 1 or -1

[rocky arch]

Hey guys, (asked my question at a bad time, so here we go again 🙈 )
I have a database with stats from Discord users.
A table is basically

Discord_id, wins, coins

I want to get ever Discord_id exactly once. Exactly where "wins" is max. And I want to show all 3 values.

My thoughts: "DISTINCT", but I can only get one column.
"GROUP BY", but "GROUP BY" does not give me the max win value.

(currently the last one was executed).
Would work fine, if I could use the "ORDER BY id" before the "GROUP BY".

[faint blade]

I am sorry I have been searching around for a while but could not find anything easy to fix this. Especially for such a niche database as Vitess

[faint blade]

There are multiple rows with the same Discord_id, and you wish to select the row with the highest wins?

[rocky arch]

yes. with the highest "wins" or the biggest "id" (this would be the last entry, which would be the same as highest "wins")

So I want the top score from everyone

[rocky arch]

@faint blade sorry are you still here? you are currently my only hope 😂 👀

[stray moss]

I have my solution already but it just needs to update from the last row to prevent conflicts...

https://www.db-fiddle.com/f/fZY8dLTbtRFF3Nwe4rPT2L/0

this is the snippet

I need to fix the error

[faint blade]

I am sorry for the delay, I had to do something. You'll want something like: ```sql
SELECT * FROM UserData GROUP BY (Discord_id) ORDER BY wins DESC; -- You can add LIMIT 10 to select top ten

[rocky arch]

All good thanks.
This would not work. Because "Group By" does not take the highest "wins". And "Order by" would just order all users according to the wrong "wins". 🤔

[faint blade]

What do you mean "wrong 'wins'"?

[rocky arch]

Group by just gives me one "wins" for each user. And this "wins" is not the highest.

This is your command.
And as you can see, the "wins" are all very low. But they should be way higher.

I need something like "GROUP BY(user_id WHERE user_id = max).
I know this is not sql... but this is basically what I need

Right now its "GROUP BY(user_id WHERE user_id = min)" haha

[faint blade]

Can you try this? (adding MAX() over wins)```sql
SELECT * FROM UserData GROUP BY (Discord_id) ORDER BY MAX(wins) DESC; -- You can add LIMIT 10 to select top ten

[rocky arch]

No because "ORDER BY" just affects the values after "GROUP BY".

So GROUP BY "deletes" everything except one value. And I cant order one value

Order by has to be in front of Group by, but this does not work

[faint blade]

Ah, hmm, I found this: ```sql
SELECT Discord_id, MAX(wins) as max_win FROM UserData GROUP BY (Discord_id) ORDER BY max_win DESC; -- You can add LIMIT 10 to select top ten

[stray moss]

@faint blade hi

https://www.db-fiddle.com/f/fZY8dLTbtRFF3Nwe4rPT2L/0

I have the snippet here

could u help me try to fix the update error?

I think the error is caused by it updating 0.5 to 1 when it's already taken

but 1 is getting updated to 2 next so idk

it needs to somehow update starting from the last one

[faint blade]

Yeah I get what you mean, but I do not know the fix. Several sources even seem to say it's flat out impossible: https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-keys.html

In an SQL statement that inserts, deletes, or updates many rows, foreign key constraints (like unique constraints) are checked row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records that it must examine. MySQL checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. This means that it is not possible to delete a row that refers to itself using a foreign key.

[rocky arch]

Okay I still try to understand how it works... but I think it works. Thank you so much!

[faint blade]

Hurray . What's confusing about it? The order of what's evaluated maybe?

[rocky arch]

yes exactly

because only "SELECT user_id, MAX(wins) FROM UserData" gives me one result

[faint blade]

Yeah that's because you select the user ID with the highest amount of wins

[stray moss]

this is honestly so sad

[faint blade]

I don't fully understand it either, but by having MAX(wins) as max_win we evaluate the highest number of wins before/during the GROUP BY which we can later use in ORDER BY I suppose... maybe? 😅

I do not have much experience with GROUP BY and other aggregate functionalities like HAVING

[rocky arch]

Yeah I thought the same, but it also works without "as max_win" haha

[faint blade]

Yeah - like often recommended in different Stack Overflow comments you'll have better luck with PostgreSQL because it follows the SQL standard quite closely.

[faint blade]

How does it look like then? Do you just have ORDER BY MAX(wins)?

[stray moss]

I've been trying to solve this issue for the past week lol, every day I advance one step and now this is the last step and it doesn't work

[rocky arch]

The difference is, that "order by wins" does not work correctly.

But thank you so much. This is exactly what I need 🙏

[faint blade]

Try doing SELECT user_id, MAX(wins) wins FROM UserData GROUP BY (user_id) ORDER BY wins DESC;. That will probably show the wins column in order (which was picked arbitrarily out of all rows present).

[faint blade]

Is there actually any kind of deeper reason you picked Vitess? I have never heard of it before

[rocky arch]

yeah that works 👌

awesome

[stray moss]

Nope, it just advertised free cloud hosted 10 GB and I was like "sure, vitess must be like postgres, they're both SQL! right?"

then I read "YouTube uses Vitess" and I was like "This must be a good database system"

[storm mauve]

seems like it was created by Youtube for Youtube? which might mean that it is overly specialised

[faint blade]

-because the command you're doing is veeerryyy harmful for performance. See, since you're modifying close to every single row your database will need to lock all of those rows in the entire database which means that no other connection can update those rows (it may affect other operations as well, I am not sure which operations can run concurrently)

[faint blade]

😅 if only... PostgreSQL is closest to the SQL standard

Can't you host a PostgreSQL instance locally? That's what I do on my free VPS.

[stray moss]

oh, sad. What's another way to handle something like discord channel positions then?

[stray moss]

I don't have a VPS the website is hosted statically with serverless functions as api

[faint blade]

Well, first I would recommend thinking over it.. do you really need to store positions?

[stray moss]

then how would I know what order the channels are

[faint blade]

Which serves what purpose?

[stray moss]

the client should be able to move channels up and down

so like

# ch1
# ch2
# ch3

they should be able to drag ch3 and put it in front of ch1

[stray moss]

I can store positions in the browser localstorage... but what if they use multiple devices often? that would make it really messy and for new members too

[storm mauve]

anyway I'm a bit late and scrolled through briefly but that highest wins per player might be a PARTITION BY use case? not sure

[faint blade]

I.. don't think so? Legit never heard of it lol

The elements of the PARTITION BY list are interpreted in much the same fashion as elements of a GROUP BY clause, except that they are always simple expressions and never the name or number of an output column. Another difference is that these expressions can contain aggregate function calls, which are not allowed in a regular GROUP BY clause. They are allowed here because windowing occurs after grouping and aggregation.

From PostgreSQL

[faint blade]

I mean, really, the second question is just to reconsider the constraint. Do you really need it to be unique, afaik Discord positions aren't guaranteed unique.

Discord sends a CHANNEL_UPDATE for each affected channel iirc

[stray moss]

wait that's a good point...

then how....?

[faint blade]

Order by snowflake

[stray moss]

yeah but how are the positions being ordered??

if only one CHANNEL_UPDATE gets sent

@faint blade just checked Discord's gateways -
in ready event they do give a unique position field for each channel object

[faint blade]

Categories are ordered by positions, within categories channels are ordered by order. If order is equal then I it's determined by its ID but I can't remember whether older/newer snowflakes come before/after

[faint blade]

It says "the position will always be unique"?

[stray moss]

no I just looked at the payload

in a guild JSON hashmap there's a "channels" key its value is an array and each channel object has a unique position field

[faint blade]

That's like going for a walk in the forest and determining that you're the last human alive 😅

Observed behaviour doesn't have to be guaranteed 😬

You observe that you're the human there, but you can't guarantee that you are

[stray moss]

OK let me ask some discord api wrapper maker, maybe they know discord channels actually work

[faint blade]

I am failing to find the issue/discussion on the Discord API docs where a code snippet was shared

[stray moss]

I looked deeper, seems like category channels' positions are other channels' positions

but it means "where" the category channel goes

splits up things

but the text channels, their position doesn't repeat

I think it's only unique for text channels

[stray moss]

@faint blade so I continued to explore the discord API

I found out that when I have 10 text channels, and I move the 10th to 1st

discord will send 10 CHANNEL_UPDATE events for each channel

[faint blade]

Yes ^^

[stray moss]

now that's another problem 😭

I can update now

but I can't send multiple channel_update events

[grizzled swift]

what would be the best way to store value A and value B on a json, where value B depends on value A? for example user -> choice

i was thinking list of dicts but that is overkill and i already messed it up 😅

[weak mist]

Hello everyone! I have this problem with marshmallow and sqlalchemy. I don't know how to serialize 'join results'. I want schema to serialize all result data (including nested data), but I don't get them out anyhow. It's all in the code and comments. I hope someone will have time and eye to see the problem and get back with clue. Thanks in advance. https://paste.pythondiscord.com/xojivadobo

[paper flower]

You're trying to serialize result of sqlalchemy core query, try using orm

[weak mist]

@paper flower reading documentation, I didn't even know there is difference

thanks for pointing out

[paper flower]

Basically you have to replace

with engine.connect() as conn:
    result = conn.execute(select(User, Address.email_address).select_from(User).join(User.addresses).where(User.id == 1)).first()

with

with Session() as session:
    stmt = select(User).options(selectinload(User.address)).filter(User.id == 1)
    user = await session.scalar(stmt)

selectinload would load related entities using separate select ... where id in ... query, there are other loading methods btw

And session.scalar should return a single result

[weak mist]

@paper flower thanks, I wasn't even aware this core query and ORM stuff

I copied your reply and got whole section in documentation on this topic

will be reading today I guess

😉

[paper flower]

You can get general overview or core and orm here https://docs.sqlalchemy.org/en/14/tutorial/index.html#unified-tutorial

[weak mist]

thanks man, I appretiate it. You probably saved me tons of time

[patent portal]

Hello people. I'm trying to get the average difference between travel date and booking date.

Here's my dummy data.

Here's what I've done so far:

SELECT  bookingcustomeremailaddress, AVG(traveldate - bookingdate) as avg_days
FROM FLIGHT
GROUP BY bookingcustomeremailaddress

here's the output:

what am I doing wrong? TT

[keen forge]

does it have anything todo with

the fact that they are date data type ??

[grim vault]

You sure that they are date datatypes and not just strings? Which DB is that?

[patent portal]

yep, their date datatypes

i made the dummy data

[patent portal]

mysql

anyway it's been solved already. thanks though!

[round perch]

Hey guys, I am a beginner to Django and i have some database question puzzling me.

I hope to seek some advice here.

So I am aware that Django gave us a default user model.
Now, when i created another model class Employee with fields like gender, birthday, as well as i wanted to include first_name, last_name (both which exist originally in User model).

My first question:
Is the best way to link both classes using OneToOneField?

My second question:
Apart from OneToOneField linking the User. Is it possible to have OneToOneField for both first_name and last_name under Employee model?

Or if i should just add a normal CharField for firstname and lastname under Employee model and ignore the default firstname and lastname field in the given User model?

What I am trying to achieve is I want the user to be able to edit their first name and last name by accessing the ModelForm of Employee instead of ModelForm of the User model.

Or if there's a better way to handle my situation?

Here's my Employee class.

class Profile(models.Model):
    FEMALE = 'F'
    MALE = 'M'

    GENDER_CHOICES = [
        (FEMALE, 'Female'),
        (MALE, 'Male'),
    ]

    # user_id = models.ForeignKey(User, on_delete=models.CASCADE, related_name="employee")
    user = models.OneToOneField(User, on_delete=models.CASCADE)
    
    # Should i use OneToOneField here?
    fn = models.CharField(max_length=50)
    ln = models.CharField(max_length=50)

    gender = models.CharField(max_length=20, choices=GENDER_CHOICES)
    designation = models.CharField(max_length=50)
    phone_number = models.CharField(max_length=15)
    date_joined = models.DateField(auto_now_add=False)
    manager_id = models.ForeignKey(User, on_delete=models.CASCADE, related_name="manager")
    department_id = models.ForeignKey("Department", on_delete=models.CASCADE, related_name="employee_department")
    address = models.TextField()

[unreal portal]

Hi, I have a table in this schema, it has contracts information, like where the job is, what is the salary, if it has a retirementplan etc...
Id, Continent, Country, City, Industry, Job, Salary, RetirementPlan, HealthInsurance, and it continues...

And I'm trying to get average values for the contracts in cities, I want to know what is the average contract in Amsterdam, like, what is the average salary, does they have Health insurance in general or not, etc... For the all columns, if it is a continues value, I want to get the average, if it is categorical string value, I want to get the most frequent string.

For continues values, I have a query which gives it to me, but I'm stuck at getting the most frequent string categorical values in columns.

SELECT c."Continent", c."Country", c."City", avg(c."Salary")
FROM "Contracts" c
GROUP BY c."Continent", c."Country", c."City"

For example, in RetirementPlan column, the possible values are: "Yes", "Very rare", "Not common". What should I add to this query, to get what is the most frequent value for a city.

So it should return something like
Europe | Netherlands | Amsterdam | 3000 | Yes

3000 is avg(salary), Yes is the most frequent value for amsterdam, in the column RetirementPlan

[unreal portal]

We can imagine it like
Table:

Continent | Country     | City      | Salary | RetirementPlan
Europe    | Netherlands | Amsterdam | 1000   | Yes
Europe    | Netherlands | Amsterdam | 2000   | Yes
Europe    | Netherlands | Amsterdam | 3000   | Very rare
Europe    | Germany     | Berlin    | 4000   | Not common
Europe    | Germany     | Berlin    | 5000   | Yes
Europe    | Netherlands | Berlin    | 6000   | Not common

I want to get

Continent | Country     | City      | Average Salary | FrequentRetirementPlan
Europe    | Netherlands | Amsterdam | 2000           | Yes
Europe    | Germany     | Berlin    | 5000           | Not common

I'm still looking for the solution

[stray moss]

UPDATE tableName
SET ... = ...
ORDER BY fieldName DESC;

I want to do something like this in MySQL v8 but they removed this feature from 5.7

[grim vault]

There is a Berlin in the Netherlands?
I can only think of:

SELECT Continent, Country, City, avg(Salary),
  (select C.RetirementPlan from (
    select B.RetirementPlan, count(*)
      from Contracts B
      where (B.Continent, B.Country, B.City) =
            (A.Continent, A.Country, A.City)
      group by 1
      order by 2 desc limit 1
  ) as C)
  FROM Contracts A
 GROUP BY Continent, Country, City;

[unreal portal]

Thank you! Everybody knows that the Berlin has started to move around the world.

🙂

[stray moss]

yo guys how do I make NOT NULL constraint in MySQL only apply on a condition

[rough hearth]

Anyone ever worked with neo4j? I'm using it for a project at work and I'm interested to know how others feel about it, just in general.

[keen minnow]

It was proprietary and we weren't too enthusiastic about their clustering modes

[rough hearth]

Interesting. What would you have used instead, given the choice?

[keen minnow]

We looked at neo4j, dgraph, janusgraph and another one I forgot the name of.
We ended up with janusgraph + bigtable.

But overall the state of distributed graph DBs has a lot of room for improvements and I would suggest to also evaluate if a regular mysql/postgres would work. But that's also highly dependent on your workload

[storm mauve]

I played around with it a bit a while ago (just for testing it though, nothing big or serious) - it is interesting, but designing the database schema/model properly (efficient and easy to work with) can be nontrivial even for things that should™️ be simple.
https://maxdemarzi.com/2015/08/26/modeling-airline-flights-in-neo4j/ for example ; that guy seems to know what he's doing... but I'm not sure where should you draw the line for the efficiency vs usage trade-off

[stark crypt]

can anyone help me🖐️

mysql-connector-python

[upper rampart]

hey i want to select a specific row in a table in php how would i do that.

[midnight cedar]

Anyone know any free resources I can use to learn DynamoDB with Python

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @torn sphinx until <t:1647764616:f> (9 minutes and 59 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

[snow niche]

await self.bot.db.execute("DELETE FROM modlogs WHERE guild_id=$1", guild.id)
``` will this delete the row from a table of the specified guild? (pgsql)

[grim vault]

Assuming the asyncpg module is used, it will delete all rows with that specific guild id.

[snow niche]

yup thanks!

[grim vault]

Don't forget to commit

[burnt turret]

i think asyncpg autocommits by default

[undone lichen]

why is it giving me an error if i try inserting data in this form

mysql

[torn sphinx]

Hello the following table structure makes sense?

CREATE TABLE PRI_KEY(
   pk int8 NOT NULL
);
CREATE TABLE FOREIGN_KEY(
   fk int8,
   FOREIGN KEY(fk)
   REFERENCES PRI_KEY(pk)
   ON DELETE SET NULL
   ON UPDATE CASCADE
);

[wild prairie]

Hi guys,
I have a flask app, so when I hit on routes which are not in the same file, it always throws 404 not found error:
Directory structure:

-> user
    -> __init__.py
    -> models.py
    -> routes.py
-> app.py

app.py:

from flask import Flask
app = Flask(__name__)

from user import routes

@app.route('/') # THIS ROUTE WORKS
def index():
  return '<h1>HOME</h1>'

if __name__ == '__main__':
    app.run(debug = True)

routes.py:

from app import app

@app.route('/register', methods=['GET']) # THIS DOESN'T WORK - 404 NOT FOUND ERROR
def register_user():
  return '<h1>Register a user!</h1>'

[lament notch]

someone brought an interesting question to the dpy server, i suggested they ask here but seems they didn't.

CREATE TABLE album_position(
    album_id int REFERENCES album,
    image_id int REFERENCES image,
    position int
)

with a table like this, what would be the best way to keep position as the index of the image in the album, and be able to re-order them
like we have 5 images, positions 1,2,3,4,5 and want to move the one at position 5 to position 2, changing its position and updating position of all rows after
is there a better structure or way of thinking about this or does sql have some "easy" way to do this? (postgres specifically)

my first instinct is to do something like the following (or sql equivalent)

rows = execute('SELECT * FROM album_position WHERE album_id = ? ORDER BY position', (album_id,))

rows[new_index] = rows.pop(old_index)
start_update_at = min(new_index, old_index)

update_rows = [position, row.image_id for position, row in enumerate(rows[start_update_at:], start=start_update_at+1)]
executemany('UPDATE album_position SET position = ? WHERE image_id = ?', update_rows)

[pure cypress]

Arrays might be an alternative, but I think the lack of referential integrity makes them not worth it.

What you are doing makes sense, but I believe you should be able to do it with fewer queries

Use a where clause to limit the rows you're updating based on their position, and then just add/subtract from its position

[grim vault]

You'll also need the album_id in that update statement. But what if the image is not unique within the album? It won't work then.

[lusty kelp]

def getGuildData(guild):
    #BRUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUuu

    return guilds.find({ "guildid": int(guild.id) })```
this is for pymongo

it always returns nothing but in the mongo database
{"_id":{"$oid":"62364adbdcb4674fac34568b"},"guildid": {"$numberLong":"947945871692148756"}
its right there

[wraith shell]

Can anyone help out with a very specific SQLAlchemy question about scoped_session and query object

[jolly heron]

want to create a loop or something. all my images are in order. like part_1, part_2, part3 . want code to automatically do code for like part[i+1].png

from skimage import io, img_as_float
import numpy as np

image= io.imread(r'C:\Users\guest\Dropbox\con1_outfolder_split_30sbeforepeak2min30safterpeak\part_1.png')
image = img_as_float(image)
print(np.mean(image))

[steel lagoon]

hello

i have a question on py mongo

why does this find one id and not the correct info i need and how can i fix it so it finds what i need and provides all info

    @commands.Cog.listener()
    async def on_member_join(self,member : discord.member):
        results = collection.find()
        for result in results:
            if result["_id"] != member.guild.id : return
            else:
                for query in result:
                    if str(query) == "_id":
                        pass
                    else:
                        server = result[query]
                        channel = self.client.get_channel(server[0])
                        first_message = server[1]
                        second_message = first_message.replace("{member.mention}",f"{member.mention}")
                        third_message = second_message.replace("{member}",f"{member}")
                        final_message = third_message.replace("{member.guild}",f"{member.guild}")
                        await channel.send(f"{final_message}")```

[alpine orchid]

do you still need help with this?

[lean ravine]

if data := collection.find_one({"_id": member.guild.id}):
    ...

Dont use pymongo for discord bots, cause its blocking, use motor (async wrapper for mongo in py)

how can i fix it so it finds what i need and provides all info
can you tell how your data looks like? 🤔

[neon bronze]

Best package to use for MongoDB? i'm newbie with mongo so i'm searching for something which will help me to learn to, with a good docs if it is possible

[lean ravine]

Best package to use for MongoDB?
pymongo (https://pymongo.readthedocs.io/en/stable/)

But if you want in async-await/concurrency use motor (https://motor.readthedocs.io)

[rigid crown]

whats a good database to use for low activity databases?

[brave bridge]

Depends on what you need

[lean ravine]

sqlite3

[brave bridge]

Sometimes you can just use the file system. Sometimes SQLite3 is the right choice. Sometimes you might just want a single JSON file.

[rigid crown]

just a database that i can add more overtime, no big amounts of data. but just enough that a database is needed

[brave bridge]

how much data do you plan to store? and what kind of data? and how do you want to query it? and how often to read/update?

[rigid crown]

its a username and password system, with a boolean added for verified users, and one for unverified.

like email verification

[lean ravine]

personally, i dont prefer .json .txt as db. i suggest you to use sqlite3

[brave bridge]

how many users will you have?

I think you should be good with SQLite.

[rigid crown]

like 50 at LEAST

[brave bridge]

SQLite should be fine, even for thousands or tens of thousands of users

Also, I hope you will remember to hash the passwords:

• How NOT to Store Passwords! (with subtitles) https://www.youtube.com/watch?v=8ZtInClXe1Q
• see bcrypt https://pypi.org/project/bcrypt/

[neon bronze]

i'm getting some issue:

from motor import motor_asyncio

client = motor_asyncio.AsyncIOMotorClient('localhost', 27017)
db = client.test_databse
collection = db.test_collection

async def do_insert():
    document = {'id': 1}
    result = await db.test_collection.insert_one(document)

import asyncio
loop = asyncio.get_event_loop()
loop.run_until_complete(do_insert())

there is no current event loop

[brave bridge]

Just do asyncio.run(do_insert())

[neon bronze]

attached to a different loop

[brave bridge]

Can you show the full error and your new code?

[rigid crown]

thanks

[neon bronze]

traceback:

Traceback (most recent call last):
  File "C:\Users\giacomo.dimatteo\PycharmProjects\MongoTest\main.py", line 12, in <module>
    asyncio.run(do_insert())
  File "C:\Users\giacomo.dimatteo\AppData\Local\Programs\Python\Python310\lib\asyncio\runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "C:\Users\giacomo.dimatteo\AppData\Local\Programs\Python\Python310\lib\asyncio\base_events.py", line 641, in run_until_complete
    return future.result()
  File "C:\Users\giacomo.dimatteo\PycharmProjects\MongoTest\main.py", line 9, in do_insert
    result = await db.test_collection.insert_one(document)
RuntimeError: Task <Task pending name='Task-1' coro=<do_insert() running at C:\Users\giacomo.dimatteo\PycharmProjects\MongoTest\main.py:9> cb=[_run_until_complete_cb() at C:\Users\giacomo.dimatteo\AppData\Local\Programs\Python\Python310\lib\asyncio\base_events.py:184]> got Future <Future pending cb=[_chain_future.<locals>._call_check_cancel() at C:\Users\giacomo.dimatteo\AppData\Local\Programs\Python\Python310\lib\asyncio\futures.py:384]> attached to a different loop

code:

from motor import motor_asyncio

client = motor_asyncio.AsyncIOMotorClient('localhost', 27017)
db = client.test_databse
collection = db.test_collection

async def do_insert():
    document = {'id': 1}
    result = await db.test_collection.insert_one(document)

import asyncio
asyncio.run(do_insert())

[balmy scaffold]

Not really python, but does anyone here some understanding of mysql? Im trying to order this table in order of the numbers that comes within the table after the letters, in the column emnekode i want to put in all data with xxx1000 before its showing the data with xxx2000

[brave bridge]

you should generally initialize all the async objects inside an async function. Like this:

import asyncio
from motor import motor_asyncio

async def do_insert():
    client = motor_asyncio.AsyncIOMotorClient('localhost', 27017)
    db = client.test_databse
    collection = db.test_collection

    document = {'id': 1}
    result = await db.test_collection.insert_one(document)

asyncio.run(do_insert())

[neon bronze]

i'll try

Yeah no error, i'll check if that did something

[lean ravine]

AsyncIOMotorClient, can takes io_loop 🤔 iirc

loop = asyncio.get_event_loop()
client = AsyncIOMotorClient(..., io_loop=loop)

[neon bronze]

btw I don't find any changes in the database

[brave bridge]

asyncio initially supported passing the event loop everywhere, but later it dropped that. So now libraries are slowly deprecating this.

(and you shouldn't be passing the event loop around)

[lean ravine]

righ right, Python 3.10 removed the loop parameter,

[proven arrow]

You have not specified ordering of that column yet, and also the column is a string so to order by the integer part, you can get the substring of it and order by that value.

[bleak bough]

ohh i freaking forgot commas

[half forum]

is json a database

[brave bridge]

A "database" is some place where you persist data, and which you can query for the data you saved.

JSON is a data representation format.

However, a JSON file could be used as a "database"

[half forum]

well if it is, how would I add more items in an array
Like ```py
[
{
"guild_id": 9129390129309123 {
"users": 123123123123,
"users": 123123123123123,
}
}
]

Some thing like that

but when I do, each time the "users" gets written over by the new one

I try to use different methods and i get AttributeError: 'dict' object has no attribute 'append'

[brave bridge]

!e
A dictionary maps keys to values. It doesn't make sense to append to a dictionary, you can only set a key in it.

users = {}
users["alice"] = {"balance": 420}
users["bob"] = {"balance": 69}
print(users)
users["alice"] = {"balance": 10}
print(users)

[delicate field]

@brave bridge :white_check_mark: Your eval job has completed with return code 0.

001 | {'alice': {'balance': 420}, 'bob': {'balance': 69}}
002 | {'alice': {'balance': 10}, 'bob': {'balance': 69}}

[brave bridge]

Can you tell more about what you want to store?

[half forum]

Ok, so this is for a discord bot
I need to use the command !add pilot <userID> right

file is pilot.json, and each time it is ran, each server should have its own array

[brave bridge]

What does the command do?

ah

[half forum]

it adds the userID to the json file , and saves it for another command called queueing

Queuing will be a whole other thing that ill figure out but all of my things are stored in a json

[brave bridge]

You could have a dictionary mapping the guild ID to a list of users, like:

{
  "pilots": {
    "9129390129309123": [123, 456],
    "9129390129309123": [456, 789]
  }
}
``` or 
```json
{
  "9129390129309123": {
    "pilots": [123, 456]
  },
  "9129390129309123": {
    "pilots": [456, 789]
  }
}

[half forum]

well I like the last one more..

How would I do that?

[brave bridge]

do what? Create such a dictionary?

[half forum]

Yep, and append new users each time

[brave bridge]

Do you know how to work with dictionaries and lists?

[half forum]

well

with open("balh.json", "r") as f:
  d = json.load(f)
  
d[str(ctx.guild.id)] = {}
d[str(ctx.guild.id)]['pilots'] = []

with open("balh.json","w") as f:
   json.dump(d, f)
```?

would it be that?

[brave bridge]

That would work, if there is no such guild in the file yet

[half forum]

Ok, and if I wanted it like this

Would I do like

var = [userID] and then d[str(ctx.guild.id)]['pilots'] = var

[brave bridge]

That would overwrite the list of pilots with a list of just that one user

[half forum]

that again, how do I add to it?

[brave bridge]

Use the append method on the list

[half forum]

so d[str(ctx.guild.id)]['pilots'].append(var)

[brave bridge]

sounds correct

[half forum]

alright, ty, imma try

[half forum]

    d[str(123981923123123)]['pilots'].append(var)
AttributeError: 'str' object has no attribute 'append'```

ill be afk for a bit so brb

[brave bridge]

Can you show what d is?

The error is saying that you're trying to append to a string.

[river matrix]

Hey all, pardon me. Can someone please point me in the direction of good DB practice sites for beginners. Projects, exercises or whatever to sharpen my 'skill'

[brave bridge]

If you're just starting out, I recommend SQLBolt: https://sqlbolt.com/

Then you might want to make a simple application, like a To Do list. Python conveniently comes with an interface to SQLite: https://docs.python.org/3/library/sqlite3.html

[river matrix]

I see, thank you very much @brave bridge I'm on these right now.

[half forum]

You mean print d right? D is the json.read

[brave bridge]

right

[steel lagoon]

What is motor

[grim vault]

!pypi motor

[delicate field]

motor v2.5.1

Non-blocking MongoDB driver for Tornado or asyncio

[tall quiver]

hi

i need help with sqlite3 i keep getting an error ive had it for 3 days and havent managed to fix it

its getting frustrating

[brave bridge]

@tall quiver when asking a question, please provide the details: the code, the error, and everything else relevant

otherwise we don't know what to suggest you

[tall quiver]

i have in one of the help channels

but i will again here

soo basically

i have this save function

        def save():
            global password, username
            password = passwordCreateEntry.get()
            username = usernameCreateEntry.get()
            
            insert_password = """INSERT INTO masteraccount(password)
            VALUES(?) """
            cursor.execute(insert_password, [password])
            db.commit()
            
            insert_username = """INSERT INTO masteraccount(username)
            VALUES(?) """
            cursor.execute(insert_username, [username])

            db.commit()
            loginWindow()
            messagebox.showinfo("Account Created", "Account has been successfully created")
            

sqlite3.IntegrityError: NOT NULL constraint failed: masteraccount.username

and i keep getting this error

# Database for accounts 
with sqlite3.connect("account_vault.db") as db:
    cursor = db.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS masteraccount(
id INTEGER PRIMARY KEY,
password TEXT NOT NULL,
username TEXT NOT NULL);
""")

[brave bridge]

let's move to #help-apple then

[tall quiver]

okay

[steel lagoon]

I will not use motor and want to stick with pymongo but why is it giving this error?

@commands.Cog.listener()
   async def on_member_join(self,member : discord.member):
       results = collection.find_one({"_id": member.guild.id})
       for result in results:
           print(result)
           if result["_id"] != member.guild.id : return
           else:
               server = result[query]
               channel = self.client.get_channel(server[0])
               first_message = server[1]
               second_message = first_message.replace("{member.mention}",f"{member.mention}")
               third_message = second_message.replace("{member}",f"{member}")
               final_message = third_message.replace("{member.guild}",f"{member.guild}")
               await channel.send(f"{final_message}")``` why is it only giving the _id as the result and not the value?

[harsh pulsar]

1. find_one returns a single result document, not an iterable of results
2. a document is returned as a dict
3. iterating over a dict iterates over its keys, which in this case are strings

that should be enough information to help you understand the error 🙂

[steel lagoon]

yup it is way to complicated for what i am trying to do lol

[harsh pulsar]

what do you mean?

[steel lagoon]

let me give you the updated one

[steel lagoon]

    @commands.Cog.listener()
    async def on_member_join(self,member : discord.member):
        results = collection.find_one({"_id": member.guild.id})
        for query in results:
            print(results)
            if results["_id"] != member.guild.id : return
            else:
                server = results[query]
                channel = self.client.get_channel(server[0])
                first_message = server[1]
                second_message = first_message.replace("{member.mention}",f"{member.mention}")
                third_message = second_message.replace("{member}",f"{member}")
                final_message = third_message.replace("{member.guild}",f"{member.guild}")
                await channel.send(f"{final_message}")```

[harsh pulsar]

same problem

for query in results

[steel lagoon]

that is what i am stuck on now

[harsh pulsar]

but results can only ever be 1) a dict, or 2) None

so you are still iterating over a dict, which means you are iterating over keys

[steel lagoon]

so how do i fix this so it works properly

[harsh pulsar]

what are you trying to do

[steel lagoon]

create a fully customize able welcome message i using the database

[harsh pulsar]

that's too general

more specifically, what are you trying to do

how is this code intended to work

[steel lagoon]

ok
server[0] is susposted to get the channel
server[1] gets the message it sends everytime a person joins

[harsh pulsar]

that's a weird format

so you actually do want to iterate over the keys of the result?

i would argue that this is not a good document layout, but if it's already done then it's not worth changing it

it looks like there are only ever 2 fields on the document, _id and the actual id as a key

is that right?

i'm talking about the database layout, not your code

database layouts do not arise naturally from the fog of time

you, the programmer, chooses it

now it's possible that you have buggy code writing unintended data to the database

in which case your first job is to fix that code, instead of trying to press on working with an incorrect database

[steel lagoon]

well the _id is the id of the guild, the other id is for the array because i had nothing better to put there

[harsh pulsar]

what does this database actually contain?

for that matter, why are you even using mongodb? this is reason #1 why i recommend against it unless you are already an experienced developer, it encourages bad stuff like this

i saw it the first time. what do these values mean?

[steel lagoon]

the id of the server, the channel it sends to, the message it sends to that channel everytime a member joins

[steel lagoon]

and i will not use a json file and no sql

[harsh pulsar]

what's wrong with sql?

why wouldn't you store it in this format?

{
  "_id": 12345,  // opaque document id, created by mongo itself
  "server_id": 9876,  // discord guild id
  "welcome_channel_id": 4567,  // welcome channel id
  "welcome_message": "Hello!"  // welcome message text
}

this maps 1:1 to a single sql table

[steel lagoon]

well the _id would be the server id so then just 2 fields

[harsh pulsar]

sure, that's fine too

[steel lagoon]

i find it very hard to learn

[harsh pulsar]

CREATE TABLE welcome_messages (
  server_id BIGINT PRIMARY KEY,
  welcome_channel_id BIGINT,
  welcome_message TEXT
)

[steel lagoon]

i have tried once

[harsh pulsar]

sql forces you to organize your data this way

mongodb does not, but it's still the best way to organize data in 99% of applications