#databases | Python | Page 83

maiden sonnet Mar 14, 2020, 1:03 PM

#

( @rich trout I got it working (not optimized for now but it works), indeed you hinted me in the right direction. All I needed to do actually was replace the line trivia["category"] = { "category": raw_category } in the controller with del trivia["category"] . That way Marshmallow does not try to immediately create the object for category, and later I can attach it normally)

#

Now, will try to clean it up 🙂

rich trout Mar 14, 2020, 1:05 PM

#

oh, nice

#

👍

pearl heath Mar 14, 2020, 1:09 PM

#

@maiden sonnet wait until you have to access dict of dicts of dicts lol

maiden sonnet Mar 14, 2020, 1:09 PM

#

ahaha, I hope by then I will handle it more robustly 😉

pearl heath Mar 14, 2020, 1:10 PM

#

lots of [0][0][0]

maiden sonnet Mar 14, 2020, 1:24 PM

#

I'd love to write more functional code in Python

#

I have worked more with JS lately where that seems a tad easier

valid turtle Mar 14, 2020, 2:04 PM

#

Hi, is there a kinda local DB. I know I clould start a MYSQL DB or something but is there a way to use a DB which start with my py script and quits with it and saves itself on exit

gleaming quest Mar 14, 2020, 2:05 PM

#

Try SQLite (sqlite3) or XAMPP program with local MySQL database

#

SQLite https://docs.python.org/2/library/sqlite3.html

#

XAMPP https://www.apachefriends.org/pl/index.html

XAMPP Installers and Downloads for Apache Friends

XAMPP is an easy to install Apache distribution containing MariaDB, PHP and Perl.

#

Did I help? 😃

coarse coral Mar 14, 2020, 3:40 PM

#

Hello! I am working on my first database, and I have a question. You add a product on the back-end panel. You input product name, price, description and digital codes you wish to wish to sell. This should then send this information to the database. When a user purchases a product from you, it should take digital code from the database, and update the record.

My question: Do I create 1 table with all this information ? and for the digital codes set the format as JSON so I could have multiple digital codes in the one row ? Here is an example of what I am thinking:
Tables Products:

p_name | p_desc | p_price | p_payment_method | d_codes
Fifa 12  etc       4.55     paypal            ['232', '34344', '343434']

So my thinking was when the user purchases on the front-end, it queries the database, takes the first code '232' and delivers that to the buyer, removes from database, so the codes left would be the last two ?

or am I thinking about this wrong ?

#

or should I do something like this:

Table Products
p_name | p_desc | p_payment | foreign_key

Table Codes
foreign_key | codes

charred fractal Mar 14, 2020, 3:46 PM

#

something like this:

Table Products
p_name | p_desc | p_payment | foreign_key

Table Codes
foreign_key | codes

#

Something like that @coarse coral

coarse coral Mar 14, 2020, 3:48 PM

#

Yeah I was leaning more to that approach. I have one last question - am I right in thinking I should only assign one code per row right ? would my db not fill up pretty quickly if I have hundreds of rows ?

charred fractal Mar 14, 2020, 3:49 PM

#

I can't answer that, but it could possibly.

coarse coral Mar 14, 2020, 3:49 PM

#

okay thanks, I will wait and see if anyone else has any other advise before proceeding

charred fractal Mar 14, 2020, 3:52 PM

#

Alright.

quick geyser Mar 15, 2020, 4:20 AM

#

hey so im making a username check for a database,
and no matter what, even if the username is stored in the database its immeditaly going to else statement

print("Please enter your username.")
    USERN = input("USERNAME: ")
    cur.execute(f"""SELECT USERNAME from users where USERNAME='{USERN}'""")
    myresult = cur.fetchone()
    print(myresult)
    wildcard = myresult
    if USERN == wildcard:
        print("yes")
    else:
        print("That username is not in the system.")

any ideas? please tag me when you respond, ill be away from the pc for a while

#

yes it logs into the database at the first part of the code

#

the codes output with my iunput

Do you already have an account? yes
Please enter your username.
USERNAME: bob
('bob',)
That username is not in the system.

#

it literally finds the username but still says no haha

#

im sure im doing something so simple wrong haha

stark ferry Mar 15, 2020, 4:33 AM

#

@quick geyser have you called type() on both USERN and wildcard?

#

if they are both strings, they should be identical

#

could it be that the database returns 'bob' instead of bob?

quick geyser Mar 15, 2020, 4:35 AM

#

Thats what im thinking

#

Im not sure how to fix that

stark ferry Mar 15, 2020, 4:36 AM

#

you can strip the variable

#

im sorry, i meant do replace

#

