databases

[celest zodiac]

Yeah, don't do that :D

[vague hawk]

lol

[celest zodiac]

Look into SQLite, and perhaps use an ORM to go with it to make managing the database a little more elegant

[vague hawk]

orm.. ill look it up lol

Thanks for the suggestions guys

[torn sphinx]

So, I know that collation means... how things are indexed and order, similarly to how a library organized itself or how letters are in order so that we have a concept of "alphabetical"

shellmancer_dev-# \d user
                                          Table "public.user"
     Column     |            Type             | Collation | Nullable |             Default              
----------------+-----------------------------+-----------+----------+----------------------------------
 id             | integer                     |           | not null | nextval('user_id_seq'::regclass)
 user_name      | character varying(120)      |           |          | 
 email          | character varying(120)      |           | not null | 
 image_file     | character varying(40)       |           | not null | 
 password       | character varying(60)       |           | not null | 
 date_created   | timestamp without time zone |           | not null | 
 date_confirmed | timestamp without time zone |           |          | 
 is_over_18     | boolean                     |           |          | 
 is_admin       | boolean                     |           | not null | 
 is_verified    | boolean                     |           | not null | 
Indexes:
    "user_pkey" PRIMARY KEY, btree (id)
    "user_email_key" UNIQUE CONSTRAINT, btree (email)
Referenced by:
    TABLE "gamemaster" CONSTRAINT "gamemaster_gamemaster_id_fkey" FOREIGN KEY (gamemaster_id) REFERENCES "user"(id)
    TABLE "player" CONSTRAINT "player_player_id_fkey" FOREIGN KEY (player_id) REFERENCES "user"(id)

But what is meant here by the column "collation"

[celest zodiac]

@torn sphinx Which database is this, just so I know?

[torn sphinx]

postgres

sorry for delay

[celest zodiac]

@torn sphinx OK, I'm not as familiar with Postgres, but I think what I'm seeing here is a cousin to the way other DBs work

The "collation" column here is a property of each column type that indicates how that column's data is to be sorted

Database collations are usually for the sake of alphabetizing or sorting according to the way different regions or locales handle such things

[torn sphinx]

ah, ok

the use of that word makes sense then

[celest zodiac]

Nothing in that column probably means "use the database's default collation"

[digital moon]

hello, what is best SQL ide for storing data on discord servers?

[torn sphinx]

when you make web applications, do you use an ORM to create the table and relationships? or do you create the tables in the database first?

[upbeat lily]

Normally you use an ORM to define the tables

and a migration tool (Alembic for SQLAlchemy) to make changes to your schema without losing data

[torn sphinx]

why a migration tool if you're not migrating anything?

[upbeat lily]

At some point during development, you're schema is gonna change - if you already have data, you don't want to lose that data in the process. A migration tool is a thing that lets you change your schema safely

[torn sphinx]

so let's say you have a web application similar to Meetup and it's in place for like a full year. then you want to rename a column in a table, and drop a column in another table. how do you do that?

(if not through a SQL IDE)

Alembic?

[upbeat lily]

You'd use the migration tool. Its been too long, and I never learned them well enough to give you the exact code. But roughly what you'd do is make the changes to your schemas, then you'd run something like makemigrations with Alembic or the Django migrations tool

[torn sphinx]

hmm ok cool thanks i'll check it out

[toxic rune]

What would be the best method to develop a search system / feature for an application (or in this case, a bot) that searches data in a database? Right now what I do is kind of building dynamic SQL but I'm not sure if there's a better way, especially when you deal with more filters and such.

[little raven]

Hmm should I believe this linter, as I saw I should do connection.cursor() inside aiopg

This is the connection

This is the method which returns the connection

[native silo]

I am getting the error: No such column: context_set
The problem is just that I definetly have that column in my db. I have verified it with DB browser.

This is my code for creating the db:

def intent_db_make():
    if isfile(intent_db_path):
        intent_db_backup()

    # opening database
    conn = _sqlite3.connect(intent_db_path)
    c = conn.cursor()

    c.execute("""CREATE TABLE intents (
                row_num integer,
                tag text,
                patterns text,
                responses text,
                intent_type text,
                response_type text,
                context_set text,
                context_filter text,
                mood text
    )""")

    # apply changes and close database
    conn.commit()
    conn.close()

and this is the code for adding data to the db (this is where the error orcurs)

for row_number, i in enumerate(intents['intents'], row_num):
            print("-----------------------")
            print("Current row number is {}".format(row_number))

            # extracting data from file and adding to variable
            tag = i['tag']
            patterns = '&/&'.join(i['patterns'])
            responses = '&/&'.join(i['responses'])
            intent_type = i['intent_type']
            response_type = i['response_type']
            if 'context_set' in i:
                context_set = i['context_set']
            else:
                context_set = 'N/A'

            if 'context_filter' in i:
                context_filter = i['context_filter']
            else:
                context_filter = 'N/A'

            if 'mood' in i:
                mood = i['mood']
            else:
                mood = 'N/A'

            if tag in tag_list:
                print("Tag: '{}' already exists in database".format(tag))
                pass
            else:
                # adding the data to the database
                c.execute("INSERT INTO intents VALUES (:row_num, :tag, :patterns, :responses, \
                                :intent_type, :response_type, context_set, :context_filter, :mood)", {
                    'row_num': row_number,
                    'tag': tag, 'patterns': patterns, 'responses': responses,
                    'intent_type': intent_type, 'response_type': response_type,
                    'context_set': context_set, 'context_filter': context_filter,
                    'mood': mood})

                conn.commit()
                print("Data added to row")

        # apply changes and close database
        conn.commit()
        conn.close()

The error gets thrown at the buttom where it inserts it into the db

[glad bobcat]

Heya. I’m trying to use WITH ROLLUP on a MySQL query made through SQLAlchemy.
Unfortunately, the rollup implementation of sqlalchemy.func doesn’t produce the right syntax for the MySQL dialect.
Any idea how to fix that, or find a workaround to issue the right query at the very least?

[flint walrus]

Hey. I am trying to write a simple scraper, which requires saving data in a database and I couldn't find any design patterns. What would be the correct way to interact with a sqlite db in the scraper? Would I make some sort of a wrapper with methods like add, edit, delete or would I create connections and execute the commands directly? Also where should I store the sqlite commands for creating a database, altering tables and so on?

Please tag me in your reply.

[odd scaffold]

Hello! I'm looking for a database similar to enmap for JS where the interface is as if it were a python object, but it is actually persistant

does anyone know a database system like that?

[gentle sparrow]

https://gyazo.com/390b703550547f8dbd4245cf2dc647d3

Anyone know how to fix this error?

[lament heath]

        existing_user = User.query.filter_by(name = form.username.data , email = form.email.data).first()
        print(existing_user)
        if existing_user:
            print("This is bad news")
            return make_response(f"The name or the gmail you enter is already created")```

Im trying to check if the user who want to sign up used an account or name thats already exists

but this is not working!

any help pls?

Why the User.query is not working ?

[violet mountain]

so when i use sql in python i want to get a whole row where the data is equal to something, i get the whole row but i want to have the values separate so its like 20, 30 etc not (20,), (30,) how can i do that?

[celest zodiac]

@odd scaffold SQLite comes with Python and might serve the same job

@violet mountain Can you explain a little better? I'm not sure what you're striving for or how your current querying works

[violet mountain]

okay

so i want to get the id where the number plate and fixed are the same

[celest zodiac]

@lament heath "not working" could mean anything. Is it crashing? erroring? doing nothing?

[violet mountain]

if result:
            curs.execute('''SELECT ID FROM Faults WHERE Number_Plate=? AND Fixed=?''',(reg.get(),fixed))
            hmm = curs.fetchall()
            print(hmm[1])
``` it will print out "(30,)" but i just want it to print out "30"

[celest zodiac]

@violet mountain When you retrieve records they're as a tuple, so you can just take each row and get element 0 from it if you only have one element

[violet mountain]

wdym

im kinda confused on it

[celest zodiac]

hmm[1][0]

try that

[violet mountain]

k

[lament heath]

@celest zodiac I don't know if the problem is coming from it because I don't know how to check if db.sesson.add is working anyway

def signup():
    form= Signup()
    print("hi")
    if request.method=="POST":
        existing_user = User.query.filter_by(name = form.username.data , email = form.email.data).first()
        print(existing_user)
        if existing_user:
            print("This is bad news")
            return make_response(f"The name or the gmail you enter is already created")

        else:
            
            user= User(form.username.data,form.email.data,form.password.data)
            print(user)
            db.create_all()
            db.session.add(user)
            db.session.commit()

            print("Saved")
            flash('Thanks for registering')
            return redirect(url_for("home"))```

Here's the code for the sign up

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), unique=True)
    email = db.Column(db.String(100), unique=True)
    password=PasswordField(db.String(50))

    def __init__(self,name,email,password):
        self.username=name
        self.username=email
        self.username=password
        ```

[violet mountain]

that worked @celest zodiac why does it work like that?

[celest zodiac]

@lament heath Can you inspect the database directly'

@violet mountain when you return a record, even if it's only one column, it's expressed as a tuple or some other iterable, because there could be any number of columns

[violet mountain]

oh kk so i just need to always have [0] ty#

[lament heath]

I don't know how acutally

[celest zodiac]

@lament heath what DB are you using?

