#databases | Python | Page 100

brazen charm Jul 23, 2020, 6:36 PM

#

(query, val1, val2, val3, val4)

#

you only give it a tuple if you're using execute many or bulk insertions

#

btw you might also get the issue with the WHERE message in ($1, $2, $3, $4) as iirc that method got depreciated so now it checks an Array rather than a listed set

harsh pulsar Jul 23, 2020, 6:38 PM

#

yep try await conn.execute(query, *message_ids), for the first issue

#

what do you mean by that @brazen charm ?

#

the deprecated thing

brazen charm Jul 23, 2020, 6:39 PM

#

Its something i remember when ive been building my asyncpg orm

#

i had an issue doing if x in {set of stuff here}

#

after some googling it got deprecated in postgre (apparently) to now be x in array

torn sphinx Jul 23, 2020, 6:43 PM

#

Ok so that is working now when i unpack for running query

#

should i worry about what you say CF8?

brazen charm Jul 23, 2020, 6:43 PM

#

if it works it works ig

torn sphinx Jul 23, 2020, 6:43 PM

#

Yes it works 🙂

brazen charm Jul 23, 2020, 6:43 PM

#

what version of postgre r u on?

harsh pulsar Jul 23, 2020, 6:43 PM

#

oh, this isn't an array literal though so it's fine right? @brazen charm

brazen charm Jul 23, 2020, 6:43 PM

#

oh yeah it might be tbh

harsh pulsar Jul 23, 2020, 6:44 PM

#

i'd be very shocked if they deprecated IN (a, b)

brazen charm Jul 23, 2020, 6:44 PM

#

ah no ik what it was

#

it was if you insert a array with a single place holder

torn sphinx Jul 23, 2020, 6:45 PM

#

PostgreSQL 12.3, on windows but on the VPS it is 10.12

brazen charm Jul 23, 2020, 6:45 PM

#

yeah dw about it

torn sphinx Jul 23, 2020, 6:45 PM

#

ok thanks, and thanks for all help

brazen charm Jul 23, 2020, 6:45 PM

#

i was thinking of inserting a array with a place holder 🙂

harsh pulsar Jul 23, 2020, 6:46 PM

#

ah thats actually annoying. asyncpg won't translate a list or tuple to an array?

#

that would be a killer feature for a sql library tbh. auto generating all those damn placeholders

#

either generating an array literal or just a (a, b, ...) sequence

brazen charm Jul 23, 2020, 6:46 PM

#

tbf ive actually made a converter for that

#

https://github.com/Project-Dream-Weaver/SQLX/blob/master/sqlx/converters.py

GitHub

Project-Dream-Weaver/SQLX

MongoDB style PostgreSQL ORM, using non-blocking async and await methods for python. Designed to make queries quick and efficent with asyncpg. - Project-Dream-Weaver/SQLX

#

tho i use it for my orm

harsh pulsar Jul 23, 2020, 6:46 PM

#

you mean something less ugly than query = query_template.format(', '.join(f'${m}' for m in range(len(message_ids))))?

torn sphinx Jul 23, 2020, 6:46 PM

#

does asyncpg or postgres handle like they call sql injection or we must still do?

harsh pulsar Jul 23, 2020, 6:47 PM

#

yes that's the point of using the query placeholders

#

it prevents sql injection

#

well... helps prevent

brazen charm Jul 23, 2020, 6:47 PM

#

unless your psycopg2

harsh pulsar Jul 23, 2020, 6:47 PM

#

you can still cause it with bad database design

#

i just wish that asyncpg would spin off their binary postgres protocol parser

#

so someone can write a non-async version

#

i wonder if magicstack ever actually released that software product they were developing all this code to support

brazen charm Jul 23, 2020, 6:48 PM

#

they were doing it for a end software?

harsh pulsar Jul 23, 2020, 6:48 PM

#

maybe that was the uvloop event loop replacement

brazen charm Jul 23, 2020, 6:49 PM

#

hmm idk

harsh pulsar Jul 23, 2020, 6:49 PM

#

idk how they're even a company, i guess they do consulting

#

and just build these tools on the side

#

EdgeDB

#

that's the one

#

looks like it's out https://edgedb.com/

brazen charm Jul 23, 2020, 6:50 PM

#

i mean tbf the stuff they've done has made async python considerably better

harsh pulsar Jul 23, 2020, 6:50 PM

#

yeah they've made a huuge contribution

brazen charm Jul 23, 2020, 6:50 PM

#

oh that looks fucking coool

harsh pulsar Jul 23, 2020, 6:50 PM

#

im pretty grateful that their management supports FOSS software like this

#

its one thing coming from google and facebook, another coming from a small shop that has every incentive to keep this stuff private

#

but it's made magicstack an almost household name among python devs

#

here i am literally giving them free advertising

brazen charm Jul 23, 2020, 6:51 PM

#

tbf i have no issue supporting companies like that

#

if we didnt have asyncpg or uvloop async would be no ware near as supported overall i think

harsh pulsar Jul 23, 2020, 6:53 PM

#

precisely

brazen charm Jul 23, 2020, 6:53 PM

#

idk if you've looked at that edge db

#

but damnnnnnnnn

#

its like a freaking OOP based SQL db

harsh pulsar Jul 23, 2020, 6:55 PM

#

interesting

torn sphinx Jul 23, 2020, 7:00 PM

#

if i have 3 columns like

#

ID, date_a, date_b

#

how can i select all the rows containing only the values where datea and date_b both have values less than 2019-01-01 or have null values

harsh pulsar Jul 23, 2020, 7:03 PM

#

WHERE
    (date_a IS NULL OR date_a < '2019-01-01') AND
    (date_a IS NULL OR date_b < '2019-01-01')

#

something like that?

opaque pumice Jul 23, 2020, 7:37 PM

#

how do i use an sqlite database on a vps

harsh pulsar Jul 23, 2020, 7:38 PM

#

same as any other machine

#

a sqlite database is just a file

opaque pumice Jul 23, 2020, 7:41 PM

#

oh

#

is it possible to import an existing database to a vps, and if so how would i go about doing it

remote willow Jul 23, 2020, 7:43 PM

#

Seems like you do it the same way you would move any other file to the VPS. FTP?

torn sphinx Jul 23, 2020, 7:45 PM

#

I have an sqlite3 databse that isn't updating the values. I am making a currency system for a discord bot for anyone wondering

opaque pumice Jul 23, 2020, 7:45 PM

#

@torn sphinx did u do db.commit()?

torn sphinx Jul 23, 2020, 7:45 PM

#

    @commands.command()
    async def buy(self, ctx, *, item):
        old_bal = self.bal._get_bal()
        if item == "Burger":
            new_bal = old_bal - 2
            self.bal.update_value(column='bal', value=new_bal)
            await ctx.send("$2 has been subtracted from your account.")
    
    @commands.command()
    async def work(self, ctx):
        bal = self.bal._get_bal()
        new_bal = bal + 50
        self.bal.update_value(column="bal", value = new_bal)
        await ctx.send("You gained $50!")
        print(bal)
        print(new_bal)

    @commands.command()
    async def bal(self, ctx):
        bal = self.bal._get_bal()
        await ctx.send(f"You have ${bal} in your account")
        print(bal)

#

        query = f"SELECT * FROM {TABLE} WHERE id = ? AND uid = ?"
        self.cursor.execute(query, (self.guild.id, self.user))
        info = self.cursor.fetchall()
        if info:
            self.bal = info[0][2]
            return self.bal
        else:
            self._create_user()
            self._get_bal()

    def _create_user(self):
        try:
            query = f"""INSERT INTO {TABLE} VALUES (?, ?, ?)"""
            self.cursor.execute(query, (self.guild.id, self.user, 0))
            self.conn.commit()
        except sqlite3.Error:
            pass
    
    def update_value(self, column, value):
        query = f"UPDATE {TABLE} SET {column} = ? WHERE id = ?"
        self.cursor.execute(query, (f"{value}", self.user))
        self.conn.commit()
        self._get_user_info()```

#

@torn sphinx did u do db.commit()?
@opaque pumice yes

opaque pumice Jul 23, 2020, 7:45 PM

#

i had the same problem yesterday but it somehow works today

torn sphinx Jul 23, 2020, 7:46 PM

#

def _create_table(self):
        query = f"""CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, uid INT, bal INT)"""
        self.cursor.execute(query)
        self.conn.commit()

#

It works on my other databases

#

like the other files I have

#

Yeah I can't seem to understand whats going on

#

more rather what the issue is

torn sphinx Jul 23, 2020, 8:20 PM

#

hi
does any one know how i can find the master user and password of a database in AWS?

runic pilot Jul 23, 2020, 8:28 PM

#

you mean an RDS database?

torn sphinx Jul 23, 2020, 8:30 PM

#

yes

#

sorry for omitting that

runic pilot Jul 23, 2020, 8:31 PM

#

those were inputs when the RDS instance was created

#

no worries

torn sphinx Jul 23, 2020, 8:31 PM

#

yes they were

runic pilot Jul 23, 2020, 8:31 PM

#

I think you can get the master username, but the master password is always hidden after creation

torn sphinx Jul 23, 2020, 8:31 PM

#

but i d like to know how to find them in case i forget

#

i found that i can change the master password

#

but i dont know about the user

wooden wraith Jul 23, 2020, 11:33 PM

#

Can someone tell me how to make a connection from MySql database to vscode

runic pilot Jul 23, 2020, 11:33 PM

#

you want to connect a text editor to a database?

#

let's back up a step- what are you trying to do?

wooden wraith Jul 24, 2020, 12:03 AM

#

I am trying to learn how to use MySql

#

Because I need it to continue my course

stable pilot Jul 24, 2020, 1:58 AM

#

hey guys i am trying to migrate my db from sqlite3 to postgres, and am using docker pgloader, im using this command docker run --rm --name pgloader -v /home/ubuntu/Apex-Website/ecommerce/sqlite3.db:/etc/ dimitri/pgloader:latest pgloader ./etc/sqlite3.db postgresql:///newdb

and i get this error ```2020-07-24T01:49:10.028000Z LOG pgloader version "3.6.3~devel"
KABOOM!
FATAL error: Failed to connect to pgsql at :UNIX (port 5432): The value of CL-POSTGRES::USER is NIL, which is not of type STRING.
An unhandled error condition has been signalled:
Failed to connect to pgsql at :UNIX (port 5432): The value of CL-POSTGRES::USER is NIL, which is not of type STRING.

What I am doing here?

Failed to connect to pgsql at :UNIX (port 5432): The value of CL-POSTGRES::USER is NIL, which is not of type STRING.```

#

what to do

tepid cradle Jul 24, 2020, 2:38 AM

#

Can someone tell me how to make a connection from MySql database to vscode
@wooden wraith you can't connect mysql to vs code. You need to use something like mysql workbench to access the database

gloomy pike Jul 24, 2020, 4:23 AM

#

await db.execute("Update ProtectedTags set Uses = Uses + 1 where GuildID = ? and Tag = ?", (ctx.guild.id, tag))
await db.commit()```
two errors happen randomly sometimes:
sqlite3.DatabaseError: cannot start a transaction within a transaction
cannot commit - no transaction is active
weird because I have this exact code in my other cog only with the column name being different and i've never seen the error appear once there...

#

it still works sometimes so idk what's going on

vale lodge Jul 24, 2020, 4:29 AM

#

<@&267628507062992896> He's going through all the channels. What is up tonight...

acoustic silo Jul 24, 2020, 4:29 AM

#

sigh

#

!tempban 726017978130497556 14d You've only joined here to spam your YouTube channel across all our channels. You should've read our rules when joining; we do not allow this here.

delicate fieldBOT Jul 24, 2020, 4:31 AM

#

:incoming_envelope: :ok_hand: applied ban to @shell citrus until 2020-08-07 04:31 (13 days and 23 hours).

acoustic silo Jul 24, 2020, 4:31 AM

#

cleanup is always no fun

vale lodge Jul 24, 2020, 4:31 AM

#

Once again, thanks for all you folks do.

gloomy pike Jul 24, 2020, 4:36 AM

#

await db.execute("Update ProtectedTags set Uses = Uses + 1 where GuildID = ? and Tag = ?", (ctx.guild.id, tag))
await db.commit()```
two errors happen randomly sometimes:
sqlite3.DatabaseError: cannot start a transaction within a transaction
cannot commit - no transaction is active
weird because I have this exact code in my other cog only with the column name being different and i've never seen the error appear once there...

keen mauve Jul 24, 2020, 9:00 AM

#

hello
can someone help me setup postgres db on the host instance
and connect it with pgadmin4

dapper tide Jul 24, 2020, 9:51 AM

#

does anyone know where i can find the gigaword database?

torn sphinx Jul 24, 2020, 12:53 PM

#

I've got this huge query

            SELECT * FROM (
                SELECT 
                    emotes.emote as emote, 
                    sum(emotes.count) as message_count, 
                    sum(reactions.count) as reaction_count, 
                    sum(emotes.count) + sum(reactions.count) as total_count 
                FROM emotes 
                INNER JOIN reactions 
                    ON (emotes.emote = reactions.emote 
                        AND emotes.period = reactions.period 
                        AND emotes.guild_id = reactions.guild_id) 
                WHERE (emotes.guild_id=%s OR reactions.guild_id=%s) 
                UNION ALL 
                    SELECT 
                        emotes.emote as emote, 
                        sum(emotes.count) as message_count, 
                        0 as reaction_count, 
                        sum(emotes.count) as total_count
                    FROM emotes 
                    LEFT JOIN reactions 
                    WHERE (reactions.emote IS NULL AND reactions.guild_id=%s) 
                UNION ALL 
                    SELECT 
                        reactions.emote as emote, 
                        0 as message_count, 
                        sum(reactions.count) as reaction_count, 
                        sum(reactions.count) as total_count 
                    FROM reactions 
                    LEFT JOIN emotes 
                        WHERE (emotes.emote IS NULL AND emotes.guild_id=%s)) 
            GROUP BY emote 
            ORDER BY total_count DESC```
And I get this error `ERROR:  syntax error at or near "WHERE" at character 994`
994 is on the line `WHERE (reactions.emote IS NULL AND reactions.guild_id=%s)`
What am I missing?

#

Tbh at this point I just think about creating another table and duplicating the data, but it will at least allow for easier querying

#

Ping me if you can help, I might forget I asked here

harsh pulsar Jul 24, 2020, 1:08 PM

#

@torn sphinx you are using query parameters for this?

#

as in, you are not using % interpolation in python, right?

torn sphinx Jul 24, 2020, 1:09 PM

