#databases | Python | Page 138

burnt turret Mar 4, 2021, 3:54 PM

#

which you can use like any normal dictionary to retrieve that guild's theme

data = db_themes.find_one({"guild_id": ctx.guild.id})
data["theme"] -> gives you that guild's theme

#

for the first question, does every guild have a pre-existing default value for the theme or something in the database?

torn sphinx Mar 4, 2021, 3:56 PM

#

i wanted to do it if

#

there is no value in the database for that guild

#

which if im right would give None back

#

then id be animals in this case

burnt turret Mar 4, 2021, 3:56 PM

#

Yeah, if you try finding a document which doesn't match it'd give you None back

torn sphinx Mar 4, 2021, 3:56 PM

#

if themes is None:
self.slap_gifs = self.gifs["animals"]["slap"]

burnt turret Mar 4, 2021, 3:57 PM

#

oh huh I'm a little confused by your first query. You seem to be passing the arguments that are usually used with an update into a find_one

#

do you want to insert if the guild doesn't have existing data?

torn sphinx Mar 4, 2021, 3:58 PM

#

would that matter?

#

cause if it doesn't have existing data it would just have the default theme right

burnt turret Mar 4, 2021, 4:03 PM

#

but you'd be setting the default theme only in your code

#

i guess that is your choice really - but that query is still incorrect

#

you pass the second argument (the $set part) only for updates

torn sphinx Mar 4, 2021, 4:04 PM

#

I mean if it's better to set the default theme

#

in the database

#

im all ears

#

also how would I fix it then

burnt turret Mar 4, 2021, 4:05 PM

#

the query? just remove that second dictionary

#

the way it works is that in an update query - the first dictionary acts as a filter (to specify which documents to update) and the second one actually tells how to modify the data

#

