#databases | Python | Page 97

blazing void Jul 10, 2020, 12:28 PM

#

dump a bunch of data into the table and try it

frosty tundra Jul 10, 2020, 12:28 PM

#

how about two million entries and several thousand shards

blazing void Jul 10, 2020, 12:28 PM

#

if you're this worried about optimization, best thing to do is benchmark it

#

your simultaneous queries would be a problem

#

but that's going to be a problem regardless of database structure

runic pilot Jul 10, 2020, 12:29 PM

#

honestly you're using a database for the exact use case it's designed for

frosty tundra Jul 10, 2020, 12:29 PM

#

I mean, the database is clustered

blazing void Jul 10, 2020, 12:29 PM

#

if you really have something like this, then you'd probably want to go to memcache

frosty tundra Jul 10, 2020, 12:29 PM

#

i can implement a cache system

#

and update it every so often

runic pilot Jul 10, 2020, 12:30 PM

#

don't do that unless you're observing a reason to

#

if you don't have slow queries, you don't need to fix your slow queries

blazing void Jul 10, 2020, 12:30 PM

#

consider that the upgrade path

frosty tundra Jul 10, 2020, 12:30 PM

#

i just want a bunch of tips on how to optimize the querying so that thousands of servers can all access one database cluster with millions of entrys. That is my goal

#

shards* not servers

blazing void Jul 10, 2020, 12:31 PM

#

a single non-replicated database should be fine for what you said

frosty tundra Jul 10, 2020, 12:31 PM

#

you think so?

#

so you think bots such as Dyno use just one table for their server config

blazing void Jul 10, 2020, 12:31 PM

#

in terms of the query yes

frosty tundra Jul 10, 2020, 12:31 PM

#

if they're just getting one entry at a time

blazing void Jul 10, 2020, 12:31 PM

#

but in terms of number of simultaneous connections? you might have a latency issue

frosty tundra Jul 10, 2020, 12:31 PM

#

any ideas on how to minimize that?

runic pilot Jul 10, 2020, 12:32 PM

#

but you should also be realistic about the number of connections and look at the issues as they happen

blazing void Jul 10, 2020, 12:32 PM

#

here's the thing

#

there are lots and lots of options

#

right now you are speculating on all the possibilities and trying to get a hole in one with your architecture and schema

#

this is a bad idea

runic pilot Jul 10, 2020, 12:33 PM

#

you won't go from 0 to 10,000 simultaneous requests without hitting 250 first

#

it'll grow over time and you'll find out where the holes are

frosty tundra Jul 10, 2020, 12:33 PM

#

any tips on how to minimize latency?

blazing void Jul 10, 2020, 12:33 PM

#

use an in-memory datastore

frosty tundra Jul 10, 2020, 12:33 PM

#

so cache?

runic pilot Jul 10, 2020, 12:33 PM

#

properly structured indexes

blazing void Jul 10, 2020, 12:34 PM

#

is that a worthwhile choice? probably not

#

everything's a tradeoff

frosty tundra Jul 10, 2020, 12:34 PM

#

well I mean, cache in the shard

runic pilot Jul 10, 2020, 12:34 PM

#

a cache will be trading one set of problems for another

frosty tundra Jul 10, 2020, 12:34 PM

#

so it only updates every so often

runic pilot Jul 10, 2020, 12:34 PM

#

databases do that already

#

you don't need to do that on top of it

frosty tundra Jul 10, 2020, 12:34 PM

#

instead of always querying the database every time I need config

blazing void Jul 10, 2020, 12:34 PM

#

exactly, you're sort of implying that you can "max out your stats"

#

you can't, you can only move the points around

#

and one of those stats is "how long is it going to take me to build this thing" and "how much is it going to cost to maintain"

frosty tundra Jul 10, 2020, 12:35 PM

#

so what do you think my best bet in terms of data storage and latency?

runic pilot Jul 10, 2020, 12:35 PM

#

sql with good indexes

frosty tundra Jul 10, 2020, 12:36 PM

#

define a "good index", currently they are just guild IDs, is that good?

blazing void Jul 10, 2020, 12:36 PM

#

I think your best bet is starting simple and finding out what the issue is when you look at the actual performance and then use that to perform benchmarks

runic pilot Jul 10, 2020, 12:37 PM

#

if you're only querying using guildIds, then yes, that's a great index

frosty tundra Jul 10, 2020, 12:37 PM

#

well querying using guild ids and the config column name

#

that sounds good?

runic pilot Jul 10, 2020, 12:38 PM

#

I think so

#

definitely good enough to start

blazing void Jul 10, 2020, 12:38 PM

#

I have a 2 million row table with UUID key

#

guess how long it takes to query?

frosty tundra Jul 10, 2020, 12:39 PM

#

so you say, go with one table, using the guild ID as index to start, then see what happens from there?

#

How long?

runic pilot Jul 10, 2020, 12:40 PM

#

yeah, that'd be fine

blazing void Jul 10, 2020, 12:41 PM

#

how long do you think would make it not workable for you? bearing in mind parallel queries, and etc.

runic pilot Jul 10, 2020, 12:41 PM

#

here's an example of my latency distribution for a 150MM row table

📎 Screen_Shot_2020-07-10_at_08.40.59.png

#

median response time is 40ms

blazing void Jul 10, 2020, 12:43 PM

#

yep, I get 30ms

#

bearing in mind, this is a 1cpu instance with 4GB RAM because it doesn't get much traffic

frosty tundra Jul 10, 2020, 12:44 PM

#

ok, thanks

runic pilot Jul 10, 2020, 12:45 PM

#

also, I won't tell you to switch to postgresql, but it has a feature that lets you add indexes without locking table access

#

so if you need to add a new index while it's being used, you won't see much of a hit while doing it

sick dragon Jul 10, 2020, 1:02 PM

#

how do i use select using psycopg2? i don't understand how fetchall works

sick dragon Jul 10, 2020, 2:01 PM

#

nvm

#

well

#

should i commit when selecting things on a db? (psycopg2)?

#

oof

#

hello?

#

