#databases

1 messages · Page 139 of 1

mental quiver
#

Why execute commands on a cursor instead of executing commands through the connection object? @ me when responding please.

proven arrow
#

You can use pandas

proven arrow
mental quiver
proven arrow
#

Cursors are a way of iterating through the result set

#

Allowing you to do things like fetchone, fetchall or fetchmany. You can consume (fetch) data from it, but you can't go back to previously consumed data. However, some libraries can have scrollable cursors which allows you to do this.

#

Basically allows you to yield the data in parts or all at once.

mental quiver
#

What is the purpose of a primary key? (sqlite)

primal plover
signal totem
pure mortar
primal plover
pure mortar
primal plover
abstract pivot
#

how do i setup mysql to run on startup?

abstract pivot
#

windows 10

#

On windows search for services, there should be an option to start on startup
havent been able to find that

#

also once i download mysql, do i run mysqld --install?

#

@torn sphinx ^

pale sandal
#

Does anyone have experience with the fuzzy module or difflib?

novel oak
#

Yes SQLite

#

Because in the docs they used like that, i want to concatenate several texts

torn sphinx
# 
                elif db_type.lower() == "mysql":
                    host=database["HOST"]
                    user=database["USER"]
                    password=database["PASS"]
                    db=database["DB"]

                    from sshtunnel import SSHTunnelForwarder
                    import aiomysql#import mysql and SSH tunnel libraries

                    tunnel = SSHTunnelForwarder(
                        host,
                        ssh_username=user,
                        ssh_password=password,
                        remote_bind_address=("127.0.0.1", 3306)
                    )
                    try:
                        tunnel.start()
                        await aiomysql.connect(host='127.0.0.1', user=user,password=password, db=db,port=tunnel.local_bind_port)
                    except Exception as error:
                        logging.critical("Invalid server authorization provided")
                        raise error
                    finally:
                        self.db = client["DiscordDB"]
                        logging.info("Connected to remote database")

               ```
#

any idea why this wouldn't work. It connects to the server but cannot load the database

#

Error loading mysql database: (2003, "Can't connect to MySQL server on '127.0.0.1'")

#

nvm screw tunnel

torn sphinx
#

and

#

dont

#

type

#

like

#

this

plain radish
#

!tempmute 800015298245296168 1D You were already asked to not spam your messages like this.

delicate fieldBOT
#

:incoming_envelope: :ok_hand: applied mute to @rapid lantern until 2021-03-10 09:45 (23 hours and 59 minutes).

torn sphinx
#

thats a little harsh

torn sphinx
#

not all arguments converted during string formatting

#

how can I figure out which?

#

its a long list

# 
await cursor.execute("INSERT OR IGNORE INTO messages VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                [
                    message.id,
                    message.channel.id,
                    message.channel.name,
                    message.author.id,
                    message.author.name,
                    message.author.nick,
                    message.guild.id,
                    message.guild.name,
                    message.content,
                    message.clean_content,
                    message.created_at,
                    message.created_at.timestamp()
                ]
            )
#

created with ```python
await cursor.execute("CREATE TABLE IF NOT EXISTS messages ("
"message_id INTEGER PRIMARY KEY,"
"channel_id INTEGER,"
"channel_name TEXT,"
"author_id INTEGER,"
"author_name TEXT,"
"nick TEXT,"
"guild_id INTEGER,"
"guild_name TEXT,"
"content TEXT,"
"clean_content TEXT,"
"created_at TEXT,"
"created_at_unix INTEGER"
")")

#

@prisma girder sorry for ping but any idea?

#

ping out of desparation lol. its 1AM and i'm off in 30 min

prisma girder
#

@torn sphinx Oh, I am not using raw cursors - I prefer ORM... Which library are you using?

torn sphinx
#

yes

#

ah... I am used to sqlite

#

is their any equivalent way?

#

am trying to avoid injection attacks

#

which ? avoids

prisma girder
#

Try to use %s for strings and %d for integers, not sure that this library uses ?

jovial notch
#

can't I do this somehow for everyone?

prisma girder
#

First of all please use backticks ```

#

It is easier to read

ashen geode
# 
try:
    quary = (f"CREATE TABLE IF NOT EXISTS `uuids` (`uuidu` varchar(250) NOT NULL,`ip`"
             f"varchar(250)"
             f"NOT NULL);")

    cnx = mysql.connector.connect(user='root', host='127.0.0.1', database='uuid')
    curA = cnx.cursor(buffered=True)
    curB = cnx.cursor(buffered=True)
    curA.execute(quary)
    curB.execute("INSERT INTO uuids VALUES (?, ?)", uuid.uuid4(), ip_address)
    cnx.commit()
    cnx.close()
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
else:
    cnx.close()

This my code
My Error:
it create table but doesn't insert anything

proven arrow
ashen geode
cold ocean
#

how would i go about updating the id column so that it doesn't skip 2?

unknown.png
proven arrow
cold ocean
#

yea, id integer primary key is how i defined it

proven arrow
#

Then it’s not skipping it. There was once a row there which was then removed. You can manually enter a new record with the ID of 2.

#

It will auto increment by a value each time. The count is kept internally.

cold ocean
#

yea, i guess i kinda asked my question wrong but i want to change the id's of the messages so that it goes from 1-4

#

wihtout skipping numbers

proven arrow
#

I just told you it’s not skipping them.

#

It increments each time. You must have deleted a row with value 2. The 1-4 you see on the left is not an actual value stored in the database. It’s just what your database browser shows you to help you count.

cold ocean
#

yea iget all that, im asking, after i delete a message form the database how do i change the id's of the other messages so the numbers are consistent? i need them to go up in increments of only 1...

proven arrow
#

The only way to do that is to update and calculate the position of the rows yourself. And generally this sort of thing is not recommended, especially when you have foreign keys involved. Also what is the need of such a thing? Seems like pointless and expensive operation to do.

steep lintel
#

please help with this code i ran
import statsmodels.formula.api as sma
X_train = sma.add_constant(x_train) ## let's add an intercept (beta_0) to our model
X_train = sma.add_constant(x_test)
it came out with this error
File "C:/Users/Hommaston17/PycharmProjects/lineer regression/regression.py", line 31, in <module>
X_train = sma.add_constant(x_train) ## let's add an intercept (beta_0) to our model
AttributeError: module 'statsmodels.formula.api' has no attribute 'add_constant'
please help to solve it

cold ocean
#

i dont really know how to solve this

proven arrow
#

How can you delete a message twice?

#

Once it’s deleted isn’t it removed from the chat?

cold ocean
#

yea, but anothe message will take its place

#

if its the last message no problem but if i delete a message in the middle the others will "close the gap"

proven arrow
#

So basically you have a list of messages where the index is the id of the message?

cold ocean
#

yea kinda

proven arrow
#

So all messages are strings or a Message object?

cold ocean
#

strings

proven arrow
#

Then you would need a unique id for each message. That would be the best way to do it. Each message has a unique id which isn’t used again. Generally you would have a message class and the message would be represented as a message object which contains details like it’s unique id, when it was sent etc.

cold ocean
#

is there maybe another way of doing this? because this one would require me to change the entire way my chat works...

trim fern
#

is there a big difference between sqlite3 syntax and postgresql?

proven arrow
shell ocean
#

each delete literally requires you to update your whole table

proven arrow
shell ocean
#

AND any tables which relate to it

proven arrow
trim fern
proven arrow
#

I did that in sqlite but other databases also support it as well for whichever db you use.

trim fern
#

and insert it to postgresql db

proven arrow
#

You’ll need to make some modifications because Postgres syntax for creating tables is slightly different

#

But you can use the sqlite tool to dump it

trim fern
cold ocean
trim fern
#

sry i havent come across dump files

proven arrow
runic coyote
#

not particularly database related but in mysql workbench any one know how to change the background of the selected in the schema? It shows all black on black text? I'm on pop_os

Screenshot_from_2021-03-09_21-03-14.png
solemn ridge
#

Hi, this is my file structure for my models, each model interacts with each other. I can't get it to work, I keep getting circular imports. Anyone know how I could fix that?
Example error:

unknown.png
# 
Traceback (most recent call last):
  File "C:/Users/sn3ak/Desktop/Hyplex/backend/v1/posts/app/main.py", line 9, in <module>
    from app.models import comment, commentCollection, content, nestedComment, nestedCommentCollection, post, reaction, ReactionCollection
  File "C:\Users\sn3ak\Desktop\Hyplex\backend\v1\posts\app\models\comment.py", line 6, in <module>
    from .post import Post
  File "C:\Users\sn3ak\Desktop\Hyplex\backend\v1\posts\app\models\post.py", line 8, in <module>
    from .commentCollection import CommentCollection
  File "C:\Users\sn3ak\Desktop\Hyplex\backend\v1\posts\app\models\commentCollection.py", line 7, in <module>
    from .comment import Comment
ImportError: cannot import name 'Comment' from 'app.models.comment' (C:\Users\sn3ak\Desktop\Hyplex\backend\v1\posts\app\models\comment.py)
prisma girder
#

Can't you create all of them in one file?

solemn ridge
#

no

#

because each model is 200 lines

#

And they are 8 models that will probably become around 10-12

#

and be around 300-400 lines each

#

Wouldn't be so wise having a 4000 line file of models

prisma girder
#

Are you using those models to add type checking or inside functions/methods?

solemn ridge
#

Let me show you

#

For example this is a property of Post

    @property
    def comments(self):
        return CommentCollection.get(self._comments)

    @comments.setter
    def comments(self, commentCollectionObj: CommentCollection):
        self._comments = commentCollectionObj.id
        self.comments = commentCollectionObj
prisma girder
#

Like:

from typing import TYPE_CHECKING
if TYPE_CHECKING:
  from .comment import CommentCollection
solemn ridge
#

Alright nvm I fixed it

#

I optimised the imports in a pyramid scheme

#

So post can import all

#

But others cant import post, comment, etc

prisma girder
#

👍

solemn ridge
#

Circular imports are driving me crazy though

prisma girder
#

Me too

#

I am using TYPE_CHECKING and local imports inside functions/methods

#

Code is ugly but it works

solemn ridge
#

idk

#

I prefer having things like:

@comments.setter
def comments(self, commentList: List[Comment]):
  self.comments = comments
prisma girder
solemn ridge
#

Possibly

#

And I am also not using Flask so its rly weird trying to search ur problem and everyone is using Flask-SQLAlchemy

prisma girder
#

I am using Flask but I prefer raw SQLAlchemy pithink

#

Nvm

solemn ridge
#

I am using FastAPI

torn sphinx
#

Python

#

oh wait

torn sphinx
#

with mongodb, can i have my discord bot access multiple databases, or am i limited to one?

proven arrow
#

Is non-member a guest user who can make a booking?

#

So why do you need a model for a guest user?

#

Generally I wouldn’t store a guest user because the whole reason of guest user is they don’t want an account.

#

The way it’s done normally is the bookings table will hold the users details such as email, name, or any user detail that is mandatory for the booking. Although this data may seem duplicated in the bookings table it’s necessary because the users email/name might change or get deleted in the future. This way you can ensure the data is valid for the future and the same as when they made the booking.

#

Using this approach you can still have a member_id in the bookings table but just keep it null if it’s a guest. Or you can have 2 fk fields like you said where one is for member and non member.

#

There is no correct way generally to do this kind of stuff. Different people may suggest different ways. You have to weigh the advantages/disadvantages of the different methods, and how they relate to your application in terms of how you will access the data etc.

torpid whale
# 

mongo_client = motor.motor_asyncio.AsyncIOMotorClient(os.getenv('mongo_db'))
db = mongo_client["discord_data"]["prefixes"]

async def get_prefix(client, message):
  grab_prefix = await db.find_one({"_id": message.guild.id})

  if not message.guild:#if in dms, defaults to .
        return commands.when_mentioned_or(".")(client, message)

  return commands.when_mentioned_or(grab_prefix["prefix"])(client, message)```

