databases

[glossy perch]

,settings welcome message agdgsdg

[grim vault]

First you had INSERT/UPDATE now you have no cursor for the result you check and want to DELETE a row which does not exists in the first place? What exactly do you want to happen and when?

[autumn condor]

I have a table called groups in postgres and every group name is not unique

I've to create unique join links for every group

using the PK the sequence would make the link abc.com/group/32

[glossy perch]

ok so i set a welcome channel and a welcome message , but when the owner doesn’t want it anymore , i want to add a cmd where it removes it

[keen rock]

        ad_messages_2 = self.bot.db.Ad_Messages.find()
        for each_message in ad_messages_2:
            message_dict[each_message["ad_name"]] = ["ad_message"]
        print(ad_messages)

for each_message in ad_messages_2: TypeError: 'AsyncIOMotorCursor' object is not iterable
There are stuff in the database
But I don't know why the object isn't iterable

[tropic dagger]

Hello, how do I retrieve a bytea field with Postgres using Django's ORM? Do i have to use a Binary field?

[wise goblet]

Use inspect db command

It will auto create ORM models for all tables in the targeted db

[surreal meadow]

I am a bit good in Python, Now what should I learn for Databases
there are a lot that's why am confused:
MySQL
SQL
SQLIGHT
PostgreSQL
etc....
what should I learn??

[wise goblet]

Sqlite is very often used in the beginning
Then postgresql as main database for all types of tasks

After that people usually learn mongodb at the moment. As nosql choice

[pure sleet]

learn sql first

you can use sqlite if you want to put what you learn into action

as suggested above

[wise goblet]

Uh yeah. I missed to see sql in your choice fields

SQL language should be learned first.
Then it can be complemented with ORM language

[surreal meadow]

what's an ORM language, like Python?

[wise goblet]

Basically yeah
ORM is a... thing allowing to work with database from normal programming language, having database described in your code as classes

Quite making things easier
Especially with migrating tools

[surreal meadow]

ooh
so steps are:

1. learn SQL
2. learn SQLIGHT
3. postgresqll

I am already doing general programmes in Python, just basic input output codes

[wise goblet]

SQLalchemy library allows ORM approach in python

With addition of Alembic to make auto migrations

[surreal meadow]

where should I add SQLalchemy in the steps

[wise goblet]

As third or fourth.

Better fourth

[surreal meadow]

ay,
I appreciate the help
It's more clear now on what should I do in order , makes stuff easier when planned

[wise goblet]

👍

[tropic dagger]

In Django's shell?

Nvm I found how: python manage.py inspectdb > models.py

[topaz wharf]

What should I use instead of MongoDB with dpy?

[brazen charm]

Postgres

[topaz wharf]

hmm

[pliant robin]

SQL.execute(f'ALTER TABLE Account RENAME COLUMN diamond TO gem')

Error: sqlite3.OperationalError: near "COLUMN": syntax error

[brazen charm]

do you actually have a column called diamond

[uncut smelt]

MongoDB
I am querying a collection with ref.aggregate([{"$set": {"coordinates": "test" }}]) however it does not add the coordinates field to the DB but I can see the coordinates field if I print the returned cursor as a list. What's going on here?

[torn sphinx]

nono what, where

where do i find this book

[wise goblet]

https://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905
pretty much anywhere

in the last year they made even about Machine Learning

Crazy, huh?

[torn sphinx]

damn

now this i can read

[wise goblet]

Neural nets for little kids, woohoo)

[torn sphinx]

Manga isnt for kinds tho?

[wise goblet]

ermm.. hmm...

Well, it is a really beginner friendly material anyway

[torn sphinx]

Would you like me to suggest some seinen manga?

[torn sphinx]

Hmm okay

[wise goblet]

Well, sure

if this particular manga has any suggestive magerial... it is not intentionally in my opinion

Japaneese people are just over open about it

So some level of it would be anywhere in any manga

But in general those manga guides are kid friendly ones

[torn sphinx]

The tokyo ghoul manga was pretty good

[wise goblet]

i tried watching it, it was really bad one

[torn sphinx]

the *** scenes were better in the manga

[torn sphinx]

you didnt like tokyo ghoul?

Maybe you started in the wrong order

[wise goblet]

it had too many snots in it. with a lot of crying / denial and e.t.c.

[torn sphinx]

well heres a list of

some 'kids' manga that you were talking about

https://myanimelist.net/anime/934/Higurashi_no_Naku_Koro_ni
https://myanimelist.net/anime/22535/Kiseijuu__Sei_no_Kakuritsu
https://myanimelist.net/anime/339/Serial_Experiments_Lain
https://myanimelist.net/anime/10620/Mirai_Nikki_TV

But we should probably take this to OT

hello

[torn sphinx]

I personally, woudnt want my child reading these 😔

And then theres doujinshi

[pliant robin]

i do

[wise goblet]

I watched https://myanimelist.net/anime/22535/Kiseijuu__Sei_no_Kakuritsu

it is absolutely awesome one)

I liked that

Guilty Crown is not bad too

Code Geass

[brazen charm]

!ot 👏 This probably wants to go to ot

[delicate field]

Off-topic channels

There are three off-topic channels:
• #ot0-fear-of-python
• #ot1-this-regex-is-impossible
• #ot2-the-original-pubsta

Their names change randomly every 24 hours, but you can always find them under the OFF-TOPIC/GENERAL category in the channel list.

Please read our off-topic etiquette before participating in conversations.

[pliant robin]

@brazen charm

it says syntax error

[brazen charm]

can you show your full code?

oh

this might be because of the sqlite version python uses

1 sec

[pliant robin]

it is from my bot's db, i just wanna rename a field

[brazen charm]

can you do print(sqlite3.sqlite_version) and tell me what it says

[pliant robin]

3.22.0

[brazen charm]

okay

so you cant rename columns like that

the RENAME keyword was added in 3.25

[pliant robin]

Ong

[brazen charm]

you basically have to create a temporary table

copy the table data to the temp table

drop the old table

re-make the old table with the renamed column

transfer data back

delete temp table

or well

just rename the temp table to the old table name

[pliant robin]

uh

i might just edit the file in db browser??

[brazen charm]

if db browser does it automatically for you then stonks indeed

otherwise it might just expect rename to exist

you are on a reasonably old version of sqlite though

what os r u on?

[pliant robin]

windows rn

[brazen charm]

yikes

[pliant robin]

Works!

[brazen charm]

nice

[pliant robin]

i run the same command on db browser lol

[brazen charm]

it'll likely do the temp table stuff for you for older versions

[pliant robin]

noted!

[torn sphinx]

hello

i need help

[pliant robin]

@brazen charm i had another question

[pliant robin]

sup?

[torn sphinx]

sup

any replacements for import mysql.connector

because this one is filled with problems

[brazen charm]

Err not really

what issues are you getting

[toxic frigate]

i am looking for database migration from mysql to oracle

somebody out there who knows?

[torn sphinx]

where statement isn't returning anything

[brazen charm]

thats not an issue with the connector

whats your SQL?

[torn sphinx]

SELECT * FROM passwd WHERE `host` = 'paypal';

when i execute that from shell it returns values

but from python no luck

[brazen charm]

well what's your python code

[torn sphinx]

well

i already fixed it

[toxic frigate]

anybody knows how to migrate mysql to oracle

??

[mystic vale]

Hey guys!

I need in the cloud DB hosting. For 1 short script which was written using MySQL.connector.

I need to get a host. And after I would like to create a user and make some requests.

Probably somebody can recommend me.

I need in the host.

[toxic frigate]

i think nobody has done mysql to oracle migration before

[austere portal]

cursor.execute("SELECT * FROM table_name WHERE Id=1;")
qs = cursor.fetchone()
print(qs)
> (1, 'test')```How can I convert the query set into a `dict` like `{column_name: value}` in my case ```py
{'id': 1, name: 'test'}```

[harsh pulsar]

what database library? the db-api specifies a cursor.description attribute that has column names in it https://www.python.org/dev/peps/pep-0249/#description. some libraries also let you customize how the "row" object is created, e.g. https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.row_factory

[austere portal]

The data base library is psycopg2

[harsh pulsar]

the general solution that should work with all db-api/pep-249 compliant libraries is:

cursor.execute("SELECT * FROM table_name WHERE Id=1;")
column_names = [desc[0] for desc in cursor.description]
qs = dict(zip(column_names, cursor.fetchone()))

in psycopg2 you can write desc.name instead of desc[0] as per https://www.psycopg.org/docs/extensions.html?highlight=description#psycopg2.extensions.Column

you could also make your own cursor subclass to always implement this behavior https://www.psycopg.org/docs/advanced.html#subclassing-cursor

[austere portal]

Thanks

[harsh pulsar]

note that this doesn't handle the case where 0 rows are returned... you'll need to check for None first

[austere portal]

Thanks, I'll keep that in mind

[verbal island]

Can someone here please teach me how to use MongoDB database with my discord bot code

[calm prawn]

We use ? to send vaiable to in sqlite3 query. But What do we use in postgresql

??

[jaunty galleon]

data = await conn.fetch('''SELECT * FROM some_table WHERE id = ($1) AND name = ($2)''', 1, "Bartick Maiti")

These are the placeholders(at least for asyncpg)

[rigid crystal]

Hello

(Sqlalchemy Question)

I want to know if there is a Sqlalchemy command which allowed me to insert a new entry (Table => two columns) but at the same time, return value indicating if that pair already existed in DB? (And even if it's existed, I went in to be inserted too)
Note: using constraint is not sufficient since it will raise an error without inserting that value again, but I want that pair to be duplicated in the DB.

Btw: I can type one comment to insert and another one for query(.filter), but I'm looking for a better method if available.

Thank you

[cedar pagoda]

How do I insert a list like this: ['hi 1', 'hi 2', 'hi 3'] in python with sqlite?

[torn sphinx]

Hi pls help me with C#

[harsh pulsar]

use json.dumps before saving to the database, then use json.loads to read the data back into python

sqlite has a native json type, json1, and you can use various json functions to process the data in sql queries https://www.sqlite.org/json1.html

[little quiver]

I need some help with connecting remotely to a postgresql server. I changed the ph_hba.conf file to allow all connections and the postgresql.conf to listen to all addresses but it still wont work. Can someone help?

[zealous parcel]

Hi, How to make the connection in mysql have timeout

[delicate field]

Hey @dense barn!

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

[dense barn]

Am i doing anything wrong? It says the interaction failed. Also the result prints None.
https://paste.pythondiscord.com/subazibigi.py

[dense barn]

nvm

i think i got it

[wise goblet]

I can say that it exists in django ORM at least
Class.objects.get_or_create, update_or_create and e.t.c.

[steep wasp]

java is better!

admins

i'm saying this to you

[sick salmon]

Is anyone able to help with a mongodb query?

i believe elemmatch should be returning only the object with the symbol matching

but why is it returning the whole thing

[torn sphinx]

hey, is it possible to print all database connections

with sqlalchemy

[rigid crystal]

Thank you ,
This is really helpful😁

[granite latch]

guys can you suggest a yt tutorial or website wherin i can get complete info on decomposition of relations(Normalisation) in database management system?

[ripe matrix]

I have my connection pool setup as:
bot.db = bot.loop.run_until_complete(asyncpg.create_pool(url)), it works perfectly fine. But when I moved my url to .env it suddenly displays ValueError: invalid DSN: scheme is expected to be either "postgresql" or "postgres", got ''

Any clue?

[proven arrow]

The error gives the clue @ripe matrix

Your getting empty string back from env

So make sure your loading it properly.

[ripe matrix]

I see, I'm not sure if I'm loading it right or not

bot.db = bot.loop.run_until_complete(asyncpg.create_pool(DB_URL))```