:(

runic pilot Jul 10, 2020, 2:24 PM

#

no need to commit after selecting

#

in fact, if you're only using a transaction to select things, the typical pattern is to rollback to make sure you don't change anything that might've accidentally happened

sick dragon Jul 10, 2020, 2:39 PM

#

okay

#

welp

#

what's the difference between commit and rollback?

blazing void Jul 10, 2020, 2:47 PM

#

as their name suggests

#

when you make changes in a transaction, these changes aren't applied permanently to the database until you commit

#

rollback to cancel the changes

sick dragon Jul 10, 2020, 3:20 PM

#

@torn sphinx that's wrong syntax

#

UPDATE table SET value1 = 'one', value2 = 'two' WHERE something = something```

torn sphinx Jul 10, 2020, 5:55 PM

#

what is the postgres user password on ubuntu?

#

i install postgress, make role and create db, but never ask me for password

#

but i need enter password into my python connection

blazing void Jul 10, 2020, 6:00 PM

#

might be postgres

#

but not sure

#

username also postgres try that for a default

torn sphinx Jul 10, 2020, 6:00 PM

#

how to change the pwd?

#

for a role

blazing void Jul 10, 2020, 6:03 PM

#

there's a postgres command for it

#

don't remember exactly

#

but log in using psql

torn sphinx Jul 10, 2020, 6:03 PM

#

I tried like this: ALTER ROLE super WITH PASSWORD 'secret123'; to change password

#

but when i run python it gives error

#

that password auth failed

blazing void Jul 10, 2020, 6:05 PM

#

try logging in with psql first

#

then issue \password command

#

but also, if you don't have the right credentials, maybe google how to reset it, there is a way (I don't remember it)

torn sphinx Jul 10, 2020, 6:09 PM

#

ah ok worked thanks

#

i had do alter user username with encrypted password 'xxxx'

placid chasm Jul 10, 2020, 6:39 PM

#

If you finish a development SQL using mySQL, whats the steps to learning in python?

cloud sleet Jul 10, 2020, 7:07 PM

#

If you finish a development SQL using mySQL, whats the steps to learning in python?
@placid chasm start connecting mySQL to python

#

If you finish a development SQL using mySQL, whats the steps to learning in python?
@placid chasm And if you are a beginner then you should consider to first learn basics

blazing void Jul 10, 2020, 7:14 PM

#

If you've now learned SQL, and want to take the next step, one possible direction to go is to learn an ORM like SQLAlchemy

round isle Jul 10, 2020, 7:25 PM

#

Guys, can someone recommend me a database I can use

#

I'm very sure I used the wrong terminology there

#

But basically what I'm asking is for some alternatives to SQL Server and MySQL

blazing void Jul 10, 2020, 7:26 PM

#

postgres

round isle Jul 10, 2020, 7:26 PM

#

Is it similar to SQL?

#

Syntax wise

blazing void Jul 10, 2020, 7:26 PM

#

yes

#

full name is PostgreSQL

round isle Jul 10, 2020, 7:26 PM

#

And can it be quickly started/shut down like MySQL?

blazing void Jul 10, 2020, 7:26 PM

#

I guess? you don't normally shut down SQL servers

round isle Jul 10, 2020, 7:27 PM

#

For my usecase I need to be able to

#

That was the biggest advantage of MySQL, because I could instantly shut it down on click

blazing void Jul 10, 2020, 7:27 PM

#

why's that? maybe a different database is better in that case

round isle Jul 10, 2020, 7:27 PM

#

I'm open to suggestions

blazing void Jul 10, 2020, 7:27 PM

#

well, SQL servers aren't supposed to be shut down, the fact that you can is probably somewhat incidental

round isle Jul 10, 2020, 7:27 PM

#

The reason I am not using MySQL is because of the abysmal UI

blazing void Jul 10, 2020, 7:27 PM

#

eh?

round isle Jul 10, 2020, 7:28 PM

#

And really it being very unresponsive at times

blazing void Jul 10, 2020, 7:28 PM

#

SQL servers don't have UI. you are using some kind of db tool (which might be the "official" mysql workbench), there are lots of different tools however

#

you don't have to use MySQL workbench to work with MySQL

round isle Jul 10, 2020, 7:28 PM

#

That might be it, it's the one that came with XAMPP

#

Oh, how can I bypass it?

blazing void Jul 10, 2020, 7:29 PM

#

oh, that might be PHPmyadmin

#

is it web-based?

round isle Jul 10, 2020, 7:29 PM

#

It is

blazing void Jul 10, 2020, 7:29 PM

#

yeah, that's probably PHPMyAdmin

#

try a different tool, I use dbeaver

round isle Jul 10, 2020, 7:29 PM

#

it is PHPMyAdmin

#

Can I use Azure Data Studio for it?

blazing void Jul 10, 2020, 7:29 PM

#

I'm not familiar with that

round isle Jul 10, 2020, 7:29 PM

#

Lemme try

#

I've used it before for SQL Server

blazing void Jul 10, 2020, 7:30 PM

#

looking at the docs, doesn't seem like you can

round isle Jul 10, 2020, 7:30 PM

#

Yeah I can't

#

Rip

#

That probably means that SQL server is also out

#

Fair nuf

blazing void Jul 10, 2020, 7:30 PM

#

DBeaver is general purpose database browser, it can work with several different kinds of database

round isle Jul 10, 2020, 7:30 PM

#

DBeaver

blazing void Jul 10, 2020, 7:31 PM

#

the alternative I was going to mention was SQLite

#

SQLite is a file-based SQL database, which means there is no server

round isle Jul 10, 2020, 7:31 PM

#

...Nope, I have used DBeaver before and I liked it, so I'll stick to it

blazing void Jul 10, 2020, 7:31 PM

#

SQLite is an alternative to MySQL or Postgres

#

the database exists as a file, rather than a server, which means there's no need to shut it down. when your application quits, everything's closed

#

useful for local usage, which I think is the same way you're using XAMPP right now

round isle Jul 10, 2020, 7:32 PM

#

...wait what

#

Like SQL Server is contantly running in the background

blazing void Jul 10, 2020, 7:33 PM

#

well, it's not a server at all, but in most use-cases, it works the same way like having an SQL server

#

you make the same queries. the SQL it uses is mostly the same as other databases

#

the only difference is it lives in a file on your disk, and there's no need to run a separate server. The client library in whatever language you're using will handle the file directly

round isle Jul 10, 2020, 7:34 PM

#

Well then

blazing void Jul 10, 2020, 7:34 PM

#

Incidentally, DBeaver can also open SQLite databases

#

so DBeaver is also a useful tool for inspecting and manually managing your SQLite database

round isle Jul 10, 2020, 7:34 PM

#

Wait I have a question now going into Django

#

Basically, I start my Django app with runserver right

#

But right now I need to open XAMPP as well to run mysql

blazing void Jul 10, 2020, 7:35 PM

#

if you use SQlite, I don't think you'd need XAMPP at all

brazen charm Jul 10, 2020, 7:36 PM

#

yh sqlite is completely stand alone

#

also dont use mysql 😩

blazing void Jul 10, 2020, 7:36 PM

#

mostly because XAMPP is a PHP/Perl stack with an Apache server. But if you're running Django, you're not using any of that, you're just using XAMPP for the database

brazen charm Jul 10, 2020, 7:36 PM

#

its 90000000000% easier to just stick your django app in a docker container

#

and use the postgre image

blazing void Jul 10, 2020, 7:36 PM

#

so if you don't use MySQL and use SQLite or something, then you don't need XAMPP at all

round isle Jul 10, 2020, 7:49 PM

#

Alright, I'll look into PostgreSQL

wraith jewel Jul 10, 2020, 7:52 PM

#

might be more of a webdev questions but I'll start here. I've hit a bit of a wall trying to figure this out and could use some advice. my question requires some context so here's the stackoverflow link. thanks! https://stackoverflow.com/questions/62820427/data-access-permissions-table

Stack Overflow

Data access permissions table?

Just need some help getting over a wall I've hit.
I have a web application that whenever a user signs up, is assigned a License. This License has CustomPermissions attached to it telling what value...

vague shale Jul 10, 2020, 11:21 PM

#

@round isle if your starting just starting with Django and you did not fiddle with the settings.py then you will be using the SQLite database
you won't need the XAMPP app anymore, then you can browser the database file with something like sqlitebrowser

torn sphinx Jul 10, 2020, 11:24 PM

#

using asyncpg executemany is really slow

#

   async def on_ready(self):
        await self.db.run()
        await self.db.add_guilds(self.guilds)
        await self.db.add_users([m for g in self.guilds for m in g.members])
        # print(await self.db.get_all_users())
        print(f'Logged in as {self.user}')
        print('-----------------------')```

#

    async def add_guilds(self, guilds: [discord.Guild]):
        await self.conn.executemany("insert into guilds (id, msg_xp, level_msg) values ($1, $2, $3) on conflict do "
                                    "nothing", [(guild.id, 0, False) for guild in guilds])

    async def add_users(self, members: [discord.Member]):
        await self.conn.executemany("insert into users (id, chat_level) values ($1, $2) on conflict do "
                                    "nothing", [(member.id, 0) for member in members])```

#

the bot is only in 2 servers

#

and this takes like a full minute

#

is there something I'm doing wrong?

#

one of the servers has 1700 members the other has like 40

#

but I would think it wouldn't take so long

#

ok I used copy_records_to_table and it's much faster

#

but now I have to manually check that it's not already in the db

round isle Jul 11, 2020, 5:14 AM

#

Yeah, I don't want to use SQLite, that's why I asked

boreal steppe Jul 11, 2020, 6:55 AM

#

Hey guys - I have one table in a database which I am going to use to serve to users of a web app. Essentially the table will be updated continously (~every 1-2 seconds) from a backend service and the web app will also call a flask API to read data from this table very frequently (~every 15-30 seconds) - Is there anything that I should be aware of when doing this or anything I should do to ensure stability? The table should be around 30k records with about 20-30 columns and each request from the web app will pull back a maximum of 50 results

#

The database is going to be posgresql

oak bane Jul 11, 2020, 9:02 AM

#

hi is it possible to store a value in DB?

#

i mean MySql/Oracle DB

torn sphinx Jul 11, 2020, 9:38 AM

#

that's sorta what databases are for :v

#

what do you mean by ...value

south meadow Jul 11, 2020, 9:40 AM

#

result = cursor.execute('SELECT user_id FROM economy WHERE user_id = ?', ctx.author.id).fetchone()```
This shows - ``parameters are of unsupported type``
How do I fix it?

#

I tried another method.

class database:

    def field(self,command, *values):
        cursor.execute(command, tuple(values))
        fetch = cursor.fetchone()
        if fetch is not None:
            return fetch[0]
        return```
```py
result = database.field('SELECT user_id FROM economy WHERE user_id = ?', ctx.author.id)```
This one shows error - ``operation parameter must be str``

brazen charm Jul 11, 2020, 9:51 AM

#

Your column user_id has a STRING constraint on it

#

So do str(CTX.autho...)

south meadow Jul 11, 2020, 9:51 AM

#

Ok lemme try..

#

@brazen charm Hey it throws error - near "456055164886056961": syntax error
And I checked my db, user_id has INTEGER constraint!

#

Actually this works in my phone, but in pc its showing error..

#

The only way I'm able to solve this is by using f-string. But I don't want to use f-string.

brazen charm Jul 11, 2020, 10:31 AM

#

You should still be using ?

#

Just where you pass author I'd string it

south meadow Jul 11, 2020, 10:52 AM

#

I did that only.

#

But it simply doesn't work!

short vortex Jul 11, 2020, 11:45 AM

#

is there a tool for converting sqlite3 syntax into postgresql?

torn sphinx Jul 11, 2020, 12:09 PM

#

I have schema (Postgres) like

CREATE TABLE config(
  disabled_channel_ids bigint[] DEFAULT array[]::bigint[],
  current_raffles jsonb
);```

and queries like
```sql
UPDATE config SET disabled_channel_ids = array_append(disabled_channel_ids, 1213::bigint);

SELECT * FROM config;

but it's not doing anything. no errors but doesn't work either. can someone tell me what's the problem here?

frosty tundra Jul 11, 2020, 12:14 PM

#

is there a way I can transfer an sqlite db to mysql

#

(something with server support)

brazen charm Jul 11, 2020, 1:54 PM

#

@short vortex pretty sure any sqlite queries are valid Postgre queries

short vortex Jul 11, 2020, 1:54 PM

#

srr i meant flask syntax

#

like in sqlite you do ? and in postgres %s

brazen charm Jul 11, 2020, 1:54 PM

#

that depends on the drive

#

psycopg2 is %s yes but postgre's system is $1 univerally

#

but psycopg2 like to be dumb

#

@torn sphinx do you not COMMIT;

torn sphinx Jul 11, 2020, 1:55 PM

#

I need to commit after that?

brazen charm Jul 11, 2020, 1:56 PM

#

i mean how else do you expect it to save?

wispy loom Jul 11, 2020, 2:02 PM

#

Hello

#

So guys accessing a pickle database vs pandas dataframe on ram. Pickle would be as fast as reading data in of dataframe?

#

Of course I'll need to import the data in pickle as a dataframe

torn sphinx Jul 11, 2020, 2:12 PM

#

idk i never had to commit when using insert queries so i wasn't sure

short vortex Jul 11, 2020, 2:21 PM

#

you have to commit after insert queries unless you specify auto commit in your config

#

you probably dont need to commit if you didnt need with insert

ornate harness Jul 11, 2020, 3:03 PM

#

Heres a simple question, but I was having some trouble in wording it for a google search 😄
Lets say I have 2 data tables (A and B) and a table that links them both together (X). If I had classes application-side that represents this data, would it be best to run one JOIN query that fetches all the data or run 3, one to select a row from X, then 2 more to query the data tables?

#

Not really looking in terms of speed, just sort of a standard

blazing void Jul 11, 2020, 3:07 PM

#

this is a many-to-many relationship, and you should JOIN

#

table X is called a junction table

#

(or join table, or associative table)

#

see: https://en.wikipedia.org/wiki/Associative_entity

Associative entity

An associative entity is a term used in relational and entity–relationship theory. A relational database requires the implementation of a base relation (or base table) to resolve many-to-many relationships. A base relation representing this kind of entity is called, informall...

#

I hope these words are useful for your search

ornate harness Jul 11, 2020, 3:08 PM

#

Thanks for the specific terms, I did know this just forgot

#

😛

#

So in that diagram, say actor had a lot of columns and so did film

#

You should still do a join, and receive all info about the actor and film in one row

blazing void Jul 11, 2020, 3:09 PM

#

I think so yes

ornate harness Jul 11, 2020, 3:09 PM

#

hmm ok

glad spoke Jul 11, 2020, 3:13 PM

#

query = "UPDATE bump SET bumps=bumps+1, last_bump= the current timestamp WHERE memberID=" + author_id

Quick question, could someone please tell me how I'd add a timestamp to my update query so I can put it into my database? It would go after last_bump=

patent glen Jul 11, 2020, 3:13 PM

#

don't build sql queries with string concatenation

#

what kind of database are you using?

#

with sqlite you can do ```py
cursor.execute("update bump set bumps=bumps+1, last_bump=datetime('now') where memberid = ?", [author_id])

#

you could also generate the timestamp in python instead, if you're going to need to use it after the query

#

using time.strftime or stuff in the datetime module, and then ```py
cursor.execute("update bump set bumps = bumps+1, last_bump = ? where memberid = ?", [timestamp, author_id])

glad spoke Jul 11, 2020, 3:17 PM

#

I am using mySQL

patent glen Jul 11, 2020, 3:23 PM

#

ok then it's basically the same but %s instead of ? (despite the appearance of %s this isn't really the same thing as string formatting, as long as you do not use "query" % [arguments])

#

er

#

wait what is the type of the last_bump column

#

sqlite doesn't have a real datetime so i was assuming string but in mysql what are you using

#

and which mysql library are you using

#

you can probably use current_timestamp() [instead of datetime('now')] if you don't need to generate the timestamp in python

glad spoke Jul 11, 2020, 3:27 PM

#

it is a timestamp

📎 unknown.png

patent glen Jul 11, 2020, 3:28 PM

#

ok i think you can pass a string in YYYY-MM-DD HH:MM:SS format into that, and that is what current_timestamp() returns

#

what is the type of memberID and author_id ?

glad spoke Jul 11, 2020, 3:29 PM

#

authord_id is a string

#

even though its just got numbers in

#

memberID is an int so there is probably an issue there

#

but if I set author_id to an int would it not cause issues with my statement query since that is a string?

patent glen Jul 11, 2020, 3:32 PM

#

like i said

#

don't do "update..."+author_id

#

use the execute with list of parameters syntax

#

you haven't shown me the line where you call execute so i can't rewrite the whole thing for you

glad spoke Jul 11, 2020, 3:32 PM

#

 query = "UPDATE bump SET bumps=bumps+1, last_bump= %s WHERE memberID=" + author_id
                values = (datetime.datetime.utcnow(),)
                cursor.execute(query, values)
                db.commit()

patent glen Jul 11, 2020, 3:32 PM

#

but for an example py cursor.execute("update bump set bumps = bumps+1, last_bump = current_timestamp() where memberid = %s", [author_id])

#

or you could do that

#

er i don't know if datetime works directly

#

but then

glad spoke Jul 11, 2020, 3:33 PM

#

neither do I

patent glen Jul 11, 2020, 3:33 PM

#

query = "UPDATE bump SET bumps=bumps+1, last_bump=%s WHERE memberID=%s"
values = (datetime.datetime.utcnow(),author_id)

glad spoke Jul 11, 2020, 3:35 PM

#

hmm ok, thanks. I have a feeling datetime doesn't work though so I will need to find an alternative.

patent glen Jul 11, 2020, 3:36 PM

#

like i said, you could use current_timestamp() in sql, or once you have utcnow() call .strftime() on it

glad spoke Jul 11, 2020, 3:37 PM

#

ah ok

grim lotus Jul 11, 2020, 7:38 PM

#

📎 Screenshot_2020-07-12-01-08-05-70_572064f74bd5f9fa804b05334aa4f912.jpg

#

Just ping me up while u answer

near solar Jul 12, 2020, 1:51 AM

#

Does anyone have any experience connected to a .sdf DB with Python. I just have the file, so something like pyodbc would be necessary? I am really not sure where to start

south meadow Jul 12, 2020, 2:44 AM

#

📎 Screenshot_116.png

#

This is database class

#

📎 Screenshot_117.png

#

And this is a command

#

It always throws this error - operation parameter must be str

#

How can I fix it??

steady epoch Jul 12, 2020, 2:49 AM

#

Well that's right @south meadow it's correct error

#

Emoji is an str

#

U r giving emoji as int

#

And u also haven't made connection

#

So how it will work

south meadow Jul 12, 2020, 2:50 AM

#

Wdym

#

Check the class

steady epoch Jul 12, 2020, 2:51 AM

#

Lol haven't scrolled up

south meadow Jul 12, 2020, 2:51 AM

#

And what emoji has to do with database?

#

xD

#

Is this error coming cuz of database or something because of emoji? @steady epoch

#

Check the class bruh, it has fetchone() func

steady epoch Jul 12, 2020, 2:54 AM

#

I delete my message that's y

#

Bcz I seen that

south meadow Jul 12, 2020, 2:54 AM

#

Lol

steady epoch Jul 12, 2020, 2:54 AM

#

Send full error

#

One line aren't helpful that much

south meadow Jul 12, 2020, 2:55 AM

#

It shows 1 line only lmao

#

Wait a sec

steady epoch Jul 12, 2020, 2:55 AM

#

😅

#

Check correctly

#

It must have shown multiple lines

#

@south meadow is your user id is defined as int or str when u created db?

#

Check that

south meadow Jul 12, 2020, 2:59 AM

#

@steady epoch user_id is INT in db

#

And also it doesn't show any error without try/except

steady epoch Jul 12, 2020, 2:59 AM

#

Can I check how r u creating your db?

south meadow Jul 12, 2020, 2:59 AM

#

kk

#

📎 Screenshot_118.png

#

@steady epoch

steady epoch Jul 12, 2020, 3:04 AM

#

@south meadow remove emoji

#

And try to run command

south meadow Jul 12, 2020, 3:04 AM

#

umm ok!

steady epoch Jul 12, 2020, 3:05 AM

#

Check whether it works or not

south meadow Jul 12, 2020, 3:13 AM

#

@steady epoch Bruh! Neither it's showing any error nor it's replying....tf!

steady epoch Jul 12, 2020, 3:14 AM

#

@south meadow do u have data stored there?

#

@south meadow have u imported sqlite3

south meadow Jul 12, 2020, 3:15 AM

#

Yes

#

I have data stored there.

steady epoch Jul 12, 2020, 3:16 AM

#

The file in which u r using this command have u imported sqlite3

south meadow Jul 12, 2020, 3:16 AM

#

Everything works fine if I run it in my phone.

#

The file in which u r using this command have u imported sqlite3
@steady epoch yep

steady epoch Jul 12, 2020, 3:16 AM

#

Then may be your comp issue

#

If in your phone everything works fine

south meadow Jul 12, 2020, 3:18 AM

#

@steady epoch No I hosted it on heroku and tried there but then also same error comes...

steady epoch Jul 12, 2020, 3:33 AM

#

@south meadow heroku don't support sqlite3

#

It only support postgres sql

#

That is also limited

south meadow Jul 12, 2020, 3:37 AM

#

@steady epoch No it does support

#

My friend uses sqlite3 in heroku

steady epoch Jul 12, 2020, 3:37 AM

#

Ok

south meadow Jul 12, 2020, 3:37 AM

#

And I managed to find the full error..

#

Ignoring exception in command balance:
Traceback (most recent call last):
  File "C:\Users\harsi\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
    ret = await coro(*args, **kwargs)
  File "d:/Unusual_Coder/bot.py", line 80, in balance
    raise e
  File "d:/Unusual_Coder/bot.py", line 66, in balance
    result = database.field('SELECT user_id FROM economy WHERE user_id = ?', ctx.author.id)
  File "d:/Unusual_Coder/bot.py", line 33, in field
    cursor.execute(command, tuple(values))
ValueError: operation parameter must be str

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

Traceback (most recent call last):
  File "C:\Users\harsi\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\harsi\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\harsi\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: operation parameter must be str```

#

I tried it in another bot

#

and full error came huh

#

@steady epoch

steady epoch Jul 12, 2020, 3:51 AM

#

Well your error explains what u need to do

#

Don't pass tuple pass str @south meadow

south meadow Jul 12, 2020, 3:56 AM

#

I did that too..

#

It says parameters are of unsupported type!

#

@steady epoch

south meadow Jul 12, 2020, 4:11 AM

#

I'm fed up with this...

#

I'll use f-string now huh!

steady epoch Jul 12, 2020, 4:27 AM

#

cur.execute(f"SELECT clan_tag from clan WHERE discord_id = {member.id}")

#

@south meadow I do like this

south meadow Jul 12, 2020, 4:30 AM

#

I know I can do that..

#

That works also..

#

But I don't want to use f-string...

#

@steady epoch

steady epoch Jul 12, 2020, 4:31 AM

#

Well then I don't know

#

I do that way only

#

I don't try different stuff unless I feel to change @south meadow

south meadow Jul 12, 2020, 4:43 AM

#

@steady epoch You should not use f-stings because it makes your code and db vulnerable and might get an SQL Injection attack!

steady epoch Jul 12, 2020, 4:43 AM

#

🤔

glad rune Jul 12, 2020, 5:17 AM

#

Hi all, I'm learning Python & following this tutorial (https://realpython.com/flask-by-example-implementing-a-redis-task-queue/). It's really good but I'm having this odd error where I can't add items to postgres DB after I link a Redis worker to Python. The thing I don't get is that postman gives me a 200 & so does the terminal. The error occurs in line 63 of app.py in this repo (https://github.com/MartinScriblerus/LanguageProcessing)... here is the error-causing code:
try:
result = Result(
url=url,
result_all=raw_word_count,
result_no_stop_words=no_stop_words_count
)
db.session.add(result)
db.session.commit()
except:
errors.append("Unable to add item to database.")
return render_template('index.html', errors=errors, results=results)

torn sphinx Jul 12, 2020, 5:57 AM

#

anyone know any good services to host databases. i kinda dont want to use aws

south meadow Jul 12, 2020, 7:36 AM

#

anyone know any good services to host databases. i kinda dont want to use aws
@torn sphinx if you are looking for free ones, then go for glitch

deep maple Jul 12, 2020, 8:53 AM

#

def databaseWrite(guild, data_write, data_in):
    conn = sqlite3.connect('./Servers/DB/settings.sqlite')
    cursor = conn.cursor()
    if cursor.execute(f"SELECT COUNT(*) FROM messages WHERE guild_id={str(guild)} AND {data_in}={str(data_write)}").fetchone()[0] != None:
        try:
            print("Print database 0/1")
            cursor.execute(
                f"""INSERT INTO messages({str(data_in)}, guild_id) VALUES({str(data_write)}, {str(guild)})""")
            conn.commit()
            print("Print database 1/1")
        except Exception as e:
            print(e)
            return

    else:
        print("Reload database 0/1")
        cursor.execute(
            f"UPDATE messages SET {data_in}={str(data_write)} WHERE guild_id={str(guild)}")
        conn.commit()
        print("Reload database 1/1")

#

I do that but I always have the mm bp each time it prints and it does not relaod

#

when I launch the commabde it rewrites the value below

#

Ho no

#

It's good

lucid wharf Jul 12, 2020, 9:19 AM

#

hi! i really need someone who's good at pymongo and know discord.py too

#

i'd like to confide if my code will work

#

because i don't wanna mess things up at my collection

grim lotus Jul 12, 2020, 10:42 AM

#

Whats the difference in both

#

The second works fine while first js throwing errors

#

Ignore .ojsk py its command of my bot

fluid tusk Jul 12, 2020, 10:44 AM

#

the ";"

#

?

grim lotus Jul 12, 2020, 10:44 AM

#

Oh shit wrong ss

#

📎 Screenshot_2020-07-12-16-15-23-33_572064f74bd5f9fa804b05334aa4f912.jpg 📎 Screenshot_2020-07-12-16-15-11-94_572064f74bd5f9fa804b05334aa4f912.jpg

#

These 2

brazen charm Jul 12, 2020, 10:47 AM

#

@grim lotus Postgre doesnt support "" as representing a string type

#

'' is valid

grim lotus Jul 12, 2020, 10:47 AM

#

GWergKermeme

#

For god sake thanksss alot

hazy peak Jul 12, 2020, 12:41 PM

#

Hey guys, can this query be translated to Sqlalchemy? I am unable to find any proper examples.

UPDATE users
SET amount =
CASE
  WHEN exp BETWEEN 10 AND 199
    THEN 10
  WHEN exp BETWEEN 200 AND 499
    THEN 20
  WHEN exp BETWEEN 500 AND 999
    THEN 40
  WHEN exp BETWEEN 1000 AND 1999
    THEN 100
  ELSE amount
END

brazen charm Jul 12, 2020, 12:43 PM

#

why do it in SqlAlchemy when you've made the query??

hazy peak Jul 12, 2020, 12:44 PM

#

I am refactoring my code and wanted to move all manual queries to sqlalchemy.

hazy peak Jul 12, 2020, 1:48 PM

#

nvm, figured it out xd

ivory rain Jul 12, 2020, 2:43 PM

#

Hi all, been directed here from the help channels.

I am a bit of a novice to Python but am learning by doing.

Currently working on trade data analysis and so, I've requested data from an exchange's API for a given interval of 1hour but this provides "tick by tick" data for that whole hour, however, the OHLC data I am interested in happens in the last tick of that hour. Does anyone know of a reasonably straightforward method to access the last tick slice in each hour so I could pull the OHLC (and V if wanted) so I can populate a new CSV file?

grim lotus Jul 12, 2020, 3:21 PM

#

how to use redis for cache in Python i m using psql at the time

safe mirage Jul 12, 2020, 4:39 PM

#

@glad rune Can you specify what code you think is the problem?

glad rune Jul 12, 2020, 4:41 PM

#

for sure... (it is just a novice guess... the tutorial is really good by the way...)

delicate fieldBOT Jul 12, 2020, 4:41 PM

#

Hey @glad rune!

Uh-oh! It looks like your message got zapped by our spam filter. We currently don't allow .txt attachments, so here are some tips to help you travel safely:

• If you attempted to send a message longer than 2000 characters, try shortening your message to fit within the character limit or use a pasting service (see below)

• If you tried to show someone your code, you can use codeblocks
(run !code-blocks in #bot-commands for more information) or use a pasting service like:

https://paste.pythondiscord.com

glad rune Jul 12, 2020, 4:43 PM

#

app = Flask(name)
app.config.from_object(os.environ.get('APP_SETTINGS'))
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)

q = Queue(connection=conn)

#I THINK THIS IS THROWING THE ERROR
from models import *

#Can't find a results table in here
def count_and_save_words(url):

#

that is a condensed version of app.py

#

from app import db
from sqlalchemy.dialects.postgresql import JSON

class Result(db.Model):
tablename = 'results'

id = db.Column(db.Integer, primary_key=True)
url = db.Column(db.String())
result_all = db.Column(JSON)
result_no_stop_words = db.Column(JSON)

def __init__(self, url, result_all, result_no_stop_words):
    self.url = url
    self.result_all = result_all
    self.result_no_stop_words = result_no_stop_words

def __repr__(self):
    return '<id {}>'.format(self.id)

#

this is all of models.py

#

I'm getting a SQLAlchemy exc.operational error that there is no such table called 'results' in db

#

or I get an Assertion or an Attribute error when I move things around.

safe mirage Jul 12, 2020, 4:48 PM

#

Assuming your model still just contains Result, have you tried from models import Result instead of *?

glad rune Jul 12, 2020, 4:49 PM

#

I'll try that

#

hmm... sqlalchemy.exc.OperationalError
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: results
[SQL: SELECT results.id AS results_id, results.url AS results_url, results.result_all AS results_result_all, results.result_no_stop_words AS results_result_no_stop_words
FROM results

safe mirage Jul 12, 2020, 4:51 PM

#

Have you run your database migrations?

glad rune Jul 12, 2020, 4:51 PM

#

ah... let me try that...

#

I had run them initially & it worked but hadn't run again since updatiing w/ redis queue

safe mirage Jul 12, 2020, 4:54 PM

#

Did that fix it for you?

glad rune Jul 12, 2020, 4:55 PM

#

that's new. would that make sense given redis updates (in a worker.py file)?

lucid needle Jul 12, 2020, 5:01 PM

#

can anyone help me with aiosqlite3

#

so i am changing my sqlite3 codes to aiosqlite3 and thx for @zinc harness i messed up

#

and i think it can be fixed

#

but idk how

#

it has few problems

#

most of them is about aiosqlite3

torn sphinx Jul 12, 2020, 5:19 PM

#

Hello! So im a little suck trying to host a database on heroku
I was wondering if anyone knew some stuff about how we can set it up?
If so please tag me <3

glad rune Jul 12, 2020, 5:21 PM

#

Hi @torn sphinx ... parts 1 and 2 of this tutorial discuss postgres on heroku https://realpython.com/flask-by-example-part-2-postgres-sqlalchemy-and-alembic/

Flask by Example – Setting up Postgres, SQLAlchemy, and Alembic – R...

This tutorial shows you how to process text and then setup a task queue with Flask. In part two, we'll set up our PostgreSQL database along with SQLAlchemy and Alembic to handle migrations.

#

I got it to work by following parts 1 & 2 exactly, with the exception of the os.environ variables, which seemed to work with os.environ.get() instead of os.environ[]... otherwise, it worked great for heroku.

torn sphinx Jul 12, 2020, 5:23 PM

#

oki

#

Ill have a look at it ty

fiery spindle Jul 12, 2020, 5:26 PM

#

📎 unknown.png

#

📎 unknown.png

#

Can anybody explain? It's mongoDB. I'm trying to create an restricted user, but I don't see any database other than authentication one, even though I have dbOwner on another one.

radiant elbow Jul 13, 2020, 12:15 AM

#

the cursor.

#

@torn sphinx

#

yes - if you're able to use the Connection at all, that's an extension on top of what the Python DB-API requires, and writing your code to be as compatible with the DB-API as possible will make it more portable to other databases down the line should you ever choose to move it

#

The DB-API only has execute() on Cursor's, not on Connection's.

#

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

Python.org

PEP 249 -- Python Database API Specification v2.0

The official home of the Python Programming Language

radiant elbow Jul 13, 2020, 12:45 AM

#

@torn sphinx no, executemany takes a single statement, and a list of parameters, and runs the same statement repeatedly with different params. And it's unspecified how to get results out if the statement produces result sets, so it's really only useful for DML - insert/update/delete, etc

torn sphinx Jul 13, 2020, 12:45 AM

#

Hey uh, How would I go about connecting to an sqlite3 db file from a different directory?

#

I know how to connect to one in the same but different directories are harder

#

?

radiant elbow Jul 13, 2020, 12:47 AM

#

you just pass the full path to it (either absolute or relative)

#

Windows or Unix, @torn sphinx ?

torn sphinx Jul 13, 2020, 12:48 AM

#

Linux.

#

Aka unix.

radiant elbow Jul 13, 2020, 12:49 AM

#

so you can do something like sqlite.connect('/path/to/my.db')

torn sphinx Jul 13, 2020, 12:49 AM

#

Let me try that

#

Lets say the directory is this:

#

currentdirectory/cogs/my.db

#

do I just do cogs/my.db

radiant elbow Jul 13, 2020, 12:49 AM

#

then cogs/my.db would work.

#

yep.

torn sphinx Jul 13, 2020, 12:49 AM

#

Aye

#

let me try a sec good gent

#

It worked

#

Thanks gent.

#

Damn, python server so friendly isn't it?

bitter bobcat Jul 13, 2020, 1:40 AM

#

result = [code + "⁠—" + name for code, name in zip(df["DX_CODE"], df["DX_NM"])]

I'm trying to join items from two columns together, and I have the above line
However, it breaks whenever code is None
How can I change it so that it can handle Nones?

#

result = [str(code) + "⁠—" + str(name) for code, name in zip(df["DX_CODE"], df["DX_NM"])]```
seems to work, but is that bad practice?

torn sphinx Jul 13, 2020, 2:30 AM

#

python bois raise up! I need some help

#

How would I go about adding money to a balance?

#

Is it a bit like py SQL2.execute(f'update profiles set balance = "+2500"')

radiant elbow Jul 13, 2020, 2:42 AM

#

more like set balance = balance + 2500

torn sphinx Jul 13, 2020, 2:45 AM

#

thank i love you

#

🙂

dry anvil Jul 13, 2020, 6:34 AM

#

I have a question regarding SQLAlchemy, is someone familiar with how relationships are handled in it?

#

So in SQLAlchemy, I've been playing around with it a bit and have a relationship defined with the relationship("RedditUser") method. When I use session.query(DiscordUser).join(RedditUser) I only get users that have one or more of those relationships. But if I do it without the join and just manually access the relationship via DiscordUser.reddit_accounts I see any if they're present, so does this mean I should use join() as a filter so to speak only if I need the users with connected accounts?

gusty valley Jul 13, 2020, 8:10 AM

#

sqlite 3
How I can search some user at 2 phonetics?

frozen charm Jul 13, 2020, 10:58 AM

#

I need help with the Rollback function in sqlite 3
As an example, even if i raise an error in the middle of the execution the first Table drops anyway o.o
Does anybody know why.

#

    if ask == 1:
        try:
            cur.execute("DROP TABLE Projects")
            raise sqlite3.OperationalError
            cur.execute("DROP TABLE Temp")
            print("Droping Database Tables")
        except sqlite3.OperationalError:
            conn.rollback()
            messagebox.showinfo(title="Datenbank Reset", message="Anscheinend sind keine Daten vorhanden.")
        else:
            conn.commit()

brazen charm Jul 13, 2020, 1:24 PM

#

last time i checked SQLite had no rollback system

frozen charm Jul 13, 2020, 1:25 PM

#

https://docs.python.org/3/library/sqlite3.html?highlight=rollback

tough needle Jul 13, 2020, 1:56 PM

#

Ignoring exception in command flarieinfo:
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/dist-packages/discord/ext/commands/core.py", line 83, in wrapped
    ret = await coro(*args, **kwargs)
  File "main.py", line 503, in flarieinfo
    ctries = gettries(message.author.id)
  File "/home/pi/hangit/database.py", line 83, in gettries
    c.execute(sql, (MemberID,))
sqlite3.OperationalError: no such column: Tries

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

Traceback (most recent call last):
  File "/usr/local/lib/python3.7/dist-packages/discord/ext/commands/bot.py", line 892, in invoke
    await ctx.command.invoke(ctx)
  File "/usr/local/lib/python3.7/dist-packages/discord/ext/commands/core.py", line 797, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "/usr/local/lib/python3.7/dist-packages/discord/ext/commands/core.py", line 92, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: no such column: Tries

Why does this come up even though I have a column called Tries?

Code

def gettries(MemberID):
    conn = sqlite3.connect('Flarie.db')
    c = conn.cursor()
    tblFlaire = "Flariepoints"
    sql = f'SELECT Tries FROM {tblFlaire} WHERE MemberID = ?'
    c.execute(sql, (MemberID,))
    Tries = c.fetchone()
    conn.commit()
    conn.close()
    return Tries

(when I call this function)

left scaffold Jul 13, 2020, 1:56 PM

#

Can someone help me send information to a db ?

#

i am very stuck

#

using sqlite3 ??? or should i use mongodb

tough needle Jul 13, 2020, 1:58 PM

#

I can help if you use sqlite

#

do you have a database?

left scaffold Jul 13, 2020, 2:01 PM

#

Not yet @tough needle literally only just started

#

basically i just want to store discord bot data in a db but need help setting it up and then using it for different stuff

#

okay you able to help?

tough needle Jul 13, 2020, 2:03 PM

#

Yes, so first of you should install sqlitebrowser

left scaffold Jul 13, 2020, 2:03 PM

#

I have that

#

installed already

#

hello ???

tough needle Jul 13, 2020, 2:10 PM

#

Then you need to create a database, easiest way to do this is through code

left scaffold Jul 13, 2020, 2:10 PM

#

i mean isnt it easier to make it in the browser?

tough needle Jul 13, 2020, 2:11 PM

#

It's safer to do through code

left scaffold Jul 13, 2020, 2:11 PM

#

okay then

tough needle Jul 13, 2020, 2:11 PM

#

Here's one of my examples:
conn = sqlite3.connect('Flarie.db')
c = conn.cursor()
tblFlaire = "Flariepoints"
sql_create_table = """CREATE TABLE IF NOT EXISTS Flariepoints
(Name text NOT NULL,
Points integer,
MemberID integer PRIMARY KEY)
"""
c.execute(sql_create_table)
conn.commit()

left scaffold Jul 13, 2020, 2:11 PM

#

can i use it?

tough needle Jul 13, 2020, 2:12 PM

#

Yeah sure

#

But you need to switch out the name and functionalities

left scaffold Jul 13, 2020, 2:12 PM

#

thats similar to what i had before then i got told to add in async

tough needle Jul 13, 2020, 2:12 PM

#

So Flarie.db is YourName.db

left scaffold Jul 13, 2020, 2:12 PM

#

kk

tough needle Jul 13, 2020, 2:12 PM

#

Flariepoints is YourTableName

#

Name is column1

#

Points is column 2

#

MemberID is column 3

left scaffold Jul 13, 2020, 2:14 PM

#

@tough needle idk if you know this question but where abouts do i put it in my dpy script? In main one?

tough needle Jul 13, 2020, 2:15 PM

#

Oh I have no idea

left scaffold Jul 13, 2020, 2:15 PM

#

okay one moment

#

okay found it

#

so i have taken out column 3 as i only need guild id and channel id i think

#

@tough needle Done that part whats next?

tough needle Jul 13, 2020, 2:22 PM

#

Now you have a databse and if you want to edit it you need to define a function for it

#

for example:

#

def editcolumn1(Column1name)
conn = sqlite3.connect('dbname.db')
c = conn.cursor()
tblName = "tblname"
sql = f'INSERT INTO {tblName} VALUES ({Column1name})'
c.execute(sql)
conn.commit()
conn.close()

left scaffold Jul 13, 2020, 2:24 PM

#

ok

#

im getting an error where the bracket on the end of (Column1name) it says invalid syntax

tough needle Jul 13, 2020, 2:29 PM

#

add :

left scaffold Jul 13, 2020, 2:30 PM

#

okay now conn is erroring with the same error directly below that line

#

i just realised i did the wrong columns so now there is user, guild and reason does this affect what i already have?

#

what next tho @tough needle

storm stratus Jul 13, 2020, 2:35 PM

#

smol help?

left scaffold Jul 13, 2020, 2:35 PM

#

???

storm stratus Jul 13, 2020, 2:36 PM

#

how can we sort data from an api output

left scaffold Jul 13, 2020, 2:36 PM

#

idk

storm stratus Jul 13, 2020, 2:37 PM

#

:/

glad rune Jul 13, 2020, 3:55 PM

#

Hello, I'm trying to use Postgre and Redis with Python, but my program keeps reverting to SQLite (which is not set up) when I hit the route of my Redis worker. Any idea what might be causing this?

runic pilot Jul 13, 2020, 4:14 PM

#

how are you connecting to the db in your redis worker?

glad rune Jul 13, 2020, 4:21 PM

#

I am using psycopg2-binary and sqlalchemy and running a worker.py file that listens for input on Flask frontend & sends jobs to redis through these lines:

#

from sqlalchemy.dialects.postgresql import JSON
from psycopg2.extensions import register_adapter

register_adapter(dict, JSON)

def count_and_save_words(url):

errors = []

try:
    r = requests.get(url)
except:
    errors.append(
        "Unable to get URL. Please make sure it's valid and try again."
    )
    return {"error": errors}

# text processing
raw = BeautifulSoup(r.text).get_text()
nltk.data.path.append('./nltk_data/')  # set the path
tokens = nltk.word_tokenize(raw)
text = nltk.Text(tokens)

# remove punctuation, count raw words
nonPunct = re.compile('.*[A-Za-z].*')
raw_words = [w for w in text if nonPunct.match(w)]
raw_word_count = Counter(raw_words)
print(raw_words)
# stop words
no_stop_words = [w for w in raw_words if w.lower() not in stops]
no_stop_words_count = Counter(no_stop_words)
print("HERE IS DB JUST PRIOR TO INIT SESSION", db)
# save the results
try:
    result = Result(
        url=url,
        result_all=raw_word_count,
        result_no_stop_words=no_stop_words_count
    )
    print(result)
    print("HERE IS DB JUST PRIOR TO INIT SESSION", db)
    db.session.add(result)
    db.session.commit()
    return result.id
except:
    errors.append("Unable to add item to database.")
    return {"error": errors}

R=0
app = Flask(name)
app.config.from_object(os.environ.get('APP_SETTINGS'))
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)

q = Queue(connection=conn)
print("HERE IS DB JUST PRIOR TO INIT SESSION", db)

#

class Result(db.Model):
tablename = 'results'

id = db.Column(db.Integer, primary_key=True)
url = db.Column(db.String())
result_all = db.Column(JSON)
result_no_stop_words = db.Column(JSON)

def __init__(self, url, result_all, result_no_stop_words):
    
    self.url = url
    self.result_all = result_all
    self.result_no_stop_words = result_no_stop_words

def __repr__(self):
    return '<id {}>'.format(self.id)

@app.route('/', methods=['GET', 'POST'])
def index():
results = {}
if request.method == "POST":
# this import solves a rq bug which currently exists
from app import count_and_save_words

    # get url that the person has entered
    url = request.form['url']
    if not url[:8].startswith(('https://', 'http://')):
        url = 'https://' + url
    job = q.enqueue_call(
        func=count_and_save_words, args=(url,), result_ttl=5000
    )
    print(job.get_id())

return render_template('index.html', results=results)

#

This function makes the postgresql set up disappear & replaces with SQLite.... I believe it's due to this message: 'Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BINDS is set. '

runic pilot Jul 13, 2020, 4:24 PM

#

can you toss that into here so it's easier to read https://hastebin.com/ ?

glad rune Jul 13, 2020, 4:24 PM

#

for sure! thanks!

#

here is a link: https://hastebin.com/raw/efujawiyab

#

so I read this SO & moved models.py class into this app.py, but it still isn't working right: https://stackoverflow.com/questions/57720565/flask-sqlalchemy-neither-sqlalchemy-database-uri-nor-sqlalchemy-binds-is-set-d

Stack Overflow

Flask-SQLAlchemy: Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BI...

I am first time trying flask application factory pattern and pytest framework together.
I started with a basic sanity test for the sqlite db backend and, although the tests are working fine so far ...

runic pilot Jul 13, 2020, 4:29 PM

#

app.config.from_object(os.environ.get('APP_SETTINGS'))

#

what is APP_SETTINGS in your env?

glad rune Jul 13, 2020, 4:30 PM

#

strange ... -bash: syntax error near unexpected token `os.environ.get'

#

does this mean I missed an important step?

runic pilot Jul 13, 2020, 4:30 PM

#

that's python code, not shell code

#

looks like your config is missing SQLALCHEMY_DATABASE_URI

#

https://flask-sqlalchemy.palletsprojects.com/en/2.x/quickstart/#a-minimal-application

#


app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)

#

that's where you specify how to connect to your db

glad rune Jul 13, 2020, 4:32 PM

#

In my initial setup, the terminal says I have a postre database

#

it just changess once I hit that URL route.

runic pilot Jul 13, 2020, 4:32 PM

#

can you show me how it says that?

glad rune Jul 13, 2020, 4:34 PM

#

sure

#

here's a stacktrace from app.py that shows it going to postgre to sqlite (but the actual sqlite shows up in worker.py, which I'll send next) : https://hastebin.com/raw/doyekaseso