Can anyone explain why I'm getting a `bson.errors.InvalidDocument: cannot encode object: {'242839816280997899'}, of type: <class 'set'>` error? I've tried reading the documentation and stackoverflow but I truly have no clue. Trying to identify a bot prefix of the server the message is being sent in.
buoyant creek
#

Hello! I can use sqlite3 in repl.it?

torn sphinx
#

could anyone help me insert values into a mysql table

# 
async with self.db.cursor() as cursor:
            await cursor.execute("INSERT OR IGNORE INTO messages VALUES (%d, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                [
                    int(message.id),
                    message.channel.id,
                    message.channel.name,
                    message.author.id,
                    message.author.name,
                    message.author.nick,
                    message.guild.id,
                    message.guild.name,
                    message.content,
                    message.clean_content,
                    message.created_at,
                    message.created_at.timestamp()
                ]
            )
#

Ignore that only one is %d. For some reason that doesn't work for me

#

TypeError: %d format: a number is required, not str

#

but anyway thats not why i'm here. I can probably figure that out. I'm wondering if theirs any way to do an insert using something equivalent to sqlite3's (?,?,?) thing. Where its precompiled and thus invulnurable to injection.

brave bridge
#

this^ isn't vulnerable to injection either, it doesn't just do query % params

torn sphinx
#

Oh ok

#

Well I guess that is an issue.

async with self.db.cursor() as cursor:
            await cursor.execute("INSERT OR IGNORE INTO messages VALUES ('%d', '%d', '%s', '%d', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%d')",
                (
                    message.id,
                    message.channel.id,
                    message.channel.name,
                    message.author.id,
                    message.author.name,
                    message.author.nick,
                    message.guild.id,
                    message.guild.name,
                    message.content,
                    message.clean_content,
                    message.created_at,
                    message.created_at.timestamp()
                )
            )
#

