databases

[high geyser]

can it be anything

and should I use the index when accessing data o is it automatic

[tepid cradle]

Index name can be anything. Just make sure you have something easily identifiable, like table_column_index

[high geyser]

thanks

[tepid cradle]

It's automatic, you don't need to do anything in your query to use an index

[high geyser]

oh thanks

[tepid cradle]

To give you an idea of how much an index can help in certain cases, I have a list of Indian mutual funds. By default, it used to take 200ms to search a particular fund. After I created an index, it takes ~10 ms

[high geyser]

c.execute("""CREATE INDEX keyword_of_tag
ON tags (keyword)""")```

this is what i have

oh i understand ur example

discord.ext.commands.errors.ExtensionFailed: Extension 'tags' raised an error: OperationalError: database is locked

xD

aw_database = sqlite3.connect('alfredwigans.db')
c = aw_database.cursor()

c.execute("""CREATE INDEX keyword_of_tag
ON tags (keyword)""")
c.execute("""CREATE INDEX alias_of_tag
ON alias_storage (alias_name)""")
print("Indexes created")```

[tepid cradle]

But make sure you create only those indices which you'll actually use. Don't create too many indices, otherwise it will make your writing slower and inflate the size of the db

[high geyser]

i never closed the db

thanks

[tepid cradle]

@high geyser If you're interested in databases, I'd recommend reading how btree indices work. It's very interesting. At a broad level, it's very simple and that simplicity itself is fascinating.

[high geyser]

thanks

but am still in school

i will understand it?

am a beginner

and why cant sqlite carry out multiple queries

I added ; at the end of them

                                c.execute("""DELETE FROM tags
                                            WHERE keyword = ? AND guildid = ?""", (key_word, ctx.guild.id))
                                c.execute("""DELETE FROM alias_storage
                                            WHERE guildid = ? AND original_name=?""", (ctx.guild.id, key_word))
                                aw_database.commit()```

like this one

how can i do it in one query using sqlite?

[tepid cradle]

You'll definitely understand it. In fact, I'll explain the basics rightaway. Then you can decide whether you want to read further.
Suppose you have a list like 23, 89, 56, 22, 90, 45, 21, 96, 54
If I asked you to look for 90, your only way would be to look at each element from the beginning and see if it is equal to 90.
But if I stored the same list as 21, 22, 23, 45, 54, 56, 89, 90, 96, what you would do to find 90 is start at the middle and see if it is smaller or larger than 90. If it is smaller, you discard the first half of the data. Then you repeat this till you find your data. You can do this only because the data is sorted. The number of comparisons you have to do is much smaller since you have a sorted index.
That's the basic idea behind a btree index.

[high geyser]

oh thats powerful

when u have large chunks of data

yes ill def lookup more

@tepid cradle

thanks

[tepid cradle]

Yes, it's very powerful

[high geyser]

mhm

but sqlite cannot handle multiple queries

in one xecution?

                                c.execute("""DELETE FROM tags
                                            WHERE keyword = ? AND guildid = ?""", (key_word, ctx.guild.id))
                                c.execute("""DELETE FROM alias_storage
                                            WHERE guildid = ? AND original_name=?""", (ctx.guild.id, key_word))
                                aw_database.commit()```

@high geyser when i put this in a single execute statement I got a limitation error

[tepid cradle]

Delete queries are best not combined. Do it in two separate queries, that's the best approach for deletion

[high geyser]

oh okay

[tepid cradle]

I'm actually not entirely sure if delete can even be combined, never tried myself

[high geyser]

and since they are related, am planning of using foreign key

but I have to study about it

[tepid cradle]

yes, if you use foreign key, then you can set it up to cascade on delete so that dependent data gets deleted automatically

Again, not something I have tried myself, but I know it exists

But be careful while using delete queries, make sure you don't accidentally delete data you need since it cannot be recovered.

[nocturne bay]

y isnt this working

cur = conn.cursor()
cur.execute("INSERT INTO project_lifebot (ID, BALANCE, BANK, LOANS, BANKSPACE) VALUES(user.id, 500, 350, 0, 350)")
conn.commit()
conn.close()```

im new to database ;-;

ok i think i go the problem

[wind sand]

how would I be able to auto increment something for postgres based on the column for that record? For example if you insert a record with a name fred it will increment and have a value if 1. Done again, and the value will be 2. But if you insert Mary as the name the increment column will have a different count and start at 1 since the name is different.

[tepid cradle]

y isnt this working

cur = conn.cursor()
cur.execute("INSERT INTO project_lifebot (ID, BALANCE, BANK, LOANS, BANKSPACE) VALUES(user.id, 500, 350, 0, 350)")
conn.commit()
conn.close()```

@nocturne bay you have a variable name inside the statement which is enclosed in double quotes. In effect, that variable name is being treated as a part of the string by Python. I'm talking about user.id

how would I be able to auto increment something for postgres based on the column for that record? For example if you insert a record with a name fred it will increment and have a value if 1. Done again, and the value will be 2. But if you insert Mary as the name the increment column will have a different count and start at 1 since the name is different.
@wind sand You can do it with a custom function most likely, but it will be rather slow. Why do you want to do it? It's something easily done in a query while extracting data, not a good idea to store data this way.

[quaint tiger]

@wind sand You can do it with a custom function most likely, but it will be rather slow. Why do you want to do it? It's something easily done in a query while extracting data, not a good idea to store data this way.
I second that. Not so much because it would be slow, but it would be safer against race conditions.

[tepid cradle]

race conditions?

@quaint tiger what does race conditions mean?

[quaint tiger]

Typically, it's

• read something in one place
• read the same thing in another
• write in the first place
• write in the second place, with outdated data.

Like if you read select max(id) as max_id from FOO where name = "Mary"; and then insert a value of max_id + 1.

If you have two inserts at the same time, they might both see max_id is 4, so they bother enter a new record with a value of 5

[tepid cradle]

ok, understood. Makes sense, in context of the question.

[quaint tiger]

Probably a shitty explanation 😅

And there are ways to mitigate it....

But if you do it at read-time, you can avoid it altogether.

[tepid cradle]

I do wonder how PostgreSQL manages scenarios like these, I keep reading that it has write locks etc., don't fully understand the mechanism, but I'd guess it wouldn't let this happen.
But then, I don't really understand a whole lot about the underlying workings of a database anyway 😅

[quaint tiger]

Depends how you structure your code.

[wind sand]

ah

[quaint tiger]

If you do read; then insert, it doesn't.

Maybe if you have a Unique constraint on the two fields, it will fail to insert and not have a race condition, but a write error...

[nocturne bay]

k got it

[wind sand]

well, the reason I want to do it that way is so that the user can easily delete something by an id. But there can be multiple discord guilds storing in the same data, and it would look wired if the thing they stored started at 5 for example

[hidden nest]

Hey everybody !
I got a little problem.
I have variable in python that stores a category ID chosen by a user

user_choice = (options[category_choice][0])

The result of is for example :

42495

Now I will need to do a query that gives me back all the products contained in the chosen category.
My DB look like this :

CREATE TABLE category
(
    id   SERIAL NOT NULL
        CONSTRAINT category_pk
            PRIMARY KEY,
    tag  TEXT,
    name TEXT   NOT NULL,
    url  TEXT   NOT NULL
);

CREATE TABLE product
(
    id              SERIAL NOT NULL
        CONSTRAINT product_pk
            PRIMARY KEY,
    name            TEXT   NOT NULL,
    store           TEXT,
    nutrition_grade CHAR   NOT NULL,
    url             TEXT   NOT NULL
);

CREATE TABLE product_category
(
    product_id      INTEGER      NOT NULL
        CONSTRAINT product_category_product_id_fk
            REFERENCES product,
    category_id INTEGER      NOT NULL
        CONSTRAINT product_category_category_id_fk
            REFERENCES category,
    CONSTRAINT product_category_pk
        PRIMARY KEY (product_id, category_id)
);

I am not really sure how to do my query in this case. Any tips?

[tepid cradle]

well, the reason I want to do it that way is so that the user can easily delete something by an id. But there can be multiple discord guilds storing in the same data, and it would look wired if the thing they stored started at 5 for example
@wind sand Those concerns disappear once you get into real production environments. Nobody cares what the id is, as long as it can uniquely identify a particular record using the said id

@hidden nest You need to join the two tables then filter based on the category id

[nocturne bay]

y do i get this error

Ignoring exception in command balance:
Traceback (most recent call last):
  File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\User\pycharmprojects\vscode\lifebot.py\lifebot.py", line 84, in balance
    await open_db(ctx.author)
  File "C:\Users\User\pycharmprojects\vscode\lifebot.py\lifebot.py", line 2253, in open_db
    cur.execute(f"INSERT INTO project_lifebot (ID, BALANCE, BANK, LOANS, BANKSPACE) VALUES({user.id}, 500, 350, 0, 350)")
psycopg2.errors.NumericValueOutOfRange: integer out of range


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\bot.py", line 903, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 855, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: NumericValueOutOfRange: integer out of range

what does it mean by int out of range?

[tepid cradle]

The general syntax of a join and filter is as follow:

select * from
  table_1 
  inner join table_2 on table_1.column = table_2.column
  where table_2.other_column = value

you can also use aliases.

select * from
  table_1 t1
  inner join table_2 t2 on t1.column = t2.column
  where t2.other_column = value

[high geyser]

hey

someone pinged me

sorry i came after a long time @tepid cradle

is there a way I can set the foreign key where if the parent value gets deleted the whole row of the child value also gets deleted?

FOREIGN KEY (original_name) REFERENCES tags (keyword)
ON UPDATE NO ACTION
ON DELETE DELETE THE ENTIRE ROW```

like that

[tepid cradle]

yes. While creating a foreign key, you have the option of defining it.
In Db terminology, it's called cascade. You're on the right path, it's actually very similar to your pseudo code. Search for "on delete cascade sqlite" and you should find the answer.
@high geyser

[hidden nest]

The general syntax of a join and filter is as follow:

select * from
  table_1 
  inner join table_2 on table_1.column = table_2.column
  where table_2.other_column = value

you can also use aliases.

select * from
  table_1 t1
  inner join table_2 t2 on t1.column = t2.column
  where t2.other_column = value

@tepid cradle thx man 🙂 just one question (maybe a dumb one) the value that I store in my python code is prone to change if the user uses a different input. What or how can I insert that into my query? 🙂

[high geyser]

thanks

[nocturne bay]

what can i do bout the int out of range prob?

[tepid cradle]

@hidden nest it's called parameterized query. the syntax can vary slightly based on the library you're using. the general syntax is like this
cursor.execute("select * from table where value = ? and other value = ?", (value1, value2))

[nocturne bay]

Ignoring exception in command balance:
Traceback (most recent call last):
  File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\User\pycharmprojects\vscode\lifebot.py\lifebot.py", line 84, in balance
    await open_db(ctx.author)
  File "C:\Users\User\pycharmprojects\vscode\lifebot.py\lifebot.py", line 2253, in open_db
    cur.execute(f"INSERT INTO project_lifebot (ID, BALANCE, BANK, LOANS, BANKSPACE) VALUES({user.id}, 500, 350, 0, 350)")
psycopg2.errors.NumericValueOutOfRange: integer out of range


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\bot.py", line 903, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 855, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: NumericValueOutOfRange: integer out of range
``` ^^^^