runic pilot Jul 13, 2020, 4:38 PM

#

that looks like the stacktrace got cut off at the bottom and it missing the actual exception

glad rune Jul 13, 2020, 4:38 PM

#

ok, here's the worker.py w a bunch of text data from url: https://hastebin.com/raw/geziruzoko

#

sorry! this is the last part of app.py terminal w/ exception regarding JSON "get quoted": File "/Users/matthewreilly/Desktop/LanguageProcessing/env/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
cursor.execute(statement, parameters)
AttributeError: 'JSON' object has no attribute 'getquoted'
127.0.0.1 - - [13/Jul/2020 02:55:04] "GET /results/d8ac945c-1fc8-40de-94c6-71c05ef8def7?debugger=yes&cmd=resource&f=style.css HTTP/1.1" 200 -
127.0.0.1 - - [13/Jul/2020 02:55:04] "GET /results/d8ac945c-1fc8-40de-94c6-71c05ef8def7?debugger=yes&cmd=resource&f=jquery.js HTTP/1.1" 200 -
127.0.0.1 - - [13/Jul/2020 02:55:04] "GET /results/d8ac945c-1fc8-40de-94c6-71c05ef8def7?debugger=yes&cmd=resource&f=debugger.js HTTP/1.1" 200 -
127.0.0.1 - - [13/Jul/2020 02:55:04] "GET /results/d8ac945c-1fc8-40de-94c6-71c05ef8def7?debugger=yes&cmd=resource&f=ubuntu.ttf HTTP/1.1" 200 -
127.0.0.1 - - [13/Jul/2020 02:55:04] "GET /results/d8ac945c-1fc8-40de-94c6-71c05ef8def7?debugger=yes&cmd=resource&f=console.png HTTP/1.1" 200 -
127.0.0.1 - - [13/Jul/2020 02:55:04] "GET /results/d8ac945c-1fc8-40de-94c6-71c05ef8def7?debugger=yes&cmd=resource&f=console.png HTTP/1.1" 200 -

