#databases

1 messages · Page 130 of 1

nocturne yew
#

psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "movies_pkey"

#

how to avoid this problem

earnest parcel
#

The key is supposed to be unique but a record already has that key value

hollow moon
#

hey can someone one tell me how to get data returned based on the timestamp? like i want to get data returned based on the day or month or year

#

this is one of the Colom in sql datetime timestamp with time zone NOT NULL

rich trout
#

how certain are you it actually closed?

nocturne yew
weak tinsel
#

primary key is also supposed to be unique

#

@nocturne yew

nocturne yew
#

i added movies like fury, curious case benjamin and one more, now there are 3 brad pitt

📎 unknown.png
#

i removed primary key as actor_id

#

so i can i insert only one time actor_id

#

if i say actor_id primary key not null, it gives uniquekey.violation error

#

i need to set condition like IF EXIST ignore

weak tinsel
#

primary key by default is not null

#

afaik

nocturne yew
#

i googled in intenret, i could not find example

weak tinsel
#

well, you can make a try except clause wherein you try to insert duplicate values and if it already exists in table, you just pass

#

primary key must be enabled for this

#

or unique constraint

nocturne yew
#

cursor.execute("""INSERT INTO movies (imdb_id, full_title ) VALUES (%s, %s )""", (movie_event["id"], movie_event["fullTitle"] ),)

#

it is my code

#

where i need to say except

nocturne yew
hollow moon
#

hey can someone one tell me how to get data returned based on the timestamp? like i want to get data returned based on the day or month or year
this is one of the Colom in sql
datetime timestamp with time zone NOT NULL
pls help

austere portal
rose rover
#

Someone who has experience with both , mongo and sql which is better

deft hedge
rose rover
#

thanks

weak tinsel
#

uhh

#

i prefer SQL all the way

#

the usecases differ

lyric junco
# 
    @commands.command()
    async def premium(self, ctx,  member: discord.Member.id):
        members = str(member)
        db = sqlite3.connect('./cogs/database/premium.sqlite')
        cursor = db.cursor()
        cursor.execute("INSERT INTO premium(User_IDs) VALUES(?)", members)
        cursor.fetchone()

is this the right way to insert mentioned user id in table?

#

anyone ?

smoky pendant
sturdy vine
#

my excel sheet

for example:
i want pandas to send
Destination: n/a
Distance: n/a
etc

📎 unknown.png
#

my code:

@bot.command()
async def data(ctx, date: str):
    open(Log)
    df = pd.read_excel(Log)
    data = df.set_index(date, drop = False)
    dembed=discord.Embed(description = f'{data}', color=0x6b1aea)
    await ctx.send(embed=dembed)
#

managed to do this, so far so good (at this point i am documenting my progress as no is active on here lol)

📎 unknown.png
bleak pecan
#

Just want the NaNs? .values

sturdy vine
#

i want it to send the column "test" and the unnamed column (in place of the numbers)

bleak pecan
#

So the index & the column... One sec

#

Its just data["test"] you're after without the dtype line I suppose?

sturdy vine
#

ya

bleak pecan
#

.to_string() on that

sturdy vine
#

so

    df2 = df[[f'{date}']].to_string()
    await ctx.send(df2)
```?
bleak pecan
#

Shouldnt need the extra brackets, but should be what youre after

sturdy vine
#

alright, thanks

#

ill try it and let u know how it works

#

i want to keep test at the top and remove the number, but that ended up doing the opposite, hmmm

📎 unknown.png
bleak pecan
#

Can you print df there see where the text went.

sturdy vine
#

i wanna keep the destination, distance, start, end, and difficulty too

#

for some context, i am making a discord bot that logs data from group bike rides

bleak pecan
#

So on the read add index_col=0, or you can pick 2 columns

sturdy vine
#

ill try that thanks

# 
data = df.set_index(index_col=0, date, drop = False)

date is now giving me the error: Positional argument cannot appear after keyword argumentsPylance

bleak pecan
#

Its for the line above that one

sturdy vine
# 
df = pd.read_excel(Log)
``` the read line?
bleak pecan
#

(what its complaining about is youve but the arg data after a kwarg)

yea (Log, index_col=0)

sturdy vine
#

ohhh ok

#

thanks

#

perfect, it worked thanks 🙂

#

now to figure out how to write with pandas to the excel sheet through discord 💀

solar pollen
# 
CREATE TABLE user_preferences(
  user_id BIGINT,
  guild_id BIGINT,
  PRIMARY KEY (user_id, guild_id)
);

How could I create a primary key like this using flask sql alchemy?

sturdy vine
# 
@bot.command()
async def log(ctx, date: str, destination: str, distance: float, start: str, end: str, difficulty: float):
    open(Log)
    df = pd.DataFrame
    df = pd.read_excel(Log)
    df.write[f'{date}'] = f'{destination}', f'{distance}', f'{start}', f'{end}', f'{difficulty}'
    await ctx.send(f'New biking data logged')
    print('Documented')

how do i use pandas to write to an existing excel file? obviously df.write doesnt work

#

date is the new column, and i want everything after to be a new row in the column

simple geyser
#

I'm trying to make a discord bot store a channel ID once you run a command for later use in the code how would I go about doing so using the SQLite system

burnt turret
#

any recommendations for an async ORM?

brazen charm
#

@burnt turret orm

burnt turret
brazen charm
brazen charm
burnt turret
#

alright thanks, on a quick search this and tortoise, gino were the ones which came up

#

why would you recommend this one over the others?

#

actually scratch gino off that it seems to only support postgres right now

brazen charm
#

Litterally never used the others but orm generally is pretty robhst being built ontop of alchemy core and also supports asyncpg where as i think most will jse aiopg which is pretty slow

burnt turret
#

oh alright, skimming through tortoise it seems to be using asyncpg too; but i'll use orm anyways

#

thanks :D

proven arrow
brazen charm
proven arrow
#

Why not?

#

They are modelling user_preferences so i am assuming this is a many to many relation. In which case this is how it can be done and is perfectly fine.

brazen charm
#

Generally i find it ends up with people creating many to many relationships instead of properly normalizing the data

proven arrow
#

Well what is wrong with many to many?

brazen charm
#

They're incredibly in-efficient mostly

proven arrow
#

A user can have many preferences, each preference can belong to many users. That is what a many to many is.

proven arrow
brazen charm
#

generally this is a pet peeve of mine though

#

if you have a many-to-many relationship generally you can have a linking table and reduce data duplication

proven arrow
#

Well that is what they have here. And that is how many to many is done. Via the junction table.

brazen charm
#

If they're making strictly a linking table then that's fine i might just be miss interpreting what they're doing

proven arrow
#

Hmm, well their table is misleading to be fair as it has no mention of preference in the columns and instead has a column for a guild. But many to many as a relation is not inefficient, when done properly like you mention with the linking table.

torn sphinx
#

Hi, so currently i have query like this

# 
 async with await cursor.execute("SELECT * from prods where store=? and (cat1 = ? or cat2 = ?)", (store, cat, cat)):
            data = await cursor.fetchall()
#

so this is not an issue becausw currently is working but is there a better way to write this because i must currently pass in two values for cat

proven arrow
#

Yes you can. You can check if a value is in a list of columns using IN

#

Select * from prods where store = ? and ? IN (cat1, cat2)

torn sphinx
#

ok let me try this

#

also do you know if fast api is good?

#

i use this with that

proven arrow
#

I don't use it 🤷‍♂️ You tell me if it's good. if it does what you want and makes it easy to build your API then yeah.

torn sphinx
#

query works thanks

#

and yes for me it was easy to make my api. made my life easier but ok thanks

weak tinsel
#

uhh

torn sphinx
weak tinsel
#

offtopic but - pypresence

torn sphinx
#

alright thanks

solar pollen
solar pollen
#

Is there anyway to check the resulting SQL command for db.create_all()?

solemn ridge
#

Hello, I am having a debate on whether this is correct to do or not:

I have a "users" table that amongst other columns contains a column called "MessageCollection", the message collection column holds a foreign key for a row in the "messagecollections" table, this table holds the following columns: id and messageIDs, the messageIDs is a serialised list that contains multiple IDs of rows in the messages table. Is that the best way to store the ids or am I missing something?

#

that is how it looks

#

I will be serialising the list using json and json.dumps()