#

Yes, sure

harsh pulsar Jul 24, 2020, 1:09 PM

#

ok good

#

i guess now it's a matter of just debugging

#

consider using sqlfiddle to test and debug

torn sphinx Jul 24, 2020, 1:10 PM

#

The first part before union worked separately btw

#

Nice tool, I'll try it a bit later

harsh pulsar Jul 24, 2020, 1:10 PM

#

your 2nd and 3rd queries in the union

#

you forgot to use ON

#

you typically can't use JOIN without ON from what i remember

torn sphinx Jul 24, 2020, 1:11 PM

#

Ah, right, that may be the problem

harsh pulsar Jul 24, 2020, 1:11 PM

#

you can write FROM reactions, emotes WHERE ... instead

#

but that's an inner join

#

left and right joins, i definitely recommend ON

torn sphinx Jul 24, 2020, 1:12 PM

#

Can't check right now, but I'll try when I get home, thanks

harsh pulsar Jul 24, 2020, 1:13 PM

#

also using table aliases is typically recommended when possible

#

oh nvm you're using tbl.col

#

that's fine

#

yeah you just need ONs

torn sphinx Jul 24, 2020, 1:16 PM

#

Yeah, probably

#

It's the biggest query I've ever wrote

whole mica Jul 24, 2020, 1:46 PM

#

i am using mysql
how can i connect python code from other computers to the database in localhost?

#

or should i make a database which is not stored in localhost

whole mica Jul 24, 2020, 2:06 PM

#

plz ping me when reply thx a lot

torn sphinx Jul 24, 2020, 3:54 PM

#

sqlite3 database not updating for some reason, no error given. ```py
@commands.command()
async def buy(self, ctx, *, item):
old_bal = self.bal._get_bal()
if item == "Burger":
new_bal = old_bal - 2
self.bal.update_value(column='bal', value=new_bal)
await ctx.send("$2 has been subtracted from your account.")

@commands.command()
async def work(self, ctx):
    bal = self.bal._get_bal()
    new_bal = bal + 50
    self.bal.update_value(column="bal", value = new_bal)
    await ctx.send("You gained $50!")
    print(bal)
    print(new_bal)

@commands.command()
async def bal(self, ctx):
    bal = self.bal._get_bal()
    await ctx.send(f"You have ${bal} in your account")
    print(bal)

Database file:py
def _get_bal(self):
query = f"SELECT * FROM {TABLE} WHERE id = ? AND uid = ?"
self.cursor.execute(query, (self.guild.id, self.user))
info = self.cursor.fetchall()
if info:
self.bal = info[0][2]
return self.bal
else:
self._create_user()
self._get_bal()

def update_value(self, column, value):
    query = f"UPDATE {TABLE} SET {column} = ? WHERE id = ?"
    self.cursor.execute(query, (f"{value}", self.user))
    self.conn.commit()
    self._get_user_info()
    self._get_bal()```

harsh pulsar Jul 24, 2020, 4:08 PM

#

@torn sphinx i was going to say that maybe you didn't commit... but you did

torn sphinx Jul 24, 2020, 4:08 PM

#

Yeah

#

Its really weird

#

I have other databases and they work just fine

harsh pulsar Jul 24, 2020, 4:08 PM

#

if it helps, f"{value}" should just be value or str(value)

torn sphinx Jul 24, 2020, 4:08 PM

#

Everything that is needed is defined

#

yeah

#

I should do that

#

ohhhh

#

I think that actually might fix it

#

cause the number is an int, and that was a str

harsh pulsar Jul 24, 2020, 4:09 PM

#

ah

torn sphinx Jul 24, 2020, 4:10 PM

#

well

harsh pulsar Jul 24, 2020, 4:10 PM

#

so it's possible the error is happening but you're suppressing it somehow

torn sphinx Jul 24, 2020, 4:10 PM

#

it didn't

#

It didn't work, but there still isn't any error

harsh pulsar Jul 24, 2020, 4:10 PM

#

oh

#

uh

#

don't re-use the cursor

#

re-use the connection

#

1 cursor per query

#

banish any thought of self.cursor

torn sphinx Jul 24, 2020, 4:10 PM

#

ok

#

and replacne with self.conn?

harsh pulsar Jul 24, 2020, 4:10 PM

#

yes

torn sphinx Jul 24, 2020, 4:10 PM

#

replace*

harsh pulsar Jul 24, 2020, 4:11 PM

#

at least for inserts/updates

torn sphinx Jul 24, 2020, 4:11 PM

#

self.conn.execute(query, (value, self.user))

#

?

harsh pulsar Jul 24, 2020, 4:11 PM

#

yes

#

for select, conn.execute will return a cursor

#

well, conn.execute always returns a cursor

torn sphinx Jul 24, 2020, 4:11 PM

#

oh ok

harsh pulsar Jul 24, 2020, 4:11 PM

#

but you only need to actually use that cursor for select queries

#

note that this is sqlite3 behavior and some other database libs require you to manually create a cursor

#

but even then, the same rule applies: 1 cursor per query

#

do not share cursors

torn sphinx Jul 24, 2020, 4:12 PM

#

alright

whole mica Jul 24, 2020, 4:12 PM

#

i am using mysql
how can i connect python code from other computers to the database in localhost?

torn sphinx Jul 24, 2020, 4:13 PM

#

@harsh pulsar it is returning a recursion error, meaning that the maximum recursion depth was reached

north bramble Jul 24, 2020, 4:14 PM

#

@torn sphinx I always us the ON clause in all my joins and the results ar different depending on the order of the joins. Here are some of my typical join clauses that are in some of my simpler queries. Not knowing your DB structure I can't make a solution from your data but you can get the idea from these I hope.

LEFT JOIN sheep_table AS sire_table ON sheep_table.sire_id = sire_table.sheep_id
LEFT JOIN sheep_table AS dam_table ON sheep_table.dam_id = dam_table.sheep_id
LEFT OUTER JOIN sheep_cluster_table ON sheep_table.sheep_id = sheep_cluster_table.sheep_id
LEFT JOIN cluster_table ON cluster_table.id_clusternameid = sheep_cluster_table.id_clusterid

torn sphinx Jul 24, 2020, 4:15 PM

#

I've got to it and this is my query now:
https://pastebin.com/ecc4bH4p
BUT for some reason it returns numbers that are way more that they actually are. What's even worse, it only gives me the inner joined part

Pastebin

Long query - Pastebin.com

Pastebin.com is the number one paste tool since 2002. Pastebin is a website where you can store text online for a set period of time.

#

Shoulda used pastebin

north bramble Jul 24, 2020, 4:17 PM

#

@torn sphinx There's missing ) somewhere in the SELECT sections. Not sure where as I don't quite see what you are trying here's a query of mine that has lots of SELECT clauses

https://pastebin.com/z9hAdtX3

Pastebin

Long SQLite Query - Pastebin.com

Pastebin.com is the number one paste tool since 2002. Pastebin is a website where you can store text online for a set period of time.

harsh pulsar Jul 24, 2020, 4:17 PM

#

@torn sphinx show your code

torn sphinx Jul 24, 2020, 4:17 PM

#

Like there's no way that kekw was used 300+ times for today
https://i.imgur.com/RLL7FfL.png

Imgur

#

    def update_value(self, column, value):
        query = f"UPDATE {TABLE} SET {column} = ? WHERE id = ?"
        self.conn.execute(query, (value, self.user))
        self.conn.commit()
        self._get_bal()

    def _get_bal(self):
        query = f"SELECT * FROM {TABLE} WHERE id = ? AND u_id = ?"
        self.conn.execute(query, (self.guild.id, self.user))
        info = self.cursor.fetchall()
        if info:
            self.bal = info[0][2]
            return self.bal
        else:
            self._create_user()
            self._get_bal()

#

shoot, wrong order

#

its flipped around

harsh pulsar Jul 24, 2020, 4:38 PM

#

@torn sphinx get rid of self.cursor entirely

#

never write self.cursor ever

#

it is cursed

#

an angry snake will visit you at night and hiss at you until you stop it

#

        cursor = self.conn.execute(query, (self.guild.id, self.user))
        info = cursor.fetchall()

do this

gloomy pike Jul 24, 2020, 5:25 PM

#

await db.execute("Update Blacklist values set Channels = ? where GuildID = ?",
                                 (json.dumps([]), ctx.guild.id))
await db.commit()```
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: SystemError: <sqlite3.Connection object at 0x0000021599A88C60> returned NULL without setting an error

#

anyone know what that error means?

opaque finch Jul 24, 2020, 5:39 PM

#

I want to convert a Date column to string inside a dataframe. the date column is dd-mm-yyyy. I want to convert it to ddmmyyy

worldly lotus Jul 24, 2020, 5:41 PM

#

maybe

#

is the date column it's own object

#

or is it a string

#

cause it its a string, you can just do string.replace('-', '')

opaque finch Jul 24, 2020, 5:47 PM

#

is a datetime64 object

worldly lotus Jul 24, 2020, 5:48 PM

#

just get the str() value of it

#

that may work

#

im not very familiar with datetime64

#

and then use the str functions to modify it

#

just dir('')

#

to see everything you can do with it

ashen kettle Jul 24, 2020, 7:08 PM

#

hey

#

i having and error

#

with my sql database

urban smelt Jul 24, 2020, 7:56 PM

#

📎 unknown.png

#

📎 unknown.png

#

i specify that the table is called Users, not users, in every line
where did i made a mistake?

#

📎 unknown.png

#

📎 unknown.png

runic pilot Jul 24, 2020, 8:04 PM

#

what's the full stacktrace say?

urban smelt Jul 24, 2020, 8:05 PM

#

📎 unknown.png

#

(rui.py is my run file)

#

@runic pilot ^^

runic pilot Jul 24, 2020, 8:07 PM

#

what's line 142

#

no need to tag me, I'll respond if I'm here

urban smelt Jul 24, 2020, 8:07 PM

#

ok im sorry

#

one sec

#

📎 unknown.png

#

it's my error handler

runic pilot Jul 24, 2020, 8:10 PM

#

just before you raise try doing this import traceback; traceback.print_exc()

#

to get more info

urban smelt Jul 24, 2020, 8:15 PM

#

📎 unknown.png

#

like this?

runic pilot Jul 24, 2020, 8:15 PM

#

yeah

urban smelt Jul 24, 2020, 8:15 PM

#

ok

runic pilot Jul 24, 2020, 8:16 PM

#

why are you raising an error in an else block?

#

what's the full code surrounding it?

urban smelt Jul 24, 2020, 8:16 PM

#

📎 unknown.png

#

and btw it gave the same thing

#

📎 unknown.png

runic pilot Jul 24, 2020, 8:21 PM

#

you should see 2 stack traces separated by The above exception was the direct cause of the following exception:

#

do you see that?

urban smelt Jul 24, 2020, 8:22 PM

#

omg im extremely stupid

#

📎 unknown.png

runic pilot Jul 24, 2020, 8:23 PM

#

All identifiers (including column names) that are not double-quoted are folded to lower case in PostgreSQL

#

(https://stackoverflow.com/a/20880247/8015233)

Stack Overflow

Are PostgreSQL column names case-sensitive?

I have a db table say, persons in Postgres handed down by another team that has a column name say, "first_Name". Now am trying to use PG commander to query this table on this column-name.

select *...

urban smelt Jul 24, 2020, 8:26 PM

#

hm

#

but

#

the database's name is Users

#

does that not matter?

runic pilot Jul 24, 2020, 8:27 PM

#

try "Users" and not Users

urban smelt Jul 24, 2020, 8:27 PM

#

ok

lime echo Jul 24, 2020, 8:30 PM

#

Hello, I store a datetime.datetime object in my database (as BLOB) but when I retrieve it, it becomes a str instead of a datetime.datetime object.

#

How to solve this?

urban smelt Jul 24, 2020, 8:33 PM

#

alright so now it works, but other problem showed up

#

📎 unknown.png

#

📎 unknown.png

#

it doesn't save

#

rdbaker are you there?

lime echo Jul 24, 2020, 8:43 PM

#

where is the code of you saving it? @urban smelt

urban smelt Jul 24, 2020, 8:44 PM

#

📎 unknown.png

#

@lime echo ^^

proven arrow Jul 24, 2020, 8:45 PM

#

self.client.execute?

#

Isnt that your bot instance?

lime echo Jul 24, 2020, 8:46 PM

#

You should be using your cursor there, not your bot instance.

urban smelt Jul 24, 2020, 8:46 PM

#

oh

lime echo Jul 24, 2020, 8:47 PM

#

@proven arrow do you know how to store a datetime object in a database?

proven arrow Jul 24, 2020, 8:47 PM

#

Which database?

urban smelt Jul 24, 2020, 8:47 PM

#

@lime echo self.client.pg_con.execute?

lime echo Jul 24, 2020, 8:49 PM

#

@proven arrow sqlite3.

#

@urban smelt send me the code where pg_con is defined.

urban smelt Jul 24, 2020, 8:50 PM

#

i...haven't defined it

lime echo Jul 24, 2020, 8:50 PM

#

@urban smelt normally, you should connect your database, did you do that?

urban smelt Jul 24, 2020, 8:50 PM

#

yeah i did

copper echo Jul 24, 2020, 8:51 PM

#

    await conn.executescript("""
        CREATE TABLE IF NOT EXISTS guilds (
            guild_id BIGINT NOT NULL UNIQUE,
            auto_role BIGINT NULL,
            mute_role BIGINT NULL,
            logs JSON DEFAULT '{}'
        ); 
        CREATE TABLE IF NOT EXISTS giveaways (
            description TEXT,
            winners_count INT,
            creator BIGINT,
            message_id BIGINT,
            channel_id BIGINT,
            emoji TEXT,
            ending_time TIMESTAMP
        ); 
        CREATE TABLE IF NOT EXISTS giveaways_ended (
            description TEXT,
            ending_time INT,
            winners_count INT,
            creator BIGINT,
            message_id BIGINT,
            channel_id BIGINT,
            emoji TEXT,
            winners BIGINT[] DEFAULT '{}'
        );
        """)``` For some reasons im getting `sqlite3.OperationalError: near ")": syntax error` i dont know if thats a problem with sqlite because syntax looks correct for me

lime echo Jul 24, 2020, 8:51 PM

#

@urban smelt send me the line of that code.

urban smelt Jul 24, 2020, 8:51 PM

#

📎 unknown.png

#

nvm

#

i did define pg_con

lime echo Jul 24, 2020, 8:52 PM

#

Perfect, what are you using btw @urban smelt

#

as a query language