In my .env I have DATABASE_URL = user="postgres", password="a", host="127.0.0.1", database="dbname"

[torn sphinx]

How to connect to my 🖥 DB Table using my mother's 🖥

[granite latch]

do ping me here if u get em

[proven arrow]

You can use this library to read from env file https://pypi.org/project/python-dotenv/

Also its better to store your variables separately as opposed to a single value. Then build the connection url in your py file

[proven arrow]

The book by CJ date should cover it if you want the theory side. #databases message

For videos you might just have to browse and find one that you can understand.

[granite latch]

hello

its present in another book

could you please provide link for that?

Forms and All That Jazz, which is a companion to the present book─see Appendix G)```

this is what is mentioned in that book

[torn sphinx]

maybe this is not the place, but should DAO classes be table dependant?

i mean, if i got say a relation X to Y, id suppose i need a DAO for X, a DAO for Y, but also a DAO for X related to Y?

[proven arrow]

What about holidays?

[slow pebble]

Dm me

[proven arrow]

Depends how the relation is. For example let’s say you have a Roles table and Users table. The roles are assigned when the user is created. So I would just add it to the user dao.

[torn sphinx]

but say i got a user and tags tables, when i tag an user, i have to relate the tag to it, so in that case the relationship gets formed by the tag dao, but if say i delete that user and the tag ends up with no relations, i want to delete that tag

where does that piece of logic go in the whole structure?

clearly it should not be in the user dao logic, but how far up?

it seems to me that if i don't have a user-tags dao i would be handling database logic in the api level

but now again, having a user-tags dao seems like im exposing db implementation

in the same sense, imagine if i delete a tag i also want to delete all users associated with it

so in that case, i would need all daos to know about all the db implementation, right?

[proven arrow]

Not all the db, just the tables it needs to manipulate.

What’s the relationship type anyways?

[torn sphinx]

sorry, let me rephrase

theres 8 tables: categories, tags, users, lists, and (fk -> fk) users-to-categories, tags-to-categories, tags-to-users, lists-to-users

some deletions are on the db level, if you delete an user it deletes that relation, that kind of thing

but i also want to, say i delete tag X, i want to delete all users associated to it if they are not associated to other tags

i also want to delete tag X if its not associated to anything, say i delete all users associated to it, it should go too

i want to have the possibility to use different dbs, so im implementing an abstraction layer between the controller logic and the actual db logic

but im having issues understanding what goes where

[jade swan]

mutes.update_one({"_id": ctx.guild.id}, {
                         "$unset": {str(member.id): ""}})

This is how I delete a field from a documentation right?

[proven arrow]

@torn sphinx sorry had to go in a meeting so couldn’t respond. But the goal of what your doing is for easier maintainability. Pick whatever you find easier to read and maintain.

I would make a separate layer that deletes the tags, and just call the method in when you delete the user.

[errant arch]

Problem MySQL Databse in Python
Code in Python:

cursor.execute("SELECT * FROM tblUsers WHERE userID=%s", (1)) # userID is an interger so userID='%s' does not work
cursor.fetchall() #returns None, although there is an entry where userID == 1
# I think you have to put something different than %s but after some google searching I didn't find anything

[proven arrow]

@errant arch can you try this? I added a conma after the 1 so it’s a tuple.

cursor.execute("SELECT * FROM tblUsers WHERE userID=%s", (1,))
cursor.fetchall()

[grim vault]

also cursor.fetchone() should be fine.

[errant arch]

Works thx 😄 Thought it would be a little bit more complicated 😂

[proven arrow]

Yeah the parameters should be passed as an iterable. So a tuple or list or dict works.

[torn sphinx]

no problem

My question is the following:

# Controller level
def delete_user(user_id: str):
  self.user_dao.delete_user(user_id)

class UserDaoSQL(UserDAO):
  def delete_user(user_id: str):
    # sqlite logic to delete user

If a tag ends up orphan, i should delete the tag, so somebody here needs to query the Tags table, and delete any orphan tags

so

# Controller level
def delete_user(user_id: str):
  self.user_dao.delete_user(user_id)
  self.tag_dao.handle_orphans()

or

class UserDaoSQL(UserDAO):
  def delete_user(user_id: str):
    # sqlite logic to delete user
    self.handle_orphan_tags()

or

# Controller level
def delete_user(user_id: str):
  self.user_dao.delete_user(user_id)
  self.user_to_tag_dao.handle_orphans()

class UserToTagDAOSQL(UserToTagDAO):
  def handle_orphan_tags():
    # handle orphan tags
  def handle_orphan_users():
    # handle orphan users

I assume the second option is wrong, cause its having to do Tag things, and the class is explicitly handling User stuff. So looking at 1 and 3, number 1 makes more sense, right?

[dreamy flint]

in the WHERE Clause in PostgreSQL it tells me to do this (https://www.postgresqltutorial.com/postgresql-json/)

WHERE guild_info -> 'guild_id' = 837241873277386763;

But then it says

ERROR:  operator does not exist: json = bigint
LINE 3: WHERE guild_info -> 'guild_id' = 837241873277386763;
                                       ^

And of course I can't compare json to bigint but then how do i get the integer of the key "guild_id"

nvm

[zealous parcel]

Hi, why the call does not close when it has finished a work?

Knowing that I put the close code

cursor.close()   

[torn sphinx]

hello i have this problem with lists its not related to databases but thought i ask here

i webscraped some stuff and i made a loop for it now i want to add the things i scraped to a list. but it doesnt work

i put list.append(variable)

in the loop

it works outside the loop but it only adds the last thing that was scraped

[dreamy flint]

ain't there a difference between cursor.close and exit or logout or something? idk i don't got these problems with my database.

[hexed estuary]

are you perhaps closing the cursor but not the database connection (assuming you create one every time)?

[austere portal]

How the code going to be if I am using cursor.fetchall?

And is there a Connection.row_factory in psycopg2 as well?

[grim zephyr]

Guys how is PostgreSQL better than MySQL

[hexed estuary]

if this:
https://developer.okta.com/blog/2019/07/19/mysql-vs-postgres
is to be believed, it's slightly nicer in many ways

[tepid walrus]

I think alot of it comes down to support

mysql is owned by Oracle who already have their own db paid service

and they are not updating like they used to

while post gre is entirely open and have more updates

[austere portal]

Does psycopg2 have a built in Cursor subclass which implements the behaviour which I need.
I am too lazy to make my own Cursor subclass

[glacial spindle]

['product', 'size']

[
    ('product1', '8'), 
    ('product1', '8'), 
    ('product1', '9'), 
    ('product1', '10'), 
    ('product2', '5Y'), 
    ('product2', '6Y')
]

If I have a SQLite3 database like this, is there a select query that can get all unique sizes per unique product

like return [('product1', '8', '9', '10'), ('product2', '5Y', '6Y')]

[proven arrow]

@glacial spindle ```sql
SELECT DISTINCT product, size
FROM products
GROUP BY product, size

That will give as individual rows, but if you want it like [('product1', '8', '9', '10'), ('product2', '5Y', '6Y')] then you can use:

SELECT product, GROUP_CONCAT(size, ', ')
FROM (SELECT DISTINCT product, size
        FROM products
        GROUP BY product, size) t1
GROUP BY product; 

[dusky gazelle]

anyone got any experience with flyway ?

i have someone who's having a weird issue

[pure mortar]

is it worth learning a nosql database

like mongodb

[proven arrow]

If you need to use it then sure.

Can majority of problems be solved without a nosql database? Yes.

[gusty mulch]

Relational databases are the most applicable to most situations when it comes to discord bots right? Which provider would be the best to go for when starting out with a DB for a bot? I was thinking of running my bot on a raspberry pi initially as I have a few lying around, does that affect my DB choices?

[forest swallow]

So if i am storing the user message count for each day should i insert a new row for each message for each user for each server? or is there any better way?

[brave bridge]

sqlite3 already comes with Python, you can try it out: https://aiosqlite.omnilib.dev/en/stable/ (for async applications)

Postgres/MySQL probably could run on a raspberry pi, but that would probably take up lots of resources. You could consider using a database-as-a-service service like ElephantSQL, but that comes with a big latency cost, and they don't have 100% uptime, of course.

[torn sphinx]

How do I create a list in mongo db? I need to remove the "" and replace with [] for dpy

I need it to be in the form prefix : ["??" , "m/"]

@brave bridge Not sure if I should ping or if its against the rules, If it is please forgive me this time

[brave bridge]

@torn sphinx Unless you're in conversation with someone or you want to address someone personally, don't ping anyone

I don't know anything about mongo

[flint imp]

as i said

[torn sphinx]

Ok

[flint imp]

use unset and set operator

[torn sphinx]

I have no idea how to do that 😔

[flint imp]

https://docs.mongodb.com/manual/reference/operator/update/unset/

[torn sphinx]

So I need a shell?

[flint imp]

r u in compass

[torn sphinx]

I am in the cluster collection

[flint imp]

if you have an interface for your mongo db you can easily do that without typing the query

[torn sphinx]

I have no idea on how to use that but ill try learning

[edgy wolf]

I want to state in the beginning that this is a mix python and postgresql question.
I am currently using tortoise-orm that handles all the sql related stuff for me but today I got stuck while inserting into a JSONB field.
Here's my table:

class PointsInfo(models.Model):
    class Meta:
        table = "pt_info"

    id = fields.BigIntField(pk=True, index=True)
    guild_id = fields.BigIntField(index=True)
    kill_points = fields.IntField(default=1)
    posi_points = fields.JSONField(default=dict)
    default_format = fields.IntField(default=1)
    data: fields.ManyToManyRelation["PointsTable"] = fields.ManyToManyField("models.PointsTable", index=True)


class PointsTable(models.Model):
    class Meta:
        table = "pt_data"

    id = fields.BigIntField(pk=True, index=True)
    points_table = fields.JSONField()
    created_by = fields.DatetimeField()
    created_at = fields.DatetimeField(auto_now=True, index=True)
    edited_at = fields.DatetimeField(null=True)
    channel_id = fields.BigIntField(null=True)
    message_id = fields.BigIntField(null=True)

I want to insert a row in pt_data table and then add that row to data column in pt_info table

_dict = {'quotient': [1, 20, 20, 40], 'butterfly': [2, 14, 14, 28], '4pandas': [3, 10, 8, 18], 'kite': [4, 10, 5, 15]}

table = await PointsTable.create(points_table=_dict, created_by= 123456789)
points= await PointsInfo.get(id=3)
await points.data.add(table)

but I get an error while doing this:

File "/home/deadshot/softs/softs/total-quotient/Quotient-Bot/.venv/lib/python3.8/site-packages/tortoise/models.py", line 655, in __init__
    for key in meta.fields.difference(self._set_kwargs(kwargs)):
  File "/home/deadshot/softs/softs/total-quotient/Quotient-Bot/.venv/lib/python3.8/site-packages/tortoise/models.py", line 682, in _set_kwargs
    setattr(self, key, field_object.to_python_value(value))
OSError: [Errno 75] Value too large for defined data type

idk why python couldn't parse that dict

so I want to do this with raw SQL, since I have doing this with tortoise-orm , I don't have much experience with raw SQL , I know the basics but this is advanced for me.

What should I do here? can you help me with writing the raw SQL query for what I want to achieve here?

