pure cypress Jan 30, 2020, 2:58 AM

#

i.e. each unique item getting its own row

toxic rune Jan 30, 2020, 3:00 AM

#

Yeah that's why I thought of going the json format but I'm unsure what's the best approach. Since this is a small dataset and I'm working with larger stuff / more joins.

pure cypress Jan 30, 2020, 3:02 AM

#

It's pretty repetitive code so perhaps you could create a generic function for it

#

that would simplify the query

#

This may be of interest too

#

https://dba.stackexchange.com/questions/247241/convert-a-row-to-a-nested-json-document-containing-all-relations

Database Administrators Stack Exchange

Convert a row to a nested JSON document containing all relations

In PostgreSQL 10.10, I have created a trigger function in PL/pgSQL that converts the NEW row to a JSON object using to_jsonb(NEW). But now I need to include the records on the other side of the for...

#

quite cool that it is even possible

toxic rune Jan 30, 2020, 3:11 AM

#

Well so far with the larger data set not having performance issues or anything even with nested joins and multiple json functions, but I was curious if there was another clean(ier) approach to this other than separate functions, of course.

pure cypress Jan 30, 2020, 3:13 AM

#

Well I think the link I sent is a pretty nice solution

#

Depends where you draw the line between clean and complex

#

It's certainly quite advanced

toxic rune Jan 30, 2020, 3:25 AM

#

Yeah I'll look at it better on PC and do some tests hopefully it works so I can proceed

void otter Jan 30, 2020, 6:14 AM

#

@trail rune
Postgres is maybe what you want to take a look at
Open source and very fast, howoever not very scalable

#

Nosql dbms'es like mongodb or couchdb are highly scalable but orm's can get wacky

trail rune Jan 30, 2020, 6:15 AM

#

not sure about scaleabilty. I am trainee at comany who only seem to just be getting into cloud.
I see Docker as removing patching process altogether but its gonan take years to get there I think

void otter Jan 30, 2020, 6:15 AM

#

Ah okay

#

Then postgres is what you are looking for

toxic rune Jan 30, 2020, 8:27 AM

#

@pure cypress Okay so I did some tests with the method I was going to try when I made the SO post. These are my results (Warning, this looks really nasty...)

Query/Function: https://mystb.in/xicuwomeju.sql
Result: https://mystb.in/vereqisico.json

So yes, this is the kind of result that I want. The issue is I'm not conformed with the result is that for tables that have < 3000 rows, this taking ~15-35ms seems a lot? And well, the function could look nicer. Maybe I'm overthinking it.
I personally feel it could be optimized a bit, but not sure where. I went with that table design for these particular tables (the reason why you see duplicate names and descriptions because some change description/name mid-way through their levels.) so I thought this would be the less annoying way for now.

EDIT: Thought I'd mention this data is to display on Discord embeds.

reef hawk Jan 30, 2020, 12:44 PM

#

I was working on figuring out inheritance, but I get the error when I try to inherit a table from another:
ERROR: child table is missing column user_id. From what I found on google, it seems to be a parent key issue? user_id is my primary key for my parent table (the table I want to inherit), do I need to make a column for it in the child class? If so, why?

trail rune Jan 30, 2020, 1:19 PM

#

your child table needs a reference to its parent table. A link so to speak

belongs to has the number of the parent tables Primary key

#

so the merc belongs to dave jones.

#

You can join the tables together using the PK and Fk in this case belongs to is the Forigen key to the tbl_user table

#

so

   JOIN tbl_user as u on u.pk = c.Belongs_to
where c.pk = 2

#

Does this help? I worry I made it difficult

spare umbra Jan 30, 2020, 2:13 PM

#

hey guys im working on a user todo system, in which every userid can have multiple todos stored in todos

Am i doing it right? I made a undefining 1-n relationship between users and todos. So todos has the userid as foreign key but its not primary in todos

Is it even needed to make the relationship 1-n? Like am i not even saying that by making userid_fk a non unique/primary key?

📎 unknown.png

pseudo silo Jan 30, 2020, 3:14 PM

#

maybe use an associative table containing foreign keys for all 3 of them?

#

@spare umbra many-to-many relationship i believe

spare umbra Jan 30, 2020, 3:20 PM

#

Why would that be better?

native vapor Jan 30, 2020, 3:43 PM

#

Why would your approach would not work?

#

if you want the reminder of a user you can fetch them, if you want to find user that have reminder, idk tomorow you can fetch them as well?

spare umbra Jan 30, 2020, 4:05 PM

#

Yea im just unsure about the 1-n relationship from users and todos. Isnt it 1-n automatically when setting userid_fk in todos as non primary key? so one userid can exist in multiple rows @native vapor

#

Like what does 1-n do differently

native vapor Jan 30, 2020, 4:13 PM

#

I'm not sure i understand the wording, but alternatively, 1 todo can have multiple users or not in your current setting?

spare umbra Jan 30, 2020, 4:13 PM

#

no 1 user can have multiple todos

#

At least thats what im going for

#

@trail rune You seem to know well about sql. Could u maybe explain to me the difference between a 1-n relationship and the foreign key not being a primary key? sry for the ping btw

trail rune Jan 30, 2020, 4:40 PM

#

Most likely means a 1 to many relationship.

Like this A car can have one or more owners

#

A primary key is just a unique identifyer. Like you passport number.

In the passport table your passport number is the primary key.

You may go on holiday and when you do you passport will get scanned.

Do border control will have a table that contains your passport number also.

I. Their table your passt number becomes a foreign key.

This is all figurtively speaking.

So in short a foreign key is a reference to a primary key in another table which acts a link between the two tables.

spare umbra Jan 30, 2020, 4:51 PM

#

Okay thx for the explanation :)

trail rune Jan 30, 2020, 4:57 PM

#

No worries

wild locust Jan 30, 2020, 5:37 PM

#

Does anyone know of any guides I can follow for connecting my MariaDB database to my Discord.py bot?

spare umbra Jan 30, 2020, 7:12 PM

#

python mysql connector

spare umbra Jan 30, 2020, 8:22 PM

#

Best way to store a date (datetime obj) in sql db for a reminder command?

cinder sierra Jan 30, 2020, 8:39 PM

#

for a discord bot? @spare umbra

spare umbra Jan 30, 2020, 8:40 PM

#

Yes

cinder sierra Jan 30, 2020, 8:40 PM

#

i'd probably store the message ID with the date for the reminder

#

if you just want a once a day check, check the db every day for the current date and remind the author of the message id

spare umbra Jan 30, 2020, 8:41 PM

#

No its up to minute wise

#

Checks every minute

cinder sierra Jan 30, 2020, 8:42 PM

#

then i mean you can still do the same idea but you're going to be querying the database a lot

wraith heath Jan 30, 2020, 9:00 PM

#

need a tad of help for sql

#

my code can currently read off the database correctly

#

and if i write to the database, the code can read what i just "wrote"

#

but whatever i write doesn't actually go to the database it just disappears

cinder sierra Jan 30, 2020, 9:18 PM

#

because you need to commit

reef hawk Jan 30, 2020, 9:25 PM

#

@trail rune thanks for the explanation but idk I made a new table with the exact same values and it worked for some reason

trail rune Jan 30, 2020, 9:29 PM

#

Cool nice one

reef hawk Jan 30, 2020, 10:50 PM

#

Sorry if this is a relatively easy problem to solve; I read the docs on inheritance and I'm still confused on how I would do this.

Let's say I have the parent table being car_model, which has the name of the car and some of it's features as the columns (e.g. car_name, car_description, car_year, etc). Basically a list of cars.

I have the child table being car_user, which has the column user_id.

Basically, I want to link a car to the car_user, so when I call
SELECT car_name FROM car_user WHERE user_id = "name", I could retrieve the car_name. I would need a linking component that links car_user to the car.

How would I do this?

I was thinking of doing something like having car_name column in car_user, so when I create a new data row in car_user, it could link the 2 together.

What's the best way to solve this problem? I think this is what ArchaicLord was trying to explain, I understand but don't know how to implement it

reef hawk Jan 30, 2020, 11:13 PM

#

nevermind, it's not inheritance, it's many-to-many relationship

toxic rune Jan 31, 2020, 1:01 AM

#