urban smelt Jul 24, 2020, 8:52 PM

#

PostgreSQL

#

wait what

lime echo Jul 24, 2020, 8:52 PM

#

@copper echo ); should never be inside quotation.

#

Yeah postgreSQL that's the answer @urban smelt

urban smelt Jul 24, 2020, 8:53 PM

#

ok

#

yeah so?

#

why it doesn't work

#

should i sent the whole code? @lime echo

copper echo Jul 24, 2020, 8:53 PM

#

so what should i do if i want to create multiple tables at once

proven arrow Jul 24, 2020, 8:54 PM

#

@lime echo Yes you can store the datetime, be sure to pick the right data type

lime echo Jul 24, 2020, 8:55 PM

#

@urban smelt I don't know much about PostgreSQL, but normally, after you open your database, you need to create a cursor c=db.cursor() for instance, then use that cursor when you execute your database.

urban smelt Jul 24, 2020, 8:55 PM

#

oh

copper echo Jul 24, 2020, 8:55 PM

#

in asyncpg you dont have to

lime echo Jul 24, 2020, 8:55 PM

#

@proven arrow I picked BOB but it stores it as str.

copper echo Jul 24, 2020, 8:55 PM

#

im using it

proven arrow Jul 24, 2020, 8:56 PM

#

Oh well then you can still convert the string into datetime

copper echo Jul 24, 2020, 8:56 PM

#

@urban smeltyou were right here https://canary.discordapp.com/channels/267624335836053506/342318764227821568/736323794943213679

lime echo Jul 24, 2020, 8:57 PM

#

Yeah that's smart, I will try it now.

#

canary.discordapp.com

#

wtf is that

proven arrow Jul 24, 2020, 8:57 PM

#

You probably want to use datetime.strptime as well to convert it

copper echo Jul 24, 2020, 8:57 PM

#

@lime echo so what should i do to create multiple tables with one script

#

its something like beta discord

lime echo Jul 24, 2020, 8:58 PM

#

@copper echo try ditching all the ;

urban smelt Jul 24, 2020, 8:58 PM

#

📎 unknown.png

#

@copper echo where have i made an error

copper echo Jul 24, 2020, 8:58 PM

#

show me error from terminal lol

urban smelt Jul 24, 2020, 8:59 PM

#

i dont get one

lime echo Jul 24, 2020, 8:59 PM

#

@proven arrow that converts it from datetime to str. I want the opposite.

copper echo Jul 24, 2020, 8:59 PM

#

so its correct ¯_(ツ)_/¯

#

or you have error handler

urban smelt Jul 24, 2020, 8:59 PM

#

📎 unknown.png

#

it doesn't save

#

i do have error handler

copper echo Jul 24, 2020, 8:59 PM

#

so dont load it

urban smelt Jul 24, 2020, 9:00 PM

#

but i added
else:
raise error

proven arrow Jul 24, 2020, 9:00 PM

#

What? no, its str to datetime. https://docs.python.org/3/library/datetime.html#strftime-strptime-behavior @lime echo

copper echo Jul 24, 2020, 9:00 PM

#

still disable it for a second

urban smelt Jul 24, 2020, 9:00 PM

#

ok

proven arrow Jul 24, 2020, 9:01 PM

#

You are using pools correct? @urban smelt

urban smelt Jul 24, 2020, 9:01 PM

#

yes

copper echo Jul 24, 2020, 9:01 PM

#

ALSO Kranthos thank you

#

it worked

proven arrow Jul 24, 2020, 9:01 PM

#

Did you get a connection from pool first?

#

async with pool.acquire() as con:
    await con.execute(...)

con = await pool.acquire()
try:
    await con.execute(...)
finally:
    await pool.release(con)

async with pool.acquire() as con:
  async with con.transaction():
    await con.execute(...)

From the docs you can use like this.

#

https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.pool.Pool
https://magicstack.github.io/asyncpg/current/api/index.html#connection-pools

torn sphinx Jul 24, 2020, 10:26 PM

#

When I run this code, I don't get any errors for around 5-10 seconds until nothing sends and i get the following errors. I'm using mongoDB and all other commands owrk

@client.command()
async def points(ctx):
    memberid = ctx.author.id
    result = collection.find_one({"id":memberid})
    await ctx.send(f'Points: {result["points"]}')

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ServerSelectionTimeoutError: 713373939472269322:27017: [Errno 11001] getaddrinfo failed

gloomy pike Jul 24, 2020, 10:35 PM

#

@tasks.loop(seconds=30)
    async def check_reminders(self):
        cursor = await db.execute("Select MemberID, Reminder from Reminders where Time <= ?", (time.time(),))
        result = await cursor.fetchall()

        for tup in result:
            try:
                member = self.bot.get_user(tup[0]) or await self.bot.fetch_user(tup[0])
                await member.send(embed=to_embed(tup[1], info=True))

            except:  # Member deleted account, member doesn't share server with bot etc.
                pass

        await db.execute("Delete from Reminders where Time <= ?", (time.time(),))
        await db.commit()```

#

sqlite3.OperationalError: cannot start a transaction within a transaction
last 2 lines, although i thought including the task was helpful as well

sterile raft Jul 24, 2020, 11:38 PM

#

#help-avocado

whole mica Jul 25, 2020, 12:32 AM

#

how to connect a localhost database from another computer?
i am using mysql

sinful condor Jul 25, 2020, 12:36 AM

#

can someone help me with getting sqlite into my project

normal igloo Jul 25, 2020, 1:41 AM

#

@whole mica another computer in the same network?

whole mica Jul 25, 2020, 1:41 AM

#

uhh no

#

im making a game
and it will be run in different computers

#

i want them to connect to the database and get data

#

or save

sinful condor Jul 25, 2020, 1:48 AM

#

this is sort of a database thing as well as a discord.py but I think it is more a database thing I am using this code to store stuff in sqlite py @setup.command() async def autorole(ctx, arole:discord.Role): if ctx.message.author.guild_permissions.manage_messages: db = sqlite3.connect('main.sqlite') cursor = db.cursor() cursor.execute(f"SELECT autorole FROM main WHERE guild_id = {ctx.guild.id}") result = cursor.fetchone() if result is None: sql = ("INSERT INTO main(guild_id, autorole) VALUES(?,?)") val = (ctx.guild.id, arole) await ctx.send(f"Channel has been set to {arole.mention}") elif result is not None: sql = ("UPDATE main SET autorole = ? WHERE guild_id = ?") val = (arole, ctx.guild.id) await ctx.send(f"Welcome Channel has been updated to {arole.mention}") cursor.execute(sql, val) db.commit() cursor.close() db.close() all of the other stuff works right just not this one but I am getting this error ``` File "bot.py", line 359, in autorole
cursor.execute(sql, val)
sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.

normal igloo Jul 25, 2020, 1:51 AM

#

@whole mica generally SQL is never public facing. You want a server that talks to the SQL server for you

#

With public facing SQL any random person can get all the info in the database

whole mica Jul 25, 2020, 1:53 AM

#

ooh ic thx
so how can i make a server that talks to my sql server?

normal igloo Jul 25, 2020, 1:53 AM

#

Well I assume you have the SQL bit down already from your current program

#

https://realpython.com/python-sockets/

Socket Programming in Python (Guide) – Real Python

In this in-depth tutorial you'll learn how to build a socket server and client with Python. By the end of this tutorial, you'll understand how to use the main functions and methods in Python's socket module to write your own networked client-server applications.

#

Here is an intro to networking in python

whole mica Jul 25, 2020, 1:54 AM

#

ooooh ok thx a lot

normal igloo Jul 25, 2020, 1:55 AM

#

It is a bit of a big subject, so definitely don't be afraid to ask questions in #networks

whole mica Jul 25, 2020, 1:57 AM

#

kk thx

bright arrow Jul 25, 2020, 2:18 AM

#

x_value_temperature = df_temperature.resample('M', on='DATE').mean()

#

after i did this

#

x_value_temperature becomes a weird dataframe

#

i cant get rid of the DATE column

#

temperature_2019 = pd.read_csv('C:/Users/dotha/PythonNotebook/File/temperature (2019) NYC.csv')
temperature_2020 = pd.read_csv('C:/Users/dotha/PythonNotebook/File/temperature (2020) NYC.csv')
list_of_temperature_2019 = temperature_2019['Temperature'].tolist()
list_of_temperature_2020 = temperature_2020['Temperature'].tolist()

temperature =[]
for i in range (len(list_of_temperature_2019)):
    variable = ((list_of_temperature_2020[i]-list_of_temperature_2019[i])/list_of_temperature_2019[i])*100
    temperature.append(variable)

temperature = pd.Series(temperature, name = "Average Temperature")
df_temperature = pd.concat([temperature_2019['DATE'],temperature], axis=1)

df_temperature['DATE'] = df_temperature['DATE'].astype('datetime64[ns]')
df_temperature = df_temperature[df_temperature['DATE'].dt.month != 6]

x_value_temperature = df_temperature.resample('M', on='DATE').mean()
#x_value_temperature = x_value_temperature.drop(columns="DATE")
x_value_temperature['Average Temperature']

#

📎 unknown.png

tepid cradle Jul 25, 2020, 2:56 AM

#

You can vectorize your for loop. It's cleaner and much faster.
like this:

list_of_temperature_2019 = temperature_2019['Temperature'].values
list_of_temperature_2020 = temperature_2020['Temperature'].values
temperature = ((list_of_temperature_2020-list_of_temperature_2019)/list_of_temperature_2019)*100

#

.values basically converts it to Numpy array, which supports vectorized (element-wise) calculations like these.

bright arrow Jul 25, 2020, 2:58 AM

#

sp temperature will be a list?

#

btw this should be in data sci instead

#

sorry

tepid cradle Jul 25, 2020, 3:00 AM

#

yes, it will be a list. The result will be identical to your for loop. You should try it out, it's much more efficient than a for loop and you would want to learn vectorized operations like these to make your code run faster

bright arrow Jul 25, 2020, 3:01 AM

#

wow saved me a lot of troubles

#

thx

tepid cradle Jul 25, 2020, 3:02 AM

#

Now back to your original question, what is the problem you're facing?

bright arrow Jul 25, 2020, 3:03 AM

#

the 'DATE' column wont drop

tepid cradle Jul 25, 2020, 3:04 AM

#

I think if you just convert it to a list, it will go away. The date in the output is not a column, it's just the index for the series. The output is a series, not a dataframe

bright arrow Jul 25, 2020, 3:06 AM

#

hmm if that is the case i can convert it to a df then

tepid cradle Jul 25, 2020, 3:07 AM

#

Do you want a dataframe?

#

because then all you have to do is write x_value_temperature[['Average Temperature']] instead of x_value_temperature['Average Temperature']
Double box brackets

bright arrow Jul 25, 2020, 3:08 AM

#

to be honest, i am very lost right now

#

i am trying to follow

#

"The more 'scientific method' would be to do a regression where the y-variable is PM2.5 (and try the daily %diff of PM2.5 as well just to see what happens) and x-variable is something independent of covid but a good predictor of PM2.5 levels, like weather (temperature/rainfall). You may need to add a dummy variable (a binary variable) for weekend when less industrial activity means less pollution usually. You wouldn't need to go all the way back to 2010 for this but maybe 2018 would be good (try it out with the data you have so far first if you want). Note that when you do a regression you need to train the regression model with data before quarantine starts, then use that model to predict what things would have looked like had quarantine not happened against our actual observations (where quarantine did happen)."

#

btw do you know how to drop 59th element in the list_of_temperature_2020?

#

your way

tepid cradle Jul 25, 2020, 3:10 AM

#

Claim a help channel and ping me from there. This is giong quite off-topic for this room

#

Just copy paste your question there

bright arrow Jul 25, 2020, 3:11 AM

#

lmao i didnt know there is help channel. how do i claim a channel hmm

tepid cradle Jul 25, 2020, 3:12 AM

#

Go to the help available sections, there will be two channels there. Take any one of them. Refer #❓｜how-to-get-help

urban smelt Jul 25, 2020, 6:42 AM

#

Did you get a connection from pool first?
@proven arrow I used this to connect to it

📎 unknown.png

teal sail Jul 25, 2020, 8:08 AM

#

Should i learn sql or nosql

tepid cradle Jul 25, 2020, 8:47 AM

#

You'll eventually have to learn both, most likely. But I'd say start with SQL.
Mostly because there's no single nosql language. Each nosql db has its own query language. Whereas most parts of SQL are fairly standardised for the commonly used databases like MySQL, PostgreSQL, MS SQL, SQLite, etc. Only when you reach intermediate to advanced stuff, will these start to vary somewhat. Basically when you're dealing with Db specific implementation of a feature.
SQL is useful and easy to learn.

teal sail Jul 25, 2020, 8:48 AM

#

So i am good to go with mysql as a beginner?

tepid cradle Jul 25, 2020, 9:42 AM

#

yeah, MySQL is good for starting out with. You can install the database on your local machine and use mysql workbench or php mysql or something like that

#

Linux or Windows?

teal sail Jul 25, 2020, 10:15 AM

#

Windows

tepid cradle Jul 25, 2020, 1:38 PM

#

You can install MariaDB (an open source implementation of MySQL) and Dbeaver community edition. It's a bit of an advanced Db management tool, but you should be able to use it

sinful condor Jul 25, 2020, 1:47 PM

#

can someone help me possibly in dms about how to convert my discord level system from saving stuff in json to sqlite, or should I just ask here?

teal sail Jul 25, 2020, 1:49 PM

#

@tepid cradle can u recommend mysql tutorials

tepid cradle Jul 25, 2020, 1:50 PM

#

Let me look up

#

Give this a try https://www.tutorialrepublic.com/sql-tutorial/

SQL Tutorial - An Ultimate Guide for Beginners

SQL Tutorials for Beginners - Learn the concepts of world's most popular query language for databases in simple and easy steps with lots of live examples.

#

You can also ask for help here if you are stuck with anything specific

#

can someone help me possibly in dms about how to convert my discord level system from saving stuff in json to sqlite, or should I just ask here?
@sinful condor Is your JSON nested?

sinful condor Jul 25, 2020, 1:55 PM

#

nested?

#

@tepid cradle

#

heres what my json looks like {"449756487510654988/721149838573699154": {"xp": 112, "level": 4, "warnings": 0}, "469228384321536011/732567342525186111": {"xp": 30, "level": 3, "warnings": 1}, "603611557162647552/721149838573699154": {"xp": 5, "level": 1, "warnings": 0}, "663117661139632148/721149838573699154": {"xp": 61, "level": 3, "warnings": 0}, "449756487510654988/732567342525186111": {"xp": 53, "level": 3, "warnings": 0}, "530818947227058186/732567342525186111": {"xp": 0, "level": 1, "warnings": 0}, "599632471038099496/732567342525186111": {"xp": 1, "level": 1, "warnings": 0}, "482154322084233217/732567342525186111": {"xp": 0, "level": 1, "warnings": 0}}