wildcard=wildcard.replace('"'",'')

#

though stripping whitespace is not a bad idea

quick geyser Mar 15, 2020, 4:38 AM

#

Hmmm lemme try that

stark ferry Mar 15, 2020, 4:38 AM

#

Also, looks like your result is a tuple

#

and your input is a string

quick geyser Mar 15, 2020, 4:38 AM

#

Tuple?

stark ferry Mar 15, 2020, 4:38 AM

#

like a list but inmutable

#

exampleTuple=(1,3,4)

#

exampleTuple[2]

quick geyser Mar 15, 2020, 4:39 AM

#

Oh gotcha

stark ferry Mar 15, 2020, 4:39 AM

#

so you gotta get the fist element since you used fetchone

quick geyser Mar 15, 2020, 4:39 AM

#

Should i str the result

stark ferry Mar 15, 2020, 4:39 AM

#

wildcard=myresult[0]

quick geyser Mar 15, 2020, 4:40 AM

#

Wildcard = str(myresult)

#

Oh ignore me

#

Ok😂

stark ferry Mar 15, 2020, 4:40 AM

#

you also could do

#

     print('yes')

#

that would chec k the tuple for USERN

quick geyser Mar 15, 2020, 4:43 AM

#

that just did it

#

your a beautiful person

#

xD

#

    USERN = input("USERNAME: ")                         
    cur.execute(f"""SELECT USERNAME from users where USE
    myresult = cur.fetchone()                           
    print(myresult)                                                
    if USERN in myresult:                               
        print("yes")                                    
    else:                                               
        print("That username is not in the system.")

#

works now

#

  print("Please enter your username.")                  
  USERN = input("USERNAME: ")                           
  cur.execute(f"""SELECT USERNAME from users where USERN
  myresult = cur.fetchone()                             
  print(myresult)                                       
  wildcard = myresult[0]                                
  wildcard = wildcard.replace("''", '')                 
  if USERN == wildcard:                                 
      print("yes")                                      
  else:                                                 
      print("That username is not in the system.")      
``` also works

#

dont even need the wildcard.replace

#

yea i got an error of tuple earlier haha

#

sweet thanks, i appricate ya

#

this is the error now if i purposly miss type a username

#

argument of type 'NoneType' is not iterable

#

    print("Please enter your username.")                
    USERN = input("USERNAME: ")                         
    cur.execute(f"""SELECT USERNAME from users where USE
    myresult = cur.fetchone()                           
    print(myresult)                                                
    if USERN in myresult:                               
        print("yes")                                    
    else:                                               
        print("That username is not in the system.")    
``` ysing this code

quick geyser Mar 15, 2020, 5:20 AM

#

im guessing if the SELECT USERNAME from users where USERN comes back nonetype, its htorwing an error. any idea?

charred fractal Mar 15, 2020, 5:22 AM

#

What SQL are you using?

quick geyser Mar 15, 2020, 5:22 AM

#

mysql workbench

#

im just wondering if theres a check to see if it returns Null then it would reask for usernae

charred fractal Mar 15, 2020, 5:24 AM

#

I would suggest doing if(len(myresult)) == 0: print("no") else: print("yes")

quick geyser Mar 15, 2020, 5:28 AM

#

object of type 'NoneType' has no len()

rich trout Mar 15, 2020, 5:43 AM

#

.fetchone() either returns a single result, or None

quick geyser Mar 15, 2020, 5:44 AM

#

i got it to work i think

rich trout Mar 15, 2020, 5:44 AM

#

👍

quick geyser Mar 15, 2020, 5:44 AM

#

    USERN = input("USERNAME: ")
    PASSW = input("PASSWORD: ")
    cur.execute(f"""SELECT USERNAME from users where USERNAME='{USERN}'""")
    myresult = cur.fetchone()
    print(myresult)
    cur.execute(f"""SELECT PASSWORD from users where PASSWORD='{PASSW}'""")
    myresult2 = cur.fetchone()
    print(myresult2)
#    myresult = myresult[0]
#    wildcard = wildcard.replace("''", '')
    if myresult is None:
        print("That username and/or password is not in the system.")
        login()
    elif myresult2 is None:
        print("That username and/or password is not in the system.")
        login()
    myresult = myresult[0]
    myresult2 = myresult2[0]
    if myresult in USERN and myresult2 in PASSW:
        print("pass")
    else:
        print("That username and/or password is not in the system.")
        print("Make sure")
        login()

#

i know i can make it smaller in teh future, but just getting it to work is one thing for me

rich trout Mar 15, 2020, 5:44 AM

#

yeah, that's how you use it 👍

#

I would suggest looking into parameterization as well though

quick geyser Mar 15, 2020, 5:45 AM

#

will do! this is gonna be an app here soon once i figure out a few more things xd

#

got a long way to go

#

looooooolk

rich trout Mar 15, 2020, 5:45 AM

#

It's this bit "Instead, use the DB-API’s parameter substitution." at the top of the sqlite3 library: https://docs.python.org/3/library/sqlite3.html

#

good luck!

north niche Mar 15, 2020, 7:31 AM

#

What is best async ORM for Python (PostgreSQL)?

fringe tiger Mar 15, 2020, 11:03 AM

#

That's a hard question ping me if you find answer @north niche

echo beacon Mar 15, 2020, 11:20 AM

#

Hello guys, I am trying to use the same model in two different models... the model is s Link and stores additional data about a link/web URL and I am trying to use it in multiple models (using Flask and SQLAlchemy). The problem is I don't know how to do it because the Link model binds the relationship with a hardcoded name in the code: links = db.relationship('Link', backref='project', lazy=True) and project_id = db.Column(db.Integer, db.ForeignKey('project.id'), nullable=False). First of all, is my approach reasonable or is there a better way to do it? If not, what should I do to make it work?
Thank you in advance 🙂

fringe tiger Mar 15, 2020, 12:52 PM

#

@north niche so from a bit of googling these 3 stand out
https://github.com/python-gino/gino
https://github.com/encode/orm
https://github.com/tortoise/tortoise-orm

Problem with Gino is that for now it supports only asyncpg dialect.
Problem with encode/orm is that it seems it's the youngest and not developed a lot in terms of releases/commits/contributors (all 3 suffer from that but I think it's most prevalent here).
Not sure about Tortoise, can't see any problem that stands out right away from readme.

#

So my guess is that tortoise-orm would be the best overall, if someone can double check those 3 repos and give opinon that would be great

north niche Mar 15, 2020, 1:11 PM

#

@fringe tiger I like too Tortoise, but only one problem: I wanna use pg arrays. Do you know how to use this?

fringe tiger Mar 15, 2020, 1:17 PM

#

all I could find is this Per-DB overrides: https://tortoise-orm.readthedocs.io/en/latest/fields.html#tortoise.fields.base.Field.GENERATED_SQL

#

I didn't use it so I don't know

fringe tiger Mar 15, 2020, 3:48 PM

#

I tried using it , looks good ... but can't figure out how to do checks/constraints with tortoise-orm model

#

I'm thinking of overwritting init, doing function checks, and then super() the model

#

hm ye seems to work

#

altho not sure if I should use this and how https://tortoise-orm.readthedocs.io/en/latest/models.html#tortoise.models.Model.check

quick geyser Mar 15, 2020, 5:19 PM

#

is a WHERE type allowed in f"""INSERT INTO users(LIKES) VALUES (%s), WHERE USERNAME (%s)"""?

#

if so, how would that properly be written

fringe tiger Mar 15, 2020, 5:57 PM

#

no, logically it doesn't make sense. Use update then.

quick geyser Mar 15, 2020, 5:59 PM

#

i just changed it over

#

didnt fully understand insert haha

#

okay so if it updates the number to 3, is there a way i can make it just add onto 3 seperated by a comma instead of replacing 3 to the new number?

#

basically cell has 3,4,5,6,7
instead of cell = 3
then cell =4...

#

likes = F"""UPDATE users SET LIKES = {test} WHERE USERNAME = '{USERN}'"""
cnx.commit()

#

maybe join would work?

fringe tiger Mar 15, 2020, 6:09 PM

#

I'm not sure what you're asking but yes that query seems valid, just don't hardcode the string use the placeholders %s

echo beacon Mar 15, 2020, 7:13 PM

#

Anybody here knows SQLAlchemy?

#

How can I create one to many relationship without mentioning the binding model in both models

#

I mean I have 3, lets say page, book and magazine. I want to use pageModel in bookModel and MagazineModel, but I don't know hot to achieve that, documentation and tutorials use properties like backref to "bind" the related model back to the originating model... if that makes any sense

wicked fog Mar 16, 2020, 12:54 AM

#

@echo beacon me

#

Let's say your setup is books and pages

#

With the relationship being many pages to one book

#

You have a column in the pages table which foreign keys out to the id column of the books table

#

Your sqlalchemy python objects are Book() and Page() respectively

#

In the definition of Book you have

#

pages = relationship("Page", primaryjoin="Page.book_id==Book.id", backref="book")

#

So then in python book_object.pages gets you an iterable of associated Page objects

#

And page_object.book gets you the single Book object

#

@echo beacon ^

echo beacon Mar 16, 2020, 1:26 PM

#

@wicked fog thank you for the answer, but it isn't exactly what I'm looking for

#

Imagine the scenario you've described but with one more model, lets say a Magazine (similar to Book, but not the same so I would like to have it separately and also it would be best to use Page model in both Book and Magazine)

cerulean pendant Mar 16, 2020, 1:28 PM

#

@echo beacon you can just omit the backref and it works fine

#

in sqlalchemy you can declare the relationship on any direction -- many -> one or one -> many -- and with or without the backref

echo beacon Mar 16, 2020, 1:30 PM

#

So pages = db.relationship('Page') should work, right?

cerulean pendant Mar 16, 2020, 1:31 PM

#

if you have the foreignkeys in place, yes

echo beacon Mar 16, 2020, 1:31 PM

#

The code ran without errors... but I cannot find a column named books in the table

#

Is that how it shall work?

cerulean pendant Mar 16, 2020, 1:31 PM

#

that depends on "the code"

echo beacon Mar 16, 2020, 1:32 PM

#

All it does so far is initialise the db with the declared models

#

And the model has declared its row like mentioned above

wicked fog Mar 16, 2020, 1:33 PM

#

you'd need your column declared as well

cerulean pendant Mar 16, 2020, 1:33 PM

#

maybe you could show an executable example of your code

#

maybe

#

it depends

wicked fog Mar 16, 2020, 1:33 PM

#

probably something like book_id = Column(Integer, ForeignKey("books.id"))

#

or something along those lines

#

then you can have book = relationship(....)

echo beacon Mar 16, 2020, 1:35 PM

#

So I am not actually having books and pages, but rather Project model which contains a collection of links (URLs):class Project(db.Model): id = db.Column(db.Integer, primary_key=True) ... many entities links = db.relationship('Link')

#

Where Link is also a model declared in other file

#

And then there is Profile model which should also contain collection of links (URLs), so I want to reuse the Link model

#

Does that make sense to you guys?

cerulean pendant Mar 16, 2020, 5:00 PM

#

@echo beacon it seems you want a many to many relationship then

echo beacon Mar 16, 2020, 7:17 PM

#

@cerulean pendant isn't many to many relationship a relationship between 2 models (1. with 2. and 2. with 1. with variable count)? I am trying to have 3 models... 2 models in relationship with the 3rd.

wicked fog Mar 16, 2020, 7:33 PM

#

ok, three objects, Projects, Links, and Profiles

#

let's cover all the bases

#

@echo beacon

#

Can a single Project be associated with one Link, or multiple Links (multiple I think)?

#

Can a single Project be associated with just one Profile, or multiple Profiles?

#

can a single Link be associated with just one Project, or multiple projects?

#

can a single Link be associated with just one Profile, or multiple profiles?

#

can a single Profile be associated with just one Project, or multiple projects?

#

can a single Profile be associated with just one Link, or multiple Links?

#

@echo beacon answer me those ^ questions and that will tell us what kind of relationships we are working with

echo beacon Mar 16, 2020, 8:07 PM

#

@wicked fog sorry for late response and thank you for patience ... so there are Profiles, each contains Projects and Links, and each Project contains its own Links

wicked fog Mar 16, 2020, 8:12 PM

#

A Project contains multiple Links
A Project can be associated with multiple profiles
A Link is associated with no more than one project
A Link is associated with no more than one profile
A profile can contain multiple links
a profile can contain multiple projects
@echo beacon that accurate then?

echo beacon Mar 16, 2020, 8:14 PM

#

I would say point n. 2 shall be A project can be part of multiple profiles (many-to-many)

#

But other points fit my needs

gentle sparrow Mar 16, 2020, 8:20 PM

#

https://prnt.sc/rhed2c

Lightshot

Screenshot

Captured with Lightshot

#

Anyone know why I'm getting this error?

pure cypress Mar 16, 2020, 8:22 PM

#

Did you go to the URL in the error?

#

It should explain it

#

Wrong channel by the way. If you have further questions about that ask in #tools-and-devops

gentle sparrow Mar 16, 2020, 8:31 PM

#

Ok thanks

#

And let me try that

echo beacon Mar 16, 2020, 8:35 PM

#

@wicked fog are you able to help given that info?

wicked fog Mar 16, 2020, 8:39 PM

#

@echo beacon yes

#

ok

#

here we go

#

let's start with an easy one

#

a project can contain many links; a link is only part of one project

#

in the links table you have a project_id column which foreign keys out to the primary id of your project table

#

then in sqlalchemy as part of the Link object definition you have

#

class Link():
    __tablename__="links"
    project_id = Column(Integer, ForeignKey("projects.id")
    project = relationship("Project", backref="links")

#

we can do something similar with Profiles, I'm guessing a Link is associated with the person who added it

#

you'll have a user_id column in your links table as well

#

class Link():
    __tablename__="links"

    project_id = Column(Integer, ForeignKey("projects.id"))
    project = relationship("Project", backref="links")

    user_id = Column(Integer, ForeignKey("users.id"))
    user = relationship("User", backref="links")

#

that's two of your three relationships taken care of

#

now for the tricky one

#

profiles and projects are many to many

#

that means you need another table

#

let's call this table ProfileProjectRels ("profile project relationships")

#

and it would be set up in sqlalchemy like so:

#

class ProfileProjectRel():
    __tablename__="profileprojectrelationships"
    
    user_id=Column(Integer, ForeignKey("users.id"))
    user = relationship("User", backref="projectrels")

    project_id=Column(Integer, ForeignKey("projects.id"))
    project = relationship("Project", backref="userrels")

#

basically, this table is a listing of user/project ID pairs

#

now, in Users

#

class User():

    __tablename__="users"

    @property
    def projects(self):
        return [x.project for x in self.projectrels]

#

Similarly, your projects class

#

class Project():

    __tablename__="projects"

    @property
    def users(self):
        return [x.user for x in self.userrels]

#

@echo beacon ^ there you go

#

also I just realized I sort of turned Profile into User somwhere along the line

echo beacon Mar 16, 2020, 9:02 PM

#

@wicked fog I get this error: sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'link.project_id' could not find table 'projects' with which to generate a foreign key to target column 'id'

#

Maybe there is a small mistake but I just blindly rewrote the code

#

I changed some stuff and it executed

#

Changed plurals like ForeignKey("projects.id") to ForeignKey("project.id")

wicked fog Mar 16, 2020, 9:08 PM

#

@echo beacon you'll have to rename stuff to match your own table/column names of cours

#

note that in ForeignKey("tablename.columname") you're using the name of the table in the db

#

whereas for relationship("Thing") you're using the name of the Python class

echo beacon Mar 16, 2020, 9:09 PM

#

Makes sense!

#

Thank you a LOT @wicked fog, you saved me ~~hours~~ days of searching, I wouldn't be able to do this kind of DB setup myself. You're a real lifesaver, I really appreciate your help ❤️.

wicked fog Mar 16, 2020, 9:14 PM

#

@echo beacon yeah sqlalchemy is a lifesaver but it has a steep learning curve

echo beacon Mar 16, 2020, 9:15 PM

#

One more question @wicked fog when I want to populate the tables, how to go about that? Do I need to instantiate profileProjectRels object and add Profile and Project to it?

wicked fog Mar 16, 2020, 9:17 PM

#

@echo beacon so at the very beginning of your program you'll have a SQLalchemy session object created

#

so to do an insert

#

let's say a new user signs up

#

you would do something like this

#

new_user = User(username="captainmeta4", hashed_password="apoehfaaerq")
session.add(new_user)
session.commit()

#

again, adapt to your own table/column names

echo beacon Mar 16, 2020, 9:22 PM

#

And how would I add a project?

#

session.add(new_user)
session.commit()

Does not work

wicked fog Mar 16, 2020, 9:25 PM

#

@echo beacon you'd create a new ProfileProjectRel object

#

wait

#

now

#

no

#

you'd add a project as a project

echo beacon Mar 16, 2020, 9:27 PM

#

profile = Profile()
profile.displayName = "petak5"

project = Project()
project.title = 'Hello, World!'

link = Link()
link.title = 'Google'

project.links.append(link)
profile.projects.append(project)

db.session.add(profile)
db.session.commit()

#

I did this but no data is added to db

#

But when I add only the project object, it gets into the DB

#

But when I add only the project object, it gets into the DB

#

profile = Profile()
profile.displayName = "petak5"

project = Project()
project.title = 'Hello, World!'

link = Link()
link.title = 'Google'

project.links.append(link)
profile.projects.append(project)

db.session.add(profile)
db.session.commit()

wicked fog Mar 16, 2020, 9:30 PM

#

@echo beacon do you have a project_id column in your links table?

#

to add the link to the project, you'd set link_object.project_id=x

echo beacon Mar 16, 2020, 9:34 PM

#

But the id is not calculated yet

#

When I run this code, only profiles table gets populated

torn sphinx Mar 16, 2020, 9:36 PM

#

hey guys

#

could anybody help me with pandas?

hasty hinge Mar 16, 2020, 10:56 PM

#

Hello, I'm actually using MySQL (with the AioMySQL module in Python) for my Discord bot, I have a file with all functions defined, so I just have to execute them when I need them, but right now I think I could improve the file, because I see I'm creating a new connection for every time I execute a function, for example, if I execute 3 functions on 1 second, 3 connections would be created, how could I improve this code?
https://paste.pythondiscord.com/luwemubuzu.py

#

I'm relatively new with AioMySQL, so sorry if this is a dumb question.

slim shard Mar 17, 2020, 12:36 AM

#

Hey! You can create a pool of connections. In connection pooling, after a connection is created, it is placed in the pool and it is used again.

pool = yield from aiomysql.create_pool(host='127.0.0.1', port=3306,
                                           user='root', password='',
                                           db='mysql', loop=loop, autocommit=False)
def my_query():
    with (yield from pool) as conn:
            cur = yield from conn.cursor()
            yield from cur.execute("SELECT ...")
            ...

#

In your file you used connections pool wrongly, because in each function you are creating new pool and connections.

hasty hinge Mar 17, 2020, 12:54 AM

#

Okay, thanks!

slim shard Mar 17, 2020, 1:04 AM

#

You're welcome

quick geyser Mar 17, 2020, 3:13 AM

#

when i update a value with a list it inputs a number
3 for example
then when i select the value and append another number it adds quotes around the orginal and adds the second
'3', 23 for example,

["'3', 23", 42]
``` it gets bad, how can i remove those quotes from the value

shell ocean Mar 17, 2020, 3:14 AM

#

that's a single value.

#

show your code

#

also, seems like wrong channel...?

quick geyser Mar 17, 2020, 3:22 AM

#

Its pulled from a database so I figured right channel

steady epoch Mar 17, 2020, 11:47 AM

#

  File "C:\Users\hp\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\bot.py", line 863, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\hp\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 728, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\hp\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 88, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "IF": syntax error```

#

    async def add_clan(self, ctx,user:discord.User, clan_tag):
        """Command is used to register a user to the database"""
        fixed_tag = utils.correct_tag(clan_tag)
        clan = await self.coc_client.get_clan(fixed_tag)
        db  = sqlite3.connect("bot_db.sqlite")
        db.execute("INSERT IF NOT EXISTS INTO register (clan_tag,discord_id) VALUES(?,?)",(clan,user))
        db.commit()
        await ctx.send(f"{clan.name} linked to {user.mention} ")```

#

getting error

rich trout Mar 17, 2020, 11:49 AM

#

sqlite doesn't support INSERT IF NOT EXISTS

#

You'll need to reword it like INSERT OR REPLACE, or like this:
INSERT INTO memos(id,text) WHERE NOT EXISTS(SELECT 1 FROM memos WHERE id = 5);

steady epoch Mar 17, 2020, 12:00 PM

#

@rich trout i want to tell user too if user is not registered

rich trout Mar 17, 2020, 12:02 PM

#

does your current one do that?

#

You could also do INSERT OR IGNORE, which should return either a new rowid or nothing, but let me check

steady epoch Mar 17, 2020, 12:03 PM

#

and i wnt to notify user if he/she is not registered already

rich trout Mar 17, 2020, 12:06 PM

#

There

#

!e ```py
import sqlite3
db = sqlite3.connect(":memory:")
c = db.cursor()
c.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
print(c.execute("INSERT OR IGNORE INTO users VALUES (1, 'bob')").lastrowid)
print(c.execute("INSERT OR IGNORE INTO users VALUES (1, 'bob')").lastrowid)

delicate fieldBOT Mar 17, 2020, 12:06 PM

#

@rich trout :white_check_mark: Your eval job has completed with return code 0.

001 | 1
002 | 1

rich trout Mar 17, 2020, 12:06 PM

#

bleh

steady epoch Mar 17, 2020, 12:07 PM

#

will it send to the same table which i wnt

rich trout Mar 17, 2020, 12:08 PM

#

!e ```py
import sqlite3
db = sqlite3.connect(":memory:")
c = db.cursor()
c.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
print(c.execute("INSERT INTO users VALUES (1, 'bob')"))
print(c.execute("INSERT INTO users VALUES (1, 'bob')"))

delicate fieldBOT Mar 17, 2020, 12:08 PM

#

@rich trout :x: Your eval job has completed with return code 1.

001 | <sqlite3.Cursor object at 0x7f80571512d0>
002 | Traceback (most recent call last):
003 |   File "<string>", line 6, in <module>
004 | sqlite3.IntegrityError: UNIQUE constraint failed: users.id

rich trout Mar 17, 2020, 12:08 PM

#

Could use that for it

#

provided you use a UNIQUE column

steady epoch Mar 17, 2020, 12:09 PM

#

hmm

#

what should i write query?

rich trout Mar 17, 2020, 12:10 PM

#

Use a try and just INSERT normally

#

If you have a UNIQUE column--like discord id might be in your case--then it will throw an error you can catch

#

Or you could just SELECT to check first, which might be faster if you anticipate it failing lots of the time

steady epoch Mar 17, 2020, 12:11 PM

#

hmmmm u meant except right?

rich trout Mar 17, 2020, 12:11 PM

#

yeah

steady epoch Mar 17, 2020, 12:12 PM

#

what is the better option in your opinion

rich trout Mar 17, 2020, 12:12 PM

#

If it's a command you expect users to run manually, just try and catch the exception

steady epoch Mar 17, 2020, 12:12 PM

#

hmmmmmm

#

try:

#

if user is not none
then execute

#

except:

rich trout Mar 17, 2020, 12:14 PM

#

try:
  c.execute("INSERT...")
except sqlite3.IntegrityError:
  # already exists

steady epoch Mar 17, 2020, 12:14 PM

#

hmmm

rich trout Mar 17, 2020, 12:16 PM

#

You'll want to do CREATE TABLE clan_tags_etc (discord_id INTEGER UNIQUE, etc...)

steady epoch Mar 17, 2020, 12:16 PM

#

i have table already

rich trout Mar 17, 2020, 12:17 PM

#

since this is clearly a discord bot, you can just do a SELECT from to check if it exists before inserting, so long as you don't await anything in between the two

steady epoch Mar 17, 2020, 12:19 PM

#

i am awaiting

#

oops

#

@rich trout

#

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

#

got this error

rich trout Mar 17, 2020, 12:23 PM

#

Did you mix up the order of the id and tag? that could cause that

steady epoch Mar 17, 2020, 12:24 PM

#

!add_clan 384158530623635456 #P99CRYU2

#

i used this

#

@rich trout

rich trout Mar 17, 2020, 12:26 PM

#

What's your insert/select statement look like?

steady epoch Mar 17, 2020, 12:27 PM

#

    async def add_clan(self, ctx,user:discord.User, clan_tag):
        """Command is used to register a user to the database"""
        fixed_tag = utils.correct_tag(clan_tag)
        clan = await self.coc_client.get_clan(fixed_tag)
        db  = sqlite3.connect("bot_db.sqlite")
        try:
            db.execute("INSERT  INTO register (discord_id,clan_tag) VALUES(?,?)",(user,clan))
            db.commit()
            await ctx.send(f"{clan.name} linked to {user.mention} ")
        except sqlite3.IntegrityError:
            await ctx.send(f"{user} is already registered")```

rich trout Mar 17, 2020, 12:27 PM

#

You're passing in a user object, probably want to be passing in the id from it

steady epoch Mar 17, 2020, 12:28 PM

#

what should i write @rich trout

rich trout Mar 17, 2020, 12:29 PM

#

in your (user, clan) you're passing in a discord Member and a string

#

it needs an integer (the user's id) and a string

steady epoch Mar 17, 2020, 12:30 PM

#

    db = sqlite3.connect("bot_db.sqlite")
    cursor = db.cursor()
    cursor.execute('''
         CREATE TABLE IF NOT EXISTS register(
         clan_tag text NOT NULL, 
         player_tag text NOT NULL,
         discord_id integer NOT NULL,
         guild_id integer NOT NULL,
         PRIMARY KEY(discord_id)
         )
    ''')```

#

i have made this table

rich trout Mar 17, 2020, 12:31 PM

#

hm

#

You'll need an SQLIte unique clause

#

UNQIUE(discord, guild)

#

at the end, so that sqlite throws an error if the same user on the same server tries to set another tag

#

and then you catch it and tell them it's already set

steady epoch Mar 17, 2020, 12:32 PM

#

actually i am new to db

#

where should i put unique

#

i have made primary key discord id to keep it differentiated

rich trout Mar 17, 2020, 12:34 PM

#

at the bottom, like primary key is

#

youll need a comma like the rest of the columns too

#

Here's an example:

steady epoch Mar 17, 2020, 12:34 PM

#

ooo

rich trout Mar 17, 2020, 12:34 PM

#

CREATE TABLE shapes(
    shape_id INTEGER PRIMARY KEY,
    background_color TEXT,
    foreground_color TEXT,
    UNIQUE(background_color,foreground_color)
);

steady epoch Mar 17, 2020, 12:35 PM

#

ok

#

@rich trout will this solve my problem

#

???

rich trout Mar 17, 2020, 12:37 PM

#

yeah

steady epoch Mar 17, 2020, 12:37 PM

#

ok

#

@rich trout same error

rich trout Mar 17, 2020, 12:40 PM

#

Is your insert statement still db.execute("INSERT INTO register (discord_id,clan_tag) VALUES(?,?)",(user,clan))?

#

Shouldn't be (user, clan)--that'll turn into something like ("Bob#122", "clan_test"), which doesn't work cause you can't turn "Bob#122" into a number

#

try user.id instead

steady epoch Mar 17, 2020, 12:41 PM

#

ok

#

same error @rich trout

rich trout Mar 17, 2020, 12:43 PM

#

Hm

steady epoch Mar 17, 2020, 12:43 PM

#

!add_clan 384158530623635456 P99CRYU2

#

this i am using

rich trout Mar 17, 2020, 12:43 PM

#

So you'll notice that the table is this:

#

         clan_tag text NOT NULL, 
         player_tag text NOT NULL,
         discord_id integer NOT NULL,
         guild_id integer NOT NULL,

#

But you're only passing in two things

#

probably need to pass all of them, and in order

steady epoch Mar 17, 2020, 12:44 PM

#

but in query 2 things are mentioned only

rich trout Mar 17, 2020, 12:45 PM

#

true

steady epoch Mar 17, 2020, 12:45 PM

#

then y this erro

rich trout Mar 17, 2020, 12:45 PM

#

What's your insert look like now?

steady epoch Mar 17, 2020, 12:45 PM

#

    @commands.command(name="add_clan")
    async def add_clan(self, ctx,user:discord.User, clan_tag):
        """Command is used to register a user to the database"""
        fixed_tag = utils.correct_tag(clan_tag)
        clan = await self.coc_client.get_clan(fixed_tag)
        db  = sqlite3.connect("bot_db.sqlite")
        try:
            db.execute("INSERT  INTO register (discord_id,clan_tag) VALUES(?,?)",(user.id,clan))
            db.commit()
            await ctx.send(f"{clan.name} linked to {user.mention} ")
        except sqlite3.IntegrityError:
            await ctx.send(f"{user} is already registered")```

rich trout Mar 17, 2020, 12:46 PM

#

Well, here you're passing in clan, not clan_tag. Does clan have an id?

steady epoch Mar 17, 2020, 12:47 PM

#

yep

rich trout Mar 17, 2020, 12:48 PM

#

Then you should probably pass the id, not the clan itself

steady epoch Mar 17, 2020, 12:48 PM

#

i am checking through get_clan method

rich trout Mar 17, 2020, 12:48 PM

#

In your db.execute() line you're passing in the clan object from your coc client

#

you should pass in a tag or an id there

#

depending on how you did it originally

steady epoch Mar 17, 2020, 12:49 PM

#

i am passing tag only

#

get_clan make sure it should be correct

rich trout Mar 17, 2020, 12:49 PM

#

Well, the error you got and the code you shared above indicates you're passing the clan object--not the clan tag

#

yes, which you store the clan object in clan, and then pass clan, instead of clan_tag to the insert command

steady epoch Mar 17, 2020, 12:50 PM

#

hmm means i need to do clan.tag?

#

@rich trout my bot executed but its giving i am registered already

rich trout Mar 17, 2020, 12:53 PM

#

Hm. Try printing the error message. IntegrityError might be used for if you're short values on an insert. But otherwise it should be working

steady epoch Mar 17, 2020, 12:53 PM

#

ok

rich trout Mar 17, 2020, 12:53 PM

#

--and by printing the error message I mean just adding a raise to the end of that except block so it prints again

steady epoch Mar 17, 2020, 12:58 PM

#

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: IntegrityError: NOT NULL constraint failed: register.player_tag

#

@rich trout

rich trout Mar 17, 2020, 12:58 PM

#

Yep, as I thought

#

your CREATE TABLE tells sqlite that you have to provide a player tag, and a clan tag, and a discord_id, and a guild_id

#

but you're only giving it two of the four

steady epoch Mar 17, 2020, 12:59 PM

#

i think i need to remove not null

rich trout Mar 17, 2020, 12:59 PM

#

that would work too

steady epoch Mar 17, 2020, 1:00 PM

#

i want to add player id nxt to their discord id

#

when player uses !add_player command

#

@rich trout

rich trout Mar 17, 2020, 1:02 PM

#

Sure, if you want

#

just have to add it to the command and insert like the other bit

steady epoch Mar 17, 2020, 1:02 PM

#

but for this command how can i pass player_tag

rich trout Mar 17, 2020, 1:04 PM

#

Same way as you pass clan_tag I assume?

steady epoch Mar 17, 2020, 1:05 PM

#

if i will dont give player id will it throw error @rich trout

rich trout Mar 17, 2020, 1:07 PM

#

For each item there, either the command needs to require it and you need to provide it to sqlite in the insert statement, or it needs to be nullable (without NOT NULL)

#

I've also noticed you've got guild_id as NOT NULL, but aren't passing it in

steady epoch Mar 17, 2020, 1:08 PM

#

i think i need to remove not null from every thing except discord id @rich trout

rich trout Mar 17, 2020, 1:09 PM

#

Probably a good idea

torn sphinx Mar 17, 2020, 1:26 PM

#

Guys could someone help me with manipulating a database

steady epoch Mar 17, 2020, 1:26 PM

#

    async def th(self,ctx , clan_tag):
        db = sqlite3.connect("bot_db.sqlite")
        if clan_tag is None:
            stmt = db.Cursor()
            stmt.execute(f"SELECT clan_tag from register WHERE discord_id = {ctx.author.id}")
            clan = stmt.fetchone()
        else:
            fixed_tag= utils.correct_tag(clan_tag)
            clan = await self.coc_client.get_clan(fixed_tag)
            await ctx.trigger_typing() 
        th = {13: [], 12: [],11: [], 10: [], 9: [], 8: [], 7: [], 6: [],5: [] ,4: [],3:[],2:[],1:[]}
        emoji=''


        async for player in clan.get_detailed_members():
            th[player.town_hall].append(player.name)
        for i,j in th.items():
            if not j:
                pass
            else:
                emoji += f'{(os.getenv(str(i)))} {len(j)}\n'
                embed = discord.Embed(colour=discord.Colour.blue(),title=f'{clan.name} {clan.tag}',description=f'**TOWNHALL COMPOSITION**\n{emoji} \n TOTAL MEMBERS : {clan.member_count}/50')
                embed.set_thumbnail(url=clan.badge.url)
                embed.timestamp = datetime.datetime.utcnow()
        await ctx.send(embed=embed)```

#

@rich trout its not fetching one data which i inserted

rich trout Mar 17, 2020, 1:27 PM

#

did you forget a commit()?

steady epoch Mar 17, 2020, 1:28 PM

#

nopw

#

nope

#

my register command worked

#

but this clan tag has not been fetched

#

@rich trout

rich trout Mar 17, 2020, 1:31 PM

#

I don't see anything wrong with it. Are you sure it's showing up in the database properly?

#

I bet you didn't insert a guild_id and it's back to throwing an integrityerror

steady epoch Mar 17, 2020, 1:33 PM

#

@rich trout check this

📎 Capture1.PNG

rich trout Mar 17, 2020, 1:33 PM

#

ok

#

Oh, I see it

#

clan = stmt.fetchone()

#

Are you missing a await self.coc_client.get_clan(fixed_tag)?

steady epoch Mar 17, 2020, 1:35 PM

#

i dont need to check that again as it register clan after checking only

rich trout Mar 17, 2020, 1:35 PM

#

Right, but right now clan is a string, returned from sqlite

#

and then you immediately go after clan.get_detailed_members, as if it was a Clan Object

steady epoch Mar 17, 2020, 1:36 PM

#

so what i correct

#

@urban goblet
discord.ext.commands.errors.MissingRequiredArgument: clan_tag is a required argument that is missing.

#

this error is coming

#

actually bot is not selecting anything

rich trout Mar 17, 2020, 1:38 PM

#

Ah, you want clan_tag=None then, if you want it to be optional on your def line

#

otherwise it defaults to required

steady epoch Mar 17, 2020, 1:38 PM

#

ooo

#

@rich trout
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'sqlite3.Connection' object has no attribute 'Cursor'

rich trout Mar 17, 2020, 1:42 PM

#

Cursor -> cursor

steady epoch Mar 17, 2020, 1:42 PM

#

ok

fluid frigate Mar 17, 2020, 1:45 PM

#

alright I'm about to pull my hair out with how clumsy SQLAlchemy is. Maybe someone here can help me.

Effectively, I have two tables, a "Users" table, which contains one row for every user in the system, and a "Warnings" table, which has a one-to-many relationship with the Users table, where each user could have any number of warnings related to them. It's fairly straightforward, but here's the data model:

class UserTable(_Base):
    __tablename__ = 'Users'

    User_Id = Column('User_Id', Integer, primary_key=True, nullable=False)
    Discord_Id = Column('Discord_Id', Integer, unique=True, nullable=False)


class WarningTable(_Base):
    __tablename__ = 'Warnings'

    Warning_Id = Column('Warning_Id', Integer, primary_key=True, nullable=False)
    User_Id = Column('User_Id', Integer, ForeignKey('Users'), nullable=False)
    Warning_Stamp = Column('Warning_Stamp', DateTime, nullable=False)

What I want to do, is query for the Discord_Id, and get all the warnings associated to that user. Here's the SQL statement I want to achieve.

SELECT Warnings.* 
FROM Users
LEFT OUTER JOIN Warnings ON Users.User_Id == Warnings.User_Id
WHERE Users.Discord_Id = <provided id>

I've been wracking my head for over a day on this and trying different things, and the best I've gotten seems to be this statement:

warning_rows = session.query(WarningTable). \
               select_from(UserTable). \
               outerjoin(UserTable.User_Id). \
               filter(UserTable.Discord_Id == discord_id)

But when I try to execute this, I just get an error back saying AttributeError: entity.
For context, among other things, I'm referencing the SQLAlchemy's documentation example, shown here: https://docs.sqlalchemy.org/en/13/orm/query.html?highlight=query#sqlalchemy.orm.query.Query.select_from

Any help would be greatly appreciated at this point, as I'm about this close to just saying fuck it and trying to manually shove text() sql down alchemy's throat.

steady epoch Mar 17, 2020, 1:47 PM

#

@rich trout
Traceback (most recent call last):
File "C:\Users\hp\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\bot.py", line 863, in invoke
await ctx.command.invoke(ctx)
File "C:\Users\hp\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 728, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\hp\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 88, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'tuple' object has no attribute 'upper'

#

    async def th(self,ctx , clan_tag=None):
        db = sqlite3.connect("bot_db.sqlite")
        if clan_tag is None:
            stmt = db.cursor()
            stmt.execute(f"SELECT clan_tag from register WHERE discord_id = {ctx.author.id}")
            clan_tag= stmt.fetchone()
            fixed_tag= utils.correct_tag(clan_tag)
            clan = await self.coc_client.get_clan(fixed_tag)
        else:
            fixed_tag= utils.correct_tag(clan_tag)
            clan = await self.coc_client.get_clan(fixed_tag)
            await ctx.trigger_typing() 
        th = {13: [], 12: [],11: [], 10: [], 9: [], 8: [], 7: [], 6: [],5: [] ,4: [],3:[],2:[],1:[]}
        emoji=''


        async for player in clan.get_detailed_members():
            th[player.town_hall].append(player.name)
        for i,j in th.items():
            if not j:
                pass
            else:
                emoji += f'{(os.getenv(str(i)))} {len(j)}\n'
                embed = discord.Embed(colour=discord.Colour.blue(),title=f'{clan.name} {clan.tag}',description=f'**TOWNHALL COMPOSITION**\n{emoji} \n TOTAL MEMBERS : {clan.member_count}/50')
                embed.set_thumbnail(url=clan.badge.url)
                embed.timestamp = datetime.datetime.utcnow()
        await ctx.send(embed=embed)```

rich trout Mar 17, 2020, 1:48 PM

#

gross discord.py ate the error message

fluid frigate Mar 17, 2020, 1:48 PM

#

it does that a lot .-.

rich trout Mar 17, 2020, 1:49 PM

#

I would venture a guess that clan_tag= stmt.fetchone() is giving you (clan_tag,), and you need to use [0] to get it out

steady epoch Mar 17, 2020, 1:49 PM

#

what should i write clan_tag,[0] where

rich trout Mar 17, 2020, 1:50 PM

#

fetchone()[0]

steady epoch Mar 17, 2020, 1:50 PM

#

ok

#

@rich trout ty broooooooo it worked

rich trout Mar 17, 2020, 1:52 PM

#

Scytail, what sql is it emitting right now?

fluid frigate Mar 17, 2020, 1:52 PM

#

i don't know, i can't get it to run. whenever I try to get that statement to execute, it just throws an error about attributes

#

i think it's blowing up when it's trying to build the SQL to begin with

#

I've even tried sticking relationship references in my tables to see if I could somehow impact the statement that way:

steady epoch Mar 17, 2020, 1:55 PM

#

@rich trout if i register different clan with same discord id and i do fetchall will it show all?

fluid frigate Mar 17, 2020, 1:55 PM

#

class UserTable(_Base):
    __tablename__ = 'Users'

    User_Id = Column('User_Id', Integer, primary_key=True, nullable=False)
    Discord_Id = Column('Discord_Id', Integer, unique=True, nullable=False)
    Warnings = relationship("WarningTable", back_populates="User")


class WarningTable(_Base):
    __tablename__ = 'Warnings'

    Warning_Id = Column('Warning_Id', Integer, primary_key=True, nullable=False)
    User_Id = Column('User_Id', Integer, ForeignKey('Users.User_Id'), nullable=False)
    Warning_Stamp = Column('Warning_Stamp', DateTime, nullable=False)
    User = relationship("UserTable", back_populates="Warnings")

rich trout Mar 17, 2020, 1:55 PM

#

It should, provided you're on a different guild, because you made them unique

fluid frigate Mar 17, 2020, 1:56 PM

#

^

rich trout Mar 17, 2020, 1:56 PM

#

Ah, those relationships are important

fluid frigate Mar 17, 2020, 1:56 PM

#

yeah, but i still have the same problem

rich trout Mar 17, 2020, 1:56 PM

#

What's the full traceback?

fluid frigate Mar 17, 2020, 1:56 PM

#

it's a mess, but hold on

rich trout Mar 17, 2020, 1:57 PM

#

👍 I read messes for a living

fluid frigate Mar 17, 2020, 1:57 PM

#

Traceback (most recent call last):
  File "C:\Users\Ben\Documents\Git\Manageable\venv\lib\site-packages\discord\ext\commands\core.py", line 79, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\Ben\Documents\Git\Manageable\Code\Cogs\ModToolsCog.py", line 39, in warn
    self._remove_outdated_warnings(target_member)
  File "C:\Users\Ben\Documents\Git\Manageable\Code\Cogs\ModToolsCog.py", line 63, in _remove_outdated_warnings
    user_warnings = DataAccess.lookup_warnings_by_discord_id(target_member.id)
  File "C:\Users\Ben\Documents\Git\Manageable\Code\Base\Decorator.py", line 83, in __call__
    return self.run(*args, **kwargs)
  File "C:\Users\Ben\Documents\Git\Manageable\Code\Data\DataAccess.py", line 23, in run
    return_data = super(DatabaseMethod, self).run(*args, **kwargs)  # Execute the method
  File "C:\Users\Ben\Documents\Git\Manageable\Code\Base\Decorator.py", line 93, in run
    return self.decorated(*args, **kwargs)
  File "C:\Users\Ben\Documents\Git\Manageable\Code\Data\DataAccess.py", line 46, in lookup_warnings_by_discord_id
    warning_rows = session.query(WarningTable). \
  File "C:\Users\Ben\Documents\Git\Manageable\venv\lib\site-packages\sqlalchemy\orm\query.py", line 2312, in outerjoin
    return self._join(
  File "<string>", line 2, in _join
  File "C:\Users\Ben\Documents\Git\Manageable\venv\lib\site-packages\sqlalchemy\orm\base.py", line 220, in generate
    fn(self, *args[1:], **kw)
  File "C:\Users\Ben\Documents\Git\Manageable\venv\lib\site-packages\sqlalchemy\orm\query.py", line 2426, in _join
    right = onclause.property.entity
  File "C:\Users\Ben\Documents\Git\Manageable\venv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 977, in __getattr__
    return self._fallback_getattr(key)
  File "C:\Users\Ben\Documents\Git\Manageable\venv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 951, in _fallback_getattr
    raise AttributeError(key)
AttributeError: entity

#

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

Traceback (most recent call last):
  File "C:\Users\Ben\Documents\Git\Manageable\venv\lib\site-packages\discord\ext\commands\bot.py", line 863, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\Ben\Documents\Git\Manageable\venv\lib\site-packages\discord\ext\commands\core.py", line 728, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\Ben\Documents\Git\Manageable\venv\lib\site-packages\discord\ext\commands\core.py", line 88, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: entity

#

enjoy hahaha

#

in case it matters, the "decorator" stuff basically just wraps my sql methods in some simple code to handle sql sessions, doesn't impact the queries or anything

rich trout Mar 17, 2020, 1:59 PM

#

ew gross

#

lol

fluid frigate Mar 17, 2020, 1:59 PM

#

don't hate it actually works p well

rich trout Mar 17, 2020, 2:00 PM

#

I see, I was right

fluid frigate Mar 17, 2020, 2:00 PM

#

completely abstracts away session management, and my discord bot doesn't have to care about databases at all now, which cleans up the data layer from the business layer

rich trout Mar 17, 2020, 2:00 PM

#

Something's not being passed to the join properly

fluid frigate Mar 17, 2020, 2:00 PM

#

yeah that's what i thought

#

but i cannot for the life of me figure out what's going on

#

especially since I'm mirroring sqlalchemy's code example so closely

rich trout Mar 17, 2020, 2:01 PM

#

yeah

#

let me just..

#

oh

#

wow

#

wth

fluid frigate Mar 17, 2020, 2:03 PM

#

did we miss something obvious

rich trout Mar 17, 2020, 2:03 PM

#

Idk

#

I blindly copied the example and reproduced your error

fluid frigate Mar 17, 2020, 2:03 PM

#

hooray for broken documentation \o/

rich trout Mar 17, 2020, 2:04 PM

#

found it

#

it's a tiny irritating impossibly easy to miss detail that reminds me of exactly why I don't like sqlalchemy's docs

#

It also seriously needs a better error message

#

let me get my phone I'm reporting a bug

#

Ok, the detail: you must join on the relationship not the column

fluid frigate Mar 17, 2020, 2:06 PM

#

yeah "AttributeError: entity" means literally nothing lol

#

oh what

#

w h a t

#

how was i supposed to know that lmao

rich trout Mar 17, 2020, 2:06 PM

#

IKR

#

q = session.query(Owner).select_from(Dog).\
    join(Dog.owner).\
    filter(Dog.name == 'Spark')

works

#

q = session.query(Owner).select_from(Dog).\
    join(Dog.owner_id).\
    filter(Dog.name == 'Spark')

#

AttributeError: Entity

fluid frigate Mar 17, 2020, 2:08 PM

#

ahhhhh it works

#

thanks so much

#

i would never have figured that out haha

rich trout Mar 17, 2020, 2:08 PM

#

oh good, it's already in their issue tracker

#

last..touched..dec...19..2019

fluid frigate Mar 17, 2020, 2:08 PM

#

;-;

rich trout Mar 17, 2020, 2:11 PM

#

Hm. Well, at least there's attention to it

#

glances at a particular, related error message from ponyorm that happens when you screw up capitalization during a query

#

anyway

#

glad it's identified, and I'll keep that in the back of my mind if someone else runs into it

fluid frigate Mar 17, 2020, 2:13 PM

#

yeah i'll have to remember that too, thank you so much

#

so many of my code pieces just clicked into place with that bug gone you have no idea lol

rich trout Mar 17, 2020, 2:15 PM

#

:P

#

fireworks2

thorn gale Mar 17, 2020, 3:40 PM

#

Hello, is this the right place to ask a question about a sqlalchemy query?

rich trout Mar 17, 2020, 3:43 PM

#

yep

thorn gale Mar 17, 2020, 3:48 PM

#

So I have this query :

db.session.query(UserCatalogs).filter(UserCatalogs.id == catalog_id).first().child_cat

Which gives me :

gChild('14', '786','1'), CatalogChild('15', '786','1'), CatalogChild('16', '785','1'), CatalogChild('17', '784','1')]```

But I would like to somehow recover the numbers(761, 786 etc) inside the CatalogChild but I do not know how to and I guess I'm googling it wrong too. I mean I would basically like to do :

 ```db.session.query(UserCatalogs).filter(UserCatalogs.id == catalog_id).first().child_cat.item_id```

But ofc, it doesn't work

rich trout Mar 17, 2020, 3:55 PM

#

Well, it's giving you a list of results

#

So you'd have to use a for loop, for example, to get them out

#

for child_cat in db.session.query(UserCatalogs).filter(UserCatalogs.id == catalog_id).first().child_cat:
  print(child_cat, child_cat.item_id)

thorn gale Mar 17, 2020, 3:59 PM

#

Alright, thanks you so much ❤️

sonic mural Mar 17, 2020, 5:53 PM

#

Using SQLAlchemy, what is the best practice for checking if an entry exists with a specific value. Would it just be a query.one() enclosed in a try-except block or is there a specific method for checking if an entry exists?

cerulean pendant Mar 17, 2020, 6:02 PM

#

you can also use first() and check for None

cyan yew Mar 17, 2020, 6:02 PM

#

getquery = "SELECT * FROM requests"
requests = cursor.execute(getquery)

cerulean pendant Mar 17, 2020, 6:02 PM

#

or you can use exists() in a subquery

cyan yew Mar 17, 2020, 6:02 PM

#

does that store all the rows in my table requests into a variable named requests

cerulean pendant Mar 17, 2020, 6:03 PM

#

no.

cyan yew Mar 17, 2020, 6:03 PM

#

(im new to this)

#

oh

#

how do I do it?

cerulean pendant Mar 17, 2020, 6:03 PM

#

you still have to call one or more of cursor.fetchone/cursor.fetchall/cursor.fetchmany to actually get the data

cyan yew Mar 17, 2020, 6:03 PM

#

oh ok

#

does fetchone get the latest

#

?

cerulean pendant Mar 17, 2020, 6:04 PM

#

fetchone will get one record

#

the next

cyan yew Mar 17, 2020, 6:04 PM

#

I did requests = cursor.execute(getquery).fetchall() and I got this error;

AttributeError: 'NoneType' object has no attribute 'fetchall'

cerulean pendant Mar 17, 2020, 6:04 PM

#

yeah, you have to call it in the cursor

#

no method chaining

cyan yew Mar 17, 2020, 6:05 PM

#

ok

cerulean pendant Mar 17, 2020, 6:05 PM

#

cursor.execute(getquery)
data = cursor.fetchall()

cyan yew Mar 17, 2020, 6:05 PM

#

ok thx

sonic mural Mar 17, 2020, 6:07 PM

#

or you can use exists() in a subquery
@cerulean pendant
ty

#

Ok question numero dos

#

I'm storing an entry which could either be a a BigInteger or a unicode emoji.
My thoughts are to convert the unicode emoji to some integer value

#

However, I'm not too sure on how to do that

#

(In sqlalchemy on a postgres server)

cyan yew Mar 17, 2020, 6:14 PM

#

('d', 'd', 'd', 'd', datetime.datetime(2020, 3, 17, 17, 2, 30), 1) if my cursor has this as a return, how do I access the first d, second d, so on

#

getquery = "SELECT * FROM requests"
cursor.execute(getquery)
rows = cursor.fetchall()
print(rows)

#

printing returned the d d dd stuff

#

I submited d d d d as a test

knotty parcel Mar 17, 2020, 7:17 PM

#

With PostgreSQL how to store a list that is made up of all strings?

Currently got: cursor.execute("CREATE TABLE applications(guildid BIGINT, app VARCHAR(255), appid BIGINT, questions TEXT[], roleid BIGINT") but it throws an error ```syntax error at end of input
LINE 1: ... VARCHAR(255), appid BIGINT, questions TEXT[], roleid BIGINT

#

So store a list of strings

#

@cerulean pendant Pinged you as you seem online and who what you are doing

cerulean pendant Mar 17, 2020, 7:41 PM

#

@knotty parcel you are missing a )

knotty parcel Mar 17, 2020, 7:53 PM

#

Thanks

calm charm Mar 17, 2020, 8:15 PM

#

does anyone know how to make it so that the each user has a different inventory, with a variety of things being inside the inventory.

#

with sqlite?

fresh zephyr Mar 17, 2020, 8:37 PM

#

What is the item, is it just a string?

#

You don't really need sqlite for that

cerulean pendant Mar 17, 2020, 8:47 PM

#

I mean, sqlite is pretty good

fresh zephyr Mar 17, 2020, 8:49 PM

#

Yeah, it is

#

It might just be an overkill for him

frail hearth Mar 17, 2020, 10:04 PM

#

@calm charm for PostgreSQL, i used this:

CREATE TABLE items (
    itemid bigserial NOT NULL,
    title text NULL,
    description text NULL,
    rarity int2 NULL,
    emoji text NULL,
    CONSTRAINT "items-primarykey" PRIMARY KEY (itemid)
);

CREATE TABLE useritems (
    userid int8 NOT NULL,
    gildid int8 NOT NULL,
    itemid int8 NOT NULL,
    CONSTRAINT useritems_unique UNIQUE (userid, gildid),
    CONSTRAINT useritems_fk FOREIGN KEY (itemid) REFERENCES items(itemid)
);

items is a table that holds all items.
useritems holds the items in peoples inventories

#

example data for items would be

 itemid |   title   |            description             | rarity | emoji
--------+-----------+------------------------------------+--------+-------
   0    | Test Item | This item Should Not Be Obtainable |      0 |   ⚠️

#

data for useritems would be

       userid       |       gildid       | itemid
--------------------+--------------------+--------
 477034228366966785 | 647850202430046238 |      0

#

hope this helps!

calm charm Mar 17, 2020, 10:17 PM

#

@frail hearth do I have to manually insert an item with the details into the items table

#

and what is gildid?

#

like guiild id?

frail hearth Mar 17, 2020, 10:25 PM

#

guildid is the I'd of the guild

#

Items have to be inserted, however you could probably make a command

calm charm Mar 17, 2020, 10:26 PM

#

how would I retrieve what's in the inventory for let's say the ctx.author.id

#

how wuld I select from inventory where userid is userid in useritems

frail hearth Mar 17, 2020, 10:29 PM

#

Unsure tbh, try looking up usage info for primary keys and foreign keys

charred fractal Mar 17, 2020, 10:29 PM

#

user_item.

frail hearth Mar 17, 2020, 10:29 PM

#

Gtg, apologies

calm charm Mar 17, 2020, 11:03 PM

#

u use like INNER JOIN

#

but i dont understand what it's saying

alpine patio Mar 17, 2020, 11:15 PM

#

hey

#

how to update a table by checking if something is new

charred fractal Mar 17, 2020, 11:26 PM

#

Example?

alpine patio Mar 17, 2020, 11:31 PM

#

a table with
id , name , email
.....
and let's say someone changed his name

#

I'm getting my data table as a json and then I want to check " does id = 11231 changed his name or not"

cerulean pendant Mar 17, 2020, 11:34 PM

#

if you really want to check, you have to fetch the data from the database and compare

#

but usually what we do is just send a UPDATE command - if nothing changed then it will update to the same value and nothing happens

alpine patio Mar 17, 2020, 11:35 PM

#

aaah I see

#

thank you

#

cursor = connection.cursor()
         sql_update_query = """Update students set type = %s where id = %s"""
         inputData = (type, id)
         cursor.execute(sql_update_query, inputData)
         connection.commit()
         print("Record Updated successfully ")

#

so i did something like that for 'type'

#

do i need to make another function for 'name' and 'mail' or is there an easier way

#

📎 unknown.png

cerulean pendant Mar 17, 2020, 11:48 PM

#

you can update all fields with a single query

alpine patio Mar 17, 2020, 11:48 PM

#

did it like that

#

wish me luck

cerulean pendant Mar 17, 2020, 11:49 PM

#

🍀

alpine patio Mar 17, 2020, 11:52 PM

#

@cerulean pendant ayyy worked

#

brainmon

torn sphinx Mar 18, 2020, 12:47 AM

#

can someone help me with sql database pls?

delicate fieldBOT Mar 18, 2020, 12:48 AM

#

Asking good questions will yield a much higher chance of a quick response:

• Don't ask to ask your question, just go ahead and tell us your problem.
• Don't ask if anyone is knowledgeable in some area, filtering serves no purpose.
• Try to solve the problem on your own first, we're not going to write code for you.
• Show us the code you've tried and any errors or unexpected results it's giving.
• Be patient while we're helping you.

You can find a much more detailed explanation on our website.

torn sphinx Mar 18, 2020, 12:51 AM

#

can someone pls help me with this homework lool i got 15 mins to hand it in

#

lol

wicked fog Mar 18, 2020, 12:53 AM

#

!rule 5

delicate fieldBOT Mar 18, 2020, 12:53 AM

#

Rules

5. Do not provide or request help on projects that may break laws, breach terms of services, be considered malicious/inappropriate or be for graded coursework/exams.

silk ember Mar 18, 2020, 12:53 AM

#

tell us your problem and we'll see if we can help

#

we can't do it for you though

calm charm Mar 18, 2020, 6:00 AM

#

why does this sql CREATE_TABLE_SQL = """CREATE TABLE Inventories ( user_id INTEGER PRIMARY KEY item_id INTEGER PRIMARY KEY FOREIGN KEY(user_id) REFERENCES Users(user_id) FOREIGN KEY(item_id) REFERENCES Items(item_id) );""" get me sqlite3.OperationalError: near "item_id": syntax error

shell ocean Mar 18, 2020, 6:13 AM

#

comma?

calm charm Mar 18, 2020, 6:15 AM

#

Nope

#

commas dont make a difference

#

wait nvm got it

#

ok so I got it working

#

Now how do I enter an item with has an id of 0 and name of test and desc of test to the ctx.author.id

#

Like if command run, add 1 of the test item to the ctx.author.id

#

also how are we measuring how much of each item they have

frail hearth Mar 18, 2020, 7:02 AM

#

Repetition of the same item

#

But with different item id'd

calm charm Mar 18, 2020, 7:08 AM

#

Ok now how do I get lets say an item description, item name, itemid that associates with the userid

#

Also how do I put each of those into an individual variabpe

frail hearth Mar 18, 2020, 7:12 AM

#

You'd have a variable for the items

calm charm Mar 18, 2020, 7:13 AM

#

Let's take this to dms

frail hearth Mar 18, 2020, 7:14 AM

#

Sure, I probably wont be able to help for the next 9 hours, as I have school. After I can try to make a mockup idea

calm charm Mar 18, 2020, 7:15 AM

#

jfc. 9 hours?

#

that's too long

#

it's fine

#

ping me when you need to talk

#

cuz imma probably be asleep

#

or just dm me

gentle sparrow Mar 18, 2020, 9:59 AM

#

Very Cliché question. I’m trying to make a social media like Twitter/Instagram and I want to use python/Django for some backend stuff. I don’t know anything about backend. Does anyone have any advice on where I should start ? I do know about frameworks and things like AWS but I don’t know how to use any of them.

#

If possible can anyone go into detail on what Instagram would specifically use Django for

solemn ridge Mar 18, 2020, 12:45 PM

#

@gentle sparrow go on #web-development

finite lynx Mar 18, 2020, 3:07 PM

#

In SQLAlchemy if I would like to return all columns that contain a string I should do:
Something.query.filter_by(name='string').contains()?

azure drift Mar 18, 2020, 3:22 PM

#

hi foizal

torn sphinx Mar 18, 2020, 3:23 PM

#

hi doniyal

azure drift Mar 18, 2020, 3:24 PM

#

Wald

torn sphinx Mar 18, 2020, 3:24 PM

#

personally isted of using MYSQL use tkinter

#

insted*

azure drift Mar 18, 2020, 3:25 PM

#

@calm charm sqlite3 to make data base doesnt work something is wrong its a problem many people have so use tkinter instead and os here is a video link

#

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

YouTube

johan godinho

How to create a graphical register and login system in python using...

(How to create a graphical register and login system in python using Tkinter) - This video is a basic tutorial which uses tkinter to build a program which will allow users to register an account on the software which they can later log in to. The second part of this tutorial w...

▶ Play video

#

https://www.youtube.com/watch?v=Z-deSpgtIG0&t=117s&pbjreload=10

YouTube

johan godinho

How to create a graphical register and login system in python using...

(How to create a graphical register and login system in python using Tkinter Part 2) - This tutorial is the follow up video based on part one of this series where I will be showing how to create basic register and login system using python.

Link to part 1 : https://youtu.be/...

▶ Play video

#

https://www.youtube.com/watch?v=OqfcGng4oKs&t=279s

YouTube

johan godinho

How to create a graphical system using python part 3

How to create a graphical system using python part 3 - This is the last video based on the Tkinter system creation series and explains how to allow a user to login into a system and gain a session. The video shows how to implement basic features into a software using modules s...

▶ Play video

wicked fog Mar 18, 2020, 3:26 PM

#

@finite lynx session.query(Object).filter(Object.variablename.contains("string")).all()

calm charm Mar 18, 2020, 3:32 PM

#

But I don't have a register login db

hasty hinge Mar 18, 2020, 3:34 PM

#

📎 unknown.png

#

Hello, I'm having an error in my MySQL DB when I try to add a unicode emoji as text, could you help me?

#

📎 unknown.png

#

I already set the encoding as utf8mb4 but the error keeps happening

📎 unknown.png

shell geode Mar 18, 2020, 4:33 PM

#

as far as I know, \xERe\as123\12312 is bytes, so you need to decode them to str

#

I might be wrong

serene pawn Mar 18, 2020, 5:01 PM

#

SO im trying to write a script that reads SQL tables

upbeat lily Mar 18, 2020, 5:03 PM

#

If you're looking to get started with SQL, w3schools is pretty decent for an intro https://www.w3schools.com/sql/

gentle sparrow Mar 18, 2020, 5:09 PM

#

@solemn ridge Thx for the redirection

solemn ridge Mar 18, 2020, 5:20 PM

#

np

finite lynx Mar 18, 2020, 8:07 PM

#

@wicked fog Ty

#

the variable name would be for example a column in a table and the Object is the table?

#

Also why sometimes querying would be done like with session.query etc and other times with Sometable.query.filter_by etc?

wicked fog Mar 18, 2020, 9:14 PM

#

@finite lynx yes Object is a python object which you've got mapped to a db table, and variablename is an object property which you've got mapped to a column in that table

#

query() is the general interface for selecting from a table

hasty hinge Mar 18, 2020, 9:17 PM

#

I am trying to do a file of functions with sql calls but I don't know how to create one pool for all functions, this is what my file looks like:
https://paste.pythondiscord.com/luwemubuzu.py
All of this goes with a discord.py bot.

silk ember Mar 18, 2020, 9:58 PM

#

you can attach the pool to your bots instance

#

there's an example for it i think

#

ah here's one example https://gist.github.com/jegfish/cfc7b22e72426f5ced6f87caa6920fd6

Gist

Example code for connecting to and using a postgres database using ...

Example code for connecting to and using a postgres database using discord.py and asyncpg. - example_db.py

#

i'd probably just do something like this though ```python
class Bot(commands.Bot):
def init(self):
super().init(command_prefix=prefix, description=description)
self.loop.create_task(self.initialize_database())

async def initialize_database(self):
    self.db = await aiomysql.create_pool(**credentials)

#

then you have access to your pool wherever you have access to your bot instance

strong compass Mar 18, 2020, 10:43 PM

#

guys i have been stuck on this for 3 hours dont know wat to do

#

import discord
from discord.ext import commands, tasks
from itertools import cycle
import random
import os
import asyncio
from discord.utils import get
import datetime
import sqlite3


class Wallet(commands.Cog):

    def __init__(self, client):
        self.client = client

    @commands.command(pass_context=True)
    async def wallet(self, ctx):
        db = sqlite3.connect('Main.db')
        cursor = db.cursor()
        cursor.execute("SELECT user_id FROM wallet")
        result = cursor.fetchall()
        if result is None:
            cursor.execute((f"INSERT INTO wallet (user_id) VALUES ({ctx.message.author.id})"))
            db.commit()
            cursor.close()
            db.close()
        elif result is not None:
            cursor.execute((f"UPDATE wallet SET (user_id) = ({ctx.message.author.id})"))
            db.commit()
            cursor.close()
            db.close()

        member = ctx.author
        embed = discord.Embed(title=f'- Customer - {member.name} - Wallet -', description=None, colour=discord.Color.
                              blue())
        embed.add_field(name="Platinum Tokens", value="0")
        embed.add_field(name="Held Platinum Tokens", value="0")
        embed.set_thumbnail(url=f"{member.avatar_url}")
        embed.timestamp = datetime.datetime.utcnow()
        embed.set_footer(text='MafiosOS Management Team', icon_url="https://cdn.discordapp.com/attachments"
                                                                   "/658963133125230602/664277505858994201"
                                                                   "/Mafioso_Discord.gif")
        await ctx.send(embed=embed)


def setup(client):
    client.add_cog(Wallet(client))

everything works properly but it wont insert or update user_id on the database

slim shard Mar 18, 2020, 11:18 PM

#

When you call fetchall() method, an empty list is returned when no rows are available.

#

Maybe if result: ... and elif not result: ... will work.

strong compass Mar 18, 2020, 11:28 PM

#

still doesnt work

slim shard Mar 18, 2020, 11:30 PM

#

Ahh, maybe if not result: ... and elif result: ...

#

Throws any exception?

strong compass Mar 18, 2020, 11:33 PM

#

omg

#

thank you soo much

#

worked

slim shard Mar 18, 2020, 11:33 PM

#

\o/

strong compass Mar 18, 2020, 11:40 PM

#

@slim shard

#

it works

#

but

#

if another user do that command again it will take same row

#

and change id

slim shard Mar 18, 2020, 11:55 PM

#

what does this function need to do?

strong compass Mar 18, 2020, 11:56 PM

#

i just want it to add user id of users who use that command into a user_id

#

and keep storing them

calm charm Mar 19, 2020, 6:24 AM

#

If I have a column in sqlite, how do I assign a variable to it

#

example I have a column named item_id how do I assign a variable so that i can do if item_id == 1:

#

also let's say I have like 15 rows, can I make it so that each there are going to be 5 rows per result, and the user has to type .list 2 to see the next 5. and .llist 3 to see the last 5.

steady epoch Mar 19, 2020, 7:27 AM

#

db.execute("INSERT IF NOT EXIST INTO register (discord_id,clan_tag) VALUES(?,?)",(user.id,clan.tag))

#

i am using sqlite

#

error is coming it says its incorrect

#

if

finite lynx Mar 19, 2020, 8:01 AM

#

Whem setting up Flask-Migrate, should I better do it in the database config file or my models file?

torn sphinx Mar 19, 2020, 11:26 AM

#

I want to save the amount of messages users send monthly. Saving each individual message is overkill, so how should I go about creating a table with monthly entries? Thought about setting all dates to the 1st day of the month

cerulean pendant Mar 19, 2020, 11:28 AM

#

I usually have a yearmonth field: 202002 202003 202004

torn sphinx Mar 19, 2020, 11:29 AM

#

As a string/integer field?

cerulean pendant Mar 19, 2020, 11:30 AM

#

yes - and it is sortable so makes it easy for my use case

#

I figured I don't need the day as I won't be using it anyway

solemn ridge Mar 19, 2020, 11:30 AM

#

You can also use epochs

torn sphinx Mar 19, 2020, 11:31 AM

#

I was gonna use the date field but I guess a string or int should work too. But which one is faster to process?

solemn ridge Mar 19, 2020, 11:32 AM

#

epochs is just an int

#

4945432342

#

soemthing like that

calm charm Mar 19, 2020, 11:32 AM

#

https://discordapp.com/channels/267624335836053506/342318764227821568/690083182103625740

solemn ridge Mar 19, 2020, 11:32 AM

#

This is the epoch for rn

#

1584617552000

#

In my country

#

@calm charm I take it you have set up a model

#

?

calm charm Mar 19, 2020, 11:33 AM

#

wat

solemn ridge Mar 19, 2020, 11:33 AM

#

How are you connecting to your dtabase?

calm charm Mar 19, 2020, 11:33 AM

#

through a connection?

solemn ridge Mar 19, 2020, 11:33 AM

#

how then?

calm charm Mar 19, 2020, 11:33 AM

#

._.?

solemn ridge Mar 19, 2020, 11:34 AM

#

Send me the connection block

calm charm Mar 19, 2020, 11:34 AM

#

connection = await aiosqlite.connect('currency.db')
        cur = await connection.cursor()

solemn ridge Mar 19, 2020, 11:34 AM

#

Why are you using aiosqlite tbw?

calm charm Mar 19, 2020, 11:34 AM

#

it's asynchronous

solemn ridge Mar 19, 2020, 11:34 AM

#

so?

calm charm Mar 19, 2020, 11:34 AM

#

stops blocking

solemn ridge Mar 19, 2020, 11:34 AM

#

DB transactions cant get blocking

#

since you are using sqlite

calm charm Mar 19, 2020, 11:35 AM

#

aiosqlite is very similar to sqlite3

#

it doesnt matter

#

u just add await to almost all commands

#

so

solemn ridge Mar 19, 2020, 11:35 AM

#

I know how aiosqlite works

calm charm Mar 19, 2020, 11:35 AM

#

Like i have the column item_id

solemn ridge Mar 19, 2020, 11:35 AM

#

Anyways you need to setup a model for your table

calm charm Mar 19, 2020, 11:35 AM

#

what is a model?

solemn ridge Mar 19, 2020, 11:35 AM

#

What is the name of the table and the columns it has?

#

Let me explain.

cerulean pendant Mar 19, 2020, 11:36 AM

#

I was gonna use the date field but I guess a string or int should work too. But which one is faster to process?
@torn sphinx which kind of processing you plan on doing?

torn sphinx Mar 19, 2020, 11:36 AM

#

Adding to the entry's fields. Frequently

#

Like, I'm counting messages

cerulean pendant Mar 19, 2020, 11:37 AM

#

then you won't process

calm charm Mar 19, 2020, 11:37 AM

#

Name of table is Items columns are: item_id item_name item_description item_icon_emoji and name

cerulean pendant Mar 19, 2020, 11:37 AM

#

you just run UPDATE tb SET msgcount = msgcount + 1 WHERE yearmonth = ?

solemn ridge Mar 19, 2020, 11:37 AM

#

Basically. A table is like a class
Its attributes are its columns. Every row is a different instance, an object in other words, of class. If you want to create a new row, you can just create a new object and then commit that object to the database. If you want to update an object, you query all the rows using a filter of yourchoice, the thing you get is your object, you update the attributes of the object, you commit and it is updated

cerulean pendant Mar 19, 2020, 11:38 AM

#

if the yearmonth column is indexed it doesn't matter which type you use, it will be the same speed @torn sphinx

torn sphinx Mar 19, 2020, 11:38 AM

#

Alright, thanks

solemn ridge Mar 19, 2020, 11:38 AM

#

So how are you currently adding values to your table

calm charm Mar 19, 2020, 11:38 AM

#

but how do I create a python var for it?

#

through update or insert commands?

#

or manually through the editor

#

if it's a one time thing

solemn ridge Mar 19, 2020, 11:39 AM

#

Please stop using the ? it is kinda irritating, I am not sure if you don't mean it like that

#

So

#

A model looks like that

torn sphinx Mar 19, 2020, 11:39 AM

#

@calm charm you can use raw SQL like I did with my crappy bot I'm remaking. Here's an example


GET_POSTCOUNT_THREE_MONTHS = """SELECT COUNT(*) FROM messages 
WHERE guild_id = 107900245270110208 AND author = ? 
AND datetime(timestamp) > datetime('now', '-3 months')"""
async def get_postcount_three_months(connection, member):
    try:
        cursor = await connection.cursor()
        pctm = await cursor.execute(GET_POSTCOUNT_THREE_MONTHS, (member,))
        postcount = pctm.fetchone()[0]
        await cursor.close()
        return postcount
    except OperationalError as e:
        print(f"DB error when getting topping eligibles:\n{e}")
        await sleep(1)
        await get_postcount_three_months(connection, member)```

solemn ridge Mar 19, 2020, 11:39 AM

#

dont.

calm charm Mar 19, 2020, 11:39 AM

#

._.

torn sphinx Mar 19, 2020, 11:39 AM

#

Better don't, yeah. ORMs are handy

calm charm Mar 19, 2020, 11:39 AM

#

i dont think that's what i need

solemn ridge Mar 19, 2020, 11:40 AM

#

Either way, using a Model is 100% times better practice

torn sphinx Mar 19, 2020, 11:40 AM

#

Notice the try/except - aiosqlite tends to error a lot

solemn ridge Mar 19, 2020, 11:40 AM

#

than insert, update

calm charm Mar 19, 2020, 11:40 AM

#

I just wanna turn a column into a variable

solemn ridge Mar 19, 2020, 11:40 AM

#

You can't

#

That easily

torn sphinx Mar 19, 2020, 11:40 AM

#

You need to fetch that column from the database first

solemn ridge Mar 19, 2020, 11:41 AM

#

Using insert, update

#

Is very unreliable

#

I take it you have one of these:

async with aiosqlite.connect(...) as db:
  ### stuff

db = await aiosqlite.connect(...)

calm charm Mar 19, 2020, 11:42 AM

#

like i have a column named item_id. and I wanna run: ```py
if item_id == 1:
name = 'pill'

#

ye

#

2nd 1

solemn ridge Mar 19, 2020, 11:42 AM

#

Ok

#

So your model is going to be this

calm charm Mar 19, 2020, 11:42 AM

#

the fr3ak is a model

solemn ridge Mar 19, 2020, 11:49 AM

#

class Item(db.Model):
  __tablename__ = 'Items'
  item_id = db.Column(db.Integer, primary_key=True)
  item_name = db.Column(db.String)
  item_description = db.Column(db.String)
  item_icon_emoji = db.Column(db.String)
  name = db.Column(db.String)
  def __init__(self, item_id: int, item_name: str, item_description: str, item_icon_emoji: str, name: str):
    self.item_id = item_id
    self.item_name = item_name
    self.item_description = item_description
    self.item_icon_emoji = item_icon_emoji
    self.name = name

This is your model.
If you want to append a new row to your database you can just do:

new_Item = Item(item_id='...', item_name='...', item_description='...', item_icon_emoji='...', name='...'
db.session.add(new_Item)
db.session.commit()

If you want to query the object, filter by say item_id and update something you can do:

  #Saying we have a variable called given_id which is the id of the item you want to update
  items = Item.query.all()
  for item in items:
    if item.item_id == given_id:
      item_you_want = item
  item = ('update the attributes you want here')
  db.session.add(item)
  db.session.commit()

#

This a complete base for what you want to do

calm charm Mar 19, 2020, 11:50 AM

#

ok

#

ic

solemn ridge Mar 19, 2020, 11:50 AM

#

?

calm charm Mar 19, 2020, 11:51 AM

#

I see

solemn ridge Mar 19, 2020, 11:51 AM

#

Oh

#

Does this cover your needs?

uneven elbow Mar 19, 2020, 11:54 AM

#

Hello everyone! I'm new to this stuff and having problems hosting my django web app on pythonanywhere.com for the past 2 days. I keep getting Error running WSGI application. sqlalchemy.exc.DatabaseError: (sqlite3.DatabaseError) file is encrypted or is not a database. If someone could help me out, I would give sum that I can afford to you as thanks.

cerulean pendant Mar 19, 2020, 12:08 PM

#

@uneven elbow It seems the sqlite file you're using is corrupted somehow

#

did you try reuploading it?

uneven elbow Mar 19, 2020, 12:13 PM

#

I pulled from git, I will try to upload it manually again. Maybe my path is wrong?

cerulean pendant Mar 19, 2020, 12:14 PM

#

no, the error is not related to path, but to a file that is not a database

#

when you get that error, it means sqlite found the file you're pointing it to

#

but just couldn't use it because it is not a database

#

@uneven elbow send the code

#

!paste

delicate fieldBOT Mar 19, 2020, 12:18 PM

#

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.

cerulean pendant Mar 19, 2020, 12:18 PM

#

we can take a look if there is something wrong in it

#

maybe a screenshot of your files in the file explorer will help pinpoint the issue

uneven elbow Mar 19, 2020, 12:18 PM

#

you want the settings.py file?

cerulean pendant Mar 19, 2020, 12:18 PM

#

is it a django site?

uneven elbow Mar 19, 2020, 12:18 PM

#

yes

cerulean pendant Mar 19, 2020, 12:19 PM

#

ok, then that could help too - the file that defines the database

uneven elbow Mar 19, 2020, 12:21 PM

#

https://paste.pythondiscord.com/kutewokejo.py (settings.py)

#

https://paste.pythondiscord.com/ahavatosex.py (error log)

cerulean pendant Mar 19, 2020, 12:22 PM

#

ok, now the screenshot of your files in the file explorer - I'm looking for the db.sqlite3 file

uneven elbow Mar 19, 2020, 12:23 PM

#

https://prnt.sc/rizo1z

Lightshot

Screenshot

Captured with Lightshot

cerulean pendant Mar 19, 2020, 12:27 PM

#

ok, try renaming the db.sqlite3 file to something else, say db.sqlite3.backup

#

django should regenerate the file

uneven elbow Mar 19, 2020, 12:30 PM

#

and reload the site?

#

nothing happens

cerulean pendant Mar 19, 2020, 12:34 PM

#

@uneven elbow no errors?

uneven elbow Mar 19, 2020, 12:35 PM

#

same error log

#

2020-01-19 14:27:27,855: File "/home/MaxxelZ/mysite/main.py", line 32, in <module>
2020-01-19 14:27:27,856: db.create_all()

#

but why is this showing in error log. my django site doesn't have main.py

#

and the path is wrong aswell

#

# To use your own django app use code like this:
import os
import sys
#
## assuming your django settings file is at '/home/MaxxelZ/mysite/mysite/settings.py'
## and your manage.py is is at '/home/MaxxelZ/mysite/manage.py'
path = '/home/MaxxelZ/django_site'
if path not in sys.path:
    sys.path.append(path)

os.environ['DJANGO_SETTINGS_MODULE'] = 'django_site.settings'
#
## then:
from django.core.wsgi import get_wsgi_application
application = get_wsgi_application()

#

this is the wsgi.py file

cerulean pendant Mar 19, 2020, 12:41 PM

#

the error log should change if you rename the file

#

if it didn't change, then you are not running the same code as you are modifying

#

because if the file is not there anymore, it should give a different error

uneven elbow Mar 19, 2020, 12:42 PM

#

I will try to upload everything again

cerulean pendant Mar 19, 2020, 12:47 PM

#

@uneven elbow I still think your db.sqlite3 file is somehow corrupted

#

renaming it should change the error log

#

it has to change

#

if it doesn't, we can't proceed because the log is not being updated or something

#

and that makes it very hard to troubleshoot

uneven elbow Mar 19, 2020, 12:48 PM

#

it didn't change, so I will try to upload everything from begginging

cerulean pendant Mar 19, 2020, 12:48 PM

#

there's no way that renaming the file won't change the log

#

it must give a different error

finite lynx Mar 19, 2020, 2:07 PM

#

When filtering in sqlalchemy is it possible to have a or statement?

#

For example

.filter(username='string' or id='string')

#

Oh wait, I guess I found it

📎 Screenshot_20200319-160933_Chrome.jpg

lusty igloo Mar 19, 2020, 2:33 PM

#

Is it bad if to make my code shorter overall I use f-strings in fetchrow method? To be exact I use it in place "SELECT {variable} FROM..." or is it a thing that won't really matter?

solemn ridge Mar 19, 2020, 2:34 PM

#

it doesnt matter

#

it just makes your code more semantic

#

which is better

lusty igloo Mar 19, 2020, 2:34 PM

#

Its more about
I have same function in 5 files but only 1 thing changes, that is that variable so I though
make 6th file containing Class which takes name of variable and has that function and just import it within those files

#

But thanks

upbeat lily Mar 19, 2020, 2:38 PM

#

I'm pretty sure you shouldn't be using fstrings with databases at all. You should use paramaterisation, not manual interpolation

#

You should be doing this @lusty igloo

 query = 'SELECT * FROM foo WHERE bar = ? AND baz = ?'
 params = (a, b)
 cursor.execute(query, params)

solemn ridge Mar 19, 2020, 2:38 PM

#

Oh I thought it was a string

#

I didnt see it was db related

lusty igloo Mar 19, 2020, 2:41 PM

#

I don't intend to fetch all columns but single column from single row

#

so I have

(f"SELECT {variable} FROM foo WHERE bar = $1",other_var)
``` as the `variable` is the only thing that changes between those files

upbeat lily Mar 19, 2020, 2:43 PM

#

I think as long as variable isn't anywhere close to the user or other I/O, it should be safe

lusty igloo Mar 19, 2020, 2:43 PM

#

It is not

upbeat lily Mar 19, 2020, 2:44 PM

#

I'd still look to avoid using them with SQL though, paramaterisation isn't too difficult

lusty igloo Mar 19, 2020, 2:44 PM

#

its in other class so it looks like

def __init__(self):
  self.func = DBFetch("variable").func``` more a thing

torn sphinx Mar 19, 2020, 4:28 PM

#

_bot.players.aggregate([{"$match":{"rating":"95"}}])
``` Im attempting to aggregate using pymongo but this returns an object

#

how would i obtain the data

#

sorry i have this disease called dumb fuck

#

i got it

finite lynx Mar 19, 2020, 5:36 PM

#

If I would make a new non nullable column for my table with a default value, would all the objects in the table have the default value or would there be a error

uneven elbow Mar 19, 2020, 5:45 PM

#

@cerulean pendant I somehow fixed it by uploading everything from beggining

cerulean pendant Mar 19, 2020, 5:51 PM

#

@uneven elbow good!!

#

I knew there was something wrong with the file - it was corrupted!

#

@uneven elbow What I find strange is that the error message didn't change from "File corrupted" to "File not found" when you renamed it

#

but whatever

uneven elbow Mar 19, 2020, 5:52 PM

#

But guess what

#

theres a new error when I try to make post request. CSRF verification failed. Request aborted. Posted about this problem on django discord and they couldn't see whats wrong

cerulean harbor Mar 19, 2020, 7:29 PM

#

hey

languid flame Mar 20, 2020, 10:11 AM

#

hello

#

I am a beginner

#

want a simple easy database that is online

#

Easy to acess

#

with low latency

calm charm Mar 20, 2020, 10:37 AM

#

sqlite3

torn sphinx Mar 20, 2020, 11:59 AM

#

I have this MT Dataset, what can be done with such dataset, as a Data scientist, what questions can be asked regarding this dataset, what can be the yields, what to analyze, etc.

So far I thought to make a classification on age, gender.

To find the reasons why patients undergo, surgery, allergy tests, etc. what else can be done with it, any suggestions, please?

Any suggestion is appreciated.

📎 MT_DATASET.PNG

mild haven Mar 20, 2020, 3:10 PM

#

should I use sqlite3 or pymysql

charred fractal Mar 20, 2020, 9:56 PM

#

https://pastebin.com/Vxg1RJNY
I need help on making it send to each user that is in quiz to send to the user's dm
Updated: Error: ```Ignoring exception in command quiz:
Traceback (most recent call last):
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
ret = await coro(*args, **kwargs)
File "c:\Users\User\Desktop\FlippingFlamingo\cogs\quiz.py", line 93, in quiz
await message.author.send(f"{Question}")
AttributeError: 'int' object has no attribute 'send'

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

Traceback (most recent call last):
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\discord\ext\commands\bot.py", line 892, in invoke
await ctx.command.invoke(ctx)
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\discord\ext\commands\core.py", line 797, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\User\AppData\Local\Programs\Python\Python36\lib\site-packages\discord\ext\commands\core.py", line 92, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'int' object has no attribute 'send'

Pastebin

[Python] Quiz Command. - Pastebin.com

rain wagon Mar 20, 2020, 10:25 PM

#

@charred fractal maybe you should use bound statements instead of concencation

#

just saying

charred fractal Mar 20, 2020, 11:13 PM

#

What do you mean @rain wagon ?

torn sphinx Mar 21, 2020, 6:50 AM

#

So I am approaching on migrating to a new db lib

#

Motor for MongoDB

#

What do I do to start?

#

I'm reading the docs right now

languid flame Mar 21, 2020, 4:13 PM

#

Hi

#

I need a database for a discord bot

#

I will use AWS DB

#

SHould I use postreg or MYSQL

#

i will actually ise postreg

#

I have databse exp

#

None with python

torn sphinx Mar 21, 2020, 5:38 PM

#

So I need a basic intro to using databases with discord.py. If you can, please redirect me to a video or a document that explains everything, thank you!

#

PS, I've already downloaded SQLite if that's any help.

#

nvm

torn sphinx Mar 21, 2020, 6:24 PM

#

Does anyone know what this error means

#

pymongo.errors.OperationFailure: not authorized on feudalmod to execute command { find: "modlog", filter: { guild_id: 674386972898361354 }, limit: 1, singleBatch: true, lsid: { id: UUID("91aa7d43-8403-4463-b494-e97fce4e37aa") }, $clusterTime: { clusterTime: Timestamp(1584814269, 1), signature: { hash: BinData(0, B26D7DF11DC80B5F34CD42A2EA0E95361F9A5C87), keyId: 6762369017630949377 } }, $db: "feudalmod", $readPreference: { mode: "primaryPreferred" } }```

#

I looked it up and it basically means that you are missing permissions

#

But I have a url that i connect to but it allowed me to use it for a while then it started giving me this error

warped sequoia Mar 21, 2020, 7:10 PM

#

i don't understand why my database is sorting the bottom 3 zeros after like it's a string, but when i copy paste the other 0 on top of them, they still do it

📎 e9511384867de22324dab89aa716b9e9.png

bold light Mar 21, 2020, 7:22 PM

#

How can I have a team and a player reference eachother, ie. I want to have a teamlist for the player that has a list of teams they are a part of and I also want to have a list of all the players that are on a team as part of the team class. I am trying to do this in Mongo. Ty in advance.

pure cypress Mar 21, 2020, 8:09 PM

#

@finite lynx Which dialect are you using?

#

E.g. SQLite, Postgres, MySQL, etc

#

I am honestly not familiar with SQLAlchemy. Maybe it abstracts it away so the behaviour is consistent across all of them.

#

But there are dialects, if not all of them, that will update existing rows with the default value.

finite lynx Mar 21, 2020, 8:41 PM

#

Ok, it is SQLite

finite lynx Mar 21, 2020, 9:04 PM

#

Ty

copper echo Mar 21, 2020, 9:59 PM

#

        query = "INSERT INTO guilds (guild_id, register_message, register_emoji, register_role) VALUES ($1, $2, $3, $4) " \
                "ON CONFLICT (guilds.guild_id) " \
                "DO UPDATE SET register_message = $2, register_emoji = $3, register_role = $4 WHERE guild_id = $1"
``` so this give me error

#

Command raised an exception: PostgresSyntaxError: syntax error at or near ")"

#

i i use ON CONFLICT (guild_id) instead of following i get Command raised an exception: AmbiguousColumnError: column reference "guild_id" is ambiguous

#

if you know how to help me please ping, because im out of ideas

finite lynx Mar 21, 2020, 10:53 PM

#

Are primary_keys always be the same counting upwards or can you change that?

half gyro Mar 21, 2020, 10:56 PM

#

@finite lynx primary keys don't have to be numbers at all

finite lynx Mar 21, 2020, 10:57 PM

#

Ok

#

Ty

pure cypress Mar 21, 2020, 10:58 PM

#

@copper echo I believe ON CONFLICT (guild_id) is the only way and you'd have to prepend the fields with the table name in the SET clause to avoid the ambiguity error.

#

And, for what it's worth, in the SET clause you can access values being inserted with excluded.field_name but I guess using $1 etc is fine.

copper echo Mar 21, 2020, 10:59 PM

#

so i have to use excluded.guild_id instead of that?

#

im not the best in sql

pure cypress Mar 21, 2020, 11:00 PM

#

For example, either guilds.guild_id = $1 or guilds.guild_id = excluded.guild_id

copper echo Mar 21, 2020, 11:00 PM

#

oooh, so you think problem is in WHERE clause?

pure cypress Mar 21, 2020, 11:01 PM

#

Yeah probably

copper echo Mar 21, 2020, 11:01 PM

#

ill try later thank you!

#

also can i ping you if it wont be this?

pure cypress Mar 21, 2020, 11:01 PM

#

It doesn't know if you want to be checking the old guild id or the new one

#

So it says it's ambiguous

copper echo Mar 21, 2020, 11:01 PM

#

ye makes sense

pure cypress Mar 21, 2020, 11:01 PM

#

Yeah you can ping me for that though if that doesn't work I'm out of ideas honestly 😅

finite lynx Mar 21, 2020, 11:35 PM

#

Woild that be a good idea for making the if for example for users a random arrangement of letters and numbers and just saving the primary key to a different named column?

pure cypress Mar 22, 2020, 12:44 AM

#

Sounds like you're thinking of a UUID

#

But why wouldn't a plain autoincremented integer suffice for a user ID?

celest zodiac Mar 22, 2020, 12:46 AM

#

@pure cypress One scenario where you wouldn't use an autoincremented integer is if you have, for instance, a horizontally sharded database with a very high rate of inserts

#

If you use an ID column, you need to have one node as a source of truth for the value of that column universally

#

A UUID can be keyed to, say, the shard ID, and so is guaranteed to be unique

#

If you need that one node as a source of truth for anything, that means any insert operations are going to block until you get that ID from that node

#

With a UUID you don't need that, since the right kind of UUID can guarantee uniqueness across all the nodes

#

But if you just have one node, then an ID column is fine.

pure cypress Mar 22, 2020, 12:48 AM

#

Neat. Makes sense. Wouldn't have considered that since I haven't ever operated on such a scale.

celest zodiac Mar 22, 2020, 12:49 AM

#

I found out about this the other day when doing research about ID columns. I'm working on a project that's basically a personal wiki, and I considered exactly the same question myself. For what I'm doing, an autoincremented integer is fine because it isn't meant to scale.

finite lynx Mar 22, 2020, 7:18 AM

#

@pure cypress Yeah UUID I guess, and so generating that for each user so there wouldn't be a possibility for a user to loop through all users

#

Can I for example generate it using the UUID model and them save it to a string field in my database?

#

But maybe I should find something less hardcore for this use case, because I don't think I need a 128bit id for my user

#

uuid.uuid4().hex[:8]

#

I could do this, when generating uuids like this should I better still make sure that there isn't a equal uuid that exists already?

reef hawk Mar 22, 2020, 10:42 AM

#

are database an o1 lookup? E.g. search through a column for a certain ID with a lookup instead of scrolling through the entire column for that column

#

if not, is it possible to make it an o1 lookup

torn sphinx Mar 22, 2020, 12:18 PM

#

heya, how do I make a list dataset in an SQL database?

languid flame Mar 22, 2020, 1:02 PM

#

hello

#

with asuncpg

#

i used fetch

#

this is the response

#

[<Record name='dydy' age=10>]

#

how do I manipulate this to get the data in name

#

and the age

#

like is there x['name']

#

or something

rich trout Mar 22, 2020, 1:18 PM

#

results[0].name

#

if use use .fetchone() it will give you the result rather than a list of results

#

@finite lynx The reason uuid4 is so many bits is precisely so you don't have to worry about checking for an equal uuid

#

Your database should have a uuid field--MYSQL and POSTGRES both support them, which will help you avoid the severe penalty of string keys.

#

For more reading on the reasons and details behind uuid's (and the various types), see https://stackoverflow.com/questions/703035/when-are-you-truly-forced-to-use-uuid-as-part-of-the-design/786541#786541

Stack Overflow

When are you truly forced to use UUID as part of the design?

I don't really see the point of UUID. I know the probability of a collision is effectively nil, but effectively nil is not even close to impossible.

Can somebody give an example where you have no

#

To put these numbers into perspective, one's annual risk of being hit by a meteorite is estimated to be one chance in 17 billion, equivalent to the odds of creating a few tens of trillions of UUIDs in a year and having one duplicate. In other words, only after generating 1 billion UUIDs every second for the next 100 years, the probability of creating just one duplicate would be about 50%.

#

Really the impact of using a uuid in your design is not that significant, so you really shouldn't worry about it if you've decided to use it. If you're going to have problems with it, you were already going to have problems and need to look at your problem domain much more closely.

#

@reef hawk This depends on what you mean by O(1). Realistically, an INTEGER PRIMARY KEY lookup is the absolute fastest you can expect from a database and most other software in general

#

This is because the rows of a particular table are of a fixed size, and thus if you know you want item, say, 52, then it will be at rowsize*52, and you can immediately grab and send it.

#

For other columns/types, once you or the db determines there should be something called an INDEX, you get O(logn) or better performance due to it having a preconstructed decision tree of what values go to what rows. It's only for unindex, unsorted columns that databases will do a scan, or for some kinds of comparisons. For example, it's easy to tell whether, say, the primary key is less than 10. That's rows 1-9, which are all contiguous and immediately found. Finding all rows that have a "type" of 6 (like in the case of a foreign key) can be fast with an index. All the values for 6 are together, so it knows it needs to give you rows, 1, 2, 6, 9, etc. If there is no index, though, it has to check every row by definition. But it's also very fast at this.

#

There are a few exceptions, one of which is STRING/VARCHAR columns. Because these kinds of columns have variable length and LOTS of data assigned, they can be very unwieldy to work with. You should avoid using them for querying. As an example, grouping a database's list of records based on first name is expensive. It needs to keep track of every (Variable length chunk of data) visited, and sort them, and then collate them into a result. It's faster than python doing the sorting because its got lots of internal optimizations, but if you're not trying to get a sorted-by-name listing then there are much better options.

languid flame Mar 22, 2020, 3:06 PM

#

thanks

#

What should I use to host a PostregSQL database\

#

For a discord bot that will very frequently acess the database with asyncpg

#

I was planning on using AWS RDS

#

is there a better alternative?

grim lily Mar 22, 2020, 3:13 PM

#

I have a Project class, User class and AssociationUserToProjectClass

class AssociationUserToProject(SqlAlchemyBase):
    __tablename__ = 'user_to_project'
    num = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    member_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('users.id'))
    project_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('projects.id'))
    project_role = sqlalchemy.Column(sqlalchemy.String)

I want to project_role be accessible from User, but it's not, and i don't know to do it.
Can anyone help me,please?

brisk oar Mar 22, 2020, 4:55 PM

#

in aiosqlite3 con.row_factory = aiosqlite3.Row, raise ```py
Traceback (most recent call last):
File "C:\Users\PC\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
ret = await coro(*args, **kwargs)
File "C:/Python/smth/test.py", line 25, in db
self.bot.db = await data_base.Data(self.bot.state['js'], self.bot.state['db'])
File "C:\Python\smth\cogs\utils\data_base.py", line 9, in call
fn = obb.init(*args, **kwargs)
File "C:\Python\smth\cogs\utils\data_base.py", line 20, in init
self.con.row_factory = db.Row
ValueError: no active connection

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

Traceback (most recent call last):
File "C:\Users\PC\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\bot.py", line 892, in invoke
await ctx.command.invoke(ctx)
File "C:\Users\PC\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 797, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\PC\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 92, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: no active connection```

#

class AsyncMeta(type):
    async def __call__(cls, *args, **kwargs):
        obb = object.__new__(cls)
        fn = obb.__init__(*args, **kwargs)
        if inspect.isawaitable(fn):
            await fn
        return obb


class Data(metaclass=AsyncMeta):
    def __init__(self, js, file):
        self.file = file
        self.js = js
        self.con = db.connect(self.file, check_samethread=False)
        self.con.row_factory = db.Row```

finite lynx Mar 22, 2020, 5:32 PM

#

@rich trout Would that still hold if I make it 10 characters long though? As for a users Id I don't that complex of an Id

rich trout Mar 22, 2020, 5:38 PM

#

Less so, but there is a uuid type you should be using in the database

waxen iron Mar 22, 2020, 6:33 PM

#

How relevant is motorengine right now? The library has not been updated for a very long time. Will it be compatible with the latest version of pymongo? https://motorengine.readthedocs.io/en/latest/

#

Or maybe someone knows another good async orm for mongo?

torn sphinx Mar 23, 2020, 12:10 AM

#

How would one convert

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

to aiosqlite3?

calm charm Mar 23, 2020, 1:47 AM

#

ummm

#

    result = function()
sqlite3.OperationalError: database is locked```

#

wdym

random sandal Mar 23, 2020, 1:48 AM

#

Hi, currently im learning ER Diagram, so i am a beginner and I need help.

#

📎 ER_Diagram.png

#

A Virus has a name, an ID, and a date of discovery.
A Viral Disease has a name, an ID, and a date of discovery as well.
A Viral Disease is caused by a Virus and you can assume that a Virus causes one Viral Disease.
Viral Diseases are distinguished between being fatal and nonfatal.
Viruses infect. Each citizen in this database has been infected. They, therefore, have at least one Viral Disease.
A citizen has a name, a BSN, and an age.

#

Can someone say if i am doing right or wrong and how should i do point 4?

#

Unfortunately, fatal Viruses have killed and are killing people. When that occurs, a time-of-death is noted.

#

anyone tips how i should do step 7 ?

random sandal Mar 23, 2020, 2:36 AM

#

@pure cypress or @rich trout any tips for this new guy

rich trout Mar 23, 2020, 2:40 AM

#

I’m out on the water ATM, but a time of death is related to a given person, and perhaps a cause of death

#

If that cannot be inferred by the tables you already have

#

Does your model handle multiple infections?

#

As described, it does not

#

If not, then you can probably assume that the disease the person had at their time of death was the disease that killed them

#

Therefore, TOD is only logically keyed to person

#

@random sandal if you can follow that logic the answer shouldn’t be too far away

reef hawk Mar 23, 2020, 5:32 AM

#

@rich trout I'm usually using bigint primary key as my lookup

#

I guess that'd be the fastest way to retrieve it? for ex

SELECT col_1, col_2 FROM datatable WHERE p_key = $1 (value)

languid flame Mar 23, 2020, 5:48 AM

#

Okay

#

Is AWS good for postrge hosting

#

I want cheap but fast hosting

#

what would ypu recommend

north niche Mar 23, 2020, 7:19 AM

#

@languid flame DigitalOcean

languid flame Mar 23, 2020, 7:43 AM

#

Thanks➡️

#

@north niche I am using windows

#

Is digital ocean also good for bots

#

I want to use only one platform

north niche Mar 23, 2020, 8:01 AM

#

DigitalOcean offer VPS and Kubernetes for hosting @languid flame

languid flame Mar 23, 2020, 8:54 AM

#

But its 20$ per month

#

any cheaper alternatives?

#

15$ for the database and 5$ for the droplet

north niche Mar 23, 2020, 9:24 AM

#

You can host DB in droplet too @languid flame

random sandal Mar 23, 2020, 10:43 AM

#

@rich trout How do I point 4 making a distinguish being fatal and non fatal?

rain abyss Mar 23, 2020, 12:00 PM

#

Hi, so I made a database with sqlite but now I need something for production. I was planning on using mySQL, but I don't really understand how I would be able to interact with it externally. Do I need php? Can I use python? I've only just learned how to work with databases so if someone could help me with this I would greatly appreciate it

rich trout Mar 23, 2020, 1:08 PM

#

You can use python--see mysql-connector for python

#

@reef hawk yep

rain abyss Mar 23, 2020, 2:09 PM

#

I'm taking a look ay my-sql connector and it seems like a good choice for accessing the database on my local machine, but if I wanted to connect to it from outside of my network what would I do?

Would it be inefficient to create something like a flask server that then runs mysql connector commands? @rich trout

rich trout Mar 23, 2020, 2:10 PM

#

You can in fact connect to a database on a remote machine. It's not usually the best of ideas because exposed databases are often targets (you'll see services like amazon have an "internal network" and an "external network" for these kinds of reasons)

