sweet nebula Nov 1, 2019, 5:14 PM

#

yes its just the name of the solver

#

when looking through the database i just want to see whether a certain solver is available to the user

#

if so then a button will be usable

#

otherwise it wont

#

i hope that explains it?

rich trout Nov 1, 2019, 5:25 PM

#

That is one of the reasonable ways to do that kind of thing, yes

#

Generally, though, it's User <-> SolverAccess <-> Solvers

#

Where SolverAccess is a link between a User and a Solver, so a solvers name and details are only in one place

#

You also would take advantage of the fact that a missing entry could mean no access

toxic rune Nov 1, 2019, 5:30 PM

#

I have the following tables (I also added example data which is not from that table but equal design.) they're the smallest and simplest ones I have but the same methodology should apply to all the tables in that schema.

https://hastebin.com/iwikometav.sql

What's a good design/approach to have these tables UPDATE when I have new local data rather than just DELETE the entire table and INSERT again? (Which in theory, isn't possible because I'd need to drop the entire schema first because other tables depend of Game.Zodiac for referential integrity.)
Any projects or example code where I can see how this is done?

To explain how these works if needed: zodiac_id is the unique ID identifier of a Zodiac sign which is generated in insert order (based on the local data order, I'm not sure if there's a proper approach for this.) and zodiac_identifier works as the text unique identifier of that zodiac sign.

The second table should be self explanatory I guess, I can have multiple translations for one zodiac_id and that table holds the translations.

rich trout Nov 1, 2019, 5:31 PM

#

You can INSERT into live tables, or UPDATE WHERE to edit records

toxic rune Nov 1, 2019, 5:32 PM

#

Oh yeah I'm aware of that, just not sure any proper method because I'd need to be comparing all the rows from the live tables vs data I have (which isn't a database)

rich trout Nov 1, 2019, 5:32 PM

#

Well, that depends on what the differences between your local data and the database are

#

You could use upsert to "update or insert" a record, given that you can tell which it is

rain wagon Nov 1, 2019, 5:34 PM

#

Dimbreath, do you mean new locale date, as in, new translation?

#

From what I see, these are only translations, they should never change as such

toxic rune Nov 1, 2019, 5:34 PM

#

No, more specifically if let's say a zodiac_name is changed (i.e. there was a typo or something)

#

Probably most basic example but it can happen

rain wagon Nov 1, 2019, 5:35 PM

#

Simply use an UPDATE statement to update the text

rich trout Nov 1, 2019, 5:35 PM

#

You'd need something consistent from which to identify your rows

#

If you store the id's a simple UPDATE will do wonders

toxic rune Nov 1, 2019, 5:35 PM

#

^ Yeah that was mostly my point I failed to explain

rich trout Nov 1, 2019, 5:35 PM

#

If you don't store something consistent, it's not clear on what each row references, and you really do have no option other than rebuilding everything

#

this is one of the reasons why so many applications have a "Key" string they attach to things

#

You do have the option of "nearest-text" search via textual distances, but that's rather expensive and likely overkill, depending

rain wagon Nov 1, 2019, 5:37 PM

#

UPDATE Game.ZodiacTranslation SET zodiac_name="new text" WHERE zodiac_id=X AND locale_id=X;

#

SHouldn't this work?

rich trout Nov 1, 2019, 5:37 PM

#

Another option is to keep "migrations" -- that is, whenever you change something, write in instructions that update from the old to the new, and run them in order. That way there's no confusion on what gets turned into what

rain wagon Nov 1, 2019, 5:38 PM

#

maybe I am thinking too simple here

toxic rune Nov 1, 2019, 5:38 PM

#

How does the key string they attach to things work?
Yeah that works, but as Bast said I need a proper way to identify rows because the application I have to insert/update the data doesn't really have knowledge of which row is what, since as of now it just simply inserts

#

Because since I'm testing I just drop entire schema and rebuild it (not live yet)

#

But this isn't feasible on live because even if it takes less than a minute, 0 minutes downtime is better than 1 lol

rich trout Nov 1, 2019, 5:40 PM

#

If you're wondering what to do on live, I suggest migrations

#

You'll only need to do them once, and they're fairly simple to write

toxic rune Nov 1, 2019, 5:41 PM

#

And also the application doesn't have knowledge on if there's data on the table, of if the table even exist (at least now)

rich trout Nov 1, 2019, 5:42 PM

#

Well, there is a reason almost all applications have downtime. But an easy way to avoid that is to simply live-swap the running application and databases, instead of trying to update them at-the-moment. And if you are updating them in the moment, you should probably be using migrations to make sure they go properly

toxic rune Nov 1, 2019, 5:43 PM

#

Here I'm mainly talking about data update, in the case there's a schema update (unlikely for now) then yeah I'd deal with downtime

rich trout Nov 1, 2019, 5:44 PM

#

WSGI, Nginx, and etc use that technique. They keep a running list of "workers" that work with a specific version of the data, and whenever the data changes they add in new workers with the old data, and remove older workers, until only the new are left.

toxic rune Nov 1, 2019, 5:44 PM

#

Well they're bigger projects, this is a discord bot and only one person working on it rip

rich trout Nov 1, 2019, 5:45 PM

#

Yeah, in your case you'll probably just want to do migrations

#

It's what I use for my bot, anyway

sweet nebula Nov 1, 2019, 5:45 PM

#

bast should i pm u about my thing now saying ur talking to someone else about something else now?

rich trout Nov 1, 2019, 5:46 PM

#

Grab a help channel, maybe?

#

Here's an easy migration for a typo:

#

UPDATE zodiacs SET zodiac_name = fixed WHERE zodiac_name = typoed

toxic rune Nov 1, 2019, 5:49 PM

#

I wonder how this would look with tables that hold over 15 columns or more lol

#

long lines

rich trout Nov 1, 2019, 5:49 PM

#

You don't have to update every property at once

#

The idea of a migration is you only change what you have to

toxic rune Nov 1, 2019, 5:52 PM

#

In that case wouldn't I need to check every row in the table to see what differs from the data I have vs what's in the row columns?

rich trout Nov 1, 2019, 5:54 PM

#

Well, no, provided you keep a little bit of information on what you've got in the database

#

If you're generating the data based on some input it might be more complicated, but if you're fixing a typo, writing a quick snippet to fix the typo database-side is easy enough

toxic rune Nov 1, 2019, 5:55 PM

#

It's data from another database (but different since it's SQLite)

rich trout Nov 1, 2019, 5:55 PM

#

hm

toxic rune Nov 1, 2019, 5:56 PM

#

Except that I filter out since that SQLite database has lot of stuff I don't need/care about

#

And grab what I need for my own database

rich trout Nov 1, 2019, 5:56 PM

#

right

#

Well, part of your issue is that you don't have a proper "key" in mind. Something that identifies a row from the others. If you did, this would all solidify easily based on that key. It's the fact that there isn't one that's causing the mess

#

But in the case where you're transcribing over an externally-controlled database, then you could abuse levenshtein distance to act as your "key". If you don't do it manually, which is unfortunately a popular option

#

The problem is, in your tables, you're using integer primary keys to relate them. That's the only attached piece of data. If your input changes, there's no way to determine what the change was--they could have swapped the Knight and Warrior classes for all you know. Since that's indeterminate, the amount of work that the computer can do is quite limited.

#

Which is the ultimate purpose behind migrations: "differential based updating of schema", or to put it non-pretentiously, "changing what changed in the way that matches how they changed it". If you can't determine what the changes were, then you can't do that, and simply have no choice but to accept the side effects.

#

But, if you have a clear way to view your external database in a way that lets you visualize what has changed--say, they have an integer primary key id column available that you can map across, then you can update--by mirroring their updates.

UPDATE zodiac SET zodiac_name = new_name WHERE remote_id = changed_remote_id

#

Hopefully that wasn't too dense >.>

toxic rune Nov 1, 2019, 6:04 PM

#

Some tables can have a proper key (a text string) that can act as a unique identifier for these rows, at least for non translation tables.

#

Sadly the SQLite database (which I generate) is data that doesn't come from nice files so there's not really any relation unless I set them up manually when querying the values

rich trout Nov 1, 2019, 6:06 PM

#

If you can squeeze out a proper key for everything, you'll be golden

#

otherwise the computer can only do it's best, and you can use levenshtein or some other technique that's only "mostly right" to do some work, but you're stuck making sure everything is proper manually

#

It's the pain of unmanaged data

toxic rune Nov 1, 2019, 6:10 PM

#

Well I've been trying to work with integer primary keys since as far as I'm concerned they're faster to index and work it

#

Sounds like a drawback I'm having with them right now heh

rich trout Nov 1, 2019, 6:11 PM

#

Yeah, it is a weakness of deduplication

#

If you remove all the extra data, you lose the ability to correct based on it

toxic rune Nov 1, 2019, 6:12 PM

#

I was thinking

#

Actually not sure if it'd work...

#

https://hastebin.com/ilenaboquh.sql

If I store locally on the updating tool the id for both of these tables and map them to the unique identifier I have from the other database.
I can do the following checks at least:

If the unique identifier from the SQLite database doesn't have a ID, it means it's new data.
If the unique identifier from the SQLite database has an ID, it means it exist.

#

Still doesn't help with data comparison to check if anything was updated

#

But it at least sorts the issue of if there's new data

rich trout Nov 1, 2019, 6:16 PM

#

Updating to the same data doesn't actually count as a change

toxic rune Nov 1, 2019, 6:17 PM

#

Wouldn't there still be some delay/latency if I update (even if there's nothing to update?)

rich trout Nov 1, 2019, 6:18 PM

#

That latency will always be there unless you manage a second copy elsewhere

#

Either you have a list of differences to apply, at which point you're doing minimal work, or you have a desired end state, where you do more work but don't have to manage another copy of a database

#

Regardless, I do not see the queries of the type you're working with here taking very long at all

toxic rune Nov 1, 2019, 6:23 PM

#

~~Well they're fast if I do them sitting next to the server, if I do them from my PC then it takes quite a bit. Latency sucks...~~

rich trout Nov 1, 2019, 6:23 PM

#

Oh, that RTT will kill you ;-;

#

That's what executemany() is for

#

or executescript()

toxic rune Nov 1, 2019, 6:26 PM

#

Yeah D:

#

Yeah sadly having a list of differences to apply is probably too hard / impossible with this data

#

That method will probably work, I check if there's an id or not, update or insert if needed

#

as long as i dont lose a copy of the id mapping it shouldnt be a problem

#

and if i have a schema update for some reason then in this case i dont mind regenerating everything again i guess

rich trout Nov 1, 2019, 6:29 PM

#

👍

toxic rune Nov 1, 2019, 6:29 PM

#

now if i lose that mapping im fucked i guess

#

I'll see how I do it later, thanks for the help!

#

Hmm sadly executemany() ignores the result of the operation so I can't return the row id...

#

https://github.com/MagicStack/asyncpg/issues/137

GitHub

executemany not discarding result aka fetchmany · Issue #137 · M...

With the following query INSERT INTO people (name) VALUES ($1) RETURNING id and values to create [('anne',), ('ben',), ('charlie',)]. I believe there...

rich trout Nov 1, 2019, 6:38 PM

#

F

jade ice Nov 1, 2019, 6:54 PM

#

||I think that is a #cybersecurity question|| <response to deleted post>

trail epoch Nov 1, 2019, 6:55 PM

#

It's mixed, but yeah, I'll move it.

jade ice Nov 1, 2019, 6:55 PM

#

It could be both

trail epoch Nov 1, 2019, 6:56 PM

#

Yeah is about storing in the db, but... who knows, I really dont have that much hope to get an answer. Maybe I'll end trying both.

sweet nebula Nov 1, 2019, 7:44 PM

#

https://cdn.discordapp.com/attachments/342318764227821568/639825483609014282/unknown.png

#

if this was what i had before

#

ive changed it to this

#

📎 unknown.png

#

does this work up to 3NF?

trail epoch Nov 1, 2019, 7:50 PM

#

I see the normalization.

sweet nebula Nov 1, 2019, 7:53 PM

#

?

trail epoch Nov 1, 2019, 7:59 PM

#

https://en.wikipedia.org/wiki/Database_normalization#Satisfying_3NF

Database normalization

Database normalization is the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. It was first proposed by Edgar F. Codd as part of his relational model.
Normalizatio...

#

It means that I see that on the law, bru. No Models referencing others directly, all are using an ids to identify the values for relations, like separating the Models to have their "own business", like I did from my family, I am now a FK.

sweet nebula Nov 1, 2019, 8:01 PM

#

so its all good 🙂

trail epoch Nov 1, 2019, 8:05 PM

#

Where did you did the graphic, @sweet nebula ? I like that bg.

sweet nebula Nov 1, 2019, 8:06 PM

#

i found it online

#

https://app.sqldbm.com/

SqlDBM - Online Database Modeler

trail epoch Nov 1, 2019, 8:07 PM

#

Thanks, keep hacking.

lofty summit Nov 2, 2019, 7:37 AM

#

Hi, how to store lists in a table? And specifically lists of dates? Do I have to use JSON?

silk lava Nov 2, 2019, 7:43 AM

#

anyone able to help with sql queries?

rain wagon Nov 2, 2019, 9:10 AM

#

@lofty summit Never, ever store lists in databases and especially not json. Use at least normal form 3 to fill a database.

#

Otherwise the performance will be piss poor and you lose all powers a db gives you

#

@silk lava Sure, if you ask a question containing one

silk lava Nov 2, 2019, 9:12 AM

#

ive got this database setup

📎 unknown.png

#

What is the card number and the name of the borrowers who did not have a loan on September 1st, 2005?

#

select distinct lo.cardno, bo.fname||' '||bo.lname as "Borrower Name"
from borrower bo,loan lo
WHERE bo.cardno = lo.cardno
and lo.datein not in (select lo.datein from loan lo
    where lo.datein not between '1/SEP/2005' and '1/SEP/2005')
