databases

[crisp nymph]

on the web somehow?

like I am used to firestore

[torn sphinx]

maybe a stupid question but, would sqlite work on a VPS server for hosting a discord bot? since its a local file

ive never worked with VPSes before

[proven arrow]

@torn sphinx Yes would work

[gloomy pike]

Is there a way to return the deleted rows in sqlite

or at least get if any rows were deleted at all

or will I have to make another query

[harsh pulsar]

@torn sphinx a sqlite database is just a regular file, so if you can read/write files on the filesystem you can use sqlite

[shell ocean]

or at least get if any rows were deleted at all
@gloomy pike I don't think you can in SQLite (but I could be wrong)

[worn nimbus]

I have a doubt i think it's related to this channel. I'm working on a trello clone and i'm using python + flask with postgreSQL and sqlalchemy.

I'm doing a validation, i want to know if the user is the board's owner before creating a new list (where tasks are saved). Would it be ok if first i fetch the board, check if the user actually owns the board, and then, create a list?

Example:

@board.route('/boards/<board_id>/new-list', methods=['POST'])
@protected_route
def boards_new_list(board_id):
    req_data = request.get_json()
    user_id = g.user.get('id')
    title = req_data.get('title')

    if title is None:
        return jsonify(msg="Missing param: title"), 401

    requested_board = Boards.query.filter_by(id=board_id).first()

    if requested_board.user_id is not user_id:
        return jsonify(msg="You can't perform this action."), 403

    new_list = Lists(board_id, title)
    db.session.add(new_list)
    db.session.commit()

    if new_list is not None:
        return jsonify({
            "id": new_list.id,
            "title": new_list.title,
            "tasks": []
        }), 200

Or would it be a bad practice / performance? i'm moving into python and learning sql

[limber trail]

I'm using postgres/asynpg on python and I heard that putting returning at the end of the statement was supposed to return the column specified but I'm just getting the status of the operation back. Any help is appreciated

[harsh pulsar]

@worn nimbus that seems fine to me. the lookup should be fast because it's using the primary key. however don't write is not when you mean to write !=. is and is not are low-level checks of whether the objects have the same "id", which in cpython is just a memory address. it's not what you want in 99% of cases.

[worn nimbus]

Cool! thanks!

Oh i got it, i thought is and is not were only a more pythonic way of doing it but i was wrong, gonna refactor all my comparisons haha ty

[harsh pulsar]

yw. is and is not are used a lot internally in python, but very rarely by normal users

[lean plover]

Would this be a good place for questions about file directories? Is a filesystem considered a database?

[granite saffron]

guys could you tell me the difference between lists and tuples?

pls ping me in ur answer

[lean plover]

guys could you tell me the difference between lists and tuples?
@granite saffron

[granite saffron]

yes?

[lean plover]

guys could you tell me the difference between lists and tuples?
@granite saffron tuple is immutable and list is mutable

[granite saffron]

yes i dont understand this lol

[lean plover]

means you can change lists after they have been declared.

tuples cannot be changed after declaration

[granite saffron]

mmmhhh...

so for change a tuple what i need to do

[lean plover]

You should be able to change the tuple to a list ---> list(some_tuple)

[granite saffron]

mm ok thx

[delicate field]

Hey @lean plover!

Uh-oh! It looks like your message got zapped by our spam filter. We currently don't allow .txt attachments, so here are some tips to help you travel safely:

• If you attempted to send a message longer than 2000 characters, try shortening your message to fit within the character limit or use a pasting service (see below)