#

does that help

tepid cradle Jul 25, 2020, 1:58 PM

#

Yeah, this is nested. As in, the values of the top level keys are objects, not just strings or ints

#

I asked because if it was not nested, it's very easy to convert it to tables. I don't really know how to convert this. Or at least, not well enough to explain it to someone else

sinful condor Jul 25, 2020, 1:59 PM

#

so do i basically have to rewrite all of the code to get it into sqlite?

tepid cradle Jul 25, 2020, 2:06 PM

#

No, only the parts which interact with your JSON file

sinful condor Jul 25, 2020, 2:06 PM

#

which in this is basically everything

#

I se maybe 10 lines out of 100 that dont interact with a json file

tepid cradle Jul 25, 2020, 2:07 PM

#

What does your program do?

sinful condor Jul 25, 2020, 2:07 PM

#

its a discord bot

#

its a level system for a discord bot so everything needs to be stored

#

every message it gets activated

#

and this is my code ```@client.event
async def on_message(message):
db = sqlite3.connect('main.sqlite')
cursor = db.cursor()
cursor.execute(f"SELECT prefix FROM main WHERE guild_id = {ctx.guild.id}")
result = cursor.fetchone()

await update_data(users, message.author)
await add_xp(users, message.author, 1)
await level_up(users, message.author, message.channel)

cursor.execute(sql, val)
db.commit()
cursor.close()
db.close()

async def update_data(users, user):
userguild = f'{user.id}/{user.guild.id}'
if user == client.user or str(userguild) in users:
return
else:
users[str(userguild)] = {}
users[str(userguild)]["xp"] = 0
users[str(userguild)]["level"] = 1
users[str(userguild)]["warnings"] = 0

async def add_xp(users, user, exp):
if user == client.user:
return
else:
userguild = f'{user.id}/{user.guild.id}'
users[str(userguild)]["xp"] += exp

async def level_up(users, user, channel):
if user == client.user:
return
else:
userguild = f'{user.id}/{user.guild.id}'
xp = users[str(userguild)]["xp"]
lvl_start = users[str(userguild)]["level"]
lvl_end = int(xp ** (1/3))

if lvl_start < lvl_end:
    userguild = f'{user.id}/{user.guild.id}'
    await channel.send(f"{user.mention} has leveled up to level {lvl_end}")
    users[str(userguild)]["level"] = lvl_end

``` basically everything interacts with a json file in some way or another

#

the top section is the one I am working on

tepid cradle Jul 25, 2020, 2:09 PM

#

OK. If you're not familiar with SQL, you can explore other options. You have nosql databases like MongoDb where you can directly store the JSON. Or you can go for SQLAlchemy ORM where you can build models and write SQLAlchemy commands instead of writing SQL queries directly. But all of them will require you to change pretty much all the lines

#

If you already know SQL, then IMO writing plain old SQL queries is the easiest

sinful condor Jul 25, 2020, 2:10 PM

#

ok

tepid cradle Jul 25, 2020, 2:11 PM

#

Because of its age and maturity, there's also plethora of help content available for SQL, which doesn't seem to be the case for MongoDb.

torn sphinx Jul 25, 2020, 2:34 PM

#

How would I make this a parametrized query?

#

curs.execute(f"SELECT value FROM prices WHERE name = "{asset}" AND stamp >= {fromstamp} AND stamp <= {tostamp}'")

tepid cradle Jul 25, 2020, 2:37 PM

#

which database? The syntax varies slightly depending on that

brazen charm Jul 25, 2020, 2:38 PM

#

bad f strings

#

and your error is the " are ending the strings

tepid cradle Jul 25, 2020, 2:38 PM

#

curs.execute("SELECT value FROM prices WHERE name = %s AND stamp >= %s AND stamp <= %s", (asset, fromstamp, tostamp))
This would be the general syntax

brazen charm Jul 25, 2020, 2:38 PM

#

you also open a ' but then dont close it

torn sphinx Jul 25, 2020, 2:39 PM

#

thanks gkrou

#

that worked

tepid cradle Jul 25, 2020, 2:39 PM

#

👍

#

In general, don't use quotes inside your query, parameterization takes care of it wherever needed

torn sphinx Jul 25, 2020, 2:42 PM

#

yes, i just read this before but it didnt really make sense

#

now it does

#

https://stackoverflow.com/questions/11160637/sqlite3-operationalerror-unrecognized-token-01t00-python-datestamp

Stack Overflow

sqlite3.OperationalError: unrecognized token: "01T00" Python datestamp

I'm experiencing a problem inserting values into a SQLite database. The data I download from the Norwegian Parliament site data.stortinget.no. The error I get is: sqlite3.OperationalError: unrecogn...

sinful condor Jul 25, 2020, 2:44 PM

#

hey @tepid cradle I am almost done at least trying to switch it over to sqlite here is one section but I have a question ```py
async def add_xp(users, user, exp):
db = sqlite3.connect('main.sqlite')
cursor = db.cursor()
cursor.execute(f"SELECT xp FROM users WHERE userguild = {userguild}")
result = cursor.fetchone()
expe = result += 1
if user == client.user:
return
else:
userguild = f'{user.id}/{user.guild.id}'
sql = ("UPDATE users SET xp = ? WHERE userguild = ?")
val = (expe, userguild)

tepid cradle Jul 25, 2020, 2:50 PM

#

yes. That's not the cause of the error though. When you do cursor.fetchone(), it returns a tuple, no matter what your query. So you might need to do result[0] += 1 or even result[0][0] += 1
Just print out result and check what it is, I'm not quite sure if it's a tuple or a list of tuples.

sinful condor Jul 25, 2020, 2:51 PM

#

I cant print result because I cant get the bot to load because of the errors

tepid cradle Jul 25, 2020, 2:51 PM

#

Also, don't use f-strings for SQL queries, it is bad practice as it leaves your code vulnerable to SQL injection attacks.

sinful condor Jul 25, 2020, 2:51 PM

#

I switched it to result[0] += 1

tepid cradle Jul 25, 2020, 2:51 PM

#

Then run the query manually on the terminal or a separate file and see the response

sinful condor Jul 25, 2020, 2:52 PM

#

it says it is null

#

because I havent added anything into the database yet

tepid cradle Jul 25, 2020, 2:55 PM

#

It returns a tuple, result[0] should be fine

#

fetchall returns a list of tuples, fetchone returns a tuple

sinful condor Jul 25, 2020, 2:55 PM

#

yeah it still has the error though

tepid cradle Jul 25, 2020, 2:55 PM

#

Are there any other errors? I can see one in the last block

#

add something after return

sinful condor Jul 25, 2020, 2:56 PM

#

right now it only shows this one but I know there are alot more because when I fix one like 5 more pop up

#

heres all of the code in a pastebin https://pastebin.com/raw/CYqkr98A

#

what should I add after return

tepid cradle Jul 25, 2020, 2:58 PM

#

You should add something after return. It's not an error, but right now it's not returning anything, so you won't know what happened if the function goes into that block

#

Whatever you suppose should happen in the else condition

#

If nothing should happen, then there's no need of that if condition at all

sinful condor Jul 25, 2020, 2:58 PM

#

yes but I dont want the bot to be able to level up and get xp and stuff

tepid cradle Jul 25, 2020, 2:59 PM

#

Then return None or False or something

sinful condor Jul 25, 2020, 2:59 PM

#

ok

tepid cradle Jul 25, 2020, 3:00 PM

#

Then this whole block is problematic:

else:
    userguild = f'{user.id}/{user.guild.id}'
    sql = ("UPDATE users SET xp = ? WHERE userguild = ?")
    val = (expe, userguild)

you create the variable sql (which you should ideally name query), but don't use it.

#

Nor do you use the variable val anywhere. The function just ends after that line, effectively rendering the else block meaningless

sinful condor Jul 25, 2020, 3:01 PM

#

I am meant to put cursor.execute(sql, val) below it I think

tepid cradle Jul 25, 2020, 3:01 PM

#

OK

#

Then it would make sense. Then don't put brackets around the query. Again, not an error, but unnecessary

sinful condor Jul 25, 2020, 3:02 PM

#

ok what else

#

the invalid syntax error is still popping up how can I fix that

tepid cradle Jul 25, 2020, 3:04 PM

#

where?

sinful condor Jul 25, 2020, 3:05 PM

#

expe = result[0] += 1

tepid cradle Jul 25, 2020, 3:05 PM

#

show your updated code

sinful condor Jul 25, 2020, 3:06 PM

#

https://pastebin.com/raw/TCjPK3Ms

tepid cradle Jul 25, 2020, 3:06 PM

#

And the error message

sinful condor Jul 25, 2020, 3:06 PM

#

its just invalid syntax invalid syntax (<unknown>, line 944)pylint(syntax-error)

#

its not letting me start the bot because of it

tepid cradle Jul 25, 2020, 3:07 PM

#

The code itself looks fine

#

Can you try running that function separately with some dummy values

sinful condor Jul 25, 2020, 3:10 PM

#

I dont get the error when I put it in a dummy thing

tepid cradle Jul 25, 2020, 3:11 PM

#

Then it's just the linter showing the error

#

Oh, sorry

#

It is incorrect syntax

#

remove the second =

sinful condor Jul 25, 2020, 3:12 PM

#

so expe = result[0] + 1

tepid cradle Jul 25, 2020, 3:12 PM

#

expe = result[0] + 1

sinful condor Jul 25, 2020, 3:12 PM

#

ok

tepid cradle Jul 25, 2020, 3:12 PM

#

+= is used when you want to increment the value of that variable itself, not when you are assigning it to a new variable

sinful condor Jul 25, 2020, 3:14 PM

#

I am getting ``` await coro(*args, **kwargs)
File "bot.py", line 911, in on_message
await add_xp(result, message.author, 1)
File "bot.py", line 944, in add_xp
expe = result[0] + 1
TypeError: 'NoneType' object is not subscriptable

tepid cradle Jul 25, 2020, 3:16 PM

#

That's because there are no values in the Db. If you fetch from a blank table, you'll get this error

sinful condor Jul 25, 2020, 3:17 PM

#

why didnt the updata_data not kick in to make a section for them

tepid cradle Jul 25, 2020, 3:19 PM

#

You need to commit after insert or update queries. Just add cursor.commit() to commit

sinful condor Jul 25, 2020, 3:21 PM

#

would it be cursor.commit or db.commit

tepid cradle Jul 25, 2020, 3:23 PM

#

cursor

sinful condor Jul 25, 2020, 3:23 PM

#

cursor got error but db worked I have been using db for every commit in my bot. but I have a problem

📎 unknown.png

#

its making 2 different things

#

and it is happening whenever I put a message

#

📎 unknown.png

tepid cradle Jul 25, 2020, 3:33 PM

#

You have two separate insert queries

#

Use just one

sinful condor Jul 25, 2020, 3:34 PM

#

how do I put it in one

tepid cradle Jul 25, 2020, 3:35 PM

#

you're inserting in the same table, why do you want two queries?

sinful condor Jul 25, 2020, 3:35 PM

#

no I want to make it one but I dont know how

tepid cradle Jul 25, 2020, 3:36 PM

#

you're already inserting values in 2 columns, just add a third as well

sinful condor Jul 25, 2020, 3:37 PM

#

ok now it makes one but every message it still makes another one

#

📎 unknown.png

tepid cradle Jul 25, 2020, 3:37 PM

#

what do you want to do?

#

INSERT INTO inserts a new row

#

use UPDATE if you want to update existing row

sinful condor Jul 25, 2020, 3:38 PM

#

that first one is only supposed to happen if there is nothing in there with that userguild(userid/guildid)

tepid cradle Jul 25, 2020, 3:38 PM

#

But then you'll have to check if it already exists, otherwise update will throw an error

sinful condor Jul 25, 2020, 3:39 PM

#

that is what the if result is not None or user == client.user: return is supposed to do

tepid cradle Jul 25, 2020, 3:41 PM

#

First correct this part

    else:
        if result is None:
            sql = ("INSERT INTO users(userguild, xp) VALUES(?,?)")
            val = (userguild, startxp)
            sql1 = ("INSERT INTO users(userguild, level) VALUES(?,?)")
            val1 = (userguild, startlvl)
            cursor.execute(sql, val)
            cursor.execute(sql1, val1)

First, use elif for second condition, not another if inside your else>
Second, if you've already checked that it is not none, and that was not satisfied, then it's obviously none. So you don't need to check again at all

sinful condor Jul 25, 2020, 3:42 PM

#

ok

#

Here is the code async def update_data(users, user): userguild = f'{user.id}/{user.guild.id}' startxp = '0' startlvl = '1' db = sqlite3.connect('main.sqlite') cursor = db.cursor() cursor.execute(f"SELECT userguild FROM users WHERE userguild = {userguild}") result = cursor.fetchone() if result is not None or user == client.user: return elif result is None: sql = ("INSERT INTO users(userguild, xp, level) VALUES(?,?,?)") val = (userguild, startxp, startlvl) cursor.execute(sql, val) db.commit() it still does the seperate thing

#

or like this if result is not None or user == client.user: return else: sql = ("INSERT INTO users(userguild, xp, level) VALUES(?,?,?)") val = (userguild, startxp, startlvl) cursor.execute(sql, val) db.commit()

#

I am also getting this error ``` File "bot.py", line 941, in add_xp
expe = result[0] + 1
TypeError: 'NoneType' object is not subscriptable

#

@tepid cradle

tepid cradle Jul 25, 2020, 3:54 PM

#

cursor
@tepid cradle

sinful condor Jul 25, 2020, 3:55 PM

#

where

high bone Jul 25, 2020, 3:56 PM

#

So, a question about the asyncpg library to access a postgresql database. I need to dynamically select the table name, but that librayr doesn't provide a SQL object like pyscopg2 does. What should I do?

#

I can reword the question if it's unclear

brazen charm Jul 25, 2020, 3:58 PM

#

format it like a string

#

thats all psycopg2 does

#

among of random things

high bone Jul 25, 2020, 3:58 PM

#

Yeah but what about safe parsing?

#

They say to NEVER EVER EVER EVER concatenate or .format() SQL code

#

I wish there was just a safe_format() function or something

torn sphinx Jul 25, 2020, 5:02 PM

#

https://puu.sh/GaScM/aedbc28e12.png

#

def listPriceHistory(asset, fromstamp, tostamp):
        conn = sqlite3.connect('testing.db')
        curs = conn.cursor()
        
        try:
            curs.execute('SELECT value, staml WHERE name = ? AND stamp >= ? AND stamp <= ?', (asset, fromstamp, tostamp))
        
        except sqlite3.Error as e:
            print("An error occurred:", e.args[0])

        return curs.fetchall()```