rain abyss Mar 23, 2020, 2:12 PM

#

Sorry I should clarify more. I have made an application that interacts with the database since the users aren't really code savvy, so at this point it would be discord bot > sql server. Say I decide to host it with a droplet, should I do discord bot > flask server on droplet > run sql commands?

rich trout Mar 23, 2020, 2:13 PM

#

No need

#

Either you've got the msyql server on the same machine as the bot--at which point it's identical to local, even when accessing it as it is in fact on localhost for connections, or it's on a different machine in the same cloud, where you should look for the "private address" to set up a direct server connection for

#

I would suggest having them on the same machine--you're unlikely to need more and it's usually cheaper

rain abyss Mar 23, 2020, 2:15 PM

#

So say I decide to have both on the same machine, if someone runs a command, I can just use mysql.connector to get that data? That would be incredibly simple

#

Because that's what I'm doing in testing right now with sqlite, I don't know why this didn't even cross my mind

#

I don't know why I thought I would have to over complicate things. Thanks for your help bast, really appreciate it

rich trout Mar 23, 2020, 2:15 PM

#

👍

rain abyss Mar 23, 2020, 2:42 PM

#

Hey @rich trout, another quick question. So I'm reading that it can take some time to open and close connections to the mysql database, so do you think I should open the connection when I start the discord bot, and then pass the connection object to my specific functions when I call them?