• If you tried to show someone your code, you can use codeblocks
(run !code-blocks in #bot-commands for more information) or use a pasting service like:

https://paste.pythondiscord.com

[lean plover]

Would this be a good place for questions about file directories? Is a filesystem considered a database?

[blissful narwhal]

not really, but im not sure where you would ask that tbh haha

#tools-and-devops maybe?

or good 'ol off topic

[lean plover]

I should clarify that the question is about searching for writing a python script for searching for files in a directory (and subdirectories)

[blissful narwhal]

you maybe want to use the "glob" module?

[south cobalt]

is there a nice clean way more me to say delete all rows from all tables in my database

[blissful narwhal]

I should clarify that the question is about searching for writing a python script for searching for files in a directory (and subdirectories)
there is also os.walk

[lean plover]

Thank you but my question is more about the efficinet way to search for lists of files in a file directory and I have the os.walk part figured out: https://stackoverflow.com/questions/63716090/more-efficient-python-script-for-determining-if-many-files-exist-in-a-directory

*kinda

[blissful narwhal]

hmm, well I do not want to fill this channel up with off-topic.

maybe move this to devops or a help channel?

[lean plover]

Will do.

[south cobalt]

def resetall(self):
        cursor = self.cnxn.cursor()
        delete_query = '''EXEC sp_MSForEachTable 'DELETE FROM ?'  GO'''
        cursor.execute(delete_query)
        self.cnxn.commit()
        cursor.close

maybe someone could point out whats gone wrong here?

idk if the string i have for the del query is remotely valid just trying some stuff bassed off stack overflow answers not sure if it will apply to my library or db

[torn sphinx]

This is my rep command. I want to this to add +1 points every time a user is mentioned.

    @commands.command()
    @commands.guild_only()
    @commands.cooldown(1, 30, commands.BucketType.user)
    async def rep(self, ctx, user:discord.Member=None):
      db=cluster['UserData']
      collection=db["reps"]
      if user is None:
        await ctx.send('You need to specify who you want to rep.')
      elif user == user:
        data={
          "_id": user.id,
          "points": "1" # here. How do i add +1 ?
        }
        collection.upsert_one(data)
        await ctx.send(f'**You gave a rep to {user.mention}!**')

[burnt turret]

MongoDB's update statement has a $inc method which increments by the number you provide
(A fun thing about the $inc operator is that there's no corresponding operator to decrement a value, so you'd do $inc and put a negative value if you wanted to decrement)

collection.update_one({"_id":user.id},{"$inc":{"points":1}}

this increments the points field for the specified user by 1 @torn sphinx

also that user == user seems pointless, you can put an else there

[torn sphinx]

Thank you @burnt turret.

i didnt get pinged for some reason.

[burnt turret]

oh oof

[torn sphinx]

it didnt show notification

strange

oh @burnt turret one more thing. I can delete a document by doing delete_by_id(_id) right?

is it correct?

[burnt turret]

oh i'm not sure, lemme check

[torn sphinx]

okie

or is it delete_one?

[burnt turret]

oh oops sorry i was in class and i forgot

yeah delete_one is a thing @torn sphinx pretty sure you'd pass in the filter the same way you do for find
..delete_one({})

[torn sphinx]

alright

thanks

[burnt turret]

oh dont leave it as empty lmao that might delete the very first document btw, i just wanted to show how you'd do it

you put some parameter there, like "_id":member.id or something

[torn sphinx]

ok thanks for that

i’m trying to delete a warning i gave

so yeah

[burnt turret]

oh alright

the $inc worked?

[torn sphinx]

yes!

works

thanks

[burnt turret]

oh great, its been a while since ive used that lmao so i was a little unsure

[torn sphinx]

i forgot to add $ first

[burnt turret]

oh alright 👍

[summer jetty]

heyo what's a good online database I can use with python?

[shell ocean]

say I have two tables in SQL, inventory and item. inventory has a column max_capacity. item has a FK column to inventory, inventory_id. is there a way to add a constraint on item such that the number of rows with the same value of inventory_id will not be larger than the corresponding value of max_capacity?

I'm guessing I would need a materialised view...but is there any way to do without? (I'm using Django)

[crisp nymph]

Hey guys! I have a json file containing list of dictionary. Each dictionary looks like what I have below in the image. Now is it possible for me to sort of convert this json into sql? The thing that I cant think of is the ingredients key and the aliases

[torn sphinx]

try importing it

what are you using

[shadow bobcat]

https://paste.pythondiscord.com/luxoluhezi.sql
This is an error Im getting. I think its the database not being able to connect as that is mentioned in line 12 of the error. The only username that Im using is postgres, moonl is the name of my system, I don't know why that is mentioned in the error

[torn sphinx]

This is gonna sound really stupid, but how do I use fetchone() with aiosqlite?

I literally forgot how

Nvm, found it.

[torn sphinx]

@burnt turret so it says "You gave a rep to @user" but nothing is stored in the DB.

Here’s the code i have rn:

    @commands.command()
    @commands.guild_only()
    @commands.cooldown(1, 43200, commands.BucketType.user)
    async def rep(self, ctx, user:discord.Member=None):
      db=cluster['UserData']
      collection=db["reps"]
      if user is None:
        await ctx.send('You need to specify who you want to rep.')
      else:
        collection.update_one({"id": user.id},{"$inc":{"points":1}})
        await ctx.send(f'**You gave a rep to {user.mention}!**')

[burnt turret]

oh doesn't this data already exist in the db?

[torn sphinx]

uh update just updates it..

[burnt turret]

you'd have pass upsert=True as a kwarg in the update statement

[torn sphinx]

i forgot

yeah

thanks

[torn sphinx]

Sup

Can I add a value to my aiosqlite db?

Like if it's 2

And then I do a cmd with my bot

That adds 3

Then it says 5 there

You get it.

[quaint tiger]

say I have two tables in SQL, inventory and item. inventory has a column max_capacity. item has a FK column to inventory, inventory_id. is there a way to add a constraint on item such that the number of rows with the same value of inventory_id will not be larger than the corresponding value of max_capacity?
@shell ocean not sure about DB constraints, but you can easily do that in a model's validation phase... https://docs.djangoproject.com/en/3.1/ref/models/instances/#validating-objects
I think in clean(), you can just filter the item s on inventory_id, get the count, and raise django.core.exceptions.ValidationError if its too high.

Probably better to ask in #web-development if you have more questions about that.

[burnt turret]

https://paste.pythondiscord.com/luxoluhezi.sql
This is an error Im getting. I think its the database not being able to connect as that is mentioned in line 12 of the error. The only username that Im using is postgres, moonl is the name of my system, I don't know why that is mentioned in the error
bump. anyone know what the issue is?

[tepid cradle]

Hey guys! I have a json file containing list of dictionary. Each dictionary looks like what I have below in the image. Now is it possible for me to sort of convert this json into sql? The thing that I cant think of is the ingredients key and the aliases
@crisp nymph You don't have a list of dictionaries in a JSON file, you have "an array of objects" in a JSON file.
It is important to understand that JSON is not a python data type. JSON objects and python dictionaries are fairly similar, but they are not the same. For instance, a python dictionary can contain a Pandas dataframe or a Numpy array, but a JSON cannot.
To convert this to a tabular dataframe, you'll first have to load it into Python using the json library.
Next, you'll either have to split it (if you want to store the data in multiple tables) or flatten it (if you want to store in the same table).

[orchid flame]

how do i use the discord py package in visul studio code?

[tepid cradle]

say I have two tables in SQL, inventory and item. inventory has a column max_capacity. item has a FK column to inventory, inventory_id. is there a way to add a constraint on item such that the number of rows with the same value of inventory_id will not be larger than the corresponding value of max_capacity?
@shell ocean Thank you for asking this very interesting question. Here is your solution:

create table inventory_master (
    item_id serial primary key,
    item_name varchar(30),
    max_qty int
)

create table inventory (
    po_number serial primary key,
    item_id int,
    arrival_date date,
    order_qty int,
    rate numeric,
    value numeric,
    CONSTRAINT fk_inventory FOREIGN KEY(item_id) REFERENCES inventory_master(item_id)
)    

create function public.checkInventory(item int, d_qty int)
returns int
language plpgsql
as $$
declare total int;
declare max_q int;
declare allow int;
begin
    select sum(order_qty) into total from inventory where item_id = item group by item_id;
    select max_qty into max_q from inventory_master where item_id = item;
    if total + d_qty > max_q then
        allow = 0;
    else 
        allow = 1;
    end if;
    return allow;
end;
$$;

alter table inventory 
    add constraint check_max_q
    check(public.checkInventory(item_id, order_qty) = 1)
    
insert into inventory_master(item_name, max_qty) values 
    ('item_1', 10),
    ('item_2', 20)
    
INSERT INTO public.inventory
    (item_id, arrival_date, order_qty, rate, value)
values
    (1, '2020-08-01', 8, 20, 160), --gets inserted successfully
    (2, '2020-08-01', 15, 10, 150);

INSERT INTO public.inventory
    (item_id, arrival_date, order_qty, rate, value)
values
    (1, '2020-08-02', 8, 20, 160); --fails

select * from inventory

Explanation coming in 15 minutes

[crisp nymph]

@crisp nymph You don't have a list of dictionaries in a JSON file, you have "an array of objects" in a JSON file.
It is important to understand that JSON is not a python data type. JSON objects and python dictionaries are fairly similar, but they are not the same. For instance, a python dictionary can contain a Pandas dataframe or a Numpy array, but a JSON cannot.
To convert this to a tabular dataframe, you'll first have to load it into Python using the json library.
Next, you'll either have to split it (if you want to store the data in multiple tables) or flatten it (if you want to store in the same table).
@tepid cradle tabular dataframe?

I am not sure I understand

like, I was thinking how an array of strings would be stored in a single "cell".

and array of maps

[tepid cradle]

There's no cell in a database. You need to go through a few SQL tutorials first.
While it is possible to store JSON array/objects in SQL databases, I strongly advise against it, as that completely defeats the purpose of a relational database.
Flatten out your JSON, it's not that difficult.

@shell ocean First thing first, my answer is PostgreSQL specific. But it should be possible to replicate this in other databases using stored procedures.
In my above answer I have used a check constraint.
A check constraint doesn't allows you to refer another table directly, but it allows you to use a function, which, in turn, can refer to other tables. So I have written a function which takes the item id and the quantity being entered as parameters, then checks the current total, adds the quantity being inserted, and checks if the result is greater than the max_qty in the main table. If the quantity exceeds the max allowed, then it returns 0, otherwise 1.
The constraint is that the return value should be one. So if the return value is 0, then the constraint fails.
The last 3 statements are just for validation.

[astral gorge]

hey i have problem with mysql connectivity pls help

guys pls i need help

@tepid cradle

@burnt turret

i donot know mysql username and password and idk how to reset it pls help

[tepid cradle]

Don't ping people randomly. Just ask your question and wait.
I don't know the process for MySQL, but presumably, if it's a linux machine, you should be able to do it as the root user.
This is literally the first result from a Google search for "forgot mysql password"
https://support.rackspace.com/how-to/mysql-resetting-a-lost-mysql-root-password/

[astral gorge]

Beginning configuration step: Writing configuration file

Saving my.ini configuration file...
Path cannot be the empty string or all whitespace.
Ended configuration step: Writing configuration file

'

what should i do now

[shell ocean]

@shell ocean not sure about DB constraints, but you can easily do that in a model's validation phase... https://docs.djangoproject.com/en/3.1/ref/models/instances/#validating-objects
I think in clean(), you can just filter the item s on inventory_id, get the count, and raise django.core.exceptions.ValidationError if its too high.
@patryk.tech#4359 yes, that’s where I have my validation logic, but I want it to be a database-level constraint.

@shell ocean First thing first, my answer is PostgreSQL specific. But it should be possible to replicate this in other databases using stored procedures.
In my above answer I have used a check constraint.
A check constraint doesn't allows you to refer another table directly, but it allows you to use a function, which, in turn, can refer to other tables. So I have written a function which takes the item id and the quantity being entered as parameters, then checks the current total, adds the quantity being inserted, and checks if the result is greater than the max_qty in the main table. If the quantity exceeds the max allowed, then it returns 0, otherwise 1.
The constraint is that the return value should be one. So if the return value is 0, then the constraint fails.
The last 3 statements are just for validation.
@tepid cradle yup, I understand. thanks a lot for helping! I’m not sure how I’ll put that into Django, but I’ll figure it out.

[gloomy pike]

what's the least wordy way to check for two columns can be equal if both of them can be null?

sqlite3

[south cobalt]

is there a way to export my entire database to excel

i have 12 different unique tables and i want to export them all to the same excel sheet, they all have the same number of rows

[patent glen]

@gloomy pike sqlite3 you can just use "is"

[other databases you have to do "is not distinct from"]

[gloomy pike]

@patent glen sorry for ping, but it doesn't seem to be working

Here's the query

query = f"""
        Select * from {p}TagAliases
        Inner join {p}Tags on {p}TagAliases.Tag = {p}Tags.Name
        and {p}TagAliases.LocationID is {p}Tags.GuildID
        where LocationID = ? and Alias = ?
        """```
When I substitute a normal number for ``LocationID``, query gives me a regular row, but when I put ``None`` for LocationID then printing query gives me ``None``

[grave scaffold]

printing query?

[gloomy pike]

well, printing the select from query, but I didn't think it was necessary to include that

[tepid cradle]

what's the least wordy way to check for two columns can be equal if both of them can be null?
@gloomy pike

SELECT * FROM my_table
  WHERE (col1 = col2) or (col1 is null and col2 is null)

[gloomy pike]

alright i'll try that

@tepid cradle is it possible to do that inside an inner join?

[grave scaffold]

When you say “substitute” you mean you’re providing None as one of the bind variables, right? Not literally substituting None in the text

[gloomy pike]

yes

None as null None

[tepid cradle]

yeah, sure. You can just replace the column references with the relevant columns

@tepid cradle is it possible to do that inside an inner join?
@gloomy pike

[grave scaffold]

K, and when you do so it’s returning 0 rows?

[gloomy pike]

yes

[grave scaffold]

K. Ignore me then

lol

[gloomy pike]

hmm seems like where col1 = col2 or (col1 is null and col2 is null) does not work either

idk if it's an sqlite thing or what

but when i insert 0 and compare with = normally I get the row without None

[grave scaffold]

When you say doesn’t work, you mean the same result as before? Or an actual error

[gloomy pike]

same result as before

I'm getting none

[grave scaffold]

And youre sure you shouldn’t be? Lol. There are actually rows with a null LocationID with matching rows with a null GuildID?

Give me a sec to recreate your situation and see what I get

This is in sqlite, you said?

[gloomy pike]

Yes

sqlite3

For now I've resorted to inserting 0 in place of Null, but if you find anything please lmk

[grave scaffold]

k, will do

[tepid cradle]

@gloomy pike seems to work fine for me. Can you show your full code.

[grave scaffold]

yeah it's working fine for me too, directly in sqllite3

let me test it with the python lib now..

[rain apex]

asyncpg.exceptions._base.InternalClientError: got result for unknown protocol state 3

I can't figure out what his error means after looking it up for 30 minutes

query = await self.con.fetchrow("""
                SELECT prefix FROM Guilds
                WHERE guild_id = $1
            """, msg.guild.id)

[grave scaffold]

@gloomy pike fascinating! It is not working in python for me either.

[rain apex]

this is the query that causes it

[grave scaffold]

@gloomy pike The problem appears to be the syntax LocationID = ? which is simply getting string-replaced (I presume) with the value you pass in, in this case None (though it's getting translated to Null), howerver LocationID = null is not valid. Or at least, it doesn't match nulls. It has to be LocationID is null. I have no idea how to fix this, or indeed if it is fixable.

[gloomy pike]

= doesn't compare nulls

[grave scaffold]

@gloomy pike scratch that, it appears (in sqlite) you can just use is

[gloomy pike]

so does LocationID is null work?

subbing in the string "null" directly?

[grave scaffold]

@gloomy pike So just change the query to be....

[gloomy pike]

ok

[grave scaffold]

        Select * from {p}TagAliases
        Inner join {p}Tags on {p}TagAliases.Tag = {p}Tags.Name
        and {p}TagAliases.LocationID is {p}Tags.GuildID
        where LocationID is ? and Alias = ?
        """

That works for me ^

For both when it's null, and when it's not null (so, just like the is in the join clause)

[gloomy pike]

Oh

I think I"ve already tried that before though

and still it doesn't return any rows

[grave scaffold]

Hmm

Well, it's working for me. Try it again maybe? make sure you change both ='s to is

[gloomy pike]

Oh maybe

I forgot to change the second =

Indeed it was the second =

small things

Thx

[grave scaffold]

Yup

no prob!

I learned something, too

[gloomy pike]

Glad to hear that

[patent glen]

what exactly is going on there with {p}?

[odd spade]

7217

[marble wigeon]

In my cause of learning django, I decided to start with small projects, I'm working on a simple registration page. I have my form on the home page (index.html). And I have a view.py page I pass all the data from the form to my postgres db. I realise data does not save in db, instead all the registration data display in terminal and the url bar

[harsh pulsar]

@marble wigeon can you share the code in your view module?

!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.pydis.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.

[marble wigeon]

@harsh pulsar def index(request):

if request.method == 'POST':
    first_name = request.POST['fName']
    last_name = request.POST['lName']
    username = request.POST['uName']
    password1 = request.POST['password1']
    password2 = request.POST['password2']
    email = request.POST['email']
    
    
    
    user = User.objects.create_user(username=username, password=password1, email=email, first_name=first_name, last_name=last_name)
    user.save()
    print('user created')
    return redirect('/')
else:

    return render(request, 'index.html')

[granite stratus]

hey I have no prior knowledge of how databases work so can anyone suggest me some places from where I could learn it

[quaint tiger]

hey I have no prior knowledge of how databases work so can anyone suggest me some places from where I could learn it
@granite stratus YouTube: https://www.youtube.com/watch?v=qw--VYLpxG4 Postgresql is great and very robust. SQLite is not as robust, but easier to start with (doesn't require a server)
Also, this book: https://www.oreilly.com/library/view/seven-databases-in/9781680505962/ it covers NoSQL as well.

[granite stratus]

aight themks

[granite stratus]

ohk wait but what I do when I am done with it @quaint tiger

[quaint tiger]

lol. @granite stratus store data in it?

Why do you want DB?

[granite stratus]

can we do it for free?

[quaint tiger]

You can use it to store website data, etc.

Yes.. SQLite, and Postgres are both free.

MySQL also has a free version. So does MongoDB.

[granite stratus]

ohk thnx man

I really don't need it rn but I am learning it for the future

[quaint tiger]

Go through a tutorial, and you can store whatever data it suggests. Then think of applications. Maybe write a TODO app, or keep track of your daily weight, exercise routine, meals, write a journal... etc.

[wintry basalt]

All Imports

import string
import random
import itertools
import keyboard
import mouse

Password Generator

if name == 'main':
s1 = string.printable
plen = int(input('Enter The Password Length\n'))
s = []
s.extend(list(s1))
TdPw = itertools.product(s1, repeat = plen)
for item in TdPw:
print (''.join(item))

THE TOP PART U CAN SKIP SORRY THOUGH.

I want the user to input max and min length and the plen should be from max to min length (note:plen means password lengths)

HELP PLS

Hey I gotta go pls DM me the answer Kindly.

[vapid ruin]

from where do i download sql lite ?

https://sqlitebrowser.org/dl/

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

this or that ? i am confused

is there is like 2 version of it a sql lite browser and sql ?

[quaint tiger]

There are different options, @vapid ruin ... what are you trying to use it with?

And what platform are you on?

If you want to use it from inside python, it's in the standard library: https://docs.python.org/3/library/sqlite3.html

If you want to use the CLI shell, download the sqlite-tools package for your platform from https://www.sqlite.org/download.html

I would rather learn the shell first, and then try tools like sqlite browser. While they may make things easier, they also don't teach you basics, so they can hinder your progress...

[edgy plinth]

Hey everyone, not sure if this is right channel for my question but I'm pretty sure u would let me know. Im trying to create bot that logins to site(NIKE.COM) using requests, tried to copy all workflow that happing from opening site to login to account, and I'm getting denied from server, im pretty should that i going something wrong with cookies but i have no idea what. If anyone know anything about this can could give me a hint, i will be so thankful!!

[carmine heart]

@edgy plinth Nike does not allow you to automate their website like that.

[fading pine]

Is there a comparable alternative for pgadmin? Pgadmin is very heavy on the the browser

[minor ruin]

I've run pgadmin on a ipad, not sure how much lighter you want

[ashen shadow]

hello everyone

lets talk

[tepid cradle]

Is there a comparable alternative for pgadmin? Pgadmin is very heavy on the the browser
@fading pine there are many alternatives. I personally recommend DBeaver. You can download the community edition

[haughty flame]

Can PostgreSQL be stored inside your storage system?

Or does it have to be online?

[tepid cradle]

@haughty flame You can install it on any computer with Linux, Windows, or Mac OS (it is also available for Solaris and BSD platforms).
The database will reside on the machine where you install PostgreSQL.
Most databases require a client server model. But that doesn't mean that the server has to be running on a remote machine. The server can very well be running on your local machine itself.

[celest yacht]

why does sqlite3 return entries in a tuple in a list?

[tepid cradle]

@celest yacht That's the usual way of returning values from Db in Python. Afaik, all Db libraries return data that way.

PEP 249 (https://www.python.org/dev/peps/pep-0249) recommends returning results as a sequence of sequences, a list of tuples satisfies that requirement. There may be other considerations, but I have limited knowledge of this.
But you can easily convert it to a different type if you want. What format do you prefer? I can help you convert it.

[celest yacht]

i needed a string, but i got it no worries, just wondering

i used to re.sub("[(),[]']", "", c.fetchall()) but i found c.fetchall()[0][0]

[tepid cradle]

If you need a single value, then you just need to do subsetting.
Each tuple is a row from the Db. If you've use fetchmany or fetchall, you will get a list of tuples.
Assuming your result set is named rows, you can use rows[0] to access the entire first row. For individual value, you can use rows[0][0] format.
If you are only looking for the first row, then you shouldn't use fetchall as you're fetching the rest of the data unnecessarily. You can use c.fetchone() instead.

[celest yacht]

oh what

i never knew that

what does c.fetchone() return?

also a list of tuple?

[tepid cradle]

fetchone() will return only one row, so it will be just a tuple, not a list of tuples.

[celest yacht]

ah okay

thanks

[torn sphinx]

anyone knows anything about

aiosqlite???

[tepid cradle]

In a server dedicated to Python, I would assume there would definitely be someone who knows about a commonly used Python library.

[twilit marlin]

Is that a... challenge?

[boreal niche]

Nani!?

[patent glen]

@celest yacht if you want something other than a tuple there's a way to get it

look at the row_factory stuff at https://docs.python.org/3/library/sqlite3.html

[proud needle]

I'm wondering why it says id is not detected in field_list everytime i put in ```py
query= "SELECT id, manga_author, manga_title, manga_chapter, manga_status"
cursor.execute(query)
rows = cursor.fetchall()
update(rows)

idk what im doing, everything is set in place, and the table is already setup with id as primary key

[celest yacht]

ty random

[quaint tiger]

query= "SELECT id, manga_author, manga_title, manga_chapter, manga_status"
You're missing a table to select from...

query= "SELECT id, manga_author, manga_title, manga_chapter, manga_status FROM foo"

[proud needle]

okay

thanks

holy crap

thanks so much

[quaint tiger]

NP :)
Sometimes you can stare at a problem for hours and miss the most obvious things lol