UNION ALL
select distinct lo.cardno, bo.fname||' '||bo.lname as "Borrower Name"
from borrower bo,loan lo
WHERE bo.cardno = lo.cardno
and lo.dateout not in (select lo.dateout from loan lo
                    where lo.dateout not between '1/SEP/2005' and '1/SEP/2005')
                    order by cardno;

#

this is what i have but not getting the desired result @rain wagon

rain wagon Nov 2, 2019, 9:13 AM

#

is this mssql?

silk lava Nov 2, 2019, 9:13 AM

#

orcale

rain wagon Nov 2, 2019, 9:13 AM

#

ugh 😄

silk lava Nov 2, 2019, 9:13 AM

#

not familiar with it?

rain wagon Nov 2, 2019, 9:14 AM

#

I don't really know it, no. Sorry. For example that: ||' '||. NEver seen this.

silk lava Nov 2, 2019, 9:14 AM

#

thats a way of concatenate

rain wagon Nov 2, 2019, 9:14 AM

#

I know standard sql

silk lava Nov 2, 2019, 9:15 AM

#

yea i've got that query made about ^^ but not exactly returning the right data

#

https://media.discordapp.net/attachments/557168081420681237/640091293568991244/unknown.png

#

this is what i currently returns

#

https://cdn.discordapp.com/attachments/557168081420681237/640091357809213450/unknown.png

#

desired outcome

#

with 60 rows

#

currently does return the 60 rows but not correct data

rain wagon Nov 2, 2019, 9:19 AM

#

Hmm, you only query if they returned a book on the specific date or brought one in

#

but you don't query if there is a book not brought back

#

So, if someone has borrowed a book on Aug 31 and didn't bring it back, this won't be in this query

silk lava Nov 2, 2019, 9:20 AM

#

yes

#

just only people who did not have a loan on the specific date

rain wagon Nov 2, 2019, 9:21 AM

#

But if I get a book on 31 Aug and bringt it back on Sept 2nd, I did have a loan, or not?

#

what value does DateIn have when the book is still loaned? NULL?

silk lava Nov 2, 2019, 9:22 AM

#

they are all dates in the "datein"

rain wagon Nov 2, 2019, 9:23 AM

#

It is the date the book is returned, right?

silk lava Nov 2, 2019, 9:28 AM

#

yes, im pretty sure

rain wagon Nov 2, 2019, 9:32 AM

#

That is one tough query, but anyway, you need to make sure nobody has a book loaned on that date, then it should be ok

#

It would be helpful if the DateIn Date would be null if the book is still loaned

lofty summit Nov 2, 2019, 11:35 AM

#

@rain wagon Hey, if lists are ineffective, if I want to stock an unknown list of dates I need a new table?

rain wagon Nov 2, 2019, 1:30 PM

#

@lofty summit What is a tab in a database for you?

graceful nimbus Nov 2, 2019, 1:37 PM

#

Hey, I'm kind of stuck rn.
I have 2 lists. 1 List with the existing database column names. And a list with their values. Both are ordered in the same way. So the first item in list number 1 is the column name for value number 1 in the other list.
So I'm trying to find a way to put all the values in the database. In 1 line

lofty summit Nov 2, 2019, 1:41 PM

#

@rain wagon table sorry

rain wagon Nov 2, 2019, 1:48 PM

#

Database tables are literally lists. One table is what you know as an excel sheet. Each page of an excel sheet is a table.The file is the database.

#

You wouldn't write it all into one field, would you?

#

Each date into one cell

#

@graceful nimbus I can't follow you

#

can you show sample data?

graceful nimbus Nov 2, 2019, 1:52 PM

#

📎 unknown.png

#

So this data, I want to import into my DB

#

📎 unknown.png

#

So the keys, like intro1 etc

#

Are the column names, and each value has to join their column

#

Even if it's empty

rain wagon Nov 2, 2019, 1:54 PM

#

why are there 28 columns named introX?

#

make one table, containing rows

#

1 column named intro should be enough

graceful nimbus Nov 2, 2019, 1:55 PM

#

I'm creating a financial web app. Each value a user fills in, will need to be saved indivual

rain wagon Nov 2, 2019, 1:56 PM

#

You need to resolve the n:m relation in a third table then

graceful nimbus Nov 2, 2019, 1:57 PM

#

Yeh but, how would I export all those values in the database

#

📎 unknown.png

#

So it will end up in 1 row

rain wagon Nov 2, 2019, 1:57 PM

#

So, you have many users, that enter many things, right?

#

what exactly do they enter? Bids?

#

Stock prices?

graceful nimbus Nov 2, 2019, 1:58 PM

#

From company name to investments, ....

rain wagon Nov 2, 2019, 2:00 PM

#

Don't worry about the export for now, bring the data base into the third normal form first. I can't give you a solution without knowing the whole purpose, but you probably need a table company, then a table investments and investments being the table that resolves that which users enters for which company which is referenced with foreign keys.

#

When your database in the third normal form you can write a python script to sort the data over

#

https://en.wikipedia.org/wiki/Database_normalization

Database normalization

Database normalization is the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. It was first proposed by Edgar F. Codd as part of his relational model.
Normalizatio...

graceful nimbus Nov 2, 2019, 2:01 PM

#

I'm not planning on exporting each value in their own catogory

#

because I have like 120 input fields a user has to fill in

#

I'm just searching for a way to do this:
INSERT INTO x (%s) VALUE (%s)
Much more simplystic

#

Because writing 240 %s isn't really the best way

inner pecan Nov 2, 2019, 7:11 PM

#

Hi, I want to use fetchmany/fetchone to process a SELECT statement in chunks for all my data but I then try and do an execution on that data so my fetchmany stops too early. Like this:

c.execute("SELECT * FROM table1 JOIN table2 USING(Symbol)")