Thanks.

[torn sphinx]

Will compass work?

[rancid silo]

Anyone work with hiveql or pyspark? I am wondering any alternative to running recursive query since its currently not supported?

[torn sphinx]

hello

import sqlite3

conn = sqlite3.connect("wordbase.db")
conn.execute("CREATE TABLE IF NOT EXISTS words(word)")


def inserting(data):
    conn = sqlite3.connect("wordbase.db")
    cursor = conn.cursor()
    print(data)
    cursor.execute("INSERT INTO words VALUES (?)", (data,))
    conn.commit()
    conn.close()


def deleting(counter3):
    conn = sqlite3.connect("wordbase.db")
    cursor = conn.cursor()
    counter3 += 1
    print("count: ", counter3)
    counter = str(counter3)
    cursor.execute(f"DELETE FROM words WHERE rowid = {counter}")
    conn.commit()
    conn.close()
    print(getting_data())


def getting_data():
    conn = sqlite3.connect("wordbase.db")
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM words")
    conn.commit()
    return cursor.fetchall()

I'm trying to delete data from database but nothing is deleted at all

please help me out

[old geyser]

Ok I don't know ask to get helped at help general i mean sey: can someone help me at #databases

[proven arrow]

@torn sphinx The way you are deleting is not the best way to delete data as you can't guarantee your deleting what you want.

But your issue is probably because you increment counter by 1 so it wont find the row

[torn sphinx]

what is the solution?

[proven arrow]

Try without counter3 += 1

[torn sphinx]

but I need this one

Okey let me try then

[proven arrow]

You can delete by the word, cursor.execute("DELETE FROM words WHERE word = ?", ('the-word',))

[torn sphinx]

still same

[proven arrow]

Works for me, #bot-commands message

[torn sphinx]

def deleting_var():
        data_number = len(getting_data())

        counter3 = 0

        list_data = listbox.get(ANCHOR)
        listbox.delete(ANCHOR)
        list_data = ''.join(list_data)

        counter3 = 0
        converted_data = list()
        word_list = getting_data()

        while counter3 != data_number:
            converting = ''.join(word_list[counter3])
            converted_data.insert(counter3, converting)

            if(converted_data[counter3] == list_data):
                print(converted_data[counter3] + '<---->' + list_data)
                deleting(counter3)
                print("breaking...x")
                counter3 = 0
                break

            else:
                counter3 += 1```

I use the function here

that's why I need rowid 🙂

[proven arrow]

But the rowid you are giving to the query doesn't exist. So make sure you pass the correct value then.

[torn sphinx]

mmm

[cedar saddle]

When I create a table and insert a value in pycharm using sqlite3, why does the database file look like this ?

at the end of the file the values are inserted, but i don't understand where all the null is from

or why

[brave bridge]

SQLite doesn't store its database in a human-readable format, you're not supposed to read it in plain text. There are various programs for viewing the contents, though.

[cedar saddle]

Aah

[brave bridge]

e.g. https://sqlitebrowser.org/

[torn sphinx]

@proven arrow would you come to #help-corn

?

I'm waiting for you

[cedar saddle]

thx, going to download now and see if i can make it work 😄

[civic cargo]

pycharm itself can read sqlite!

actually that may only be the pro version

[proven arrow]

Is that with the database tool it has built in or just by opening the file.

[cedar saddle]

ye i read it's with the pro version actually somewhere, but thought it waas outdated

[cedar saddle]

is unittesting for creation of database, tables etc necessary using sqlite?

[proven arrow]

No kind of testing is necessary for anything. But you can write a test for it if you need or want full coverage.

[cedar saddle]

class TestCreateTable(TestCase):
    def test_create_table(self):
        database_test = "store_hidden_string.db"

        conn = hidden_string.create_database_connection(database_test)
        test1 = hidden_string.create_table(conn)
        test1.execute("""CREATE TABLE IF NOT EXISTS
                testing(id INTEGER PRIMARY KEY AUTOINCREMENT, num INTEGER, string TEXT)""")
        self.Equal(test1, """CREATE TABLE IF NOT EXISTS
                testing(id INTEGER PRIMARY KEY AUTOINCREMENT, num INTEGER, string TEXT)""")

i'm not sure say if i wanted to tst the creation of a table in a db

but i thought this would work

doesn't

[proven arrow]

What do you mean your not sure if you want to test? Either you do or you don’t?

For testing you can use the :memory: database

And you can use a statement like this to check if it exists,
select exists(select name from sqlite_master where type='table' AND name=?), (‘table_name’,)

It will return 1 or 0.

[cold basin]

Print the information of client_master, product_master, sales_order table in the
following format for all the records :-
{cust_name} nas placed order {order no} on {s_order_date}.

write sql query for this?

Help

[proven arrow]

Help people help you, by providing more details to your question with some code/data set.

[cold basin]

Actually I have created two tables ( client_master and sales order tables)
And I want to write above mentioned text into sql query

[mild dove]

?

[cold basin]

Column cust_name belong to client_master table and columns order no and s_order_date belongs to productmaster table

[proven arrow]

Show your table structure

[cold basin]

Print the information of client_master, product_master, sales_order table in the
following format for all the records :-
{cust_name} nas placed order {order no} on {s_order_date}.

The above query belongs to these tables

[proven arrow]

select 
  client_master.name, sales_order.s_order_no, sales_order.s_order_date 
from 
  client_master 
inner join 
  sales_order on client_master.client_no = sales_order.client_no;

@cold basin That gives you the data you need. You can use the data to format it.

And next time make sure to send a proper screenshot rather than an image which is very hard to read. 😉 👍

[woeful jolt]

How to prevent sqlite3 input sql injection

[crystal compass]

mongodb doesn't create the database/ collection

@client.event
async def on_ready():
    client.mongo = motor.motor_asyncio.AsyncIOMotorClient(str(My database url))
    client.db = client.mongo["Discord"]
    client.warns = Document(client.db, "warns")

[proven arrow]

#databases message

[cold basin]

Ok will this work for now

[woeful jolt]

like
somebody register name ' ' or "" to crack the python

thanks

[cedar cargo]

  File "c:\Users\mazze\OneDrive\Documenti\GitHub\Crypto\src\variabili.py", line 20, in <module>
    data = sqlite3.connect("../FILES/database.db")
sqlite3.OperationalError: unable to open database file
``` but why? 😢

[proven arrow]

@cedar cargo File doesn't exist at that location you gave for the file

[cedar cargo]

but the file exist

[proven arrow]

Then it would be silly for sqlite to give that error.

[cedar cargo]

yea but the file exist 😂

look at the file there

[proven arrow]

./FILES/database.db

[woeful jolt]

sql = ("INSERT INTO CLAN_USER_INFO(guild_name, guild_id, user_name, user_id, clan_name, clan_role_id, clan_id, roles_in_clan, join_clan_date, clan_invites, clan_user_exp) VALUES(?,?,?,?,?,?,?,?,?,?,?)")
val = (ctx.guild.name, ctx.guild.id, user_name, ctx.author.id, clan_name_dp, clan_role_id, clan_id, 'owner', date_now, 0, 0)
print(val)
cursor.execute(sql, val)
db.commit()

Do you think this code is prevent from sql injection ?

i upgrade it

[cedar cargo]

no the python file is in a src faulder

[proven arrow]

yes its fine

[woeful jolt]

Oh jesusthanks

[cedar cargo]

maybe the database is locked?

[austere portal]

Thanks

[dreamy flint]

yo @proven arrow You seem bigbrain, #help-cheese
You do postgresql right? hehe

[keen tartan]

is there a library or package channel?

[harsh pulsar]

the docs will explain what fetchall returns, and you can use that information accordingly

i don't know if it has row_factory, i didn't see it in the docs, so i assume it doesn't

[thin sandal]

yo

[torn sphinx]

for sqlalchemy when defining a model, is it possible to use a class method to query and do something with the model itself?

@classmethod
def test(cls):
    test = session.query(cls).first()
    print(f'{test.attribute} {test.attribute2}')
    test.some_method()

above doesn't work but I could imagine its something close to that

any nudge in the right direction would be appreciated

[dusk oriole]

guys anyone know how can I reduce the attack in kdd nsl to 2% ?

[dusk oriole]

more explnation : I don't want to drop all the rows that are labeled as attacks I only want to reduce their number, I can do it manually but it will take a lot of time, if anyone knows a better way to do it please , and thank u

[wraith wraith]

I am a beginner in Python and I am planning to build a desktop application with python and it basically should read RFID tag and run a query with the RFID code. The problem is I don't know how to get the RFID card code in my python code. Any idea how to do that? tnx!

[austere portal]

cursor.execute("INSERT INTO table_name (col1, col2, col3) VALUES(%s, %s, %s)", (val1, val2, val3))```So say the value of `val3` is "default" then the query is going to be `INSERT INTO table_name (col1, col2, col3) VALUES(10, 20, 'default')` but I don't want the string "default" I want it to be the default type in postgresql

[austere portal]

I tried doing this:py cursor.execute("INSERT INTO table_name (col1, col2) VALUES(%s, %s)", (val1, val2)) But I get an psycopg2.errors.NotNullViolation error

[proven arrow]

@austere portal Does the column have a default value?

[austere portal]

yes

[proven arrow]

Show the create table statement

or table structure

[cedar pagoda]

846710584824561674 856143191220682753 9c328aaffeca49c1baed20077b5f65e8
Error
_ClientEventTask exception was never retrieved
future: <ClientEventTask state=finished event=on_raw_reaction_add coro=<bound method Modmail.on_raw_reaction_add of <lib.cogs.modmail.Modmail object at 0x000001E0A503F0A0>> exception=TypeError('cannot unpack non-iterable NoneType object')>
Traceback (most recent call last):
  File "C:\Users\Lasse\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\client.py", line 348, in _run_event
    await self.on_error(event_name, *args, **kwargs)
  File "C:\Users\Lasse\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\Lasse\Documents\Discord Bots\Multi Server Bot\lib\cogs\modmail.py", line 214, in on_raw_reaction_add
    Submit_Channel_ID, Questions = db.execute("SELECT submit_channel, Questions FROM modmail WHERE GuildID = ? AND MessageID = ? AND Emoji = ?", payload.guild_id, payload.message_id, payload.emoji.name)
TypeError: cannot unpack non-iterable NoneType object

    @Cog.listener()
    async def on_raw_reaction_add(self, payload):
        try:
            print(payload.guild_id, payload.message_id, payload.emoji.name)
            Submit_Channel_ID, Questions = db.execute("SELECT submit_channel, Questions FROM modmail WHERE GuildID = ? AND MessageID = ? AND Emoji = ?", payload.guild_id, payload.message_id, payload.emoji.name)

        except:
            print("Error")
            raise
            return

        if Emoji == None:
            return

        else:
            message = await self.bot.get_channel(payload.channel_id).fetch_message(payload.message_id)

            user = await bot.fetch_user(payload.user_id)

            await message.remove_reaction(payload.emoji, user)

Why do I get this error? The values in the database and the from the code (payload.guild_id, payload.message_id, payload.emoji.name) are the same

[austere portal]

CREATE TABLE IF NOT EXISTS Post (
    id SERIAL,
    title VARCHAR(50) NOT NULL,
    body TEXT NOT NULL,
    date TIMESTAMP NOT NULL
    )```

[proven arrow]

i dont see a default value

So of course the error is expected

[austere portal]

wait i remember adding a default value

Silly me

Anyway thanks for helping