[tepid cradle]

@nocturne bay can you show the code which causes this error. It is difficult to understand without the code

[nocturne bay]

okk

async def open_db(user):
    cur= conn.cursor()
    cur.execute("SELECT ID FROM project_lifebot")
    rows = cur.fetchall()
    if user.id in rows:
        conn.close()
        return False
    else:
        cur = conn.cursor()
        cur.execute(f"INSERT INTO project_lifebot (ID, BALANCE, BANK, LOANS, BANKSPACE) VALUES({user.id}, 500, 350, 0, 350)")
        conn.commit()
        conn.close()
    return True

[hidden nest]

@hidden nest it's called parameterized query. the syntax can vary slightly based on the library you're using. the general syntax is like this
cursor.execute("select * from table where value = ? and other value = ?", (value1, value2))
@tepid cradle ok I will check 🙂 I am using psycopg2 on a postgresql DB 🙂

[tepid cradle]

for psycopg2, just replace ? in my example with %s

[high geyser]

@tepid cradle

i tried it

it doesnt work though

like when the parent value deletes

child value remains

[nocturne bay]

ok so wheres the prob? any idea?

[tepid cradle]

@nocturne bay when you do cur.fetchall(), it returns a list of tuples. So when you do if user.id in rows:, it will never return true because you are comparing an int to a tuple.

[high geyser]

I checked the db

with db browser

[nocturne bay]

@nocturne bay when you do cur.fetchall(), it returns a list of tuples. So when you do if user.id in rows:, it will never return true because you are comparing an int to a tuple.
@tepid cradle so what do i do?

[tepid cradle]

you need to compare the user.id to the contents of each tuple @nocturne bay
Look at the output of cur.fetchall() by printing it to the console. That will help

[nocturne bay]

k

[high geyser]

#FOREIGN KEY (original_name) REFERENCES tags (keyword)
#ON UPDATE CASCADE
#ON DELETE CASCADE
#PRIMARY KEY("id")
#)""")```

this is what the foreign key setup looks like

so, when the parent value is deleted is the entire row of child value also deleted?

original_name exists

see?

it didnt get deleted

but inside tags table keyword row is entirely deleted

[tepid cradle]

@high geyser sure you have implemented the foreign key on the child table and not the parent table?

[high geyser]

oh

[tepid cradle]

can't think of anything else

[high geyser]

ill check thanks

no its correct

like what I want is if the keyword in tags table is deleted original name in alias_storage must also be deleted. so I guess am right

c.execute("DROP TABLE alias_storage")
c.execute("""
CREATE TABLE "alias_storage" (
"alias_name"    TEXT,
"original_name"    TEXT,
"guildid"    INTEGER,
"authorid"    INTEGER,
"date_created"    BLOB,
"time_created"    BLOB,
"id"    INTEGER,
FOREIGN KEY (original_name) REFERENCES tags (keyword)
ON UPDATE CASCADE
ON DELETE CASCADE
PRIMARY KEY("id")
)""")```

this is how I created table alias_storage

[nocturne bay]

i see it returns like this

[(1,), (2,)]```

so how do i compare it to user.id?

[tepid cradle]

Looks right, not sure why it's not working. @high geyser
just an unrelated tip, generally it's a good idea to put the id as the first column. Just a visual thing, doesn't really matter.

[high geyser]

okay

c.execute("""DELETE FROM tags
             WHERE keyword = ? AND guildid = ?""", (key_word, ctx.guild.id))
                                aw_database.commit()``` this is how i delete keyword from tags

[tepid cradle]

I'll check and let you know later in the evening, if you're still stuck by then. Need to go now.

@nocturne bay look up list and tuple subsetting in Python. That's a Python question, not really a Db one. You just need to know how to extract values form lists and tuples

[high geyser]

thanks

[nocturne bay]

@nocturne bay look up list and tuple subsetting in Python. That's a Python question, not really a Db one. You just need to know how to extract values form lists and tuples
@tepid cradle yeah after asking i found that out so went to another channel

ty anyways

[high geyser]

wait I think

I have foreign key support turned off

how to turn it on

[nocturne bay]

this time i did this but it still doesnt work ;-;

async def open_db(user):
    cur= conn.cursor()
    cur.execute("SELECT ID FROM project_lifebot")
    rows = cur.fetchall()
    ids = [t[0] for t in rows]

    if user.id in ids:
        conn.close()
        print("ok")
        return False
    else:
        cur = conn.cursor()
        cur.execute(f"INSERT INTO project_lifebot (ID, BALANCE, BANK, LOANS, BANKSPACE) VALUES({user.id}, 500, 350, 0, 350)")
        conn.commit()
        conn.close()
    return True

@tepid cradle ;-;-;-; ^^^

it still says int out of range

[high geyser]

anyone help me how to turn pragma key support on my library doesnt support it

#c.execute("""
#CREATE TABLE "alias_storage" (
#"alias_name"    TEXT,
#"original_name"    TEXT,
#"guildid"    INTEGER,
#"authorid"    INTEGER,
#"date_created"    BLOB,
#"time_created"    BLOB,
#"id"    INTEGER,
#FOREIGN KEY (original_name) REFERENCES tags (keyword)
#ON UPDATE CASCADE
#ON DELETE CASCADE
#PRIMARY KEY("id")
#)""")``` I created table like this.

While deleting the following keyword from tags [which is parent value column] I get this error. Please help me
```py
c.execute("""DELETE FROM tags
             WHERE keyword = ? AND guildid = ?""", (key_word, ctx.guild.id))
             aw_database.commit()```
```py
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: foreign key mismatch - "alias_storage" referencing "tags"```

please help me

should I turn pragma keys on before every transaction?

Does this have to do anything with instances

@tepid cradle can u help me when u come back pls

I figured out to turn on pragma keys

The parent table does not exist, or The parent key columns named in the foreign key constraint do not exist, or The parent key columns named in the foreign key constraint are not the primary key of the parent table and are not subject to a unique constraint using collating sequence specified in the CREATE TABLE, or The child table references the primary key of the parent without specifying the primary key columns and the number of primary key columns in the parent do not match the number of child key columns.

found these reasons

The parent key columns named in the foreign key constraint are not the primary key of the parent table and are not subject to a unique constraint using collating sequence specified in the CREATE TABLE is this why?

should the parent column be a primary key

????????????

pls ping me when help

[hidden nest]

Thanks to @tepid cradle I did fin a query that gives me back all my products in a chosen category.

SELECT product.name as product_name FROM product INNER JOIN product_category ON product.id = product_category.product_id WHERE category_id = 42495

But I might have a little problem in my script. I wanna replace the fixed value I tried in my query by the value stored in a variable that is dependent on the user input.

        cursor.execute("SELECT product.name as product_name FROM product INNER JOIN product_category ON product.id = product_category.product_id WHERE VALUE = %s", user_choice)
product_result = cursor.fetchall()
print(product_result)

I tried this but getting back an error

TypeError: 'int' object does not support indexing

[empty vortex]

Hey everyone ! I have a problem with database modeling, I would like to ask you for advice ..
I want to store link information between devices, my table device has columns that help to describe a device.
I'm using a Python script that makes a network discover through SSH protocol, this script is supposed to build links between devices with the result of CDP in every device.
While the process is running, every device is visited and a list of Device object is built. At the end, a function loop over every device and create links between them.

Here's a simple example of data :

devices_list = []

device1 = {
    "hostname":"device-test-01",
    "cdp": [
        {"hostname": "device-test-02", "local-int": "g0/1", "dest-int": "g0/2"}
    ]
}

devices_list.append(device1)

device2 = {
    "hostname":"device-test-02",
    "cdp": [
        {"hostname": "device-test-01", "local-int": "g0/2", "dest-int": "g0/1"}
    ]
}

devices_list.append(device2)

Let's imagine I have this list at the end of my process and I want to create links between these two devices based on them cdp entries. I have imagine this modelisation :

The fields Dev_Id_A and B are foregin keys from t_Device and the field Lin_Dev_A_Int and B are for identifiying the connected interface

Now, if I loop over my devices_list to create links in my table t_links, I will have to check on every device if the link was already created in the column LIN_Dev_Id_A OR in the colum LIN_Dev_Id_B .. Does someone see a better solution to
store these data ?

Hope it is clear enough.

[hidden nest]

@tepid cradle got it my bad 😉

[empty vortex]

sry @hidden nest if I'm in the middle of something 😄

[hidden nest]

sry @hidden nest if I'm in the middle of something 😄
@empty vortex not at all 😉 no worries 😉

[lime echo]

How do I export my Postgres database from my localhost to Heroku? Heroku tutorial is a bit confusing.
I am using Windows by the way.

[high geyser]

Can someone pls help me

#c.execute("""
#CREATE TABLE "alias_storage" (
#"alias_name"    TEXT,
#"original_name"    TEXT,
#"guildid"    INTEGER,
#"authorid"    INTEGER,
#"date_created"    BLOB,
#"time_created"    BLOB,
#"id"    INTEGER,
#FOREIGN KEY (original_name) REFERENCES tags (keyword)
#ON UPDATE CASCADE
#ON DELETE CASCADE
#PRIMARY KEY("id")
#)""")``` I created table like this.

While deleting the following keyword from tags [which is parent value column] I get this error. Please help me
```py
c.execute("""DELETE FROM tags
             WHERE keyword = ? AND guildid = ?""", (key_word, ctx.guild.id))
             aw_database.commit()```
```py
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: foreign key mismatch - "alias_storage" referencing "tags"```

@high geyser

Pls ping me when help

[boreal niche]

TypeError: 'NoneType' object is not subscriptable

[slender sentinel]

https://cdn.discordapp.com/attachments/696432394974265374/756478578245500978/unknown.png
can anyone help mw with the syntax

