databases

[rustic geyser]

um idk

cuz it doesnt exists

[austere portal]

what?

i dont think that you can alter non existing colums

[rustic geyser]

oh-

then

CREATE banner TEXT

[austere portal]

no

ADD column_name data_type```

[proven arrow]

sqlite alter table is not like other databases. It has limited functioanlity

You cant alter columy types

[austere portal]

I didnt know that

[rustic geyser]

um

    db2 = sqlite3.connect('memes.sqlite')
    cursor2 = db2.cursor()
    cursor2.execute('''
              ALTER TABLE memes
              ALTER COLUMN guid_id INTEGER,
              ALTER COLUMN CHANNEL_ID INTEGER,
              ADD banner TEXT
              ''')
    db2.commit()```

correct?

[proven arrow]

Whats wrong with creating the correct table in the first place?

[rustic geyser]

idk

is thar correct? @austere portal

else i got another idea

[austere portal]

yes, it should be correct

[rustic geyser]

oki

[austere portal]

oh wait

@rustic geyser
#databases message

[rustic geyser]

172.18.0.1 - - [11/Jul/2021 14:58:10] "GET / HTTP/1.1" 200 -
Ignoring exception in on_ready
Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/discord/client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "main.py", line 56, in on_ready
    cursor2.execute('''
sqlite3.OperationalError: near "ALTER": syntax error

um

ig ill use my other idea

[proven arrow]

Godspeed

If you actually read peoples suggestions maybe you'd know why the error is there

[rustic geyser]

hm

FIXED FINALLY OH MY

tysm @austere portal @proven arrow and @shell ivy

ill surely comeback when i encounter another error

[austere portal]

👍

[rustic geyser]

imma recover my removed data from repl.it now

[austere portal]

Thanks

[austere portal]

I knew that sqlite has limited functionality, but I didn't know that it is that much limited

[rustic geyser]

@client.event
async def on_member_join(member):

        
    inviter = await tracker.fetch_inviter(member)
    await client.wait_until_ready()
    db = sqlite3.connect('memes.sqlite')
    cursor = db.cursor()
    cursor.execute("SELECT channel_id FROM memes WHERE guild_id = ?", (member.guild.id,))
    result = cursor.fetchone()
    if not result:
        return
    cursor.execute("SELECT banner FROM memes WHERE guild_id =?", (member.guild.id,))
    result2 = cursor.fetchone()
    print(result)
    print(result2)
    channelloo = result[0]
    final_channel = member.guild.get_channel(int(channelloo))
    if not result2:
      await final_channel.send(f'{member.mention} has just joined invited by {inviter.mention}')``` it prints result correct but it prints result2 none but it should print yes

[austere portal]

ig there is no data

[rustic geyser]

why

it sets it to yes

it is supposed to be yes

[austere portal]

did you add a row with the banner value "yes"?

[rustic geyser]

@client.command()
async def banneron(ctx):
     db = sqlite3.connect('memes.sqlite')
     cursor = db.cursor()
     cursor.execute("SELECT channel_id FROM memes WHERE guild_id = ?", (ctx.guild.id,))
     result = cursor.fetchone()
     print(result)
     if not result:
        await ctx.send("You have not assigned a welcome chane yet use `*setwelcomechannel #channel`")
        return
     cursor.execute("INSERT INTO memes(guild_id, banner) VALUES(?,?)", (ctx.guild.id, "yes",))
     db.commit()
     db.close()
     await ctx.send("Done, Now i will send banner whenver someone joins")

this works fine and sends the done message but idk if it changes value

[rustic geyser]

yes but not with the value yes i just created new row/column whatever

database looks fine

it has yes in it

[austere portal]

ok

[rustic geyser]

so um yk whats wrong

[austere portal]

[rustic geyser]

wdym

how do i solve dis issue

and once again its noting of async cuz sqlite3 works totally fine

[little pumice]

How can i handle the " unique error ( sqlite3 ) " ?
like :
except sqlite3.errors.unique:

[austere portal]

That will raise sqlite3.IntegrityError

Thanks

[grim vault]

If this is executed you'll have a row with an banner an a guild_id but no channel_id. A select with fetchone() will give you just one row (as the name suggest) and it is undefined which row you will get. You need to update the entry with the banner, not make a new insert! eg like:

@client.command()
async def banneron(ctx):
     db = sqlite3.connect('memes.sqlite')
     cursor = db.cursor()
     cursor.execute("SELECT channel_id FROM memes WHERE guild_id = ?", (ctx.guild.id,))
     result = cursor.fetchone()
     print(result)
     if not result:
        await ctx.send("You have not assigned a welcome chane yet use `*setwelcomechannel #channel`")
        return
     sql = "UPDATE memes SET banner = ? WHERE guild_id = ?"
     val = ("yes", ctx.guild.id)
     cursor.execute(sql, val)
     db.commit()
     db.close()
     await ctx.send("Done, Now i will send banner whenver someone joins")

I don't know if the banner is per guild and channel or only guild, you can remove the channel from the update if it's only guild specific. looking over the other messages I guess it's guild specific only.

[thorn geode]

(oh and sqlite is blocking. Consider using a(io)sqlite instead).

[uneven stream]

#help-pretzel anybody

[little pumice]

I have two things

email = unique
user = unique

how can i get the responsible of this error

[austere portal]

What do you mean?

[little pumice]

for example

the user typed "chamsou" and there is "chamsou" before
and it is a username

how can i tell him to change the username

not the email

[rustic geyser]

lemme try @grim vault

[austere portal]

Use a try except block

[rustic geyser]

AND OFC repl.it is reinstalling packages

[little pumice]

but this will give me in general

[austere portal]

You can have a try except block for each input

[little pumice]

ok lemme see

[rustic geyser]

i hate repl.it 😩

[little pumice]

@austere portal

[austere portal]

Yes

[little pumice]

UNIQUE constraint failed: users.email

how can i get "users.email" if it's email or username

in python

[austere portal]

try:
    email=input(">>> ")
    # insert it to the db
except sqlite3.IntegrityError:
    print("email aldready in database")```

[little pumice]

but it can be username, not always email

[austere portal]

try:
    email=input(">>> ")
    # insert it to the db
except sqlite3.IntegrityError:
    print("email aldready in database")
try:
    user_name=input(">>> ")
    # insert it to the db
except sqlite3.IntegrityError:
    print("username aldready in database")```

[little pumice]

it's on the same try, except

[austere portal]

have multiple try excepts

or have a function which handles it

@little pumice

[little pumice]

i sent u my script

on dms

( flask )

[austere portal]

def handle_integrity(data):
    try:
        # insert data into db
    except sqlite3.IntegrityError:
        # handle the error```

[austere portal]

Sorry, I don't know flask

[little pumice]

thx anyways

[austere portal]

Hi, does anyone have experience using asyncpg and pytest-asyncio?

[devout girder]

How do you store dictionary-like values in sqlite

[austere portal]

I'm pretty sure that you can store json in sqlite

[pure sleet]

i think you can easily convert a dictionary to json with the json module

[torn sphinx]

hi, i'm completely new to the concept of databases. all I know is I would like a pythonic way to save data to a cloud/network location where any person with access to that cloud/network location could also access that data

any recommendations for easy ways to do this?

also, if the method addresses multiple people trying to access and modify the database simultaneously, that would be great.

[austere portal]

Hi, does anyone have experience using asyncpg and pytest-asyncio?

[trail lintel]

yo anyone has a code to read gmail through python?

[vernal stratus]

I'm getting this for 3 days straight

[burnt turret]

You want %Y as opposed to %y by the looks of it https://strftime.org

How is the related to databases?

[distant topaz]

okay thanks:)) it's for the database of a business..

[proven arrow]

And you were storing this business data in a text file?

[little pumice]

SELECT * FROM users WHERE username or email = ?
is it correct?

username or email = ?

[grim vault]

No, you need both: SELECT * FROM users WHERE username = ? OR email = ?

[little pumice]

ah, ok thx

@grim vault

c.execute("SELECT * FROM users WHERE username = ? OR email = ?", (str(b),))
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 1 supplied.

[grim vault]

There are two ? so you need two arguments, if they are the same value you can: (str(b), str(b))

[little pumice]

yes, it's working ty

[austere portal]

Hi, does anyone have experience using asyncpg and pytest-asyncio?

[sharp tundra]

why do i get

Command raised an exception: OperationalError: no such column: in_a_clan

despite there already being values if i run

x = database.execute("SELECT in_a_clan FROM userdata")
x = x.fetchall()
print(x)

[frail shadow]

is json suitable for storing passwords?

[hexed estuary]

Well, if you're storing passwords as plaintext, JSON won't make this any more wrong 🙂

[frail shadow]

sha256 with salt my guy

[bright dune]

presumably you're going to be writing to it and reading often as you gain more hashes to store, right?

json would probably be ok if you have a small amount, otherwise you need something that scales better

[prisma girder]

You can ask this kind of questions on #cybersecurity 🙂

[bright dune]

i thought he was asking more about the storage 🤔

he's already salted and hashed them

[torn sphinx]

Hey, I need some help!
I am build a UI program! I want that when the button gets clicked it browses a folder and its address gets inserted in the entry field! And that same address should be temporarily save in the json file too! Once the program is closed the json entry will also be deleted!! How do I do that?

[prisma girder]

How is it related to databases? You should check #user-interfaces

[torn sphinx]

Json? 🤔

[prisma girder]

Storing a file is only a part of your problem, I think that the more important part is to adding hooks to actions in the UI

[torn sphinx]

I have developed the ui! And it's working so fine! But the actual issue is that, temporary storing values!

[prisma girder]

Okay, then use tempfile module

!d tempfile

[delicate field]

tempfile

Source code: Lib/tempfile.py

This module creates temporary files and directories. It works on all supported platforms. TemporaryFile, NamedTemporaryFile, TemporaryDirectory, and SpooledTemporaryFile are high-level interfaces which provide automatic cleanup and can be used as context managers. mkstemp() and mkdtemp() are lower-level functions which require manual cleanup.

[prisma girder]

!d tempfile.TemporaryFile

[delicate field]

tempfile.TemporaryFile

tempfile.TemporaryFile(mode='w+b', buffering=-1, encoding=None, newline=None, suffix=None, prefix=None, dir=None, *, errors=None)```
Return a [file-like object](https://docs.python.org/3/glossary.html#term-file-like-object) that can be used as a temporary storage area. The file is created securely, using the same rules as [`mkstemp()`](https://docs.python.org/3/library/tempfile.html#tempfile.mkstemp "tempfile.mkstemp"). It will be destroyed as soon as it is closed (including an implicit close when the object is garbage collected). Under Unix, the directory entry for the file is either not created at all or is removed immediately after the file is created. Other platforms do not support this; your code should not rely on a temporary file created using this function having or not having a visible name in the file system.

The resulting object can be used as a context manager (see [Examples](https://docs.python.org/3/library/tempfile.html#tempfile-examples)). On completion of the context or destruction of the file object the temporary file will be removed from the filesystem.

[prisma girder]

!e

from tempfile import TemporaryFile


with TemporaryFile() as file:
  file.write(b"test")

[delicate field]

@prisma girder :x: Your eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 4, in <module>
003 |   File "/usr/local/lib/python3.9/tempfile.py", line 581, in TemporaryFile
004 |     prefix, suffix, dir, output_type = _sanitize_params(prefix, suffix, dir)
005 |   File "/usr/local/lib/python3.9/tempfile.py", line 118, in _sanitize_params
006 |     dir = gettempdir()
007 |   File "/usr/local/lib/python3.9/tempfile.py", line 287, in gettempdir
008 |     tempdir = _get_default_tempdir()
009 |   File "/usr/local/lib/python3.9/tempfile.py", line 219, in _get_default_tempdir
010 |     raise FileNotFoundError(_errno.ENOENT,
011 | FileNotFoundError: [Errno 2] No usable temporary directory found in ['/tmp', '/var/tmp', '/usr/tmp', '/snekbox']

[prisma girder]

!e

from tempfile import TemporaryDirectory

directory = TemporaryDirectory()
print(directory)

[delicate field]

@prisma girder :x: Your eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 3, in <module>
003 |   File "/usr/local/lib/python3.9/tempfile.py", line 779, in __init__
004 |     self.name = mkdtemp(suffix, prefix, dir)
005 |   File "/usr/local/lib/python3.9/tempfile.py", line 348, in mkdtemp
006 |     prefix, suffix, dir, output_type = _sanitize_params(prefix, suffix, dir)
007 |   File "/usr/local/lib/python3.9/tempfile.py", line 118, in _sanitize_params
008 |     dir = gettempdir()
009 |   File "/usr/local/lib/python3.9/tempfile.py", line 287, in gettempdir
010 |     tempdir = _get_default_tempdir()
011 |   File "/usr/local/lib/python3.9/tempfile.py", line 219, in _get_default_tempdir
... (truncated - too many lines)

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

[torn sphinx]

I got it! 👍

Thanks bro!

[prisma girder]

Your welcome!

Unfortunately I cannot show an example with bot eval command

[simple moth]

Hello everyone! Our team is having issues with trying to copy the data from one database to another (stuff like user permissions, and so on), and our DBA is unavailable.
Can you suggest me some resources that I can go though to understand the issue and setup the new DB in a way that allows us to copy the data correctly?

[torn sphinx]

@prisma girder is there anyway that the temp file shouldn't delete until the program is closed?

[prisma girder]

Afaik the file will be removed when the __exit__ function will be called

So

with tempfile() as file:
  ...
# file no longer exists

[harsh pulsar]

are these different database applications? like mysql -> postgres? or just migrating between servers running the same application? why does this need to happen right now while your DBA is out?

[simple moth]

@harsh pulsar
Oracle -> Oracle
Migrating from a setup running on a local machine to a docker container in cloud.
Well, it's just the development is blocked by DB availability, and the timeline's tight, so we may as well try to handle it ourselves.
And it's less dedicated DBA and more "the least clueless of us"

[harsh pulsar]

i see. i don't use oracle but maybe they have "export" and "restore" tools? i know postgresql does

[simple moth]

Yeah, they should have them, but what's the best place to learn how to do that properly? Googling for official docs?

[harsh pulsar]

that's what i do, but i'm not exactly a database pro either 🙂

[craggy idol]

Anyone around?

Is there a special kind of data base that specialises in images or can handle images really well?

[acoustic abyss]

@harsh pulsar back to square one i suppose. i've looked into IPFS for justuse, but it's kind of fragmented and the python part is basically zombified 😦 also, i talked to a guy who explained to me (in length!) why exactly they think it's a bad idea for what i have in mind.. well, i'm still tempted to build a prototype just to see if it works at all.. maybe i need to get back to the decentralized database idea

or maybe something like a blockchain

or maybe it's just too soon to realize something like that

or maybe i need to try and realize it myself :p

damn 😐

[rugged hornet]

!e

[delicate field]

Command Help

!eval [code]
Can also use: e

*Run Python code and get the results.

This command supports multiple lines of code, including code wrapped inside a formatted code
block. Code can be re-evaluated by editing the original message within 10 seconds and
clicking the reaction that subsequently appears.

We've done our best to make this sandboxed, but do let us know if you manage to find an
issue with it!*

[lost sonnet]

!e print("hello")

[sharp tundra]

why do i get

Command raised an exception: OperationalError: no such column: in_a_clan

despite there already being values if i run

x = database.execute("SELECT in_a_clan FROM userdata")
x = x.fetchall()
print(x)

[pure sleet]

can i see your table structure for userdata?

[honest arrow]

has anyone used async mongo?

[pearl vapor]

sqlite is a good starting db for bots right

[bright dune]

sure. if you have concurrent writing you'll have issues though

[hardy ibex]

I'm stuck, I'm trying to update a user's balance in a users table with (f"UPDATE users SET balance = ? WHERE user = ?"(newbal, ctx.author.id)), but it just does nothing. What did I do wrong here?

SQLite, btw

[pure sleet]

are you committing? etc

[hardy ibex]

How in Hell did I forget to do that?

Thanks lol

[dense barn]

Also you missed a comma after the last ?.

[hardy ibex]

Thanks

[dense barn]

Np

[hardy ibex]

Still not working

c.execute(f"UPDATE users SET balance = ? WHERE user = ?",(newbal, ctx.author.id,))

c is cursor

And I comited this time

[pearl vapor]

so basically, change to smn else when it gets used more

[bright dune]

sure

[harsh pulsar]

don't ask to ask. state your question and if someone knows the answer they can help. as it happens i use motor (the asyncio mongo client) at work, so maybe i can help. but it's more efficient if you just ask, instead of hoping someone like me happens to be around..

[honest arrow]

what do you use for modeling?

[harsh pulsar]

we have our own ODM that we use internally

[honest arrow]

will pydantic be ok?

[harsh pulsar]

yes i was just about to say, i wish we just used attrs/pydantic instead of a full-scale ODM

if you can serialize it to json you can write it to a mongo collection 🤷‍♂️

[honest arrow]

I can directly write it to a mongo collection as a dict right?

[harsh pulsar]

using motor? yes, it's the same as pymongo

[honest arrow]

I'm using motor

[harsh pulsar]

the docs have usage examples

[honest arrow]

pydantic or motor?

[harsh pulsar]

both, but not examples of using them together

[honest arrow]

I saw bot docs just wanted to whats the standard when using motor

[harsh pulsar]

there's no standard

at least not that i know of

[honest arrow]

modelling standards and stuff

what should we use for modeling.

[harsh pulsar]

ah, yeah im not aware of any. frankly i don't really like mongo and i don't know why my org uses it...

but pydantic seems fine

go with that, and write some functions or methods to interact w/ the database

[honest arrow]

there a common picks to choose from usually. like we can choose from posgresql and sqllite

that's what I was asking

[harsh pulsar]

but postgresql and sqlite are 2 different relational databases, mongo is mongo

[honest arrow]

just and example

😅

I meant that they are two general picks when it comes to relational databases

[harsh pulsar]

maybe you want to compare attrs vs pydantic?

there are also other tools like marshmallow, jsonschema, etc.

[honest arrow]

is attrs from the stdlib?

no right

yeah, it's not from stdlib

[honest arrow]

not now because I'm already combined my sould with pydantic

I started using pydantic while I ask the question and pydantic seems fine

[glad marten]

So I’m using sql alchemy and I’m joining data from 3 separate tables. But I want to exclude any rows that match by Id in the third table

How in the world can I do this? I know what needs to be done but for some reason it’s not turning out how I expected

For example I have
40 rows being generated from the first two tables
2 Id’s match the third table so I’d want them removed from the query
So I should be left with 38 rows left

[harsh pulsar]

@honest arrow you could probably rig up a basic ODM on top of Pydantic like this

from motor.motor_asyncio import AsyncIOMotorCollection, AsyncIOMotorDatabase
from pydantic import BaseModel
from pymongo.results import UpdateResult


class Document(BaseModel):
    """Base document class."""
    collection_name: ClassVar[str]

    def to_mongo_dict(self) -> Dict[str, Any]:
        """Serialize to a dict containing only BSON-friendly data types."""
        ...

    def from_json_dict(self) -> str:
        """Deserialize from a dict as emitted from PyMongo/Motor."""
        ...

    @property
    def collection(self) -> AsyncIOMotorCollection:
        return db[self.collection_name]

    async def save(self, db: AsyncIOMotorDatabase) -> UpdateResult:
        await self.collection.update(self.to_mongo_dict(), upsert=True)


class Person(Document):
    user_id: str
    age: int
    email: str
    verified_email: bool

[honest arrow]

oh, thanks very much

[honest arrow]

bro can I use any type other than the ones supported in json

like Default Dict for example

[honest arrow]

is db.collection.create_index in pymongo like onetime thing or do we have to run it everytime I run the program?

!d pymongo.collection.Collection

[delicate field]

pymongo.collection.Collection

class pymongo.collection.Collection(database, name, create=False, **kwargs)```
Get / create a Mongo collection.

Raises [`TypeError`](https://docs.python.org/3/library/exceptions.html#TypeError "(in Python v3.9)") if name is not an instance of `basestring` ([`str`](https://docs.python.org/3/library/stdtypes.html#str "(in Python v3.9)") in python 3). Raises [`InvalidName`](https://pymongo.readthedocs.io/en/stable/api/pymongo/errors.html#pymongo.errors.InvalidName "pymongo.errors.InvalidName") if name is not a valid collection name. Any additional keyword arguments will be used as options passed to the create command. See [`create_collection()`](https://pymongo.readthedocs.io/en/stable/api/pymongo/database.html#pymongo.database.Database.create_collection "pymongo.database.Database.create_collection") for valid options.

If create is `True`, collation is specified, or any additional keyword arguments are present, a `create` command will be sent, using `session` if specified. Otherwise, a `create` command will not be sent and the collection will be created implicitly on first use. The optional `session` argument is *only* used for the `create` command, it is not associated with the collection afterward.

[honest arrow]

!d pymongo.collection.Collection.create_index

[delicate field]

pymongo.collection.Collection.create\_index

create_index(keys, session=None, **kwargs)```
Creates an index on this collection.

Takes either a single key or a list of (key, direction) pairs. The key(s) must be an instance of `basestring` ([`str`](https://docs.python.org/3/library/stdtypes.html#str "(in Python v3.9)") in python 3), and the direction(s) must be one of ([`ASCENDING`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.ASCENDING "pymongo.ASCENDING"), [`DESCENDING`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.DESCENDING "pymongo.DESCENDING"), [`GEO2D`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.GEO2D "pymongo.GEO2D"), [`GEOHAYSTACK`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.GEOHAYSTACK "pymongo.GEOHAYSTACK"), [`GEOSPHERE`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.GEOSPHERE "pymongo.GEOSPHERE"), [`HASHED`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.HASHED "pymongo.HASHED"), [`TEXT`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.TEXT "pymongo.TEXT")).

To create a single key ascending index on the key `'mike'` we just use a string argument:

```py
>>> my_collection.create_index("mike")
```  For a compound index on `'mike'` descending and `'eliot'` ascending we need to use a list of tuples...

[harsh pulsar]

a mongo index is created once and continuously updated when the database is written to

[dense barn]

Can i filter out queries for postgresql? Like if the card name has a certain word it ignores it and chooses another one.

[harsh pulsar]

can you be more specific

[dense barn]

ok, so i have a column named card_name which contains certain words like holo,rare etc. Now im fetching all the card_names from that db, and after it get those, i randomly choose 10 card_names from it. If that randomly choose 10 card_names have a word called Holo, i want it to choose another card name from the list it got before and ignore the card name which has holo in it.

[tame ravine]

whats a good automatic database backup solution?

[hearty magnet]

What's wrong here i dont get it ?

Code :

from os.path import isfile
from sqlite3 import connect

from apscheduler.triggers.cron import CronTrigger

DB_PATH = "./data/db/database.db"
BUILD_PATH = "./data/db/build.sql"

cnt = connect(DB_PATH, check_same_thread=False)
cur = cnt.cursor()


def with_commit(func):
    def inner(*args, **kwargs):
        func(*args, **kwargs)
        commit()

    return inner


@with_commit
def build():
    if isfile(BUILD_PATH):
        scriptexec(BUILD_PATH)


def commit():
    cnt.commit()


def autosave(sched):
    sched.add_job(commit, CronTrigger(second=0))


def close():
    cnt.close()


def field(command, *values):
    cur.execute(command, tuple(values))

    if (fetch := cur.fetchone()) is not None:
        return fetch[0]


def record(command, *values):
    cur.execute(command, tuple(values))

    return cur.fetchone()


def records(command, *values):
    cur.execute(command, tuple(values))

    return cur.fetchall()


def column(command, *values):
    cur.execute(command, tuple(values))

    return [item[0] for item in cur.fetchall()]


def execute(command, *values):
    cur.execute(command, tuple(values))


def multiexec(command, valueset):
    cur.executemany(command, valueset)


def scriptexec(path):
    with open(path, "r", encoding="utf-8") as script:
        cur.executescript(script.read())

Error :

sqlite3.OperationalError: near "EXITS": syntax error

[mortal scarab]

@hearty magnetqhat are ur queries

[hearty magnet]

@mortal scarab these are the queries

    UserID integer PRIMARY KEY,
    XP integer DEFAULT 0,
    Level integer DEFAULT 0,
    XPLock text DEFAULT CURRENT_TIMESTAMP
);

[mortal scarab]

CREATE TABLE IF NOT EXISTS exp (
    UserID integer PRIMARY KEY,
    XP integer DEFAULT 0,
    Level integer DEFAULT 0,
    XPLock text DEFAULT CURRENT_TIMESTAMP
);

[hearty magnet]

@mortal scarab Thanks it worked!

[mortal scarab]

@hearty magnet btw use a class its a pretty messy because u dont share instance connections in SQLite

and im assuming ur using these outside this module

[hearty magnet]

Yep

[mortal scarab]

i use a context manager approach to cinnections

[hearty magnet]

I am using it for making a discord bot rather than making just practicing

[mortal scarab]

FYI i dont have a need for connstatly quering a DB

but good practice to keep the connections instance specific like 2 objects dont share a same cinnection

[hearty magnet]

I heard constant queries are great for discord bot

[mortal scarab]

ohh no i meant i kill my connections as im done requesting for data for my use case

[hearty magnet]

Ohh i see

I am just a beginner so

[mortal scarab]

np just start good practices from the start

[hearty magnet]

Sure, Thanks for the suggestion!

[slate cipher]

is mongodb compatible with repl

?

[torn sphinx]

Hello, is anyone of you familiar with using tkinter and sqlite ?

[vernal stratus]

I'm trying to create a column in pgadmin but getting this error

[velvet fable]

Ive used sqlite before, how can I help? (ping when replying)

[torn sphinx]

@velvet fable have u used shelve?

[sharp tundra]

why do i get

Command raised an exception: OperationalError: no such column: in_a_clan

despite there already being values if i run

x = database.execute("SELECT in_a_clan FROM userdata")
x = x.fetchall()
print(x)

[prisma girder]

There is no column like in_a_clan in userdata

[sharp tundra]

but there issssssssssssss

[grim vault]

Show the output of

SELECT "sql" FROM sqlite_master WHERE name = 'userdata' AND type = 'table'```
or
```sql
PRAGMA table_info('userdata')```

[vernal stratus]

What’s the difference between asyncpg and psycopg2?

[harsh pulsar]

Asyncpg uses async, psycopg2 does not. Also asyncpg uses the binary postgres protocol and psycopg2 uses the text protocol

[grim ether]

@client.event
async def on_message(message):
    databas = motor.motor_asyncio.AsyncIOMotorClient('localhost', 27017)
    database = databas["bruhbot"]
    blacklistcoll = database['blacklist']
    blacksearch = await blacklistcoll.find_one({'user': message.author.id})

    if blacksearch:
        return
    await client.process_commands(message)

@client.command()
@commands.is_owner()                           
async def blacklist(ctx,member: discord.User):
    databas = motor.motor_asyncio.AsyncIOMotorClient('localhost', 27017)
    database = databas["bruhbot"]
    blacklistcoll = database['blacklist']
    await blacklistcoll.insert_one({'id': member.id})
    await ctx.send(f'{member} is now blacklisted!')

Why my blacklist didn't store the user id to the database

Anyone help ?

[uneven stream]

is there a way I can get all the table column names(keys)?

[austere portal]

You can use Cursor.description to get it

E.g: ```py
field_names = [i[0] for i in cursor.description]

[uneven stream]

o

thanks!

cursor = await ______.execute('SELECT 1')
[_ for _ in cursor.description]
```returned me ```py
[('1', None, None, None, None, None, None)]

well, it did return tuples with column names in index 0```py
cursor = await ______.execute('SELECT * from ____')
[ for _ in cursor.description]

wts wrong

[alpine ledge]

{'name': 'bob-omb', 'tier': 3, 'price': 50, 'energy': 2, }
{'name': 'freeze missile', 'tier': 1, 'price': 600, 'energy': 4, 'freeze': 1}

I have 2 documents on mongodb like this with one that have The key freeze of value one and other without it .

#name is a paramter which can be the names of above
Item = await itemcoll.find_one({"name": name})
print(if Item['freeze'] is not None)

When I tried to get the 'freeze' key this happens KeyError: 'freeze'
SO how can I check if the freeze doesn't exist without updating all other docs

[harsh pulsar]

some databases also have special "metadata tables" that you can query for this information

[uneven stream]

o

[harsh pulsar]

use the .get method on the dict

item = await itemcoll.find_one({"name": name})
print(item.get('freeze') is not None)

!d dict.get

[delicate field]

dict.get

get(key[, default])```
Return the value for *key* if *key* is in the dictionary, else *default*. If *default* is not given, it defaults to `None`, so that this method never raises a [`KeyError`](https://docs.python.org/3/library/exceptions.html#KeyError "KeyError").

[uneven stream]

sqlite

[harsh pulsar]

you can use PRAGMA_TABLE_INFO https://www.sqlite.org/pragma.html to get column names from a table, and you can query the sqlite_master table to get all tables. see this example which combines both of them https://stackoverflow.com/a/50548508/2954547

[tawny zealot]

yo yo its ye boi data

[uneven stream]

o

thanks!

[tawny zealot]

l o l

[sonic hinge]

i keep getting this error raise ServerSelectionTimeoutError( pymongo.errors.ServerSelectionTimeoutError: connection closed,connection closed,connection closed, Timeout: 30s, Topology Description: <TopologyDescription id: 60eddbcb6bfcaa9262a6cbdc, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('cluster0-shard-00-00.4fln9.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed')>, <ServerDescription ('cluster0-shard-00-01.4fln9.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed')>, <ServerDescription ('cluster0-shard-00-02.4fln9.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('connection closed')>]>
and idk whta this error means or how to fix it

[gusty mulch]

So I'm not particularly great with DBs so can someone tell me why I am getting this error

UndefinedColumnError: column "member" does not exist
```with this code```py
    async def get_user(self, member):
        async with self.pool.acquire() as conn:
            res = await conn.fetchrow("SELECT * FROM user WHERE member=$1", member)
            if not res:
                res = await conn.fetchrow(
                    "INSERT INTO user VALUES ($1, $2, $3,) RETURNING *", # $4, $5, $6, $7, $8, $9, $10, $11
                    member, None, None,)
        return res
```when the below code works perfectly fine```py
    async def get_data(self, guild):
        async with self.pool.acquire() as conn:
            res = await conn.fetchrow("SELECT * FROM data WHERE guild=$1", guild)
            if not res:
                res = await conn.fetchrow(
                    "INSERT INTO data VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) RETURNING *",
                    guild, None, None, [], None, None, None, False, [], [], False,)
        return res

[harsh pulsar]

i bet you can guess what that error means

UndefinedColumnError: column "member" does not exist

[gusty mulch]

well it does exist, unless I made a spelling mistake

[harsh pulsar]

can you print the schema for the table? i don't know what graphical tool you're using

[delicate field]

Hey @gusty mulch!

It looks like you tried to attach file type(s) that we do not allow (.sql). We currently allow the following file types: .gif, .jpg, .jpeg, .mov, .mp4, .mpg, .png, .mp3, .wav, .ogg, .webm, .webp, .flac, .m4a.

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

[gusty mulch]

Hi, Well I have a file for the schema but @delicate field won't let me post it, Is it okay if I send it in DMs or should I try to root around the file for the schema bits

[gusty mulch]

Hi, Sorry for the second ping but here is the output from my db Wasn't sure what you needed so I grabbed everything

[harsh pulsar]

!paste @gusty mulch thanks. in the future you can use our "paste" site to post longer pieces of code. see the text box below 👇

[delicate field]

Pasting large amounts of code

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

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

[upper basin]

can i execute statements without a cursor using just the mysql connection

[slender atlas]

I know that on sqlite3, aiosqlite, and asyncpg it does allow you to do that, but it will return a Cursor object with the result of the query (for later use, I guess).

[upper basin]

thank you! i have another question. does mysql not have a rowcount attribute? like if i wanted to get the number of rows affected by an insert ignore in sqlite, i used c.rowcount, but now i get an attributeerror : 'int' has no object 'rowcount'

[slender atlas]

What is the library you are using?

And how do you define c?

[burnt turret]

Cursors on mysql.connector does have a rowcount attribute, but in your case c just isn't a Cursor (but an int as the error says)

[jaunty yew]

Hmmm I'm going to make a guess that you mutated c into an int in an earlier line of code and forgot about it

[gusty mulch]

Oh yeah I forgot about that 😅

[craggy jewel]

Anyone have any experience with sqlite3.. I am having errors with "blob" data types

[prisma girder]

Can you paste your errors here?

[last igloo]

Ignoring exception in on_ready
Traceback (most recent call last):
File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\discord\client.py", line 343, in _run_event
await coro(*args, **kwargs)
File "C:\Users\38050\Desktop\ds bot\bot.py", line 54, in on_ready
cursor.execute(f"INSERT INTO users (name, id, login, password, cash, lvl, xp, rub, welc) VALUES ('{response.author}','{response.author.id}','{login}','{passwo}',0,1,0,0,0)")
File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 568, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\mysql\connector\connection.py", line 686, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\mysql\connector\connection.py", line 418, in _send_cmd
return self._socket.recv()
File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\mysql\connector\network.py", line 248, in recv_plain
raise errors.InterfaceError(errno=2013)
mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query

code:
cursor = mydb.cursor()
cursor.execute(f"INSERT INTO users (name, id, login, password, cash, lvl, xp, rub, welc) VALUES ('{response.author}','{response.author.id}','{login}','{password}',0,1,0,0,0)")

[gusty mulch]

Hi, sorry for yet another ping but I remove the large block of code and whacked it onto sourcebin instaed: https://sourceb.in/VutjmisRuo. Were you able to see where I was going wrong? I think the db might be getting confused with user table bc it has put it in as CREATE TABLE public."user" ( which doesn't seem right and isn't how I entered it

[upper basin]

there is no unique or exclusion constraint matching the ON CONFLICT specification
this is the error showing for :

cursor = await self.bot.primedb.execute("insert into userlevels (guildid, userid, xp, lastmsg) values ($1,$2,$3,$4) ON CONFLICT (guildid, userid) DO NOTHING",message.guild.id,message.author.id,1,message.created_at)

[grim vault]

A double-quote is SQL standard to use for identifiers if they may collide with keywords. It could be that user has a special meaning, try:

    async def get_user(self, member):
        async with self.pool.acquire() as conn:
            res = await conn.fetchrow('SELECT * FROM "user" WHERE member=$1', member)
            if not res:
                res = await conn.fetchrow(
                    "INSERT INTO user VALUES ($1, $2, $3) RETURNING *",
                    member, None, None)
        return res```

[gusty mulch]

okay will do, thank you

[grim vault]

I also removed the , after $3 which would be a syntax error.

Ah, in the insert it might also be needed to quote the table name. And it might still fail because you are giving None for a not null column. If you don't have a value for a column define which values you wanna fill, eg:

                res = await conn.fetchrow(
                  'INSERT INTO "user" (member) VALUES ($1) RETURNING *', member)```

[harsh pulsar]

thanks for stepping in, sorry i had to log off last night

interestingly the discord syntax highlighting was also highlighting the word member so i'm not sure if that's a keyword in some sql dialect

[dim sluice]

What is foreign key and primary key things in sqlite3

[harsh pulsar]

a lot of tool that generate SQL will defensively quote all column and names

it's more typing but it's not a bad habit to get into

[harsh pulsar]

primary key is a column that uniquely identifies each row. it's kind of like the "label" for the row. so necessarily it must be unique within the table.

foreign key is a column in table A that points to the primary key (or other unique column) of table B. so it's "foreign" because it goes outside the current table.

there are probably a ton of articles on this topic if you search the web

[dim sluice]

I mean what are they used for?

[harsh pulsar]

lots of articles on that too 😉

two things:

1. semantically indicating to users what the purpose of the column is
2. the database can use primary keys and foreign keys to make queries more efficient, especially with joins

[dim sluice]

is a roeign key supposed to be the same as primary key or smt?

[upper basin]

where do i put the unique in the create statement for pgsql

create table if not exists userlevels (guildid bigint, userid bigint, xp bigint, lastmsg timestamp unique(userid, guildid) )

outside the inner brackets?

[grim vault]

You'll need a comma , after timestamp otherwise the unique would be part of the column definition.

[upper basin]

got it, thanks!

[upper basin]

so uh i have the same question but for asyncpg

@commands.Cog.listener()
async def on_message(self,message):
  if not message.author.bot:  
    cursor = await self.bot.primedb.execute("insert into userlevels (guildid, userid, xp, lastmsg) values ($1,$2,$3,$4) ON CONFLICT (userid, guildid) DO NOTHING",message.guild.id,message.author.id,1,message.created_at)

    if cursor.rowcount == 0 :
      timedata = await self.bot.primedb.fetch("select lastmsg from userlevels where guildid = $1 and userid = $2 ",message.guild.id,message.author.id) 
      timelastmsg = timedata[0]

this is the code

[harsh pulsar]

@upper basin it returns a string status line and they expect you to parse it... https://github.com/MagicStack/asyncpg/issues/311

that's kind of wack imo

https://github.com/MagicStack/asyncpg/issues/359

https://github.com/encode/databases/issues/61#issuecomment-699499574

[upper basin]

oof

is there any other way i can do it? like set a variable to true if that query was succesful

[harsh pulsar]

it doesn't sound that hard to parse for what it's worth

[upper basin]

i have no idea how to get the status line

[harsh pulsar]

con.execute returns the status line as a string

it doesn't return a cursor

[upper basin]

ahh got it, thanks!

[grim vault]

You could also just do the select first and if that has no result do the insert.

[upper basin]

..o yea that didnt occur to me lol

one final question(hopefully), how do you commit changes?

do i acquire a connection and then await connection.commit()?

[upper basin]

also known as auto-commit.``` does this mean i dont need to commit?

[opaque grotto]

def with_commit(func):
    def inner(*args, **kwargs):
        func(*args, **kwargs)
        commit()

    return inner


@with_commit
def build():
    if isfile(BUILD_PATH):
        scriptexec(BUILD_PATH)


def commit():
    cxn.commit()


def close():
    cxn.close()


def field(command, *values):
    cur.execute(command, tuple(values))

    if (fetch := cur.fetchone()) is not None:
        return fetch[0]

Can anyone help me

at the scriptexec?

[harsh pulsar]

it means the opposite. unless you deliberately and specifically start a transaction, you don't need to start a transaction or commit

every insert, update, etc. will be committed automatically

[upper basin]

ahh thanks!

[clear lynx]

i'm trying to create a ttl index in mongodb, but instead of deleting one document each time (different timestamps), it deletes all the documents at the same time. How is that possible?

[tight delta]

Hello everyone. I had a question

I had a .csv file and I converted it into dataframe and later into xlxs in one sheet. But what I want is to segregate data to dataframe into 4 sheets depending on one of the column’s data

[last igloo]

Ignoring exception in on_ready
Traceback (most recent call last):
File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\discord\client.py", line 343, in _run_event
await coro(*args, **kwargs)
File "C:\Users\38050\Desktop\ds bot\bot.py", line 55, in on_ready
cursor.execute(f"INSERT INTO users (name, id, login, password, cash, lvl, xp, rub, welc) VALUES ('{response.author}','{response.author.id}','{login}','{password}',0,1,0,0,0)")
File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 538, in execute
self._connection.handle_unread_result()
File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\mysql\connector\connection.py", line 1277, in handle_unread_result
raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found

[storm wind]

Pydantic / MongoDB best practice question.
I have a relatively simple mongoDB, and a lot of the collections have relationships to each other

For example, I have 'session' that has some attributes, and also links to two remote records events (based on a unique ID)

I'd like my model object to be a bit 'self-contained' so that when I instantiate a session model object, I can say session.events and it will return me a list of event model objects

so that means that my session Model should be able to actually lookup the events (instantiate them) internally.

are there patterns here already that I might be missing?

[torn sphinx]

Can someone explain error handling with pymongo in discord.py?

[storm wind]

One solution I had considered is to always instantiate my model objects with a pointer to the mongodb so they can do the 'find' internally..

but I'm not sure how to add an attribute to a BaseModel record that is not serializable.

for example this:

class RespawnEvent(BaseModel):
    db: pymongo.database.Database
    event_type: EventType
    before_event_record_uuid: UUID # These need to be looked up 
    after_event_record_uuid: UUID # These need to be looked up

the db is not serializable...

I know how to exclude it from serialization, but I don't know how to leave it in the 'init' but not validate it.

[storm wind]

import pymongo

[torn sphinx]

facepalm I thought I did.

[storm wind]

Maybe I'll simplify my question. How can I have a Pydantic BaseModel class like this:

class Person(BaseModel):
    db: pymongo.database.Database
    name: str
    age: int
    weight: int

that I can instantiate like this:

data = {'name': 'Bob', 'age': 17, 'weight': 140}
mongoDB: pymongo.database.Database = client.db
myPerson: Person = Person(db=mongoDB, **data)

[storm wind]

Welp, answered my own question... LOL

class Config:
  arbitrary_types_allowed = True

[gusty mulch]

Okay so I have a little bit of sql and a little bit of python/discord.py in this one. My sql can be found here: https://sourceb.in/6bsV8oKbP0 and my python/dpy can be found below.

# In my bot.py file - Not my main file btw
async def get_user(self, member):
     async with self.pool.acquire() as conn:
         res = await conn.fetchrow('SELECT * FROM "members" WHERE member=$1', member)
            if not res:
                res = await conn.fetchrow(
                    "INSERT INTO members VALUES ($1, $2, $3,) RETURNING *", # $4, $5, $6, $7, $8, $9, $10, $11
                    member, None, None,)
        return res

# In my test.py file - it's it's own cog
import logging
import discord
import io

from config import developerid
from discord.ext import commands
from discord.utils import get

log = logging.getLogger(__name__)


class log(commands.Cog):
    def __init__(self, bot):
        self.bot = bot

    @commands.command(description = "Used for testing.", usage = "test", hidden = False)
    async def test(self, ctx, member: discord.Member):
        member = await self.bot.get_user(member)
        mistakes = member[3] + 1
        async with self.bot.pool.acquire() as conn:
            await conn.execute("UPDATE data SET mistakes=$3 WHERE member=$1", mistakes, ctx.author.id,)
            await ctx.send(embed = discord.Embed(description = f"Incrimated value to {mistakes}.", colour = self.bot.primary_colour))

def setup(bot):
    bot.add_cog(log(bot))
```I am trying to make it so that I can track how many times a user joins one of the servers in the db and how many mistakes they have made (it's a part of my counting cog).

[dense barn]

Can I duplicate a database/table in postgres?

[fast jungle]

so I started setting up SQLite browser for my discord bot

is there any documentation to help me do this?

[bitter bone]

My crud function:

The DB

This is how i check if the game existed or not:

But eventhough it exists and is shown in the DB

I get this error:

[pure sleet]

what's the * argument for here?

[fierce sand]

Are phone hardware good enough to run a discord bot

[pure sleet]

you're thinking of turning your phone into a server? bruh

[fierce sand]

Well i have a budget problem so...

Ima turn nokia into AWs

[pure sleet]

just get some cheap shared directadmin hosting that supports python/nodejs apps

you can get away with paying a few bucks a year if your app is not that demanding

[bitter bone]

to make it a keyword argument

[austere portal]

You can duplicate a table

CREATE TABLE new_table AS existing_table```

That should do it

[somber plinth]

can someone guide me on how to get started with building web applications in python..? the only modules I know to use are tkinter and pyautogui as of now

[prime kelp]

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

how do i get the credentials at the top?

is postgres the best database?

[neon fable]

i need help

no no no what ewer

sorry

[prisma wagon]

helpme

can someone

[grim ether]

@client.event
async def on_message(message):
    databas = motor.motor_asyncio.AsyncIOMotorClient('localhost', 27017)
    database = databas["bruhbot"]
    blacklistcoll = database['blacklist']
    blacksearch = await blacklistcoll.find_one({'': message.author.id})

    if blacksearch:
        return
    await client.process_commands(message)

@client.command()
@commands.is_owner()                           
async def blacklist(ctx,member: discord.User):
    databas = motor.motor_asyncio.AsyncIOMotorClient('localhost', 27017)
    database = databas["bruhbot"]
    blacklistcoll = database['blacklist']
    await blacklistcoll.insert_one({'id': member.id})
    await ctx.send(f'{member} is now blacklisted!')

Help me

It didn't store the id to database

[lilac tusk]

Hello guys

Is there any difference in the syntax of Flask Sqlalchemy when working with the different databases like Postgresql and Sqlite?

Or the syntax is always same although using different databases ?

[pure sleet]

it's pretty much the same

[lilac tusk]

so it's not fully same ?

[pure sleet]

i just said it's the same. the difference might be the way you specify your connection uri

[lilac tusk]

thanks bro

[austere portal]

You need to start the postgres server

[austere portal]

And get the credentials from it

[upper basin]

i need some help regarding a pgsql statement

create table if not exists userlevels
(guildid bigint, 
userid bigint, 
level bigint,
xp bigint, 
lastmsg real, 
unique(userid, guildid))

is the table

data = await self.bot.primedb.fetchrow(f'''select rank,xp from(select userlevels.*, rank() over (order by xp desc) as rank from userlevels) userlevels where userid = $1''',user.id)
await ctx.send(f"data is {data}")

nexttenusers = await self.bot.primedb.execute(f"select userid, xp, level from userlevels where xp <= $1 order by xp desc LIMIT $2",data[1],10)
print(underusers)

await ctx.send(f"rank : {data[0]} totalwishcount : {data[1]} \n next 10 users : {nexttenusers}")

is the code

nexttenusers gives me SELECT 10 as output

data is giving me correct output, i cant figure out nexttenusers

for more context, im trying to get the rank of the mentioned user and then the ranks of ten users below that

[grim vault]

execute() returns a status line which tells you that the select is indeed 10 rows. You'll need fetchall() I think it's called.

[upper basin]

o shit i missed that oof my bad

[grim vault]

You also need to add ... AND userid != $2 order by xp desc LIMIT $3", data[1], user.id, 10), otherwise it's not the next 10 users.

[upper basin]

wouldnt it just give the record of the single user then?

[grim vault]

No? I just added the not equal to the id of the user you got the rank of which is included in xp <= $1 (see the less than or equal the xp you selected in the first place).

[upper basin]

oh once again i have missed the not equal

need to get my glasses checked..

hmm thank you!

appreciate the help!

[gray surge]

Can you run databases on a TCP server

[granite latch]

how come the leafpages be 0.1 * 1.5B
B = no. of pages
then why arent they considering the internal nodes?
also why are they multiplying the 10 with 0.67R when they compose of only 1/10th of the rows
shudnt it be 0.1 * 0.67*R?

ppl can anyone explain me heap file with unclustered B+ tree index?
im reading a book rn
could u please explain me the following?

[brazen charm]

I mean general server based dbs all run on the tcp layer so... yes

[gray surge]

alright, thanks :D

[torn sphinx]

so with this

    async def cog_command_error(self, ctx, err):
        if isinstance(err, pymongo.errors.DuplicateKeyError):
            print("document already exists")
        return await super().cog_command_error(ctx, err)```I still get
```transfer loaded
A member has joined
{'_id': 774714204703031306, 'wallet': 0, 'bank': 0}
Future exception was never retrieved
future: <Future finished exception=DuplicateKeyError("E11000 duplicate key error collection: dusk-bank.bank index: _id_ dup key: { _id: 774714204703031306 }, full error: {'index': 0, 'code': 11000, 'keyPattern': {'_id': 1}, 'keyValue': {'_id': 774714204703031306}, 'errmsg': 'E11000 duplicate key error collection: dusk-bank.bank index: _id_ dup key: { _id: 774714204703031306 }'}")>
Traceback (most recent call last):
  File "/usr/lib/python3.8/concurrent/futures/thread.py", line 57, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/collection.py", line 698, in insert_one
    self._insert(document,
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/collection.py", line 613, in _insert
    return self._insert_one(
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/collection.py", line 602, in _insert_one
    self.__database.client._retryable_write(
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1498, in _retryable_write
    return self._retry_with_session(retryable, func, s, None)
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1384, in _retry_with_session
    return self._retry_internal(retryable, func, session, bulk)
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1416, in _retry_internal
    return func(session, sock_info, retryable)
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/collection.py", line 600, in _insert_command
    _check_write_command_response(result)
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/helpers.py", line 226, in _check_write_command_response
    _raise_last_write_error(write_errors)
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/helpers.py", line 207, in _raise_last_write_error
    raise DuplicateKeyError(error.get("errmsg"), 11000, error)
pymongo.errors.DuplicateKeyError: E11000 duplicate key error collection: dusk-bank.bank index: _id_ dup key: { _id: 774714204703031306 }, full error: {'index': 0, 'code': 11000, 'keyPattern': {'_id': 1}, 'keyValue': {'_id': 774714204703031306}, 'errmsg': 'E11000 duplicate key error collection: dusk-bank.bank index: _id_ dup key: { _id: 774714204703031306 }'}```

[stray moss]

Traceback (most recent call last):
  File "main.py", line 11, in <module>
    asyncio.run(main())
  File "/usr/lib/python3.8/asyncio/runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "/usr/lib/python3.8/asyncio/base_events.py", line 616, in run_until_complete
    return future.result()
  File "main.py", line 8, in main
    thing = await collection.insert_one({"_id":1, "exchange_rate": 50000})
RuntimeError: Task <Task pending name='Task-1' coro=<main() running at main.py:8> cb=[_run_until_complete_cb() at /usr/lib/python3.8/asyncio/base_events.py:184]> got Future <Future pending cb=[_chain_future.<locals>._call_check_cancel() at /usr/lib/python3.8/asyncio/futures.py:360]> attached to a different loop

hey guys, what does this error mean and how would I fix it?

[pure cypress]

You probably created/used an object that called get_event_loop before asyncio.run was executed. Since asyncio.run creates a new event loop, that aforementioned object wound up attached to a different event loop.

[stray moss]

yeah

motor probably does that

[velvet horizon]

Had this error come up also when using uv-loop, but not when using base-asyncio

[granite latch]

anyone?

[torn sphinx]

guys help, i want to make an program to flip through a list of games in an excel sheet column and randomly choose one using pandas. but how do i designate a row as a dataframe?

[glacial forum]

can someone help me

[pearl iron]

seems like u found

[glacial forum]

yes

[pearl iron]

put question so u dont have to wait for response

[wide umbra]

what are the data bases in python

[pearl iron]

Python supports various databases like SQLite, MySQL, Oracle, Sybase, PostgreSQL, etc. Python also supports Data Definition Language (DDL), Data Manipulation Language (DML) and Data Query Statements. The Python standard for database interfaces is the Python DB-API.

@wide umbra

[fierce sand]

Does python support notMySQL?????

[last igloo]

File "C:\Users\38050\Desktop\ds bot test1\bot.py", line 35, in on_ready
if cursor.execute(f"SELECT id FROM users WHERE id = {response.author.id}").fetchone() is None:
AttributeError: 'int' object has no attribute 'fetchone'

[exotic adder]

If anybody has the time, I'm trying to learn how to store logins/create them with sqlite3. Already have functional gui application for it. In addition, being able to send the program on another network to communicate with my personal DB

Q: Since Sqlite is serverless, will the networking not work as intended?

[upbeat slate]

Hello, i just started using SQL and need some help

I have this command that I would like to use to add people to hornyjail for 2 minutes

my question is, how can I check if a users ID is already added into the table?

my table has pingid(integer) and date(timestamp with timezone)

Help much apreciated

[uneven stream]

if I wanna do something like _ in list in sql filter, do I do it like
WHERE _ = foo1 OR _ = foo2 OR _ = foo3...?

[brazen charm]

depends on the implementation

generally you can use the IN operator to do column IN (1, 3, 4)

generally this doesn't allow arbitary array sizes though of options, postgres gives an any() function to do that

[whole pendant]

Should I make UUID field for my post model for social media website or integer is okay

[upbeat slate]

please someone?

[uneven stream]

thanks!

[ionic pecan]

what have you tried so far?

[ionic pecan]

you can have multiple clients accessing sqlite at the same time, but not across hosts, no

[ionic pecan]

what is "notMySQL"? is it "other databases than MySQL"?

[ionic pecan]

something to be aware of though, is that postgresql may sometimes not use indices with = ANY where it would use them with IN, i learnt that the hard way past week :P

[ionic pecan]

integer is fine, but you need to be aware that if you link to posts by ID and not e.g. a slug ("mywebsite.com/blog/the-great-war-around-lemons-presence"), people may figure out how many posts you have, and if you hide / delete posts. but that's pretty much the worst part about it

that said, a slug is the best thing to use for this

https://stackoverflow.com/a/427160 has some explanation on it

[obsidian mortar]

heloo

anyone could help me with SQL server?

i just learn about database and wanna show some table with query :"

i've a table like this

and I want to make some queries to create a stored procedure for
displays data from budget submissions, where is the submission period
are in a certain year, certain semester and submissions are made on
specific date range, then you are also asked to create a view
to display data containing the school year, semester, beginning balance, balance
the end is taken from the number of submissions - total nominal transfer, date
submission, date of issue, transaction code, type of transaction, description,
credit is taken from the number of applications, debits are taken from expenses, the remaining
the balance is taken from the remaining balance in the expense table.

[fierce sand]

You can create one now!

Just tell someone to make a mySQL and then try to connect to it, GG you just create a not

Sorry i can't complete the typing because my screen is broken and i cant type Q so i need to use google handwrote in order to use it

[shadow bobcat]

... that's not how mysql works

I mean, there is no "notMySQL" database

[ionic pecan]

yeah i think that's a relic of the google handwriting thing maybe

[burnt turret]

If you're thinking of a remote MySQL database server, yes the standard mysql connector libraries can connect to them

[burnt cloak]

hey, does this channel support for a module??

[ionic pecan]

this looks a lot like an assignment. excluding that, what have you tried already?

[ionic pecan]

for database-related modules, yes

[obsidian mortar]

yes its my assign ment :"

[obsidian mortar]

i tried to write like this

[ionic pecan]

okay, I see

so the text above talks about stored procedures, have you worked with those yet?

[obsidian mortar]

yeah.. i need to make a queryabout stored procedure :"

but i really dont know how

i just know select :"

[ionic pecan]

have you learnt about stored procedures as part of the course giving you the assignment?

[ionic pecan]

i mean, yes, but why?

[burnt cloak]

does this channel support for modules??

[whole pendant]

you replied me ?? if yess i think there may me multiple posts by adding both i can get result faster

[burnt cloak]

wdym

[ionic pecan]

i replied to you above, did you see that?

[burnt cloak]

i installed a module by using pip install pyautogui in cmd but VScode say pyautogui is not defined

[ionic pecan]

you won't make retrieving a post faster by using a primary key consisting of multiple columns, you need an index for that, and whilst sane database engines usually place an index on the primary key, having multiple fields in it still doesn't speed it up as far as i'm aware

that said, i also wouldn't care about optimizing this too much. retrieving something like this goes very very fast, most of the time of a request for a site on your blog will be spent in your python code, not database

[burnt cloak]

help me

[ionic pecan]

this sounds a bit like a tooling issue, so i would suggest trying #editors-ides with that. maybe you're in a virtual environment, or vscode is, who knows, but this is the wrong channel

a database isn't a bad choice, but which actions would you want to put in there?

sorry, i still don't quite know what you want to track. what does instagram track there?

i would assume that's implemented as a sort of notification system, since when you hit follow, the other user gets a notification

yeah, a relational database sounds like a good idea for it then, because you can associate notifications with a user id

please don't delete your messages

there's other people who can learn from what you asked

[torn sphinx]

Have you tried doing a crud system but instead of one table , there is two tables?

[autumn nymph]

can someone help me, I can't startproject on django, path incorrect somehow. I am using vsc

[ionic pecan]

you're probably looking for #web-development with that question

[autumn nymph]

thanks

[torn sphinx]

whats up with heroku deleting sqlite data every 24 hours?

[digital rose]

has anyone inserted 100,000s of rows into redshift before? I know it's best to use s3 for that, but I'm not 100% sure if I have that available. The script will also live on a server, so local storage isn't really an option

[exotic eagle]

Is it only SQL in here, or can I ask Flask/SQL related

Basically, I'm working with Python and MySQL, both in their respective Docker-containers. My budget table has columns: incomes, expenses, savings and investments. The data will come in as a dict, that will be based on following JSON: {"1":{"amount":"1.00","category":"Juju","type":"incomes"},"2":{"amount":"211.00","category":"Jujsau","type":"incomes"},"new":[]} My idea is to format the query dynamically based on the type-key present in the dict. I'm having the following error thrown: mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''incomes' = 1.0, categories = 'Juju' WHERE id = 1' at line 1

                                        UPDATE budget SET %s = %s, categories = %s WHERE id = %s
                                    """
                    updateTuple = (innerDict.get("type"), float(innerDict.get("amount")), innerDict.get("category"), int(key), )
                    cursor.execute(updateQuery, (innerDict.get("type").replace("'", ""), float(innerDict.get("amount")), innerDict.get("category"), int(key), ))

Basically in the query, the column name i want it to be formatted based on the type-key. My guess it that when I run execute, all formatted string types will have ' added, but that causes issues when its the column name

[upbeat slate]

well...

i tried using try except

turns out that selecting what does not exists does not raise any error

also sorry for late reply

[torn sphinx]

Have you guys work with sqlite and tkinter where there is a crud system. But instead of one table there are two separate tables?

[ionic pecan]

can you show the code you had when you were selecting?

[upbeat slate]

let me re write it sec

[ionic pecan]

something like this should ideally be done in two steps:

1. where you build the query itself without the data (column names only, and make sure to sanitize this properly, functions named something like "quote identifier" are usually what you want) using something like str.format
2. where you pass the remaining arguments to cursor.execute.

so for your code above i'd do something like this:

                    updateQuery = """
                                        UPDATE budget SET %s = %%s, categories = %%s WHERE id = %%s
                                    """
                    # build a query with the proper column name
                    # "quote_ident" is arbitrarily assumed to exist here,
                    # see maybe something like https://stackoverflow.com/a/64412951
                    query = updateQuery % (quote_ident(innerDict.get('type')),)
                    # run the formatted query with the values sanely interpolated
                    cursor.execute(query, (float(innerDict.get("amount")), innerDict.get("category"), int(key)))

does that make sense? once again, if the type / the column name is not sanitized properly and this is artbirary user data this will launch you into a world of hurt, so watch out. the %%s basically converts it to %s after the percent formatting for query, since %% -> %

[whole pendant]

i want to make a **post **model in my project for my social media project which like the instagram post model can you tell me what kind of fields should a have to added

[upbeat slate]

 @client.command()
async def hornyjail(ctx, arg1, ping : discord.Member):
    try:
        ping = ping.id
    except AttributeError:
        pass

    if arg1 == "add":
        try:
            datac.execute(
f'''
select {ping} from hornyjail
'''
)
            print('user already enjailed')
        except:
            # i just get timenow and store it with ping in hornyjail

[upbeat slate]

there

[ionic pecan]

do you have other code that uses your database in your project yet?

[upbeat slate]

nope

that is the very first one

[ionic pecan]

so you haven't worked with SQL before?

[exotic eagle]

Ah I see, lovely stuff thank you very much sir

[upbeat slate]

nope

first time

[ionic pecan]

okay, that's fine. is the table already created and existing? which fields does it have?

[upbeat slate]

it exists and it has pingid and date

[ionic pecan]

and pingid is the user ID of the member, i assume?

[upbeat slate]

yes

i dont know what i missed

[ionic pecan]

okay, so you basically want to filter the records in the table for entries of that pingid. in SQL, that's done using WHERE. for instance, to get all entries of a user with ID 1234, you would use code like this:

user_id = 1234
# case doesn't matter for these words, but it's common practice to
# uppercase these keywords for readability reasons
cursor.execute("SELECT * FROM hornyjail WHERE pingid = %s", (user_id,))
entries = cursor.fetchall()

can you try playing around with that a bit?

[upbeat slate]

Yes leave it to me

[ionic pecan]

like printing out entries for the author of a message

👍

[upbeat slate]

thanks for the tip!

😄

[exotic eagle]

@ionic pecan ok, smth I haven't understood. The quote_ident method I basically need to escape the quote with backticks? So basically, it would work like: ```return str = "{}".format(param)

[upbeat slate]

@ionic pecan

its a nope

[proven arrow]

What is datac?

[upbeat slate]

cursor for databse

[proven arrow]

Why do you want to copy instagrams model? The columns you need are the ones you want your app to have.

[proven arrow]

Well if that’s a global cursor you should avoid using one .

[upbeat slate]

how should i fix it ¯_(ツ)_/¯

also can we please stay on my issue?

i cant handle that much info at once

[proven arrow]

Well that could be part of the issue

[upbeat slate]

okay

how to fix it then

[proven arrow]

Start by being more specific. What’s “a nope”?

[upbeat slate]

basically

i want to check if this pingid exists ion hornyjail

i thought selecting what does not exists wpould raise an error

[formal coral]

is asyncpg by default on auto-commit? I have only used sqlite3 till now so I only know manual commit.

[proven arrow]

Then why do you have a try except? Except happens when there is an exception. Its for handling errors, not when data is missing.

[proven arrow]

yeah

[formal coral]

thanks

[whole pendant]

the thing is that i am making insta clone

[proven arrow]

@upbeat slate To check if an id exists you can use:

cursor = db_connection.cursor()
cursor.execute('select exists (select * from hornyjail where pingid = ?)', (ping_id,))
result = cursor.fetchone() # returns 1/0

Also looking at your code seems you completely ignored the suggestions of other users trying to help you.

[proven arrow]

You need to decide what columns your table should have. The columns you need is for the info you need to store. If you don't know what columns your app needs to store then you need to do a little more background research for your app and think of what you will need.

If you have questions regarding a schema you have already designed then people might be able to help.

[whole pendant]

okay will design first then ask sorry for inconvenience

[exotic eagle]

big man ting wys

i just came off road innit

[ionic pecan]

yes but also no, that's escaping for identifiers that may not be valid identifiers otherwise, not escaping arbitrary user input. that's unfortunately a bit harder :(

do you have a predefined list of columns that can be set here?

your function could just do something like

def sanitize_budget_column(column):
    if column in ('food', 'gas'):
        return column
    raise ValueError(f"unknown column: {column!r}")

[wicked lichen]

Hey

Is ther any module we can use db with python without any framework

[ionic pecan]

sqlite3

[wicked lichen]

PostgreSQL?👀

[harsh pulsar]

psycopg2 for postgres

[ionic pecan]

psycopg2

[wicked lichen]

Ok ok 👀

Thx 🙂

[upbeat slate]

i did not ignore anything my dude

but ty anyways

[wheat swan]

I have mongo db (ATLAS or cloud) how can I use collation.insert_one pls tell
eg - in db I have this json {"_id" : 123, "name" : "ALPHA"}
and I want to add "age" : 18 to it how can I add it ?

[sage summit]

you could find the document first i think and then create a new key value pair to replace it

doc = collation.find_one({"_id": 123})
doc["age"] = 18
collation.replace_one({"_id": 123}, doc)

something like this i believe

[wheat swan]

will replace_one remove _id : 123 and add age : 18

?

[sage summit]

it replaces the document with _id: 123 with the dictionary doc

essentially, the idea is to get the dictionary from the document using find_one and manipulate it to the way you want and use it to replace the document you got it from using replace_one

[wheat swan]

I want it to be json {"_id" : 123, "name" : "ALPHA", "age" : 18 }

[sage summit]

ye that's what it returns then

[sage summit]

but understand this

[wheat swan]

okay thnx

[sage summit]

i have this pipeline atm to function as somewhat of a leaderboard

pipeline = [
            {"$group": {"_id": "$user_id", "points": {"$max": "$points"}}},
            {"$sort": {"points": -1}},
            {"$skip": 10 * (page - 1)},
            {"$limit": 10 * page}
        ]

is there a way to return the rank of a specific user_id using it?

[balmy goblet]

how to start up with database

[sage summit]

wdym

[balmy goblet]

idk

how to set up

and all

[dense barn]

Is it better than asyncpg?

[austere portal]

psycopg2 is synchronous usage and asyncpg is for asynchronous usage.

[dense barn]

Oh lol

Alr

[harsh pulsar]

There's also aiopg which I believe is a more direct async equivalent of psycopg2

Asyncpg does things in a slightly nonstandard way, but arguably easier to use

[austere portal]

yeah, aiopg uses psycopg2

[glacial forum]

anyone know how to display "form submitted successfully" after pressing the submit button in php?

[stray river]

Hey so i am new to database and stuff just learning about mongoDb. So i have a problem, i want to cache data temporarily so i dont have to make a request to database. I implemented cache by storing it in a var as dict and i think there is prob a better way to cache stuff

i also heard of Redis

[white linden]

what's wrong whit this?

[ember terrace]

It depends on your use case. If you're not caching much data and the data isn't super important then it might be fine to just keep it in a dict. For a robust, full featured program, you probably will want to implement some kind of queue with redis, or if you want "batteries included" rabbitmq/celery.

I'm going to update sqlalchemy 1.3 to 1.4 on my server. I know about the migration page which details all the changes, but in practical terms, what's the best way to go about this? Make the changes I think I need and then just hunt for the rest through exceptions and stack traces?

[median wave]

is there a suggested limit of tables a SQLITE DB could have

[proven arrow]

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

It’s more than you’ll ever need

[valid barn]

I'm using pydantic, and want to ensure something passed in is EITHER a Boolean or an Integer.

Anyone has any suggestions?

[median wave]

So a little over 2 billion right

[valid barn]

I'm assuming this is what unions are for but Hella confused 😂

[static stream]

Can someone help me about correct database usage?
https://pastebin.pl/view/raw/9910102a

[ionic pecan]

generally you only want to use the cursor / connection for as long as you need it, so the second form would be better. that said, if you use the database a lot, common practice is to open the database once for the lifetime of the app and then just open a cursor every time you need it. there are a few suggestions i would have for the code regardless, if you're interested:

• await (await... is very hard to read
• your code is vulnerable to SQL injection, check the documentation on passing parameters to cursor.execute

[ionic pecan]

yes, definitely a union.
https://pydantic-docs.helpmanual.io/usage/types/#unions

[ionic pecan]

yes

in practice you should keep it far below that

[median wave]

also can i auto-increment a database based on per guild?

[ionic pecan]

sorry, we can't help you with this screenshot only. is this a python program, is this yours, do you have any tracebacks, logs, which database is this, which libraries are you using ..

[ionic pecan]

this server is for Python, but in general for something like that you would usually use something called a flash message

[ionic pecan]

what do you mean with "auto-increment a database"?

do you want to count something?

[median wave]

Well like when making a database there is a option to put auto-increment but let's say a discord bot has that and is in multiple guilds, the values would increase by one if the command is used in any one of the guilds the bot is in, though how do i make it increase by one only in the guild the command was used in?

I'd guess the value is stored and increased by one each time but is that the only way?

[static stream]

why await (await ... is hard to read? i got no different options to make it work 😅
about SQL Injection, i found out

[austere portal]

Don't use f-strings.

[austere portal]

You can use the AUTOINCREMENT for sqlite.

[median wave]

but thats not per guild

[static stream]

i fixed that already

[austere portal]

you manually increase the value by one

[static stream]

In my code id is already existing value

and it INSERTS it to the table

[austere portal]

no, that wasnt for you

[static stream]

oh sorry lol

[median wave]

So then i wouldn't be using auto-increment right

[ionic pecan]

do you want to have IDs unique per guild? like starting at 1 regardless of how often the command was used on other guilds?

[austere portal]

You can have a command for the guild id and another column indicating the command uses

[median wave]

I think you meant columm for guild id

[static stream]

for row in await (cursor.execute("SELECT name, cash FROM users ORDER BY cash DESC LIMIT 5")):
TypeError: 'Cursor' object is not iterable

[errant knoll]

What would be the best way to store these kind of data entries: https://pastebin.com/GuRLpWZN
Currently it's in a json file but the file is already 5000 lines and its only going to get bigger...

[valid barn]

Thanks mate! 😄

[marsh mango]

where to type this in?

here?

[bright dune]

are you on linux?

[marsh mango]

windows 10

[bright dune]

that's a linux command

[brazen charm]

you'll have sqlite installed by default anyway on windows

just import the module

import sqlite3

[formal coral]

you are running a linux command in a python repl

[upbeat slate]

@client.command()
async def modrole(ctx, arg1, ping : discord.Role = None):
    try:
        ping = ping.id
    except AttributeError:
        pass
    author_id = ctx.author.id
    guild_owner_id = ctx.guild.owner_id
    guild_id = ctx.guild.id

    if author_id == guild_owner_id:
        if arg1 == 'set':
            c.execute('select * from registered_guilds where guild_id = ?', (guild_id,))
            length = c.fetchone()
            if length == None:
                c.execute(
                '''
                insert into registered_guilds (guild_id, modrole_id) values (?, ?)
                ''', (guild_id, ping)
                )
                await ctx.send(f'`the role <@{ping}> is now the moderator role on this server!`')

                database.commit()
            
            else:
                c.execute(
                '''
                update registered_guilds
                set
                    modrole_id = ?
                where
                    guild_id = ?
                ''', (ping, guild_id)
                )
                await ctx.send(f'`the role <@{ping}> is now the moderator role on this server! (UPDATED)`')

                database.commit()
        
        elif arg1 == "show" or arg1 == "display":
            c.execute('select modrole_id from registered_guilds where guild_id = ?', (guild_id,))
            modrolevar = c.fetchone()
            await ctx.send(f'`the moderation role on your server is <@{modrolevar}>`')

        else:
            await ctx.send('`invalid command!`')

    else:
        await ctx.send('`only server owner can use this command!`')

i have an issue with this

for some reason it created a new guild_id instead of updating it

and i sends the first message (without updated)

[austere portal]

yes lol

[upbeat slate]

basicallt how can i check if a row exists in a table?

[snow dock]

help.

using tinydb btw

this is how it's supposed to look

[harsh pulsar]

@snow dock maybe you're supposed to use a dict, not a string? {"0": 8, ...} instead of '{"0": 8, ...}'

[snow dock]

ty worked!

[harsh pulsar]

@snow dock pymongo works the same way. you pass it python objects, and it converts to json/bson internally

[snow dock]

ah k ty
I spent 2 hours trying to fix and didn't think of trying what you said because I thought it'll gimme an error or something

I'm new to python so I didn't have a full idea on how this works

[ionic pecan]

your code looks good to me! nice use of paramtrization to prevent SQL injection. can you check the table manually with select guild_id from registered_guilds to see for 100% that the correct ID is in there?

[stray river]

ok thanks i will just store cache in a dict then for now maybe upgrade later

Does anyone use Motor (Asyncio MongoDb) client

I just started using it, there is no type hinting nothing 😦

it has like very haky code just to support one more framework

[open iris]

Check type if dict or list => json.dumps(<YOUR_DICT_NAME>) that will convert to String then json.loads(<YOUR_DICT_NAME>).
If type is string => json.loads(<YOUR_DICT_NAME>)

maybe that solve.

[narrow moth]

Is it safe to use f-strings if you parse them through a function that detects database syntax commands like (something better than) re.search(r'(\);)')?

[manic jungle]

So I've been working on a discordpy bot for the last week or so, and just got onto working with MongoDB. But now I'm getting an issue where I get this error [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate
I've been googling around about it for 2 hours and trying different solutions but nothing has worked yet. Here's the code:

import pymongo

mongoURI = "hidden"

cluster = pymongo.MongoClient(mongoURI, serverSelectionTimeoutMS=5000, tls=True)
db = cluster['test']
collection = db['test']

counter = collection.find_one({'_id': 'counter'})

[stray river]

try setting tls to false maybe

[manic jungle]

That was one of the solutions but that'll bypass TLS which isn't really all that safe right?

The solution I was using just to test was adding ssl_cert_reqs='CERT_NONE' in there

but that bypasses the cert which is a no-no right?

[stray river]

i dont think its much of a security issue, you need a password to access db, if you wanna use ssl encryption then you will have to get a certifcate and verify using that

i am not completely sure how tls works tho

[manic jungle]

Yeah, pymongo wants to use TLS I think?

[stray river]

i dont wanna comment on it bcs i will endup giving wrong suggestion not completely sure of how tls works and how to use it

[fierce sand]

Does saving in your own server ideal? Or cloud is better by saving data in cloud and then thw only file saved in server is somesort of tokens?

[manic jungle]

I'm using MongoDB Atlas, with a Shared/Free Cluster cause this is a pretty small project

[stray river]

you will need to generate ssl certificate and self sign it

[stray river]

same i am using that too,

[manic jungle]

I'm just not really sure how to go about that?

[stray river]

https://aboutssl.org/ssl-certificate-for-localhost/

[manic jungle]

Is openssl available on Windows or only Mac/Linux

[stray river]

Or you will have to buy a certificate from a certified authority

openssl comes pre-installed in linux i think

not sure about windows

Just leave tls if you are working on a small project

[stray river]

So i am using Motor, and when i connect to db, it shows 3 connections connected to the db

[mint tundra]

I have a Pandas dataframe that's being updated every minute (or more) with another row at the bottom on the table.
index is a timestamp.
i would like to maintain only 3 **consistent **rows (the moment another row is inserted, the head row will be deleted.
how would you do that?

That's an example:
9:58
9:59
10:00
10:02

Here is what I have tried:

        while (self.df.tail(1).index - self.df.head(1).index).seconds[0] > 120:
            self.df.drop(index=self.df.index[0], inplace=True)

I think that's not the right way lol 😅

[ionic pecan]

do you mean using them for running SQL commands and sanitizing user input through something like that regex?

[unkempt prism]

No while loops run this immediately after you add the row.

self.df = self.df.tail(3)

See https://stackoverflow.com/questions/14663004/how-to-get-the-last-n-rows-of-a-pandas-dataframe

[mint tundra]

thanks man, I don't only need the last 3 rows, i have to use pandas.between_time to make sure the last 3 are consistent as well, that means less than 3 minutes can be kept after every update

I'm trying to implement it but pandas shouts
Cannot convert arg [Timestamp('2021-07-16 14:28:00+0000', tz='UTC')] to a time
any idea how to solve it ?

THREE_MINS = relativedelta(minutes=2)
last_bar = bars.index[-1]
three_mins_ago = (last_bar - THREE_MINS)
print(bars.between_time(start_time= three_mins_ago, end_time=bars.index[-1]))```

[outer crypt]

if anyone is familiar with SQL and can give me a hand that would be great ^^

i'm trying append an element to avatar_history which is type TEXT[] when there is a conflict for user_id but for some reason it just keeps on overwriting the last element instead of extending the list by 1. I'm not exactly sure what's going wrong and was wondering if I could get some advice

INSERT INTO useravatar (user_id, avatar_history)
VALUES ('2', '{866266318125531136}')
ON CONFLICT (user_id)
DO UPDATE SET
   avatar_history = array_append(EXCLUDED.avatar_history, '5555555');

running the code above twice i will end up with
866266318125531136, 5555555
but if i run it a third time (assume I modify 5555555 to some other digit, i end up with
866266318125531136, [some_other_digit]

[marsh mango]

🤦 how is this command for windows?

[formal coral]

in windows, its already there

[unkempt prism]

Seems messy. Just select a subset of the dataframe first with all that’s in the last 3 minutes. See https://pandas.pydata.org/pandas-docs/dev/getting_started/intro_tutorials/03_subset_data.html

[narrow moth]

Either/or, I can set up users to view-only in my current project but I've seen the "don't use f-strings" line a few times so I was wondering what the best practice is for using python to interact with a database in a situation that users need to insert/update.

[ionic pecan]

what kind of application are you building?

[harsh pulsar]

@mint tundra @unkempt prism between_time is for "time" by itself, not "date+time". I agree, just use .loc to filter

x.loc[(x >= min_time) & (x <= max_time)

[harsh pulsar]

You can do the sanitization yourself, but it's hairy. Let the database do it for you. However, if you need to construct a query with dynamic table and column names, then your only option is string interpolation. In that case, I believe the best practice is to have the user select from a hardcoded list of valid/known tables or columns, rather than trying to sanitize and quote the input properly

Or you can place firm restrictions on user inputs, like "must be ascii alphanumeric with no whitespace". That way you can safely wrap it in "" to quote it (or [] if your db uses that)

[narrow moth]

Just a little ("little", uses postgres because learning is fun) database for RPG minis in my DnD game. Realistically, I'm the only one who has miniatures but others have a few.

Ascii alphanumeric should suffice for all the fields we'd use. The French did some great sculpts, and the Germans are also pretty decent, but we'll cröss that bridgé when we get to it.

Thanks.

[mint tundra]

how should i define min_time and max_time if the indexes are timestamps ?

[harsh pulsar]

like you did, with pd.Timestamp

[upbeat slate]

i figured out the issue

i was storing as reals

so when they are fetched they are as a scientifical number

i fixed by storing them as integers

😄

ty for the reply anyways

[ionic pecan]

👍

[mint tundra]

was able to solve this with to_pydatetime() and between_time()
@harsh pulsar @unkempt prism Thanks!

[torn sphinx]

Do databases usually read the whole file or just a few lines?

[hexed estuary]

Databases usually don't need to read the entire database to perform a simple query, that's their thing

they usually use B-trees I believe, so they can locate a row with a specific key in O(log n)

[delicate field]

@unreal kraken 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!

[last igloo]

What port are using for mysql.connector? (For cursor.execute, I open it on my server)

[edgy lotus]

    if (typeof data !== 'string') throw new error(errorMessage);
                                        ^

RangeError [MESSAGE_CONTENT_TYPE]: Message content must be a non-empty string.
    at Function.verifyString (C:\Users\moham\OneDrive\Desktop\My Stuff\Campfire\node_modules\discord.js\src\util\Util.js:406:41)
    at MessagePayload.makeContent (C:\Users\moham\OneDrive\Desktop\My Stuff\Campfire\node_modules\discord.js\src\structures\MessagePayload.js:113:22)
    at MessagePayload.resolveData (C:\Users\moham\OneDrive\Desktop\My Stuff\Campfire\node_modules\discord.js\src\structures\MessagePayload.js:128:26)
    at TextChannel.send (C:\Users\moham\OneDrive\Desktop\My Stuff\Campfire\node_modules\discord.js\src\structures\interfaces\TextBasedChannel.js:165:61)      
    at Query.<anonymous> (C:\Users\moham\OneDrive\Desktop\My Stuff\Campfire\commands\xp.js:18:25)
    at Query.<anonymous> (C:\Users\moham\OneDrive\Desktop\My Stuff\Campfire\node_modules\mysql\lib\Connection.js:526:10)
    at Query._callback (C:\Users\moham\OneDrive\Desktop\My Stuff\Campfire\node_modules\mysql\lib\Connection.js:488:16)
    at Query.Sequence.end (C:\Users\moham\OneDrive\Desktop\My Stuff\Campfire\node_modules\mysql\lib\protocol\sequences\Sequence.js:83:24)
    at Query._handleFinalResultPacket (C:\Users\moham\OneDrive\Desktop\My Stuff\Campfire\node_modules\mysql\lib\protocol\sequences\Query.js:149:8)
    at Query.EofPacket (C:\Users\moham\OneDrive\Desktop\My Stuff\Campfire\node_modules\mysql\lib\protocol\sequences\Query.js:133:8) {
  [Symbol(code)]: 'MESSAGE_CONTENT_TYPE'
}``` can any1 help me with this error?

[silk star]

Hey all. I wrote the article about the columnar database DuckDB. Check it out if you are interested in analytical workload queries. https://4l1fe.github.io/articles/2021-07-14/

[green phoenix]

Hi. I need to access and convert Lotus Notes 6.51 databases. Anybody had any experience with that or a know-how?

[ionic pecan]

usually you don't need to pass a port, as it will use the default mysql port (3306)

[ionic pecan]

this server is for python

[edgy lotus]

Uhm, there is channels for other languages too yk

[ionic pecan]

databases usually are not based on a single file or sets of lines, postgresql for instance organizes its data storage in a binary format across a sets of files, mostly unrelated to the actual contents of the database. that said, most databases (and your operating system) have a memory buffer that they will read from before going to raw disk access

[dense barn]

How would I connect to my postgres database which uses asyncpg in heroku?

[ionic pecan]

does heroku provide you with connection credentials?

[dense barn]

Yea, they have a add on called postgres

Idk the exact name but they have a add on for it

[ionic pecan]

okay, so wait, what do you have right now? a postgresql database on heroku via that add on, or just your app?

[dense barn]

Just my app but I have the add on installed, tho I don't know how it works

[ionic pecan]

this seems to explain it: https://towardsdatascience.com/set-up-heroku-postgresql-for-your-app-in-python-7dad9ceb0f92 heroku should give you a database url after creating the add-on

[dense barn]

Ahh

Ok ill look into it thanks!

[ionic pecan]

👍 feel free to ask here if you run into any problems

[dense barn]

Free tier only gives you 10000 rows, that's gonna be a problem

Oh God it's worse than I thought

[uneven stream]

is there a better way to do this?

async with self.db.execute(query) as cursor:

    result = await cursor.fetchone()
    if result is not None and keys[0] != '*' and len(keys) == 1: result = result[0]

# --------------------------

async with self.db.execute(query) as cursor:
    result = await cursor.fetchall()
    if not result: result = None
    elif keys[0] != '*' and len(keys) == 1: result = [_[0] for _ in result]

[torn sphinx]

code?

Also, why does it not update my database even though commands work that require the db to be used (like custom prefix commands)?

[last igloo]

Traceback (most recent call last):
File "/usr/local/lib/python3.9/site-packages/discord/client.py", line 343, in _run_event
await coro(*args, **kwargs)
File "/home/bot/bot.py", line 49, in on_ready
cursor.execute(f"INSERT INTO users (name, id, login, password, cash, lvl, xp, rub, welc) VALUES ('{response.author}','{response.author.id}','{login}','{password}',0,1,0,0,0)")
File "/usr/local/lib/python3.9/site-packages/mysql/connector/cursor.py", line 568, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "/usr/local/lib/python3.9/site-packages/mysql/connector/connection.py", line 686, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
File "/usr/local/lib/python3.9/site-packages/mysql/connector/connection.py", line 418, in _send_cmd
return self._socket.recv()
File "/usr/local/lib/python3.9/site-packages/mysql/connector/network.py", line 248, in recv_plain
raise errors.InterfaceError(errno=2013)
mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query

pls help

i run it on pc where is this db

but it's give me error

[torn sphinx]

What is a good database to use for dicord.py that is avabile for phone / android?

[pure sleet]

maybe sqlite

[tranquil zinc]

for discord py? im trying to figure tht out too

a very easy and beginner friendly and light way to maka databases is tiny.db

[torn sphinx]

Im using sqlite actually

[tranquil zinc]

its very compicated.......

[torn sphinx]

It wasn't updating the DB. Sadly afterwards the disk was malformed and I now have to start with the the bot again.. most if my features and comments rely on them

[torn sphinx]

Not for me.

I guess not when you get the hang of it

[brave bridge]

TinyDB seems to be just a wrapper over a JSON file, doesn't sound like a good idea to me

[gritty marsh]

Any help would be greatly appreciated

[torn sphinx]

I see..

But is it just me or is there something wrong with sql db's?

[torn sphinx]

Do I need to install that?

[pure sleet]

no

[last igloo]

hey how i can fix it

[torn sphinx]

Says connection lost

[last igloo]

i know

how i can fix it

[torn sphinx]

So reconnect?

[last igloo]

how?

[torn sphinx]

I don’t know, not sure what your code is like

[last igloo]

Ignoring exception in on_ready
Traceback (most recent call last):
File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\discord\client.py", line 343, in _run_event
await coro(*args, **kwargs)
File "C:\Users\38050\Desktop\ds bot test\bot.py", line 49, in on_ready
cursor.execute(f"INSERT INTO users (name, id, login, password, cash, lvl, xp, rub, welc) VALUES ('{response.author}','{response.author.id}','{login}','{password}',0,1,0,0,0)")
File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\pymysql\cursors.py", line 148, in execute
result = self._query(query)
File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\pymysql\cursors.py", line 310, in _query
conn.query(q)
File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\pymysql\connections.py", line 548, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\pymysql\connections.py", line 775, in _read_query_result
result.read()
File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\pymysql\connections.py", line 1156, in read
first_packet = self.connection._read_packet()
File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\pymysql\connections.py", line 692, in _read_packet
packet_header = self._read_bytes(4)
File "C:\Users\38050\AppData\Roaming\Python\Python39\site-packages\pymysql\connections.py", line 748, in _read_bytes
raise err.OperationalError(
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

[last igloo]

when i use PyMysql

[torn sphinx]

How to use it?

[pure sleet]

man, that's what you have to figure out

[torn sphinx]

Okay thanks

[torn sphinx]

sqlite3?

[last igloo]

cursor.execute(f"SELECT id FROM users WHERE id = {response.author.id}")
UnboundLocalError: local variable 'cursor' referenced before assignment

[pure sleet]

if it works for your use case then yes

[last igloo]

connection = pymysql.connect(cursorclass=pymysql.cursors.DictCursor)
cursors = connection.cursor()

[last igloo]

there is ok

[torn sphinx]

Is sqlite3 the same thing as sqlite?

[last igloo]

i delete some secret

[pure sleet]

man, it's a version of sqlite

[last igloo]

sqlite3 is good

use this

[torn sphinx]

Sorry

[wraith python]

Hello, can someone help me to choose the right way to store my data? Not really a Python or db exclusively related question, I know, but Python is definitely involved here.

So, basically I need to store structured but complex data (various "RPG" items of different types with different properties). The thing is I cannot use NoSQL server-client solutions at all.
So now I came to two options: redislite or a bunch of JSONs in a bunch of folders.

I think I can use a relational solution as well but it will be a pain in the ass.

So, if someone has some time, I would really appreciate if you could help me.

[wheat swan]

how to check if _id already exits ? in mongodb (atlas) Python

[wraith python]

Maybe someone here is familiar with ZODB?.. Anyone?..

[wheat swan]

no but do u know mongodb ?

[delicate field]

Hey @steep ingot!

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

[steep ingot]

Why is this re-entering the details for the message into the csv as a new line when i add a reaction?

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

[steep ingot]

@foggy stag your name says to ping for help and I need help with this ^

[foggy stag]

ah, sorry i'm not the best with databases.

[steep ingot]

The issue isn't with the actual database I don't think but could you take a quick look and see if theres a reason a reaction would cause it to write to the db when the only thing that writes to the db is the on_message

[foggy stag]

okay

[steep ingot]

thanks

[foggy stag]

yeah honestly im not sure

[steep ingot]

Ok, thanks. Is there a database pro here who wouldn't mind a ping?

[foggy stag]

@remote plinth

[remote plinth]

what

im not pro anymore

[foggy stag]

lying

[remote plinth]

no-

idk how to use csv

[steep ingot]

D:

[hardy ibex]

So I was using a json file as a db for a while because yes. I'm switching everything to SQL now, but I'm stuck on how I would (or should) structure the table for this, can anyone help with this?

Basically, it's a very basic stock market atm

henry in this example being the company, the owner being identified with their user ID, the investors identified by their user IDs with their value being how much money they've invested in the company

[indigo flare]

I would have a companies table, an intermediate investments table, and an people table,

people -own many> investments
people -own many> companies

companies -many> investments
companies -one> person (owner)

investment -one> person

I think at least

[hardy ibex]

Hmm

[slate terrace]

I'm struggeling to get this to work

UPDATE `tbl_cars` INNER JOIN `tbl_brands` ON `tbl_brands`.`id` = `tbl_cars`.`brand_id` SET %s = %s WHERE `tbl_brands`.`name` = %s AND `tbl_cars`.`model` = %s;

The column field, right after the SET parameter isn't being read properly

This is a variable that depends on a parameter in a command

[pine niche]

yo im new to coding could someone teach me some tricks??

[slate terrace]

Yt guides 😉

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''limited' = 1 WHERE tbl_brands.name = 'Alpine' AND tbl_cars.model = '...' at line 1

[pine niche]

i dont understand yt guides

i want to make a website that if i send someone a bat file when they open it it will send a picture about theere backround

[slate terrace]

Fixed, using +var+

[errant gulch]

Hey, may I ask a quick SQL question here?

what is wrong here : query_4 = "\ SELECT b.store_name, a.date, a.sales_qty \ FROM data_store_sales AS a\ INNER JOIN data_store_cad AS b ON a.store_code = b.store_code\ WHERE a.sales_qty = \ (SELECT MAX(sales_qty) \ FROM data_store_sales);\ "

[grim vault]

Missing spaces before some \

There is also no WHERE clause in the sub-select.

[errant gulch]

it was the spaces

thxs

[steady garnet]

Hi everyone, I have a quick question. How do you convert between csv file toward json

[torn sphinx]

@steady garnet do you care what the final structure looks like?

[steady garnet]

i just need the date_time and 1 column. that column has a crap load of array inside it.

[exotic adder]

so like imagine I use sqlite as the db for my program that requires a login, and i give that file to other people, is it not possible for them to access my locally stored db?

even if i add networking?

[steady garnet]

aghhh can you please explain it in simple english please 😦 It's my 1st time

[steady garnet]

in here I have the Athena by AWS, I was successfully get the data but it some how stuck with csv

[torn sphinx]

file = open("sample.csv", "r")
dict_reader = csv.DictReader(file)

dict_from_csv = list(dict_reader)[0]
json_from_csv = json.dumps(dict_from_csv)

print(json_from_csv)

[steady garnet]

thank you let me try it

[steady garnet]

can I write it out as a separate json?

[torn sphinx]

sure can. I will let you figure that one out

it is just a file

[steady garnet]

It just a file, By using ur example I can convert it to json. how can I write it out?

[steady garnet]

So sorry to bothering you again, the json part only contain 1 frame. My csv has like 1000+ frame. how can I include them

[torn sphinx]

is it a csv file or a frame you are dealing with?

[torn sphinx]

is the .db file password protected
it is just a file

[steady garnet]

csv, I suppose I just need to add the range of it behind ```list(dict_reader)[0:297778]

[wanton kelp]

how can i store a python object in a database ?

[somber breach]

how enter multiple data inside a single row in sqlite using python ?

[prisma girder]

You can use BLOB type and pickle this object but it's unsafe solution

[exotic adder]

the file contains the passwords, I'm just a little too lazy. And what I mean by give that file, is give the program and add networking.

[boreal pond]

how is it possible to migrate from cockroachdb to postgresql

is there anyway to achieving it

[torn sphinx]

                <?php
                while($row = mysql_fetch_array($query)){
                echo '
                 <tr class="entry">
                     <tr>
                     <td style="width: 60px;" class="text-center">'.$row['id'].'</td>
                     <td style="width: 270px;"><a style="color: red; font-weight: bold;" href="/user" target="_blank">'.$row['username'].'</a></td>
                     <td style="width: 90px;" class="text-center">0</td>
                     <td class="text-center">0</td>
                     <td class="text-center" title="Not Done">Today</td>
                 </tr>
                ';
                }
                ?>
``` does anyone know why this isnt showing anything

[prisma girder]

This is PHP code however this is Python server

[torn sphinx]

@prisma girderyes its php mysql

im trying to get users from database onto a html page but it isnt displaying users

[obsidian mortar]

hello

may i ask something about website?

why this is happen?

[burnt turret]

You missed a comma

After the query

They're separate args to execute

Heh it's easy to miss. Yw 👌🏻

[static stream]

how do i add date to SQLITE3 with python, then check for time, and do something after specified time is past?
example:
right now is 6am
i want to check how much minutes is till 7am
and after i see its 7am im able to do something

[pure sleet]

use the datetime library

sql supports datetime objects

[static stream]

how do i work with them ;-;

[pure sleet]

look it up on google

[static stream]

already did that and i would not say that i found alot on it

like i can print current time

but how do i add to current time some minutes?

[pure sleet]

what?

[pure sleet]

??

[static stream]

today = datetime.datetime.utcnow()

with this you get current time right

all values year month....

how do i save exact the same value, but with added values like 1hour more or 10minutes more

[pure sleet]

why would you want to do that?

[static stream]

For game that requires time to wait

like planting

nothing grows up immediately

[pure sleet]

hmm

[pure sleet]

https://www.geeksforgeeks.org/python-datetime-timedelta-function/#:~:text=timedelta() function,-Last Updated %3A 27&text=Python timedelta() function is,ways to perform date manipulations. maybe this what youre looking for

[static stream]

Thanks

And how about getting only hours/minutes/seconds?

[pure sleet]

https://docs.python.org/3/library/datetime.html#timedelta-objects

i don't know if you want the date to be in seconds, but if so, i'm sure you can find the answer on google. me personally i don't know

[static stream]

kk

[static stream]

timestamps but ordered as Hours/Minutes/Seconds like its 34mins till next whole hour

[pure sleet]

again, google cause i dont know

[static stream]

yeah

[slate terrace]

for some reason I can access mariadb via phpmyadmin but not with php or python.

Any ideas as to why that is?

[novel pollen]

Hey everyone! New to Python but learned the basics. My manager asked me 2 questions and I wanted to know if Python/Pandas can do this.

1. I have a data set from MySQL that is about 848 million rows. Can Python/Panda/Jupyter Notebook pull that much data?

2. Can python/pandas do a link or association analysis?

[pallid shard]

How to update something to MongoClient

class Config(commands.Cog):
    def __init__(self, client):
        self.client = client


    @commands.command(aliases=["log-channel","logchannel","channel_log"],usage=f"logchannel [Channel]")
    async def log_channel(self,ctx,channel : discord.TextChannel):
        if collection.count_documents({"guild_id" : ctx.guild.id}) == 0:
            collection.insert_one({"guild_id" : ctx.guild.id ,"channel.id" : channel.id})

        embed = discord.Embed(title=f"",description=f"![mark](https://cdn.discordapp.com/emojis/867020907003641856.webp?size=128 "mark") Log channel has been setup \n\n**log channel:** {channel.mention}",color=0x70cc00)
        embed.set_author(name=f"{ctx.author}",icon_url=ctx.author.avatar_url)
        embed.set_footer(text="Channel for moderation log")
        await ctx.send(embed=embed)
            
        collection.insert_one({"guild_id" : ctx.guild.id ,"channel.id" : channel.id})
        z = collection.find_one({"channel.id"})
        print(z)
            
def setup(client):
        client.add_cog(Config(client))```

because its creating new db

[torn sphinx]

Anyone use jsonlogic ?

[pallid shard]

i can hel you with .json

[torn sphinx]

Hmm it’s with this https://jsonlogic.com

[pallid shard]

its not .py

[torn sphinx]

And?

[torn sphinx]

can someone help #help-ramen

[prime falcon]

how would i add SQLite to:

    @commands.command()
    @commands.check_any(commands.is_owner())
    async def reaction_role(self, ctx, question:str, emoji1:str, role1:str, emoji2:str, role2:str):
        msg = await ctx.send(question)
        await msg.add_reaction(emoji1)
        await msg.add_reaction(emoji2)

i need it to log the strings, and the guild

[faint blade]

Do you already know how to use SQLite?

[prime falcon]

nope

[prime falcon]

no

[faint blade]

You should get one of these two libraries

• https://github.com/omnilib/aiosqlite
• https://github.com/Rapptz/asqlite

It allows you to query SQLite in your Discord bot, if you use normal SQLite modules they will block your bot.

[prime falcon]

okhow do i get the aiosqlite?

[faint blade]

Scroll down and read the document, it has installation steps and examples

[thorn geode]

Do you know the SQL language?

[prime falcon]

no

i just neeed a DB for my reaction roles module

[thorn geode]

You will struggle unless you know the basics of the language

you can learn here: https://sqlbolt.com

[prime falcon]

is pluralsight a good place to learn?

[thorn geode]

£245, you must be using it for a lot of things

If there's an SQL course there then I'm sure it'll teach you the basics at the very minimum

(assuming you've already got some sort of subscription with them, if you don't then don't bother getting one)

[prime falcon]

well you see i have a dad who works with sql

i can just ask him for it

[thorn geode]

Sure

You can even ask him to teach you lol

[copper wyvern]

any sqlite foreign key tutorials

[pure sleet]

what do you want to do specifically?

[copper wyvern]

so a column in my table is gonna be like

"Red" "Yellow" or "Green"

and its gonna be spammed a lot

so duplicate values and such

I hear the better way to do it is to assign an int for the string with a foreign key or something

although i cant find any resources on it

[pure sleet]

what do you mean by spammed a lot?

do you have a relationship between two tables?

also what are you using to interact with the sqlite db?

https://www.sqlite.org/foreignkeys.html you can read here on how to assign a foreignkey when creating tables

[copper wyvern]

there's gonna be a lot of rows with duplicate values in that column

using python

[pure sleet]

need to be more specific than that

[pure sleet]

i still don't get what you are trying to actually do

[proven arrow]

They are wanting to normalise their table and create a relationship.

[pure sleet]

it sounds like there's a relationship but he's not explaining what that relationship is

he just said there's a column for red yellow and green and it's gonna be spammed a lot. that's not really descriptive

[proven arrow]

Colours with duplicate values, so would be a 1-M

Yeah ahah, maybe they miss the correct terminology

[pure sleet]

ok, so i guess the solution should be in the article i sent

[copper wyvern]

say a table called toys had structure

CREATE TABLE toys 
(
weight INT,
height INT,
color TEXT,
)```

and there were 1000000 toys  (rows)  inserted into that db. there are a lot of "RED" color toys and "BLUE"/"YELLOW/"GREEN"/etc. 
was wondering if foreign keys can help improve performance on queries where it would look like
```sql
SELECT * WHERE color == "RED"```

and since searching by INT is quicker than searching the string "RED" i could have a `0` in place of "RED" and a `1` in place of "BLUE" and so on
was wondering if foreign keys were the way to do this / is there a better way
my DBMS is sqlite and im using aiosqlite in python to interact with the DB

[pure sleet]

wait, what's the primary key for toys here?

[grim vault]

You don't need one. The linked table will need one.

[pure sleet]

two toys can have the same weight, color and height, how will you identify which is which?

[proven arrow]

Indexes improve performance. When you make the relationship, the foreign key would be indexed, and would be an integer so which is why it would be faster. Columns can also be indexed without a foreign key FYI. Also by making the extra table for the colours it would help you achieve the basic database normalisation rules.

[grim vault]

SQLite does not index an foreign key, you'll need to do that yourself, like:

CREATE TABLE colors
(
  color_id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE toys
(
  toy_id INTEGER PRIMARY KEY,
  weight INTEGER,
  height INTEGER,
  color_id INTEGER REFERENCES colors
);
CREATE INDEX fk_toys_colors ON toys(color_id);

[copper wyvern]

so this effectively makes operations on toys like sql SELECT * WHERE color_id == "RED" faster? or is that query not valid, and the query would be more literal like sql SELECT * WHERE color_id == 1

[proven arrow]

https://www.guru99.com/database-normalization.html

For now you can only worry about 1nf, 2nf, 3nf

[torn sphinx]

anyone know how i can convert this tuple to just 15
(15,)

@bot.command()
async def idcount(ctx):
    sql = f"SELECT COUNT(*) FROM vrp_user_identities"
    mycursor.execute(sql)
    myresult= mycursor.fetchall()

    print(myresult[0])

so far thats my code

this is database, im using mysql and it returns tupples correct?

what i want is [(15,)] to just be 15 on its own

oh

why 2 [0]'s?

i done 1 and it removes outer brackets

tuples why do they even exist lol

why cant everything be easy

@bot.command()
async def idinfo(ctx, permid):
    sql = "SELECT * FROM vrp_user_identities WHERE user_id = '%s'"
    mycursor.execute(sql, (int(permid)))
    myresult = mycursor.fetchall()
    print (myresult)

anyone know why this returns [] and not

but it is correct?

its converted to an int

]

see

[heavy epoch]

Ok

Bye

[torn sphinx]

what?

[heavy epoch]

I'ma sleep

[torn sphinx]

night