[ionic pecan]

which database library is this?

[cedar pagoda]

sqlite

[ionic pecan]

.execute never returns anything

you need to call .fetchone afterwards

which will return None for no match, and the row (as tuples) for a match

[cedar pagoda]

ohhhh yessss thank you xDD didnt reconized that I used execute xD

[torn sphinx]

When someone type Good morning it sends Good morning ctx.author.mention at the channel that save in the db
How to do it?

[gaunt garden]

is there a way to get a list of pending queries on a connection in aiosqlite? I'm trying to grab that info if say my bot disconnects/turns off unexpectedly.

[torn sphinx]

hey, im getting this error, does someone know how i can fix this?

scheduler        | sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30.00 (Background on this erro
r at: http://sqlalche.me/e/14/3o7r)

i already added this:

app = Flask(__name__)
app.config.from_object('config.DevelopmentConfig')
# app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///database.sqlite'
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://root:root@db/main'

app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {
    "pool_pre_ping": True,
    "pool_recycle": 300,
    "connect_args": {
        "connect_timeout": 30
    }
}

CORS(app)

db = SQLAlchemy(app)

but it still didnt fix it

[magic gulch]

Hey Guys,

i am running into an issue with an delete function if my project... it simply does nothing.

    output = {}
    mydb = mysql.connector.connect(
      host=,
      user=,
      password=,
      database=
    )
    cursor = mydb.cursor()

    sql = f'DELETE FROM {table} WHERE `{select_collumn}` = {select_data};'
    print(sql)
    try:
        cursor.execute(sql)
        return True

    except:
        return False

Even after i tried to execute the sql statement written by hand

        cursor.execute("DELETE FROM km_import WHERE `killmail_id` = 93274148;")
        return True```
It returns only true but don't delete the row in my table.
I tried to run the statement by hand via console with the same user and it works fine.

No exception btw.

[proven arrow]

@magic gulch Can you add mydb.commit() after the execute and try again

[magic gulch]

i hate myself sometimes

[torn sphinx]

could anyone help me with this?

class Macro(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    fps = db.Column(db.Integer, nullable=False)
    path_to_file = db.Column(db.String(50), nullable=False)
    type = db.Column(db.String(10), nullable=False)
    created = db.Column(db.DateTime, default=datetime.utcnow())

    def __repr__(self):
        return 'name: %r' % self.id

it doesnt see the column or the integer

please ping me if you are willing to help

[upper delta]

Hello can someone help me build a modlogs command for discord.py?

I have sqlite as database but I don't know how to add all moderation data in it

Can no one help me?

[torn sphinx]

use sqlalchemy for that

look up a tutorial for it

[upper delta]

Can you not help me with it? I can give you nitro for it if you'd like

[torn sphinx]

you just need to insert data in a sqlite db?

[upper delta]

As well as make a modlogs command and make moderation commands like warn, ban, kick and mute to dump data in it

[torn sphinx]

i can help you later with sqlalchemy im not home rn

[upper delta]

Ok thanks alot please dm me whenever you're back home

[proven arrow]

@upper delta If your new to sql or databases in general you should avoid jumping straight to an ORM, as Lvcas suggests. Instead try to go through the basics first.

The world wont end no, but by jumping straight to an ORM like sqlalchemy, you will just end up crippling yourself from understanding the language and best practices that go along with it.

[uneven holly]

I am reading psycopg2 documentation and trying to understand the % placeholder.
Is the placeholders only purpose for the VALUES and to prevent SQL injections?

[proven arrow]

Yes

[uneven holly]

Thank you 🙂

[brave bridge]

If I have a nested structure, like

CREATE TABLE IF NOT EXISTS problems (
    id INTEGER NOT NULL PRIMARY KEY ASC,
    title TEXT NOT NULL,
    desc TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS problem_tests (
    problem_id INTEGER REFERENCES problems(id) ON DELETE CASCADE,
    input TEXT NOT NULL,
    output TEXT NOT NULL,
    UNIQUE (problem_id, input, output)
);
```, on a server-based database (Postgres/MySQL), what would be a proper way to select all problems together with their test cases?
And how would I do it with a single query in SQLite (which doesn't have arrays)?

[proven arrow]

@brave bridge an inner join

[brave bridge]

So I'll get a result like this

id  title      desc  input  output
1   FizzBuzz   ...   1      1
1   FizzBuzz   ...   1      2
1   FizzBuzz   ...   3      Fizz
1   FizzBuzz   ...   5      Buzz
2   Factorial  ...   3      6
3   NoTestsYet ...   NULL   NULL
``` and then I'll need to loop over the results in some clever way?

[proven arrow]

Your table has not null for input output

But yeah would be like that

[brave bridge]

But if there are no problems, an inner join will put NULLs, no?

[grim vault]

No, that would be an outer join.

[brave bridge]

ah

So if I need to include problems with no tests as well, I'll need an outer join?

[proven arrow]

Left outer join yeah

I’m trying to find an article I linked once here that explains it well

Can’t seem to find the link

[brave bridge]

won't this approach waste lots of bandwidth if I have a large amount of data in desc?

[proven arrow]

Databases are made to join

The link #databases message

[brave bridge]

e.g., if the metadata (title, description etc.) takes 5KB of space, and I have 100 tests for each problem, fetching 100 problems will transfer at least 50 MB of data

[proven arrow]

Oh I misread

[grim vault]

Depending on the context, I would use two selects. First the proplems and then another select for the test.

[proven arrow]

The bandwidth and data you pull also depends on what your storing

What is this logs?

[brave bridge]

I'm storing programming problems. The description can contain fancy markup like tables, equations etc., so it might take on the order of kilobytes

[proven arrow]

Well depends what you want to optimize it for. There’s many questions to ask for when performance tuning

Sometimes it may not be worth it and you just let it be

But you can paginate the results if you like

[brave bridge]

I was thinking of only including the metadata in the first row, and then padding things with NULLs.

id  title      desc  input  output
1   FizzBuzz   ...   NULL   NULL
1   NULL       NULL  1      2
1   NULL       NULL  3      Fizz
1   NULL       NULL  5      Buzz
2   Factorial  ...   NULL   NULL
2   NULL       NULL  3      6
3   NoTestsYet ...   NULL   NULL
``` but this sounds like a hack

Or, as Berndulas suggested, first fetch all metadata rows, then fetch all test rows.

[brave bridge]

I think I'll go with this, because I have more one-to-many relations that I'll need to query

thank you for the help @grim vault @proven arrow

or maybe I'll give up and store each problem as a JSON field

[trim lintel]

How to count positive or negative differences between two tables which is exactly the same?

@brave bridge do you know ^

[brave bridge]

I don't understand your question

[trim lintel]

Ok sorry

Wait

Basically I gave 2 tables exactly same like column name etc.
Only difference is the content in the tables is different.

So I want to get a query to get the difference between two tables for each cell

If you know then ping me please 😬

[brave bridge]

But how do you determine which rows to subtract?

[trim lintel]

Table B - Table A

[brave bridge]

Oh, you want to find the difference, as in, which rows were added and which are missing?

[trim lintel]

No so say the first row first column value of table B is 10. And the same cell in Table A value is 3. The difference is 7.

I want the 7

Like this for each cell

[brave bridge]

How do you determine the order of columns?

is there some primary key?

[trim lintel]

It’s the same, structure for both tables

Consider it data sets for two different points in time

[brave bridge]

AFAIK, SQL doesn't guarantee any particular order. You have to sort by some criteria (primary key, date, etc.) if you want to get a consistent order.

[trim lintel]

Yeah there is primary key. For each row

[brave bridge]

Would something like this work? ```sql
SELECT t1.foo - t2.foo as foo, t1.bar - t2.bar as bar, ...
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id

[trim lintel]

Hmm not sure I’ll try that later

But maybe not because pk might be different

As it’s not a foreign key

[brave bridge]

what do you mean by that?

[trim lintel]

That query assumes each row has the same PK value

But it might not so it won’t join all rows

[brave bridge]

right, because otherwise you don't know which row in t1 corresponds to which row in t2.

[trim lintel]

Yeah exactly which is why I was looking for cell by cell approach

I’ll think of it later now, thanks for the reply but still

[brave bridge]

That might depend on the database you're using. For example, SQLite has a special rowid column in each table (unless you tell it not to use it).

But why are you changing the value of the primary key? That sounds like something you shouldn't do.

[trim lintel]

Value can change depending on transactions

It’s controlled by db

[brave bridge]

What kind of data do you store?

[trim lintel]

Just events

It’s a fucking pain if you ask me

If it was up to me it would not be like this but here we are, and I have to deal with it ahah 😂

Well I’ll work on it later now

[south crest]

Anyone familiar with the Datstax Cassandra python ORM?

Basically, I want to create a multi table queryset. The table schemas are the same except for the table name. How can this best be achieved?

[torn sphinx]

!code

[velvet flume]

I am using pymongo
I want to list all collections inside a db
Currently I am using list_collection_names to list them
But they are coming in random order
I want them to sort according to the last modified

If it helps I have one document called meta inside each collection which has timestamp in it

class JSONEncoder(json.JSONEncoder):
    def default(self, o):
        if isinstance(o, ObjectId):
            return str(o)
        return json.JSONEncoder.default(self, o)


collections = database.list_collection_names()
all_bots = []
for i in collections:
    i_one = database[i].find_one({"meta": True})
    all_bots.append(i_one)
all_bots_json = json.loads(JSONEncoder().encode(all_bots))

[sick salmon]

no idea how your documents are defined

but you can just find and orderby

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

db.collection.find( { $query: {}, $orderby: { age : -1 } } )

[velvet flume]

Document is simple as

{
   "_id": {
      "$oid": "60cc2663c9be7504ed3a1e79"
   },
   "meta": true,
   "modified_timestamp": 1623991907.88602,
   "published": false,
}

@sick salmon

[sick salmon]

ok well

just use orderby

[velvet flume]

Works

What if I want to have two sort conditions?
First
If it's published it should be sorted
And then the rest
Everything according to modified_timestamp?

[sick salmon]

$sort

[velvet flume]

Currently
I just sorted according to timestamp but is there any easy way to include according to published True also?

collections = database.list_collection_names()
all_bots = []
for i in collections:
    i_one = database[i].find_one({"meta": True})
    all_bots.append(i_one)
all_bots_json = json.loads(JSONEncoder().encode(all_bots))

all_bots_json.sort(key=itemgetter('modified_timestamp'), reverse=True)
pprint(all_bots_json)

[sick salmon]

ok first of all

why are you looping through and find_one

just do find

[velvet flume]

is find faster?

[sick salmon]

your method is inefficient

the database has a built in method to find multiple

[velvet flume]

Ah, thought so

I only have one document with meta: True

rest are of different structure
Hence, I used find_one

[sick salmon]

if you want to sort by multiple

you need to use $sort with pymongo aggregate

idk the syntax

https://docs.mongodb.com/manual/reference/operator/aggregation/sort/

i think you can just use sort

i dont use pymongo

.sort([("field1",pymongo.ASCENDING), ("field2",pymongo.DESCENDING)])

[velvet flume]

Hmmm
Cannot figure out the query

[sick salmon]

https://stackoverflow.com/questions/8109122/how-to-sort-mongodb-with-pymongo

[velvet flume]

According to my understanding this would sort documents inside collection right?

I have one document inside every collection
That needs to be sorted

Correct me if I'm wrong

[sick salmon]

huh

but you said

your document is

{
   "_id": {
      "$oid": "60cc2663c9be7504ed3a1e79"
   },
   "meta": true,
   "modified_timestamp": 1623991907.88602,
   "published": false,
}

what is there to sort

[velvet flume]

Yes

[sick salmon]

o.o

[velvet flume]

This document I have inside each collection

So now I want to sort that

If I do list_collection_names it shows random order

[sick salmon]

seems convoluted way of ordering db

you want to extract documents from each collection and then sort by two different keys

yes?

[velvet flume]

so collection could be

collection_name = mango
onedocument_inmango = {
   "_id": {
      "$oid": "60cc2663c9be7504ed3a1e79"
   },
   "meta": true,
   "modified_timestamp": 1623991907.88602,
   "published": false,
}

#Collection2
collection_name = apple
one_document_inapple = {
   "_id": {
      "$oid": "60cc2663c9be7504ed3a1e79"
   },
   "meta": true,
   "modified_timestamp": 1624261776.6484642,
   "published": false,
}

#Collection3
collection_name = banana
one_document_inbanana = {
   "_id": {
      "$oid": "60cc2663c9be7504ed3a1e79"
   },
   "meta": true,
   "modified_timestamp": 1621991907.88602,
   "published": true,
}

So here it should show as
banana, apple, mango (banana because published rest based on timestamp)

[sick salmon]

i have no idea about this one

[median wave]

anyone knows sqlite

@bot.command()
async def change(ctx, new : str=None):
    if new is None:
        db = await aiosqlite.connect("prefix.db")
        async with db.execute("SELECT prefix FROM prefixes WHERE guild_id = ?", (ctx.guild.id)) as cursor:
            data = await cursor.fetchone()
            await ctx.send(data)

    else:
        db = await aiosqlite.connect("prefix.db")
        await db.execute("UPDATE prefixes SET prefix = ? WHERE guild_id = ?", (new, ctx.guild.id))
        await db.commit()```

```s\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: parameters are of unsupported type```

[velvet flume]

okay, did you understand the concern though?

[sick salmon]

yes

[fringe tiger]

that error seems to come from dpy iteself,post the full traceback. Also I think the data is going to be a tuple of one tuple thus you'd need to get string representation of that for your ctx.send

[median wave]

well what i want is for it to show the prefix from database in that part if new is None

[fringe tiger]

please re-read my message

[median wave]

oh sorry, its a long error though

    ret = await coro(*args, **kwargs)
  File "c:\Users\tenuk\OneDrive\Documents\Bot folder\test\botcode.py", line 101, in change
    async with db.execute("SELECT prefix FROM prefixes WHERE guild_id = ?", (ctx.guild.id)) as cursor:
  File "C:\Users\tenuk\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\aiosqlite\context.py", line 41, in __aenter__
    self._obj = await self._coro
  File "C:\Users\tenuk\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\aiosqlite\core.py", line 184, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "C:\Users\tenuk\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\aiosqlite\core.py", line 129, in _execute
    return await future
  File "C:\Users\tenuk\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\aiosqlite\core.py", line 102, in run
    result = function()
ValueError: parameters are of unsupported type

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

Traceback (most recent call last):
  File "C:\Users\tenuk\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\tenuk\OneDrive\Documents\Bot folder\test\botcode.py", line 123, in on_command_error
    raise error
  File "C:\Users\tenuk\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\tenuk\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\tenuk\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: parameters are of unsupported type```

[fringe tiger]

ah I see

you forgot the ,

this needs to be a tuple (ctx.guild.id) just add , so it looks like (ctx.guild.id,)

[median wave]

ohhhh tysm

okay so now i had made all the stuff for my custom prefix but i do not have it as the prefix for the bot is what's in the database. Could anyone help me with that?

i have the change prefix and get_prefix

[fringe tiger]

out of topic but you can pass a callable to Bot as command_prefix kwarg https://discordpy.readthedocs.io/en/stable/ext/commands/api.html?highlight=command_prefix#discord.ext.commands.Bot.command_prefix and that callable will return the prefix based on passed guild. For additional questions you can use #discord-bots

[tame nova]

cursor.execute(f"SELECT name FROM applications WHERE guild_id = '{ctx.message.guild.id}' and name = '{name.content}'")
sqlite3.OperationalError: no such table: applications```

how do i fix this?

[alpine zinc]

create the table

[grim vault]

and use bindings parameter

cursor.execute("SELECT name FROM applications WHERE guild_id = ? and name = ?", (ctx.message.guild.id, name.content))```

[bitter bone]

Need some help; #tools-and-devops message

[tropic dagger]

I wonder how you can store an image with values outside of 0 and 255 as a byte array. i've seen methods with cv2 and pillow, but I just want the values in case I want to display them on a heatmap later.

Turning them into a PNG compresses the data and I don't want any loss.

So I was wondering if there was another way to extract data from a np array in a bytestring and store it in a db for later

[cunning yew]

This is what happens when you do it like in this picture

different messages

I want to put them all in the same message

but how?

[cerulean python]

Concatenate a string and take the sending out of the for loop

[cunning yew]

can you give example ?

[unkempt prism]

messages = []
for i in data:
    ID = "blah"
    # ...
    messages.append(f"{ID}")
await ctx.send('\n'.join(messages))

[cerulean python]

Basically this. I would have actually concatenate because you need to watch out for the 2k character limit

[cerulean python]

(basically send the message in the for loop if the concatenated string would be over 2k adding the next row)

[cunning yew]

i will do page system

[cerulean python]

[median wave]

When making a date, which data type would be best?

[cunning yew]

you can use datetime

[median wave]

where is that?

sorry I'm new to this

[cunning yew]

like timestamp ?

[median wave]

yeah i want something like that

[cunning yew]

first: from datetime import datetime

,

here

[median wave]

done

[median wave]

how do you put that field in the database ?

[cunning yew]

1minute

[median wave]

im manually making the table here, not sure which data type to use if i were to do date

[cunning yew]

no

wait

        db = sqlite3.connect("infs.db")
        cursor = db.cursor()
        type = ("Warning")
        cursor.execute(f"INSERT INTO inf (User,UserID,Moderator,ModeratorID,Timestamp,Type,Reason) VALUES(?,?,?,?,?,?,?)", (user.name, user.id, ctx.author.name,ctx.author.id,timestamp,type,reason))
        await ctx.send(f"{ctx.author.mention} tarafından {user.name}#{user.discriminator} kullanıcısı **{reason} ** sebebinden dolayı `Uyarıldı`")
        db.commit()
        cursor.close()
        db.close()```

if you dont create table

you must create table

this is insert

sorry my english is bad

[median wave]

Yeah but over here in the screenshot i sent above, for date which data type should i use?

INTEGER or NUMERIC etc.

[cunning yew]

i make this

[median wave]

oh text

okay thanks!

[median wave]

Uh sorry but one more question, how do you select multiple items from the columms?

[cunning yew]

1m

@median wave like this ?

cursor.execute(f"""SELECT ID,User,Moderator,Timestamp,Type,Reason from inf WHERE UserID = {member.id} OR ModeratorID = {member.id} ORDER BY ID DESC""")

i made it

[median wave]

Is it not possible to split my reason, moderatorid and timestamp here?

    @commands.command()
    async def warnings(self, ctx, member : discord.Member):
        db = await aiosqlite.connect("modlogs.db")
        async with db.execute("SELECT reason,moderatorid, timestamp FROM warnings WHERE guildid = ? AND userid = ?", (ctx.guild.id, member.id)) as cursor:
            data = await cursor.fetchone()

[proven arrow]

@median wave what do you mean split?

[median wave]

like i want to be able to put them on seperate lines

[proven arrow]

With Sql or for using the data in python?

[median wave]

Right now this is what I have

[proven arrow]

Yeah so just index it to get the value you want

It’s a tuple, https://www.w3schools.com/python/python_tuples.asp

[median wave]

I want it to write Reason:{reason}
ID: {ID}
Date: {date}

[proven arrow]

Hmm but not really a database question. You should know how to manipulate and use basic structures.

If not, know is a good time to brush up on the knowledge, so you can use it later on as well 😉

[median wave]

well are there any docs?

[lyric cliff]

The execute function returns a tuple here's some examples https://www.w3schools.com/python/python_tuples.asp
use indexing.

[median wave]

Sorry I can't seem to find the execute function

oh i got it

Thanks

[winter harbor]

SO I asked this earlier and got one answer... asked something similar elsewhere and got another... gonna ask one more time because the conclusion I've reached is insane.

I have a project that entails wanting to allow multiple users the same fine-grained access control over a filesystem that the operating system provides. Problem is, I don't want to use operating-system user accounts. In Linux terms, I basically want to have a userspace program managing a folder wherein all the files within are owned/accessed by 'fake uids/gids' not found in /etc/passwd.

It seems that, so far, as I'm operating in Python, the only practical way to do this would be to use a SQLite3 file as a 'virtual filesystem' that is accessed by a custom FS implemented in PyFilesystem2.
is there something out there I'm not aware of which is appropriate for this situation
it seems kinda ridiculous that a situation like this is so unusual

[harsh pulsar]

@winter harbor do you need to store the files themselves in sqlite? or can you keep a "registry" of files in sqlite while keeping the files themselves on the filesystem, perhaps in some kind of obfuscated form?

maybe the files can be kept in a big flat directory with random strings for names

[winter harbor]

that's not at all a bad idea.

[harsh pulsar]

and the actual file names with their paths and permissions are stored in the sqlite database

a dedicated user could certainly use strings, grep, etc. to figure out which files are which, but if you don't have to worry about such users then you don't have a problem

plus if you store them in sqlite they could just read the sqlite database anyway

[winter harbor]

what I'm more argh about is the fact that there doesn't seem to be any obvious existing way to deal with this issue

[harsh pulsar]

i think it's an issue that mostly only shows up if you're developing a cloud storage service with shared access to files

[winter harbor]

so you're basically saying use sqlite3 for the filetable but dump the actual file binary content to the filesystem.

which is sensible.

[harsh pulsar]

that's my off-the-cuff suggestion having never worked on such a system before 😛

[winter harbor]

I actually found an 'sqlfs' based on sqlite3 which uses FUSE, but... the problem is that FUSE doesn't do what I want it to do at all so it's not much use to me

[frigid glen]

Hey All, I thought I would share an easy solution for the common issue of "shared state among workers". This is normally solved by DB access or Redis service . Introducing, easycaching, https://github.com/codemation/easycaching - persistent & shared caching, without the complexity. Hope it helps, glad to hear what you think.

[torn sphinx]

does someone know a fix to close idle txn connections with sqlalchemy?

[autumn epoch]

How would I save a dictionary to a postgres db with asyncpg?

[pliant tiger]

Hello, I have a discord bot that stores everything in a SQLite Database, and have multiple tables for things from different cogs like automoderation, levels, currency, etc.

Should I make multiple databases? or can I continue using different tables

?

[harsh pulsar]

save as jsonb

[autumn epoch]

How do I do that?

Nvm found it

[grim zephyr]

I have some doubts about MySQL like whenever I will try to connect with it using mysql_connector will it automatically switch on its server or I am supposed to switch on the server manually using xampp

[proven arrow]

You will have to switch it on each time through xampp.

Unless you installed it different and it starts as a service

[grim zephyr]

@proven arrow

Any db that works for 24*7

[brittle mountain]

Hi, i have this code

import urllib.request as urlrequest
import xml.etree.ElementTree as ET
from datetime import datetime, timedelta

today = datetime.today()
yesterday_till_high_noon = today + timedelta(hours=12)
datum_vertretungsplan = yesterday_till_high_noon.strftime("%Y%m%d")


def hol_vertretungsplan():
    url_vertretungsplan = f'https://www.reclamgymnasium.de/mobil/mobdaten/PlanKl' + \
        datum_vertretungsplan + '.xml'
    print(url_vertretungsplan)
    try:
        xml_vertretungsplan = ET.ElementTree(
            file=urlrequest.urlopen(url_vertretungsplan))
    except ET.ParseError:
        print('Es ist noch kein Vertretungsplan für dieses Datum verfügbar.')
        exit()
    return xml_vertretungsplan

vp = hol_vertretungsplan().getroot()
meine_klasse = '9.2'
plan_datum = vp.find('Kopf/DatumPlan').text
klassenplan = vp.find(f'Klassen/Kl[Kurz="{meine_klasse}"]/Pl')
#faecher = klassenplan.findall('Fa')
print(f'Vertretungsplan für {plan_datum}')
for stunde in klassenplan.iter('Std'):
    fach = stunde.find('Fa').text
    lehrer = stunde.find('Le').text
    raum = stunde.find('Ra').text
    print(fach, lehrer, raum)```
if i print 
fach, lehrer, raum
 it it works but if i want to use it any where else it dosent work

[proven arrow]

Download mysql or configure xampp to start it automatically @grim zephyr

[grim zephyr]

Will it start it automatically when I will switch on my pc until I switch it off

[proven arrow]

Yes as a system service

[grim zephyr]

But I want it to be online 24/7 cause I need a db for my discord bot

Any other db that is online for 24/7

[proven arrow]

Either you run your pc 24/7 or find an online hosting provider or free service.

[grim zephyr]

I will host my bot on heroku but where to host a db?

@proven arrow

I will host my bot on heroku but where to host a db?

[proven arrow]

🤷‍♂️ Host it wherever you want.

Usually easiest to host it on same server

[grim zephyr]

Suggest me some site to host a db cause i don't know much about db

[proven arrow]

https://www.digitalocean.com/

[grim zephyr]

Any other site that is not paid

[proven arrow]

I dont know you'll have to look around. Just search the web you'll find some.

[grim zephyr]

Maybe mongodb is a 24*7 db

[indigo flare]

I am wanting to store a value from 0.1 to 2.0 (inclusive) in a postgres column, looking at the real it seems like a bit much, what should I store this kind of thing as

if anyone is interested, it is a speed multiplier for audio

[grim zephyr]

Hey guys is there any db that iss online for 24/7

@indigo flare

Bro I need help

[indigo flare]

yep, any db you host yourself on a VPS :)

have you seen how cheap hetzner is, like seriously, run your bot and DB on it and you are fine

[grim zephyr]

I don't want to host my db myself I want someone to host it cause I cannot switch on my pc for 24*7

[indigo flare]

pay someone then, if you don't want to do something yourself, you pay someone else to do it

that is how everything works

[grim zephyr]

I want a MySQL db which will be online for 24*7 but how about a mongodb maybe it's a 24 / 7 db

[harsh pulsar]

all major databases are designed to be running 24/7

[cold quail]

 async def _execute(self, failure_retry: int, cursor: mysql.Cursor, sql, val=None):
        for x in range(failure_retry):
            try:
                if val:
                    await cursor.execute(sql, val)
                else:
                    await cursor.execute(sql)
                return cursor
            except OperationalError:
                if x < failure_retry - 1:
                    continue
                else:
                    raise OperationalError

Is this a way to execute something or isn't this working?

[harsh pulsar]

@cold quail don't re-use cursors between queries

[fluid scarab]

mongodb has a free tier. only 512 mb of storage though

[stray moss]

hi, I have a dict inside a class that I would like to store in mongodb. But the dict sometimes changes, is there a way I can make it so every time the dict updates, it gets put into mongodb

[cold quail]

ok thanks

[quiet quarry]

hello guys does any have some experiences with connection pooling?

my pool is getting exhausted

I am closing the connections but the problem still occurs

[dense barn]

can we add images in sql3 db ?

[delicate field]

@slender narwhal Please don't try to ping @everyone or @here. Your message has been removed. If you believe this was a mistake, please let staff know!

[slender narwhal]

sory miss

rly

[harsh pulsar]

Did you read the error message? It suggests a specific solution

[harsh pulsar]

You can save them as binary blobs

[balmy jay]

i already figured it out

[dense barn]

lemme try

still returns it as a link, is that what its supposed to do? @harsh pulsar

[harsh pulsar]

A link? Huh?

[grim zephyr]

which is the best python module to connect with a mysql db for read and write operation

[delicate field]

Hey @loud crane!

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

[loud crane]

https://paste.pythondiscord.com/rusafiheze.sql

i get this error when I try to connect to mongodb in windows

but works fine in linux

ping me to reply

[grim zephyr]

Anyone here who can tell me how can I make my bot read my SQL database

[wraith shell]

Hello,
Could anyone please guide me where I can find some basic knowledge of regression testing in ETL and reporting environment.
I guess what I am trying to say is, how I make sure that some user is not messing someone else’s work while creating reports in the database.

I was thinking that maybe we should follow some rules such as object naming convention so that no one deletes an already existing object .
But that still does not stop someone from doing DML actions in someone else’s table.

I am trying to implement a reporting environment with the help of Apache Airflow and trying to figure out how to keep things in order.

[grim zephyr]

@wraith shell do you know how to make my bot read a mysql db and write in it

[digital wharf]

Do you know sql firstly?

It's just a create table statement

Wouldn't wanna spoon-feed since you declined the example though heh : )

[grim zephyr]

i just want it in a big db for multiple server prefixes

[digital wharf]

https://www.w3schools.com/sql/sql_create_table.asp

[grim zephyr]

conn.execute('''CREATE TABLE prefix
         (guild_id INT PRIMAR KEY       NOT NULL,
        prefix varchar(255))'''
         );

[grim zephyr]

(2003, "Can't connect to MySQL server on '185.27.134.10'")

[median wave]

How do I get the laetst row content from a SQLITE table discord.py

[median wave]

In SQLITE, how do I get the last row content?
Like I want to make a case number command where each time a moderation command is used the case number increases by one
So I guess one way of doing that is checking what is the most recent case number and adding one to that

[oak carbon]

i want to do is insert some data to an table and return that that how can i do it?

[unkempt prism]

in postgres you can using the returning clause.

insert into my_table(a,b,c) values (1,2,3) returning *

Especially useful when you have automatically incremented ids, timestamps etc.

[oak carbon]

yh ty

[torn sphinx]

Just use auto increment

It increases by value each time automatically

[median wave]

    ret = await coro(*args, **kwargs)
  File "c:\Users\tenuk\OneDrive\Documents\Bot folder\test\botcode.py", line 167, in case
    async with db.execute("SELECT user_id, type, mod, reason, time FROM modlogs WHERE case = ?", (case,)) as cursor:
  File "C:\Users\tenuk\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\aiosqlite\context.py", line 41, in __aenter__
    self._obj = await self._coro
  File "C:\Users\tenuk\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\aiosqlite\core.py", line 184, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "C:\Users\tenuk\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\aiosqlite\core.py", line 129, in _execute
    return await future
  File "C:\Users\tenuk\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\aiosqlite\core.py", line 102, in run
    result = function()
sqlite3.OperationalError: near "=": syntax error

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

Traceback (most recent call last):
  File "C:\Users\tenuk\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\tenuk\OneDrive\Documents\Bot folder\test\botcode.py", line 232, in on_command_error
    raise error
  File "C:\Users\tenuk\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\tenuk\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\tenuk\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\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: OperationalError: near "=": syntax error```

@bot.command()
async def case(ctx, case : int):
    db = aiosqlite.connect("final.db")
    await db
    async with db.execute("SELECT user_id, type, mod, reason, time FROM modlogs WHERE case = ?", (case,)) as cursor:
        data = await cursor.fetchone()
        embed2 = discord.Embed(title=f"Case {case}", value=data, color=discord.Color.green())
        await ctx.send(embed=embed2) 

[grim vault]

case is an SQL keyword, use ":

async with db.execute('SELECT user_id, type, mod, reason, time FROM modlogs WHERE "case" = ?', (case,)) as cursor:```

[verbal island]

@keen rock hey you told me to ping you

[median wave]

oh, okay thanks
And why does my embed just make the title as Case 2 when I do (commmand prefix)case 2?

[keen rock]

Did you connect to the cloud db?

[verbal island]

No

[keen rock]

Finish the prompt then @verbal island

it should give you something like this: myclient = motor.motor_asyncio.AsyncIOMotorClient("localhost", 27017)

But for cloud and not localhost

[verbal island]

Wait btw does it mean that I’m hosting my bot cloud or locally or cloud/locally hosting my monodb database if that makes sense?

[grim vault]

Well, case(ctx, case : int) and you do (commmand prefix)case 2 which means that case = 2 and f"Case {case}" will be "Case 2" which is exactly what you get.

[oak carbon]

query = """INSERT INTO timer (event, extra, created, expires) VALUES ($1, $2::jsonb, $3, $4) RETURNING id"""
row = await self.bot.db.fetchrow(query, event, {'args': args, 'kwargs': kwargs}, event_time, when)

CREATE TABLE IF NOT EXISTS timer (
        id                                       SERIAL NOT NULL,
        event                                    VARCHAR,
        extra                                    JSONB,
        created                                  TIMESTAMP WITHOUT TIME ZONE,
        expires                                  TIMESTAMP WITHOUT TIME ZONE
    )

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: DataError: invalid input for query argument $2: {'args': [711043296025378856, 8493129529... (expected str, got dict)```

[keen rock]

I don't understand

[torn sphinx]

(expected str, got dict)

[oak carbon]

huh then show should i insert if i make it str

[keen rock]

How do you connect to your db

from the mongo db compass

[grim vault]

json.dumps({'args': args, 'kwargs': kwargs})

[oak carbon]

VALUES ($1, $2::jsonb, $3, $4)

wouldnt this do anything/make any diff?

[torn sphinx]

Yeah no point doing json.dumps because that just stores it as string.

[oak carbon]

so what should i do?

[torn sphinx]

There quite few things wrong

You need to fix them

You are doing insert with fetch

[oak carbon]

it will just return the row inserted

thats not the issue most probably

[oak carbon]

any other way?

[harsh pulsar]

depending on the database, dumping to "json string" might be the correct way to pass the data

this often is the case with postgres for example

and is definitely the case with sqlite

[grim vault]

sqlite doesn't have a JSONB datatype.

[harsh pulsar]

sqlite has json1

[oak carbon]

that dict contains datetime object also so, i get error

[harsh pulsar]

json itself doesn't have a "datetime" type. maybe postgres has some kind of support for timestamp data inside jsonb, but i doubt it. you might have to "dump" your data from a "dict of python stuff" to "a dict of basic types that are 1:1 with json"

e.g. by converting the datetime to rfc3339 format string

[oak carbon]

hmm sure but i have also seen a bot do the same thing and directly passing dict and it working for him

[white geode]

What are some high level steps to convert mongo db to postgres db?

[harsh pulsar]

if the dict values are just strings, ints, etc. then they don't need to do anything

[grim vault]

Or they have registered an adapter.

[oak carbon]

they have datetime object in it

[grim vault]

You can extend the json encoder.

[oak carbon]

how can i do it?

[grim vault]

eg, I'm using:

class SetJSONEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, set):
            return list(obj)
        if isinstance(obj, (Enum, Flag)):
            return obj.value
        return json.JSONEncoder.default(self, obj)