(i'll brb, a little busy)

torn sphinx Mar 4, 2021, 4:07 PM

#

Yh dw

#

By removing the second dictionary it wouldnt store the value for themes in the database tho right

astral gorge Mar 4, 2021, 4:09 PM

#

hi I have a question

#

if double precision PSQL type has a 15 digits precision, then as long as my number is less than 16 digits, it WILL NOT be approximated? ie 1234567.8901

burnt turret Mar 4, 2021, 4:16 PM

#

torn sphinx By removing the second dictionary it wouldnt store the value for themes in the d...

it wouldn't; it won't now either

#

because you can't insert/update data with a find query

#

a find only retrieves data

#

you'll have to write another query, to update/insert as well

torn sphinx Mar 4, 2021, 4:16 PM

#

Ahh

#

Ok how do I do that

#

Ig i could for the update just do

#

Find and update

burnt turret Mar 4, 2021, 4:17 PM

#

can you explain again what exactly you want done there? do you need to retrieve the data, or is just updating enough?

torn sphinx Mar 4, 2021, 4:17 PM

#

They do the command

#

Set theme "theme"

#

The theme they set, if valid

#

Needs to be put in the database with the guild

#

And then in the slap command

#

It retrieves that data

#

And tells the slap command from what category to take

#

The gifs from

burnt turret Mar 4, 2021, 4:19 PM

#

right I get it now

#

btw careful with sending multiple messages, it can trip @delicate field s spam filters lmao

torn sphinx Mar 4, 2021, 4:20 PM

#

Ahh noticed

burnt turret Mar 4, 2021, 4:20 PM

#

so anyways, in your first command you don't really need to retrieve the data at all.
you want to insert data if that guild id doesn't exist yet in the database, and if it does exist you want to update it

#

insert + update = upsert, does exactly ^

#

so the query should be something like

db_themes.update_one({"guild_id": ctx.guild.id}, {"$set": {"Theme": theme}}, upsert=True)

notice again, the first dictionary tells the db which document to update, and the second dictionary tells the db to set that document's Theme key to whatever the theme was

#

upsert=True does the insert-if-not-exists, else-update logic

torn sphinx Mar 4, 2021, 4:23 PM

#

I see

#

So that's all that needs changing basically

burnt turret Mar 4, 2021, 4:24 PM

#

yeah i dragged that out a lot haha

#

but that query logic, once you understand it is quite helpful

torn sphinx Mar 4, 2021, 4:24 PM

#

I mean extra explanation is always useful

#

And welcome

burnt turret Mar 4, 2021, 4:25 PM

#

last thing:

#

https://gist.github.com/anand2312/840aeb3e98c3d7dbb3db8b757c1a7ace

Gist

pymongo vs Motor

pymongo vs Motor. GitHub Gist: instantly share code, notes, and snippets.

torn sphinx Mar 4, 2021, 4:25 PM

#

Yeah i heard motor is better since it's async

burnt turret Mar 4, 2021, 4:25 PM

#

tl;dr is using pymongo is blocking, motor is an async mongodb driver lib

#

ehh I wouldn't call it better, it can be kinda slow

#

but at least it won't block

torn sphinx Mar 4, 2021, 4:25 PM

#

In what situations would it be better

burnt turret Mar 4, 2021, 4:26 PM

#

in this situation I'd say it is better, because it won't block your bot

#

rn your bot will be frozen for the nanoseconds that it is interacting with the db

#

it won't feel like much now, but it can add up quickly and make your bot laggy

#

using motor will keep the db interactions asynchronous too

torn sphinx Mar 4, 2021, 4:27 PM

#

Is it a big change from setting up?

burnt turret Mar 4, 2021, 4:27 PM

#

Nah

#

I go through the process in that gist

#

pretty simple

torn sphinx Mar 4, 2021, 4:28 PM

#

Aightt i'll take a look at it

#

And again big thanks

lilac bolt Mar 4, 2021, 4:47 PM

#

I have a question. If you were making a client that had some sort of "log in" or any sort of data that needed to be trasnferred to a database, how would you do this? Would you put the database on an API and have it request the API? or would you use a server of some sort?

modest pulsar Mar 4, 2021, 4:53 PM

#

Someone told me that sqlite3's stocking in json... Is it true?

burnt turret Mar 4, 2021, 5:00 PM

#

modest pulsar Someone told me that sqlite3's stocking in json... Is it true?

what do you mean by "sqlite stocking"?

#

afaik you can't store json in sqlite

modest pulsar Mar 4, 2021, 5:07 PM

#

burnt turret what do you mean by "sqlite stocking"?

No, sqlite is stocking in json

#

Someone told me that

#

Is it truz

#

True

burnt turret Mar 4, 2021, 5:07 PM

#

oh no i don't think it does

modest pulsar Mar 4, 2021, 5:07 PM

#

Ok thx

burnt turret Mar 4, 2021, 5:07 PM

#

one minute, i can try verifying

#

https://www.sqlite.org/fileformat.html ehh i can't figure out what the format is but by reading that i think i can safely say it isn't json

modest pulsar Mar 4, 2021, 5:12 PM

#

Ok thx

sick perch Mar 4, 2021, 5:19 PM

#

I recall that Sqlite started to add json colum data helpers, but that's certainly not about file format

#

Ah, https://www.sqlite.org/json1.html

burnt turret Mar 4, 2021, 5:20 PM

#

interesting, hadn't heard of that before

jagged cove Mar 4, 2021, 5:50 PM

#

how do I use row_number() to get someone's position in an ordered table? I've tried everything i can think of and read every documentation but i can't find the right syntax nor do i understand how to use it at all, and everything i've tried has just returned an error.

sick perch Mar 4, 2021, 5:56 PM

#

jagged cove how do I use `row_number()` to get someone's position in an ordered table? I've ...

What database?

sick perch Mar 4, 2021, 5:58 PM

#

jagged cove how do I use `row_number()` to get someone's position in an ordered table? I've ...

Also keep in mind that window functions need a window to work with

jagged cove Mar 4, 2021, 6:05 PM

#

i have never used window functions before

#

the most complex thing i've ever done is order a query by experience in descending order

#

either way context is i need someone's exact position in the leaderboards when ordered by experience, the table is called g{id} (guild specific tables) and users are saved by discord id

#

id INTEGER PRIMARY KEY, experience INTEGER, level INTEGER (table structure excluding unrelated fields)

burnt turret Mar 4, 2021, 6:10 PM

#

🤔 so something like

SELECT id, experence, level, ROW_NUMBER() OVER (ORDER BY experience DESC) FROM tablename
```?

jagged cove Mar 4, 2021, 6:12 PM

#

i'll try it, thanks a lot

burnt turret Mar 4, 2021, 6:13 PM

#

why do you have tables per guild though?

sick perch Mar 4, 2021, 6:14 PM

#

What exactly is the row number used for again?

jagged cove Mar 4, 2021, 6:14 PM

#

it's a discord bot and users can earn experience and coins for chat activity, but i didn't want coins to overlap servers

burnt turret Mar 4, 2021, 6:14 PM

#

#databases message has some reasons on why you wouldn't do something like that

jagged cove Mar 4, 2021, 6:14 PM

#

to display the current user's position on the leaderboard

#

the leaderboard is sorted by experience in descending order

burnt turret Mar 4, 2021, 6:14 PM

#

jagged cove it's a discord bot and users can earn experience and coins for chat activity, bu...

you could have all the users' data in a single table, and add a guild ID column to it as well?

#

(edited the query I sent to order by desc)

jagged cove Mar 4, 2021, 6:16 PM

#

that was my original idea but my friend (who is a more experienced developer) proceeded to be very offended by this and called me a dumbass

#

then told me to do it with guild-specific tables instead

sick perch Mar 4, 2021, 6:17 PM

#

Sounds bad, insulting beginners is a red flag

jagged cove Mar 4, 2021, 6:17 PM

#

but either way the guild table structure is pretty cemented and if i must make a change i would edit the creation command to add an extra field/delete any other one etc

#

not necessarily a beginner but it's what we do we tease each other a lot and stuff

burnt turret Mar 4, 2021, 6:17 PM

#

if you plan on changing, i'd say the sooner the better

jagged cove Mar 4, 2021, 6:18 PM

#

either way i severely doubt he had any bad intentions

burnt turret Mar 4, 2021, 6:18 PM

#

what was their reasoning to use guild specific tables?

jagged cove Mar 4, 2021, 6:18 PM

#

he insisted that records should have ids that don't ever overlap, so having multiple records with the same user ID = huge no no

#

i suggested adding a third ID column that's fully unique but he still didn't rlly think that was a good plan

#

either way you make some solid points but i still feel this is one of the more logical solutions to overlapping balances and guild specific cosmetics etc

#

and i also have access to a MySQL database so a few extra tables wouldn't be the end of the world (i think/hope dearly)

burnt turret Mar 4, 2021, 6:22 PM

#

if you store guild ID + user ID, it remains unique though (a user's data is specific to a guild, and if they're in another guild with your bot, that guild's ID will help you uniquely identify the user's data for that guild)

jagged cove Mar 4, 2021, 6:22 PM

#

you mean like id = {userid},{guildid} or something like that?

burnt turret Mar 4, 2021, 6:22 PM

#

i don't know about how this affects performance, but I feel like querying would be much more intuitive this way. But that's just my opinion.

jagged cove Mar 4, 2021, 6:23 PM

#

personally haven't had any issues with query's actually

burnt turret Mar 4, 2021, 6:23 PM

#

jagged cove you mean like id = `{userid},{guildid}` or something like that?

not really, the table would have a guild_id column along with everything else you have

sick perch Mar 4, 2021, 6:23 PM

#

Ids in table names is a total horror and indicates a design failure

jagged cove Mar 4, 2021, 6:23 PM

#

i made a bunch of functions + i use the discord API so whenever i need to retrieve data i just set table to f"g{ctx.guild.id}" which has worked just fine for me

#

yeah that made sense to me as well but idk

#

also i guess

burnt turret Mar 4, 2021, 6:24 PM

#

rethinking your database structure might be a good idea

jagged cove Mar 4, 2021, 6:25 PM

#

probably but i wouldn't have a clue what to do with it and even then i already have months of work built upon this structure from day one so i feel like it's a bit late for a structural reform

#

very first thing i did on this project was set up a script that automatically adds new users and guilds to the database as a unique table (guilds as unique, users don't have their own tables)

#

if you guys had to estimate, how big an issue do you think my current database structure will be in the future?

burnt turret Mar 4, 2021, 6:28 PM

#

I can't really say, I'm not knowledgeable enough on the topic, but I have a feeling managing the database will be a nightmare as your bot grows

#

I can't really imagine how you'd be doing joins and other fun stuff

jagged cove Mar 4, 2021, 6:28 PM

#

wdym joins?

#

like join announcements in servers?

burnt turret Mar 4, 2021, 6:29 PM

#

joins let you combine different tables together in a query, to consolidate different inter-related data

#

if that made any sense

jagged cove Mar 4, 2021, 6:30 PM

#

it didn't but then again i'm not the brightest

#

i don't see what i'd really need that for though

burnt turret Mar 4, 2021, 6:30 PM

#

say you have this table with the users' experience and everything. you could have another table that stores, say, inventories.
with a JOIN you could, for example, get both the experience and the inventory together in a single query

jagged cove Mar 4, 2021, 6:31 PM

#

oh i have the users' inventories in the same table

#

so far a guild table houses users' experience, currency, level, id, aswell as cosmetics such as their profile colours, a name prefix, etc

burnt turret Mar 4, 2021, 6:32 PM

#

haha dump them all in together, avoid the need to join huh

jagged cove Mar 4, 2021, 6:32 PM

#

haha yeah

#

i feel making a seperate table would just be unnecessary anyway

#

doesn't really have any added value

burnt turret Mar 4, 2021, 6:32 PM

#

you're potentially making millions of tables here with guild-specific tables

#

and you have a problem with making 1 extra table? :p

jagged cove Mar 4, 2021, 6:33 PM

#

honestly if this bot reaches a million guilds then god is already on my side anyway

#

not necessarily but i just don't see what the point would be

burnt turret Mar 4, 2021, 6:33 PM

#

Maybe someone else here could better explain the benefits of doing joins

#

but i don't think your current database structure is the best way to do things out there

jagged cove Mar 4, 2021, 6:35 PM

#

it's probably not but i wouldn't know what other solution to use, plus if it doesn't pose any severe issues i'd hate to mess with it right now as nearly all features are built on this structure

#

if i ever do a remake i'll make sure to revise the database structure though

burnt turret Mar 4, 2021, 6:35 PM

#

fair enough

jagged cove Mar 4, 2021, 6:36 PM

#

oh also i tried the row_number() solution you guys sent me and i got a syntax error

#

i was wondering how to get the row number of a specific record id aswell btw

#

since i only need to get the leaderboard position of the current user so

#

Ignoring exception in command leaderboard:
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/site-packages/discord/ext/commands/core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "/Users/macbook/Desktop/Projects/Discord BOT/main.py", line 1613, in run
    pos = db.get_leaderboards_pos(ctx.author.id, f'g{ctx.guild.id}')
  File "/Users/macbook/Desktop/Projects/Discord BOT/database/functions.py", line 217, in get_leaderboards_pos
    c.execute(f"SELECT id, experience, level, ROW_NUMBER() OVER (ORDER BY experience) FROM {table}")
sqlite3.OperationalError: near "(": syntax error

burnt turret Mar 4, 2021, 6:40 PM

#

oh wait that's sqlite

#

i'd written that thinking of postgres

jagged cove Mar 4, 2021, 6:43 PM

#

ohh that explains it yeah

#

i use sqlite for development but my friend is gonna be hosting it once it's released and he has access to a mysql database which he will connect it to afterwards

burnt turret Mar 4, 2021, 6:44 PM

#

Oh, in that case you'd want to make sure that every query you write matches the SQL standard which is accepted by all databases (and not specific to one)

jagged cove Mar 4, 2021, 6:45 PM

#

will do

burnt turret Mar 4, 2021, 6:45 PM

#

Sorry I gotta go now - this is what I could find for the row_number function on sqlite https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-row_number/

SQLite Tutorial

admin

SQLite ROW_NUMBER() Window Function By Practical Examples

This tutorial shows you how to use the SQLite ROW_NUMBER() to assign a sequential integer to each row in the result set of a query.

jagged cove Mar 4, 2021, 6:45 PM

#

thanks a lot, have a good one

brave bridge Mar 4, 2021, 7:17 PM

#

hey @glad spoke, our filter got mistriggered. Can you post the question without the invite domain name?

glad spoke Mar 4, 2021, 7:17 PM

#

I am retyping it now

#

thanks :3

#

Hey. I am currently trying to delete a discord invite from my SQL database when the invite is deleted. The error is with the SQL and nothing to do with discord.py so this channel is appropriate imo.

Here is the error:
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '://an invite link' at line 1

#

the code I use to do this:

        url = "https://d1sc0rd.gg/{}".format(invite.code)
        print(url)
        query = "DELETE FROM invites WHERE url=" + url
        cursor.execute(query)
        db.commit()

I don't actually have it as d1sc0rd. I just wrote it like that to avoid a filter. :D

#

any help is greatly appreciated

brave bridge Mar 4, 2021, 7:20 PM

#

@glad spoke You should never format SQL queries like that. If someone types https://discord.gg/'';DROP TABLE invites; (not exactly this, but something similar), you're in big trouble.
You should use formatting tools built into your database adapter:

cursor.execute("DELETE FROM invites WHERE url=?", url)

#

As a side note, if you're using discord.py, you should be using an asynchronous database adapter (aiosqlite for sqlite3, asyncpg for Postgres)

glad spoke Mar 4, 2021, 7:21 PM

#

oh, cool. I'll have to look into that for sure

#

I imagine it'd work better when the bot is on more servers

brave bridge Mar 4, 2021, 7:22 PM

#

brave bridge As a side note, if you're using discord.py, you should be using an asynchronous ...

because when you're making a blocking call (like input(), time.sleep(42) or cursor.execute(...)), the entire event loop gets clogged.
On a small scale it doesn't matter much, though.

brave bridge Mar 4, 2021, 7:23 PM

#

brave bridge <@!291255422885560320> You should never format SQL queries like that. If someone...

So this is how you should always format queries. It does the right thing (wraps the string into quotes and escapes all special characters).

glad spoke Mar 4, 2021, 7:23 PM

#

thanks for your help

#

however, one last thing.
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

I got this error when running the script you provided me with. I'm not sure why though.

brave bridge Mar 4, 2021, 7:26 PM

#

@glad spoke Can you show the code?

glad spoke Mar 4, 2021, 7:26 PM

#

sure thing

brave bridge Mar 4, 2021, 7:27 PM

#

You can use https://paste.pydis.com not to trigger the filter

glad spoke Mar 4, 2021, 7:27 PM

#

oh cool

#

https://paste.pythondiscord.com/oguzireqaf.rb

#

I hope I did that right

brave bridge Mar 4, 2021, 7:29 PM

#

Ah I see

#

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

glad spoke Mar 4, 2021, 7:29 PM

#

Ah, should I be using VALUES (%s) instead? I'm familiar with that

brave bridge Mar 4, 2021, 7:29 PM

#

It should be

cursor.execute("DELETE FROM invites WHERE url=%s", url)

or

cursor.execute("DELETE FROM invites WHERE url=%(url)", {"url": url})

#

it sucks that different syntax variants are supported depending on the adapter

glad spoke Mar 4, 2021, 7:30 PM

#

agreed

#

hm, both gave me errors.

wispy oasis Mar 4, 2021, 7:32 PM

#

if anyone might find this useful, thought i'd post it here: https://github.com/AtakamaLLC/notanorm . it's easier-to-use (for me) than hitting the db directly, protects against sql injections, etc.

GitHub

AtakamaLLC/notanorm

Contribute to AtakamaLLC/notanorm development by creating an account on GitHub.

final plinth Mar 4, 2021, 7:36 PM

#

Why is this solution written in a subquery way ? Is there any difference in performance when it can be written in lesser lines?

#

There are few more where the query was written in a similar fashion

brave bridge Mar 4, 2021, 7:38 PM

#

where did you find the other solutions?

final plinth Mar 4, 2021, 7:38 PM

#

It was in github

brave bridge Mar 4, 2021, 7:39 PM

#

Well, if you want to see the performance of each, you should just create a very big table and check the query time 🙂

final plinth Mar 4, 2021, 7:39 PM

#

I'm doing a coursera course for a free trial and to check my query i saw this solution in github

#

But i don't get why it is written that way?

brave bridge Mar 4, 2021, 7:40 PM

#

I have no idea myself. Doesn't make much sense to me.

proven arrow Mar 4, 2021, 7:41 PM

#

They are pretty much doing the same thing. The other solution is just selecting from the subquery

#

There will be no performance difference

final plinth Mar 4, 2021, 7:41 PM

#

Yes, the results are same

brave bridge Mar 4, 2021, 7:42 PM

#

the way you wrote it seems most obvious to me.
Although the last one also gets the count -- why?

final plinth Mar 4, 2021, 7:42 PM

#

But i was just confused whats the point of extending no. Of lines as im a beginner

proven arrow Mar 4, 2021, 7:42 PM

#

You can always add EXPLAIN to the front of the query if you want to see how the query runs

final plinth Mar 4, 2021, 7:43 PM

#

proven arrow You can always add EXPLAIN to the front of the query if you want to see how the ...

Ooo will it run on the coursera platform?

proven arrow Mar 4, 2021, 7:44 PM

#

brave bridge the way you wrote it seems most obvious to me. Although the last one also gets ...

The last one is on a different table so it’s probably a different question they are attempting for

brave bridge Mar 4, 2021, 7:44 PM

#

ah right

proven arrow Mar 4, 2021, 7:44 PM

#

final plinth Ooo will it run on the coursera platform?

I don’t know, never heard of what that is

final plinth Mar 4, 2021, 7:44 PM

#

Yeah, i just attached that image because that query can also be written without using the subquery way

proven arrow Mar 4, 2021, 7:45 PM

#

But to answer your question there’s no difference. If this is some sort of competition people sometimes change their solution so it doesn’t look “copied”

final plinth Mar 4, 2021, 7:46 PM

#

I hope that is the answer to this

#

Thank you!

brave bridge Mar 4, 2021, 7:47 PM

#

@proven arrow random question: can you recommend any resources on relational algebra?

proven arrow Mar 4, 2021, 7:49 PM

#

brave bridge <@!613362435860070414> random question: can you recommend any resources on relat...

I had some links and books on my laptop which is broke currently and waiting for a new one. If I can find them again I will share them.

brave bridge Mar 4, 2021, 7:50 PM

#

alright, thanks 👍

jagged cove Mar 4, 2021, 7:50 PM

#

brave bridge <@!291255422885560320> You should never format SQL queries like that. If someone...

i could be wrong on this and i'm not a security expert, but can't people input things like url; DROP TABLE invites; # whatever the SQL comment symbol is ?

#

to kind of insert another command between the actual one

brave bridge Mar 4, 2021, 7:51 PM

#

jagged cove i could be wrong on this and i'm not a security expert, but can't people input t...

That's exactly what formatting in conn.execute is for. It escapes all special characters.

jagged cove Mar 4, 2021, 7:52 PM

#

oh it does? i had no idea

brave bridge Mar 4, 2021, 8:02 PM

#

jagged cove oh it does? i had no idea

Yep. Imagine making sure to escape every value correctly.

I wish there was a way to warn people on using f-strings or other formatting forms in queries.

jagged cove Mar 4, 2021, 8:07 PM

#

f strings also fuck up?

#

my whole database functions file uses c.execute(f'query with dumb var or whatever') lol

proven arrow Mar 4, 2021, 8:19 PM

#

jagged cove f strings also fuck up?

Whenever you have user input or unsanitised data coming from external sources like an api you should always use parameterised queries.

jagged cove Mar 4, 2021, 8:20 PM

#

true

#

i don't think i have any user-input sql queries but i'll be sure to check before release

#

in case i do, how do i fix this?

proven arrow Mar 4, 2021, 8:21 PM

#

Well Sql injection is one benefit. Prepared statements also have other benefits, like it can give you better performance.

proven arrow Mar 4, 2021, 8:23 PM

#

jagged cove in case i do, how do i fix this?

In your Sql query string the value, would be replaced by a placeholder. The actual value to be sent to the database should be supplied separately. What placeholder you need, and how you pass the values differ between some libraries that don’t follow the python db-api or however they implement it, but you should find examples in their documentation.

jagged cove Mar 4, 2021, 8:24 PM

#

alright thanks

#

do you recommend asking someone to do some basic pentesting on shit like this?

#

nothing too big just checking for input vulnerabilities etc

#

cause i've found another issue where display names (which are saved in the database) can cause errors

final plinth Mar 4, 2021, 8:25 PM

#

One more doubt,
where col Like '%.99%'
Why this condition works? Shouldn't a wildcard only work for text strings? @Lufthansa-Pilot#0623

jagged cove Mar 4, 2021, 8:25 PM

#

if you put multiple backslashes in your display name it returns an error for invalid tokens etc

final plinth Mar 4, 2021, 8:27 PM

#

I have used triple quotes and store the query in a var.. Never got any issues @rose

#

Ignore if it's out of context

nocturne lance Mar 4, 2021, 8:38 PM

#

Hello! How would i make a database that stores users balances in discord.py?
please ping me if you know!

jagged cove Mar 4, 2021, 8:43 PM

#

@nocturne lance

proven arrow Mar 4, 2021, 8:43 PM

#

final plinth One more doubt, `where col Like '%.99%'` Why this condition works? Shouldn't a ...

It matches characters and those are valid characters

jagged cove Mar 4, 2021, 8:43 PM

#

dm me, i'll show u how i did it

nocturne lance Mar 4, 2021, 8:46 PM

#

jagged cove <@!457382208878149642>

yes?

jagged cove Mar 4, 2021, 8:47 PM

#

dm me

final plinth Mar 4, 2021, 9:10 PM

#

proven arrow It matches characters and those are valid characters

The column contains float type values. '%' works on numbers, floats as well. In programming dont we use wildcards only on strings to match values?
How is it still working on floats?

proven arrow Mar 4, 2021, 9:15 PM

#

final plinth The column contains float type values. '%' works on numbers, floats as well. In ...

Some databases allow it

torn sphinx Mar 4, 2021, 9:20 PM

#

anyone here use pg_dump before?

torn sphinx Mar 4, 2021, 9:50 PM

#

hey i got a problem

#

need help with its not big of deal

#

pyfrom flask_wtf import FlaskForm

#

its unable to import

#

any help ?

#

nvm ish working

#

I have some static config data stored in yaml and I've created a class to correspond. Is this considered bad practice or even, the wrong approach?

My reason for doing so is to be able to easily access the variables with simple dot notation. There will be many instances across many scripts that these variables would need to be used.

What I do know is this is a rather static approach in that it relies on me to maintain the class should anything change in the yaml configuration. Is there any alternative that is more dynamic?

docker:
  socket_unix: null
  socket_tcp:
    ip_address: 0.0.0.0
    port: 2375
    cert_path: null
  containers:
    mongodb:
      name: mongodb_bot
      image: mongo
    redis:
      name: bot_redis
      image: redis

class docker:
    socket_unix = config['docker']['socket_unix']
    class socket_tcp:
        ip_address = config['docker']['socket_tcp']['ip_address']
        port = config['docker']['socket_tcp']['port']
        cert_path = config['docker']['socket_tcp']['cert_path']
    class containers:
        class mongodb:
            name = config['docker']['containers']['mongodb']['name']
            image = config['docker']['containers']['mongodb']['image']
        class redis:
            name = config['docker']['containers']['redis']['name']
            image = config['docker']['containers']['redis']['image']

Now accessible using dot notation...

print(docker.socket_unix)

glad spoke Mar 4, 2021, 10:14 PM

#

I've tried doing my script a little differently:

cursor.execute("DELETE FROM invites WHERE code=" + str(invite.code))

however I get this error:
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'q8e5Bvwq' in 'where clause'

q8e5Bvwq is the invite.code (a discord invite code which is no longer valid because I deleted it)

#

any help is hugely appreciated! :3

proven arrow Mar 4, 2021, 10:18 PM

#

torn sphinx I have some static config data stored in yaml and I've created a class to corres...

I wouldn’t say it’s bad practice. I’ve seen some popular frameworks do a similar thing.

torn sphinx Mar 4, 2021, 10:18 PM

#

proven arrow I wouldn’t say it’s bad practice. I’ve seen some popular frameworks do a similar...

Alright, it's still super clunky in that it relies on my to maintain the class should anything change in the yaml file

#

is there a more dynamic approach?

proven arrow Mar 4, 2021, 10:19 PM

#

glad spoke I've tried doing my script a little differently: ```py cursor.execute("DELETE F...

I believe you were already told to use parameterised query previously by another user. If you did you wouldn’t have this issue.

#

Your error is being raised because your value of the invite code was not passed to the query as a string literal, so it’s treated as a column.

proven arrow Mar 4, 2021, 10:22 PM

#

torn sphinx Alright, it's still super clunky in that it relies on my to maintain the class s...

Yeah that’s the only thing, with the approach you mentioned. You would have to recache the values.

torn sphinx Mar 4, 2021, 10:22 PM

#

I'm not so worried about the values, i'm more worried about new keys/entries to the config

proven arrow Mar 4, 2021, 10:22 PM

#

But how often would those values change?

torn sphinx Mar 4, 2021, 10:23 PM

#

Obviously ideally not at all lol

#

So not often

proven arrow Mar 4, 2021, 10:23 PM

#

New keys would be added with code change no?

torn sphinx Mar 4, 2021, 10:23 PM

#

But my OCD tells me things should be more dynamic than that

torn sphinx Mar 4, 2021, 10:23 PM

#

proven arrow New keys would be added with code change no?

I meant, new keys added in the yaml config. I'm trying to find a solution to have that dynamically updated in the class itself

#

Would probably require a whole seperate class dedicated to creating that I'd guess?

proven arrow Mar 4, 2021, 10:24 PM

#

Your over complicating it

torn sphinx Mar 4, 2021, 10:25 PM

#

Complicating it to make it less complicated later yes

#

I'm looking for some futureporoofing I guess

proven arrow Mar 4, 2021, 10:25 PM

#

You add new keys to the config when you change some piece of code or add new feature or something. So your going to have to write the code so it reads those keys anyways.

torn sphinx Mar 4, 2021, 10:26 PM

#

Let's say I add a new property/key to the yaml, I'd like that to be reflected in the class automatically. Therefore I only need to deal with one yaml file and nothing else

torn sphinx Mar 4, 2021, 10:26 PM

#

proven arrow You add new keys to the config when you change some piece of code or add new fea...

Ah true

#

Forget what I said

proven arrow Mar 4, 2021, 10:26 PM

#

The class is not magically going to understand what the keys in the config file mean

torn sphinx Mar 4, 2021, 10:26 PM

#

Yeah I guess, if python doesn't know it's there then there's no point lol

#

Has to be defined somewhere I guess

#

Yeah, my OCD of living in a perfect magic world haha

#

Cheers

soft gorge Mar 4, 2021, 10:29 PM

#

Anyone have recomendations for taking a SQLite DB and uploading to google sheets? I cam stuck at taking my Select and parsing through the contecnt of fetchall

proven arrow Mar 4, 2021, 10:30 PM

#

You can try export it as a csv and then import into sheets

#

Some database browsers have a built in option to do this

soft gorge Mar 4, 2021, 10:32 PM

#

I was using g spread so it could connect via sheets api and would still want to use that

#

It's simple to append new info just taking my info and creating a new list with variables is confusing me

proven arrow Mar 4, 2021, 10:33 PM

#

So what format does the api require your data to be in

proven arrow Mar 4, 2021, 10:33 PM

#

soft gorge It's simple to append new info just taking my info and creating a new list with ...

What exactly is confusing?

#

SQLite will return rows as tuples

#

Each element of the tuple is a column in the row

soft gorge Mar 4, 2021, 10:34 PM

#

variable = [dictName['Name']]

#

How the g spread api appends info from a dict

#

So to take the info from a tuple and convert into a dict

#

is confusing me

proven arrow Mar 4, 2021, 10:38 PM

#

Either loop through it and make your own list of dictionaries where each dict is a row.

#

Another way would be to use the sqlite Row class or make your own row_factory with sqlite.

#

The second option would return you the data in a dictionary format when you fetch it from the database. https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.row_factory Either you make your own row_factory or use the already made Sqlite.Row class for this. The code in the link gives examples.

soft gorge Mar 4, 2021, 10:42 PM

#

Row_Factory def will be helpful wow

#

Thank you so much man

torn sphinx Mar 4, 2021, 10:58 PM

#

how do I delte duplicate rows in psql?

glad spoke Mar 4, 2021, 11:04 PM

#

proven arrow I believe you were already told to use parameterised query previously by another...

Yeah I tried with that but it didn’t work sadly

torn sphinx Mar 4, 2021, 11:09 PM

#

@burnt turret so I haven't looked into the motor things yet but just wanted to see if everything worked at first with pymongo. Anyways for some reason it sometimes mixes the themes together so lets say id set it to theme animals, it would still use the previous theme yet for either all gifs. or it would use the previous theme for the slap command for example but the hug command would be updated to the current theme. idk it seems kind of weird to me lmao

coarse dust Mar 4, 2021, 11:28 PM

#

let's say I have some data coming over an API like

{"author": "foo", "books": [{"title": "bar"}, ..]}

i'd store it in two tables authors and books with a foreign key to author-id. so far so good, that works.
Now, both authors and books have IDs - over the API you can UPDATE the whole entry meaning you'd send something like:

{"author": "foo", "books": [{"title": "foobar"}, ..]}

how would I go about properly updating that one book title that changed? or how would something like that usually be done?

white geode Mar 5, 2021, 12:12 AM

#

Do you think mongoDB triggers would be a good solution to this problem?
#help-croissant message

Or would there be a way to do it in Python/Mongo?

rough hearth Mar 5, 2021, 1:49 AM

#

@upbeat granite this channel might be the one

#

though I'm not sure what a p2p is

chilly hornet Mar 5, 2021, 2:52 AM

#

Hi guys! new to the community here. I have questions specifically about python pandas that no one I know seems to be able to answer. Is anyone willing to take a jab at my question? lemon_hyperpleased

rain plank Mar 5, 2021, 3:09 AM

#

torn sphinx how do I delte duplicate rows in psql?

Define "duplicate rows"

torn sphinx Mar 5, 2021, 3:10 AM

#

nvm

worldly plaza Mar 5, 2021, 4:18 AM

#

@burnt turret What should I do to get the primary key?

burnt turret Mar 5, 2021, 4:18 AM

#

show your code and explain the question again please

worldly plaza Mar 5, 2021, 4:19 AM

#

    @tasks.loop(seconds=30.0)
    async def checker(self):
        connection = mysql.connector.connect(
        host='localhost',
        user='root',
        password='open1234',
        database='discordbot',
        use_unicode=True,
        charset='utf8',
        port=3306
        )
        connection.autocommit = True
        cursor = connection.cursor(buffered=True)
        cursor.execute("DELETE FROM muted WHERE clock <= %(time)s", { "time": datetime.now() })
        cursor.close()
        connection.close()

I am trying to get their user_id before I delete them from the table then set the user_id to a variable so I can then remove their muted role aswell as remove them from the database of muted users @burnt turret

burnt turret Mar 5, 2021, 4:21 AM

#

just googled it and apparently MySQL doesn't have an equivalent to the RETURNING clause I'd said about earlier

#

you'll need two queries, first to SELECT and then to DELETE

worldly plaza Mar 5, 2021, 4:22 AM

#

burnt turret you'll need two queries, first to SELECT and then to DELETE

So the first would be similar to the second but its purpose would be to look for those in the db and get the user_id and the second would remove them from the db correct?

burnt turret Mar 5, 2021, 4:22 AM

#

yep

#

also, two other things

#

you're using mysql.connector which is blocking - use aiomysql

worldly plaza Mar 5, 2021, 4:23 AM

#

Maybe later

burnt turret Mar 5, 2021, 4:23 AM

#

the other thing is that opening a new connection each time is wasteful, you should be making a single connection for your bot and using it everywhere

#

you can make one on the bot starting up and assign it to a bot variable, which would then be accessible across all files

worldly plaza Mar 5, 2021, 4:24 AM

#

burnt turret you can make one on the bot starting up and assign it to a bot variable, which w...

Aren't variables reset per function? Also once I select any piece of data with an expired date how will I get the primary key?

burnt turret Mar 5, 2021, 4:25 AM

#

Bot variables are tied to your instance of commands.Bot

worldly plaza Mar 5, 2021, 4:25 AM

#

burnt turret Bot variables are tied to your instance of `commands.Bot`

So I would define it with my command_prefix n shit?

burnt turret Mar 5, 2021, 4:26 AM

#

sure, like

bot.db = mysql.connector.connect(...
``` after you do ```bot = commands.Bot(command_prefix=...)```

#

this would then be accessible everywhere as bot.db, and in cogs that'd be self.bot.db

worldly plaza Mar 5, 2021, 4:27 AM

#

Alright, I will do that tomorrow, anyhow how do i fetch the primary key of a piece of data?

burnt turret Mar 5, 2021, 4:27 AM

#

worldly plaza Aren't variables reset per function? Also once I select any piece of data with a...

I don't understand - don't you know what the primary key of the table will be?

#

so can't you just model your query to just get that column

worldly plaza Mar 5, 2021, 4:28 AM

#

burnt turret so can't you just model your query to just get that column

Would it look something like this?

SELECT user_id FROM muted WHERE clock <= %(time)s

burnt turret Mar 5, 2021, 4:28 AM

#

yeah

worldly plaza Mar 5, 2021, 4:28 AM

#

Then I fetch the data and do my magic to remove the role right?

burnt turret Mar 5, 2021, 4:29 AM

#

I guess so

worldly plaza Mar 5, 2021, 4:30 AM

#

burnt turret I guess so

So does this work?

    @tasks.loop(seconds=30.0)
    async def checker(self):
        connection = mysql.connector.connect(
        host='localhost',
        user='root',
        password='open1234',
        database='discordbot',
        use_unicode=True,
        charset='utf8',
        port=3306
        )
        connection.autocommit = True
        cursor = connection.cursor(buffered=True)
        cursor.execute("SELECT user_id FROM muted WHERE clock <= %(time)s", { "time": datetime.now() })
        member = cursor.fetchall()
        muted = get_role(815735188045692969)
        await int(member).remove_roles(muted)
        cursor.execute("DELETE FROM muted WHERE clock <= %(time)s", { "time": datetime.now() })
        cursor.close()
        connection.close()

burnt turret Mar 5, 2021, 4:31 AM

#

the database part should be fine I think

#

not so much about the dpy part - member will now be a list

worldly plaza Mar 5, 2021, 4:31 AM

#

Ah shit

burnt turret Mar 5, 2021, 4:32 AM

#

you'll need to get that user's discord.Member object (guild.get_member possibly) first, before calling remove_roles on it

#

get_role(815735188045692969) this cannot be used alone like this, it's a method

#

I don't remember of what exactly, most probably...guild I'd guess. Or bot

worldly plaza Mar 5, 2021, 4:33 AM

#

burnt turret I don't remember of what exactly, most probably...guild I'd guess. Or `bot`

It is guild

worldly plaza Mar 5, 2021, 4:33 AM

#

burnt turret not so much about the dpy part - `member` will now be a list

Also how do I convert it not into a list?

burnt turret Mar 5, 2021, 4:34 AM

#

thing is at any time you may be getting more than one users who meet that condition right (more than one user's mute may be expiring then)

#

you will want it to be a list

#

the better solution would be to loop over the list it gives you and do the role removing for each member

worldly plaza Mar 5, 2021, 4:36 AM

#

burnt turret the better solution would be to loop over the list it gives you and do the role ...

So will I want to do something like this?

for members in member:
    guild.get_member(members)

burnt turret Mar 5, 2021, 4:36 AM

#

yeah

#

those variables are a bit misleading though haha

worldly plaza Mar 5, 2021, 4:36 AM

#

I am running out of names Sadge

burnt turret Mar 5, 2021, 4:37 AM

#

assign the list that the db gives you to members and then do for member in members

worldly plaza Mar 5, 2021, 4:38 AM

#

burnt turret assign the list that the db gives you to `members` and then do `for member in me...

Would this work?

member = cursor.fetchall()
        for members in member:
            guild.get_member(members)
        muted = guild.get_role(815735188045692969)
        await members.remove_roles(muted)

burnt turret Mar 5, 2021, 4:38 AM

#

the indentation looks off

worldly plaza Mar 5, 2021, 4:38 AM

#

Just discord

#

My indentation is normal lol

jolly rivet Mar 5, 2021, 4:38 AM

#

hello guys

burnt turret Mar 5, 2021, 4:38 AM

#

burnt turret assign the list that the db gives you to `members` and then do `for member in me...

also please do this those variable names hurt me 😔

jolly rivet Mar 5, 2021, 4:39 AM

#

I know python basics

worldly plaza Mar 5, 2021, 4:39 AM

#

burnt turret also please do this those variable names hurt me 😔

Okay lol

#

@burnt turret Since I don't have any args how am I suppose to get guild? I don't have ctx or anything

burnt turret Mar 5, 2021, 4:41 AM

#

oh, you don't store guild ID along with the user IDs?

#

would be easier I'd say

worldly plaza Mar 5, 2021, 4:42 AM

#

burnt turret oh, you don't store guild ID along with the user IDs?

In the function I only passed in self

burnt turret Mar 5, 2021, 4:42 AM

#

No I mean in your database

#

if I were you, I'd store the guild ID of each user as well

worldly plaza Mar 5, 2021, 4:42 AM

#

Oh yeah I only have 2 things, mute time and user_id

burnt turret Mar 5, 2021, 4:43 AM

#

if the same person is muted in two guilds using your bot, there'd be no way of knowing which guild to unmute in if you don't store the guild ID as well

worldly plaza Mar 5, 2021, 4:43 AM

#

burnt turret if the same person is muted in two guilds using your bot, there'd be no way of k...

The bot is only for one server

burnt turret Mar 5, 2021, 4:43 AM

#

oh in that case you can just hard code your guild ID in

#

use guild = bot.get_guild(your guild id)

worldly plaza Mar 5, 2021, 4:44 AM

#

burnt turret use `guild = bot.get_guild(your guild id)`

would I do self.client?

burnt turret Mar 5, 2021, 4:44 AM

#

yeah - whenever I say bot. i just mean "your instance of commands.Bot" - if you call it client do that

#

so yes, self.client.get_guild(...

worldly plaza Mar 5, 2021, 4:46 AM

#

burnt turret yeah - whenever I say `bot.` i just mean "your instance of commands.Bot" - if yo...

It said NoneType has not attribute to get_role

burnt turret Mar 5, 2021, 4:46 AM

#

that's because the bot's cache hasn't filled yet, because the loop is running before the bot is ready

#

so get_guild returned None

worldly plaza Mar 5, 2021, 4:47 AM

#

burnt turret so `get_guild` returned None

So will it work just fine?

burnt turret Mar 5, 2021, 4:47 AM

#

it should

#

if you've got the IDs right

#

you could have the loop wait for the bot to be ready by adding a before_loop

#

https://discordpy.readthedocs.io/en/latest/ext/tasks/index.html there's an example titled "Waiting until the bot is ready"

worldly plaza Mar 5, 2021, 4:49 AM

#

burnt turret you could have the loop wait for the bot to be ready by adding a `before_loop`

What would I put in said before_loop

burnt turret Mar 5, 2021, 4:49 AM

#

refer the example I linked, it gives you exactly what you need

worldly plaza Mar 5, 2021, 4:50 AM

#

burnt turret refer the example I linked, it gives you exactly what you need

The bot isn't removing anything from the db etc. anymore

burnt turret Mar 5, 2021, 4:51 AM

#

how are you testing this?

worldly plaza Mar 5, 2021, 4:51 AM

#

Muting the person for 1 minute and waiting for them to be unmuted

burnt turret Mar 5, 2021, 4:52 AM

#

huh. can you show the code again

worldly plaza Mar 5, 2021, 4:52 AM

#

burnt turret huh. can you show the code again

    @tasks.loop(seconds=30.0)
    async def checker(self):
        connection = mysql.connector.connect(
        host='localhost',
        user='root',
        password='open1234',
        database='discordbot',
        use_unicode=True,
        charset='utf8',
        port=3306
        )
        connection.autocommit = True
        cursor = connection.cursor(buffered=True)
        guild = self.client.get_guild(814695010603368488)
        cursor.execute("SELECT user_id FROM muted WHERE clock <= %(time)s", { "time": datetime.now() })
        members = cursor.fetchall()
        for member in members:
            guild.get_member(member)
        muted = guild.get_role(815735188045692969)
        await member.remove_roles(muted)
        cursor.execute("DELETE FROM muted WHERE clock <= %(time)s", { "time": datetime.now() })
        cursor.close()
        connection.close()

burnt turret Mar 5, 2021, 4:52 AM

#

        muted = guild.get_role(815735188045692969)
        await member.remove_roles(muted)
``` these two lines need to be indented under the for loop

#

also, the datetime.now() will be slightly different between both queries

#

I'd assign it to a variable or something at the start and then use that variable in both queries

worldly plaza Mar 5, 2021, 4:54 AM

#

burnt turret also, the `datetime.now()` will be slightly different between both queries

Alright, also it is saying get_member is undefined, how will I define it?

burnt turret Mar 5, 2021, 4:55 AM

#

where is it saying that?

#

for guild.get_member?

worldly plaza Mar 5, 2021, 4:55 AM

#

burnt turret where is it saying that?

Wait no I read it wrong, it says NoneType has no attribute to get_member

burnt turret Mar 5, 2021, 4:56 AM

#

that's the same thing as I'd said earlier about the get_guild returning None

#

sorry I gotta go now though :/

worldly plaza Mar 5, 2021, 4:56 AM

#

burnt turret sorry I gotta go now though :/

Ah alright, thank you for your help :)

torn sphinx Mar 5, 2021, 1:17 PM

#

    @command()
    async def slap(self, ctx: Context, member : Member ):
        data = db_themes.find_one({"guild_id": ctx.guild.id})
        if data is None:
            slap_gifs = self.gifs["anime"]["slap"]
        else:
            slap_gifs = self.gifs[data["Theme"]]["slap"]

        if "used_slaps" not in dir(self) or not self.used_slaps:
            self.used_slaps = copy.copy(slap_gifs)
        emb = Embed(title=f" **{ctx.author.name}** slaps **{member.name}**...", color=0x3498db)
        bla = random.choice(self.used_slaps)
        self.used_slaps.remove(bla)
        emb.set_image(url=bla)

If I set the theme to for example humans, it still uses the previous set theme until I reload the cog. Sometimes it even uses gifs of 2 of the 3 themes I have when both of them are not the theme I set it to

#

  @set.command(name="theme")
    @has_permissions(administrator=True)
    async def set_theme(self,ctx: Context, theme: str):
        if theme not in self.gifs.keys():
            await ctx.send("this theme doesn't exist")
        if theme in self.gifs.keys():
            self.slap_gifs = self.gifs[theme]["slap"]
            db_themes.update_one({"guild_id": ctx.guild.id}, {"$set": {"Theme": theme}}, upsert=True)
            data = db_themes.find_one({"guild_id": ctx.guild.id})
            if data is data:
                await ctx.send("theme has been set to " + theme)

this it the set theme command to set a theme

#

wondering what I did wrong and how to fix this

torn sphinx Mar 5, 2021, 1:29 PM

#

torn sphinx ```py @set.command(name="theme") @has_permissions(administrator=True) ...

@commands.has_permissions(administrator=True)

#

no need

#

I use that

#

from discord import *
from discord.ext.commands import *

#

the commands

#

work

#

just

#

#

so I set the theme to humans which should get a gif out of the humans category

#

in my json file

#

but it still gets them out of the animals category

#

when I reload the cog it works good again but everytime I set a theme sometimes it works sometimes it doesn't

chrome monolith Mar 5, 2021, 1:46 PM

#

Can I take another database and expand on its attributes? How?

glad spoke Mar 5, 2021, 1:53 PM

#

sql = "DELETE FROM invites WHERE code = %s"
cursor.execute(sql, invite.code)

code is just a discord invite code.

For some reason this gives me the error:
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s' at line 1

#

I've tried several things to fix this but have made no real progress

#

any help is greatly appreciated :D

burnt turret Mar 5, 2021, 2:02 PM

#

pretty sure mysql connector expects the second argument to be a tuple

#

so,

cursor.execute(sql, (invite.code,))
``` (the comma at the end makes it a single element tuple)

#

also if you're using this for a discord bot, mysql connector is blocking https://discordpy.readthedocs.io/en/latest/faq.html#what-does-blocking-mean
you should be using aiomysql

torn sphinx Mar 5, 2021, 2:09 PM

#

@burnt turret do u know why my database isn't fully functional though?

#

and changed it to motor btw

burnt turret Mar 5, 2021, 2:11 PM

#

can you link me to your code?

#

a bit busy atm but i'll try looking through it

torn sphinx Mar 5, 2021, 2:12 PM

#


    @command()
    async def slap(self, ctx: Context, member : Member ):
        data = await db_themes.find_one({"guild_id": ctx.guild.id})
        if data is None:
            slap_gifs = self.gifs["anime"]["slap"]
        else:
            slap_gifs = self.gifs[data["Theme"]]["slap"]

        if "used_slaps" not in dir(self) or not self.used_slaps:
            self.used_slaps = copy.copy(slap_gifs)
        emb = Embed(title=f" **{ctx.author.name}** slaps **{member.name}**...", color=0x3498db)
        bla = random.choice(self.used_slaps)
        self.used_slaps.remove(bla)
        emb.set_image(url=bla)

#

the command that retrieves the info from the database

#

    @set.command(name="theme")
    @has_permissions(administrator=True)
    async def set_theme(self,ctx: Context, theme: str):
        if theme not in self.gifs.keys():
            await ctx.send("this theme doesn't exist")
        if theme in self.gifs.keys():
            self.slap_gifs = self.gifs[theme]["slap"]
            await db_themes.update_one({"guild_id": ctx.guild.id}, {"$set": {"Theme": theme}}, upsert=True)
            await ctx.send("theme has been set to " + theme)

the command that writes to the database

#

and sets the theme

#

when I execute the set theme command multiple times it doesn't change the theme of the gives anymore eventho if I print data["Theme"] it says the theme changed

burnt turret Mar 5, 2021, 2:17 PM

#

so it changes it the first time you use the command only?

torn sphinx Mar 5, 2021, 2:18 PM

#

Yeah mainly

burnt turret Mar 5, 2021, 2:19 PM

#

what does it send in the channel? that it has changed the theme?

#

because I don't see anything wrong with that query

torn sphinx Mar 5, 2021, 2:21 PM

#

Yeah it does

#

I made a command

#

That shows what theme the server currently has

#

And it says

#

It's the theme I set

burnt turret Mar 5, 2021, 2:22 PM

#

so you can be sure that the database is getting updated

#

the issue must be in the first command then

torn sphinx Mar 5, 2021, 2:22 PM

#

So im thinking the problem is with the slap command

#

Getting the new theme gifs from the json file

burnt turret Mar 5, 2021, 2:23 PM

#

if "used_slaps" not in dir(self) or not self.used_slaps: i don't understand this condition

#

aren't they both doing the same thing

#

okay wait not really

torn sphinx Mar 5, 2021, 2:24 PM

#

It's like

#

You have multiple gifs

#

In the dictionary right

#

And i want it to not use the same gif twice

#

Till it went through all gifs

#

Of that theme

#

Couldve done this with a for loop too probably but I couldnt figure that one out

white steppe Mar 5, 2021, 3:33 PM

#

what might be wrong with this code ? ```<?php

// Prepare variables for database connection

require_once "connectconfig.php";

// Connect to your database

$conn = new mysqli("$host", "$db_user", "$db_password", "$db_name");

$conn=mysqli_connect("$host", "$db_user", "$db_password", "$db_name");

if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
exit();
}

//$variableINT = $variable;
//$variableINT = "elomelo";
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
exit();
}

$sql = "SELECT * FROM datatable ORDER BY minid DESC LIMIT 1;";
$result = mysqli_query($conn,$sql);

$data = mysqli_fetch_array($result,MYSQLI_NUM);

//$data = mysqli_fetch_row($sql);
//if ($conn->query($sql) == TRUE) {
//echo "Jakis numerek2";
// } else {
// echo "Error: " . $sql . "<br>" . $conn->error;
//}
$conn->close();

// Prepare the SQL statement
echo "temp 1 = ";
echo $data[0];
echo "temp 2 = ";
echo $data[1];
// Execute SQL statement

?>

#

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in retrivedatauser.php on line 28
temp 1 = temp 2 =

torn sphinx Mar 5, 2021, 3:36 PM

#

@burnt turret so yeah it basically only works if u reload the cog / restart the bot

burnt turret Mar 5, 2021, 3:44 PM

#

sorry, I'm busy right now, maybe someone else can answer

calm barn Mar 5, 2021, 4:02 PM

#

does anyone of u use

#

mongodb?

sick perch Mar 5, 2021, 4:14 PM

#

Just threw one out for postgresql

brave bridge Mar 5, 2021, 4:36 PM

#

@white steppe This is a Python server. If you want to get help with PHP, you might ask in an off-topic channel or find a server dedicated to PHP.

torn sphinx Mar 5, 2021, 5:17 PM

#

@set.command(name="theme")
    @has_permissions(administrator=True)
    async def set_theme(self,ctx: Context, theme: str):
        if theme not in self.gifs.keys():
            await ctx.send("this theme doesn't exist")
        if theme in self.gifs.keys():
            await db_themes.update_one({"guild_id": ctx.guild.id}, {"$set": {"Theme": theme}}, upsert=True)
            await ctx.send("theme has been set to " + theme)

have this command to set a theme ^^

    @command()
    async def slap(self, ctx: Context, member : Member ):
        data = await db_themes.find_one({"guild_id": ctx.guild.id})
        if data is None:
            slap_gifs = self.gifs["anime"]["slap"]
        else: 
            slap_gifs = self.gifs[data["Theme"]]["slap"]

to retrieve theme from database and make it get the slaps from that theme from the json file.
but it only sets the gifs to that theme when I reload or in rare occasions it updates it.

How do I fix this

proven arrow Mar 5, 2021, 5:38 PM

#

white steppe what might be wrong with this code ? ```<?php // Prepare variables for data...

Your query is failing and returning false hence why it gets the Boolean. Have some error handling to see why it fails.

#

Also that’s php so maybe wrong place to ask

torn sphinx Mar 5, 2021, 6:35 PM

#

hi

#

In mysql's DATETIME datatype, I can save datetime.datetime.utcnow()?

proven arrow Mar 5, 2021, 7:00 PM

#

torn sphinx In mysql's `DATETIME` datatype, I can save `datetime.datetime.utcnow()`?

You can format the time with python using strftime method. The format to be inserted needs to be
YYYY-MM-DD HH:MI:SS

knotty terrace Mar 5, 2021, 7:16 PM

#

Hey,I'm relatively new to coding, and for the last few months I've been experimenting with swift and using firebase for a backend for a messaging application. I've just completed it, and now want to create the same app, but using a sql-database, using mysql as the rdbms. I've been doing some research into it and have begun learning sql. But it also seems that I will need to learn another language e.g php or python to do so.My question is, if i were to learn python is it possible to code my frontend in swift whilst coding the backend in both sql and python?Sorry if its a stupid question.Thanks for your time

worldly plaza Mar 5, 2021, 7:17 PM

#

Hello, my python bot is saying it cannot connect to my SQL database despite me establishing a connection.

mysql.connector.errors.OperationalError: 2055: Lost connection to MySQL server at 'localhost:3306', system error: 10038 An operation was attempted on something that is not a socke

burnt turret Mar 5, 2021, 7:19 PM

#

is that the full error?

worldly plaza Mar 5, 2021, 7:19 PM

#

burnt turret is that the full error?

Yup

torn sphinx Mar 5, 2021, 7:19 PM

#

anand

#

i found the mistake

burnt turret Mar 5, 2021, 7:19 PM

#

oh what was it?

torn sphinx Mar 5, 2021, 7:19 PM

#

okay so

burnt turret Mar 5, 2021, 7:19 PM

#

yours was the themes not updating problem right?

torn sphinx Mar 5, 2021, 7:19 PM

#

it does update the themes

#

but

#

#

this part

#

my theory is that it emptys the current theme's list

#

first

#

so it first uses all gifs in there

#

cause it basically makes a new list with the non uses gifs

#

used*

burnt turret Mar 5, 2021, 7:20 PM

#

oh pithink

torn sphinx Mar 5, 2021, 7:21 PM

#

so now I need to find a way to fix that 😂

worldly plaza Mar 5, 2021, 7:22 PM

#

burnt turret is that the full error?

I really have no idea why it will not connect to the db

burnt turret Mar 5, 2021, 7:22 PM

#

torn sphinx so now I need to find a way to fix that 😂

maybe ask in pygen or in a help channel #❓｜how-to-get-help

burnt turret Mar 5, 2021, 7:23 PM

#

worldly plaza I really have no idea why it will not connect to the db

were you whom I'd told earlier about how it is super wasteful to make a new connection everytime?

worldly plaza Mar 5, 2021, 7:23 PM

#

burnt turret were you whom I'd told earlier about how it is super wasteful to make a new conn...

Yes, and I am doing that now

burnt turret Mar 5, 2021, 7:23 PM

#

googling the error seems to suggest this happens because of very large queries and such

worldly plaza Mar 5, 2021, 7:23 PM

#

It is querrying like a very small piece of info

burnt turret Mar 5, 2021, 7:24 PM

#

but i'm gonna guess that your program made more connections than the server could handle. unlikely, but that's the only reason i can think of.

white steppe Mar 5, 2021, 7:24 PM

#

brave bridge <@!512642515447578634> This is a Python server. If you want to get help with PHP...

ok

worldly plaza Mar 5, 2021, 7:26 PM

#

burnt turret but i'm gonna guess that your program made more connections than the server coul...

So what should I do to fix this?

burnt turret Mar 5, 2021, 7:27 PM

#

what i'd said earlier - rewrite your code to use a single connection/single connection pool

worldly plaza Mar 5, 2021, 7:27 PM

#

burnt turret what i'd said earlier - rewrite your code to use a single connection/single conn...

That is what I am trying to do

proven arrow Mar 5, 2021, 7:27 PM

#

knotty terrace Hey,I'm relatively new to coding, and for the last few months I've been experime...

Sure you can. In fact I have a similar setup for some of my mobile apps, for the backend.

worldly plaza Mar 5, 2021, 7:28 PM

#

burnt turret what i'd said earlier - rewrite your code to use a single connection/single conn...

I added

client.db = mysql.connector.connect(host='localhost', user='root', password='open1234', database='discordbot', use_unicode=True, charset='utf8', port=3306)

to the beginning of my code

burnt turret Mar 5, 2021, 7:28 PM

#

worldly plaza Hello, my python bot is saying it cannot connect to my SQL database despite me e...

heyo Lufthansa-Pilot, what do you think is causing this?

burnt turret Mar 5, 2021, 7:29 PM

#

worldly plaza I added ```py client.db = mysql.connector.connect(host='localhost', user='root'...

if you're rewriting it now would also be a good time to move to an async driver like aiomysql I'd say

worldly plaza Mar 5, 2021, 7:29 PM

#

I am not rewriting just changing it's format

proven arrow Mar 5, 2021, 7:31 PM

#

burnt turret heyo Lufthansa-Pilot, what do you think is causing this?

Probably with how they setup their connection

burnt turret Mar 5, 2021, 7:33 PM

#

#databases message this is an example, would it be possible for it to error just because of too many connections?

worldly plaza Mar 5, 2021, 7:34 PM

#

burnt turret https://discord.com/channels/267624335836053506/342318764227821568/8172499785178...

I created a pool and now it says NoneType has no attribute to cursor

proven arrow Mar 5, 2021, 7:34 PM

#

Probably not, because too many connections has its own specific error which would say that

#

But you should avoid making connections like that in a loop anyways.

worldly plaza Mar 5, 2021, 7:35 PM

#

Why is it returning None?

proven arrow Mar 5, 2021, 7:35 PM

#

What is none?

worldly plaza Mar 5, 2021, 7:35 PM

#

I have

cursor = self.client.db.cursor(buffered=True)

proven arrow Mar 5, 2021, 7:37 PM

#

What is db defined as?

worldly plaza Mar 5, 2021, 7:37 PM

#

proven arrow What is db defined as?

client.db = mysql.connector.connect(host='localhost', user='root', password='open1234', database='discordbot', use_unicode=True, charset='utf8', port=3306, pool_name="discord_pool", pool_size=4)

proven arrow Mar 5, 2021, 7:40 PM

#

Are you even running that statement. Also I think you should stop troubleshooting that and first switch to a async library because it looks like your making a discord bot. So no point continuing with your current module because either it’ll cause more issues down the line/ or you will have to replace it later on.

worldly plaza Mar 5, 2021, 7:44 PM

#

proven arrow Are you even running that statement. Also I think you should stop troubleshootin...

It is a bit of a deadline situation, I am going to do it like this and once released I will roll out some updates to switch to the async SQL version.

polar osprey Mar 5, 2021, 8:23 PM

#

Hey guys I have a string column in a table set to unique and allow Nulls

#

I enter informatino like this:

        direct_cursor.execute(
            f"INSERT INTO Material (mat_nr, etat_nr, bezeichnung, verbrauchsmat, notiz)"
            f"VALUES ({mat_nr},{etat_nr},'{bez}','{verb}','{note}')")

proven arrow Mar 5, 2021, 8:24 PM

#

worldly plaza It is a bit of a deadline situation, I am going to do it like this and once rele...

Alright then in that case you need to first get a connection from the pool which you are not doing currently. You can read more here with examples https://dev.mysql.com/doc/connector-python/en/connector-python-connection-pooling.html

polar osprey Mar 5, 2021, 8:24 PM

#

problem with that is that it doesn't work bc it requires the ' ' quotations around etat_nr (which is the column I'm talking about) since it's string

#

but if I put them in there and the field is left empty it's gonna literally write "Null" in there because it regards Null as a string

#

is there a more elegant solution to this than to catch it with an if clause and have two seperate commands one for when it's empty and one for when it contains a string

torn sphinx Mar 5, 2021, 8:42 PM

#

There's another problem with your statement

polar osprey Mar 5, 2021, 8:42 PM

#

there is?

torn sphinx Mar 5, 2021, 8:43 PM

#

Your statement is vulnerable to SQL injection if any of the variables are gathered from user input

sand timber Mar 5, 2021, 8:44 PM

#

hey, I was wondering if I could make this postgresql query more performant / easier to read:

SELECT level, xp FROM dc_user WHERE (level >= (SELECT level FROM dc_user WHERE user_id = $1) AND xp > (SELECT xp FROM dc_user WHERE user_id = $1)) OR level > (SELECT level FROM dc_user WHERE user_id = $1) ORDER BY level DESC, xp DESC```

torn sphinx Mar 5, 2021, 8:44 PM

#

https://en.wikipedia.org/wiki/Prepared_statement#Python_DB-API

Prepared statement

In database management systems (DBMS), a prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with high efficiency. Typically used with SQL statements such as queries or updates, the prepared statement takes the form of a template into which certain constant values are subs...

proven arrow Mar 5, 2021, 8:44 PM

#

Where are you seeing null ? @polar osprey

#

If it’s in the database then null is not an actual value. It just says null to show you there is nothing there.

polar osprey Mar 5, 2021, 8:46 PM

#

proven arrow If it’s in the database then null is not an actual value. It just says null to s...

Yeah that's what I'm talking about. I want to leave the field blank.

proven arrow Mar 5, 2021, 8:47 PM

#

It is blank

#

Null equals nothing

polar osprey Mar 5, 2021, 8:47 PM

#

is [Null] = "" ?

#

no it's not I just double checked. Would have surprised me too

#

look at this screenshot

#

I want [NULL] to be in there and not ""

#

because the column is set to unique which would mean I could only have one blank ("") field in there. What I want though is to allow nothing to be there but if something is there it has to be unique

proven arrow Mar 5, 2021, 8:51 PM

#

Yeah that’s not null then what you have there

polar osprey Mar 5, 2021, 8:52 PM

#

which was my point from the start 🙂

#

that is my problem

proven arrow Mar 5, 2021, 8:52 PM

#

Then In your query you can leave out specifying that column for the insert

polar osprey Mar 5, 2021, 8:52 PM

#

but I think zombee has the solution for me

proven arrow Mar 5, 2021, 8:52 PM

#

polar osprey but I think zombee has the solution for me

That’s not a solution, it’s how you should be writing your query to avoid security issues

polar osprey Mar 5, 2021, 8:53 PM

#

torn sphinx https://en.wikipedia.org/wiki/Prepared_statement#Python_DB-API

thanks a lot. It's been a while and I totally forgot about this. I even prepared two cursors in my code one for direct and one for prepared.

#

I'm pretty sure I can solve my problem with this

#

@proven arrow I'm pretty sure it will solve both

#

if it doesn't I'll rephrase my question because I don't think you understood what my problem was

#

but I sincerely thank you for trying to help me

#

sorry I did a bad job of explaining what I wanted

proven arrow Mar 5, 2021, 8:55 PM

#

You need 2 queries. The inner query selects the MAX value of that column, whos result you can use as a filter in the where clause of the outer query.

#

SELECT * FROM table WHERE delka = (SELECT MAX(delka) FROM table)

native bridge Mar 5, 2021, 9:01 PM

#

How do I correctly delete a collection in mongodb? Im using

db_guilds._id = (f'{guild.id}') # insertion
#some code
db_guilds.remove({_id: ObjectId(f'{guild.id}')})``` but I get the problem that _id isnt defined and i dont know why since I saw a  lot of  examples on the internet with exactly this soultion. Does anyone know how to  fix this?

empty bone Mar 5, 2021, 9:16 PM

#

I need some help with my script: https://stackoverflow.com/q/66499396/15073127

Stack Overflow

get saperated string with a comma from a table python

I trying to get saperate strings from a columm in a table. If I output them without anything they are looking like this:
('https://google.com',)
('http://example.com',)
('http://example.com,http://...

exotic scaffold Mar 5, 2021, 9:28 PM

#

Error: https://paste.pythondiscord.com/huhiloduxe.sql

tender jasper Mar 6, 2021, 4:00 AM

#

    tk.Button(Entersubj, text = "Submit", command = lambda: SubjectstoDB(subjectentered,subname)).grid(row = 7, column = 0)
  File "C:\Users\vivaa\Desktop\aplicacion\masterappf.py", line 226, in SubjectstoDB
    c.execute("SELECT student_id FROM student WHERE student_name = ?",(subname.get(),))
AttributeError: 'str' object has no attribute 'get'``` What does this error mean?

burnt turret Mar 6, 2021, 4:19 AM

#

subname is a string

#

and strings don't have a method called get

tender jasper Mar 6, 2021, 5:25 AM

#

Thanks!

#

File "C:\Users\vivaa\Desktop\aplicacion\masterappf.py", line 319, in Displaygrades
    c.execute("SELECT subject_id FROM StudentSubJunc WHERE student_id = ?",(studid[0],))
sqlite3.OperationalError: no such column: subject_id```

#

conn.execute("""CREATE TABLE IF NOT EXISTS StudentSubJunc(
                student_id INTEGER,
                subject_id TEXT,
                FOREIGN KEY(student_id) REFERENCES student(student_id)
                FOREIGN KEY(subject_id) REFERENCES subjects(subject_id)
                )""")```

#

Huh??????????

dusk oriole Mar 6, 2021, 5:26 AM

#

hello so I have this method that is suposed to search in my data base if a student exists or doesn't but it always gives me that the student doesn't exist even tho i have it on my table "etudiant " I tried every other way possible but it always give me that the student doesn't exist (am using pyqt5 I enter the string "matricule " that is sepcial for each student from a qlineedit )

#

if anyone can help me please

burnt turret Mar 6, 2021, 6:05 AM

#

dusk oriole hello so I have this method that is suposed to search in my data base if a s...

you don't need to manually quote the argument (don't put single quotes around the %s)
in your cursor.execute, the second argument needs to be a tuple (i.e it should be like (matricule,) with the comma at the end)

dusk oriole Mar 6, 2021, 6:11 AM

#

😭😭😭😭😭 oh my god it worked THANK U

fickle lion Mar 6, 2021, 6:12 AM

#

what does the () control for sql datatypes? Like varchar(45), smallint(10), etc.

floral violet Mar 6, 2021, 6:21 AM

#

how to install mysql for windows10 64-bit?

proven arrow Mar 6, 2021, 6:37 AM

#

fickle lion what does the () control for sql datatypes? Like varchar(45), smallint(10), etc.

For int types it makes no difference to the value stored in database as the size is still the same. The only time it might change something is if you use something like zerofill for the column (but even that is just for how the number is displayed).
As for varchar the variable length is used for enforcing constraints on that column. So if you were storing for example a name, you could set it to varchar(100) so you can’t enter more characters than that.

twilit hinge Mar 6, 2021, 8:53 AM

#

for anyone using asyncpg in a controller / service / repository project, how are you handling transactions? do you create connection object in service layer and pass it to the repositories?

#

tried to make @transactional like Spring that sets connection to ContextVar in background (with repositories pulling from this ContextVar), but that was closest I could get to something like Spring where transaction management is handled by a java proxy

sick perch Mar 6, 2021, 9:55 AM

#

twilit hinge tried to make `@transactional` like Spring that sets connection to `ContextVar` ...

In python having repository as pattern seems uncommon to me, typically they are integrated with the models that are integrated with the db/transaction layers as well

#

For example with sqlalchemy the session, which handles transactions and units of work for all models is either in a context var or passed around

#

I'm unfamiliar with the tools one would use with async tho

twilit hinge Mar 6, 2021, 10:00 AM

#

yup that's what I'd do when using an ORM. Trying to experiment without an ORM for a side project, so am using asyncpg and that's why I introduced the repository layer to compensate. Allows me to mock out the SQL calls pretty easy

#

but end result I got was something like this in repository layer:

#

async def read_account_by_hashed_api_key(self, transaction: Connection, hashed_api_key: str) -> Optional[Account]:
    record = await transaction.fetchrow("select * from account where hashed_api_key = $1", hashed_api_key)

    if not record:
        return None

    return Account(**record)

#

and this in service layer:

#

async def get_labeled_account(self, api_key: str) -> Optional[Account]:
    hashed_api_key = get_hash(api_key)
    
    async with self.connection_factory.new_connection() as connection:
        return await self.account_repository.read_account_by_hashed_api_key(connection, hashed_api_key)

#

connection_factory is injected into the service, and there is new_connection and new_transaction available. lot of this would probably be simplified with ORM, but trying to see where raw SQL takes me

sick perch Mar 6, 2021, 10:31 AM

#

twilit hinge `connection_factory` is injected into the service, and there is `new_connection`...

In that case having a repository manager that combines transactions with the repository would potentially be useful

It might also be useful to use the low level sqlalchemy apis

twilit hinge Mar 6, 2021, 10:32 AM

#

alright I'll check out those APIs, have only used ORM side of sqlalchemy so will be new experience. thanks for the help

empty bone Mar 6, 2021, 10:33 AM

#

it is possible to use a python function with a php variable in a php script?

sick perch Mar 6, 2021, 10:43 AM

#

empty bone it is possible to use a python function with a php variable in a php script?

While technically it sounds possible, the performance costs is potentially unfathomable, why even do that?

sick perch Mar 6, 2021, 10:45 AM

#

empty bone it is possible to use a python function with a php variable in a php script?

For context, the typical way php is executed is a bad spot for in addition starting python with it

empty bone Mar 6, 2021, 10:46 AM

#

sick perch While technically it sounds possible, the performance costs is potentially unfat...

I've been dealing with a specific problem for several hours and haven't found a solution yet. I'm sitting at a script that takes a name and various urls (through an input field, so they are separated from each other by a comma) and puts them into a database. I would like a 2nd column to be created for each url, which can be either online or offline. I want the urls to be separated from each other first with the explode () function and then always to have a single column. I have a Python script that checks whether a url is online or offline.

empty bone Mar 6, 2021, 10:47 AM

#

empty bone I've been dealing with a specific problem for several hours and haven't found a ...

If I can use the python function in the php script I could avoid doing that

sick perch Mar 6, 2021, 10:50 AM

#

empty bone I've been dealing with a specific problem for several hours and haven't found a ...

Depending on the setup /requirements I'd either just write everything in python or port the online check to php, most likely by Google, stackoverflow and ensuring my name ain't on the result

empty bone Mar 6, 2021, 10:53 AM

#

The problem is that my entire site is built on php. The uptime checker in php doesn't work as reliably as the one in python. But since I need an accurate checker, it has to be written in python. If you have the time, I would give you a detailed description of my problem

sick perch Mar 6, 2021, 10:57 AM

#

empty bone The problem is that my entire site is built on php. The uptime checker in php do...

In that case, how about making the python function into a Web service /background process and either just letting it write to the database or using it as an Web api

empty bone Mar 6, 2021, 11:03 AM

#

That would actually be the best solution. I've already tried to split the individual urls somehow. I just haven't understood how I can check every single url whether it's online or offline. I also have no idea how to make this clear in the database for each url. I would then like to pull the individual urls from the database with php and see immediately whether they are online.

#

https://stackoverflow.com/q/66499396/15073127

Stack Overflow

Get separated string with a comma from a table

I trying to get separate strings from a columm in a table. If I output them without anything they are looking like this:
('https://google.com',)
('http://example.com',)
('http://example.com,http://...

sick perch Mar 6, 2021, 11:10 AM

#

If you want per URL data, don't store multiple URL in a single field, one dB row per URL and you can solve it

#

And if it absolutely has to be a single field, use json or something else that is absolutely clearly specified

empty bone Mar 6, 2021, 11:13 AM

#

That make sense, but I want to use only one input field for the urls. I cant use js for my project.

sick perch Mar 6, 2021, 11:15 AM

#

So split it up before you store it, in the database

#

Based on what you present so far im strongly under the impression that you should just use php all the way, if you struggle with comma/whitespace separated strings, the quality of the uptime checks doesn't seem like a overly relevant factor and figuring one language is already going to be enough

burnt turret Mar 6, 2021, 1:19 PM

#

🤔 i think RDB would be simpler

#

a table is easier to visualize imo

#

it's likely they'd have already seen stuff like excel spreadsheets

lost echo Mar 6, 2021, 1:26 PM

#

Greetings, I'm working with MSSQL with fronted as React.js. I have several foreign keys inside tables whose data needs to be reflected on the front-end.
Example:
Table 1: Customer_id (PK), Name, Phone, Email, company_id (FK)
Table 2: company_id (PK), Company_Name

Now when I'm fetching data for table 1, I need Company_Name instead of company_id. How can I achieve such a result at the front-end?

hearty jungle Mar 6, 2021, 1:47 PM

#

Hi, quite a specific problem i have.
Im currently using mongodb for a flask app and was working fine on my local ip.

However, i tried deploying my app on pythonanywhere and pymongo is throwing this error:

pymongo.errors.ServerSelectionTimeoutError: No replica set members found yet, Timeout: 30s, Topology Description:

Anyone come across this before ?

proven arrow Mar 6, 2021, 1:55 PM

#

lost echo Greetings, I'm working with `MSSQL` with fronted as `React.js`. I have several f...

You need to make a join on table 2

#

Depends exactly what your trying to each. The theory of databases? Or something else? In any case it’s important to teach or get the point across the advantages/disadvantages of a relational db over a non relational, and when to use one or the other.

lost echo Mar 6, 2021, 1:57 PM

#

proven arrow You need to make a join on table 2

yes, I figured that a bit later. Tested it and it's working just how I wanted. Thanks!

proven arrow Mar 6, 2021, 2:16 PM

#

Then you need to decide what solution is the best for your app i.e. Nosql or Sql db

#

But relational databases will be a good starting point to introduce to databases

#

They cover most of the use cases someone will need. They can learn about nosql later on when a relational db is not good enough to solve their problem, if that’s ever the case.

sick perch Mar 6, 2021, 2:22 PM

#

Typically no sql needs specific use cases to shine

#

For a lot of the classical use cases sql / rmdbs are in a very good spot

valid jewel Mar 6, 2021, 4:55 PM

#

I use sqlite3 to make a discord game and I have a guild id and then a user id, how can I select coins from the table players if the guild id is gid and user id is usrid?

burnt turret Mar 6, 2021, 5:01 PM

#

you've almost written the query out there haha

#

cursor.execute("SELECT coins FROM players WHERE guild_id = ? AND user_id = ?", (gid, usrid))

#

also, look into aiosqlite - sqlite is blocking https://discordpy.readthedocs.io/en/latest/faq.html#what-does-blocking-mean

calm barn Mar 6, 2021, 5:39 PM

#

anyone here using mongosh?

wind rock Mar 6, 2021, 6:36 PM

#

im making a website on django and im having some issues understanding how to implement a database
do i have to create a class for each table on the models.py file? even if im using a cloud based mysql database?

sick perch Mar 6, 2021, 6:44 PM

#

wind rock im making a website on django and im having some issues understanding how to imp...

yes, those are typically how you access your data, in addition there is typically migration files there so that a database can be updated to the most recent database schema without trouble

wind rock Mar 6, 2021, 6:45 PM

#

thank you!

hoary sun Mar 6, 2021, 10:01 PM

#

when I try to "import mysql.connector" into my python VSC, I get an error that says "unresolved import 'mysql'
Even though I did 'pip install mysql-connector-python'
In my mac terminal prompt
I have python 3.8.3
Does anyone know why?

#

Okay, so I did what https://dba.stackexchange.com/questions/183672/unable-to-import-mysql-connector-in-a-python-venv-session asked me to do , but I got 'RuntimeError: PyPI's XMLRPC API is currently disabled due to unmanageable load and will be deprecated in the near future'

Database Administrators Stack Exchange

Unable to import mysql.connector in a Python venv session

I've used venv to create a virtual environment for Python 3.5.2. I have installed the mysql.connector. The connector works fine as long as I am not in an activated venv environment.

from mysql.con...

#

Did the folks who manage the PyPI endpoint system shut it down for hosting?

half gyro Mar 6, 2021, 10:14 PM

#

hoary sun Did the folks who manage the PyPI endpoint system shut it down for hosting?

the search endpoint has been turned off, if that is what you mean. Use the search on the website.

hoary sun Mar 6, 2021, 10:22 PM

#

half gyro the search endpoint has been turned off, if that is what you mean. Use the searc...

Im basically trying to setup a mysql database with a python socket.

#

the 'import' statement is causing the hurdle

half gyro Mar 6, 2021, 10:23 PM

#

it's common to install into one python, and try to run a different one, and it can't be found.

#

@hoary sun try python -m pip install instead of pip install

hoary sun Mar 6, 2021, 10:23 PM

#

I'm doing this on VSCode

brazen charm Mar 6, 2021, 10:23 PM

#

you're trying to connect to MySQL with raw sockets????

hoary sun Mar 6, 2021, 10:23 PM

#

with python sockets

brazen charm Mar 6, 2021, 10:23 PM

#

you mean a MySQL py lib connector or like sockets

hoary sun Mar 6, 2021, 10:24 PM

#

MySQL py lib connector

brazen charm Mar 6, 2021, 10:24 PM

#

okay coolio

hoary sun Mar 6, 2021, 10:24 PM

#

and...

brazen charm Mar 6, 2021, 10:24 PM

#

are you using a virtual environment as of right now?

hoary sun Mar 6, 2021, 10:25 PM

#

VSCode

brazen charm Mar 6, 2021, 10:25 PM

#

okay, but that doesnt answer the question bloblul

hoary sun Mar 6, 2021, 10:25 PM

#

i don't know what a virtual environment is lol

brazen charm Mar 6, 2021, 10:25 PM

#

send a SS of your folder quickly

hoary sun Mar 6, 2021, 10:25 PM

#

kk

#

#

@brazen charm

brazen charm Mar 6, 2021, 10:27 PM

#

click the icon in the top left corner

#

where it lists all your files and directory

#

send take a ss then send it

#

something like this, but with VSC

#

No idea why i dont have VSC installed but whatever

hoary sun Mar 6, 2021, 10:28 PM

#

It looks nothing like that

#

What I'm doing is from a youtube tutorial

brazen charm Mar 6, 2021, 10:28 PM

#

take a ss of that section

hoary sun Mar 6, 2021, 10:28 PM

#

So I'm like at the beginning

#

https://www.youtube.com/watch?v=g60QghtJmjY

YouTube

edureka!

Python Database Connection | How to Connect Python with MySQL Datab...

**Python Certification Training: https://www.edureka.co/data-science-python-certification-course **
This Edureka video on 'Python Database Connection', you will learn how to establish a connection between Python and MySQL DB and perform CRUD operations on it.
Following are the topics discussed:
Introduction to Python & MySQL
Integrated Connecti...

▶ Play video

#

Start at 6:21

#

He's running this on Pycharm

brazen charm Mar 6, 2021, 10:29 PM

#

👏 Send a SS of your folder directory like i sent

hoary sun Mar 6, 2021, 10:29 PM

#

I don't have that

#

It looks nothing like that

#

half gyro Mar 6, 2021, 10:30 PM

#

I would ask in #editors-ides how to install packages for VSC

brazen charm Mar 6, 2021, 10:30 PM

#

okay

#

there we go

#

thats fine

hoary sun Mar 6, 2021, 10:30 PM

#

oh ok

brazen charm Mar 6, 2021, 10:31 PM

#

do you have a open console in VSC

hoary sun Mar 6, 2021, 10:31 PM

#

yes

#

it's not open

#

I have two consoles

brazen charm Mar 6, 2021, 10:31 PM

#

if you do python -V what does it display

hoary sun Mar 6, 2021, 10:31 PM

#

3.8.3

brazen charm Mar 6, 2021, 10:31 PM

#

cool

#

right

#

firstly, you'll want to open the folder area as a folder with VSC rather than a file

#

it'll make our lives easier

hoary sun Mar 6, 2021, 10:32 PM

#

okay...

brazen charm Mar 6, 2021, 10:32 PM

#

https://i.imgur.com/GjjAz5o.gif

Imgur

#

like this^

hoary sun Mar 6, 2021, 10:33 PM

#

Do i need to save my file prior to this?

brazen charm Mar 6, 2021, 10:34 PM

#

i would probably hit save quick yeah

#

just to be safe

hoary sun Mar 6, 2021, 10:34 PM

#

yes

#

i did that, now I will do what you showed me in the video

brazen charm Mar 6, 2021, 10:35 PM

#

once the folder is open a new terminal

hoary sun Mar 6, 2021, 10:35 PM

#

okay

#

..

brazen charm Mar 6, 2021, 10:35 PM

#

https://i.imgur.com/P74WsKY.gif

Imgur

hoary sun Mar 6, 2021, 10:35 PM

#

im doing this on a mac btw

brazen charm Mar 6, 2021, 10:35 PM

#

thats fine

hoary sun Mar 6, 2021, 10:35 PM

#

kk...

brazen charm Mar 6, 2021, 10:36 PM

#

first thing we're going todo is make a virtual environment (you should probably get into the habit of doing this process because it'll stop these issues)

hoary sun Mar 6, 2021, 10:36 PM

#

okay

brazen charm Mar 6, 2021, 10:36 PM

#

run in the console

#

python -m venv my-venv

hoary sun Mar 6, 2021, 10:37 PM

#

you mean terminal?

brazen charm Mar 6, 2021, 10:37 PM

#

same thing

#

you should see it make a new folder called my-venv

hoary sun Mar 6, 2021, 10:37 PM

#

Please say terminal from now, as I have an SQL console and a Debug console, so it confuses me.

brazen charm Mar 6, 2021, 10:37 PM

#

aight

hoary sun Mar 6, 2021, 10:38 PM

#

it did not

brazen charm Mar 6, 2021, 10:38 PM

#

what did it say?

hoary sun Mar 6, 2021, 10:38 PM

#

brazen charm Mar 6, 2021, 10:39 PM

#

yeah thats fine

#

it wont say anything

#

run ls in the terminal

hoary sun Mar 6, 2021, 10:39 PM

#

okay

brazen charm Mar 6, 2021, 10:39 PM

#

i think mac has ls atleast

hoary sun Mar 6, 2021, 10:39 PM

#

brazen charm Mar 6, 2021, 10:39 PM

#

did it list a set of files and folders?

hoary sun Mar 6, 2021, 10:39 PM

#

yup

brazen charm Mar 6, 2021, 10:39 PM

#

did you see my-venv in there

hoary sun Mar 6, 2021, 10:39 PM

#

yes

brazen charm Mar 6, 2021, 10:40 PM

#

cool, you now have a virtual environment, this is like a mini interpreter that is completely blank of any 3rd party modules

#

now we need to activate it

#

run

#

source my-venv/bin/active

hoary sun Mar 6, 2021, 10:41 PM

#

I got No such file or directory

brazen charm Mar 6, 2021, 10:41 PM

#

sorry i mistyped

#

try source my-venv/bin/activate

hoary sun Mar 6, 2021, 10:42 PM

#

kk

#

(my-venv) Abdur-Rahmans-MacBook-Pro:~ abduraziz$

brazen charm Mar 6, 2021, 10:42 PM

#

perfect

hoary sun Mar 6, 2021, 10:42 PM

#

This is what it's saying ( as it is)

brazen charm Mar 6, 2021, 10:42 PM

#

now you're essentially in the virtual environment

#

so what ever python command you run now will be run from that environment

#

now you should be able to install the mysql module again

#

pip install <insert mysql connector name thing here>

hoary sun Mar 6, 2021, 10:43 PM

#

what is inside the vector symbols?

#

Like what do I put in there?

brazen charm Mar 6, 2021, 10:44 PM

#

look at your tutorial for that

hoary sun Mar 6, 2021, 10:44 PM

#

oh

brazen charm Mar 6, 2021, 10:44 PM

#

pip install mysql-connector

#

that should be it looking at pycharm's view

tropic steeple Mar 6, 2021, 10:50 PM

#

any1 could help me out with spliting dataframes in Help-gallium?

flint osprey Mar 7, 2021, 12:35 AM

#

how do I make r2p2 not defined as None without putting flight before invisibility

delicate fieldBOT Mar 7, 2021, 12:36 AM

#

Hey @flint osprey!

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

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

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

https://paste.pythondiscord.com

flint osprey Mar 7, 2021, 12:36 AM

#

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

#

how do I make r2p2 not defined as None without putting flight before invisibility

strange locust Mar 7, 2021, 2:49 AM

#

hey guys I need help with sql

haughty cloud Mar 7, 2021, 2:57 AM

#

is it easier for me to learn SQL or just use like a txt file xd

timber gorge Mar 7, 2021, 3:36 AM

#

Im using sqlite3, how I can make a command to set a variable and use it in an event? I want to make it so if the bot goes offline the server owner doesn’t need to set everything again

torn sphinx Mar 7, 2021, 3:39 AM

#

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

Imgur

#

How to delete the array ?

burnt turret Mar 7, 2021, 3:39 AM

#

do you want to delete that entire document or just that array that's associated to the key blocked?

torn sphinx Mar 7, 2021, 3:39 AM

#

Like , if there multiple ids in there , and i want to pull out one guild id from the array

#

and delete it;s contant

torn sphinx Mar 7, 2021, 3:40 AM

#

burnt turret do you want to delete that entire document or just that array that's associated ...

That array only

#

Like 0 array contains guild_id , i want to find and delete that 0 array

burnt turret Mar 7, 2021, 3:41 AM

#

you'd have to use an update with the $unset operator used for that field

#

that'll remove that blocked: array[] field

#

oh wait that's a nested array?

torn sphinx Mar 7, 2021, 3:42 AM

#

Yeah , array inside array

#

collection.update(
  { _id: id },
  { $pull: { 'contact.phone': { number: '+1786543589455' } } }
);

#

Got this on stack

burnt turret Mar 7, 2021, 3:43 AM

#

why did you make it an array inside an array?

#

that would work if it was dicts inside the array

torn sphinx Mar 7, 2021, 3:43 AM

#

Then what ?

#

I tried them

#

But they were more nested

#

Array -> Array -> Object

burnt turret Mar 7, 2021, 3:44 AM

#

what does the inner array represent?

#

seems like guild id and?

torn sphinx Mar 7, 2021, 3:44 AM

#

Yes

#

guild id and reason

burnt turret Mar 7, 2021, 3:44 AM

#

torn sphinx Array -> Array -> Object

no it would be Array[Object]

#

where each object is like

{
guild-id:
reason:
}

torn sphinx Mar 7, 2021, 3:45 AM

#

newuser = {"id": ctx.guild.id, "channel": channel.id,'blocked' : []} # Initial 

await info.update_one({"id": ctx.guild.id},{"$addToSet": {"blocked": [guild_id,reason]}})
# On blocking

burnt turret Mar 7, 2021, 3:45 AM

#

instead of the list, why aren't you passing a dict to addToSet?

torn sphinx Mar 7, 2021, 3:46 AM

#

Then it created a array , and inside it object

#

wait lemme show you

#

or wait a sec

burnt turret Mar 7, 2021, 3:48 AM

#

i guess you could work with it being an array too

burnt turret Mar 7, 2021, 3:49 AM

#

torn sphinx ```py collection.update( { _id: id }, { $pull: { 'contact.phone': { number: ...

here instead of using dot notation with a key (the key object) you could....arrayFilters and indices

torn sphinx Mar 7, 2021, 3:50 AM

#

burnt turret i guess you could work with it being an array too

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

Imgur

#

await info.update_one({"id": ctx.guild.id},{"$addToSet": {"blocked":{f"{guild_id}":reason}}})

burnt turret Mar 7, 2021, 3:50 AM

#

eh i didn't mean like that

burnt turret Mar 7, 2021, 3:50 AM

#

burnt turret where each object is like ``` { guild-id: reason: } ```

like this

#

now the key is arbitrary

torn sphinx Mar 7, 2021, 3:50 AM

#

Lemme try

knotty rivet Mar 7, 2021, 3:51 AM

#

haughty cloud is it easier for me to learn SQL or just use like a txt file xd

a .txt file works, a more adaptive solution is using an SQL db tho

torn sphinx Mar 7, 2021, 3:53 AM

#

burnt turret no it would be Array[Object]

Didn't work

#

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

Imgur

#

How to remove numbering :thonk:

#

                    await info.update_one(
                        {"id": ctx.guild.id},
                        {"$addToSet": {
                            "blocked": {
                                "guild": f"{guild_id}",
                                "reason" : reason
                            }
                        }})

burnt turret Mar 7, 2021, 3:54 AM

#

torn sphinx https://i.imgur.com/lD0IGay.png

this is of that form

#

it's an array with object elements

torn sphinx Mar 7, 2021, 3:55 AM

#

burnt turret it's an array with object elements

So it is right ?

burnt turret Mar 7, 2021, 3:55 AM

#

yeah

torn sphinx Mar 7, 2021, 3:55 AM

#

How to pull now ?

burnt turret Mar 7, 2021, 3:55 AM

#

let me think one second

torn sphinx Mar 7, 2021, 3:56 AM

#

collection.update(
  { "id": ctx.guild.id },
  { $pull: { 'blocked.guild': { guild: 'id here' } } }
);

#

Something like this ?

burnt turret Mar 7, 2021, 3:56 AM

#

let me refer the docs one sec

torn sphinx Mar 7, 2021, 3:57 AM

#

Kk

timber gorge Mar 7, 2021, 3:57 AM

#

Im using sqlite3, how I can make a command to set a variable and use it in an event? I want to make it so if the bot goes offline the server owner doesn’t need to set everything again

burnt turret Mar 7, 2021, 3:58 AM

#

torn sphinx ```py collection.update( { "id": ctx.guild.id }, { $pull: { 'blocked.guild':...

🤔 what happens if you do {$pull: {'blocked.guild': id here}}?

torn sphinx Mar 7, 2021, 3:58 AM

#

Uhh idk pull , saw on stack

burnt turret Mar 7, 2021, 3:58 AM

#

your query would imply a more nested structure

#

try it and tell me what happens, i haven't used pull a lot myself either

torn sphinx Mar 7, 2021, 3:59 AM

#

But if pull fails to work , it gives no error smh , just pass it

burnt turret Mar 7, 2021, 3:59 AM

#

i think that happens with every query

#

it'll just say UPDATE count: 0/DELETE count: 0

torn sphinx Mar 7, 2021, 4:01 AM

#

burnt turret it'll just say UPDATE count: 0/DELETE count: 0

what if it has multiple array

#

Smh i need to find the that guild id and remove it from the blocked array

burnt turret Mar 7, 2021, 4:01 AM

#

🤔

torn sphinx Mar 7, 2021, 4:03 AM

#

pymongo.errors.WriteError: Cannot use the part (guild) of (blocked.guild) to traverse the element ({blocked: [ { guild: "815992843326980106", reason: "test" } ]}), full error: {'index': 0, 'code': 28, 'errmsg': 'Cannot use the part (guild) of (blocked.guild) to traverse the element ({blocked: [ { guild: "815992843326980106", reason: "test" } ]})'}

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

Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 902, in invoke
    await ctx.command.invoke(ctx)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 1340, in invoke
    await ctx.invoked_subcommand.invoke(ctx)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 864, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: WriteError: Cannot use the part (guild) of (blocked.guild) to traverse the element ({blocked: [ { guild: "815992843326980106", reason: "test" } ]}), full error: {'index': 0, 'code': 28, 'errmsg': 'Cannot use the part (guild) of (blocked.guild) to traverse the element ({blocked: [ { guild: "815992843326980106", reason: "test" } ]})'}

#

await info.update_one({ "id": ctx.guild.id },{"$pull": {'blocked.guild': { "guild" : f'{guild_id}'}}})

#

Using this

burnt turret Mar 7, 2021, 4:03 AM

#

try what I said

#

this would work if it was more nested

#

like

blocked: [
  {
    guild: {guild: id here}
  }
]
``` but that's pointless

torn sphinx Mar 7, 2021, 4:04 AM

#

await info.update_one({ "id": ctx.guild.id },{"$pull": {'blocked.guild': f"{guild_id}"

#

ik some } and ) are missing

burnt turret Mar 7, 2021, 4:05 AM

#

close the braces and the parentheses

#

yeah

torn sphinx Mar 7, 2021, 4:05 AM

#

You mean this ?

burnt turret Mar 7, 2021, 4:05 AM

#

tell me if it works

#

are you sure you're storing the guild IDs as strings and not ints?

torn sphinx Mar 7, 2021, 4:06 AM

#

Yes

torn sphinx Mar 7, 2021, 4:06 AM

#

torn sphinx https://i.imgur.com/lD0IGay.png

see this

burnt turret Mar 7, 2021, 4:06 AM

#

right 👌

torn sphinx Mar 7, 2021, 4:06 AM

#

await info.update_one({ "id": ctx.guild.id },{"$pull": {'blocked.guild': f'{guild_id}'}})
``` Didn't work

burnt turret Mar 7, 2021, 4:07 AM

#

was there an error?

torn sphinx Mar 7, 2021, 4:07 AM

#

pymongo.errors.WriteError: Cannot use the part (guild) of (blocked.guild) to traverse the element ({blocked: [ { guild: "815992843326980106", reason: "test" } ]}), full error: {'index': 0, 'code': 28, 'errmsg': 'Cannot use the part (guild) of (blocked.guild) to traverse the element ({blocked: [ { guild: "815992843326980106", reason: "test" } ]})'}

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

Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 902, in invoke
    await ctx.command.invoke(ctx)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 1340, in invoke
    await ctx.invoked_subcommand.invoke(ctx)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 864, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: WriteError: Cannot use the part (guild) of (blocked.guild) to traverse the element ({blocked: [ { guild: "815992843326980106", reason: "test" } ]}), full error: {'index': 0, 'code': 28, 'errmsg': 'Cannot use the part (guild) of (blocked.guild) to traverse the element ({blocked: [ { guild: "815992843326980106", reason: "test" } ]})'}

torn sphinx Mar 7, 2021, 4:07 AM

#

burnt turret was there an error?

^

burnt turret Mar 7, 2021, 4:07 AM

#

that error looks the same as the last one. are you sure you reloaded the cog etc?

torn sphinx Mar 7, 2021, 4:08 AM

#

Yes

burnt turret Mar 7, 2021, 4:08 AM

#

but we aren't using the part guild of blocked.guild pithink we're using part guild of blocked

torn sphinx Mar 7, 2021, 4:09 AM

#

Hmm k

#

I won't make unblock command ig

#

;^;

burnt turret Mar 7, 2021, 4:09 AM

#

let me see if i can model the query some other way

torn sphinx Mar 7, 2021, 4:09 AM

#

Kk , ping me

burnt turret Mar 7, 2021, 4:11 AM

#

@torn sphinx try

await info.update_one({ "id": ctx.guild.id }, {"$pull": {'blocked.guild': {"$eq": f"{guild_id}"}}})

torn sphinx Mar 7, 2021, 4:12 AM

#

kk wait

torn sphinx Mar 7, 2021, 4:13 AM

#

burnt turret <@456226577798135808> try ```py await info.update_one({ "id": ctx.guild.id }, {"...

Guess what

#

~~it worked~~

#

kek

#

    raise WriteError(error.get("errmsg"), error.get("code"), error)
pymongo.errors.WriteError: unknown top level operator: $eq, full error: {'index': 0, 'code': 2, 'errmsg': 'unknown top level operator: $eq'}

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

Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 902, in invoke
    await ctx.command.invoke(ctx)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 1340, in invoke
    await ctx.invoked_subcommand.invoke(ctx)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 864, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: WriteError: unknown top level operator: $eq, full error: {'index': 0, 'code': 2, 'errmsg': 'unknown top level operator: $eq'}

#

Another error

#

😭

burnt turret Mar 7, 2021, 4:14 AM

#

ah shit

#

wonder why we can't use eq there

torn sphinx Mar 7, 2021, 4:14 AM

#

I wonder what eq is

burnt turret Mar 7, 2021, 4:14 AM

#

equal to

torn sphinx Mar 7, 2021, 4:15 AM

#

ohh kk

timber gorge Mar 7, 2021, 5:18 AM

#

Any idea why my SQLite setup installation got stuck in 0%?

buoyant marsh Mar 7, 2021, 12:52 PM

#

hi

#

do you know how to save emojis in a db? I can save them but when I try a select mysql (mariadb) returns everything and does not care of the exact emoji type

rain plank Mar 7, 2021, 1:10 PM

#

Probably save the id

#

If it's custom and it's discord emojis

proven arrow Mar 7, 2021, 1:35 PM

#

buoyant marsh do you know how to save emojis in a db? I can save them but when I try a select ...

How are you trying to store the emojis? Also what collations are you using for the database and table?

buoyant marsh Mar 7, 2021, 1:36 PM

#

proven arrow How are you trying to store the emojis? Also what collations are you using for t...

fixed it by switching from utf8 to utf8mb4_bin which recognizes different emojis

proven arrow Mar 7, 2021, 1:37 PM

#

Yeah mb4 should do it

raven trail Mar 7, 2021, 2:01 PM

#

Anyone online have experience intergrating MongoEngine with Discord.py?

solemn ridge Mar 7, 2021, 5:16 PM

#

Hi this is my db.py file for SQLAlchemy (not flask related)

engine = create_engine('mysql://root@localhost:3306/hyplex')

Base = declarative_base()

Session = sessionmaker(bind=engine, autocommit=False, autoflush=False)
db_session = Session()

meta = MetaData()


class DB:
    @staticmethod
    def create_all():
        Base.metadata.create_all(engine)

    def __init__(self):
        self.session = db_session

All my models share the same Base which I import from this file
When I try to do DB.create_all(), no table is created on the db

torn sphinx Mar 7, 2021, 6:00 PM

#

hi

#

I'm trying to use mysql database, using aiomysql (built-in pymysql). The problem is that it rejects the connection

#

First I tried on my device, then on a hosting and it keeps rejecting

#

#

When I try with 127.0.0.1, it is the same even with the server activated.

#

async def pool_sql():
    return await aiomysql.create_pool(host='127.0.0.1', port='3306', 
            user='root', password='-----', 
            db='mysql', autocommit=True
            )

slender rose Mar 7, 2021, 6:16 PM

#

Anyone knows how to do a fetch on postgres where if you have a column with datetime objects, you only fetch the rows where current time and the time there has 12 hours difference

torn sphinx Mar 7, 2021, 6:27 PM

#

what

proven arrow Mar 7, 2021, 6:31 PM

#

torn sphinx

The port needs to be a int value not a string like you have

torn sphinx Mar 7, 2021, 6:33 PM

#

so the whole problem is because the port must be int and not str? lol.

#

So I'm pretty new to databases. I want to use a sqlite database for my discord bot, but i'm not sure where to start. I've looked at a few tutorials, but they just type out the code without explaining what it does. I need to know how to do it myself. What should I do to get started?

#

(ping me)

proven arrow Mar 7, 2021, 6:40 PM

#

torn sphinx So I'm pretty new to databases. I want to use a sqlite database for my discord b...

Well the main/ or most important thing is to be aware of SQL which is the query language that allows you to interact with and manipulate your database. You should have or try to get some basic understanding of this first. Then when it comes to use the database with python it’s pretty straightforward, because you’ll just be writing sql queries through the library function.

torn sphinx Mar 7, 2021, 6:41 PM

#

proven arrow Well the main/ or most important thing is to be aware of SQL which is the query ...

oh wait sql has its own language?

#

I will learn that first then let you guys know if i need anything else. Thanks!

proven arrow Mar 7, 2021, 6:44 PM

#

torn sphinx oh wait sql has its own language?

Yes but it’s quite high level and readable, so it’s easy to pick up. There are some links pinned in this channel or you can view the Khan academy link for intro https://www.khanacademy.org/computing/computer-programming/sql

Khan Academy

Intro to SQL: Querying and managing data | Khan Academy

Learn how to use SQL to store, query, and manipulate data. SQL is a special-purpose programming language designed for managing data in a relational database, and is used by a huge number of apps and organizations.

torn sphinx Mar 7, 2021, 6:44 PM

#

epic!

gaunt garden Mar 7, 2021, 7:56 PM

#

Im trying to mark the user disabled and store the date they leave so their information can be deleted in the future if they don't return in a set time (15 days) The issue im having is its not setting in my database and im not sure why.

#

database function's code:

    db = sqlite3.connect('main.sqlite')
    c = db.cursor()
    sql = ("UPDATE economy_profiles SET active = ? and removal = ? WHERE guild_id = ? and user_id = ?")
    val = (status, removal, guild, member)
    c.execute(sql, val)
    db.commit()
    c.close()
    db.close()

#

event code:

#

#

its storing in the database with the correct guild and user, but its changing 'True' to 0 instead of 'False' and not inserting FinalTime

novel oak Mar 7, 2021, 7:59 PM

#

does anyone know how to solve this?

DB = sqlite3.connect('D:\\Python\\Scripts\\DiscordBots\\IceBot\\UsersIceEmpire.db')
        c = DB.cursor()
        c.execute(f"SELECT WarnsInfo FROM UsersDiscordIceEmpire WHERE ID = '{member.id}';")
        UserWarnsInfo = c.fetchone()
        print(UserWarnsInfo) #This returns -> ('None',)
        UpdatedUserWarnsInfo = UserWarnsInfo + (reason,)
        print(UpdatedUserWarnsInfo) #This returns -> ('None', 'Prueba')
        c.execute(f"UPDATE UsersDiscordIceEmpire SET WarnsInfo = '{UpdatedUserWarnsInfo}' WHERE ID = '{member.id}';")
        DB.commit()
        DB.close()

the error

Ignoring exception in command warn:
Traceback (most recent call last):
  File "C:\Users\Jose Manuel\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "d:\Python\Scripts\DiscordBots\IceBot\ModCmds.py", line 43, in warn
    c.execute(f"UPDATE UsersDiscordIceEmpire SET WarnsInfo = '{UpdatedUserWarnsInfo}' WHERE ID = '{member.id}';")
sqlite3.OperationalError: near "None": syntax error

gaunt garden Mar 7, 2021, 8:04 PM

#

c.execute("UPDATE UsersDiscordIceEmpire SET WarnsInfo = ? WHERE ID = ?", (UpdatedUserWarnsInfo, member.id,))

try that? from what i've heard its not recommended to use f-strings in queries.

gaunt garden Mar 7, 2021, 8:05 PM

#

gaunt garden Im trying to mark the user disabled and store the date they leave so their infor...

.

novel oak Mar 7, 2021, 8:06 PM

#

gaunt garden ```py c.execute("UPDATE UsersDiscordIceEmpire SET WarnsInfo = ? WHERE ID = ?", (...

i think the problem isn't there but thx

gaunt garden Mar 7, 2021, 8:08 PM

#

how come your {} are in single quotes?

#

and why do you have ; at the end? just out of curiousity

novel oak Mar 7, 2021, 8:09 PM

#

gaunt garden how come your {} are in single quotes?

because i though they needed to be like that

gaunt garden Mar 7, 2021, 8:09 PM

#

gaunt garden ```py c.execute("UPDATE UsersDiscordIceEmpire SET WarnsInfo = ? WHERE ID = ?", (...

this as far as i know is the preferred method

novel oak Mar 7, 2021, 8:10 PM

#

gaunt garden this as far as i know is the preferred method

yeah i know but the other is easiest 😆

gaunt garden Mar 7, 2021, 8:10 PM

#

i thought so too until it didn't work anymore and i came asking 😂

torn sphinx Mar 7, 2021, 9:37 PM

#

who can learn me every single step in effective data storage (python & csv files) 😋

torn sphinx Mar 7, 2021, 10:43 PM

#

hi, I would like to know if the following code is fine and will not have problems when it is used

async def get_guild_prefix(self):
    pool = await pool_sql_create()
    async with pool.acquire() as section:
        async with section.cursor() as temp:
            prefix = await temp.execute('SELECT prefix FROM prefix WHERE gulldid = %s', [self])
            if prefix == 0:
                await temp.execute('INSERT INTO prefix(guildid, prefix) VALUES(%s, %s)', [self, '?'])
                temp.close()
                pool.close()
                await pool.wait_closed()
                return '?'
            temp.close()
            pool.close()
            await pool.wait_closed()
            prefix = await temp.fetchone()
            return prefix[0]

My question is because waiting for pool.wait_closed() I would delay, idk.

proven arrow Mar 7, 2021, 11:07 PM

#

@brave bridge From the other day when you asked. The SQL and relational theory book by Date, should cover it in good amount of detail that’s needed. Here is a pdf link: http://index-of.co.uk/Database/SQL Database/SQL and Relational Theory How to Write Accurate SQL Code(True pdf)by C.J.Date(pradyutvam2)[o'reily].pdf

Or take a look at codds publication who kind of first bought it around https://dl.acm.org/doi/10.1145/362384.362685

#

Ive not read those but I asked some dba’s and that’s what they gave. Although they did say it’s mostly overkill, and not something they consider on daily basis.

brave bridge Mar 7, 2021, 11:11 PM

#

proven arrow <@!461097636791844865> From the other day when you asked. The SQL and relational...

Thanks a lot, I'll check those out. But please don't post pirated links here 👀

torn sphinx Mar 7, 2021, 11:12 PM

#

torn sphinx hi, I would like to know if the following code is fine and will not have problem...

help

pure mirage Mar 7, 2021, 11:13 PM

#

mah fam

flint osprey Mar 7, 2021, 11:24 PM

#

how do I make a database

#

anyoine...

prisma girder Mar 7, 2021, 11:42 PM

#

flint osprey how do I make a database

What do you mean?

flint osprey Mar 7, 2021, 11:42 PM

#

like idk how to start

#

im new to databsaes

prisma girder Mar 7, 2021, 11:42 PM

#

You can start with some introduction into SQL

flint osprey Mar 7, 2021, 11:43 PM

#

ok

torn sphinx Mar 8, 2021, 12:01 AM

#

torn sphinx hi, I would like to know if the following code is fine and will not have problem...

@proven arrow help me

#

my question is whether it is convenient to close the pool after using it, pool.close() and pool.wait_closed

#

Knowing that it will be used many times

proven arrow Mar 8, 2021, 12:02 AM

#

Why close the pool?

#

Pool should be closed when you are shutting down the app

torn sphinx Mar 8, 2021, 12:06 AM

#

I don't know how it works, I am not a newbie but also a professional in sql (no).

#

@proven arrow So I don't close the pool? From what I see it acquires an open pool, or thread to perform the operations. What I have in mind is not close when collecting the guild prefix, but if at the end of a command

proven arrow Mar 8, 2021, 12:13 AM

#

To keep it simple, opening database connections can be expensive. So the purpose of a pool is to minimise the cost of opening these connections. A pool keeps a collection of database connections. When your app needs a connection it takes one from the pool and returns it back to the pool, when it’s done using it. This way you avoid the connect/disconnect complexity.

#

So if you are going to close the pool each time, you defeat the purpose of the pool in the first place.

torn sphinx Mar 8, 2021, 12:20 AM

#

Wait, I am understanding.

rugged cove Mar 8, 2021, 12:24 AM

#

What database should I get started with?

torn sphinx Mar 8, 2021, 12:25 AM

#

@proven arrow I was able to understand, but then taking connections from the pool would you be using a thread?

proven arrow Mar 8, 2021, 12:30 AM

#

torn sphinx <@613362435860070414> I was able to understand, but then taking connections from...

No, the library will have an acquire and release method to manage the connections, or use a context manager. And I’m going sleep now so don’t expect a response.

rugged cove Mar 8, 2021, 12:33 AM

#

I started learning SQL, from what I understand on this tutorial SQL is like a database over network am I correct? https://realpython.com/python-sql-libraries/

Introduction to Python SQL Libraries – Real Python

In this step-by-step tutorial, you'll learn how to connect to different database management systems by using various Python SQL libraries. You'll interact with SQLite, MySQL, and PostgreSQL databases and perform common database queries using a Python application.

torn sphinx Mar 8, 2021, 12:55 AM

#

proven arrow No, the library will have an acquire and release method to manage the connection...

Thanks for the help!

rugged cove Mar 8, 2021, 1:03 AM

#

What sql python lib is the easiest?

brave bridge Mar 8, 2021, 1:16 AM

#

rugged cove What sql python lib is the easiest?

It's not really the matter of a library. There are different database management systems (RDBMS) -- SQLite, Postgres, MySQL and some others; and Python simply has libraries to interact with them.

The most simple and barebones relational database is SQLite.

midnight kelp Mar 8, 2021, 1:23 AM

#

Hello, i'm trying to print with prettytable from asyncpg and i'm trying to use from_db_cursor(), the example said i need to pass in a cursor but it throws an error. This is what i have now ```py
pg_con = await asyncpg.connect(database="x", user="x", password="x")
async with pg_con.transaction():
cursor = pg_con.cursor("SELECT * FROM eco")
table = from_db_cursor(cursor)
print(table)

and the traceback

Traceback (most recent call last):
File "C:\Users\SMKN3-01\Desktop\jeyy's\codes\python\tests.py", line 27, in <module>
asyncio.get_event_loop().run_until_complete(main())
File "C:\Users\SMKN3-01\AppData\Local\Programs\Python\Python38-32\lib\asyncio\base_events.py", line 616, in run_until_complete
return future.result()
File "C:\Users\SMKN3-01\Desktop\jeyy's\codes\python\tests.py", line 13, in main
table = from_db_cursor(cursor)
File "C:\Users\SMKN3-01\AppData\Local\Programs\Python\Python38-32\lib\site-packages\prettytable\prettytable.py", line 1825, in from_db_cursor
if cursor.description:
AttributeError: 'CursorFactory' object has no attribute 'description'

rugged cove Mar 8, 2021, 1:26 AM

#

brave bridge It's not really the matter of a library. There are different database management...

Thx, I am currently learning SQL, I am very confused, can you help me out a bit?

#

Like for example

#

Where am I supposed to run SELECT * from tanle name and other commands?

rugged cove Mar 8, 2021, 1:32 AM

#

brave bridge It's not really the matter of a library. There are different database management...

Is this a good tutorial? https://youtu.be/p3qvj9hO_Bo

YouTube

Web Dev Simplified

Learn SQL In 60 Minutes

In this video we will cover everything you need to know about SQL in only 60 minutes. We will cover what SQL is, why SQL is important, what SQL is used for, the syntax of SQL, and multiple examples of SQL. SQL is the standard language for interacting with and manipulating data in a relational database system, and is one of the most important con...

▶ Play video

rugged cove Mar 8, 2021, 2:27 AM

#

Thanks

#

Is this a good tutorial? https://youtu.be/p3qvj9hO_Bo

YouTube

Web Dev Simplified

Learn SQL In 60 Minutes

In this video we will cover everything you need to know about SQL in only 60 minutes. We will cover what SQL is, why SQL is important, what SQL is used for, the syntax of SQL, and multiple examples of SQL. SQL is the standard language for interacting with and manipulating data in a relational database system, and is one of the most important con...

▶ Play video

solid tundra Mar 8, 2021, 9:51 AM

#

    @commands.Cog.listener()
    async def on_member_join(member):
        db = sqlite3.connect("main.sqlite")
        cursor = db.cursor()
        cursor.execute(f"SELECT channel_id FROM main WHERE guild_id = {member.guild.id}")
        result = cursor.fetchone()
        if result is None:
            return
        else:
            cursor.execute(f"SELECT msg FROM main WHERE guild_id = {member.guild.id}")
            result1 = cursor.fetchone()
            members = len(list(member.guild.members))
            mention = member.mention
            user = member.name
            guild = member.guild
            embed = discord.Embed(title="ChizLanks", description=str(result1[0]).format(members=members, mention=mention, guild=guild))
            channel = client.get_channel(id=int(result[0]))

            await channel.send(embed=embed)

#

This should get the Channel's ID and the msg's content from the DB and then send it to a channel

#

it's not working tho without any errors

#

any ideas why?

burnt turret Mar 8, 2021, 9:53 AM

#

how are you testing this?

#

you'll need the members intents enabled for this event to work - there's a message pinned in #discord-bots explaining intents

solid tundra Mar 8, 2021, 10:05 AM

#

Well I did enable members intents

#

still not working

#

and also what do you mean by how am I testing that?

burnt turret Mar 8, 2021, 10:12 AM

#

how are you saying that it is not working?

#

what goes wrong/which part doesn't work as expected

solid tundra Mar 8, 2021, 10:17 AM

#

burnt turret what goes wrong/which part doesn't work as expected

Well everything is fine as it comes to errors, the event itself was not working, Like no message was showing up etc. etc. It seems I needed to add a self that's why, now it's working fine.

#

Anyways thank u!

burnt turret Mar 8, 2021, 10:18 AM

#

Yep, all* methods in a class take self as the first parameter

brave bridge Mar 8, 2021, 10:36 AM

#

@solid tundra Side note: you shouldn't use f-strings or other built-in interpolation to format SQL queries. Here it likely won't cause any issues, but it's very easy to let SQL injection into your system. If you do

cursor.execute(f"SELECT user_id FROM users WHERE username = '{username}'")

and a user named '; DROP TABLE users; -- joins your server, you're in trouble. You should use the database adapter's interpolation:

cursor.execute(f"SELECT user_id FROM users WHERE username = ?", (username,))

solid tundra Mar 8, 2021, 10:37 AM

#

brave bridge <@!434843854030635009> Side note: you shouldn't use f-strings or other built-in ...

I see, so f-strings are bad for SQL?

burnt turret Mar 8, 2021, 10:39 AM

#

solid tundra I see, so f-strings are bad for SQL?

#databases message this message explains why

brave bridge Mar 8, 2021, 10:39 AM

#

For any interpolation of external things, such as query parameters when constructing a URL (which is often overlooked). The two main issues are:

You can get the interpolation wrong (e.g. you need to quote and escape things correctly)
You can introduce a risk of injection (where a user inputs malicious data)

#

@solid tundra ^ and the above link as well

#

You could argue that you're only including values with a few select possibilities (e.g. just ints). But you can overlook a possibility of it not being true; and Python is dynamically typed, so there isn't really any guarantee that there isn't a bug that will put a string in there.

solid tundra Mar 8, 2021, 12:03 PM

#

I see alright

#

thanks a lot for the info!

torn sphinx Mar 8, 2021, 12:48 PM

#

rugged cove I started learning SQL, from what I understand on this tutorial SQL is like a da...

No, SQL is the programming language to query databases.

torn sphinx Mar 8, 2021, 12:51 PM

#

rugged cove Where am I supposed to run SELECT * from tanle name and other commands?

It depends on the database you are or will be using. What do you want to learn? What operating system are you using? What database (MySQL, PostgreSQL, SQLite) is mentioned in the course or tutorial?

mental quiver Mar 8, 2021, 4:36 PM

#

In what case would you want to use hidden columns in an sqlite db? @ me when responding please.

torn sphinx Mar 8, 2021, 5:08 PM

#

How to start with databases?

#

aright, thanks

proven arrow Mar 8, 2021, 5:23 PM

#

mental quiver In what case would you want to use hidden columns in an sqlite db? @ me when res...

Mostly to avoid breaking your application when you want to make changes to a table. They don’t appear in select * queries, and it’s not necessary to specify the invisible column name when inserting. So if your code currently relies on the above then the invisible column won’t be seen by the existing code, and will save you any errors. However the new column can still be referenced by new applications/code.

novel oak Mar 8, 2021, 5:25 PM

#

Help pls

DB = sqlite3.connect('D:\\Python\\Scripts\\DiscordBots\\IceBot\\UsersIceEmpire.db')
            c = DB.cursor()
            c.execute("SELECT XP FROM UsersDiscordIceEmpire WHERE ID = ?", (member.id,))
            UserXP = c.fetchone()

this is the error returned

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Users\Jose Manuel\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "d:\Python\Scripts\DiscordBots\IceBot\main.py", line 173, in ON_MESSAGE
    UserXp = float(UserXP[0])
TypeError: 'NoneType' object is not subscriptable

I've tried to print the value returned from the DB and returns None, don't know why.

proven arrow Mar 8, 2021, 5:26 PM

#

novel oak Help pls ```py DB = sqlite3.connect('D:\\Python\\Scripts\\DiscordBots\\IceBot\\U...

If it returns none then there was no match found in the database with those values

novel oak Mar 8, 2021, 5:28 PM

#

proven arrow If it returns none then there was no match found in the database with those valu...

yeah i know that, but is the code correct?

buoyant creek Mar 8, 2021, 5:28 PM

#

Hello! How I can store the guild_limit as int?

proven arrow Mar 8, 2021, 5:29 PM

#

novel oak yeah i know that, but is the code correct?

You need to handle the case where no value is returned from the database, to avoid an error.

#

Add an if check or try catch

proven arrow Mar 8, 2021, 5:29 PM

#

buoyant creek Hello! How I can store the guild_limit as int?

What is that?

novel oak Mar 8, 2021, 5:29 PM

#

proven arrow You need to handle the case where no value is returned from the database, to avo...

yes but first I want the code to work

proven arrow Mar 8, 2021, 5:30 PM

#

I told you why it doesn’t work, and how to handle it. What more do you want?

novel oak Mar 8, 2021, 5:32 PM

#

proven arrow I told you why it doesn’t work, and how to handle it. What more do you want?

how to solve it, because it should work, I have opened the DB and the values are there

#

and it should work

#

but doesn't

#

and why do you want me to do an error handling system is the DB simply doesn't works

proven arrow Mar 8, 2021, 5:33 PM

#

Database works for everyone else?

novel oak Mar 8, 2021, 5:33 PM

#

what do you mean?

burnt turret Mar 8, 2021, 5:34 PM

#

cross check your query and make sure it is right (are you passing in the right data type? etc)

#

there's no reason for that query to return None otherwise if you're so sure there are matching rows in the database🤷‍♂️

novel oak Mar 8, 2021, 5:36 PM

#

burnt turret cross check your query and make sure it is right (are you passing in the right d...

how can i make the member.id query to be an str value? because the problem could me there, but the value of the variable is int? how could i change it?

DB = sqlite3.connect('D:\\Python\\Scripts\\DiscordBots\\IceBot\\UsersIceEmpire.db')
            c = DB.cursor()
            c.execute("SELECT XP FROM UsersDiscordIceEmpire WHERE ID = ?", (member.id,))
            UserXP = c.fetchone()

burnt turret Mar 8, 2021, 5:37 PM

#

your id column is set to a text type?

#

to pass it in as a string, just cast member.id to str with the str() function - str(member.id)

novel oak Mar 8, 2021, 5:37 PM

#

like this?

#

DB = sqlite3.connect('D:\\Python\\Scripts\\DiscordBots\\IceBot\\UsersIceEmpire.db')
            c = DB.cursor()
            c.execute("SELECT XP FROM UsersDiscordIceEmpire WHERE ID = ?", (str(member.id),))
            UserXP = c.fetchone()

burnt turret Mar 8, 2021, 5:38 PM

#

you can try it and see

novel oak Mar 8, 2021, 5:38 PM

#

ok

novel oak Mar 8, 2021, 6:06 PM

#

burnt turret you can try it and see

ok know works perfectly, last question, is any why I can add into a field of the DB text, I mean to the existing text
eg:

DB = sqlite3.connect('D:\\Python\\Scripts\\DiscordBots\\IceBot\\UsersIceEmpire.db')
c = DB.cursor()
c.execute("SELECT Text FROM UsersDiscordIceEmpire WHERE ID = ?", (str(member.id),))
UserText = c.fetchone()
#Let's say that printing that returned a tuple with the text ('text1')

Then could i do something to add the new text ('text2'), to the tuple with the texts, then the tuple to be ('text1', 'text2')

#

because i am trying that in one way but returns error

burnt turret Mar 8, 2021, 6:06 PM

#

you can only do that if your table has more columns already

novel oak Mar 8, 2021, 6:08 PM

#

but i wan t to add the text to the same column

novel oak Mar 8, 2021, 6:09 PM

#

burnt turret you can only do that if your table has more columns already

like if it was

UserTexts
|text1|
|text1, text2|
|text1|
|text1, text2, text3|

burnt turret Mar 8, 2021, 6:10 PM

#

i don't really understand, maybe someone else can answer

novel oak Mar 8, 2021, 6:15 PM

#

burnt turret i don't really understand, maybe someone else can answer

I mean like if the space in the column was a list with diferent texts

burnt turret Mar 8, 2021, 6:15 PM

#

that's a single column?

proven arrow Mar 8, 2021, 6:27 PM

#

novel oak but i wan t to add the text to the same column

You can use the concat operators || to add strings together. https://www.sqlitetutorial.net/sqlite-string-functions/sqlite-concat/

SQLite Tutorial

admin

Looking for SQLite CONCAT? Use The Concatenation Operator || Instead

SQLite does not support the CONCAT function. Instead, you use the concatenation to concatenate two strings into a string.

torn sphinx Mar 8, 2021, 7:27 PM

#

how i generate random emails?

#

and passwords

#

@novel oak

sick perch Mar 8, 2021, 7:30 PM

#

there is a number of libraries to generate those - a typical suggestion for applications is not to ever generate passwords

novel oak Mar 8, 2021, 8:18 PM

#

proven arrow You can use the concat operators || to add strings together. https://www.sqlitet...

this returns error

c.execute(f"SELECT WarnsInfo || {reason} FROM UsersDiscordIceEmpire WHERE ID = ?", (str(member.id,)))

#

isn't correct, right?

prisma girder Mar 8, 2021, 9:33 PM

#

novel oak this returns error ```py c.execute(f"SELECT WarnsInfo || {reason} FROM UsersDisc...

Which DB do you use?

rain plank Mar 8, 2021, 9:34 PM

#

Well it looks like SQLite to me

prisma girder Mar 8, 2021, 9:35 PM

#

Okay, I think that using {reason} is not secure solution but maybe '{reason}' should work

rain plank Mar 8, 2021, 9:42 PM

#

I mean if the reason was some reason' FROM UsersDiscordIceEmpire; DROP TABLE UsersDiscordIceEmpire;-- then it still wouldn't be secure

prisma girder Mar 8, 2021, 9:46 PM

#

Yep, but it should work 🙂

#

I don't see how reason variable is created so... It can be or can't be a security issue

proven arrow Mar 8, 2021, 10:03 PM

#

novel oak this returns error ```py c.execute(f"SELECT WarnsInfo || {reason} FROM UsersDisc...

Why are you using concat in select? What are you trying to do?

primal plover Mar 8, 2021, 10:42 PM

#

HELLO

#

Does anybody understands how to import large csv file into a database table using python?