[nocturne bay]

anyone its a basic ques ig but im too new in db

how can i put a variable in this line?

cur.execute(f"INSERT INTO project_lifebot (ID, BALANCE, BANK, LOANS, BANKSPACE) VALUES(5, 500, 350, 0, 350)")```

pls help anyone

[high geyser]

@nocturne bay

replace variable with ?

and then pass a tuple after the string containing the value of the variable

[nocturne bay]

i didnt gt u...

@high geyser

[high geyser]

what variable u want to put

[nocturne bay]

suppose x

[high geyser]

say ID and balance

[nocturne bay]

is a variable

instead of 5 of id i want to put a variable

[high geyser]

cur.execute(f"INSERT INTO project_lifebot (?,?, BANK, LOANS, BANKSPACE) VALUES(5, 500, 350, 0, 350)",(5,500))```

like this

[nocturne bay]

@high geyser but wheres the x? the variable that i wanted to use

[high geyser]

wait

[nocturne bay]

is it alwys ?

[high geyser]

cur.execute(f"INSERT INTO project_lifebot (?,?, BANK, LOANS, BANKSPACE) VALUES(x, 500, 350, 0, 350)",(5,500))```

like this

[nocturne bay]

oh

[high geyser]

hope I helped u

[nocturne bay]

is it necessary to put 5 and 500 later?

(5,500))

this thing^

[high geyser]

no wait

I messed up

heres the correct syntax

cur.execute(f"INSERT INTO project_lifebot (ID, BALANCE, BANK, LOANS, BANKSPACE) VALUES(?, 500, 350, 0, 350), (x,)")```

hope u got it

[slender sentinel]

@high geyser can u help with mine

[high geyser]

?

what

[nocturne bay]

ohhhh tyyyy

[slender sentinel]

https://cdn.discordapp.com/attachments/696432394974265374/756478578245500978/unknown.png

this one

[high geyser]

send code

not a ss

[slender sentinel]

how do i send code

[high geyser]

!code

[delicate field]

Discord has support for Markdown, which allows you to post code with full syntax highlighting. Please use these whenever you paste code, as this helps improve the legibility and makes it easier for us to help you.

To do this, use the following method:

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

Note:
• These are backticks, not quotes. Backticks can usually be found on the tilde key.
• You can also use py as the language instead of python
• The language must be on the first line next to the backticks with no space between them

This will result in the following:

print('Hello world!')

[slender sentinel]

def delete():

    def click2():
        cur = conn.cursor()
        cur.execute('DELETE FROM students WHERE ID = %s'),(idval.get(),)
        conn.commit()
        
        delmain.destroy()


    #Add Student GUI
    delmain = Toplevel(master=dataframe)
    delmain.grab_set()
    delmain.configure(width=460, height=200)
    delmain.iconbitmap('add.ico')
    delmain.resizable(False,False)
    delmain.title('Add Student')
    delmain.config(bg='#B2FFFF')

    #Label
    l1 = Label(delmain, text='#Enter Student Id To Delete Student ', font=('Comic Sans MS',14,'italic'), anchor='w' ).place(x=10, y=10)
    l2 = Label(delmain, text='Enter ID :', bg='#CCCCFF', font=('Comic Sans MS',14,'italic'), relief=GROOVE, borderwidth=3, width=9, anchor='w' ).place(x=10, y=50)

    #Variable
    idval = StringVar()

    #Entry Box
    e2 = Entry(delmain, font=('times',13), bd=3, textvariable=idval ).place(x=140, y=55)

    #Button
    delete = Button(delmain, text='DELETE', font='times 14 bold italic', command=click2, width = 15, bd=4, activebackground='#F88379', bg='#f9e3be', activeforeground='white').place(x=10, y=100)


    delmain.mainloop()

[nocturne bay]

how do i send code
@slender sentinel copy ur code and paste it inside

[high geyser]

which linw wrrors

errors

[boreal niche]

TypeError: 'NoneType' object is not subscriptable

[slender sentinel]

the cur,execute

[high geyser]

which line

[boreal niche]

?

[slender sentinel]

4 th

[high geyser]

idval.get() whats this

send me the error too

codeblock it

the full error

[high geyser]

.

[nocturne bay]

@high geyser for some reason it says syntax error ;-;

if user.id not in ids:
        
        cur.execute(f"INSERT INTO project_lifebot (ID, BALANCE, BANK, LOANS, BANKSPACE) VALUES(?, 500, 350, 0, 350), (user.id,)")
        conn.commit()
        conn.close()
            
        return False```

[high geyser]

I cant read ss pls its too hard

[slender sentinel]

ohh sry

[high geyser]

what syntax error

paste full code

[slender sentinel]

Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\Jithan CK\AppData\Local\Programs\Python\Python38\lib\site-packages\mysql\connector\connection_cext.py", line 487, in cmd_query
    self._cmysql.query(query,
_mysql_connector.MySQLInterfaceError: 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 '%s' at line 1

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\Jithan CK\AppData\Local\Programs\Python\Python38\lib\tkinter\__init__.py", line 1883, in __call__
    return self.func(*args)
  File "C:\Users\Jithan CK\Dropbox\My PC (LAPTOP-NP02B41L)\Desktop\ip project\main2.py", line 87, in click2
    cur.execute('DELETE FROM students WHERE ID = %s'),(idval.get(),)
  File "C:\Users\Jithan CK\AppData\Local\Programs\Python\Python38\lib\site-packages\mysql\connector\cursor_cext.py", line 264, in execute
    result = self._cnx.cmd_query(stmt, raw=self._raw,
  File "C:\Users\Jithan CK\AppData\Local\Programs\Python\Python38\lib\site-packages\mysql\connector\connection_cext.py", line 491, in cmd_query
    raise errors.get_mysql_exception(exc.errno, msg=exc.msg,
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s' at line 1

[high geyser]

full error

@nocturne bay

[nocturne bay]

paste full code
@high geyser k

[high geyser]

i meant error

[nocturne bay]

File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\User\pycharmprojects\vscode\lifebot.py\lifebot.py", line 84, in balance
    await open_db(ctx.author)
  File "C:\Users\User\pycharmprojects\vscode\lifebot.py\lifebot.py", line 2253, in open_db
    cur.execute(f"INSERT INTO project_lifebot (ID, BALANCE, BANK, LOANS, BANKSPACE) VALUES(?, 500, 350, 0, 350), (user.id,)")
psycopg2.errors.SyntaxError: syntax error at or near ","
LINE 1: ...bot (ID, BALANCE, BANK, LOANS, BANKSPACE) VALUES(?, 500, 350...
                                                             ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\bot.py", line 903, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 855, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: SyntaxError: syntax error at or near ","
LINE 1: ...bot (ID, BALANCE, BANK, LOANS, BANKSPACE) VALUES(?, 500, 350...
                                                             ^```

there ^^^

[high geyser]

one at a time pls

[nocturne bay]

oh im sorry ;-;

[high geyser]

@slender sentinel try using ? instead of %s

am not that familiar with sql though

[slender sentinel]

ok

[high geyser]

ik sqlite

@nocturne bay what language ur using

[nocturne bay]

postgresql

sql

[slender sentinel]

@high geyser nope not working..thx though

[high geyser]

wait I gtg sorry

someone else might help u here

[nocturne bay]

ok ;-;

y rnt there any official helpers in this channel ;-;-;-;-;

[nocturne bay]

anyone its a basic ques ig but im too new in db
how can i put a variable in this line?

cur.execute(f"INSERT INTO project_lifebot (ID, BALANCE, BANK, LOANS, BANKSPACE) VALUES(5, 500, 350, 0, 350)")

pls help anyone

[karmic nest]

wdym how you can put a variable?

f"{variable_name}"

[nocturne bay]

@karmic nest f string doesnt work in postgresql or any dbs

[karmic nest]

oh then idk

does this work? if u do "hi" + variable

[nocturne bay]

nope

[slow heath]

I have a question if the last conversation is solved but I don't want to step on toes if your still discussing

I have a postgresql database and I want to put information from it onto a web app. How do I autofill and html template with the fields from the database?

[tepid cradle]