...
ins_column["value"] = json.dumps(value, cls=SetJSONEncoder)```

[harsh pulsar]

good point

[oak carbon]

what is Flag?

[grim vault]

It's just an example, you'll need datetime.datetime. Enum and Flag are from the enum module.

[blazing onyx]

hey i need a little help with MySQL, can i ask here?

[little pumice]

hello

i'm getting an error

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: InterfaceError: Error binding parameter 0 - probably unsupported type.

!code

this is my code of error :

@client.command()
async def addtoken(ctx, userid: int, maxuses: int):
    if ctx.author.id != 746302070201647115:
        await ctx.send("You don't have the permissions to use this command!")
    else:
        token = uuid4()
        conn = sqlite3.connect("db.db")
        c = conn.cursor()
        c.execute("INSERT INTO tokens VALUES(?,?,?,?,?)", (token,userid,0,maxuses,"True"))
        conn.commit()
        c.execute("SELECT FROM tokens WHERE token = ?", (token,))

        for row in c:
            tkn = row[0]
            uid = row[1]
            ues = row[2]
            mes = row[3]
            sts = row[4]

            await ctx.author.send(f"The '{tkn}', was successfully created with the following parameters:\n   User ID : {uid},\n   Uses : {ues}\n,   Max Uses : {mes}, Status : {sts}")

this is my table code :

c.execute("CREATE TABLE IF NOT EXISTS tokens(token TEXT PRIMARY KEY, userid INT, uses INT, maxuses INT, status TEXT)")

[harsh pulsar]

@little pumice uuid4 returns a UUID object, not a string. use the .hex attribute to get a text representation. see https://docs.python.org/3/library/uuid.html

[little pumice]

i did it

my problem was in token = uuid4(), i maked it str(uuid4)

[harsh pulsar]

don't do that, do token = uuid4().hex

token = str(uuid4) is very definitely not what you want

[grim vault]

You didn't get a syntax error for the missing * in c.execute("SELECT FROM tokens WHERE token = ?", (token,))?

[little pumice]

ok lemme test

[little pumice]

nope, but i fixed it

[harsh pulsar]

token = str(uuid4()) is the same as uuid4().hex but less obvious and therefore worse

[little pumice]

@harsh pulsar another question, how can i delete a row after a specific date ( automatically ) and this specific date it's from the same table, i missed it

[harsh pulsar]

WHERE?

DELETE FROM ... WHERE?

[little pumice]

("DELETE FROM tokens WHERE expiredate = ?", (date,)

something like that?

[gaunt meadow]

in the WHERE clause, how can i filter without the exact word

like there are ABC company, BCD school and CDE university
then i use ABC only to filter the choices

[little pumice]

@harsh pulsar but how can i make it an automatically process, everyday

like a background task

[harsh pulsar]

use the LIKE operator?

[gaunt meadow]

oh, thx

just looked up it

[mortal light]

I might have to add database to the project I have been working on. We have an aws account. What databases would be a good choice?

[teal rampart]

What's the best way to cache mongodb data?
I need to make the same call multiple times and it's slowing my application

[pure bough]

Hey, anyone experience with Alembic running in async loop? I'm aware of async template introuced recently, but my problem is that I have already an async loop created in my up, so I'm not able to create another one in env.py

[dense barn]

@client.command(aliases=["c"])
@commands.cooldown(1,1,commands.cooldowns.BucketType.user)
async def card(ctx):
    db = sqlite3.connect(db_path)
    cursor = db.cursor()
    cursor.execute("SELECT user_id FROM pokesets WHERE user_id = ?",(ctx.author.id,))
    result = cursor.fetchone()
    if result is None:
        await ctx.send(f"{ctx.author.mention}, please use the {prefix}lss (lets start sleevin) command before using any of the other commands, thanks!")
        return
    elif result is not None:
        def check(m):
            return m.author == ctx.author and m.channel == ctx.channel and m.content.lower() == "pb" or m.content.lower() == "gb" or m.content.lower() == "ub" or m.content.lower() == "mb"
        msg = await ctx.send(content="Type `pb`![pokeball](https://cdn.discordapp.com/emojis/856592973348995132.webp?size=128 "pokeball"),`gb`![greatball](https://cdn.discordapp.com/emojis/856592428374687775.webp?size=128 "greatball"),`ub`![ultraball](https://cdn.discordapp.com/emojis/856593002578313217.webp?size=128 "ultraball") or`mb`![masterball](https://cdn.discordapp.com/emojis/856593038439481364.webp?size=128 "masterball") to catch it!",embed=random.choice(pokes))
        reply = await client.wait_for("message",check=check)
        if reply.content.lower() == "pb":
            int = random.randint(1,2)
            if int == 1:
                embed = msg.embeds
                embed2 = msg.embeds[0].fields
                pc = embed2[0].value
                image = embed[0].image.url
                cursor.execute("INSERT INTO pokesets(pc,user_id) VALUES(?,?)",(pc,ctx.author.id,))
                cursor.execute("SELECT pb FROM pokesets WHERE user_id = ?",(ctx.author.id,))
                result2 = cursor.fetchone()
                print(result2)
                cursor.execute("UPDATE pokesets SET pb = ? WHERE user_id = ?",(-1*result2,ctx.author.id,))
                embed2=discord.Embed(description="The Pokemon was successfully caught with a ![pokeball](https://cdn.discordapp.com/emojis/856592973348995132.webp?size=128 "pokeball")`PokeBall!`")
                embed2.set_author(name=f"Congratulations, {ctx.author.name}!",icon_url="https://cdn.discordapp.com/attachments/856373686809788430/856619128726224916/671848138935500836.png")
                embed2.set_image(url=image)
                await msg.edit(content="You caught the pokemon with a ![pokeball](https://cdn.discordapp.com/emojis/856592973348995132.webp?size=128 "pokeball")`Pokeball`!",embed=embed2)
                await ctx.send(pc)
``` Im trying to multiply the result2 with -1 but it says this:
```py
Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\25dch\github\pythonbot\PokeSets.py", line 107, in card
    cursor.execute("UPDATE pokesets SET pb = ? WHERE user_id = ?",(-1*result2,ctx.author.id,))
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.
```i dont know if im doing anything wrong

