databases

[tepid crow]

@wraith trail do you know what is going on with my error?

im kinda confused

[atomic warren]

@wraith trail same error

[wraith trail]

@atomic warren Don;t know then ,sorry

[atomic warren]

k np

[somber falcon]

@wraith trail hey so hmm im using postgreSQL and getting some errors and idk why so can you help me figure out whatare causing the errors?

the part of code which is giving the error btw its kind've discord.py related but the error is from the database

@commands.Cog.listener()
    async def on_message(self, message):
        if message.author == self.client.user:
            return

        if message.author.bot == True:
            return

        author_id = str(message.author.id)
        guild_id = str(message.guild.id)

        user = await self.client.pg_con.fetch("SELECT * FROM users WHERE user_id = $1 AND guild_id = $2", author_id, guild_id)

        if not user:
            await self.client.pg_con.execute(f"INSERT INTO users (user.id, guild_id, lvl, exp) VALUES ($1, $2, 1, 0)",
                                          author_id, guild_id)

        user = await self.client.pg_con.fetchrow("SELECT * FROM users WHERE user_id = $1 AND guild_id = $2", author_id, guild_id)

        await self.client.pg_con.execute("UPDATE users SET exp = $1 WHERE user_id = $2 and guild_id = $3", user['exp'] + 1,
                                      author_id, guild_id)

        if await self.lvl_up(user):
            await message.channel.send(f"Congrats {message.author.mention}! You leveled up to {user['lvl'] + 1}")

[wraith trail]

Sorry I can't right now I'm afraid - , hopefully someone else can help

[somber falcon]

oh okay

[tepid crow]

(author_id, guild_id)

i think needs to be inside ()

[wraith bear]

Hello I'm looking for a very simple "database" that just allows me to put in objects with date, and search through them using dates. Objects contain lists and handling of dates and such in SQL turns me away from it (I have no need whatsoever for a relational database anyway).

Hopefully I'd like a very simple system that saves on disk and doesn't require an external service (like MongoDB)

Any ideas?

I could roll out my own solution since it's really simple, but I'd rather use a proven system and not have to do memory caching and all by myself and risk screwing it up when there is no need

[modest matrix]

Maybe tinydb or shelve?

[wraith bear]

I tried tinydb and had some weirdly horrible performances (0.5 seconds to insert a single document)

Haven't heard of shelve though, I'll check it out thanks

[harsh pulsar]

theres also dbm

[torn sphinx]

Cant' you just pickle the object?

[harsh pulsar]

Pickle bad

[tepid crow]

using asyncpg

 @commands.command()
    async def warn(self, ctx, name, *, reason):
        #await self.client.pg_con.execute('SELECT * FROM Warning WHERE NAME = %s', (name,))  ##self.client.pg_con.execute()
        rows = await self.client.pg_con.fetch('SELECT * FROM Warning WHERE NAME = $1', (name,))
        if len(rows) == 0:
            await self.client.pg_con.execute('INSERT INTO Warning (NAME, REASON1) VALUES ($1, $2)', (name, reason))
            await ctx.send(f'Katawrithike sthn bash dedomenwn {name} kai {reason}', delete_after=10)

when im running the command

im getting that error DataError: invalid input for query argument $1: ('Aggelos',) (expected str, got tuple)

im just doing (prefix)warn Aggelos vdm

[lapis oriole]

@tepid crow with asyncpg your arguments must be unpacked

it's not py await db.smtg(query, (a, b))
but py await db.smtg(query, a, b)

[torn sphinx]

It's taking my input as a string rather than an actual input and its annoying, iv'e searched for solutions but none worked...

[misty zenith]

can someone please help me with a database question? I'm still pretty new to Python (programming in general) and i honestly don't know what to do anymore