while c.fetchmany(1000) != []: # keep going untill all rows processed
    do stuff
    if condition is met:
        c.execute("INSERT INTO table3 VALUES(?,?,?),[values]" # this makes it so my next fetchmany = [] due to it fetching the NEW execute I guess? how do i fix so it keeps looping?```

#

is this possible?

lofty summit Nov 2, 2019, 7:13 PM

#

@inner pecan what about using multiple cursors?

inner pecan Nov 2, 2019, 7:14 PM

#

oh, I didn't know that was a thing

#

I'll try that.. thanks!!

#

so I can just define cursor2 = conn.cursor() for example?

#

yeah that fixed it, perfect, thanks!!

lofty summit Nov 2, 2019, 7:16 PM

#

you're welcome

late ingot Nov 3, 2019, 3:09 AM

#

connection = db.connect('db.sqlite')
cursor = connection.cursor()
cursor.execute('CREATE TABLE msgcount (count INTEGER)')
connection.commit()
connection.close()

client.message_counter = 0

connection = db.connect('db.sqlite')
cursor = connection.cursor()
cursor.execute('INSERT INTO msgcount (count) values (f"{client.message_counter}")')
connection.commit()
connection.close()```

this should work right?
basically im creating a database for a few things and since i've never used sqlite before im trying something simple (just one item named message_count)
but do f-strings work in this?i used it when i executed `'INSERT INTO msgcount (count) values (f"{client.message_counter}")'`

#

im gonna go afk soon so ping me with an answer thx

rich trout Nov 3, 2019, 4:47 AM

#

That won't work, because the f"{ is part of the string, because f-strings require you to start them at the start of the string and not inside

#

I would point you towards passing parameters:

#

cursor.execute("insert into a (b) values (?)", value)

toxic rune Nov 3, 2019, 4:52 AM

#

Is it possible to create multiple PREPARE statements using execute_batch using psycopg2? Their example uses one excute to prepare and then batch execute but was curious if I can batch PREPARE multiple statements.

http://initd.org/psycopg/docs/extras.html#psycopg2.extras.execute_values

rich trout Nov 3, 2019, 4:55 AM

#

There is a chance but I very much doubt it

toxic rune Nov 3, 2019, 4:56 AM

#

Doing every one separately kills performance when I run it from a place not close to the server heh

rich trout Nov 3, 2019, 4:58 AM

#

You can always use ; to run multiple, you could see if the result set stays normal

toxic rune Nov 3, 2019, 4:59 AM

#

Oh I can try that I guess yeah

true skiff Nov 3, 2019, 6:17 AM

#

Any suggestions for best python libraries for connecting to mySQL database via Azure? I'm using pyodbc but it doesn't seem to support CREATE IF NOT EXISTS for table creation nor INSERT IGNORE for data.

torn sphinx Nov 3, 2019, 6:36 AM

#

quick question how can i insert one column from another table with some values from the psycopg query?
something similar to this but that works

insert into mytable(col1, col2, col3, col4)
values(%s, %s, select col3 from mytable2 where condition = %s, %s)

patent glen Nov 3, 2019, 7:04 AM

#

@torn sphinx not sure but try sql insert into mytable(col1, col2, col3, col4) select %s as col1, %s as col2, col3, %s as col4 where condition = %s

torn sphinx Nov 3, 2019, 7:18 AM

#

nope i tried that and i also tried with named parameters like:

insert into mytable(col1, col2, col3, col4)
select %(col1)s, %(col2)s, t2.col3, %(col4)s
from mytable2 where condition = %(condition_value)s

but it's not inserting anything in the case condition is null

#

not sure how i'd solve t hat (this is postgres)

glad spear Nov 3, 2019, 10:36 AM

#

Having some trouble determing if what I want to do makes sense to do in a relational DB.

For the large majority of the data I'm handling for this application, I can easily wrap my head around getting it to conform to either the 3rd normal form, or with one of the more exceptional tables, the 6th normal form.

However, I have 2 groups of data which are not strongly related to the easy cases, but which the application still needs to handle. The problem with this particular data is that I have a relation which is slightly harder to express.

The closest I've gotten, would still require an unordered set of integers of unknown size to serve as a composite primary key.

That, or I'd need a table describing the relationships there, but that would still require a variable number of entries, or remove any of the performance I'd have from a unique index which isn't just an incremental counter.

lofty summit Nov 3, 2019, 10:37 AM

#

Without example it's hard to help I think

glad spear Nov 3, 2019, 10:42 AM

#

Okay, at the highest level, I'm looking for a decent way to be able to stick a relation which looks like:

{1, 2, 3} -> values
{3, 8, 2, 4} -> other values
{2, 1, 3} -> values from first example

into a relational DB in a reasonable manner. I don't expect that it would be as performant as more normal data, but lowering the complexity around engineering the application would be what is gained by finding a good way to do this.

The problem is that the values which arent part of the key cant be made into an index, they wont be remotely unique, holding only a handful of boolean values, and a number.

rain wagon Nov 3, 2019, 10:43 AM

#

I have a similar bad boy in my current ap

#

It is a table holding settings, only one row is needed and it does not fit in the normal forms, but alas, need to save it somewhere

#

it also requires some encryption, so it just safer in the db

#

has no relations with anything

glad spear Nov 3, 2019, 10:45 AM

#

Well the numbers in the example might appear to be out of nowhere, but this is intending to describe characteristics of a grouping. All of the numbers used as part of the key have other meanng, but data describing groups of variable size doesnt seem to be easy to fit into a relational DB

#

the numbers are all used as a primary key in a different table.

#

but the data for {1, 2} would be different than to {1,2,3} so, I'm having some amount of issue with finding a way to not need more tools tacked on.

#

Keeping this at a higher level than any specific DB's handling since I'm not neccessarily beholden to any solution right now.

#

Best solution I've come up with so far is still having a table with an autoincrementing index as the primary key, storing the numbers in an array, and storing the data I'd want associated with that array next to it, with a constraint that the arrays be unique and possibly having some preprocessing function to ensure the array was always arranged the same way.

#

There's clearly an easier way to handle this particular data by not using a relational db, but with how much of it does work extremely well with one, I'm having trouble judging if that solution is fine, or needs other handling.

lofty summit Nov 3, 2019, 11:50 AM

#

How to add a CHECK CONSTRAINT with value from another table? I saw on StackOverFlow to use a function, but it would have some side-effects

ionic pecan Nov 3, 2019, 12:07 PM

#

whats your usecase

torn sphinx Nov 3, 2019, 12:54 PM

#

I am looking to fetch data from a collumn and make it into a variable

#

f.e if collumn numbers has the data 1
number_variable = c.execute('SELECT numbers from data').fetchone()
print number_variable and then it prints [(1,)]

#

how can i make it just print 1

#

without the [()]

indigo dawn Nov 3, 2019, 1:05 PM

#

when creating a foreign key do i always use the id of table b or do i use the column i'm interested in if it's got more than one column?

torn sphinx Nov 3, 2019, 1:06 PM

#

do yk anything ab my question ? @indigo dawn ^_^

indigo dawn Nov 3, 2019, 1:07 PM

#

ahhh i think it's returning a tuple right?

torn sphinx Nov 3, 2019, 1:07 PM

#

yes

#

looks like it

indigo dawn Nov 3, 2019, 1:07 PM

#

it's a tuple inside a list

#

so maybe change the type and slice it

torn sphinx Nov 3, 2019, 1:19 PM

#

how would i do that ?

#

sliced_numbers = numbers[-1]

#

and now it prints (1,)

#

@indigo dawn

indigo dawn Nov 3, 2019, 1:32 PM

#

print(type())

torn sphinx Nov 3, 2019, 1:34 PM

#

int() argument must be a string, a bytes-like object or a number, not 'tuple'

indigo dawn Nov 3, 2019, 1:47 PM

#

pop it into a new variable as a string then?

#

then slice that

late ingot Nov 3, 2019, 11:29 PM

#

https://paste.pydis.com/cafisutiri.py
whenever the person sends a message, it prints result (the amount of messages the client has seen) but it always prints [] instead of a value

rich trout Nov 4, 2019, 8:20 AM

#

You need to call con.commit(), I suspect

primal valve Nov 4, 2019, 12:29 PM

#

is there a reason to use a pool over a connection in asyncpg?

lofty summit Nov 4, 2019, 6:05 PM

#

@rich trout Would not it be con.commit() instead?

rich trout Nov 4, 2019, 6:06 PM

#

yes

magic swallow Nov 4, 2019, 6:54 PM

#

How can i open php lite admin on pycharm?

fringe tiger Nov 5, 2019, 12:46 AM

#

I keep getting 2003, "Can't connect to MySQL server on 'localhost'" when trying to connect to local database. My original problem:
https://discordapp.com/channels/267624335836053506/342318764227821568/639817363944112138
@urban cradle tried but all I saw under user and host were values I already used.

Basically I can connect with console no problem, but on the same system running the Python script to connect to local database, using the same data as connecting from console, fails!

I even tried following tutorials like this https://www.tecmint.com/fix-error-2003-hy000-cant-connect-to-mysql-server-on-127-0-0-1-111/
all of it works, my service is up and running I checked every step and my Python script still refues to connect

Tecmint: Linux Howtos, Tutorials & Guides

Aaron Kili

Fix: ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0...

This tutorial will explain how to solve "ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)" which occur when you try to access the MySQL.

cobalt cipher Nov 5, 2019, 12:48 AM

#

What lib are you using to connect to MySQL, and how did you connect via it?

fringe tiger Nov 5, 2019, 12:49 AM

#

I posted the link to original problem, it has the code https://discordapp.com/channels/267624335836053506/342318764227821568/639817363944112138

#

It works on my local PC, but on VPS it just won't connect

#

database is created btw, and yes I'm using password and did secure connection. It all works on my home PC but on vps it wont connect

#

like sudo mysql -u root -h localhost -p works and if I do show databases; it works

#

and I just don't know for the life of me why it won't connect using the python script

#

the lib is aiomysql

cobalt cipher Nov 5, 2019, 12:59 AM

#

Yes I just went over it, hmm, so the only error you get is the 2003?

#

This sounds crazy, but did you try to do show databases; via subprocess?

fringe tiger Nov 5, 2019, 1:02 AM

#

no, I did it directly with mysql, wym subproces?

#

I don't even know what I'm doing I'm trying everything trey

cobalt cipher Nov 5, 2019, 1:03 AM

#

You run show databases; from a terminal, right?

#

Try to run that same command via subprocess in python

fringe tiger Nov 5, 2019, 1:04 AM

#

yes from terminal. But how do I do it from python if I can't connect to it

#

I'm looking it up online, never used subprocess. How will it make a difference?

toxic rune Nov 5, 2019, 1:06 AM

#

@primal valve A pool is a set of connections, since connecting to a database is expensive, what you do by pooling is to have standby connections to be used.
I think some libraries like asyncpg lets you decide after how many queries that connection from the pool will be discarded and generate a new one.

cobalt cipher Nov 5, 2019, 1:06 AM

#

It's more of a test, if you can show databases; then from the script it should at least be able to ping to the host / show the databases

#

Then we can narrow down the error to aiomysql being weird or smth

#

if you cannot ping / show db altogether when using the exact same cmd from python, we need to check if something else is blocking python process

hidden basin Nov 5, 2019, 1:12 AM

#

Hey everyone! I am trying to practice SQL at home and I have SSMS downloaded. What do I do for the connect screen to just connect to a databse I create on my machine?

#

Additional info: I have done this before so I have the server software somewhere, but I forgot how I connected to the database before.

#

Please ping me if you have an answer. Thank you.

fringe tiger Nov 5, 2019, 1:29 AM

#

@cobalt cipher ok after googling a bit I think I made a working script
I get ERROR 1698 (28000): Access denied for user 'root'@'localhost' when using this

from subprocess import Popen

cmd_array = ["mysql", "-u", "root", "-p", "-e",  'show databases;']

p = Popen(cmd_array)
raw_data = p.communicate()[0]

cobalt cipher Nov 5, 2019, 1:29 AM

#

oh dear

#

You did a sudo when you test connection to the database, yes?

fringe tiger Nov 5, 2019, 1:30 AM

#

yes

#

if I prefix it ith sudo it works

#

meh i give up for today I've got no idea wwhat I'm doing

hidden basin Nov 5, 2019, 2:01 AM

#

Fyi I am still awaiting a response.

tawny sail Nov 5, 2019, 2:21 AM

#

u will need to download mysql too

#

not sure how SSMS works but i assume its similar to workbench and once u have mysql downloaded on ur machine and configure root pass, the connection should popup there, if not then add it with host=localhost and port=3306(mostly) with username and pass

#

@hidden basin

silk lava Nov 5, 2019, 9:04 AM

#

Hi, I've made a sql view with a check option but I cannot currently insert any new data into that view.

CREATE OR REPLACE VIEW NATIONAL_ITEMS (ITEMNO, ITEMDESC, iTEMPRICE) AS 
select item.itemno, item.itemdesc,item.itemprice
from invoiceitem,item
where item.itemno not in (select invoiceitem.itemno from invoiceitem)
and item.itemdesc like '%National%'
group by item.itemno, itemdesc, itemprice
WITH CHECK OPTION;

#

I get this error message when trying to insert into that view

📎 unknown.png

#

INSERT INTO NATIONAL_ITEMS VALUES ('123-456', 'National TV', 100);

The SQL statement trying to insert

urban cradle Nov 5, 2019, 10:06 AM

#

@fringe tiger

#

did you try creating a new user, rather than using root

#

also, why are you using subprocess to open the db connection?

rich trout Nov 5, 2019, 10:15 AM

#

You're trying to edit a View. You can't do that you either need edit the underlying tables directly or use a temporary table. In your case, that would be setting item.itemno, item.itemdesc, and item.itemprice in item, with a check to make sure that it matches invoiceitem

silk lava Nov 5, 2019, 11:00 AM

#

ive got it resolved

#

i had an unnecessary, table in it

torn sphinx Nov 5, 2019, 11:44 AM

#

@fringe tiger
I have found this in my notes for mysql/mariadb on debian. Not sure if its the same problem, but it could be.

if mariadb is installed from debian repository, normal users can't login as root
to fix this (in terminal):

mysql -u root -p

use mysql;
update user set plugin='mysql_native_password' where user='root';
flush privileges;
quit

fringe tiger Nov 5, 2019, 7:23 PM

#

@torn sphinx tried it no luck
@urban cradle idk first time using subprocess idk what do I do and yes I tried with another user

#

oke I got it working

#

I want to burn it but it would be a waste of my hard work

urban cradle Nov 5, 2019, 8:06 PM

#

well if you're accessing / handling the database you're probably better off using a sql module rather than using subprocess to do it

fringe tiger Nov 5, 2019, 8:14 PM

#

I didn't do that, I did it because Shirayuki wanted me to test something @urban cradle

proper copper Nov 5, 2019, 10:31 PM

#

Anyone have experience in concurrency when dealing with a "get_or_create" action (i.e. find objectA, if objectA doesn't exist, create it). I'm attempting to "get_or_create" 100 items at a time, but am running into concurrency issues where two transactions will attempt to create an object that is shared between them.

This will throw an IntegrityError, but I still want the transaction to continue or at least know how to restart it gracefully in for a better user experience. This is all in Postgres.

inner pecan Nov 6, 2019, 2:00 AM

#

I have a table ComparisonEvent which I want to have an auto increment EVENT_ID primary key column.
I want a ComparisonResult table, which has auto increment RESULT_ID primary key. I want this tables foreign key to be EVENT_ID.

E.g each EVENT_ID can have multiple RESULT_ID's belonging to it in the second table.

My problem:

I insert my ComparisonEvent details with my python script so I get:

EVENT_ID OTHER_IRRELEVANT_COLUMNS
4

I then want to insert data into the ComparisonResult table, but I want to insert with EVENT_ID 4 into this table. What is the best way to do this?

#

(I could grab the most recent EVENT_ID row after I do my ComparisonEvent insert, to get the EVENT_ID - but there is a chance if I have multiple versions of the script inserting at once I could get the EVENT_ID for another script's run by mistake doing this, due to timing?)

#

probably a very noob question,. sorry

inner pecan Nov 6, 2019, 2:32 AM

#

connection.insert_id() is what i want. Nvm.

silk lava Nov 6, 2019, 9:51 AM

#

select distinct bo.cardno, bo.fname||' '||bo.lname as "Borrower Name"
from borrower bo,loan lo
WHERE bo.cardno in (select lo.cardno from loan lo where dateout < '1/SEP/2005' and datein > '1/SEP/2005') 
order by cardno asc;

Hi, I've got a SQL problem. I want to get all borrowers who did not have loans where it was on the 1 september 2005

#

But I currently have this but it return not the correct data

#

📎 unknown.png

#

This is the database basically.

torn sphinx Nov 6, 2019, 2:37 PM

#

Umm hello.. I want to store a possibly big list of discord members for every server - which react on a message,but I also want to clear the reactions - to make it anonymous - the list so that people can not vote more than once..

My question is.. what do you recommend to hold that list? I use postgresql,or could I just store them in a text file or something,what do you recommend? Thank you

#

Please @ ping me

lofty summit Nov 6, 2019, 4:36 PM

#

@torn sphinx Depends on how big your list is and what operations you want to do with it. To be safe if you think your list could be very big use Postgres. It also provides tools to make sure that your entries are well formatted. But if your list won't be huge, and you don't have already a database, a file should be fine.

torn sphinx Nov 6, 2019, 6:29 PM

#

On wikipedia you can edit certain articles,

#

if you just click the edit button, it opens a window on the same page and it inputs a copy of the article you want to edit.
and you can just write whatever you want and publish the changes

#

i want to create something similar for my website

#

I don't know how i should search for it on google that is why i am asking here

full lintel Nov 6, 2019, 6:33 PM

#

april fools

#

im kidding

torn sphinx Nov 6, 2019, 6:34 PM

#

👀

#

oh please. what is wrong with your profile photo

#

you know its no nn november right?

#

Off-topic - ok

#

i'll copy paste this .in case people don't see it

#

if you just click the edit button, it opens a window on the same page and it inputs a copy of the article you want to edit.
and you can just write whatever you want and publish the changes
i want to create something similar for my website
I don't know how i should search for it on google that is why i am asking here```

fossil leaf Nov 6, 2019, 7:01 PM

#

rub rub rub

torn sphinx Nov 6, 2019, 7:45 PM

#

what is the database to use

#

for what i want to build?

torn sphinx Nov 7, 2019, 12:41 AM

#

content staging.. some CMS would support that

gleaming loom Nov 7, 2019, 3:43 AM

#

Hi. I'm trying to do something similar to FlickChart, and I'm trying to think of how the database would look

#

Flickchart is a site where users build up a list of their favorite movies. They're presented with two movies, and have to pick which one they like more. through doing that over and over, a ranking can be formed

#

I'm wondering how the database would be structured. I would need a Users table, and I'd want a table to store the results of every comparison

#

beyond that, I'm not sure. How would I, for instance, keep track of the different movies each user has compared?

rain wagon Nov 7, 2019, 3:48 AM

#

https://en.wikipedia.org/wiki/Relational_database https://en.wikipedia.org/wiki/Database_normalization

Relational database

A relational database is a digital database based on the relational model of data, as proposed by E. F. Codd in 1970.
A software system used to maintain relational databases is a relational database management system (RDBMS). Many relational database systems have an option of...

Database normalization

Database normalization is the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. It was first proposed by Edgar F. Codd as part of his relational model.
Normalizatio...

#

In the article about normalization, read only until 3NF

#

you can skip the rest

#

But in the end, it all depends on how detailed you want your data to be. Do the movies have all actors listed or only title and starring? DO the actors have a page?

#

etc

gleaming loom Nov 7, 2019, 3:51 AM

#

no. the movie might have the IMDB id, so it can be looked up using that

#

I guess one thing I was struggling with is, how would I store my list of ranked movies separate from your list of ranked movies?

rain wagon Nov 7, 2019, 3:53 AM

#

Resolve the n:m relation. Many users vote on many movies.

#

That is a n:m relation, so you need a third table to resolve it

#

  int id_movie FK references t_movie.id
int id_user FK references t_users.id
} ```

#

quick pseudo sql

gleaming loom Nov 7, 2019, 3:57 AM

#

so in that, each movie would be in there several times, but it's ok, because the combination of user id and movie id should be unique? and that record would also include the current rating (maybe last time it was voted on or something as well?)

rain wagon Nov 7, 2019, 3:58 AM

#

you can add any data there you need. And yes, the two foreign keys can be the pk.

#

Well depends on your DB, but most should allow it

#

you can also use a separate id

#

the point is, you have a reference to the user and a reference to the movie

#

that is how you resolve n:m

gleaming loom Nov 7, 2019, 4:00 AM

#

ok, thanks

rain wagon Nov 7, 2019, 4:00 AM

#

And of course user_id and movie_id must be unique

gleaming loom Nov 7, 2019, 4:00 AM

#

and then, to hold each comparison, it'd be something like this?

#

table battles {
    int id_user FK references t_users.id
    int id_winning_movie FK references t_movie.id
    int id_losing_movie FK references t_movie.id
}

rain wagon Nov 7, 2019, 4:03 AM

#

Whatever you see fit, but yes, that could be done this way

#

BUt wouldn't it be better to give it a score insteadn?

#

scratch that

gleaming loom Nov 7, 2019, 4:04 AM

#

this is to build up. a personalized list

#

so your list will be different from mine, even though we may have voted on the same movies

rain wagon Nov 7, 2019, 4:05 AM

#

yeah, I need my coffee first. It is too early 😄

gleaming loom Nov 7, 2019, 4:05 AM

#

that's fine. thanks for your help

#

I want to keep track of all the comparisons. however, I don't think I want to go through the whole comparison table every time the user wants to view their list, so I think I also want to have a table that is just a list of what they've compared, and the derrived score

#

or I guess, as it would be, it'd be like your earlier example, with the one table that has a combo of movie id and user id to identify it

final plinth Nov 7, 2019, 12:32 PM

#

Hi! Im trying to run .exe file which i converted from a .py code using pyinstaller. The code is working fine but when im executing the .exe in cmd its giving me an error"
Fetchall raise errors. InterfaceError : no result set to fetch from.
Failed to execute the script"

#

📎 Screenshot_2019-11-07-17-59-29-597_com.google.android.gm.png

#

Please ping me if anyone got a solution or wanna know more about this!

rain wagon Nov 7, 2019, 5:20 PM

#

The queries are empty, what do you expect? @final plinth

#

In a nutshell, this is horrible code. No error handling, wrong prints etc pp

#

And I wouldn't show screenshots where one can make out usernames either

#

Furthermore,shipping this as an executable, you will have your database filled with garbage in no time

#

Please learn security principles first, before you convert python scripts into binaries

final plinth Nov 7, 2019, 5:24 PM

#

Thank you for all that but I'm new to programming

rain wagon Nov 7, 2019, 5:24 PM

#

Which is why I am being candid with you, to save you from mistakes

final plinth Nov 7, 2019, 5:25 PM

#

I guess you didn't read above that the code is working fine when it ran on pycharm

rain wagon Nov 7, 2019, 5:25 PM

#

It didnt, because .... is not a valid sql statement

final plinth Nov 7, 2019, 5:25 PM

#

All i was doing was creating two data frames using two queries

#

I guess you have never used pycharm then?

#

It shortened those queries inside quotes

lofty summit Nov 7, 2019, 5:26 PM

#

converting a .py into .exe is a bad idea

rain wagon Nov 7, 2019, 5:26 PM

#

The + chars are kind of badly visible, my bad I guess

lofty summit Nov 7, 2019, 5:26 PM

#

especially if you're a beginner

final plinth Nov 7, 2019, 5:27 PM

#

@lofty summit i was trying that if i can convert it when im using MySQL db

rain wagon Nov 7, 2019, 5:27 PM

#

The first step here is to build in proper error handling, so you can actually see an error message

#

a try catch block goes a long way

#

Here is an example from my database code: ```python
#------------------------------------------------------------------------
def db_get_all_stations():
#------------------------------------------------------------------------
try:
cursor = get_database().cursor(cursor_factory=DictCursor)
cursor.execute("""
SELECT id_stations,
url,
name,
format,
image
FROM t_stations
ORDER BY name ASC;
""")
return cursor.fetchall()
except psycopg2.Error as err:
print(err.pgerror, file=sys.stderr)