[devout girder]

What's aiosqlite's equivalent to sync sqlite's cursor.affected_rows()?

[mental depot]

Can I get help saving this data to a sqlite db?

def home(request):
    url = f'https://newsapi.org/v2/everything?q=gaming&from= {DATE}&sortBy=popularity&language=en&apiKey={API_KEY}'
    response = requests.get(url)
    data = response.json()
    
    articles = data['articles']
    
    context ={
        'articles' : articles
    }

    return render(request, 'news/home.html', context)

[remote plinth]

umm

in sqlite we use ? than f-strings so.. what do we use in mysql

[torn sphinx]

dont go#help-pie

[harsh pulsar]

it depends on the specific database library, check their docs

[jaunty fiber]

hello!

someone here to answer a quick question about beautiful soup

?

[solid hatch]

hi i am quite lost

[thorn geode]

What with?

[grim vault]

result2 = cursor.fetchone() -> result2 will be a tuple, if you want to use the selected value you must use result2[0].

[dense barn]

yep, i got it thanks anyway

[mental depot]

NameError: name 'article' is not defined
Need some help with this

con = sqlite3.connect('news_api/artilces.db')
cur = con.cursor()
url = f'https://newsapi.org/v2/everything?q=gaming&from={DATE}&sortBy=popularity&language=en&apiKey={API_KEY}'
response = requests.get(url)
data = response.json()