[lament heath]

sqlalchemy

[celest zodiac]

No, the underlying DB, not the ORM

[lament heath]

Do you mean this flask_sqlalchemy sorry im new to databases and these stuff

[celest zodiac]

@lament heath I'm talking about the underlying database itself -- is it SQLite? MySQL?

[lament heath]

oh sqlite

[celest zodiac]

OK

there's actually a standalone GUI you can use to inspect SQLite databases

https://sqlitebrowser.org/

I do a lot of SQLite stuff and it's incredibly handy

so this way you can look directly in tables, etc. and make sure things are being recorded as intended

[lament heath]

oh thanks a lot i will check this out

[manic patio]

probably asked a lot here but what would be the best integrated/supported in python, postgresql or mysql/mariadb

[dusty helm]

hey guys, i'm haivng a little problem with SQLAlchemy (flask), the code is the following:

from flask import Flask, render_template
from flask_sqlalchemy import SQLAlchemy
import sqlite3

db_file = 'spacegame.db'

conn = sqlite3.connect(db_file)
cursor = conn.cursor()

# Create Table Users

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + db_file
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)


class User(db.Model):
    __tablename__ = "User"
    id = db.Column('id', db.Integer, primary_key = True)
    nickname = db.Column('nickname', db.String(100))
    email = db.Column('email', db.String(100))
    password = db.Column('password', db.String(20))

ERROR:

    id = db.Column('id', db.Integer, primary_key = True)
TypeError: Column() got an unexpected keyword argument 'primary_key'

but primary key show be "expected", right?

[lament heath]

id = db.Column("id",db.Integer, primary_key=True)

[dusty helm]

same error

[lament heath]

hm maybe the db file

I was getting an error

[dusty helm]

i'll try something

[lament heath]

try to change it to name.sqlite3

[dusty helm]

i deleted the entire class

i think my library is broken

[lament heath]

@celest zodiac I download it but the code is actually not saving any values to databases

def signup():
    form= Signup()
    print("hi")
    if request.method=="POST":
        existing_user = User.query.filter_by(name = form.username.data , email = form.email.data).first()
        print(existing_user)
        if existing_user:
            print("This is bad news")
            return make_response(f"The name or the gmail you enter is already created")

        else:
            name=form.username.data
            email=form.email.data
            password=form.password.data
            db.session.add(User(name,email,password))
            db.session.commit()

            print("Saved")
            flash('Thanks for registering')
            return redirect(url_for("home"))```

And its not showing any errors

[celest zodiac]

Is the save action running?

[lament heath]

its printing saved

and name,email,password have a value also

    id = db.Column("id",db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True)
    email = db.Column(db.String(100), unique=True)
    password = db.Column(db.String(100), unique=True)

    def __init__(self,name,email,password):
        self.username=name
        self.username=email
        self.username=password
        ```

[dusty helm]

solved by:

Reinstalling flask-sqlalchemy

[lament heath]

oh

[dusty helm]

yeah

i guess my library was broken

that was my reaction too

oh
@lament heath

[lament heath]

yes maybe

Nice btw

[dusty helm]

yes, but thanks guys!

[lament heath]

wlc

your db is working

?

[dusty helm]

i must check

but i guess it is

i changed the name of the file to spacegame.sqlite3

it was spacegame.db

[lament heath]

ye did it work?

Im having the bug above

[dusty helm]

it did not solved the issue

but it's not giving me any exception

i'll try to create a table

[lament heath]

yup

[dusty helm]

i'll come back, if it works i'll tell you

[lament heath]

ok nice

[dusty helm]

how to use sqlite3 via shell?

i'm checking videos, they go to the cmd first, create the db

and then they go coding

[lament heath]

there's an app for it

https://sqlitebrowser.org/

serdar just told me about it

[dusty helm]

i'll download itr

[lament heath]

Nice good luck😀

[dusty helm]

i'm really needing

hahaha

[lament heath]

lol

me 2

Im stuck the data is not being saved

[dusty helm]

i never did anything with databases + programming

my dbs were made on excel

hahahah

you commiting?

the connection

commit + close?

[lament heath]

db.session.commit()

but what is close?

[dusty helm]

i guess it's not used for web applications

idk

i wish i knew

really

dbs are fucking complex

[lament heath]

np... Thanks

I didn't go to deep with them

But I don't know why this code is not showing at least one error

Its just not saving

[little raven]

@lament heath what db are you using?

[gentle sparrow]

Anyone know if you can get hacked through 3rd party packages? lol

[celest blaze]

of course

[gentle sparrow]

@celest blaze Are you familiar with Djoser?

[celest blaze]

never heard of it

you can also be hacked through 1st-party code

[gentle sparrow]

What does that even mean?

And I want to just put it out there that my troll alarm is kind ringing

kind of*

[celest blaze]

I meant it more or less literally: the code that you yourself write can have bugs that could be exploited. The famous example is https://xkcd.com/327/

xkcd: Exploits of a Mom

Her daughter is named Help I'm trapped in a driver's license factory.

[gentle sparrow]

my programming iq isn't high enough to understand this meme bro

but i get the memo

But what I was asking is if someone could make an open source package

and install shit on their computer when they install that package

[little raven]

I have tried the a command which requires such

with gives me an AttributeError

coroutine does not have attribute cursor

[dusty helm]

hey guys, i'm really newbie at databases, could someone give me a light

about relations

i'm using sqlite3

i have a game "ready" but it's not using databases

i have my data stored on files

and i want to put it all in databases

so i can fix balancing

i have 4 races at my game

the races share the same properties, like "minerals, population"

things like that

they share all the data

how can it work properly?

[torn sphinx]

Databases are pretty complicated and represent a steep learning curve imo
As I just learned it fairly recently myself, I'm going to offer you some advice on what to start reading first.
You need to understand "The Normal Forms" or "Database Normalization"
https://www.lifewire.com/database-normalization-basics-1019735
You can read about it here or you can watch a video about it, but its pretty important.
The thing you will have to realize is that at first, you will be designing your database only to discover it was missing something you have to add.
But a database is locked in stone. Editing it is very difficult without destroying the data you have in it. So in the beginning, you're going to want to just use a lot of test data to try to get it right.
I think you are right to start with sqlite. You might eventually want a real, online database server but it only makes it harder to understand if you start there imo.
Good luck
@dusty helm

Also, unrelated, I just found the obscure solution to my database problem haha http://blog.code4hire.com/2017/06/setting-up-alembic-to-detect-the-column-length-change/

[torn sphinx]

I'm really excited about the possibilities of a JSON field in pg.

I'm pretty sure that means that you can store data in the database that isn't necessarily structured

Is that right?

https://www.2ndquadrant.com/en/blog/postgresql-anti-patterns-unnecessary-jsonhstore-dynamic-columns/

[solid void]

That's right

[torn sphinx]

I hate Alembic.

Basically what alembic does is assemble a migration file that invariably never works so that I can search stackoverflow for how to edit to make the specific change im trying to make.

[lament heath]

@little raven Im using sqlite 3

[little raven]

I have never used sqlite3 before, but by my experience with modern database python apis, in general there should be a connection.commit() method which you have to do

You should refer to sqlite3 docs 😄

[lament heath]

Yup I put db.session.commit() but it still not working

And the thing is its not giving any errors to let me know why this is happening

Im pretty new to databases and flask

[torn sphinx]

I have a huge database problem right now that I don't feel like writing up the problem yet. It's such a big problem that I have to do good job of writing up the problem before I bother asking it. And Ive been up all night. So Im just gonna get some rest first.

@lament heath are you perhaps forgetting db.session.add(thing_to_add) first?

You don't need that if you are updating something already in the db, but if you are adding something that is not there, you need to use that.

[lament heath]

@torn sphinx I wrote the code here serval times before but I will put it again

Actually nothing is saved

In the database

[torn sphinx]

have you done db.create_all() to initialize the tables in your database?

You dont need that in your code because it only needs to happen one time ever.

[lament heath]

Oh

[torn sphinx]

Usually you do it in flask shell

[lament heath]

I run it on the main code

[torn sphinx]

I dont think that would drop the fields in your database but it might be messing things up. You only run it once to give the database the outline set up in your models. Once its done you shouldnt need to do it again

[lament heath]

Oh

Im trying to open discord from pc

1s