rich trout Mar 23, 2020, 2:42 PM

#

That is one way to do it, but I would suggest something called a connection pool if there's a nice one provided

#

https://github.com/aio-libs/aiomysql may be worth looking into

GitHub

aio-libs/aiomysql

aiomysql is a library for accessing a MySQL database from the asyncio - aio-libs/aiomysql

rain abyss Mar 23, 2020, 2:43 PM

#

Great, I'll check that out

random sandal Mar 23, 2020, 4:36 PM

#

@rich trout at point 4 do i need to make a new entity with fatal and non fatal as properties? is this the good way to slove that?

rich trout Mar 23, 2020, 4:54 PM

#

is there some reason you can't just.. add a new property to the entity on the map?

vast parrot Mar 23, 2020, 6:23 PM

#

Hello, i use pyrebase to store and push data to firebase.
But how can store only the value of the key?
Example in json:

{
  "name": "Morty",
  "age": 33,
  "hobby": "Skating",
  "wife": false
}

I want to store the hobby value how can i do it?

lofty summit Mar 23, 2020, 8:09 PM

#

my_dic["hobby"]

#

nvm

gentle sparrow Mar 23, 2020, 9:54 PM

#

Can anyone dumb down what migrations in Django are

#

like DUMB down to the core

void otter Mar 23, 2020, 10:15 PM

