databases

[signal cloak]

collection.update({"_id": user_id, "notes.id": note_id},
    {"$pull": {"notes."})```

smt like that

[dull cypress]

you need to read the original data, remove that one object and write it back to the db

so you'll be using the equivalent of "read" and "upsert"

[signal cloak]

but how do I get the notes[0 ] for example

or i need a different structure

[dull cypress]

use python's dict syntax

look up how to remove an element by index

[solid tundra]

https://paste.ofcode.org/zgHbHvN5FxiSTyfYyNck99

For some reason the first if statement is not working. I've checked the DB and there is no Channel_ID. I've also checked if the embed is correct and there are no errors and it works fine. Does anyone know what am I doing wrong?

[vivid mesa]

im getting this error when i try to insert data into mongoDB

An existing connection was forcibly closed by the remote host,connection closed,connection closed, Timeout: 30s

why tho

[simple kelp]

I've got a working Discord bot which has a config.json file used to store all configuration/settings. To change a setting, for example the prefix, you can either edit the file's prefix value or use a command through Discord. However, this setup feels a bit janky - here's issues I'm worried about so far:

• The entire file is assigned to an object when loading the config, which I think is bad practice?
• The entire file is overwritten for each edit made from Discord, since it's just the object being dumped
• There's no way to add comments to the JSON because they just get overwritten on dump
• I'm really struggling to find a way to have independant configuration settings for different servers.

Any tips/recommendations? Thanks in advance.

After looking for some help with the above, I've been recommended to try out some database stuff, but I have no idea where to begin. Could someone give me a couple pointers please?

[near shuttle]

i have alot of csv files with lines of data in it, which need to be read and re-read sometimes and then some math on them... Would it be faster to use mysql to store all the data in a database and then run over it? espacially if i need multiple lines from different time periods? feel like all this casting stuff from files to data and back into files is kinda slow.

[burnt turret]

A database is definitely what you should be using. Generally the data you'd be storing for a discord bot would be relational (i.e could easily be put into tables) so a relational database like PostgreSQL or MySQL will work very well for this use case. SQLite is also another popular choice as it is rather easy to set up (it's a file based database)
Although if you are already using JSON, it could be easier to switch to MongoDB, as it stores data in basically JSON format, but I'd still prefer one of the SQL databases

Whichever database you use, you should be careful to be using the async library for interacting with the database; for example, if you used SQLite, you'd use the aiosqlite module, for postgresql that'd be asyncpg

If you were to use one of the relational databases I recommended, you'd have to learn SQL (structured query language) which is the language you'd use to query the data. https://sqlbolt.com is an excellent resource for learning SQL

[simple kelp]

Ok, thank you. So, as far as I understand, the different names/modules are different data storage methods, but they all use the same language called SQL?

Or are they more like different variants of SQL?

[burnt turret]

they are all different databases, and they all can be queried using the same language SQL

although each database has some minor quirks of SQL used, in general if you know SQL you should be able to use any of them

[simple kelp]

I see, thank you.

[tranquil totem]

getting this error after switching to motor '_'

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: '_asyncio.Future' object is not subscriptable

@burnt turret this happened when i switched to motor using your method

[burnt turret]

Show your code

most likely you haven't awaited something that needs to be awaited

[tranquil totem]

heres the full error:```py
Ignoring exception in on_message
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/CaliberBot/cogs/settings.py", line 63, in disable
prefix = await collection.find_one({"_id": ctx.guild.id})["prefix"]
TypeError: '_asyncio.Future' object is not subscriptable

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/core.py", line 422, in dispatch_error
await injected(cog, ctx, error)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 71, in wrapped
ret = await coro(*args, **kwargs)
File "/home/runner/CaliberBot/cogs/settings.py", line 140, in disable_error
raise error
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 902, in invoke
await ctx.command.invoke(ctx)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 864, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 94, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: '_asyncio.Future' object is not subscriptable

[burnt turret]

just wrap the query in parentheses

[tranquil totem]

and main.py```py
import discord
import datetime
from discord.ext import commands
import os
import random
import re
import cogs
import asyncio
intents = discord.Intents(messages=True, guilds=True, reactions=True, members=True)
import pymongo
from pymongo import MongoClient
import motor.motor_asyncio as motor
from datetime import datetime

client = motor.AsyncIOMotorClient(os.environ["DB_login"])
db = client.discord_database
collection = db["server_data"]
async def server_prefix(bot, message):
prefix = await collection.find_one( { "_id": message.guild.id } )
return commands.when_mentioned_or(str(prefix["prefix"]))(bot, message)

client = commands.Bot(command_prefix=server_prefix, activity=discord.Game (name="!help"), help_command=None, case_insensitive=True, intents=intents)

[burnt turret]

like (await collection.find_one({"_id": ctx.guild.id}))["prefix"]

[tranquil totem]

hmmm

yeah but i now have to wrap it up everywhere

you could add it in your procedure in one of the steps

to add parenthesis, or it wont work

[burnt turret]

no, only in the places where you're doing stuff like...that where you directly try accessing the data in the same line

[tranquil totem]

oh

[burnt turret]

no that is something that you should be thinking about yourself?

[tranquil totem]

its also the case in normal if statements

[burnt turret]

it is not something motor specific

[tranquil totem]

hmmmmmmmmm

my bad

fair point

[burnt turret]

you understand what is happening right?

[tranquil totem]

where do i add the parenthesis? while defining the variable??

[burnt turret]

the order of the operations happening is what's relevent here

[tranquil totem]

somewhat, yes

[burnt turret]

when you do
await collection.find_one({"_id": ctx.guild.id})["prefix"] it is trying to get the key prefix from the function itself, before awaiting it but that's not what we want; we want to access the data from the result of the function
so by doing (await collection.find_one({"_id": ctx.guild.id}))["prefix"], the precedence is clear: do the function first, and then access the data

if you were assigning it to a variable it'd just become two line

var = await collection.find_one({"_id": ctx.guild.id})
var["prefix"]  # do stuff with this

[tranquil totem]

ohhhhhhh

i get it

so even py var = (await collection.find_one({"_id": ctx.guild.id}))["prefix"] would work right

[burnt turret]

yeah

[tranquil totem]

alright thanks

[tranquil totem]

whats this error now```py
Ignoring exception in command deny:
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/CaliberBot/cogs/suggest.py", line 70, in deny
suggestion_channel = self.bot.get_channel(await collection.find_one({"_id": ctx.guild.id}))["suggestion_channel"]
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/client.py", line 790, in get_channel
return self._connection.get_channel(id)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/state.py", line 1111, in get_channel
pm = self._get_private_channel(id)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/state.py", line 329, in _get_private_channel
value = self._private_channels[channel_id]
TypeError: unhashable type: 'dict'

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

Traceback (most recent call last):
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 902, in invoke
await ctx.command.invoke(ctx)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 864, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/ext/commands/core.py", line 94, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: unhashable type: 'dict'

where did dict come from

code:```py
@commands.command()
async def deny(self, ctx, msg_id, *, reason = None):
# channel = await collection.find_one({"_id": ctx.guild.id})
# s_channel = channel["suggestion_channel"]
# suggestion_channel = self.bot.get_channel(s_channel)

    suggestion_channel = self.bot.get_channel(await collection.find_one({"_id": ctx.guild.id}))["suggestion_channel"]

    message = await suggestion_channel.fetch_message(msg_id)
    embed = message.embeds[0]

    if embed.title != "Suggestion:":
        embed = discord.Embed(description = "This suggestion was already approved/denied", color = 0xf35353)
        await ctx.send(embed = embed)
    else:
        embed2 = embed.copy()
        embed2.title = "Suggestion Denied"
        embed2.add_field(name = "Denied by:", value = ctx.author, inline = False)
        embed2.add_field(name = "Reason:", value = reason, inline = False)
        embed2.color = 0xf35353
        await message.edit(embed=embed2)
        user_id = (message.embeds[0].footer.text[9:27])
        user = await self.bot.fetch_user(int(user_id))
        await user.send(f"Hey **{user}**, unfortunately your suggestion on **{ctx.guild}**, {embed2.description}, was denied due to the following reason:\n{reason}")
        embed3 = discord.Embed(description = "Suggestion denied", color = 0x43b581)
        await ctx.send(embed=embed3, delete_after = 6)
        await asyncio.sleep(5)
        await ctx.message.delete()

what seems to be the problem here

[burnt turret]

you're passing in the entire dictionary from the database to get_channel on this line

suggestion_channel = self.bot.get_channel(await collection.find_one({"_id": ctx.guild.id}))["suggestion_channel"]

you probably meant to do
suggestion_channel = self.bot.get_channel(await collection.find_one({"_id": ctx.guild.id})["suggestion_channel"])

[tranquil totem]

i think

but didnt you just say that the await has to be in ()

oh right my bad

theres self.bot.get_channel()

[keen gorge]

So this is not just a question for python and databases but for databases in general. I'm using Mysql for my bots database and now heard that this is a relational database. So like connections between different tables. What is this good for in a bot for example? I'm a bit newer to this I'm sorry for this dumb question

I also have a diagram

[burnt turret]

look into foreign keys; in your case gid would be an excellent foreign key i think by relating together data of the same guild across multiple tables

[keen gorge]

so like to connect all gid's

[versed geode]

do y'all use naked sql or sqlalchemy? i'm happy with naked sql since sqlalchemy is too confusing for me, but is there a reason to switch?

[proven arrow]

Depends if you need the benefits of an orm. And other factors like scope of project, who else is working on the project etc.

[shy shuttle]

using raw sql will generally leave you open to sql injection since it probably means you are not sanitizing your inputs before sending them to the database

[proven arrow]

What?

[versed geode]

what if the inputs pretty much always come from a trusted source and also i have heavy validation on the public inputs?

[proven arrow]

Sql injection is with how one writes queries. Everything uses raw sql in the end whether you use an ORM or not

[versed geode]

not even - or = is allowed

[shy shuttle]

right..... sql alchemy at least allows for proper parameterization of queries as a safeguard. that lkike saying "everything is 1's and 0's so why bother with abstractions like programming languages"

[proven arrow]

Well no you said raw Sql leaves you open to sql injection, which is not true.

And that comparison is not like what I said. 😂

You can still have parameterised queries if you want to write queries your self. And that’s how it’s supposed to be done.

[shy shuttle]

it's almost never good practice to send raw user input to the database, but its up to you. If you are sure you completely trust whatever it is that is processing that input, go for it. but unless it was built specifically to deal with dataqbase input, it might not be adequate.

[versed geode]

so should i change this?: i ask for user input, reject if it has anything other than alphanumeric characters, then pass the validated input into a fstring

[shy shuttle]

im not saying "OMG GUYZ ORM IS SO COOL" I hate ORM's, but you don't have to use it to be able to take advantage of some level of input sanitizing.

[proven arrow]

Just use parameterised query man. Like I said to you already. No Sql injection can happen like that.

[versed geode]

alright, thanks

is that the same as the ? statements?

[shy shuttle]

we are saying the same thing, i think

[proven arrow]

Yes, explained here. #databases message

[shy shuttle]

dont do this:

val = input("GIVE ME THE INPUTZ: ")
db.execute(SELECT * FROM TABLE WHERE X=" + val)

[versed geode]

alright i'll try to switch. thanks

[solid tundra]

Anyone can help here?

[proven arrow]

What is the value of channel_result?

print the value before the if statment and check

[solid tundra]

Well it should be Null and it should also print None. I'll test it out later when I re-open my pc.

[proven arrow]

Also you might want to share your table structure/how you insert data.

[torn sphinx]

import sqlite3 

conn = sqlite3.connect("TestDataBase.db") 

cursor = conn.cursor() 

cursor.execute("""CREATE TABLE IF NOT EXISTS Students( 

studentId INT PRIMARY KEY, 

name TEXT, 

age INT, 

grade INT); 

""") 

conn.commit()

Is there a way i can make somehow an input so it inserts in database

[proven arrow]

Input from where?

[torn sphinx]

Is there a way so when i run the code i can type student id name age and grade

[proven arrow]

If you want it from the console just use the input() function

x = input()

[torn sphinx]

I dont know how to dou it with mysql

[proven arrow]

Ah

It’ll be the same execute function you use.

[torn sphinx]

It would be like a=input(grade)

[proven arrow]

id = input(“enter your id”)
....
cursor.execute(“insert into Students (studentId, name, age, grade) VALUES (?,?,?,?)”, (id, name, age, grade))

Do the same input function for all variables

[torn sphinx]

Oh thx

Txys

[obtuse glacier]

so i have at table; guild_id, user_id, exp, lvl

i need to find what the users level is in the guild, and if they have the same level then to rank them by highest exp for that level

but everything ive tried everyone just gets rank 1

[shell ocean]

order by lvl and then by exp

[obtuse glacier]

exp ascending or descending

[mystic pasture]

#user-interfaces message need help for a database GUI please 😄

[shell ocean]

what do you think?

[obtuse glacier]

well whoevers exp is higher theyll get the higher rank so probably descending

Order by lvl desc and exp asc

Would that be right

[torn sphinx]

hi

can someone tell me what this means

[stray fulcrum]

is using something like this bad?

[torn sphinx]

whats the point of closing the db if you need it to keep running whilst u have ur program running

[stray fulcrum]

I was told it should be closed instead of keeping it open, not sure what the effects are though

[torn sphinx]

async def db_database():
    global connection
    connection = await asqlite.connect("database.db")
    global cursor 
    cursor = await connection.cursor() 
client.loop.run_until_complete(db_database())  

if ur gonna use sqlite use that ^

[stray fulcrum]

! thanks

[torn sphinx]

ofc

[austere portal]

How can I search my postgres database if a row contains a given string?

These are the rows id | name 1 | "bread" 2 | "noodles" 3 | "buns" 4 | "biscuits" so if i search the letter 'b' the query should return the rows where the name contains 'b'
(which is row 1, 3, 4)

[hazy dew]

hello, i am getting this error, asyncpg.exceptions.InvalidPasswordError: password authentication failed for user "postgres" even though i am entering the correct password

[formal shell]

query = """INSERT INTO members (member_id, counting_points)
           VALUES ($1, $2) ON CONFLICT (member_id)
           DO UPDATE SET
                counting_points = counting_points + 1;
        """

Why this query returns error?

Error:

asyncpg.exceptions.AmbiguousColumnError: column reference "counting_points" is ambiguous

[hazy dew]

ConnectionRefusedError: [Errno 111] Connect call failed ('127.0.0.1', 5432) can someone help i am using postgresql

[torn sphinx]

Is this correct?

@bot.command()
@commands.has_permissions(administrator = True)
async def colour(ctx, colour):
    db = sqlite3.connect('embed_colour.sqlite')
    cursor = embed_colour.cursor()
    cursor.execute("SELECT colour FROM embed_colour WHERE guild_id = {ctx.guild.id}")
    result = cursor.fetchone()
    if result is None:
        sql = ("INSERT INTO embed_colour(guild_id, colour) VALUES(?,?)")
        val = (ctx.guild.id, 0xFFD700)
        await ctx.send(f"The embed colour has successfully been set to {colour}")
    elif result is not None:
        sql = ("UPDATE embed_colour SET channel_id = ? WHERE guild_id = ?")
        val = ( 0xFFD700, ctx.guild.id)
        await ctx.send(f"The embed colour has successfully been updated to {colour}")
    cursor.execute(sql, val)
    db.commit()
    cursor.close()
    db.close()

[royal pebble]

Don't use what (in kaih we trust) sent in production. Avoid global variables as much as possible. You should be closing the connection like before. I would create a class to hold the db instance with both the method to open and close the DB. Even if you're using async you don't need global variables just create a class or find another way to handle the db instance. Not with global variables.

[royal pebble]

Creating a pymongo DB instance and opening a collection.

[indigo flare]

trying to run the query/command ```sql
CREATE TABLE guilds (
guild_id bigint PRIMARY KEY,
logs_channel bigint NOT NULL
);
CREATE TABLE relationships (
linker bigint UNIQUE,
linkee bigint UNIQUE,

FOREIGN KEY (guilds)
    REFERENCES (guilds)
    ON DELETE CASCADE

);``` seems to give the error asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "(" and I can't figure out why (new to foreign keys)

wait, figured it out, the correct syntax was ```sql
CREATE TABLE guilds (
guild_id bigint PRIMARY KEY,
logs_channel bigint NOT NULL
);
CREATE TABLE relationships (
linker bigint UNIQUE NOT NULL,
linkee bigint UNIQUE NOT NULL,

FOREIGN KEY (linker)
    REFERENCES guilds
    ON DELETE CASCADE,
FOREIGN KEY (linkee)
    REFERENCES guilds
    ON DELETE CASCADE

);```

[sharp vault]

Thought maybe someone here could help, does anyone understand how to read from databases using PHP ? because ive been stuck for a while

either way ill chuck this here if anyone could help it would be greatly appreciated :

<?php
session_start();

    include("connection.php");
    include("functions.php");

    if($_SERVER['REQUEST_METHOD'] == "POST")
    {
        $u_username = $_POST['user_username'];
        $u_password = $_POST['user_password'];
        
        if(!empty($u_username) && !empty($u_password) && !is_numeric($u_username))
        {
            //if the input fields arent empty + username isnt a number
            
            //read from database

            $query = "select * from users where user_username = '$u_username' limit 1";

            $result = mysqli_query($con, $query);

            if($result){

                if($result && mysqli_num_rows($result) > 0)
                {
        
                    $user_data = mysqli_fetch_assoc($result);
                    
                    if($user_data['u_password'] === $u_password){

                        $_SESSION['user_userid'] = $user_data['user_id'];
                        header("Location: Websiteattempt (1).php");
                        die;
                    }
                }
            }
            echo "Wrong username or password!";
        }else
        {
            echo "Please enter some valid information";
        }
    }
?>

entering correct/incorrect information results in the same "Please enter some valid info" message

my variables are :
username entered by user into login = u_username
pass entered by user into login = u_password

username in database = user_username
password in database = user_password

im aware its a mess right now ive tried alot

[burnt turret]

you'd probably have better luck with that in some PHP server

[tame nova]

why?

[burnt turret]

what happens if you pass them all with parameters?

await self.bot.db.execute("INSERT INTO guilds(id, prefix, raid) VALUES($1, $2, $3)", guild.id, "k-", "f")

[tame nova]

hmm how would that work

I'm a bit new in postgres

like guild is the table
I'd prefix raid are the columns

[burnt turret]

I'm not exactly sure why your query didn't work but I figured it had some issue with the quotes and such; so passing them also as parameters would fix the issue if that was the case

[tame nova]

and you provided indexes for each value for the column

[burnt turret]

all I changed was the way you were passing in the values

[tame nova]

oh hmm

[burnt turret]

instead of adding the values for prefix and raid in the query itself as you had, I'm passing it in separately

[tame nova]

ahhh

[proven arrow]

Double quotes are for identifiers so remove them from the query

Single quotes for string

[burnt turret]

right that makes sense

[tame nova]

ah yes

[burnt turret]

i only remember mysql letting me use either :p

[tame nova]

sorry about the mistakes I'm new to postgresql lol

so

await self.bot.db.execute("INSERT INTO guilds(id, prefix, raid) VALUES($1, $2, $3)", guild.id, 'k-', 'f') ?```

[proven arrow]

Now it doesn’t matter if you pass it through the parameters, with single or double quote s

I was referring to your initial query/error

[tame nova]

ah okay

thank you it works 🙂

[tame nova]

but what was the issue when I wanted to add the values

[burnt turret]

read what Lufthansa-Pilot said

Double quotes are for identifiers so remove them from the query
Single quotes for string

[tame nova]

wait uh

I'm confused

[burnt turret]

writing the query as "INSERT INTO guilds(id, prefix, raid) VALUES($1, 'k-', 'f')"

[tame nova]

ohhhh

[burnt turret]

using single quotes for the values instead of double quotes

[tame nova]

I did the opposite

so k and f were colums

and that's it was raising an error

right.

?

ah thanks now I understood lol

[dull scarab]

If i have a transactions table, and an account table, does it make sense to have the total sum/amount per account be stored in the account table (which does require 2 database queries on every transaction, to insert a new transaction, and update the account sum), or just SUM the transaction table everytime i'm asking for the sum+

[torn sphinx]

Hey so, how do I creat data bases for python

Like for storing data whic are numbers and letters

I just need help with basics

Ping me if you can help

[proven arrow]

The rules of database normalisation would say not to store the balance in the accounts table.
However in this case it makes sense to store the balance in the accounts table, mostly because of how many times you need it. It would be better for performance.

Consider the transactions table as what has happened in the past, like logs.

[proven arrow]

Can you be more specific? What are you working on? When do you store this data? What do you want to do with this data?

[torn sphinx]

I already got help, so cya

Thanks for helping

.close

L that dosent work

[drifting axle]

Hey, I'm creating a db schema to store data about web pages. I want to store the page url, html, metadata and its text content. Since the metadata (title, meta description etc.) and text are retrieved from the page's html, am I better breaking these attributes into their own table to preserve 3rd normal form?

Pages
  url (VARCHAR)
  html (TEXT)
  text (TEXT)
  title (VARCHAR)
  meta_description (TEXT)

Goes to

  Pages
    url (VARCHAR)
    html_id (FOREIGN KEY)

  Html
    html (TEXT)
    text (TEXT)
    title (VARCHAR)
    meta_description (TEXT)

[austere portal]

Thanks

[proven arrow]

You can use the LIKE operator

https://www.postgresql.org/docs/13/functions-matching.html#FUNCTIONS-LIKE

[austere portal]

Thanks

So the query should be SELECT Name FROM <table> WHERE Name LIKE '_b_'

[proven arrow]

No

If you see it says: “An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters.”

[austere portal]

Oh, Thank you very much

[stone canopy]

I have been working with an sqllite3 database. but every once in a while the database will roll back to a radnom past point. Has anyone experienced this problem before and is there a solution.

[stray fulcrum]

Is there a way to set a variable in a dictionary without making it a string?

[obtuse glacier]

Wrong channel my bad

[wicked kiln]

Is it safe to try an sql INSERT and use sqlite3.IntegrityError to handle a situation where you would not make a record in the db because that record already exists (having failed unique constraint) .
or could I potentially be using a too general error to handle that specific issue by doing that

Is it better to query the database for the record with the unique field first and then use that response to determine if you should run the INSERT sql at all.

I like the first idea better if its safe.

[hasty creek]

♾️

[torn sphinx]

can anyone tell me how to create php or other but public webserver

[torn sphinx]

can anyone help me pls

[dull scarab]

I mean, yeah. But I would have to make 2 queries per transaction update then to make sure the account amount is correct.

Sub-question, I'm trying to create an account if it does not exist when inserting a transaction. Using postgres, how would I approach this?

INSERT INTO Transactions(account_id, amount)
VALUES (
    INSERT INTO Accounts(account_id, account_name) 
        VALUES ($1, 'Balance account')
        WHERE NOT EXISTS (
            SELECT account_id from Accounts WHERE account_id = $1
        )
        RETURNING account_id,
    $2
)
RETURNING transaction_id;```

[torn sphinx]

can anyone help me pls

[dull scarab]

You haven't asked anything, not provided any info besides a red line

[rain plank]

!code

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

[naive furnace]

If i have these tables:

Person_ID Name Surname   Profession_ID

Profession_ID Profession

and I create a View with these fields

Name Surname Profession

Can i insert data into this view? Im trying to make a React Express + MySQL site. I made it so that CRUD works for the tables if they arent combined into a view, but I cant get CRUD to work for the view..

Pls @ me if u have advice/tips

[proven arrow]

That depends how you made the view

You might have to make separate inserts based on the join type you have, if you do have a join.
You can see the list of conditions here where you can/can’t update it https://dev.mysql.com/doc/refman/8.0/en/view-updatability.html

[proven arrow]

You can’t insert like that. But you can use a CTE to do so. However, I think this is just over complicating a simple thing. It’ll be easier/ more readable if you just insert the transaction, and catch any integrity errors; and if there are then you can create the account/transaction.

[dull scarab]

I didn't think about catching that, but idk. I ended up with

WITH ensure_account AS (
    INSERT INTO Accounts(account_id, account_name)
    VALUES ($1, 'balance account')
    ON CONFLICT DO NOTHING
)
INSERT INTO Transactions(account_id, amount)
VALUES ($1, $2)
RETURNING transaction_id```

Which i'm not super against tbh

[proven arrow]

I’m not sure what kind of system/domain your working on, and so if you think it’s suitable in your case then sure use it.

[indigo flare]

how do you have multiple ON CONFLICT statements per INSERT? I tried sql INSERT INTO guilds(guild_id, logs_channel) VALUES ($1, $2), ($3, $4) ON CONFLICT (guild_id) DO NOTHING, ON CONFLICT (logs_channel) DO UPDATE SET logs_channel = EXCLUDED.logs_channel; but like, that just errors with PostgresSyntaxError: syntax error at or near ","

and without the , it errors with PostgresSyntaxError: syntax error at or near "ON"

[untold quartz]

do I capitalize data types such as int and varchar? should I write INT and VARCHAR?

[worldly pond]

i am making and ticket bot but i need help with the data currently i am working on the ticket reopen idk how i can get that channel ticket user it
this is now i am storing the data

ticket_id is ticket channel id

[untold quartz]

do I need semicolons after my SQL statements?

[brazen charm]

usually no, if you want to run several queries at once though then yes

[torn sphinx]

What is the best way to learn how to use python with postgresql in your opinion?
I usually learn through videos, btw.
I have close to zero knowledge on databases. I know python fundamentals.

I do know about injections but that's about it. btw

[proven arrow]

Either works. There’s no standard, use what works for you

[proven arrow]

Not possible

[proven arrow]

Python with Postgres is not much work. There’s only few functions that you need, to know about. Most of the work is done through the sql query.

[indigo flare]

Fuck.... Is there a way to do something similar to what I am doing there or will it take 2 inserts in a transaction

[torn sphinx]

even if I don't know SQL?

[proven arrow]

Just have a single on conflict where you only update the log channel.

[indigo flare]

Then catch the error from asyncpg if there is an error for the guilds, okay

[proven arrow]

You need to know Sql, if you want to write sql queries. However, an alternative is to use an ORM, with which you write queries using python (the orm will translate the python to Sql query for you).

But sql is not much work to learn the basics. It’s very readable, and high level, so you should give it a go.

[torn sphinx]

so basically, I need to learn SQL first then move on to postgresql?

[proven arrow]

Because whichever approach you take it’s important to know how things work under the hood.

[proven arrow]

Postgres is one of the many databases that uses the SQL language. But yes in my opinion you should try to learn the basics of Sql.

[limpid nexus]

Guys, I currently have server using sqlite as its database.
I store a lot of images in base64 (around 500k images right now) and I need something that would handle those a little bit faster, without errors
Which database should I choose?

[proven arrow]

Handle faster for what?

Where is the bottleneck now? Writing or reading.

[limpid nexus]

honestly, right now there is no bottleneck

[proven arrow]

Also I’ve never really heard people use base64 to store images.

Generally they use BLOB type

[limpid nexus]

I am getting base64 images to my API, thats why I store them this way

would it be better to store them as BLOB?

[proven arrow]

Right well, any server based database MySQL/Postgres would be faster.

Pick one your familiar with or your team members know.

[proven arrow]

Blob would be more efficient for storage and the database would probably optimize it better.

[limpid nexus]

Also, when using my database for reading and writing at the same time, I sometimes get weird error (something like: database disk image is malformed)
Situation looks like that:
I have 2 python servers running, one for monitoring numbers of items etc. (reading) and the other one for API functioning (writing)
When I want to read A LOT of items, this error occurs.
Then, both servers crash and I have to manually restart them
What could be the cause?

I suspect that while reading, something is being put into database, and then it crashes, but no idea how to deal with it

[proven arrow]

Unsure, I never used SQLite for anything production. Probably something is corrupting it.

It will have locks in place when your reading writing together so I doubt it’s that.

[limpid nexus]

what could corrupt the database?

I use sqlalchemy with model validation

• my models are really really simple (like each item has 2 ints and 2 strings, and it is always correct)
• for reading I am also using simple queries with sqlalchemy (like

db.query(models.Item).offset(skip).limit(limit).all()

and I am using non-async database connection in my server (if that changes anything)

[proven arrow]

As I said unsure. Maybe hardware or file system issue.

[limpid nexus]

okay, thank you very much for whole help ❤️

[torn sphinx]

I'm looking to tune PostgreSQL as I run out of connections a lot

Can anyone point me in the right direction?

[autumn epoch]

How would I copy a postgres database with all of its data from one linux server to another?

[torn sphinx]

pg_dumpall > backup

psql -f backup postgres

[autumn epoch]

K

[foggy iron]

if u are a expert about pandas help me in #help-ramen pls
its a really hard problem

[shell ocean]

stop spamming your help channel please

[delicate field]

Hey @fallen scaffold!

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

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

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

https://paste.pythondiscord.com

[naive furnace]

Hey
so like instead of doing these 2 seperate operations

INSERT INTO Person (Name,Surname,Job_ID) VALUES (John,Peterson,1)
INSERT INTO Job (Job) VALUES Teacher

I wanna do

INSERT INTO Person (Name,Surname,Job) VALUES (John,Peterson,Teacher)

is that possible?

[grave eagle]

hi!, I am doing a discord bot, and i need to use a database to store infos like lvls banlists and things like settings per guild, i'm trying sqlite but i think it isnt the best choise, does someone has a suggestion, and/or a "simple" way to access to databases without getting "locked" out?

[civic schooner]

what is the point with databases

?

Am I able to link a login system I made to SQL databases?

[proven arrow]

Of course. Does your login system not store any data?

[civic schooner]

it stores data in a flat file

but I want it to store in a database

[proven arrow]

Ok

So what’s stopping you?

[civic schooner]

how do I do this`?

[proven arrow]

Well which database do you want to use? Do you know how to write simple queries?

[civic schooner]

I don't know

I don't know anything

I don't know why it is even more than one

[civic schooner]

this is so annoying

[proven arrow]

What?

Anyways I got a team meeting, be back later

[past aspen]

        val = pocket_data + random.randint(0,500)```

TypeError: unsupported operand type(s) for +: 'int' and 'str'

^ should i convert it into a float and do it then?

[civic schooner]

why did you redirect me here lilboy

[hexed tartan]

hello

i have exe

and i want to sell it

okay

and i want to add login

With token

and i want to control the account to delete account and add account

[foggy iron]

i have one pkl file like the img
i'm trying to replace the values inside the dict from "'s" to "s", but the values are dicts and i need to keep the type as dict
i know if i want change the keys i can use:

di['Name'] = di['Name'].map(lambda d: {k if k != 'EN-US' else 'en': v for k, v in d.items()})

but how about change the values?

[prisma girder]

Have you tried something like this?

di['Name'] = di['Name'].map(lambda d: {k if k != 'EN-US' else 'en': v.replace("'s", "s") for k, v in d.items()})

[foggy iron]

u are a angel on my life

i was trying:

di['Name'] = di['Name'].map(lambda d: {v if v.contains("'s") else v.replace("'s", "s"): v for k, v in d.items()})

i'm kinda of noob with lambda, and with everything else too . _.

[prisma girder]

In first part of {k if k != 'EN-US' else 'en': v.replace("'s", "s") for k, v in d.items()} (I mean k if k != 'EN-US' else 'en') you are changing the key, after the : you are putting the value for chosen key

[foggy iron]

so it only change the values of the chosen key?

[prisma girder]

You can select value for chosen key, like in the following example

x = {
  "x": [0, 1, 2, 3, 4],
  "y": 8,
}
y = {k: sum(v) if isinstance(v, list) else v for k, v in x.items()}

You are creating new dict from old one and sum all values if you have a list object

!e

x = {
  "x": [0, 1, 2, 3, 4],
  "y": 8,
}
y = {k: sum(v) if isinstance(v, list) else v for k, v in x.items()}
print("x", x)
print("y", y)

[delicate field]

@prisma girder :white_check_mark: Your eval job has completed with return code 0.

001 | x {'x': [0, 1, 2, 3, 4], 'y': 8}
002 | y {'x': 10, 'y': 8}

[prisma girder]

Using non one-line solution you can write something like

x = {
  "x": [0, 1, 2, 3, 4],
  "y": 8,
}
y = {}
for key, value in x.items():
  if isinstance(value, list):
    value = sum(value)
  y[key] = value

[foggy iron]

https://tenor.com/view/patrick-star-spongebob-squarepants-wow-mindblown-gif-13169052

1sec pls

[waxen vortex]

hmmmm, hi

i'm trying to make a bot on discord, that generate key for my members

and for some staff reasons, i need to backup these key with ctx.author.id on a db file. how can i do that

[prisma girder]

You can use SQLite and store those values inside table like members_keys with columns id, author_id, key

[waxen vortex]

i don't know nothing about database 😅

[prisma girder]

How many keys do you need to store? Maybe you can put them inside CSV file or some JSON?

[waxen vortex]

30-40 ( delete the file every month )

[prisma girder]

If you know nothing about databases and SQL it looks that you should store those values inside JSON-like file

[foggy iron]

so this only change the value of a specific key right? if i have multiple keys in one specific dict like:

{'EN-US': ""Master's Tame Giant Stag"", 'DE-DE': 'Zahmer Riesenhirsch des Meisters', 'FR-FR': ""Cerf géant apprivoisé de l'adepte"", 'RU-RU': 'Ручной гигантский олень (мастер)', 'PL-PL': 'Oswojony Jelonek Olbrzymi Mistrza', 'ES-ES': 'Ciervo Gigante Domado del Maestro', 'PT-BR': 'Veado-gigante Manso do Mestre', 'ZH-CN': '大师级家养巨鹿', 'KO-KR': '마스터의 길들인 거대 숫사슴'}

so i'll need to do a different code right? something where i dont will specific the key i think

[prisma girder]

However here you have some tutorial about SQLite in Python https://www.sqlitetutorial.net/sqlite-python/
I never used it, its first occurrence in my search

[foggy iron]

do a dataframe using pandas with 2 columns
1 user.id / 2 keys
when someone want pick a key u can run pandas to check where the first column is empty and fill with a user.id

[waxen vortex]

But how do I get the bot to store the information inside it?

[waxen vortex]

i'm gonna try

[foggy iron]

after u write in a row with pandas u can do .to_json('file') for example

[prisma girder]

Let say that you want to change EN-US key to be lowercase and make uppercase title for DE-DE

olddict = {'EN-US': "Master's Tame Giant Stag", 'DE-DE': 'Zahmer Riesenhirsch des Meisters', 'FR-FR': "Cerf géant apprivoisé de l'adepte", 'RU-RU': 'Ручной гигантский олень (мастер)', 'PL-PL': 'Oswojony Jelonek Olbrzymi Mistrza', 'ES-ES': 'Ciervo Gigante Domado del Maestro', 'PT-BR': 'Veado-gigante Manso do Mestre', 'ZH-CN': '大师级家养巨鹿', 'KO-KR': '마스터의 길들인 거대 숫사슴'}
newdict = {key.lower() if key == "EN-US" else key: value.upper() if key == "DE-DE" else value for key, value in olddict.items()}

!e

olddict = {'EN-US': "Master's Tame Giant Stag", 'DE-DE': 'Zahmer Riesenhirsch des Meisters', 'FR-FR': "Cerf géant apprivoisé de l'adepte", 'RU-RU': 'Ручной гигантский олень (мастер)', 'PL-PL': 'Oswojony Jelonek Olbrzymi Mistrza', 'ES-ES': 'Ciervo Gigante Domado del Maestro', 'PT-BR': 'Veado-gigante Manso do Mestre', 'ZH-CN': '大师级家养巨鹿', 'KO-KR': '마스터의 길들인 거대 숫사슴'}
newdict = {key.lower() if key == "EN-US" else key: value.upper() if key == "DE-DE" else value for key, value in olddict.items()}
print(olddict)
print(newdict)

[delicate field]

@prisma girder :white_check_mark: Your eval job has completed with return code 0.

001 | {'EN-US': "Master's Tame Giant Stag", 'DE-DE': 'Zahmer Riesenhirsch des Meisters', 'FR-FR': "Cerf géant apprivoisé de l'adepte", 'RU-RU': 'Ручной гигантский олень (мастер)', 'PL-PL': 'Oswojony Jelonek Olbrzymi Mistrza', 'ES-ES': 'Ciervo Gigante Domado del Maestro', 'PT-BR': 'Veado-gigante Manso do Mestre', 'ZH-CN': '大师级家养巨鹿', 'KO-KR': '마스터의 길들인 거대 숫사슴'}
002 | {'en-us': "Master's Tame Giant Stag", 'DE-DE': 'ZAHMER RIESENHIRSCH DES MEISTERS', 'FR-FR': "Cerf géant apprivoisé de l'adepte", 'RU-RU': 'Ручной гигантский олень (мастер)', 'PL-PL': 'Oswojony Jelonek Olbrzymi Mistrza', 'ES-ES': 'Ciervo Gigante Domado del Maestro', 'PT-BR': 'Veado-gigante Manso do Mestre', 'ZH-CN': '大师级家养巨鹿', 'KO-KR': '마스터의 길들인 거대 숫사슴'}

[prisma girder]

@foggy iron

[waxen vortex]

my brain 😦

[foggy iron]

u dont need to care about this dict problem . _.
ur case is more simple, try to search about pandas read and pandas to file and how to use pandas to change values of one column

[foggy iron]

but its all about keys right? i mean, i'm thinking if this code

di['Name'] = di['Name'].map(lambda d: {k if k != 'EN-US' else 'en': v.replace("'s", "s") for k, v in d.items()})

change the "'s" in all d.items.values or if it only change in the chosen key "en-us"

[waxen vortex]

i'm gonna search, ty

[prisma girder]

It changes values for every key and updates key name only for en-us

You have ... if [condition] else ... only on key side so you are updating key only if condition is true but values are changed for every key (there is no ... if [condition] else ... on the value side)

It's hard to explain it for me 😦

[foggy iron]

hmmm i think i gotcha

so he change the "'s" "s" thing for every key, and the "en-us" to "en" its a condition for only the key right?

[prisma girder]

Yep, that's it

[foggy iron]

gotcha

[foggy iron]

u know how i can print the lines where contains a specific word?

like:

di[di["Name"].astype(str).str.contains("Delivery:")]

cause i'll need to drop some . _.

[prisma girder]

di['Name'] is a list of dicts?

[foggy iron]

ye

[prisma girder]

Give me a second

It should be something like this

filter(lambda d: any("word" in v for v in d.values()), di['Name'])

!e

x = [{"x": 0, "y": 1, "z": 2}, {"a": 5}, {"a": 3, "b": 8}]
y = list(filter(lambda d: any(v >= 5 for v in d.values()), x))  # find all dicts with at least one value greater or equal to 5
print(y)

[delicate field]

@prisma girder :white_check_mark: Your eval job has completed with return code 0.

[{'a': 5}, {'a': 3, 'b': 8}]

[prisma girder]

@foggy iron

[foggy iron]

gonna try that ty

[foggy iron]

i have one doubt about performance, i was talking with a person in the #data-science-and-ml and he said i should not take this way of work with dicts as rows, u think i should do different? i need these lines to do query of names and take the language, u think if i used the languages names('en', 'fr', 'ru', 'es'...) as columns it could be the same speed? cause i know this way of keys as columns could be easier

example:
someone will type Carrot Seeds, so he will look at the column check if there is any Carrot Seeds in the values and if it has so he will take the language else he will say he dont found and suggest closest values
thats how i'm trying to use it

[prisma girder]

I would say that all depends

[prisma girder]

Is it looks like this?

[d for d in di["Name"] if any(v == "query" for v in d.values())]

Yeah, it probably will be very slow for large structures

[foggy iron]

ye i think, i dont tried it yet

[foggy iron]

so u think i should try work it was columns?
like

[i for subl in np.where(di[['en', 'es', 'fr', 'ru'...]].str.find('query') > -1) for i in subl]

it takes the index of the matchs values

[prisma girder]

I think that you can create a list of all possible values and then mapping value to language

[foggy iron]

i dont got what u mean . _.

the only way i know how to do is: look if the string is in the column/values if its so give me the index of the match result of the dataframe

[prisma girder]

I never used dataframe so... I think that I cannot help 😦

[spark matrix]

Hello, I was able to connect to MySQL from the XAMPP control panel until a few hours ago, but now I cannot. It just says "Attempting to start SQL service ..." and nothing else. I ve tried so many things that maybe I might have made things worse. Is there anybody who knows the solution?

[torn sphinx]

so I'm starting to use sqlite3, and I want to have a statement that will Insert if the primary key(s), don't exist, or Update that row with the provided fields. I've been spending time on Google, and I can't find an answer that I understand, or that the majority of people are agreeing on. If anyone can help me out, that would be awesome

[proven arrow]

https://www.sqlite.org/lang_UPSERT.html

INSERT INTO table(col1, col2) VALUES(1, 'B')
ON CONFLICT(col1) DO UPDATE SET col2=excluded.col2

[autumn epoch]

So I have a Postgres database and was wondering how I can insert if the users if id is not in the system. Also how would I set a column to something if it is none

[proven arrow]

It’s not so clear what you meant

[autumn epoch]

So basically I want to Insert into the table if there is no information with his/her user id

And I want it to set a column to a certain thing if it is None

[proven arrow]

And if there already is information with his id?

[autumn epoch]

Does nothing

[proven arrow]

Ok

[proven arrow]

By None you mean if it doesn’t exist?

[autumn epoch]

Yes

Because whenever I add a new column and select it, it says None

[proven arrow]

Ok now your jumping places.

Adding column is different to adding row

But to insert a row, and if it already exists then ignore any errors, you can use https://www.postgresqltutorial.com/postgresql-upsert/
For the on conflict you want: ON CONFLICT(user_id) DO NOTHING

[autumn epoch]

Wait a sec I need to go but I will explain further what I mean

[sweet echo]

How could I detect a change in a SQL database through python?

[prisma girder]

Do you mean to track insert and update queries?

You need to set up triggers or track query log

[sweet echo]

Say I change data inside a table how would I detect it

[proven arrow]

Why do you need to detect a change? What’s making the change

There’s no easy way. Use triggers as they said.

[sweet echo]

I have two scripts. One changes the database and one should detect changes in it

[proven arrow]

Also what database?

[prisma girder]

You can make it in ugly way by quering database every one minute for example and check number of rows, remember that number and after another query compare

But it's tracking only inserts and it's ugly and bad

[proven arrow]

If your the one making the change then just write to logs

[oak swan]

hi

i cannot update the row

when i update it throw this

[autumn epoch]

@proven arrow Basically when someone first uses my bot it inserts their user id and a bunch of information. When I add a new column it comes out as None when I select it. How would I insert into the table only when their user id is not there and check every column that it is not None

[proven arrow]

Adding a new column will always be null, because the column didn’t exist when you added the rows

[oak swan]

@proven arrow its exsist

[proven arrow]

No I meant to reply to finger person

[oak swan]

oh

nvm

[autumn epoch]

Are rows the data?

And columns are what the data is?

Like if it is their user id or their balance in my case

[proven arrow]

No I meant to reply to finger person

[autumn epoch]

?

[proven arrow]

Like a table you have rows and columns. Row holds each record, and is made up of columns

[autumn epoch]

Ok

So how would I insert into a table if the table doesn't have their userid in it?

[autumn epoch]

@proven arrow

[proven arrow]

What’s wrong with a normal insert?

If data doesn’t exist you add it. I don’t see the issue.

If something doesn’t make sense share your tables, queries, and actual/expected outcome

[autumn epoch]

I am just trying to make my code cleaner, right now I select it, check if it is None, then insert it.

[storm parcel]

For python which database is best. Mysql or PostgreSQL

[brazen charm]

Postgres

Postgres wins in most things overall tbh

it will pretty much tank anything you throw at it

python wise Psycopg2 (sync) and AsyncPG (asyncio) are two very nice drivers as well

[foggy iron]

i have one doubt about performance, i have a pickle file(the img) with a lot of dicts in rows and i need these lines to do query of names and take the language, u guys think if i use the languages names('en', 'fr', 'ru', 'es'...) as columns it could be faster? cause i know this way of keys as columns could be easier

How i gonna use this file -> example:
someone will type Carrot Seeds, so he will look at the column check if there is any Carrot Seeds in the values and if it has so he will take the language else he will say he dont found and suggest closest values

so what u guys think its the fast way to do it? using dicts as rows in a dataframe or using the languages as columns and say for he look at all collumns? (i'm using pandas to read the file)

[gleaming tendon]

wut does this channel cover wut databases, meaning like sql code etc

@foggy iron Wut is a good app for sql cause i just use notepad for that

[distant kayak]

@app.route('/api/login', methods=['GET'])
def api():
    token = echange_code(request.args.get('code'))
    user_guilds = get_user_guilds(token)
    db = cluster['myFirstDatabase']
    collection = db['users']
    guilds_collection = {"guilds": user_guilds}
    data_guilds = [guilds_collection]
    datafind_guilds = collection.find_one(data_guilds)
    if datafind_guilds:
        collection.delete_many(data_guilds)
        collection.insert_many([guilds_collection])
    else:
        collection.insert_many([guilds_collection])
    return redirect('/')

there is any error

and when findone the data it didn't delete data and make it

[gaunt yarrow]

Probably a dumb question but how might I set a variable to equal a value in a specific field in my db? Using Mongodb for reference.

[distant kayak]

but varriable made like this
[{...}]
@gaunt yarrow

not variable

[gaunt yarrow]

??? what now.

[distant kayak]

sec

[lusty nest]

how to make server sided toggle command that enables/disables commands

[proven arrow]

Can you be more specific?

What database? What do you need help with? Structuring db, writing query or something else

[sand trench]

Hi guys. I need help please. So, I'd like to check if a database exist in my flask app. If it does not I'd like to create a new database as well as create a default user with a password on the fly when the database is created. How do I go about this?

[austere portal]

with psycopg2.connect(db_uri) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM FoodProducts;")
        rows = cur.fetchall()``` How can I convert this query result to json format, Thanks

[proud oxide]

Postgres has a to_json() function

https://www.postgresql.org/docs/9.3/functions-json.html

[torn sphinx]

In my views , NoSQL looks better to me

[austere portal]

Thanks

But how is the query going to be?

[oak swan]

Msg 8101, Level 16, State 1, Procedure Tri_IUD_Users_HEC_Officers_Uni_tbl, Line 5 [Batch Start Line 7] An explicit value for the identity column in table 'HEC.dbo.Users_HEC_Officers_Uni' can only be specified when a column list is used and IDENTITY_INSERT is ON.

can someone know how to fix it

[vocal marsh]

Hi all.. I'm having a rather hard time aggregating intersecting tsranges in postgres. If anybody would be interested..
http://stackoverflow.com/questions/67178854/

[signal cloak]

Hey I was wondering, in a discord bot do I need to close and open connection with db, or keep one always open

I took the answer depends

But idk what to do it my bot

The DB get request everytime users use some commands

and when they update, their discord name/discriminator or leave or join the server

[crisp arch]

I'd suggest looking into connection pools

then each command you can pull a connection from the pool and use that

[vague forge]

guys mysql if i want to group these bunch of items that has the same id ,what should i input in command ?

[burnt turret]

^ @signal cloak there's an example here #databases message

[signal cloak]

@burnt turret So How does pool works >?

[austere portal]

Thanks

[signal cloak]

btw I am using mongo is that still duable, if yes, do i need to use async drivers?

[torn sphinx]

can anyone help me to make a cloud gaming server

[deft shell]

what i need to do?

[nocturne lance]

hello. Im new to databases/squlite3, could anyone help me create a database? What i would like it to do is a vouching system where if someone got help from an other person they can do +vouch and it would show that they have x vouches.

[burnt turret]

with mongo you'd just use motor, and do almost the same procedure (make one connection, and assign it to a bot variable and then use it throughout the bot)
and yes, the async driver for mongo is called motor. if you're using pymongo right now, this gist will help you switch https://gist.github.com/anand2312/840aeb3e98c3d7dbb3db8b757c1a7ace

[signal cloak]

ty so much

[nocturne lance]

if anyone has time, could someone help me create a database system in discord.py? Ping me!

[dark oxide]

Any good articles/documentation for spinning up a PostgreSQL DB in a docker container. I haven't been able to find anything helpful.

[brazen charm]

Compose probably the easiest

version: '3'

services:
  postgres:
    image: postgres:13
    restart: always
    environment:
      POSTGRES_DB: postgres
      POSTGRES_USER: secret
      POSTGRES_PASSWORD: secret
      PGDATA: /var/lib/postgresql/data
    volumes:
      - database-data:/var/lib/postgresql/data
    ports:
      - "5432:5432"

volumes:
  database-data:```
is a pretty simple compose setup

you'll need to run docker volume create database-data first if i remember correctly though

[dark oxide]

Ok, thank you!

[bitter cove]

all i do is this
CLUSTER = MongoClient('mongodb+srv://DexTer:**@discord.okk4l.mongodb.net/test?retryWrites=true&w=majority')
pymongo.errors.ConfigurationError: query() got an unexpected keyword argument 'lifetime'
any idea?
help

@brazen charm

@burnt turret

[nocturne dock]

What is the cost of ordering a table that is already ordered?

i.e. ORDER BY an already ordered table

[humble creek]

If I use sqlite3, do I need to do anything to convert to Postgresql for Heroku?

[digital wharf]

syntax likely

[manic marsh]

what's the best way to store data as a file to immitate database

i was thinking like a key as filename

and the value is the file content(compressed)

[proven arrow]

Depends on the optimiser/query execution plan. Even though you know it’s ordered the database doesn’t know this.

And depends which sorting algorithm it picks.

[proven arrow]

What kind of database?

[manic marsh]

key and value database

im having hard time deciding how to store the data in the disk

[proven arrow]

Why not json?

[manic marsh]

that's not pog

storing a json in 1 file is eh

gonna take alot of time just to write 1 change

so im kinda thinking something like

splitting it

currently i have the idea of

[proven arrow]

Well you’ve not said enough of what your doing/working on

[manic marsh]

Folder -> scopes
Files -> key as filename, value as file content(compressed bytes)

a key and value database

for simple operation of set and get

[proven arrow]

There could be dozen ways to do it. Also why make your own solution when there already exists stuff for this?

[manic marsh]

mongodb doesn't work

in here

by using cellular data its gonna raise timeout

with wifi its either slow

or it won't connect

locally is impossible in mobile

also something simple and small

like a few kb of files

i already make the server running locally

but data storing is my concern

i dont want to make it scaleable

since its for something small

like postgresql for config

overkill

while we can use json

something like that

[proven arrow]

What is the data? How much read write?

[manic marsh]

bytes

im not using types like int, string, float

so im storing bytes

[proven arrow]

SQLite can work

[manic marsh]

noo no no

im making my own

also its a key and value

not relational

[nocturne dock]

So it's possible for the cost to be 1 if the database is aware or very large if the database is not aware

[proven arrow]

Again it depends. How the optimiser decides to do it could be different.

You have to check the execution plan in your case

For example, you can have an index on a column. You would think it’s aware and will use that index, but it could end up not using the index at all for the ordering, and instead just use something like filesort. However, whichever method it picks its probably the best based on your table setup.

You can take a look at this test, although it’s with Sql server the idea behind it’s the same https://sqlsunday.com/2021/02/15/is-a-sort-faster-when-data-already-sorted/

[humble creek]

So Heroku only uses postgresql, how can I convert a SQLite db to PostgreSQL?

Real newbie here

[real summit]

hi

trying to make a bot which stores your xp and coins

am im completely lost

[autumn rune]

I have some code which constantly gives me the following error: the server expects 1 argument for this query, 170 were passed\nHINT: Check the query against the passed list of arguments.

My code is:

    async def set(self, to, value, where, where2, schema, table):
        print(value)
        query = f"""
        UPDATE {schema}.{table}
        SET {to} = $1::text[]
        WHERE {where} = {where2}
        """
        print(query)
        try:
            result = await self.conn.executemany(query, value)

(there is more code, but I just put the stuff which is essential to solving the problem)

[north birch]

ggg

ez

boii

[bitter cove]

guys why no one is helping me

why are you ignoring me..

[blissful mist]

could anyone help me with these??

[crisp arch]

Hi, please don't drop random youtube links in this channel

[bitter cove]

can you help me?

hello?

[real summit]

in the sense it updates once (the first time) and then it doesnt

[valid jewel]

why did you indent everything after cursor = db.cursor()?

[real summit]

its discord formatting i didnt

its under a command btw not an event

[valid jewel]

also why are you doing it like that

cursor.execute("SELECT user_xp, user_level FROM users WHERE client_id = " + str(ctx.author.id))
```Just do
```py
cursor.execute("SELECT user_xp, user_level FROM users WHERE client_id=?", (str(ctx.author.id,)))

[real summit]

there is no difference....

[valid jewel]

its better

ok with your way how can you have 2 values?

something like

cursor.execute("SELECT user_xp, user_level FROM users WHERE client_id=? AND guild_id=?")

[real summit]

i dont need guild_id its not unique to a server

[brave bridge]

An SQLite database is literally just a file. Does Heroku support storing files persistently?

[humble creek]

no, ephemeral

which is why i need postgresql

do i just have to rewrite everything

[brave bridge]

Table creation statements that work on sqlite should mostly work on postgresql, and you can export and import the data via CSV

Apparently, there's also a program for importing stuff into postgres
https://pgloader.readthedocs.io/en/latest/ref/sqlite.html

[humble creek]

ah okay ill give it a shot and just publish then

oh interesting

[brave bridge]

I haven't used it myself, just found it

[sullen pine]

i need to get [select count(*) from table a where date>2020-05-01 and id after 10] the id is not ordered. so i got use > < operators. any help

[night tusk]

hey anyone can help

[bitter cove]

File "C:\Users\ZeroD\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\helpers.py", line 164, in _check_command_response
    raise OperationFailure(errmsg, code, response, max_wire_version)
pymongo.errors.OperationFailure: Authentication failed., full error: {'ok': 0, 'errmsg': 'Authentication failed.', 'code': 8000, 'codeName': 'AtlasError'}

help please

there are over 5000 characters error

this is the last of it

[dense barn]

@welcome.command()
@commands.has_permissions(manage_messages=True)
@commands.has_permissions()
async def message(ctx,*,text):
    async with asqlite3.connect(db_path) as db:
        async with db.cursor() as cursor:
            await cursor.execute(f"SELECT enabled AND enabled FROM welcome WHERE guild_id = ?",(ctx.guild.id, ))
            result = await cursor.fetchone()
            print (result)
            if result == (0,):
                await ctx.send("Welcome commands are disabled! To turn it on please type .enable welcomecommand")
                return
            elif result is None:
                sql = ("INSERT INTO welcome(guild_id, msg) VALUES(?,?)")
                val = (ctx.guild.id, text)
                await ctx.send(f"Message has been set to: ``{text}``")
            elif result is not None:
                sql = ("UPDATE welcome SET msg = ? WHERE guild_id = ?")
                val = (text, ctx.guild.id)
                await ctx.send(f"Message has been updated to: ``{text}``")
                await cursor.execute(sql, val)
                await db.commit()
                await cursor.close()
                db.close
```i have this command which is supposed to make a column in the welcome table if the result is None, but it wont do that. and i have no idea why

[glad drift]

I'm trying to connect to my Heroku db. It worked previously, and I've changed nothing in the script, but I am getting this error:

Traceback (most recent call last):
  File "/home/alan/dev/python/smog_usage_stats/scripts/DBManager.py", line 17, in <module>
    CONN = pg2.connect(
  File "/home/alan/dev/python/smog_usage_stats/venv/lib/python3.8/site-packages/psycopg2/__init__.py", line 127, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL:  role "alan" does not exist

but I dont understand why?

[night tusk]

hey anyone can help

[dense barn]

?

[night tusk]

ah my db isnt storing any data

its saying that db is locked

[dense barn]

same

uh

what db are you using

[night tusk]

sqlite3

[dense barn]

did you change anything in it

?

[night tusk]

nope

[dense barn]

idk alot about it, sorry

[night tusk]

ok

[manic marsh]

its because

something is accessing the database

@night tusk

like it won't allow 2 write

[night tusk]

yea

i removed some data manually

0.23

3

3

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @night tusk until 2021-04-21 12:58 (9 minutes and 58 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

[manic marsh]

@night tusk yeah uhm dont do that

[analog sigil]

!unmute 776768034223947777

[delicate field]

:incoming_envelope: :ok_hand: pardoned infraction mute for @night tusk.

[analog sigil]

!paste

[delicate field]

Pasting large amounts of code

If your code is too long to fit in a codeblock in discord, you can paste your code here:
https://paste.pydis.com/

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[night tusk]

vivek sorry

my glass accidentally fells on my keyboard and then it started spamming

sorry

so @manic marsh

what should i do

[manic marsh]

not now

[night tusk]

what?

[proven arrow]

What is the issues?

If it’s db locked your referring too, then make sure you close any database browsers you have open.

[torn sphinx]

#web-development

ignore 🙂

[austere portal]

Thanks

Fixed it

[woven bough]

!e from os import system;from subprocess import getoutput;system('echo "tralala" >> out.txt');getoutput(['ls'])

[glad drift]

I am still stuck on this, if anyone can help?

From what I gather, the role designation is the name of the user I'm using to connect, but that value is not "alan" it is some jumble string heroku autogenerates

[teal phoenix]

What if I just remove dangerous characters from a string? Will it prevent SQL injection?

[cyan yacht]

If you remove the characters manually, it may lead to unexpected behaviour

Instead, you could pass the values as parameters to the query

[cyan yacht]

https://realpython.com/prevent-python-sql-injection/

This is one resource I found useful

[teal phoenix]

Okay thanks ❤️

[keen moat]

guysss

my flask database doesnt work:(

[lone delta]

How do i remove a JSON element from a file with nested elements in python

[stone canopy]

I have been experiencing random rollbacks in a sqllite3 database. One day my table is up to date the next day it was at values from last week. IDK why this is happening

[haughty crypt]

is there anyone that can help with mySQL?

[naive adder]

I have been experiencing random rollbacks in a sqllite3 database. One day my table is up to date the next day it was at values from last week. IDK why this is happening
@stone canopy did you commit?? If you didnt it wont save

[wooden basin]

Hi, I have a question, I am using PsycoPg2, do I have to commit every time I execute or commit at the end of each function?

[eternal raptor]

Hi, when I was creating a role in PhpPgAdmin, I saw it... What's this? I want to give an access to adding data to tables and databases, delete data, updating data. Can anyone here help me? Thanks for any help, guys!

[eternal raptor]

yes

[stark iris]

.

[wooden basin]

yes? what?

[eternal raptor]

you must always commit on the end of function.

if you want to save progress in database

[wooden basin]

I have made commits in each execute, can that cause failures?

[eternal raptor]

no

[wooden basin]

Something strange happens to me and sometimes the commits work fine and sometimes they don't, and I don't understand

[eternal raptor]

shouldn't

[eternal raptor]

hm... can you show me example?

[wooden basin]

of course, look:

    elif result[3] == True:
                            await member.send("Hey, it's your turn to rest, try to clear your head a bit, I'll let you know when your rest is over!")
                            remaining = result[5]
                            cursor.execute(
                                "UPDATE pomodoro_test SET break = FALSE")
                            next_notification = next_notification + \
                                datetime.timedelta(minutes=5)
                            cursor.execute("UPDATE pomodoro_test SET next_notification = %s WHERE ident = %s", (
                                next_notification.strftime("%H:%M:%S"), ident))
                            cursor.execute(
                                "UPDATE pomodoro_test SET remaining = %s WHERE ident = %s", (remaining-1, ident))
                            conn.commit()
                            conn.close()
                   ```

sometimes the break changes it for me but sometimes it doesn't @eternal raptor

[eternal raptor]

Hi, when I was creating a role in PhpPgAdmin, I saw it... What's this? I want to give an access to adding data to tables and databases, delete data, updating data. Can anyone here help me? Thanks for any help, guys!
It's necessary, help me plz xD

[eternal raptor]

hm... first... you code wrong

wait

I had a tutorial

[wooden basin]

ik, im starting

[eternal raptor]

wait a second

sooo

I found

import discord
import aiosqlite

  @commands.Cog.listener()
    async def on_member_join(self, member):
        async with aiosqlite.connect('C:/Users/sebik/Desktop/lptw/lptwdb.db') as db:
        cur = await db.cursor()
        selectQuery = 'SELECT cash FROM economy WHERE UserID = ?'
        await cur.execute(selectQuery, (member.id,))
        result = await cur.fetchone()
        do...code
        await db.commit()

it's a piece of my private bot

[wooden basin]

cool

[eternal raptor]

I can't find tutorial, because anyone gave me a few months ago

I can send you in PV chat my command for check amount money from database

write to me in PV\

[misty adder]

5215681

It as kahoot

Funny

[eternal raptor]

bump

[timid bronze]

i have document like this, how can i change name?

it's mongodb

[dreamy flint]

I don't understand what's wrong

INSERT INTO vote_user_data (discord_id, total_votes, points, last_voted)
VALUES(257073333273624576, 2, 150, '2021-04-22')
ON CONFLICT (discord_id)
DO UPDATE SET total_votes = total_votes+2, points = points+150, last_voted = '2021-04-22'
WHERE discord_id=257073333273624576;

https://cdn.discordapp.com/attachments/806145425052270592/834761660932816926/unknown.png
https://cdn.discordapp.com/attachments/806145425052270592/834764568093392906/unknown.png

[upper basin]

i've installed aiosqlite, but im unable to import it, can anyone help

[torn sphinx]

How do I print everything in my sql db?

[signal cloak]

I need to add it in every cog ?

[glad drift]

in terms of this psycopg2.OperationalError: FATAL: role "alan" does not exist what is role most likely? is it the value for user in:

CONN = pg2.connect(
    database = os.environ.get('PG_DATABASE'),
    user     = os.environ.get('PG_USER'),
    password = os.environ.get('PG_PASSWORD'),
    host     = os.environ.get('PG_HOST'),
    port     = os.environ.get('PG_PORT'),
    sslmode  = "require"
)

I'm asking, because that's not the value I expect to have there.

[rich drift]

[SOLVED (sort of)]
Hello!
I'm new to using sqlalchemy. I'm writing a script that populates a database in large batches. Each batch has roughly 2000-3000 records with 10s to 100s of thousands of records being inserted into tables that have a relationship with the 'primary' entries.

My script functions correctly, but it's not fast and it gets slower towards the end of the batch (beginning at 1.3 iterations/second, ending at ~2 seconds / iteration). I've come up with a few ways to improve my script, but I have no intuition about which approaches would actually optimize it.

The ideas I've come up with are:

1. Create compound primary keys for the related tables to potentially make checking for existing records (to avoid adding duplicates) faster.
2. Manually flushing the sqlalchemy Session on every iteration (or every n iterations).
3. Some sort of in-memory caching of related table records for faster lookups.
4. Altering some other attribute or setting of the Session that I don't yet know about.
5. Some other code pattern/algorithm that I don't yet know about.
6. Accept that what my script is doing is just supposed to take a long time.
7. Sacrifice a goat.

For all the more experienced sqlalchemy users out there, which avenue do you suggest I explore first?
I'll post my script and/or table schema if that will help.

Edit: Some more searching led to this article: https://benchling.engineering/sqlalchemy-batch-inserts-a-module-for-when-youre-inserting-thousands-of-rows-and-it-s-slow-16ece0ef5bf7 which seems to suggest that If I can create composite primary keys for the supporting tables, it will enable sqlalchemy to to take advantage of psycopg2's execute_values function (luckily I'm using postgres).

Alternatively, based on this part of the sqlalchemy documentation (https://docs.sqlalchemy.org/en/13/faq/performance.html#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow) I can try using the bulk_save_objects or bulk_insert APIs

[glad drift]

depending on where you live, the goat might be the fastest option

[torn sphinx]

cursor.execute("UPDATE users SET user_xp = "+ str(newXP)+ ", user_level = " + str(currentLevel)+", user_coins = " + str(currentCoins)+", WHERE client_id = " + str(ctx.author.id))

wrong syntax
idk how

help pls

[cyan yacht]

probably the comma before WHERE

[sinful saffron]

Hi, How can I execute a function when X table is updated

I think im trying to create a trigger

[eternal raptor]

Hi, when I was creating a role in PhpPgAdmin (PostgreSQL), I saw it... What's this? I want to give an access to adding data to tables and databases, delete data, updating data. Can anyone here help me? Thanks for any help, guys!

[torn sphinx]

yea, someone confirmed it in general

tysm anyway

@cyan yacht can you help me out with one more thing

cursor = db.cursor()
    cursor.execute("SELECT user_xp, user_level, user_coins FROM users WHERE client_id = " + str(ctx.author.id))
    result = cursor.fetchall()

    if len(result) == 0:
        cursor.execute("INSERT INTO users VALUES(" + str(ctx.author.id) + ", " + str(XP) + ", 1" + ", " + str(COINS) + ")")
        db.commit()
    else:
        newXP = result[0][0] + XP
        newCoins = result[0][0] + COINS
        currentLevel = result[0][1]

        if newXP > 0:
            currentLevel = newXP/100

        cursor.execute("UPDATE users SET user_xp = "+ str(newXP)+ ", user_level = " + str(currentLevel)+", user_coins = " + str(newCoins)+" WHERE client_id = " + str(ctx.author.id))
        db.commit()

the xp and levels update properly but the coins dont

it increases exponentially

[vestal brook]

what data stream services are free on AWS

[cyan yacht]

Sorry, can't figure out why that's happening

[thorn lark]

how can i make my pc a mysql server and connect it from any machine using the credentials set by me. can someone give me a tutorial on that please

[floral thistle]

So I'm making a website that uses FastAPI for the backend, and postgres as its database. Which ORM would you guys recommend? Tortoise-ORM or Sqlalchemy?

[half plover]

where i can learn sql?

[torn sphinx]

somewhat database related, does anyone know if you can synchronize shelve accesses between processes?

like process A writes to the database, does sync(), process B also has the database open, does sync(), sees the value written by A

[brazen charm]

Generally i would really recommend not doing that

if you're getting to that point i'd use a proper server based DB or cache for that (Postgres, MySQL, Redis, etc...)

as soon as you start trying to roughly sync this with several processes you're gonna run into race conditions and a generally messy interface

[heady willow]

hi, i'm trying to update around of 400 rows with a function, but i want to do the update with just a query, something like "update table set status = 0 where id in [1,2,3,4,...,400], any idea?

[bright hound]

Hey everyone. I'm trying to start up postgres from my vscode terminal and I can't figure out how to do it. Do I need to download one of the VScode extensions or can I do it without it? I feel like I did it before without it but now I can't figure it out.

Specifically vscode isn't recognizing the psql command

I have added the \bin and \lib directories to path and it is recognized from the normal windows command prompt

[bright hound]

I forgot to install postgreSQL in my condas environment

But now when I run my script I get my printout that I successfully connected to the database, but my command prompt doesn't show the postgres command prompt it reverts back to my file directory prompt

[bright hound]

Or is it better to wrap my connection script in a function and just call it every time I need to read or write from/to the database?

[astral hearth]

i am hosting a postgres database on a VPS and i was wondering what the best way to access it would be through my local machine. an SSH?

[eternal raptor]

http://sqlbolt.com

[torn sphinx]

 cursor = db.cursor()
    cursor.execute("SELECT user_xp, user_level, user_coins FROM users WHERE client_id = " + str(ctx.author.id))
    result = cursor.fetchall()

    if len(result) == 0:
        cursor.execute("INSERT INTO users VALUES(" + str(ctx.author.id) + ", " + str(XP) + ", 1" + ", " + str(COINS) + ")")
        db.commit()
    else:
        print(result)
        global newXP
        global currentLevel
        global newCoins
        newXP = result[0][0] + XP
        newCoins = result[0][0] + COINS
        currentLevel = result[0][1]

        if newXP > 0:
            currentLevel = newXP/100

        cursor.execute("UPDATE users SET user_xp = "+ str(newXP)+ ", user_level = " + str(currentLevel)+", user_coins = " + str(newCoins)+" WHERE client_id = " + str(ctx.author.id))
        db.commit()

it adds the xp to the coins otherwise the code runs fine, any fixes?

[eternal raptor]

Hi, when I was creating a role in PhpPgAdmin (PostgreSQL), I saw it... What's this? I want to give an access to adding data to tables and databases, delete data, updating data. Can anyone here help me? Thanks for any help, guys!

[teal phoenix]

SELECT * FROM global_stats WHERE username = %(username)s,'username':SKULLGAMING_YT;

any idea why i am getting error?

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%(username)s,'username':SKULLGAMING_YT LIMIT 0, 25' at line 1

[silk pendant]

That should work... ```
SELECT * FROM global_stats WHERE username = %(username)s, username = "SKULLGAMING_YT";

[teal phoenix]

oh

[teal phoenix]

it didnt

[candid cradle]

what are you trying to do

[teal phoenix]

i am running the query in phpmyadmin but its not working

and then ill copy the query in my code

[torn sphinx]

Hello

in my code the shutil.copy is automatically overwriting the files without throwing an error

i don't want that

i want it to throw error

why is this happening

[craggy idol]

a doubt in mungodb

how can change the content within a post

as in

    "content":{1:"hi",2:"hello"}```

what i want to do is that just change the content without changing anything else. How can i do that ? not alter change the entire content it self like replace it with another. how can it be done

[craggy idol]

someone help me out please

[torn sphinx]

just change the key value

[craggy idol]

can you show an example

[torn sphinx]

eg dictname["change"] = something

[craggy idol]

ummm i was not asking for a dict

but for a db

[torn sphinx]

you have to enter db name and query data

for that you have to google,

there's lot of things involved with db, like server, db name, ip, login if necessary etc

connection to db etc

[burnt turret]

what have you tried so far?

[craggy idol]

well i did how to find/query for a post using the id

add a post using the id

and check if a post exist using the id

only how to alter them remains

[burnt turret]

you'd be using an update query for this
https://docs.mongodb.com/manual/reference/method/db.collection.update/

[craggy idol]

and delete

[burnt turret]

i'd recommend you go over the entire tutorial that's on mongodb's site first so you're familiar with the create/read/update/delete operations

[craggy idol]

yeah about i can't figure out how read this documentation

[burnt turret]

which part did you not understand?

[craggy idol]

well it's like staring into a book of a language that i don't speak

[burnt turret]

https://www.tutorialspoint.com/mongodb/mongodb_update_document.htm maybe this makes more sense to you, it's written like a tutorial, ping me if you are still not clear on what to do

[torn sphinx]

    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ProgrammingError: Not all parameters were used in the SQL statement

@burnt turret can you pls help

@bot.command()
async def test(ctx):
    phrases = ["You went on a quest and stumbled upon a ", "You searched everywhere and found ", "You went on a quest to find a "]
    creatures = [":slime: **Creature:** slime", ":zombie_eye: **Creature:** zombie", ":bear: **Creature:** bear", ":skull: **Creature:** skeleton", ":pixel_bat: **Creature:** vampire", ":pixel_snake: **Creature:** snake", ":japanese_goblin: **Creature:** goblin", ":giant: **Creature:** giant", ":wolf: **Creature:** wolf", ":spider: **Creature:** giant spider"]
    COINS = random.randint(5, 20)
    XP =random.randint(20, 80)
    x = random.randint(1, 500)
    y = random.choice(creatures)
    a = random.choice(phrases)

    if x == 50:
        y = ":rpgdragon: dragon"
        XP = 2000
        COINS = 1000

    await ctx.send(f"{a}\n{y}\n **{xp} XP:** {XP} **{coin} COINS:** {COINS}")
    cursor = db.cursor()
    cursor.execute("SELECT user_xp, user_level, user_coins FROM users WHERE client_id = ?", [ctx.author.id])
    result = cursor.fetchall()

    if len(result) == 0:
        cursor.execute("INSERT INTO users VALUES(?, ?, 1, ?)", [ctx.author.id, XP, COINS])
        db.commit()
    else:
        print(result)
        global newXP
        newXP = result[0][0] + XP
        global currentLevel
        currentLevel = result[0][1]
        global newCoins
        newCoins = result[0][2] + COINS


        if newXP > 0:
            currentLevel = newXP/100

        cursor.execute("UPDATE users SET user_xp(?, ?, ?) WHERE client_id = ?", [newXP, currentLevel, newCoins, ctx.author.id])
        db.commit()

[craggy idol]

well thank you my guy i understood it, lemme try it out

[burnt turret]

don't ping people randomly
can you send the specific query that raised this error?

[torn sphinx]

yea

cursor.execute("SELECT user_xp, user_level, user_coins FROM users WHERE client_id = ?", [ctx.author.id])

[burnt turret]

whats the full error again?

[torn sphinx]

you can use set function of mongodb

[craggy idol]

thanks man but i got it down

[torn sphinx]

import sqlite3
conn = sqlite3.connect("TestDataBase.db")
cursor = conn.cursor()
id=input("enter ur id ")
name=input("enter ur id ")
age=input("enter ur id ")
grade=input("enter ur id ")
cursor.execute("""CREATE TABLE IF NOT EXISTS Students(
   studentId INT PRIMARY KEY,
   name TEXT,
   age INT,
   grade INT);
""")

cursor.execute("""insert into Students(studentId,name ,age ,grade ) VALUES(?,?,?,?)""",(id,name,age,grade))
conn.commit()

hello what did i dou wrong i want to make an input so i can insert in data base by input

[slate hazel]

Hey! I'm using MongoDB in my project, and I need to check if a certain information already exists in my database. So if I use db.collection.find(), and it doesn't find anything fitting my params, what kind of data does it return?

Is it just an empty dict, is it None...

[burnt turret]

find returns a Cursor object

might be easier to use find_one which would return None if there was no matching documents

why are you checking this?

[slate hazel]

Oh yeah thanks! That's what I need!

[slate hazel]

I'm creating a discord bot, and I am creating a user profile as soon as they join the server, and to avoid multiple profiles of the same user I am checking it based on their discord ID

[burnt turret]

right, so there's something else that'd be better suited for this use-case

actually nvm i was thinking of something else lmao 😅

i was thinking of doing an upsert -> UPdate if exists, INsert if it doesn't but that's not exactly what you want to be doing
https://docs.mongodb.com/manual/reference/method/db.collection.update/#std-label-update-upsert if you want to read about it

[slate hazel]

Yeah that would work, but it's not EXACTLY what I wanted as you said. But that's an interesting function! I will definitely use that somewhere else

Thanks mate!

[eternal raptor]

https://mystb.in/WordpressLabUndertake.python

help

guys

[torn sphinx]

yes, what do you need

[eternal raptor]

https://mystb.in/WordpressLabUndertake.python
help guys, how to fix it ?

[torn sphinx]

okay

!voiceverify

[torn sphinx]

Can anybody help with a flask app that uses SQLite3 and deploying to flask?

[twilit oyster]

How do I update something that is a table inside a table in pymongo

[rapid mulch]

Mongo has operators to patch structured documents

[twilit oyster]

I saw them but I don't know which one to use

[rapid mulch]

$set: { "table1.table2.table3": "new value" }
https://docs.mongodb.com/manual/reference/operator/update/set/#set-fields-in-embedded-documents

[twilit oyster]

Oh okay

Thanks

https://docs.mongodb.com/manual/reference/operator/update/positional/
I was looking at this

[jade swan]

import pymongo
from pymongo import MongoClient

cluster = MongoClient(
    "mongodb+srv://ScopesCodez:[e-mail]/myFirstDatabase?retryWrites=true&w=majority")

db = cluster["test"]
collection = db["test"]

post = {"_id": 0, "name": "Pizza", "price": 50}

collection.insert_one(post)

I keep getting the error :

  File "C:\Users\K I N G\AppData\Local\Programs\Python\Python38\lib\site-packages\pymongo\topology.py", line 215, in _select_servers_loop 
    raise ServerSelectionTimeoutError(
pymongo.errors.ServerSelectionTimeoutError: connection closed,connection closed,connection closed, Timeout: 30s, Topology Description: <TopologyDescription id: 60836bf47b0b86695434d250, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('scoopy-shard-00-00.cyrmt.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed')>, <ServerDescription ('scoopy-shard-00-01.cyrmt.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed')>, <ServerDescription ('scoopy-shard-00-02.cyrmt.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed')>]>

I'm sorry but I'm a starter at MongoDB and I haven't really used an actual database other than JSON before.

[sacred skiff]

im all about them databases

[teal phoenix]

yea

[topaz glen]

hello guys, is anyone experienced in sqlalchemy, im trying to impliment it in my react app, all of that is fine, but when i try to import it it gives me a error saying ti cant find the module

im using python 3.10, im using vscode and no virtual environment and sqlalchemy version is 1.4.11

[eternal raptor]

https://mystb.in/WordpressLabUndertake.python
help guys, how to fix it ?

[hollow ore]

Any recommendations on how to integrate two databases after the merging of two SaaS products?

Tutorials or walkthroughs

[eternal raptor]

https://mystb.in/WordpressLabUndertake.python
help guys, how to fix it ?

[eternal raptor]

so what should i do?

[floral thistle]

AWS vs Azure vs Google?

[untold quartz]

do I name my SQL tables like StudentNames, studentnames or student_names?

[brazen charm]

usually personal preference

generally i go for snake_case

just be consistent really

[eternal raptor]

https://mystb.in/WordpressLabUndertake.python
help guys, how to fix it ? I'm trying to connect from my PC to PostgreSQL on VPS

[bright hound]

Heyyyy guyssss

I have a question

nvm

lol

[autumn epoch]

So I have a bunch of users in my database that have multiple entries due to a glitch a while back. How could I remove everyone of the multiple ones and leave 1, so their data doesn't get wiped.

Btw my database is postgres

[proven arrow]

Identify which rows are duplicate and delete them

[autumn epoch]

Is there an easy day to do that?

[proven arrow]

https://www.postgresqltutorial.com/how-to-delete-duplicate-rows-in-postgresql/

And this is why you should rethink your table structure, and add some constraints so it avoids problems like this.

[autumn epoch]

Thank you

I will look into it

[bright hound]

Ok I have an actual question now

I'm making api calls and getting json data in response. I want to automagically create tables in postgres based on the key:value pairs in the json data. Is there a way to detect the data types for the values so when I create the tables I can define the data types in the table i.e. 'name varchar(15)' etc. Is regex my only real option here?

I'm using python

[proven arrow]

Not sure what your up to but creating tables like that per api call is not such a good idea.

To get the type you can just use the python type function, to get its type and then use this to work out which type you need for sql.

[bright hound]

Why is that a bad idea

[shell ocean]

because tables represent types of entities

it’s the same idea as dynamically creating variables

you should use an appropriate data structure e.g. dictionaries instead

[wise goblet]

well, it is at least bad for regular SQL database

the most dynamically created tables, are usually with usage of ORM things and migrations
but they still remain changed only when coder changes the code and deploys new web site version

you could solve the issue with two options
(this option is a rough one)

1. make something like table where
   column1 - data type in form of string
   column2 - jsonified string

2. or... (this option is better for performance and much smoother)
   You could use something like nosql MongoDB
   that allows storing data almost exactly like python dictionaries 😉
   this choice sort of corresponds with an answer above me

as far as I have heard, DBs like mongoDB aren't really strict on dynamically changed 'tables'
so it should work properly

3. if you have relativelly small amounts of data, you can just use regular dictionaries though
   and just store as json in files between running instances. MongoDB would shine better when you have large amounts of data.
   otherwise it could be slightly overkill perhaps

[bright hound]

I appreciate the response and the suggestions from both of you. I ended up starting this:

def get_data_type(value):
    if (isinstance(value, str) or isinstance(value, list)):
        return 'varchar()'
    elif isinstance(value, bool):
        return 'boolean'
    elif isinstance(value, int):
        return 'integer()'
    else:
        return 'varchar()'

It's by no means complete yet, but since all my data is coming from the same source I think it will be relatively easy to code in all the edge cases.

[burnt turret]

what's this for exactly?

[torn sphinx]

alter table users add column items varchar(20) not null default = xyz;

returns a syntax error

[bright hound]

It's for getting data types. I'll be passing in json data then passing the return value into a string format statement in order to auto populate a sql string along with the key. Then the sql string gets executed and creates a table with the column name and data type so something like { name: "john"} ends up in the sql string like "name varchar(),"

That way when I get json data with 200 key value pairs I don't have to write a column name and data type for every one of them

[burnt turret]

so you'd be making a table with just one row in it each time?

[bright hound]

Nooooo

Each key value pair gets translated and appended to the CREATE TABLE string

So in a json data file with 200 key value pairs it auto populates the string with 200 column names and data types

Then at the end the create table string gets executed and voila I have a table and I didn't have to do anything lol

[burnt turret]

creating tables dynamically doesn't sound like the best idea though

you now have a table with no constraints on it?

[bright hound]

Yeah that's coming soon

[burnt turret]

what will you be doing with this data? are you sure you want to be using SQL and not a document database?

[bright hound]

I just started on it today

Yeah sql is the right thing for it

[burnt turret]

why do you think so?

[bright hound]

Because of the nature of the data and the applications for which the data will be used

[burnt turret]

the nature of the data doesn't look like it fits SQL when you need to be dynamically making tables like this in the first place

[bright hound]

I can see how you might think that

Not all the tables will have so many columns

But some will

[burnt turret]

where's this data coming from exactly? why do you even need to be creating tables like this in the first place?

[bright hound]

Regardless, it's being written as a reusable utility that can ingest any json data, so I can use it for any project forever

Why you want to know so much about my data?

[burnt turret]

because this feels like you're overcomplicating something

something that would be better done in other ways

[bright hound]

Tbh I probably should have just created the schema by hand, but that's not any fun

[burnt turret]

i can already see flaws with this

[bright hound]

Like what

[burnt turret]

like: JSON can be arbitrarily nested, what'll you do then?

[bright hound]

Nested loops

[burnt turret]

making each individual key a column?

[bright hound]

Depends on the data

[burnt turret]

but you'd need to know how nested they are in the first place to be making nested loops

you'd have to recursively travel through it

[burnt turret]

so it wouldn't be a general tool anymore, you'd have to rewrite it for every case

[burnt turret]

and if you make every key a column you now don't know that they were nested in the first place because that relationship is lost now

[bright hound]

What would type() return when it hits a nested json instance?

[burnt turret]

dict probably

it can also encounter lists, what will you implement then?

[bright hound]

Lists are currently turned to varchar

[burnt turret]

it can be a list of dicts too

[bright hound]

Yeah that's all fine

I can store the list or dict as a string and if I need to work with that data I can request it and parse it

[burnt turret]

if you're gonna be storing some dicts or list as string then why not just store the entire json as string?

[bright hound]

Because then I wouldn't have much of a database

[burnt turret]

so you're making a table for the sake of having many columns?

definitely sounds like a job that document databases were made for

[bright hound]

That wouldn't be very logical would it

I mean, operational efficiency alone is a good enough reason not to use a focument

[burnt turret]

but you'd be losing out on most if not all benefits of using a relational database by dynamically creating tables like this

no constraints, can't normalize data

[bright hound]

There will be constraints

I haven't gotten there yet

[burnt turret]

and how are you planning on implementing that? now i'm just curious

[bright hound]

Well, that would be much simpler to do manually if I decide to go that route. But I'm sure if I thought hard enough I could figure out some ways to do it programatically

[burnt turret]

alright

more questions: you said that you'd just turn any nested document/list into string and slap it into the database; but not the entire json object. for an arbitrarily nested object, at which depth do you decide to just give up and turn to a string?

[bright hound]

That's a good question. Currently I do have some nested data but thankfully it's pretty clean so everything that's nested is only one level deep

I guess that's something I'll have to consider more in the future when I run into it

But again, I think nested for loops do the trick

[burnt turret]

alright then

[bright hound]

You raised some good points. Thank you for questioning me

[burnt turret]

good luck with your project!

[bright hound]

🙏

You could write a function to check the length of every item until you reach a level where every item is 1, that would tell you how many levels deep the data is and you could keep track with an iterator then you could auto-populate a function with the correct number of loops and the desired action inside each loop

@burnt turret

[burnt turret]

so you'd be dynamically making code as well?

[bright hound]

Sure. I already am with the sql code

[burnt turret]

and so you'll make every key into a column?

[bright hound]

You could

[burnt turret]

but now you'll lose the relationship that existed in the first place, that the data was nested

[bright hound]

True. Could break each nest into a sub table

[burnt turret]

exactly, lots of choices and lots of decisions to be made 🤷‍♂️

good luck though, interesting idea

[bright hound]

Ty

https://cloud.google.com/bigquery/docs/schema-detect

Deep learning

[eternal raptor]

https://mystb.in/WordpressLabUndertake.python
help guys, how to fix it ? I'm trying to connect from my PC to PostgreSQL on VPS

[coarse orbit]

im not sure if this is the right place but would this be correct?
INSERT INTO table IF NOT EXISTS bla bla bla

[proven arrow]

No

[coarse orbit]

@proven arrow dw

i got it, im checking if there is no data

if there isnt

im inserting

else pass

[proven arrow]

Ok but it’s possible to do it using a single query instead of if else

[coarse orbit]

its not an issue as im using a function

to write to my db

[eternal raptor]

https://mystb.in/WordpressLabUndertake.python
How to fix it? I'm trying connect with PostgreSQL server on VPS from PC, but I have a problem...

[pulsar stag]

whats the best orm for postgres

sqlalchemy seems like the big one

but are there other options i should look at

[wise goblet]

i know that besides sqlalchemy
exists Django ORM which I use
and also peewee and PonyORM

perhaps there is point to check them out
peewee and PonyORM can be possibly easier to use

the most important thing for me, is having the best migrate
I love Django ORM for that

[pulsar stag]

can i use django orm outside of a django project?

[burnt turret]

SQLAlchemy is pretty good

you can use alembic for migrations

[wise goblet]

yeah, it should be possible

[wise goblet]

I did not like alembic for some reasons
may be I did not use it right...
...but migrations were not so reliable

[burnt turret]

🤔 maybe. I haven't used it much but I didn't have any problems with it when I did

although I'd rather just not use an ORM any day :p

[wise goblet]

ORM is...

https://i1.wp.com/teacherluke.co.uk/wp-content/uploads/2015/11/palpatine-good.jpg

[burnt turret]

I personally prefer writing SQL directly, already familiar with it; why spend time learning how to use an ORM as well (disclaimer: that's my personal opinion)

[wise goblet]

in my opinion ORM is much more human readable
(Readability counts)
and easier to support

[pulsar stag]

More relevant to typed Lang’s like c# but ORMS provide compile time type checking and validations

That alone is big

[proven arrow]

Generally you would take into consideration the size of application as well, and what other people working on your project know. Also unless you know how to write good efficient sql or have dba’s on your project, orms generally would be the better option.

[proven arrow]

And yeah readability, plus development speed is a important thing.

Rather than wasting extra time writing efficient queries, let the orm do it for you.

We use orms in all our projects. Only write raw queries when we want something specific, or when performance is an issue.

[torn sphinx]

How does aiosqlite compare to postgresql for simple things like warns or being accessed frequently

Or even just using a json file for storing simple info

[narrow juniper]

Hello! I've just created my first Python package/library that combines the powers of SQLAlchemy and Pydantic, nothing special. I'm currently applying for an internship where I need to flex my skills in creating and uploading a package. Any feedback is greatly appreciated, especially if you have tips that have to do with setuptools or packaging in general.

Github: https://github.com/Wouterkoorn/sqlalchemy-pydantic-orm
(Github pages: https://wouterkoorn.github.io/sqlalchemy-pydantic-orm/)
(PyPI: https://pypi.org/project/sqlalchemy-pydantic-orm/)

[restive parrot]

Hello I am trying to solve a Question (sql) on hackerrank

https://www.hackerrank.com/challenges/interviews/problem?isFullScreen=true&h_r=next-challenge&h_v=zen

SET sql_mode='';
SELECT 
    co.*, 
    SUM(challenge.submissions) AS a,
    SUM(challenge.acc_submissions) AS b,
    SUM(challenge.total_views) AS c,
    SUM(challenge.total_unique_views) AS d
FROM Contests as co
INNER JOIN 
    (
        SELECT 
        contest_id, 
        View_Stats.total_views as total_views,
        View_Stats.total_unique_views as total_unique_views,
        Submission_Stats.total_submissions as submissions,
        Submission_Stats.total_accepted_submissions as acc_submissions
        
        FROM Colleges
    INNER JOIN Challenges ON Challenges.college_id = Colleges.college_id
    INNER JOIN View_Stats ON View_Stats.challenge_id = Challenges.challenge_id
    INNER JOIN Submission_Stats ON Submission_Stats.challenge_id = Challenges.challenge_id
    )challenge ON challenge.contest_id = co.contest_id
GROUP BY contest_id
HAVING a + b + c + d > 0
ORDER BY contest_id

this is My code

I can't understand why this is wrong

https://pastebin.com/azDP8usq
this code however works (took from discussions section)

i am using MYsql

thanks In advanced :)

[bright hound]

@burnt turret

def get_data_keys(data): #recursion would make this complete
    unpacked_keys = []
    for key in data:
        if isinstance(data[key], list): #If it's a list (list of dictionaries in this case)
            for item in data[key]: # for each item in the list
                if isinstance(item, dict):
                    for x in item: #for each key in the dictionary
                        if x not in unpacked_keys:
                            unpacked_keys.append(str(x))
        else:
            unpacked_keys.append(key) 
    return unpacked_keys

This is currently tailored to my specific use case but it would be easy to adapt to be more general

[rugged magnet]

hello guys i currently need help about optimizing my mysql program in python, now i have many clients running the program all at once on the same machine but everytime the program is run i need to iterate to an entire 4k row db which take up a whole lot of RAM, now if i try to make the program do sql queries per row it will overload the server CPU, is there any way i can make it so that multiple processes will either have one shared memory or mysql not taking all cpu during queries?

[fathom flume]

Are there any good tutorials for manipulating sql db's with python

[fathom flume]

GUYS ^^^^

[bitter bone]

https://realpython.com/python-sql-libraries/

And yeah even this is good, https://www.sqlitetutorial.net/sqlite-getting-started/

[fathom flume]

ok thxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

[torn sphinx]

hey, can someone help me with sqlalchemy?

[jagged cove]

hey guys

it's been a little while cause i've gotten smart enough to solve my own problems but i'm back now

def user_in_users(id):

    q = f"""SELECT EXISTS(SELECT 1 FROM users WHERE id={str(id)} LIMIT 1)"""

    print(c.fetchone())

    if c.fetchone():
        return True

    else:
        return False

this is returning 'False' despite the fact that there is a record of the user in the database

although i just checked it out and it does seem a bit strange (the id particularly)

k so i checked and for some reason it's logging the wrong id

which is why it never finds the correct one back

my_id = 667437184936050708
record = 2147483647

no clue why this is happening

my code is ```py
if db.user_in_users(message.author.id):
pass
else:
db.insert_into_users_status(message.author.id, 'pending')

def insert_into_users_status(id, status):

    lstatus = ['pending','accepted','denied']

    date = str(datetime.utcnow()).split()[0]
    time = str(datetime.utcnow()).split()[1].split('.')[0]

    if not status.lower() in lstatus:
        raise AttributeError(f"'Status' must be in {lstatus}.")
        return

    q = f"""INSERT INTO users (id,status,join_date,join_time) VALUES({id},'{status.lower()}','{date}','{time}');"""

    c.execute(q)
    conn.commit()

[green cove]

Isn't the "double fetchone() call the issue here?

the first one yields the actual row, the other yields what, "next record" but there's no record left (LIMIT 1)

record = c.fetchone(); print(record); if record: return True

[jagged cove]

no i think i actually found the issue

this is due to my lack of intelligence or functioning brain

but i forgot to actually execute q

therefor, there's nothing to fetch and thus always None regardless of if a record exists

[snow niche]

can anyone pls tell me if im doing it correctly, i just learned it and it doesnt seem to work with discord command

async def initialize():
    await client.wait_until_ready()
    db = await aiosqlite.connect("DisabledCmds.db")
    await db.execute("CREATE TABLE IF NOT EXISTS guildData (guild_id int, command text")
    await db.commit()

client.loop.create_task(initialize())
client.run('token')
asyncio.run(initialize.db.close())

# toggle command
@client.command()
async def toggle(ctx, *, command):

    toggledb = await aiosqlite.connect('DisabledCmds.db')
    command = client.get_command(command)

    if command is None:
        await ctx.send("I can't find a command with that name!")

    elif ctx.command == command:
        await ctx.send("You cannot disable this command.")

    else:
        command.enabled = not command.enabled
        ternary = "enabled" if command.enabled else "disabled"

        await toggledb.execute("INSERT OR IGNORE INTO guildData (guild_id, command) VALUES (?,?)", (ctx.guild.id, command))
        await toggledb.commit()

        await ctx.send(f"I have {ternary} {command.qualified_name} for you!")

[lapis wind]

what do you mean 'doesn't seem to work'

errors?

[snow niche]

it doesnt store it

[snow niche]

i did get some actually

Task exception was never retrieved
future: <Task finished coro=<initialize() done, defined at c:/Users/Public/Documents/Vector bot/main.py:8> exception=OperationalError('incomplete input')>
Traceback (most recent call last):
  File "c:/Users/Public/Documents/Vector bot/main.py", line 11, in initialize
    await db.execute("CREATE TABLE IF NOT EXISTS guildData (guild_id int, command text")
  File "C:\Users\TOSHIBA\AppData\Local\Programs\Python\Python37\lib\site-packages\aiosqlite\core.py", line 184, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "C:\Users\TOSHIBA\AppData\Local\Programs\Python\Python37\lib\site-packages\aiosqlite\core.py", line 129, in _execute
    return await future
  File "C:\Users\TOSHIBA\AppData\Local\Programs\Python\Python37\lib\site-packages\aiosqlite\core.py", line 102, in run
    result = function()
sqlite3.OperationalError: incomplete input

[lapis wind]

I've never used aiosqlite or sqlite before, but looks like you didn't put in all the info you needed to

[snow niche]

yea

[lapis wind]

look at the docs to make sure you're putting all the parameters

[snow niche]

@wide jolt do u know database? if so can u help me pls

[wide jolt]

why are you pinging me specifically?

actually i do see the issue though

@snow niche you're missing a closing parenthesis on this line:

await db.execute("CREATE TABLE IF NOT EXISTS guildData (guild_id int, command text")

in the sql string specifically

honestly though, don't ping staff members (or anyone for that matter) randomly for help in the future please

[glossy flume]

I have a data set like so. My task is to find all case numbers that involve "minor" string in the description column

i ran this

but im getting a blank table. There should be at least one

List all case numbers for crimes involving minors?

where am i going wrong?

the data type of DESCRIPTION is VARCHAR if that helps

[raw saffron]

use ILIKE for case insensitive

%minor% if it's a part of a bigger string

[glossy flume]

SQL0104N An unexpected token "ILIKE" was found following "TA WHERE DESCRIPTION". Expected tokens may include: "IN". SQLSTATE=42601 SQLCODE=-104

[raw saffron]

what DBMS are you using?

[glossy flume]

ibm_db

[raw saffron]

you have to lowercase the string then probably

WHERE LCASE(DESCRIPTION) LIKE '%minor%'

[glossy flume]

OMG

[raw saffron]

or just make sure the string you are searching is the correct case

[glossy flume]

BEAUTIFUL