cur.execute(f"INSERT INTO project_lifebot (ID, BALANCE, BANK, LOANS, BANKSPACE) VALUES(?, 500, 350, 0, 350), (x,)")```

@high geyser Happy to see you're already helping others, great progress 😄 . And also happy to see that you made a mistake, then realised it and posted the correct answer.
Edit: You still got it wrong though 😅

@nocturne bay as I pointed out earlier, it's called parameterized query. If you look this term up along with the library name, you'll find your answer. Also, Psychisimp had actually given you the answer above, although, I realise it's not exactly correct.

[nocturne bay]

oh

[tepid cradle]

So the basic idea is that you use a placeholder in the query where you want to variable to go, then pass the values in a tuple as the second parameter to cur.execute, like this:
cur.execute("INSERT INTO project_lifebot (ID, BALANCE, BANK, LOANS, BANKSPACE) VALUES(?, 500, 350, 0, 350)", (x,))

The placeholder and the way you pass values can differ somewhat based on the library used. I forgot which one you were using

[nocturne bay]

kk lemme try

oh gosh ur back yay!

[tepid cradle]

😆

Which library and database is it?

[nocturne bay]

pycopg2

postgresql

[tepid cradle]

Oh, right. Then use %s in place of question mark in the above example

[nocturne bay]

k

[tepid cradle]

rest is fine

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @nocturne bay until 2020-09-18 13:16 (9 minutes and 59 seconds) (reason: newlines rule: sent 187 newlines in 10s).

[lusty igloo]

!unmute 711992231350894673

[delicate field]

:incoming_envelope: :ok_hand: pardoned infraction mute for @nocturne bay.

[lusty igloo]

Please refrain from posting a lot of spammy whitespace next time @nocturne bay

If your code is long considering also using our paste site

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

[slow heath]

I have a postgresql database and I want to put information from it onto a web app. How do I autofill and html template with the fields from the database? Does anyone know? I tried looking on stack overflow and google and haven't found anything helpful

[nocturne bay]

Please refrain from posting a lot of spammy whitespace next time @nocturne bay
@lusty igloo k i mistakenly copied whitelines while copying my code ;-;

[tepid cradle]

I have a postgresql database and I want to put information from it onto a web app. How do I autofill and html template with the fields from the database? Does anyone know? I tried looking on stack overflow and google and haven't found anything helpful
@slow heath because you're not explaining your question well. What framework are you using for this web app?

[slow heath]

python and flask

sorry for being unclear

also I meant to say autofill *an htm template not *and

[boreal niche]

Command raised an exception: InternalError: Unread result found

MySQL

[slow heath]

@slow heath because you're not explaining your question well. What framework are you using for this web app?
@tepid cradle did the above help?

[tepid cradle]

Yes, that did help me understand the question.
Unfortunately I'm not familiar with Flask.

[brazen charm]

@slow heath just serialize it all into a dict and send it as context for jinja

you can access dicts via dict.key similar to how js would see it for JSON stuff

[slow heath]

Ok thanks

[nocturne basin]

Could someone who's good in SQLalchemy help me out in #help-burrito ?

[fringe crater]

                    database.execute(f"UPDATE PROFILE SET COINS = COINS + 1000 WHERE USER_ID IN ({placeholders1})",[correct_player]).fetchall()

why am i getting this error Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.

[torn sphinx]

Django.db.utils.Databaseerror: database disk image is malformed
Can anyone solve this problem..

[harsh pulsar]

@fringe crater what is placeholders1?

And what database library is this?

[proud needle]

def delete_mangadb():
    author = t1.get()
    #prompt to ask if user is sure to delete database
    if messagebox.askyesno("Are you sure?", "Delete Manga title?"):
        delete= "DELETE FROM mangatable WHERE ID ="+author
        cursor.execute(delete)
        clear()
    else:
        return True

Okay terminal told me this is syntax error, but i can't spot it. can anyone help me please? thank you

[tepid cradle]

Django.db.utils.Databaseerror: database disk image is malformed
Can anyone solve this problem..
@torn sphinx what database are you using? Local or remote?

@proud needle what is clear()? Don't think that's a Python command

[torn sphinx]

Local database @tepid cradle

[tepid cradle]

sqlite?

[torn sphinx]

Yes

[tepid cradle]

I am not sure of the reason for this error. But if you didn't have any data in the database, then delete the db file and existing migrations, then rerun the migrations. That will fix the problem.
You shouldn't do that however if you have data in it which you need.

[torn sphinx]

Okay thank you so much for the help @tepid cradle I ll try n let u knw

[proud needle]

@proud needle what is clear()? Don't think that's a Python command
@tepid cradle its an sql command

[tepid cradle]

You're running that in python

[proud needle]

yes

because im running a connector for mysql

so it allows me to do that

oh wiat

[nocturne bay]

i need help
im using postgresql and psycopg2 as the library

i want to insert a variable but its not working

its working only when i put a numerical/text value

heres me code

if user.id not in ids:
        
        cur.execute(f"INSERT INTO member_balance (ID, WALLET, BANK, LOANS, BANKSPACE) VALUES(%s, 500, 350, 0, 350)", (10,))
        conn.commit()
     
        conn.close()
            
        return False```

oh i got one thing now

if my int is as big as a user id

like

711992231350894673

it says int out of range

but y?

[tepid cradle]

so it allows me to do that
@proud needle You can't directly run SQL commands in your Python script. You need to use cursor.execute("SQL command goes here")

[nocturne bay]

it goes max 10 digits but y?

i can continue upto 711992231

[pseudo cove]

because the int is probably represented internally using 32 bit

2147483647 is the max 32 bit num

@nocturne bay

[quaint tiger]

There is BIGINT in MySQL....

and postgres

and sqlite

[spark basalt]

"If the struct listed in PEP 0237 is accurate, longs' lengths (in digits) are stored as unsigned 32-bit integers, up to 4,294,967,295 digits"
https://stackoverflow.com/questions/538551/handling-very-large-numbers-in-python

"this type is called long and is separate from the int type... In Python 3.0+, the int type has been dropped completely."

[tepid cradle]

@nocturne bay if you want numbers that big, use bigint data type in postgres

Or better still, store it as varchar since I'm pretty sure you're not going to be using it in any calculations

[proud needle]

@proud needle You can't directly run SQL commands in your Python script. You need to use cursor.execute("SQL command goes here")
@tepid cradle ah okay cheers

[nocturne bay]

because the int is probably represented internally using 32 bit
@pseudo cove what if i make it a str?

that would be better ig

i wont use it anyways

[torn sphinx]

is possible to delete only the parent row in mysql, and keep child row data?

Cannot delete or update a parent row: a foreign key constraint fails

I have like this setup:

Products
  - ID
  - NAME
  ....

ORDER_ITEM
  - ID
  - QTY
  - USER_ID
  - ORDER_ID REFERENCES ID ON TABLE PRODUCTS

[marsh tinsel]

why do i get this error pymongo.errors.ConfigurationError: The DNS operation timed out after 21.124611854553223 seconds

[errant herald]

in sqlite3 with its cursor.execute() function, will this work?:
query = """select contents from table where tablevar = ? or ?"""
cursor.execute(query, (var1, var2))

i am trying to get a list of all the rows with one thing or another in one of the columns

[quaint tiger]

Should be where tablevar in (?, ?) I think.

[errant herald]

thanks!

ill see if it works

[quaint tiger]

No guarantee, not in sqlite atm 😅 ... but NP

[errant herald]

it works dont worry

thanks!

[tepid cradle]

@errant herald as a general rule if a = b or c doesn't work in any language (at least, not in the ones I know of). It's always if a = b or a = c

!or-gotcha

[delicate field]

When checking if something is equal to one thing or another, you might think that this is possible:

if favorite_fruit == 'grapefruit' or 'lemon':
    print("That's a weird favorite fruit to have.")

While this makes sense in English, it may not behave the way you would expect. In Python, you should have complete instructions on both sides of the logical operator.

So, if you want to check if something is equal to one thing or another, there are two common ways:

# Like this...
if favorite_fruit == 'grapefruit' or favorite_fruit == 'lemon':
    print("That's a weird favorite fruit to have.")

# ...or like this.
if favorite_fruit in ('grapefruit', 'lemon'):
    print("That's a weird favorite fruit to have.")

[errant herald]

yeah dw i figured it out now, just got another problem in chlorine

[torn sphinx]

In PyMongo, if I use collection.find(), and pass a dict where one of the values is an empty string, so like this: collection.find({"key": ""})
Would that look for everything where the key is "key" and the value can be anything? Or does that look specifically for a key of "key" and an empty string as the value?

[proven arrow]

is possible to delete only the parent row in mysql, and keep child row data?
@torn sphinx You can set on delete to SET NULL
https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

But make sure the field for the foreign key is also nullable

[nocturne bay]

how can i pass a variable in cloumns?

im using postgresql

and psycopg2 library

in docs it says this

