databases

[delicate field]

@austere portal :white_check_mark: Your eval job has completed with return code 0.

001 | 
002 | 
003 |  
004 |  
005 |  
006 |  
007 |  
008 | T
009 | e
010 | s
011 | t
... (truncated - too many lines)

Full output: https://paste.pythondiscord.com/hazajijesu.txt?noredirect

[austere portal]

Add a comma to make it a tuple

[grim zephyr]

I have enough experience with SQL and enough with PostgreSQL too but it's really a mess to work with and mongo it's the best

Try using mongo bud

[shrewd frigate]

?

[pure sleet]

mongodb and postgres, although both databases, serve different purposes. it makes no sense to tell someone to ditch one for the other

[torn sphinx]

[half rain]

For anyone who likes to use SQLAlchemy and FastAPI but hates building parallel model classes, the maker of FastAPI has a new package to reduce the doubling up of work https://github.com/tiangolo/sqlmodel and just write the classes once

[austere portal]

Cool

[harsh pulsar]

we might end up adopting odmantic for our mongo stuff, cool to see a sql equivalent now. i wish the tech leads at my org weren't so allergic to sql 😆

i guess not having to do migrations is a big plus

[long dome]

How to get the hidden ID/ROWID table with a SELECT Query with asyncpg (POSTGRESQL Python)

[torn sphinx]

never heard of asyncpg but in postgres you can get the page and tuple id with select *, ctid from mytable;

theres a list here https://www.postgresql.org/docs/9.5/ddl-system-columns.html

[stuck shell]

What is the purpose of a hidden id?

[torn sphinx]

rowid is from oracle so no idea, but for postgres it's dumping interval variables

[stuck shell]

So maybe OID instead of CTID for postgres may be the answer?

[quasi pulsar]

Depending on the use case and requirements, either OID or CTID should work

[torn sphinx]

i dont even know if you can do that anymore WITH OID doesnt even work for me and they removed it from the guide https://www.postgresql.org/docs/current/ddl-system-columns.html

it's in 9.5

[stuck shell]

Ahhh. I didn't noticed they removed it from postgres. IN this case CTID but you have to be aware that they can change over time.

[quasi pulsar]

Here's a good example of ctid in action: https://www.enterprisedb.com/postgres-tutorials/what-equivalent-rowid-postgresql

[rose blade]

Guys i have to make CRM and im not sure how to design data structures. Basically there needs to be an app where user logs in -> selects any specific project -> in that project is the db of contacts and according to the selected state under the specific record, the record goes to the different db, and is removed from the previous one, but now if have to operate on so many DBs - 1 for auth, 1 main for a project and 2 secondary for a records with specific state and i can feel that's kinda weird and that's not a proper solution. I dont have much experience in designing/architecting things so i propably don't know the obvious things, so im looking for any help I already have a diagram which looks like:

https://cdn.discordapp.com/attachments/856929242311163934/880235973210935306/unknown.png

[harsh temple]

who can help me setup a database for a discord bot

[shell ocean]

in general...don't make databases where tables will do

[harsh temple]

does anyone know how to use replit database

[tranquil zinc]

but im not trying to do drop table

[tranquil zinc]

its very ez, they hv instructions

[harsh temple]

link ?

[torn sphinx]

guess you're out of luck then

[tranquil zinc]

confused noises play

[desert sandal]