#

migrations aren't really what they say they are

#

you would say it is used for migrating data?

#

it's used for preserving data when you change table definition

#

let's say you have some certain table which holds some data

#

and you edit, add or remove a column

#

it would pretty much break the db if data stays the same

#

migration is a way of fiddling with table without losing data

vast parrot Mar 23, 2020, 10:17 PM

#

lol i solve that problem

gentle sparrow Mar 23, 2020, 11:13 PM

#

I see

#

Thanks

#

So

#

Let's use a real life example

#

Usernames and passwords

#

Say you wanted to permanently ban a user

#

What you would do is delete their column right

#

@void otter How would that look like in a migrations and models file

#

What would i need to edit to delete that user

torn sphinx Mar 24, 2020, 1:06 AM

#

So how would I make it so that there’s one big chunk (a guild) and inside that chunk, there are users. Each user would usually have a None for the amount of warns they have, but when they’re warned, it would give them a warn. How would I make this? Would this end up with me making a new table for each guild or something?

#

{ [“guild_id”]{ [“user_id”]{ [“warn”]{ [“mod (who warned”]{ [“date”]{ [“reason”]{ } } } } } }

#

Somewhat this?

#

~~But that looks like JSON to me :/~~

fallow ingot Mar 24, 2020, 1:18 AM

#

How can i use a local mysql database? I am using right now an external online database, but these hosts rejects the connection if i start my flask project from like pythonanywhere. Is there a module for a local database or something like that?

calm charm Mar 24, 2020, 4:26 AM

#

~~is there a command or like a system that automaticall backups your guys db~~ Is there a way to automatically backup a database through discord.py or any python script

languid flame Mar 24, 2020, 7:48 AM

#

hello

#

asyncpg.exceptions.TooManyConnectionsError: remaining connection slots are reserved for non-replication superuser connections

#

what is this error

#

Is it because my bot connects and disconnects a lot

toxic rune Mar 24, 2020, 9:13 AM

#

I might be completely wrong here but, are you gracefully shutting down your bot? I had to deal with a similar problem where I was forcing it to shut down and it wouldn't release the connections so I had to restart the PostgreSQL service.

void otter Mar 24, 2020, 9:48 AM

#

@gentle sparrow each database and each framework has it's own implementation of migration

#

only one i can remember of the top of my head if flask_migrate

#

a lot of people use libraries tied with their framework of choice

languid flame Mar 24, 2020, 10:08 AM

#

oh no

#

I was just spam connecting

#

realised rather than reconnetc