@torn sphinx Im a bit late sry: ```@app.route("/signup", methods=["POST","GET"])
def signup():
form= Signup()
print("hi")
if request.method=="POST":
existing_user = User.query.filter_by(name = form.username.data , email = form.email.data).first()
print(existing_user)
if existing_user:
print("This is bad news")
return make_response(f"The name or the gmail you enter is already created")

    else:
        name=form.username.data
        email=form.email.data
        password=form.password.data
        print(name,email,password)
        usr= User(name,email,password)
        db.session.add(usr)
        db.session.commit()

        print("Saved")
        flash('Thanks for registering')
        return redirect(url_for("home"))




return render_template("signup.html",form=form)```

    _id = db.Column("id",db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    email= db.Column(db.String(100))
    password = db.Column(db.String(100))

    def __init__(self,name,email,password):
        self.username=name
        self.username=email
        self.username=password```

I don't know why

I cant see any problem

its none

[torn sphinx]

have you tried printing usr to see if there is a usr object there? Also, you are saving passwords in plaintext. I know that is not your current problem but you cant do that.

[lament heath]

I need to hash them

[torn sphinx]

Yeah worry about that later.

Um so from your db viewer I can see that you have your tables created.

print something at the beginning of the else statement where it starts saving stuff to the user

To see if its even getting there

[lament heath]

<user (transient 72462424)>

ok

[torn sphinx]

mm, ive seen transient before. I forget what that means.

[lament heath]

is it an object?

[torn sphinx]

yeah I think it means that its an object that isnt added yet.

Try to print it after the add but before the commit

[lament heath]

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked
[SQL: INSERT INTO user (name, email, password) VALUES (?, ?, ?)]
[parameters: (None, None, None)]
(Background on this error at: http://sqlalche.me/e/e3q8)```

its giving me this error now

[torn sphinx]

SQLite is meant to be a lightweight database, and thus can't support a high level of concurrency. OperationalError: database is locked errors indicate that your application is experiencing more concurrency than sqlite can handle in default configuration. This error means that one thread or process has an exclusive lock on the database connection and another thread timed out waiting for the lock the be released.

Do you have mutliple instance of the database open?

Perhaps in a viewer, in a terminal, in your app

[lament heath]

oh

[torn sphinx]

Close that viewer and any terminals you have open connected

and try it then

[lament heath]

yes it closed the viewer

I printed the usr after add

<user (transient 75400264)>

[torn sphinx]

honestly, Im not sure. I dont use sqlite but it should be very simple.

Does it get to thanks for registering and redirecting home?

[lament heath]

It redirect the user to home yes

[torn sphinx]

def __repr__(self):
        return f"User({self.id}, {self.name}, {self.email})"

Add this to your user model as a method.

Look at the print again

[lament heath]

ok

[torn sphinx]

You might need to use self._id

I just noticed you labeled it that

[lament heath]

None,None

yes deleted it

[torn sphinx]

Yep thats the problem

the data is never getting in there

[lament heath]

oh

why?

[torn sphinx]

Not sure lets see

[lament heath]

like the name,email, and password

I created have values

            print("hi")
            name=form.username.data
            email=form.email.data
            password=form.password.data
            print(name,email,password)
            usr= user(name=name,email=email,password=password)
            
            db.session.add(usr)
            print(usr)
            db.session.commit()

            print("Saved")
            flash('Thanks for registering')
            return redirect(url_for("home"))

[torn sphinx]

I got it

User(name=name,email=email,password=passowrd)

Now hash those passwords.

That will work

lol

[lament heath]

is it ? lol

[torn sphinx]

its not working?

[lament heath]

which one?

[torn sphinx]

Damn so your original one said usr= User(name,email,password)

And I over looked that

They have to be keyword args

[lament heath]

so I have to do it like this usr= User(name,email,password)

[torn sphinx]

no

Thios is mine for example

@app.route('/register', methods=['GET', 'POST'])
def register():
    if current_user.is_authenticated:
        return redirect(url_for('home'))

    form = RegisterForm()

    if form.validate_on_submit():
        hashed_pw = ph.hash(form.password.data)
        user = UserAccount(email=form.email.data, password=hashed_pw)

        db.session.add(user)
        db.session.commit()

        send_verify_email(user)

        flash(f"Account created for {form.email.data}"
              + "Check your email to verify account for full functionality. You may login now.", 'success')
        return redirect(url_for('login'))

    return render_template("register.html",
                           title="Register",
                           form=form)

[lament heath]

oh

But I don't see the name

of the user*

or you just forget it?

[torn sphinx]

You mean the model?

[lament heath]

name=form.username.data

[torn sphinx]

Oh, I dont ask users for a name. I verify by email and password.

I let them add an alias after they make an account but its not connected to auth.

In my view, the less data collected that I dont need, the more protected my users are

[lament heath]

oh

[torn sphinx]

But there is nothing wrong with using a username if its important to the app

i just dont need it

[lament heath]

ya I understand

what should I import for the hash password?

pip install argon2-cffi

?

[torn sphinx]

that is what I use yeah

[lament heath]

ok

[torn sphinx]

I have no idea why your thing wont save.

[lament heath]

why its returning None maybe thats the point?

[torn sphinx]

So look

Is there anywhere in your script where you are saying User(name, email, password) Because if you are, you are not saving anything.

You have to say User(name=name, email=email, password=password)

That says, "Take this name variable and put it to the name field in my database"

[lament heath]

I put it like this

I will try it again

still User( None, None)

[torn sphinx]

I would recomend at this point pushing your entire app to a github and sharing the github. Because I dont see whats wrong

[lament heath]

oh

you wanna see the file?

[torn sphinx]

yes

[lament heath]

ok

give me a sec

[torn sphinx]

k

[lament heath]

https://github.com/Mohammad142/py_group_project

[torn sphinx]

Oh you know what I just realized, You have a constructor so you can use positional arguments.

This is how I do mine

class UserAccount(db.Model, UserMixin):
    """
    UserAccount 1-1 PlayerProfile
    UserAccount 1-1 GameMasterProfile
    """
    __tablename__ = 'user'

    id = db.Column(db.Integer, primary_key=True)
    user_name = db.Column(db.String(120), nullable=True)
    email = db.Column(db.String(120), unique=True, nullable=False)
    image_file = db.Column(db.String(40), nullable=False, default="default.jpg")
    password = db.Column(db.String(77), nullable=False)
    date_created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    date_confirmed = db.Column(db.DateTime, nullable=True)
    is_over_18 = db.Column(db.Boolean, default=False)
    is_admin = db.Column(db.Boolean, nullable=False, default=False)
    is_verified = db.Column(db.Boolean, nullable=False, default=False)

    player = db.relationship('PlayerProfile', uselist=False, back_populates="user")
    gamemaster = db.relationship('GameMasterProfile', uselist=False, back_populates="user")

    def get_token(self, expires_sec):
        s = Serializer(app.config['SECRET_KEY'], expires_sec)
        return s.dumps({'user_id': self.id}).decode('utf-8')

    @staticmethod
    def verify_token(token):
        s = Serializer(app.config['SECRET_KEY'])
        try:
            user_id = s.loads(token)['user_id']
        except:
            return None
        return UserAccount.query.get(user_id)

    def __repr__(self):
        return f"User('{self.id}', '{self.email}')"

    def __init__(self, **kwargs):
        super(UserAccount, self).__init__(**kwargs)

        # Create Row in DB When Created
        db.session.add(self)
        db.session.commit()

        # Apply a player/gamemaster profile to users on creation
        new_player = PlayerProfile(id=self.id)
        new_gm = GameMasterProfile(id=self.id)

        # Commit to DB
        db.session.add(new_player)
        db.session.add(new_gm)
        db.session.commit()

I know thats ridiculously long but lol

[lament heath]

ye lol

[torn sphinx]

I dont use a constructor. Thats the __init__ where you create an object. I just use the super() thing and accept keyword arguments.

And thats why i thought you needed keyword arguments

[lament heath]

np

Im just thinking why its returning None... I have another flask file and its working well there

[torn sphinx]

And nothing is different about either?

[lament heath]

    _id = db.Column("id",db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    email=db.Column(db.String(100))

    def __init__(self, name, email):
        self.name=name
        self.email=email        
    


@app.route("/home/")
def home():
    return render_template("index.html")

@app.route("/view")
def view():
    return render_template("view.html", values=users.query.all())

@app.route("/login/", methods=["POST","GET"])
def login():
    if request.method=="POST":
        session.permanent = True
        user=request.form["nm"]
        session["user"]=user

        found_user= users.query.filter_by(name=user).first()

        if found_user:#If user have logged in before
            session["email"] = found_user.email
        else:#If theres no user
            usr=users(user,"")
            db.session.add(usr)
            db.session.commit()
            


        return redirect(url_for('user'))
    else:
        if "user" in session:
            flash("Already Logged in")
            return redirect(url_for("user"))
        
        return render_template("loginPage.html")```

Im not seeing any difference

[torn sphinx]

Are you familiar with how you use flask shell?

[lament heath]

I don't think... I don't what is it tbh

[torn sphinx]

you know you can type python at command line and it will open up a thing for you to try out code and see what works? Well, flask shell is a command that you can use from your envrionment that will load up that interactive thing for you to test out commands but it will load up your database so that you can play with it.

[lament heath]

oh

I just need to type it on the cmd?

[torn sphinx]

(venv) virufac@box:~/Projects/shell_hacker_game$ flask shell
Python-dotenv could not parse statement starting at line 1
Python 3.8.0 (default, Oct 28 2019, 16:14:01) 
[GCC 8.3.0] on linux
App: shellmancer [development]
Instance: /home/virufac/Projects/shell_hacker_game/instance
>>> from shellmancer.models import UserAccount
>>> user = UserAccount(email="[e-mail]", password="sdfdsfsD")
>>> user
User('1', '[e-mail]')

Thats how you do it in linux im sure its the same in windows

you'll do from main import user based on your code

[lament heath]

Could not locate a Flask application. You did not provide the "FLASK_APP" environment variable, and a "wsgi.py" or "app.py" module was not found in the current directory.

[torn sphinx]

And then try to input a user manually

okay then do

set FLASK_APP=main.py

Im assuming you're on windows?

[lament heath]

yes

[torn sphinx]

okay then do that

and try agian

[lament heath]

I put in on main.py file?

[torn sphinx]

whatever your file is called where you initialize your app.

[lament heath]

yes the file name is main.py

[torn sphinx]

then do that

[lament heath]

where i should put this FLASK_APP=main.py

[torn sphinx]

oh directly into the command line.

cmd or powershell or whever you are in your environment

[lament heath]

oh

[torn sphinx]

its called an environment variable

flask shell depends on it to work

[lament heath]

Error: Got unexpected extra argument (FLASK_APP=main.py)

[torn sphinx]

and you have to use the keyword set FLASK_APP=main.py

[lament heath]

oh all

flask shell set FLASK_APP=main.py

i put it like this

[torn sphinx]

no no.

first set FLASK_APP=main.py

second flask shell

two commands

[lament heath]

oh ok

it working now

I opened it

[torn sphinx]

from main import user

now try to get a user in there with something other than None lol

[lament heath]

ya lol

I have to import db?

[torn sphinx]

if you want to save it you do.

You can do that, btw and see if it works if you cut out all the other stuff

Im hoping that you will just find that particular syntax required to get a correct user object.

you know what you should also consider

email = db.Column(db.String(120), unique=True, nullable=False)
password = db.Column(db.String(77), nullable=False)

See how ive set nullable and unique keywords?

[lament heath]

db.session.add(admin)

[torn sphinx]

That makes it to where it cant be none

[lament heath]

oh

I think the error is from the class

[torn sphinx]

but then you have to drop all your tables and create it again.

[lament heath]

because is still returning None

[torn sphinx]

yeah, I think its that constructor.

or some name

All three things are none?

the name, the email, and the password

[lament heath]

yup

[torn sphinx]

hey

    def __init__(self,name,email,password):
        self.username= name
        self.username=email
        self.username=password

Did you notice that they are all self.username?

[lament heath]

dam

What a mess

[torn sphinx]

class user(db.Model):
    _id = db.Column("id",db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    email= db.Column(db.String(100))
    password = db.Column(db.String(100))

also you dont have any field called username

it happens

I hope that it the fix

Im surprised i didnt see that.

[lament heath]

I hope too thx a lot if it work or not because you really spent time helping me

[torn sphinx]

Its cool, people spend a lot of time helping me too and I like this stuff. At least now you know how to use flask shell because its helpful when stuff doesnt work

I have to give back at some point.

[lament heath]

yes

Always give back and I think it will return to you

yaaaaaaaaaaaaaaaaaa

it worked

[torn sphinx]

Cool.

Its always a silly thing.

[lament heath]

ya

Thanks a ton

[torn sphinx]

For sure

[lament heath]

I was stuck with this

for a while

[torn sphinx]

I just thinks its nuts I had to have you open flask shell to find that.

I should have seen it too

[lament heath]

np

yes who thought its a silly thing like this

Thanks again 😄

[torn sphinx]

No problem. Im glad I get to help someone finally, I usually am the one getting help.

[lament heath]

Yes its a good feeling

Password need to be hashed in databases too?

[torn sphinx]

yes, you should only save the hash in the database. The plaintext you should discard. You only use what they enter in the form to turn into a hash and then when they login, you use if ph.verify(user.password, form.password.data): to see if its that right one. If it is it will be True

[lament heath]

oh

[torn sphinx]

that way not even you know what their password is.

[lament heath]

            name=form.username.data
            email=form.email.data
            hashed_pw = ph.hash(form.password.data)```

hash it like this?

[torn sphinx]

And if your database is breached you'll not endanger people.

yeah that works

[lament heath]

oh

wait

[torn sphinx]

fix your indents but yeah

[lament heath]

$argon2id$v=19$m=102400,t=2,p=8$k+I8FmCFZnGCvo4otSSChA$AxnuEZMNS6G+CZr+F54Xiw

this one is hashed?

[torn sphinx]

yep that is what you save.

[lament heath]

Oh i thought hashed is something like only points .....

I think if he was a good hacker he will find a way

[torn sphinx]

nah, a hash is just a string that is generated from another string based on an alogirithm so that one always can be changed back to the other but that you can't use a hash table to figure out one from the other. Its hard to explain.

https://www.codeproject.com/Articles/704865/Salted-Password-Hashing-Doing-it-Right

[lament heath]

with p=8$k+I8FmCFZnGCvo4otSSChA$AxnuEZMNS6G+CZr+F54Xiw

[torn sphinx]

Keep in mind that all the stuff that is explained in that article is done for you by argon2

[lament heath]

oh yes

[torn sphinx]

But no, you save the entire thing

Now just whats on the end

[lament heath]

CZr+F54Xiw

its a good way

[torn sphinx]

Honestly, its a matter of a lot of time and comprehensive word list that makes cracking that hash virtually impossible

[lament heath]

Do they use something like this in big companies?

yes like every word in it mean something maybe

[torn sphinx]

I mean, in big companies hashing passwords is the first line of defense.

They do a lot more than that.

But yeah, thats why its important that users use passwords that are not guessable

Because if they dont, its almost impossible to crack them because of how much tiem it would take.

But yeah, i gotta get going. good luck man.

[lament heath]

ye

thanks

cya

[twin tundra]

Hello. Any sqlalchemy user? I have the next code.
And I want get all Players by match_id, so first idea was kind of this.
So the question is how to do it in the most appropriate way - optimized as in query way as in code clarity.

P.S.
don't mind lazy settings

    def get_by_match_id(match_id):
        return session.query(Player).filter(match_id in Player.matches).all()

class Player(Base.Model):
    __tablename__ = 'players'

    id = Column(Integer, primary_key=True)
    matches = relationship('Match', secondary='players_matches',
                           backref=backref('players', lazy='joined'), lazy='joined')


class Match(Base.Model):
    __tablename__ = 'matches'

    id = Column(Integer, primary_key=True)
    
 
class PlayersMatches(Base.Model):
    __tablename__ = 'players_matches'

    id = Column(Integer(), primary_key=True)
    match_id = Column('user_id', Integer(), ForeignKey('matches.id'))
    player_id = Column('role_id', Integer(), ForeignKey('players.id'))

for now I'm using session.query(Player).join(PlayersMatches).join(Match).filter(Match.id == match_id).all() (though have to properly test it)

[torn sphinx]

I've never really used join before. I'm not sure hot it works yet.

[latent berry]

don't know if this is the right place to ask, but i'm trying to deploy a postgres database to heroku, under their free hobby plan.
tried following their docs, which tells you to upload a pg_dump to an s3, sign the url, and then run a command to import it into heroku

I've done all that, uploading, signing, and when attempting to import i get
expected success, got code 500. I've tried changing the url which I sign, modifying the database and making a new dump, and changing the privacy settings on my s3, but nothing seems to work.

here's the docs for what I'm trying to do, I've followed it all to the t and gave it a few tries, not sure what I'm doing wrong.
https://devcenter.heroku.com/articles/heroku-postgres-import-export

[tropic ice]

Hi, is this the place for questions related to matplotlib?

[runic pilot]

@latent berry what happens when you pass in the -v flag for verbose output?

[tropic ice]

Hey <@&267629731250176001>, all of my help requests get archived before I can receive an answer. Any help? Apologies in advance for the tag.

[acoustic silo]

that's most likely because the people looking through the help channels don't know how to help with your problem. you can try in one of these topical channels, which moves slower and you'll be more likely get an answer. however, you probably want #data-science-and-ml instead of #databases . also, please don't ping moderators next time unless you need moderation.

[latent berry]

@runic pilot at which stage? attempting the final stage of importing with the flag makes no difference

i'm going to try troubleshoot some aws permissions as well

[runic pilot]

if you curl that s3 object can you get it?

[latent berry]

i get an access denied, which i presume is because it's set to private

not really used aws before, but setting to public didn't make much difference

think i might have mistyped my aws credentials or something

[runic pilot]

setting it to public should definitely make a difference

[untold hull]

Hello, i got a task been days on it, but my solution is poor looping and need advice please.
Got 2 databases A & B .

they need to update each other, (add , delete , update) both databases table row's have same ID.

A is MySql and B is Access database.

my solution was looping between records with nested looping comparing their ID's and it will be slow with many records,

Any other solution please that i can work on ?

[rain wagon]

There is no solution here with Microsoft Access

At least not automated

you can import pure sql into Access, but you cannot automate it to my knowledge

Maybe if you write an addin

I also highly question the premise of the solution itself

@untold hull

[latent berry]

i turned on every permission I can, now i'm able to curl it but heroku is now complaining in a different way hahahaha

[untold hull]

the idea, need php web app and vb6 desktop app.
they work together and the desktop app updates mysql when got internet connection, otherwise data is saved on access,
different databases, server and local one, internet connection not stable but they need to be connected with each other

[rain wagon]

vb6?

the fuck

[untold hull]

this task will make me crazy lol, not possible ya ?

yeah really they want me build that also with vb6

forget vb6 xD, but that connection between database on mentioned situation is possible ?

[rain wagon]

Well, if you got an application as a middle man, then you can translate between the two

But you need to sync yourself

and that will be hard

[untold hull]

hmm

[rain wagon]

Maybe if you use a local Microsoft SQL Server instead and an online MSSQL Server?

I can't give you pointers there, but at least they'd speak the same language

[untold hull]

then it can be solved?

[rain wagon]

Maybe there is some easy sync possible then

I mean, it is pretty simple between 2 MySQL Servers with replication

I have never done it with an unstable connection though

[untold hull]

i got your point

thanks alot man, well the task i got must be solved with given databases and language , i think i will drop it lol

[rain wagon]

Sorry I couldn't help more

[untold hull]

i appreciated all :)

[indigo mason]

Does anyone know of the internal sqlite3 functions that would allow me to open a database from memory/a bytesio

[rain wagon]

conn = sqlite3.connect(":memory:")

[indigo mason]

This is opening not creating

I'm trying to open a locked database for reading and short of just copying the file there doesn't seem to be way that sqlite3 would allow

[rain wagon]

That creates a database in memory

[indigo mason]

Creating not opening*

[rain wagon]

opening and creating is the same in sqlite

If it exists, it is opened, if not, it is created

[indigo mason]

Opening an existing database on disk

[rain wagon]

You cannot do that from BytesIO though

there is no built-in way

[indigo mason]

So I'm just going to have to copy the file

Oh well

Seems rather limiting on SQLite3's part

[rain wagon]

I mean, you can read the master table, pull all data and commit it into a new one

idk what exactly you are trying to achieve with BytesIO here

[indigo mason]

Read from a locked database

[rain wagon]

Reading from a database is done with sql

that isn't limiting, that is the whole point

[indigo mason]

Sure but it is filed based

The file reading being kept from you is fine for a high level API

But there is no lower level option of passing the bytes to the parser

[rain wagon]

Again, simply not the point of a database

And if you simply copy it, it will retain the lock

The easiest method to rescue it is using the cli util, dumping it and importing it into a new file

[indigo mason]

What do you mean not the point of a database

[rain wagon]

to have access to the underlying bytes via it's api

[indigo mason]

I do not want to access the bytes

I just want to give it bytes instead of a filepath

[rain wagon]

The best you can then is to insert a blob

but I wouldn't do that

[lethal frigate]

Are there any Python & sqlite helpers online? Need some help getting started, any help would be appreciated!

[delicate field]

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.

[small gust]

I'm writing a task app in Flask + SQLAlchemy and I have a DB design question: If I have a Task model with foreign keys to Steps, what should I do when I want to cancel a Task? Should I delete it and cascade the delete to include the Steps? Or should I use a status flag on everything (completed, canceled, pending, etc) and not delete anything, just change the status?

[runic pilot]

both patterns are fine, it depends if you want to keep data around for bookkeeping or "undeleting" something in the case that a user deleted something by accident

[violet mountain]

quick question about database

so i store something into a database using sqlite3

when it stores it gives it self its own ID

how can i print out the ID without knowing?

[celest zodiac]

@violet mountain I don't know what you mean by "print out the ID without knowing"

[violet mountain]

okay so when i save to the database, i have an auto increment ID which i don't change

i want to print out the ID of the row when its saved

[celest zodiac]

Generally, when you save a database object, the object will have an .id property which will be updated with the newly saved ID

[violet mountain]

this is how my database is set up

the invoice is the auto increment

when i add to the database i don't add to the invoice it auto increment it self

[celest zodiac]

so, once you save the new record object, the Invoice field for that object should be auto-populated

[violet mountain]

yes

it adds one

and so on

i want to print out the invoice when the row is added

[celest zodiac]

right, I'm saying the object you've created to interact with the database to save that record should have that value automatically updated

so you just print the field from that object once you've saved

[violet mountain]

how can i do that?

@celest zodiac

[celest zodiac]

That would depend on the ORM - can you show me the code where you save a newly created object?

[violet mountain]

give me 5 mins let me quickly write the code for it

def adding(self,first,last,repair,paid,phone,address,reg,hidden):
        #checks if the car doesn't exists
        db = sqlite3.connect("Pro.db")
        curs = db.cursor()
        curs.execute('''SELECT Number_Plate FROM Vehicles WHERE Number_Plate=?''',(reg,))
        result = curs.fetchone()
        if result:
            left = str(repair.get()) - str(paid.get())
            curs.execute('''INSERT INTO Faults(Number_Plate,First_Name,Last_Name,Repair_Cost,Amount_Left,Phone_Number) VALUES(?,?,?,?,?,?)''',(reg.get(),first.get(),last.get(),repair.get(),paid.get,left,phone.get(),address.get(),reg))
            db.commit()
            db.close()
        else:
            hidden.config(text="Nah")

@celest zodiac thats the program

[celest zodiac]

OK, you're using basic SQLite3, not an ORM

I think it works this way - after curs.execute you'd say row_id = curs.lastrowid

and that would be whatever row ID was generated for the last insert

(It's specific to the cursor in use)

(Note that if you use .executemany you'll get None for that row ID)

https://stackoverflow.com/questions/6242756/how-to-retrieve-inserted-id-after-inserting-row-in-sqlite-using-python

[violet mountain]

oooo ty

[violet mountain]

@celest zodiac erm why am i getting this error with the database?

curs.execute('''INSERT INTO Payments(Number_Plate,First_Name,Last_Name,Repair_Cost,Amount_Paid,Amount_Left,Phone_Number) VALUES(?,?,?,?,?,?,?)''',(reg.get(),first.get(),last.get(),repair.get(),paid.get(),left,phone.get(),address.get()))
            db.commit()

[celest zodiac]

should left be left.get()?

[violet mountain]

no its a variable

[celest zodiac]

also, for the phone_number column you're using address

[violet mountain]

OHHHHHHH

[violet mountain]

@celest zodiac sorry if i'm bothering you but last thing, can you have a look at my database and tell me if its in third normal form?

[celest blaze]

maybe just pass pre? I dunno

[torn sphinx]

hi can somoene help me with an sql query question

[inner plume]

Im using postgres SQL and was wondering the "proper" way to store expanding amounts of informations, aka is there a way to store tables within tables?

[charred fractal]

I don't think it is necessarily called storing tables within tables.

[inner plume]

I found it it’s table relationships

[torn sphinx]

hi

how would I get the second rank based on this

SELECT firstname, department, startdate,
  RANK() OVER ( PARTITION BY department ORDER BY startdate DESC) AS rank
FROM Employees;

[naive galleon]

WITH ranked_data AS (
      SELECT firstname,
             department,
             startdate,
             RANK() OVER ( PARTITION BY department ORDER BY startdate DESC) AS rank
      FROM Employees
    )
    SELECT * 
    FROM ranked_data
    WHERE rank = 2;

one option

[torn sphinx]

thank you

why are these called window functions.. what else is a window function

[naive galleon]

window functions create what you can think of as an adhoc subtable per row based on the criteria supplied to the function and return a value

depending on your flavor of sql you may have an NTH_VALUE window function available which would clean the above up

i think of the 'subtable' as a 'window' on the data based on the row, function, and params, hence the name i suppose

[violet mountain]

can anyone help me out with normalizing my database?

[torn sphinx]

https://docs.sqlalchemy.org/en/13/orm/extensions/mutable.html#module-sqlalchemy.ext.mutable

What does this mean? I assume that it means that when I change the column, it's not going to be able to tell in migration. But it did and I migrated a column of sqlalchemy.dialects.postgresql.JSONB successfully into the database. I' wondering if without what is set up in the above documentation, what does that mean? Am I unable to change the data in the json without that?

[steel plover]

Im pretty confused, how do u make a remote db?

Like can u host one on remote postgres host or something?

[noble oak]

Would sqlite be a good database to use (I'm not planning on bringing the bot to many servers, probably 3 at max)

[torn sphinx]

hey so i understand databases and queries and data pretty well. and connection strings. i am now learning python. if i just want to pull data into my .py file, what should i use?

sqlalchemy?

psycopg2 - got it.

thanks

[quartz tusk]

with open(file, mode=mode, encoding=encoding) as f:

    reader = csv.reader(f)
    next(reader)

    for row in reader:
        cur.execute(f"INSERT INTO {table_name} ({','.join(cols)}) VALUES (f{','.join(row)}) ")
    conn.commit()

table_name = name of table
cols = columns of table
row = the row from csv being inserted

Traceback (most recent call last):
File "C:\Users\arjun\Codes\misc\Kaggle Datasets\zomato_analysis\add_to_db.py", line 70, in <module>
cur.execute(f"INSERT INTO {table_name} ({','.join(cols)}) VALUES (f{','.join(row)}) ")
psycopg2.errors.SyntaxError: syntax error at or near "Storyss"
LINE 1: ...urkish,vietnamese,wraps) VALUES (f19049583,Street Storyss,In...

This is the error I'm getting. Is there any way to iteratively add data to a db?

or is my syntax completely wrong?

[rich trout]

There's many ways to iterably add data

but I would suggest, first, using something called parameterized queries

Here's a relevant example from the postgres wiki:

namedict = ({"first_name":"Joshua", "last_name":"Drake"},
            {"first_name":"Steven", "last_name":"Foo"},
            {"first_name":"David", "last_name":"Bar"})

You could easily insert all three rows within the dictionary by using:

cur = conn.cursor()
cur.executemany("""INSERT INTO bar(first_name,last_name) VALUES (%(first_name)s, %(last_name)s)""", namedict)

If you do not specify the (first_name) and just use %s, a tuple of elements ("Joshua", "Drake") or a list of them, functions the same

this will handle escaping for you, but perhaps not the table name. Best not to have dynamic table names, however

[quartz tusk]

Ah thanks but I used pandas.to_sql() method, seemed easier cause I had over 125 columns

[pliant pendant]

i wonder if this is the best way for me to access my values

WITH main AS (
    SELECT * FROM users.info
    WHERE users.info.id = 1
)
SELECT * FROM main
LEFT JOIN users.inventory as inv
    ON main.id = inv.user_id
LEFT JOIN users.ratelimits as ratelimits
    ON main.id = ratelimits.user_id;

or can i optimize it further

[solid void]

you can replace the * with the actual columns you want

[pliant pendant]

tru smh, also i do wonder, how do i implement values for a function that i made

DO $$ 
DECLARE
    ratelimit     INTEGER := 42;
    reset_timeout timestamptz := NOW() - '1 hour'::interval;
BEGIN 
    IF reset_timeout >= NOW() THEN
        ratelimit := 45;
        reset_timeout := NOW() + '1 hour'::interval;
        RAISE NOTICE 'reset';
    ELSE
        IF ratelimit > 0 THEN
            ratelimit := SUM(ratelimit - 1);
            RAISE NOTICE 'decrement';
        ELSE
            RAISE NOTICE 'error';
        END IF;
    END IF;
END $$;

however, i would want the ratelimit and reset_timeout variables to be

SELECT image AS ratelimit,
image_refresh AS reset_timeout
FROM users.ratelimits
WHERE user_id = 1;

Using PostgreSQL 11

[torn sphinx]

Ok I am totally stuck. So I want to create a database where I save a server_id and in that server_id I want to save data for every user

like this in json

{
guildid: {
          userid: {
                  warns: 5
                  }
        }
}

does anybody know how to do this

[flint walrus]

Hello guys, I know only little about sqlite and I need something like this:
insert into table (...) values (...) if there is no row with x=a and y=b in the table

What would be the correct statement for that, please?

please tag me in case you answer

[umbral ridge]

Do you want to update every row that has missing data? @flint walrus

[mystic shore]

I am making a bot with a web app. You can change the prefix for the bot in the web app. When you change the prefix in the webapp, it sends a request to the API written in flask, which then updates the prefix in mongo. The bot (separate python app) then reads the prefix from the mongo database. The problem is, the bot caches the prefix as it would be insanely resource intensive to check the database for every single message the bot sees. Is there a way I can detect when the prefix has been updated in mongo so the bot can update its cache?

[runic pilot]

how resource intensive would it be?

have you tested to see under what amount of load it starts to buckle?

[mystic shore]

i have not tested it, but a query takes a short amount of time but when its being constantly bombarded with messages that adds up

is there a way to check how long a mongo query takes?

[runic pilot]

you can benchmark how long it takes in your code and log that

[mystic shore]

i'd like to avoid the problem altogether instead of working around it tho. Is there an event or something that fires when an item in mongo changes?

[runic pilot]

my point is that there are a lot of easy ways to handle high volume load and stay performant without using a cache (which will only add another layer of problems), but yes, I think there is something like that in mongo https://www.mongodb.com/presentations/event-based-subscription-mongodb

[mystic shore]

interesting, thanks!

[mystic shore]

hmm

is there an actual docs page on this?

for anyone just joining in im trying to figure out a way to detect when a document in a mongo database updates

[celest blaze]

they might have a way of sending some sort of message when that happens -- I know that AWS' dynamodb has a mechanism for that, e.g.

[clever topaz]

@mystic shore Not familiar with MongoDB, but this may help - https://stackoverflow.com/questions/9691316/how-to-listen-for-changes-to-a-mongodb-collection

[cinder sierra]

@mystic shore off of that stack overflow i found a link to a new feature added that'll do just that.
https://emptysqua.re/blog/driver-features-for-mongodb-3-6/#change-streams

[pliant pendant]

i need some refractoring

how do i make this look more beautiful

DO $$ 
DECLARE
    ratelimit INTEGER := (SELECT image FROM users.ratelimits WHERE user_id = 2);
    reset_timeout timestamptz := (SELECT image_refresh FROM users.ratelimits WHERE user_id = 2);
BEGIN
    IF reset_timeout <= NOW() THEN
        UPDATE users.ratelimits
        SET image_refresh = NOW() + '1 hour'::interval,
            image = DEFAULT
            WHERE user_id = 2;
        RAISE NOTICE 'reset';
        
    ELSE
        IF ratelimit > 0 THEN
            UPDATE users.ratelimits
            SET image = image - 1
            WHERE user_id = 2;
            RAISE NOTICE 'decrement';
            
        ELSIF ratelimit <= 0 THEN
            RAISE NOTICE 'out of credits';
            
        ELSE
            INSERT INTO users.ratelimits(user_id) VALUES (2)
            ON CONFLICT DO UPDATE SET
                image = excluded.image,
                image_refresh = excluded.image_refresh;
            RAISE NOTICE 'error';
        END IF;
    END IF;
END $$;

does it look good or can i make it look better

it runs flawlessly

also instead of RAISE NOTICE 'out of credits'; and such, how can i replace these to returning true or false

[mystic shore]

that works, but i just realized a much better way to do this, i can just store the ID of the item in mongo so it odesnt have to find it every time

it just has to look at it

[trail blade]

anyone know of a widget that displays contents of an sqlite3 database in Tkinter?
or something close to that

[cinder sierra]

are you just wanting an easy way to view your db?

@trail blade

[trail blade]

no unfortunatley

i can see what the db has easily enough by printing it out

but i need to display the db contents on a GUI as a table

[cinder sierra]

i can just suggest making a custom function then. i could help if you wanted but it might not be until tomorrow.

[trail blade]

no worries m8, has to be done tonight lol

[half lintel]

Can anybody here help me with a mutiple update function?

I'm just looking for some feedback to make it more elegant / better

[fathom fiber]

How i can output the value BALANCE from table BANK

[pliant pendant]

@fathom fiber you get a record object

you use it the same way as a json

[fathom fiber]

i find out how to do that

but thanks for help

[pliant pendant]

sorry for my delays smh

[fathom fiber]

no problem

[autumn coral]

psql -h localhost -p 5432 -U amigoscode test
Why doesn't this work on psql terminal?
I think I'm following a pretty old tutorial because half the things don't even work for me that works for the person doing the tutorial
https://www.youtube.com/watch?v=qw--VYLpxG4&t=137s

Its this one

[undone roost]

what are the database init, migrate and update commands in fastApi if i am using SQLAlchemy as my ORM?

[flint walrus]

@umbral ridge No I don't, I just wanna get rows for which a certain condition evaluates as true.

[umbral ridge]

okay..

select * from agents where commission = '' OR working_area ='';

like that?

http://sqlfiddle.com/#!5/eef2d/1

[runic pilot]

@undone roost SQLAlchemy only gives you ORM capabilities, you're looking for a migration library, try alembic https://alembic.sqlalchemy.org/en/latest/ it's made to play well with SQLAlchemy because it's by the same author

[undone roost]

@runic pilot Yes alembic i will be using....do i have to use docker in it ?

[runic pilot]

no, docker is used to make a single unified layer to the OS, nothing to do with app level code aside from installation

[undone roost]

@runic pilot refer to the following article https://medium.com/@ahmed.nafies/fastapi-with-sqlalchemy-postgresql-and-alembic-and-of-course-docker-f2b7411ee396 thr guy has used docker ...but i don't necessarily have to ..right?

[runic pilot]

the docker stuff is entirely optional

I've been using flask+sqlalchemy+alembic+postgres for 5 years and only started using docker in the last year and a half

if you're using a mac, just use this to get a PGSQL server up and running https://postgresapp.com/

then make a setup_db.sh file:

createdb myapp
createdb myapp_test
createuser -s myapp_user

then use postgresql://myapp_user@localhost:5432 as your SQLALCHEMY_DATABASE_URI

[undone roost]

i am using mysql

and windows 😬

[torn sphinx]

https://pastebin.com/FFcp7dNg

im not sure why im getting this erro

error

[runic pilot]

remove the single quotes

[torn sphinx]

from all of them?

[runic pilot]

yeah

[torn sphinx]

oh

damn

i didnt even see that

[undone roost]

@runic pilot i had a previous project ....from which i copied files and pasted into a new project directory.....so when i run the command alembic init . it says directory . already exist and is not empty

[runic pilot]

that makes sense

because you copied the files from an old project

[undone roost]

what to do?

i mean when to run alembic init .

if i leave the directory empty and run the command and then copy the files ..will that work?

[runic pilot]

I'd imagine it would

read through this for more info https://alembic.sqlalchemy.org/en/latest/tutorial.html

[undone roost]

okay it did

now i can copy files ig

one thing...how to create tables from the models i have?

[runic pilot]

outlined here https://alembic.sqlalchemy.org/en/latest/autogenerate.html

[solid void]

@autumn coral it's from 2019, and postgresql dev are not the kind to break things from one release to another, they usually add features, not remove them.

[undone roost]

@runic pilot the article tells to create tables with alembic but i want to create tables with the models.py file i have created

[violet mountain]

anyone good at normalising databases?

[runic pilot]

from myapp.models import Base
target_metadata = Base.metadata

put that in the env.py file and it'll detect your models automatically

that's like one of the first lines in the link I sent, please read that carefully

[undone roost]

about this....my structure is
app
alembic
versions
env
database

so what will be the import line for me

database is where the declarative base is

[runic pilot]

from database import Base

[rain wagon]

@violet mountain Just ask

[undone roost]

no module named database

[violet mountain]

@rain wagon i need help with normalizing my database

[rain wagon]

Nobody will answer unless you finally ask a damn question

[violet mountain]

how would i normalize this table:

https://gyazo.com/e659a7e3d25f96f33c2423f2ed5a7f33.png

[rain wagon]

give me a minute

[runic pilot]

what's the context?

this could be totally normalized already, for all we know

[undone roost]

module not found error

[runic pilot]

and normally there's more than one table

[violet mountain]

hmm want the other tables

[runic pilot]

what's the business logic of this table? why does this table exist?

[violet mountain]

https://gyazo.com/6d256b11adc47b0318cf0e07b6798567.png

https://gyazo.com/0f23b7a8d63483a750cd3e492912c462.png

https://gyazo.com/b6b6c3c5055f2537c70a082cd0f81771.png

thats all the tables in the database

[runic pilot]

yup that looks pretty well normalized

[violet mountain]

it can not be normalized more than that right?

[rain wagon]

https://dbdiagram.io/d/5e5b1f8c4495b02c3b879b35

This is how it is normalized

1 customer can have many bills

you can break it down even more if you'd really want to

[runic pilot]

there are different forms of normalization, do you know which normal form you're supposed to use?

[rain wagon]

you'd do at least 3

that is pretty standard

[violet mountain]

i was told to do 3rd normal form

[rain wagon]

I've taken out amountleft since that is kind of redundant

can do the math with a select

[violet mountain]

dam

scorcher24 ty

i was going to 3rd normal my who database but it would over complicate my program as much as it is rn

[rain wagon]

3rd normalform is something I'd consider the minimum

but even then, as I said, one customer can have many bills

[violet mountain]

i might just lose like 5marks

its fine

[rain wagon]

so the previous form blows up in your face as soon as the customer wants a second car repaired

you'd have to make a whole new dataset with the same data you already have on file

[runic pilot]

you've got a redundant relation to the customerId in the bills table

[rain wagon]

oh shoot

your right

corrected it

[violet mountain]

lol i feel like the way ive done it should be alright tbh

tbh idek myself

[runic pilot]

it looks like your tables don't reference each other

[violet mountain]

ik

[rain wagon]

oh there is even more tables

[violet mountain]

i don't need it in my program

@rain wagon what do you suggest do i go and get the whole database normlised or just leave it as it is

[rain wagon]

https://dbdiagram.io/d/5e5b1f8c4495b02c3b879b35

I've added more tables

Now you got vehicles seperate and it gets referenced in the bill

It's also a not so bad idea to separate parts and colors, as there are a lot standard colors and parts etc pp

@violet mountain I would really stick with NF3 and continue this way of doing it, because it means your db can be extended, you can do proper joins etc pp

[dusty helm]

guys. using sqlite for medium projects, it's cool?

[rain wagon]

It has only advantages

@dusty helm If it's on the web, use a rmdbs

well, a server based rmdbs

[dusty helm]

like pgsql?

[rain wagon]

for example, or MySQL

[dusty helm]

ok, cool

i'm trying to create a sample saas

actually idk wtf i am doing right now

but thank you dude

[violet mountain]

kk @rain wagon

scorcher can you do one more thing to it? can you add the faults

if possible

[runic pilot]

we shouldn't be helping you on your homework

Rule 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.

[violet mountain]

not homework just a project for myself tbh

before i actually do my whole project

[runic pilot]

what was this referring to?

i might just lose like 5marks

[violet mountain]

for when i actually make the whole project

[rain wagon]

@violet mountain I leave that to you as an exercise 😉

[violet mountain]

ty

[vocal moon]

When I try to install MySql onto my computer it tells me that Python(64-bit) is not installed however I do have python installed
https://cdn.discordapp.com/attachments/470275631314239489/700789588083343431/unknown.png
https://cdn.discordapp.com/attachments/470275631314239489/700789794069807204/unknown.png

[cinder sierra]

check if it's in PATH properly

[vocal moon]

how do i do that?

i clicked Add to path during installation

[vocal moon]

@cinder sierra do all versions of python work for it?

[cinder sierra]

I'd imagine?

[vocal moon]

hm

is there anyway to fix the issue?

[autumn coral]

@autumn coral it's from 2019, and postgresql dev are not the kind to break things from one release to another, they usually add features, not remove them.
@solid void Then this > https://www.youtube.com/watch?v=qw--VYLpxG4&t=137s < video tells me to do psql --help and he gets a help menu, if he types psql -h localhost -p 5432 -U amigoscode test he gets a message, if I do it, even wrong, it doesn't answer me

Now why is that?

[fallen blaze]

https://hastebin.com/ducoyureko.py

The command says it works but nothing saves in the database

[rain wagon]

@fallen blaze Because you only commit if the result is not None

[solid void]

@autumn coral i guess you didn't install postgres properly, or you have a firewall bloking the request, connecting to postgres with psql is the most basic thing. i use similar commands to connect to my db very often, it works with any version.

[autumn coral]

@solid void Apparently it doesn't psql's bin folder on path

I added it to the path, and now it works!

Btw is there a way to get rid of all the users in psql?

I made one named 'test' to follow a tutorial

And now I can't get rid of it

And is there a good PostgreSQL tutorial you'd recommend me?

There's not many good ones out there

Or even the docs

I can't find the official ones

[solid void]

The postgresql doc is great, but it's not exactly a tutorial, i can't say i can recomment one, i was advise to try ”a curious moon”, a (not free) book that is a big tutorial, i bought it and did part of it, but didn't finish.
The youtube video you linked seems very good though (but i only watched/listened to the first 30mn or so)

[autumn coral]

Oh

[solid void]

Any sql tutorial should give you the basics anyway

[autumn coral]

I can't order it right now though

Or can I?

Do they do shipping right now?

[solid void]

There is a pdf version

[autumn coral]

Ooo..

[solid void]

https://bigmachine.io/products/a-curious-moon/?utm_source=blog&utm_campaign=curious_moon_blog

Bonus points if you are into astronomy :)

[autumn coral]

I'm not

But anyways, I'll order it

And until that, I'll watch the tutorial

🙂

Btw how much knowledge does one need to learn PostgreSQL?

@solid void

I'm sorry if I'm bothering you a lot

This is the last one

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

Like till which level?

Variable? 😂
Oop?
Machine learning?

MySql?

[solid void]

I don't understand the question, i guess a lot of knowledge can be useful, but not definitely necessary

Sql definitely,but the tutorial will teach you at least the basics of that

[autumn coral]

oh

[solid void]

ML is unrelated

[autumn coral]

That was an example

[solid void]

MySql is a lot like Postgres, so it does help if you kno it, but you don't need to learn it first

What can help is an understanding of data normalisation, it'll just make you design better DBs

To learn sql, the concept of an array (rows/columns) and of a relationship, are necessary, i think that's about it, and data types like string, int, float, date, etc

[autumn coral]

Btw, I feel so bad for all the main people like mods, admins and owner

I saw ves getting irritated in tortoise server

He would probably mute him if it was this server lmfao 🤣

[solid void]

We are only humans, but the team is great, and we can take time off when needed, because there are many other people helping, but yeah, sometime users can be tiring.

[autumn coral]

I had never expected such a big/great/helpful community about python when I thought of learning it

Especially here in discord

[solid void]

Hm yeah, i don't go a lot in other servers (aside one where i'm admin), but you have to understand that rules and moderation can be different other there

[autumn coral]

mhm

[solid void]

But that's a bit offtopic for here :)

[autumn coral]

Yes, agree

I just hope ves doesn't go "How dare you be rude to me just because I'm in a different server 😠 " and bans me here

[torn sphinx]

hey I need some help

with prostreSGL

so i tried to connect my database from another pc and it didn't seem to work

this is my code

async def create_db_pool():
    client.pg_con = await asyncpg.create_pool(database = 'levels', user = 'postgres', password = password, host = '127.0.0.1', port = port)```

is there anything I can do to fix it?

[autumn coral]

@torn sphinx Just for curiosity, is this a localhost?

[torn sphinx]

wdym by localhost

like is it on the same pc?

because its not

[autumn coral]

How did you setup that host?

[torn sphinx]

like how I got the host

I got it from the PGAdmin 4

website url

[autumn coral]

for free?!

You can get a remote host for free?

[torn sphinx]

its the usual one

idk

[autumn coral]

huh?

[torn sphinx]

its a host that everyone uses

[autumn coral]

Did you pay for it though?

[torn sphinx]

no

[toxic cedar]

....

[torn sphinx]

im confsed

[autumn coral]

O_o

Don't mind me

I'm a newbie at this stuff

[torn sphinx]

if u open the pgadmin 4 website thing

same

[autumn coral]

I just started learning PostgreSQL

[torn sphinx]

im newbie too

in the pgadmin4 website u can see in the url theres an ip and the port

[autumn coral]

https://www.youtube.com/watch?v=qw--VYLpxG4&t=137s

[toxic cedar]

What are you implying. Are you having trouble setting it up?

[torn sphinx]

when u open up ur pgadmin4

yeah

[autumn coral]

mhm?

[torn sphinx]

well idk

I am trying to connet it from another oc

pc*

but it didn't seem to work

async def create_db_pool():
    client.pg_con = await asyncpg.create_pool(database = 'levels', user = 'postgres', password = password, host = '127.0.0.1', port = port)```

this is my cmd

[toxic cedar]

Yeah because your dbs is restricted to the first machine

[torn sphinx]

I mean code

so how do i fix that

?

[toxic cedar]

you need to make it available online,

[torn sphinx]

you got any idea how?

like r u sure this is correct what am doing

because I think I got this from my head

[toxic cedar]

most people uses VPS or dbs from providers, If you are planning to host it locally you can ,but the system needs to be online 24/7

[torn sphinx]

oh

but its online

why didn't it work?

[toxic cedar]

Its is online but there is still configurations to do to make it available online

[torn sphinx]

how? though

[toxic cedar]

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

you can get a detailed explanation from this video

[torn sphinx]

ok

[solid void]

when you put "host='127.0.0.1" it tells the db is on your pc

if the db is on another pc, you must give the ip of that pc

[torn sphinx]

can some one say me if they use php or node.js ? https://www.lieferando.de/

[vocal moon]

ive just installed postgre im trying to learn how to use it, what should i do now

[pliant pendant]

good question, only you can answer that tho

[solid void]

@torn sphinx 1/ that's off topic (not database related) 2/ no, you can't know that just from looking at the website, sometime there might be hints, but they are not proofs (and some programmers like to put false hints)

[pliant pendant]

wooshwoosh i yoink in here with a question

reset_timeout := format('SELECT %I_refresh 
    FROM users.ratelimits 
    WHERE user_id = $2'
        , $1);

this gives error in PostgreSQL 11, however i wonder what would be the correct way to write this kind of statement

another attempt 30 minutes later

      EXECUTE format('reset_timeout := (SELECT %I_refresh 
      FROM users.ratelimits 
      WHERE user_id = $2)
      ', $1);

however, it also gives errors

been stuck at this error for nearly 1 hour now

[celest blaze]

:=? Is that the famous Walrus?

[pliant pendant]

:= is actually an assignment operator in PostgreSQL

but when I think about it, yeh it kinda look like it

[solid void]

it's confusing because python3.8 has that "walrus operator", and it wasn't clear if it was all sql or python+sql in your first snippet

[pliant pendant]

been stuck for hours to no avail

reset_timeout := EXECUTE format('(
        SELECT %I_refresh 
    FROM users.ratelimits 
    WHERE user_id = $2
        )', $1);

[fallen blaze]

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

Error im getting^

[lavish ferry]

async def lettercheck(bot, message):
    word = await bot.db.fetchrow("SELECT letter FROM adm WHERE guild_id = $1", message.guild.id)
    if not word:
        word = "k"
        return True
    else:
        word = word['letter']
        return True

#some command:
    @commands.command()
    async def check(self, ctx):
       await ctx.send(f"{lettercheck}")```
why can't I insert my text into another function?
is returning this: `<function lettercheck at 0x040CF418>`

[celest blaze]

you probably want to write await ctx.send(f"{lettercheck()}")

or even

await ctx.send(lettercheck())

no point using an f-string with something that simple

[lavish ferry]

ok

[spark stone]

hi there, i have an issue with shelve dict, im trying to save my data by creating a dict in dict

topDict = shelve.open(config.topPath)
topDict['psn'] = {}
topDict['psn']['guy'] = (0, 0, 0)
print(topDict['psn'])
# output is {}

[manic patio]

What would be the best way to use a db in python (on a flask app) without an ORM, as I need to be using SQL purely commands

[celest blaze]

just get the python library for that database

hopefully it's not MySQL

[fallen blaze]

 @bot.command()
 async def addword(ctx, text):
     with open('curses.json', 'w') as p:
         json.dump([str(text)], p)```

Instead of adding to the json it deletes anything old and replaces it. Also I beleive this fits under databases since Im using json as a database 😂

[celest blaze]

you should read the JSON file into a variable, add the word to that variable, then dump the variable back out

that's inefficient, but at least it'll preserve your data

[raw quail]

so i have a mongodb document that looks like this:

{"_id": "1", "accounts": {"[e-mail]": "Example user", "[e-mail]": "Random user", "points": 5}}```
and i have an email/user and want to get the points
how would i get the document that has the email in the `accounts` dict

[raw quail]

I've found the solution by the way.
Accessing the dict with dot notation i think it's called
basically db.find_one({"accounts.{}".format(email): user})

[safe sail]

Hello is there anything wrong with having a design like this?

The plan is that when either a product/customer is deleted, so are the license keys associated with it

[charred fractal]

Honestly you shouldn't do it like that but you could.

[safe sail]

How else could I do it?

Just drawing it, it looks like it's kinda wrong?

[charred fractal]

You probably want to flip your paper so the customer is at the top then branches out, but idk

[errant sable]

I get the following error whenever I try to execute query that has brackets

query ```db.execute("DELETE FROM grocery_item WHERE user_id= %s", (user["id"]), )

error

TypeError: 'int' object does not support indexing

[safe sail]

I remember having an issue like this:

Try:

db.execute("DELETE FROM grocery_item WHERE user_id= %s", (user["id"], ))

[cinder sierra]

First though, look at what your DB library suggests as its protection against injections

And use it instead of %s

But smye is right, it expects a tuple

@errant sable

[errant sable]

placing the comma inside the parentheses fixed the issue

[dusty helm]

guys, i'm new to databases, so i'll ask a tip, i am making a game and i want to know the following:

my game is a space game (classic) where you have to mine asteroids and stuff, you have 4 races, and each race has its own balancing.

it's better to create a class User AND a class Player

or make them one

?

[cinder sierra]

One

Don't repeat the table if it references the same thing

[dusty helm]

hmmm

got it

thank you

[torn sphinx]

hello

whoever knows mongodb

do you know how to make an index required

so that if your try to insert a document without that index it wont allow it

please mention me if you know the answer to this

[toxic rune]

I usually query characters like:

SELECT * FROM MyTable WHERE name ~* "blabla";
However I need to query multiple ones, how can I achieve the same effect as ~* but with a IN (...) query? If it's possible. Related to this, sometimes ~* won't give me a result, is it possible to know which one of the item on the list gave no result from the IN query condition?

[torn sphinx]

    query = "INSERT INTO books(title,isbn) " \
            "VALUES(%s,%s)"```

can someone explain what VALUES(%s, %s) means

[torn sphinx]

%s of course means string, aint it?

[cinder sierra]

Yes. Placeholders for string values

Check the DB library you're using for what you should be putting there instead of %s. That's liable to SQL injection

[solid void]

%s can be a fine placeholder, just don't use % after the string to replace it, but pass the params to the sql call

https://www.python.org/dev/peps/pep-0249/#paramstyle

i prefer "named" and "pyformat" styles if they are available, as it's more legible and easier to update

[cinder sierra]

not entirely sure but i can say, you'll want to swap to an async db library (not sure what there is for mysql) so that your queries don't block the bot's process

also @solid void i'm curious in case you know the answer, will all libraries support any of those or only some?

when i got into sqlite ages ago everything i saw said sqlite only supported qmark and that others only supported one and not others as well or something like that

[solid void]

i think libraries are free to implement any number of these, so you'll want to check the doc of the specific lib you want to use

i'm happily using named (:variable_name) with sqlite these days

[cinder sierra]

ty

[dusty helm]

hey guys, is there any site or source of "ready to use" models for databases?

[runic pilot]

python's cookiecutter library has some good stuff, I used to use this template a lot https://github.com/cookiecutter-flask/cookiecutter-flask

[torn sphinx]

https://gyazo.com/19ff817e1a325fa45c1d298992290e6a
im getting this error

announcement_type = request.form.get('xpmsgs')
lvlup_msg = request.form.get('lvlupmsg')
db.execute("UPDATE xpmsgs SET whereto = %s AND levelupmessage = %s WHERE guildid = %s", announcement_type, lvlup_msg, int(id))

this is how im running it
https://gyazo.com/920c220b5bef79eff601e869ea38ef5d
and this is my table

[cinder sierra]

@torn sphinx placeholders expect a tuple

[torn sphinx]

what place holder

[cinder sierra]

db.execute('query', (arg1, arg2, arg3))

%s

[torn sphinx]

all my other queries word fine

[cinder sierra]

Give it a tuple and lmk if it works

[torn sphinx]

ok

[cinder sierra]

Just add parentheses around them

[torn sphinx]

nope, same error

[cinder sierra]

Can I see the code?

I may have explained the tuple thing wrong

[torn sphinx]

announcement_type = request.form.get('xpmsgs')
lvlup_msg = request.form.get('lvlupmsg')
xpmsgs = db.execute("SELECT * FROM xpmsgs WHERE guildid = %s", int(id)).fetchone()
if xpmsgs is None:
    db.execute("INSERT INTO xpmsgs VALUES(%s, %s, %s)", (announcement_type, lvlup_msg, int(id)))
else:
    db.execute("UPDATE xpmsgs SET whereto = %s AND levelupmessage = %s WHERE guildid = %s", (announcement_type, lvlup_msg, int(id)))

I doubt it's because its not in a tuple

[cinder sierra]

That's been it for every other syntax error for me in every DB library I've used for python