Detected change in '/Users/matthewreilly/Desktop/LanguageProcessing/app.py', reloading
Restarting with stat
Redis<ConnectionPool<Connection<host=localhost,port=6379,db=0>>>
HERE IS DB JUST PRIOR TO INIT SESSION <SQLAlchemy engine=postgresql:///wordcount_dev>
Python-dotenv could not parse statement starting at line 1

forest trout Jul 13, 2020, 4:40 PM

#

python 3.6?

glad rune Jul 13, 2020, 4:40 PM

#

yep

forest trout Jul 13, 2020, 4:40 PM

#

well good luck

glad rune Jul 13, 2020, 4:40 PM

#

is that bad news?

forest trout Jul 13, 2020, 4:40 PM

#

i have no knowledge on this topic yet

#

is that bad news?
probably

#

the website actually says what went went wrong

glad rune Jul 13, 2020, 4:42 PM

#

where should I look?

forest trout Jul 13, 2020, 4:43 PM

#

https://hastebin.com/raw/geziruzoko

#

if you look at this it actually says what line cause the error

glad rune Jul 13, 2020, 4:43 PM

#

app.py 32? from 'Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BINDS is set. '
HERE IS DB JUST PRIOR TO INIT SESSION <SQLAlchemy engine=sqlite:///:memory:>??