[proud needle]

bruh it was taking me hours to identify wtf is going on....

tysm man

u saved my grades

[quaint tiger]

Happy to help

[proud needle]

rows = cursor.fetchall(rows)
UnboundLocalError: local variable 'rows' referenced before assignment

okay thats odd

def external_clear():
    rows = cursor.fetchall(rows)
    query = "SELECT ID, Author, Title, Chapter, Status FROM mangatable"
    def internal_clear():
        global query
        global rows
        cursor.execute(query, multi=True)
    update(rows)```

that is hmmm

[quaint tiger]

Why do you have nested functions?

rows = cursor.fetchall(rows)
Python evaluates the right part of the assignment first, so rows is uninitialized and cannot be used in cursor.fetchall(rows)

Are you using sqlite?

[proud needle]

im using mysql

[quaint tiger]

mysql.connector?

[proud needle]

yep

i got rid of the nested functions now

[quaint tiger]

Is update a function you defined?

[proud needle]

def update(rows):
    DB.delete(*DB.get_children())  # gets
    for i in rows:
        DB.insert('', 'end', values=i)

[tepid cradle]

You don't need to pass any arguments to cursor.fetchall()

[proud needle]

ah i see

[tepid cradle]

the way it's generally done is:

sql_query = "select * from some_table where col2 = %s"
cursor = connection.cursor()
cursor.execute(sql_query, ('some_value',))
rows = cursor.fetchall()

[quaint tiger]

Or for row in rows: ... for row in cursor:

Instead of fetchall

[proud needle]

ohhhh okay okay, thanks

[tepid cradle]

Instead of fetchall
@quaint tiger You still need to do fetchall() first. Otherwise the variable rows is not defined anywhere

I mean, you could also do fetchmany(), not necessarily fetchall()

But you have to fetch the rows before iterating over it

[quaint tiger]

@quaint tiger You still need to do fetchall() first. Otherwise the variable rows is not defined anywhere
Right, sorry. I meant for row in cursor, not for row in rows.

Then you don't need to fetch it. https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-select.html

[elder glade]

how to use mysql with py

im nab at dbs

[proven arrow]

You would start by choosing a MySQL driver so Python can access the MySQL database.

[quaint tiger]

im ### at dbs
@elder glade https://pythondiscord.com/pages/code-of-conduct/
Examples of behavior that contributes to creating a positive environment include:
Using welcoming and inclusive language

You should not be using that word pejoratively.

That said, you can use SQLAlchemy as an ORM, which takes care of handling the DB for you, or you can use MySQL Connector. https://dev.mysql.com/doc/connector-python/en/

[elder glade]

Ok sorry for using a rude word

i edited it :)

[quaint tiger]

Thank you 🙂

[mortal yew]

How can implement upvote, comment section reletes to a question and answer in django models?

[twilit marlin]

@mortal yew Ask this in the web development section

[pine sable]

@client.command()
async def thanks(ctx,member: discord.Member):
    await ctx.send(f"{ctx.author.mention} you have succesfully thanked {member.mention}")

now i want to count number of times a person is thanked [i already made a database for that ] can anyone help me to make that command?

[echo urchin]

How to setup a database online?

[left scaffold]

Hiya, would SELECT * FROM suggestions WHERE code={code} this with await db.fetchall() get every bit of data from each field where code={code}? Also how do I split the findings across different parts of an embed? for example put guild id under an embed field name guild id etc?

[pure cypress]

It would return all fields of rows for which the code field equals {code}.

It would be a 2 dimensional list/tuple

The first dimension contains the rows, the second dimension contains the fields for the row

For example results[0][3] would get the 4th field of the 1st row.

[hazy mango]

What's the best way to install redis on a Windows 10 machine? It seems redis only really supports linux distros.

[pure cypress]

Probably Docker

[hazy mango]

That's kinda like a VM, right?

[pure cypress]

In a sense, yes

[hazy mango]

https://docs.docker.com/docker-for-windows/ this yea?

[pure cypress]

Yes

[hazy mango]

Alright, thank you

Do you know of any good tutorials for windows docker/redis with docker?

[pure cypress]

This page shows how to start the container

https://hub.docker.com/_/redis

[hazy mango]

Thanks 👍

Is this the right channel for this kinda thing by the way?

[pure cypress]

You'll want to expose the default port with the -p option so you can connect to it from your host via localhost

I dunno, either here or #tools-and-devops

[hazy mango]

👌

[left scaffold]

It would return all fields of rows for which the code field equals {code}.
@pure cypress So your saying it won't return everything in that row? If so how do I get it to return everything in that row?

[pure cypress]

It will

[deep siren]

can anyone help me?

i have a problem with mysql/phpmyadmin

anyone?

@here?

[mortal yew]

I know We can relate two tables using keys, but I can't get visual picture about this concept someone explain to me in visual way by using examples.

[keen cedar]

I have a (dumb) question that I'd like to ask.
Aren't databases like really big dictionaries easier and faster to manage?

Im starting to learn flask for web dev and I would like to know that because if they in fact are, I can use a dict instead of a whole db for small tests

I think learning all about MySQL and all of that is a little over kill for storing ~10 values

[radiant elbow]

I have a (dumb) question that I'd like to ask.
Aren't databases like really big dictionaries easier and faster to manage?
@keen cedar tables are sorta like dictionaries, except values can be looked up not just by one key but potentially by several. Databases are collections of tables.

[graceful plume]

Anyone here know MySQL?

[graceful plume]

I could use some help.

[twilit marlin]

If you need some specific help, you are better off opening a help channel #❓｜how-to-get-help

[celest yacht]

in sqlite3, is it better to
c.execute(f"SELECT {value} FROM {table} WHERE param={value2}")
or
c.execute("SELECT ? FROM ? WHERE param=?", (value, table, value2))

[rancid pine]

isnt it the same tho

@celest yacht this is all sql it should be the same

[celest yacht]

obviously it's the same, which is why i asked is it better to

eg. follows pep 8 better? i guess

[mint cairn]

but actually i think the second is more scalable and elegant

[minor jewel]

Question about Python hash functions for a SQLite database project.

So I've got a 1tb SQLite database, and performance is dragging down a lot because some tables have really wide primary keys that span multiple columns...

I'm planning to alleviate that burden by dropping the constraint entirely and instead handling the uniqueness on the Python side. I'm going to hash every record, and then just use the hashcode as the primary key for the table.

I feel good about this solution, and I just have a question about which hashing function to use. I don't need it to be cryptographic - I just need it to be fast and relatively collision-free for like 10MM records. I could just use the Python3 built-in object.hash() function, but I don't know if there's a better option.

@celest yacht: The answer to your question is to use the second one: c.execute(query_string, (values)).

Reason: The first way, you're serializing your values using the generic string-formatting function. The second way, you're passing the values to the SQLite execute() command and allowing it to pair them up.

[celest yacht]

yeah, assuming im opening up injection opportunities with the first

[minor jewel]

That is - in the first way, all of your values are generically serialized to values through repr and then injected into a generic string. The second way, you're asking/trusting SQLite to serialize the values so that SQLite can store them. Sure, they still have to end up as strings, but trusting SQLite to do it is much more appropriate.

Yes, that's one very good reason, and the primary reason that many people cite. SQLite knows how to serialize injected string values to avoid injection attacks.

[celest yacht]

oki

ty all

[minor jewel]

np 🙂

[weak charm]

Hello, what are you using to see data from a sql database, like sqlite and postgresql?

[mortal yew]

Does find using a primary key is faster compared using other thing like name for example?

[quaint tiger]

Does find using a primary key is faster compared using other thing like name for example?
@mortal yew not necessarily. You can add indexes to fields, and then they can be accessed pretty quickly.

If you use them a lot for selection (username, email), it is a good idea to add indexes.

[deep siren]

i need help in phpmyadmin

CREATE TABLE accounts(USERNAME VARCHAR(255) UNIQUE, PASSWORD VARCHAR(255))
MySQL reportet:

#1071 - Key is too long. The maximum key length is 767

[torn sphinx]

why make username 255 long?

normally 25 is long enough

[deep siren]

OK THANKS

[pine sable]

@client.command()
async def thanks(ctx,member: discord.Member):
    await ctx.send(f"{ctx.author.mention} you have succesfully thanked {member.mention}")

@thanks.command()    
async def text(self, ctx, *, text):
    db = sqlite3.connect("main.sqlite") 
    cursor = db.cursor() 
    cursor.execute(f"SELECT msg FROM main WHERE guild_id = {ctx.guild.id}")
    result = cursor.fetchone() 
    if result is None:
         sql = ("INSERT INTO main(guild_id, msg) VALUES(?,?)")
         val = (ctx.guild.id, text) 
         await ctx.send(f"Message has been set to '{text}'") 
    elif result is not None: 
         sql ("UPDATE main SET msg = ? WHERE guild_id = ?") 
         val = (text, ctx.guild.id) 
         await ctx.send(f"Message has been updated to .{text}'") 
         cursor.execute(sql, val) 
         db.commit() 
         cursor.close() 
         db.close() 

why this is not working ?

[deep siren]

@torn sphinx i love you thanks, it works

[torn sphinx]

👍

@pine sable any errors?

[pine sable]

the command promt just closes

[torn sphinx]

mk

[pine sable]

?

[mortal yew]

@mortal yew not necessarily. You can add indexes to fields, and then they can be accessed pretty quickly.
@quaint tiger I know, But apart from index, just a primary and other fields, apart from index

[quaint tiger]

I don't understand the question.

If you have a primary key that is unsigned int autoincrement, you can find that using binary search, which is very fast, so searching by ID is usually the fastest.

If you add indexes to other fields, if you do select * from foo where bar="baz"; and you have an index on column bar, it will also keep track of the column in a btree (probably) index, and can scan that in milliseconds, too.

It might not be as fast as querying by id, but it shouldn't be noticeably slower.

It's generally best to use ids anyways (especially for relations - have TableB with a foreign key to TableA.id), but sometimes it's necessary to search another way (e.g. a login system would take a username or e-mail, not an id), and those fields should be indexed.

[indigo steeple]

how can I pass an integer parameter into an SQL query?

say some userId

[quaint tiger]

select * from users where id=1;

[indigo steeple]

Well say I don't know the value of the userId yet

is there a way to represent it with a variable

and then a way to pass in a value to the variable

i'm new to SQL to my question might be incomplete

[quaint tiger]

Using python, you usually run something like cursor.execute("select * from users where id=?", userid)

Depends on your DB/library, of course.

[indigo steeple]

I assume that'd just put userid into the string in python. What about in a SQL program itself. Like, I want to represent a variable x, which might get its value by another line in the same SQL program.

[quaint tiger]

SQL isn't "really" a programming language.

You can write functions, but you typically either write SQL queries by hand in the shell, or you use another language (c, python, php, etc.) to query it.

[indigo steeple]

Ok

I think something like running 'psql something.sql -v x=1', where I guess psql handles it

(PostgreSQL)

[quaint tiger]

That should work...

[indigo steeple]

thank you sir

[quaint tiger]

select * from users where id=:x; should be it.

[gusty venture]

please come to help magnesium to help me 😦

[deep siren]

what means this?:
Warning in ./libraries/sql.lib.php#613
count(): Parameter must be an array or an object that implements Countable

Backtrace

./libraries/sql.lib.php#2128: PMA_isRememberSortingOrder(array)
./libraries/sql.lib.php#2079: PMA_executeQueryAndGetQueryResponse(
array,
boolean true,
string 'Server',
string 'accounts',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
string '',
string './themes/pmahomme/img/',
NULL,
NULL,
NULL,
string 'SELECT * FROM accounts',
NULL,
NULL,
)
./sql.php#221: PMA_executeQueryAndSendQueryResponse(
array,
boolean true,
string 'Server',
string 'accounts',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
string '',
string './themes/pmahomme/img/',
NULL,
NULL,
NULL,
string 'SELECT * FROM accounts',
NULL,
NULL,
)

is this a problem

[hazy mango]

Is it possible to have a system whereby stuff will get stored in Redis for a specified amount of time (given on insertion), and once that time passes it'll be moved into an sql database?

[quaint tiger]

Is it possible to have a system whereby stuff will get stored in Redis for a specified amount of time (given on insertion), and once that time passes it'll be moved into an sql database?
@hazy mango AFAIK, that's not something redis by itself would support, but maybe you could use celery? It can use redis anyways for tasks. Just insert the data, and a task at the same time.

[hazy mango]

Thanks, I'll look into that 👍 @quaint tiger

[reef zodiac]

INSERT INTO auctions VALUES 
  (%s, %s, %s, %s, %s, FROM_UNIXTIME(%s), %s, %s) 
ON DUPLICATE KEY UPDATE price=VALUES(price), ending=VALUES(ending)

how would I correctly format this string with pymysql
specifically for use with execute many
when doing a singular execute i can just pass 2 more values and it will format, but with execute many, it doesn't work
because of how it formats it internally to a statement with multiple values
i.e INSERT INTO auctions VALUES (1, 'test'), (2, 'test')

[crisp nymph]

Hey guys, I have a column full of names. One such name is 'King Titan'. Now suppose I have a variable called Beta King Titan in python. Is there a way I can select that row?

[quaint tiger]

Hey guys, I have a column full of names. One such name is 'King Titan'. Now suppose I have a variable called Beta King Titan in python. Is there a way I can select that row?
@crisp nymph what DB are you using, and what library to query it?

[crisp nymph]

SQLite3

[quaint tiger]

name = "Beta King Titan"
# assuming c is an sqlite cursor, your table is named FOO, and your column is name
c.execute("SELECT * from FOO where name=?", name)

[crisp nymph]

yea well

no

lol

what you are saying is

SELECT * FROM my_table where name="Beta King Titan"

whereas,

the row I wish to select looks like this

[quaint tiger]

Oh... Why is your variable Beta King Titan then?

[crisp nymph]

It is,

coz it is?

I am just asking if it is possible

lol

[quaint tiger]

Your question is not very clear....

[crisp nymph]

Sorry about that. English isnt my first language

[quaint tiger]

Now suppose I have a variable called Beta King Titan in python.
That's not a valid python variable name, so I presume you mean the value?

[crisp nymph]

yes I meant the value. Sorry

[quaint tiger]

Where does that variable come from? And why is it prefixed with Beta?

You can cut the string, of course...

[crisp nymph]

That is the input of user

yep I thought of replace

but I was thinking if there was some other way

using sql

[quaint tiger]

You can use search algorithms to find a match that is close...

Postgresql has a ton of options.

SQLite, I am not sure. Let me have a quick look.

[crisp nymph]

The reason I didnt want to use replace, was that there will be other prefixes too other than beta. Like alpha and more. getting my point?

You can use search algorithms to find a match that is close...
@quaint tiger in sql?

Whoops. Sorry. Bad internet

[quaint tiger]

https://www.sqlite.org/fts5.html something like this, maybe.

If you want users to be able to enter a name, and want to find the closest result(s).

Though you'd have to play around with options to find which one works best for your inputs...

[crisp nymph]

Ok

Thanks for the help 😁

[inland stone]

General Q: Say I want to have some excel-like functionalities, specifically, excel formulas (e.g. "this cell = that cell + 1") in mySQL, is that feasible somehow? or does it actually required running python scripts to process the data and populate automatic columns?

[twilit marlin]

even if this was possible, you'd have to write statements to do this manually anyway. I think its much easier to just make python scripts @inland stone

[inland stone]

@twilit marlin Thank you.

Also saw this on the subject https://forums.mysql.com/read.php?172,571501,575556

[reef zodiac]

data = await asyncio.gather(*tasks)
# This returns something like: [(1, 2 ... 8), (1, 2, ... 8)]
await cur.executemany("INSERT INTO auctions VALUES (%s, %s, %s, %s, %s, FROM_UNIXTIME(%s), %s, %s)"
                      " ON DUPLICATE KEY UPDATE price=VALUES(price), ending=VALUES(ending)", data)
await conn.commit()

any ideas why I'm getting an error and the string isn't formatted?

[twilit marlin]

yep it specifies UPDATE statements, which you'd probably have to run manually. So you'll need something to automate it, and python is a viable option though there could be othwr ways too @inland stone

[inland stone]

but it says that they are triggered automatically when a new record is created? @twilit marlin

[twilit marlin]

I must not have read correctly then, sorry. If it does thats awesome!

[inland stone]

but I'm guessing that still, it would be a wrong way to look at things

i.e. bad practice

[torn sphinx]

how do i add a new line thingy to write data

i'm new to sqlite

[merry raven]

i think its INSERT but im not completely sure

[torn sphinx]

oki

[twilit marlin]

it is

[torn sphinx]

where do u see it

@twilit marlin

[merry raven]

you type it

[twilit marlin]

you need to write a query

there must be a box to type

[torn sphinx]

yes that wy i need a box

[twilit marlin]

INSERT INTO TABLE_NAME VALUES(USERID=12, Pokes=122,... )

[torn sphinx]

i have no idea

isn't there a button to create a box

[twilit marlin]

in your case TABLE_NAME is users

[torn sphinx]

it is

[merry raven]

what program you using?

[twilit marlin]

you can type into the top right box

which says text or something like that

[torn sphinx]

uh

lemme see the name

sqlitedatabasebrowserportable3.12.0

[twilit marlin]

it's sqlite then. cool

[merry raven]

Ok

[torn sphinx]

i'm new to sqlite
@torn sphinx

[twilit marlin]

type the insert statement into the top right box, then select apply

[torn sphinx]

@twilit marlin show me where the box is

[twilit marlin]

top right man

[torn sphinx]

edit database cell?

[twilit marlin]

under "EDIT DATABASE CELL"

yep

[torn sphinx]

oh ok

i can't type

[twilit marlin]

try switching between the modes

[torn sphinx]

wym

ok

[twilit marlin]

actually, why don't you just consult tue documentation of this software?

this is basics, it will be surely available online

[torn sphinx]

but i can't type

[merry raven]

can you give me a link to the software? @torn sphinx

in DMs

[torn sphinx]

@merry raven uh y

https://sqlitebrowser.org/dl/

[merry raven]

bc I dont know what im looking for

thank you

[torn sphinx]

ok

[twilit marlin]

@torn sphinx you have to select "Execute SQL" from tabs on top

and then run the insert statements there

[torn sphinx]

ok

@twilit marlin what is da statement

[twilit marlin]

INSERT INTO TABLE_NAME VALUES(USERID=12, Pokes=122,... )
@twilit marlin

[torn sphinx]

wait

nvm

ok

@twilit marlin what i do when i'm done btw

[twilit marlin]

press enter or something, find a way to execute the statement

btw, you need to end the statement with a ; @torn sphinx

[torn sphinx]

ok

i'm pressing enter dude

@twilit marlin it aint working

[twilit marlin]

god, just find a way to execute the statements lol

click a few buttons or something

[torn sphinx]

it aint have noting to do with sqlite so it aint spoonfeeding

[twilit marlin]

bro, just look this up yourself, you'll find it

good luck

[torn sphinx]

just tell me how to exucute

execute*

frick

pls jujst tell me

[twilit marlin]

google search,

"how to execute statements in sqlite browser "

[torn sphinx]

@twilit marlin i press run button

Execution finished with errors.
Result: no such column: UserID
At line 1:
INSERT INTO users VALUES(UserID=707197889881047041, Pokes=Pikachu, Lvl=100, Health=500 )

@twilit marlin dis error ^

[nova hawk]

There is no UserID column in the users table.

[torn sphinx]

but there is

[twilit marlin]

there is actually

[torn sphinx]

^

[twilit marlin]

resend the image for @nova hawk

[torn sphinx]

ok

[nova hawk]

Make sure that image is the same database file as the one you are currently connected to.

[torn sphinx]

it is

it won't even let me make anew colum

@nova hawk here is it

[nova hawk]

SELECT sql FROM sqlite_master
WHERE tbl_name = 'users' AND type = 'table'```
Could you run this from python?