cur.execute(                                                # correct
...     SQL("INSERT INTO {} VALUES (%s)").format(Identifier('numbers')),
...     (10,))```

but as i did this it says SQL not defined

heres my code

async def update_acc(user, change=0, mode='wallet'):
    cur= conn.cursor()
    cur.execute(SQL("UPDATE member_balance set {} = {} + %s WHERE ID = %s").format(Identifier(mode, mode)),
                (change, str(user.id)))
    conn.commit()

@tepid cradle could u help me? 😅

[tepid cradle]

What is Identifier?

[burnt turret]

In PyMongo, if I use collection.find(), and pass a dict where one of the values is an empty string, so like this: collection.find({"key": ""})
Would that look for everything where the key is "key" and the value can be anything? Or does that look specifically for a key of "key" and an empty string as the value?
@torn sphinx It would check where the value for key is an empty string as fair as I know. If what you're trying to do is get all records where that key exists, mongodb has an operator called $exists which takes a bool (Note: I know for sure mongodb has this but I've never used it in pymongo as such, so maybe look into the syntax specific to pymongo)
The query to get the records where that key exists would look something like
collection.find({"key": {"$exists": True}}) this, I think

[torn sphinx]

I was able to find an answer and passing the empty string looks for any value. But thank you. That may be better practice. If I got confused on it, anyone reading my code might as well. @burnt turret

[burnt turret]

Oh alright, said it searches for an empty string based on what I've tried through the shell itself and not a driver.

[torn sphinx]

@burnt turret Actually I think you were right. I found the answer on Stack Overflow but it doesn't seem to be working for me. Ty for your help 😂

[nocturne bay]

@jovial notch r u using postgresql?

[brazen charm]

pst you could solve that by looking at the stack trace

[jovial notch]

Replying to https://canary.discordapp.com/channels/267624335836053506/342318764227821568/756928901996871801
Yes I do @nocturne bay.

[nocturne bay]

oh

@jovial notch the thing that u did in values

u should do %s

in each case

then put the valus in another parenthesis

example

[jovial notch]

What do you mean, can you show me?

[nocturne bay]

yes wait a bit

[brazen charm]

@nocturne bay thats wrong

you're being missled by Psycopg2 which is probably where you got that idea from

[nocturne bay]

huh y?

oh

yeah i use psycopg2

my bad then

[brazen charm]

Postgre uses $n notation for place holders but psycopg2 likes to confuse people into bad habits

[jovial notch]

@brazen charm I really dont get what the error for. I be using asyncpg for 1 year now never had the syntax error.

[brazen charm]

@jovial notch remove the space between the table name and the (

greekmafiabans(user, mod, reason, avatar)

as a habit

tho i imagine it will still error

[nocturne bay]

is asyncpg better than psycopg2?

[brazen charm]

yes

considerably better

[nocturne bay]

oh

[brazen charm]

in terms of consistency, performance etc...

@jovial notch whats your current table layout btw

that syntax error is normally when you are trying to refrence a column that doesnt exist

[jovial notch]

Replying to @brazen charm from https://canary.discordapp.com/channels/267624335836053506/342318764227821568/756931938693939323
its not that i tried it aswell

all columns are character varying @brazen charm

[brazen charm]

hmm that is strange

[jovial notch]

ikr

u seen my code to

[brazen charm]

does it work on pgadmin's query tool?

[jovial notch]

hmm didnt try

[brazen charm]

if you hard code the values u wanna insert just to test

[jovial notch]

@brazen charm

[brazen charm]

i mean if you do the insert command with the query tool

like

[jovial notch]

@brazen charm

[burnt turret]

is user a reserved sql keyword?

[jovial notch]

https://canary.discordapp.com/channels/267624335836053506/342318764227821568/756932772425105448

[brazen charm]

its gotta be

[jovial notch]

i dont know what u mean

[brazen charm]

thats a good catch @burnt turret

pgadmin seems to comfirm it

both user and mod are key words reserved in postgre apparently

[jovial notch]

so i just need to chage column names

ohyes

😛

i though that too

@burnt turret so what was my problem?

[burnt turret]

user and mod are reserved keywords in postgre, so you can't be using them like that (pretty sure they have other functions)

[jovial notch]

@burnt turret are there any bypasses i could do

or disable all of them?

[burnt turret]

why not just use a different column name?
i don't think there's a way you can override the reserved keywords

[jovial notch]

@burnt turret a reserve keywords is size too

so i can't store an avatar_url?

this is my url for example

[burnt turret]

you can't be using reserved keywords as your table/column name

it's fine if they are in the data itself, as it is in this case (assuming that column is not called size)

[jovial notch]

@burnt turret should i even store avatar?

if they change it it will still display their old avatar right?

[burnt turret]

yeah

You can always get the user's avatar in your code from their Member object, so storing it seems pointless (off-topic for this channel now though)

[agile laurel]

Is there away to store members data in a cloud? Using discord py?
So u can reuse that data for future commands?

😁

[tropic kayak]

yes

what database are you going to use

[agile laurel]

I dont even know what databases are

:(

I am just trying to make an attack feature for my bot

When members attack someone or steal golds from someone it takes that golds integer away from them.

So i need to store those current golds in a cloud.

Any helps?

:D

@tropic kayak

Aight

Imma learn

database

[torn sphinx]

@agile laurel SQL, Postgres is a decent one to learn & has a nice async library asyncpg

[tropic kayak]

if you are looking for nosql use mongodb

[agile laurel]

I have 0% idea of what u guys are talking about

[tropic kayak]

then learn

[torn sphinx]

https://www.youtube.com/watch?v=qw--VYLpxG4
@agile laurel

Honestly easier to learn than something like Python/JS, etc.

[tepid escarp]

is it possible to set an integer as a table name using asyncpg?

[pseudo cove]

why

sql table names have to start with alpha or underscore

@tepid escarp

but this sounds like xy problem

[tepid escarp]

ok

[sharp bobcat]

Hey there, I'm having some problems with PostgreSQL, specifically the way I'm inserting into my table.

I have a table like this

SERVERID | ROLE1 | ROLE2

Where SERVERID is UNIQUE and ROLE1 and ROLE2 are set defaultly to 0 (they're ints)

what I need is a way to first insert 1 role with serverid into the table and second time I need to insert the other role, without overwriting the already set one.

I've been using this:
INSERT INTO roles(serverid, role1) VALUES ($1, $2)
INSERT INTO roles(serverid, role2) VALUES ($1, $2)

this resulted in an error that says that the serverid already exists.
What would be the correct way to do this?

[brazen charm]

sounds like you probably have serverId as being a unique constraint

btw generally a bad idea having a column for each seperate role if you intend for the amount you can have to be dynamic

[sharp bobcat]

the amount of roles will certainly not change

and yes, serverid is unique, it should be

what I need is a way to write into that row and overwire the 0 in role2 to something else

[shell ocean]

what I need is a way to write into that row and overwire the 0 in role2 to something else
@sharp bobcat ...UPDATE?

[brazen charm]

yeah update would work

you can probably do something like IF EXISTS UPDATE xyz

[shell ocean]

UPDATE table SET role2 = new_value WHERE server_id = something

[sharp bobcat]

yeah, that's probably what I was looking for, I'm very new to postgres so i didn't know about it, thanks for the help

although now that I'm thinking about it might still be a problem since I don't know which will be first, I've got 2 functions controlled by user which will write into the db and either one of them can be first (role1 or role2) because of this I can't be certain if that row already exists, how could I implement a check for it?

I probably could get away with 2 calls and if statement directly in python, but I wonder if there's a way to do this purely with postgres

[shell ocean]

although now that I'm thinking about it might still be a problem since I don't know which will be first, I've got 2 functions controlled by user which will write into the db and either one of them can be first (role1 or role2) because of this I can't be certain if that row already exists, how could I implement a check for it?
@sharp bobcat hm so to be clear

one function per role, right?

bu either can be first?

[sharp bobcat]

yes

so I don't know when to use INSERT and when UPDATE

[shell ocean]

hm I'm guessing you want INSERT ON CONFLICT

because this is basically an upsert, right?

INSERT INTO table VALUES (role1_value, null) ON CONFLICT unique_id DO UPDATE SET role1 = role1_value WHERE server_id = server_id_value

and flip it for role2

unique_id needs to be the name of the UNIQUE constraint on server_id, whatever it's called

not really sure if this is the best way

[sharp bobcat]

I was thinking of this:

IF EXISTS (SELECT FROM roles WHERE serverid=$1) THEN
            UPDATE roles SET participant_role=$2 WHERE serverid=$1
        ELSE
            INSERT INTO roles(serverid, participant_role) VALUES ($1, $2)
        END IF;

but your approach is probably better

[shell ocean]

I mean

I don't really know SQL

although I feel like IF EXISTS would be less efficient

IF that matters to you

[digital current]

does anyone know how to take out/filter something of databases?

[sharp bobcat]

efficiency is not much of a concern in this case, I'd just like to learn the proper way this should be done, generally and I think yours is better

[shell ocean]

does anyone know how to take out/filter something of databases?
@digital current what are you using to access the database?

[digital current]

A mongodb connection

[shell ocean]

okay, that's important info

MongoDB vs, say, Cassandra, or some SQL dialect, would mean different syntax etc.

so what do you want to do?

I'm assuming you're using pymongo?

[digital current]

yup

[shell ocean]

what filter?

[digital current]

i'm trying to get the "Wins" and "Losses" from a discord member and showing it normally without the (ID) and stuff

[brazen charm]

use the find() function on a collection

[digital current]

for example:

[brazen charm]

remember that it takes the first dict as the query and the second as the filter

you can either use the filter to target everything to include or everything to exclude but not both

https://www.w3schools.com/python/python_mongodb_find.asp

w3schools does a good example of using filters to change what gets returned

[digital current]

this entire time i was looking at this website https://docs.mongodb.com/manual/reference/method/db.collection.find/#examples

thank u so much

i'll see if i get it to work

any idea on why only 1 being sent to server?

[proud needle]

def delete_mangadb():
#prompt to ask if user is sure to delete database
    try:
        confirm = tk.messagebox.askquestion('Delete manga', 'are you sure to delete manga?')
        if confirm == 'yes':
            audata = str(t2.get())
            query = "DELETE FROM mangatable WHERE Author = "+audata
            cursor.execute(query, audata)
            cursor.commit()
            tk.messagebox.showinfo('Delete manga', 'Deleted: %d' % cursor.rowcount)
            clear()
        else:
            tk.messagebox.showinfo('Return','Deletion cancelled.')
    except mysql.connector.Error as err: #done this to error check what happens if data fails to delete
        print("Error: {}".format(err))

can anyone please help me where the syntax error is? thank you

Error: 1054 (42S22): Unknown column 'Test' in 'where clause' okay i get this error instead now

[torn sphinx]

I have this table example:

message_id, message_text, created_at
118, "hh", 1600568834
228, "hello", 1600568835
118, "hi", 1600568836 #this message was edited
078, ":D", 1600568837

I want to sort it based on the created_at, but if the message was edited, I want them to be grouped with the first instance. The output would be:

118, "hh", 1600568834
118, "hi", 1600568836
228, "hello", 1600568835
078, ":D", 1600568837

How could I make this query?

[proud needle]

def delete_mangadb():
#prompt to ask if user is sure to delete database
    try:
        confirm = tk.messagebox.askquestion('Delete manga', 'are you sure to delete manga?')
        if confirm == 'yes':
            audata = str(t2.get())
            query = "DELETE FROM mangatable WHERE Author = "+audata
            cursor.execute(query, audata)
            cursor.commit()
            tk.messagebox.showinfo('Delete manga', 'Deleted: %d' % cursor.rowcount)
            clear()
        else:
            tk.messagebox.showinfo('Return','Deletion cancelled.')
    except mysql.connector.Error as err: #done this to error check what happens if data fails to delete
        print("Error: {}".format(err))

can anyone please help me where the syntax error is? thank you
@proud needle Re: I fixed it

def delete_mangadb():
#prompt to ask if user is sure to delete database
    try:
        confirm = tk.messagebox.askquestion('Delete manga', 'are you sure to delete manga?')
        if confirm == 'yes':
            audata = t1.get()
            query = """DELETE FROM mangatable WHERE id = %s"""
            cursor.execute(query, (audata,))
            mdb.commit()
            tk.messagebox.showinfo('Delete manga', 'Deleted: %d' % cursor.rowcount)
            clear()
        else:
            tk.messagebox.showinfo('Return','Deletion cancelled.')
    except mysql.connector.Error as err: #done this to error check what happens if data fails to delete
        print("Error: {}".format(err))```
if anyone's wondering this is how it got fixed ahahah damn

[placid flicker]

Does anyone know how to connect to a remote Oracle Database in Python?

[pure cypress]

You can do it with pyodbc

[elder glade]

is asyncpg better than psycopg2?

[shell ocean]

is asyncpg better than psycopg2?
@elder glade not "better"..."different".

asyncpg is asynchronous.

[elder glade]

oh

[nocturne bay]