lofty summit Nov 7, 2019, 5:29 PM

#

@rain wagon better to use logging module

rain wagon Nov 7, 2019, 5:29 PM

#

In this case, no, since I want it to show up in the apache error log

final plinth Nov 7, 2019, 5:29 PM

#

Ok... I'll work on this but i just wanna know why am i getting an error during execution and not when im running that code?

rain wagon Nov 7, 2019, 5:30 PM

#

The IDE may be masking the error, you are using some paths that are not available etc pp

#

which is a reason why I prefer simple editors like VSCode

#

I don't know all of the environment you are using there

lofty summit Nov 7, 2019, 5:31 PM

#

or no IDE at all, just you, your code and the terminal/console

rain wagon Nov 7, 2019, 5:31 PM

#

An IDE is a big beast, use a simple editor until you are familiar with the language

#

And always check return values, use try except etc

final plinth Nov 7, 2019, 5:32 PM

#

I just started learning like last week and i have an assignment which requires some statistical tests to be done

rain wagon Nov 7, 2019, 5:33 PM

#

Based on the result, the script is unable to fetch from the db. This can have multiple reasons. Connection is not working, typo in the sql statement etc

#

So, first thing to implement: Check if the connection succeeds

final plinth Nov 7, 2019, 5:34 PM

#

Idk really because everything works when im running .py it fetches data and merge the results which then i use for statistics

rain wagon Nov 7, 2019, 5:35 PM

#

Also, in the mysql connection, the host is empty

final plinth Nov 7, 2019, 5:36 PM

#

Those credentials...i edited them out😐

#

Before sending the screenshot

rain wagon Nov 7, 2019, 5:37 PM

#

host isn't a credential, it is the IP of the server

final plinth Nov 7, 2019, 5:37 PM

#

Yeah i mean whatever is required to connect to the db

rain wagon Nov 7, 2019, 5:37 PM

#

Are you using a hostname or an IP?

final plinth Nov 7, 2019, 5:38 PM

#

IP

rain wagon Nov 7, 2019, 5:38 PM

#

what does it start with?

#

first 3 numbers

final plinth Nov 7, 2019, 5:38 PM

#

160

rain wagon Nov 7, 2019, 5:38 PM

#

so it's a public server

#

well, in a public net 🙂

final plinth Nov 7, 2019, 5:39 PM

#

Ooo

rain wagon Nov 7, 2019, 5:39 PM

#

It's not in your home, is it?

final plinth Nov 7, 2019, 5:39 PM

#

Nope

rain wagon Nov 7, 2019, 5:40 PM

#

Then my only advice remains to put in more error handling to see where the actual error occurs.

#

and then go from there

#

But as @misty lava said, learn the language first before you dabble with executables

#

Just run the script in a terminal by using python scriptname.py

lofty summit Nov 7, 2019, 5:42 PM

#

^

final plinth Nov 7, 2019, 5:43 PM

#

Well my whole project is to connect to a Mysql db then do some statistical tests and results for which should be printed in PDFs dynamically for all cases

#

Thanks for the advice!

#

That's why i was focussing more on stats area than making the code more error free

#

😐

rain wagon Nov 7, 2019, 5:45 PM

#

Doing proper error handling saves more time down the road than typing it

lofty summit Nov 7, 2019, 5:45 PM

#

@final plinth Don't try to make a .exe then, it'll be easier! 😉

final plinth Nov 7, 2019, 5:46 PM

#

@lofty summitheheh yeah...but still i just wanted to know why it wasnt fetching during execution when its working otherwise?

#

Thanks both of u

rain wagon Nov 7, 2019, 5:46 PM

#

my first guess would be Windows Firewall. MySQL is on port 3306 by default and WIndows may not like that.

lofty summit Nov 7, 2019, 5:46 PM

#

We can't help your more without a more complete traceback

rain wagon Nov 7, 2019, 5:47 PM

#

Although, if the connection is established from the inside, it should allow it

#

scratch that

#

router shouldn't be an issue

#

Windows FIrewall because you allowed Python, but the exe you created is a new program

#

So Windows Firewall says nope, screw you

final plinth Nov 7, 2019, 5:49 PM

#

Ooo...i will look into that!

#

So should i use vscode as editor?

#

Or something else which helps me learning better?

rain wagon Nov 7, 2019, 5:50 PM

#

No. You should use what you want. I am just saying that IDE can make things easier or more complicated. I was once screwing around with pyVCL in PyCharm and it complained about not finding VCL libraries. I have reinstalled VLC in different places, tried different code, was pulling my hair out for 3 hours. Ran the script on a Terminal and it worked.

gleaming loom Nov 7, 2019, 5:50 PM

#

@final plinth : In PyCharm, if you click the ... that it added, it should expand it out to the full statement

#

PyCharm is fine, but like all JetBrains IDEs, sometimes it collapses things like that to make it easier to scan over

final plinth Nov 7, 2019, 5:52 PM

#

I haven't tried others and pycharm was the first thing that pops up in google so i sticked with it

rain wagon Nov 7, 2019, 5:52 PM

#

then stick with it.

#

But remember to try your code without it once in a while

final plinth Nov 7, 2019, 5:53 PM

#

I'll keep that in mind scorcher

#

Anything else which might be better for learning this language?

#

Im a rookie

gleaming loom Nov 7, 2019, 5:54 PM

#

practice

final plinth Nov 7, 2019, 5:54 PM

#

With no programming background but i am really liking this data science field

rain wagon Nov 7, 2019, 5:55 PM

#

Read about basics, before you tackle the hard stuff.

#

I know, you want to do what you like, but having basics under your belt really makes things easier.

#

Humble Bundle is often selling Book Bundles with Data Science in Python, maybe get one of those

final plinth Nov 7, 2019, 5:57 PM

#

O'reilly ones?

rain wagon Nov 7, 2019, 5:59 PM

#

Depends on the bundle, but sometimes those too

#

RIght now none though

final plinth Nov 7, 2019, 6:01 PM

#

Ooo

delicate fieldBOT Nov 7, 2019, 6:02 PM

#

Resources

The Resources page on our website contains a list of hand-selected learning resources that we regularly recommend to both beginners and experts.

lofty summit Nov 7, 2019, 7:16 PM

#

Hey, I'd like to add a check constraint on tab_2, so that you cannot have more than number_not_to_exceed value items with the same id. Should I use a Sql function for that?

📎 Screen_Shot_2019-11-07_at_19.14.49.png

rain wagon Nov 7, 2019, 7:23 PM

#

@lofty summit You can only have one item that has an id. What you mean is probably not exceeding the number of lines

#

If your db can do contraints, and seeing this is psql, which supports it, it is not a bad idea

lofty summit Nov 7, 2019, 7:24 PM

#

@rain wagon different rows of tab_2 can have the same id because they're linked with a one to n relationship.

rain wagon Nov 7, 2019, 7:25 PM

#

Well, id is marked serial, I don't think it is a good idea to have duplicates there, but if we are talking about a combined PK then I am afraid I don't have much experience to draw from.

#

but generally, SERIAL is used for PK and should never produce doublettes

lofty summit Nov 7, 2019, 7:26 PM

#

@rain wagon It's a Pk, but in tab_1 in tab_2 it's a FK

#

I'll check with a Trigger function then, though I don't have any experience with them. https://stackoverflow.com/questions/27107034/constraint-to-check-values-from-a-remotely-related-table-via-join-etc/27107221#27107221

Stack Overflow

CONSTRAINT to check values from a remotely related table (via join...

I would like to add a constraint that will check values from related table.

I have 3 tables:

CREATE TABLE somethink_usr_rel (
user_id BIGINT NOT NULL,
stomethink_id BIGINT NOT NULL
);

CR...

rain wagon Nov 7, 2019, 7:27 PM

#

BIGSERIAL is a PK in tab_1, so doublettes should never occur. But maybe my database knowledge is too limited here, so I am not going to start something 😄

#

All I know: ID's have to be unique if they are a PK

lofty summit Nov 7, 2019, 7:29 PM

#

@rain wagon There are unique in tab_1 but not in tab_2 😅 do you know what's a foreign key?

rain wagon Nov 7, 2019, 7:29 PM

#

I do know that. A FK is a reference to a key in another table. BUt it has the same value. So if that value is a PK in another table, there is no chance of getting doubles.

#

I always assume I am the one that is stupid, but I do know some shit.

lofty summit Nov 7, 2019, 7:30 PM

#

You're simply wrong, sorry.

#

Do you know what is a 1-to-n relationship?

rain wagon Nov 7, 2019, 7:31 PM

#

I know that

lofty summit Nov 7, 2019, 7:31 PM

#

Ok, let me give an example it'll be clearer I think 😅

#

#Tab 1

id | number_not_to_exceed 
------+-------------
1     | 2
2     | 4


#Tab 2

id | 
------+
1     |
1     |
1     | #RAISE ERROR because `number_not_to_exceed` == 2
2     |
2     |
2     | #No prob, `number_not_to_exceed` >3

rain wagon Nov 7, 2019, 7:35 PM

#

postgres=# create table test.discord(id BIGSERIAL PRIMARY KEY);
CREATE TABLE
postgres=# insert into test.discord VALUES(1);
INSERT 0 1
postgres=# insert into test.discord VALUES(1);
ERROR:  duplicate key value violates unique constraint "discord_pkey"
DETAIL:  Key (id)=(1) already exists.```

lofty summit Nov 7, 2019, 7:36 PM

#

You don't understand what's foreign key, I'm sorry.

rain wagon Nov 7, 2019, 7:36 PM

#

if you say so

lofty summit Nov 7, 2019, 7:38 PM

#

Do I really need to put in your hand a fully valid Sql code? I was the one to search for help at the beginning

rain wagon Nov 7, 2019, 7:39 PM

#

There is no need to be rude. Your id column is a PK and as FK, it will have the same value on your second table. If you cannot insert doubles on the first table, there won't be a double when it is a FK. If you think otherwise, then maybe I am missing something, but the console output speaks for itself. Anyway, just wait for someone more enlightened than me. No hard feelings.

lofty summit Nov 7, 2019, 7:40 PM

#

one second

#

CREATE TABLE tab_1(
id BIGSERIAL NOT NULL PRIMARY KEY,
n INT NOT NULL CONSTRAINT CHECK (n > 0)
);

CREATE TABLE "tab_2"
(
 "date"        date NOT NULL DEFAULT CURRENT_DATE,
 id BIGSERIAL NOT NULL,
 CONSTRAINT "from_tab_1" FOREIGN KEY ( "id" ) REFERENCES "tab_1" ( "id" )
);

INSERT INTO "tab_1"
(n)
VALUES (10) RETURNING tab_1.id;

INSERT INTO "tab_2"
(id)
VALUES (2);

INSERT INTO "tab_2"
(id)
VALUES (2);```

#

@rain wagon Surprise, two rows with the same id ;)

X=# SELECT * FROM tab_1;
 id | n  
----+----
  1 | 10
  2 | 10
(2 rows)

X=# SELECT * FROM tab_2;
    date    | id 
------------+----
 2019-11-07 |  2
 2019-11-07 |  2
(2 rows)

#

I hope it's clearer now! Never meant to be harsh 😉

toxic rune Nov 7, 2019, 8:35 PM

#

Since with around ~2m rows count gets a bit slower already

rustic grove Nov 7, 2019, 11:39 PM

#

with sqlalchemy i get this error sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table user has no column named username

rustic grove Nov 8, 2019, 12:44 AM

#

nm

#

just had to remove the sqlite.db

noble kite Nov 8, 2019, 5:01 AM

#

Hi. Anybody have experience with flask-migrate and sqlite?

torn sphinx Nov 8, 2019, 4:39 PM

#

thank you, @lofty summit , I just made another table in postgree,I was confused when I asked that question 😂

west lark Nov 8, 2019, 7:23 PM

#

Hey. If I have the following tables/relationship "city">"state">"country" and another table "address" is it OK for me to link "address" to "city", "state", "country" or should I only create a relationship with "city" ? Using Django ORM