[reef zodiac]

https://hastebin.com/pobafaxavu.pl ideas on how to stop it from locking?

[twilit marlin]

at this point since it's not working, you should just follow a video tutorial

[reef zodiac]

i'm inserting 1k values at once but from the logs it looks like it's doing it one at a time

[torn sphinx]

pls

[boreal niche]

Can you convert SQlite 3 to postgresSQL. Ping me if u can help since ill be sleeping when i get the ping :).

[warped frigate]

does postgresql cache queries automatically? i've been looking for anything i can do to speed up a query that takes about .01 seconds to select rows from a table where a certain field is in an array of about 200 elements and is done tens of thousands of times

i've already added an index on all the involved columns in the table

my query is this (psycopg2):

cur.execute("SELECT row_id, spread, symbol FROM Y_spread_working WHERE symbol IN %s ORDER BY row_id LIMIT %s", [tuple(set(symbol_set[j])), o])```
"o" is the expected number of results to return, from my debugging the query always returns exactly o values

and it needs to be ordered by row_id

i have other basically identical queries that execute in about 10-15% of the time that this takes, but those have a much smaller symbol set to match to, so i think the bottleneck is the "WHERE symbol IN"

[wary stirrup]

i have some questions about postgresql

i see this

1. what are this folders?

2. where can i do queries?

3. where should i create tables?/where i create tables, where are going to be?

thanks in advance and please if you have answers dont mind to ping me even if i'm offline

[vapid ruin]

i have my mobile App connected to my mysql workbench but iam having an issue with enabling port 3306 ... how should i do that ?

sorry for interruption

please guys my mobile app doesn't get connected to mysql bench it's unable to reach it ... i have to type the IP address of my PC in my program and enable the 3306 port ?

[nocturne basin]

What's the most efficient way to store data from an API request's to a Postgres DB on a scheduled basis? Would I need to store the JSON data in another object before parsing it through SQLalchemy?

[twilit marlin]

why store it anyway? Do you actually want to store it? Just parse it directly if storage is a useless step

[nocturne basin]

@twilit marlin good question. The API is for financial data and usage is expensive in terms of requests.

Storing the data allows me to make one request for newdata points

[twilit marlin]

then maybe store that into a dictionary and then dump that data into a file. that will allow you to store the data as well as easily provide lookups

[harsh pulsar]

@nocturne basin you can store json in postgresql with the jsonb data type

another possibility is to store the raw unadulterated responses in a "blob store" system like amazon s3

[tepid cradle]

What's the most efficient way to store data from an API request's to a Postgres DB on a scheduled basis? Would I need to store the JSON data in another object before parsing it through SQLalchemy?
@nocturne basin Unless the API response is so big that parsing it on the fly will take time, you can just go ahead and parse the response right after the API call. Then store it in the PostgreSQL Db. There is no need to store the json separately.
How big do you expect the data to be?

And how often do you see yourself making these requests?

[pure cypress]

@torn sphinx Run a select query with a where clause to filter for that user's reaction. If the query result is empty, then you can store their reaction. If it's not empty, then they must have reacted already and you should therefore abort storing their current reaction.

With Python

It depends on which method you use

fetchone() will return None for an empty result

fetchall() will return an empty list. You could do if not result_list: or if len(result_list) == 0 to check if the list is empty.

fetchone is probably what you'll be using, since presumably there's only one row per user here anyway.

[pure cypress]

You're welcome

[boreal niche]

Can you convert SQlite 3 to postgresSQL. Ping me if u can help since ill be sleeping when i get the ping :).

Can you convert SQlite 3 to postgresSQL. Ping me if u can help since ill be sleeping when i get the ping :).

[twilit marlin]

Can you convert SQlite 3 to postgresSQL. Ping me if u can help since ill be sleeping when i get the ping :).
@boreal niche sqlite databases can be exported in different formats (json, yaml) and then be imported into postgresql so yeah it can be done

[proven arrow]

Each poll should have its own unique ID, maybe you can do this with message ID?

Then you can filter to see if they have entered this poll.

Also you should really not be sending them DMs. Because what if the user spams the reaction, it will just spam DMs.

And last note, use an async DB driver, because currently every reaction event will cause a blocking call to be made.

[wary stirrup]

@torn sphinx dbeaver

Lemme search on internet

If i found nothing i'll switch to pgadmin

[woeful zephyr]

I want to learn and install robomongo in my fedora os can anyone please suggest resources

@pure cypress do you have something

[haughty flame]

Can I use sqlite3 methods such as .fetchall() and .fetchone in PostgreSQL.

asyncpg

I'm a little confused cause I was going to rewrite my codes since I just found out that sqlite3 was blocking for asynchronized environment.

It also use $X instead of ? and :name

Also, can I store the database in my physical storage without needing cloud?

[wary stirrup]

ERROR: syntax error at or near "("

uh?

[twilit marlin]

you forgot a comma second last line@wary stirrup

[wary stirrup]

oh thx

[twilit marlin]

sure np

[wary stirrup]

i'm a python dev

[twilit marlin]

ok?

[wary stirrup]

i dont use commas xD

[twilit marlin]

wait till you learn tuple unpacking

or lists

or dictionaries

[wary stirrup]

i know

let's say i had this error because i'm blind

not because i'm a python dev 😅

[twilit marlin]

yeah lol

[tepid cradle]

let's say i had this error because i'm blind
@wary stirrup lol, it's very common. I make these errors all the time. And I have seen many questions where the problem was just a missing comma or brackets. In fact, missing closing paranthesis is probably the most commonly asked problem in the help channels

For DBeaver, click on SQL Editor on the menu and select SQL Editor

In the toolbar, there's an icon which looks like a scroll with plus icon, seventh from the left

[wary stirrup]

For DBeaver, click on SQL Editor on the menu and select SQL Editor
@tepid cradle yeah i had it

[tepid cradle]

You can customize it in Windows > preferences > general > keys > New SQL editor

I change it to ctrl+T to make it feel similar to browser tabs

[haughty flame]

Can I store asyncpg in my physical storage without needing any cloud servers?

[tepid cradle]

Asyncpg is a Python library

[haughty flame]

No, I meant can I connect to a local db?

[tepid cradle]

Yes, sure

If you have a pgsql server on your local machine, that is

You just need to install Postgres

It's available for Windows, Linux, and Mac

[flint wadi]

Why doesn't sqlalchemy's update method require a commit?

[weak charm]

How to to something like this in sqlalchemy? Post.query.order_by(Post.likes.count().desc())

[lime echo]

Every time I update my bot on Heroku, its sqlite database loses some recent data. How can I solve this problem?

[nocturne basin]

@harsh pulsar Any beneficial reason why I would store the raw JSON strings into the database? Instead of parsing it neatly and providing appropriate dtypes

[harsh pulsar]

@nocturne basin because that is raw data and raw data is valuable

Can't replace it

That's why I recommend storing the raw data in a write-only data store like S3

With backups

Can always restore db from raw responses

[twilit marlin]

Every time I update my bot on Heroku, its sqlite database loses some recent data. How can I solve this problem?
@lime echo Heroku has an ephemeral filesystem, that is it deletes all generated files (like database files) every 24 hours. The free tier provides the postgresql database option and you should be using that instead. Its faster than sqlite anyway.

Corey Schafer has a tutorial on this for django

[lime echo]

@twilit marlin thank you so much!
is there a way to constantly get my postgresql database from Heroku and keep it as a backup on my computer?

[warped frigate]

So, I've been looking for anything i can do to speed up a query that takes about .01 seconds to select rows from a table where a certain field is in an array of about 200 elements and is done tens of thousands of times. I've already added an index on all the involved columns in the table, and my query is this (psycopg2):

cur.execute("SELECT row_id, spread, symbol FROM Y_spread_working WHERE symbol IN %s ORDER BY row_id LIMIT %s", [tuple(set(symbol_set[j])), o])```
"o" is the expected number of results to return, from my debugging the query always returns exactly o values, and it needs to be ordered by row_id. I have other basically identical queries that execute in about 10-15% of the time that this takes, but those have a much smaller symbol set to match to, so i think the bottleneck is the "WHERE symbol IN". Any reccomendations on how to speed this up?