torn sphinx
# 
self.Item_Name = "Test"
qty = 1
users_col.update_one("_id":f"{fm}x{to}", {"$inc":{f"inventory.{self.Item_Name}": amount:qty}, upsert=True)

@burnt turret

burnt turret
#

what was the error here again?

#

can't increment right?

#

what is amount here? asking because the syntax there looks a bit odd

torn sphinx
#

its quantity of the item

#

integer

autumn epoch
#

So im trying to connect to my postgresql database but it gives me this error:

raised an error: OperationalError: FATAL:  Peer authentication failed for user "MyUserName"
proven arrow
#

@autumn epoch can you show how you are connecting to the datbase and what module you are using

autumn epoch
# 
conn = psycopg2.connect("dbname='users' user='username' password='password'")
proven arrow
#

try providing the hostname

autumn epoch
#

Where would I get that?

proven arrow
#

add host='localhost' to the connection string

autumn epoch
#

Ok

#

Should that be first?

#

Before dbname

proven arrow
#

doesnt really matter

autumn epoch
#

Ok

#

New error @proven arrow :

ror: OperationalError: could not translate host name "host=localhost" to address: Name or service not known
proven arrow
#

what platform are you on?

autumn epoch
#

linux machine

#

Server edition

proven arrow
#

try host=127.0.0.1

autumn epoch
#

Shouldnt I also add the port

proven arrow
#

you can

autumn epoch
#

So 5432

proven arrow
#

yes if that is what its running on

autumn epoch
#

Same error

proven arrow
#

peer error or hostname error?

autumn epoch
#

Hostname error

#

It worked on my macbook

#

The postgresql might not of been setup properly

proven arrow
#

can you connect to postgres via cli on your server?

autumn epoch
#

Wdym cli?

proven arrow
#

A terminal

autumn epoch
#

Yes that is how I connect

#

I ssh to it

proven arrow
#

But to postgres from your server

burnt turret
# 
      {_id: f"{fm}x{to}"},
      {$inc: {f"inventory.$[elem].amount": qty},
      {arrayFilters: [{elem.name: self.Item_Name}], upsert: 1}
)
``` @torn sphinx you want to be using mongo's arrayFilter option here - you can see the example from https://docs.mongodb.com/manual/reference/method/db.collection.update/#update-specific-elements-of-an-array-of-documents
thing is, i've written that in a way that (i think) would be working on mongo shell, ~~and not pymongo itself. i can come back in a while and try explaining this to you and help you convert this to something that works in pymongo, because i'd have to refer it's docs again, if that's fine?~~
```sql
user_col.update_one({"_id": f"{fm}x{to}"}, {"$inc": {"inventory.$[elem].amount": qty}, array_filters=[{"elem.name": self.Item_Name}], upsert=True)
``` i _think_ that's what it would look like in pymongo
proven arrow
#

Or you can try turning off peer connection from your postgres settings, but even with that on it should work. But I would first make sure your postgres service is running before you go troubleshooting with that or before you modify any more python code.

autumn epoch
#

I might try doing a fresh reinstall of postgresql

indigo smelt
#

can someone please give an article so i can learn databases?

zealous plume
#

i need a large database of caracal pictures

civic trail
#

I use json as a database

brazen charm
young seal
#

Anyone have experience using python + ssis / visual studio

loud ore
#

hi, I have a problem that many of u can solve. Can help me at #help-honey

#

please!

frozen root
#

hello

#

every one

fallow silo
#

What are the advantages of ORM?

sturdy vine
#

BadZipFile: File is not a zip file

#

i get this error while trying to use openpyxl

lyric junco
# 
from assets.imports import *


class Premium(commands.Cog):

    def __init__(self, bot):
        self.client = bot
        db = sqlite3.connect('./cogs/database/premium.sqlite')
        cursor = db.cursor()
        cursor.execute('''
                                CREATE TABLE IF NOT EXISTS post(
                                    User_IDs INTEGER
                                )
                                ''')

    @commands.Cog.listener()
    async def on_ready(self):
        print("premium Cog has been loaded\n-----")

    @commands.command()
    async def premium(self, ctx,  member: discord.Member.id):
        members = str(member)
        db = sqlite3.connect('./cogs/database/premium.sqlite')
        cursor = db.cursor()
        cursor.execute("INSERT INTO premium(User_IDs) VALUES(?)", members)
        cursor.fetchone()


def setup(bot):
    bot.add_cog(Premium(bot))

I want to add temporary data to this for 30 days and create a loop which checks every 24h

proven arrow
#

@fallow silo They can make development a lot easier and you can have complex SQL queries with little SQL knowledge. They automate a lot of the stuff for you, and can save you time from writing queries.

fallow silo
proven arrow
#

Yes they generally should allow you to write raw queries as well

sturdy vine
#

can anyone help me with making new columns in an existing excel file?

torn sphinx
#

how can I set up a database for storing warns for my discord bot?

proven arrow
torn sphinx
#

no idea

#

@proven arrow i am very new to databases

#

no prior experience

proven arrow
# torn sphinx <@!613362435860070414> i am very new to databases

Do you know how they work on a basic level like there is tables and rows? I dont really know of any good beginner guides but a quick google search should return some. Although there are different kinds of databases, and the one you would want to look into is a relational database (or commonly known as an SQL database). This is basically just a database where you have tables. Each table can have many columns/rows. Just like a spreadsheet. And to interact with the database you use a query language called SQL (its easy to understand so dont worry you dont have to learn a new language.) I could find this video on khan academy which should make it easy to understand. https://www.khanacademy.org/computing/computer-programming/sql/sql-basics/v/welcome-to-sql

Khan Academy
Welcome to SQL (video) | SQL basics | Khan Academy

SQL is useful for creating and querying relational databases. Learn how to use SQL with this interactive course!

torn sphinx
#

thanks for the resources

proven arrow
#

In that case that link should cover how to use sql.

kind temple
#

Hey guys, can someone validate if my sql code is syntax correct?

#

im pretty bad at it

torn sphinx
#

@proven arrow but quick question, how do I use sql within python?

proven arrow
#

With what database?

torn sphinx
#

@proven arrow explain?

#

what database would be best for storing warns of a user?

#

i want to actively modify it

proven arrow
#

discord warns?

torn sphinx
#

yes

proven arrow
#

How many times will you be writing or reading from the database?

torn sphinx
#

multiple times

#

depends

proven arrow
#

And do you want a server based database or file based? Server meaning the database needs to be installed and run on a server, and file would require no installation of anything and sits as a single file on your pc.

torn sphinx
#

i can't say a set number

proven arrow
#

You can decide that whenever, but here is an example of how it can be with sqlite that comes installed with python

torn sphinx
torn sphinx
proven arrow
#

!eval

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()
delicate fieldBOT
#

You are not allowed to use that command here. Please use the #bot-commands channel instead.

proven arrow
#

Right im not sure why it doesnt work here, but thats a simple way of how you can make a table, insert data, and select. @torn sphinx

torn sphinx
#

thanks!

proven arrow
#

I updated it to show how you can pass variable parameters to it as well

proven arrow
# torn sphinx which is better?

A server based is normally more powerful and will be able to write more data, handle more users. Its important if you will be writing a lot to it.

torn sphinx
#

im planning to switch to azure anyway

torn sphinx
proven arrow
# torn sphinx sqlite is for smaller databases?

No not always depends what your doing. That link explains it well. But most importantly, sqlite can only have a single writer at any given time. That means you cant have multiple processes/users writing data to it at the same time. So if you have a lot of requests at a time to write then you may run into issues.

#

And it lacks some features which a server based db has, but that you probably will never use.

#

Its quite common when you want embedded databases as well. Like I recently developed an mobile app, and I used sqlite as the database for on device storage, and for serving data from the api.

river sand
proven arrow
river sand
proven arrow
#

You might get full page reloads but that's fine. Or use Ajax for it. Better to ask in #web-development

#

Why would it be annoying?

river sand
#

Truuuee

lavish narwhal
#

Does anyone know how you could retrieve duplicate entries? Right now I’m using SQLite and it’s only retrieving the first entry it finds.

river sand
#

Not sure, just my fear. I've never seen a nice flask app lol

lavish narwhal
#

So if I’m searching for a users warnings, it just gets the first one it finds

river sand
#

Thanks @proven arrow

proven arrow
#

Front end makes it nice. Flask is back end

proven arrow
lavish narwhal
#

yeah one second

torn sphinx
#

on the sqlite page

lavish narwhal
# 
databaseData = [database.MRP_Blacklist_Data.DiscUsername, database.MRP_Blacklist_Data.DiscID, database.MRP_Blacklist_Data.Gamertag, database.MRP_Blacklist_Data.BannedFrom, database.MRP_Blacklist_Data.KnownAlts, database.MRP_Blacklist_Data.ReasonforBan, database.MRP_Blacklist_Data.DateofIncident, database.MRP_Blacklist_Data.TypeofBan, database.MRP_Blacklist_Data.DatetheBanEnds]
        for data in databaseData:
            try:
                q: database.MRP_Blacklist_Data = database.MRP_Blacklist_Data.select().where(data == string).get()
            except:
                continue
            else:
                dataFound = True
                break
river sand
#

Really isn't it a web framework?

#

isn't there a getall()

proven arrow
torn sphinx
#

ok

lavish narwhal
#

I’m using peewee if that helps

river sand
proven arrow
river sand
lavish narwhal
river sand
#

have you tried just executing this query on the db outside your code? should it be returning multiple lines? and is this string variable appropriate for all the queries? @lavish narwhal

#

This is the first time I'm seeing peewee so nothing is really jumping out at me right now

#

Other question are you just overwriting the same variable each time?

#

q: database.MRP_Blacklist_Data = database.MRP_Blacklist_Data.select().where(data == string).get()

#

also also q: <var> = <expr> is this a python3 thing I don't know about?

lavish narwhal
#

@river sand sorry for the late response. But yeah, I’ve gotten multiple responses outside of the code and apparently in the example that’s how I defined the variable q

river sand
#

I don't think this is the problem but do you know what it's supposed to do?

#

but also going back to the quickstart page, it specifies get only returns a single record

#

it looks like without the get operator, the query returns an iterable

lavish narwhal
#

So using the second example should bring back multiple records right?

river sand
lavish narwhal
#

Ah!

#

Looks like I didn’t surf through the docs deeply

#

Thanks! I got an idea now on how I can resolve the issue.

river sand
#

Sounds good

river sand
torn sphinx
#

@proven arrow any way of inserting multiple lines into a value?

sturdy vine
#

can anyone help me with pandas and openpyxl?

lyric junco
# 
    async def follow(self, ctx, tag):
        db = sqlite3.connect('./cogs/database/post.sqlite')
        cursor = db.cursor()
        cursor.execute(f"SELECT TAG_NAME FROM post WHERE TAG_NAME = '{tag}'")
        result = cursor.fetchone()

        if not result:
            await ctx.send("No one created this tag, you can be the first")
            return

        try:
            db = sqlite3.connect('./cogs/database/users.sqlite')
            author_id = str(ctx.message.author.id)
            cursor = db.cursor()
            user_sql = "INSERT INTO users(AUTHOR_ID, TAG_NAME) VALUES(?,?)"
            values = (author_id, tag)
            cursor.execute(user_sql, values)
            db.commit()
            results = cursor.fetchone()
            if results is not None:
                await ctx.send(f"You're already following {tag}")
                return
            await ctx.send(f"Followed {tag}")
        except discord.Forbidden:
            await ctx.send("This tag doesn't exist anymore")

why this always overwrite table??

#

please help me

karmic parcel
#

Does anybody know how to fix django.db.utils.DatabaseError?

pure dirge
#

Is there a way to use input(f" type function like how print works?

shell ocean
#

and how did you get it

pure mortar
#

is this the SQL channel

weak tinsel
#

i have a question regarding asyncpg and dpy

#

i made a async function that returns a connection pool in main.py , where the bot is initiated

#

and i access the pool in the cogs by - pool = await function()

#

and then do pool.fetch or pool.execute etc

#

the problem im facing is that as i load more cogs, and execute some db based commands in discord

#

i get an error - too many connection pools for the user - ...

#

so , is there a better way of doing this?

burnt turret
#

what you should be doing is making a single connection pool in your main.py, and assigning it to a bot variable

weak tinsel
#

oh

burnt turret
#

after which it'll be accessible in all cogs as self.bot.<name of variable you set>

weak tinsel
#

so,

#

pool = asyncpg.create_pool()

#

bot.pool = pool

#

?

burnt turret
#

you could directly do bot.pool = asyncpg...

weak tinsel
#

ahh

#

yes

#

thanks

#

also

#

how do i execute() , fetch () etc

#

bot.pool.execute()?

burnt turret
#

with the bot variable itself

#

yep

weak tinsel
#

nice

#

thanks a lot

#

@burnt turret one more thing

#

i can put the pool in the init method right?

burnt turret
#

i don't remember how pools are made, but if it requires an await you probably won't be able to

#

one sec

weak tinsel
#

nono

#

what i meant is

#

self.pool = bot.pool

#

in the init dunder

burnt turret
#

oh lmao

#

sure why not

weak tinsel
#

aight

burnt turret
#

that's just assigning a variable

#

i thought you were asking how you'd make the pool itself in an init

weak tinsel
#

@burnt turret new problem, how to access the pool outside of a cog, in a separate py file?

burnt turret
#

you can just import your instance of the bot there too

weak tinsel
#

hmm i tried that

burnt turret
#

and?

weak tinsel
#

from main import bot

#

pylint gave an error so i dropped it

#

i did not test it tho

burnt turret
#

it'll work if you get the import statement right

weak tinsel
#

hmm , i will try

wet raft
#

hi is anyone familiar with using python shelve as database?

#

is it possible to store pictures in shelve?

ruby whale
#

hello, from what I know there is a library in JavaScript called "sequelize" which allows programmers to r/w the db without having to remember the SQL commands. (https://sequelize.org/master/)

Is there an equivalent library in python?

proven arrow
ruby whale
#

tysm

glossy iron
ruby whale
#

Yeah but I went for encode/orm since it supports async which is what I forgot to mention

delicate fieldBOT
#

:incoming_envelope: :ok_hand: applied mute to @torn sphinx until 2021-01-26 15:11 (9 minutes and 59 seconds) (reason: discord_emojis rule: sent 49 emojis in 10s).

lavish narwhal
#

Oof

torn sphinx
#

it works! thank you

#

but upserting didn't seem to work so I created an if-else statement to check if item doesn't exists, I just $push if it doesn't

tiny needle
#

instead of concatenation, how do you insert a value from a variable in psql

lavish narwhal
#

@river sand

@commands.command()
    async def DBget2(self, ctx, *, string: str):
        try:
            database.db.connect(reuse_if_open=True)
        except:
            await ctx.send("ERROR: Error Code 1")
            return
        
        dataFound = False
        databaseData = [database.MRP_Blacklist_Data.DiscUsername, database.MRP_Blacklist_Data.DiscID, database.MRP_Blacklist_Data.Gamertag, database.MRP_Blacklist_Data.BannedFrom, database.MRP_Blacklist_Data.KnownAlts, database.MRP_Blacklist_Data.ReasonforBan, database.MRP_Blacklist_Data.DateofIncident, database.MRP_Blacklist_Data.TypeofBan, database.MRP_Blacklist_Data.DatetheBanEnds]
        for data in databaseData: 
            query = database.MRP_Blacklist_Data.select().where(data = string).get()
            for person in query:
                await ctx.send(person.DiscUsername)

Trying it this way but im getting a keyword error. Isn't this the right way of fetching all the matching records? 🤔

weak tinsel
#

can .format() lead to a sql injection??

#

ahh sheesh it can

#

so much for textbooks suggesting .format()

river sand
#

@lavish narwhal can you paste the error?

lavish narwhal
#

yeah one second

burnt turret
river sand
#

TypeError: where() got an unexpected keyword argument 'data'

lavish narwhal
#

Normally that error doesn't pop up.

#

This was my old way old way of doing it

#

(without an error)

databaseData = [database.MRP_Blacklist_Data.DiscUsername, database.MRP_Blacklist_Data.DiscID, database.MRP_Blacklist_Data.Gamertag, database.MRP_Blacklist_Data.BannedFrom, database.MRP_Blacklist_Data.KnownAlts, database.MRP_Blacklist_Data.ReasonforBan, database.MRP_Blacklist_Data.DateofIncident, database.MRP_Blacklist_Data.TypeofBan, database.MRP_Blacklist_Data.DatetheBanEnds]
            for data in databaseData:
                try:
                    q: database.MRP_Blacklist_Data = database.MRP_Blacklist_Data.select().where(data == string).get()
                except:
                    continue
                else:
                    dataFound = True
                    break
#

Only difference is that its in a nested for loop

river sand
#
  1. data == string it looks like you have a single = in the last snippet
lavish narwhal
#

Hm.

river sand
#
  1. according to the quickstart there's no .get()
#

you're creating an iterable to loop over so you should be using query = database.MRP_Blacklist_Data.select().where(data == string)

#

then for x in query: do whatever

lavish narwhal
#

alright let me test it out

river sand
lavish narwhal
#

It just rapid sent stuff 🤔

# 
@commands.command()
    async def DBget2(self, ctx, *, string: str):
        try:
            database.db.connect(reuse_if_open=True)
        except:
            await ctx.send("ERROR: Error Code 1")
            return
        
        dataFound = False
        databaseData = [database.MRP_Blacklist_Data.DiscUsername, database.MRP_Blacklist_Data.DiscID, database.MRP_Blacklist_Data.Gamertag, database.MRP_Blacklist_Data.BannedFrom, database.MRP_Blacklist_Data.KnownAlts, database.MRP_Blacklist_Data.ReasonforBan, database.MRP_Blacklist_Data.DateofIncident, database.MRP_Blacklist_Data.TypeofBan, database.MRP_Blacklist_Data.DatetheBanEnds]
        for data in databaseData: 
            query = database.MRP_Blacklist_Data.select().where(data == string)
            for person in query:
                await ctx.send(person.DiscUsername)
#

But the intended solution was to show the duplicate records (so there should technically be 2 responses sent out as there are 2 records named stuff)

river sand
#

I'd recommend putting in a few breakpoints and seeing what the variables are around that query if you're not getting the results you expect

lavish narwhal
#

I've already tried using it with some breakpoints but no luck with that either.

severe bane
#

hey guys

#

idk if my question should be here ... but I want to know how to store a dictionary in a csv file and then be able to read it and create the same dictionary from it

torn sphinx
#

hey I got a certain key that is referenced as a foriegn key in a lot of other tables. any way to drop that key in one table, and have it get removed in all the others with it?

#

preferably in one statement

delicate yoke
#

Some one recently mentioned an asynchronous sqlite package for python in the #discord-bots but I can't remember the name of it. Can someone please point me in the right direction?

proven arrow
proven arrow
torn sphinx
#

anyone know why this won't work

# 
select * from question where question.quizname, question.quizowner in (select quizname, quizowner from quiz_keys where quiz_key=4269);
#

figured it out

delicate yoke
#

I've also found some things online that say sqlite has an official extension to make sqlite "asynchronous" but really it just runs the database writes on a separate thread. Which is the better one to use?

#

And thanks for your help Pilot

torn sphinx
#

can anyone help me with postgresql

#

im kinda having trouble with it not recognizing it in path

#

nvm windows doing weird stuff

indigo smelt
#

what is the best database to use

#

for py

inland stone
#

depends on what you want to do with it

hollow bear
#

Store data in it

torn sphinx
#

im using postgreSQL for a multipurpose discord bot, what do you recommend: a big table with everything in it, or small tables for each section of the bot like economy, level system, inventory

#

it would be kinda weird to just create the data for the user with its id in every single table

torn sphinx
#

also is there some sort of documentation to use for postgreSQL with python

#

i dont seem to find any

#

and ive been searching for a while now

smoky pendant
#

usually u would like to have a table for each class

weak stirrup
#

Does anyone have any suggestions for how to model an "availability schedule" for a user, similar to a square site booking calendar. Listing time slots of different lengths for each day, available/booked, , and storing them efficiently for each user? Eventually I plan to fetch/set this info over a Djano REST API i am creating but have been stuck on it a couple weeks now

torn sphinx
#

what library should i use to connect to a sql database?

#

@torn sphinx for discord, aiosqlite

#

im already using postgresql

#

which is good ig

#

it also has this async thing support with another library

#

but for now i want to connect to it

#

o nvm asyncpg does that

#

cool

weak tinsel
#

yes, use asyncpg

torn sphinx
stuck wraith
#

hi can someone help me?

#

with a batch script

narrow saffron
#

!past

#

!paste

delicate fieldBOT
#

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.

delicate fieldBOT
slender isle
#

whats wrong this script

coarse ether
#

your db name is dbname in mongodb?

#

just make sure to add the proper dbname

#

and it might work

pearl adder
#

hey

#

can anyone tell me why im getting an error when running this

# 
mycursor.execute("CREATE TABLE LoginTbl (UserID int IDENTITY(1,1)PRIMARY KEY, Username VARCHAR(20), Pword VARCHAR(20))")
slender isle
#

@coarse ether thanks i will test

coarse ether
#

@slender isle is it working

slender isle
#

1 minute

slender isle
coarse ether
#

if you go to atlas

#

and then click on collections

#

and add a db as dbname

slender isle
#

Let i look

#

@coarse ether its like this

#

@coarse ether what should i do now

#

error is this

coarse ether
#

in mongoclient change dbname to socialmedia

slender isle
#

but where is mongoclient

#

in google

#

or in code

#

i will chance

#

?

#

@coarse ether

drowsy edge
#

Heyo how do u guys know where a website is taking its data from, like for example covid19.who

coarse ether
#

@slender isle send code via text

proven arrow
coarse ether
#

sqlite

proven arrow
#

Sqlite doesnt have identity for auto increment

#

They have the wrong syntax if they are using sqlite

thorn canyon
#

DOes anyone know why I getting this error? k_watafak

    conn.execute(f'''INSERT INTO prefixes (id, prefix) VALUES ({ctx.guild.id}, "{prefix}")''')
sqlite3.IntegrityError: UNIQUE constraint failed: prefixes.id```
#

oh

#

understand

#

so i need INSERT INTO prefixes (prefix) VALUES ("{prefix}") WHERE id == {ctx.guild.id}?

#

it's always in the table

proven arrow
thorn canyon
#

k_watafak what?

#

... okay

slender isle
delicate fieldBOT
#

Hey @karmic parcel!

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

slender isle
# 
from datetime import datetime
from termcolor import colored

cluster = MongoClient("mongodb+srv:Yusuf_Mert:<Murat1299>@socialmedia.knkvp.mongodb.net/<dbname>?retryWrites=true&w=majority")

db = cluster["socialmedia"]["messaging"]
all = db.find({})
date = datetime.now().strftime("%x")

for messages in all:
    try:
        if date != messages["date"]:
            print(colored(f"Today - {messages['time']}", 'red'))
        else:
            print(colored(f"{messages['date']} - {messages['time']}", 'red'))
        print (colored("From: ",'green'), messages['id'])
        print(colored("Message: ", 'green'), messages['message'])
        print("--------------------")
    except:
        pass

person = input("Name: ")
message = input("Message: ")

time = datetime.now().strftime("%X")
msg = {"id" : person, "message":message, "date":date, "time":time}
db.insert_one(msg)```
#

@coarse ether

#

whats the problem

torn sphinx
slender isle
karmic parcel
slender isle
torn sphinx
#

so problem is in how you connect to database

slender isle
#

@torn sphinx ab türk müsün

torn sphinx
#

this line you need fix

slender isle
torn sphinx
#

dirilis osman hayranı

#

wait let me check how it can be done

coarse ether
# 
from datetime import datetime
from termcolor import colored

cluster = MongoClient("mongodb+srv:Yusuf_Mert:<Murat1299>@socialmedia.knkvp.mongodb.net/socialmedia?retryWrites=true&w=majority")

db = cluster["socialmedia"]["messaging"]
all = db.find({})
date = datetime.now().strftime("%x")

for messages in all:
    try:
        if date != messages["date"]:
            print(colored(f"Today - {messages['time']}", 'red'))
        else:
            print(colored(f"{messages['date']} - {messages['time']}", 'red'))
        print (colored("From: ",'green'), messages['id'])
        print(colored("Message: ", 'green'), messages['message'])
        print("--------------------")
    except:
        pass

person = input("Name: ")
message = input("Message: ")

time = datetime.now().strftime("%X")
msg = {"id" : person, "message":message, "date":date, "time":time}
db.insert_one(msg)```
#

replace this with your code

slender isle
#

Ok

coarse ether
#

in this line mongodb+srv:Yusuf_Mert:<Murat1299>@socialmedia.knkvp.mongodb.net/<dbname>?retryWrites=true&w=majority you had to edit this to this mongodb+srv:Yusuf_Mert:<Murat1299>@socialmedia.knkvp.mongodb.net/socialmedia?retryWrites=true&w=majority

#

see <dbname>

#

@slender isle did it work?

karmic parcel
torn sphinx
#

@karmic parcel can you even use mongo with django?

#

your error is trying to do sql statement which not work with mongo

tranquil totem
#

how do I connect my pymongo code to motor?

#

what do I have to import and what do I have to type and what do I have to connect?

karmic parcel
shell ocean
#

but why do you want to use NoSQL with Django?

karmic parcel
slender isle
#

i look at this

#

@coarse ether

#

but

#

can you write this codes and send me

#

because i have error

#

and it cant fix

#

@coarse ether

#

@torn sphinx

#

can you help me

#

@torn sphinx can you send me your discord server

coarse ether
#

@slender isle replace that line with this mongodb+srv://Yusuf_Mert:<Murat1299>@socialmedia.knkvp.mongodb.net/socialmedia?retryWrites=true&w=majority

#

and it will work

#

just noticed now that you had missed mongodb+srv://

coarse ether
#

first of all install dnspython

#

and add the above line

#

for me it worked

#

you need the package dnspython and the problem is with this mongodb+srv:Yusuf_Mert:<Murat1299>@socialmedia.knkvp.mongodb.net/socialmedia?retryWrites=true&w=majority edit this to mongodb+srv://Yusuf_Mert:<Murat1299>@socialmedia.knkvp.mongodb.net/socialmedia?retryWrites=true&w=majority

coarse ether
#

and it did work

raven trail
#

Heya! Anyone on right now have experience with setting up Motor for use with MongoDB?

indigo smelt
# 
db = await aiosqlite.connect(...)
cursor = await db.execute('SELECT * FROM some_table')
row = await cursor.fetchone()
rows = await cursor.fetchall()
await cursor.close()
await db.close()``` I wrote this, following an article. Can someone explain what it does?
coarse ether
#

it connects to a db

#

then the cursor executes some query

raven trail
#

it defines all your connection parameters to an asynchronous db

indigo smelt
#

What does the ... do in the ()

coarse ether
#

that is your connection url

indigo smelt
#

so i need to change that?

coarse ether
#

it is just that if you are connecting to mongodb atlas then your username and password will be seen

#

and hence the code is written with ...

#

yes you need to change that

#

which db are you using

indigo smelt
#

where do i get my connection url

indigo smelt
coarse ether
#

replace it with "file.db" and create a file.db

burnt turret
indigo smelt
coarse ether
#

yes

indigo smelt
#

ok

raven trail
#

I'm trying to set up the connection parameters in a cog but I'm not sure about exact syntax to convert to a self.variable that I can access

indigo smelt
#

db = await aiosqlite.connect('file.db') So what i have now is this

coarse ether
#

and create the file file.db

raven trail
#

unless I just do something like self.db = connectioninfo

indigo smelt
#

on the pc?

coarse ether
#

just run the code it might only create

indigo smelt
#

alr

coarse ether
#

if any errors send the image here.

indigo smelt
# 
db = await aiosqlite.connect('file.db')
cursor = await db.execute('SELECT * FROM some_table')
row = await cursor.fetchone()
rows = await cursor.fetchall()
await cursor.close()
await db.close()``` I  got an error: `await` outside function, but the article never said to put it in a function
burnt turret
coarse ether
#

loaddb() async {
db = await aiosqlite.connect('file.db')
cursor = await db.execute('SELECT * FROM some_table')
row = await cursor.fetchone()
rows = await cursor.fetchall()
await cursor.close()
await db.close()
}
loaddb()

#

run this

indigo smelt
#

ok

coarse ether
#

python right

indigo smelt
#

yes

burnt turret
coarse ether
#

no wait

#

I gave js syntax

indigo smelt
#

loaddb() async { Invalid syntax this line, at the async

indigo smelt
#

lol

coarse ether
#

yes sorry bout that js syntax

# 
    db = await aiosqlite.connect('file.db')
    cursor = await db.execute('SELECT * FROM some_table')
    row = await cursor.fetchone()
    rows = await cursor.fetchall()
    await cursor.close()
    await db.close()
await load()
indigo smelt
#

alr thx lemme try

coarse ether
#

make sure of formatting

raven trail
#

so I define it similar to how I did word interval here:

client = commands.Bot(command_prefix='?')
client.word_interval = 7

just with a database or something variable?

burnt turret
burnt turret
indigo smelt
#

yeah i just got error imma put await

coarse ether
raven trail
#

okay terrific 🙂 thank you

#

I will bumble around with it, much appreciated anand!

indigo smelt
#

and now it says that an await is outside the function

coarse ether
#

try with the updated code

#

I edited it thanks to @burnt turret

burnt turret
#

yeah you can't await outside an async function though

indigo smelt
#

still await outside the function

burnt turret
#

this has become a basic #async-and-concurrency question now more than dbs, but i think you have to do loop.run_until_complete on load

indigo smelt
#

that doesnt look right..

#

@burnt turret ello?

#

hello*

coarse ether
#

loop.run_until_complete(load())

#

maybe it is this

indigo smelt
#

ok

coarse ether
#

or you can use asyncio.run(load())

indigo smelt
#

It says loop is not defined

coarse ether
#

use asyncio.run(load())

indigo smelt
#

ok

#

asyncio is not defined

burnt turret
#

my bad i'm back

#

what is this in?

#

is this for a discord bot?

indigo smelt
#

Yes

burnt turret
#

you'd need bot.loop.run_until_complete(load) then

indigo smelt
#

alr

burnt turret
#

assuming your instance of commands.Bot is called bot

indigo smelt
indigo smelt
burnt turret
#

🤔 i'm not really sure anymore, move to #discord-bots and show the code, this has gone off-topic for this channel

indigo smelt
#

alr

coarse ether
indigo smelt
#

ok

coarse ether
# 
loop=asyncio.get_event_loop()
loop.run_until_complete(load())``` if this still does not work move to [#discord-bots](/guild/267624335836053506/channel/343944376055103488/)
burnt turret
#

i mean, get_event_loop will be getting the same loop as bot.loop, the difference is the load() instead of load - my bad there

indigo smelt
coarse ether
#

bot.loop.run_until_complete(load())

#

just edit what @burnt turret told to this

indigo smelt
#

sqlite3.OperationalError: no such table: some_table Alr new error

coarse ether
#

now it

#

is working

burnt turret
#

make the table

raven trail
#

Anand if you have a moment - what is the best practice for setting DB connections and making calls if I plan to have numerous collections each taking different data?

coarse ether
#

the problem is you should create a table in the db

indigo smelt
coarse ether
#

table

#

cursor = await db.execute('SELECT * FROM some_table')
error comes here

#

some_table is not defined

#

that is it

indigo smelt
#

ok

coarse ether
#

want me to give a sample table

#

db

#

I can quickly create one via django

#

and send the file to you

indigo smelt
# 
table = newTable()``` This is how you create a table if I am not mistaken?
coarse ether
#

wait

indigo smelt
#

yes

coarse ether
#

cursor = await db.execute('CREATE TABLE IF NOT EXISTS some_table')

#

edit the line to this

indigo smelt
#

ok

coarse ether
#

that way it will create the table

indigo smelt
#

sqlite3.OperationalError: incomplete input Error

#

0-0

coarse ether
#

now edit the line to ```cursor = await db.execute('SELECT * FROM some_table')

indigo smelt
#

alr

#

It says there is no table

#

sqlite3.OperationalError: no such table: some_table

coarse ether
#

is file.db present

indigo smelt
#

shouldn't the script create it?

coarse ether
#

yes did it create it

indigo smelt
#

i don't know how can i check

coarse ether
#

which editor are you using??

indigo smelt
#

an IDLE

coarse ether
#

send your project directory image from files.

indigo smelt
coarse ether
#

not that

#

the project dir

#

content

burnt turret
indigo smelt
#

what i am confused lmao

#

do you want me to show you the editor?

indigo smelt
burnt turret
#

exactly that lmao you need to specify the columns as well

#

in that statement you're only telling to create a table but not telling what the columns of the table are

indigo smelt
#

so what columns do i need to add 🤔

burnt turret
indigo smelt
#

but its aiosqlite not sql

#

😳

burnt turret
#

aiosqlite is a database driver module, which uses sqlite as a database. sqlite and other relational databases uses sql as the query language

#

so, you need to know sql, if you want to use sqlite - and you're using sqlite, through aiosqlite

indigo smelt
#

ok

raven trail
#

@burnt turret Are there any videos etc that would be useful in understanding better the workflow for DB interactions? Relatively new to it, I've done some DB work but mostly with Django where Django did most of the work

#

I have questions but I don't want to barrage you with simple questions for answers a simple video could provide

burnt turret
#

is this specific to mongodb?

#

actually whatever db it is i wouldn't know about any good videos for it - i was taught mysql and mongodb at school, so i haven't really learnt much from other sources than school and documentation for these

burnt turret
raven trail
#

Well, I want to create a series of methods to connect/access DB but without experience/knowledge of how/why I would want to do certain things I'm finding it hard to think ahead, I've got my entire script neatly compartmentalized into classes/cogs, I want to create just a few basic methods to access DB and then call those methods depending on what I need without having to rewrite the same lines of code every time I call DB lol

#

I feel like a bumbling idiot haha

burnt turret
#

🤔 let me think if i've seen anything good for mongodb

#

there's probably a freecodecamp video on it, they're generally good and explain in detail

burnt turret
raven trail
#

ORM?

#

I pulled the freecodecamp video as you suggested 🙂

burnt turret
# raven trail ORM?

stands for an Object Relational Mapping, it's what you're familiar with in django

#

using classes to represent the database tables

raven trail
#

will that work with Motor?

#

oh I was already planning on using Motor

#

convenient that that is the second you had suggested haha

burnt turret
#

oh motor isn't the same as motor engine

#

motor engine will be similar to what you were used to doing in django (with the use of classes) i assume, while motor is closer to actual mongodb syntax

raven trail
#

So I can remove the regular motor code I had written and uninstall the package in favor of this one you think?

burnt turret
#

i wouldn't vouch that hard for it haha i've never used it - i was just presenting you with the options, especially because you aim to try and create something like that anyways (even though on a smaller scale)

raven trail
#

well I mean, the amount of code I had implemented is not exactly a dealbreaker haha, I had like 15 lines tops so far

#

:p

#

I'll give it a try, appreciate the advice Anand!

#

It seems like MotorEngine uses Tornado, I saw that when I was reading the Motor docs that there was a distinction between that and asyncio so I'm not sure here, let me glance back at it

burnt turret
torn sphinx
#

@burnt turret heeeelllppp

burnt turret
#

ask your question, someone will answer if they can

slender isle
#

@coarse ether

coarse ether
# 
from datetime import datetime
from termcolor import colored

cluster = MongoClient("<dbname>")

db = cluster["socialmedia"]["messaging"]
all = db.find({})
date = datetime.now().strftime("%x")

for messages in all:
    try:
        if date != messages["date"]:
            print(colored(f"Today - {messages['time']}", 'red'))
        else:
            print(colored(f"{messages['date']} - {messages['time']}", 'red'))
        print (colored("From: ",'green'), messages['id'])
        print(colored("Message: ", 'green'), messages['message'])
        print("--------------------")
    except:
        pass

person = input("Name: ")
message = input("Message: ")

time = datetime.now().strftime("%X")
msg = {"id" : person, "message":message, "date":date, "time":time}
db.insert_one(msg)```
slender isle
#

Thank you

coarse ether
#

it was just the same name wait I will send the code for your db

#

mongodb+srv://Yusuf_Mert:<Murat1299>@socialmedia.knkvp.mongodb.net/socialmedia?retryWrites=true&w=majority add this to <dbname>

#

it must work

slender isle
#

Can you test is it works with my db

coarse ether
#

k wait

#

running

tiny needle
#

if anyone can help in #help-kiwi it would be appreciated

polar sandal
#

My mind is in a pretzel right now. I am trying to create a many to many relational database. I created the two separate tables but now need to create a joining table. Do I add information to that table the same way I would any other normal table?

raven trail
#

Anyone with experience in setting up/using μMongo / MotorEngine for Async MongoDB connection in dpy? I'm trying to set it up and am kind of overwhelmed, hoping to find someone to bounce occasional questions off in implementation

jolly trench
# 
import os
import sqlite3

conn = sqlite3.connect(os.path.abspath("source/data/py-deposit.db"))
db = sqlite3.Cursor(conn)

sqlite3.OperationalError: unable to open database file
can anyone help me? im trying to connect to sqlite database but it will always say that the path is invalid, the only path I’m able to use is the relative path, but I want to be able to execute the script from the parent directory

proven arrow
#

@polar sandal Yes you would add in the same way. Although to make the relationship you only need to add data to the joining table. Assuming the values you reference, exist in the other tables.

tepid cradle
#

@jolly trench you need to use ../ to refer to parent directory.
conn = sqlite3.connect(os.path.abspath("../py-deposit.db")) should work

jolly trench
hot sierra
#

is there a way to enter a set of values into a table only once ?

polar sandal
proven arrow
hot sierra
#

okay it worked thanks

proven arrow
raven trail
pure mortar
#

best way to master SQL? anyone have any good resources?

proven arrow
pure mortar
#

beginner lol

#

oh ok

#

everyone always says its an experience thing

proven arrow
#

Well yeah because you can learn SQL, but the advanced queries or scenarios will only come when you are faced with certain problems, which you may not come across whilst going through tutorials.

pure mortar
#

sounds just like coding

proven arrow
pure mortar
#

inb4 leetcode for SQL

#

jk

proven arrow
pure mortar
#

thanks

#

ill try to work through these

sudden phoenix
#

Hi all I got a strange thing with sqlalchemy, If someone got an idea..

#

toimport = db.query(Csvfiles).filter(Csvfiles.parsed == False).all()

#

is translated to :

#

WHERE csvfiles.parsed = 0

#

this is the expected behaviour, but

#

toimport = db.query(Csvfiles).filter(Csvfiles.parsed is False).all()

#

is translated to :

#

WHERE 0 = 1

#

does someone knows why.. because the second sentence is more "pep8"..

#

I use a sqllite.

pearl adder
#

is anyone here good with mysql in python?

bitter ingot
#

@pearl adder working on an sqlite3 project right now, i could try and help

#

i also need help, i have some tables, but i need to store per-guild per-user data. how would i do this in a database?

📎 unknown.png
#

i am storing pictures that relate to a user, so i need to be able to store like 100+ pictures per-user

pearl adder
bitter ingot
#

for that i'm not sure, currently i'm just making a high-level system to easier edit my database with functions n classes

lyric junco
#

how can I delete data from sqlite after amount of time from saving it?

bitter ingot
lyric junco
# bitter ingot 1) save data 2) save timestamp of when data was saved 3) check every now and the...

I did all that steps but idrk how it was supposed to delete after specified time

    @tasks.loop(hours=24)
    async def check_for_membership(self):
        db = sqlite3.connect('./cogs/database/premium.sqlite')
        cursor = db.cursor()

        cursor.execute(f"SELECT * FROM premium")

        current_date = datetime.now()
        for row in cursor:
            member = self.client.fetch_user(int(row[0]))
            member_id = row[1]
            date = datetime.strptime(row[2], '%Y-%m-%d %H:%M:%S.%f')
            if current_date.date() - date.date():
                print(f"Premium Ended for {member}")
                cursor.execute(f"DELETE FROM premium WHERE User_IDs='{member_id}'")

        db.commit()

I want it to auto delete any data after 30 days but Idk how or where to put "30"

bitter ingot
#

get the unix timestamp

#

and then get 30 * 86400

#

!e
print(30 * 86400)

delicate fieldBOT
#

You are not allowed to use that command here. Please use the #bot-commands channel instead.

bitter ingot
#

ok bot

lyric junco
#

uhm Idrk how (I'm kinda new to db)😅

#

can you edit my code if it's not bothering you?

bitter ingot
#

i can't, i'm just about to leave

#

but use google

lyric junco
#

oh ok

#

ok thx

bitter ingot
#

how to get unix time
then
just check if the current unix time is whatever 30 * 86400 is above the old one

lyric junco
#

thx

torn sphinx
#

where's your query

#

it is returning the correct document, yes?

#

just it's giving you all of the fields and you only want some of them?

#

So you need to specify what fields you want specifically using a projection

# 
inventorydoc = settings.col.find({"serverid":message.guild.id,"playerdata.userid":message.author.id}, {'playerdata': 1})
#

try that

#

it should return the playerdata field, and its subfields

#

yes

torn sphinx
#

just started learning postgres super happy about it

sleek halo
#

hi

#

i need help please

drowsy edge
#

hey guys im trying to get an xlsx file using an online link

#

and heres the code `import requests
import xlrd

url = 'https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&cad=rja&uact=8&ved=2ahUKEwj_9_fVwb7uAhXTNcAKHXkXAtQQFjAAegQIARAC&url=https%3A%2F%2Fwww.rki.de%2FDE%2FContent%2FInfAZ%2FN%2FNeuartiges_Coronavirus%2FDaten%2FFallzahlen_Kum_Tab.xlsx%3F__blob%3DpublicationFile&usg=AOvVaw356d2ETTlb6cb1E4GaZI0c'
headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:84.0) Gecko/20100101 Firefox/84.0'}

page = requests.get(url, headers=headers)
workbook = xlrd.open_workbook(file_contents=page.content)
worksheet = workbook.sheet_by_index(0)`

#

it raises the error Unsupported format, or corrupt file: Expected BOF record; found b'<html la'

#

how can i fix that?

modern parcel
#

guys how do i script a database diagram in sql server management studio with the datatypes

restive crater
#

I am making a python app with google cloud MySQL. It all worked fine, but for some reason at some point it just stopped working and gave me this error:

mysql.connector.errors.DatabaseError: 2003 (HY000): Can't connect to MySQL server on 'IP' (10060)

When I try to connect to it from the cloud shell on https://console.cloud.google.com/, it works, so I think it's because google cloud SQL is blocking connections from my IP. But after I allowed connections from my IP and 0.0.0.0/0, it still gave me the same error.

Any help will be appreciated.

Google Cloud Platform

Google Cloud Platform lets you build, deploy, and scale applications, websites, and services on the same infrastructure as Google.

burnt turret
#

have you tried using their cloud SQL proxy to connect?

unique dove
#

Anyone familiar with MongoDB (and PyMongo)?
I need to insert a list of documents into a collection.
I am using: ins = database.collection_name.insert_many(json.loads(json_util.dumps(pythonListOfDictionaries)), ordered=False)

However, some document's IDs will already be present into DB, in such case, it should update the already-present documents.
Therefore, it seems that that update_many with upsert=True is the correct function
But update_many asks for a "filter" in the parameter, I don't need any filter, just insert the documents and update if any doc is already there
Not sure how to use update_many
Anyone can suggest the way?

restive pilot
#

@unique dove How can Mongo know if it should insert or update if it does not know if it exist or not? Because, when you insert a entry to MongoDB, duplicates are allowed per default. There are no unique indexes except _id which is generated by default (for mongodb to keep track of stuff..)
So, you need to pass a filter= on a field to check if it should update or insert the entry.

unique dove
#

@restive pilot I didn't mention it, but every entry in the list of dictionaries has a "_id"

#

I am currently using insert_many and try/except/pass block, so that if a doc exists, it doesn't insert it, if it doesn't exist, it inserts it. The try/except/pass block under which insert_many is, is to avoid the python script throwing an exception in case it finds duplicates.
This works, but now I want to update the code so that if some docs in the list already exist (_id), it should just update them, if not, just add them

#

update_many works differently (for example upsert starts coming into play, and the required "filter" also)

restive pilot
unique dove
#

@restive pilot Wow thanks, so much new (to me) stuff there

restive pilot
#

So, the principle is that you need to filter on something for the database to understand what you want to update

unique dove
#

How efficient is this on big collections compared to my current method (that works only partly, doesn't update, insert_many with unique indexes to avoid duplicates and try/except/pass)

restive pilot
#

bulk_write is more efficient as all the operations will be sent as one command for the server to process instead of many individuals.

unique dove
#

I see

restive pilot
#

One nice thing with bulk_write is that you mix replaceone, updateone and so forth in the same process 😄
But really, shouldn't be that much of a difference compared to updatemany

unique dove
#

Regarding line 27: {"_id": book["_id"]}
I am using a custom index ("IDD")
If I create the index in mongodb, can I just replace that with {"IDD": book["IDD"]} and expect the same performance as with _id ?

restive pilot
#

Yes! As mentioned _id is the unique field in MongoDB, I'm using that as a filter key 🙂
You probably want to indexIDD in your DB to make sure things go blazing fast

unique dove
#

Regarding "But really, shouldn't be that much of a difference compared to updatemany"

Not sure I understand this part, does it mean that it could have been also done with update_many?

restive pilot
#

Yes, but then you are limted to update operations only (and insert if it does not exist if you pass upsert=true)

With bulk_write you can mix and match all the operations

nocturne yew
#

i need a help

unique dove
#

Thanks @restive pilot !

restive pilot
#

Np! 🙂

restive crater
drowsy edge
#

hey guys so i have a downloadable link right here that when u click on it it automatically download the .xls file. How can i open and save this file somewhere with python?

📎 Screenshot_2021-01-28_123004.png
kindred crow
restive crater
kindred crow
restive crater
#

Make sure python is in path @kindred crow

kindred crow
brazen charm
#

you mean why is it asking for a password

heady hatch
#

I am having difficulty understanding how to set the following up.

and even a harder time trying to explain what I'm trying to do.

long story short, imagine a table called desk and several tables each representing a device - phone, fax, printer, laptop.

In python you can have a list called devices and reference the class object item to include all three different types of those devices.

In sql, I'm having a hard time understanding how to reference multiple tables to one table called devices.

Could you point me in the right direction on how to achieve this?

kindred crow
heady hatch
#

the image is just a visualization of the flow not a literal setup

kindred crow
# 
db=c.connect(host='localhost',user='root',database='abc',passwd='//pass//')
mc=db.cursor()
mc.execute('show tables')
for i in mc:
    print(i)```
#

im trying to run this-

#

but

#

why is it not working?

near stag
#

Dunno if this is the BEST place for this question, but would it be a better idea to use dictionaries/lists, or an XML file for handling storage of multiple-user's input (all input comes from discord)

torn sphinx
#

how do i output just one field from mongodb document?
Like, if i have multiple fields in a document, how do i output just one single chosen field?

torn sphinx
#

Or how do i find the number of elements when i go through Cursor object with for

restive pilot
#

Selecting is passing a second object to the find .find() method with fields you want.
E.g .find({},{title:1})

torn sphinx
#

Le' me try

restive pilot
#

You can add .count after .find() to get the number of documents returned by the query

proven arrow
heady hatch
#

yes. I suppose that is what I'm trying to do.

proven arrow
#

You can achieve that through a foreign key column.

#

Which is basically a column you can add in your table so that it points to another row in another table, creating a link.

pine geode
#

I'm using SQLAlchemy. I have a config file where Base, engine, and session are defined. In my models file, i import Base and session from the config file. I use the imported session to make class methods like this:

@classmethod
    def find_by_name(cls, search):
        return session.query(cls).filter(cls.name == search).first()```

In my unittest file, I want to use a different database (i.e. sqlite in memory), and make a session based on that. I'm not able to figure out how to make one session for regular use, and a different one for testing, and then to import the correct one into my models file.
#

I could add session as an argument to the class method, but I feel like there should be a better way.

torn sphinx
#

Im using asyncpg with my pgSQL db, should i .close connection each time i have finished adding, deleting or updating the data in the tables? or just leave it active to be re used later on?

burnt turret
wind siren
#

Hey all

#

I wanted to learn how to use database ?

#

How can I do it?

#

Which what databse I should begin?

indigo flare
#

depends, what type of data do you want to store?

true kelp
#

how can i delete rowid via varible eg. ```python
delete = input("type your number to delete")

#

and use the delete to delete the data

#

my code ```python
delete = input("type the number you want to delete? \n\n")
c.execute("DELETE FROM password WHERE rowid='(delete)';")
print("successfully deleted ",delete)

#

anybody got a solution

#

i use sql

#

ok 😢

#

its been 35m

burnt turret
#

what database is this?

true kelp
#

sq lite

#

Structured Query Language

#

google says

#

idk

burnt turret
#

so what is happening now is that you're trying to delete a row where the rowid is literally the string delete

#

but you want it to have the value of the variable delete,

true kelp
#

how do i do that

burnt turret
#

basically you are supposed to keep a placeholder value there, and the delete is passed as a separate argument

true kelp
#

i have tried to do: ```py
delete = input("type the number you want to delete? \n\n")
c.execute("DELETE FROM password WHERE rowid='(?)';",(delete))
print("successfully deleted ",delete)

burnt turret
#

i'm not sure what the placeholder is for sqlite but it might be something like

c.execute("DELETE FROM password WHERE rowid=?;", (delete,))
burnt turret
#

moreover what went wrong there is that doing (delete) doesn't make it a tuple

#

but the execute function wants it's second parameter to be a tuple - so to make a single element tuple we write (delete,)

#

!e

a = (10)
b = (10,)
print(type(a), type(b))
delicate fieldBOT
#

You are not allowed to use that command here. Please use the #bot-commands channel instead.

burnt turret
#

i always forget, but that was supposed to demonstrate that the first one without the comma wouldn't be a tuple but the second one would be

true kelp
#

ok

#

@burnt turret it "works" (doesnt crash) but the nothing is deleted

burnt turret
#

i'm guessing the rowid column type in your database is an integer

#

input() function gives you a string

true kelp
#

cant i just add int(input())

burnt turret
#

yes that's what you have to do

true kelp
#

here comes the problem with that ``` c.execute("DELETE FROM password WHERE rowid=?;",(delete))
ValueError: parameters are of unsupported type

burnt turret
true kelp
#

oh yeah fogot to edit the code

astral gorge
#

I'm using psql, how can I define an autoincrement non-unique id?

burnt turret
#

non-unique?

burnt turret
true kelp
#

im going to bed il do this in the morning but it still does not delete any data

#

gn

astral gorge
#

yes, I tried this, but doesn't work @burnt turret

CREATE TABLE IF NOT EXISTS report(
    server_id integer,
      report_id serial primary key (server_id, report_id);
#

so report_id would have a different autoincrement value for each server

#

and start from 0 for each server

burnt turret
#

🤔 i'm not very clear on what you're trying to do, you want to start the report_id from 0 for each server?

astral gorge
#

yes, and then increment it by 1 independently, for each submitted report for specific servers

burnt turret
#

i don't really know how you'd do that, maybe look into GENERATED columns or something
also, i don't see how report_id could be a primary key in that case as the column could have multiple rows of the same value

astral gorge
#

yeah true

#

I can just create another table, like report_ids

proven arrow
torn sphinx
#

Still beginner, how can I add data to an database?

#

And how can I print them out?

burnt turret
#

what database?

torn sphinx
#

sqlite @burnt turret

burnt turret
#

you use INSERT to add data to a database, while you use SELECTs to get data from it

narrow mist
torn sphinx
#

i have two tables, each table has a category column.
Both tables is storing information on products at a specific location store. How i can get all different categories from these tables and remove duplicates?

#

so result should give me only categories where there is a product for it in a store

proven arrow
#

Get categories of both tables then use UNION operator to combine results

torn sphinx
#

didnt know about connection pools, i guess i can just add connections in every file i have thats part of the discord bot

burnt turret
#

after which itll be accessible in all your cogs as well

torn sphinx
burnt turret
#

why would i make that up haha

torn sphinx
#

i mean i never heard of that

#

and i just searched that

#

and there is nothing

burnt turret
#

Need to keep track of a variable between functions? No problem!

⚠️ Careful what you name it though, else you might overwrite something ⚠️

Just add it to your commands.Bot or discord.Client instance like so:

bot = commands.Bot(...)
bot.my_variable = 0

async def foo():
    bot.my_variable += 1

# In a cog
@commands.command()
async def counter(self,ctx):
    await ctx.send("Current Counter is at {}".format(ctx.bot.my_variable))

This also allows you to access this from other cogs/extensions/functions. Anywhere you have access to the bot instance

#

i'm not sure where it is in the documentation

torn sphinx
#

is it as simple as that?

burnt turret
#

yes

torn sphinx
#

wow

#

so just (name of the bot).variable

#

cool

#

thanks man

burnt turret
#

yep

#

in cogs that's self.bot.<variable>

torn sphinx
#

that will be really useful

thorn canyon
#

how in sqlite3 names the dict or list type?

hard canyon
#

How can I use derby database in python?

torn sphinx
#

userlist = users_col.find({"_id":{"$regex":f"^{myvar}"}}).limit(10).where()
can you help me set up $where, I want to get users who has value x in their inventory (inventory is an array of dicts)

#

@burnt turret 🥺👉👈

delicate fieldBOT
#

:incoming_envelope: :ok_hand: applied mute to @torn sphinx until 2021-01-29 19:16 (9 minutes and 59 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

torn sphinx
burnt turret
#

🤔 can you show a general inventory?

torn sphinx
#

I just did that now testing it rn

burnt turret
#

i'm about to head to bed rn haha i'll try my best to help

torn sphinx
#

thx SCWblushHEART

burnt turret
#

i haven't used $where much, so i could try thinking of some alternate way

#

documentation seems to say you could be giving it javascript code too to match which looks pretty useful but i don't really know well how to use it

restive pilot
burnt turret
#

i'm not the one who asked the question here

restive pilot
#

My bad

#

@torn sphinx see ^

torn sphinx
#

why is redis cool

#

apart from caching

restive pilot
#

it's a really fast key-value store?

torn sphinx
#

I know what . does like user.inventory

#

but what does .$ do

#

like user.$.inventory

#

OperationFailure: unknown operator: inventory, full error: {'operationTime': Timestamp(1611949079, 1), 'ok': 0.0, 'errmsg': 'unknown operator: inventory', 'code': 2, 'codeName': 'BadValue', '$clusterTime': {'clusterTime': Timestamp(1611949079, 1), 'signature':}}

#

why does this raise this error

fading breach
#

how do I count how many documents contain a query with motor(mongodb)

restive pilot
torn sphinx
#

I have this query: users_col.find({"_id":{"$regex":f"^{myvar}"}}).limit(10).sort()
I want to sort with a dict key value (descending)
an example inventory:

inv = [
{"name:":"example", "amount":5},
{"name:":"example2", "amount":2},
{"name:":"example3", "amount":0} # these dicts are examples of an item object
]

I want to sort by amount of "example" item
what should I do

summer citrus
#

I've been using sqlite3 for a while now since my discord.py bots have only ever needed to store text basically, but now I'm wanting to store datetime and I was wondering what the best option would be for a simple python db to use?

#

I know that sqlite can store datetime as a text type, but I thought I should maybe try something different that actually supports datetime

inland stone
#

it's always good to do new stuff, but I do wonder why you would need the db to support that, surely you can use it as a string using strXtime etc?

summer citrus
#

Yeah, I just thought I should maybe learn to use a db that supports the data type datetime, but I could also just store it as a string
I'll try the string way first and see how it goes, just seemed a bit messy changing it to text and back

inland stone
#

it can be not messy if you have a function that does all the ugly stuff in the background when you call it. But yeah I get what you mean, even that code itself would be a bit messy. I am also a noob, I first set up my project on mysql, and now I rebuilt it with mongodb

summer citrus
#

yeah I was thinking of just having a method somewhere to do that for me haha, thanks

#

Also, I often open my connection at the start of my file, but should I only be starting a connection when a specific method needs it?
I have multiple files that will use the database, and each of them starts a connection with a cursor and I wondered if it was bad practice

inland stone
#

i think the best practice is to use 'with' for such connections

#

you familiar?

summer citrus
#

I've used 'with' for opening json files, usually something like:

with open("my_file.json", 'r') as f:
    token = json.load(f)['TEST']
#

I'm guessing you would just open a connection at the start of each method?

inland stone
#

so i think here you would use something like

    with conn.cursor() as cur:
        cur.execute(query)
        fetch = cur.fetchall()
        print(fetch)
        return fetch

this is the code I used for mysql

#

tbh not sure how exactly this would work in sqlite

summer citrus
#

in sqlite I usually open in at the start of the file like

conn = sqlite3.connect("evo_db.db")
c = conn.cursor()
#

then use c to execute SQL

inland stone
#

so yeah it would be the same I think

#

that is, your first line

#

conn =

#

and then with conn.corsur() as c:

summer citrus
#

so should I ever close my connection?

inland stone
#

that does the open and close for you on a per need basis

summer citrus
#

ohhh right

inland stone
#

as it does with files

summer citrus
#

I'll give it a try later on
It's just because some of my methods have timers, so they may have a connection open for 1 minute, and I was wondering if I would be able to have another method access the db while the other one was still connected

inland stone
#

that's a question indeed. what does google say about it?

summer citrus
#

Ohh that's really useful to know then, thanks!

solemn root
#

How can I remove data entries where I remove the older entry and keep the newer entry? I’m using autoincrement for ids.

inland stone
#

@solemn root try giving a bit more details

solemn root
#

I want to replace data in a table through variables. Ex. (Autoincremented id, user_cash, user_token) = (1, 45, 677344363535264534) and replace that with (1, myPurse, author.id), so the data consistently has 1 entry per user and not thousands of individual logs recording each interaction

#

I can’t figure out how to have the database remove the oldest entry if there are 2 entries by the same user, and to remove the oldest one. (Ultimately what I want to do)

#

Ideally something like this:

|1|45|55796434697575657|
|2|57|46975756575579643|
 •
 •
 •

Replace(...)

|1|76|55796434697575657|
|2|106|46975756575579643|
 •
 •
 •```
#

currently get either deleting very first data entry (w/o a second one to prompt deletion) or TypeError from Replace function

restive pilot
#

e.g

user_data = [{"id": 1, "cash": 1337, "token":1111111}]
user_data[0]["cash"] = 1338
print (user_data[0])

but it highly depends on ur database 😛 What type of database are you working with? E.g mongodb which is document based?

solemn root
#

sqlite3

restive pilot
#

Also, don't focus on removing and inserting, focus on updating by index instead

#

@solemn root
Looks like you need some introduction to DB since you don't understand the fact that you are supposed update an entry instead of deletion and insertion again
E.g some quick reading should do it: https://www.ibm.com/cloud/learn/relational-databases#toc-what-is-a--8q6isCrS

So Sqlite3 is a RDBMS, so things are stored in a table using rows and columns.
So, the for example the id column would hold a unique value that can't be changed, the next column user_cash (in ur example) is a integer (signed) that holds digits. so, whenever you want to update the user_cash column (change it) you first find the row that is unique for the user by the id column and then you get the data for from the row. When you have the data you change it.
Once you changed the data, you go again and update the database with the changes based where the row is equal to the id column

elfin steppe
#

Hi all, have any of you guys built a database from scratch?

#

I am looking for a course that could guide me through the whole process cause I am teaching myself database.

inland stone
#

I have. No course was needed...

#

well, for my basic model anyway

solemn root
#

@restive pilot How can I set user_cash = myPurse ? myPurse is another variable I have which controls user_cash, but I have to write my execute lines like this:

crsr.execute("""UPDATE mytable SET user_cash = myPurse WHERE (not sure what expression I need here to grab any unique id) """)

restive pilot
#

read what I wrote again. It's a simple thing. Have you used Excel before?

solemn root
#

yes

restive pilot
#

Excel is pretty much the same thing, to find a row to update you first find the column and then the row matching that value you want to update

#

but multiple rows can have the same value, but you know that id column is unique right

#

so id column can't have two values of the same, so use that column to find the data. once you have the data you probably have a dict{} which is a key-value pair. Update the value in that dict and then grab the id from id dict, and execute a UPDATE-statement WHERE ID = to the ID from the dict that you got earlier when you first grabbed the data

#

I mean, I could give you the answer, but then you wouldn't understand how a RDBMS work and once you need more complex statement, you would have issues again.
everyone starts somewhere! 🙂

jade mulch
#

why is python3.9 unable to locate mysql module, I have installed connector but it's showing notfounderror. However if I check from the terminal its ok but when I use pycharm it's not

#

pls help

hard canyon
#

same with me I've installed mysql server but at time of connecting my database in my python program it says bad handshake error. May be because of version difference. but in netbeans I'm using wamp server and that mysql database I'm trying to connect with my python program.

#

I've no clue what I'm doing!?

primal cave
#

Someone can help me with the mongodb database?

hasty juniper
#

hmmm

    bot.con = await asyncio.create_pool(database="Evelone", user="postgres", password="admin")
AttributeError: module 'asyncio' has no attribute 'create_pool'
# 
async def createDbPool():
    bot.con = await asyncio.create_pool(database="Evelone", user="postgres", password="admin")

bot.loop.run_until_complete(createDbPool())
bot.run("Token123")
burnt turret
#

you're thinking of asyncpg.create_pool

#

or whatever db driver module you're using, but not asyncio

hasty juniper
#

xd thanks

torn sphinx
#

@hasty juniper im sure u meant asyncpg

hasty juniper
#

ye

midnight cloak
#

Can anyone help me

#

Mongo db

fading breach
#

@midnight cloak sure

#

btw its called mongodb

#

not mangodb

midnight cloak
#

Ok

fading breach
#

mangoes are delicious though

#

lol

#

you should use motor

#

instead of pymongo

midnight cloak
#

I don't understand!

fading breach
#

motor is a driver for async and mongodb