i have this data base with 4 different tables. from every table i need to get some information from the year 2014 but when i print my fetch all. i get every result over a 100 times. my query:
'''SELECT b.Besteldatum, k.Achternaam, B.Aantal, a.Prijs
FROM Bestellingen as b, klanten as k, Bestelregels as B, Artikelen as a
WHERE Besteldatum > 2014 AND Besteldatum < 2015;

this is my DB

[jade dune]

@misty zenith try WHERE Besteldatum BETWEEN 2014 AND 2015;

also use a join on tables

because im pretty sure youre just getting every single combination from all tables

[tepid crow]

@lapis oriole if i have this

await self.client.pg_con.execute('INSERT INTO Warning (NAME, REASON1) VALUES ($1, $2)', (name, reason))

it should be like that:

await self.client.pg_con.execute('INSERT INTO Warning NAME, REASON1 VALUES $1, $2', name, reason)

?

[lapis oriole]

Yeah, exactly

[tepid crow]

and one more thing

[lapis oriole]

yeah ?

[tepid crow]

do you see the fetch method on my code?

[lapis oriole]

yeah

[tepid crow]

(name,) this ,

will stay there?

[lapis oriole]

No

You just input is as a normal arg

[tepid crow]

oh alright then let me try that

[lapis oriole]

Because, internally, asyncpg uses *args

[tepid crow]

okey give me a min

Thanks mate

all works now ^^

[old raven]

Hey, I want to adjust this code so that I can grab the ChannelID from one mongodb cluster and then send the xp and level up messages to that channel. I already made a command where the user can set the channel for xp messages (+channel {channel_id}) and I had that ID sent to a collection called msg. I’m stuck on the part where I grab the ID from the collection and send the xp messages to that channel. Can someone help me?

Code:
https://paste.pythondiscord.com/nibowupico.py

[vale badger]

What is this error?

[keen sundial]

Maybe the string field cannot support utf-8 emoji (that is 🌱)

Looks like a mysql error. https://stackoverflow.com/questions/39463134/how-to-store-emoji-character-in-mysql-database

[lilac fulcrum]

Is it possible to just select one column in SQLite3?

Like fetchone?

[lapis oriole]

How can I, with asyncpg, run an INSERT statement with a RETURNING clause ?

Because running tag_id = await db.fetchval("INSERT INTO public.tags VALUES ($1, $2, $3, $4) RETURNING id", ctx.author.id, ID, name, content) raises an error : PostgresSyntaxError : syntax error at or near "$1" Even if the statement IS executed

[cloud tundra]

Influxdb: how do I increase a value at insert?

[lapis oriole]

Like an index @cloud tundra ?

[cloud tundra]

I'm actually way over my head here. I have stored measurement:messages, tags: chan_id, chan_name, fields: author, nick.
I want to count total messages, total messages in different chans, top chans, top users, etc.
think I see the problem in the wrong perspective

[lapis oriole]

Then just fetch all with, for example, same chan_id -> len, etc...

[cloud tundra]

do you have time for a pm?

[lapis oriole]

Yeah @cloud tundra

[inner raven]

if I have a db with (name, id), is there a big difference between fetching all rows then checking if id in [x1,...,xy] on the results vs something likeselect * from table where id in (x1, ..., xy)?

[tepid crow]

i think its the same thing

[inner raven]

End result would probably be the same but is there anything that might make one better than the other or would that be library specific

[tepid crow]

2nd method but im not sure ^^

wait for someone else

[golden wadi]

whats the best practice about using cursors over a database connection (PGSQL) - like one for every command?

[past widget]

Is learning databases hard

?

I want to use SQLITE

where can I learn?

[tepid crow]

youtube

[proven arrow]

where can I learn?
@past widget This site is good: https://sqlbolt.com/

[past widget]

ty @tepid crow @proven arrow

[tepid crow]

np:D

[golden wadi]

im using psycopg2 to insert data into a table

there arent any errors, and querying the table using psycopg2 shows me its there

but when i use my local psql client its always empty 🤔

also when i restart the application they're gone

is psycopg2 caching something?

[runic pilot]

are you committing after you insert?

make sure you commit your changes

[surreal gyro]

hello

i needed some help

[fallow mist]

Could someone please help me create a DB for my bot, I have the code already but it's all in .json files for the user data and i have 1000+ users in my server, and now it's a problem. I need a database but i have absolutely no idea how to work around that. Please msg me if you voluntarily have time to help me around. It is running python.

[molten vector]

use either sqlite or mysql

then probably sqlalchemy

[fallow mist]

is that to me?

[molten vector]

u

[fallow mist]

Yeah, I figured they needed to be used, but again I have no idea how to really use them which is why I asked if anyone voluntarily has time to help around

[molten vector]

you're in a help server

so step 1: get a database up and running

[fallow mist]

Mm i understand 😄

[deft sapphire]

Dumb database-related question; I'm using cx_Oracle, but I need to guarantee a module oracle/instantclient/12.1.0.2.0 loads before I run my script else it complains of a file libclntsh.so being missing

This is for Pycharm

[torn sphinx]

Hey ! I post this message in this channel because i'm pretty sure the problem I have is not related to discord.py but to my gestion of dictionnaries and databases.

I'm creating a game bot where all the player data is contained into a dictionnary. This dictionnary have a key for each user id. The value of this key is another dictionnary that contains the stats of the player.
To store the data when the bot goes down, I use a JSON file.

When I launch the bot, This code runs :

database = {}
with open ("database.json") as f:
    database = json.load(f)

the dictionnary take the value of the one described into the file.

Whenever i want to save the dictionnary into the file, i run this code :

with open('database.json', 'w') as f:
    json.dump(database, f)

the file is emptyed and take the value of the dictionnary.

Everything with this seems fine : the data is stored into the file, and when i launch the bot again the dictionnary takes the good value.

BUT

whenever i want to modify the dictionnary, to change a player's stat for example. It does not modify the dictionnary, but creates a new entry with the default values i set (with the same key, idk how is this even possible) and stores the new value into this second entry.
When i save the database into the file and load it again, the first entry is deleted and only the second one is keeped, which is bad because it cancels all the changes that could have been done in the first place...

Do you know why is this happening ? I remember using the exact same code month ago on different projects and it was working just fine !
Also, I'm currently using JSON as prototyping purpose before I learn something new (I know it's bad)

also, here is my full code (please note that the token of the bot has been changed) : https://pastebin.com/7JS9LJJb

[woven knot]

https://www.youtube.com/watch?v=CSHx6eCkmv0
I am learning Flask by following Corey Schafer's Flask Tutorial and am having trouble writing the newly 'registered users' to my sqlitedb. The SQLite DB file is created, I can register a user, and no traceback error appears. specifically, my error varies from the video tutorial when registered user is not appearing in the DB like in 9:58. instead, I receive a None value.

the question: why isn't the newly registered user not appearing in the SQLite db?

[strange onyx]

#discord-bots

[frosty magnet]

Hey, Guys, Hope you all well ,I wanna ask ,which certification to get for a database/data analysis relate role ? I'm looking at Microsoft certifications they all retiring soon ,Is there any other cert you can recommend ? Thanks .

[torn sphinx]

@strange onyx my problem is not related to the lib. That's why i'm posting it in there

If you know what's wrong, please tell me

[tawny sail]

I have a discord bot and a website hosted on a single droplet on digital oceans, I want data which is to be accessed by the bot and not the site(maybe later in the future) and I will have to store it in the database, So should I use the database which is being used by the site (sqlite db) and use an API to exchange information with the bot or make an entire new DB for the bot?
I kinda wanna use the django ORM for this, If i go with the former option, how much performance will it cost me compared to the latter?

[torn sphinx]

1st option

I’m too lazy to type it

[tawny sail]

api option u mean?

[torn sphinx]

Ye

[tawny sail]

okay, any specific reasons for choosing option 1?

[torn sphinx]

No history with django

@tawny sail I just use an API all the time I find it easy to use one

[tawny sail]

okay

[torn sphinx]

Sorry for bad answer tbh

[oak schooner]

yo, i created a bot and id like to know that if this is the right way to store user info,
i have to store everyones info upon guild join for som reasons
and so i cant store info of each user in a sep mongodb doc
im kinda scared this might be a problem when the bot joins huge servers

[gaunt frigate]

Having some issue with anytime i try to insert something into a db

> add_watchlister(name="Zzz9194",note="Criminal",agent=304989797485903873)
> Traceback (most recent call last):
>   File "<stdin>", line 1, in <module>
>   File "dbcmds.py", line 36, in add_watchlister
>     cursor.execute(command)
> sqlite3.OperationalError: no such column: Zzz9194

Using sqlite3
This is my code

def add_watchlister(name: str, note: str, agent: int):
    with sqlite3.connect('watchlist.db') as connection:
        cursor = connection.cursor()
        command = "INSERT INTO " + table_name + f" VALUES ({name},{note},{agent})"
        cursor.execute(command)
        connection.commit()

    return True

[hazy mango]

A) You shouldn't use f-strings within sql statements (you should use $x / ? syntax)

B) What's does command actually become? (print it)

@gaunt frigate

[brazen charm]

SQLite uses the ? placeholders instead of the $x sytle like postgres just btw

[tribal trench]

No other differences?

I want to change a sqlite3 code into postgresql

[brazen charm]

if youre going from sqlite to postgres the diffrence isnt much no

you do however have to check datatypes

[tribal trench]

I kept everything as text in sqlite3

For simplicity

[brazen charm]

as postgres has alot more and (like i mentioned before uses the $1, $2, $3 placeholder system, unless its psycopg2 which wants to be special)

[tribal trench]

Yeah psycopg2

[brazen charm]

psycopg2 does this weird system of instead of the $ system it uses standard python formatting as the place holders (%s)

and then it formats those

which is a really weird and imo rather dangerous

[tribal trench]

what about asyncpg?

[brazen charm]

that does use the standard place holder system

of $ for example

[tribal trench]

Everyone says its better

[brazen charm]

because it is

in reality

[tribal trench]

I feel it hard to change from sqlite3

[brazen charm]

If i use postgres is mostly async anyway but asyncpg is much less scuffed than psycopg2

it wont be that hard

[tribal trench]

There is no cursor

Or something like that

?

[gaunt frigate]

@hazy mango Do you have anywhere i can read up on that syntax

[brazen charm]

no theres a cursor still

@gaunt frigate https://www.sqlitetutorial.net/sqlite-python/

[tribal trench]

I will read the documentation and try once more

[gaunt frigate]

@hazy mango

>>> add_watchlister("Zzz9194","Bad",304989797485903873)
INSERT INTO Watchlist VALUES (Zzz9194,Bad,304989797485903873)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "dbcmds.py", line 37, in add_watchlister
    cursor.execute(command)
sqlite3.OperationalError: no such column: Zzz9194

The command comes out as INSERT INTO Watchlist VALUES (Zzz9194,Bad,304989797485903873)

I meant for the $x syntax @brazen charm

[hazy mango]

Try specifying the columns

[gaunt frigate]

With a = or :

wait let me read it

[hazy mango]

INSERT INTO Watchlist(column1, column2, column3) VALUES (value1, value2, value3)

[gaunt frigate]

Oh ok

Also do you know where I can read on the $x syntax wtv it is

[brazen charm]

Sqlite doesnt use that syntax

it uses ? as the place holders not $

[hazy mango]

^^

[gaunt frigate]

so like in js?

`Hello ${my_var}`

[hazy mango]

So cur.execute("INSERT INTO ? (column1, column2, column3) VALUES (?, ?, ?)", (table, value1, value2, value3))

[brazen charm]

no

[gaunt frigate]

Ohh

ok

This error

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "dbcmds.py", line 35, in add_watchlister
    cursor.execute("INSERT INTO ? (name, note, agent) VALUES (?, ?, ?)", (table_name, name, note, agent))
sqlite3.OperationalError: near "?": syntax error

[brazen charm]

cant do ? for table names

only values

[gaunt frigate]

ah

Ay thanks it works like a charm

2nd Question ;~;

cursor.execute("SELECT * FROM Watchlist WHERE agent = ?", (agent))

ValueError: parameters are of unsupported type

agent comes as a str type, is it supposed to be int?

The params are

(agent: int):

[reef hawk]

are you trying to say that it doesn't exist?

why do you have a ? in ur thing, it's not a string either if that's what you're looking for

[unreal tangle]

I have a MySQL table with the following structure:

        CREATE TABLE IF NOT EXISTS `player_inventory` (
          `player_id` INT(10) UNSIGNED NOT NULL,
          `map_id` INT(10) UNSIGNED NOT NULL,
          `type` ENUM('INVENTORY','BANK','INBOX') NOT NULL,
          `item_id` INT(10) UNSIGNED NOT NULL,
          `amount` INT(10) UNSIGNED NOT NULL,
          PRIMARY KEY (`player_id`, `map_id`, `item_id`, `type`),

Here is some example data:
https://xavie.ru/i/1G2Jlx4.png

I want to combine every map_id together-- to become map_id=0

But In doing that, I want the other parts of the primary key to get combined

Essentially I want to go from the top to bottom

I could easily do this with code

But I need a SQL-only solution for this

Any insight on how to do this without looping over every row?

[grand estuary]

@unreal tangle You will need to use the GROUP BY clause and aggregate function SUM.

SELECT
    PLI.`player_id`,
    0 AS `map_id`,
    PLI.`type`,
    PLI.`item_id`,
    SUM(PLI.`amount`) AS `amount`
FROM
    `player_inventory` PLI
GROUP BY
    PLI.`player_id`,
    PLI.`type`,
    PLI.`item_id`
;

[pure cypress]

I think you need to group by the item id and type too, no?

[grand estuary]

Yes you are right, thankyou. I shall ammend.

[unreal tangle]

Oh I never played around with group by, thank you

[torn sphinx]

I have a simple query: UPDATE MyTable SET MyColumn = NULL WHERE Column1 = $1 AND Column2 = $2 RETURNING MyColumn
And I'm using asyncpg.

How can I know whether 1) There's a row that matches these conditions. 2) Whether the value has been updated to NULL or was NULL?

[pure cypress]

I don't think you can quite know that. You could at least find out if the update affected the row

I'm not sure if it would still consider it updated even if the new value is the same as the old

https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

Well, of course you can know that if you just do select queries before/after

[torn sphinx]

Oh I see, thanks

[dusty helm]

someone could help me out on modeling my db?

i understand the logic, but can't apply, some concepts are shady to me

and a reference of tool for visual modelling would be handy

[past widget]

uh

sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.

  cursor.execute(f"INSERT INTO warns (memId, reason, date) VALUES (?, ?)", (member.id, reason, datetime.date))

pls help

[pure cypress]

Don't you need three placeholder question marks?

You only have 2, but are trying to use 3 variables

[past widget]

wdym?

oh

nah

still same error

I'll send the schema

memId TEXT, reason TEXT, date TEXT

I tried memId as int

same for date

but still the same

[pure cypress]

I guess you need to convert them to strings since your columns are all text

[past widget]

huh?

Convert which?

[pure cypress]

The things in your tuple

They need to be strings

str(member.id) and so on

[past widget]

yep

I did that ty

But btw

the reason is being stored with

('',)

how do I remove those?

[pure cypress]

What is the type of reason before it's converted to a string?

Based on what you showed, it may be a tuple. Is that right?

[past widget]

Yep

It's an arg in the async

[pure cypress]

If it's a tuple then use reason[0] to get the first value out of it

[past widget]

👍 ty

uh, lol

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

cursor.execute('SELECT memid, reason, date FROM warns WHERE memid = ?', str(member.id))

[pure cypress]

You need to put it in a tuple, even if there's only 1 item you want to substitute

(str(member.id),)

(the comma at the end is important)

[past widget]

oh

I'm sorry, I'm really new to dbs, and I've just finished the sqlbolt lessons

Btw, I still get the same error

I added the paranthases and the comma

but still get the same error

[pure cypress]

Can you show your code?

[past widget]

sure

this is the part that is getting the error

  db = sqlite3.connect('warns.sqlite')
  cursor = db.cursor()
  cursor.execute('SELECT memid, reason, date FROM warns WHERE memid = ?', (str(member.id)),)
  result = cursor.fetchone()

[pure cypress]

Move the comma to the left once, so it's inside the parenthesis

[past widget]

it'll be outside

like, outside the cursor.execute

oh sorry

[pure cypress]

I said left, not right

[past widget]

I thought you meant right 😂

now i get ValueError: parameters are of unsupported type

[pure cypress]

🤔

It's supposed to look like this

cursor.execute('SELECT memid, reason, date FROM warns WHERE memid = ?', (str(member.id),))

[past widget]

yep, it is like this

cursor.execute('SELECT memid, reason, date FROM warns WHERE memid = ?', (str(member.id),))

[pure cypress]

Yeah that looks right

I'm not sure what is wrong

[past widget]

hmmm

[pure cypress]

Are you sure the error is coming from that line?

[past widget]

I'll double check

oh, I'm so sorry lol, it was the line underneath, which was the exact same

like the really exact same

Btw, when you told me to use result[0]

it only stored the first word

[pure cypress]

So the value error is fixed?

[past widget]

Yeah

Ty

[pure cypress]

reason has many words in it?

It's not just a single string?

Is it like reason = ("hello", "world", "good", "bye")?

[past widget]

Well, it is storing it like that, which I don't want

it may a small sentence

[pure cypress]

If it's like that, you can join them together

" ".join(reason)

Puts a space between each word

[past widget]

Will it be:
This is a reason
Instead of
('This', 'is', 'a', 'Reason')

[pure cypress]

Yes

[past widget]

Well, that stored 1 word

with s p a c e s

[pure cypress]

Are you still using [0]?

[past widget]

nope

When I add the [0]

it only stores 1 letter now

[pure cypress]

I feel like things are not what you say they are

!e py reason = ('This', 'is', 'a', 'Reason') joined = " ".join(reason) print(joined)

[delicate field]

@pure cypress :white_check_mark: Your eval job has completed with return code 0.

This is a Reason

[past widget]

str(" ".join(reason[0]))

[pure cypress]

As you can see, that does work

[past widget]

This returns one letter

str(" ".join(reason))

The returns a word with s p a c e s

[pure cypress]

That means reason is just one word

Not a tuple of words like you originally said

[past widget]

I don't want it a tuple

And the reason may be a sentence

or a word

[pure cypress]

So it's never a tuple?

Cause you said it stores it like a tuple

[past widget]

reason is an arg in the function

async def warn(ctx, member:discord.Member, reason:str):

Cause you said it stores it like a tuple
Yep, it does store it like a tuple

[pure cypress]

How does that happen if it's defined as a str?

[past widget]

idk

I questioned myself the same

since I already defined it

[pure cypress]

But when you tried to join it, it was a string

Where are you seeing that it's a tuple?

[past widget]

When I don't use join

it stores it ('like, 'this')

[pure cypress]

Can you do print(type(reason))?

[past widget]

okie

<class 'str'>

[pure cypress]

So, it's definitely not a tuple

[past widget]

then why is it doing so?

[pure cypress]

I dunno

Try ctx, member:discord.Member, *, reason:str

[past widget]

jst to make it clear

here is the full line

  cursor.execute("INSERT INTO warns (memid, reason, date) VALUES (?, ?, ?)", (str(member.name), " ".join(str(reason)), str(datetime.now().strftime("%b/%d/%Y, %X"))))

okie

so, I did add the * in the arg

and now it shows the full

ty

[pure cypress]

👍

[rough hearth]

I have a program (consumer software with a GUI, not a python program) that's basically a window into an sqlite database

and you can store files in it and stuff.

I tried opening the database in the database tool in pycharm so I can see how it's structured

it's supposed to be encrypted though

I don't see anything interesting but I also didn't get prompted to enter a password

[past widget]

It is sending "done" but not deleting the row

    cursor.execute("DELETE FROM warns WHERE warnid = ?", (str(warnnum),))
    await ctx.send("Done")

anyone knows a possible solution?

[torn sphinx]

did you forget to connection.commit()

[past widget]

oh

ty

[barren fiber]

Is there an easy way to stay connected to a MySQL server? I'm having the problem of my bot just not working after long periods of inactivity since it disconnects

[stable pilot]

hey guys

can aanyone please help me

    @commands.command()
    @commands.check(channelsList.check_channel)
    async def linkaccount(self, ctx):
        userint = ctx.author.id
        conn = await DiscordConnection.get_conn()
        async with conn.cursor() as mycursor:
            check = "SELECT COUNT(*) FROM wallet WHERE discordID = %s"
            await mycursor.execute(check, (userint,))
            myresult = await mycursor.fetchone()

        print("result:", myresult)
        print(ctx.author.id)

        if myresult[0] > 0:
            await ctx.send('`Your account is already in the system.`')
            print('already present')
        else:
            sql = "INSERT INTO wallet (discordID) VALUES (%s)"
            val = (ctx.author.id)
            async with conn.cursor() as mycursor:
                await mycursor.execute(sql, val)
                await conn.commit()
            print('created')
            await ctx.send(
                '`Congratulations your account has now been created, to start gambling message a staffmember`')

i have this code

https://i.imgur.com/1x5Pk8a.png

it gives me this error

[past widget]

Can I do so?

        db = sqlite3.connect('./assets/warns.sqlite')

[tribal trench]

yes

[torn sphinx]

File "/root/Tomori/plugins/guild.py", line 70, in welcomeChannel
self.cur.executescript(sql)
sqlite3.OperationalError: near "ON": syntax error

sql is

INSERT INTO guilds(id, welcomeChannel) VALUES({ctx.guild.id}, {channel.id})
        ON CONFLICT(id) DO UPDATE SET welcomeChannel={channel.id};```

and my sqlite3 version is 2.6.0

why do i get this err

[gaunt frigate]

In my function when I run a SELECT * FROM Watchlist

I get this error
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: no such table: Watchlist

However when I run the command on the CLI
sqlite> .tables Watchlist

with sqlite3.connect('watchlist.db') as connection:
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM Watchlist WHERE name = ?",(name))
        connection.commit()
    
        return cursor.fetchall()

[tepid crow]

W

not w

watchlist.db

ur table is named Watchlist

@gaunt frigate

[tribal trench]

conn = sqlite3.connect('./db/welcome.sqlite')
cur = conn.cursor()
cur.execute(f"SELECT channel_id FROM welcome WHERE guild_id = {ctx.guild.id}")
results = cur.fetchone()
if results is None:
sql = ("INSERT INTO welcome(guild_id, channel_id) VALUES (?,?)")
val = (ctx.guild.id, channel.id)
await ctx.send(f'**Welcome Channel has been set to: ** `{channel.mention}`')
elif results is not None:
sql = ("UPDATE welcome SET channel_id = ? WHERE guild_id = ?")
val = (channel.id, ctx.guild.id,)
await ctx.send(f'**Welcome Channel has been updated to: **  `{channel.mention}`')
cur.execute(sql, val)
conn.commit()
cur.close()
           conn.close()```
How do I convert thos into asyncpg?

[tepid crow]

create a pool

and then read documention from asyncpg

you will find everything here

https://magicstack.github.io/asyncpg/current/

[tribal trench]

I have read that

[tepid crow]

it has a build in method for execute

[tribal trench]

and got error too many connections

[tepid crow]

did you create a pool?

[tribal trench]

Yeah

I created a pool in main file

[tepid crow]

show me that part of code

if possible

[tribal trench]

async def create_db_pool():
    client.pg_con = await asyncpg.create_pool(host="ec2-5m", database="d", user="h", password="21")

client.loop.run_until_complete(create_db_pool())

[tepid crow]

alright

perfect

now

no need to do that conn = sqlite3.connect('./db/welcome.sqlite')

[tribal trench]

ohh

I did that with host and other stuffs

[tepid crow]

are you working with cogs?

[tribal trench]

Yeah

[tepid crow]

alright

you dont need to get a cursor if you use asyncpg module you can just await self.bot.pg_con.execute()

[tribal trench]

If I used the code in main file will it work on cogs?

[tepid crow]

what do you mean

[tribal trench]

you dont need to get a cursor if you use asyncpg module
@tepid crow
I already got that error

[tepid crow]

and you need to await them

[tribal trench]

what do you mean
@tepid crow
I meant if I used pool on bot.py , will it work on cogs

[tepid crow]

ofc

can u show me ur __init__ ?

[tribal trench]

           self.client = client```

[tepid crow]

alright

now lets look in your command

remove the connection thing

u dont need it anymore

and the cursor

[tribal trench]

ok removed

[tepid crow]

alright

[tribal trench]

how do I execute?🤯

it was cur.execute

[tepid crow]

what was cur?

ur cursor

[tribal trench]

conn.cursor()

[tepid crow]

now you can just await self.client.pg_con.execute()

[tribal trench]

ohk

[tepid crow]

client.pg_con this is what you defined your pool

[tribal trench]

yes

[tepid crow]

no need to commit the changes anymore

asyncpg does it

[tribal trench]

results = cur.fetchone()
fetchone isn't available in asyncpg?

[tepid crow]

no

give me 1 sec

[tribal trench]

ok

[tepid crow]

lets take a look here

read what fetch and fetchrow does

[dusty helm]

guys, someone could help me with one doubt?

[tepid crow]

sure

!ask

[delicate field]

Asking good questions will yield a much higher chance of a quick response:

• Don't ask to ask your question, just go ahead and tell us your problem.
• Don't ask if anyone is knowledgeable in some area, filtering serves no purpose.
• Try to solve the problem on your own first, we're not going to write code for you.
• Show us the code you've tried and any errors or unexpected results it's giving.
• Be patient while we're helping you.

You can find a much more detailed explanation on our website.

[dusty helm]

i have one table "Carriers" that should store all kinds of cargo carriers,

and i have a table "Schedule" that has 2 columns (intl_carrier_id) and (national_carrier_id).

how can i link both carriers to the carrier table?

and which kind of relationship would be used

idk why, but this is being tricky, i started with databases 2 weeks ago, but during the week i have to work and i'm not being able to put more energy at this

i mean

i have one table "Carriers" that should store all kinds of cargo carriers,

and i have a table "Schedule" that has 2 columns (intl_carrier_id) and (national_carrier_id).

how can i link both carriers to the carrier table?
@dusty helm

the result should be something like:

| name

1 | american airlines
2 | ...
3 | ...
4 | ...
5 | John Doe's Truck & co.

intl_carrier_id = 1
national_carrier_id = 5

the code i tried is not very usable, i tried to make 2 connections, but i did it poorly, idk if i need two relationships or just 1

@tepid crow

[tepid crow]

you can fetchall

and get the indexes from the list

if i understand correctly what are you trying to do

[dusty helm]

idk what that is

could you give a glimpse?

i found an example that should do, but it's good to learn more ways of doing it

https://stackoverflow.com/questions/31804710/two-columns-joining-the-same-table-with-sqlalchemy

[tribal trench]

asyncpg.exceptions.TooManyConnectionsError: too many connections for role "uvccn
csi"```

I am getting this error still

[tepid crow]

too many connections

close all the connections

from dashboard

and try again

@dusty helm i mean do you just want to get the columns from those tables and print them?

[dusty helm]

no i want it to be stored

as values

i have

| name | type

1 | american airlines | airline
2 | truck company lt | truck company

and i want to be able to get the values

2 values from this table to one table

[tepid crow]

alright

SELECT the data from the first table

and INSTERT them to the other

[tribal trench]

I could only connect when I remove this client.loop.run_until_complete(create_db_pool())

[brazen charm]

because run_until_complete is blocking

[past widget]

um

I need quick help please

I am in a bit hurry

So, I have a warn command,
which basically stores the member name, the reason, the date, the one who warned
however, when someone gets warned it stores the data like this:
| Name | Reason | Date | Warner |
however, I want to make a column, so when someone gets warned, if it's the first time, it be stored like this:
| Name | Reason | Date | Warner | 1 |
1 represents that this is the first time he gets warned
and if it's the second time, another warn is stored as:
| Name | Reason | Date | Warner | 2 |
and so on, with the third war, it's 3, with the 4th, it's 4, and so on, so, how do I do that? Is it possible? This is my current command without the number thing:

@commands.command(pass_context = True)
@commands.has_permissions(manage_messages = True)
async def warn(self, ctx, member:discord.Member, *, reason:str):
  db = sqlite3.connect('warns.sqlite')
  cursor = db.cursor()
  cursor.execute("INSERT INTO warns (memid, reason, date, warner) VALUES (?, ?, ?, ?)", (str(member), "".join(str(reason)), str(datetime.now().strftime("%b/%d/%Y, %X")), str(ctx.author))
  db.commit()
  await ctx.send(f"Warned {member.mention} successfully :+1:")

So, is it possible? (Ping me)

Please help

[pure cypress]

@past widget You can do a query to get the most recent number before you insert the new row. However, this may not be concurrency-safe.

I imagine a more proper way to do it would be to define an SQL function and maybe something with triggers, but sqlite doesn't support those things.

[past widget]

The bot isn't public, and I still have some control over it in critical situations, however, how can I get "the most recent number"?

[pure cypress]

Presumably the most recent number will be the largest, so you could use max()

By the way, you should use aiosqlite instead of sqlite3 since the former supports asyncio

[past widget]

Yep

I updated it

Presumably the most recent number will be the largest, so you could use max()
But that will give the last number for an new warn even if it wasn't for the same person

[pure cypress]

Use a where clause to filter by member

[past widget]

OK

ty

Oh btw, last question, you mean by max() the py function and not the sql func right?

[pure cypress]

I mean the SQL function

You could do it with Python I guess but it's easier to just do it in SQL

[past widget]

Yep

Ty

[pure cypress]

I'm not entirely sure how aiosqlite achieves concurrency, but I am concerned that this naïve method is not concurrency-safe.

You could fetch the max value, then another warn is added, then you go back to add your warn but the max value is no longer correct.

You could fix it with a manual lock on the DB but I'm not sure if it's even necessary

[past widget]

I don't think I really need to lock it

Since as I said, the bot is private

and I'm the only one who has access to it

[pure cypress]

Well, OK

I suppose even if you do two warns quickly it probably wouldn't be quick enough to cause issues

I personally don't like the idea of leaving such flaws open. You could create a unique constraint on the member ID and the count columns so it at least shows an error.

[opal flint]

Question about a best practice thing: I'm writing a program that's connecting to test equipment and then pulling data at a set interval. I'm storing the data I pull in a sqlite database, but I'm not sure what's the best practice way of storing the connection information (you have to manually set-up the connection due to constraints set by the equipment).
Do I create a table in the db for the connection info or store it in some other type of datafile?

I'm not worried about the security of it since it's an offline network out in a remote location. Just curious about how it's typically done.

[past widget]

You could fetch the max value, then another warn is added, then you go back to add your warn but the max value is no longer correct.
SO, i'm back home, so, I should make a column, add in it a num, and then call the max before and after right?

I tried to do as you said, but I guess, I didn't make it right:

  cursor.execute("SELECT MAX(warnid) AS warnid FROM warns WHERE memid = ?", (member))
  result = cursor.fetchone()
  if result is None:
    cursor.execute("INSERT INTO warns (memid, reason, date, warner, warnid) VALUES (?, ?, ?, ?, ?)", (str(member), "".join(str(reason)), str(datetime.now().strftime("%b/%d/%Y, %X")), str(ctx.author), str(1)))
  else:
    cursor.execute("INSERT INTO warns (memid, reason, date, warner, warnid) VALUES (?, ?, ?, ?, ?)", (str(member), "".join(str(reason)), str(datetime.now().strftime("%b/%d/%Y, %X")), str(ctx.author), str(+1)))
    db.commit()

as so?

[crisp nymph]

Hey guys. I am new to DBs. This can be a really weird question, but hear me out. I want to know if it is possible. I have suppose 3 tables. One is called Posts, Another is called User, and the third one is called binds. Now, I get the data from somewhere on the internet after providing the id of a User. now, the "POST" I get has an id of its own. Now I don't want to get duplicate data in my table, while ensuring that each User has his own posts. So thats where binds table comes in. It has 2 columns of PostID and UserID. If the ID of the post data that is collected on the behalf of a user is not present in binds table, then add that post data to the Posts table. If it is, then Add a row to binds table but with the new username. I know this is weird, but do you get my point?

mention if replying

[torn sphinx]

With

'''CREATE TABLE userdata(
id SERIAL PRIMARY KEY NOT NULL UNIQUE,
userid BIGINT NOT NULL,
NumeralName TEXT)'''

Am i right in assuming the ID will be an auto incrementing, unique, key that can never be null?

[celest blaze]

probably. I suppose it depends on the database, but it'd be surprising and annoying if not

[pure cypress]

@past widget You needed to do str(result[0] + 1) rather than str(+1)

[past widget]

But I am getting an error

in

  cursor.execute("SELECT MAX(warnid) AS warnid FROM warns WHERE memid = ?", (member))

ValueError: parameters are of unsupported type

[past widget]

for the method

Have I done it right?

[steady epoch]

Heya I am switching to asynpg

Is there any guide or so for discord bot

Db

I wnt to know how to make connection pool and all

And how to make cursor

Plz ping when u answer

[pure cypress]

@past widget A tuple with one value is written like (member,). Notice the comma at the end. Without the comma, it is not a tuple.

[rough hearth]

I'm trying to open a password-protected sqlite database

It's my database, I just don't see an option to view the password protected data in PyCharm's database tool.

[pure cypress]

Can you use a custom JDBC in PyCharm?

This answer seems relevant

https://intellij-support.jetbrains.com/hc/en-us/community/posts/360007633799-How-to-open-SQLCipher-passwrd-protected-file-in-Datagrip

There's a JDBC fork that supports auth/encryption

[rough hearth]

I think it might be that I've had it all the way open this whole time

and it's just really cryptic the way that the data is structured.

This database contains large textual data (prose that I've written) and it appears to be storing it as binary data

but I don't know how to go from binary back to readable text.

[pure cypress]

Well it may be that the connection isn't encrypted but the content is

If that makes sense

[rough hearth]

yes

[pure cypress]

Like, you don't need to authenticate to connect

[rough hearth]

Let me know if you have any ideas for solving that.

I think the blobs are decrypted within the program that created this database from my inputs

and that the encryption key is created by the password, but the algorithm for this is probably meant to be a secret.

[pure cypress]

Well it really does sound like it used SQLCipher

So I suggest you try that JDBC fork if PyCharm supports it

[torn sphinx]

Hello how would I get a data base connected to my bot and able to type
!logs <#channelNameHere> and every server have its own log channel

[pure cypress]

That's a broad question.

You need to pick a database, set it up, install a library to connect to it, decide on what you need to store in the database, design a schema around that, and write the SQL to populate the database

[torn sphinx]

O_o

What would you suggest for a data base I need something like good but ez for a beginner

[pure cypress]

If you're new to it all I suggest you find a general database tutorial to get acquainted with them

[torn sphinx]

.....

[pure cypress]

Aiosqlite is a good choice for a beginner

[torn sphinx]

Ok

[pure cypress]

Because it needs less set up

[torn sphinx]

So google “how to learn Aiosqlite” ?? Learn/setup

[pure cypress]

Hmm not exactly

[torn sphinx]

Hmm 🤔

[pure cypress]

You can Google how to set it up, which would be in their official documentation. But I think for a general idea of how databases work you'll want to look up something less specific than aiosqlite

[torn sphinx]

Ok

[pure cypress]

The basic knowledge for databases will apply to aiosqlite too

[torn sphinx]

So basic knowledge for databases hmm ok

[pure cypress]

You need to learn some sql

Like how to create a table

[torn sphinx]

Ok

[pure cypress]

How to insert values into a table

Things like that

[torn sphinx]

I have DB browser SQLite

I have been using that

[pure cypress]

I'm not familiar with that tool

[torn sphinx]

hmm

Ok well I will start learning sql

@pure cypress so this is what I need to learn

[pure cypress]

Yeah that's probably fine

[torn sphinx]

It’s 3 hours my god it’s 3:06 am we’re I live

[pure cypress]

There may be some mysql specific stuff in there but that isn't so bad

Well you don't have to watch it now

[torn sphinx]

I will pin it for tomorrow

[ripe stag]

Hi all, quick question on SQLAlchemy model best practices. Is it ok to add methods to a model that report back about it's state? For example, if you have a model that stores a calendar event, it might have a method to tell you if its occurring at the moment? It feels odd having business logic that close to the DB implementation.

[gaunt frigate]

In my function when I run a SELECT * FROM Watchlist

I get this error
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: no such table: Watchlist

However when I run the command on the CLI
sqlite> .tables Watchlist

with sqlite3.connect('watchlist.db') as connection:
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM Watchlist WHERE name = ?",(name))
        connection.commit()
    
        return cursor.fetchall()

Ok I messed around with the code

I get a new error now >_>

File "<stdin>", line 1, in <module>
  File "watch-cmds.py", line 16, in get_specific_watchlisters_from_name
    cursor.execute("SELECT * FROM Watchlist WHERE name = ?", (name))
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 7 supplied.

    with sqlite3.connect('watchlist.db') as connection:
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM Watchlist WHERE name = ?", (name))
        connection.commit()
    
        return cursor.fetchall()

[signal patrol]

Hi guys, could someone tell me why the following is crashing my PostgreSQL:

import psycopg2
conn = psycopg2.connect(dbname='MYDB',
                        user='ME',
                        password='PASSWORD',
                        host='localhost')
cur = conn.cursor()
query = 'alter table nulls_sample add column MYCOLUMN integer;'
cur.execute(query)

[brazen charm]

you planning on telling us what the error is or??

[signal patrol]

@brazen charm , no error whatsoever. PostgreSQL becomes unresponsive and I have to restart it in order to keep using it.
Python doesn't throw any error.
The new column is not created.
psql becomes unresponsive

[velvet saddle]

@gaunt frigate I think you fetch before commit

[gaunt frigate]

uh its after

I sent my code right below the error

[torn sphinx]

S

[uncut cedar]

Whats the difference between MySQL and SQLite?

Can MySql work just like SQLite? I mean, locally.

[celest blaze]

similarly, not just like it.

MySQL's big advantage is that it's a server, which means other machines can use it

sqlite can only be used by programs running on the same machine as the database file

otoh, sqlite is falling-off-a-log easy to set up and use

mysql is a pain in the butt

[toxic rune]

I have this small schema: https://mystb.in/OccupationalCapsWorkforce.yaml

What I need is to group by main_stat_id and sum them, same for sub_stat_1_id and sub_stat_2_id. I thought this schema design would work neat but I think to do that grouping I would need to do it in separate (or one big long query)
So I thought, is it a better idea to separate the stats part into a separate table and scale these with rows? Basically one row per stat, value like: (Not sure how to set up relationships) (A tree can have 6 levels and each level can update multiple stats, different.)

CREATE TABLE Game.ZodiacTreeStat (
  id SMALLINT NOT NULL,
  level SMALLINT NOT NULL,
  stat_id SMALLINT NOT NULL,
  stat_value FLOAT NOT NULL
);

[mild wraith]

I have a small question regarding csv

errr basically its a cashier system, and for the transaction function, I plan to assign each item in the register a price tag

but i cant seem to do it and i've only managed to get the code to add up all the values of what i've entered

i dont really know how to

[celest blaze]

paste some code

tell us both what it does, and what you want it to do

ideally, paste simple code that is nevertheless complete enough to demonstrate your problem.

[mild wraith]

    print("-------------------------")
    print("Transaction #")
    print("-------------------------")
    global cashier_fields
    global cashier_database
    global item_database 
 
    cashier_data = []
    running_total = 0.0
    for field in cashier_fields:
        if field != "Total Amount Paid":
            value = str(input("Enter " + field + ": ")) 
            cashier_data.append(value)
            if field != "Transaction#":
                running_total += float(value)
    cashier_data.insert(1, running_total)
    
 
    with open(cashier_database, "a", encoding="utf-8") as f:
        writer = csv.writer(f)
        writer.writerows([cashier_data])
 
    print("Transaction processing..")
    print("Transaction processed!")
    input("Press any key to continue")
    return ```

sorry i was looking for it

so essentially

its a segment that takes the multiple inputs and adds them together and automatically writes it to a db i prepared

but now i ran into a problem

how am i going to assign float values (item prices) to each of the fields

[torn sphinx]

hello

I need some help understanding windowing

I'm windowing some user sessions based on considering a session as being not more than 30 minutes of inactivity

but I'm not sure how to include total urls visited and unique urls visited during the session, do I need window this count too, when would I need to do this

[torn sphinx]

nvm all done now

my code was working.. I just didn't trust it

[past widget]

sqlite3.OperationalError: table warns has no column named warnnum
although I have a column called warnnum (Ping me)

[echo beacon]

Hi, I am working on an API using Flask. For DB we use MySQL and SQLAlchemy to interface with it. We have a many to many relationship between two models utilising association object. Now I'm working on create route for one of those object... imagine a situation where there are objects A and B in many to many relationship as described above. Object A gets created first and is added to the DB, then in the route I am working on I want to create object B and bind it to object A (many to many relationship). The docs have an example of how to add objects with this relationship to the db (link here: https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#association-object), creating both objects A and B and then adding them to DB, that works for me. But I fail to find the correct code to add newly created object B to relationship with A that is fetched from the DB.

I can share my screen with the relevant code if you are experienced with SQLAlchemy and willing to help 🙂

To be precise, I tried executing the same code with the fetched object A and got this exception: sqlalchemy.exc.InvalidRequestError: Object '<Association at 0x10da7fad0>' is already attached to session '2' (this is '1')

I tired solving it using session.merge but it didn't work, or I probably just don't know how to use it 🤷‍♂️

[echo beacon]

Okay I solved it, we used db.session() instead of db.session multiple times in the API, that caused the problems with multiple sessions running alongside

[eternal raptor]

Hi, I have a problem

    @commands.command()
    async def utworzprof(self, ctx):
        memberr = str(ctx.message.author)
        user = ctx.message.author.name
        role = discord.utils.find(lambda r: r.name == 'Zarabiacz', ctx.message.guild.roles)        
        if role in ctx.author.roles:
            await ctx.send(f"{user} masz już utworzony profil.")
        else:
            conn = sqlite3.connect('bazaNinjaSerwer.db')
            c = conn.cursor()
            member = ctx.message.author
            c.execute("INSERT INTO ekonomia (ID, User, NinjaGold) VALUES (?, ?, 0)", (member.id, memberr))
            conn.commit()
            wynik = c.fetchone()
            print(wynik)
            await ctx.send("Utworzono profil!")
            role = discord.utils.get(ctx.guild.roles, name='Zarabiacz')
            await ctx.author.add_roles(role)
            await ctx.send("Utworzono profil!")
            conn.commit()
            conn.close()

When I use this command: n!utworzprof : Error : attempt to write a readonly database

but this command works

    @commands.command(pass_context=True)
    @commands.has_role("Zarabiacz")
    async def mojstankonta(self, ctx):
        conn = sqlite3.connect('bazaNinjaSerwer.db')
        c = conn.cursor()
        c.execute(f"SELECT NinjaGold FROM ekonomia WHERE ID == {ctx.message.author.id}")
        wynik = c.fetchone()
        embed = discord.Embed(title="Bank", description=f"{ctx.message.author.name} Posiadasz: {wynik} NinjaGoldów  ")
        await ctx.send(embed=embed)
        conn.commit()
        conn.close()

Why It doesn't work?
Please ping me

[misty zenith]

quick question, if i get a lot of similar results and want to know which result happens the most often. how would i do that

i suck at databases

('Two Fanfares for Orchestra: II. Short Ride in a Fast Machine', 3441)
("Wellington's Victory or the Battle Symphony, Op.91: 2. Symphony of Triumph", 3442)
('Romeo et Juliette: No. 11 - Danse des Chevaliers', 3444)
("Symphonie Fantastique, Op. 14: V. Songe d'une nuit du sabbat", 3446)
("Symphonie Fantastique, Op. 14: V. Songe d'une nuit du sabbat", 3446)
('Peer Gynt Suite No.1, Op.46: 1. Morning Mood', 3450)
('Symphony No. 41 in C Major, K. 551, "Jupiter": IV. Molto allegro', 3454)
('Rehab', 3455)
('Rehab', 3455)
("You Know I'm No Good", 3456)
('Me & Mr. Jones', 3457)

like how do i count the 3455 for example

[deft sapphire]

SELECT       `column`,
             COUNT(`column`) AS `value_occurrence` 
    FROM     `my_table`
    GROUP BY `column`
    ORDER BY `value_occurrence` DESC
    LIMIT    1;

This is a rough skeleton, but should work for your purposes. Remove the LIMIT clause if you want them all in descending order. Might need adjusting depending on your db flavor.

[past widget]

sqlite3.OperationalError: table warns has no column named warnnum
although I have a column called warnnum (Ping me)
This is my schema:

CREATE TABLE "warns" (
    "memid"    TEXT,
    "reason"    TEXT,
    "date"    TEXT,
    "warner"    TEXT,
    "warnid"    TEXT,
    "warnnum"    TEXT
);

This is the full error:

    cursor.execute("INSERT INTO warns (memid, reason, date, warner, warnnum, warnid) VALUES (?, ?, ?, ?, ?, ?)", (str(member.mention), "".join(str(reason)), str(datetime.now().strftime("%b/%d/%Y, %X")), str(ctx.author.mention), str(random.randint(9, 9999)), str(1)))
sqlite3.OperationalError: table warns has no column named warnnum

[misty zenith]

awesome dude, thank you!

[deft sapphire]

@past widget rough shot in the dark, but try enclosing the column name with escaped single quotes?

[past widget]

hmmm?

It is closed

        cursor.execute("INSERT INTO warns (memid, reason, date, warner, warnid, warnnum) VALUES (?, ?, ?, ?, ?, ?)", (str(member.mention), "".join(str(reason)), str(datetime.now().strftime("%b/%d/%Y, %X")), str(ctx.author.mention), str(1), str(random.randint(9, 9999)))) 

I did single quote

still the same

[keen ivy]

does anyone have experience working with bigquery packages for Pandas/Python?

im trying to write a dataframe to a BQ table but keep getting authentication errors even though im certain the service account is setup properly and I have the json key stored locally

im pointing to the json key via filepath and its reading it, but i cant get the code to work

[brave lotus]

what's wrong with this statement

might it be the quotes? it added those itself

or am i looking over something really obvious

[craggy jackal]

Yeah no quotes there

[brave lotus]

ah okay thanks

[craggy jackal]

If you're inserting the table via a variable itll add them pretty sure

[brave lotus]

i guess im not supposed to insert the table name

[craggy jackal]

Since it is sanitizing them

[brave lotus]

yeah

[craggy jackal]

If the table name isn't from user input and you can assure 100% it won't be messed with

You can just directly put it in via f-string

[brave lotus]

yeah i made it a global variable so if something changes it'd be easy

but since it won't change i might aswell hardcode it

thanks!

[torn sphinx]

# BAN COMMAND
    @commands.command()
    @commands.has_permissions(ban_members=True)
    async def ban(self, ctx, member: discord.Member, *, reason="No reason was provided."):
        cursor.execute(
            "SELECT ban_reason FROM cb_718580065255948318 ORDER BY id DESC LIMIT 1")
        message = cursor.fetchone()

        embed = discord.Embed(
            title=f"You have been banned from: {ctx.author.guild.name}",
            colour=0x6DA862, timestamp=ctx.message.created_at)
        embed.set_thumbnail(url=self.bot.user.avatar_url)
        embed.set_footer(text=self.bot.user.name,
                         icon_url=self.bot.user.avatar_url)

        embed.add_field(name="Moderator: ",
                        value=f"{ctx.author}", inline=False)
        embed.add_field(name="Reasoning: ", value=f"{reason}", inline=False)

        await member.send(embed=embed)
        await member.ban(delete_message_days=0, reason=reason)
        await ctx.send(f"{message}")

hello there, i am working with mysql database, and i am wanting to update the ban message to something i set, it logs to the database but doesnt update to the bot (unless it gets restarted of course) how could i make it update and pull the latest one? my code is above 🙂

[south sparrow]

is there any way I can simulate stress testing of a cloud database?

[torn sphinx]

put stress on it?

[chrome vault]

Is there any free service to get online sql database hosting ?

[eternal raptor]

    @commands.command()
    async def utworzprof(self, ctx):
        memberr = str(ctx.message.author)
        user = ctx.message.author.name
        role = discord.utils.find(lambda r: r.name == 'Zarabiacz', ctx.message.guild.roles)        
        if role in ctx.author.roles:
            await ctx.send(f"{user} masz już utworzony profil.")
        else:
            conn = sqlite3.connect('bazaNinjaSerwer.db')
            c = conn.cursor()
            member = ctx.message.author
            c.execute("INSERT INTO ekonomia (ID, User, NinjaGold) VALUES (?, ?, 0)", (member.id, memberr))
            conn.commit()
            wynik = c.fetchone()
            print(wynik)
            await ctx.send("Utworzono profil!")
            rolee = discord.utils.get(ctx.guild.roles, name='Zarabiacz')
            await ctx.author.add_roles(rolee)
            await ctx.send("Utworzono profil!")
            conn.commit()
            conn.close()

Error: attempt to write a readonly database

[eternal raptor]

    @commands.command()
    async def utworzprof(self, ctx):
        user = ctx.message.author.name
        role = discord.utils.find(lambda r: r.name == 'Zarabiacz', ctx.message.guild.roles)        
        if role in ctx.author.roles:
            await ctx.send(f"{user} masz już utworzony profil.")

        db = await aiosqlite.connect('bazaNinjaSerwer.db')
        member = str(ctx.message.author)
        member_id = ctx.message.author.id
        cursor = await db.execute("INSERT INTO ekonomia (ID, User, NinjaGold) VALUES (?, ?, 0)", (member_id, member))
        await db.commit()
        wynik = await cursor.fetchone()
        print(wynik)
        await ctx.send("Utworzono profil!")
        rolee = discord.utils.get(ctx.guild.roles, name='Zarabiacz')
        await ctx.author.add_roles(rolee)
        await ctx.send("Utworzono profil!")
        await db.commit()
        await db.close()

error it same (is above...)

[quick quail]

collection.update_one({"_id": str(payload.message_id)}, {"$inc": {"votes": {self.__emotes_dict[payload.emoji.name]}:1}}) (MongoDB)

what correction should I make in order for the code to work?
I basically want to increment the values in "votes" field

emotes_dict is a dictionary that returns the numbers as strings

[eternal raptor]

how to change database (SQLite) from read-only to read-write?

[sick dragon]

@deft badge here too

[deft badge]

yeah, don't worry, we'll handle it

[sick dragon]

okei

[west beacon]

how to change database (SQLite) from read-only to read-write?
@eternal raptor Don't know. If you don't get an answer here, maybe you should ask in an SQLite-centered community? SQLite is not part of Python, so don't expect people here to know it well.

[undone marsh]

how do i open a dot file in vcs?

[opal flint]

SQLite3 is actually part of the python standard library.
But @eternal raptor are you getting a specific error when trying to write to the db? If it's from the "attempt to write a readonly database" it's usually because another process has the database open and is trying to write to it or maybe the DB is encrypting it?

[sharp crest]

Hello! Im new here and also new with python. And i cant find a "beginner-help" chan. So im a bit confused. Im making a small sheet as test- and i dont know what i do 'wrong'

for i in range(0, 45, 5):
    i = float(i)
    print ("{0:>4}{0:>6}".format (i, i*2.54))

Its a bit strange, cause its just a simple sheet with 2 areas. But instead that i *2.54 will work out - it gives this as output:
0.0 0.0
5.0 5.0
10.0 10.0
15.0 15.0

and i dont understand why the i*2.54 doesnt work out and instead just "i" is used.

Even when i do (i, (i*2.54)) it wont work out. I also tried to make a new var so it gets calculated in that. But even that wont work.

[opal flint]

@sharp crest This channel is more for database specific help. You can grab a help channel, check out #❓｜how-to-get-help .
BUT! I'm pretty sure it's because {0:>4}{0:>6} is saying to use the 0 index of the values (i.e. just i) for both.
Try: {0:>4}{1:>6}

[sharp crest]

aah... thanks... x.x i simply oversaw it

yea - i dont want to open a new channel for something small like this

[torn sphinx]

How can i transfer mongodb database from one vps to another vps?

@ me for responce

[vague haven]

how do i remove a document from a mongo collection?

[eternal raptor]

@bot.command()
async def utworzprof(ctx):
    conn = sqlite3.connect("databaza.db")
    c = conn.cursor()
    user = ctx.message.author.name
    role = discord.utils.find(lambda r: r.name == 'Zarabiacz', ctx.message.guild.roles)        
    if role in ctx.author.roles:
        await ctx.send(f"{user} masz już utworzony profil.")
    else:
        member = str(ctx.message.author)
        member_id = ctx.message.author.id
        lol = c.execute("INSERT INTO ekonomia (ID, User, NinjaGold) VALUES (?, ?, 0)", (member_id, member))
        wynik = lol.fetchone()
        print(lol)
        print(wynik)
        rolee = discord.utils.get(ctx.guild.roles, name='Zarabiacz')
        await ctx.author.add_roles(rolee)
        await ctx.send("Utworzono profil!")
        conn.commit()
        conn.close()

Traceback (most recent call last):
File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
ret = await coro(*args, **kwargs)
File "c:\Users\user\Desktop\hbot\arr.py", line 69, in utworzprof
await ctx.author.add_roles(rolee)
File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\member.py", line 641, in add_roles
await req(guild_id, user_id, role.id, reason=reason)
File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\http.py", line 221, in request
raise Forbidden(r, data)
discord.errors.Forbidden: 403 Forbidden (error code: 50001): Missing Access

File "C:\Users\user\AppData\Local\Programs\Python\Python37-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: Forbidden: 403 Forbidden (error code: 50001): Missing Access

@opal flint , @west beacon

[nocturne dock]

How do you read a query execution plan? There seem to be a number of steps, and an "estimated cost". Is the goal to minimize cost? How do you translate cost into time?

[eternal raptor]

? i don't understand

[vital edge]

Hello, I tried to discover Databases and I tried to installed mysql on my ovh server
I ended up with MariaDB and didn't know what it was

Then i tried to connect to the DB using python and this code :

When I run it it doesn't stops and keep running

Whereas when I tried with mysql.connector it stopped and printed things

I would've like to use mysql.connector instead but when I use it it doesn't work

when I replace it with that it errors

[vague haven]

what does this error mean?
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: InvalidDocument: documents must have only string keys, key was 714797824386007070

[vague haven]

why do i get this error? and what does it mean? and how do i fix it?

InvalidDocument: documents must have only string keys, key was 714797824386007070

im using pymongo

[tepid crow]

im trying to connect from my vps to my database but i keep getting that error (postgresql)

ConnectionRefusedError: [Errno 111] Connect call failed ('127.0.0.1', 5432)

[echo jay]

Hi I keep getting an issue with MySQL and I get AttributeError: 'MySQLConnection' object has no attribute 'fetchone'

Code: ```import mysql.connector

db_string = mysql.connector.connect(
host = "localhost",
user = "devuser",
password = "devuser-password",
database = "devdb",
port = "3306",
use_pure=True
)

print(db_string)

cursor = db_string.cursor()
cursor.execute("select database();", 'devuser')
test_auth = db_string.fetchone()

print("INFO: Successfully connected to the", test_auth, "database.")```

[past widget]

Why is num giving 1 for each field? (Although in th db there is 1, 2, 3, etc...) (Ping me)

          await cursor.execute('SELECT warnid FROM warns WHERE memid = ?', (str(member.mention),))
          result = await cursor.fetchone()
          for num in result:
            warnDetails = await cursor.execute('SELECT reason, date, warner, warnnum FROM warns WHERE memid = ?', (str(member.mention),))
            for columns in warnDetails:
                embed.add_field(name=f"Warning #{num}:", value='**Reason:** ' + columns[0] + '\n**Date:** ' + columns[1] + '\n**Moderator:** ' + columns[2] + '\n**Warn ID**: ' + columns[3], inline=False)

[torn sphinx]

Is there a way to undo something that is written in a database? Basically, before it commits to my database, under a certain condition, I'd like my application to completely disregard what it just wrote into the database. That way the app doesn't read that info later and do something wrong. And instead reads the older version.

[pure cypress]

Yes, it's called transaction control

You start a transaction and can choose to abort it or commit it

[torn sphinx]

Thanks! Would I use that exact word? abort? @pure cypress

Oh, nvm. That isn't one of the options 😂

[pure cypress]

No, it's either "commit" or "rollback". I suggest you look at your database's docs on transactions to get an idea of how they are used

[torn sphinx]

Ok, thank you!

[lethal shard]

Anyone have any database projects that a newcomer like myself can do? And possible help setting up a database? Thank you

SQLite a good option?

[pure cypress]

Yes, it is a good option.

For projects, you could write an inventory manager or some fake bank app.

[junior lichen]

Any suggestions for a good lightweight database solution that can be used on a raspi for super basic data capture?

[pure cypress]

sqlite

[ripe helm]

whats the best way to do this following example?

Lets say i have three columns: id, premium, things

How would I grab all the rows that premium == 'True', modify 'things' in a specific way, and then write back the new values back to the db?

in sqlite3

[calm knoll]

what's the table name

[ripe helm]

lets just say

members

[calm knoll]

UPDATE members
SET things = <figure this part out>
WHERE premium = "True"``` or something along those lines

[ripe helm]

well

things will be modified in a kinda complex way

i cant squish it in one line

[calm knoll]

what's the function

[ripe helm]

oh wait

so i should define it in a function

ok just assume its called 'modify'

[calm knoll]

you could use the sqlite api in a programming language and offload that part to python or smthing like that

[ripe helm]

sry what

[calm knoll]

in other programming languages like python or c++ you can use call sqlite functions

[ripe helm]

oh i see what u mean

but is it possible to just run it through a python funtion

[calm knoll]

yes https://docs.python.org/3.8/library/sqlite3.html#sqlite3.Connection.create_function

sorry I'm not terribly experienced with databases

good luck

[ripe helm]

alright thanks anyways

[ripe helm]

if anyone else can help me out itd be appreciated :)

[eternal raptor]

@bot.command()
async def utworzprof(ctx):
    async with aiosqlite.connect("databaza.db") as db:

        user = ctx.message.author.name
        role = discord.utils.find(lambda r: r.name == 'Zarabiacz', ctx.message.guild.roles)        
        if role in ctx.author.roles:
            await ctx.send(f"{user} masz już utworzony profil.")
        else:
            member = str(ctx.message.author)
            member_id = ctx.message.author.id
            await db.execute("INSERT INTO ekonomia (ID, User, NinjaGold) VALUES (?, ?, 0)", (member_id, member))
            await db.commit()
            sql_comm = db.execute("INSERT INTO ekonomia (ID, User, NinjaGold) VALUES (?, ?, 0)", (member_id, member))
            await db.commit()            
            wynik = sql_comm.fetchone()
            print(wynik)
            rolee = discord.utils.get(ctx.guild.roles, name='Zarabiacz')
            await ctx.author.add_roles(rolee)
            await ctx.send("Utworzono profil!")
            await db.commit()
            await db.close()

attempt to write a readonly database

[atomic warren]

@eternal raptor it's a example or question?

[eternal raptor]

question

wait

I have a problem. I'm trying write a command, which will make an user profile, but when I use this command, my Discord bot (Python language) send an error:

Ignoring exception in command utworzprof:
Traceback (most recent call last):
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\user\Desktop\hbot\arr.py", line 65, in utworzprof
    await ctx.author.add_roles(role)
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\member.py", line 641, in add_roles
    await req(guild_id, user_id, role.id, reason=reason)
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\http.py", line 221, in request
    raise Forbidden(r, data)
discord.errors.Forbidden: 403 Forbidden (error code: 50001): Missing Access

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

Traceback (most recent call last):
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\bot.py", line 892, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\core.py", line 797, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-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: Forbidden: 403 Forbidden (error code: 50001): Missing Access```

[brazen charm]

missing permissions

[eternal raptor]

could you help me with this problem? please...

[atomic warren]

Ye

[eternal raptor]

i know, but i don't know how to get this permissions by bot...

[atomic warren]

Just rank it

[eternal raptor]

bot?

[atomic warren]

Ye

I think

[eternal raptor]

wait pls ok?

[atomic warren]

Its a #discord-bots

Question

[eternal raptor]

on discord.py people write : not d.py related ...

[atomic warren]

Oof

It looks like it is

[eternal raptor]

Traceback (most recent call last):
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\aiosqlite\core.py", line 225, in close
    await self._execute(self._conn.close)
  File "C:\Users\user\AppData\Local\Programs\Python\Python37-32\lib\site-packages\aiosqlite\core.py", line 140, in _conn
    raise ValueError("no active connection")
ValueError: no active connection

em?? xD What does it mean?

[atomic warren]

Not so good in db

Maybe

.connect

You closed it

Last time

Send code I geuss

[eternal raptor]

i check database and bot add me to database

[atomic warren]

Oh

[eternal raptor]

@bot.command()
async def utworzprof(ctx):
    async with aiosqlite.connect("databaza.db") as db:

        user = ctx.message.author.name
        role = discord.utils.find(lambda r: r.name == 'Zarabiacz', ctx.message.guild.roles)        
        if role in ctx.author.roles:
            await ctx.send(f"{user} masz już utworzony profil.")
        else:
            member = str(ctx.message.author)
            member_id = ctx.message.author.id
            await db.execute("INSERT OR IGNORE INTO ekonomia (ID, User, NinjaGold) VALUES (?, ?, 0)", (member_id, member))
            await ctx.author.add_roles(role)
            await ctx.send("Utworzono profil!")
            await db.commit()
            await db.close()

[atomic warren]

Hm

I dont know to be fair

[brazen charm]

you dont need to close it with a context manager

[eternal raptor]

maybe i delete ``` await db.close()

[brazen charm]

the context manager opens and closes it for you with aiosqlite

[eternal raptor]

@brazen charm CF8 ??? What does it mean?

[brazen charm]

delete the db.close()

[eternal raptor]

ok

[atomic warren]

@eternal raptor what does that cose do thi

Tho

Code*

[eternal raptor]

All good

wait

make an user profile to economy (ID, nickname with tag, 0 cash)

[atomic warren]

Oh

That's a good example I needed one lil

Lol

[eternal raptor]

hah

your welcome 🙂

[atomic warren]

:)

But

[eternal raptor]

programming is my hobby

I'm 14 yo

[atomic warren]

I suck in sqlite

I'm 13

[eternal raptor]

sqlite == aiosqlite

[atomic warren]

And it's my hobby too

[eternal raptor]

i can help you with convert

from aiosqliteto sqlite

[atomic warren]

sqlite == aiosqlite
@eternal raptor me suck in both

I wanna learn db for dpy

[eternal raptor]

hah

[atomic warren]

Kinda same as you

Ik very basive

Basic

Like Create table

Inserinto

[eternal raptor]

I recommend sqlite browser

Is good sqlite editor

[atomic warren]

Oh what's that

And do you mean I dont use it from the python?

[eternal raptor]

wait

https://www.youtube.com/watch?v=2pbL7gTHMRw

watch this

[atomic warren]

Oh

[eternal raptor]

Sqlite browser is the best sqlite editor.

[atomic warren]

So does it see you what's in the db right

[eternal raptor]

and easy-to-use

Yes, it see.

You can make an tables, columns with options (Autoincrement, notnull, unique, etc.)

[atomic warren]

Oh

[eternal raptor]

a tables***

oops

[atomic warren]

So it reduces some work right

[eternal raptor]

my English isn't so good

[atomic warren]

Like instead of Create table I do it manually

Same

[eternal raptor]

I'm from Poland, and you?

[atomic warren]

India

[eternal raptor]

oo

We helped me at 30 minutes, but other peoples writes to me : not d.py related, other : d.py related , write on this server: discord,gg / asdada

etc...

I'm trying to write this command for 5 days.

[atomic warren]

Oh

[tepid crow]

im trying to connect from my vps to my database but i keep getting that error (postgresql)
ConnectionRefusedError: [Errno 111] Connect call failed ('127.0.0.1', 5432)

[long canyon]

does anyone have good video courses for beginners?

[atomic warren]

Ye

[torn sphinx]

Is it okay if I perform 3 joins instead sth faster on a small db?

[atomic warren]

https://youtu.be/byHcYRpMgI4

[eternal raptor]

@long canyon i recommend sololearn, not video, buut very good tutorials.

or freecodecamp

is above

[long canyon]

https://youtu.be/byHcYRpMgI4
@atomic warren thankkkk youuu!

[atomic warren]

I just need time to see it

Np

@eternal raptor mind helping me later with mysql?

Like tmr

Today I will learn

[eternal raptor]

ok 😄

i can help you 🙂

[atomic warren]

Thanks

[eternal raptor]

by the way, I will also learn: D

by the way, I will also learn 😄

tmr ??? what does it mean?

[tepid crow]

Tommorow?

[eternal raptor]

oh thank you

[hazy herald]

Hi, i need some advice on which dbms i should learn next. I am learning machine learning, so i would be working with data a lot, i have read that database management will be very important. In my college, we have completed MySql. So i know intermediate relational database management. Should i learn NoSql database management like MongoDB?

[rustic wind]

Hey, I started a project with Python KivyMD. It'll become an App for my mobile phone but I realised, that I have to save data in a dynamic way, that I can change these values, add and delete some. Is a database the way to go and what kind of database should I use?

[tepid crow]

im trying to connect from my vps to my database but i keep getting that error (postgresql)
ConnectionRefusedError: [Errno 111] Connect call failed ('127.0.0.1', 5432)

when im running my bot

[steel patrol]

hi! i'd like to seek help from someone who's proficient in pymongo

[ionic quartz]

does any1 have a database template for discord.py

[glad bobcat]

Any sqlalchemy power users here?

I have an issue with merging inside a scoped session. It’s issuing integrity errors when it should be pretty much fullproof.

[deep maple]

@bot.command()
async def translate_setting(ctx, arg):
    try:
        cursor.execute(f"""UPDATE traduction SET langue={arg} WHERE server_id={ctx.guild.id}""")
        conn.commit()
    except:
        print("error 1")
        cursor.execute(f"""INSERT INTO traduction(langue, server_id) VALUES({arg}, {ctx.guild.id})""")
        ```

error:

    cursor.execute(f"""INSERT INTO traduction(langue, server_id) VALUES({arg}, {ctx.guild.id})""")
sqlite3.OperationalError: no such column: en```

help me please

I send a message but stored in arg

but he sends me this error, I don't understand

[rich trout]

It's probably choking because you're manually constructing your sql query

don't do that, use parameterization like this:

cursor.execute("INSERT INTO mytable (thingy, thingabob) VALUES (?, ?)", (value1, value2))

This will automatically make sure all data is escaped and passed appropriately

[opal flint]

Is using context managers for accessing a db recommended?

[tepid crow]

im trying to connect from my vps to my database but i keep getting that error (postgresql)
ConnectionRefusedError: [Errno 111] Connect call failed ('127.0.0.1', 5432)
when im running my bot

@rich trout any clue what is going on here?

[ripe helm]

If I have three columns, id, premium, and things

How would I select all rows that have premium == ‘True’

And then run a python function run on each ‘things’

Where premium is true

[jade dune]

select * from things where premium = True

[misty zenith]

anyone ever worked with the chinook database?

i need to figure out what album was sold most. can't figure it out for shit

[delicate field]

Hey @misty zenith!

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

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

Hey @misty zenith!

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

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

[minor matrix]

Any of the helpers/mods know where (if, even) we could post this? https://www.reddit.com/r/Python/comments/hjgvcz/automate_the_boring_stuff_with_python_online/

it's free this month

Literally the author of it posted on reddit 😄

[signal pier]

hey so um, I was working with Mongodb, I wanted to know how do I make python return the value of an attribute.

[torn sphinx]

hello what database to work with in python? easy for starters 😉

[modest matrix]

sqlite, which is file based

So you don't have to bother installing a server

[sick dragon]

I'm using psycopg2 to be able to interact with my Postgres database, and I can't figure out how to insert a BIGINT. Is %d valid?

[brazen charm]

last time i check psycopg2 uses standard python string formatting

which personally i hate because it can make stuff confusing

[gaunt frigate]

def get_all_clearances() -> List[Tuple]:
    with connect(file_name) as connection:
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM Clearance")

        return cursor.fetchall()

2020-07-01T18:32:19.979907+00:00 app[worker.1]: discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: no such table: Clearance

sqlite> .tables
Clearance

Claims table doesn't exist
Table exists...

[finite lynx]

Could anyone help me a bit, I dont quote understand how server_default works in sqlalchemy from the documentation

[torn sphinx]

    async def new(self, ctx, *, subject=None):
        conn = sqlite3.connect('ticket.db')
        print(1)
        c = conn.cursor()
        print(2)
        if not subject:
            subject = 'No subject'
        c.execute("INSERT INTO ticket VALUES(:one)", dict(one=1))
        c.execute("UPDATE ticket SET number = number + 1")
        conn.commit()

        n, =c.execute("SELECT number FROM ticket").fetchone()
        print(n)``` anyone knows why it stops after the first exec ?

[merry mirage]

Hello. In MongoDB, is it possible to find by this aggregation pipeline:
1 - group,
2 - limit maximum number of samples for each group?

[old raven]

Does anyone know how I can count the number of items within an array in mongodb?

[lethal shard]

Anyone know how to use SQL on visual studio code?

[solar gale]

how do i get the count off of a sqlite3 cursor?

[vocal moon]

is there anyway to set a starting number for the SERIAL datatype is psql

[solar gale]

you can ignore my last question, i figured out how to do what i needed to do lol

[kindred inlet]

@lethal shard you using the mssql extension?
https://docs.microsoft.com/en-us/sql/visual-studio-code/sql-server-develop-use-vscode?view=sql-server-ver15

[pine geode]

general sqlite question:
I have one .py file with a bunch of funcs that interact with a database (i.e. add a movie, rate a movie, etc). The funcs are called by a discord bot.

should I define the sqlite connection within each function? Or does it make more sense to just define it once at the top of the file as a global variable that each function then uses?

[rain field]

oh i didnt know this channel existed

can someone take a look at #help-cookie

its a database question

[solar gale]

im trying to catch this error to let the user know that they screwed up when attempting to add a record:
sqlite3.IntegrityError: UNIQUE constraint failed: RegisteredPlayers.UserID

i've tried it like this:

try:
    pass
except IntegrityError:
    print("HELLO WORLD")

but it still stops the execution

am i just writing it incorrectly?

[rain field]

how can I store a list inside a column

a list of strings

also

can i use alter table

to add elements to that list in a column

[solar gale]

actually, nvm. i wrote it correctly, but i incorrectly thought that it stopped the execution lol

@rain field i would do that by converting the list into a single, delimited string, which would look like this:

["hello","darkness","my","old","friend"]

converted into

"hello,darkness,my,old,friend"

i forget how exactly to concatenate a list of strings with a delimiter, but i do know that you can easily recreate the list from that single string by just doing:
mylist = "hello,darkness,my,old,friend".split(',')

now i remember lol

@rain field use this to convert the list into a big string

a = ["hello","darkness","my","old","friend"]
b = ','.join(a)
print(b)

and use this to convert it back into a list

c = b.split(',')
print(c)

[rain field]

hmm

that would just

recreate

my problem

@solar gale

could you take a look at this

[thorn jolt]

use join bro and you will join the list to a string

[rain field]

def sendlog(User, message):
userlogs = self.server.Cursor.execute("select logs from table where username = '{}'".format(User.username))
if userlogs == None:
  userlogs = message
else:
  userlogs = userlogs+", "+message
self.server.Cursor.execute("update table set logs = '{}' where username = '{}'".format(userlogs,User.username))```

look

this always overrides

the column's value

to the new message

i want it to keep its old value + the new message string

[thorn jolt]

don't insert the data using format bro.. causes a lot of problems including sql injection

[rain field]

what should I use

%s?

i cant use f strings

since im under python 2.7

[thorn jolt]

cur.execute("SELECT * FROM tasks WHERE priority=?", (priority,))

this is how you do it

[rain field]

so ?

[thorn jolt]

by passing a list or tuple to thte execute with the data

this is more secure

[rain field]

okay

but you think the formatting was the problem?

i dont think so

[thorn jolt]

and you can use join to turn ['item one', 'item two'] into 'item one, item two' by doing ', '.join(['item one', 'item two'])

[rain field]

hmmm i dont think you understand the issue

basically lets say the contents of the column logs for user A is "x"

I want to update it

to

"x, y"

y is another message

that the user sent

but!

if logs was empty

i would want only "y"

[thorn jolt]

if u just want to add to a string you can do +=...

userlog = 'test'
userlog += 'ing'
print(userlog) # will print 'testing'

[rain field]

sure

but what if

userlog was None

at the start

I would want only "ing"

thats why I check if the select query is None

[thorn jolt]

"x, y"
@rain field then do userlog += ', {}'.format(message) or userlog += ', '+message is cleaner and simpler and better

[rain field]

okay but it doesnt work

its as if

my select query

is always None

[thorn jolt]

i would want only "y"
@rain field okay.. now you do userlog = userlog+', '+message if userlog else message

[rain field]

o

lemme try that

[solar gale]

change how you're selecting data by correctly formatting your string

that's the 1st glaring issue

[rain field]

so i'll use ?

[thorn jolt]

@rain field okay.. now you do userlog = userlog+', '+message if userlog else message
@thorn jolt use this bro... will work great with your code... and please send the query in a safe way

[rain field]

will do lemme try it

ill tell you if it works

[thorn jolt]

take your time

[solar gale]

this is from the reference:

execute():
Executes an SQL statement. The SQL statement may be parameterized (i. e. placeholders instead of SQL literals). The sqlite3 module supports two kinds of placeholders: question marks (qmark style) and named placeholders (named style).

# This is the qmark style:
cur.execute("insert into people values (?, ?)", (who, age))

# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

ref: https://docs.python.org/3/library/sqlite3.html

you need to format your strings using either way in order for it to work

[thorn jolt]

yes... this is great.. it's safe against sql injection and clean

[rain field]

@thorn jolt do I need a comma

after

even if there is only 1 argument

cur.execute("SELECT * FROM tasks WHERE priority=?", (priority,))

the comma after priority

(priority,)

[thorn jolt]

when you are using a tuple in python and it only has one item it will not count as a tuple... so you need a comma... u wont need a comma if u use a list

(priority,)
@rain field if you remove this comma python will remove the () in run time

[rain field]

oh its a tuple yea

if i use this

{}

its fine without

im an idiot

[thorn jolt]

if u use curly braces {} it will be a normal dictionary yes.. no need for a comma

[rain field]

@thorn jolt alright so changed everything up

and still the same issue

it overrides the column

uh i use "text"

in my db

is that the issue maybe?

should I use varchar

?

[thorn jolt]

use varchar of course

but this is not the issue

please show me the code

[rain field]

okay

1 thing you must know

is that

its in french partially

like variable names

and stuff

historiquesender = self.server.Cursor.execute("select logs from users where username = ?",(self.username,))
historiquereceiver = self.server.Cursor.execute("select logs from users where username = ?",(User.username,))

historiquesender = str(historiquesender)+', '+"from "+str(self.username)+" to "+str(User.username)+": "+message if historiquesender else "from "+str(self.username)+" to "+str(User.username)+": "+message

historiquereceiver = str(historiquereceiver)+', '+"from "+str(self.username)+" to "+str(User.username)+": "+message if historiquereceiver else "from "+str(self.username)+" to "+str(User.username)+": "+message

self.server.Cursor.execute("update users set logs = ? where username = ?",(historiquesender,self.username))
self.server.Cursor.execute("update users set logs = ? where username = ?",(historiquereceiver,User.username))

historique

means

history

de

means

from

and a

means to

i can change them

for clarity

@thorn jolt

you gotta understand that its a message sent

so i have to update

for both

the sender

and receiver

self is the client class so its the local client (always sender)

and user