rich trout Nov 8, 2019, 7:45 PM

#

you should only link it to city, and you can fetch the country through address.city.state.country

#

But be aware that not every address has a city, nor a proper street

patent glen Nov 8, 2019, 8:46 PM

#

@west lark what is the key structure of the tables?

#

anyway, I would be hesitant to normalize address data to that extent - for example, does a PO Box always have a city (in the US they do, but I don't know about other countries)?

#

I would probably have country and state IDs as part of a compound key in the city table, which makes it easy to link to everything without worrying about it linked to the wrong state for the city it's linked to

hollow stream Nov 9, 2019, 12:21 AM

#

what does the "address" table have, like unit number and street address?

silk lava Nov 9, 2019, 11:43 AM

#

from loan ln1 left join
     book
     on ln1.isbn = book.isbn
having MIN(ln1.dateout) < date '2004-01-03' or
       sum(case when book.yearpublished < 1920 then 1 else 0 end) > 0
group by ln1.cardno
order by ln1.cardno asc;

Anyone know of a different way of writing this?

empty harness Nov 9, 2019, 7:07 PM

#

Hey guys, i came here to ask what's the best / fastest solution for the following task:
Used technology: MySQL database + Python
I'm downloading a .sql file. It's format:

INSERT INTO `table` VALUES (int1,int2,int3,int4,int5,'txt1',int6,'txt2',int7,'txt3',int8);
INSERT INTO `table` VALUES (int1,int2,int3,int4,int5,'txt1',int6,'txt2',int7,'txt3',int8);
.
.

Values in each row differ.
I need to split these data in 3 tables. And i will be updating data that are already in the tables and if not then i will create new row.
So i came up with 2 solutions:
#1
Split the values from the file via python and then perform for each line 3x select + 3x update/insert in transaction.
#2
Somehow bulk insert the data into temporary table and then manipulate with the data inside database - meaning for each row in the temporary table i will perform 3 select queries (one to each actual table) and if i find row i will send 3x (update query and if not then i run insert query).

I will be running this function multiple times per day with over 10K lines in the .sql file and it will be updating / creating over 30K rows in database.
Which option would be faster and why and/or are there any other better options on how to do it?

Thanks in advice, popcorn 🙂

torn sphinx Nov 9, 2019, 7:27 PM

#

how do you get a table with data from a sql file?

#

i did this in my command line

#

\i \Users\user\PycharmProjects\PlayGround\sqlfile.sql

#

on my computer i did that

#

but it says that i am missing a required argument

#

i am using windows with PostgreSQL

#

what am i doing wrong?

torn sphinx Nov 9, 2019, 8:08 PM

#

and when i do this: \i USERS\user\PycharmProjects\PlayGround\sqlfile.sql

#

it still doesn't work

ionic pecan Nov 9, 2019, 8:09 PM

#

"how do you get a table with data from a sql file?" you mean execute a sql script on the database?

torn sphinx Nov 9, 2019, 8:09 PM

#

yes exactly

#

the script looks like this:

ionic pecan Nov 9, 2019, 8:11 PM

#

hm it seems like \i is parsing the file you pass it a bit oddly

torn sphinx Nov 9, 2019, 8:11 PM

#

    id BIGSERIAL NOT NULL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(50),
    gender VARCHAR(50),
    favcolor VARCHAR(50)
);
insert into people (first_name, last_name, email, gender, favcolor) values ('Esme', 'Rantoul', 'erantoul0@angelfire.com', 'Female', 'Aquamarine');
insert into people (first_name, last_name, email, gender, favcolor) values ('Corine', 'Braams', 'cbraams1@w3.org', 'Female', 'Teal');
insert into people (first_name, last_name, email, gender, favcolor) values ('Rosalie', 'Turbern', 'rturbern2@nsw.gov.au', 'Female', 'Orange');
insert into people (first_name, last_name, email, gender, favcolor) values ('Robert', 'Scoyne', null, 'Male', 'Teal');
insert into people (first_name, last_name, email, gender, favcolor) values ('Rachele', 'Ellacott', 'rellacott4@paginegialle.it', 'Female', 'Turquoise');
insert into people (first_name, last_name, email, gender, favcolor) values ('Thaddeus', 'Sergison', null, 'Male', 'Violet');
insert into people (first_name, last_name, email, gender, favcolor) values ('Karylin', 'Raspel', 'kraspel6@fema.gov', 'Female', 'Red');
insert into people (first_name, last_name, email, gender, favcolor) values ('Stephannie', 'Sisse', 'ssisse7@earthlink.net', 'Female', 'Fuscia');
insert into people (first_name, last_name, email, gender, favcolor) values ('Nahum', 'O'' Donohue', null, 'Male', 'Blue');
insert into people (first_name, last_name, email, gender, favcolor) values ('Kandace', 'Goggin', 'kgoggin9@home.pl', 'Female', 'Maroon');
insert into people (first_name, last_name, email, gender, favcolor) values ('Alyda', 'Liffe', null, 'Female', 'Green');
insert into people (first_name, last_name, email, gender, favcolor) values ('Baxy', 'Simonnin', null, 'Male', 'Khaki');```

#

yeah i don't know what's going on

ionic pecan Nov 9, 2019, 8:14 PM

#

from my testing it seems like if you don't add any \ into the path it opens it properly

#

maybe move the sql file to the directory where you execut e the psql and from the psql you can then include it directly from the current directory

#

just \i sqlfile.sql

#

oh wait, I think I know why

#

Anything starting with a backslash is interpreted as a psql command

#

try single quotes

torn sphinx Nov 9, 2019, 8:16 PM

#

like this?

#

\i '\USERS\user\PycharmProject\PlayGround\sqlfile.sql'

ionic pecan Nov 9, 2019, 8:16 PM

#

naw i think you can use the original path you used with \ in the beginning

torn sphinx Nov 9, 2019, 8:17 PM

#

that

ionic pecan Nov 9, 2019, 8:18 PM

#

what

#

oh you edited

#

not sure if windows is sensitive to case in path

#

original was \Users\user\PycharmProjects\PlayGround\sqlfile.sql

#

so users just titilecased

torn sphinx Nov 9, 2019, 8:20 PM

#

i don't think windows is case sensitive

#

this doesn't work either:

#

\i '\\USERS\\user\\PycharmProject\\PlayGround\\sqlfile.sql'

ionic pecan Nov 9, 2019, 8:21 PM

#

whats the error

torn sphinx Nov 9, 2019, 8:22 PM

#

/USERS/user/PycharmProject/PlayGround/sqlfile.sql: No such file or directory this is the error when i run: \i '\\USERS\\user\\PycharmProject\\PlayGround\\sqlfile.sql'

ionic pecan Nov 9, 2019, 8:22 PM

#

what about single backslashes?

torn sphinx Nov 9, 2019, 8:23 PM

#

even worse

#

i get this error USERSuserPycharmProjectPlayGroundsqlfile.sql: No such file or directory

ionic pecan Nov 9, 2019, 8:24 PM

#

what the hell

#

okay, then the only other idea i have is the current directory one

#

you can change working directory in psql

#

'\cd'

torn sphinx Nov 9, 2019, 8:26 PM

#

and then

#

is that the command?

#

cuz nothing happens

#

I don't know in which directory my SQL Shell is

ionic pecan Nov 9, 2019, 8:28 PM

#

you mean psql or the script?

torn sphinx Nov 9, 2019, 8:29 PM

#

psql

ionic pecan Nov 9, 2019, 8:29 PM

#

i don't think that matters since it should be in your PATH

torn sphinx Nov 9, 2019, 8:30 PM

#

then how do i put my sql file in the current dir

#

?

#

ya still there??

torn sphinx Nov 9, 2019, 9:32 PM

#

😩

torn sphinx Nov 9, 2019, 10:11 PM

#

can anyone help me

torn sphinx Nov 10, 2019, 8:29 AM

#

What a Cruel World

spare umbra Nov 11, 2019, 12:37 PM

#

How do i connect my python application to an online database (e.g. MySQL)
So i can access it from any python application on different pcs?

fringe tiger Nov 11, 2019, 12:40 PM

#

You just connect to it as usual

#

you state host, port, user, password and database name

#

iif it's a online database the connection has to be open for your user and IP

spare umbra Nov 11, 2019, 12:47 PM

#

And how do i make it open for only one ip and user? And what is the user specified by?

fringe tiger Nov 11, 2019, 12:47 PM

#

user aka username

#

I'm not sure how to allow outer connections to users / IPs, I googled that when I needed but not knowleadgable about it

floral mason Nov 11, 2019, 2:09 PM

#

@spare umbra you need to configure your SQL server to serve a connection at 0.0.0.0 - that’s the internal IP address which routes to your host’s external IP

#

In order for your app to connect, you need to connect to your SQL server host’s external IP and specify the port (Servers all come with a default port specified, you can look it up.)

#

Ideally, you have a DNS alias (domain name, i.e. my-sql-server.prod.net) masking the specific IP address

#

Since your database will be publicly accessible, you want to make sure you’ve set up access control and defined what users/groups have access to what data and at what level

#

You probably also want to limit access to your server to a specific IP-range so that you don’t get spammed

lofty summit Nov 11, 2019, 2:48 PM

#

To sanitize my datas input, using psycopg2, is parsing the inputs as parameters sufficient?

#

maybe it's more #cybersecurity related though...

pure scroll Nov 11, 2019, 2:58 PM

#

sanitize in terms of being safe from sql injections or what do you mean?

lofty summit Nov 11, 2019, 2:58 PM

#

yes, avoid sql injection

#

Even if most of my inputs are not direct input from users (using an API), I don't want to take any risk. Currently I generate SQL dynamically (I know it's very bad lol)

void otter Nov 11, 2019, 3:02 PM

#

If you want to protect yourself from sql injections is to use other people's libraries

#

Someone here said if you think there is not better regex solution, it is

#

Your best bet is to take a look at pypi

lofty summit Nov 11, 2019, 3:05 PM

#

I think psycopg2 module already have some checks against sql injection, it's the reason why you execute queries like that cursor.execute("INSERT INTO %s;", (my_data,))) instead of cursor.execute("INSERT INTO"+my_data+";") I was asking if this protection is sufficient or not.

fringe tiger Nov 11, 2019, 3:07 PM

#

placeholders are sufficient yes

lofty summit Nov 11, 2019, 3:09 PM

#

@fringe tiger Ty!

torn sphinx Nov 11, 2019, 7:35 PM

#

Hey guys

#

i wanted to include Databases to my webpage

#

but when i launched it to heroku, i got an internal server error

#

I used PostgreSQL

#

Why ?? did i get that error

#

TBH, i saw it coming because the SQL engine that i am using is not installed for the user that's going to access my website

#

i am basically connecting to my database from my Python application

#

getting that data and displaying it on the webpage

#

but all of that is on the local host

#

So i need serious help on how i am going to fix thus

#

this

#

because i promised my teacher that my website will be fully functional when she visits again.

#

Please man

#

I need serious help here

craggy jackal Nov 11, 2019, 8:02 PM

#

So is is Postgres actually running on your server?

torn sphinx Nov 11, 2019, 8:03 PM

#

Localhost yea

craggy jackal Nov 11, 2019, 8:03 PM

#

Can you do psql with your DB user and login with the terminal client?

torn sphinx Nov 11, 2019, 8:04 PM

#

?

craggy jackal Nov 11, 2019, 8:04 PM

#

Have you made a database yet?

torn sphinx Nov 11, 2019, 8:04 PM

#

I am using Windows so i use the command line

#

Yeah man! I got the database with all the data in it

#

A lot of data

craggy jackal Nov 11, 2019, 8:05 PM

#

Alright

#

So from python it is failing to connect?

#

Are you using psychopg2?

#

Or sqlalchemy or what?

torn sphinx Nov 11, 2019, 8:05 PM

#

I use psycopg2

#

When i test the app

#

Everything works perfectly

craggy jackal Nov 11, 2019, 8:06 PM

#

So can you do psycopg2.connect without error?

torn sphinx Nov 11, 2019, 8:06 PM

#

Yes

#

Only when i launch it to heroku, i get the error

craggy jackal Nov 11, 2019, 8:07 PM

#

What do you mean launch to heroku

torn sphinx Nov 11, 2019, 8:07 PM

#

But not in the process

#

I have made the website with flask

#

And i launched it to the heroku server

craggy jackal Nov 11, 2019, 8:08 PM

#

Are you developing it on your laptop and then pushing it to the heroku server after when you're ready?

#

Like via git or something?

#

As for as the DB goes, make sure you are doing localhost and port is 5432

#

Other than that it sounds like a flask error

torn sphinx Nov 11, 2019, 8:09 PM

#

No the port is on 5432

#

And i did it on localhost

craggy jackal Nov 11, 2019, 8:10 PM

#

Do you have a traceback you can show?

torn sphinx Nov 11, 2019, 8:10 PM

#

And yeah when I'm ready i push the changes to heroku

#

I didn't get an error

#

I mean yeh

#

I got an error but not in my python application

#

Its showed up on the website itself

#

I'm serious about this. I really need help here

void otter Nov 11, 2019, 8:14 PM

#

can you screenshot the error then

#

errors mean much more than you think

torn sphinx Nov 11, 2019, 8:14 PM

#

Internal server error

void otter Nov 11, 2019, 8:14 PM

#

do you have full trace

torn sphinx Nov 11, 2019, 8:15 PM

#

the full error:Internal Server Error The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.

#

Ok i'll search for it

#

Oh yeah. I'll run the command ```heroku logs --tail

#

This is it

#

https://pastebin.com/hyM3wNXC

Pastebin