knotty pond Jul 25, 2020, 6:05 PM

#

you need to specify which table you're selecting from

#

SELECT value, staml FROM [db name] WHERE ...

torn sphinx Jul 25, 2020, 6:06 PM

#

oof

#

yeah fixed

#

thanks

#

and i also somehow managed to misspell stamp

#

Hi, if there's anyone here who knows about MongoDB I have a collection that stores points for each user in a discord server. I want to create a leaderboard function and find the top 10 users with the most points (which is the value of each post) but not sure how i can index each line in the DB. I could probably figure out the python part of it but for now i'm not sure how to grab the highest 10 {"points"} values from the collection

slow crystal Jul 25, 2020, 6:24 PM

#

Hey guys, I think this is the right place to ask this, if not, please point me in the right direction.

Hey so, I'm trying to get some data from a redis website. (It has a .php ending) and then cache that data and then put it into either google sheets or some kind of analysis tool. I'm really new to this so I'm flying blind.

Which fit this situation? I'm guessing there's something out there that will already listen to a redis site and then cache your data but I'm not sure what that would be called. There's so many options and I'm pretty sure that there's probably a good way to do this so if you guys have any suggestions I would really appreciate it!

shy kite Jul 25, 2020, 6:30 PM

#

I need to revise my DB in python, what would be a good starting point?

#

where should i start learning / resources available

tribal dome Jul 25, 2020, 7:08 PM

#

i have a really dumb question. In mongodb, what data type should my dict be stored as?
right now ihave a dict inside an array, and it defaults it to string, is that normal?

useritems[ #array
  0: "{ some info}" #string
  1: "{more info}" #string
]```

torn sphinx Jul 25, 2020, 8:04 PM

#

how do i connect to a rds with mysql workbench cuz i cant seem to make a successful connection

blazing void Jul 25, 2020, 9:45 PM

#

it's been a long time since I used AWS, but...have you added inbound connection rules that would allow your computer to connect?

#

if I recall correctly, AWS default security group settings don't have any inbound rules set, and so ignore incoming connections from outside by default

slender atlas Jul 25, 2020, 10:10 PM

#

sqlite3's cursor closes automatically after finishing running the file right?

queen nebula Jul 25, 2020, 10:23 PM

#

I'm not sure on whether it's necessary, but it should be closed when it's garbage collected (not guaranteed to happen); if it doesn't support a context manager directly you could use closing from contextlib to make sure it's closed

slender atlas Jul 25, 2020, 10:23 PM

#

k thanks

torn sphinx Jul 25, 2020, 10:26 PM

#

i just realized i made my thing with public accessiblity as no

#

now i gotta figure out how to change it to yes

blazing void Jul 25, 2020, 10:34 PM

#

you'll need to add an inbound rule that lets you connect. you could add 0.0.0.0/0, but ideally you don't do that

#

maybe best add just your IP address for now

#

in future you could proxy through a bastion. workbench should support connecting with an SSH port forward

restive stone Jul 26, 2020, 1:33 AM

#

Yo yo yo I need help with adding a dB to my Discord bot - I already a dB for my prefixes but I need one for blacklisting members so banned members can’t use the bot
What I have so far:

@commands.Cog.listener()
async def on_message(self, message: discord.Message):
    embed = discord.Embed(
        colour=discord.Colour.red()
)
embed.add_field(name=“U were banned”, value=“reason”
if not message.author.id in Blacklist: 
await command.process(message)
else:
    message.send(embed=embed)

#

Don’t mind the indents I’m on mobile typing this

#

Preferably sqlite3

sharp magnet Jul 26, 2020, 1:38 AM

#

Does this look about right for a discord bot?

📎 unknown.png

#

Or would my "types" be different?

vale hearth Jul 26, 2020, 1:52 AM

#

i'm not sure if this is the right place to ask this

#

but what's the best way to do feature selection

#

measuring how close a feature is to a specific feature, like hospital death

sharp magnet Jul 26, 2020, 2:03 AM

#

with connection:
     cursor.execute("""
     SELECT coins FROM users WHERE ? = userid
     """)
     if cursor.fetchone() # <--- Here ```
Trying to make this check if it can't find coins e.g. meaning not in the database

rich trout Jul 26, 2020, 2:05 AM

#

cursor's remember what you did with them

#

so, that .fetchone() will grab the first result, and then the cursor will forget what it just did

#

calling .fetchone() again will return an error

#

you should store the result of .fetchone() to do stuff with it

sharp magnet Jul 26, 2020, 2:08 AM

#

So store it as a variable?

#

Then do something like

if x:
  #add the coins
else: 
  #inset a value into the database?

#

X being the cursor.fetchone btw

tepid cradle Jul 26, 2020, 3:56 AM

#

have you committed the changes? Usually, if there is no syntax error, then forgetting to commit is the reason for SQL statements not working

plucky pelican Jul 26, 2020, 5:28 AM

#

    async def register(self, ctx):
        db = sqlite3.connect('economy.sqlite')
        cursor = db.cursor()
        cursor.execute(f"INSERT INTO economy (user_id) \ VALUES ({ctx.author.id})")
        result = cursor.fetchone()
        if result is None:
            await ctx.send("k")
        db.commit()
        cursor.close()
        db.close()```

This code shows this error when i run it:

#

Command raised an exception: OperationalError: unrecognized token: ""

#

Ping me Thanks

restive stone Jul 26, 2020, 6:04 AM

#

Okay so back when I was very new to discord.py I had a broken json prefix thing and now I have 45 guild IDs and prefixes and I am wondering if it’s possible to remove all of them and when a person runs a command and the guild ID isn’t in the SQLite3 DB it would add it.

TL;DR
If remove all guild_ID and prefixes is it possible when a user runs a command it adds them back? So I am not seeing guild IDs and prefixes that don’t use my bot anymore?

gaunt pasture Jul 26, 2020, 6:41 AM

#

📎 unknown.png

#

i want to replace all of the values if "guild" is the same instead of adding a new row with the same guild value. how can i do that?

tepid cradle Jul 26, 2020, 7:33 AM

#

Just check if the guild already exists in the database. If yes, then run an update query, if no, then run an insert query

gaunt pasture Jul 26, 2020, 7:51 AM

#

@tepid cradle i dont know how to check

#

i've never worked with this before

tepid cradle Jul 26, 2020, 7:54 AM

#

Use a select query to count the number of existing rows with that particular guild, then check if it's zero, which means that it doesn't exist

wary abyss Jul 26, 2020, 8:37 AM

#

i am working on a bot where admins can enter few questions to the user , and stores it in db , but the problem is i want the maximum number of questions to be 15 and the min can be any if the user wants 5 he can Type done when he completes 5 questions and only 5 must be saved in db py @commands.command() @commands.guild_only() @commands.has_permissions(administrator = True) async def applyquestions(self,ctx): conn = sqlite3.connect('infobot.sqlite') c = conn.cursor() c.execute('''CREATE TABLE IF NOT EXISTS application_qustions (guildid TEXT , application_id TEXT , qus1 TEXT ,qus2 TEXT ,qus3 TEXT ,qus4 TEXT ,qus5 TEXT,qus6 TEXT,qus7 TEXT,qus8 TEXT,qus9 TEXT , qus10 TEXT) ''') question = [] for i in range(1,11): a = await ctx.send(f"enter your question ") q = await self.bot.wait_for("message" , check = lambda message : message.channel == a.channel and message.author == ctx.author) qustionf = q.content if (qustionf in ["done" , "Done"]): break else : question.append(qustionf) await ctx.send(question) conn = sqlite3.connect('infobot.sqlite') c = conn.cursor() c.execute('''CREATE TABLE IF NOT EXISTS application_qustions (guildid TEXT , qus1 TEXT ,qus2 TEXT ,qus3 TEXT ,qus4 TEXT ,qus5 TEXT,qus6 TEXT,qus7 TEXT,qus8 TEXT,qus9 TEXT , qus10 TEXT) ''') c.execute(f"INSERT INTO application_qustions(guildid , qus1, qus2, qus3, qus4, qus5, qus6, qus7, qus8, qus9, qus10 ) VALUES('{ctx.guild.id}','{question[0]}','{question[1]}','{question[2]}','{question[3]}','{question[4]}','{question[5]}','{question[6]}','{question[7]}','{question[8]}','{question[9]}')") conn.commit()
Problem: when user stops at 5 questions it wont be saved in db , and i get a error ``discord.ext.commands.errors.CommandInvokeError: Command raised an exception: IndexError: list index out of range`

tepid cradle Jul 26, 2020, 8:46 AM

#

Lol.. @wary abyss , you posted this question earlier, and I worked out an answer, but when I came back to post it, the question had disappeared 😆

wary abyss Jul 26, 2020, 8:46 AM

#

@tepid cradle sooo sorry , i thought no one would answer and deleted it sorry

tepid cradle Jul 26, 2020, 8:47 AM

#

Here you go

#

base_query = "insert into table_name(col1, col2, col3) values {}"
questions = ['q1', 'q2', 'q3']
query = base_query.format("(%s, %s, %s), "*len(questions))[:-2]
print(query)
cur.execute(query, questions)

delicate fieldBOT Jul 26, 2020, 8:47 AM

#

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

tepid cradle Jul 26, 2020, 8:48 AM

#

OK Mr. bot 😛

wary abyss Jul 26, 2020, 8:48 AM

#

okayyy can u give a small explaination whats going here

#

i am kinda new to sqlite

tepid cradle Jul 26, 2020, 8:50 AM

#

sure, that's why I wanted to do it with eval, would have been easier to explain. But anyway.
So I'm creating the query without placeholders, with just {}.
Then I'm getting the questions in a list.
Then I take a list of placeholders, i.e. (%s, %s, %s), and multiply it with the length of the above list. This gives me a list of list of placeholders just as long as the messages list requires. Then I add it to the query.

#

In the end, your query will look like this

📎 unknown.png

#

This has nothing to do with Sqlite though, it's all just python

#

the [:-2] only removes the last two characters, i.e., , which would otherwise cause an SQL syntax error

wary abyss Jul 26, 2020, 8:52 AM

#

ooh! okayyy

#

but where do i insert it? , wait let me edit the code and send it

#

aaaaa

tepid cradle Jul 26, 2020, 8:53 AM

#

This is your query. You have to replace your query with this. Since you don't know the number of inputs, you can't use a static query. You have to prepare your query this way

wary abyss Jul 26, 2020, 8:53 AM

#

oooh! okayyy

#

tysm

#

💯💯

tepid cradle Jul 26, 2020, 8:55 AM

#

Let me know if this works for you

wary abyss Jul 26, 2020, 8:55 AM

#

yeah sure i am trying it out

tepid cradle Jul 26, 2020, 8:56 AM

#

There's also cursor.mogrify(), which I don't fully understand. You can check out this answer for that method, if you want:
https://stackoverflow.com/questions/8134602/psycopg2-insert-multiple-rows-with-one-query

Stack Overflow

psycopg2: insert multiple rows with one query

I need to insert multiple rows with one query (number of rows is not constant), so I need to execute query like this one:

INSERT INTO t (a, b) VALUES (1, 2), (3, 4), (5, 6);
The only way I know is

wary abyss Jul 26, 2020, 8:56 AM

#

okayy i will tysm bro

#

@tepid cradle py @commands.command() @commands.guild_only() @commands.has_permissions(administrator = True) async def applyquestions(self,ctx): question = [] for i in range(1,11): a = await ctx.send(f"enter your question ") q = await self.bot.wait_for("message" , check = lambda message : message.channel == a.channel and message.author == ctx.author) qustionf = q.content if (qustionf in ["done" , "Done"]): break else : question.append(qustionf) await ctx.send(question) conn = sqlite3.connect('infobot.sqlite') c = conn.cursor() c.execute('''CREATE TABLE IF NOT EXISTS application_qustions (guildid TEXT , qus1 TEXT ,qus2 TEXT ,qus3 TEXT ,qus4 TEXT ,qus5 TEXT,qus6 TEXT,qus7 TEXT,qus8 TEXT,qus9 TEXT , qus10 TEXT) ''') base_query = "insert into application_qustions(col1, col2, col3) values {}" questions = ['q1', 'q2', 'q3'] query = base_query.format("(%s, %s, %s), "*len(questions))[:-2] print(query) c.execute(query, questions)
is this correct? i have a error

📎 unknown.png

tepid cradle Jul 26, 2020, 9:11 AM

#

wait, why are you redefining the questions list. In my example it was obviously just an example. You already have that list above. You'll have to adapt my code to suit the parameters that you have. Like, you have some 10-11 parameters, I had used only 3 to show how it would work.
you can't just copy paste my code, work on it to suit your application.

wary abyss Jul 26, 2020, 9:12 AM

#

okayyy lol , got it

tepid cradle Jul 26, 2020, 9:12 AM

#

I'll explain it further if it's not clear what my code is doing, you can ask

#

If you have questions about any specific part of my code

wary abyss Jul 26, 2020, 9:13 AM

#

okayyy , ig i have a idea how its working , i will try according to it if its not working i will ask

smoky pond Jul 26, 2020, 11:35 AM

#

I am quite new to sql, I am using postgresql if a have a table my_table

 id |   name
----+----------

How could I get a random row that contains a certain text.

I have this so far, to choose a random row:

select * from my_table offset random() * (select count(*) from my_table) limit 1;

How would I modify this to only choose from rows with name like whatever?

burnt turret Jul 26, 2020, 11:43 AM

#

using mongodb (pymongo), can i update a document to have the values of a dictionary?
like if i have a dict a = {'key':'value', 'key':'value'} , how can i update a field to this dictionary?

pliant spoke Jul 26, 2020, 1:34 PM

#

@bot.command()
async def warn(ctx, member: discord.Member, *, reason=None):
    await ctx.send(f"{member.mention} has been warned for {reason}.")
    await member.send(f"You've been warned in Rocket Legion for the following reason: {reason}")
    

    with open('warns.json', 'w') as f:
        data = json.load(f)

    if not member.id in data:
        warns[member.id] = 1
    else:
        warns[member.id] += 1

    with open('warns.json', 'w'):
        json.dump(data, f, indent=4)