cur= conn.cursor()
cur.execute("SELECT wallet FROM member_balance WHERE ID = %s", (str(user.id)))
    wallet = cur.fetchall()```

what did i do wrong here

[shell ocean]

@nocturne bay if you think something is wrong, you should generally post the error, too.

[nocturne bay]

ohk

[shell ocean]

anyway, my guess is that you actually want cur.execute("SELECT wallet FROM member_balance WHERE ID = %s", (user.id,))

I'm not sure what library that is, but in general parameters are taken as a tuple

[nocturne bay]

im using psycopg2

[shell ocean]

and (str(user.id)) is not a tuple; you need a comma for a one-element tuple

yeah, you want (user.id,)

[nocturne bay]

oo

[shell ocean]

I don't think you need to cast it to str first

but if you do that would be (str(user.id),)

[nocturne bay]

it worked ty @shell ocean

[errant herald]

is the syntax for this going to work? i wish to have a search filter that will still accept a string in the table if it is for example 'apple' and the filter is 'app'

query = """select columnone from table where ? in columnone"""
cursor.execute(query, filter)

well, i know the syntax for it isnt correct, as itl think that ? IS the table, but is there a way to accomplish this with correct syntax?

help would be greatly appreciated

wait, doing something seemingly uneccesary made it work, nevermind!

so new problem, i removed the errors, but i still dont know how to make an if filter in columnone, only vice versa

anyone know?

[brazen charm]

you know you're almost correct

but wdym by if filter?

[errant herald]

if _ in _

the query

sorry, should have elaborated

[brazen charm]

you mean like SELECT * FROM table WHERE column In values?

[errant herald]

yes

but column and values are switched

you get what im trying to achieve?

with them as column In values i can have more leniency with searching through the data

[tired relic]

How do you create a new table after db.create_all() command in flask-sqlalchemy?

[brazen charm]

@errant herald yeah but in sql you do column in values

not values in columns

its a lil weird ik but you'll get used to it

[errant herald]

yeah...

but can i get around that somehow?

there must be a way

[brazen charm]

i mean no cuz you cant change the syntax

thats just how the query lang works

[errant herald]

no i mean not change the syntax laws but achieve the same outcome via another method

[brazen charm]

you can do column IN (v1, v2) AND column2 IN (v3, v4)

you cant get around doing column first tho

[errant herald]

wait, will that do it?

column IN (v1, v2) AND column2 IN (v3, v4)

what do you mean by that

noo dont dissapear lol

[runic pilot]

are you trying to make some text search features?

[errant herald]

yes

[runic pilot]

are you using postgres?

if so, here's a great place to look for doing more advanced text search queries https://www.postgresql.org/docs/9.5/textsearch.html

[errant herald]

ok

i am using sqlite though

[tepid cradle]

The best way to handle this kind of data is to just store them as key-value pairs. create one column for key and another for value.
All you need to do to find a specific key is select * from table where key = something
@torn sphinx

[stuck sonnet]

Looking for a way to get a subscription based website that allows subscribers to connect to a database in order to look at data that we would be making/providing. Is there any web frameworks or things I should look into?

[errant herald]

how do i do a %?% in sqlite's execute function?

like:

cursor.execute("select content from table where content like %?%", (searchquery) #searchquery is always a string

how can i do that in correct syntax?

please ping me if i dont respond

[queen saffron]

anyone here familiar with datatables https://datatables.net/ ?

[high geyser]

                c.execute("""SELECT authorid
                            FROM alias_storage
                            WHERE guildid=?,alias_name=?""",(ctx.guild.id,alias_name))``` while trying to execute this I got the following error. How to fix this? pls help me
```py
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near ",": syntax error```

nvm all I had to do was replace comma with ADD

[jovial notch]

Why don't id integer get's generated?

[tepid cradle]

@torn sphinx read my message once again. Let me know if you still have a doubt

@jovial notch you need to define it as a serial column

[jovial notch]

Oh that's why, why after I set anything as serial it gets renamed to integer @tepid cradle?

[tepid cradle]

Oh that's why, why after I set anything as serial it gets renamed to integer @tepid cradle?
@jovial notch that's normal. It will create a sequence and apply that as the default value for that column

But then, it should work. Has always worked for me.
Are you putting any value in your insert query for id? Show the insert query

@errant herald try using %% instead of just %

[errant herald]

ok

blackcursor.execute("select contents from black where contents LIKE ?", ('%%'+cardsearchquery+'%%'))

that turned up an error, when cardsearchquery is 'ha'

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

@tepid cradle

[proven arrow]

Try convert the arguments to a tuple

[errant herald]

by?

thanks so much for helping haha

(cardsearchquery,)?

[proven arrow]

Add a comma after the argument

Yes

[errant herald]

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