[Bash] 2019-11-11T19:33:25.065869+00:00 app[web.1]: [2019-11-11 19...

#

this is the website

#

https://boekverslag2004.herokuapp.com/

#

its for school

#

so this is serious

void otter Nov 11, 2019, 8:23 PM

#

jinja says it is templating error

#

html error

torn sphinx Nov 11, 2019, 8:29 PM

#

🤔

#

So?

#

I'm schocked

#

Cuz when i test it,

#

Everything works just fine

void otter Nov 11, 2019, 8:31 PM

#

man repeat the error locally and find the bug source

#

repeat the error with all production config

torn sphinx Nov 11, 2019, 8:32 PM

#

Which thing now

#

Let me follow steps please

void otter Nov 11, 2019, 8:33 PM

#

i am busy now too

#

follow the bug trace

torn sphinx Nov 11, 2019, 8:34 PM

#

sweatcat

#

😭

torn sphinx Nov 11, 2019, 8:54 PM

#

the error happened when i tried to connect to the postrgre database

feral cipher Nov 11, 2019, 8:56 PM

#

Could anyone tell me something?
I'm in the mood to "cache" in my web project.
I saw some people talking about ** Redis ** would it be advisable to use it or would have another? roothink

hollow stream Nov 12, 2019, 1:00 AM

#

why do you need caching?
experiencing performance issues or just to learn caching?

steel plover Nov 12, 2019, 5:38 AM

#

Does different database use different language or same sql?

void otter Nov 12, 2019, 5:43 AM

#

It depends, most of today's databases today use sql but there are some that don't

hollow stream Nov 12, 2019, 5:46 AM

#

did you use raw SQL in your setup?

#

note different database have different features

#

although they're all SQL, they're slightly different SQL

void otter Nov 12, 2019, 5:48 AM

#

Yes i have used sql before

hollow stream Nov 12, 2019, 5:50 AM

#

no i asked @steel plover

steel plover Nov 12, 2019, 5:52 AM

#

I havent setup anything yet but planning to use posgres for database

hollow stream Nov 12, 2019, 5:53 AM

#

if you use raw SQL query just gotta make sure they run in a postgres dB
if you use some ORM libraries however, should be ok

steel plover Nov 12, 2019, 5:54 AM

#

I checked their web and looks like raw SQL

hollow stream Nov 12, 2019, 5:54 AM

#

🤔

#

just use whatever they use too

swift hamlet Nov 12, 2019, 12:09 PM

#

I’m using python3.6

I'm getting a “2006 MySQL server has gone away” operational error and would like some help
I have a discord app on a relational mysql database, using sqlalchemy and mysqlclient. The app works perfectly, but after about 8 hours I get the above exception, as I presume a connection in the pool has become stale. When I run the command a second time the app is fine again for another 8 hours

I have created the engine as follows within a singleton:

engine = create_engine('mysql+mysqldb://COC:COC@localhost/COC?charset=utf8',pool_use_lifo=True, pool_pre_ping=True)
db_session = scoped_session(sessionmaker(bind=engine))

My understanding is “pool_pre_ping” will retry and re-establish the connection, but perhaps its too late as part of a singleton

In my main file I have the following:

from config import db_session
session = db_session()
.... some code ....

This then takes a connection from the pool and makes it available for the code.
What is the best way to re-establish the connection?

torn sphinx Nov 12, 2019, 4:26 PM

#

ok guys

#

i have made a website with a postgresql database

#

but for the development

#

i want to turn it into production by

#

using SQLalchemy

icy shore Nov 12, 2019, 4:57 PM

#

can someone tell me why my for loop is not print out all the rows? I have 14 rows in my table and the print Total and printing each row shows on the screen but the for loop is not printing the 14 rows.

code:

with connection.cursor() as cursor:
sql_select_query = """SELECT * from tbl_user"""
cursor.execute(sql_select_query)
records = cursor.fetchall()
print("Total rows are: ", len(records))
print("Printing each row")
for row in records:
print("Id: ", row[0])
print("Name: ", row[1])
print("Email: ", row[2])
print("password: ", row[3])
print("\n")

torn sphinx Nov 12, 2019, 5:03 PM

#

ok i want to do something else

#

when you create a hobby-dev database with the heroku command,

#

how do you actually insert data inside of it

rain wagon Nov 12, 2019, 5:18 PM

#

INSERT INTO tablename(rowname1, rowname2) VALUES(dataforrow1, dataforrow2)

torn sphinx Nov 12, 2019, 5:38 PM

#

oh so its the same as inserting into the development database

#

ok

torn sphinx Nov 12, 2019, 6:27 PM

#

so i am using SQLAlchemy now

#

with this commanddb.session.add()

#

i can add data to my database

#

which command do i use to get data from the database

#

so i can print it out in my python file

#

or display on a webpage

tropic kindle Nov 12, 2019, 6:29 PM

#

you use the model you specified: MyModel.query.filter_by(columnName="name")

#

sqlalchemy is an ORM object-relational-mapper around your db data

torn sphinx Nov 12, 2019, 6:31 PM

#

hmm ok

#

so if my model is this

#

    __tablename__ = 'hele_boekverslag'
    id = db.Column(db.Integer, primary_key=True)
    sentence = db.Column(db.Text())

    def __int__(self, sentence):
        self.sentence = sentence```

#

i do:

#

Xala_Boek.query.filter_by(columnName='sentence')

#

right?

#

@tropic kindle

tropic kindle Nov 12, 2019, 6:33 PM

#

almost columnName was a placeholder since i didnt know your columns. Use the actual name of the column:

#

Xala_boek.query.filter_by(id=id)

#

or Xala_boek.query.filter_by(id=id, sentence=sentence)

torn sphinx Nov 12, 2019, 6:34 PM

#

i see

#

ok here we have a problem

#

def xala():
    db_data = Xala_Boek.query.filter_by(id='1')

    return render_template('Xala-Sembene_Ousmane.html', para=db_data)```

#

when i display the para variable on the webpage

#

i see this

#

SELECT hele_boekverslag.id AS hele_boekverslag_id, hele_boekverslag.sentence AS hele_boekverslag_sentence FROM hele_boekverslag WHERE hele_boekverslag.id = %(id_1)s

#

but when i print it in the python file, i get the actual sentence

#

do you know the cause @tropic kindle ?

tropic kindle Nov 12, 2019, 6:42 PM

#

sorry no clue

torn sphinx Nov 12, 2019, 6:44 PM

#

oh man

tropic kindle Nov 12, 2019, 6:46 PM

#

ah wait, maybe its because filter by returns a list of your model obejcts!

#

so it would be something like [Xala_Boek, Xala_Boek], which you can access again by their respective fields db_data[0].id, db_data[0].sentence

torn sphinx Nov 12, 2019, 6:49 PM

#

oh yeah it worked!

#

wouldn't have thought about that.

#

thx

torn sphinx Nov 12, 2019, 7:14 PM

#

ok just one more question @tropic kindle if you are willing to answer

#

how do i get the id of the last row in the table

tropic kindle Nov 12, 2019, 7:15 PM

#

i think when you do something like this

#

Xala_Boek.query().all()

#

this returns everthing in a list, which you can get the len of

torn sphinx Nov 12, 2019, 7:17 PM

#

did you mean table or list?

tropic kindle Nov 12, 2019, 7:19 PM

#

i think all() returns a list of all rows in the table as your model object

torn sphinx Nov 12, 2019, 7:23 PM

#

and then get the id from that?

#

this commandXala_Boek.query().all() gives me this errorTypeError: 'BaseQuery' object is not callable

#

I just want the largest id from the table

#

I want the largest id from the table rows

#

I have so little knowledge about querying data with SQL alchemy, so that's why i ask

torn sphinx Nov 12, 2019, 8:51 PM

#

Hello

hollow stream Nov 12, 2019, 9:31 PM

#

try .query.all()

torn sphinx Nov 12, 2019, 9:41 PM

#

What comes before the dot is my db.Model right?
@hollow stream

hollow stream Nov 12, 2019, 9:43 PM

#

um yea

torn sphinx Nov 12, 2019, 9:44 PM

#

Ok

#

man

#

it returned a list of these```<class 'main.Xala_Boek'>

#

but yeah now i have a solution

#

on how to get the id from that

torn sphinx Nov 13, 2019, 12:55 AM

#

help me understand licenses.. when do you actually need an enterprise version of a db that's open source..

torn sphinx Nov 13, 2019, 1:31 AM

#

when it's really honking big

#

and when you could save alot if time if it was managed

torn sphinx Nov 13, 2019, 1:47 AM

#

what if I'm deploying on my own datacenter

hollow stream Nov 13, 2019, 1:50 AM

#

when do you actually need an enterprise version of a db that's open source
you don't

torn sphinx Nov 13, 2019, 1:51 AM

#

really.. it's not like winrar that annoys you into getting enterprise version if you run it within a company?

hollow stream Nov 13, 2019, 1:53 AM

#

https://opensource.org/faq#commercial
is this your situation?

#

In particular, copyleft-style Open Source licenses require that, in at least some cases, when you distribute the software, you must do so under the same license you received it under.

torn sphinx Nov 13, 2019, 1:55 AM

#

ok.. so if I host a service on something open source running on my resources.. I'm selling the service, not the s/w

#

so that should be ok.. iiuc

toxic cedar Nov 13, 2019, 6:38 AM

#

What the heck NoSql

#

Is there a way i could get it working with django

#

Because aws is generously giving away 25Gigs free forever

hollow stream Nov 13, 2019, 6:43 AM

#

it's not relational anymore

#

mongo is nosql - document storage

#

you can call redis nosql - key value storage

void otter Nov 13, 2019, 7:01 AM

#

@toxic cedar
You could but you'd need an orm that supports nosql

toxic cedar Nov 13, 2019, 7:02 AM

#

So. I can't use the django orm. Would traditional queries work.

#

Because last time i checked it was all visual based .

void otter Nov 13, 2019, 7:10 AM

#

Nosql doesn't have queries like sql

#

That's why it's called nosql

#

https://medium.com/@gauravbaliyan/using-mongodb-nosql-with-django-without-changing-django-orm-cefee6f94d37

Medium

Use mongoDB(nosql) with django without changing django ORM

MongoDB is an open-source document oriented database that provides high performance, high availability, and automatic scaling.

hollow stream Nov 13, 2019, 7:15 AM

#

i take you mean "traditional" query as in relational data query

#

sql DB and nosql DB have different ways to store and query data

eager sparrow Nov 13, 2019, 11:11 AM

#

Hey I am in first year in CS, does anyone know a website where i can train sql requests ?

torn sphinx Nov 13, 2019, 11:19 AM

#

https://www.quora.com/What-is-the-best-website-to-practice-SQL-queries

What is the best website to practice SQL queries? - Quora

Well a very good question which almost every individual faces when he/she start learning SQL.SQL is easy to learn but the problem is how to practise.And yes,it’s really important for interviews. 1)Have basics cleared from HeadFirst SQL book. It is...

eager sparrow Nov 13, 2019, 11:31 AM

#

Thanks a lot

rugged sluice Nov 13, 2019, 12:11 PM

#

I can say: Youtube 🙂

subtle flax Nov 13, 2019, 12:19 PM

#

My redis pubsub channel always closes after ~5 minutes, message is Connection has been closed by server.
This only happens on 1 server and only inside docker. It does not happen anywhere else with the same redis server.
I am using aioredis library. How I could debug this?

#

I added a simple reconnect loop and it loses connection every 5 minutes

floral mason Nov 13, 2019, 2:16 PM

#

I imagine you need to have a heartbeat in order to maintain a long-lived connection. I’m afk, but what do the aioredis docs say about connection lifetimes with pub/sub?

#

Most tcp connections will eventually be closed by the server if it’s been a significant amount of time.

#

It may be that the connection timeout set for the specific server is lower than the others you’re testing against

subtle flax Nov 13, 2019, 2:56 PM

#

I'm testing against 1 redis server

#

problem occurs only on 1 machine and only when I use docker. Using same docker version on other machine does not reproduce issue

floral mason Nov 13, 2019, 5:52 PM

#

i don't know enough about docker networking voodoo to help you diagnose that, unfortunately. i don't believe this is an issue with redis itself or the aioredis client, if the issue is only observable when running in docker from a specific machine

#

i'd advise you to look at how docker is managing the networking layer on each machine and try to find differences there

subtle flax Nov 13, 2019, 5:58 PM

#

if it is redis server that closes connection, can this be logged somehow?

proven wagon Nov 13, 2019, 6:23 PM

#

in postgreSQL, what does the LIKE part do?

CREATE TABLE public.tablename
(
    LIKE public.other_tablename
  
    ExampleColumn int
)```

#

What i want to do at least, is have 2 tables, one for users, and one for messages.
When i fetch a user from that table all of the messages with the same author_id as the id of the user im trying to fetch are also fetched 🙂

lofty summit Nov 13, 2019, 6:33 PM

#

I would say you would need to use WHERE

proven wagon Nov 13, 2019, 6:34 PM

#

So there is no way i can make a column for users that relates to the messages table?

lofty summit Nov 13, 2019, 6:34 PM

#

smth like (pseudo-code)

SEARCH into messages WHERE "author"= author_id

#

You don't need to make a column in users

proven wagon Nov 13, 2019, 6:35 PM

#

well thats basicly what i want though 🙂

#

But ofc i can make 2 fetch queries, one from users and one from messages

lofty summit Nov 13, 2019, 6:39 PM

#

You will need to, I don't understand how can a column solve that?

proven wagon Nov 13, 2019, 6:39 PM

#

Neither do i 🙂

lofty summit Nov 13, 2019, 6:40 PM

#

I'm pretty sure you can't then 😉

floral mason Nov 13, 2019, 7:11 PM

#

@proven wagon what you want is a foreign key

#

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    created_on TIMESTAMPTZ DEFAULT current_timestamp
);

CREATE INDEX idx_username on users(username);

CREATE TABLE messages (
    id BIGSERIAL PRIMARY KEY,
    content TEXT,
    author_id BIGINT REFERENCES users(id) NOT NULL,
    created_on TIMESTAMPTZ DEFAULT current_timestamp
);

CREATE INDEX idx_author_id on messages(author_id);

#

then your query would be something like

#

SELECT
    messages.id as message_id,
    messages.content,
    messages.author_id,
    users.username as author_username,
    message.created_on
FROM messages JOIN users on users.id == messages.author_id
WHERE
    ...