o = json.dumps(data)
obj = json.loads(o)
for artilce in obj['articles']:
    print(artilce)
    cur.execute("Insert into Article values (?, ?, ?)", 
                (artilce['title'], artilce['publishedAt'], artilce['url']))
    con.commit()

[grim vault]

artilce != article check your naming.

[mental depot]

got it thank you

[dense barn]

@client.command()
async def stats(ctx):
    db = sqlite3.connect(db_path)
    cursor = db.cursor()
    cursor.execute("SELECT date FROM pokesets WHERE user_id = ?",(ctx.author.id,))
    result = cursor.fetchone()
    await ctx.send(result[0].strftime("%y-%d-%m"))
``` im trying to format the time it gets from the db as year-day-month but i get an error saying:
```py
gnoring exception in command None:
discord.ext.commands.errors.CommandNotFound: Command "s" is not found
Ignoring exception in command stats:
Traceback (most recent call last):
  File "C:\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\25dch\github\pythonbot\PokeSets.py", line 65, in stats
    await ctx.send(result[0].strftime("%y-%d-%m"))
AttributeError: 'str' object has no attribute 'strftime'

[grim vault]

sqlite does not have a date datatype, so your select will return a string which can't be formated with .strftime(). You'll need to convert it into a datetime object first with .strptime(...).

[dense barn]

ah

well

i tried that but it says this: i think the seconds is whats causing the error