(╯°□°）╯︵ ┻━┻

cardsearchquery = '%% '+cardsearchquery+' %%'
blackcursor.execute("select contents from black where contents LIKE '?'", (cardsearchquery,))```

[proven arrow]

Try with a single %

[errant herald]

ok

[pseudo cove]

no

Ignore luft

drop the single quotes around the qmark

[errant herald]

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

ok

[proven arrow]

They never had that before anyways

[pseudo cove]

actually you might need to follow what luft said

[errant herald]

ok, it doesnt error

but it doesnt return anything either

the contents column contains 'ham'

and h, ha, ham doesnt return it

[pseudo cove]

There's spaces in your query

[errant herald]

remove spaces and double %?

[pseudo cove]

you want "%"+cardsearchquery+"%"

[errant herald]

OH MY FORGING GORGE IT WORKS

i have spent hours on this thank you so much @pseudo cove and @proven arrow

[high geyser]

I have a doubt. Like when I use a discord command, I begin a transaction on a database. If certain reactions are added, I ether rollback or commit the transaction.
However, when I use another command that needs access to the same database, the transaction becomes committed, whether the reaction is added or not. How can I prevent this? I am using sqlite3

[clear reef]

How would I make it so that my code registers words in guildId and then takes action?

[minor pewter]

your "no idea what im doing here" is selecting everything from words where words = word and guildid = ctx.guild.id

[clear reef]

yes okay so im doing it correct

so im getting the words

i think?

[minor pewter]

in your on_message get the word for the guild then compare message.content to the word

word = await db.execute(...
if word in message.content:
  # do something```

[clear reef]

thanks friend

[glass gorge]

im curious, anyone have experience running postgres on a mac

and if so, how's that different from running it on linux

I ask because I'm working a flask app, and I'm going to use postgress once I deploy. But I want to get it working locally first

[cinder jasper]

I don't know if this is the right channel to help, but I had a question on the best approach. I'm using python to pull some web data into a sqlite3 database. When i do a subsequent check, I only want to insert new records, not ones that exist. What is the most efficient way to accomplish this? Should I use python and pull a list from the db and compare to trim down the list to insert? I wasn't sure if there ways a way to do like an "INSERT IF NEW RECORD" type thing maybe using SQL? Any help or just pointing me to a topic is appreciated. (using python3 on a raspberry pi fyi)

[mild arrow]

Hey everyone, is it possible to host a database and access it through a tkinter app?

[opaque crystal]

@cinder jasper do you have any unique identifier?

if so, I would just do a SELECT towards that identifier and if it returns a row, move to the next

if not, INSERT

[cinder jasper]

that's a good approach, thanks @opaque crystal - I was thinking of making a list and then comparing but that seems like it would be the most simple apparoch

[opaque crystal]

Not sure if it is resource optimized

but as long as you are not expecting 12000 read/writes per instant I guess you'll be fine ^^

[cinder jasper]

oh, not, not even close

like 100 records tops

[opaque crystal]

yeah, should not take too long then

especially since I guess you have the database locally on the pi

[cinder jasper]

eventually i want to push this from sqlite3 to mysql on my webserver - but i want to make sure i can get it working first

[opaque crystal]

Alright, getting rid of the sqlite completely or having it as a middle man?

if as a middle man you could keep it as "the truth" and bulk upload the updates to the mysql online

if getting rid of the sqlite I guess the only latency you'll add is the request to the remote server which could increase the load times slightly

I think I would remain in the same logic though

I work with case management systems (business side though, not dev) and I think that is how we do, check for a specific number, if it is present we just update/skip

one check per request

[cinder jasper]

i was thinking of the middle man and then figure out a way to sync it with the online database

[opaque crystal]

Yeah, that could be a good thing I believe

[mild arrow]

Hey everyone, is it possible to host a database and access it through a tkinter app?

[opaque crystal]

keeping the checks local and then pushing whatever is new in one go

@mild arrow https://www.geeksforgeeks.org/create-mysql-database-login-page-in-python-using-tkinter/ looks like it

[mild arrow]

Does anyone know how to use flask to store a database used in a tkinter app\

[pseudo cove]

flask isn't a db thing

@mild arrow

what do you want to do

and why can't you store the db locally?

[mild arrow]

I have a tkinter app . I want to host the database so a small amount of people can use the app to add and view the database like i can currently when it is stored locally

@pseudo cove is this possible?

[digital current]

does anyone know how do i make a input field ? for example i want people to type their name after !name so it goes to the database~~

[opaque crystal]

@digital current is this still tkinter?

[torn sphinx]

how can i make a discord.py command that allows a channel to spam

with aiosql

[delicate field]

Hey @torn sphinx!

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

[torn sphinx]

https://paste.pythondiscord.com/ujusibupik.py

like a command !allowchannel #mybypassedChannel

but it saves to the db

so when i load up the bot the channel is still bypassed

and like if the channel is in the db then allow invites and allow spam

and if you want you can see my anti-spam/anti-link right here
https://paste.pythondiscord.com/ujusibupik.py

[rustic umbra]

HALP ME
I don't know what its referring to ..

what key specification?

I'm using MySQL and I'm workin on the MySQL Workbench..

Please, Ping me when you have the solution.

[torn sphinx]

Any idea how to decompress a 120gb sql file when I have 4gb on my PC?

[torn sphinx]

i think i have done it !

@client.event
async def on_guild_join(guild):
    owner = guild.owner
    guildid = guild.id
    await client.change_presence(activity=discord.Activity(type=discord.ActivityType.watching, name=f"{len(client.guilds)} Guilds | And Links!"))
    embed=discord.Embed(color=0x12a8f3)
    embed.add_field(name="Thanks for inviting me!", value="Thanks for inviting me! \nHere are some things you need to do first `.setup` please set me up first!, keep in mind I need permissions for this! \nThanks! make sure to use `.help` to see my commands!", inline=False)
    embed.set_footer(text="Made by: ImBugleLOL#8849 | Today at %s:%s CDT" % (now.hour, now.minute))
    await owner.send(f"{owner.mention}")
    await owner.send(embed=embed)
    conn = await aiosqlite.connect('servers.db')
    await conn.execute("INSERT INTO servers VALUES (?)", (guildid))
    await conn.commit()
    await conn.close()
``` this sould work right
for saving a guild in my db

[torn sphinx]

@commands.command()
    @commands.check(commands.dm_only())
    async def authtoken(self, ctx, token):
        if "'" in token:
            await ctx.send("Hmm... SQLi?")
        elif "-" in token:
            await ctx.send("Hmm... SQLi?")
        else:
            cursor.execute(f"SELECT * FROM users WHERE user_token = '{token}'")
            account = cursor.fetchone()
            acc = account.json()
            await ctx.send("Başarılı! Kullanıcı Adınınız: `" + acc['username'] + "` Olarak Kaydedildi!" )

i want to make a system to connect website accounts with discord accounts

[brave bridge]

@torn sphinx First of all, your way of preventing sql injection doesn't really work, and it may miss some corner cases. All relational DB adapters in Python support safe substitution:

# no!
cursor.execute(f"SELECT * FROM users WHERE user_token = '{token}'")

# yes!
# (the specific syntax may depend on the specific library, so check the library docs)
cursor.execute(f"SELECT * FROM users WHERE user_token = ?", (token,))

Besides that, what issues are you having?

[torn sphinx]

it said that cannot convert tuple to json but i will try

jsonStr = json.dumps(mytuple)

this thing

now

[brave bridge]

can you give more context, i.e. more code and the error message? Surely tuples are json serializable

!e

import json
print(json.dumps((1, 2, 3)))

[delicate field]

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

[1, 2, 3]

[torn sphinx]

oh

thanks

[acoustic ledge]

!e

[delicate field]

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

[torn sphinx]

Error While Using Load Command: Command raised an exception: TypeError: tuple indices must be integers or slices, not str

um

bruh

[brave bridge]

can you show the entire traceback?

It's probably in the acc['username'] part -- print out acc

[torn sphinx]

i already tried that

i did [1] instead of 'username' and it worked

[brave bridge]

well, acc must be a tuple then

[low iron]

I want to learn how to use Postgresql with asyncpg but I don't understand what to use as @host when connecting to the database

[tepid cradle]

@rustic umbra try using varchar instead of text. I'm not entirely sure, but it's giving an error because it doesn't allow text column as primary key

@low iron host is the address of the system where the database is hosted. If it's on your own machine, you can use http://localhost or http://127.0.0.1

Where is your database?

[low iron]

Oh
On my machine
Thanks

Also is there a way to delete rows by ID?

[tepid cradle]

ID is the name of a column?

@low iron

[low iron]

No, but in sqlite3 there were row IDs
aren't there row IDs in postgres?

[tepid cradle]

Internal IDs are there, but you can't use those. If you want to use row id, create an id column.
Also, I doubt what you're saying about SQLITE. Most likely you used an ORM like sqlalchemy which internally added an id column.

[low iron]

Oh okay, thanks

There are rowid tables that have row ids in sqlite iirc
https://sqlite.org/rowidtable.html

[tepid cradle]

Ok, I was not aware of this. Thanks.
PostgreSQL might have something similar, but I've never seen anyone use it in queries

@low iron postgresql calls it ctid and it is of the form of a tuple. You can use it queries if you want, like this
select ctid, * from table where ctid = '(0,10)'

[rustic umbra]

@tepid cradle Yeah I already had figured it out, but still thanks.

Also this so helpfull otherwise it would have token me ages to just CODE all this

[tepid cradle]

What application is this?

[low iron]

@tepid cradle Ah okay thank you

[tepid cradle]

@rustic umbra ?

[quaint tiger]

MySQL workbench, I think

[low iron]

Another question, is it possible to store dicts in postgres?

[quaint tiger]

Another question, is it possible to store dicts in postgres?
@low iron https://www.postgresql.org/docs/12/datatype-json.html serialize them to json

Should be as simple as json.loads and json.dumps

[low iron]

I want to make a discord bot and json is blocking, I'm avoiding using json
If I have to write/read json why use a database in the first place

Thanks anyway, I'll find another solution

[quaint tiger]

https://magicstack.github.io/asyncpg/current/usage.html#example-automatic-json-conversion

[inland wraith]

is json counted here

[brazen charm]

It shouldn't be but people treat it like it is

[twilit marlin]

json is underrated

[brazen charm]

Json is overated

Overrated and misused by many

[twilit marlin]

https://tenor.com/view/sad-cry-crying-kitten-sad-cat-gif-16988132

[torn sphinx]

iam in need of help regarding

sqlite database

i am fetching the data but unable to process the code

[jade topaz]

SO i've got a question i'm not sure if this is the right place to be asking this - I've got a bunch of markdown files and I'd like to build a sort of "back-of-book" style index for all of them. is there a database that's good for that sort of thing?

if there are any flat file solutions (like sqlite) suitable for this sort of application i'd be interested in hearing about them

[cinder jasper]

sqlite database
i am fetching the data but unable to process the code
@torn sphinx
So you can query the datbase - do you get anything back? I just did my first script for sqlite3 and it seems to have worked

[glass gorge]

i downloaded postgres on my laptop

but idk where it actually loaded to, and I need to change the db uri in my flask app

[minor venture]

In my database i have a column whit a character type
i get a list with all of the values in that column and store it in a variable called db_user_id why wont this if statement work?

    if str(ctx.author.id) in db_user_id:
        print("Already used command")

The id's match?

plz ping me

my guess is it is a data type issue but it could be something else

[torn sphinx]

@minor venture

[minor venture]

yes?

[torn sphinx]

you want to match the id right

[minor venture]

yes if the ids are the same i want it to print already used command

[torn sphinx]

which database you are using

and can you show full code

[minor venture]

postgres

cur.execute("SELECT user_id from used_command")
    db_user_id = cur.fetchall()
    print(db_user_id)
    print(ctx.author.id)
    if int(ctx.author.id) in db_user_id:
        print("Already used command")
        return

[torn sphinx]

use *from

[minor venture]

in the SELCET statement?

[torn sphinx]

yes

[minor venture]

Command raised an exception: SyntaxError: syntax error at or near "from"
LINE 1: SELECT user_id *from used_command

[torn sphinx]

and then

 
for ctx.author.id in db_user_id:
     print("Already use command")

[minor venture]

Command raised an exception: AttributeError: can't set attribute

@torn sphinx

[tepid cradle]

Don't use select * from ..., use cursor.fetchone() instead

@minor venture fetchall() gives you a list of tuples, so if you search it for a string, then you'll never get true. fetchone() will return one row

[minor venture]

ok thank you

[tepid cradle]

If your query is supposed to return multiple rows, then you'll have to flatten it and check
if int(ctx.author.id) in [i[0] for i in db_user_id]:
this is one way of doing that

[minor venture]

is there a better way to find if an item is in a column?

like fetchone(123445325) returns T/F

[tepid cradle]

Yes

cur.execute("select count(*) from table where user_id = ?", (ctx.user.id,))
is_present = cur.fetchone()[0]

[minor venture]

thank you i will try that in a bit

[tepid cradle]

You basically count the number of times that value is present in a column. If it is more than 0, then it exists

[minor venture]

im getting an syntex error at the ?

@tepid cradle

what should i put there?

[tepid cradle]

Psycopg2?

[minor venture]

yes

[tepid cradle]

put %s instead of ?

@minor venture https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries

[minor venture]

thank you

sorry one more thing @tepid cradle
Command raised an exception: UndefinedFunction: operator does not exist: character = bigint

how can i fix that?

[harsh pulsar]

@minor venture you are comparing text to a number

this is typical when comparing user id's

can you share your query and the surrounding code?

ah i see

cur.execute("select count(*) from table where user_id = ?", (ctx.user.id,))

yeah, like this

can you share your table schema?

it looks like user_id is probably text, while discord user ids are integers

the easiest thing to do is

cur.execute("select count(*) from table where user_id = ?", (str(ctx.user.id),))

but the actual correct solution will depend on your particular case

[minor venture]

Column is character with limit of 18

[tepid cradle]

Then the above solution will work.

[minor venture]

ok thanks!

[tepid cradle]

I mean Salt Rock Lamp's solution

[minor venture]

first time working with DB really

[harsh pulsar]

databases can be hard, if you make a mistake it's hard to change a table after it's had data written to it

you would have to create a new column then delete the old column... its a mess

[minor venture]

yeah nothing in production so i just truncate the table when i mess something up

[torn sphinx]

str not need

cur.execute("select * from table where user_id =?",(ctx.user.id,))```

[harsh pulsar]

@torn sphinx it is needed. their user_id has a text data type whereas ctx.user.id is an integer.

[torn sphinx]

yes it works

[unreal slate]

does heroku support sqlitee database?

[tepid cradle]

@unreal slate It doesn't. It provides postgresql database which you can use

[unreal slate]

ok

[uneven smelt]

@harsh pulsar Or use transactions.

[harsh pulsar]

@uneven smelt i was more talking about the case where maybe you used the wrong schema

or realized later that your database tables are poorly designed

not just if you wrote the wrong row or something

[uneven smelt]

oh yes, that's another topic

[limber trail]

Hello folks, I have a folder with files names as P1 to P100 and V1 to V100, I want to loop over every single pair (i.e (P2, V2) and perform a particular block of code. this one does it but I get some pairs twice. Can you please help?

    for i in range(100):
        for file in os.listdir(directory):
            if file.startswith("P"+ str(i+1)):
                result1 = file
            if file.startswith("V" + str(i+1)):
                result2 = file
        f.write(result1 +', '+ result2 +'\n')
f.close()```

[harsh pulsar]

@limber trail this isn't really a database question. i recommend you ask this in a general help channel: see #❓｜how-to-get-help

[digital current]

does anyone has any idea why this isnt working?

pls someone i need it for an hour from now

[harsh pulsar]

is this homework? @digital current

[digital current]

kinda

[harsh pulsar]

the error message is telling you that collection is a str object, which obviously has no insert_one method

it looks like the config file here does not have the format your code expects it to have

[digital current]

this is the config

[brazen charm]

you know you dont actually connect at all to mongo

[digital current]

yea i just figured it

damn i was hoping it'd work

does anyone know how to fix it

[brazen charm]

yeah, connect to it in the file

[digital current]

but i need to get it from a settings

.ini

[brazen charm]

you cant 🤔 Unless ini has some magical method of running python instead of just being static data

pretty sure this homework instructions are telling you what you should do in your python file and just has it in the settings

[digital current]

here, this is what connects it right?

[harsh pulsar]

not in your ini file...

that's python code

[digital current]

i know

i just want the value of it

to get that from a .ini

[brazen charm]

well it'll be a string

you should defo go through https://www.w3schools.com/python/python_mongodb_getstarted.asp guide for pymongo it takes about 5 mins to read and will clean alot of this up for you

[torn sphinx]

Any good pymongo tutorials I can watch/read?

besides official docs

[digital current]

i just want a value from a .ini file to be placed there. is it possible?

[brazen charm]

@torn sphinx the link i just sent

[torn sphinx]

oh wow didn't see that lol

Thanks

[brazen charm]

@digital current you had it in your original code of getting things from it

[digital current]

yea but it wasn't working probably because of syntaxes

on a print i can get a value

how do i define that?

i just tried this and it gives me a "none"

[brazen charm]

well remove the [] from it

its parsing them as strings

[digital current]

got it to work thx @brazen charm

[rustic umbra]

@tepid cradle Its Workbench.

[harsh pulsar]

2 TB is overkill

as long as it's 1 row per user, that data will probably fit into a few GB

[torn sphinx]

#help-chocolate
Help if you know sqlite

[wind sand]

hello, for asyncpg I am trying to increment an number by 1 from an row I am fetching if one of my if statements is true, and if it is, update the database. However, I keep getting the error TypeError: 'asyncpg.Record' object does not support item assignment when trying to do so. What can I do to fix this?

[tepid cradle]

@torn sphinx here are two tables I have in my own project's database and their sizes to give you an idea. 2 TB is approximately 30,000 times of what you'll actually need.
Table1 - 230,000 rows x 10 columns : 41 MB
Table2 - 11.3 million rows x 4 columns : 1.5 GB

[golden shadow]

I have the following code to grab a list of 6 posts from my table posts:

latest_posts = Posts.query.limit(6)

However that code ^ grabs the first 6 posts on the database not the last 6 posts, I have implemented something similar for getting 6 latest posts of the current logged in user:

curr_user = Users.query.filter_by(username=current_user.username).first()
    curr_user_posts = curr_user.created_posts[-6:]

But I can't just implement the same thing with Posts table, I also don't want to just grab all posts and then slice them up, I just want to grab the 6 latest posts from posts table.

[wind sand]

anyone??

[wind sand]

nvm, figured it out

[tepid cradle]

@golden shadow you need to order it by date descending and then use limit 6.
And if this is a common enough usecase, which it seems like it would be, you can add an index on the date column in descending order. That will speed up the fetching significantly

Unfortunately, I can't answer this in terms of an ORM, can guide you only with raw SQL

[torn sphinx]

i am making products page for my website

this is my products table

and I made a system like this

@app.route("/products", methods=["GET"])
def products():
    cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute(f"SELECT * FROM products")
    results = cursor.fetchall()
    resultjson = json.dumps(results)
    return resultjson

it gives this kind of result

i want to scrape

id name price or other things from json

i am little bit new to sql/json stuff

[trail steppe]

Where is ur front-end

[torn sphinx]

bruh

i commented

it out

return render_template("products.html")

@trail steppe

[trail steppe]

Yes but where it is

Its not executing ur page its just display raw data

[torn sphinx]

i know

i want to scrape raw data

for example

how can i get names from there

the raw data

the tuple

thats what i am asking

[proven arrow]

fetchall() will return all your rows, which you currently are doing and storing in results
You can iterate over these rows and get the item you want.

As an example,

for row in results:
  print(row[0])

[torn sphinx]

oh

[trail steppe]

But it will not fetch in that page@proven arrow

[proven arrow]

That's not what they asked. They gave no details of such implementation.

[trail steppe]

@proven arrow yes thats what im talking

[proven arrow]

But they can pass this data to their view if that's what you mean.

[trail steppe]

Yaa right

I think using jinja

[surreal charm]

with pydantic,

class Company(Base):
   id = Column(Integer, primary_key=True)
   name: Column(String)
   desc: Column(String)

class CompanySchema(BaseModel):
   name: Optional[str]
   desc: Optional[str]

def update_company(company_id, db: Session, input: CompanySchema):
   company = db.query(Company).filter_by(id==company_id).one()
   db.query(Company).filter_by(id==company_id).update(input)
   db.commit()
   db.refresh(company)
   return company

Paraphrasing a bit but the above example is using pydantic and sqlalchemy -- is there a better way to write that update function?

1. it makes two database calls
2. because it's a pydantic model, with optional name and desc -- if you dont supply a name/desc, it will set the value to None -- how to only update arbitrary what is provided via input: CompanySchema

[golden shadow]

thanks @tepid cradle

[glad hare]

OK, so I wanna learn databases

Is there any YouTube channel that teaches it?

[craggy girder]

how do i delete a record from a sql database but instead of the oid i want to do it with the content of the table?

[wind sand]

oh yeah, for some reason my asyncpg would stop processing requests from time to time, then I would have to restart my bot and I am not sure why thats happening

does anyone know what could cause such a thing?

[proven arrow]

Maybe you are closing the connection somewhere

[hidden nest]

Hey everybody. I got a little trouble with an INSERT into my database.
When I try just the query with fixed numbers it works with

INSERT INTO favorite(source_product_id, replacement_product_id)  VALUES (189499,189446)

But when I try the query in my python script using psycopg2

     cursor.execute("INSERT INTO favorite(source_product_id, replacement_product_id) VALUES (%s, %s)",(product["id"], sub))

I get an error : TypeError: 'int' object is not subscriptable
Here is what I don't get. I checked my variable I am trying to insert by type and getting the correct ones :

189499
<class 'int'>
189446
<class 'int'>

[proven arrow]

@hidden nest Because you cant index integers, like that.
This is what you are trying to do, which will give an error:

x = 5423
print(x["id"])

[hoary sonnet]

@hidden nest try using str () function

str() to convert data to str and int() to convert data to int

[wind sand]

hmmm. maybe

tho, my connection pool only closes at the end of each event listener or command. This is for discord.py btw

[proven arrow]

Are you using the pool with an async with block?

If so it should release the connection to the pool itself with that, so you dont need to.

[hidden nest]

thx @hoary sonnet and @proven arrow 😉 was my bad I used the wrong variable. My correction one was already an INT
Just for info it's working good now like this :

        cursor.execute("INSERT INTO favorite(source_product_id, replacement_product_id)  VALUES (%s, %s)", (product, sub))
        self.conn.commit()
        cursor.close()

[crude root]

How would I use a database to save and interact with data.. im making a python game and want to save stuff like player level, and inventory but be able to edit and read it

[grim lotus]

Depends on what db you using

[craggy girder]

i wanted to ask how can i edit my main database if i have already created it?
like i want to add one more row to my table called email in here-

conn.execute("""CREATE TABLE usernames (
            user_name text,
            pass_word text)""")```

[grim lotus]

That's called alter table

I guess

Read about ALTER TABLE on the internet it may be helpful

[keen mauve]

hi

anyone familiar with pgadmin

ive created a backup of my db "dump.sql"

how can i restore that using pgadmin?

[pulsar kestrel]

would be cool if someone can help me

[proven arrow]

Your missing a comma at the end of the second to last line @pulsar kestrel

[pulsar kestrel]

tyy

[little flume]

how do i use use parametrized queries in mysql

[little flume]

hello?

[torn sphinx]

hi

i want to add

some rows to my table

like

i want to add

account_created_at

referral_link

and that stuff

[little flume]

how do i use use parametrized queries in mysql.

[proven arrow]

@torn sphinx You can use Alter Table to add a new column.
ALTER TABLE table_name ADD column_name datatype;
For example,
ALTER TABLE users ADD created_at TIMESTAMP;
https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

@little flume You can use a placeholder for the value, and then give the parameters as a separate argument.
c.execute("SELECT * FROM some_table WHERE some_column = %s AND other_column = %s", (param1, param2))

But the placeholder you use may differ between the library you use.

[little flume]

lemme try

what would you put in other_column? @proven arrow

[proven arrow]

A column name, but that's just an example if you want to filter by multiple columns.

The key thing here is that we replace the value from the query with a placeholder, which we provide as the arguments later.

[little flume]

i dont need the other_column paramater

[proven arrow]

Do you understand SQL query structure?

[little flume]

wdym

nvm

yea i understand @proven arrow

[proven arrow]

So then you should know that other_column in that example can just be removed

[little flume]

Ok

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: InternalError: Unread result found

@proven arrow

i get that error

[proven arrow]

Can you show the code?

[little flume]

ye

cashamountdownload = MySQLConnection.mycursor.execute("SELECT cash_amount FROM Money WHERE moneyuser = %s", (str(usercash)))

[pliant nexus]

[str(usercash)] like i said in the other channel!!

[little flume]

now i get

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: unsupported operand type(s) for +: 'NoneType' and 'int'

[pliant nexus]

what is the full traceback

[little flume]

lemme check

Ignoring exception in command addcash:
Traceback (most recent call last):
  File "C:\Users\FrenchFries8854\AppData\Roaming\Python\Python38\site-packages\discord\ext\commands\core.py", line 83, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:/Users/FrenchFries8854/Documents/Coding/Python/Discord BOTS/EarthCraft-BOT/bot-main.py", line 1185, in addcash
    MySQLConnection.mycursor.execute("UPDATE Money SET cash_amount = %s WHERE moneyuser = %s", (cashamountdownload + amountcashadd, str(usercash)))
TypeError: unsupported operand type(s) for +: 'NoneType' and 'int'
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "C:\Users\FrenchFries8854\AppData\Roaming\Python\Python38\site-packages\discord\ext\commands\bot.py", line 892, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\FrenchFries8854\AppData\Roaming\Python\Python38\site-packages\discord\ext\commands\core.py", line 797, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\FrenchFries8854\AppData\Roaming\Python\Python38\site-packages\discord\ext\commands\core.py", line 92, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: unsupported operand type(s) for +: 'NoneType' and 'int'```

[pliant nexus]

ok, well, the error isn't on that line

[little flume]

where is the error?

1185?

[pliant nexus]

the exception tells you it's because you did cashamountdownload + amountcashadd and cashamountdownload is None and amountcashadd is an int

[proven arrow]

Did you fetch the result from the cursor execute?

[little flume]

lemme checkl

[proven arrow]

Cursor.execute creates the cursor object, not fetches the result.

[little flume]

ah

i dont think i am fetching result

how do i do that

@proven arrow

[proven arrow]

cursor = c.execute(...)
result = cursor.fetchone()```

There is also fetchall() and fetchmany()

[little flume]

cursor = MySQLConnection.mycursor.execute("SELECT cash_amount FROM Money WHERE moneyuser = %s", [str(usercash)])
result = cursor.fetchone()

???

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'NoneType' object has no attribute 'fetchone'

i get that

@proven arrow

@pliant nexus

??

[pliant nexus]

@little flume drop the cursor = on that line

[proven arrow]

Also why the list for the arguments. Should be a tuple.

[pliant nexus]

doesn't matter really

any iterable, and a single-element list is easier to make

[charred zephyr]

Looking at this guide for a CRUD app using psycopg2, I'm wondering if it's really necessary to define the connection details in each CRUD method...

https://kb.objectrocket.com/postgresql/postgresql-crud-example-in-python-with-psycopg2-part-2-1141

There is a summary in Just the Code

[proven arrow]

Generally you want only a single connection or a connection pool across your application. I wouldn't recommend creating a new connection each time the function is run because that can be expensive.

[charred zephyr]

Do you have an example of this using psycopg2?

[proven arrow]

Ive never used it, but you can take a look at the docs. Just initiate the pool class.
https://www.psycopg.org/docs/pool.html

[torn sphinx]

hey guys, i have a weird issue with my table.
it just keeps row id incrementing even if i delete all records from table. how can this be resolved?

[proven arrow]

How do you mean it keeps incrementing?