Is there anything in mongo that allows me to exclude the field 'names' from the find() output (not talking about the fields exclusion by 0/1 i mean to say remove field names so it'll find and give the output which has the address just like that without being as "address" : ABC Colony, Park Street)

[grim vault]

The point was that your commands is just a string and so your for loop iterates over each char of the string.

[hot cypress]

Anyone familiar with pandas pls help me understand why this doesn't work. Both are an attempt at the same thing (it was originally in Portuguese i just translated to English)?

dados['Feelling'].where(dados['Feelling'].isin(['Satisfied', 'Really Satisfied', 'Indifferente']),'Didnt like', inplace=True)  #type is category
dados['Feelling'].where(dados['Feelling'].isin(['Didnt like', 'Indifferente']),'liked')
                                          'where substitutes whatever is False (this is Pandas version of where)'

ValueError: Cannot setitem on a Categorical with a new category, set the categories first

dados['Feelling'].cat.rename_categories({'Really Satisfied':'Liked', 'Really Unatisfied':'Didnt like', 'Satisfied':'Liked', 'Unsatisfied':'Didnt like'}, inplace=True)
dados['Feelling']

ValueError: Categorical categories must be unique

And why would this work:

Feelling_dic={'Really Satisfied':'Liked', 'Really Unatisfied':'Didnt like', 'Satisfied':'Liked', 'Unsatisfied':'Didnt like'}
dados['Feelling']=dados['Feelling'].map(Feelling_dic)
dados['Feelling']```

 if the ``Categorical categories`` also aren't unique and it also ``setitem on a Categorical with a new category, set the categories first``?
It was from my college's study list of exercises. It wanted me to substitute the 5 initial categories for the three last ones. I'm sry if it's confusing i have a hard time with writing in any language (even Portuguese).

[primal notch]

Need some help with PGSQL
I have a table of categories which can reference to each other and form trees. What I want is to get the whole tree's IDs by the root element
I thought about chaining something for like this 5 times, but it doesn't work

SELECT root.id, ch.id FROM (
    SELECT id FROM categories
    WHERE id = 3
) AS root, (
    SELECT id FROM categories
    WHERE parent_id IN root
) AS ch;```

ERROR: syntax error at or near "root"
LINE 6: WHERE parent_id IN root```

I mean I can do

SELECT root.id, ch.id FROM (
    SELECT id FROM categories
    WHERE id = 3
) AS root, (
    SELECT id FROM categories
    WHERE parent_id IN (
        SELECT id FROM categories
        WHERE id = 3
    )
) AS ch;```
But it's even uglier than my previous tries

[remote tide]

can someone, Help me i have this, exe and i wanted to extend it, I wanted to add a log so for when ever, someone open's it it give's me the time and date or there login and i have no clue how to do that

[faint blade]

WITH root AS (
    SELECT id FROM categories
    WHERE id = 3
);
SELECT root.id, id from categories WHERE parent_id = root.id;

Maybe?

[faint blade]

That is both not something that should be asked here, nor something that I think anyone will help you with. That sounds malicious

[remote tide]

noooo

[grim vault]

I think you need recursive, like:

WITH RECURSIVE
  category_tree(id) AS (
    SELECT 3
     UNION
    SELECT categories.id FROM categories, category_tree
     WHERE categories.parent_id = category_tree.id
  )
SELECT * FROM category_tree;

[remote tide]

i'm asking like how to log a login like if someone forget's it lol

@faint blade

[faint blade]

I would say there's better ways to accomplish it, either way I won't help you.

[tacit spear]

thats because it is

he asked this in another server

its to log discord tokens

[remote tide]

no?

xd ill screen shot it's for logins

[remote tide]

When u login with your bot's token, That will then be logged xd

not a discord" token

https://tenor.com/view/funny-animals-happy-halloween-dog-fluffy-cute-gif-10136551

@tacit spearand what that is, asking i made the client u did not, It's asking if it was a discord token,

[tacit spear]

plus you got angry and called a mod sped

[remote tide]

yes and left

telling me how to make my client

[narrow mist]

I don't think we are helping you make a discord bot that will send the token to a remote server "in case they forget it or something"

just not gonna happen

[remote tide]

bro, It's not a token logger, that sound like some skidded stuff, do u know how websites have a backend where all there user's are logged

IM asking how would i do that to my exe

[narrow mist]

I've just noticed your screenshot mentions nuke bot, that's definitely a no

[vocal matrix]

Well it is, the problem is that you seem to be logging tokens which under our rules is not allowed.

[remote tide]

@narrow mistnuke bot, Xd it nuke's a channel not a server

it's a discord bot with multiple options

[vocal matrix]

we still won't help with that either...

[narrow mist]

@remote tide weird, because the source code looks exactly like a nuke bot

[remote tide]

how did u get my source/

[narrow mist]

Your repo is public..

[tacit spear]

lol

[remote tide]

xd u think i'm, that stupid XD it's a pub git hub

u obs went to the github link xd

\

[narrow mist]

!mute 710698160615325737 4d Seems like you are new here, so you can have one last chance. Playing the smartest with moderators isn't going to fly here, as well as asking for help with token grabbing, nuke bots, and other ToS breaking stuff. You won't be given another chance, don't throw it away and go read another time #rules.

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @remote tide until <t:1630334920:f> (3 days and 23 hours).

[primal notch]

@faint blade@grim vault much appreciated! The recursive clause is what I was looking for

[torn sphinx]

What theme is that

[austere portal]

Looks like github dark mode

[torn sphinx]

Oh

Ty

[broken prawn]

ok so i had a question, how would i add a database to my login form

[austere portal]

Depends on the backend framework you are using

[broken prawn]

it's not for my website

it's for my exe application

[torn sphinx]

lmao ,

he took his github account off

[torn sphinx]

should i change it from this to that ?

yes i should try it

[harsh temple]

i use the repl database can anyone here answer my question

[harsh pulsar]

possibly, might as well ask

you mean repl.it?

[harsh temple]

yes

[tiny nimbus]

Hi guys! Quick question, how do you define a technical challenge while managing data performance? For example, When I twit, all my followers receive the update in their feed at 1.5k requests per second

But I can only send 600 requests per second

[faint blade]

What do you mean?

When you tweet from Twitter you send 1 request, Twitter then takes care of telling all of your followers "Hey here's new content!". Those requests don't account for your rate limit as far as I know.

Besides, this doesn't seem to be the right channel for this sort of stuff

[tiny nimbus]

I meant from the back-end request handling, how would the db work this requests and how would you optimize the sender twit to receiver? This was just an example using twitter, but would be applicable to any architecture with a "follower" feature

Looking at it from a technical challenge perspective

[faint blade]

No matter what happens you will need to make 1.5K requests.

You have one inbound that creates the Snowflake (ID), then it triggers some form of callback I guess that then needs to push this to all of your followers.

Depending on how Twitter does this it may be trivial, or more complicated. I know Discord for example, has a WebSocket with each user that it simply sends out all messages it receives.

Sometimes you have the more casual approach of the app every once-in-a-while sending a request asking for new tweets

Which would not require these 1.5K requests at first for each tweet

[tiny nimbus]

Are WebSockets used instead of threading for a message, or would the WebSocket use a thread for a direct channel with the end-user?

[faint blade]

I guess we're kind of starting to get into other regions than databases..

They're not mutually exclusive, you can use threading and WebSockets. WebSockets just allow you to send data back and forth as opposed to like a normal requests where you always have request -> response once.

I know in Discord to solve the issue of big guilds, they have "fake users" that get events from WebSocket A, then forward that to the users they're connected to (WebSocket B).

Say WebSocket B also has this form of "mail-man" fake user, WebSocket C. Which is connected to even more users!

This way you won't have one poor server being connected to a million WebSockets

The way databases come into play, st least for Discord, is that they use an eventually consistent database. It doesn't matter if it's a bit wrong (as opposed to SQL). It's just messages.

That means that we know that eventually things will turn out fine.

[dense barn]

im trying to make a table in a database using a command line but its sending a syntax error at near CREATE

CREATE TABLE vote(user_id bigint,vote_count bigint);
``` idk what im doing wrong, can anyone point it out?

bruh

for some reason it worked now :\

so trashy

[faint blade]

What most likely happened was that you didn't have a semicolon on the last time you typed it out if I had to guess

So the query became CREATE TABLE vote(use_id bigint, vote_count bigint)CREATE TABLE vote(use_id bigint, vote_count bigint);

[tiny nimbus]

Got it. Thank you for your help and time to explain!

[faint blade]

I am not sure what question I answered, but I am happy you learnt something 😅

[tiny nimbus]

A technical challenge example would be exactly that efficiency in how to handle the information delivery from server to end-user, having the Web Socket as a solution for it gave me a better understanding on how to identify them and look for solutions. So you helped a lot more than you think haha

[tiny nimbus]

One more question, now query based haha I need to select the lowest price for an item, however that item has to have changed price 7 times in a specific timeframe to be selected

I was thinking on doing something like:

SELECT 
  item,
  MIN(price)
FROM table_items
WHERE 1=1
  AND year_sold = XXXX
  AND item IN (SELECT item, COUNT(DISTINCT(price)) AS count
                FROM table_items
                GROUP BY item
                HAVING count >=7
              );

But then I remembered that that's not how nested queries work haha

Is there an alternative on how to solve?

[stuck shell]

@tiny nimbus first, this query will not work because outer select do not have group by

Also this is somehow screwed because why is item contained in table multiple times? This kinda does not make a sense....

[rustic yarrow]

I have been asked to improve our Postgres database performance with not much specifics on what underlying issue or queries are. I was curious about BigQuery. At what point is BigQuery faster and more perfoany than traditional SQL RDMS like Postgres? Does BigQuery support all the normal SQL clauses and statements?

Right now database is around 80 GB on disk. I don't work too much with databases, so if anyone has any ideas what are good RDMS or cloud solutions to start looking at that might be helpful.

[harsh pulsar]

bigquery is a google-hosted big data thing

you probably don't need it. start by figuring out what exactly constitutes acceptable improvement

postgresql has a huge range of options for configuration and performance tuning

you will want to start benchmarking individual queries, looking at what indexes are configured, looking at how the tables are designed, etc.

heck, maybe you just need to run postgres on a faster machine

e.g. https://dba.stackexchange.com/q/95496/105807 -- see how specific and tunable it is? it would be nuts imo to move to a different database, especially to something like a cloud data warehouse, just because someone is vaguely dissatisfied with "database performance"

[unborn wave]

Thanks for the info!

[broken prawn]

okay so when i start, My exe the exe and the login form start's at the same time and pops up next, to each other what i'm trying to say is i want the login form to start first then after i login then the main program will then load
how would i do that can someone help me

[unkempt prism]

80 GB isn't very big for pg.

You can check out what queries are running at the moment. Great when there is people complaining about slowness and you suspect that a query is taking minutes and not milliseconds.

SELECT pid,
       now() - pg_stat_activity.query_start AS duration,
       query,
       state,
       *
FROM pg_stat_activity
WHERE true    AND state = 'active'

And you can put the problem queries through: https://www.postgresql.org/docs/13/using-explain.html

And

There is a postgres slack that have very knowledgeable people that will help you get the most out of postgres.

[rustic yarrow]

oh wow thanks for all the tips this will help a lot i think

[tardy void]

Does anyone have an idea what can be the code to create this table??

I'm New.

[stuck shell]

@tardy void CREATE TABLE etc...? Maybe try to explain your question a bit better 🙂

[tardy void]

Yes create tabel and all

[stuck shell]

Do you trying to replicate this table? Or maybe query because this looks like tui output of query.

[placid rune]

<@&831776746206265384> rule 6 discord nitro spam

[jade osprey]

Hi i am getting this error :

Ignoring exception in command set_log_channel:
Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "/home/runner/Ignisbot/cogs/level.py", line 18, in set_log_channel
    cur = await self.bot.db.execute("SELECT logs_channel FROM Channels WHERE  guild_id = ?", (ctx.guild.id))
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 184, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 129, in _execute
    return await future
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/aiosqlite/core.py", line 102, in run
    result = function()
ValueError: parameters are of unsupported type

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 939, in invoke
    await ctx.command.invoke(ctx)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 863, 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: ValueError: parameters are of unsupported type

here is my code

  @commands.command()
  async def set_log_channel(self, ctx, channel_name : str):
    cursor = await self.bot.db.execute("INSERT OR IGNORE INTO Channels (guild_id, logs_channel) VALUES (?,?)", (ctx.guild.id, channel_name))
    if cursor.rowcount == 0:
      await self.bot.db.execute("UPDATE Channels SET logs_channel = ? WHERE  guild_id = ?", (channel_name,ctx.guild.id) )
      cur = await self.bot.db.execute("SELECT logs_channel FROM Channels WHERE  guild_id = ?", (ctx.guild.id))
      data = await cur.fetchnode()
      logs_id = data[0]
      await ctx.send(logs_id)

I want to get the name of the channel to use later

I just got into databases

so please keep your answers simple

here is my initilization

async def initialize():
    await bot.wait_until_ready()
    bot.db = await aiosqlite.connect("./data/database.db")
    await bot.db.execute("CREATE TABLE IF NOT EXISTS guildData (guild_id int, user_id int, exp int, warn_reason TEXT, PRIMARY KEY (guild_id, user_id))")
    await bot.db.execute("CREATE TABLE IF NOT EXISTS Channels (guild_id int, logs_channel text, PRIMARY KEY(guild_id))")

I have passed imported and passes this in my cog

[frozen python]

What is the difference between these two statements that give the same results?

[grim vault]

The second parameter to execute() is not a tuple (missing , after ctx.guild.id:

      cur = await self.bot.db.execute("SELECT logs_channel FROM Channels WHERE  guild_id = ?", (ctx.guild.id,))```
But you don't need the select because at this point `logs_channel` must be the same as `channel_name`.

[jade osprey]

I was just testing if it saved thats why i am selecting again

[grim vault]

I didn't see any commit()

[jade osprey]

i'll add await self.bot.db.commit()

[grim vault]

And bases of what I see you can just INSERT OR REPLACE, no UPDATE needed.

Or use the UPSERT logic.

[jade osprey]

so can I delete that line

[grim vault]

Maybe don't do it. An INSERT OR REPLACE does trigger an ON DELETE clause if you have foreign key joins to the table, so an UPSERT would be better:

await self.bot.db.execute(
    "INSERT INTO Channels (guild_id, logs_channel) VALUES (?,?)"
    " ON CONFLICT(guild_id) DO UPDATE SET logs_channel = excluded.logs_channel",
    (ctx.guild.id, channel_name)
)```

@jade osprey ^^

[jade osprey]

thanks

[jade osprey]

thanks for the help but I used this code and it works:

  @commands.command()
  async def set_log_channel(self, ctx, channel: discord.TextChannel=None):
    if channel == None:
      return await ctx.send("You need to mention a channel")

    await self.bot.db.execute('''UPDATE Channels
                          SET logs_channel=?
                          WHERE guild_id=?''',
                       (channel.id, ctx.message.guild.id))
    await ctx.send("Twitch channel set to <#{}>".format(channel.id))

    await self.bot.db.commit()
    

I had used this code before you replied

[grim vault]

This will only work if there is already an entry for the guild_id in the table. If there never was and INSERT it will not work.

[hazy mango]

Fwiw it was a scam that steals user tokens, so if you did happen to click on the link make sure you change your discord account password to regen your account token

[jade osprey]

How do I select a value form a table as a varable

this is my code

  @commands.command()
  async def save_name(self, ctx, name):
    self.bot.db.execute("""
    INSERT INTO testTable
     (guild_id, name) 
     VALUES (?,?)
    """, (ctx.guild.id, name) )

how do i select name from here and save it as varable in a diffrent command

[jovial marten]

how do I connect any of this three tópics c Database triggers Query optimization Database transaction scheduling with "the right to be forgotten" ?

[lone island]

i need help on taking leaderboard over json data in postresql

[harsh pulsar]

Sounds like a homework assignment. Did you come up with any ideas on your own?

[torn sphinx]

!rule 8

[delicate field]

Rules

8. Do not help with ongoing exams. When helping with homework, help people learn how to do the assignment without doing it for them.

[narrow moth]

Databases are so bloody awesome.
Even the most basic understanding and ability to use them just ... on an organisational level, is amazing.
And if you're good at them; manipulating data and optimisation... Man, that would be such an awesome skill.

[torn sphinx]

hello, i was wondering about any cool database to use for fun, just wanted to try something new. any ideas?

[brazen charm]

postgres

[narrow moth]

PostgreSQL is totally awesome, but it is a pretty big one. The scope and scale of it is massive. It's still awesome to play with and plays well with python

[torn sphinx]

i tried it

i basically tried like all sql ones

and mongodb

[brave bridge]

have you tried EdgeDB?

[torn sphinx]

uh no

isnt it like postgre

[brave bridge]

It uses postgres as a base, but it has its own language and such

https://www.edgedb.com/tutorial

[torn sphinx]

will check 👍

[narrow moth]

I looked pretty hard at ArangoDB and was really impressed.
https://www.arangodb.com/

[brave bridge]

If you just want something really small, try SQLite

[torn sphinx]

i just want to try something new

[narrow moth]

Arango looks like it's a pretty different beast from the SQL relational databases.
It's def on my to do list, but I wanted a more stability and history since I'm really new at all this.

[harsh pulsar]

i have found the reason why mongodb truly does suck for storing things that aren't "documents": no migrations

i think at one point "no migrations" was billed as a positive thing. but if your data does actually have a schema, the mongodb ecosystem basically has the attitude of "fuck you do it yourself"

unless anyone know of a tool that does mongodb migrations without me having to write (and very very carefully test) a bunch of code, that'd be wonderful

[torn sphinx]

dont use mongodb

[harsh pulsar]

i wish!!

[brazen charm]

accidentally miss spelling the collection Delete it all

[echo finch]

can i ask for help with MySQL in here?

About getting python to interact with it properly

[prisma girder]

Yep! Go ahead

[echo finch]

import mysql.connector

cnx = mysql.connector.connect(user='root', password='',
                              host='localhost',
                              database='ecranked')
print(cnx) 
c = cnx.cursor()
data = c.execute("SHOW TABLES")
print(data)

That returns NONE
Even though i have tables

And its connecting to this database cause if i change the password,username,or database name it fails
Im "able" to execute insert query's without errors but they dont actually do anything in the databse
Also the root user has full admin privalges

_ _

[prisma girder]

What is your library?

[echo finch]

Uhh I’m not sure. How can check? I just saw that the import was valid. I’ve done something similar a long time ago

[prisma girder]

How you installed mysql library for Python?

[echo finch]

I know I ran a PiP install for that a long time ago

Idk if the package name is just “MySQL” or something else

[echo finch]

"mysql-connector-python" 8.0.26

i dont know if i did syntax wrong or something?

I mean its just like its not really connecting

When I did it a long time ago the sql was handled by xampp but now I’m just hosting it by itself

[prisma girder]

!pypi mysql-connector-python

[delicate field]

mysql-connector-python v8.0.26

MySQL driver written in Python

[prisma girder]

Give me a second

[prisma girder]

It should be like this but I am not sure

import mysql.connector

cnx = mysql.connector.connect(user='root', password='',
                              host='localhost',
                              database='ecranked')
print(cnx) 
cursor = cnx.cursor()
cursor.execute("SHOW TABLES")
for data in cursor:
  print(data)
cursor.close()
cnx.close()

[echo finch]

Oh

Its cursor.close()

I never saw that anywhere

Alright that fixed it

[prisma girder]

🎉

[prisma girder]

Code almost copied from examples

[visual sluice]

hii

[primal notch]

I have a mongo collection like this

root
- _id
- someData
- [childID]```
Is there a way to ensure that childIDs are unique within each root? I couldn't find how to do that with indexes, and I don't think that querying childIDs and checking if the ones I'm about to push are present already is efficient

Okay, I just had to use $addToSet instead of $push https://stackoverflow.com/a/20027485

[primal notch]

Another thing, what's the best way of imitating sql foreign key constraint in mongo?

It seems that I should just run cleanup queries each time I delete something that's being referenced elsewhere

[brave bridge]

are you sure you need mongo and not a relational database? 🙂

[primal notch]

Yeah, I only need to relate 2 collections

[brave bridge]

ah

[harsh pulsar]

i don't know if you can do that in mongo

does it have triggers?

huh yeah, mongo does have triggers

ah, no

it's a mongodb atlas feature that lets you run arbitrary javascript in response to database events

unclear if you can do anything like that in mongo itself

[primal notch]

Well, apparently I'll have to run some checks on the application level

[jovial marten]

I have a question can triggers if done badly grant access to unwanted hackers as in they can add an admin user to the database and then access the information ?

[static rose]

File "directory_was_here", line 81, in guild_data_check
    if guilds_db.count_documents({'guild_id': str(guild.id)}) != 0:
AttributeError: 'int' object has no attribute 'id'

async def guild_data_check(self, guild):
        guilds_db = self.client['stain']['guilds']
        if guilds_db.count_documents({'guild_id': str(guild.id)}) != 0:
            return

this has worked before

but now it doesnt

could anyone tell me the issue if there even is one

[brave bridge]

You're passing an int as the guild argument

[static rose]

so it would be async def guild_data_check(self) and not guild_data_check(self, guild)

the way i have it now has been working for months

its just now stopped working

[brave bridge]

Because before you were passing a Guild object as the guild argument, and now you're passing an integer. Find the place where you're calling the function and debug there.

[static rose]

hmmmm ok brb

[floral sand]

how do we put a variable? (python)

[little basin]

can anybody recommend a resource or a book where I can learn to create a gui for mysql on python ?

[jade osprey]

just started with databases i am getting a an error
here is my code

import random 
import sqlite3

conn = sqlite3.connect('database.db')

c = conn.cursor()
# guild_id : {member_id: [count, [(admin_id, reason)]]}

c.execute("""
    CREATE TABLE warnings (
        guild_id text,
        member_id text,
        admin_id text,
        reason text
    )


"""
)

c.execute("INSERT INTO warnings VALUES ('some_cool_people','person1','admin1','test_Reason' )")
c.execute("INSERT INTO warnings VALUES ('epic guild','warnedperson1','Person2','reason 2' )")
c.execute("INSERT INTO warnings VALUES ('epic guild','warnedperson1','Person2','reason 3 and some crap' )")

print('added to databse')


member = ''

reasons = c.execute(f"""
SELECT reason 
FROM warnings
WHERE member_id = ANY (
    SELECT reasons
    FROM warnings
    WHERE member_id = 'warnedperson1'
)
""")
for reason in reasons:
    print(reason)



conn.commit()

conn.close()

here is my error

added to databse
Traceback (most recent call last):
  File "/Users/mainuser/Desktop/sqlite_test/bot.py", line 30, in <module>
    reasons = c.execute(f"""
sqlite3.OperationalError: near "SELECT": syntax error

what is the right syntax

I want to select all the warings given to warnedperson1

[faint blade]

Then just do SELECT reason FROM warnings WHERE member_id = 'warnedperson1' and select multiple rows

[limber pebble]

Heyy guys, how can i store python class objects array in mysql using pymysql?

reasons = Column(json.dumps(String))
This is how I was trying to do it but i got an error, saying
Object of type mappingproxy is not JSON serializable

[torn sphinx]

Hello I need help

Does anyone here use replit db?

I doubt that a piece of data got erased for my db in replitdb

Now, Im not sure that was it my mistake or is replitdb that good

If anybody experienced anything like that while using replitdb, pls respond

[woven bough]

import random 
import sqlite3

conn = sqlite3.connect('database.db')
# guild_id : {member_id: [count, [(admin_id, reason)]]}

conn.execute("""
    CREATE TABLE warnings (
        guild_id text,
        member_id text,
        admin_id text,
        reason text
    )


"""
)

c = conn.cursor()

c.execute("INSERT INTO warnings VALUES ('some_cool_people','person1','admin1','test_Reason' )")
c.execute("INSERT INTO warnings VALUES ('epic guild','warnedperson1','Person2','reason 2' )")
c.execute("INSERT INTO warnings VALUES ('epic guild','warnedperson1','Person2','reason 3 and some crap' )")

conn.commit() # see that

print('added to databse')


member = ''

c.execute(f"""
  SELECT reason 
  FROM warnings
  WHERE member_id = ANY (
    SELECT reasons
    FROM warnings
    WHERE member_id = 'warnedperson1'
)
""")

for reason in c.fetchall():
    print(reason)

conn.close()

@jade osprey

[jade osprey]

thanks

[woven bough]

noprob dude

[woven bough]

u must use cursor.fetchall() or cursor.fetchone() to get data correctly

import mysql.connector

cnx = mysql.connector.connect(user='root', password='',
                              host='localhost',
                              database='ecranked')
print(cnx)
cursor = cnx.cursor()
cursor.execute("SHOW TABLES")
for data in cursor.fetchall():
  print(data)

cursor.close()
cnx.close()

[charred arch]

can i post a error of database not related to python?

or its not allowed?

[inner sentinel]

looking at the topic, go for it

[charred arch]

code and output

@inner sentinel did u slept

[harsh pulsar]

maybe you need a password?

[charred arch]

i fked up the config

i am reinstalling the php

[thin hill]

How to do i add a Database to my bot using Replit?

[austere portal]

replit has a built in database

[grim zephyr]

@austere portal

plz help

[austere portal]

yes

[grim zephyr]

can u tell me how to add integer to a integer in mongo and how to update something

[austere portal]

i havent used mogo

[grim zephyr]

oh i see

[whole coral]

hello everyone. Here's a quick question, I want to set to zero a value a field in postgresql database of my discord bot every 1st day of month. Any advices on how to do it better? Should I make a listener in Cogs or an event in script, or a simple "while" loop will do?

[dense barn]

How do I alter a column to set it as primary key using a command line in postgres?

[whole coral]

check this https://stackoverflow.com/questions/29075413/change-primary-key-in-postgresql-table

[dense barn]

The column already exists

[grim zephyr]

@dense barn help plz

await w.update_one = {"guild": ctx.guild.id, "member": member.id, "warnn" = "warnn" + 1}`

what is wrong in it

?

[dense barn]

idk anything about mongo

[grim zephyr]

oh i see

[torn sphinx]

#discord-bots help pls

[jaunty galleon]

You sure it works that way?

Isn't it await collection.update(query, data)?

Like:

query = {'guild': ctx.guild.id, 'member': member.id}
data = {'$inc': {'warnn': 1}}
await collection.update(query, data)```

[sharp sphinx]

How do I enable the "spellfix" extension for sqlite? I do

self._db = sqlite3.connect(':memory:')
self._db.enable_load_extension(True)
self._db.load_extension('./spellfix')

yet all I get is The specified module could not be found.
How do I solve this on Windows 10?

[sharp sphinx]

nvm I fixed it

[grim zephyr]

@jaunty galleon

    w.find_one_and_update = {{"guild": ctx.guild.id, "member": member.id },{"$set": {"warnn" : "warnn" + str(1)}}}
TypeError: unhashable type: 'dict'```

[jaunty galleon]

What is w?

A collection?

And it's find_one_and_update? Why noy just update_one

[grim zephyr]

its motor

TypeError: unhashable type: 'dict'```

this is making me mad

[jaunty galleon]

So in motor shouldn't it be await collection.update_one(query, data)

[grim zephyr]

this error

lemme try

@jaunty galleon

[jaunty galleon]

Try update_one

[grim zephyr]

i am using that only

[jaunty galleon]

Wdym?

[grim zephyr]

i already tried that before u said

and it worked

[jaunty galleon]

um what worked?

[grim zephyr]

ye

its motor

[jaunty galleon]

I know motor doesn't have update method I think

[grim zephyr]

i think that too

and i am confirmed that it doesnt exist

in motor

@jaunty galleon

how to subtract

i wanna subtract from it too

[jaunty galleon]

um

I don't really know pymongo

Maybe soe $sub method but maybe search the docs

[grim zephyr]

someone help

@faint blade sry for ping but can u tell me how to subtract a data from mongo like i want to do 1 - 1

[torn sphinx]

Hello, I have a problem with this function

    if player_id == "all":
        cursor.execute("""SELECT ? FROM player""", column)
    else:
        try:
            idlist = list(player_id)
        except:
            idlist = [player_id]
        exec = "SELECT {} FROM player WHERE ID in {}".format(column, idlist)
        print(exec)
        cursor.execute(exec)
    print(cursor.fetchall())```

[grim zephyr]

make a table

[torn sphinx]

but the table already exist, the TABLE player, and 12123 is just the ID of a player

[grim zephyr]

"""CREATE TABLE IF NOT EXISTS"""

[torn sphinx]

already done

but it talk about the table 12123, I try to access the the table player

[faint blade]

I don't know MongoDB sorry

[grim zephyr]

ah i see

[torn sphinx]

So ?

Someone know ?

[torn sphinx]

someone know why this command try to access to the 12123 table and not player table
SELECT * FROM player WHERE ID in [12123]

[grim zephyr]

@jaunty galleon

do u know any way by which i can get the highest value from the db

[jaunty galleon]

No idea

[grim zephyr]

ok

[jaunty galleon]

Try maybe: collection.find().max(query)

[grim zephyr]

what am i supposed to put in place of query

[torn sphinx]

Is it normal ?

[jaunty galleon]

https://docs.mongodb.com/manual/reference/operator/meta/max/

[grim zephyr]

maybe i fixed in my own way

@jaunty galleon

is there any method that can list some int from range

!e

for i in range(8):
  print(i)

[delicate field]

@grim zephyr :white_check_mark: Your eval job has completed with return code 0.

001 | 0
002 | 1
003 | 2
004 | 3
005 | 4
006 | 5
007 | 6
008 | 7

[pure mortar]

most popular databases for python web apps currently?

im considering using mongoDB for my next project

the goal is to become more familiar with some of the more common ones rn

[brazen charm]

postgres is pretty popular

[pure mortar]

ok. maybe ill make a SQL and a NoSQL version

[steep ingot]

How do i access a mongodb database from a heroku app?

I can't seem to get it work without setting an ip which won't work for heroku.

[torn sphinx]

postgres is web scale

[steep ingot]

I can not use postgres though, is there no way to access a mongodb from heroku at all?

[ornate isle]

what do you mean? does the mongo database only allow access from certain IP addresses? where does the mongo instance live?

[steep ingot]

Yes, or at least I can't find a way for it to allow any ip with the correct login to access it

[ornate isle]

what kind of data/structure will your app need? sql vs. nosql is a huge difference (and nosql means a ton of different things) and which to use will be best informed by the kind of data you will be using with it

[ornate isle]

you can definitely disable it, though i'm not familiar enough with the state of mongo security these days to know if that's actually a horrible idea. but whitelisting 0.0.0.0 should effectively disable it. the safer option that may cost a little bit of money would be to use one of the static IP addons for heroku: https://elements.heroku.com/addons#network

[steep ingot]

Yeah ok, I’ll try the 0.0.0.0 thing since I can make a 128 character password and username so keyboard spam go brrr and hopefully it’ll work. I will see about the static IP addons. Might have to ask some community members to split the cost but whatever

[harsh pulsar]

@steep ingot are you hosting mongodb and your app on the same heroku instance?

Oh i see above. Surely someone can control who can access the db?

Where is mongo hosted

[grim zephyr]

@harsh pulsar sorry for the ping bud can u plz help me, i want to sort a list in mongodb using motor but unable to do it

[inner ivy]

whats a good database to use for lists like ['one','two']

bc the only one ik of is json and json always has data loss its annoying

[grim zephyr]

use mongo for jsonn

thats good

[inner ivy]

i already use mongo atm

but is there a way to make mongo have lists like: ['one,'two','three']

bc as far as ik mongo is like:
{"one":"stuff"}

[grim zephyr]

well u r a mongo user right

[inner ivy]

a brand new one, but yes

[grim zephyr]

do u know how to sort using motor

[inner ivy]

dont think so

i usually know functions but not what theyre called

lol

[grim zephyr]

lol

me trying to sort a list but dont know how to

nothing is working

[inner ivy]

wait so can i use mongodb to have lists @grim zephyr?

[grim zephyr]

probs

[inner ivy]

let me try ig

well ig not @grim zephyr

[grim zephyr]

me trying to sort a lis

list

lol

[inner ivy]

when i do like:

col.insert_one(list)```

it dont work

[grim zephyr]

no u cannot do it like that

[inner ivy]

then how

bc the error basically means that it has to be {"stuff":"stuff"}

[grim zephyr]

its not possible

[grim zephyr]

it has to be like this

[inner ivy]

thats why i asked in the first place

whats a database thats good that can be used to store lists

bc i want to be able to just get the list from the database and loop through it

[grim zephyr]

try sql

postgre

[torn sphinx]

[brave bridge]

@grim zephyr Do you understand what

XXXXXXX object is not subscriptable

means?

[grim zephyr]

i am getting confused with sorting

[brave bridge]

It means that you're trying to take an object of type XXXXXXX and perform the ...[...] operator on it.

[grim zephyr]

wdym

plz explain

[brave bridge]

!e

x = {"foo": "bar"}

print(x["foo"])

^ this operation is called "subscription"

[delicate field]

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

bar

[grim zephyr]

this caused the error

test = r.find({"guild": ctx.guild.id, "member": member.id}).sort("warn", -1)
fin = test["warn"]
print(fin)

but why?

[brave bridge]

!e

x = 42

print(x["foo"])

^ this is what you're getting when you try to subscript something that you shouldn't.

[delicate field]

@brave bridge :x: Your eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 3, in <module>
003 | TypeError: 'int' object is not subscriptable

[grim zephyr]

when i tried to print test i got something like motoraiocursor position at

[brave bridge]

Did you forget to await something, perhaps?

[grim zephyr]

lemme check

oh ye i forgot to await it lemme try once more

[brave bridge]

Whenever you do asynchronous input/output, you need an await. If you don't have an await, you're doing something wrong.

[haughty gust]

oooo database in python

nice

[grim zephyr]

Traceback (most recent call last):
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\USER\Desktop\ZENESIS DEVELOPEMENT\PYTHON\ZENESIS\WARN SYSTEM [MONGO]\commands\warn.py", line 102, in remove_warn
    test = await r.find({"guild": ctx.guild.id, "member": member.id}).sort("warn", -1)
TypeError: object AsyncIOMotorCursor can't be used in 'await' expression
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: object AsyncIOMotorCursor can't be used in 'await' expression

[brave bridge]

you'll have to check the motor docs on how to use find and sort

[grim zephyr]

find needs await but dont know about sort

[burnt turret]

collection.find doesn't need to be awaited in motor

https://motor.readthedocs.io/en/stable/tutorial-asyncio.html#querying-for-more-than-one-document

[grim zephyr]

still getting the same error

Traceback (most recent call last):
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\USER\Desktop\ZENESIS DEVELOPEMENT\PYTHON\ZENESIS\WARN SYSTEM [MONGO]\commands\warn.py", line 103, in remove_warn
    async for x in test["warn"]:
TypeError: 'AsyncIOMotorCursor' object is not subscriptable
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: 'AsyncIOMotorCursor' object is not subscriptable

test = r.find({"guild": ctx.guild.id, "member": member.id}).sort("warn", -1)
async for x in test["warn"]:
    print(x)```

[burnt turret]

That's not the same error

It says that you can't do test["warn"]

Just think about it, the cursor contains multiple documents, all of which would have different values for the "warn" key

[grim zephyr]

but why i need the highest value of warn thats why i descended the whole documents

[grim zephyr]

every warn has a different int

[burnt turret]

Yes

[grim zephyr]

but i want to get the highest one and delete it

[grim zephyr]

^

[burnt turret]

why are you first retrieving it if all you need is to delete?

please be patient, i'm reading the docs

[grim zephyr]

oops sry

actually i am making a warn system and one of my collection is containing data that when a user was warned and why, but i want to delete that data whenever someone tried to remove the warn like if the user wants to remove 4 warns then the code will delete the first four values and for that i am trying to get the warn value so that i can delete a particular document

[burnt turret]

right, but did you understand why the error happens in your current code?

[burnt turret]

this

to access the warn from each document, you want

async for x in test:
  x["warn"]

[grim zephyr]

ye it have different values and i want to get the highest value

[grim zephyr]

ok

lemme try

@burnt turret

is there a way to get only one value at a time like i want the highest only

i am getting all the value at once but i want only the highest

maybe i need to use limit

[burnt turret]

yeah .limit(1) is what i see online

[grim zephyr]

thanks anand you helped me a lot

[jolly rivet]

Hey guys,
Does anyone know sql ?😢

if yes please ping me or dm

[brave bridge]

Just ask your question. Don't ask if there's an expert around here

[jolly rivet]

do u know

where did u learn from?

online or offline

I just want resources to learn

no questions

[brave bridge]

@jolly rivet
If you want to learn SQL: https://sqlbolt.com/
Different database management systems have their own dialects of SQL, so there are going to be different resources for each one.

[jolly rivet]

ok thanks

[brave bridge]

For example, if you're using SQLite, it has a reference:
https://www.sqlite.org/lang.html
So if you want to see how to do a particular thing (like create an index), you should go there.

[lone island]

SELECT * FROM gametb ORDER BY items -> 'bal' ->> 'money' DESC
why is the desc not working in this query ?

it doesn't effect anything

[grim zephyr]

pun is not none
Ignoring exception in command warn:
Traceback (most recent call last):
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\USER\Desktop\ZENESIS DEVELOPEMENT\PYTHON\ZENESIS\WARN SYSTEM [MONGO]\commands\warn.py", line 108, in warn
    punish = pool["punishment"]
TypeError: '_asyncio.Future' object is not subscriptable
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\USER\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: '_asyncio.Future' object is not subscriptable```

pool = p.find_one({"guild": ctx.guild.id})
            print(pool)

this is where the error is occuring

but there is nothing in the db

[austere portal]

Looks like pool is an asyncio.Future object which is not subscriptable

ig you need to await p.find_one

[steep ingot]

It’s on their default thing. Atlas I think it’s called. I can find how to make it work with JavaScript (I think it’s js anyway)but not python.

[steep ingot]

Actually it’s on aws, I’m using the Ireland site for testing but production will be on the Oregon servers

[torn sphinx]

Hello

[delicate valley]

best material for databases... any suggestions?

[steep ingot]

Whitelisting 0.0.0.0/0 worked. When you set up a user there are 2 ways u can do it, one is when you click connect on the specific db, the way I was doing it. Setting access to any ip there didn’t work but when you go to the place to add more ips in future there’s a button for any ip which added 0.0.0.0/0, pain in the ass to figure out they were different.

[prisma girder]

Check pinned messages

[austere portal]

Any good free services to host a postgres database?

[prisma girder]

Heroku?

[wheat iron]

Hi everyone, I have a question concerning a dataframe I am trying to reshape, basically I have a datframe of this type

An I am trying to reshape it to this form

I am currently treating the datframe using pandas

If any one has the answer that would be awesome

[dense oar]

MongoDB (pymongo) question, why does the following code:

client = pymongo.MongoClient("...")
print(client.primary)

Returns None

But the following code:

client = pymongo.MongoClient("...")
sleep(0.001)
print(client.primary)

Returns ('192.168.0.200', 27017) which is correct?

Why does it not return the primary correctly when not sleeping for 1 ms? Same goes for client.secondaries

[austere portal]

Thanks, I'll use that

[rustic saffron]

Hi, I need to extract the "bot1" information of a user in Firebase with Pyrebase. But not from a specific user. But the one who has executed the code. I don't know if I explain myself

[rotund abyss]

does anyone have faced this on macos big sur
`ERROR: Could not find a version that satisfies the requirement psycopg2 (from versions: 2.0.10, 2.0.11, 2.0.12, 2.0.13, 2.0.14, 2.2.0, 2.2.1, 2.2.2, 2.3.0, 2.3.1, 2.3.2, 2.4, 2.4.1, 2.4.2, 2.4.3, 2.4.4, 2.4.5, 2.4.6, 2.5, 2.5.1, 2.5.2, 2.5.3, 2.5.4, 2.5.5, 2.6, 2.6.1, 2.6.2, 2.7, 2.7.1, 2.7.2, 2.7.3, 2.7.3.1, 2.7.3.2, 2.7.4, 2.7.5, 2.7.6, 2.7.6.1, 2.7.7, 2.8, 2.8.1, 2.8.2, 2.8.3, 2.8.4, 2.8.5, 2.8.6, 2.9, 2.9.1)
ERROR: No matching distribution found for psycopg2``

[torn sphinx]

UPDATE score_id 
SET score = score + 100 
WHERE userid = 123
``` shouldnt it add 100 to the score everytime its ran ?

[steep ingot]

I'm having issues with pymongo. Why is this code returning 0 for a value that should be about 15?py dayAvg = db.playercount.aggregate( [{ '$group' : {'_id': '$Date', 'avg':{'$sum' : '$players'} }} ] ) for x in dayAvg: print(x)
It returns:{'_id': 'Aug-30-2021', 'avg': 0} {'_id': None, 'avg': 0}

[torn sphinx]

What can I use to save data like a username and a couple values

For multiple people and read and overwrite it

[latent arrow]

how to make json counter i forgot

[austere portal]

Can you elaborate?

[latent arrow]

what do you mean

just so like everytime a run the code the counter goes up by one

i know i am bad

and like with json

[latent arrow]

was that eloborate?

[austere portal]

Can you give more detail on what you are trying to do?

[latent arrow]

just everytime

whine i in visual studio code

run the code

it prints one

the second time i start

2

then the third time 3

and that it remembers it

everytime

[austere portal]

oh ok

You can create a json file and open it and increment the value

[latent arrow]

thats the problem

i didnt code for like 3 months

and forgot how to do it

[austere portal]

You can use the json module and the built in open function ```py
import json

with open("counter.json", "w+") as f:
try:
data = json.load(f)
data["count"] += 1
except json.decoder.JSONDecodeError:
data = {"count": 1}

json.dump(data, f)```This code is not tested but should work

@latent arrow

[latent arrow]

thx your the best i will try

@austere portal or i havent coded in years or i am frking stupid do you know whats wrong?

[austere portal]

can you share all of the code

[latent arrow]

from witch part

[austere portal]

all of it

[latent arrow]

j

it takes the picture

but when i added the json thing

i doesnt saves it as a picture

[austere portal]

sorry, i am not familiar with the open-cv library

[latent arrow]

it is not about that

it works completly fine

but when i try to save file

i just want to add a track to it

like image 1, 2etc

but when i name it

file = f"test_image{data}.png"

data is the counter from json

[austere portal]

Replace data with data["count"]

[latent arrow]

file = f"test_image{data["count"]}.png

[latent arrow]

?

[austere portal]

yes, like that

[latent arrow]

??

it doesnt work

at least i get error

[austere portal]

my bad, replace "count" with 'count'

[latent arrow]

i get error

i just realized

i need to replace it

to

[austere portal]

i made a typo, its count not counter

[latent arrow]

its working

kinda

the counter is not going up

when i restart the program

@austere portal

[austere portal]

wait a bit

[torn sphinx]

has a database a password so one can modify or retrieve data from it? If so, where to store this password then? And can you have admin password who can do anything, and a read_only password which can only retrieve data?

[frozen quail]

How can I connect to MySQL inside kubermetes pod that is hosted on GCP?

[faint blade]

No, not quite. You can do SELECT Name from Group_Channel WHERE ChannelId = 123

Or, if you want to select the Lang group row you can use a sub query like SELECT * FROM Lang_group WHERE Name = (SELECT Name FROM Group_Channel WHERE ChannelId = 123)

[primal notch]

Looking for some mongo help. I have a deep structure like

quiz
- [nodes]
- - question
- - - [options]

And I'm trying to query an option by its id, but it returns the whole options array

db.quizes.find({"nodes.question.options._id": ObjectId("612deb90e3dc8bc1edda865f")}, {"nodes.question.options.$": 1})

{ "_id" : ObjectId("612d354f1dc7f3e83203520a"), "nodes" : [ { "question" : { "options" : [ 
  { "_id" : ObjectId("612deb90e3dc8bc1edda865f"), "key" : "A", "text" : "Test Option", "value" : 1, "order" : 0 }, 
  { "_id" : ObjectId("612debdae8f93b6834f164f3"), "key" : "B", "text" : "Test Option 2", "value" : 0, "order" : 0 } 
] } } ] }```

What am I doing wrong? It works with shallower queries, like geting a node

[molten sequoia]

Hi
Probably you got this already and I can't find straight forward explanation / solution as I used to with other issues
I've build a class of variables to be stored on SQL server Table
The simple way to put it is that there is a value of NULL I want to store in INT column.
The variable value X in python in X = None , and the query looks like
"""INSERT INTO table (int column)
VALUES ('{}')""".format(x)

I get the error - Conversion failed when converting the varchar value 'None' to data type int

Any idea why?

[unkempt prism]

by having the single quotes you have a VARCHAR.

I assume you are using string formatting for this. You should try to use bindings that will handle situations like this and have extra benefits like protecting against sql injection.

[molten sequoia]

Thanks. actually this makes sense to another issue I've encountered
Can you suggest such binding to this simple example of code?

[molten sequoia]

BTW - hen quotes removed, I get the Invalid column name 'None' error
Annoying, I'm lost

[unkempt prism]

SQL doesn’t understand None. You need to change it to a string null.

[brave bridge]

@molten sequoia You should never use f-strings of .format() to create queries. This will make your code vulnerable to SQL injection. You need to use the built-in query formatting of your adapter library. (they also handle all the formatting correctly, like inserting NULL on None)

It will look something like ```py
execute("INSERT INTO table (column) VALUES(?)", (x,))

[molten sequoia]

Then store the string Null into the INT column? it just gives me a different error

[molten sequoia]

Can you elaborate on the buit-in query formatting?
I built a function that gets a query, and handles the DB conn interfact
Question is how to deliver the variable 'query' in the right formatting
Can send you a code sample if would be useful

[brave bridge]

Libraries that let you interact with databases have a mechanism to safely and correctly substitute parameters into queries. For example, in sqlite3 you can do:

connection.execute("DELETE FROM users WHERE name = :user_name", {"user_name": form.user.name})
``` which is different from ```py
connection.execute("DELETE FROM users WHERE name = '{}'".format(form.user.name))
```  in that:
- it will work correctly if the name has a `'` character
- it will insert NULL correctly if `name` is `None`
- it will correctly raise an error if `name` is, say, an integer (that's probably a programming error)
- it will not erase your entire database if the user provides `' OR ''='` as the  name

[molten sequoia]

Very helpful.
Managed to sort this. Thanks a lot!

[torn sphinx]

Please help me to overcome from this problem,

The Cursor.execute(val) and my_connect.commit() functions are not running in this code can anyone help me please..🙏

i am not abel to insert data into table through the code......

[faint blade]

Can you scroll down and show the except part?

[harsh sparrow]

Do not screenshot, you might copy pas the code, so people can debug along.. 🙂 jut for advice

[faint blade]

See there is the issue, you're getting an error but not doing anything with it

[torn sphinx]

where bro i am not seeing any error while running the code and it will connect the database correctly but only the problem is not inserting user given data to table of the database..

only cursor.execute(val) and my_connect.commit() these functions are not running i checked can u help me to overcome this problem....

#######-------------Adding the image button
#######-------------This the ADD Student image Button
def addstudent(): # add button work
def submitadd():
id = idval.get()
usn =usnval.get()
name = nameval.get()
fathername = fatherval.get()
mothername = motherval.get()
address = addressval.get()
contactno = contactval.get()
dob = dobval.get()
gender = genderval.get()
category = categoryval.get()
cast = castval.get()
email = emailval.get()
try:
val = 'INSERT INTO studentdata2 (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) VALUES ',(id, usn, name, fathername, mothername, address, contactno, dob, gender, category, cast, email)
Cursour.execute(val)
print('mm')
my_connect.commit()
res = messagebox.askyesnocancel('Notificatrions', 'Id {} Name {} Added sucessfully.. and want to clean the form'.format(id, name)
, parent=addroot)
if(res==True):
idval.set('')
usnval.set('')
nameval.set('')
fatherval.set('')
motherval.set('')
addressval.set('')
contactval.set('')
dobval.set('')
genderval.set('')
categoryval.set('')
castval.set('')
emailval.set('')
except:
pass

[faint blade]

This is because you're doing try: and then except: pass

Try removing that, you're swallowing the error

[torn sphinx]

wt to do can u explain???

[faint blade]

!e ```py
try:
raise RuntimeError('Oops!')
except:
pass

[delicate field]

@faint blade :warning: Your eval job has completed with return code 0.

[No output]

[faint blade]

@torn sphinx

We don't see error because we catched it and did nothing with it

[grim vault]

            sql = 'INSERT INTO studentdata2 VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
            val = (id, usn, name, fathername, mothername, address, contactno, dob, gender, category, cast, email)
            Cursour.execute(sql, val)

You have a wrong insert syntax.

[torn sphinx]

not working bro....

def addstudent(): # add button work
def submitadd():
id = idval.get()
usn =usnval.get()
name = nameval.get()
fathername = fatherval.get()
mothername = motherval.get()
address = addressval.get()
contactno = contactval.get()
dob = dobval.get()
gender = genderval.get()
category = categoryval.get()
cast = castval.get()
email = emailval.get()
try:
strr = 'INSERT INTO studentdata2 VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
val = (id, usn, name, fathername, mothername, address, contactno, dob, gender, category, cast, email)
Cursour.execute(strr,val)
my_connect.commit()
res = messagebox.askyesnocancel('Notificatrions', 'Id {} Name {} Added sucessfully.. and want to clean the form'.format(id, name)
, parent=addroot)
if(res==True):
idval.set('')
usnval.set('')
nameval.set('')
fatherval.set('')
motherval.set('')
addressval.set('')
contactval.set('')
dobval.set('')
genderval.set('')
categoryval.set('')
castval.set('')
emailval.set('')
except:
messagebox.showerror('Notifications','Id already exist try another id....', parent=addroot)

[grim vault]

Does the table studentdata2 have only the 12 columns (in that order) like the insert is using?

[torn sphinx]

yes bro..

[grim vault]

We really need to see the error, remove the try/except to get the full traceback.

[torn sphinx]

ok ...

def addstudent(): # add button work
def submitadd():
id = idval.get()
usn =usnval.get()
name = nameval.get()
fathername = fatherval.get()
mothername = motherval.get()
address = addressval.get()
contactno = contactval.get()
dob = dobval.get()
gender = genderval.get()
category = categoryval.get()
cast = castval.get()
email = emailval.get()
strr = 'INSERT INTO studentdata2 VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
val = (id, usn, name, fathername, mothername, address, contactno, dob, gender, category, cast, email)
raise RuntimeError('Oops!')
Cursour.execute(strr, val)
my_connect.commit()
res = messagebox.askyesnocancel('Notificatrions', 'Id {} Name {} Added sucessfully.. and want to clean the form'.format(id, name)
, parent=addroot)
if(res==True):
idval.set('')
usnval.set('')
nameval.set('')
fatherval.set('')
motherval.set('')
addressval.set('')
contactval.set('')
dobval.set('')
genderval.set('')
categoryval.set('')
castval.set('')
emailval.set('')

when above code runs showing this error after running the code...

[grim vault]

Yeah, well that's what raise does, don't do it. remove the raise RuntimeError('Oops!') and lets see the real error.

BTW, but it did reach the raise RuntimeError('Oops!'), so there was no error before it.

[torn sphinx]

After removing the raise RuntimeError() function in code....

[faint blade]

There you go!

Like it says, Cursor is not defined. Where are you expecting it to be defined?

You need to give the function the cursor you want to use, or open a new one with the database

[torn sphinx]

let's see i will try this now....

[torn sphinx]

sqlite3.OperationalError: database is locked
``` Can someone explain what it's mean ?

cursor.execute("""
     INSERT INTO player VALUES (:ID, :Pseudo, :Balance,  :Earned, :Lost, 
     :Post, :Experience, :Salary, :Composition, :ContributePoint, 
     :Contribution, :ActyToday, :ActyWeek, :ActyMonth)
    """, values)

the lines which cause the error

[compact crown]

Why can I not install request ;((

[torn sphinx]

Thanks bro for suggesting me raise RuntimeError() function this will help a lot to find a problem in my code bro...

[compact crown]

I import request nope, I use the terminal nope

[torn sphinx]

finally i solve my problem ......

[torn sphinx]

thanks bro for your help its means a lot......

[faint blade]

Hm? That's not what I said, what I showed was how your code swallowed the error. Because if you looked at the output, there was no error. Exactly like how your code didn't have an error (it had one, but it didn't show it)

This channel is for databases specifically. You should read #❓｜how-to-get-help.

I'd recommend asking your question in #python-discussion or reading #❓｜how-to-get-help for how you can get a personal help channel.

[torn sphinx]

i really bro it's a new learning for me and i used the with keyword to solve the cursor problem . thanks for u r help bro means a lot.....👍 @faint blade

sorry i don't know where to ask but thanks man next time i will keep in my mind...

[dense barn]

what data type would i use to save milliseconds in postgres? ig i can use bigint?

or may i say epoch time

[tiny nimbus]

Does anyone has a good data warehousing project/tutorial/challenge that can be used to practice how to build one?

[harsh pulsar]

there's an actual timestamp data type, with microsecond resolution. it's 8 bytes, same size as a bigint

maybe there are good reasons to prefer bigint, maybe depending on what kinds of queries you're doing?

[frozen quail]

Can I return self In Pydantic base model?

[faint blade]

Yes, you can always return self

[dense barn]

Im just saving the epoch time when they use the command and then fetch it to make a timestamp like this: <t:1624385691:t>

[waxen swift]

can anyone gib some summary on array operations in py mongo
their docs are trash

[forest pumice]

is there any way to SELECT in SQLite for multiple conditions?

based on what im reading theres no way for me to filter based on multiple columns

[digital wharf]

Should be able to do multiple WHERE clause parts?

[forest pumice]

ah

thanks

[harsh pulsar]

i'd personally just use native python datetimes anyway, but if you already have the data as epoch time then i don't think there's a strong argument against saving it as a bigint

[harsh pulsar]

did you have a more specific question? i agree that their docs are a bit disorganized in that area, but "trash" is not warranted

[waxen swift]

lmao thanks for ur attention
i found the solution

oof oof

[harsh pulsar]

you can't use multiple WHERE clauses, but you can use logical operators AND and OR

SELECT *
FROM my_table t
WHERE
  t.x >= 0 AND t.y + t.z <> 0

[dense barn]

alright

[harsh pulsar]

it's more that saving as timestamp lets you take advantage of all the postgres date/time handling functionality

[dense barn]

Ohk

[molten sequoia]

Hi I got a question for creation of application which deal with users submitting prediction on real events, then based on the events, they get scores/points that are calculated in a non straight-forward way (iterative IF functions), yet, amount of processed and stored data is relatively small. DB in general is considerably basic, not enterprise like, nor DWH relevant.
THE QUESTION - Would it be recommended to:

1. pull data from SQL db, run the processing of data using python, then store the results in a "scores" table on SQL (using pyodbc)
   or
2. define the function inside the SQL db using queries (calculated columns) and scheduled job (for updating tables)

What do you think?

FYI @brave bridge @unkempt prism

[faint blade]

Just do an AND I see now that your question was already answered

[river topaz]

Hello, I'm trying to filter this DataFrame only by the values "1ª dose" of the column "vacina_descricao_dose", but it returns an empty value. How can I fix this?

---

print(df)

---

vacina_nome vacina_dataaplicacao vacina_descricao_dose
0 AstraZeneca 2021-03-31 1ª Dose
1 Coronavac 2021-02-24 1ª Dose
2 Coronavac 2021-03-17 1ª Dose
3 AstraZeneca 2021-05-26 1ª Dose
4 AstraZeneca 2021-03-02 1ª Dose
... ... ... ...
2568078 AstraZeneca 2021-02-01 1ª Dose
2568079 Pfizer 2021-06-18 1ª Dose
2568080 AstraZeneca 2021-05-09 1ª Dose
2568081 AstraZeneca 2021-05-08 1ª Dose
2568082 AstraZeneca 2021-06-16 1ª Dose
2568083 rows × 3 columns

---

filtro = df.query("vacina_dataaplicacao == '1ª Dose'")
print(filtro)
​_____________________________
Empty DataFrame
Columns: [vacina_nome, vacina_dataaplicacao, vacina_descricao_dose]
Index: []

[harsh pulsar]

!code @river topaz

[delicate field]

Here's how to format Python code on Discord:

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

These are backticks, not quotes. Check this out if you can't find the backtick key.

[harsh pulsar]

also usually we handle pandas questions in #data-science-and-ml , but we can leave it here

[river topaz]

Thank you very much!

[harsh pulsar]

also can you provide actual sample data

i suspect there might be extra whitespace or something else strange/wrong with your text data

is this from excel? csv?

[fading moss]

Hi, so I have the following code or line

cur.execute(f'''INSERT INTO GUILD_{ctx.guild.id} (user_id) VALUES ({x.id})''')
``` and i need it to ignore if the(user_id) already exists

[harsh pulsar]

1. don't use f-strings for passing data into sql, use parameterized queries
2. make sure the user id is the primary key of the table, and use whatever "upsert" functionality is provided by your database

[sharp blaze]

await conn.execute("UPDATE keys SET $1 = $2::character varying WHERE id = $3;", network, a, username)

anyone see any problems with that i'm getting syntax error near $1

[harsh pulsar]

you usually can't parameterize column names

you have to use string interpolation, but very carefully: match network against a hard-coded list of valid column names, don't accept arbitrary user input

[brave bridge]

or you might want to not use columns for that, and instead store key-value pairs

but that depends on your use case

[sharp blaze]

i'll probably do this

[south ruin]

Hey guys, I wanna try executing the following query using sqlalchemy

SELECT
  *
FROM 
  mysql.user
WHERE
  host="%"

But for some reason I cannot get it to run without getting the following error:

ValueError: unsupported format character ''' (0x27) at index 50

db_engine.execute("SELECT * FROM mysql.user WHERE host='%';")

Any idea how to overcome this issue?

[oak swan]

i have two databased1and d2 i create new database name test i need data from both backup .sql files is both have diffrent data is there any way to do that ?

[harsh pulsar]

don't use '' with the placeholder, i think

[oak swan]

@harsh pulsar can u pls help in my question

[harsh pulsar]

i don't really know what you're asking and i'm probably not qualified to answer anyway. at least clarify what database you are using.

[oak swan]

i am using sql database

see

i have database name test i create backup 1 day ago , and 1 backup today

so there are two backup with diffrent data in both backup files

now i want to restore both backup files

on same database

in way i get all my data from both backups

[harsh pulsar]

what database? mysql, ms sql server, oracle, postgres, sqlite, ...?

[oak swan]

mysql

[harsh pulsar]

that information is important to include

[oak swan]

sorry

[harsh pulsar]

i have no idea, but it sounds like you're asking "how do i restore a mysql database from 2 separate backups, made on 2 separate days"

[oak swan]

ya

overwrite the exsisting database

[south ruin]

The thing is - it's not a placeholder, it's a wildcard

[harsh pulsar]

admittedly i don't use sqlalchemy like this, i'll have to check the docs on what exactly this does

[south ruin]

And the exact same query works if you replace the '%' with a 'localhost'

[south ruin]

Thanks a bunch!
If you have any other idea I'd be pleased to hear

[harsh pulsar]

well the docs suggest that a plain string is deprecated https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.Connection.execute

OH

you're using % in = but maybe you mean to be using LIKE?

db_engine.execute("SELECT * FROM mysql.user WHERE host LIKE '%'")

like this?

[grim vault]

Use text:

from sqlalchemy import text
...
sql_stmt = text("SELECT * FROM mysql.user WHERE host=:host")
db_engine.execute(sql_stmt, host='%')```

[grim vault]

% is the placeholder for from any host for users in mysql. I don't think he meant the SQL wildcard.

[harsh pulsar]

ah, i didn't know that

[gusty mulch]

Hi can I get some clarification on how I should set up one of my database tables.
I currently have a table (data) which stores guild specific info (such as prefixes, log channels etc) but I want to make a table which also stores stuff about a member (such as how many times each member has joined a specific server). I am thinking I am going to need to add a guild ID as a foreign key to the table but I'm not sure.
I currently have another table (preferences) which stores some member info (however this isn't connected to any guild) so i'm not sure if I should add a optional guild to this table or make a whole new table.
You can see a file of my db dump here: https://sourceb.in/7cqPTDKltk
Please ping on reply

[vernal tide]

Can one user be member of more than one guild:?

[gusty mulch]

theoretically yes that's how I want it to work. it doesn't need to do that rn as the bot is only tracking the members of one server but I want to expand upon the implementation at a later date with another bot and that one will need to have members that are in multiple guilds

[vernal tide]

Then

you will need a many to many relation and thus an intermediate table with user id / guild id (as the only columns in the simplest case)

I would probably put that in right away if you need it since it will be some hassle to edit in later

If you are unsure, leave it out!

[gusty mulch]

okay so I'm setting up a new member-guild table with 2 columns (member and guild). Do I need to mark them both as primary keys or neither as primary keys? since they are technically foreign keys of my preference and data tables correct?

[vernal tide]

My SQL is rusty so I might be off. But afaik, all tables need a primary key, in vanilla postgres. You can however have two columns be a primary key together. Like (name,address) being unique even though two people can live at the same address so name and address mighnt not be unique individually

Having (memberID, guildID) as a primary key together would thus make sense and they would be verified unique by postgres

[gusty mulch]

ah okay cool. that makes sense. I do have some sql knowledge but I'm not very familiar with the dual primary keys

[vernal tide]

https://stackoverflow.com/questions/1285967/postgres-how-to-do-composite-keys

looks relevant

composite key seems to be the correct term

I think 3 or 4 or ... columns might work, too

[gusty mulch]

okay thank you for your help

[vernal tide]

no problem my friend, thanks for reminding me about composite keys!

[gusty mulch]

np

[torn sphinx]

does the header of a database file give a hint what a database it is(sql/sqlite/postgres....)

[brave bridge]

SQL is a query language, not an implementation of a database.

Only SQLite is stored as a single file, other databases are not.

[harsh temple]

suggested db for discord bots ?

[tranquil zinc]

i guess postgres but i use tinydb its ez

[harsh pulsar]

sqlite

[scarlet cobalt]

postgres

[south ruin]

Exactly

[south ruin]

Thanks, seems like it solved the issue!!

[torn sphinx]

tbh anything would work kinda

Hello, use sqlite3 with python and not sure about a thing, if we specify a DEFAULT value to a column, we don't need to specify this column when creating a new row, right ?

because it don't work

cursor.execute("""
CREATE TABLE IF NOT EXISTS member
(
ID             int PRIMARY KEY UNIQUE,
Pseudo          text,
Balance         int DEFAULT 1000,
Earned          int DEFAULT 0,
Lost            int DEFAULT 0,
Post            text DEFAULT 'Stagiaire',
Experience      int DEFAULT 0,
Salary          int DEFAULT 0,
Composition     text DEFAULT '',
ContributePoint int DEFAULT 0,
Contribution    text DEFAULT '',
ActyToday       int DEFAULT 0,
ActyWeek        int DEFAULT 0,
ActyMonth       int DEFAULT 0,
Here            int DEFAULT 1
)""")

but

 cursor.execute("INSERT INTO member (ID, Pseudo) VALUES (?, ?)", member_id, pseudo))

sqlite3.OperationalError: table member has 15 columns but 2 values were supplied

[harsh pulsar]

@torn sphinx the if not exists makes me wonder if an older version of the table already exists but doesn't have the constraints

What does the query pragma table_info('member') show?

[torn sphinx]

Sorry, the problem is solved

it not come from the programm

[harsh pulsar]

No, the table already exists so you need to drop it or use ALTER TABLE

[brave bridge]

or you can run an ALTER TABLE statement if you don't want to lose all the data

[harsh pulsar]

Sqlite doesnt have it

[brave bridge]

it does

https://www.sqlite.org/matrix/lang_altertable.html

[harsh pulsar]

I could've sworn that it didn't, maybe this is relatively new?

Nope, goes back before 2018

Maybe what I was thinking was that you can't alter a column without dropping it?

Oh, maybe it was that you can't rename a table or column without dropping

Never mind, ignore me

[brave bridge]

@harsh pulsar IIRC the most recent addition (2020?) is that you can drop columns

before that, you could add but not remove 😄

[harsh pulsar]

Hmmmmm

[coral totem]

I am trying to create a list in an SQL database,
the list should look like this: [[int, int], [int, int], ...]
Should i save the list in a column in the following format: INT|INT\0 (\0 and | are the separators)
or should i use pickle?
or, instead of all of that, create a table containing columns for everything?

[woven bough]

cursor.execute("INSERT INTO member (ID, Pseudo) VALUES (?, ?)", (member_id, pseudo,))

sqlite sometimes is kinda weird...

[timber crescent]

guys i facing this error in mysql that is making me go to existential crisis

Django is throwing this exception when i try to save my model.
(1366, "Incorrect string value: '\xF0\x9D\x92\xAE\xF0\x9D...' for column 'nickname' at row 1")

The said value of nickname is "𝒮𝒽𝐸𝓃𝑔".
But when i try to save it manually using mysql shell it works

Im using django:latest and mysql:latest

nvm i fixed it

[pure mortar]

congrats

[torn sphinx]

Can anyone help to solve this error and wt is this error saying i am not understanding help me ???

[harsh pulsar]

some databases support arrays or even arbitrary json. what database are you using and what exactly are you storing? you might want to consider using a different table layout entirely

for example @coral totem if you need to save something like the x,y coordinates in a 2d game, don't try to save a pair (x,y), just save an x column and a y column

similarly if you're trying to do something like store a player's inventory in an RPG, you wouldn't want to store that as an array usually. you'd want separate tables:

players:
id | username
...

items:
id | display_name | rarity | gold_value
...

inventories:
id | player_id | item_id
...

[coral totem]

yep, nosql databases, i am using sqlite currently

[harsh pulsar]

postgres supports both arrays and json, and sqlite supports json

[coral totem]

yep, thats exactly the answer i was looking for, thanks, i am gonna do that

[coral totem]

i am gonna create multiple tables instead, thanks

[harsh pulsar]

great

make sure to use foreign key constraints

[coral totem]

yep 🙂

[harsh temple]

https://youtu.be/Y9DzfPJsP2s omh this tutorial is so helpful

[torn sphinx]

Does anyone know how I can print a table for the data in my sqlite database?

Like this for example:

[modern flint]

You can try using module named "tabulate"

[harsh temple]

so you see that im trying to do that for every discord server, should i make an on_guild_join event and like it copys that again

[lusty tree]

Hey can someone recommend me a way to learn sql??

i tried mosh's tutorial on yt, but the sql file he linked in the desc doesn't even work, and i have no idea how to fix it

[austere portal]

#databases message

That's a good reasource

[lusty tree]

ty ill try that 😄

[austere portal]

[signal skiff]

hey can anyone help me out with a proper video about function fminuc used in advance optimization

[torn sphinx]

try #data-science-and-ml

[torn sphinx]

Hi

I have a .Db file.

How do u open it using myql command line

Am new to these stuff so am sry

[lone island]

SELECT * FROM gametb ORDER BY items -> 'bal' ->> 'money' DESC LIMIT 1;
Why is DESC not working on json types ?

[torn sphinx]

Try to do select on that value and then order by the column

Also you are creating more issues by storing in json. Why have a relational db just to store in json ?

[lone island]

it's not just json it's only a part of

and it should be on db due to reasons

[lone island]

Also not working

[lone island]

anyone?

[torn sphinx]

mysql> select browser from events;
+----------------------------------------------------------------------------+
| browser                                                                    |
+----------------------------------------------------------------------------+
| {"os": "Mac", "name": "Safari", "resolution": {"x": 1920, "y": 1080}}      |
| {"os": "Windows", "name": "Firefox", "resolution": {"x": 2560, "y": 1600}} |
| {"os": "Mac", "name": "Safari", "resolution": {"x": 1920, "y": 1080}}      |
| {"os": "Windows", "name": "Firefox", "resolution": {"x": 1600, "y": 900}}  |
| {"os": "Windows", "name": "Firefox", "resolution": {"x": 1280, "y": 800}}  |
| {"os": "Windows", "name": "Chrome", "resolution": {"x": 1680, "y": 1050}}  |
+----------------------------------------------------------------------------+
6 rows in set (0.00 sec)

mysql> SELECT browser ->> '$.os' as MY_ALIAS FROM events ORDER BY MY_ALIAS;
+----------+
| MY_ALIAS |
+----------+
| Mac      |
| Mac      |
| Windows  |
| Windows  |
| Windows  |
| Windows  |
+----------+
6 rows in set (0.00 sec)

try giving it an alias, and then ordering by that alias. worked for me

[lone island]

SELECT user_id, items -> 'bal' ->> 'money' as balance FROM gametb ORDER BY balance DESC;```

it's kinda randomized

DESC not effecting much

and my issue is with desc

[torn sphinx]

is this postgres?

[lone island]

ye

[torn sphinx]

oh

it's text

not a number

[lone island]

oh

[torn sphinx]

im not sure exactly how casting with json works but try like (items -> 'bal' ->> 'money')::int maybe

[lone island]

it's working

thanks

[pseudo flame]

# Database administrative login by Unix domain socket
local   all             postgres                                trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 trust
host    all             all             ::0/0                   md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5```
im trying to setup a postgres database that i can access remotely. i've port forwarded the port 5432, and the database works fine locally. above is my `pg_hba.conf` file. when i try to connect remotely using python and asyncpg i get:

ConnectionRefusedError: [WinError 1225] The remote computer refused the network connection```

never mind i figured it out

[torn sphinx]

if you dont mind im very curious what the solution was

[pseudo flame]

https://blog.logrocket.com/setting-up-a-remote-postgres-database-server-on-ubuntu-18-04/

i found this article

i forgot this step

you have to set it to this:

listen_addresses = '*'

[torn sphinx]

ah ya, thought it was something about unix sockets thanks for sharing

[pseudo flame]

alright

[simple kelp]

Does anyone know what I'm doing wrong here? (SQLite3)

[grim vault]

You're mixing column and fk definition, first do the column than the fk.

CREATE TABLE Inventory
(
  owner INTEGER,
  item TEXT,

  FOREIGN KEY (owner) REFERENCES Users(user_id),
  FOREIGN KEY (item) REFERENCES Items(name),
  PRIMARY KEY (owner, item)
);```
or
```sql
CREATE TABLE Inventory
(
  owner INTEGER REFERENCES Users(user_id),
  item TEXT REFERENCES Items(name),

  PRIMARY KEY (owner, item)
);```

[dry pond]

Hi I'm working with sqlite python and selected a string value which is a long date details ''Wed Apr 10 14:47:20 +0000 2013'

I want to convert it to datetime...or just fetch the date and time from that string

what format should I use?

[grim vault]

That's "%a %b %d %H:%M:%S %z %Y"

>>> import datetime
>>> datetime.datetime.strptime("Wed Apr 10 14:47:20 +0000 2013", "%a %b %d %H:%M:%S %z %Y")
datetime.datetime(2013, 4, 10, 14, 47, 20, tzinfo=datetime.timezone.utc)```

And row is a tuple you'll need to use row[0] and remove row = str(row)

[gusty mulch]

Hi I'm getting this error when I try to add info to one of my db tables https://sourceb.in/fCD0rv8btJ. The event triggering it looks like this https://sourceb.in/nSt4cyA17d and I'm accessing the db like this in my bot.py file https://sourceb.in/EP2zmPbuvd. The table I am trying to access uses a composite key created from the member and guild ID's

Edit: nvm I figured out how to fix it

[fading moss]

Hi, so I have the following code or line

cur.execute(f'''INSERT INTO GUILD_{ctx.guild.id} (user_id) VALUES ({x.id})''')
``` and i need it to ignore if the(user_id) already exists

[dry pond]

Thank you so much!

[dreamy coyote]

Hi

can someone explain each line of this

elif(ch==3):
FlightID=input("Enter FlightID:")
c=input("Are you sure you want to cancel this flight reservation? yes or no:")
if c=='y' or c=='Y':
mycursor.execute("delete from airline where FlightID='"+FlightID+"'")
print("Flight Reservation,"+FlightID+",deleted")
mydb.commit()
else:
break
else:
break
print("Thank you for choosing our company!")

[mortal light]

Using psycopg2. Is below the right way to format that would fetch the first row:

    def db_fetch_one(self) -> None:
        """
        Method fetches first row from database
        """
        query = """
                SELECT * FROM {}
                """
        query_with_table = sql.SQL(query).format(sql.Identifier(self.table_name))
        
        with self.connection.cursor() as cursor:
            cursor.execute(query_with_table)
            result = cursor.fetchone()
            return(result)

[north bronze]

if channel = 3 , then ask user to input the flight ID number. then ask user to input into variable c whether to cancel flight reservation, yes or no. if Y or y (yes) --mycursor.execute() executes the given database command or query which is to delete from airline where the FlightID matches the one provided by user, and commit changes, else if no or anything else break out the loop and print remaining print statement "thanks for choosing our company"

sounds about right anyhow, hehe

@dreamy coyote this is for code you've provided.

[mortal light]

I had previously written queries that used f-string someone here and also the docs discouraged the use of string concatenation to form a query.

[bright dune]

use parameterized queries, not string interpolation

[mortal light]

Sorry what do you mean by parameterize queries?

[bright dune]

they prevent sql injections

[mortal light]

Oh yes, I did that also, but for values, so

    def db_insert_one_test(self) -> None:
        """
        Method that inserts one row for testing
        """
        # {} is a placeholder for table_name
        # %s are placeholders for values being inserted 
        query = """
                INSERT INTO {}
                (column_foo, column_bar, column_date)
                VALUES (%s, %s, %s)
                """
        query_with_table = sql.SQL(query).format(sql.Identifier(self.table_name))

        column_foo = "This is Foo"
        column_bar = "This is Bar"
        column_date = 112233

        query_values = (column_foo, column_bar, column_date, )

        with self.connection.cursor() as cursor:
            cursor.execute(query_with_table, query_values)
            print("Successfully inserted data into table")

I dont think we can use %s for tables

So %s is called parameterized query

[mortal light]

Is it better to use context manager when you have multiple methods that are accessing the same table one at a time? Because with context manager it will open a session and then close it at the end of the method call. And when the program gets to next method it will open a new session and then close it.

As opposed to not using context manager it would keep the session open during all methods and then close the session once all the way at the end?

[torn sphinx]

Can someone please help me with something? I am trying to make a custom ID for each row in my sqlite table. This is so it will make it easier for me to delete them. I also want to write a function that can automatically update these ID's if I delete a row. For example, if there are 10 rows and I decide to delete row number 5, I don't want the order of the custom ID's to be (1, 2, 3, 4, 6, 7, 8, 9, 10). I want it to be (1, 2, 3, 4, 5, 6, 7, 8, 9). Does anyone know how I can do this?

[torn sphinx]

Can someone that knows sqlite please help me in #help-candy

[harsh pulsar]

this is a hard problem in general. you basically would have to rewrite the entire table in order to make this work. the best you can get is to use the built-in sqlite row id, which is a monotonically increasing integer, although they won't be consecutive if you delete or modify rows

[torn sphinx]

I tried using row ids, but it didn’t workout

[solar rose]

How inefficient is it to make a database connection every time I want to make a data query? I'm only grabbing a few lines of text, will there be any appreciable delay in my program

(ping me if you respond)

[torn sphinx]

from psycopg faqs:

When should I save and re-use a connection as opposed to creating a new one as needed?
Creating a connection can be slow (think of SSL over TCP) so the best practice is to create a single connection and keep it open as long as required. It is also good practice to rollback or commit frequently (even after a single SELECT statement) to make sure the backend is never left “idle in transaction”. See also psycopg2.pool for lightweight connection pooling.
https://www.psycopg.org/docs/faq.html

[torn sphinx]

Hello, I've never worked with databases and, I would like to learn SQlite3. Can you suggest a few websites where I can learn it? I've watched some tutorials. They've used slightly different syntaxes. Please ping me if you have any suggestions for me.

[delicate field]

@torn sphinx Per Rule 6, your invite link has been removed. If you believe this was a mistake, please let staff know!

Our server rules can be found here: https://pythondiscord.com/pages/rules

[analog sigil]

https://discord.gg/cEe4VkjN
check out the top links in #resources
@torn sphinx

your deleted message^

[analog sigil]

We've add the server to the whitelist. It will not get zapped by our bot anymore.

[dry pond]

Hi I wonder how to grab values starting with '#'?

[rough hearth]

You are more likely to get help if you copy and paste the text itself.

[dry pond]

sure

[grim vault]

I guess you're using mysql and backticks ` are for SQL identifiers, use single quotes: '

[dry pond]

Ahah I got it...had to use " " instead of ' or `

Thanks

[chilly creek]

I am super confused rn if MongoDB is ACID or BASE - they claim to be ACID and since versions 4.0 and 4.2 also for multi-document transactions (before only document wide) but many websites say that MongoDB is BASE.

[lusty tree]

db = sqlite3.connect('my_database.db')
cursor = db.cursor()
cursor.execute('''
    CREATE TABLE IF NOT EXISTS main(
    user_ign TEXT,
    user_id TEXT,
    user_elo TEXT,
    user_ovr TEXT
    )
    ''')

this is a thing for sqlite3 in python

but the thing is when i open the file, the only row that exists is user_ign

[copper axle]

So I have a question , in mysql I dont know how to create like a specific group of tables that fall under 'a' and then execute a code to get all the table names under 'a'

[copper axle]

hk = 'SELECT SUM(price) as total_expenditure FROM ' + TableName
mycursor.execute(hk)

Is this statement correct?

[faint blade]

Do you not know the tablename before?

What you're doing right now is susceptible to injection, what if someone makes TableName contain malicious text? You will execute that code

[torn sphinx]

im new to python

hi

/:

[copper axle]

I didnt get you

Im really new to python

In this under choice 3

I use the statement

But its running in an infinite loop for some reason

[faint blade]

Why do you ask for a specific table?

TableName = input("Enter Storage Table name: ") is the point that you can have several storage tables?

[copper axle]

Oh thats cause the store owner would be able to create his own storage table name

Just making it totally custamizable

[tidal urchin]

I am trying to make a user and pass manager so I have two inputs and it writes it into a microsoft access table, is this possible and anyone have a tutorial or know where I should start

[copper axle]

I actually figured out the answer

I have another doubt

In the code is it possible to find out like which id is selected the most?

[lusty tree]

Sqlite3 stuff:

db = sqlite3.connect('my_database.db')
cursor = db.cursor()
cursor.execute('''CREATE TABLE main
               (user_ign TEXT, user_id INT, user_elo INT, user_ovr INT)''')

cursor.execute("INSERT INTO main VALUES ('haha', 1, 1, 1)")

Code:

#Register
@client.command(pass_context=True)
async def register(ctx, member: discord.Member, *, nick):
    if member.id == ctx.author.id:
        await member.edit(nick='[' + str(elo_amt) + '] ' + nick)
        await ctx.send(f'You have registered as {member.mention} ')
        role = discord.utils.get(client.get_guild(ctx.guild.id).roles,
                                 id=882783428406284308)
        await member.add_roles(role)
        cursor.execute("INSERT INTO main (user_ign, user_id, user_elo, user_ovr) VALUES (?, ?, ?, ?)",(str(ctx), int(member.id), 0, 60))
    elif member.id != ctx.author.id:
        await ctx.send(f' Only register yourself!')

It doesn't add new columns and i don't understand why. the 'haha, 1, 1, 1' is there, but when i register, new info isn't put in. ping me if you reply

[austere portal]

You need to commit

[lusty tree]

i do

do i have to do it in the function?

[faint blade]

You can have a history table, this would save a history of IDs you have looked up. Then you can count all the amount of times someone has searched a specific ID, and find the most searched ones

Other than that, no

[austere portal]

And doesnt look like cursor is defined (in the 2nd code block)

[copper axle]

How would I create the table, im not familiar with it. Please do help me out

[austere portal]

You need to commit to save the changes

This method commits the current transaction. If you don’t call this method, anything you did since the last call to commit() is not visible from other database connections. If you wonder why you don’t see the data you’ve written to the database, please check you didn’t forget to call this method.

[lusty tree]

ahh

okay

[faint blade]

You can simply do something like this: ```sql
CREATE TABLE IF NOT EXISTS history(
searched INT
);

Then for each search insert an history ```sql
INSERT INTO history (searched) VALUES (%s);

And query for it like this: ```sql
SELECT COUNT(*) FROM history ORDER BY COUNT(*) DESC;

[faint blade]

Well free to replace INT with whatever type the ID actually is

[woven bough]

use db.commit() after cursor.execute()

[copper axle]

Where will I add this code?

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

[faint blade]

The first one creates the table, where do you create the other tables?

[lusty tree]

no worries i did it and it worked!!

[copper axle]

At the start of the code

Got it

[woven bough]

noice

[copper axle]

And the other statements?

@faint blade Is it okay if I dm you personally?

[tidal urchin]

I am trying to make a user and pass manager so I have two inputs and it writes it into a microsoft access table, is this possible and anyone have a tutorial or know where I should start

[faint blade]

Those are examples of queries, INSERT means we add a row to the table. So whenever someone tries to search an item you want to insert history. The SELECT query will find the most searched one

[copper axle]

So this should be a global function?

Im really new to this

[faint blade]

If you want it to, these are queries. So the code that you give to mycursor.execute()

That's SQL

I don't think you're gonna be repeating this code a lot, so there's probably no need

[copper axle]

Oh okay

Is it okay if i can personally dm you?

[faint blade]

No, we can use this channel

[copper axle]

Sure!

There is an error

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

Is the placement wrong?

[woven bough]

you must pass the data type after the name of the column

and no spaces between the words

ex searched_emno VARCHAR(20)

[copper axle]

Then how would specify the id I want to keep track of

[woven bough]

u can write as

mycursor.execute("""
  create table if not exists history (
    id int,
    searched_emno datatypehere,
    other_column_name datatype
  );
""")

spliting them by ,

[lusty tree]

@client.command(pass_context=True)
async def give_elo(ctx, member: discord.Member, elo_giving_amt):
    elo_giving_amt = int(elo_giving_amt)
    cursor.execute("SELECT user_elo FROM main WHERE user_id = (?)", (member.id,)
    user_elo = 
    cursor.execute("UPDATE main SET user_elo = (?) WHERE user_id = (?)", (int(user_elo) + elo_giving_amt, member.id))
    db.commit()

how can i make it so that user elo is the executed thing? fetchall didn't seem to work?

[copper axle]

Im still getting an error

I really need help with this

[woven bough]

send entire error

plz

[copper axle]

Sure

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

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

[woven bough]

mycursor.execute("INSERT INTO history (searched_empno) VALUES (%s);")

pass the values to execute

mycursor.execute("INSERT INTO history (searched_empno) VALUES (?);", (value,))

dont use %s if u dont pass any value

[copper axle]

Value in this case would be the id right?

[woven bough]

its depends

should be?

[long dome]

question, I'm using POSTGRESQL database in localhost in a VPS, is there a way for me to connect to that localhost Database from another ip/computer?

[copper axle]

i didnt get you

[woven bough]

@copper axle dude, you copy my code, was an example

[copper axle]

Ohhh

Alirght one sec

But it still should give the value right?

[woven bough]

u must pass the values according to the structure

[lusty tree]

hey znairy could u help me after u help this guy?

[woven bough]

(id, searched_empno) = (1, "anystuff")

[woven bough]

i hope so

[copper axle]

Yess that error went, now I have to define value

So value would be the id i want to track

Right?

[woven bough]

yep, when you want to search it

[woven bough]

send your question

[lusty tree]

gimme one sec i might've solved it

[woven bough]

no prob

[copper axle]

Heyy, im really sorry to bother you but in the code it says that I havent defined the id that I want to search that is empno but I have

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

[woven bough]

if you create the table like

mycursor.execute("""
  create table if not exists history (
    id int AUTO_INCREMENT,
    searched_emno datatypehere,
    other_column_name datatype
  );
""")

when you go to insert data, it increments 1 by 1 in id
then u can write mycursor.execute("INSERT INTO history (searched_empno) VALUES (?,?);", (None, value,))

None = default value in id

dude, i need to leave now

sy

[copper axle]

Sure no issues

[woven bough]

see ya, i hope u can fix these probs

[tidal urchin]

I am trying to make a user and pass manager so I have two inputs and it writes it into a microsoft access table, is this possible and anyone have a tutorial or know where I should start

[torn sphinx]

hi

im new to sqlite, can you force set the TEXT?

[ivory igloo]

my program saves json stuff in just one line, is it possible to make it better to look at without doing it manually everytime it is changed (because it will happen all the time)

[indigo flare]

why are you saving stuff to a json file, in general

much better to actually use a database for variable data

[modest needle]

Hello guys, I'm trying to build a counting program for a handheld device running Windows CE. I'm wondering if there's an API that can lookup into a sort of barcode database to get the product details (UPC, EAN,...) I tried with some but they were very limited, do you guys have any recommendations?

[ivory igloo]

figured it would be the easiest and an "actual" database would be too much
well i got the problem sorted out anyway

[indigo flare]

sqllite

is a good, local, file based database you can use instead of json

where you can actually query stuff and design tables

[mystic trout]

Command raised an exception: OperationalError: near "<": syntax error

CODE:

sql_test = '''CREATE TABLE {}(password)'''.format(us)

        cur.execute(sql_test)

WHY? and how correct

Command raised an exception: OperationalError: near "<": syntax error

CODE:

sql_test = '''CREATE TABLE {}(password)'''.format(us)

        cur.execute(sql_test)

WHY? and how correct

[austere portal]

what is the value of us?

[mystic trout]

us = str(username)

[austere portal]

what is username?

and why are you creating a table for each username?

[mystic trout]

@commands.command()
    @commands.cooldown(1, 3, commands.BucketType.user)
    async def create_mods(self, username, password):
        us = str(username)
        pass1 = str(password)
        conn = sqlite3.connect('moderators_check.db')
        cur = conn.cursor()

[austere portal]

why are you creating a table for each username though?

[mystic trout]

Need

[indigo flare]

no you don't