[twilit marlin]

@twilit marlin thank you so much!
is there a way to constantly get my postgresql database from Heroku and keep it as a backup on my computer?
@lime echo It does have an export option if I remember correctly. maybe you can make a pythonn script that downloads that data every hour via scheduler (like cron)?

The paid tiers keep backups natively without you having to do anything

but they're kinda expensive especially if your app doesn't have a lot of users

[lime echo]

Oh I certainly have to do that.
I don't plan on getting a paid tier for now, but maybe for the future.
I have used Sqlite3 with my bot, maybe I should just download all those database, export them to PostgreSQL then use PostgreSQL instead.

@twilit marlin do I need to remake the databases or can I just switch from Sqlite to PostgreSQL on Heroku?

[twilit marlin]

the sqlite file will be deleted anyway, so you might as well as ignore it and setup the postgres directly. Or you can download it and then export that data if you really need a backup @lime echo

[lime echo]

@twilit marlin alright, I will download it, then export it to PostgreSQL then use PostgreSQL so forth.
I just wonder if I can actually export the data from Sqlite3 to PostgreSQL or not?

[twilit marlin]

not directly, just export in into a fixture and then apply these fixtures to the new database. this can done using the default manage.py commands @lime echo

[burnt turret]

How do I get the text from an asyncpg.Record object?