``` `UnsupportedOperation: not readable` idk what that error means (ik this is dpy but i was told to ask here since its a python issue not a dpy issue)

tribal plume Jul 26, 2020, 1:45 PM

#

When joining tables A and B on indexed columns of B, a full scan in A will be needed?

gaunt pasture Jul 26, 2020, 2:43 PM

#

@torn sphinx hey so i got everything set up but i may need your help actually translating if you're down. i spent about 2-3 hours last night trying to understand the basics of SQLite and i do now, but majority of those 2-3 hours was me having issues with "translating" the JSON command into an SQLite command

tepid crow Jul 26, 2020, 2:49 PM

#

@pliant spoke you meed to read it first them write on it

pliant spoke Jul 26, 2020, 2:56 PM

#

@tepid crow k thx. now im getting this ```py
@bot.command()
async def warn(ctx, member: discord.Member, *, reason=None):
await ctx.send(f"{member.mention} has been warned for {reason}.")
await member.send(f"You've been warned in Rocket Legion for the following reason: {reason}")

with open('warns.json', 'r') as f:
    data = json.load(f)

if not member.id in data:
    data[member.id] = 1
else:
    data[member.id] += 1

with open('warns.json', 'w'):
    json.dump(data, f, indent=4)``` `ValueError: I/O operation on closed file.` in the json.dump line

orchid yacht Jul 26, 2020, 3:04 PM

#

@hazy mango do I use asyncpg?

hazy mango Jul 26, 2020, 3:05 PM

#

Yea

#

That's the module you use inside Python

#

Then you use PgAdmin4 on your PC to view the database contents

#

@orchid yacht

orchid yacht Jul 26, 2020, 3:10 PM

#

Alrighty

tepid crow Jul 26, 2020, 3:33 PM

#

try closing the file (im not sure if thats gonna help) @pliant spoke

orchid yacht Jul 26, 2020, 3:36 PM

#

Hey @hazy mango, so I am currently in PgAdmin4 and it's asking me to add a server where do I go from here?

#

Oh I have got this now https://i.imgur.com/18xc5Kv.png

Imgur

hazy mango Jul 26, 2020, 3:41 PM

#

Right okay

#

So

#

Gimme a sec

#

@orchid yacht you there?

orchid yacht Jul 26, 2020, 3:42 PM

#

Yeah im here

hazy mango Jul 26, 2020, 3:43 PM

#

Okay

#

So

#

Right-click on servers and press Create

#

Then call it like my_local_server or something

#

Call it what you want but that's the common name

orchid yacht Jul 26, 2020, 3:43 PM

#

Done, and for connection info?

#

Should the host name be 127.0.0.1?

hazy mango Jul 26, 2020, 3:44 PM

#

Yea

#

That's localhost

#

Aka your PC

orchid yacht Jul 26, 2020, 3:45 PM

#

https://i.imgur.com/gyNRrRN.png

Imgur

#

thinkBox

hazy mango Jul 26, 2020, 3:45 PM

#

I'm trying to remember how tf to do this lmao

orchid yacht Jul 26, 2020, 3:45 PM

#

Do I need to open the port in my firewall or smth?

hazy mango Jul 26, 2020, 3:45 PM

#

Probably

orchid yacht Jul 26, 2020, 3:45 PM

#

Wouldn't think so since it's local

#

but I'll try

brazen charm Jul 26, 2020, 3:45 PM

#

are you running this on localhost?

#

or is it on a external server

orchid yacht Jul 26, 2020, 3:46 PM

#

local

#

The firewall is not the issue

blazing void Jul 26, 2020, 3:47 PM

#

aren't you already connected? go into schemas

brazen charm Jul 26, 2020, 3:47 PM

#

if its the default settings:

host: localhost
port: 5432
username: postgres
password: none/postgres

#

also yeah you are already connected lol

orchid yacht Jul 26, 2020, 3:47 PM

#

KEKW

blazing void Jul 26, 2020, 3:48 PM

#

your screenshot already shows you're looking at the default postgres db, I dunno if this is the database you meant to connect to, but it's a database

orchid yacht Jul 26, 2020, 3:48 PM

#

It's local so that works yeah

#

https://i.imgur.com/dN9LARE.png

Imgur

blazing void Jul 26, 2020, 3:49 PM

#

well, your tables are under schema folder

orchid yacht Jul 26, 2020, 3:49 PM

#

Sweet ty

#

@hazy mango got it PogU

hazy mango Jul 26, 2020, 3:50 PM

#

👍

#

Honestly I've not used PgAdmin for like a year so forgotten a lot lmao

orchid yacht Jul 26, 2020, 3:50 PM

#

Still appreciate you helping

blazing void Jul 26, 2020, 3:51 PM

#

I think the "schemas" thing catches people out because it's not there on MySQL or sqlite. but that's a postgres feature, not a pgadmin one, you'll see it if you use any database browser

#

as it appears on dbeaver for example

📎 unknown.png

orchid yacht Jul 26, 2020, 3:57 PM

#

@hazy mango what's the next step? Creating the tables?

hazy mango Jul 26, 2020, 3:58 PM

#

Yep

orchid yacht Jul 26, 2020, 3:58 PM

#

To hold the userid and jump_url

hazy mango Jul 26, 2020, 3:58 PM

#

Yea, so say ban_messages

orchid yacht Jul 26, 2020, 3:58 PM

#

https://i.imgur.com/G1ejp4X.png

Imgur

hazy mango Jul 26, 2020, 3:58 PM

#

Then the columns would be like jump_url of type text and user_id of type bigint kinda thing

#

All ids are classified as bigint btw

orchid yacht Jul 26, 2020, 3:59 PM

#

Okay good to know, I made the tables

#

Is that all we have to do in pgadmin4?

hazy mango Jul 26, 2020, 4:00 PM

#

Pretty much I think

#

Make sure you create the field correctly though

#

E.g. making so they can't be None

orchid yacht Jul 26, 2020, 4:01 PM

#

https://i.imgur.com/GQFicWk.png type also none?

Imgur

hazy mango Jul 26, 2020, 4:01 PM

#

Sorry

#

I meant not null

#

Just leave type as whatever it was

orchid yacht Jul 26, 2020, 4:02 PM

#

Okay but Not NULL? should be yes correct?

hazy mango Jul 26, 2020, 4:02 PM

#

Yes

orchid yacht Jul 26, 2020, 4:03 PM

#

Alright I installed and imported asyncpg but I can't really find a setup guide or anything thinkBox

hazy mango Jul 26, 2020, 4:06 PM

#

sec

#

There is one

orchid yacht Jul 26, 2020, 4:06 PM

#

Nice

hazy mango Jul 26, 2020, 4:07 PM

#

https://magicstack.github.io/asyncpg/current/api/index.html#connection-pools you want to make a pool and make it part of your bot

#

I can give example in a sec

#

class MyBot(commands.Bot):
    """Custom class for bot."""
    def __init__(self):
        ...
        # Create postgreSQL database pool
        self.database_pool = self.loop.run_until_complete(asyncpg.create_pool(host="localhost", user="user",password="password", database="name"))

gaunt pasture Jul 26, 2020, 4:10 PM

#

hey so i have a "database" in JSON and i'm trying to rework it into SQLite, could any of you help me? i've never worked with it before

hazy mango Jul 26, 2020, 4:10 PM

#

I have a load of other stuff in that class but that's the main database bit

#

And then I actually have a function for running statements and stuff

#

https://paste.pythondiscord.com/osevafazut.py this is all the database stuff in my class @orchid yacht

#

Then I do likepy await self.bot.execute_sql('SELECT thing FROM table WHERE otherthing=$1', (val_for_$1), index=0)

gaunt pasture Jul 26, 2020, 4:12 PM

#

i have some code right now that looks like this:

c.execute("UPDATE commtable SET " + arg1 + " = " + arg2 + " WHERE guild = " + guild_id)

that prints this:

UPDATE commtable SET rsample = y WHERE guild = 716030141234544681

but i get the error "OperationalError: no such column: y"

#

(commtable is the name of my table)

hazy mango Jul 26, 2020, 4:14 PM

#

You need 'y' not y I'm assuming

#

@gaunt pasture

#

This isn't how you should do concatenation though in sql

orchid yacht Jul 26, 2020, 4:15 PM

#

I don't need to add those other database functions that you have do I?

gaunt pasture Jul 26, 2020, 4:15 PM

#

so, like, str(arg2)?

hazy mango Jul 26, 2020, 4:15 PM

#

I found I needed them to fix a bug but idk if you'll get it or not @orchid yacht

#

@gaunt pasture no

#

Just use the proper way

gaunt pasture Jul 26, 2020, 4:15 PM

#

idk the proper way haha

hazy mango Jul 26, 2020, 4:15 PM

#

What database are you using?

orchid yacht Jul 26, 2020, 4:15 PM

#

Okay will try with out first then

gaunt pasture Jul 26, 2020, 4:16 PM

#

uhh

#

?

hazy mango Jul 26, 2020, 4:16 PM

#

What database language

gaunt pasture Jul 26, 2020, 4:16 PM

#

does showing you my code help? lmfao

#

Python?

#

idfk

hazy mango Jul 26, 2020, 4:16 PM

#

Sqlite, PostgreSQL, MySQL etc.

gaunt pasture Jul 26, 2020, 4:16 PM

#

i just installed SQLite and watched some youtube tutorials

#

oh ok SQLite

hazy mango Jul 26, 2020, 4:17 PM

#

Then do```python
"UPDATE commtable SET ?=? WHERE guild=?", (arg1, arg2, arg3)

#

Something like that

gaunt pasture Jul 26, 2020, 4:18 PM

#

  File "C:/Users/Nik/PycharmProjects/beatsbot/beats.py", line 1957, in db
    c.execute("UPDATE commtable SET ?=? WHERE guild=?, (arg1, arg2, guild)")
sqlite3.OperationalError: near "?": syntax error

#

i think maybe the question marks need parentheses but i thought i tried that already

brazen charm Jul 26, 2020, 4:19 PM

#

cant sub column names with ?

#

same goes for tables

#

you can only use ? for values

gaunt pasture Jul 26, 2020, 4:19 PM

#

ah :p

#

so how do i use a variable to call for a column

orchid yacht Jul 26, 2020, 4:21 PM

#

@hazy mango so with the pool setup now I would want to make an on_message event have it check the ban log channel and look for the user_id and then send the userid and jump_url to the table right?

hazy mango Jul 26, 2020, 4:22 PM

#

Wdym? @orchid yacht

orchid yacht Jul 26, 2020, 4:22 PM

#

Well I want to grab the userids and jump_urls now right?

hazy mango Jul 26, 2020, 4:22 PM

#

async with self.bot.database_pool.acquire() as connection:
   jump_url = await connection.fetchval("SELECT jumpurl FROM table WHERE userid=$1", (user_id,), column=0)