#

and your WHERE clause can be the filters you want

#

i.e., a user.id

#

@subtle flax https://www.digitalocean.com/community/tutorials/how-to-find-redis-logs-on-ubuntu

proven wagon Nov 13, 2019, 9:19 PM

#

Man you took the time to write this, i feel bad that i already kind of solved it in a different worse way

floral mason Nov 13, 2019, 9:40 PM

#

haha, this is my bread-and-butter (plus IDE help)

hollow stream Nov 13, 2019, 9:53 PM

#

@subtle flax are you possibly reaching maxclients ?
or buffer size (how big is your pub-ed payload)

#

which part of your code fails on that (server closed connection) error ?

gritty pine Nov 14, 2019, 6:47 AM

#

hey i have a raw sql query like this

select date(CONVERT_TZ(create_date,'UTC','Australia/Sydney') ) as create_date

how do i replicate this in sqlalchemy? just started learning it (been doing raw sql up to now, but silly) but couldn't find much on the convert_tz aspect. The db is mysql

torn sphinx Nov 14, 2019, 7:03 AM

#

what's your table name

#

@gritty pine

gritty pine Nov 14, 2019, 7:04 AM

#

does it matter? i mean i know it's not written in that statement but i didn't show the whole thing

#

TB is a fine placeholder

torn sphinx Nov 14, 2019, 7:07 AM

#

TB?

#

wut

#

I just want to know why you want to do this in sqlalchemy..

#

when you can do

hollow stream Nov 14, 2019, 7:08 AM

#

you better off querying the date as is (assumed utc) as a source of truth from your dB,
and juggling with timezone using your python datetime

#

just a thought

torn sphinx Nov 14, 2019, 7:10 AM

#

df.createOrReplaceTempView("table_name")
sqlDF = spark.sql("sql statement here")

gritty pine Nov 14, 2019, 7:13 AM

#

why @hollow stream , when mysql provides nice functions to do that in place?

#

@torn sphinx i don't understand the confusion: there is a builtin mysql function i am trying to find out if sqlalchemy provides a way to use it without calling raw SQL, the table name is irrelevant

#

I find python TZ manipulation a pain, whereas the sql function is clear and is daylight savings-aware

hollow stream Nov 14, 2019, 7:19 AM

#

@gritty pine reason:

you preferably have as little entry to your database (its essentially a network dependency) as possible
your roundtrip towards dB should fetch truthful to the data stored, avoid confusion (which data is this coming from? why is my column showing 8am but my stack trace returns 5pm?)
if you need another timezone from that time data later, do you make another query? or simply another datetime call in python
you would usually move to ORM set up instead of writing raw query like this

torn sphinx Nov 14, 2019, 7:19 AM

#

so why don't you run this with sparksql

hollow stream Nov 14, 2019, 7:21 AM

#

of course yours favour simplicity, which varies on use cases :) i'm just sharing my own experience

gritty pine Nov 14, 2019, 7:23 AM

#

I understand those points, i just don't think they apply here. For example if i want to change the tz without going to db each time as you suggest, it's no different, just transforming from a different starting tz.

#

I am currently learning sqlalchemy specifically to move to orm model

hollow stream Nov 14, 2019, 7:24 AM

#

its no different in your use case

#

since you're in control of every flow in your own project right now

#

back to main topic, in sqlalchemy you should start using Data Model

#

which maps to your main tables

#

if you wanna run any query

gritty pine Nov 14, 2019, 7:29 AM

#

Yep, working on doing that

hollow stream Nov 14, 2019, 7:29 AM

#

connection = engine.connect()
res = connection.execute("query here")

#

given you have configured engine

gritty pine Nov 14, 2019, 7:30 AM

#

To clarify, i already have mysql connector stuff setup in python to run the code, I'm just moving to sql alchemy

#

From raw sql

#

If only pandas had a nice update on insert flag ><

hollow stream Nov 14, 2019, 7:31 AM

#

thats a vanilla example, quite simply execute any query you write
you can start looking into Session pool

#

which will provide a singleton for different parts of the app to connect (say your app is kept alive instead of 1 off run-exit, like a web server)

gritty pine Nov 14, 2019, 7:32 AM

#

Yep, already got the raw sql working :)

#

Cool, will do, ta

#

Well

#

That part less important, running on awa lambda

#

*aws

hollow stream Nov 14, 2019, 7:33 AM

#

keep in mind that with that set up you make a connection every time theres a request from client

gritty pine Nov 14, 2019, 7:34 AM

#

Yep

#

Not my first rodeo :)

hollow stream Nov 14, 2019, 7:34 AM

#

yeah so the db.engine approach is fine

#

but yeah sqlalchemy provides these Data Model to bind to your table,
with which i say querying data as is is the better approach

gritty pine Nov 14, 2019, 7:35 AM

#

Yeah i need that if only to make use of the batch save or whatever it's called

#

And also wanna avoid issues with escape chars/injection etc

hollow stream Nov 14, 2019, 7:38 AM

#

of course
sqlalchemy allows binding parameters like any ORM

#

take a glance at this
https://security.openstack.org/guidelines/dg_parameterize-database-queries.html

#

the doc looks kinda non mobile friendly so i didnt bother check it

cloud estuary Nov 14, 2019, 8:11 AM

#

How do i put this to .json?

{'469817660872589312':[1000, 45, 65, 1], '494150455149199360':[75, 3, 2, 0]}

subtle flax Nov 14, 2019, 8:14 AM

#

@hollow stream I doubt it. That machine is 3rd or 4th client of server, I connected other clients, they were not disconnected after 5 minutes.

I don't send any payload. Connection dies itself without doing anything.

I see disconnect messages in debug logs of aioredis

#

I haven't checked redis logs yet

torn sphinx Nov 14, 2019, 8:15 AM

#

@cloud estuary json.dumps(your_dictionary)

cloud estuary Nov 14, 2019, 8:16 AM

#

dump you mean

#

not dumps

torn sphinx Nov 14, 2019, 8:16 AM

#

no its dumps

cloud estuary Nov 14, 2019, 8:20 AM

#

when i do

import json
with open ('tst.json') as acc_json:
    read_content = json.load(acc_json)
a = {'469817660872589312':[1000, 45, 65, 1], '494150455149199360':[75, 3, 2, 0]}
json.dumps(a)```

#

tst.json remains the same

hollow stream Nov 14, 2019, 8:25 AM

#

@subtle flax connections will close on themselves (pub sub)
to elaborate, when you pub you initiate a connection to the server, send a payload, go idle
after a while server should terminate that connection

if nothing is causing exception/ blocking your operations by exception, then i dont think you should stress about it

#

i gotta check again tho but i remember reading about it when i implemented my ws based notifications

subtle flax Nov 14, 2019, 8:28 AM

#

My sub connection dies, it should not happen. And it doesn't happen on other hosts

hollow stream Nov 14, 2019, 8:28 AM

#

nvm i was wrong about the above

#

but sub is a push from server

#

the client is idle

#

Recent versions of Redis (3.2 or greater) have TCP keepalive (SO_KEEPALIVE socket option) enabled by default and set to about 300 seconds. This option is useful in order to detect dead peers (clients that cannot be reached even if they look connected).

#

maybe this is relevant?

subtle flax Nov 14, 2019, 8:32 AM

#

Maybe, I'll check

#

yes, I have it set to 300 seconds, I'll change it now and see what happens

hollow stream Nov 14, 2019, 8:42 AM

#

the thing is sub clients are idle

#

the server initiates, not the client, in a sub

#

i would check when that happens, whether or not your sub client still receive the pub-ed message

#

if not then id be worried and maybe think about forcing some sort of keep alive (have the server send some heartbeat to sub client in intervals, like someone else suggested)

subtle flax Nov 14, 2019, 8:46 AM

#

Setting tcp-keepalive to 3000 seconds didn't work, I've got a disconnect after 315 seconds.

I've checked, after disconnecting, sub messages are lost. I temporarily fixed it with reconnect loop.

Will look at debug logs now

hollow stream Nov 14, 2019, 8:48 AM

#

restart docker container?

#

sorry

#

rebuild image and restart container

#

reconnect loop works then,
didnt expect the library not have that built in

subtle flax Nov 14, 2019, 8:50 AM

#

I also have weird issue with docker now, daemon dies every few hours without a reason. Systemd does not restart it, as if someone did systemctl stop docker.service

hollow stream Nov 14, 2019, 8:50 AM

#

weird

#

id be very concerned if thats a prod VM

#

syslog or systemd logs give daemon exception?

subtle flax Nov 14, 2019, 8:51 AM

#

That's a laptop used as server

#

This is all I have

Nov 14 00:13:36 XJ9onArch dockerd-rootless.sh[26184]: time="2019-11-14T00:13:36.584167661+03:00" level=info msg="shim reaped" id=ac0ef86f9f53e3bca1ea35f89e10e2fd7c36a8b5bb5c18afb028c5994cee13e6
Nov 14 00:13:36 XJ9onArch dockerd-rootless.sh[26184]: time="2019-11-14T00:13:36.594823433+03:00" level=info msg="ignoring event" module=libcontainerd namespace=moby topic=/tasks/delete type="*events.TaskDelete"
Nov 14 00:13:36 XJ9onArch systemd[26019]: Stopping Docker Application Container Engine (Rootless)...
Nov 14 00:13:36 XJ9onArch systemd[26019]: docker.service: Succeeded.
Nov 14 00:13:36 XJ9onArch systemd[26019]: Stopped Docker Application Container Engine (Rootless).```

hollow stream Nov 14, 2019, 8:52 AM

#

sounds wild

#

hm,

#

phone out of battery atm, maybe i can look around when i get home, someone else might have an idea

subtle flax Nov 14, 2019, 8:54 AM

#

All I've got from debug redis logs: (I use 2 connections, pub and sub) 6535:M 14 Nov 08:53:09.613 - Client closed connection 6535:M 14 Nov 08:53:09.614 - Client closed connection

#

Redis says client closed connection, client says it was redis. So it must be something in-between that causes it?

#

Oh, I see now why docker daemon dies, systemd kills user units when user logs out

torn sphinx Nov 14, 2019, 8:59 AM

#

Laptop used as a server....

hollow stream Nov 14, 2019, 8:59 AM

#

spierman meme loool

torn sphinx Nov 14, 2019, 9:00 AM

#

You get free resources to run it on gcp etc... Aws gives credits to students.. there's no reason why he has to run anything from his laptop

subtle flax Nov 14, 2019, 9:01 AM

#

My friend's laptop*

hollow stream Nov 14, 2019, 9:02 AM

#

it might not be the laptop fault still

torn sphinx Nov 14, 2019, 9:03 AM

#

It could be.. docker crashes on my laptop when I try to do demos.. not enough resources

subtle flax Nov 14, 2019, 9:04 AM

#

It has enough resources, it doesn't crash

subtle flax Nov 14, 2019, 9:23 AM

#

Sending messages to that channel helped, disconnect happens ~300 seconds after last message

subtle flax Nov 14, 2019, 12:56 PM

#

I created another container for testing, disconnects are synchronized between them, using connection from 1 of them prevents it from failing in both containers

#

Running redis-cli in subscribe mode inside 2nd container keeps connection open in both containers. Is this aioredis bug then?

hollow stream Nov 14, 2019, 1:06 PM

#

sounds like it

#

i never ran into connection terminated by server like that

#

even from redis doc it says only cases such as maxclients or reach buffer limit ...

subtle flax Nov 14, 2019, 1:08 PM

#

Are there good aioredis alternatives?

pure scroll Nov 14, 2019, 1:08 PM

#

can you show your code for for pub/sub ?

hollow stream Nov 14, 2019, 1:09 PM

#

what does the redis package lack that you use from aioredis?

pure scroll Nov 14, 2019, 1:09 PM

#

aio i suppose ?

subtle flax Nov 14, 2019, 1:09 PM

#

https://github.com/IOMirea/jarpc/blob/dedb71c284429cc6944afff40b2f4acc62ceaa88/jarpc/connection.py#L114

#

I need an async library yes

pure scroll Nov 14, 2019, 1:10 PM

#

I mean how do you use it

#

ah wait

#

that is your code

#

thought it was part of aioredis

subtle flax Nov 14, 2019, 1:10 PM

#

I just create server, it's reproducible using example server for me

pure scroll Nov 14, 2019, 1:14 PM

#

I think you will have to write a code that does reconnection and/or sends keepalive msg

hollow stream Nov 14, 2019, 1:14 PM

#

you shouldn't have too

pure scroll Nov 14, 2019, 1:15 PM

#

thing is that you network partitions are natural and can happen to any system, so ability to restore connection is required anyways.
Plus in case of master-slave setup of redis you should be prepared for failover

#

in this case connection will also be terminated

subtle flax Nov 14, 2019, 1:15 PM

#

I already implemented recomnects, but it's not normal that it happens every 5 minutes

pure scroll Nov 14, 2019, 1:16 PM

#

you can set different ttl when establishing connection

#

you have two things:

redis server default keepalive
connection specific keepalive

subtle flax Nov 14, 2019, 1:17 PM

#

It's not redis server, I tried changing setting without any effect

pure scroll Nov 14, 2019, 1:17 PM

#

alternatively, you can always write issue ticket on github, aiolibs dudes are quick to respond usually

hollow stream Nov 14, 2019, 1:17 PM

#

what aioredis version is it?

#

is this 0.3

subtle flax Nov 14, 2019, 1:17 PM

#

Latest stable, 1.3

#

There is a similar issue in repo from 2018, I don't see any progress/investigation on it
https://github.com/aio-libs/aioredis/issues/409

hollow stream Nov 14, 2019, 1:27 PM

#

i'm having trouble understanding this bit:

        while not self._closed:
            ...
            try:
                self._sub = await pool.acquire()
                self._pub = await pool.acquire()
            except (aioredis.ConnectionClosedError, OSError):
                if not self._reconnect:
                    raise

#

what is calling close() on a Connection in your app?

subtle flax Nov 14, 2019, 1:32 PM

#

Nothing, connection dies itself, wdym?