<Record tag='tester' value='this is a test tag #1'>
``` ~~one of these~~, got it

[lime echo]

@twilit marlin sounds like doable then, thanks! I am downloading PostgreSQL now.
I do have another question though. I am using MySQL for my website and I wonder if I could just use PostgreSQL for it too or should I just stick with MySQL. I ask this mainly because I don't know the difference between them and I don't know them in depth.

[twilit marlin]

neither do I but if you use something like django you probably don't need to worry since it handles ORM for you. But I prefer Postgresql since it's an industry standard. But MySQL is heavily used too so either should be fine really @lime echo

[lime echo]

@twilit marlin I will just read about them more and see what I can come up with. If I can use 1 SQL for everything that I use (like website, bot and so on) it would be the best because with only 1 SQL, I will be less overwhemled.

[twilit marlin]

yeah whatever works for you dude, good luck!

[lime echo]

Thanks man!

[tepid cradle]

@twilit marlin sounds like doable then, thanks! I am downloading PostgreSQL now.
I do have another question though. I am using MySQL for my website and I wonder if I could just use PostgreSQL for it too or should I just stick with MySQL. I ask this mainly because I don't know the difference between them and I don't know them in depth.
@lime echo For larger applications, PostgreSQL is often used. I would recommend going with it.
They are very closely matched in many aspects, including performance and data integrity. When I read up about them, what was recommended was that if your application is write heavy, i.e., you write a lot of data into the database on an ongoing basis, then use PostgreSQL as it handles multiple concurrent write operations better.
If your application is read heavy, then go with MySQL as it provides slightly better read performance.

[nocturne basin]

@harsh pulsar Thank you kindly for that response. Does thousands of rows of JSONB data slow down any processes?

[tepid cradle]

In any case, if you have an application with a few hundred users and 10-12k rows of data, all this hardly matters, you can use either

[lime echo]

@tepid cradle thank you so much man! I might just use PostgreSQL then.

[twilit marlin]

@lime echo For larger applications, PostgreSQL is often used. I would recommend going with it.
They are very closely matched in many aspects, including performance and data integrity. When I read up about them, what was recommended was that if your application is write heavy, i.e., you write a lot of data into the database on an ongoing basis, then use PostgreSQL as it handles multiple concurrent write operations better.
If your application is read heavy, then go with MySQL as it provides slightly better read performance.
@tepid cradle I didn't know that. thats very interesting thanks

[tepid cradle]

Just to give an example, I have a PgSQL database running on my Raspberry Pi 4B 4GB. In that I have a table with ~11 million rows. Retrieving around 2000 rows using a query on indexed column takes around 40 milliseconds, that's 0.04 seconds.
How much faster can you ask for!

And do note that this is Raspberry Pi. Servers are much much faster in processing.

[nocturne basin]

Question about Postgres and storing JSONB data. Does this help database speed and preformance?

[twilit marlin]

@tepid cradle can I ask how do you get 11 mn rows into a db. What kind of data do you store?

[tepid cradle]

This particular table stores daily historical prices for all mutual funds in India.
The initial database was built using Quandl's free AMFI dataset. The table is updated on a daily basis using a script running on the same Pi which downloads the daily prices directly from AMFI website. It is provided by them in a text file.

[twilit marlin]

oh that does makes sense. thanks

[tepid cradle]

It's been great for learning. My Raspberry Pi is an always on machine with multiple cron jobs (all of them calling python scripts), two databases (PgSQL for my projects and MySQL for Kodi), and two Telegram bots running on it. It's amazingly stable. It runs for weeks on end, without a single restart, and hardly ever becomes slow.

[twilit marlin]

I plan to get working on hardware once I am finsihed with something that I am working on for the moment.

[quaint tiger]

@tepid cradle can I ask how do you get 11 mn rows into a db. What kind of data do you store?
@twilit marlin lol. I used to do cellular network optimization for an operator in Mexico. 100,000,000+ records in one table was normal.

[twilit marlin]

Must have been a real pain to maintain

[quaint tiger]

That's not even all of one day's events, seeing as there were close to 10,000,000 mobiles just in the capital.

[tepid cradle]

yeah, for real world Dbs, billion rows is not a lot, it's fairly common

[quaint tiger]

Honestly, not really. Some of the queries did take ~45 minutes...

But those were scheduled daily

Once you aggregate the data, querying smaller sets is quite efficient.

[twilit marlin]

Bruh that hoestly sounds dope

[tepid cradle]

yeah, mainly depends on the amount of data being fetched. Even with a billion rows, with a well indexed table, you can query small chunks of data in milliseconds

[twilit marlin]

I never thought querying would take 45 minutes ever since mine get completed even before i can see it being logged lol

[tepid cradle]

Those are the kind of queries where you actually get into optimisation or queries itself

simple queries of the form select * from table where col = value don't offer any scope for optimisation

[quaint tiger]

A lot of fetching and aggregating data for different events.

It wasn't fully optimized, but the first time we ran those they took like 26 hours

[tepid cradle]

lol..

[quaint tiger]

We figured 45 minutes scheduled at 4 AM was fast enough.

[tepid cradle]

Did you have a separate Db for this kind of reporting purposes?

In my previous organisation, they had a separate instance of the database only for reporting purposes. It was updated once a day to sync with the live database, then all the reporting jobs would run exclusively on it

[quaint tiger]

We ran everything on one colocation server

That was actually hosted in Canada

So transfer all that data hourly lol

Actually, come to think of it, I lied.

The scripts did generate KML files for Google Earth, and HTML reports.

We didn't query the DB that much to display the reports.

Only to get a list of cells, sites, BSCs, etc.

Anyways, to get back to the topic of databases and handling massive amounts of data, hbase is worth looking into.

we used MySQL like a bunch of chumps

But HBase wasn't really a thing when we started the project.

[harsh pulsar]

@nocturne basin I actually would just store the raw data outside the database if possible

[light vigil]

Hey guys, I will appreciate any help on how to efficiently track timestamps of when data is inputted into different columns. Would you suggest that i create a column for the time the data was inserted in the colums (form inputs) in a different column? My reservation on that method is I need to track a lot of columns and would require a ton of additional columns to track the time for each column entry. Does anyone have any suggestions on any methods that make the process of tracking time of input in each column a whole lot easier? Does anyone use the logging library for this? Thx

[quaint tiger]

JSON metadata?

Most SQL DBs support JSON nowadays, you can use a JSON column...

Use the column name as JSON key

[tepid cradle]

@light vigil You're updating different columns of the same table at different times? Can you give some details about your usecase which requires data to be stored this way?

[light vigil]

@quaint tiger Thanks. Can you kindly explain a bit more? I am working with MongodB.

@tepid cradle Thanks. So I am creating a workflow system where people fill out forms for when different processes occur. So for example, when user 1 creates a purchase order, he upload the file into the dB. User 2, approves or rejects the submission. I want to log when the file was submitted by user 1 and when it was approved or rejected by user 2.

[tepid cradle]

Oh, MongoDB.
You should not use the word rows and columns for NoSQL Dbs.
The correct terms are collections (equivalent of tables), documents (equivalent of rows), and fields (equivalent of columns).

[light vigil]

Yes. Sorry. My bad.

[gritty frost]

hey guys, why am I getting a typerror when I try to execute this command: db.execute("SELECT id FROM users WHERE username = :username", dict(username = username))

TypeError: execute() takes no keyword arguments

[tepid cradle]

Seems like the best way would be to just store the time in fields named something like "created_on", "approved_on" and so on

[light vigil]

Okay. Thanks.

[tepid cradle]

This is actually a fairly common practice. Most real world (SQL) Dbs have columns like created, modified, deleted, deactivated_on, etc.

hey guys, why am I getting a typerror when I try to execute this command: db.execute("SELECT id FROM users WHERE username = :username", dict(username = username))
@gritty frost which database, which library?
Try removing the dict from there and passing it as an actual dict instead, though, that shouldn't matter, but why use an extra function when you can just replace () with {}.

[gritty frost]

just tried it

line 115

its a sqlite database

[twilit marlin]

i think you meant to use string.format() but somehow messed that up? @gritty frost

[gritty frost]

sorry, Im fairly new.. I just finished CS50 and am currently switching from their IDE to VS Code

[tepid cradle]

db.execute doesn't return anything. You have to do db.fetchall() after that

Try running the query in the console/Jupyter Notebook to see what values are returned from queries. That helps visualise how you need to use the results in your code

[twilit marlin]

@tepid cradle i just noticed you have R2D2 in your pfp. I like that.

[gritty frost]

db.execute doesn't return anything. You have to do db.fetchall() after that
@tepid cradle okay, I will google fetchall and see how to implement that! thank you!

[tepid cradle]

😆
Just got bored of the default icon one day and put whatever came to mind first

@gritty frost here's a live example

!e

import sqlite3
db = sqlite3.connect(':memory:')
db.execute("create table test(col1 int, col2 varchar)")
db.commit()
db.execute("insert into test values (1, 'me'), (2, 'you')")
db.commit()
cursor = db.cursor()
cursor.execute("Select * from test")
print(cursor.fetchall())

[delicate field]

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

[tepid cradle]

You can run this in #bot-commands channel and check for yourself 🙄

[gritty frost]

can I use questionmarks with select query?

im trying to create a register function. So after typing the username, the function should check if this username is already in the database

thats what you see in line 115 in the screenshot above

[harsh pulsar]

@gritty frost yes

Assuming your database library uses question marks as placeholders

Different libraries have different syntax

[gritty frost]

kk im using sqlite3

[tepid cradle]

@gritty frost check #bot-commands channel

[torn sphinx]

oki

i'm trying to make a simple pokemon feature for my bot, that just saves the pokemon name, and allows you to show info for it can someone HeLp me pls

[dapper bison]

Hi, i have a sqlalchemy question, i have 3 tables
1 Story
2 StoryComment
3 StoryBlogComment
tables 1 and 2 have a relationship

comments = relationship(
        "StoryComment",
        backref="story",
        order_by="desc(StoryComment.parsed_time)",
    )

how do i change this relationship, to combine tables 2 and 3?
here is stackoverflow link https://stackoverflow.com/questions/63783599/sqlalchemy-relationship-between-3-tables-cab

[inland stone]

Hello. Can you tell me why I get syntax error for this command?
INSERT INTO jobs ('NameAtCustomer', 'VAT', 'SubtotalILS', 'POID') VALUES('MI0021735', 0.27, 15.33, '20787');

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''NameAtCustomer', 'VAT', 'SubtotalILS', 'POID') VALUES('MI0021735', 0.27, 15.33,' at line 1")

ok it looks like i need those silly `