```iirc @orchid yacht

gaunt pasture Jul 26, 2020, 4:23 PM

#

so how do i use a variable to call for a column

orchid yacht Jul 26, 2020, 4:26 PM

#

That grabs the info right? How does it get the info?

hazy mango Jul 26, 2020, 4:26 PM

#

Wdym? @orchid yacht

#

Actually sec

orchid yacht Jul 26, 2020, 4:26 PM

#

How does the table get the userids and jump_urls?

hazy mango Jul 26, 2020, 4:27 PM

#

I don't get what you're saying

orchid yacht Jul 26, 2020, 4:28 PM

#

The database tables are empty now

#

how do I fill it with info

hazy mango Jul 26, 2020, 4:28 PM

#

Right you'd have to fill manually ig

#

Or add from now on

orchid yacht Jul 26, 2020, 4:28 PM

#

How would I add from now on?

hazy mango Jul 26, 2020, 4:29 PM

#

Each time the bot sends a message about person being banned, insert into the database the message.jump_url and the user id of person that was banned

orchid yacht Jul 26, 2020, 4:29 PM

#

Doesn't matter if it's missing old messages

hazy mango Jul 26, 2020, 4:29 PM

#

https://www.w3schools.com/sql/sql_insert.asp this is how to insert into a database

orchid yacht Jul 26, 2020, 4:29 PM

#

Sweet will have a look at that

hazy mango Jul 26, 2020, 4:35 PM

#

@ me if you need any more help @orchid yacht, afk for a bit

orchid yacht Jul 26, 2020, 4:35 PM

#

Wil do! ty

gaunt pasture Jul 26, 2020, 4:55 PM

#

so right now i have

"UPDATE commtable SET {col}={val} WHERE guild={gld}"

and it's telling me

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: no such column: ***this is what val is***

#

so whatever {val} is, let's say it's "hello", it would say
OperationalError: no such column: hello

#

this is the whole line:

c.execute("UPDATE commtable SET {col}={val} WHERE guild={gld}".format(col=arg1, val=str(arg2), gld=guild_id))

tepid cradle Jul 26, 2020, 5:26 PM

#

I think it's converting the column name to string

#

wait, let me check something

#

Oh, the opposite is happening actually

#

You string doesn't have quotes around them.

#

So your query becomes update commtable set arg1 = hello whereas it should be update commtable set arg1 = 'hello'

#

@gaunt pasture
You can resolve this by using parameterized query. You should anyway not use string formatting for SQL queries, it's a security risk. change your query like this:
c.execute("UPDATE commtable SET {col} = %s WHERE guild = %s".format(col=arg1), ("Hello", 12345))

#

Your db library will automatically take care of the required quotes

gaunt pasture Jul 26, 2020, 5:31 PM

#

col needs to be a variable too

#

so %s = %s?

#

@tepid cradle

  File "C:/Users/Nik/PycharmProjects/beatsbot/beats.py", line 1956, in db
    c.execute("UPDATE commtable SET {col} = %s WHERE guild = %s".format(col=arg1), (arg2, guild_id))
sqlite3.OperationalError: near "%": syntax error

tepid cradle Jul 26, 2020, 5:34 PM

#

No, you can't parameterize column names, that's why I left it like that.

#

Oh, and if you're using sqlite, replace %s with ?

gaunt pasture Jul 26, 2020, 5:34 PM

#

i had that before and it said syntax error for ?

#

  File "C:/Users/Nik/PycharmProjects/beatsbot/beats.py", line 1957, in db
    c.execute("UPDATE commtable SET ?=? WHERE guild=?, (arg1, arg2, guild)")
sqlite3.OperationalError: near "?": syntax error

tepid cradle Jul 26, 2020, 5:35 PM

#

No, no. As I said, you can't parameterize column name

#

For column name, use string formatting. Use query parameters only for the values

gaunt pasture Jul 26, 2020, 5:35 PM

#

okay

#

makes sense

tepid cradle Jul 26, 2020, 5:35 PM

#

Also, close quotes after your query

#

c.execute("UPDATE commtable SET {}=? WHERE guild=?".format(arg1), (arg2, guild)")

gaunt pasture Jul 26, 2020, 5:36 PM

#

c.execute("UPDATE commtable SET {col} = ? WHERE guild = ?".format(col=arg1), (arg2, guild_id))

#

here's my line

#

putting more quotes gives it a red line

torn sphinx Jul 26, 2020, 5:39 PM

#

Hi,
Why do i need to upload pictures to a file in a model like in the example below?
isnt the purpose of a database to store data? or it gives the picture an id in the db and it is stored somewhere else?

picture= models.ImageField(upload_to='pictures/', default='pictures/none.png', null=True, blank=True)

tepid cradle Jul 26, 2020, 5:40 PM

#

c.execute("UPDATE commtable SET {col} = ? WHERE guild = ?".format(col=arg1), (arg2, guild_id))

@gaunt pasture This is right. Earlier you had closed the quotes at the end of the line, I think. Does this run fine?

#

Hi,
Why do i need to upload pictures to a file in a model like in the example below?
isnt the purpose of a database to store data? or it gives the picture an id in the db and it is stored somewhere else?

picture= models.ImageField(upload_to='pictures/', default='pictures/none.png', null=True, blank=True)
@torn sphinx Databases store text data. If you want to store images in the database, you can convert them to base64 strings. But it's not advisable as it will increase the size of the database which 1. makes it slow, and 2. increases your bills because usually database storage space is more expensive than file storage

gaunt pasture Jul 26, 2020, 5:43 PM

#

yes!! it does!

#

thanks so much

torn sphinx Jul 26, 2020, 5:44 PM

#

ok ,thanks, it is very clear now

gaunt pasture Jul 26, 2020, 5:44 PM

#

is it possible to do something like UPDATE commtable SET {col} = ? IF EXISTS

tepid cradle Jul 26, 2020, 6:01 PM

#

what do you want to happen if it doesn't exist? @gaunt pasture

gaunt pasture Jul 26, 2020, 6:08 PM

#

#IF THIS GUILD DOES NOT HAVE DATA
        
        c.execute("INSERT INTO commtable (guild, " + arg1 + ") VALUES (?, ?)", (guild_id, arg2))
        
        #IF THIS GUILD HAS DATA ALREADY
        
        c.execute("UPDATE commtable SET {col} = ? WHERE guild = ?".format(col=arg1), (arg2, guild_id))

#

i tried

c.execute("IF NOT EXISTS (SELECT 1 FROM commtable WHERE guild = ?)", (guild_id))

but got this error:

  File "C:/Users/Nik/PycharmProjects/beatsbot/beats.py", line 1952, in db
    c.execute("IF NOT EXISTS (SELECT 1 FROM commtable WHERE guild = ?)", (guild_id))
sqlite3.OperationalError: near "IF": syntax error

#

@tepid cradle

tepid cradle Jul 26, 2020, 6:13 PM

#

I think the simplest way is to do a count. In pseudo code, this would be:
if count of guild > 0
then run update query
else
run insert query

gaunt pasture Jul 26, 2020, 6:14 PM

#

what is "if count of guild > 0" in not pseudo code 😅

#

cuz that's the issue i'm having rn

tepid cradle Jul 26, 2020, 6:14 PM

#

But if the column is a primary key, you can also use on conflict with replace
https://www.sqlite.org/lang_conflict.html#:~:text=The ON CONFLICT Clause&text=The ON CONFLICT clause is,therefore might not be familiar.

gaunt pasture Jul 26, 2020, 6:14 PM

#

i don't think it's a primary key

#

maybe it should be :p

#

again, first time working with this

tepid cradle Jul 26, 2020, 6:15 PM

#

cursor.execute("select count(*) from table_name where column = ?", (arg1))
value = cursor.fetchall()
if value[0][0] > 0:
    #update query
else:
    #insert query

gaunt pasture Jul 26, 2020, 6:19 PM

#

@tepid cradle

    c.execute("SELECT COUNT(*) FROM commtable WHERE guild = ?", (guild_id))
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 18 supplied.

tepid cradle Jul 26, 2020, 6:20 PM

#

I think your guild_id is a list

gaunt pasture Jul 26, 2020, 6:20 PM

#

?

tepid cradle Jul 26, 2020, 6:21 PM

#

The parameter that you're passing, guild_id. Is it int or list?

gaunt pasture Jul 26, 2020, 6:21 PM

#

📎 unknown.png

#

string

#

it's an int at first

#

just like 28572380509

#

and then i make it a string

tepid cradle Jul 26, 2020, 6:22 PM

#

Not sure what's happening in that case

#

Try running the queries in an interactive console

#

will help you figure out the error

gaunt pasture Jul 26, 2020, 6:23 PM

#

no clue what that means

tepid cradle Jul 26, 2020, 6:25 PM

#

Your IDE has a console, or you can run it in the command line interactively by entering the python console

#

You can also use Jupyter Notebook

#

I have to go, getting late here. Hope you figure it out, all the best

gaunt pasture Jul 26, 2020, 6:36 PM

#

alr

orchid yacht Jul 26, 2020, 6:50 PM

#

@hazy mango how do I change the port that it looks at? I am not using the default one

class MyBot(commands.Bot):
    """Custom class for bot."""
    def __init__(self):
        ...
        # Create postgreSQL database pool
        self.database_pool = self.loop.run_until_complete(asyncpg.create_pool(host="localhost", user="user",password="password", database="name"))

#

I currently have this but it's not adding anything to the tables

class MyBot(commands.Bot):
    """Custom class for bot."""
    def __init__(self):
        ...
        # Create postgreSQL database pool
        self.database_pool = self.loop.run_until_complete(asyncpg.create_pool(host="localhost", user="postgres", password="password", database="postgres"))


@bot.event
async def on_message(msg):

    punishment_logs = bot.get_channel(736628279267557396)
    embed = msg

    if msg.channel == punishment_logs and msg.author.bot == False:
        await MyBot.execute(f'''
            INSERT INTO postgres (userid, jump_url) VALUES ({embed.footer.text}), ({msg.author.id});
        ''')

orchid yacht Jul 26, 2020, 7:19 PM

#

Please ping me when you respond

sinful condor Jul 26, 2020, 8:33 PM

#

I have this code async def add_xp(users, user, exp): userguild = f'{user.id}/{user.guild.id}' db = sqlite3.connect('main.sqlite') cursor = db.cursor() cursor.execute(f"SELECT xp FROM users WHERE userguild = {userguild}") result = cursor.fetchone() expe = result[0] + exp sql = ("UPDATE users SET xp = ? WHERE userguild = ?") val = (expe, userguild) cursor.execute(sql, val) db.commit() and on the expe = line I am getting an error TypeError: 'NoneType' object is not subscriptable when I do expe = '2' it works with no errors is there something wrong with that line or with result or is there another problem please ping if you respond

wind sand Jul 26, 2020, 9:54 PM

#

Hello, I am having a bit of trouble with aiosqlite for python. The guys over at the discord.py server said you all could help.

worldly lotus Jul 26, 2020, 10:22 PM

#

@sinful condor you're trying to use the fetchone() function, which means that it will only find one. if you use fetchall() or fetchmany(size) it should work

#

@sinful condor https://pynative.com/python-cursor-fetchall-fetchmany-fetchone-to-read-rows-from-table/

sinful condor Jul 26, 2020, 10:24 PM

#

@worldly lotus now it says list index out of range

worldly lotus Jul 26, 2020, 10:24 PM

#

@sinful condor oh i know, it's that it didn't find any results

#

try maybe checking your execute line

sinful condor Jul 26, 2020, 10:25 PM

#

can we go into dms?

#

I have a few problems that I have with my database

#

I have been trying to convert this from json to sqlite

worldly lotus Jul 26, 2020, 10:26 PM

#

okay

#

i dont know much about dbs

opaque pulsar Jul 26, 2020, 10:40 PM

#

hello, i'm getting a strange error when attempting to do a migration in my django app. in my settings.py i have py DATABASES = { 'default': { 'ENGINE': 'django.db.backends.postgresql', 'NAME': os.environ['POSTGRES_DATABASE'], 'USER': os.environ['POSTGRES_USER'], 'PASSWORD': os.environ['POSTGRES_PASSWORD'], 'HOST': os.environ['POSTGRES_HOST'], 'PORT': os.environ.get('POSTGRES_PORT', '5432'), } }
my POSTGRES_HOST env var is set to ruby.db.elephantsql.com. when i run python manage.py migrate, i get: Traceback (most recent call last): File "/mnt/d/Python/formal/venv/lib/python3.7/site-packages/django/db/backends/base/base.py", line 220, in ensure_connection self.connect() File "/mnt/d/Python/formal/venv/lib/python3.7/site-packages/django/utils/asyncio.py", line 26, in inner return func(*args, **kwargs) File "/mnt/d/Python/formal/venv/lib/python3.7/site-packages/django/db/backends/base/base.py", line 197, in connect self.connection = self.get_new_connection(conn_params) File "/mnt/d/Python/formal/venv/lib/python3.7/site-packages/django/utils/asyncio.py", line 26, in inner return func(*args, **kwargs) File "/mnt/d/Python/formal/venv/lib/python3.7/site-packages/django/db/backends/postgresql/base.py", line 185, in get_new_connection connection = Database.connect(**conn_params) File "/mnt/d/Python/formal/venv/lib/python3.7/site-packages/psycopg2/__init__.py", line 127, in connect conn = _connect(dsn, connection_factory=connection_factory, **kwasync) " to address: Name or service not knownanslate host name "ruby.db.elephantsql.com
i'm not sure what happened to the error message on the last line, but that's what it gave me. here's the full error message, i left out most of it because it didn't fit: https://hasteb.in/puxefefe.sql

wind sand Jul 26, 2020, 10:46 PM

#

oh yeah... I forgot to ask my original question when I first made my comment. Sorry about that

opaque pulsar Jul 26, 2020, 10:50 PM

#

ok i believe it's just a weird formatting error reading from the env vars although it appears perfectly fine whenever i print them

#

yeah just an extra newline lol it's working now

wind sand Jul 26, 2020, 10:54 PM

#

ok, so... for python aiosqlite does anyone know why would cursor.fetchone() not be returning an single tuple when I run an query?
Everything else would start doing this behavior as well. This would only happen with a few queries. Anyone know how I can fix up this behavior?

minor zealot Jul 26, 2020, 11:35 PM

#

in sqlite3 i need a help to make my bot do something if the user is already existed in my data and if the user does not exist in my data the bot should do something else but i can't find a way to do it only this and it does not work

#

for record in records:

#

if record[0] == user_id:

#

print("true")

#

elif record[0] != user_id:

#

print("false")

tough needle Jul 27, 2020, 12:55 AM

#

Am in the process of switching from sqlite3 to aiosqlite, but I can't seem to ```py
import aiosqlite

#

NameError: name 'aiosqlite' is not defined

wind sand Jul 27, 2020, 12:57 AM

#

anyone??

#

hello??

#

@tough needle did you install it?

tough needle Jul 27, 2020, 12:59 AM

#

Yup

#

@wind sand

wind sand Jul 27, 2020, 1:03 AM

#

are you using an virtual envoiment? Double check to see if it is installed. You may of installed it to the wrong directory

proven arrow Jul 27, 2020, 1:03 AM

#

If it wasnt installed, it would probably give modulenotfound or import error

tough needle Jul 27, 2020, 1:04 AM

#

@wind sand seems right, can't find it in my intepreter

#

but how do I fix it

tough needle Jul 27, 2020, 1:32 AM

#

import aiosqlite
ModuleNotFoundError: No module named 'aiosqlite'

aiosqlite is installed, but could be the wrong intepreter. Anyone think they could help?

sharp magnet Jul 27, 2020, 1:52 AM

#

with connection:
            cursor.execute("""
            SELECT coins FROM users WHERE ? = userid
            """)
            x = cursor.fetchone()

#

How could I add a value to them coins?

#

^ Using sqlite3 btw

tough needle Jul 27, 2020, 1:56 AM

#

do you mean change the value of the row coins or?

#

change the value of coins per user

sharp magnet Jul 27, 2020, 1:56 AM

#

@tough needleChange the users coins

tough needle Jul 27, 2020, 1:56 AM

#

UPDATE coins

#

no wait

sharp magnet Jul 27, 2020, 1:56 AM

#

I'm trying to just make a simple "add coins" command atm

tough needle Jul 27, 2020, 1:57 AM

#

UPDATE users SET coins = ? WHERE userid = ?

sharp magnet Jul 27, 2020, 1:57 AM

#

So i'm trying to select there coins to check if they are in the database if they are then im trying to figure out how to use the selected coins + the added coins (this will be an admin command) and then update the database

#

def add_coins(self, connection, user_id, added_coins)
        with connection:
            cursor.execute("""
            SELECT coins FROM users WHERE ? = userid
            """)
            x = cursor.fetchone()
            if x:
                cursor.execute("""
                UPDATE users SET coins = ?
                """, ((x + added_coins),))```

#

Does that look about right?

#

I was planning on using "return" as a way to make my discord bot inform the user on the outputted coins and stuff.

tough needle Jul 27, 2020, 1:59 AM

#

currentpoints = getPoints(member.id)
newpoints = currentpoints + points
changePoints(member.id, newpoints)

I recommend something like this

dusky siren Jul 27, 2020, 2:00 AM

#

(Sqlite3)

c.execute("SELECT * from cards WHERE collectionid = {}".format(cardid))
this is throwing an error due to cardid containing a hyphen, since it's trying to find the column of the stuff before the hyphen. Is there any way I can fix this so that sqlite reads the string as a string and not reading the hyphen as a minus symbol?

sharp magnet Jul 27, 2020, 2:00 AM

#

def add_coins(self, connection, user_id, added_coins)
        with connection:
            cursor.execute("""
            SELECT coins FROM users WHERE ? = userid
            """)
            x = cursor.fetchone()
            if x:
                new_coins = x + added_coins
                cursor.execute("""
                UPDATE users SET coins = ? WHERE user_id = ?
                """, (new_coins, user_id))

#

@tough needle That looking about right?

tough needle Jul 27, 2020, 2:01 AM

#

Try it out

sharp magnet Jul 27, 2020, 2:01 AM

#

Well I could if I currently had a value in the database

#

.-.

#

I havnt figured out how to add the data into it so

#

eh

tough needle Jul 27, 2020, 2:02 AM

#

INSERT OR IGNORE INTO users VALUES (row1, row2...)

sharp magnet Jul 27, 2020, 2:02 AM

#

wdym "ignore into"

tough needle Jul 27, 2020, 2:04 AM

#

if there already is data we don't want to overwrite it

sharp magnet Jul 27, 2020, 2:04 AM

#

That's why I was checking "if x:"

#

So "else:" would mean it hasnt selected coins

#

meaning they are not in the database

#

right>?

#

📎 unknown.png

#

eh

#

nvm

#

^ I messed something up above it so

dusky siren Jul 27, 2020, 2:08 AM

#

(Sqlite3, apologies for repeating but I don't want this to be lost and I need help still)

c.execute("SELECT * from cards WHERE collectionid = {}".format(cardid))
this is throwing an error due to cardid containing a hyphen, since it's trying to find the column of the stuff before the hyphen. Is there any way I can fix this so that sqlite reads the string as a string and not reading the hyphen as a minus symbol?

sharp magnet Jul 27, 2020, 2:12 AM

#

@tough needle Any ideas?

📎 unknown.png

#

📎 unknown.png

#

I had to change all this

#

Because discord.py cant use global variables so

torn sphinx Jul 27, 2020, 2:17 AM

#

how many tables r required to represent a class with a list of tuples that have a length 2? one for the class and another one with columns for key, tuple[0] and tuple[1]?

sharp magnet Jul 27, 2020, 2:24 AM

#

  add_coins(user_id, added_coins)
RuntimeWarning: Enable tracemalloc to get the object allocation traceback```