@pure cypress Sorry for the ping, but regarding the performance stuff on the PSQL database, would it be wise to use materialized views? I did some tries to improve performance and generating a materialized view of the table already converted into jsonb (the data doesn't change only every few weeks) and it was able to drop the query execution times under 8ms. I'd assume this is a proper approach into squeezing some performance.

pure cypress Jan 31, 2020, 1:23 AM

#

I'm not the best person to ask since I don't have experience with db optimisation.

#

For what it's worth, it sounds like a good idea to me if you run the query frequently and the data doesn't change often

toxic rune Jan 31, 2020, 2:34 AM

#

I see

#

Trying to reduce function planning time right now

torn sphinx Jan 31, 2020, 6:27 AM

#

I am not super familiar with real databases. I am currently learning Django with intentions to use it for an app I hope to take to production... so I would like to learn a db that I can use to such an end. Assuming that I may have someone who can help me host the db on their server while I am learning, what databases do you recommend for this type of use? I understand that sqlite is not a good option for some reason if you are making a serious app.

This app would involve users who use their accounts to build campaigns and play rpg command interface games of flash fiction using their own writing and assets with premium accounts. it would require the ecommerce of these account attributes and in short

it would be a full production ideally.

What database should i learn to use, as my first real database used in any program ive made myself ever?

#

PostgreSQL, MariaDB, MySQL, Oracle and SQLite are officially supported

#

and I believe one of those is my most likely choice

#

What are attributes of these?

void otter Jan 31, 2020, 6:30 AM

#

Posgres has gained huge traction and is really fast

#

MariaDB is a derrivate of mysql

#

Mysql is old school dev's choice, really reliable

#

And sqlite is 99.99% of time used for testing queries

torn sphinx Jan 31, 2020, 6:32 AM

#

I understood only that about the latter. I feel like its a waste of time to learn using that if I plan to really use these dbs.

Is it true that most people use the old dbs only because the data they have that is old relies on them?

void otter Jan 31, 2020, 6:32 AM

#

Most of the time

#

I mean, it is human's desire to evolve and improve

torn sphinx Jan 31, 2020, 6:33 AM

#

so Postgres is the more modern solution for new data? MariaDB for someone familiar with MySQL already?

#

Here is a question

void otter Jan 31, 2020, 6:33 AM

#

But learning mysql is never gonna hurt you, they are eveywhere

torn sphinx Jan 31, 2020, 6:33 AM

#

In terms of sysadmin of ther server the db operates on...

#

Which is the least inconveniencing?

void otter Jan 31, 2020, 6:34 AM

#

Postgres can be used for absolutely anything

#

I would say mysql and postgres are the one worth learning

torn sphinx Jan 31, 2020, 6:35 AM

#

Thank you. I'll ask my server guy what he would prefer with that in mind

void otter Jan 31, 2020, 6:35 AM

#

Cheers

torn sphinx Jan 31, 2020, 6:35 AM

#

ty

native vapor Jan 31, 2020, 9:27 AM

#

And sqlite is 99.99% of time used for testing queries

#

can you develop on that?

#

Is it improper for real world web apps?

#

@void otter

void otter Jan 31, 2020, 9:50 AM

#

Yes, it is used to develop on

#

But it isn't really suitable for production apps

nova hawk Jan 31, 2020, 9:56 AM

#

Depends on size ofc.

#

SQLite is something that you can bundle with your apps for example

#

For example both chrome and firefox use SQLite to store your browser history.

torn sphinx Jan 31, 2020, 10:49 AM

#

So, my friend who said he could help me with hosting the db told me that I should just spin up a vm for now and set it up as he will not be able to host it unless I work up an image and instructions on how to deploy it with docker. These are things I am not going to distract myself with for now, but as long as its development stage, I know how to do a vm.

Ive decided on nginx, because this is what he prefers and he might help me host it eventually, postgres, and will probably do it on some linux distro.

Any recommendations on this stack for this db?

#

Or is it fairly straight forward with few complications based in server stack?

void otter Jan 31, 2020, 11:41 AM

#

What are you developing?

torn sphinx Jan 31, 2020, 11:55 AM

#

https://git.hackers.town/just_another_one/shell_hacker_game

HackersTown - Gitea

shell_hacker_game

(WIP) this-gen "terminal" (web app) text based, research, command line and rpg text adventure with educational and socially conscious take on cyberpunk and the modern age; characterized by attempt to demonstrate reality and not hollywood hacking.

#

Ignore that card:

(WIP) Terminalmancer is a FOSS (Free and Open Source) engine for CyberPunk role-playing and choice based flash fiction campaigns wherein the primary interface with the fiction and the game is in the terminal emulator.

#

https://git.hackers.town/just_another_one/shell_hacker_game/src/branch/master/docs/campaign-creation/1-campaignclass.settingaccess.interfaceemphasis.txt

HackersTown - Gitea

shell_hacker_game

(WIP) this-gen "terminal" (web app) text based, research, command line and rpg text adventure with educational and socially conscious take on cyberpunk and the modern age; characterized by attempt to demonstrate reality and not hollywood hacking.

rain wagon Jan 31, 2020, 3:27 PM

#

I'd recommend CentOS as OS

#

It is very well suited for servers and has a tight security off the bat

#

SELinux can be a pain though

#

But if you set it to permissive, it gives warnings about what you need

#

@torn sphinx

torn sphinx Jan 31, 2020, 3:29 PM

#

I have started with perhaps the most work intensive option. Should I go back lol. I mean I can do it, but its taking time away from learning Django.

📎 unknown.png

#

That being said I just learned that Django requires Apache to run in production.

#

Actually thats a lie

#

Django supports many other deployment options. One is uWSGI; it works very well with nginx.

#

Wonder why thats a footnote

river plume Jan 31, 2020, 3:47 PM

#

Why not nginx ???

rain wagon Jan 31, 2020, 3:50 PM

#

It does not matter if you use nginX or Apache

#

nginX can handle more than 10.000 connections and unless you go near that, it does not really matter

#

and even then, you can use apache on the app server and nginX as a reverse proxy and load balancer

#

which reminds me, have the nginX CEO's been released from jail yet? 😄

#

btw, I am hosting a flask app on apache on my rpi for development. Runs smooth

tender dagger Jan 31, 2020, 5:47 PM

#

In sqlite is there a way to skip results?

#

Like SKIP 10 LIMIT 10

#

To return #11-#21

cinder sierra Jan 31, 2020, 5:53 PM

#

@torn sphinx Flask is a decent alternative to Django if you don't need all of the forced parts of Django

tender dagger Jan 31, 2020, 5:59 PM

#

Found it

#

LIMIT 10, 10

torn sphinx Feb 1, 2020, 12:42 AM

#

Well, what I need is... something that can build a very unique application. I dont need a microblog. I need something that doesnt exist in any similar form as i yet know of. @cinder sierra Django seems like its used by people who are taking on such big things more isn't it? Anyway, I will get back into here when I actually have the database set up. I'm setting up uwsgi and nginx and a lot various stuff before i get to it but im sure ill have db questions in time.

cinder sierra Feb 1, 2020, 12:53 AM

#

well even if a project is big Django isn't always the answer

karmic hawk Feb 1, 2020, 1:31 AM

#

Could anyone take a look at the layout for a database I am designing and let me know if it's flawed?

toxic rune Feb 1, 2020, 4:43 PM

#

I have the following JOIN (which works) https://mystb.in/mesekaqini.sql, however the LEFT JOIN is inefficient in this case since I assume it's converting all the rows to jsonb and aggregating them before filtering the rows which bumps the execution time up to 100ms which seems absurd for something like this.
Is there any way I can lower that execution time? I assume a filter inside the JOIN to only convert and aggregate the skill_ids that I need would work, but I'm not sure how to approach that. I also thought of a materialized view but I'm not sure if there's a better/easier approach.

trail rune Feb 1, 2020, 6:53 PM

#

It's will be the sub selects that are slowing you down

toxic rune Feb 1, 2020, 6:55 PM

#

Yeah it's the left join that calls to jsonb function, if I can filter before it would improve it a a lot but I'm not sure how I can filter there. Or any other approach

trail rune Feb 1, 2020, 6:56 PM

#

Left join on I'd = I'd where = blah and y = bar

#

I don't know what db you using it's not something I have seen before.
I know MySQl does a thing that if you search more than 20% of the total rows it does a full table lookup regardless if you try limiting it

toxic rune Feb 1, 2020, 7:01 PM

#

PostgreSQL

trail rune Feb 1, 2020, 7:01 PM

#

Not used that yet but hopefully soon as I am looking at Django.

#

What I know though is when you use a join the ON clause can be used to set Conditions to reduce the data set if you so require

#

To me it looks like your left joining a sub selection when you could just left join.

Select join1.column, join2.columa
Left join1 on tblx.id = tblec where tblx.date between x and y
From table Foo
Where table Foo.name like '%asd%
Order by foo.date

toxic rune Feb 1, 2020, 7:08 PM

#

Hmm I haven't tried that I will try later

#

Technically the only filter I need would be the skill_id from one table be the same as the one being joined

trail rune Feb 1, 2020, 7:10 PM

#

When you join your joint both tables together to make one big table

#

Union join can be quite useful also

#

It's a revelation for sure. I remember doing sub selects then being shown most sub selects can be made more efficient using join conditions instead

#

Let me know how you get on. Hopefully it helps. I don't profess to be an expert.

toxic rune Feb 1, 2020, 7:16 PM

#

Me neither lol. I've been trying a lot of different stuff. On other functions I was able to pass the psql function variable inside the select and filter using where before the join which helped me reduce but in this case I can't do that

#

YukariCry

rustic quarry Feb 1, 2020, 8:34 PM

#

ach socket address (protocol/network address/port) is normally permitted```

#

why getting this error?

torn sphinx Feb 1, 2020, 8:40 PM

#

How to create a database?

rustic quarry Feb 1, 2020, 8:40 PM

#

lmfao

rain wagon Feb 1, 2020, 8:40 PM

#

There is already something running on that address

rustic quarry Feb 1, 2020, 8:40 PM

#

nvm

#

like what?

rain wagon Feb 1, 2020, 8:41 PM

#

Another service

#

Only one program can bind a network port for listening

rustic quarry Feb 1, 2020, 8:41 PM

#

and how to find it?

rain wagon Feb 1, 2020, 8:41 PM

#

try netstat

rustic quarry Feb 1, 2020, 8:41 PM

#

not giving out

#

tried 4-5 times

rain wagon Feb 1, 2020, 8:42 PM

#

What are you trying to serve there?

torn sphinx Feb 1, 2020, 8:42 PM

#

So...

rain wagon Feb 1, 2020, 8:43 PM

#

I need more information about what you are doing

#

Also, the address 0.0.0.0 in this context means on all ip addresses, was that the intent?

rustic quarry Feb 1, 2020, 8:44 PM

#

im trying to make an economy discord bot that fetches data from the server like currency , money etc

#

im trying to create a global market so yes ig

rain wagon Feb 1, 2020, 8:46 PM

#

check your code, that is all I can say at this point

#

or restart your computer

#

maybe an instance is running in the background, hogging the port

#

@torn sphinx What kind of database? Single file or server?

#

@rustic quarry have you tried netstat -anob | find "8080"?

rustic quarry Feb 1, 2020, 8:55 PM

#

yes

#

even killed it

#

still the same error

#

any suggestions?

pseudo silo Feb 1, 2020, 9:03 PM

#

@torn sphinx elaborate on what you mean with create a database

rain wagon Feb 1, 2020, 9:06 PM

#

@rustic quarry Try running it with elevation. 8080 shouldn't need elevation, since it is not within the well known ports, but who knows

torn sphinx Feb 1, 2020, 9:24 PM

#

I don’t know anything about it, i want to some tutorials

rustic grove Feb 1, 2020, 9:31 PM

#

When should I use many-to-one and one-to-many in sqlalchemy?

pseudo silo Feb 1, 2020, 9:54 PM

#

@torn sphinx search postgrestutorial

trail rune Feb 1, 2020, 10:06 PM

#

@rustic grove
one to may is when one record can be asscioated with many records in 1 other table.

A student can attend 1 or more classes = 1 to many

Many to Many = A record in one table can be associated with multiple records in another table .

e.g student_enroments table might infomation about students and classes

Student
id, name,

classes
id, class_name, student_id

student_enrolment
id student_id, class_id.

student enroment means that the school is made up of many classess and many students.

rustic grove Feb 1, 2020, 10:06 PM

#

ah okay

#

thanks

#

thats what I thought

trail rune Feb 1, 2020, 10:08 PM

#

No problem

rustic grove Feb 1, 2020, 10:48 PM

#

@trail rune In one-many sqlalchemy how do I pick which object?

#

Like I don't want to select all of my trades

trail rune Feb 1, 2020, 11:07 PM

#

i dont know sqlalchemy

but would be something like

Select * from classes where fk_student_id = 1
Where 1 = Joe blogs

#

if you wanted to see the name of student you would have to join the student table to classes table

rustic grove Feb 1, 2020, 11:13 PM

#

kk

toxic rune Feb 1, 2020, 11:22 PM

#

@trail rune Doesn't seem like I can use a WHERE in that place. Guess I'll just go with a materialized view. warShrug

trail rune Feb 1, 2020, 11:36 PM

#

yeah your right,

join_condition is an expression resulting in a value of type boolean (similar to a WHERE clause) that specifies which rows in a join are considered to match.```

so you can use AND , OR, NOT =   I guess

#

from postgres documentation. As I said before I haven't done postgres before

#

i think its same for MySQL just they way i am explaining it is not translating to the exact syntax you need.

toxic rune Feb 1, 2020, 11:54 PM

#

The issue is that I'm already doing the ON join_condition, it still takes above 100ms. I need to filter before it generates all the jsonb_agg, which it seems it's only possible to do on the SELECT.

#

I tried with an AND, but it still seems to be generating the agg of all rows before performing the join? If I add a WHERE inside the actual JOIN the time is way, way lower.

#

If I hardcode any of the values I need inside the LEFT JOIN I end up with ~3ms.

trail rune Feb 2, 2020, 12:07 AM

#

Some queries are as good as they can be.

The only things that spring to mind now would be to use a
Stored function or stored procedure.
Stored procedures are stored in memory and used to increase performance on queries that are called all the time.

#

without seeing the full query and what not and even then i am not sure I know enough to help any further

toxic rune Feb 2, 2020, 12:09 AM

#

Well, there's not much more to it. I guess I'll go the MV way, should help I hope

#

The only other approach I can think of is make this on a separate function, that way I think I can filter it.

trail rune Feb 2, 2020, 12:18 AM

#

Hopefully someone more knowledgeable in postgres can help.
I don't know your schema and what you are trying to do.
I read the query a few times. it doesn't make sense to me.

Feels bad I tried to do what I could

cursive sphinx Feb 2, 2020, 3:57 AM

#

I've read that postgresql, mysql can support multiple users at same time. So if i updated different rows in same table at same point of time it wouldn't lock the table or rows until first commit ended?

toxic rune Feb 2, 2020, 5:02 AM

#

@trail rune I actually tracked down a bit more on the query plan and the join/group condition is causing the issue.

QUERY PLAN |         ->  Hash Right Join  (cost=66.83..72.50 rows=3 width=40) (actual time=54.596..58.201 rows=3 loops=1)
-[ RECORD 12 ]--------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |               Hash Cond: (skill.skill_id = op_skill.skill_id)

#

actual time=54.596..58.201

#

(This query ran for 60ms)

#

QUERY PLAN |         ->  Hash Right Join  (cost=66.83..72.50 rows=3 width=40) (actual time=54.596..58.201 rows=3 loops=1)
-[ RECORD 12 ]---------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |               Hash Cond: (skill.skill_id = op_skill.skill_id)
-[ RECORD 13 ]---------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |               ->  HashAggregate  (cost=61.91..64.72 rows=225 width=36) (actual time=53.873..58.051 rows=225 loops=1)
-[ RECORD 14 ]---------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |                     Group Key: skill.skill_id

#

I might know of an idea to fix this. I think.

deft pasture Feb 2, 2020, 5:13 AM

#

panda_ohno

#

I cant seem to get down how to check if something already stored. working with sqlite3 and want to store info only one time. I thought i had it last time but what i have currently keeps adding the same things

#

conn = sqlite3.connect('database.db')
cursor = conn.cursor()
cursor.execute("""CREATE TABLE IF NOT EXISTS products(
        images TEXT, 
        prices TEXT""")
try:
        cursor.execute("""INSERT INTO products(images, prices)  VALUES(?, ?)""", (item_img, item_price))
        print(f'Stored item {item_name} image/price to database.')
        alert = "NEW"
except:
    print("Change in image or price.")
    alert = "CHANGE"```

toxic rune Feb 2, 2020, 7:38 AM

#

Without knowing much more about details of images/prices, you have no unique constraint on either of them or at least a way to assign a unique identifier to a pair of item_img/item_price.

rustic quarry Feb 2, 2020, 7:49 AM

#

ConnectionRefusedError: [WinError 1225] The remote computer refused the network connection

#

how is this possible?

rain wagon Feb 2, 2020, 8:40 AM

#

@rustic quarry Firewall

#

NAT issues

#

there are quite a few answers there

rustic quarry Feb 2, 2020, 9:01 AM

#

how to disable the firewall?

#

and what are nat issues?

rustic quarry Feb 2, 2020, 9:20 AM

#

i also get this error ````[WinError 1214] The format of the specified network name is invalid```

long wagon Feb 2, 2020, 6:11 PM

#

@rain wagon can u help me with something

#

i want to iterate through a db to find as many results of the same instance as i can

#

its mongo

long wagon Feb 2, 2020, 6:35 PM

#

    for grp in gb:
        CheckGroup = await grpCollection.find_one({'ServerId': ctx.guild.id})
        print(CheckGroup)```

#

I have more than 1 commit with the same serverid

#

But it keeps printing the first one it finds

#

How can I check for multiple and then stop when they're all found?

prisma kernel Feb 3, 2020, 9:28 PM

#

from general pythonhelp to here since it always gets buried:
on a server with user defined config stuff i want to save: I already have a sqlite db, but the configs will be of variable size (some will probably remain empty while some could have a few dozen options and values) and will probably change more often than i care to migrate the db
what should i do?
just a server side configparser ini? i will have in the double digits users for now but i try to at least know what would be the proper way to do it
or should i json serialize/pickle some form of dict?

cinder sierra Feb 3, 2020, 10:52 PM

#

that didn't seem very clear...what are you asking about?

prisma kernel Feb 3, 2020, 11:51 PM

#

how to save those values in the best way

#

0-60 key value pairs with technically know but rapid-develop-pace changing keys and values for around 80 users

cinder sierra Feb 4, 2020, 1:07 AM

#

well it seems like you have a weird setup compared to anything i've ever heard. i don't know what you're trying to do, who's supposed to have this config, why in the world you even have 0-60 config options, etc. unless you give us specifics we can't know what's best for you

wild locust Feb 4, 2020, 1:33 AM

#

Are there any guides anywhere on how to use mysql.connector? I'm basically looking for examples on how to implement it into code (inserting, retrieving, modifying)

cinder sierra Feb 4, 2020, 2:21 AM

#

there's quite a few guides out there if you just search for python mysql guides, what specifically are you looking for other than that?

rich trout Feb 4, 2020, 3:42 AM

#

@prisma kernel what to do depends on how exactly those keys are changing. Are they all "well-defined" at least at the application level (eg, some are option lists, some are numbers, some are strings)? If they are, then a properly made DB is the correct choice, with appropriate data columns. For option-menu's with many choices you may want to go with a table for choices (that can be added and removed on the fly) foreignkeyed to a table of config per user.

If they are not well-defined, then you'll either need to go with a different DB format like a document store (such as mongodb), or do a "metakey" approach where you serialize everything to strings and handle them yourself (eg, rows of "user|keyname|keyvalue" so you can grab what you need but lose all the convenience of database datatypes

rustic quarry Feb 4, 2020, 4:38 AM

#

since when did it chnged ? asyncpg.exceptions.UndefinedFunctionError: operator does not exist: bigint[] -> unknown HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

prisma kernel Feb 4, 2020, 10:12 AM

#

@rich trout thanks! and basically, some fiddling around with my vision and my users revealed that all of the datatypes will be strings. So i will use a combined key of username and key and the value will always be string, in an extra table

copper verge Feb 4, 2020, 10:40 AM

#

I'm havin problems with the usage of indexes in my mongodb. I created a compound index on 2 fields and then analyze the performance of a query that searches for a specific value in these 2 fields. But for some reason the query does not make use of the index.

📎 unknown.png

#

📎 unknown.png

#

📎 unknown.png

copper verge Feb 4, 2020, 11:53 AM

#

Finally fixed the problem, the performance analyzer uses the index and i'm satisfied by the performance.

But,
when using the query now in code the query takes like 5-6 seconds although the performance analyzer performes the query in 55-100ms Oo
Why on earth ?

paper grail Feb 4, 2020, 7:41 PM

#

Lot of factors. One issue could be if you're using an ORM and the ORM is converting all the sql data into objects.

#

Hey everyone so running a Flask app with SQLalchemy. I have a user model and a bunch of relationships on it. I want to target specific columns in different models and change a column on user. For example, if an instance of user will have a relationship with user_experiences I want to say if a specific column on user_experiences changes, then I want user.needs_review = True.

I was thinking of doing this with event listeners but was worrying about whether that’s scalable. E.g you change user_experiences and then under the hood that runs a query to get user and changes the boolean there. Also not sure if it’s wise to have all these event listeners on various other models. Any suggestions?

visual agate Feb 4, 2020, 10:24 PM

#

sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread 
id 88756 and this is thread id 90288.

this error code happened after I used an executor to execute sqlite code. the script works, so i wanted to know if this error will cause any problems

#

@ me if you're able to help pls :)

pure cypress Feb 4, 2020, 10:27 PM

#

@visual agate https://docs.python.org/3/library/sqlite3.html#multithreading

visual agate Feb 4, 2020, 10:28 PM

#

oh thanks

pure cypress Feb 4, 2020, 10:29 PM

#

I believe you could open a new, concurrent connection in another thread, but sqlite still locks the db (not sure exactly when it locks/unlocks) so the other thread would have to wait.

flint bobcat Feb 5, 2020, 6:58 PM

#

Having a query as following within a grafana graph

SELECT
  created AS "time",
  entity_id AS metric,
  CAST(state AS UNSIGNED)
FROM states
WHERE
  entity_id like 'sensor.temperature_sonde_%'
ORDER BY created

Is it possible to replace/sed the entity_id before displaying it ?

copper echo Feb 6, 2020, 11:20 PM

#

how do i fetch and compare json to null or something, really i dont know how to call it xd select * from users where tinder != '{}'

#

tinder is ofc json with default value '{}'

#

so i could compare to default value

celest zodiac Feb 7, 2020, 4:27 PM

#

I have a database I'm writing an export-to-JSON function for, with each row's ID included. Multiple tables are exported, with ID-based relations between them. It occurs to me that if I import this back into a new database, I won't be able to rely on the original IDs, as there might be existing IDs with the same numbers. I'm having trouble figuring out a way to manage this except by including a legacy_id field for each row in each table (which would store the imported ID so we could in time restore proper table relations).

#

This is not so much a Python issue as a generic database issue but I thought it might be useful to raise it here.

fringe tiger Feb 7, 2020, 6:16 PM

#

why does this not work https://discordapp.com/channels/267624335836053506/439702951246692352/675394082562113616

torn sphinx Feb 7, 2020, 6:27 PM

#

here x.xxx refers to the last half of the public ip of server running database

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'rnttrntrntrntrnntre',
        'USER': 'rntnttrntrntrntrnrn',
        'PASSWORD': 'sdvsvdsvdsvdsvvd',
        'HOST': '192.168.x.xxx, localhost',
        'PORT': '5432',
    }

further above that

ALLOWED_HOSTS = ['192.168.x.xxx', '192.168.y.yyy']

here we have the same ip from above and also the public ip of the computer i develop which is also the hosting the virtual host that is the other ip.

this ufw status on... server but not client.

Status: active                                                                                                                       
5432                       ALLOW       Anywhere

Do I need to open up both ports for both machines and.. I removed localhost because it was saying this, but now it says the same thing with the public ip.

 Is the server running on host "192.168.x.xxx" and accepting
        TCP/IP connections on port 5432?

Do I need to serve up the database in a different way? The only thing i can think of left is open the same port on client side.

#

postgres, nginx, ubuntu 18

#

python 3.6

#

Are the items in the databases['default']['HOST'] supposed to be a string of csv?

#

./manage.py migrate this is what im failing at

#

maybe it has a limitation on remote access

#

doubt it

#

im gonna try just localhost, maybe the entire time the only port it wanted open was client

#

Oh damn

#

dont you need to restart firewalls?

#

There might have bee some stuff I didnt realize I skilled about a gunicorn program. So hopefully this fixes it.

torn sphinx Feb 7, 2020, 7:37 PM

#

Hopefully it works when I try to make the connection this time

📎 unknown.png

#

I was only on the developer server.

cinder sierra Feb 7, 2020, 7:37 PM

#

192.168 unless i've forgotten is moreso an internal IP, not a public IP

#

and no, clients don't need that port open unless you have something rejecting outgoing traffic on that port which is generally never a case

torn sphinx Feb 7, 2020, 7:40 PM

#

Im pretety sure thats how they make them distinct from declaring private ips with dns Either way, yeah, thats what im talking about Im pretty sure ive heard them called public plenty.

#

but thank you

#

I hope it works

cinder sierra Feb 7, 2020, 7:40 PM

#

i promise you those aren't public IPs

torn sphinx Feb 7, 2020, 7:40 PM

#

you are right

cinder sierra Feb 7, 2020, 7:40 PM

#

you also never need a public IP specified when configuring a database server-side. only when it comes to client connections within an app etc.

torn sphinx Feb 7, 2020, 7:40 PM

#

I am mixing up terms

#

But thats what i meant

cinder sierra Feb 7, 2020, 7:42 PM

#

also, please only post questions for some issues you're having as all of that was very unclear as to what you were trying to say or get help with

torn sphinx Feb 7, 2020, 7:46 PM

#

mm I am sorry.

gilded narwhal Feb 8, 2020, 12:02 AM

#

@celest zodiac when importing you could build up a map of old IDs (as stored in the saved json) to new ids (as assigned when the saved rows are added to the db)

#

then at the end go back over and replace all the references to old ids with their new alternative

#

you could also use some kind of uuid to keep track of which row is which

#

it depends on what tradeoffs youd wanna make here

#

space vs export time vs import time vs general database access time (uuid foreign keys would likely have some noticeable overhead)

celest zodiac Feb 8, 2020, 12:40 AM

#

One idea I have @gilded narwhal

#

Keep a table that consists of three columns - table name, legacy ID, and new ID. With each import, we save appropriate results to that table, then modify things as needed, then drop the table when import's finished.

gilded narwhal Feb 8, 2020, 12:45 AM

#

ah i see

#

i thought legacy_id was gonna become a permanent feature of the db

celest zodiac Feb 8, 2020, 12:50 AM

#

No, I figured this is less cluttering

torn sphinx Feb 8, 2020, 1:05 AM

#

So, whenever someone who knows nginx and postgres has time to help me... I have a working server for everything excepting connecting to the database. There are so many things that are not right about it that i cant list them all at once.

#

I mean I can 😛

#

But ping me when you have time

#

I cant connect. I dont know where the db is located. Nothing is at path


Report bugs to <pgsql-bugs@postgresql.org>.
woot@wsi:~$ psql --list
psql: FATAL:  role "woot" does not exist
woot@wsi:~$ sudo psql 
psql: FATAL:  role "root" does not exist
woot@wsi:~$

woot@wsi:~$ postgresql10.psql 
psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
woot@wsi:~$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Fri 2020-02-07 23:53:03 UTC; 1h 27min ago

Just not sure what to do.

#

Welp, its time to bust out the documentation on the server itself I guess. Its just foreign to me completely. I dont even know SQL

deft pasture Feb 8, 2020, 2:15 AM

#

say im monitoring some prices on items constantly, and have them stored, but also checking for new items. How can i make it not insert the same info twice. if there's a way? sqlite

celest zodiac Feb 8, 2020, 2:49 AM

#

Same info meaning the same item

#

?

#

@deft pasture

#

A column constraint of some kind seems like the way to do it

tawny sail Feb 8, 2020, 7:51 AM

#

you can use #web-development @scenic coral

scenic coral Feb 8, 2020, 7:51 AM

#

thanks soz

hasty elm Feb 8, 2020, 1:42 PM

#

how to setup mySQL database locally for creating a database and querying the database?

gaunt jay Feb 8, 2020, 7:10 PM

#

well you could start by gaining access to a server that has a database on it

#

use the sshtunnel module to create a local connection to the server

#

and then use pymsql to login to the database n start using it

#

from sshtunnel import SSHTunnelForwarder
import pymysql
class Database:
    def initialize(self):
        self.server = SSHTunnelForwarder(
            'hostname',
            ssh_username='your username',
            ssh_password='your password',
            remote_bind_address=('127.0.0.1', 3306)
        )
        self.server.start()

        self.cnx = pymysql.connect(
            host='localhost',
            port=self.server.local_bind_port,
            user='database username',
            password='database password',
            db='name of database'
        )```

#

@hasty elm this is how i have set it up

hasty elm Feb 8, 2020, 7:17 PM

#

@gaunt jay I don't have database, i have to create that also

#

just some dummy one

gaunt jay Feb 8, 2020, 7:18 PM

#

for that you need an sql server

#

that you can connect to

#

cos databases are stored on servers

hasty elm Feb 8, 2020, 7:20 PM

#

how to get sql server?

#

what is mysql and will mysql server will work?

gaunt jay Feb 8, 2020, 7:22 PM

#

well mysql is one of the types of servers for databases

#

u cud try exploring the free options that microsoft azure or amazon aws have to offer

hasty elm Feb 8, 2020, 7:23 PM

#

so mysql is not free?

gaunt jay Feb 8, 2020, 7:23 PM

#

you have free and paid options

#

paid options wud mostly be for larger databases with greater requirements i suppose

#

https://www.freesqldatabase.com/

admin

Home

Just another WordPress weblog

#

just look for sql servers i suppose

#

this is something i found online

hasty elm Feb 8, 2020, 7:25 PM

#

we have write queries for this i think

#

can you guide me for any article that tells how to write database schema

gaunt jay Feb 8, 2020, 7:31 PM

#

check sqlzoo

#

use w3 schools to learn how to write @hasty elm

#

and use sqlfiddle to actually practice it

junior crane Feb 8, 2020, 7:43 PM

#

Hi, I would like to have an opinion about storing Array in postgresql, i have python list with 5-10 values (type integer) , should i use Integer[] in a postgresql column or i should create a new table with a foreign key for storing array on multiple row ? Also i always want the full list when i Select

torn sphinx Feb 8, 2020, 8:38 PM

#

which datatype should i use in postgresql for python colour data?
0xff0000 => ?

#

smallint?

errant sable Feb 8, 2020, 9:18 PM

#

does anyone know the query to delete all rows with sqlite3?

void otter Feb 8, 2020, 9:20 PM

#

select * from TABLENAME;

errant sable Feb 8, 2020, 11:42 PM

#

?

#

Isn't that to view?

#

I though delete query started with DELETE FROM

shut apex Feb 9, 2020, 5:13 AM

#

I have a dataframe with multiple rows with the columns representing dates. Is it possible to see how each row correlates to eachother over the time series? Not by using a graph/plot

rain wagon Feb 9, 2020, 10:45 AM

#

@errant sable Drop the table, fastest way

#

then recreate it

#

or drop the column

#

ALTER TABLE name DROP COLUMN name

#

but make sure that foreign keys are not affected

#

other way: DELETE row_name WHERE id >= 1

subtle flax Feb 10, 2020, 7:58 AM

#

Is repetable read postgres isolation ok for this translaction?

check if user name or email exist in db
create user

#

or I should do ON CONFLICT

rich trout Feb 10, 2020, 8:08 AM

#

I would suggest reading this: https://wiki.postgresql.org/wiki/SSI

SSI

#

And this:

#

https://www.postgresql.org/docs/9.5/transaction-iso.html

PostgreSQL: Documentation: 9.5: Transaction Isolation

meager gull Feb 10, 2020, 8:31 AM

#

Any database recomendation?

torn sphinx Feb 10, 2020, 8:41 AM

#

@meager gull for what usecase?

meager gull Feb 10, 2020, 8:44 AM

#

for a project, for now i don't have a specific reason, literally just storing data

rich trout Feb 10, 2020, 9:02 AM

#

By default I'd toss you towards Sqlite

#

Or Json, depending on the type and frequency of data access

#

but given you're asking for a database, sqlite is probably what you're looking for

#

RE: @subtle flax this is the text you want:

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the transaction start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the repeatable read transaction will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the repeatable read transaction can proceed with updating the originally found row. But if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message

ERROR: could not serialize access due to concurrent update

vivid summit Feb 10, 2020, 9:06 AM

#

How can i extract all the images from a .h5 file i have?

rich trout Feb 10, 2020, 9:06 AM

#

The Repeatable Read mode provides a rigorous guarantee that each transaction sees a completely stable view of the database. However, this view will not necessarily always be consistent with some serial (one at a time) execution of concurrent transactions of the same level. For example, even a read only transaction at this level may see a control record updated to show that a batch has been completed but not see one of the detail records which is logically part of the batch because it read an earlier revision of the control record. Attempts to enforce business rules by transactions running at this isolation level are not likely to work correctly without careful use of explicit locks to block conflicting transactions.

#

So you should be using ON CONFLICT

vivid summit Feb 10, 2020, 9:07 AM

#

Its the Hierarchical Data Format, ik that much

rich trout Feb 10, 2020, 9:07 AM

#

There's a python library for it

vivid summit Feb 10, 2020, 9:07 AM

#

Yea but it stores the images in ram before starting to copy

rich trout Feb 10, 2020, 9:07 AM

#

http://docs.h5py.org/en/latest/build.html seems good to me but I've never tried it

Installation — h5py 2.10.0 documentation

vivid summit Feb 10, 2020, 9:08 AM

#

So it bluescreens my computer

rich trout Feb 10, 2020, 9:08 AM

#

Ah, that's

#

but not normal and not neccessary

vivid summit Feb 10, 2020, 9:08 AM

#

To be fair its a 5gb .h5 file

rich trout Feb 10, 2020, 9:09 AM

#

You should look into shutil.copyfileobj

#

It will stream from one place to another while only buffering a bit of it in ram

vivid summit Feb 10, 2020, 9:17 AM

#

Yea i just want a simple solution

#

Cos this is an ai database for training

#

and i want to filter out all results but the ones i want

#

1 sec figred it out

#

yay now its time for the next hell

subtle flax Feb 10, 2020, 9:30 AM

#

Thanks

glacial sparrow Feb 10, 2020, 12:49 PM

#

Hi !

I'm trying to make a label in pyQT5 not clickable. I don't neceserally want to be able to click trough, but i just want to make it unclickable. Since for now, if i click it, it make the window crash after few times, and windows see the labels as "loading elements".
So i just want to make them non interactible.

Thx in advance !

hazy smelt Feb 10, 2020, 1:07 PM

#

i just installed mysql and it wont run, i use win7 whenever i click on it, the window loads and shuts down the next moment

#

pls any help on fixing it

glacial sparrow Feb 10, 2020, 1:10 PM

#

mysql server ? Client ?

hazy smelt Feb 10, 2020, 1:12 PM

#

yes

#

Command line client

tawdry fossil Feb 10, 2020, 4:09 PM

#

hi can i know for sql when do i use subqueries

rain wagon Feb 10, 2020, 5:39 PM

#

You'd use it if your actual query depends on another

#

https://www.w3resource.com/sql/subqueries/understanding-sql-subqueries.php

w3resource

Understanding Sql subqueries - w3resource

The SQL subquery is a SELECT query that is embedded in the main SELECT statement. The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.

wispy condor Feb 10, 2020, 6:00 PM

#

what is a good database for discord bots?

upbeat lily Feb 10, 2020, 6:04 PM

#

SQLite is fine depending on the size, or postgres is good for anything bigger

cinder sierra Feb 10, 2020, 7:23 PM

#

SQLite is a wonder for discord bots yeah

#

just keep in mind the connections allowed at any given time. mine has somehow managed to get write-locked at times. i assume because one event begins updating an entry as soon as, for example, a new message is sent or something. i switched to mongodb after the fact

#

do note though: my mongodb service took up ~1GB RAM so if your VPS is one of those common 1vCPU 2GB RAM instances, don't do mongodb

upbeat lily Feb 10, 2020, 8:19 PM

#

I'd strongly reccomend against MongoDB. Relational databases are just stronger for in almost every respect

#

except for they take slightly more effort to set up

cinder sierra Feb 10, 2020, 8:21 PM

#

i highly disagree lol. they both have their reasons to use

#

if you don't need a direct relationship between data, you don't have to use a relational database

#

i'm curious of what ways you believe it's stronger in though, i may be missing something i've never noticed. for me, mongodb has much faster responses due to the document structure indexing. it being schema-less is also nice if you don't need the relational part or if you do need relational, it can still be achieved by storing the document ID of each entry

#

also if it was a plain fact that RDBMS were just "better overall no matter what" then mongodb wouldn't be so popular. i just don't appreciate a blatant "relational are stronger" view without a good reasoning

torn sphinx Feb 10, 2020, 10:00 PM

#

New at SQLite with python, is this how I would define connection?

connection = sqlite3.connect(r'./messages.db')

torn sphinx Feb 11, 2020, 3:50 AM

#

So lets say you store a bunch of user profiles

#

and one day you decide to add a new key to the profile document

#

how would you port over all the previous profiles?

little raven Feb 11, 2020, 12:44 PM

#

any good database for config storage?

fringe tiger Feb 11, 2020, 12:50 PM

#

config?

rain wagon Feb 11, 2020, 3:38 PM

#

You can store configurations in any database, however, since you only need 1 row in a table to store it, but many fields, I'd suggest a json instead

#

if you have more than 1 row, then I'd say sqlite. But it is probably overkill

pale sierra Feb 11, 2020, 3:53 PM

#

Hi, I am relatively new to SQL and I am struggling with something.

SELECT (SELECT Kamerlid FROM Indieners) AS Indiener, (SELECT Partij FROM Kamerleden WHERE Kamerlid = (SELECT Kamerlid FROM Indieners)) AS Partij_Indiener,
                        (SELECT Kamerlid FROM Medeindieners) AS Medeindiener, 
                        (SELECT Partij FROM Kamerleden WHERE Kamerlid = (SELECT Kamerlid FROM Medeindieners)) AS Partij_Medeindiener, 
                        (SELECT Datum FROM Moties WHERE Motie = (SELECT Motie FROM Medeindieners))AS Datum, 
                        (SELECT Motie FROM Indieners) AS Motie
                        FROM ((Indieners NATURAL JOIN Kamerleden)NATURAL JOIN Moties) NATURAL JOIN Medeindieners```

#

This only returns one row, is there any reason why for?

#

Don't know if I am allowed to post the DB here but I can send it

small path Feb 11, 2020, 4:56 PM

#

@torn sphinx yes, that's how you do it.

#

Jesus christ, Can someone point me to an actually useful python tutorial for SQLite? The commands for opening existing tables don't work, it says they're not found. And if I create new ones, I can't see them in the SQLite browser. Why?

sharp hare Feb 11, 2020, 5:55 PM

#

random ass question, let's say I wanted to set up a web application with django, and need a databse, how would I go about getting this database server?

#

can I set up a database on my own computer?

#

database server*

cinder sierra Feb 11, 2020, 6:39 PM

#

if you're just doing a small web app just use flask for starters

#

afterwards, the database will need to be on a 24/7 running computer. using your own is generally a bad idea, especially if the web application is going to be on 24/7 and your pc is not

reef hawk Feb 11, 2020, 10:04 PM

#

What are the disadvantages of separating tables into different functions? For ex., instead of having 1 big table called user (that contains columns name, birth, family, hobbies, etc...) into several different tables like userinfo (columns of personal info), userhobbies (columns of hobbies, such as sports, music, etc)

solid void Feb 12, 2020, 1:10 AM

#

@reef hawk i would say it's worth it, normalization is better, even if it can make the schema harder to understand, and queries more complex, it'll allow avoiding duplication of information, and lead to more efficient queries if you don't always need all the information.

astral dew Feb 12, 2020, 3:15 AM

#

Guys is there any discord server for mysql? I have some question related to it

reef hawk Feb 12, 2020, 7:36 AM

#

thanks! appreciate the insight

pale sierra Feb 12, 2020, 11:44 AM

#

@astral dew You found a server? need one too

torn sphinx Feb 12, 2020, 1:24 PM

#

Problem statement :
I have 3 data frames one contains records which we need to update , insert and delete respectively.
Is there a way to create update, insert and delete queries and execute them in batches of 1500 records

#

Can any one help in this

cinder sierra Feb 12, 2020, 4:14 PM

#

executemany on only 1500 records at a time...?

shell drift Feb 12, 2020, 5:18 PM

#

too many connections for role "dlnzstsi" im using asyncpg and no-one is connected also im using elephantsql

solid void Feb 12, 2020, 9:48 PM

#

asyncpg has a connection pool i think

#

you might want to make it smaller

#

or to configure your elephantsql to accept at least as many connections as your pool allows

#

if you are on the free plan, it's just 5 connections, i assume asyncpg has a bigger pool by default

#

or maybe you didn't use the pool and create a new connection each time, in which case i would encourage you to use the pool and configure it for 5 connections

#

@shell drift

fringe tiger Feb 12, 2020, 10:23 PM

#

I was refactoring my code a bit and digged up old problem, so right now I use TEXT for sqlite where I need to store Discord IDs as they are so huge (and it works) but as I'm going over the documentation it looks like integer data type for sqlite can support 2^63-1 which is enough to store a Discord ID, however I get the last 1-2 digits wrong when saving to database as integer peterthink
Can someone elaborate why do I get this when I try to use integer?
For example:
302140846680178689
becomes
302140846680178700 when saved to DB

solid void Feb 12, 2020, 10:43 PM

#

hm, i kind of expected some float conversion to be the problem but int(float(302140846680178689)) - 302140846680178689 gives -1 and here you have 11 of difference.

fringe tiger Feb 12, 2020, 10:53 PM

#

423248695803379722
becomes
423248695803379700
It seems it just zeroes last 2 characters
However my ID
197918569894379520
remains the same nepwut
197918569894379520

solid void Feb 12, 2020, 10:54 PM

#

in your previous example it wasn't zeroing the zast two, it changed the hundreds as well

fringe tiger Feb 12, 2020, 10:54 PM

#

and since in the past I tested with my ID and it worked it really bit me in the arse when it didn't work with other

#

ye the behaviour is not consistent

solid void Feb 12, 2020, 10:55 PM

#

it is similar to the steps with high float values, but it's integers, with a definite range, it's not supposed to happen.

#

(and it's not the same steps, but that could be an implementation detail)

#

you declared them as INTEGER, not REAL, right?

fringe tiger Feb 12, 2020, 10:57 PM

#

specifically BIG INT if that makes difference

solid void Feb 12, 2020, 10:57 PM

#

https://www.sqlite.org/datatype3.html

All values in SQL statements, whether they are literals embedded in SQL statement text or parameters bound to precompiled SQL statements have an implicit storage class. Under circumstances described below, the database engine may convert values between numeric storage classes (INTEGER and REAL) and TEXT during query execution.

fringe tiger Feb 12, 2020, 10:58 PM

#

Sweat that doesnt' sound good

solid void Feb 12, 2020, 10:58 PM

#

i think it's an INTEGER anyway, from the affinity table at section 3.1.1

fringe tiger Feb 12, 2020, 11:00 PM

#

tried integer same result
302140846680178689 becomes 302140846680178700

solid void Feb 12, 2020, 11:00 PM

#

i'm not clear on what the section i quoted means exactly, i doubt it means it converts between real and integer randomly, but i'm not 100% sure it doesn't, you might want to read the page more thoroughly than i did

fringe tiger Feb 12, 2020, 11:07 PM

#

hm read it again and don't seem to find any reason why. Defated again pepe_cry but oh well I'll just use TEXT as before. Wasn't that important just wanted to know why the weird magic behaviour peterthink maybe I ask in SO as my first question

solid void Feb 12, 2020, 11:33 PM

#

that could be a good one to ask indeed

sly harbor Feb 13, 2020, 4:58 AM

#

books=Books.query.filter(Books.title.like(<how can i use place holder here>)).all()
?
how can i use place holder inside like

astral dew Feb 13, 2020, 8:18 AM

#

@astral dew You found a server? need one too
@pale sierra not yet

wraith nymph Feb 13, 2020, 8:42 AM

#

Hey does anyone has some good ideas for Database classes? Mine are currently just a mess when it comes to updating and selecting certain values.

pallid dirge Feb 13, 2020, 2:58 PM

#

need some help with bash.

trying to use ulimit to limit file size being created by bash process

How do I go about it?

running all this on flask

solid void Feb 13, 2020, 4:06 PM

#

probably better to ask in #unix

shell drift Feb 13, 2020, 6:12 PM

#

how do i change the size of my asyncpg pool?

#

and limit it to 4

tender dagger Feb 13, 2020, 6:46 PM

#

What’s better practice with SQLite? One big database file with everything, or a separate database file for each “group”

#

Each group has multiple tables so

#

In a one file solution I’d need to prefix each tables name with that group’s id

#

And I’d imagine that would lead to a very messy db

#

But the other option is having my data folder have like 20 .db files in it

#

Which also doesn’t seem very clean

cinder sierra Feb 13, 2020, 6:59 PM

#

why would you prefix a table with a group id @tender dagger?

tender dagger Feb 13, 2020, 7:01 PM

#

because that table has data specific to that group

cinder sierra Feb 13, 2020, 7:01 PM

#

that's not how you should be structuring the database my friend

tender dagger Feb 13, 2020, 7:01 PM

#

i know

cinder sierra Feb 13, 2020, 7:01 PM

#

the table is meant to be a template, not unique

tender dagger Feb 13, 2020, 7:01 PM

#

i know

#

what i mean is

#

not the generic one

#

when i create one for a specific guild

#

then i put a buncha stuff in it, users, stuff about that users, group, stuff about the group

#

but i need a different table per group users

#

i have a table for groups, name, description, and a "link" to the table of their users

cinder sierra Feb 13, 2020, 7:03 PM

#

i believe you should refresh yourself on databases and foreign keys and whatnot. the point of foreign keys and whatnot in SQL is to avoid having redundant data like that

#

my discord bot's database, SQLite, setup was a guild table with guild IDs and general data with a channels table that had a foreign key linking those channels to those guilds and same with users

tender dagger Feb 13, 2020, 7:04 PM

#

its not redundant sorry, the group data is all in one file, but the user stuff is in a different table per group since it needs to be group specific

cinder sierra Feb 13, 2020, 7:04 PM

#

you can achieve that without doing what you're doing

#

if you'd like help i can help but if you're going to refuse the help when you've asked for it then why did you ask

tender dagger Feb 13, 2020, 7:05 PM

#

but you can't have a table inside a table in sqlite

#

i just don't get how i can store it specific to guild without having a different table for each guild

cinder sierra Feb 13, 2020, 7:05 PM

#

you aren't meant to do that anyways, you're right. that table should be separate

#

you don't

#

you enter each guild into a guild table with their ID as the primary key for that entry, etc.

#

you then link channels to its guild's ID entry as a foreign key

tender dagger Feb 13, 2020, 7:06 PM

#

well that's what i'm doing

cinder sierra Feb 13, 2020, 7:06 PM

#

and same with users if you'd like

#

link with foreign keys between two tables

#

not making brand new tables

tender dagger Feb 13, 2020, 7:06 PM

#

i just have it as
<guildid>-users

cinder sierra Feb 13, 2020, 7:06 PM

#

you don't do that is my point

#

just a users table which has a guildID field that is a foreign key

tender dagger Feb 13, 2020, 7:07 PM

#

can i have multiple tables called users?

cinder sierra Feb 13, 2020, 7:07 PM

#

no

#

and you don't need that

tender dagger Feb 13, 2020, 7:07 PM

#

i thought table names had to be unique?

#

i don't get it lol

cinder sierra Feb 13, 2020, 7:07 PM

#

they do

#

yes that's why i told you to learn more about databases. you have a misunderstanding

tender dagger Feb 13, 2020, 7:09 PM

#

ah i see, so you want me to have multiple entries per user in the user table

#

but with a key that says wwhat guild its about

#

that seems really messy to me

cinder sierra Feb 13, 2020, 7:10 PM

#

my bot had one single SQLite file. this file only needed 3 tables
guilds, channels, and users
the guilds table had 2 fields:
guildID as a primary key
guildName as the guild's name as a string
the channels table had 3 fields:
guildID as a foreign key, referencing a guilds entry
channelID as a primary key
channelName as the channel's name as a string
the users table had 3 fields:
guildID as a foreign key, referencing a guilds entry
userID as a primary key
userName as the user's name as a string

#

that is the most basic structure of a discord bot database

tender dagger Feb 13, 2020, 7:11 PM

#

yeah so multiple entries of each user, with a id for what guild its about

#

but yet again idk i think thats really messy

cinder sierra Feb 13, 2020, 7:11 PM

#

that's actually extremely structured

#

and that is exactly the purpose of relational databases

tender dagger Feb 13, 2020, 7:12 PM

#

thanks, i hate it lol

cinder sierra Feb 13, 2020, 7:12 PM

#

group similar data into one table, only connecting relevant data

tender dagger Feb 13, 2020, 7:12 PM

#

i mean it makes sense

#

it just... bleh

cinder sierra Feb 13, 2020, 7:12 PM

#

we can't help you for hating how it actually works and why there's no better way

shell drift Feb 13, 2020, 7:22 PM

#

how do i change the size of my asyncpg pool?
and limit it to 4

finite lynx Feb 13, 2020, 7:33 PM

#

Can someone link/ explain how to get staryed with using sql databases?

shell drift Feb 13, 2020, 7:57 PM

#

ok i found out how to do it now anyway

pale sierra Feb 13, 2020, 11:06 PM

#

Does anyone have experience with SQLite?

cinder sierra Feb 14, 2020, 12:09 AM

#

go ahead and ask the question preferably @pale sierra

#

sqlite is still sql, which many many people active in this channel know of

shell drift Feb 14, 2020, 8:54 AM

#

dose anyone know how to make a script that goes in a postgresql and finds the guild_id of the server and gets the prefix column of the guild_id and when i do for example print(prefix) it will say ! as in

📎 unknown.png

#

with asyncpg

shell drift Feb 14, 2020, 9:37 AM

#

nvm i found it out

pliant pendant Feb 14, 2020, 6:51 PM

#

hi community, i've come for a great question AbigailMelting

#

PostgreSQL v.10.x.x

#

How do i search for similiar matches for a string

north harbor Feb 14, 2020, 6:52 PM

#

0_0

pliant pendant Feb 14, 2020, 6:53 PM

#

e.g. scenneri should be able to return scenery as a result

#

due to it having 2 steps

north harbor Feb 14, 2020, 6:53 PM

#

Can you explain what is given

pliant pendant Feb 14, 2020, 6:53 PM

#

from being scenery

#

how do u mean by given

north harbor Feb 14, 2020, 6:54 PM

#

array

#

or

#

strings

pliant pendant Feb 14, 2020, 6:54 PM

#

Tag_ID - Tag_String -

#

i only need tagstring

#

which is about 4000+ tags

#

with 60,000+ connected relations

north harbor Feb 14, 2020, 6:55 PM

#

hmmm

pliant pendant Feb 14, 2020, 6:55 PM

#

but ignore the relations

#

just focus at the string

#

how do i find similiar matches

north harbor Feb 14, 2020, 6:56 PM

#

can you make an example

#

I have some ideas

pliant pendant Feb 14, 2020, 6:57 PM

#

it has to be able to do 2 things

#

lets say examples

#

scenerii, 2 steps from scenery

#

allowweedt, 3 steps from allowed

#

stone, similiar match for stone fruit

#

driectiiun, 4 steps from direction (not allowed)

#

etc.

#

so it looks for 2 things

#

steps from real word

#

and similiar matches that follows after the match

north harbor Feb 14, 2020, 7:01 PM

#

oo

#

How much time do you have

pliant pendant Feb 14, 2020, 7:01 PM

#

2 weeks

#

ill do other stuff in bg

#

so i can save dem time

north harbor Feb 14, 2020, 7:02 PM

#

Can you add me as friends in discord?

pliant pendant Feb 14, 2020, 7:03 PM

#

here ya go

north harbor Feb 14, 2020, 7:03 PM

#

I'll be able to solve this tommorow

pliant pendant Feb 14, 2020, 7:03 PM

#

oo really??

north harbor Feb 14, 2020, 7:03 PM

#

......

pliant pendant Feb 14, 2020, 7:04 PM

#

i mean im supergrateful

north harbor Feb 14, 2020, 7:04 PM

#

oh

#

yeah

pliant pendant Feb 14, 2020, 7:04 PM

#

if you are able to come up with a solution

toxic rune Feb 14, 2020, 10:07 PM

#

Anyone knows why:

SELECT NOW() AT TIME ZONE 'UTC' - interval '1 day';
gives different result than:
TIMESTAMP WITH TIME ZONE DEFAULT (NOW() AT TIME ZONE 'UTC' - INTERVAL '1 day'),

#

I get: 2020-02-13 22:05:58.673742 for first and: 2020-02-14 03:06:01.612891+00 for second.

cinder sierra Feb 14, 2020, 10:07 PM

#

anyone familiar with postgres able to help me figure out why in the world it's reject any external IPs after i've set the config to allow all?

toxic rune Feb 14, 2020, 10:07 PM

#

Did you restart the process and allow it in ufw?

cinder sierra Feb 14, 2020, 10:08 PM

#

i don't set up firewalls for my instances. i use a general host firewall rule

#

and yes i did restart

toxic rune Feb 14, 2020, 10:08 PM

#

I think you might need to explicitly allow the port to be used, not 100% sure

cinder sierra Feb 14, 2020, 10:08 PM

#

you don't, i promise you

toxic rune Feb 14, 2020, 10:09 PM

#

Well, I talk from my experience. I had configured everything but the port and I couldn't.

cinder sierra Feb 14, 2020, 10:09 PM

#

👌

toxic rune Feb 14, 2020, 10:10 PM

#

Well I just removed the ufw rule for port 5432 and I can't access remotely anymore. warShrug

cinder sierra Feb 14, 2020, 10:10 PM

#

yes, i don't use ufw lol that's why i was saying that isn't required for me*

toxic rune Feb 14, 2020, 10:11 PM

#

Oh, makes sense. No idea then MakoDed

silent parrot Feb 14, 2020, 10:37 PM

#

hi kurwa pipul

craggy shore Feb 15, 2020, 2:17 AM

#

Is there a way to do:

-- $1 = id
-- $2 = amount

INSERT INTO cool_table (a, b) VALUES ($1, $2)
ON CONFLICT (a)
    DO UPDATE SET a = cool_table.a + excluded.a
ON CONFLICT (b)
    DO UPDATE SET a = cool_table.a + excluded.a
RETURNING a;

solid void Feb 15, 2020, 2:33 AM

#

you didn't say for which db, assuming postgres, the doc is not very clear, if i read it correctly you can only have one on conflict clause, but i'm not sure.

craggy shore Feb 15, 2020, 2:34 AM

#

yes postgres

#

so no way to do it?

solid void Feb 15, 2020, 2:34 AM

#

https://www.postgresql.org/docs/12/sql-insert.html#SQL-ON-CONFLICT

#

not that i know, but i'm not an expert either.

craggy shore Feb 15, 2020, 2:35 AM

#

me neither

solid void Feb 15, 2020, 2:36 AM

#

if you have an irc client, i'd ask on the postgresql channel on freenode, there are some serious experts there.

craggy shore Feb 15, 2020, 2:36 AM

#

I don't :c

solid void Feb 15, 2020, 2:36 AM

#

https://webchat.freenode.net/

#

the web client is not bad

#

it's the discord of the 90s, it's still very active, it's just a bit more barebone 🙂

neon horizon Feb 15, 2020, 7:08 AM

#

Does anyone know if there is software that can pull historical data from stock exchanges or crypto exchanges and output into a txt or excel so it can be processes in minitab or SAS?

#

I can do algorithm programming decent but api stuff i suck at however i was looking at a lot of crypto price predictions and they suck. The forecast looks like a copy paste of moving average and then slapped onto the end of the fit line for the previous years. Then it basically says yep here's where the currency will be this day.

#

I've done time series stuff before and it isn't that simple for something like this. It needs to monitor more than price for predicting.

#

Like i could program the equation but dont have anything to pull data for me

exotic badger Feb 15, 2020, 7:42 AM

#

@neon horizon have you considered google finance function on Google sheets by any chance? At least that'll pull data quickly and easily for stock exchanges. As for crypto, no clue

neon horizon Feb 15, 2020, 8:09 AM

#

IDK i just want to make a constantly updating data set that could predict based off market cap, amount traded, buy, sell, and price history.

#

i dont care if its super accurate jsut general ups and down predictions think it woudl be cool. I dont need to make a machine learning ai keeping track of irregular variables cause i dont have a supercomputer

#

lol

finite lynx Feb 15, 2020, 10:11 AM

#

Does SQLAlchemy require any extra installs? Other than just the library.

urban cradle Feb 15, 2020, 10:12 AM

#

just the lib

thorny musk Feb 15, 2020, 10:46 AM

#

is there anyone use neo4j?

karmic cliff Feb 16, 2020, 10:16 AM

#

im getting an error with connecting to dbs

#

Ignoring exception in on_ready
Traceback (most recent call last):
  File "/home/runner/.local/share/vir
tualenvs/python3/lib/python3.8/site-packages/discord/client.py", line 270,
 in _run_event
    await coro(*args, **kwargs)
  File "main.py", line 69, in on_ready
    db =  sqlite.connect('main.sqlite')
NameError: name 'sqlite' is not defined```

#

script- ```py
#startup
@bot.event
async def on_ready():
db = sqlite.connect('main.sqlite')
cursor = db.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS main(
guild_id TEXT,
msg TEXT,
channel_id TEXT

  ''')

change_status.start()
pingstats.start()
print("Bot is Ready")```

solid void Feb 16, 2020, 10:45 AM

#

@karmic cliff hm, maybe you need to import sqlite ?

stark lion Feb 16, 2020, 11:08 AM

#

i need help

#

how i can insert greek letters on sql via python

#

i get this error when i try

#

$ python sql.py 
Traceback (most recent call last):
  File "sql.py", line 16, in <module>
    mycursor.execute(sql, val)
  File "C:\Users\dhimi\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\cursor.py", line 
551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\dhimi\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\dhimi\AppData\Local\Programs\Python\Python37\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.DataError: 1366 (22007): Incorrect string value: '\xCE\xA4\xCE\x95\xCE\xA3...' for column 
`pythondb`.`members`.`fname` at row 1

solid void Feb 16, 2020, 11:12 AM

#

i don't use mysql, but did you set the encoding of your tables? there are two utf8 encodings in mysql, and the one just called utf8 is broken, you want the other one, iirc.

stark lion Feb 16, 2020, 11:13 AM

#

📎 Screenshot_7.jpg

#

i try to do it on phpmyadmin and i get a error

#

how i fix it?

#

to have greek words

solid void Feb 16, 2020, 11:14 AM

#

https://www.eversql.com/mysql-utf8-vs-utf8mb4-whats-the-difference-between-utf8-and-utf8mb4/

EverSQL

Tomer Shay @ EverSQL

MySQL utf8 vs utf8mb4 - What's the difference between utf8 and utf8...

The Story of UTF8 VS UTF8MB4
I once got a call from the support team, saying that one of our customers reported that the application fails to save data in one of our business-critical features. The customer is seeing a general error from the application. About 30 of his 500 us...

stark lion Feb 16, 2020, 11:15 AM

#

what i have to do?

#

sr im new with db

solid void Feb 16, 2020, 11:18 AM

#

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode.html if you set encoding to utf8 it's still an alias to the limited utf8mb3, you need to explicitely ask for utf8mb4.

stark lion Feb 16, 2020, 11:18 AM

#

i dont know how

#

📎 Screenshot_8.jpg

#

i select this is K?

solid void Feb 16, 2020, 11:20 AM

#

you don't have utf8mb4 in this list?

stark lion Feb 16, 2020, 11:20 AM

#

let me check

solid void Feb 16, 2020, 11:21 AM

#

if you select a greek specific one, it might work for greek, but cause you problems with other character sets, if you use utf8mb4 you should be good for all languages.

stark lion Feb 16, 2020, 11:21 AM

#

📎 Screenshot_9.jpg

solid void Feb 16, 2020, 11:21 AM

#

(and emojis, which is an important feature now)

#

hm, i don't know why you have all these variants, but i would pick either bin or general_ci

#

but lets check docs

stark lion Feb 16, 2020, 11:22 AM

#

📎 Screenshot_10.jpg

#

this?

solid void Feb 16, 2020, 11:23 AM

#

i'd say try it yeah.

stark lion Feb 16, 2020, 11:23 AM

#

📎 Screenshot_11.jpg

#

again i get the error :/

solid void Feb 16, 2020, 11:24 AM

#

hm, it might be an other setting, and you might need to recreate your tables with this encoding.

#

didn't use mysql in years, so i'm a bit rusty on that.

stark lion Feb 16, 2020, 11:24 AM

#

i start today

solid void Feb 16, 2020, 11:28 AM

#

postgresql works by default, no pain ```
➤ psql://chickita@01a2c45731f0:5432/chickita

insert into users (name, email, password) values ('αυτό λειτουργεί', 'test5@example.org', 'test');

INSERT 0 1
Time: 2.848 ms

➤ psql://chickita@01a2c45731f0:5432/chickita

select * from users;

┌──────────────────────────────────────┬───────────────────┬─────────────────┬─────────────────────────────────────────────────────────────────────────────────────────┬──────────────────────
│ user_uuid │ email │ name │ password │ creation_date
╞══════════════════════════════════════╪═══════════════════╪═════════════════╪═════════════════════════════════════════════════════════════════════════════════════════╪══════════════════════
│ 0a7068c0-5f65-4c2a-9f27-69fc0fb64ef9 │ test@example.org │ test │ $pbkdf2-sha256$29000$bS1FKEXo3fufEwKA8J6TMg$cG5FjA6tbDVp2iQI5bMC.NrGgaYMt8uGUED5mInbs38 │ 2020-02-15 03:31:13.7
│ 6391db7f-19db-484d-be46-1360c7a31265 │ test2@example.org │ test user 2 │ $pbkdf2-sha256$29000$3BuDUKq11hqjlNIaI2TsfQ$vPOo2p27GZctxb52e9vNBgy.AnBtf0jaIJ5EDvZlA74 │ 2020-02-15 14:48:41.3
│ 3ee818bb-1291-4721-ad2d-e41572ca1542 │ test4@example.org │ ĳ │ test │ 2020-02-16 11:25:50.5
│ c34b14cf-c6ef-489c-bc17-c4842ebb0d6f │ test5@example.org │ αυτό λειτουργεί │ test │ 2020-02-16 11:27:31.2
└──────────────────────────────────────┴───────────────────┴─────────────────┴─────────────────────────────────────────────────────────────────────────────────────────┴──────────────────────

stark lion Feb 16, 2020, 11:28 AM

#

why my is not working?

#

@solid void ??

solid void Feb 16, 2020, 11:36 AM

#

i'm using postgresql, not mysql, it's another open source database, that is a bit less known, but considered by many to be superior in a number of ways.

stark lion Feb 16, 2020, 11:37 AM

#

:/

solid void Feb 16, 2020, 11:38 AM

#

i'm sure it's possible to make it work with mysql, i just don't know enough to guide you to do it.

#

reading the docs i linked and other parts about encoding in it, will surely help you.

#

also, maybe try using the mysql cli, not phpmyadmin, just in case it's the problem.

stark lion Feb 16, 2020, 11:47 AM

#

📎 Screenshot_12.jpg

#

omg i did it

solid void Feb 16, 2020, 11:48 AM

#

nice 🙂

stark lion Feb 16, 2020, 11:49 AM

#

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci; 
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

#

i did this 2

solid void Feb 16, 2020, 11:51 AM

#

hm, i think you might want to do that again, but with utf8mb4 instead of utf8, if you will ever have to support more languages, and things like emojis, in your text fields.

#

as i said, utf8 is an alias to the limited utf8mb3 that they defined years ago as an optimisation, but is not an optimisation anymore, just a limitation, they keep for backward compatibility.

stark lion Feb 16, 2020, 11:52 AM

#

meh i dont need emoji

#

i need only english and greek

#

i want to make a program for my school library

#

to store all the books and members

solid void Feb 16, 2020, 11:52 AM

#

suits you then, there is just 0 advantage to keeping utf8mb3, but if you don't want, i have no business forcing you 🙂

stark lion Feb 16, 2020, 11:52 AM

#

and see what book is available etc

proud crater Feb 16, 2020, 11:53 AM

#

How about UTF-8?

solid void Feb 16, 2020, 11:54 AM

#

i wouldn't be surprised if some books have emoji in their title these days 😛

#

and if parents start using them in their children name pretty soon.

stark lion Feb 16, 2020, 11:55 AM

#

k then how i have to write?

#

the 2 commands

solid void Feb 16, 2020, 11:55 AM

#

exactly the same, but everytime there is utf8 written, replace it with utf8mb4

stark lion Feb 16, 2020, 11:56 AM

#

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

#

like that ?

solid void Feb 16, 2020, 11:56 AM

#

yes, this should work.

stark lion Feb 16, 2020, 11:56 AM

#

let me check

solid void Feb 16, 2020, 11:56 AM

#

oh you forgot a space

#

on the second line

#

before COLLATE

stark lion Feb 16, 2020, 11:57 AM

#

ohh yea xd

#

📎 Screenshot_13.jpg

#

works ;3

#

ty

solid void Feb 16, 2020, 11:59 AM

#

insert into tablename (fname, lname) values ('😄', '🎊');

#

ok, fixed

stark lion Feb 16, 2020, 12:02 PM

#

📎 Screenshot_14.jpg

#

error

solid void Feb 16, 2020, 12:03 PM

#

😦

stark lion Feb 16, 2020, 12:03 PM

#

📎 Screenshot_15.jpg

#

? xd

#

📎 Screenshot_16.jpg

solid void Feb 16, 2020, 12:04 PM

#

what did you do differently?

stark lion Feb 16, 2020, 12:04 PM

#

i use phpmyadmin

#

to insert it

solid void Feb 16, 2020, 12:04 PM

#

yeah, i guess your cmd font doesn't know how to render that.

stark lion Feb 16, 2020, 12:04 PM

#

putty *

#

anyway ty

solid void Feb 16, 2020, 12:05 PM

#

yw 🙂

civic rover Feb 16, 2020, 2:32 PM

#

I want to store a single piece of information in my database (mongodb). Is there a best practice to do this? Creating a collection for this and only storing one document in it seems inappropriate

faint prairie Feb 16, 2020, 3:31 PM

#

There's no harm in doing that, just depends on how often you plan on retrieving it or updating it and where the db is hosted for cost purposes

#

do you have other collections in the same database?

#

if so and the access rate is low, its probably fine if you just need somewhere to put it

#

otherwise I would find somewhere else to store it

paper cradle Feb 16, 2020, 7:34 PM

#

I don't understand the meaning of this sqlalchemy warning:
https://github.com/sqlalchemy/sqlalchemy/blob/2f27dd35020be46b35717cffdccaefc97b5f67dd/lib/sqlalchemy/orm/session.py#L1962-L1967

GitHub

sqlalchemy/sqlalchemy

The Database Toolkit for Python. Contribute to sqlalchemy/sqlalchemy development by creating an account on GitHub.

#

"Identity map already had an identity for %s, "
"replacing it with newly flushed object. Are there "
"load operations occurring inside of an event handler "
"within the flush?" % (instance_key,)

#

I do have an event handler on init which is adding stuff to a relationship

keen rock Feb 17, 2020, 8:40 AM

#

@bot.command()
async def test(ctx):
    myclient = MongoClient("localhost", 27017)
    db = myclient['LeagueBot']

    courses = db.courses

    course = {
        'author': 'Dexter',
        'course': 'Big Guy'
    }

    result = courses.insert_one(course)
    print(result)

This is a basic command that is supposed to insert to my mongodb database, although, it's not working properly... I think.
The problem is that it's not showing up in the MongoDB compass.
But... It is showing up here...

#

📎 a92b6123627cf85e7e01a97ccad78e59.png

tardy widget Feb 17, 2020, 8:49 AM

#

db.courses.find()

#

@keen rock

keen rock Feb 17, 2020, 8:50 AM

#

In console?

tardy widget Feb 17, 2020, 8:50 AM

#

ye

keen rock Feb 17, 2020, 8:51 AM

#

https://gyazo.com/b667aed698786c07ae95eb457b605d82

Gyazo

#

Works in cmd.

#

Not in the compass.

tardy widget Feb 17, 2020, 8:51 AM

#

Yeh it got inserted fine

keen rock Feb 17, 2020, 8:51 AM

#

Yeah but I can't see it in the compass.

tardy widget Feb 17, 2020, 8:51 AM

#

courses.insert_one() probably doesnt return anything

keen rock Feb 17, 2020, 8:51 AM

#

https://gyazo.com/b4b5c842bdaa7fb6c1406afe46ebfe8a

Gyazo

#

No courses collection

#

Only registry, which I made by hand.

tardy widget Feb 17, 2020, 8:52 AM

#

did you refresh

keen rock Feb 17, 2020, 8:52 AM

#

Yeah

tardy widget Feb 17, 2020, 8:52 AM

#

what is the compass

keen rock Feb 17, 2020, 8:52 AM

#

Just the offical MongoDB Community Compass.

#

I think I'm just not connecting to the right db maybe.

#

but I don't know how not.

#

I made a cluster on their website and I connected to it.

#

i think it's not the same "LeagueBot" db.

#

If that makes sense...

tardy widget Feb 17, 2020, 8:54 AM

#

Yeh i dont think that will be your database

#

Id download the compass if its possible

#

then connect it to localhost

keen rock Feb 17, 2020, 8:55 AM

#

Yeah I have it downloaded

#

I think I'm connected to the online db

#

and not the one on my pc.

#

Does that make sense?

#

But I don't know how to connect to the one on my pc.

#

@tardy widget @lament notch I fixed the issue, I was connecting to an online hosted db while I was working with a local db. Hope that makes sense, and thanks for the help.

lament notch Feb 17, 2020, 9:04 AM

#

did you manage to connect to your local one?

keen rock Feb 17, 2020, 9:05 AM

#

@lament notch Yes!

#

And the courses collection is there.

#

I was confused because I named the two different db's the same name.

hollow abyss Feb 17, 2020, 1:15 PM

#

how could i generate a fatal error for mongodb?

#

trying to test a watcher in kibana

torn sphinx Feb 17, 2020, 2:13 PM

#

I would like to let everyone know who has been aware of my database issues over the last few weeks of trying to get it to work...

(venv) woot@wsi:~/project$ ./manage.py makemigrations
No changes detected

Which might not look like a success but it is.

What I realized is that maybe there is a way to run my manage.py from my client and have it know how to connect to my server and make the changes necessary automatically. But... I had an idea. What if I just push the app to the server any time I want to do that and run the manage.py file there. Maybe that was what I was supposed to do all along. Either way, it seems like it knows what to do and isnt giving me the connection errors anymore. So I will take it. Sorry for my newbishness.

keen rock Feb 17, 2020, 2:22 PM

#

Congrats @torn sphinx

compact zinc Feb 17, 2020, 4:16 PM

#

hey there, I am pretty new to python and wanted to know if: there is good alternative to sqlite3?

#

would be cool if optimized for work in async/await syntax

tawny sail Feb 17, 2020, 5:28 PM

#

https://github.com/jreese/aiosqlite
check this out @compact zinc

GitHub

jreese/aiosqlite

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

compact zinc Feb 17, 2020, 5:29 PM

#

yea, someone told me, but I am kinda skeptical, since won't it break if I gonna have async connections? like conn = sqlite3.connect or should I declare it as global?

#

if I am not mistaken, sqlite max can have 1 connection for changing stuff

small path Feb 17, 2020, 6:37 PM

#

Using SQlite, is there a function that counts the amount of entries in a table? Google says no but I've been trying to make a function to count them with no success...

compact zinc Feb 17, 2020, 6:51 PM

#

okay I think you could do like this: select count(what_to_count) from table_name; I gonna check, 1 sec.

woeful maple Feb 17, 2020, 6:51 PM

#

Yeah, how doesn't COUNT(*) accomplish what you are looking for?

compact zinc Feb 17, 2020, 6:52 PM

#

and there are 10 rows

📎 unknown.png

#

@small path

compact zinc Feb 17, 2020, 7:08 PM

#

hey man I can suggest to you looking for just sql statements, since all of them same across all databases.. ya know, sum nerds in glasses came out with world wide standard n stuff

small path Feb 17, 2020, 8:06 PM

#

and there are 10 rows
@compact zinc It worked, thank you very much.

#

hey man I can suggest to you looking for just sql statements, since all of them same across all databases.. ya know, sum nerds in glasses came out with world wide standard n stuff
@compact zinc Haha good idea, thanks! I wasn't aware sqlite3 and SQL are the same language, I just stumbled in because I needed a quick and simple database for my semester project and found very little useful documentation.

compact zinc Feb 17, 2020, 8:11 PM

#

they not same, there are just stuff like select, update etc that are same across all db even oracle sql--> @small path

📎 unknown.png

ionic zenith Feb 17, 2020, 10:10 PM

#

I have a point system where the points are stored in the database. How would you do the following: When purchasing an item on the shop you need to check if the user has available currency before purchasing and then making the actual purchase.

Would this be possible with one query or should be done with two queries (one to check if the user has enough points and other to make the actual purchase.)

errant sable Feb 17, 2020, 11:08 PM

#

I have 5 columns , name, director, year, ratings and id. When I try to insert into column

#

    conn = psycopg2.connect("dbname='moviedatabase' user='user' password='password' host='localhost' port='5432'")
    c = conn.cursor()
    c.execute("INSERT INTO movies VALUES(%s, %s, %s, %s, NULL)", (name, director, year, ratings))
    conn.commit()
    conn.close()


insert("Casino", "Martin scorsese",1995, 8.2)```

#

I get the following error

#

null value in column "id" violates not-null constraint

#

I want the database to automatically insert an id number

minor ruin Feb 18, 2020, 12:44 AM

#

Blast, 2

cinder sierra Feb 18, 2020, 1:08 AM

#

@errant sable either set the id column as SERIAL (apparently not the recommended solution) or create a sequence for the id

reef hawk Feb 18, 2020, 5:52 AM

#

I have a database that I'm using, but basically to make it run faster/cost efficient, every time I start up the bot, I basically load all the data that I need from the database into a bot variable list, and I reference the list instead of the database afterwards, is that a good/bad idea?

#

When modifications are made to the data, what I would do is update the list/database simultaneously

torn sphinx Feb 18, 2020, 6:35 AM

#

With PGSQL, can I create a parent foreign key element when I insert a child element?

compact zinc Feb 18, 2020, 7:27 AM

#

Is this column serial?

solid void Feb 18, 2020, 9:23 AM

#

@reef hawk your way will prevent having two instances of your app talking to the db, as it would make your in memory copy inconsistent, so only one bot can live. Also, if you start to have complex/heavy queries on the data, it's likely that it'll be more efficient to ask sql to compute it than to loop over data in python.

reef hawk Feb 18, 2020, 9:24 AM

#

ahh alright

solid void Feb 18, 2020, 9:24 AM

#

@torn sphinx you can use "returning” to get the parent id when creating it, and use that to create the child,

keen rock Feb 18, 2020, 3:23 PM

#

        registry = self.bot.db.registry
        users_discord_id = ctx.message.author.id                                    ### Stores the id of the message.author into the 'users_discord_id' variable.

        if registry.find_one({'_id': users_discord_id}):
            print(f"You are already registered! If you would like to un-register consider using following command: `unregister`. Furthermore, if this username belongs to you please contact the staff team for assistance!")

I'm trying to check if someone is already signed up in the db.
It's not giving an error but It's also not working properly.
I know that I can use .find to return a dict and then I can take the value of the "_id" key. But when I print registry.find_one({'_id': users_discord_id}) it's not returning a dict.

pliant pendant Feb 18, 2020, 4:05 PM

#

Could some bigbrain sql mapper tell me whats best way to store

user(folder(image2, image), folder2(image2, image4))

TomatPrayingAmen

north harbor Feb 18, 2020, 4:26 PM

#

pycharm

pliant pendant Feb 18, 2020, 4:31 PM

#

@north harbor pycharm ASpikaThink

pliant pendant Feb 18, 2020, 5:14 PM

#

i think i have an idea doe

#

primitivekey(user) primitivekey(image) primitivekey(tag)

compact zinc Feb 18, 2020, 6:35 PM

#

@keen rock ya could try this: try:
sum code that can be reverted or not :P
catch
...

#

oh in database,

#

easy, do you have ids in database? like, I used this code in mine bot:

#

if c.execute('SELECT ID_Player from Player where ID_Player == {0}'.format(i.id)).fetchone() is None:
c.execute('INSERT INTO Player(ID_Player, Player_Name) VALUES(?, ?)', (i.id, i.display_name))

median night Feb 18, 2020, 7:20 PM

#

in mongodb, should I be using find_one or find? im reading that find with a limiter is faster

#

also find() returns a cursor

#

so if I want to check if an _id already exists in my db id rather use find()

#

but how can I set a boolean then to check if the id exists?

#

matching = self.db[self.collection_name].find({'_id': item['_id']}).limit(1)
if matching is not None:
    raise DropItem("Duplicate found")
else:
    self.db[self.collection_name].insert(dict(item))

#

this always raises "Duplicate found" even on an empty db

#

yet using find_one() it works fine

worthy prism Feb 18, 2020, 7:56 PM

#

using aiomotorengine, how would I filter for x document in a list field of reference fields of x document model?

plucky timber Feb 19, 2020, 3:14 AM

#

Hi guys, question regarding databases. I have a .db file that I failed to open with sqlite, but I can see its content with notepad.

#

Does it mean that the file is corrupted or is it an user error?

minor ruin Feb 19, 2020, 3:16 AM

#

SQLite are binary, I don't think notepad should work

plucky timber Feb 19, 2020, 3:18 AM

#

Thanks for the reply

#

This is what I got from trying to open it with sqlite

📎 unknown.png

#

But with notepad, I got this

📎 unknown.png

#

I am utterly confounded now as I never came across problems like this.

#

And does anyone know how to open .db file via postgres?

#

I mena, if I can't open it either with postgres then I can just report to the manager that the db file is broken.

pure cypress Feb 19, 2020, 3:34 AM

#

Yeah that isn't an sqlite DB. That's a dump of a pgsql database

#

It dumps it as a series of SQL commands

#

So it's basically just a text file with SQL code that, if ran, will reproduce the contents of the database from which this dump originates

#

If you are sure that file used to be an SQLite DB, then yes, it's "corrupted". Or perhaps better to just say it's been completely overwritten.

median night Feb 19, 2020, 11:47 AM

#

Ok so I have a database (mongodb) that holds some documents. Now I only want to add new documents to the database if the _id doenst already exist in the database

#

I have read that I should be using update_one() instead of doing a find() call for the id and if its not found insert() it

#

so I came up with this

#

self.db[self.collection_name].update_one(
    {'_id': item['_id']},
    {
        '$setOnInsert': dict(item).update({'first_seen': datetime.now()}),
        '$set': {'last_update': datetime.now()},
    },
    upsert = True,
)

#

but this failes with

#

pymongo.errors.WriteError: Modifiers operate on fields but we found type null instead. For example: {$mod: {<field>: ...}} not {$setOnInsert: null}

#

is it not possible to add the key and value pair to my dict inside the update call? or is my syntax just wrong?

#

If I just use dict(item) without any inserting new key and value pair it works fine

#

am I missing something obvious?

plucky timber Feb 19, 2020, 12:32 PM

#

@pure cypress I think I got it wrong. This is the first time that I have come across a database dump so I didn't know better. Thanks for help.

#

My assignment is "cloning our production database for
non-production environments and removing user PII, while still allowing the application to be hydrated
with data for development or testing."

#

The aforementioned database dump is the production database here.

#

@pure cypress So if I want to import this database dump, what shall I do? Can I just copy and paste the SQL command from the notepad and run it in Postgres?

#

Thanks.

final plinth Feb 19, 2020, 2:17 PM

#

Hi! Im fetching data from a Mysql database..i can get columns result except a column which contains an entry "Mining Operations"...if i replace this word with something else it works.
Anyway where i don't have to replace a record?

#

Because in future i have to dynamically fetch unknown no. Of records

pure cypress Feb 19, 2020, 2:41 PM

#

@plucky timber https://www.postgresql.org/docs/current/backup-dump.html#BACKUP-DUMP-RESTORE

plucky timber Feb 19, 2020, 3:02 PM

#

Thanks. I was merely using PgAdmin by copying and pasting the script into the query tool. Let me look into this. Thanks.

pliant pendant Feb 19, 2020, 4:50 PM

#

im having some interesting issue with my SQL Creation Query, could someone figure out why

#

CREATE TABLE image.authors (
    folder_name text PRIMARY KEY NOT NULL,
    author text NOT NULL,
    source_link text NOT NULL,
    status integer NOT NULL,
    last_updated date NOT NULL DEFAULT now(),
    library_id integer NOT NULL
);

CREATE TABLE image.images (
    image_id text PRIMARY KEY NOT NULL,
    r_id serial PRIMARY KEY NOT NULL DEFAULT nextval('image_rid_seq'::regclass),
    FOREIGN KEY (folder_name) REFERENCES image.authors(folder_name) ON UPDATE CASCADE ON DELETE CASCADE,
    image_type integer NOT NULL,
    image_extension integer NOT NULL,
    status integer NOT NULL,
    library_id integer NOT NULL
);

CREATE TABLE image.tag_names (
    tag_name text PRIMARY KEY NOT NULL,
    tag_id serial PRIMARY KEY NOT NULL DEFAULT nextval('tag_id_seq'::regclass),
);        (ERROR IS HERE)

CREATE TABLE image.tag_relations (
    FOREIGN KEY r_id REFERENCES image.images(r_id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY tag_id REFERENCES image.tag_names(tag_id) ON UPDATE CASCADE ON DELETE CASCADE
);

#

ERROR: syntax error next or close to ")"
LINE 23: );
SQL state: 42601
Character: 737

compact zinc Feb 19, 2020, 5:17 PM

#

@pliant pendant

📎 unknown.png

#

I think there shouldn't be ,

pliant pendant Feb 19, 2020, 5:27 PM

#

yeah i noticed LUL

#

fixed that

#

also this

torn sphinx Feb 19, 2020, 5:28 PM

#

a foreign key relation just means... one table is related to another table? Is that specific kind of relation?

pliant pendant Feb 19, 2020, 5:28 PM

#

CREATE TABLE image.images (
    image_id text PRIMARY KEY NOT NULL,
    r_id serial PRIMARY KEY NOT NULL DEFAULT nextval('image_rid_seq'::regclass),
    FOREIGN KEY (folder_name) REFERENCES image.authors(folder_name) ON UPDATE CASCADE ON DELETE CASCADE,
    image_type integer NOT NULL,
    image_extension integer NOT NULL,
    status integer NOT NULL,
    library_id integer NOT NULL
);

#

@torn sphinx or one table has a column thats related to another table with that column

#

Error is at the foreign key @compact zinc , multiple default values for some reaosn

#

@torn sphinx its like, if u have a list of unique items

#

and u have a list of buyers who buys items

#

u can have foreign keys that MUST exist in the unique items list

compact zinc Feb 19, 2020, 5:31 PM

#

I thought that that making connection to another table, but I kinda don't see why is it important,since ya do inner join anyways :/

pliant pendant Feb 19, 2020, 5:31 PM

#

it connects to the sources

#

its important for me

compact zinc Feb 19, 2020, 5:32 PM

#

yea I know, still using it :[[

pliant pendant Feb 19, 2020, 5:32 PM

#

oh wait wait

#

no its the r_id error

#

r_id serial PRIMARY KEY NOT NULL DEFAULT nextval('image_rid_seq'::regclass)

#

apparently it gives an error that it has multiple default values

compact zinc Feb 19, 2020, 5:33 PM

#

also, I could suggest doing every table one by one

pliant pendant Feb 19, 2020, 5:33 PM

#

why not multiple, to make sure everything works together

compact zinc Feb 19, 2020, 5:33 PM

#

cuz sometimes dbs don;t show error ok

torn sphinx Feb 19, 2020, 5:33 PM

#

I don't know if you've ever seen it but the example I'm working with is the Django polls app from their documentation. There are Questions and Choices. Questions have Choices. That makes perfect sense. But the use of the term foreign key doesn't. I suppose I will get it as I see more database stuff.

compact zinc Feb 19, 2020, 5:33 PM

#

well, yea ya could do transaction then

pliant pendant Feb 19, 2020, 5:34 PM

#

i see

median night Feb 19, 2020, 5:34 PM

#

in case someone read my post earlier, i was able to solve my problem with this

#

self.db[self.collection_name].update_one(
    {'_id': item['_id']},
    {
        '$setOnInsert': {**item, 'first_seen': datetime.now()},
        '$set': {'last_seen': datetime.now()},
     },
     upsert = True,
)

pliant pendant Feb 19, 2020, 5:35 PM

#

nois lookin

compact zinc Feb 19, 2020, 5:35 PM

#

btw, I hate this idea of: if 1 item, ya should use []

#

instead of ()

pliant pendant Feb 19, 2020, 5:38 PM

#

list instead of tuple

#

still stuck

#

CREATE TABLE image.images (
    image_id text PRIMARY KEY NOT NULL,
    r_id serial PRIMARY KEY NOT NULL DEFAULT nextval('image_r_id_seq'::regclass),
    FOREIGN KEY (folder_name) REFERENCES image.authors(folder_name) ON UPDATE CASCADE ON DELETE CASCADE,
    image_type integer NOT NULL,
    image_extension integer NOT NULL,
    status integer NOT NULL,
    library_id integer NOT NULL
);

at r_id serial PRIMARY KEY NOT NULL DEFAULT nextval('image_r_id_seq'::regclass)
ERROR: multiple default values given for column "r_id" in table "images"
SQL state: 42601

pliant pendant Feb 19, 2020, 6:33 PM

#

solved it myself and i also learned new things

#

pepegahands

fringe tiger Feb 19, 2020, 6:40 PM

#

nice Rainbawoopat

torn sphinx Feb 19, 2020, 9:08 PM

#

I'm currently using Mongo and I want to change how I store my documents

#

but I can't just change it because the old profiles won't work

#

and I don't want to wipe the old documents

#

so how do I update how I store documents without doing that

#

as an example, I store user profiles and let's say they have {"id": "123"}
but I want to change it so it's {"id": 123}
How would I change it so it's stored like that, but have it apply to all the previous profiles as well?

rain wagon Feb 19, 2020, 10:21 PM

#

@torn sphinx is id the primary key?

rain wagon Feb 19, 2020, 10:36 PM

#

def db_insert_auth_tokens(bearer_token, refresh_token, expires):
    try:
        connection = db_connect()
        cursor = connection.cursor()
        if connection is None:
            return render_template('error.j2', error=str("Can't get cursor from database!"))

        in_the_future = db_get_datetime(expires)
        rows = cursor.execute("""
                                    INSERT INTO t_tokens(bearer_token, refresh_token, expires)
                                    VALUES(?, ?, ?);
                                    """, (bearer_token, refresh_token, in_the_future))
        connection.close()
        return rows
    except sqlite3.Error as e:
        return render_template('error.j2', error=str(e))```

#

I don't get why none of this data lands in my database. It is weird

#

I am not getting any exceptions from sqlite3

torn sphinx Feb 19, 2020, 10:37 PM

#

@rain wagon that's just an example key, it isn't

rain wagon Feb 19, 2020, 10:39 PM

#

OK, because then you have to treat it differently. But if it is integers stored as string, write a py script that queries them and converts them. Remember to use LIMIT in your SQL statements.

#

Found the bug, it was this function:


def db_get_datetime(seconds=0):
    now = datetime.now()
    then = now + timedelta(seconds=seconds)
    return then```
Seconds is supposed to be a number, but I passed it as string from the json response. But never got any error from the function. Head -> Table

torn sphinx Feb 19, 2020, 10:54 PM

#

oh, I would use a py script

#

what what that script look like?

rain wagon Feb 19, 2020, 10:55 PM

#

I mean, you can also use pure sql

#

But it's probably easier with py

#

depends on the database though

#

https://docs.mongodb.com/manual/reference/operator/aggregation/toInt/

$toInt (aggregation) — MongoDB Manual

type conversion, convert to integer, integer conversion, aggregation, convert to int

#

But I am not savvy in mongodb

rain wagon Feb 19, 2020, 11:17 PM

#

Finally found the real cause after some headache

#

Apparently you are not executing inserts on the cursor, like psycops

#

but on the connection

plucky timber Feb 20, 2020, 12:40 AM

#

@pure cypress Thanks for the help. I eventually figured it out and learned quite a bit about dump and restore, thanks for the help.

pure cypress Feb 20, 2020, 12:40 AM

#

You're welcome

plucky timber Feb 20, 2020, 12:40 AM

#

Eventually I used this script to do it, following an example online

📎 restore_dump.PNG

#

NVM, did it sqlalchemy

gentle sparrow Feb 20, 2020, 1:51 PM

#

Can someone explain to me what WGSI is

#

And what it does

#

And how would it look like for a company like Instagram to use

cinder sierra Feb 20, 2020, 2:12 PM

#

in a short and sweet way, WSGI stands for Web Server Gateway Interface and it's a python standard with specifications indicating how a web server communicates with web applications

#

an example WSGI is gunicorn, it serves as a production environment for Flask, which is by default only intended for development environments

#

@gentle sparrow

rain wagon Feb 20, 2020, 2:18 PM

#

It's pythons version of fastcgi

#

What C/C++ programs are for fastcgi is WSGI for python

cinder sierra Feb 20, 2020, 2:23 PM

#

i don't think many will get that reference lol

rain wagon Feb 20, 2020, 2:26 PM

#

Does anyone know how to handle the hidden errors in the sqlite3 module?

#

I hate that it does not raise exceptions

#

For example: ```python
def db_has_bearer_tokens() -> bool:
result = db_get_bearer_tokens()
if result is None:
return "No tokens"
return True

#

This code fails, because in result, it says: no such table: t_tokens

#

Why does sqlite3 not raise an exception?

#

And before someone tells me to create the table, that was deliberate

#

Do I need to string parse every result to look for those?

cinder sierra Feb 20, 2020, 2:30 PM

#

i suppose so then. i've personally never had issues with their errors that they show, i've yet to run into one that isn't understandable

rain wagon Feb 20, 2020, 2:31 PM

#

I understand the error, but why does it not raise an exception? I must be missing something

#

psycopsg for example raises exceptions for cases like this

cinder sierra Feb 20, 2020, 2:32 PM

#

because they just didn't design it to?

rain wagon Feb 20, 2020, 2:33 PM

#

this is frustrating, because error handling is important when it comes to sensitive data

cinder sierra Feb 20, 2020, 2:33 PM

#

i can imagine some devs wouldn't like the functionality of some methods to cause a program to exit which would be common with newcomers

rain wagon Feb 20, 2020, 2:33 PM

#

I am reading the docs here, I can't find any error handling functions

cinder sierra Feb 20, 2020, 2:34 PM

#

just handle it yourself then if you need that functionality

rain wagon Feb 20, 2020, 2:34 PM

#

the docs for execute als do not describe all possible return codes etc

cinder sierra Feb 20, 2020, 2:34 PM

#

personally if you're working with sensitive data, using sqlite isn't a good start

rain wagon Feb 20, 2020, 2:35 PM

#

its not classified and I don't want to argue that. Any file on a users computer is not safe

cinder sierra Feb 20, 2020, 2:35 PM

#

i wasn't going to try to argue you mate, i'm just saying you just told me it's sensitive data and as you just said, any file isn't safe, so i would use postgres

rain wagon Feb 20, 2020, 2:36 PM

#

it's not feasible. It's a desktop app

cinder sierra Feb 20, 2020, 2:36 PM

#

well it is, as long as the client establishes connection to the database when it interacts with it. if i may ask for what you're trying to design i can help

rain wagon Feb 20, 2020, 2:37 PM

#

I know you can embed engines like MySQL into programs, without running a server, but I think that'd be overkill

cinder sierra Feb 20, 2020, 2:37 PM

#

no, not doing that

rain wagon Feb 20, 2020, 2:37 PM

#

and I am not even sure python has an interface for that

cinder sierra Feb 20, 2020, 2:37 PM

#

hosting a postgres on a server for your app

rain wagon Feb 20, 2020, 2:37 PM

#

I am not going to do that

cinder sierra Feb 20, 2020, 2:38 PM

#

may i ask what you're designing?

#

as i've done something like a password manager and used sqlite locally as a database for the user info

rain wagon Feb 20, 2020, 2:39 PM

#

A web enabled app that utilizes OAuth2 to sign into a game, then retrieve data from it

cinder sierra Feb 20, 2020, 2:39 PM

#

and i can help with encryption of the data

rain wagon Feb 20, 2020, 2:39 PM

#

the sensitive part is the access tokens

#

which I need to read unencryped

#

otherwise I'd use a salt and pepper with 3 rounds of blowfish

cinder sierra Feb 20, 2020, 2:39 PM

#

right...well you do you then

rain wagon Feb 20, 2020, 2:40 PM

#

?

#

you asked what I do

#

and I can't encrypt it because of said reasons

cinder sierra Feb 20, 2020, 2:41 PM

#

then i'll just say, i suggest the user creating a password that you hash with a salt and store then use AES to encrypt/decrypt the access tokens safely and there's your solution

rain wagon Feb 20, 2020, 2:41 PM

#

that does not solve the issue with sqlite error handling though

cinder sierra Feb 20, 2020, 2:41 PM

#

i'm afraid as i said before it's up to you to add that functionality

#

due to relational databases being so rigid, you should be able to safely design a solution. i can help if you need, though i don't appreciate you bashing the work of those developers

rain wagon Feb 20, 2020, 2:42 PM

#

I am what?

#

Idk why you are trying to start a fight so I am going to stop responding

cinder sierra Feb 20, 2020, 2:43 PM

#

i'm not trying to, i don't want to fight

#

i'm offering my help

rain wagon Feb 20, 2020, 2:45 PM

#

I don't see how I am "bashing". But it is a fact that string parsing as error handling is outdated, clunky, error prone and not very robust.

cinder sierra Feb 20, 2020, 2:46 PM

#

that

rain wagon Feb 20, 2020, 3:07 PM

#

I've solved it this way:

        check = connection.cursor()
        check_stmt = check.execute("SELECT name FROM sqlite_master WHERE type='table' and name='t_tokens'")
        check_result = check_stmt.fetchone()
        if check_result != "t_tokens":
            False```

keen rock Feb 20, 2020, 4:14 PM

#

            if registry.find({ "_id": ctx.message.author.id }) != None:
                a_registry = {
                    "selectedchampionname": givenselectedchampion.lower(),
                    "selectedchampionlogo": championPhotos[givenselectedchampion.lower()]
                }

                await registry.update_one({ "_id": ctx.message.author.id },{ "$set": a_registry })

                print("Worked!")
            else:
                print("Please use `register` command!")

#

if registry.find({ "_id": ctx.message.author.id }) != None: should be returning 'None' right now but it's not...
There are no documents in my collection yet it still doesn't return None...

torn sphinx Feb 20, 2020, 7:33 PM

#

Can anyone recommend some good software for designing and visualizing a database and its relations? It's the sort of thing that at least at this point in my learning them, I think I need a visual aid to get right, especially as it might get a little bit big. I think I want to work on this before I even start coding.

#

I will eventually be either entering PSQL commands or using the Django database API to write the database in.

rain wagon Feb 20, 2020, 7:41 PM

#

https://dbdiagram.io/

A Free Database Designer for Developers and Analysts

Quick and simple free tool to help you draw your database relationship diagrams and flow quickly using just keyboard

#

It's simple, but can be of good use

#

@torn sphinx

#

FOr actually creating it, dbeaver

#

Supports a lot of different db vendors and can visualize the db when created.

torn sphinx Feb 21, 2020, 12:15 AM

#

hey @rain wagon I'm a bit confused, what does aggregate mean?

#

and how would I use it to change it like that?

torn sphinx Feb 21, 2020, 12:39 AM

#

has anyone heard of kSQLDB?

#

I'm wondering if it can be used as a backend for a mobile application.. but not sure how to go about doing the performance calculations for it

solid void Feb 21, 2020, 12:45 AM

#

i'm usually using dia to create my diagrams, abusing the UML things to define my models, but i guess this is nice too

torn sphinx Feb 21, 2020, 5:46 AM

#

@rain wagon thanks i fell asleep earlier

rancid root Feb 21, 2020, 5:49 AM

#

@torn sphinx yeah i'm trialling it

#

it's pretty narrowly targeted at realtime streaming so what are you trying to do

torn sphinx Feb 21, 2020, 5:51 AM

#

hmm I want more than realtime streaming..

rancid root Feb 21, 2020, 5:51 AM

#

with exotic databases like ksqldb and clickhouse i think it's a bit risky to use them as the primary data store

#

our architectures have something like PG as the primary store with exotic stuff feeding into or sinking from it

torn sphinx Feb 21, 2020, 5:51 AM

#

say I have a few hundred thousand mobile users, I want to see if it can be the backend for the application that services them

rancid root Feb 21, 2020, 5:51 AM

#

backend to do what though

torn sphinx Feb 21, 2020, 5:52 AM

#

ok, so the users have wearable devices sending me data, and I stream the data through kafka so I can view it on dashboards and stuff.. then save it to ksqldb

#

Say something like fitbit

#

Fitbit and other trackers make available history data for the mobile application

#

I'm wondering if I can just do that through ksqldb

#

and still support all these users

rancid root Feb 21, 2020, 5:53 AM

#

you can sink a kafka stream to summary tables with it

#

i'd then push those primary tables into something like PG to serve to users

torn sphinx Feb 21, 2020, 5:53 AM

#

PG?

rancid root Feb 21, 2020, 5:53 AM

#

postgres

torn sphinx Feb 21, 2020, 5:53 AM

#

why postgres

#

also.. how do I manage autoscaling for postgres.. it's not open source right

#

I mean, the autoscaling feature

rancid root Feb 21, 2020, 5:54 AM

#

that depends entirely on what you're doing

#

most (all?) cloud vendors offer managed pg

#

and you probably don't need autoscaling

torn sphinx Feb 21, 2020, 5:54 AM

#

the thing is.. I'm running on my own servers

#

what do you mean

rancid root Feb 21, 2020, 5:55 AM

#

if you're running your own servers then you already own a fixed compute capacity, there's no reason to have autoscaling

#

just size your cluster appropriately from the start

torn sphinx Feb 21, 2020, 5:55 AM

#

I will.. but I want the DB running as a service, so it autoscales

rancid root Feb 21, 2020, 5:56 AM

#

what does that achieve

torn sphinx Feb 21, 2020, 5:56 AM

#

support more users

rancid root Feb 21, 2020, 5:56 AM

#

the number of users you can support is limited by how much compute you have

torn sphinx Feb 21, 2020, 5:56 AM

#

I imagine I'll need more memory added as the amount of historical data to save grows

rancid root Feb 21, 2020, 5:57 AM

#

the value proposition of autoscaling is when you're on thin margins and have a variable load, then your system can scale down and cost less during quiet times

#

very few businesses actually need that, it's easier to just size a big enough cluster from the start

torn sphinx Feb 21, 2020, 5:58 AM

#

I was hoping I could start small to support n users.. and expand physical capacity when I need to

shell ocean Feb 21, 2020, 5:58 AM

#

yes, you can...

#

that is a separate thing

torn sphinx Feb 21, 2020, 5:59 AM

#

Ok, let me phrase this with context from the start

rancid root Feb 21, 2020, 5:59 AM

#

idk what exactly you're doing but one of the first hard lessons i learned in startup land is trying to engineer well oiled infinitely scalable systems from scratch is a huge waste of time. Build something that works in the short term and worry about getting the users, then you can start thinking about more cost effective infinitely scalable stuff

torn sphinx Feb 21, 2020, 6:00 AM

#

I'm building something to manage device workflows. So users can build applications with the data from devices.

#

The components of the workflow are containerized. One component being a kafka cluster that's shared by n small users.

shell ocean Feb 21, 2020, 6:01 AM

#

@rancid root that is what more or less everyone building anything needs to hear, I think

torn sphinx Feb 21, 2020, 6:01 AM

#

The sink at the end of the workflow will be used to support backend of mobile applications they want to create with their devices

#

I dont know much about mobile applications.. and I'm not sure how to make historical data available to n users they want to support.

shell ocean Feb 21, 2020, 6:01 AM

#

the temptation to go "but I'll need this in a year's time" is very, very high

torn sphinx Feb 21, 2020, 6:02 AM

#

So I gave an example of fitness trackers. Where you can see your current location as well as historical data. But I'm not sure what to consider for this backend service.

rancid root Feb 21, 2020, 6:02 AM

#

to put things into perspective, i have a consumer grade desktop machine capable of sinking and summarising about 17 million IoT metrics per second. You don't need clusters and autoscaling to handle data unless you're a FAANG or something

torn sphinx Feb 21, 2020, 6:03 AM

#

well..

#

I'm doing this for a telecom company.. so they own the datacenters, connectivity..

rancid root Feb 21, 2020, 6:03 AM

#

are you collecting like enodeb stats?

torn sphinx Feb 21, 2020, 6:04 AM

#

it's not restricted to telecom, that's the idea behind letting users create the workflow

#

the idea is more like.. letting them pull data from devices, do analytics, sink it somewhere or support a mobile application

rancid root Feb 21, 2020, 6:05 AM

#

so you need to think about what kind of analytics and how real-timey the end user experience is

#databases

insert into users (name, email, password) values ('αυτό λειτουργεί', 'test5@example.org', 'test');

select * from users;