for the column names

[pseudo cove]

the column names shouldn't be strings I think @inland stone

nvm sorry for the ping

[harsh pulsar]

No that is correct

If you have to quote column names, use " not '

[wary helm]

in SQL language, is the correct order :

FROM ( AND )
WHERE() 
ORDER()```

[shell ocean]

in SQL language, is the correct order :

FROM ( AND )
WHERE() 
ORDER()```

@wary helm you shouldn't have an AND in FROM

that's where the table name goes

you can have it in WHERE

[wary helm]

soo what is select?

so from is the table name then where is the records?

or do u mean from is record

and where is field

oh wait its select * from ok i understand

then where can be ```WHERE user='rob' AND city='london';

[shell ocean]

yes

hm.

let's say I have an Employee table with is a boolean column, is_management. I have another table, Grade, with a foreign key to Employee. I want to introduce a check constraint on Grade such that it references only rows in Employee for which is_management is true.

I understand that if I wanted to do this with a check constraint, I'd need a function, since a check constraint cannot reference other tables (Postgres, btw).

is that the best way (perhaps taking into consideration that I'm using Django)? or is there any other appropriate method to enforce the above constraint?

[wary helm]

@shell ocean when i made my web-app on django, for this sort of thing i used djangos check constraint pip installion = https://pypi.org/project/django-check-constraint/

and i also followed this documentatio https://docs.djangoproject.com/en/3.1/ref/models/constraints/

[shell ocean]

hm?

no, I mean

I know how to do it with a function

[wary helm]

oh, sorry

[shell ocean]

but I'm asking whether this is the best way

no worries, thanks for the suggestion

[wary helm]

im not sure if its the best way then im not too good at this sort of stuff otherwise lol can only speak from experience

but it worked really well for me

[shell ocean]

like from what I've read

[wary helm]

especially since i had to link teachers and students

[shell ocean]

the thing about using functions in constraints is that if you change the function, Postgres doesn't recheck the DB

so you need to remove and readd the constraint

otherwise you could end up with invalid state

[wary helm]

right

i dont recall getting an error of that sort

[shell ocean]

yup, it's not really a major concern, just a possibility

and I don't actually know what approaches are available to solve the problem I have

so I guess I'm just asking around to better understand

[wary helm]

yeah im sorry i cant help u there

[shell ocean]

np thanks again for the suggestion!

[pallid locust]

im not sure if this is the correct area but i have a problem with calculating accuracy between two arrays

if i have 2 mx1 arrays, how do i compute how many elements are equal in the same position

i have an array of correct values and an array of predicted values and want to see correctly predicted/total possible

[spark ravine]

Try the help channels

[pallid locust]

ive had one posted for 10 mins or so just seeing if anyone could answer here as its not too specific

[spark ravine]

Gotcha. I would just keep pinging until someone comes; that's how I usually wait since your channel will get to the top. I'll take a look though.

[shell ocean]

if i have 2 mx1 arrays, how do i compute how many elements are equal in the same position

i have an array of correct values and an array of predicted values and want to see correctly predicted/total possible
@pallid locust what kind of array

also #data-science-and-ml would be more appropriate for that

[craggy pawn]

So I have a discord bot that updates server info every time it relaunches at midnight into a mysql database

I want to add columns such as 'user change 1 week', '1 month', '1 year', etc

would I have to update them in 2 passes or can I slam everything into 1 update statement?

[torn sphinx]

I need help with kafka consumer. Not sure if this is the right channel

[tepid cradle]

So I have a discord bot that updates server info every time it relaunches at midnight into a mysql database
@craggy pawn You can update an entire row in a single update statement.
You can even update multiple rows very easily if they all need to satisfy a single condition, for instance
update transactions set status = 'gold', promotion_date = '2020-09-08' where user_id = 19
this will update all rows where the user_id is 19
You can even update multiple rows with a separate condition for each row, but that is a bit more complicated.

[craggy pawn]

Would I be able to make calculated columns for those columns like I described?

IE tracking change in server members over time

[severe tendon]

Hello, how would you go about connecting to a openvpn server within a python script? I need to query a DB which is reachable through that vpn from a Jupiter notebook.. I have found online this api:
https://pypi.org/project/openvpn-api/
Have you ever used it? Do you recommend something else?

[tepid cradle]

Would I be able to make calculated columns for those columns like I described?
@craggy pawn I don't really understand the use case. Can you explain what you are trying to do with this.

[dry anvil]

Not sure if I'll be able to get the help here but does anyone maybe have a solution to this?

Another quick question, I'm working on a project that would be able to spin up a web server and needs to keep track of some user settings that are set once and should be stored on the system, not just per-instance or anything like that. Is there a proper way of doing this? It should work on all platforms as well.
I'm trying to have my library just keep track of some user config stuff that is static, and only needs to be initialized / edited by the library itself, no need for the user to interact with it directly.

I figure there must be some way of storing a simple YAML file or SQLite3 database on the system, in Windows you have LocalData and such but no clue what the best practices are and how to ensure it will always work.

[tepid cradle]

In the simplest form, you can store the data in a JSON file. But it is recommended to use a database, even an Sqlite database should be sufficient for this. Sqlite3 database does not need any setup or configuration. You can simply write the code in Python and the first time it connects, it will automatically create the Db

[keen rock]

For small projects use MongoDB

But dont use JSON. It's not reliable or good practice.

[tepid cradle]

@keen rock Any reason for recommending mongoDB?

[keen rock]

Idk, Its just the one I use. It's worked nicely for me.

@tepid cradle

[brazen charm]

Mongo is the closest actual database to json

Pretty solid for beginner and solid python module for sync stuff

[severe tendon]

Hello, how would you go about connecting to a openvpn server within a python script? I need to query a DB which is reachable through that vpn from a Jupiter notebook.. I have found online this api:
https://pypi.org/project/openvpn-api/
Have you ever used it? Do you recommend something else?
@severe tendon
Anyone?

[delicate field]

Hey @spiral drift!

It looks like you tried to attach file type(s) that we do not allow (.json). We currently allow the following file types: .3gp, .3g2, .avi, .bmp, .gif, .h264, .jpg, .jpeg, .mkv, .mov, .mp4, .mpeg, .mpg, .png, .tiff, .wmv, .svg, .psd, .ai, .aep, .xcf, .mp3, .wav, .ogg, .webm, .webp, .m4a, .flac.

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

[spiral drift]

Hey guys! I want to process a json file, but I just can't for the life of me figure out how. I have a json file that goes like this: (with 282513931854151681 being the user ID and balance being the user's points)

{
    "282513931854151681": {
        "balance": 1000,
        "active_bets": 0
    },
    "335394052834983938": {
        "balance": 1105,
        "active_bets": 0
    },
    "234070477335691276": {
        "balance": 96,
        "active_bets": 0
    },
    "750676246995927053": {
        "balance": 410,
        "active_bets": 0
    },
    "194491779943235584": {
        "balance": 500,
        "active_bets": 0
    },
    "261372680220049409": {
        "balance": 4034,
        "active_bets": 0
    }
}

Now I want to sort this file by the balance key, and then print the user ID and the balance. So you get something like:

1. 261372680220049409: 4034
2. 335394052834983938: 1105
3. 282513931854151681: 1000

etc etc

Any help with this would be greatly appreciated!

[shell ocean]

@spiral drift do you know how to use comprehensions?

[spiral drift]

@spiral drift do you know how to use comprehensions?
@shell ocean I've used them a bit here and there but I couldn't code them from memory

[twilit marlin]

@spiral drift sort them according to which parameter?

by the example you gave, it seems like balance

[spiral drift]

from high to low, using balance

[quaint tiger]

# j = json.load(your_file)
[(k, j[k]["balance"]) for k in sorted(j, key=lambda x: j[x]["balance"], reverse=True)]

[twilit marlin]

F

[quaint tiger]

# output
[('261372680220049409', 4034), ('335394052834983938', 1105), ('282513931854151681', 1000), ('194491779943235584', 500), ('750676246995927053', 410), ('234070477335691276', 96)]

[twilit marlin]

you're too good @quaint tiger

[quaint tiger]

Just loop through the tuples

Sorry for stealing your thunder 😅

[twilit marlin]

im impressed with how fast you were

[quaint tiger]

My secret is I always have a terminal on hand, and usually a python REPL shell open lol

I did make like 5 mistakes you don't see before I got it right 🙃

[twilit marlin]

either ways, i need to step up my python game. Hackathon maybe?

@spiral drift your problem was solved by @quaint tiger

[spiral drift]

ooooh nice thank you @quaint tiger!

Time to find a way to format this, turn the IDs into discord tags and run it with discord.py I guess lol

[quaint tiger]

Hackathon maybe?
👍
Also, those CodeWars style sites that give you coding katas.

[boreal niche]

https://prnt.sc/udrx3q

anyone?

[brazen charm]

its pretty self explanitory

[boreal niche]

what?

@brazen charm

it is

but liek

i check

ed

[brazen charm]

you sure its not running on a diffrent py interpreter than the one youve installed it on

btw

[boreal niche]

wdym?

[brazen charm]

d.py + psycopg2 = bad

[boreal niche]

O

[brazen charm]

use asyncpg not psycopg2

its better in basically every way

[boreal niche]

is it for postgres?

[brazen charm]

yes

google it™️

[twilit marlin]

@brazen charm i have used psycopg2 in the past, what's wrong with it?

[boreal niche]

and everything is the same?

[brazen charm]

sorta

[boreal niche]

:/

[brazen charm]

it fixed every psycopg2 breaks

[boreal niche]

ok

well

[brazen charm]

ill brb driving for food

[boreal niche]

more learing for me

[brazen charm]

Basically asyncpg is the 'correct' system

[boreal niche]

asyncpg.exceptions.InvalidAuthorizationSpecificationError: no pg_hba.conf entry for host "nope", user "nope", database "nopw", SSL off

@brazen charm

[harsh pulsar]

@torn sphinx do you need to commit after inserting?

[flint pagoda]

hello! I'm unsure if this is the correct channel to post in since I'm new. I'm having difficulty with an app I am making and I believe the issue is database related...

I'm using SQLAlchemy and Flask to make a DnD character creator, and whenever I submit the form to create a new character, the rendered html template that the form redirects to does not display the new character.

https://github.com/mlghr/capstone-one here's my repo. My most current branch is (tablet)

[brazen charm]

I dont know too much about sqlalchemy

but dont you actually need to call save or something

i cant remember the name

[frozen loom]

this may sound like an odd question but what do I name my database? I just have mine called database.db

like is there a common name for databases?

[brazen charm]

honestly yeah

it doesnt matter much

[frozen loom]

ok thanks lol

[flint pagoda]

basically you have your app.py, forms.py, and models.py (which contains your tables and values). I know that you need to make sure to db.session.add({obj}) and then session.commit to add them to the db but for some reason it isn't storing

[brazen charm]

i dont see where your doing add()

[torn sphinx]

hey

ok nevermind

hey

@naive sandal is this good?

[naive sandal]

yes

your column names are bad though

user_id

pokemon_id

level

health

but anyways

Pokes is an integer

[torn sphinx]

ok

[naive sandal]

are you sure you want that

[torn sphinx]

idk what i want or not dude

@naive sandal would it be good?

and how do i edit the name

o nvm

@naive sandal is user_id good?

[naive sandal]

yes

a user can have multiple pokemon right

[torn sphinx]

yes

as many as they catch

[naive sandal]

well your db should be fine

[torn sphinx]

!

[naive sandal]

You'll have to map pokemon_id to the Pokemon name and stuff though

[torn sphinx]

i have to make a thing named pokemon_id?

[flint pagoda]

oh i'm sorry. I posted the wrong branch for the github repo

https://github.com/mlghr/capstone-one/tree/tablet

[torn sphinx]

@naive sandal alright

CREATE TABLE "users" (
    "user_id"    INTEGER,
    "Pokes"    INTEGER DEFAULT 0,
    "Lvl"    INTEGER DEFAULT 1,
    "Health"    INTEGER DEFAULT 500,
    PRIMARY KEY("user_id")
);```