#

^ Anyone any idea?

feral current Jul 27, 2020, 2:24 AM

#

await

sharp magnet Jul 27, 2020, 2:24 AM

#

Just mention me if you have an idea how to fix it

tough needle Jul 27, 2020, 2:24 AM

#

await Command.__call__

sharp magnet Jul 27, 2020, 2:24 AM

#

What?

feral current Jul 27, 2020, 2:24 AM

#

the error message is telling you

#

you need to await the call

tough needle Jul 27, 2020, 2:24 AM

#

Read the error

sharp magnet Jul 27, 2020, 2:25 AM

#

@bot.command()
async def add_coins(ctx, coins:int):
    await ctx.send('Testing.')
    user_id = ctx.author.id
    added_coins = coins
    add_coins(user_id, added_coins)

#This is a seperate py file Below
def add_coins(user_id, added_coins):
        connection = sqlite3.connect('maindb.db')
        cursor = connection.cursor()
        with connection:
            cursor.execute("""
            SELECT coins FROM users WHERE ? = userid
            """)
            x = cursor.fetchone()
            print (x)
            if x:
                new_coins = x + added_coins
                cursor.execute("""
                UPDATE users SET coins = ? WHERE user_id = ?
                """, (new_coins, user_id))
                if connection:
                    connection.close()

#

I did

#

I read the error and I dont understand

#

That's the 2 peices of code

#

I know the top parts discord.py stuff but I'm not sure where the problems being caused

tough needle Jul 27, 2020, 2:26 AM

#

read the error to find out

sharp magnet Jul 27, 2020, 2:26 AM

#

Eh?

📎 unknown.png

#

I feel like a fucking idiot

#

I don't know what the errors saying

tough needle Jul 27, 2020, 2:27 AM

#

connection = await sqlite3.connect('maindb.db')

sharp magnet Jul 27, 2020, 2:29 AM

#

📎 unknown.png

#

Bro what

#

😂

#

def add_coins(user_id, added_coins):
        connection = await
        sqlite3.connect('maindb.db')
        cursor = connection.cursor()
        with connection:
            cursor.execute("""
            SELECT coins FROM users WHERE ? = userid
            """)
            x = cursor.fetchone()
            print (x)
            if x:
                new_coins = x + added_coins
                cursor.execute("""
                UPDATE users SET coins = ? WHERE user_id = ?
                """, (new_coins, user_id))
                if connection:
                    connection.close()```

tough needle Jul 27, 2020, 2:29 AM

#

...

#

you can't put a new line

sharp magnet Jul 27, 2020, 2:30 AM

#

📎 unknown.png

#

That's what you did

tough needle Jul 27, 2020, 2:30 AM

#

📎 unknown.png

#

what

sharp magnet Jul 27, 2020, 2:30 AM

#

AH

#

😂

#

For me it's on 2 different lines

tough needle Jul 27, 2020, 2:30 AM

#

weird

sharp magnet Jul 27, 2020, 2:30 AM

#

discord.ext.commands.errors.ExtensionFailed: Extension 'cogs.database' raised an error: SyntaxError: 'await' outside async function (database.py, line 14)

#

:190: RuntimeWarning: coroutine 'Command.call' was never awaited
add_coins(user_id, added_coins)
RuntimeWarning: Enable tracemalloc to get the object allocation traceback

#

^ I added "async to it

#

async def add_coins(user_id, added_coins):
        connection = await sqlite3.connect('maindb.db')
        cursor = connection.cursor()
        with connection:
            cursor.execute("""
            SELECT coins FROM users WHERE ? = userid
            """)
            x = cursor.fetchone()
            print (x)
            if x:
                new_coins = x + added_coins
                cursor.execute("""
                UPDATE users SET coins = ? WHERE user_id = ?
                """, (new_coins, user_id))
                if connection:
                    connection.close()```

#

That's the code currently

tough needle Jul 27, 2020, 2:32 AM

#

then add await to add_coins in your command

sharp magnet Jul 27, 2020, 2:33 AM

#

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'int' object has no attribute 'send' what does it even mean by that

#

I havn't tried to give it the attribute send

#

eh

#

@bot.command()
async def add_coins(ctx, coins:int):
    await ctx.send('Testing.')
    user_id = ctx.author.id
    added_coins = coins
    await add_coins(user_id, added_coins)```

#

I know that's discord.py stuff but

tough needle Jul 27, 2020, 2:34 AM

#

show errormessage

sharp magnet Jul 27, 2020, 2:34 AM

#

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'int' object has no attribute 'send' what does it even mean by that

tough needle Jul 27, 2020, 2:34 AM

#

entire

#

I need more info

vital edge Jul 27, 2020, 2:34 AM

#

with mariadb (sql) I tried this and this doesn't work
I saw things similar working on websites and idk how to do it :(

📎 unknown.png

#

is this deprecated ?

sharp magnet Jul 27, 2020, 2:35 AM

#

Traceback (most recent call last):
  File AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
    ret = await coro(*args, **kwargs)
  File "", line 190, in add_coins
    await add_coins(user_id, added_coins)
  File \AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 354, in __call__
    return await self.callback(*args, **kwargs)
  File , line 187, in add_coins
    await ctx.send('Testing.')
AttributeError: 'int' object has no attribute 'send'

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

Traceback (most recent call last):
  File "C:\UsersAppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\bot.py", line 892, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 797, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\AppData\Local\Programs\Python\Python38-32\lib\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: AttributeError: 'int' object has no attribute 'send'```

#

eh why's that went bold?

#

@bot.command()
async def add_coins(ctx, coins:int):
    await ctx.send('Testing.')
    user_id = ctx.author.id
    added_coins = coins
    await add_coins(user_id, added_coins)``` It's probably caused by that 2nd arg

tough needle Jul 27, 2020, 2:37 AM

#

remove :int

sharp magnet Jul 27, 2020, 2:37 AM

#

It needs to be an int

tough needle Jul 27, 2020, 2:37 AM

#

shouldn't hurt

#

yeah well

#

if you as an admin know that

#

the bot doesn't

#

so if you type in integers

sharp magnet Jul 27, 2020, 2:37 AM

#

But the bot can't add strings together so

#

It therefore needs to be an int no?

tough needle Jul 27, 2020, 2:38 AM

#

then do int(coins) farther down

sharp magnet Jul 27, 2020, 2:38 AM

#

added_coins = int(coins) <= that should do

tough needle Jul 27, 2020, 2:38 AM

#

y

sharp magnet Jul 27, 2020, 2:39 AM

#

Same error without the "int"

tough needle Jul 27, 2020, 2:39 AM

#

then I'm out of ideas

#

try printing out ctx

sharp magnet Jul 27, 2020, 2:40 AM

#

That should have worked anyways?

📎 unknown.png

#

I just re checked the docs

#

and it's definitely a thing

tough needle Jul 27, 2020, 2:40 AM

#

sure

#

try printing out ctx

sharp magnet Jul 27, 2020, 2:41 AM

#

📎 unknown.png

normal igloo Jul 27, 2020, 2:41 AM

#

You pass user_id into the first parameter of add_coins

#

Which is ctx in that coroutine

#

That is your problem

sharp magnet Jul 27, 2020, 2:42 AM

#

In main I have:

@bot.command()
async def add_coins(ctx, coins):
    await ctx.send('Testing.')
    print(ctx)
    user_id = ctx.author.id
    added_coins =  int(coins)
    await add_coins(user_id, added_coins)```

In database.py in a cog I have
```python
async def add_coins(user_id, added_coins):
        connection = await sqlite3.connect('maindb.db')
        cursor = connection.cursor()
        with connection:
            cursor.execute("""
            SELECT coins FROM users WHERE ? = userid
            """)
            x = cursor.fetchone()
            print (x)
            if x:
                new_coins = x + added_coins
                cursor.execute("""
                UPDATE users SET coins = ? WHERE user_id = ?
                """, (new_coins, user_id))
                if connection:
                    connection.close()```

normal igloo Jul 27, 2020, 2:42 AM

#

Last line

#

Of add_coins

#

How do you expect that to work

sharp magnet Jul 27, 2020, 2:42 AM

#

wdym

#

connection.close?

normal igloo Jul 27, 2020, 2:42 AM

#

No of add_coins

sharp magnet Jul 27, 2020, 2:43 AM

#

heh?

normal igloo Jul 27, 2020, 2:43 AM

#

Don't have two functions named the same thing lmao

#

That is your problem

sharp magnet Jul 27, 2020, 2:43 AM

#

oh

tough needle Jul 27, 2020, 2:44 AM

#

I can't seem to be able to convert from sqlite to aiosqlite

sharp magnet Jul 27, 2020, 2:44 AM

#

Command raised an exception: NameError: name 'add_coins_db' is not defined

tough needle Jul 27, 2020, 2:44 AM

#

I can install aiosqlite, but once I try to import it it does not work, module aiosqlite does not exist

#

define it then @sharp magnet

sharp magnet Jul 27, 2020, 2:44 AM

#

async def add_coins_db(user_id, added_coins):
        connection = await sqlite3.connect('maindb.db')
        cursor = connection.cursor()
        with connection:
            cursor.execute("""
            SELECT coins FROM users WHERE ? = userid
            """)
            x = cursor.fetchone()
            print (x)
            if x:
                new_coins = x + added_coins
                cursor.execute("""
                UPDATE users SET coins = ? WHERE user_id = ?
                """, (new_coins, user_id))
                if connection:
                    connection.close()

#

I have

#

That's defining it no?

#

heh

#

.-.

#

¯_(ツ)_/¯

tough needle Jul 27, 2020, 2:45 AM

#

show other code

#

and remove async

sharp magnet Jul 27, 2020, 2:45 AM

#


@bot.command()
async def add_coins(ctx, coins):
    await ctx.send('Testing.')
    print(ctx)
    user_id = ctx.author.id
    added_coins =  int(coins)
    await add_coins_db(user_id, added_coins)```

#

discord.ext.commands.errors.ExtensionFailed: Extension 'cogs.database' raised an error: SyntaxError: 'await' outside async function (database.py, line 14)

#

It needs to be inside an async

#

no?

tough needle Jul 27, 2020, 2:46 AM

#

remove await

sharp magnet Jul 27, 2020, 2:46 AM

#

Command raised an exception: NameError: name 'add_coins_db' is not defined

#

Im so confused

#

¯_(ツ)_/¯

tough needle Jul 27, 2020, 2:46 AM

#

remove await

sharp magnet Jul 27, 2020, 2:46 AM

#

I did

tough needle Jul 27, 2020, 2:46 AM

#

¯_(ツ)_/¯

#

show code then

sharp magnet Jul 27, 2020, 2:47 AM

#

@bot.command()
async def add_coins(ctx, coins):
    await ctx.send('Testing.')
    print(ctx)
    user_id = ctx.author.id
    added_coins =  int(coins)
    add_coins_db(user_id, added_coins)
``` ^ That's from main

```python
def add_coins_db(user_id, added_coins):
        connection = sqlite3.connect('maindb.db')
        cursor = connection.cursor()
        with connection:
            cursor.execute("""
            SELECT coins FROM users WHERE ? = userid
            """)
            x = cursor.fetchone()
            print (x)
            if x:
                new_coins = x + added_coins
                cursor.execute("""
                UPDATE users SET coins = ? WHERE user_id = ?
                """, (new_coins, user_id))
                if connection:
                    connection.close()
``` that's in a cog

tough needle Jul 27, 2020, 2:48 AM

#

show error

sharp magnet Jul 27, 2020, 2:49 AM

#

Traceback (most recent call last):
  File "C:\UsersAppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
    ret = await coro(*args, **kwargs)
  File  line 191, in add_coins
    add_coins_db(user_id, added_coins)
NameError: name 'add_coins_db' is not defined

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

Traceback (most recent call last):
  File "C:\UsersAppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\bot.py", line 892, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 797, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\UsersAppData\Local\Programs\Python\Python38-32\lib\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: NameError: name 'add_coins_db' is not defined```

tough needle Jul 27, 2020, 2:49 AM

#

what is your cog file called

sharp magnet Jul 27, 2020, 2:50 AM

#

initial_extensions = ['cogs.example','cogs.database']

if __name__ == '__main__':
    for extension in initial_extensions:
        try:
            bot.load_extension(extension)

        except Exception as e:
            print(f'Failed to load extension {extension}', file=sys.stderr)
            traceback.print_exc()```

#

database.py

tough needle Jul 27, 2020, 2:50 AM

#

is it a class?

sharp magnet Jul 27, 2020, 2:50 AM

#

yep

tough needle Jul 27, 2020, 2:50 AM

#

that's the issue

#

whats the class called

sharp magnet Jul 27, 2020, 2:50 AM

#

heh

#

📎 unknown.png

#

?

#

database

tough needle Jul 27, 2020, 2:51 AM

#

then typ database before add_coins_db

sharp magnet Jul 27, 2020, 2:51 AM

#

heh

tough needle Jul 27, 2020, 2:51 AM

#

database.add_coins_db