forest trout Jul 13, 2020, 4:44 PM

#

The code that caused this warning is on line 32 of the file /Users/matthewreilly/Desktop/LanguageProcessing/app.py. To get rid of this warning, pass the additional argument 'features="html.parser"' to the BeautifulSoup constructor.

glad rune Jul 13, 2020, 4:44 PM

#

ok...

#

cool

forest trout Jul 13, 2020, 4:45 PM

#

sorry if that wasn't helpful

glad rune Jul 13, 2020, 4:45 PM

#

so would that look like raw = BeautifulSoup(r.text, 'features="html.parser"').get_text()?

runic pilot Jul 13, 2020, 4:46 PM

#

no need for the single quotes

#

and that's not causing the issue

#

it's the other warning

#

/Users/matthewreilly/Desktop/LanguageProcessing/env/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py:814: UserWarning: Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BINDS is set. Defaulting SQLALCHEMY_DATABASE_URI to "sqlite:///:memory:".

glad rune Jul 13, 2020, 4:47 PM

#

cool... just checking raw = BeautifulSoup(r.text, features="html.parser").get_text()

runic pilot Jul 13, 2020, 4:47 PM

#

yes 👍

glad rune Jul 13, 2020, 4:47 PM

#

awesome

#

yes! that binds thing is odd