I added this quick and dirty loop solution for now so it just works, not sure if it's fully correct, but I don't have time to write it properly yet

#

_pub and _sub connections are completely closed, it's not just channel unsubscribing, I have to recreate them

gritty pine Nov 15, 2019, 12:45 AM

#

hey is there a way to define a base in sqlalchemy where the columns/types are defined at run time via a dict?

#

basically i want to define the ORM model based on the columns given in a list, with appropriate types also given
I see this
https://stackoverflow.com/questions/973481/dynamic-table-creation-and-orm-mapping-in-sqlalchemy
but i'm kind of surprised you need to make a table first and them map it?

Stack Overflow

Dynamic Table Creation and ORM mapping in SqlAlchemy

I'm fairly new to using relational databases, so I prefer using a good ORM to simplify things. I spent time evaluating different Python ORMs and I think SQLAlchemy is what I need. However, I've c...

lost notch Nov 15, 2019, 1:50 AM

#

What is the best database to use for setting words to file locations. So if my program send the database the word work it would respond with a file location like D:/working.jep or if it sent the word dog it would respond with C:/Dogs/Dogpictures.jpg

vale mulch Nov 15, 2019, 2:41 AM

#

I have a duplicate entry for every single entry into my table, I don't have a primary key (I know) but all of the information is the same for every duplicate entry.

#

Is there any way I can remove all the duplicates?

compact warren Nov 15, 2019, 10:58 AM

#

Hello all

#

I have two queries I wrote using SQLAlchemy

#

But I want to merge to create a list of results

#

my two models are -

#

class Subscription(db.Model):
    """ Subscription Model for storing user subscriptions """
    __tablename__ = "subscription"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    user_group_id = db.Column(db.Integer, db.ForeignKey('user_group.id'), nullable=False)
    trial_period_start_date = db.Column(db.DateTime, nullable=True)
    trial_period_end_date = db.Column(db.DateTime, nullable=True)
    subscribe_after_trial = db.Column(db.Boolean, nullable=False)
    current_plan_id = db.Column(db.Integer, db.ForeignKey('plan.id'), nullable=False)
    offer_id = db.Column(db.Integer, db.ForeignKey('offer.id'), nullable=True)
    offer_start_date = db.Column(db.DateTime, nullable=True)
    offer_end_date = db.Column(db.DateTime, nullable=True)
    date_subscribed = db.Column(db.DateTime, nullable=False)
    valid_to = db.Column(db.DateTime, nullable=False)
    date_unsubscribed = db.Column(db.DateTime, nullable=True)
    insert_ts = db.Column(db.DateTime, nullable=False)


class Invoice(db.Model):
    """ Subscription Model for storing invoice of payments """
    __tablename__ = "invoice"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    customer_invoice_data = db.Column(db.Text, nullable=False)
    subscription_id = db.Column(db.Integer, db.ForeignKey('subscription.id'), nullable=False)
    plan_history_id = db.Column(db.Integer, db.ForeignKey('plan_history.id'), nullable=False)
    invoice_period_start_date = db.Column(db.DateTime, nullable=False)
    invoice_period_end_date = db.Column(db.DateTime, nullable=False)
    invoice_description =  db.Column(db.String(255), nullable=False)
    invoice_amount = db.Column(db.Numeric(8,2), nullable=False)
    invoice_created_ts = db.Column(db.DateTime, nullable=False)
    invoice_due_ts = db.Column(db.DateTime, nullable=False)
    invoice_paid_ts = db.Column(db.DateTime, nullable=True)

#

And my queries -

customer = get_a_user(account_id)

#get subscription
user_subscription = get_user_subscription(usr_group.id)

#get customer invoice 
invoice = Invoice.query.filter_by(subscription_id = user_subscription.id)

def get_a_user(id):
    return User.query.filter_by(id=id).first()

#

I want a response in this format -

#

response_object = {
        'first_name': customer.first_name,
        'last_name': customer.last_name,
        'user_name': customer.user_name,
        'email': customer.email,
        'user_group_type_name': usr_group_type.type_name,
        'payment_details':[
                'customer_invoice_data': invoice.customer_invoice_data,
                'date_subscribed': user_subscription.date_subscribed,
                'valid_to': user_subscription.valid_to,
                'invoice_period_start_date': invoice.invoice_period_start_date,
                'invoice_period_end_date': invoice.invoice_period_end_date,
                'invoice_description': invoice.invoice_description,
                'invoice_amount': invoice.invoice_amount,
                'invoice_created': invoice.invoice_created_ts,
                'invoice_due': invoice.invoice_due_ts,
                'invoice_paid': invoice.invoice_paid_ts
            ]
}

compact warren Nov 15, 2019, 12:07 PM

#

Umm, is this the right place to ask this question?

hollow stream Nov 15, 2019, 1:18 PM

#

user has many subscription?

#

you want subqueryload @compact warren
https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html#sqlalchemy.orm.subqueryload

proven wagon Nov 15, 2019, 3:40 PM

#

asyncpg.exceptions.InvalidTextRepresentationError: invalid input syntax for type json
DETAIL:  Token "'" is invalid.```

How do i store json data in my row? .-.

mystic surge Nov 15, 2019, 5:40 PM

#

is it okay to ask for general SQL help in here?

#

or is it just purely python related?

tawny sail Nov 15, 2019, 5:52 PM

#

Anything related to databases, so yes sql is included

mystic surge Nov 15, 2019, 6:04 PM

#

I've got a little issue updating TABLE_A based on TABLE_B

Table A

AnsID    | Value 
1     | Hello World
2     | Dog
3     | Games

**Table B **

AnsID_AUD | AnsID | Value
1     | 1     | Hello World
2     | 1    | Hello Planet
3     | 1     | Hello Earth
4    | 2     | Mouse
5     | 2    | Cat
6     | 3     | Games
7     | 3     | Shoes
8     | 3    | Socks
9     | 3     | Discord

I want TABLE_A to be

AnsID     | Value 
1     | Hello Earth
2     | Cat
3     | Socks

#

any advice?

rich trout Nov 15, 2019, 6:21 PM

#

Here's some example syntax you can modify to do what you want:

#



UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

mystic surge Nov 15, 2019, 8:38 PM

#

@rich trout , Thanks i think i understand

#

I'm getting syntax error 😦

#

syntax error from is not valid from this position

oblique crow Nov 15, 2019, 11:37 PM

#

got a potential db question in help channel 4

remote hatch Nov 16, 2019, 1:38 AM

#

whats the best way to make a sub table (similar to a dict if you can make one)

hollow stream Nov 16, 2019, 3:44 AM

#

@proven wagon is your json badly formatted?

#

@remote hatch sub table?

#

you might be thinking of document storage, where the data is kept in a similar manner with dict and json

toxic rune Nov 16, 2019, 9:00 AM

#

I have a function that does a query (with multiple joins), is it possible to view it detailed on EXPLAIN ANALYZE rather than just "Function Scan"? (PostgreSQL)

ionic pecan Nov 16, 2019, 10:26 AM

#

EXPLAIN ANALYZE VERBOSE?

lofty summit Nov 16, 2019, 11:50 AM

#

How to insert values with their default value using psycopg2?

remote hatch Nov 16, 2019, 12:11 PM

#

yes @hollow stream

remote hatch Nov 16, 2019, 12:27 PM

#

i also want to table guild_id then have a subtable for that, then have sub tables in some of those subtables, or would this be better done with just columns

proven wagon Nov 16, 2019, 2:04 PM

#

@hollow stream I guess what i mean, is how i convert my dict to whatever asyncpg accepts

I see that using json.dumps was what i needed 🙂 sorry for the ping my guy

timid quartz Nov 16, 2019, 2:32 PM

#

Hi

#

def generate_server_seed():
    possible_chars = string.ascii_uppercase + string.digits + string.ascii_lowercase + string.punctuation
    last_pass = ''
    #loop
    for x in range(0, 1000, 1):
        password1 = ''.join(random.choice(possible_chars) for x in range(10000))
        hashpass = hashlib.sha256(password.encode('utf-8')).hexdigest()
        password1 = hashpass + last_pass
        newpass = hashlib.sha256(password.encode('utf-8')).hexdigest()
        last_pass = newpass
    return newpass

#main
previous_hash = generate_server_seed()

conn = mysql.connector.connect(host, user, password, base)
cur = conn.cursor()
conn.autocommit(False)

for i in range(0, hashchain_length, 1):
    newhash = hashlib.sha256(previous_hash.encode('utf-8')).hexdigest()
    previoushash = newhash

    sql = "INSERT INTO HASHCHAIN VALUES (%s, %s, 0)"
    try:
        cur.execute(sql, (i, newhash))
    except MySQLdb.Error as err:
        print('*** MySQL Error : %s' % err)
        print('-> During execution of : %s' % cur._last_executed)
cur.close()
conn.commit()
conn.close

#

im following a guide word for word but i keep running to this error:

#

  File "c:\Users\Sword\Documents\chestgames\games.py", line 31, in <module>
    conn = mysql.connector.connect(host, user, password, base)
  File "C:\Users\Sword\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\__init__.py", line 173, in connect
    return MySQLConnection(*args, **kwargs)
  File "C:\Users\Sword\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\connection.py", line 64, in __init__
    super(MySQLConnection, self).__init__(*args, **kwargs)
TypeError: __init__() takes 1 positional argument but 5 were given

[Done] exited with code=1 in 35.198 seconds```

lofty summit Nov 16, 2019, 3:05 PM

#

Hey again, how to "link" two tables? Like I've one table "product", the other is "sale", and I want to need to add a query to "sale" when I change a product number, to ensure data integrity.

oblique crow Nov 16, 2019, 5:20 PM

#

got a database question in #help-falafel if you wanna help

winter osprey Nov 16, 2019, 5:36 PM

#

hmmm

#

self.client.c.execute(f'DELETE FROM blacklist WHERE guildid={ctx.guild.id} AND command={name}') sqlite3.OperationalError: no such column: time

#

📎 unknown.png

#

am i blind or thats not how it works

#

or i need to specify that any channelid

teal rampart Nov 16, 2019, 8:34 PM

#

I'm using sqlalchemy and I get this error KeyError: 'greeting' when I try to do the following:

        welcome_message = ['hello', 'there']
        guild = self.db.get(self.db.Guild.guild_id == ctx.message.guild.id)
        already_exist = guild.greeting
        guild.greeting = ' '.join(welcome_message)
        self.session.commit()
        if already_exist:
            ...
        else:
            ...

lofty summit Nov 16, 2019, 8:36 PM

#

already_exist = guild.greeting

toxic rune Nov 16, 2019, 8:36 PM

#

@ionic pecan

                                                                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Function Scan on game.get_hero  (cost=0.25..10.25 rows=1000 width=352) (actual time=11.516..11.516 rows=1 loops=1)
   Output: name, story, grade, face_id, attribute_id, attribute, role_id, role, zodiac_id, zodiac, bra, "int", fai, des, devotion_name, devotion_slot, devotion_value, self_devotion_name, self_devotion_value, exclusive_equipment_name
   Function Call: game.get_hero('Yufine'::text, '2'::smallint)
 Planning time: 0.048 ms
 Execution time: 12.267 ms
(5 rows)

Not much detail still. I can execute the query outside function (but it's really long, can be seen on https://pastebin.com/raw/NkAYTXBy here, which im quite worried about the length regardless of whether it works and it's fast or not)

teal rampart Nov 16, 2019, 8:37 PM

#

@lofty summit How I can fix?

lofty summit Nov 16, 2019, 8:39 PM

#

Idk, never used sqlalchemy, but I can tell you guild.greeting does not exist

teal rampart Nov 16, 2019, 8:43 PM

#

I have it in my database

#

And its value is none

lofty summit Nov 16, 2019, 8:45 PM

#

@teal rampart give your whole traceback then

teal rampart Nov 16, 2019, 8:52 PM

#

I don't have a traceback XD

winter osprey Nov 16, 2019, 9:11 PM

#

Then create it

#

torn sphinx Nov 16, 2019, 9:24 PM

#

Using MongoEngine, how can I create a DateTimeField with the default value of a date, not a unix timestamp?

#

datetime.datetime.utcnow

automatically gets converted to a timestamp

lofty summit Nov 16, 2019, 9:28 PM

#

@torn sphinx what about utcnow().date()?

torn sphinx Nov 16, 2019, 9:29 PM

#

Yeah, same.. :/

#

At frontend I tried to counter this by implementing momentjs

#

All works fine except the time - always show 12:00

#

Using VueJS on frontend

#

Here's how the field looks like now

created_at = DateTimeField(required=True, default=datetime.utcnow().date())

#

Wouldn't mind having the timestamp if it showed correctly, not 12:00 always

#

Here's it getting formatted at the frontend

    data() {
        return {
            situationCreatedAt_Formatted: moment(this.situationCreatedAt).format("YYYY-MM-DD h:mm:ss")
        }
    },

#

Date shows up fine, time doesn't :/

#

Here's how it shows up on the frontend

📎 unknown.png

#

Solved it, nevermind 😄

#

Just needed a break

#

Was passing situationCreatedAt, which is an object, not the actual timestamp, which is situationCreatedAt.$date

old scarab Nov 17, 2019, 2:30 AM

#

(please tag when answering)

I'm trying to insert 212723958313779201 as an Int64 value into a mongodb collection but mongodb itself keeps changing the number when I enter it

can anyone explain why & let me know how to fix it?
it changes 2127239583137792__01__ -> 2127239583137792__00__ and it also changes other numbers after the last 2 if I type other numbers

I'm using mongodb compass community

hollow stream Nov 17, 2019, 4:08 AM

#

could you share the code that inserts @old scarab

old scarab Nov 17, 2019, 4:09 AM

#

#where member and member2 are discord.Member
self.dbConnection.insertPair({'s1': member.id, 's2': member2.id})

#

one get-around is to simply store the ids as strings instead

#

but I'm wondering why ints won't work

#

@hollow stream

#

the code doesn't matter too much here

#

because it really has to do with the database

#databases

mysql -u root -p