Ignoring exception in command None:
discord.ext.commands.errors.CommandNotFound: Command "stast" is not found
Ignoring exception in command stats:
Traceback (most recent call last):
  File "C:\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\25dch\github\pythonbot\PokeSets.py", line 65, in stats
    dt = datetime.strptime(result[0], "%d/%m/%y %H:%M")
  File "C:\Python39\lib\_strptime.py", line 568, in _strptime_datetime
    tt, fraction, gmtoff_fraction = _strptime(data_string, format)
  File "C:\Python39\lib\_strptime.py", line 349, in _strptime
    raise ValueError("time data %r does not match format %r" %
ValueError: time data '2021-06-22 18:13:46.681220' does not match format '%y/%m/%d %H:%M:%S'

[grim vault]

'2021-06-22 18:13:46.681220' is format "%Y-%m-%d %H:%M:%S.%f"

[dense barn]

oh

dam

im new to formatting time, i should look at the datetime python docs

ah

i see how it works

ty

[sleek aspen]

Good night guys, i've recently entered this discord.
I really need some help i am having a lot of problems trying to do the command "pip install impyla" on my cmd is there anyone that can help me fix this?
i am doing this on my company computer and today i went to the company office for them to switch the computer and now im having a different kind of error.

[weak yoke]

That's blurry as. Can you make a textual paste

[sleek aspen]

c:\users\andrefcastro\appdata\local\programs\python\python39\include\pyconfig.h(59): fatal error C1083: Cannot open include file: 'io.h': No such file or directory

that's the error resume message

[torn sphinx]

try to update your python version or your pip version

[sleek aspen]

i did install the python 30 mins ago and i just did the pip update

😦 *

[weak yoke]

Like a text paste of the whole thing

[sleek aspen]

c:\users\andrefcastro\appdata\local\programs\python\python39\include\pyconfig.h(59): fatal error C1083: Cannot open include file: 'io.h': No such file or directory

[weak yoke]

The whole thing

https://www.lfd.uci.edu/~gohlke/pythonlibs/#bitarray I managed to squint at the text and found you need the binary version of bitarray

https://github.com/ilanschnell/bitarray/issues/63#issuecomment-694410506

[sleek aspen]

@weak yoke so i should try the pip install bitarray right?

First time using Python my bad guys

Recently changed from UiPath to this new project

@weak yoke got the same trying to do "pip install bitarray" c:\users\andrefcastro\appdata\local\programs\python\python39\include\pyconfig.h(59): fatal error C1083: Cannot open include file: 'io.h': No such file or directory

[weak yoke]

You need the binary wheel

Remember to always paste the whole output

The bit you pasted doesn't help debug the problem

[weak yoke]

Instructions are on the top of this page I linked you

[delicate field]

Hey @sleek aspen!

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

[weak yoke]

You can use https://paste.pythondiscord.com as the bot said

[sleek aspen]

oh ok

[weak yoke]

Did you click this URL?

[sleek aspen]

https://paste.pythondiscord.com/xilalacaqe.lua

Yes but the files i try to download are not runnable

they are .whl

[shell ocean]

read again

[shell ocean]

this

[weak yoke]

Right so you see right at the top of the page,

Use pip version 19.2 or newer to install the downloaded .whl files. This page is not a pip package index.

[shell ocean]

it's literally in the first 10 lines...

[weak yoke]

Which is a link to https://pip.pypa.io/en/latest/user_guide/#installing-from-wheels

[sleek aspen]

oh ok thanks for the command !

ill give a try , thanks for trying to help !!

i will try this one :
bitarray-2.1.3-pp37-pypy37_pp73-win_amd64

[weak yoke]

Are you using pypy3.7?

It looks like you're using cPython 3.9

Which will need bitarray-2.1.3-cp39-cp39-win_amd64.whl

[sleek aspen]

ERROR: bitarray-2.1.3-pp37-pypy37_pp73-win_amd64.whl is not a supported wheel on this platform.

oh okok

So this should be the command right:
py -m pip wheel --wheel-dir=\C:\Users\andrefcastro\Desktop -r bitarray-2.1.3-cp39-cp39-win32.whl

after wheel installation

[weak yoke]

Don't you want the 64 bit one?

[sleek aspen]

Sure, can i go to the voice chat please?

if possible of course

oh all voice chats on this discord are voice disabled 😦

C:\Users\andrefcastro\Documents>py -m pip install bitarray-2.1.3-cp39-cp39-win_amd64.whl
Processing c:\users\andrefcastro\documents\bitarray-2.1.3-cp39-cp39-win_amd64.whl
Installing collected packages: bitarray
Successfully installed bitarray-2.1.3

dudes

I love this discord

U guys might even saved me from being fired xD

[weak yoke]

They should just put the wheels on pypi ffs

You should subscribe to that GitHub issue so you know if they ever do

Or raise an issue in pyimpala to let them know about the fork with wheels

[sleek aspen]

are those unnoficial modules?

Yes i will try to make this resolution public and let the credits to the guys who did that topic

thank you very much

[dense barn]

how do i add commas in numbers that fetchone gets?

@client.command(aliases=["bal"])
async def balance(ctx):
    db = sqlite3.connect(db_path)
    cursor = db.cursor()
    cursor.execute("SELECT balance FROM pokesets WHERE user_id = ?",(ctx.author.id,))
    result = cursor.fetchone()
    await ctx.reply(f"**{ctx.author.name}**, you currently have **{result[0]}** ![CardCoin](https://cdn.discordapp.com/emojis/856944099193651200.webp?size=128 "CardCoin")CardCoins!")
``` i wanna do that in this command

[dense barn]

nvm i got it, used {:,} and .format

[tender torrent]

is there a database that you would suggest I should use if I just started learning? I dont really understand how they work, or if there is a "good one" yet, so your opinions would help :D

[harsh pulsar]

sqlite

[tender torrent]

ok :D

[empty haven]

as mentioned above, SQLite is great.

[tender torrent]

ok thaaanks!

[empty haven]

Check out #databases message for some help

[uneven stream]

can I use async for for execute() from aiosqlite module?

[livid vault]

how do u check if a row already exists?

var = "INSERT INTO stats(user_name, user_id, total_msg) VALUES(?,?,?)" 
self.cursor.execute(var, [("Total"), ("000000000000000000"),(0)])
self.database.commit() #saves the information

I am trying to add a if statement to this to check whether if this row named total already exists, if yess, then ignore, else create a row named total

[harsh pulsar]

do you have a primary key on the table?

[livid vault]

yes i do

[harsh pulsar]

the primary key is user_id?

also, what database is this?

[livid vault]

import _sqlite3

with _sqlite3.connect("ServerStats.db") as database:
    cursor = database.cursor()

cursor.execute('''
CREATE TABLE IF NOT EXISTS stats(
BasicID INTEGER PRIMARY KEY,
user_name VARCHAR(20) NOT NULL,
user_id VARCHAR(20) NOT NULL,
total_msg VARCHAR(20) NOT NULL);
''')

This is how i created the database

and total always has an id of 1 but i am not sure onto how i can get a True or False result to check if that is created or not

that being the row named total

[harsh pulsar]

do you need to specifically check if the row exists? maybe you can use ON CONFLICT in your INSERT instead https://sqlite.org/lang_conflict.html

also, the (20) in your VARCHAR type is ignored by sqlite https://www.sqlite.org/datatype3.html

VARCHAR is an alias for TEXT

[livid vault]

oh ok

thanks

[remote plinth]

im trying to download mysql workbench.. is this the right download? i've windows

[pale jay]

searches for database projects without ui get's rejected
Can you guys recommend some if any?

[pale jay]

yea this should work

[proven arrow]

What?

[pale jay]

I am too lazy to build a full ui but want to do some sql project

[proven arrow]

Ok so do it.
And not sure what you meant by gets rejected?

[remote plinth]

Error Code 1044. Access denied for user '.....'@'%' to database '......'

mysql db*

what could be the reason

[proven arrow]

That the combination of host,user,port,password is incorrect

[dense barn]

does sql3 store data in array format?

[proven arrow]

No

[dense barn]

any way i can do it?

[proven arrow]

Sure but why,

What’s the list storing

[grim vault]

Store it in a text column as json.dumps() and restore it after select with json.loads().

[proven arrow]

Ideally you want atomic values, and you can break the list down and normalise it

[dense barn]

certain names that i want to array it in an embed

[dense barn]

alr, ill try

[proven arrow]

It would be better if you model your data properly rather than just smash everything into a column that’s not even json.

[dense barn]

ok

[uneven stream]

how do I check if something is deleted in aiosqlite cursor?

[proven arrow]

cursor.rowcount

Returns the number of rows affected by the statement

[uneven stream]

o

[proven arrow]

Or you can do a select exist if you want to write a query to check if it’s gone

[dense barn]

didnt quit work, also that i used fetchall maybe that could be the problem

[grim vault]

Without code and data it's hard to tell, but as 38654 has said, normalising the data schema would be preferable.

[dense barn]

@client.command(aliases=["bag","col"])
async def collection(ctx):
    db = sqlite3.connect(db_path2)
    db.text_factory = str
    cursor = db.cursor()
    cursor.execute("SELECT rowid,pc FROM pokesetspc WHERE user_id = ?",(ctx.author.id,))
    result = cursor.fetchall()
    embed=discord.Embed()
    embed.add_field(name="\u200b",value=f"\n{result}")
    await ctx.send(embed=embed)
``` heres the code

[grim vault]

What do you expect? result will be a list of tuples or an empty list if no rows are found.

[remote plinth]

no, i signed in mysql workbench and i can access anything, the only thing i could not use is making a database

oo ig it should work now

[proven arrow]

Then you don’t have permissions to perform that action with that user.

You can grant the privileges

[remote plinth]

i fixed!

[spare wasp]

hi im trying to use ravendb with python where would you say is a good place to start?

[grim zephyr]

  warnings.warn(str(exc))
Traceback (most recent call last):
  File "h:\PYTHON\TEST UNBAN\COG TEST\bot.py", line 42, in <module>
    prx.prefixes = discordmongo.mongo(connection_url = prx.db, dbname = "prefixes")
TypeError: 'module' object is not callable```

[spare wasp]

@grim zephyr try using pymongo

[grim zephyr]

i am using pymongo

[spare wasp]

k its just that you was using discordmongo

thats all

[grim zephyr]

well any way i can fix this error?

[spare wasp]

u trying to make a connection

or are you altering a connection

[grim zephyr]

making a connection

[grim zephyr]

well is it possible to read and write a sqlite db using heroku

[eternal blaze]

Is there a bot command for a wiki or FAQ for databases and online hosting? I'm a student and I have some beginner questions. I don't care about scaling, or api count, or any of that extra stuff.

[harsh night]

whats the differance bettween executing sql using db and cursor

[harsh pulsar]

The latter is required behavior by the db api specification, the former is a convenience provided by the library where it just creates and returns a new cursor for you

If the former is available, use it

[mild garnet]

can anyone explain

def update_encourage(encourage_message):
if "encouragement" in db.keys():
encouragement = db["encouragement"]
encouragement.append(encourage_message)

db["encouragement"] = encouragement

else :
db["encouragement"] = [encourage_message]

meaning

especially that database part

[plush sphinx]

It'd be better if you can keep the code this way:

YOUR CODE

By doing py paste your code here

def update_encourage(encourage_message):
  if "encouragement" in db.keys():
    encouragement = db["encouragement"]
    encouragement.append(encourage_message)

    db["encouragement"] = encouragement
  else :
    db["encouragement"] = [encourage_message]```

This way!

[bitter bone]

an ISO 8601 falls into which category of datatype i sqlalchemy? eg. 2021-06-25T10:00

this is what i am guessing

[untold ledge]

i've been trying to add 2 values to a 2 column table

but it ends up like this

it doesn't end up in any errors but i dont see the values anywhere

am i using the db browser wrong or its the code??

also in the terminal it prints out no value

[autumn wave]

Hi All... I have started learning data science from today. I am going in a clear learning path way. Will be completing the Data science in 6 months. If anyone else is interested in preparing with me, you can join me.

[grim vault]

My guess would be that you're missing the conn.commit() before the conn.close() to confirm the transaction.

[finite silo]

Guys how do I make the primary key in SQLAlchemy a custom one or at least hide it?

[gaunt meadow]

is sqlite3 LIKE operator case-sensitive?

[calm grotto]

I think case-insensitive (for ascii characters only), unless you use the case_sensitive_like pragma: https://www.sqlite.org/lang_expr.html#the_like_glob_regexp_and_match_operators

[calm grotto]

Hello. You might want to check out the #data-science-and-ml channel and the pinned messages there.

[gaunt meadow]

thx

[chilly creek]

Hi, I have a MongoDB in the backend and would like to store images. Perhaps it is different for Mongo, but usually you dont want to store raw files in a DB because it is slow and unoptimized. My idea would be to save the images to AWS S3 and save the link in MongoDB and it seems like there is MongoDB Data Lake that can help me with that - but that is a paid service so I would prefer to do it without.
I unfortunately did not found a tutorial on that.

[empty haven]

Sql languages add very simple. Think of it like this.

All keywords (commands) should be uppercase, such as SELECT, FROM, ORDER etc.

Everything else must match their corresponding value. For example, if you have a table called "Test", you have to query the table as "Test".

Booleans are the only exception to this rule. They can be either, True, TRUE, true, False, FALSE, or false

[calm grotto]

I use lowercase keywords 👀

[empty haven]

what

Go to #esoteric-python, this isn't the cursed channel.

[calm grotto]

Uppercase just makes it look like Cobol or something. Too shouty 😄

[empty haven]

While I agree, 1) it's generally better coding practice, and 2) it's easier to read IMO

[cunning jolt]

that used to be

nowadays most engines can deal with both uppercase and lowercase sql

[empty haven]

Fixed that message

When I get home I'll probably put some more detail but whatever

[delicate field]

Fat chance.

Sorry, you can't do that here!

[dense belfry]

```py
-code here