runic pilot Jul 13, 2020, 4:47 PM

#

SQLALCHEMY_DATABASE_URI might be set in your app.py, but it looks like it's not in your worker.py

glad rune Jul 13, 2020, 4:47 PM

#

I read this https://stackoverflow.com/questions/57720565/flask-sqlalchemy-neither-sqlalchemy-database-uri-nor-sqlalchemy-binds-is-set-d

Stack Overflow

Flask-SQLAlchemy: Neither SQLALCHEMY_DATABASE_URI nor SQLALCHEMY_BI...

I am first time trying flask application factory pattern and pytest framework together.
I started with a basic sanity test for the sqlite db backend and, although the tests are working fine so far ...

runic pilot Jul 13, 2020, 4:47 PM

#

try:

SQLALCHEMY_DATABASE_URI='postgresql:///wordcount_dev' python3 worker.py

glad rune Jul 13, 2020, 4:48 PM

#

where would this go?

#

in app.py?

runic pilot Jul 13, 2020, 4:48 PM

#

when you run it in your terminal

glad rune Jul 13, 2020, 4:48 PM

#

ahhhh

#

got it

#

ok so I got this error: AttributeError
AttributeError: 'JSON' object has no attribute 'getquoted'

#

which is in above stack trace. I ran this in virtual env with worker py & also started app.py in virtual env (w another terminal that has redis-server started

runic pilot Jul 13, 2020, 4:50 PM

#

which line causes that?

glad rune Jul 13, 2020, 4:51 PM

#

here is the full; trace: https://hastebin.com/raw/vudoxayugo

#

File "/Users/matthewreilly/Desktop/LanguageProcessing/app.py", line 116, in get_results

#

in context:

#

print("THIS IS CONNECTION", conn)
if job.is_finished:
print(R)
result = Result.query.filter_by(id=job.result).first()
results = sorted(
result.result_no_stop_words.items(),
key=operator.itemgetter(1),
reverse=True
)[:10]
print(jsonify(results))
return jsonify(results)

#

I have these JSON things imported as well (not sure if that's c ausing.) .........

#

from stop_words import stops
from collections import Counter
from bs4 import BeautifulSoup
from sqlalchemy.dialects.postgresql import JSON
from psycopg2.extensions import register_adapter

register_adapter(dict, JSON)

#

did this to stop a "dict" error from psycopg2-binary

glad rune Jul 13, 2020, 5:27 PM

#

does anything in that above code (or the imports) seem like it would set off the JSON object getquoted error?

robust cedar Jul 13, 2020, 6:09 PM

#

okay i have just started doing python again after being forced to in school and hating it, this time its enjoyable but holy shit u guys make it so confusing hahaha

gusty valley Jul 13, 2020, 6:52 PM

#

if cursor.execute('SELECT COUNT(*) as count FROM users WHERE party = ?', (pname,)).fetchone() <= 5:

How fix?

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:\L7ky Party\party.py", line 73, in __invite
    if cursor.execute('SELECT COUNT(*) as count FROM users WHERE party = ?', (pname,)).fetchone() <= 5:
TypeError: '<=' not supported between instances of 'tuple' and 'int'```

normal igloo Jul 13, 2020, 7:05 PM

#

fetchone gives you a record, not a single value

glad rune Jul 13, 2020, 7:11 PM

#

Hi all... been struggling with this SQLAlchemy bind error above (for the past 2 days!!) & I finally got it working thanks to lots of good advice from this forum & especially from @runic pilot. So... if anyone runs into SQLAlchemy binds not being set, the third line below is what fixed it for me:
app = Flask(name)
app.config.from_object(os.environ.get('APP_SETTINGS'))
app.config['SQLALCHEMY_DATABASE_URI'] =
os.environ.get('DATABASE_URL')

db = SQLAlchemy(app)

torn sphinx Jul 13, 2020, 7:46 PM

#

@clear reef

#

e

#

this is better place

clear reef Jul 13, 2020, 7:46 PM

#

ohh

#

📎 unknown.png

#

what now

torn sphinx Jul 13, 2020, 7:46 PM

#

make your table

#

so like

#

consider what you want to store

#

time the warn was made

clear reef Jul 13, 2020, 7:46 PM

#

warns, bans, kicks

#

o

torn sphinx Jul 13, 2020, 7:47 PM

#

ok so do you want it all in the same table then?

clear reef Jul 13, 2020, 7:47 PM

#

i dont know what is more practical

torn sphinx Jul 13, 2020, 7:47 PM

#

well

#

if you have a type column

#

you can use B W or K

clear reef Jul 13, 2020, 7:48 PM

#

hmm

torn sphinx Jul 13, 2020, 7:48 PM

#

and if you want bans for instance select * from punishments where type= 'B' and user_id=1231233213123123

#

cuz bans, warns, and kicks can all have an optional reason

clear reef Jul 13, 2020, 7:48 PM

#

I see SQLite uses C, will i have to use C?

torn sphinx Jul 13, 2020, 7:48 PM

#

nope

clear reef Jul 13, 2020, 7:48 PM

#

okie

torn sphinx Jul 13, 2020, 7:48 PM

#

https://github.com/omnilib/aiosqlite

GitHub

omnilib/aiosqlite

asyncio bridge to the standard sqlite3 module. Contribute to omnilib/aiosqlite development by creating an account on GitHub.

#

thats the library you're gonna wanna use to interact with the db

lean gate Jul 13, 2020, 7:49 PM

#

Hey guys hopefully just a quickie cant get this to work - query = "SELECT * FROM servers.servers WHERE last_updated > NOW() - INTERVAL 1 MINUTE" return await database.fetch_all(query=query)
Im trying to get records that are 1 minute old based on last_updated i am using postgres as my database and sqlalchemy as interface with encode.io/database

clear reef Jul 13, 2020, 7:50 PM

#

o

torn sphinx Jul 13, 2020, 7:50 PM

#

havent used the db or interface personally turbo, so im at a loss blobfearsweat

clear reef Jul 13, 2020, 7:51 PM

#

aoa

lean gate Jul 13, 2020, 7:51 PM

#

No problem im learning it my self, this is my first time doing PY thon and Postgres i do use ASP.NET & MYSQL on a daily tho

clear reef Jul 13, 2020, 7:52 PM

#

uhm what do i do

torn sphinx Jul 13, 2020, 7:52 PM

#

ok so

clear reef Jul 13, 2020, 7:52 PM

#

still stuck on what to do here

📎 unknown.png

torn sphinx Jul 13, 2020, 7:52 PM

#

what data do you wanna store

clear reef Jul 13, 2020, 7:53 PM

#

Warnings at the moment

torn sphinx Jul 13, 2020, 7:53 PM

#

for the warns

#

ok so

clear reef Jul 13, 2020, 7:53 PM

#

Hmm

#

reason, when and amount of warns

torn sphinx Jul 13, 2020, 7:53 PM

#

amount of warns would be in code

clear reef Jul 13, 2020, 7:53 PM

#

Yeah thats understandable

torn sphinx Jul 13, 2020, 7:53 PM

#

you'll want a warn id to clear them later if needed

lean gate Jul 13, 2020, 7:54 PM

#

@clear reef before you start creating a rational databse u should probably look into 3nf and plan it out as it can get messy real fast. think of what data types they need to be and how many tables you need then the create table part is easy.

torn sphinx Jul 13, 2020, 7:54 PM

#

i mean warnings are pretty straightfoward

clear reef Jul 13, 2020, 7:54 PM

#

Yeah i have no idea waht im doing

torn sphinx Jul 13, 2020, 7:55 PM

#

📎 unknown.png

#

this is how my warns table is set up

#

an id that goes 1, 2, 3, 4, ... as more are added to the table

clear reef Jul 13, 2020, 7:55 PM

#

I see

torn sphinx Jul 13, 2020, 7:55 PM

#

staff is the ID of the staff member, user is the ID of the warned user, etc

clear reef Jul 13, 2020, 7:55 PM

#

Ohhh

#

What about infraction_id

#

is that the warn id

torn sphinx Jul 13, 2020, 7:56 PM

#

an id that goes 1, 2, 3, 4, ... as more are added to the table
@torn sphinx

clear reef Jul 13, 2020, 7:56 PM

#

oh oops

#

okie

torn sphinx Jul 13, 2020, 7:56 PM

#

📎 unknown.png

clear reef Jul 13, 2020, 7:56 PM

#

i guess ill add pretty much the same

#

📎 unknown.png

#

?

torn sphinx Jul 13, 2020, 7:58 PM

#

well you dont want certain things to be able to be Null

#

thats what the NN (NOT NULL) checkbox is for

clear reef Jul 13, 2020, 7:58 PM

#

ohh

#

which do i check

torn sphinx Jul 13, 2020, 7:58 PM

#

the ones that have to have a value

clear reef Jul 13, 2020, 7:59 PM

#

o

#

stressed

torn sphinx Jul 13, 2020, 7:59 PM

#

the AI checkbox is AUTOINCREMENT, that column goes 1, 2, 3, 4, 5, etc

#

by itself

clear reef Jul 13, 2020, 8:00 PM

#

????

📎 unknown.png

torn sphinx Jul 13, 2020, 8:00 PM

#

that works

#

except

#

i use cleared to allow warnings to be cleared

#

so it has a default value of 0

clear reef Jul 13, 2020, 8:00 PM

#

I understood so

torn sphinx Jul 13, 2020, 8:00 PM

#

(1 if cleared)

clear reef Jul 13, 2020, 8:00 PM

#

mhm

torn sphinx Jul 13, 2020, 8:01 PM

#

so you can set defaults there too

clear reef Jul 13, 2020, 8:01 PM

#

so not null

torn sphinx Jul 13, 2020, 8:01 PM

#

i iddnt make it NN cuz i had a default but yea you can

clear reef Jul 13, 2020, 8:01 PM

#

aoaoa so no checked boxes?

torn sphinx Jul 13, 2020, 8:01 PM

#

https://aiosqlite.omnilib.dev/en/latest/?badge=latest
https://www.tutorialspoint.com/sqlite/sqlite_syntax.htm
btw

SQLite - Syntax - Tutorialspoint

SQLite - Syntax - SQLite is followed by unique set of rules and guidelines called Syntax. This chapter lists all the basic SQLite Syntax.

#

and no i didnt have any

clear reef Jul 13, 2020, 8:02 PM

#

and the reason didnt need any either

torn sphinx Jul 13, 2020, 8:02 PM

#

nope, cuz it's optional in my case

clear reef Jul 13, 2020, 8:02 PM

#

okio

#

can i "ok" now

📎 unknown.png

torn sphinx Jul 13, 2020, 8:03 PM

#

yup

#

then click "write changes"

#

then file > close db

#

file > open db read-only

#

so db browser doesnt lock the db

clear reef Jul 13, 2020, 8:03 PM

#

o

#

and then just open the file

torn sphinx Jul 13, 2020, 8:04 PM

#

the rest of the warnings is python now

clear reef Jul 13, 2020, 8:04 PM

#

i just created a whole database?

torn sphinx Jul 13, 2020, 8:05 PM

#

yup ;3

#

btw

#

the file is the database

clear reef Jul 13, 2020, 8:05 PM

#

ooo

#

how do i use

torn sphinx Jul 13, 2020, 8:05 PM

#

you just created a table

#

https://aiosqlite.omnilib.dev/en/latest/?badge=latest
https://www.tutorialspoint.com/sqlite/sqlite_syntax.htm
keep these handy

SQLite - Syntax - Tutorialspoint

SQLite - Syntax - SQLite is followed by unique set of rules and guidelines called Syntax. This chapter lists all the basic SQLite Syntax.

#

then install aiosqlite with pip

#

however you would in your ide/editor

#

file > open db read-only
and if you do this you can see the rows your bot adds to the table

clear reef Jul 13, 2020, 8:07 PM

#

📎 unknown.png

torn sphinx Jul 13, 2020, 8:08 PM

#

python -m pip...

clear reef Jul 13, 2020, 8:08 PM

#

o

#

i see lol

#

i havent done this in a while

torn sphinx Jul 13, 2020, 8:08 PM

#

its alright

clear reef Jul 13, 2020, 8:08 PM

#

now?

#

import right...?

torn sphinx Jul 13, 2020, 8:09 PM

#

yup

clear reef Jul 13, 2020, 8:09 PM

#

aiosqlite

torn sphinx Jul 13, 2020, 8:09 PM

#

yup

clear reef Jul 13, 2020, 8:09 PM

#

import aiosqlite ?

#

there

torn sphinx Jul 13, 2020, 8:10 PM

#

also

#

put

os.chdir(os.path.dirname(sys.argv[0]))

at the top of your bot.py but below imports

#

that changes the working directory (place where files are looked for) the the folder bot.py is in

clear reef Jul 13, 2020, 8:11 PM

#

ooh

#

but

#

i have seperate folders

📎 unknown.png

#

i only need to import in my moderation one

#

where the warn command is located

torn sphinx Jul 13, 2020, 8:12 PM

#

then yup just there

clear reef Jul 13, 2020, 8:12 PM

#

is it required to be in the main file

#

o

torn sphinx Jul 13, 2020, 8:12 PM

#

the import no

#

the os.chdir yes

#

and only in your main file

clear reef Jul 13, 2020, 8:13 PM

#

okio

#

now

torn sphinx Jul 13, 2020, 8:13 PM

#

welp so do you have a warn command yet

clear reef Jul 13, 2020, 8:13 PM

#

i do

torn sphinx Jul 13, 2020, 8:14 PM

#

alr

clear reef Jul 13, 2020, 8:14 PM

#

probably hella messy

📎 unknown.png

torn sphinx Jul 13, 2020, 8:14 PM

#

async with aiosqlite.connect(...) as db:
    await db.execute("INSERT INTO some_table ...")
    await db.commit()

    async with db.execute("SELECT * FROM some_table") as cursor:
        async for row in cursor:
            ...

#

this is the basic pattern

clear reef Jul 13, 2020, 8:14 PM

#

uh

#

where do i put

torn sphinx Jul 13, 2020, 8:14 PM

#

well you want it in your warn command

clear reef Jul 13, 2020, 8:15 PM

#

@bot.command()
@commands.cooldown(1, 5, commands.BucketType.user)
@commands.has_permissions(kick_members=True)
async def warn(ctx, member : discord.Member, *, reason=None):
    if member.top_role >= ctx.author.top_role:
        ctx.send(f"You cant warn someone higher ranked than you, {ctx.author.display_name}")
        return
    await ctx.message.delete(delay=None)
    if member == ctx.author:
        await ctx.send(f"You cant warn yourself, {ctx.author.display_name}...")
        return

    embed = discord.Embed(
        colour=discord.Colour.red(),
        title=f"**Reason:** `{reason}`"
    )

    embed.set_author(name=f"{member.display_name} has been warned by {ctx.author.display_name}", icon_url=f"{member.avatar_url_as()}")


    await ctx.send(embed=embed)

    embed = discord.Embed(
        colour=discord.Colour.red(),
        title=f"**Reason:** `{reason}`"
    )

    embed.set_author(name=f"You've been warned by {ctx.author.display_name}\nIn {ctx.guild.name}", icon_url=f"{member.avatar_url_as()}")
    

    await member.send(embed=embed)

torn sphinx Jul 13, 2020, 8:17 PM

#

and you're gonna want something like

async with aiosqlite.connect("asdsadas.db") as db:
  await db.execute("INSERT INTO warns(user, staff, reason, timestamp) values (?,?,?,?)", (warned_user_id, staff_id, reason, datetime.datetime.utcnow().timestamp()))

clear reef Jul 13, 2020, 8:17 PM

#

aaa

#

confusing

torn sphinx Jul 13, 2020, 8:17 PM

#

i do that here https://github.com/united-minecrafters/kaede/blob/master/cogs/administration/punishments.py#L34

GitHub

united-minecrafters/kaede

United Minecrafters Discord server private bot. Contribute to united-minecrafters/kaede development by creating an account on GitHub.

#

basically how INSERT works

#

INSERT INTO table_name(column1, column2) values (value1, value2)

clear reef Jul 13, 2020, 8:18 PM

#

stressed

torn sphinx Jul 13, 2020, 8:18 PM

#

hm ok so

#

make a new db in dbbrowser (if you middle click on the taskbar program, it''l open another copy of the program)

#

and do file > new in-memory db

clear reef Jul 13, 2020, 8:19 PM

#

mhm

torn sphinx Jul 13, 2020, 8:20 PM

#

📎 unknown.png

#

make a table like this

#

i named mine boop cuz :3

clear reef Jul 13, 2020, 8:21 PM

#

yes

#

i did

torn sphinx Jul 13, 2020, 8:21 PM

#

alr so

#

go over to the execute sql tab

#

📎 unknown.png

#

type thgat

#

ctrl + enter

#

*that

#

📎 unknown.png

#

you'll see the new row

#

ok welp

clear reef Jul 13, 2020, 8:22 PM

#

where

torn sphinx Jul 13, 2020, 8:22 PM

#

apparently you cant name a column default

clear reef Jul 13, 2020, 8:23 PM

#

oh here

torn sphinx Jul 13, 2020, 8:23 PM

#

db structure tab, right click table, modify

#

xd

#

📎 unknown.png

#

📎 unknown.png

#

you can see how the default was overridden

#

number incremented itself

clear reef Jul 13, 2020, 8:25 PM

#

📎 unknown.png

torn sphinx Jul 13, 2020, 8:25 PM

#

so small blobfearsweat

clear reef Jul 13, 2020, 8:25 PM

#

i know stressed

torn sphinx Jul 13, 2020, 8:25 PM

#

📎 unknown.png

#

put your mouse over a divier and you can resize it

clear reef Jul 13, 2020, 8:26 PM

#

there

#

📎 unknown.png

torn sphinx Jul 13, 2020, 8:26 PM

#

;3

#

ok so now add a few more records

clear reef Jul 13, 2020, 8:26 PM

#

o

torn sphinx Jul 13, 2020, 8:27 PM

#

make a few duplicate defs or texts

clear reef Jul 13, 2020, 8:27 PM

#

wah

torn sphinx Jul 13, 2020, 8:27 PM

#

📎 unknown.png

#

something like this

clear reef Jul 13, 2020, 8:28 PM

#

...?

torn sphinx Jul 13, 2020, 8:28 PM

#

use the insert into statement to add a few more records

#

so i can show you select, update, and delete :p

clear reef Jul 13, 2020, 8:29 PM

#

u

torn sphinx Jul 13, 2020, 8:29 PM

#

?

clear reef Jul 13, 2020, 8:29 PM

#

howsies

torn sphinx Jul 13, 2020, 8:29 PM

#

just like you did before

#

insert into boop(text, def) values ("adasdsa",3)

#

etc

clear reef Jul 13, 2020, 8:30 PM

#

📎 unknown.png

torn sphinx Jul 13, 2020, 8:31 PM

#

you didnt check default for def 👀

#

xd

clear reef Jul 13, 2020, 8:31 PM

#

f

#

wha

torn sphinx Jul 13, 2020, 8:31 PM

#

if you double click a cell, you can edit it in the right side

clear reef Jul 13, 2020, 8:31 PM

#

📎 unknown.png

#

op

torn sphinx Jul 13, 2020, 8:32 PM

#

you never set a defaukt, mine was 1

clear reef Jul 13, 2020, 8:32 PM

#

📎 unknown.png

torn sphinx Jul 13, 2020, 8:32 PM

#

yea that works

#

ok so

#

type

📎 unknown.png

clear reef Jul 13, 2020, 8:33 PM

#

📎 unknown.png

#

i made all def 4 now

#

yes?

torn sphinx Jul 13, 2020, 8:33 PM

#

nope

clear reef Jul 13, 2020, 8:33 PM

#

frick

torn sphinx Jul 13, 2020, 8:34 PM

#

you selected all the rows where def was 4

clear reef Jul 13, 2020, 8:34 PM

#

ohhh

#

so it only shows the 4's

torn sphinx Jul 13, 2020, 8:34 PM

#

this is how you'd get warns for a user, for instance

clear reef Jul 13, 2020, 8:34 PM

#

not the 5's

torn sphinx Jul 13, 2020, 8:34 PM

#

yup

clear reef Jul 13, 2020, 8:34 PM

#

this is how you'd get warns for a user, for instance
@torn sphinx it checks for their name and then checks thei info such as amount

#

yes?

torn sphinx Jul 13, 2020, 8:34 PM

#

so do you understand how insert and select work?

clear reef Jul 13, 2020, 8:34 PM

#

i guess

torn sphinx Jul 13, 2020, 8:35 PM

#

and sorta. you'd check for their id

#

and you'd get a bunch of rows

clear reef Jul 13, 2020, 8:35 PM

#

yeah true

#

thats what i refer to

#

267689225112125441 my id

torn sphinx Jul 13, 2020, 8:35 PM

#

you'd count the rows in the code

clear reef Jul 13, 2020, 8:35 PM

#

so if i was like

#

p.warnings @clear reef

#

it would use my id

#

267689225112125441

#

to check

#

and then fetch info

torn sphinx Jul 13, 2020, 8:35 PM

#

yup

clear reef Jul 13, 2020, 8:35 PM

#

yayhyper

#

alright

#

i see

torn sphinx Jul 13, 2020, 8:36 PM

#

so 2 more important statements

clear reef Jul 13, 2020, 8:36 PM

#

mhm

torn sphinx Jul 13, 2020, 8:36 PM

#

first

#

delete

clear reef Jul 13, 2020, 8:37 PM

#

im guessing you cna use that to clear warns

torn sphinx Jul 13, 2020, 8:37 PM

#

📎 unknown.png

#

well

#

sorta

#

delete wipes it from existence

#

run a delete query

clear reef Jul 13, 2020, 8:38 PM

#

yeah it gone

📎 unknown.png

torn sphinx Jul 13, 2020, 8:38 PM

#

ok so

#

last one

#

update

#

this is how you probably want to clear warns

#

you set cleared to 1

clear reef Jul 13, 2020, 8:38 PM

#

ohh

#

you dont erase it

#

you set it

torn sphinx Jul 13, 2020, 8:39 PM

#

📎 unknown.png

#

📎 unknown.png

clear reef Jul 13, 2020, 8:40 PM

#

📎 unknown.png

#

i had no "1" at first so i set it at 5

torn sphinx Jul 13, 2020, 8:40 PM

#

yup

#

ok so

clear reef Jul 13, 2020, 8:40 PM

#

im getting the hang of it

torn sphinx Jul 13, 2020, 8:41 PM

#

update
delete
insert
select

clear reef Jul 13, 2020, 8:41 PM

#

mhmm

torn sphinx Jul 13, 2020, 8:41 PM

#

those are the most frequent ones you'll use

clear reef Jul 13, 2020, 8:41 PM

#

i see

torn sphinx Jul 13, 2020, 8:41 PM

#

add a few more records , with different def values

#

run this

#

📎 unknown.png

#

📎 unknown.png

#

and it's DESC counterpart

clear reef Jul 13, 2020, 8:44 PM

#

o

#

📎 unknown.png

torn sphinx Jul 13, 2020, 8:44 PM

#

yup ;3

clear reef Jul 13, 2020, 8:44 PM

#

this ranked the numbers?

torn sphinx Jul 13, 2020, 8:44 PM

#

yup

clear reef Jul 13, 2020, 8:44 PM

#

from top to bottom

#

hmm

torn sphinx Jul 13, 2020, 8:45 PM

#

taking out desc makes it go the other way

#

also

#

run

clear reef Jul 13, 2020, 8:45 PM

#

ohhh

#

now opposite

#

📎 unknown.png

torn sphinx Jul 13, 2020, 8:46 PM

#

📎 unknown.png

#

notice that the resulting table is only number and text

#

also, if you want to, say, get the 3 highest defs

#

📎 unknown.png

clear reef Jul 13, 2020, 8:47 PM

#

i cant do text

torn sphinx Jul 13, 2020, 8:47 PM

#

hm?

clear reef Jul 13, 2020, 8:47 PM

#

📎 unknown.png

#

it appears blue at least

torn sphinx Jul 13, 2020, 8:48 PM

#

odd mine doesnt

clear reef Jul 13, 2020, 8:48 PM

#

and it does nothing

torn sphinx Jul 13, 2020, 8:48 PM

#

just do number,def then

#

gets the same point across

clear reef Jul 13, 2020, 8:48 PM

#

doesnt do anything

#

still

torn sphinx Jul 13, 2020, 8:49 PM

#

uh

#

do you have any def=1

clear reef Jul 13, 2020, 8:49 PM

#

oh right lol

torn sphinx Jul 13, 2020, 8:49 PM

#

:p

clear reef Jul 13, 2020, 8:50 PM

#

📎 unknown.png

torn sphinx Jul 13, 2020, 8:50 PM

#

now try the limit one if ya want

#

using order by and limit let you paginate data

clear reef Jul 13, 2020, 8:51 PM

#

huh

torn sphinx Jul 13, 2020, 8:52 PM

#

ok so

#

📎 unknown.png

#

you have your first 3

#

(your first page)

#

note that number is a primary key

clear reef Jul 13, 2020, 8:53 PM

#

📎 unknown.png

torn sphinx Jul 13, 2020, 8:53 PM

#

and has to be unique

#

so if i

#

5 was the last number

#

so i can just

#

📎 unknown.png

#

theres the next page

#

8 is the last number

#

📎 unknown.png

#

last page

clear reef Jul 13, 2020, 8:54 PM

#

i see

torn sphinx Jul 13, 2020, 8:54 PM

#

anywho

#

so you think ya get all that enough to put it into practice?

#

and you're gonna want something like

async with aiosqlite.connect("asdsadas.db") as db:
  await db.execute("INSERT INTO warns(user, staff, reason, timestamp) values (?,?,?,?)", (warned_user_id, staff_id, reason, datetime.datetime.utcnow().timestamp()))

@torn sphinx something like this

clear reef Jul 13, 2020, 8:55 PM

#

hm

torn sphinx Jul 13, 2020, 8:55 PM

#

the ? tells sqlite to use one of the passed values, and gets escaped to prevent sql injection

clear reef Jul 13, 2020, 8:55 PM

#

mhm

torn sphinx Jul 13, 2020, 8:56 PM

#

so that'd be the basic add-a-warn code

clear reef Jul 13, 2020, 8:56 PM

#

where in my warn code would i be putting this

torn sphinx Jul 13, 2020, 8:56 PM

#

wherever you feel is best

clear reef Jul 13, 2020, 8:56 PM

#

im talking about indention as well

torn sphinx Jul 13, 2020, 8:57 PM

#

change warned_user_id, staff_id, reason, etc to whateveryou have to

#

indent the async the same level as the rest of the code inside the functiopn

#

📎 unknown.png

clear reef Jul 13, 2020, 8:57 PM

#

📎 unknown.png

torn sphinx Jul 13, 2020, 8:58 PM

#

indent await 1 tab after async

clear reef Jul 13, 2020, 8:58 PM

#

yes

#

saw that now

#

what will "(warned_user_id, staff_id, reason)"

#

be in my case

torn sphinx Jul 13, 2020, 8:58 PM

#

well staff_id is probably ctx.author.id

#

reason is reason

#

and it looks like warned_user_id is member.id

clear reef Jul 13, 2020, 8:59 PM

#

okie

#

this

📎 unknown.png

torn sphinx Jul 13, 2020, 9:00 PM

#

import datetime in your code

#

thats just the current UTC time

clear reef Jul 13, 2020, 9:00 PM

#

oki

#

so it now just

#

records warns

torn sphinx Jul 13, 2020, 9:01 PM

#

except one thing

#

doing execute doesnt change the db yet

clear reef Jul 13, 2020, 9:01 PM

#

oh

torn sphinx Jul 13, 2020, 9:01 PM

#

you need an await db.commit() to record changes

#

inside the async

#

after the execute

clear reef Jul 13, 2020, 9:02 PM

#

yes?

📎 unknown.png

torn sphinx Jul 13, 2020, 9:02 PM

#

that should work yup

#

you still have the db open read-only in db browser?

clear reef Jul 13, 2020, 9:02 PM

#

ill open

torn sphinx Jul 13, 2020, 9:02 PM

#

make sure it's read-only

#

or else the program locks the database and you cant edit it in your bot

clear reef Jul 13, 2020, 9:03 PM

#

so its read only rn

#

and now i just execute the command

#

?

#

and check the "browse data"

torn sphinx Jul 13, 2020, 9:03 PM

#

yup

#

you might have to click the refresh button

#

📎 unknown.png

#

you'll get something like this

clear reef Jul 13, 2020, 9:04 PM

#

📎 unknown.png

torn sphinx Jul 13, 2020, 9:04 PM

#

import sys

#

and os

#

in main file

#

:p

clear reef Jul 13, 2020, 9:05 PM

#

📎 unknown.png

torn sphinx Jul 13, 2020, 9:05 PM

#

hm

clear reef Jul 13, 2020, 9:05 PM

#

uhm

📎 unknown.png

torn sphinx Jul 13, 2020, 9:06 PM

#

excuse me what

clear reef Jul 13, 2020, 9:06 PM

#

why vsc

torn sphinx Jul 13, 2020, 9:06 PM

#

close and reopen the db browser

#

make sure warns actually exists?

clear reef Jul 13, 2020, 9:08 PM

#

hm?

#

still the same

torn sphinx Jul 13, 2020, 9:08 PM

#

and you're sure you're acessing the right db?

clear reef Jul 13, 2020, 9:09 PM

#

is the name supposed to be "warns"

#

warns.db

torn sphinx Jul 13, 2020, 9:09 PM

#

nope

#

rememeber the file is the database

clear reef Jul 13, 2020, 9:09 PM

#

what else is there to visit

📎 unknown.png

torn sphinx Jul 13, 2020, 9:09 PM

#

are you accessing "Data.db" in the code?

clear reef Jul 13, 2020, 9:09 PM

#

uhhh

torn sphinx Jul 13, 2020, 9:09 PM

#

cuz remember, warns is a table in that db

clear reef Jul 13, 2020, 9:09 PM

#

where

torn sphinx Jul 13, 2020, 9:10 PM

#

in your connect

clear reef Jul 13, 2020, 9:10 PM

#

ohhhhhh

#

LMao

torn sphinx Jul 13, 2020, 9:10 PM

#

oop

clear reef Jul 13, 2020, 9:10 PM

#

my bad my bad

#

moment of truth PepePopcorn

📎 unknown.png

torn sphinx Jul 13, 2020, 9:11 PM

#

poor saber ):

clear reef Jul 13, 2020, 9:11 PM

#

i mightve forgotten user id

📎 unknown.png

#

poor saber ):
@torn sphinx its just my useless alt

torn sphinx Jul 13, 2020, 9:11 PM

#

you're looking at the wrong table :p

clear reef Jul 13, 2020, 9:11 PM

#

oop-

torn sphinx Jul 13, 2020, 9:11 PM

#

📎 unknown.png

clear reef Jul 13, 2020, 9:11 PM

#

📎 unknown.png

#

epicc

torn sphinx Jul 13, 2020, 9:12 PM

#

:D

clear reef Jul 13, 2020, 9:12 PM

#

GAMER MOMENT

torn sphinx Jul 13, 2020, 9:12 PM

#

except

clear reef Jul 13, 2020, 9:12 PM

#

.

torn sphinx Jul 13, 2020, 9:12 PM

#

shut down your bot and make cleared have a default :p

#

you'll have to reopen the db in db browser

clear reef Jul 13, 2020, 9:12 PM

#

how do i make it have a default again

torn sphinx Jul 13, 2020, 9:13 PM

#

first you have to give every NULL a value

#

double click on the NULL