This gives error await cursor.execute("INSERT OR IGNORE INTO messages VALUES ('%d', '%d', '%s', '%d', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%d')", File "/home/alexander/.local/lib/python3.8/site-packages/aiomysql/cursors.py", line 237, in execute query = query % self._escape_args(args, conn) TypeError: %d format: a number is required, not str

#

If I switch the comma to a % to make it an fstring it kinda works

#

but I get a different syntax error

#

and anyway thats just an old style fstring...

#

so definitly vulnurable

#

yes

#

its a unix timestamp

#

lemme drop it in an int() just to make sure

#

same error

#

I manually checked all the IDs their ints

# 
async with conn.cursor() as cursor:
                    await cursor.execute("CREATE TABLE IF NOT EXISTS messages ("\
                        "message_id INTEGER PRIMARY KEY,"\
                        "channel_id INTEGER,"\
                        "channel_name TEXT,"\
                        "author_id INTEGER,"\
                        "author_name TEXT,"\
                        "nick TEXT,"\
                        "guild_id INTEGER,"\
                        "guild_name TEXT,"\
                        "content TEXT,"\
                        "clean_content TEXT,"\
                        "created_at TEXT,"\
                        "created_at_unix INTEGER"\
                        ")")

tabled created with this

#

Tried that lol

#

I don't remember what it did...

#

ah yea

#

that worked but got me to the second error

# 
pymysql.err.ProgrammingError: (1064, "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 'OR IGNORE INTO messages VALUES ('819016848099639338', '291284109232308226', ''ot' at line 1")
#

yea

#

same error

#

the '%s'

#

?

#

um... which?

#

I think I had none of thim lilke that befre

#

so whats a varchar?

#

ah

#

ok will change it

#

OMFG

#

OK. So taking it just off of the digits did nothing

#

but removing quotes from all gets me... drum roll please... another error

# 
    raise InternalError(
pymysql.err.InternalError: Packet sequence number wrong - got 114 expected 1
#

same line

#

not sure that this is better. It is a remote database.

#

yea

#

yea. Well thx. I guess from here I need to debug whatever this is

#

thanks a bunch for the help

#

This should be fine once I move to the production server

#

Since it will be local

#

oh.

#

yea thats it this is a multithreaded program

#

GAHHHHHHHHHHHHHHHHHHHHHHHH

#

ok I can make one thread dedicated to SQL shit

#

so I did that and now have the old error 1064 again

#

I think that wasn't fixed by removing the quotes. I just had a temporary network issue that covered it up

#

for a bit

pine hedge
#

Can someone help me fix this requirement? I have python 3.8 installed.

997f1408445b0b54875abb15d3d697ca.png
#

3.8.5

#

This is for a mysql installation

torn sphinx
# 
async def message_logger(self):
        while True:
            if len(self.queue) > 0:
                msgs = []
                for _ in range(len(self.queue)):
                    msgs.append(self.queue.pop(0))

                query = "INSERT INTO messages (`message_id`,`channel_id`,`channel_name`,"\
                                                "`author_id`,`author_name`,`nick`,`guild_id`,"\
                                                "`guild_name`,`content`,`clean_content`,"\
                                                "`created_at`,`created_at_unix`) VALUES "\
                                                + ",".join("(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" for _ in msgs)
                flattened_values = [item for sublist in msgs for item in sublist]
                async with self.conn.cursor() as cursor:
                    await cursor.execute(query, flattened_values)
                await self.conn.commit()
            
            await asyncio.sleep(15)

behold my awful solution

#

but it should be able to handle a million messages an hr (number from my ass)

burnt turret
#

what's this for?

#

technically, just storing the message_id would let you retrieve all of that data later at a time when you want it

torn sphinx
#

not if user deletes it

burnt turret
#

fair enough

#

what are the laws on storing all message data like this?

#

do you have a disclaimer and a privacy policy or something which users agree to when they use this bot? (i assume it is a bot)

tulip delta
#

Hey guys

#

Just a quick newbie question

#

is it practical to go with something like a SQL for something like this

#

Please do keep in mind that i plan on having more than just 1 TrainRoute. So my question is : does using SQL make any sense, or should i just use old pain json?

bitter ingot
#

Hi, currently i'm using Python's sqlite3 library as a database, what would people here recommend as a next step for a database server?

I'm looking to add a web panel to my app, and a database server would be the best option (i believe). It also allows for me running multiple instances of the server, which is a bonus.

What should I use, which I can also use with Python and others?

(i have a spare VPS ready)

next violet
#

Hey! I got a quick question. I want to make a select statement that get every info of my columns uder_id | punishment | reason
For example: Franck#1234 | mute | spam
Select statement: cursor.execute(f'SELECT * FROM punishments WHERE user_id=?', (str(member)))

What's wrong?

jolly rivet
#

Hi

#

Anyone online

torn sphinx
next violet
#

I got it already but thanks

torn sphinx
#

Ok

jolly rivet
#

hi

#

any one there

proven arrow
tulip delta
#

how exactly do i store it?

#

would each route be a different table?

proven arrow
#

Yeah so you need to plan it with some thinking. It’s not going to be a single table.

proven arrow
tulip delta
#

Can you draw me a picture with paint?

#

would be the easiest to understnad

#

(if you can)

#

ok nvm, forget the paint if you can't be bothered

#

but if it were you, how would you do it?

proven arrow
#

It depends on the exact use case. I would first analyse and understand exactly what’s needed in the requirements. Then think of the possible tables needed, and what can be normalised etc.

#

Depends how complex you want it

tulip delta
#

i believe i've sent an image

#

could you take a look at it

#

it's this exact thing

#

but i need many of it

proven arrow
#

But for what? A production system? Exam?

tulip delta
#

It's a Ticket station project

#

Im not sure what you mean by production or exam

#

do you mean as like an outsource matterial? (something i code and then sell off?)

proven arrow
#

Yeah kind of that yeah

tulip delta
#

No well

#

It's sort of like an exam my homeroom teacher gave out

#

at first it was a CLI with python

#

but im taking it to the next level

#

like

#

i can see how this would fit in json

#

and it would be pretty easy to work with

#

but i kind of want to mess around with databases

proven arrow
#

Well looking at the image you would need a table of: Stations, Trains, Routes, Route Stations.

#

That would be a simple approach.

#

The Route stations table will just be mostly of foreign keys that store all the stations a train can be at for a Route

barren marsh
#

is there a way to relocate my sqllite 3 databases to another directory other than the directory in which my main.py file is?

proven arrow
#

Just move the file

#

And then update the path in the code

barren marsh
#

its not allowing me that

tulip delta
#

Have you closed the connection?

barren marsh
#

oh shit yes

#

i forgot bout that

#

thanks :D

tulip delta
proven arrow
#

The same as it’s parent column

tulip delta
#

But how exactly do i work with lists inside of SQL

#

how am i supposed to even create a route and store it

proven arrow
#

Why do you need a list?

tulip delta
#

well let's suppose we are looking at a route

#

i'd imagine it like this

#

['first station', 'second station'...]

#

chained like this

proven arrow
#

Lists are are usually a smell of bad design

#

Although that is one way of doing it and may be simple, it’s not the best or recommend way. To store that kind of data it’s what I said previously about making the route stations table.

tulip delta
#

i am currently working on it as we speak

proven arrow
#

The relationship you have here is a Route can have many stations, and a station can have many routes.

tulip delta
#

im trying to figure out how exactly to construct the routes

tulip delta
#

but they are predefined by me

proven arrow
#

I would draw you and example schema if I had a pc, but I don’t have one .

tulip delta
#

Sadge

#

but wait.........

#

can';t i just insert json into the DB>?

#

that would solve so many problems

proven arrow
#

That’s for you to decide. I’ve told you how to do it using a database. If you want to defeat the purpose of a relational db and just store it as json then that’s something you must decide.

tulip delta
#

I mena i'd love to do it

#

but i just can't figure out how to construct the damn list of routes

proven arrow
#

It’s been half hour. Sometimes you need to give it a little more time, or do a little more research 😐

tulip delta
#

but i am...

brave bridge
#

@proven arrow I've been reading the book by Date you suggested, and it seems like it's mostly about describing how SQL gets everything wrong

#

I learned quite a bit, though

proven arrow
#

Do you mean how the relational algebra or set theory applies to Sql?

#

Or something else

brave bridge
torn sphinx
#

sounds like nerd stuff

#

icl

harsh osprey
#

sqlite3 uses transactions by default right? or do I have to write those myself?

proven arrow
harsh osprey
#

Ah great, thank you

proven arrow
#

Although you will have to commit from python

torn sphinx
#

I am using mongodb and i wanna fetch all data related to a user and display it on a discord command

torn sphinx
#

ive been learning this in class for 6 lessons now and its been very boring lol

torpid whale
# 

mongo_client = motor.motor_asyncio.AsyncIOMotorClient(os.getenv('mongo_db'))
db = mongo_client["discord_data"]
collection = db["prefixes"]

async def get_prefix(client, message):
  grab_prefix = await collection.find_one({"_id": str(message.guild.id)})
  print(grab_prefix)

  if not message.guild:#if in dms, defaults to .
        return commands.when_mentioned_or(".")(client, message)

  return commands.when_mentioned_or(grab_prefix["prefix"])(client, message)```

results in error:
`If you meant to call the 'find_one' method on a MotorCollection object it is failing because no such method exists`

Any idea what I'm doing wrong to cause the above error cus I seem to be following https://motor.readthedocs.io/en/stable/tutorial-asyncio.html correctly
crude cedar
# 
    async def boot_delete(self, ctx, link=None):
        if link is None:return await ctx.send(f"{ctx.author.name}, o link não pode estar vazio.")
        local = self.client.db.boots.find_one({"_id":link})
        if link in local:
            await ctx.send(f"Olá {ctx.author.mention} o link está registrado")  
        else:
            await ctx.send(f"Olá {ctx.author.mention} esse link não esta registrado")    async def boot_delete(self, ctx, link=None):
        if link is None:return await ctx.send(f"{ctx.author.name}, o link não pode estar vazio.")
        local = self.client.db.boots.find_one({"_id":link})
        if link in local:
            await ctx.send(f"Olá {ctx.author.mention} o link está registrado")  
        else:
            await ctx.send(f"Olá {ctx.author.mention} esse link não esta registrado")```

error: discord.ext.commands.errors.CommandInvokeError: Command raised an exception: RuntimeError: await wasn't used with future.
prisma girder
crude cedar
prisma girder
#

How about boot_delete? Do you put await before call it?

mystic blade
#

hello colleagues. I need your help with one important questions. Im working on the micro services project on python. For database we use postgres/sqlalchemy (we cannot switch to other database btw). Today we got request from management to switch from current database model where we use columns to store each data cell individually to just use database to store json in it. I mean management requested us to recreate our database that each row will have only two fields: "id" and "data" field as jsonb. I know it sounds weird but could you help us to find arguments why this approach isn't great? We used arguments that search will be much slower, size of the database will increase significantly. What other reasons not to switch to use database only to store json?

prisma girder
# mystic blade hello colleagues. I need your help with one important questions. Im working on t...

Hey. Speaking honestly is really terrible idea - maybe use MongoDB instead? 😂 RIght, right, it must be Postgres...
For me your arguments (about increasing size and decreasing speed) are fine and fully exhaust the issue.
However you can do what they want and show real-time statistics that it was terrible idea (like higher resource usage, need to buy more servers etc.).
It's offtopic question but do people from your management are technical people? If yes I don't have other questions... If not - why they tell you how to create software?
Regards

mystic blade
prisma girder
#

If you want to store JSONs then you can store them in filesystem

#

You don't need any database

#

You need to create some kind of index file and here you are - you create your small DB system but why? pithink

#

Storing JSONs in this way (in DB instead of columns) is just pointless for me

#

It gives no advantage... Maybe you should store Python pickle objects, it makes more sense 😂

#

@mystic blade Hmmm... I think that is the way - you should suggest pickle column type (it's available in SQLAlchemy) because it's more native solution for Python than JSON. Watch then how your management is going to argument that pickle is not okay and use same arguments to argument that storing JSON is bad too pithink

mystic blade
mystic blade
prisma girder
prisma girder
west siren
#

whats the best way to store attributes about multiple things? (like attributes about multiple users). I have already looked into it, but I am feeling overwhelmed by all the options. I should also note that its going to be relatively small scale (just a personal project) and data persistance is not required. Being able to use it on the google cloud app engine would also be nice, but not required.

pure cypress
#

How often are you going to update the data for a user?

#

Do you need to have relationships?

west siren
#

prettly regularly, most likely around 25-50% of the interactions with the bot, although because it will most likely be on realitively small servers (just a few my friends and I use) there isnt going to be alot of trafic

#

if i had to guess a user update every 20 seconds with mild use, and every 2-5 seconds if there are multiple people interecting with the bot at once

pure cypress
#

Since you don't need data persistence you can store the data in dictionaries or dataclasses.

west siren
#

thats what i was planning on, but im not sure of the actual execution of it

#

I was also looking into sql, but that seems like overkill

pure cypress
#

You need a dictionary and then store data structures within the dictionary

west siren
#

so like a single dictionary with all the users, or a dictionary for each user?

pure cypress
#

Like ```py
db = {}
db["userid"] = {"name": "mark", ...}

west siren
#

ok so with that i can specify a user and a atribute?

#

that seems like exactly what im looking for

pure cypress
#

Not just one attribute, but all attributes for the user.

west siren
#

well if i need to say edit only a single attribute can i specify only that one?

pure cypress
#

yes db["userid"]["name"] = "parkman"

#

"userid" is just a placeholder. In practice, it'd have to be a unique value for the user

west siren
#

wow i think this is the exact thing that im looking for

#

now just out of curiosity could i do say db["server"]["server member"]["nickname"]?

pure cypress
#

Yes, the keys can be whatever you want as long as they are 1. unique 2. hashable

west siren
#

wow thanks! and sense this is a dictionary it can be writen to disk using json if needed?

pure cypress
#

Yes, but when you change your requirement to persistence then it actually becomes a pretty bad option.

west siren
#

well i mean i wont need it most likely

#

and even if i did i would only need to write to disk pretty rarely

#

thanks for your help, this is exacly what im looking for

pure cypress
#

If you have infrequent writes then you risk data loss

west siren
#

well i mean update that would need to be written to disk would be pretty rare, and the data I'm working with is incredibly un-valuable, data loss at worst would be a mild inconvenience.

west siren
#

one last thin, what would be the most efficient way to check if a user already exists in the database?

#

should i do something like

if user in db != "true":
  db[user]= {}
#

right my mistake

#

nice nickname btw

#

i feel that

#

wait so whats the diference between in [87] and in [88]?

#

wait nevermind i got it

#

wait is there a diference?

#

ah ok

#

thanks!

rain plank
#

Well it returns a dict_keys object

gritty cloak
#

how do i read a specific column using the csv module?

cloud reef
#

For a csv, I would import the csv with pandas. Not sure if you can limit to one col on import, but you certainly can isolate just the one column after import.

solemn ridge
#

Hi, how could I do the following with sqlalchemy:

I have a database of users, a user can follow another user but it isn't necessary they get followed back. A user can create a post, the post is timestamped with an integer timestamp. How can I get the latest X posts of a user's follow list?

torn sphinx
#

Sqlite so the database is free right?

opal flint
#

@torn sphinx sqlite is usually bundled with python, so yes free as long as you have somewhere to put it.

torn sphinx
#

Okay thanks

#

For the info

vocal marsh
#

Is it possible for a trigger in a postgress db to select data from another database (running in different docker container)?

sick perch
vocal marsh
sick perch
bitter ingot
#

Hi, is there a tutorial for setting up a MySQL server i can look at?

#

I have it installed, just not sure what to do from here

vocal marsh
# sick perch start here: https://wiki.postgresql.org/wiki/Foreign_data_wrappers - but also wh...

I'll be having a classic Django app, with postgress db, both Django and postgress running in separate containers.
Then I will have a second db, with high availability message queue to collect util. data from "clients".
The two databases are separate, because the "data database" will be long running and must be up most of the time. The "application db" for django is not that crucial, but contains some usefull information.
My idea is: I will have a "logs" table in the "data database", where I will save the util info. On each log (row), I want to have a trigger to look into the application database and add a boolean variable into "working hours" column, if the log came in working hours or not.
The catch is, that for example dates of holidays (and thus not working hours) will be in the "application db", and may by changed by the users of the app.

#

I won't need all the data from the "app db" to be available in the "data db", only few tables.

#

I want this "working hours" info to be "preprocessed" in the database, so I can do efficient queries. Something like: "select sum(period) from logs where work_hours = False".

#

But now, that I'm thinking.... I would have a problem if somebody changed a holiday in past, right?? Would'n it be better to have some kind of a join?

jade mason
#

With SQLite3 is there a way to replace a text in a print with a word if the value equals to 1?

#

So I mean if the Value in the Databse is 1 then it should output "Number" and if the Value is 0 it should output "Not"

sick perch
#

@vocal marsh there is indeed different ways to solve it, but i have a question, what type of db is that "availability message queue" - it may be possible/sensible to just use one single database depending on the impl

vocal marsh
#

Or, what would be best practice in this two databases scenario. I guess it is not that uncommon.

sick perch
vocal marsh
# sick perch <@!682985466559332409> there is indeed different ways to solve it, but i have a ...

Basically, it is a bunch of computers, sending messages to RabbitMQ (message queue), and then two consumers saving those messages to a "data db".
This is given, and there are other tools (for example Powerbi) using that db.
Now I'm developing an Django app, with it's database, to manage those computers, users, and some other stuff. And I want the "data db" to be basically unaware of the app, just doing it's data collection. And the "application app" being more flexible for the django.

sick perch
#

ok, well, you can layer your database to make data ops possible or data ops easy, personally i'm not familiar with such setups where you cross-integrate multiple databases with foreign wrappers,

personally i'd likely just use the same database as the data belongs to the same pool, and create different users with different permissions for reading/writing/seeing certain tables

vocal marsh
#

Would maybe a different "schema" be a good usecase for that?

jolly trench
#

Is there a way to encrypt sqlite databases?

sick perch
#

@jolly trench there is a sqlite extension to encrypt databases, its not part of python itself and needs to be installed separately

#

im not aware of a good python module for it right now

jolly trench
#

Would loading the db with open() and encrypting the content be secure?

sick perch
#

you would create yourself a lot of trouble if you forego all the special file and content handling sqlite has

prime swift
#

why not encrypt the contents rather than the full database

solid tundra
# 
Ignoring exception in command welcome channel:
Traceback (most recent call last):
  File "C:\Users\irdio\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped 
    ret = await coro(*args, **kwargs)
  File "c:\Users\irdio\Desktop\ChizLanksPY\cogs\Welc_Leave.py", line 40, in channel
    cursor.execute(f"SELECT channel_id FROM welcome WHERE guild_id = {ctx.guild.id}")
sqlite3.OperationalError: no such table: welcome

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

Traceback (most recent call last):
  File "C:\Users\irdio\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\bot.py", line 902, in invoke  
    await ctx.command.invoke(ctx)
  File "C:\Users\irdio\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 1340, in invoke
    await ctx.invoked_subcommand.invoke(ctx)
  File "C:\Users\irdio\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 864, in invoke 
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\irdio\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped 
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: no such table: welcome
#

I'm getting this error after running this:

# 
    @welcome.command()
    @commands.has_permissions(manage_messages=True)
    async def channel(self, ctx, channel:discord.TextChannel):
        db = sqlite3.connect("main.sqlite")
        cursor = db.cursor()
        cursor.execute(f"SELECT channel_id FROM welcome WHERE guild_id = {ctx.guild.id}")
        result = cursor.fetchone()
        if result is None:
            sql = ("INSERT INTO welcome(guild_id, channel_id) VALUES(?,?)")
            val = (ctx.guild.id, channel.id)
            await ctx.channel.send(f"Channel has been set to {channel.mention}")
        elif result is not None:
            sql = ("UPDATE welcome SET channel_id = ? WHERE guild_id = ?")
            val = (ctx.guild.id, channel.id)
            await ctx.channel.send(f"Channel has been updated to {channel.mention}")
        cursor.execute(sql, val)
        db.commit()
        cursor.close()
#

The problem is that I already have a table named welcome

brave bridge
#

(As a side note: ```py

NO:

cursor.execute(f"SELECT channel_id FROM welcome WHERE guild_id = {ctx.guild.id}")

YES:

cursor.execute(f"SELECT channel_id FROM welcome WHERE guild_id = ?"), ctx.guild.id)

solid tundra
#

I had not commited the changes, oof thank you!

brave bridge
#

👍

#

no worries, it's an easy mistake to make

torn sphinx
#

hey anyone available?

vital belfry
#

Does heroku let you create more users?

#

I need to make a read only user on their hobby tier

proven arrow
#

Keep it in the app/code

#

You want to keep the database not tightly coupled with business logic

delicate fieldBOT
#

:incoming_envelope: :ok_hand: applied mute to @tough mulch until 2021-03-11 12:47 (9 minutes and 59 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

raven trail
#

Anyone have experience implementing ODM/ORM like MongoEngine with Discord.Py?

burnt turret
#

(continuing from our discussion in the other channel) so I haven't used MongoEngine myself as I said earlier, but their FAQ seems to say that it only supports pymongo as a driver

#

So it's gonna be blocking

torn sphinx
#

@fervent parcel

#

That's my ERD:

unknown.png
fervent parcel
#

That doesnt make sense to me

torn sphinx
#

Well you store if the command is enabled or not

#

¯_(ツ)_/¯

#

Per guild

burnt turret
#

it means, create a table called CommandStatus with 4 columns

fervent parcel
#

Yes but

burnt turret
#

and then it shows the column name and what type to use

fervent parcel
#

It only stores 1 cmd doesnt it

burnt turret
#

no

torn sphinx
#

No

burnt turret
#

each row is for 1 command

torn sphinx
#

A table is not a row

#

A table stores multiple rows

#

And the ERD displays a table

fervent parcel
#

Okayy

#

Howd I create that in code?

torn sphinx
#

...that's some SQL

fervent parcel
#

Yes ik

torn sphinx
#

Will help you

#

Then add the primary keys etc.

#

Otherwise I can do it for you but you don't learn

fervent parcel
#

Ik how to do that

torn sphinx
#

Then you know how to make it

fervent parcel
#

I think I got another way of doing it
Just storing an array of disabled commands

torn sphinx
#

Upon bot restart everything will be lost, so no

fervent parcel
#

Yes, cuz I’d store that array in the db

burnt turret
#

That just makes for more complicated queries

fervent parcel
burnt turret
#

Fair enough I guess

fervent parcel
#

If anyone could like explain how itd look in code, cool
But otherwise I just dont get it

torn sphinx
#

Don't use technologies you don't know

#

There's nothing special with the ERD and how it would look

fervent parcel
#

Yeah but fun fact: if you explain it, I’ll know it

torn sphinx
#

Anyone with decent SQL knowledge would know how to implement and use that

burnt turret
#

The thing is, uMongo says that it "sticks close to the default driver syntax" - it claims it to be a feature

#

And now I'm more confused about ODMs, if you're defining a structure for the documents in your collection, why even use MongoDB anymore at that point? That's pretty much a relational database now

fervent parcel
#

Just smth like this?

    CmdName TEXT,
    GuildID INT,
    Status TEXT
)```
(Looks crap cuz im on my phone for a bit)
torn sphinx
#

why text

#

status just put a tinyint

#

0 is disabled 1 is enabled

fervent parcel
#

Hm yeah

torn sphinx
#

set a size to guildid

#

i would use varchar for ids

fervent parcel
#

im an actual idiot
I see how to use it now

#

You just select status where guildid and cmd name are things
And check if its true or false (or whatever)

raven trail
#

@burnt turret oof, yeah, that's sort of the same frustrations I ran into while researching, it doesn't seem like there is a particularly clean method to implement a nonblocking ODM Mongo build at least based on a high level view, that's why I was hoping someone who has considerably more DB work experience would be able to give a birds eye view to point me in the right direction haha, based on reading docs it seems like there are compromises in each

burnt turret
#

Yeah :/

#

I think it'd be simpler to just start writing it with motor tbh

raven trail
#

Alright, I'll have to check out the docs and start digging in

#

Do you know of any particularly good resources outside of the documentation?

burnt turret
#

for motor?

#

i believe the docs are the only reliable source (at least, i've only referred it)

#

are you familiar with querying in mongodb? (like in it's shell)

#

motor and pymongo remain very close to it, syntactically at least

primal plover
#

Hello, wanted to know if I could get some assistance in using dynamic pivot in a query using TSQL. Please let me know if this is the right channel to ask this question or point me to the correct channel

proven arrow
#

Use a trigger. Or have a background task/scheduled job. MySQL has built in event scheduling.

sick perch
#

If it should always be implied, use views or smarter queries instead of depending on "bit flipping"

proven arrow
#

Although views won’t change your values. That you will still have to do, if you still go down the route of having the status column.

torn sphinx
#

Is anyone around here familiar with SQLAlchemy?

#

Trying to insert into a many to many relationship

#

And the docs are not real clear on how that should be done

#

Also I'm doing my inserts this way: table().insert().values(**recipe.dict())

#

Not quite the other way where you create the ORM object and do some stuff with it. I think the above merely generates a query, however, I need to insert another group of PODs within the recipe object

#

Throwing them in just like that doesn't work

#

Recipe is a pydanctic object

#

It looks like this

class RecipeModelWithIngredients(BaseModel):
    id: UUID = Field(default_factory=uuid4)
    name: str = Field(max_length=50)
    description: str = Field(max_length=500)
    ingredients: List[IngredientModel]
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: Optional[datetime] = Field(default=None)
smoky flint
#

Can someone explain to me why this errors and still adds to the Database?

            cursor = self.db.cursor()
            cursor.execute("""
              INSERT INTO ticket (guild_id, ID, message_id, person_id) VALUES (?, ?, ?, ?)
            """, (self.bot.get_guild(payload.guild_id).id, self.ticket_count, ticket_message_id, payload.member.id))
            self.db.commit()
# 
ERROR
Ignoring exception in on_raw_reaction_add
Traceback (most recent call last):
  File "C:\Users\pc\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 333, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\pc\Desktop\DogCustoms\cogs\ticket.py", line 216, in on_raw_reaction_add
    cursor.execute("""
sqlite3.InterfaceError: Error binding parameter 2 - probably unsupported type.
Ignoring exception in on_raw_reaction_add
Traceback (most recent call last):
  File "C:\Users\pc\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\client.py", line 333, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\pc\Desktop\DogCustoms\cogs\ticket.py", line 216, in on_raw_reaction_add
    cursor.execute("""
sqlite3.InterfaceError: Error binding parameter 2 - probably unsupported type.
tacit umbra
# 
'''
                SELECT author_id FROM messages
                WHERE server_id = $1
                ORDER BY COUNT(author_id)
                DESC LIMIT 100
                '''
#

Thats my query

#

but I get the error, column author_id must be used in the GROUP BY clause

barren prairie
#

@tacit umbra COUNT is an aggregate function so you need to group the column passed to it.

Try

SELECT 
   author_id,
   count(author_id) as author_count 
FROM
    messages
WHERE
    server_id = $1
GROUP BY
    author_id
ORDER BY
    COUNT(author_id) DESC
LIMIT 100
tacit umbra
#

Ahh yes I understand. Thanks so much

smoky flint
# 
                    result = cursor.fetchone()
                    person = result[0]
                    await person.send('whalla')

how can i make person a object i can send dms to?

primal plover
#

Hello, wanted to know if I could get some assistance in using dynamic pivot in a query using TSQL. Please let me know if this is the right channel to ask this question or point me to the correct channel

proven arrow
#

Specify the schema name

#

I should’ve asked, Are you trying to transfer between schemas or something else?

#

You can also use pg dump to export the table and then import it into the other db/or schema

proven arrow
#

Yeah so for that your better off just using pg dump

torn sphinx
#

@lucid spear Don't pay someone for that, you can find that stuff on the internet. In any case see this:

import sqlalchemy
from databases import Database
from fastapi import FastAPI
from loguru import logger

from app.core.config import DB_DRIVER, DB_HOST, DB_NAME, DB_PASSWORD, DB_PORT, DB_USER


async def connect_to_db(app: FastAPI) -> None:
    logger.info(
        """
        Establishing connection to database {0}
        using driver {1} with user {2} on host and port: {3}:{4}
        """,
        repr(DB_NAME),
        repr(DB_DRIVER),
        repr(DB_USER),
        repr(DB_HOST),
        repr(DB_PORT),
    )
    metadata = sqlalchemy.MetaData()
    db_url = "{driver}://{user}:{password}@{host}:{port}/{name}".format(
        driver=DB_DRIVER,
        user=DB_USER,
        password=DB_PASSWORD,
        host=DB_HOST,
        port=DB_PORT,
        name=DB_NAME,
    )
    database = Database(db_url)
    engine = sqlalchemy.create_engine(db_url)
    metadata.create_all(engine)

    await database.connect()

    app.state.db = database

    logger.info("Connection established")
#

That's how I generate my connections with async

#

It requires more than SQL Alchemy

#

This example of someone doing it with FastAPI may help you figuring out how to do queries

lucid spear
torn sphinx
#

SQL Alchemy has old async stuff from what I remember, I don't know if it's all that great, I think they're upgrading it but that's a long ways off. What I do is to use that package called databases it lets you do async stuff with it

#

Though you don't necessarily have to do that, you can also run blocking code with threads.

#

It's sort of like "async" but not quite

brave bridge
#

@lucid spear We don't allow requests for paid work here. You can ask any questions you want here, though.

torn sphinx
# 
import pymongo
from pymongo import MongoClient

client = pymongo.MongoClient("mongodb+srv://Kek_Agent:<password>@cluster0.eva9c.mongodb.net/Cluster0?retryWrites=true&w=majority")
db = client["test"]
collection = db["test"]

post = {"_id":0,"name":"bob","age":5}

collection.insert_one(post)
#

I'm trying to insert data into a mongoDb database I have but I'm getting an authentication error

#

(I including my password where it says <password> i just didn't put it here)

#

If someone could help me out ping me hattip

signal nova
#

I have an automation system. I am using a sqlite database for this system. I installed sqlite on the computer I want to install the system, but I could not connect to the database. Anyone have a better idea for this system?

bitter yoke
#

Once, like a big initial setup? Or often?

eternal parcel
#

everything is perfect.. my password is written perfect but doesnt seem to work at all. this is my first time working with postgresql and i dont have any idea

unknown.png
torn sphinx
bitter yoke
#

So just to get a feel; what's acceptably fast for that? A few days? Hours? Less?

#

I won't ask 🙂

#

So I don't have a ready-made answer, but next question is -- how big are the records themselves?

burnt turret
# torn sphinx https://stackoverflow.com/questions/66597306/using-mongodb-on-discord-py-for-sch...

The accepted answer could do with some improvements; it seems to mix motor and pymongo syntax (declares client with pymongo, uses async for loop on retrieved cursor like motor). You should also be able to write your query so that instead of retrieving all documents in the db each time, you retrieve only those with time greater than current time 'datetime_field': {'$gte': datetime.datetime.now()}
(The example also assigns to all, which is a built-in function that you're now overriding, which is also not recommended. The bare except is also not best practice)

foggy iron
#

If u understand about how to convert a complex string to a list pls help me in #help-burrito , i think its not too basic, so if have a few experience pls help me ; -;

calm barn
#

can anybody tell me how can I check for array size greater than 1 in mongodb? for example something like this: {"amenities": {"$gt": {"$size": 1}}}

bitter yoke
#

oops, I forgot it was running, but I'm doing some really silly example imports, FYI -- no idea if they're close to what you'll be doing -- let me re-run it here with a proper timer

#

well maybe not so silly, mine was 2 columns

#

so I think i got 1,000,000 records into MySQL in under 6 minutes, but -- let me get back to you in 6 minutes -- and I'll try a ton more when I go to sleep if we're still on this 🙂

#

you're totally welcome, I promise i would not be re-doing this if it were not fun

#

do not underestimate my silliness -- neither of the two

#

this is a python script to spit randomized SQL to stdout, piping it into the mysql command line client

#

so it could feasibly be made a lot faster with that kind of thing

#

but then again maybe my math was off, we'll see

#

i would imagine it's about the same --

#

my gut says maybe psql would even be faster

#

but probably not enough to bother having a race

spare wasp
#

hi im currently building a discord bot and trying to store it on https://www.heroku.com and i have a database stored, but every 24hours it clears because the server does not save any files run by the worker. does anyone have ideas on how i would save it to the main directory that the bot can work from?

#

ping me if u find anything ty

bitter yoke
spare wasp
#

changing db sys

#

and if so which one

bitter yoke
spare wasp
#

ok

#

as i have made my database in sqlite which one would you reccomend to convert it and for ease of use?

#

ty in advance

bitter yoke
# spare wasp and if so which one

so sqlite is perfect for local dev stuff but heroku is set up with the theory that your current server and disk could disappear at any time, while they shuffle things around for load and things

bitter yoke
# spare wasp ty in advance

so all you need is some DB that doesn't rely on a disk file -- which, I guess this part is lucky -- is basically evreything but sqlite

#

standard answers are PostgreSQL or MySQL -- there are free heroku options for both

spare wasp
#

ok

bitter yoke
#

and getting stuff from sqlite into either one isn't too bad -- or if you can just start over with an empty db, even easier

proven arrow
#

@torn sphinx if your using a python library or odbc they generally have a executemany function to do bulk inserts. This would be a much more efficient way to make the inserts.

bitter yoke
#

yes even MySQL has a better bulk import than what I am doing out of the box here

#

basically if mine is close to tolerable speed, I think you're safe

spare wasp
bitter yoke
#

oh we're done -- 1,000,000 inserts -- each record is an int and a 50-char-or-so string

#

start/end are

Fri Mar 12 03:05:12 PST 2021```
spare wasp
#

gud luck

bitter yoke
#

and I'd be dumb to say "just multiply mine by 1000"

#

but hey it's CLOSE I BET

#

so we're still within your "days" limit but

#

now that it looks good let's rain on the parade

#

if you're going to want one or both fields indexed

#

we're blowing these times out of the water immediately

spare wasp
#

@bitter yoke tysm youve saved so much hassle

bitter yoke
spare wasp
#

if sql works ill choose that cause i have a book on it

bitter yoke
#

well if it will be full-on equality like stringA = "something" -- I'd say

#

bite the bullet now and index on import

#

if you're planning to look for substrings and "full text search" stuff

#

we're off-topic for what I hav energy for

#

ooof, yep 🙂

#

that's where it gets gray-area

#

not that it will be bad just

#

I will stop even joking about doing math to make guesses

#

i can give you one take-away I think I have come up with, but i only have one

#

basically, if you won't sweat something taking a few days at worst, you're probably okay for import time

#

but next -- now or later -- you're gonna want to ask about indexing time (which is a question for the bulk import and future inserts) and that relies on "how it will be used in real life"

#

and other such fuzzy things that involve real humans and other gross stuff

#

in my experience

#

famous last words there

#

(depends on the index tho)

#

well I did this twice so I have 2,000,000 records let me do

#

no sense in guessing when I have all this data

#

(I cleaned my HD this morning, lucky us)

#

so indexing one field

#

yes

#

it was about 10 seconds

#

good call

#

now

#

ok adding message and id

#

was even faster

#

hmmmmm

#

this is boring if it doesn't break

#

i'm gonna change it up a bit

#

yep

#

well let me confirm this

#

and here are a few records:

# 
| 1249418 | a a cYXLmjH PrZxoIuQN ezgiGjScA oPRXy qhXNrG E     |
|  770193 | a a dDEHGp xv Wf kURnMYeLkPUixQ is TXfRMYKyKS H    |
| 1166792 | a A eBCYhEUt OLW qLvRSKuwZI ul b ewowN UoSXvxdfqA  |
|  910017 | A a eIJVa b qut f UAMRI dYX PKpnUTXSR F hKFGwvR e  |
|   97422 | A a fGKUfvcGJF nVADCxTOx ZWymaN eKwCy lLmMK B Jay  |
|  741432 | A A fYVW GSwqdZQ FvYSclytwr nXxYVgp qh BFGpajcOBa  |
#

droped all indexes but primary

#

also I am dumb and of course the latter was going to optimize to just doing the ID query

#

but

#

re-adding the message index and now

#

well

#

without index was

#

0.392 sec

#

with index was

#

0.001 sec

#

and while this discord channel is not sponsored my MariaDB or Oracle

#

always know your indexes, kids (public service announcement)

#

but -- i won't do a play-by-play in public -- you'll find that if you start doing starts with and ends with and contains stuff (basically LIKE queries instead of = queries)

#

the indexes mean less and less

#

most of these DB engines will have "full text search" plugins/options/whatever which is a WHOLE other rabbit hole

#

so that's why I say

#

"probably don't sweat 'initial import time'"

#

but if you want to be the hero start thinking about "future query time" and such

#

i don't think this is as much data as it sounded like it was, in the current world

#

which is kind of terrifying

#

yep

#

well i mean

#

i also guess that

#

and would not hesitate to recommend it

#

given what I know

#

no, what I'm doing is about as bad as it could get without actually sending the text SQL from an east coast server to to a west coast server in the middle

#

my way is hacky af

#

even for text SQL mysql imports I could make much more efficient SQL statments for this

proven arrow
#

Also to add to that, disable indexes whilst inserting can also speed it up. And the fewer the insert operations the better it is. But this doesn’t mean you enter the whole billion rows into a single insert. You would get better performance if you split the billion up into smaller parts and insert those.

bitter yoke
#

yep, i just lucked out by forgetting to even create the indexes until after the insert

#

with MySQL I feel like mysqldump is doing a good job of the right balance of size-of-inserts to number-of-inserts these days, but I don't know how to make the same thing myself (apart from doing the insert slowly and dumping it and claiming I wrote the SQL)

#

but the splitting up of inserts is no joke, right? it's not just a little shaving off of time, it's significant gains?

#

even deciding to pick a random number like 50 or whatever it will allow would be noticeably better than 1 record per statement? or am I remembering it wrong?

proven arrow
#

Yeah your correct

bitter yoke
#

i think i'll kill my current "bigger attempt" and try that then, thx

proven arrow
bitter yoke
#

oh, yeah, i just saw a mention of something there I hadn't thought of -- that's saying even if you leave index on, the extended insert is going to have your back somewhat (I think)

proven arrow
#

Yeah because it’s got to redo the index

#

The cost of having indexes

bitter yoke
#

indexes are very zen that way

#

you get exactly what you ask for

#

just remember what you asked for

#

i remember this being why NoSQL seemed so inviting until we realized "wait, it still only works with indexes and there is no free lunch"

bright flame
#

Hey Guys!! I would like to get some insights about the following situation! I have a face detection algorithm which encodes the face info into a list of 128 real valued elements. So i want to have a encoded faces database for example data = {"person_name" : XYZ, encoding: [encoded_data],......} in a raspberry pie. Now I would like to know in which format I should store this data like as python objects, or pickle, or JSON, and sometimes I need to move this data to a centralised server. , i would like to know why that format. Next is there any database framework which ease the process like sqlite etc.. Final question, I want to have a hash of the data which can be published in block-chain, so that I can check for any tampering in the data. so what can I do to get a hash of the data stored in the database. Thanks in advance and redirect me if this the wrong channel to ask

bitter yoke
bright flame
#

yes I was just storing this data to a pickle object. and when a new face wants to be recognised, I will import the data and compare.

celest sleet
#

check like python sqlite3

molten meteor
#

@bright flame , if you opt for SQLite, the create_function method in the docs linked above by @celest sleet has as exemple that might just be what you wanted regarding hashing.

bright flame
raw blade
#

how do people think about long running database sessions in web apps? for example if i have a fastapi application, and i have some db.py that just has conn = psycopg2.connect(...); cursor = conn.cursor() and run cursor.execute in a bunch of functions, is there a better way to think about that? i ask because the connection closes at times, and i'd like the application to retry the connection to maximize uptime--i assume reconnecting prior to each query is bad, but i dont see anything obvious in the psycopg docs for keeping a conn alive or anything

brave bridge
rain plank
#

(If you aren't using an async framework, otherwise asyncpg.create_pool)

brave bridge
dapper root
#

CREATE TABLE collegedb (St_Name VARCHAR2(10),Uid VARCHAR2(4), Ad_Date NUMBER(10), Branch VARCHAR2(8), Fees NUMBER(5) );

error:
ORA-00904: : invalid identifier

raw blade
#

thanks guys! really appreciate it 🙂

jovial notch
#

@proven arrow

astral gorge
#

how can I convert asyncpg Record into a dictionary? because I'm somehow unable to modify the Record properties

burnt turret
#

why do you want to modify a Record object?

#

It's meant to be a read-only version of a row though

jovial notch
#

How could I copy and paste a table with it's columns and data from another database to another?

#

I'm using postgres and pgadmin4.

torn sphinx
#

look into it

jovial notch
#

where i do it in query editor

#

or where

torn sphinx
#

cli

rain plank
#

Not 100% sure but try making the first one author_id INTEGER PRIMARY KEY NOT NULL

#

oh?

burnt turret
torn sphinx
#

@burnt turret I have a slap a hug and a kiss command and wanna make a counter of how many times one person slapped the other person or kissed. should I just make a collection per item like one collection for slaps one for kisses? or is there a better way to just store them all in one collection

burnt turret
#

🤔 off the top of my head I'd probably have a single collection with documents like

{person1: id,
person2: id,
slapped_count: count,
kissed_count: count}
#

adding more commands would be just adding another field in the document

#

the disadvantage i can see would be there being a lot of documents

torn sphinx
burnt turret
#

just the first dict

#

that will be enough to match the document in the database

#

you can get the matched count from the entire document (which it will return)

torn sphinx
#

wdym matched count

burnt turret
#

I think I didn't explain that very well

doc = ...find_one({"person1": ctx.author.id, "person2": member.id})
doc["slapped_count"]
doc["kissed_count"]

would be how you'd be getting the counts

torn sphinx
#

okay that

#

does make sense

#

like a lot of sense

burnt turret
torn sphinx
#

yeah I get what u mean now

torn sphinx
#

ok let me try smth first I think im thinking way to difficult here

proven arrow
#

You need to give a length for varchar

torn sphinx
#

docs

#

ok

#

in the shell

#

terminal

#

i dunno, i just used the terminal, no fancy gui

worldly plaza
#

How do I create a pool in mysql.connector while using discord.py?

torn sphinx
#

im a little confused. my sqlite database makes a wal and shm file when ran. if i delete these files, all the data in the db seems to get lost.
is there something im doing wrong?

proven arrow
torn sphinx
#

alright, thank you

proven arrow
torn sphinx
#

im wondering if i have to close the db at all, im having a bot insert rows into it

#

i only use that db and nothing else

#

yea seems like it

proven arrow
#

When you close your app you can also close the db then

torn sphinx
#

do I have to to save the changes?

#

and will that prevent the wal files from getting huge?

proven arrow
#

You can just make commit after making changes which will save the changes

torn sphinx
#

im already doing that yeah

#

was just wondering if closing the db was optional if the code using the db was going to terminate anyways

#

because i send a keyboard interrupt to the code to close it

proven arrow
#

If there is nothing going on then it won’t have any effect

#

If you have some transactions going on then it would roll back

torn sphinx
#

i see, awesome. thanks

solid pine
#

can i get soem help with this error? I am running a gcp vm and getting this erorr regarding sqlite3

ModuleNotFoundError: No module named '_sqlite3'```
#

i apt-get installed sqlite3, and libsqlite3-dev

#

still get the error

bitter bone
#

SQLite 3.35 has been released

SQLite 3.35 has been released with some welcome additions and improvements.

See the full release notes here: https://www.sqlite.org/releaselog/3_35_0.html

torn sphinx
#

would this work?

# 
modlog.insert_one({"date": datetime.datetime.utcnow(), "id": ctx.author.id, "reason": 'Attempted Ban Command'})
bitter bone
#

try and see it!

torn sphinx
#

would datetime.datetime.utcnow() show the time currently or when this was logged?

#

bc i dont want it saying "today at 7:30" when it happened 2 days ago

worldly plaza
#

How do I create a pool in mysql.connector?

torn sphinx
#

is there anyone here that is good with data encryption and decryption I can dm? I’m working on a somewhat large project and i’m almost done with the data storage and handling, but the data I am securing is very personal so I want to encrypt it and decrypt it a specific way. I don’t really know much about it. If you wouldn’t mind please ping me in your response to this message

burnt turret
burnt turret
round osprey
#

Why does this error happen when I use async for row in cursor: in aiosqlite3?
TypeError: 'async for' received an object from __aiter__ that does not implement __anext__: generator

#

DROP TABLE tablename

bitter yoke
round osprey
#

oh yeah

#

right

bitter yoke
#

The simple answer is "because you can't, because the command is called 'DROP'" 🙂 The longer answer is that "DELETE" as an SQL verb is for deleting records from inside tables; DROP is used for changing the schema of the database itself. I don't know if there's much of a reason for which word was chosen for what, but they are different because "working data" is different from "changing the schema of the data".

#

The sort of parallel thing is that "INSERT INTO" is for putting data into a table, while "CREATE TABLE" makes a whole new table -- and "UPDATE" changes data in a table, while "ALTER TABLE" changes things about the table itself.

delicate fieldBOT
#

Hey @low mural!

It looks like you tried to attach file type(s) that we do not allow (.docx). 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.

low mural
#

Does anyone know how to solve this problem , please it's urgent

1.PNG
#

this one also

2.PNG
proven arrow
low mural
#

actually i need it urgently for the submission of an surprise assignment, so if anyone have already done it or has the answer please send it

proven arrow
#

😂

shy shuttle
#

you should be able to do that in an hour.....

burnt turret
#

VARCHAR(size)

#

how long should the items in that column be?

cursive latch
#

I have a question about adodbapi module with Microsoft SQL CE3.5

#

May I ask here?

#

Thanks, this is the code.

#

import adodbapi

conn_str = r"Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\pyuser\localDB.sdf;SSCE:Database Password='xxxx';"

conn = adodbapi.connect( conn_str )
print(conn.connection_string)
print(conn.dbms_name)
print(conn.dbms_version)

cur=conn.cursor()
cur.setinputsizes(1024)
#cur.execute("select * from users")
cur.execute("select * from users")

#

Output is;
=========================== RESTART: C:\pyuser\db1.py ==========================
Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\pyuser\localDB.sdf;SSCE:Database Password='private';
Microsoft SQL Server Compact
03.50.0000
Traceback (most recent call last):
File "C:\Users\takes\AppData\Local\Programs\Python\Python39\lib\site-packages\adodbapi\adodbapi.py", line 682, in _execute_command
recordset, count = self.cmd.Execute()
File "<COMObject ADODB.Command>", line 2, in Execute
File "C:\Users\takes\AppData\Local\Programs\Python\Python39\lib\site-packages\win32com\client\dynamic.py", line 271, in ApplyTypes
result = self.oleobj.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)
pywintypes.com_error: (-2147352567, '例外が発生しました。', (0, 'Microsoft Cursor Engine', '複数ステップの操作でエラーが発生しました。各状態の値を確認してください。', None, 0, -2147217887), None)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "C:\pyuser\db1.py", line 14, in <module>
cur.execute("select * from users")
File "C:\Users\takes\AppData\Local\Programs\Python\Python39\lib\site-packages\adodbapi\adodbapi.py", line 875, in execute
self._execute_command()
File "C:\Users\takes\AppData\Local\Programs\Python\Python39\lib\site-packages\adodbapi\adodbapi.py", line 690, in _execute_command
self._raiseCursorError(klass, _message)
File "C:\Users\takes\AppData\Local\Programs\Python\Python39\lib\site-packages\adodbapi\adodbapi.py", line 563, in _raiseCursorError
eh(self.connection, self, errorclass, errorvalue)
File "C:\Users\takes\AppData\Local\Programs\Python\Python39\lib\site-packages\adodbapi\apibase.py", line 57, in standardErrorHandler
raise errorclass(errorvalue)
adodbapi.apibase.DatabaseError: (-2147352567, '例外が発生しました。', (0, 'Microsoft Cursor Engine', '複数ステップの操作でエラーが発生しました。各状態の値を確認してください。', None, 0, -2147217887), None)
Command:
select * from users
Parameters:
[]

#

Sorry that it includes Japanse.

burnt turret
#

what are you writing this into?

#

Well then you have to be using the appropriate database connector module

#

which database is this?

#

this is written like a tutorial, it tells you how to get started

#

it doesn't teach you SQL though

#

SQL is the language you use to interact with databases (many of them)
SQLite is one such database. The sqlite3 python module lets you access an sqlite database from your python code.

#

it's generally just a .db file i think

#

I don't think you'll need to download anything

rich trout
#

Python comes with the code needed to create and manipulate sqlite files to your hearts desire

#

However, if you want to look at what's in the sqlite file using a premade GUI, there's tools out there you may want to download and use

#

Personally I find them useful when I'm writing an application that uses a database and want to know what exactly has ended up inside the database while debugging

#

I use DB browser for sqlite but I honestly did not do a lot of research. Pycharm professional can open databases. I bet there's a vscode plugin

burnt turret
#

DB browser is what I've used too

rich trout
#

SQLtools looks nice, it's a vscode plugin

#

It should add a little can icon to your icons on the left

#

to access databases from

#

And in your system information view control panel thing

#

It may ask you to install a database driver in small text

#

Right. There should be a link to click that'll send you to the marketplace with a list of drivers to use

#

One's for sqlite, which you'll want

#

ah

#

perhaps it's a better idea to try the other sqlite extension that's popular

#

alexcvzz.vscode-sqlite
specifically

#

it advertises no dependencies so it should just be install-and-run

pure meadow
#

I use quite a lot of SQL queries in one of my scripts. All my queries are about fetching, updating or deleting data. Currently the setup is built using sqlite3 library and pure sql statements . I'm looking to learn more about python libraries for SQL. Should I straight away look into SQLAlchemy or are there any interesting alternatives?

torn sphinx
#

I have a database in mongodb thats like this:
{"person1": ctx.author.id, "person2": member.id, "slapped_count": 0, "kissed_count": 0, "hugged_count": 0}])

now i wanna find out with how many people a user has hugged. how would I find that?

sudden thistle
#

I want to try out using databases, but I dont know which one to try. Does anyone have any suggestions for a free database that I can learn to use?

heavy dawn
sudden thistle
#

Discord bot

#

Storing special settings and stuff

heavy dawn
#

i would use a version of sql like sqlite for small bot or something mongodb if you want the data online
just find a thing you feel comfy with using

rain plank
#

postgres is better IMO

proven arrow
#

What do you mean other file to server?

#

Have you made the table already in the db?

#

What library are you using for the db?

#

So you would write an sql INSERT statement to insert data into the db. From python you would use the execute function of the sqlite3 module.

#

No

#

As an example

# 
import sqlite3

db = sqlite3.connect(":memory:")# make connection
# create db
db.execute("CREATE TABLE warns (id INTEGER PRIMARY KEY, user_id INTEGER, moderator_id INTEGER, reason TEXT)")
    
# insert 2 rows
db.execute("insert into warns values (1, 777, 123, 'reason a')")
db.execute("insert into warns (id, user_id, moderator_id, reason) values (?, ?, ?, ?)", (2, 567, 123, 'reason b'))

#fetch data
cursor = db.execute("SELECT * FROM warns")
rows = cursor.fetchall()
print(rows)

cursor.close()
db.close()
#

Instead of :memory: you use the path of your db file

#

Well not literally that because your table might be different

#

But try to understand the structure or what is going on

#

? Is used as a placeholder. Known as a parameterised query, It’s the safe way of writing sql queries and safeguards your application from sql injection.

#

It is

#

It takes 0 effort to use it, so why not just be safe in the first place

#

Yeah exactly, you can directly put the values there. In the example for abstraction I left it out

#

Does what need that?

#

The create table or the insert?

#

Yeah you can add that, to prevent the error.

#

But generally your tables will be created once in your app, and not try to created each time.

#

DROP TABLE tablename;

#

That’s the sql

#

No

#

That’s not entirely correct

#

The table columns need datatypes and that’s just a hardcoded string for the insert

torn sphinx
#

@burnt turret do u know how to find how many people a person has hugged?

await db_users.update_one({"person1": ctx.author.id, "person2": member.id}, {"$inc": {"hugged_count": 1}}, upsert=True)

this is how the database gets updated

proven arrow
#

No, look at the example I sent

#

That too but also the insert is incorrect

#

I already told you, your insert string is just a hardcoded string. The values will never get passed to it.

#

Look at the examples I have sent.

buoyant creek
#

Shelve is better than sqlite right?

burnt turret
#

why do you think so?

#

sqlite will let you write queries, which will let you do a lot more things

#

the shelve can store many arbitrary python objects though, so if what you want to persist cannot be easily made put into a table maybe a shelve might be better in that use case

buoyant creek
#

Idk :/

#

Im making a discord bot

#

And idk what to use

vernal sun
#

how can i upsert in sqlite3 ?

burnt turret
#

you're sending the cursor itself

#

the cursor contains the data

#

you can get the data with some fetch_x method, like fetchall()

buoyant creek
#

Anand, I was wondering because I saw someone telling me to use something else instead of sqlite3 where u add a lot of await

burnt turret
#

this will give you a list of all rows which matched your query

burnt turret
#

also don't use f-strings for SQL queries, read the third pin in this channel

buoyant creek
#

Thank you for the name, I just didn’t know the name haha, so u recommend me using sqlite3 or aiosqlite?

burnt turret
#

right so sqlite is the database itself

#

aiosqlite is a module we use to interact with this sqlite database (asynchronously)

shy shuttle
burnt turret
#

Yeah, was giving an example

#

you can directly loop over the cursor object too iirc

shy shuttle
#

yah, most python libs support that, probably the easiest way to do it.

shy shuttle
# vernal sun how can i upsert in sqlite3 ?

I think you can upsert in sqlite3 using the typical syntax:

INSERT INTO <table> (<columns>) VALUES (<values>) ON CONFLICT (<subset of columns>) DO UPDATE SET <this>=<that> WHERE <something something>
vernal sun
#

oh sweet! thanks

worldly plaza
#

How do I create a connection pool with sql.connector? I don't want to have to log into the database every single time someone runs a command.

burnt turret
#

but also, you shouldn't be using mysql-connector with discord.py, as it is blocking
look into aiomysql

#

!pypi aiomysql

delicate fieldBOT
burnt turret
#

and wow aiomysql has old documentation, they still use pre async-await syntax in the examples

worldly plaza
burnt turret
#

what did you not like about it?

worldly plaza
burnt turret
#

huh. that's certainly odd.

#

Well to each their own I guess.

buoyant creek
#

What are the docs for aiosqlite?

burnt turret
#

wait a minute edited the link

worldly plaza
#

I will also have to redo a lot of stuff if I do switch. Maybe later down the line I will switch ti aiomysql. For now my main priority is creating a pool to increase speed

buoyant creek
#

Those are the docs?

burnt turret
#

you can run blocking operations in an executor too

#

or sending it to a thread

#

!d asyncio.loop.run_in_executor

delicate fieldBOT
#
`asyncio.loop.run_in_executor` 
awaitable loop.run_in_executor(executor, func, *args)```
Arrange for *func* to be called in the specified executor.

The *executor* argument should be an [`concurrent.futures.Executor`](concurrent.futures.html#concurrent.futures.Executor "concurrent.futures.Executor") instance. The default executor is used if *executor* is `None`.

Example:... [read more](https://docs.python.org/3/library/asyncio-eventloop.html#asyncio.loop.run_in_executor)
buoyant creek
#

Bro nobody will help me because my question is 6 paragraphs long😐

worldly plaza
burnt turret
delicate fieldBOT
#
`asyncio.to_thread` 
coroutine asyncio.to_thread(func, /, *args, **kwargs)```
Asynchronously run function *func* in a separate thread.

Any *args and **kwargs supplied for this function are directly passed to *func*. Also, the current [`contextvars.Context`](contextvars.html#contextvars.Context "contextvars.Context") is propagated, allowing context variables from the event loop thread to be accessed in the separate thread.

Return a coroutine that can be awaited to get the eventual result of *func*.

This coroutine function is primarily intended to be used for executing IO-bound functions/methods that would otherwise block the event loop if they were ran in the main thread. For example:... [read more](https://docs.python.org/3/library/asyncio-task.html#asyncio.to_thread)
burnt turret
#

your bot will be frozen for the (albeit small) amount of time that it is talking to the database (but this can add up)

worldly plaza
burnt turret
worldly plaza
#

Yeah it doesn't block the event loop

burnt turret
#

which?

worldly plaza
#

using to_thread will not block the event loop

burnt turret
#

right

worldly plaza
burnt turret
#

honestly, I haven't tried that approach myself. I'd still recommend using the async library, as I think it'd be easier

worldly plaza
burnt turret
#

the async lib is aiomysql which you already decided not to use pithink

worldly plaza
burnt turret
#

and that is?

worldly plaza
burnt turret
#

show the code

worldly plaza
# 
@commands.command()
    @me_check()
    async def logging(self, ctx, channel: discord.TextChannel=None):
        conn = await aiomysql.connect(host='localhost', port=3306,
                                       user='root', password='open1234', db='discordbot')

        cur = await conn.cursor()
        if channel == None:
            await cur.execute(f"DELETE FROM logging WHERE guild = {ctx.guild.id};")
            embed = discord.Embed(description=f'**Moderation logs are now off**', colour=main.colour)
            embed.set_footer(text=f'Set by {ctx.author}')
            await ctx.send(embed=embed)
        await cur.execute(f"DELETE FROM logging WHERE guild = {ctx.guild.id};")
        await cur.execute(f"INSERT INTO logging VALUES('{ctx.guild.id}', '{channel.id}');")
        embed = discord.Embed(description = f'**Moderation logs will be sent to {channel.mention}**', colour=main.colour)
        embed.set_footer(text=f'Set by {ctx.author}')
        await ctx.send(embed=embed)
burnt turret
#

you don't seem to be committing the changes anywhere

worldly plaza
burnt turret
#

yes you're never closing them either

#

refer the docs on how you'd commit changes as well, i believe aiomysql is DB-API conforming so there should be some way to commit i think

#

or maybe pass autocommit=True to .connect

torn sphinx
# 
        await db_users.update_one({"_id": ctx.author.id}, {"$inc":{f"hugged.{member.id}": 1}})
        count = await db_users.find_one({"_id": ctx.author.id, "$exists": {f"hugged.{member.id}": True}}, {f"hugged.{member.id}": 1})
        print(count)

why doesn't this work

#

it should get the document if im right but it doesnt get anything at all

burnt turret
#

🤔 that second part doesn't look very right, why do you have {f"hugged.{member.id}": 1} at the end?

#

you're trying to insert user_name, user_id... etc not the values of those variables

#

the way you pass in the variables is with some placeholder, like this

torn sphinx
#

very good question, im guessing cause im using the member.id as ID name and the amount to store how many hugs the author has with that user

#

and the 1 to only get the one

#

of that specific member

#

cause u dont want the count of every member the author hugged

burnt turret
# 
cur.execute("INSERT INTO warnings VALUES(?, ?, ?, ?)", (user_name, user_id, author, reas))
```  no don't use f-strings or string formatting
The `?` act as a placeholder and the corresponding values will be passed in. read the third pin in this channel to understand why f-strings are  bad idea.
torn sphinx
#

whats wrong with strin formatting

#

oh

#

sure

burnt turret
#

gimme a second regg, let me try to understand what you're doing

#

yeah
look into aiosqlite though, sqlite is blocking and shouldn't be used with discord bots

#

well, almost - you're trying to insert the discord.Member object of author into the database so that will error

torn sphinx
# 
{
    "_id": ctx.author.id,
    "hugged": {
        "member1.id": 1,
        "member2.id": 4,
    }
}
#

trying to store the slaps with diffferent users

#

so it doesn't have 1000 files

#

but just one file with 1000 users in it instead

#

as example

burnt turret
#

not really, it's a different module so you can switch out the sqlite code you have with aiosqlite (i think theyre similar enough that you can just put awaits in front of queries, but check their docs)

#

author = ctx.author this is how you've defined author
you're trying to insert this author into your database.
No database will support inserting custom python objects (like this discord.Member object in this case)

torn sphinx
#

thats why theyre turned into string ig

burnt turret
#

yeah, a string can be stored

#

depends on what the type of the column is defined as

torn sphinx
#

lets say

#

I slap 10 people

burnt turret
#

so your document will have a

{
person1: 1,
person2: 2,
...

}
``` uptil 10 right
torn sphinx
#

"first person": 1 slap,
"2nd person": 4 slaps,

#

yeah

#

pretty much

#

but instead of person1 u store their memberID

#

as string

#

and the amount afterwards

#

is

#

hwo many times

#

u slapped

#

that person

#

or hugged

#

or whatever

burnt turret
#

that isn't the problem, try to read what i said again

torn sphinx
#

so everytime u slap that person it increments that value with 1

burnt turret
#

right i got the idea, i'm trying to think of how you'd query that 🤔

#

I think this is how I'd query it as well, but that last dictionary is redundant

#

wait i meant to reply to a message

burnt turret
#

count = await db_users.find_one({"_id": ctx.author.id, "$exists": {f"hugged.{member.id}": True}})

#

1 and True can be used interchangeably there

#

they mean the same

#

I'm sorry, I'm trying to help you both at the same time but I can't really keep up

torn sphinx
#

help him first dw

burnt turret
#

Right, it won't let you do that because it is of a type that it is not expecting.

# 
               (Username TEXT, Userid INTEGER, mod TEXT, reason TEXT)'''``` this is how youre defining the table
#

so the third column is where you're trying to insert the ctx.author into

#

see how you've defined that column - mod TEXT

#

the column is of type text, so you can only insert some kind of string data there (and not discord.Member which you are trying to do now)

#

you can insert str(ctx.author) instead, this will save the author's username#discrim to the table

#

But I'd probably have stored user ID for the mod too, as usernames can change on discord but IDs will always be the same for an account

#

yes

burnt turret
burnt turret
#

that would be better, yes

#

because the mod could change their username and now you've got no idea who did the warning

torn sphinx
#

good luck

#

ok anand ur all mine again 🤪

burnt turret
torn sphinx
#

ty

#

so I tried what you said but

#

still when I print

#

it gives no output

burnt turret
#

Huh that's odd

torn sphinx
#

yeah

#

it is

burnt turret
#

give me a second, i'm gonna read the docs on how $exists works again

#

it's been a while since i've used mongodb 😅

torn sphinx
#

thats why im asking you

#

gotta keep that memory fresh 😌

burnt turret
#

it's {field: {$exists: bool}}

torn sphinx
burnt turret
#

yeah

worldly plaza
burnt turret
#

remember to quote the $exists too

torn sphinx
burnt turret
worldly plaza
torn sphinx
burnt turret
#

show the code

torn sphinx
#

okay I got

#

53 less errors

#

by adding one bracket

#

it doesnt like having

#

3 brackets

#

next to each other though

burnt turret
#

, {f"hugged.{member.id}": 1} this part is unnecessary

torn sphinx
#

wouldnt change the error though

burnt turret
#

count = await db_users.find_one({"_id": ctx.author.id, f'hugged.{member.id}': {"$exists": True}})

torn sphinx
#

the last part

#

is what chooses

#

the count

#

of the member

#

that u just hugged

#

like u have the list
person1 : 4
person2 : 1
...

#

the last part chooses person1 or person2 or personX

worldly plaza
#

@burnt turret Where do I define the connection to the db so that it connects when the bot goes online then allows me to refer to it from any function?

burnt turret
burnt turret
#

first pin, there's a link for asyncpg with discord.py

torn sphinx
#

does it make a lot of difference

burnt turret
#

although it is asyncpg the procedure is the same

torn sphinx
burnt turret
torn sphinx
#

without it

#

doing ur way

#

{'_id': 271016446518231046, 'hugged': {'271016446518231046': 17, '620307267241377793': 1, '729826500941185034': 1}}

#

u get this

#

I guess it wouldnt amke

#

much of a difference

burnt turret