is that good?

@naive sandal all of them should be integer?

[naive sandal]

That's up to you

[torn sphinx]

huh

[naive sandal]

it depends on what you want to store

[torn sphinx]

i want to store pokemon, lvl for the pokemon, and health

@naive sandal all is well then

so i guess i get started on the catching command

@naive sandal so first thing i can do?

[naive sandal]

make it send random pokemon first

[torn sphinx]

@naive sandal lol i been done with that

    random_pokemon = random.choice(poke)
    name = random_pokemon["name"]
    url = random_pokemon["url"]
    description = random_pokemon["desc"]
    em = discord.Embed(title = name, description = description)
    em.set_author(name=f"{ctx.author.name}")
    em.set_image(url=f"{url}")
    em.set_footer(text="To catch the pokemon type .catch")
    await ctx.send(embed=em)```

@naive sandal bamity bam

i can't wait

@naive sandal fisrt step is?

@client.command()
async def pstart(ctx):
    embed=discord.Embed(title="Welcome to the world of Pokémon!", description="To Start your adventure, choose one of these pokémon with the ``.pick <pokémon>`` command, like this: ``.pick Squirtle``")
    embed.set_author(name="Start a new journey")
    embed.add_field(name="Generation I", value="Bulbasaur | Charmander | Squirtle", inline=False)
    embed.add_field(name="Generation II", value="Chikorita | Cyndaquil | Totodile", inline=False)
    embed.add_field(name="Generation III", value="Treecko | Torchic | Mudkip", inline=False)
    embed.add_field(name="Generation IV", value="Turtwig | Chimchar | Piplup", inline=False)
    embed.add_field(name="Generation V", value="Snivy | Tepig | Oshawott", inline=False)
    embed.add_field(name="Generation VI", value="Chespin | Fennekin | Froakie", inline=False)
    embed.add_field(name="Generation VII", value="Rowlet | Litten | Popplio", inline=False)
    embed.add_field(name="Generation VIII", value="Grookey | Scorbunny | Sobble", inline=False)
    embed.set_image(url="https://i.imgur.com/oSHo1IZ.png")
    await ctx.send(embed=embed)
``` yeah and btw i want them to start before they start catching with commands

[naive sandal]

add a wait_for and let the user choose

[torn sphinx]

@naive sandal ita not a message event

and i hardly know wait_for

[naive sandal]

right, because you can only call wait_for in message events

[torn sphinx]

yes

[naive sandal]

read da docs

also #discord-bots

wrong channel

[torn sphinx]

ye u can only do it in message events right?

ok

[naive sandal]

no of course not

[lusty grail]

hey evertone. a quick-ish, I hope question. In there any good/recommended way to verify the status of posgres connection using psycopg2? i.e. if the connection was lost (network/DB issue) I want that instead of throwing an exception the connection would be simply re-established with no data lost

also, is it advised to close the cursor of the connection after I am done with specific INSERT/UPDATE? Or it's better to keep the cursor alive?

[brazen charm]

yeet it

should only use the cursor for one query generally

[lusty grail]

yeet?

[brazen charm]

delete it

[lusty grail]

got it ok

what about first questuon?

any ideas?

[brazen charm]

I dont think there is really any method

if the system disconnects it will be down to something externally causing it

because psycopg2 should auto re-establish and recycle connections providing nothing external causes an issue

[lament trail]

Anyone have any recommendations for resources to learn about relational databases?

[lusty grail]

you mean if I lost the internet (or DB lost it) for some time and then connection is back and I try to, i don't know, create cursor and run a query against it the psycopg will re-establish the connection by itself?

[lusty grail]

also, did anyone had an issue that psycopg while commiting the request would just nullify all numerical values?

on a second thought I asked in #help-kiwi but if anyone has an idea I am open

[spark ravine]

I'm in need of generating random bigint IDs in Postgres between a couple of ec2 instances. My current idea is using Python's "secret.randbelow" to generate IDs and check if the ID exists, which may be inefficient if too many IDs exist (IDK I haven't tried yet. Still using an autoincr. but I'm in need of changin that to randomized IDs), but I noticed other companies like Discord and Instagram use some kind of time based generation method. Anyone have any suggestion?

[lusty grail]

Why not use UUID instead of pure int key? @spark ravine

[lime echo]

I just installed Postgre and when I try to open it from psql it tells me that I didn't give a password. How can I solve this?
Also, I want to visually open my Postgre database, is there any software that I should get?

[twilit marlin]

I just installed Postgre and when I try to open it from psql it tells me that I didn't give a password. How can I solve this?
Also, I want to visually open my Postgre database, is there any software that I should get?
@lime echo the software is pgadmin or dbeaver

[frozen loom]

I'm using aiosqlite for a discord bot

Should I be closing the connection every time I check for the prefix? Are the close() functions necessary at the end?

async def get_prefix(client, message):
    async with connect(DB_PATH) as db:
        cur = await db.cursor()
        await cur.execute('SELECT prefix FROM guilds WHERE guild_id = ?', (message.guild.id,))
        prefix = await cur.fetchone()
        if not prefix:
            return when_mentioned_or('guh ')(client, message)
        else:
            return when_mentioned_or(prefix)(client, message)

        await cur.close()
        await db.close()

[lime echo]

@lime echo the software is pgadmin or dbeaver
@twilit marlin thank you.

@frozen loom no, not really but it's like closing your house door when you're leaving your house. Closing it isn't necessary but it makes it less possible for external intrusions.

[frozen loom]

Alright I gotcha

Thanks!

[spark ravine]

@lusty grail I still need to make FKs to that table, so indexing the uuid would be a pain.

[tepid cradle]

@torn sphinx What data type is it, the value of this?

Varchar is usually a good fallback when you're unsure

But trying to pass a class object of this type directly will not work

Your don't connect to tables, you connect to a Db

It doesn't matter. You can only connect to a database as a whole

You can't connect to a part of the database

I can't understand your question. Please use commas and full stops to make it readable.

[twilit marlin]

lol

[random creek]

hi I want to begin with database

can someone help me

I am making a discord bot

and I want to store data of each user

[proven arrow]

Well what kind of database are you looking for to begin with? SQL or NoSQL
Do you know how databases work on a high level (just the basic stuff)? And how confident are you with SQL? @random creek

[random creek]

ummm

[proven arrow]

SQL database is probably what most people go for when making bots like you say.

[quaint tiger]

SQLite Databases With Python - Full Course
https://www.youtube.com/watch?v=byHcYRpMgI4 probably a good start (note that I didn't watch it though)

[austere solstice]

Hey guys! is someone acquainted with Django and Fixtures?

[twilit marlin]

@austere solstice just post the question https://dontasktoask.com/

[austere solstice]

Hey again 😋

Okey:

I'm having a problem:

I'm trying to populate my database with django fixtures.

This is my model:
https://paste.pythondiscord.com/qitecabalu.py

This is my json:
https://paste.pythondiscord.com/uxanixacox.json

But when I execute python .\manage.py loaddata .\rules\fixtures\rules.json

I get the next error:

AttributeError: Problem installing fixture 'C:\Users\Ángel - Trabajo\Documents\AVC.\rules\fixtures\rules.json': 'NoneType' object has no attribute 'id'

[high geyser]

I am really new to databases

someone can u help me with sql?

I have made a discord bot but it accesses data only from json files. Thats not secure. I want to change to database. How can I start

and wih what kind of db?

Ive got some questions

whats a port number?

and whats a locale?

can I retrive the settings I used to create my db later?

[proven arrow]

Port is location at what a program will connect to and use for communicating with other services. If you think of an IP address as the address of a large building, then ports are suite numbers in that building

And locale is just the region, if i remember.

Different services will use different ports. For example, HTTP will use port 80 to communicate with your PC when delivering sites over this port. Similarly different databases will use different ports on your computer, to make a connection, so that it can communicate with other programs through that port.

[high geyser]

whats the maximum port number?

[proven arrow]

Not sure the exact number, but it's 16 bit integer, which is 2^16

You can check on the web for the exact number

[high geyser]

oh okay

and I am converting a few json files into a single database could u help me

how can I create a table?

and also different rows in the table

[proven arrow]

Well since you are new, I can recommend you read up or look up some SQL tutorial first? Most of the beginner questions will be answered through a good tutorial. Thereafter you will find it easier to integrate and use a database in your project. But if you have a specific question regarding databases or don't understand something you can ask here.

[quaint tiger]

65535, I think

ports 1-1024 are reserved (system ports), so you can only use them if you run as root.