cursive ibex Mar 28, 2020, 9:23 PM

#

    async def add_channel(self, uid, name, description, category):
        if not self.ascii_checks([name, description, category]):
            return 'Bad arguments.'

        await self.db.execute(
            "SELECT * FROM users_channels WHERE user_id=%s", (uid,))
        if await self.db.fetchone() is not None:
            await self.db.execute(
                "SELECT channel_name FROM channels WHERE channel_id=%s", (self.db.fetchone(),))
            return await self.db.fetchone()[0]

        await self.db.execute(
            "INSERT INTO users (user_id) "
            "VALUES (%s)", (uid, ))
        await self.db.execute(
            "INSERT INTO channels (channel_id, channel_name, description, category) "
            "VALUES (%s, %s, %s)", (1, name, description, category))
        await self.db.execute("SELECT LAST_INSERT_ID();")
        channel_id = await self.db.fetchone()

        await self.db.execute(
            "INSERT INTO users_channels (user_id, channel_id) "
            "VALUES (%s, %s)", (uid, channel_id))
        await self.conn.commit()
        return 'Successful'

I've got this working. The problem is I'm using LAST_INSERT_ID(), would this work async? in a discord bot, if two people execute this command at once, would the ids glitch out?

clever topaz Mar 28, 2020, 9:24 PM

#

@midnight verge Actually it appears that it doesn't really matter (and in fact is necessary to have multiple sessions when multithreaded/processing): https://stackoverflow.com/a/49760990/12829466

#

@midnight verge TBH I think that this is a little insignificant and whichever method you use will work just as well (if you're worried about performance). What I would do is use the easiest/most appropriate method based on the use-case. And worst case, it shouldn't be too hard to change. Getting a second opinion won't hurt if you still are concerned, though.

#

@midnight verge Actually another thing I'd suggest is that you look at some bigger open-source projects using sqlalchemy and see how they do it. That's always a good way of ensuring your method is the correct method. I haven't using sqlalchemy enough to give a better answer.

cursive ibex Mar 28, 2020, 9:29 PM

#

TypeError: not all arguments converted during string formatting

@clever topaz you know why this occurs?

clever topaz Mar 28, 2020, 9:32 PM

#

@cursive ibex As for the last_insert_id, then I'm not sure. With regards to your error, which line is it occuring on?

cursive ibex Mar 28, 2020, 9:32 PM

#

I can't get to know..

#

It's running from another file

#

Unless I debug

📎 unknown.png

clever topaz Mar 28, 2020, 9:33 PM

#

I think that the arguments passed to the statement are not supposed to be in a tuple.

cursive ibex Mar 28, 2020, 9:33 PM

#

Lemme debug it, I'll get back to you in a min

#

        await self.db.execute(
            "INSERT INTO channels (channel_id, channel_name, description, category) "
            "VALUES (%s, %s, %s)", (str(1), name, description, category))

#

It happens here

#

Oh, I didn't add an extra %s

clever topaz Mar 28, 2020, 9:39 PM

#

@midnight verge https://github.com/sqlalchemy/sqlalchemy/blob/master/examples/association/basic_association.py - One of sqlalchemys examples - they use a single session that isn't connected to the Order model.

cursive ibex Mar 28, 2020, 9:41 PM

#

And now I get INCORRECT INTEGER VALUE

clever topaz Mar 28, 2020, 9:42 PM

#

@cursive ibex Perhaps it isn't supposed to be str(1)?

cursive ibex Mar 28, 2020, 9:43 PM

#

It's an INT AUTO_INCREMENT field

#

And a MySQL error, not a python error

midnight verge Mar 28, 2020, 9:43 PM

#

Actually another thing I'd suggest is that you look at some bigger open-source projects using sqlalchemy and see how they do it. That's always a good way of ensuring your method is the correct method. I haven't using sqlalchemy enough to give a better answer.
@clever topaz yeah that's what I am going to do

One of sqlalchemys examples - they use a single session that isn't connected to the Order model.
Indeed but I am also afraid of falling into an anemic domain model antipattern (https://www.martinfowler.com/bliki/AnemicDomainModel.html), that's why I have a such hard time to decide 🤔 Still going to document myself, thanks for your insights

clever topaz Mar 28, 2020, 9:51 PM

#

@midnight verge Out of interest - why doesn't that apply with the Django method of selection via the model?

midnight verge Mar 28, 2020, 9:52 PM

#

@clever topaz because that's the “Django way” to do things (put logic in models), the framework has been designed that way

clever topaz Mar 28, 2020, 9:53 PM

#

I see. Is it anemic or not (I'm not familiar with the concept)? Either way, surely there was a reason for Django deciding to do so in the way they did?

midnight verge Mar 28, 2020, 9:55 PM

#

Django models are not anemic at all, quite the contrary actually.

Either way, surely there was a reason for Django deciding to do so in the way they did?
Opinionated tradeoff decision, all frameworks have theirs

clever topaz Mar 28, 2020, 9:55 PM

#

@cursive ibex Not sure why, I'd have to see the complete code and error. Also, I tried cursor.lastrowid (albeit with aiosqlite) and it worked perfectly.

#

@midnight verge Interesting! This gets into the lower level domain of analysing models which I don't venture into - being a hobbyist I'm content with using whatever is the default (as long as it's functional for my purpose). Any chance you could update me when you make your decision?

#

Also, any idea of how many rows you envisage working with?

midnight verge Mar 28, 2020, 10:01 PM

#

@clever topaz yeah, well that's basically the old-and-neverending ORM/ActiveRecord vs DataMapper holy war.

being a hobbyist I'm content with using whatever is the default (as long as it's functional for my purpose)
Probably the best decision

Any chance you could update me when you make your decision?
I am leaning towards separating the logic from models right now, but I still feel like I need to document myself more 😂

Also, any idea of how many rows you envisage working with?
Quite few, but it's more about me wanting to experiment and learn to do things “the right way” than actual needs (honestly in the current state I could just stick with a brainless datastore that I would query and iterate over manually, but that's not what I want to achieve)

#

Let's say millions being very optimistic

clever topaz Mar 28, 2020, 10:03 PM

#

Let's say millions being very optimistic
@midnight verge I see - efficiency matters at that scale! Mine are in the 10s of thousands range. What do you mean by document? Documenting your thought-processes, ect.?

midnight verge Mar 28, 2020, 10:04 PM

#

Reading stuff, basically, I may have not used the right idiomatic word 😅

clever topaz Mar 28, 2020, 10:06 PM

#

@midnight verge Ah, got it. Well all the best with your research/project!

gentle sparrow Mar 28, 2020, 10:36 PM

#

Anyone in here familiar with flutter and Django

#

Both flutter and Django

#

Or even just Django honestly

midnight verge Mar 28, 2020, 10:46 PM

#

@gentle sparrow don't ask to ask, just ask

gentle sparrow Mar 28, 2020, 10:48 PM

#

I'm looking for a guider but

#

Sure

#

Anyone know the best way the make a loginpage database for a social media with Django

#

Mobile

torn sphinx Mar 29, 2020, 4:52 AM

#

What is the best field (SQLAlchemy, Flask, Postgres/SQLite) to represent a list of objects that will be instances of another model, in a One to Many relationship with the "origin" class in this case. That class will involve a method that is, "Create an instance of the thing that you can have several of, and attach it to you in the list of the Many had by the One.

#

example: A creator creates many posts.

#

Thats a simplification of what im doing but it works

pure cypress Mar 29, 2020, 4:57 AM

#

At the database level, a table should not have such list as a field.

#

It'd just be a foreign key and that's it. You could do a simple select query to get the posts of a "creator".

#

If you're using an orm then it may have fields you can access that represent the one to many relationship

#

I know django does this

acoustic silo Mar 29, 2020, 5:01 AM

#

sqlalchemy will also do this

torn sphinx Mar 29, 2020, 5:01 AM

#

I think that i do understand hwat you mean, im familiar with the one django uses. I wish flask had something as good as that but im using flask nevertheless for other reasons

#

yeah

#

so its a foreign key field.

#

i think that there are solutions similar to djangos but it takes more work to learn them

#

im getting there

#

So, okay...

#

In flask-sqlalchemy I am already doing some one to one relationships and they are specified in the orm there as this

#

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

#

Those dont seem to be fields tho

#

I dont actually have a field for these things i dont think

#

https://paste.pythondiscord.com/ijebunekon.py

#

what i amtrying to do now is Game Master to Campaigns, but I may have not even done Users to Players/GMs righjt yet

#

ahh nvm

#

the foreign keys are in the models beneath user

#

me dumb

calm charm Mar 29, 2020, 6:09 AM

#

Hello

#

is there a way in sqlite to make it so that if a column has a value of 1, after 5 minutes, it changes back to 0?

#

ping me if u have an answer

torn sphinx Mar 29, 2020, 8:18 AM

#

Guys

#

I need an advice

rain wagon Mar 29, 2020, 9:04 AM

#

@calm charm Give it a timestamp, next time you query it, calculate the time delta and if it has been longer than 5 minutes, then set it to 0 again.

#

But no, there is no automatic way

solid void Mar 29, 2020, 11:05 AM

#

@torn sphinx just ask

torn sphinx Mar 29, 2020, 11:26 AM

#

Is there a quick way to create tables in mysql using a spreadsheet? @solid void

rotund badge Mar 29, 2020, 3:45 PM

#

My friend is reccomending I use a DML in my project, but I've generally been advised that it actually adds more unnecessary complexity.
What do you guys think?
(The project is a Discord bot that interfaces with a SQL database pretty often)

solid void Mar 29, 2020, 4:07 PM

#

@torn sphinx you can certainly export the spreadsheet to csv and load it, i'm not a mysql expert, i know postgres can do it, but i'm sure there is a way to do the same with mysql

rain wagon Mar 29, 2020, 4:53 PM

#

@rotund badge DML is a subset of SQL

#

so not sure what your friend is on about

#

It means Data Manipulation Language and is the part that modifies data

#

So, INSERT, UPDATE, DELETE etc

rotund badge Mar 29, 2020, 4:56 PM

#

Like, that I should make classes that have functions that do INSERT/UPDATE/DELETE for me, given certain inputs like the stuff to insert and the table name. Rather than just write a SQL statement for each part in my code that I want to INSERT/UPDATE/DELETE. @rain wagon

rain wagon Mar 29, 2020, 4:56 PM

#

That is an ORM

#

you can use SQLALchemy for that

#

And there are many valid approaches

#

writing the sql code into each class yourself is just as valid

#

However, the abstraction of a orm can help

#

BUt SQL itself consists out of DDL, DML and DCL. Each do their part. DML modifies and handles data, DDL creates tables and columns and DCL controls the access to it

torn sphinx Mar 29, 2020, 7:35 PM

#

For mysql in python, how can I fetch a certian data and assign it to a variable ???

torn sphinx Mar 29, 2020, 8:15 PM

#

nm, i answered my own question xD

raw geyser Mar 29, 2020, 8:21 PM

#

hey all - got a quick q... is there any tools out there to house a .db file and have a web based GUI all in a one stop?

#

im imaging something like postgresql would do the trick... im pretty new to the SQL world so trying to discover the best method of reading this .db and creating gui tools.

#

even better if the tool synergizes well with VSCode Editor

torn sphinx Mar 29, 2020, 8:41 PM

#

How can i add already defined variables in my sql statment

sql = ("UPDATE servers SET prefix=%(p) WHERE id=%(ID)")```
I tried this method but I keep getting errors

solid void Mar 29, 2020, 9:46 PM

#

you can usually pass the parameters to the function that excute the query, like connection.execute('update servers set prefix = %s where id = %s', (prefix, id))

#

there are various possible ways to declare the variables, some db connectors support different ones

torn sphinx Mar 29, 2020, 9:46 PM

#

ooh lol ok. let me try that. thank you

solid void Mar 29, 2020, 9:48 PM

#

https://www.python.org/dev/peps/pep-0249/#paramstyle gives more info about the syntax that a db-api connector can support

Python.org

PEP 249 -- Python Database API Specification v2.0

The official home of the Python Programming Language

#

named and pyformat are nicest imho, as they allow naming things and passing them as a dictionary

torn sphinx Mar 29, 2020, 9:49 PM

#

thanks !

torn sphinx Mar 29, 2020, 10:47 PM

#

Ran that and ran my own attempts and still no.

#

my issue is query won't work with one %s parameter

somber cargo Mar 30, 2020, 3:55 AM

#

Ho do we restore Data from Dump Files ?

Now basically I already have created the database with the tables and its data . But I want to replace those data from the dump file for example the dumpfile is

dump-test-databasetest.sql.gz.

I am using maria DB and beaver tool , OS : Ubuntu , is there a command to directly restore the data from the database from the dump file ?

Thank you

cerulean pendant Mar 30, 2020, 4:07 AM

#

how did you dump?

#

usually you just use your phpMyAdmin database manager to load it

somber cargo Mar 30, 2020, 4:08 AM

#

is there like a command to do it ?

cerulean pendant Mar 30, 2020, 4:09 AM

#

probably - I never used it though - in the rare event that I had to restore the database, we used the graphical interface

#

did you use a command to generate the dump?

rain wagon Mar 30, 2020, 4:38 AM

#

mysql -u username -p dbname < data-dump.sql

#

-p is prompt for password

#

db must exist

#

@somber cargo

somber cargo Mar 30, 2020, 4:42 AM

#

Access denied for user 'username'@'localhost' (using password: YES)

rain wagon Mar 30, 2020, 4:43 AM

#

is username a mariadb user for your db?

#

don't just blindly copy commands >_<

somber cargo Mar 30, 2020, 4:44 AM

#

root is my username

rain wagon Mar 30, 2020, 4:44 AM

#

uff

#

Always make a separate user for your databases, but for now use root then

#

Did you run mysql_secure_ installation after installing mariadb?

somber cargo Mar 30, 2020, 4:45 AM

#

I am receiving error ERROR 1062 (23000) at line 530: Duplicate entry '20190617051131-apply-schema.js' for key 'PRIMARY'

rain wagon Mar 30, 2020, 4:46 AM

#

Well, then the data export is apparently faulty

#

See if you can fix it, but that is something you have to do yourself

raw geyser Mar 30, 2020, 5:06 AM

#

https://www.python.org/dev/peps/pep-0249/#paramstyle gives more info about the syntax that a db-api connector can support
@solid void ty

Python.org

PEP 249 -- Python Database API Specification v2.0

The official home of the Python Programming Language

rain wagon Mar 30, 2020, 8:24 AM

#

#tools-and-devops

#

Nothing to do with databases

reef hawk Mar 30, 2020, 10:08 AM

#

sorry u mentioned binlog, but I have no clue what youre talking about; I will do more research later on but just wondering if it’d work in my case: I just want to automatically backup the database every 30 minutes or so, and keep a copy of it

#

store it for a week or so and then delete the oldest one

rain wagon Mar 30, 2020, 10:32 AM

#

@reef hawk The binlog keeps all changes to the database

#

Google about it, there are lots of articles handling mysql backups and there are multiple ways of doing it

reef hawk Mar 30, 2020, 10:47 AM

#

👌👌tyvm

somber cargo Mar 30, 2020, 12:12 PM

#

Hi , what type of error should I throw if user try to update data which is for example the user can only update data with status equal to 1 and if he try to update a data which status is equal to zero , what type of error should I throw ? is it bad requesr (500) or is it forbidden error ? thanks

rain wagon Mar 30, 2020, 12:53 PM

#

This isn't really a case to throw a server error of any kind

#

@somber cargo

carmine totem Mar 30, 2020, 2:35 PM

#

hey

#

i have bug while creating db in mysql

#

📎 unknown.png

#

what can i do?

rain wagon Mar 30, 2020, 3:24 PM

#

It says right there what you should do @carmine totem

sour plover Mar 30, 2020, 3:57 PM

#

Hello, I have a curios question connected with dbs and Django orm.

from django.db import models
class Family(models.Model):
  pass
class Parent(models.Model):
  name = models.TextField()
  family = models.ForeignKey(Family, related_name="members")
class Boy(Parent):
  favourite_car = models.CharField(max_length=10)
class Girl(Parent):
  favourite_doll = models.CharField(max_length=10)

actually I need serializer to present Family and it's members

class FamilySerializer(serializers.ModelSerializer):
   members = ... ??

I've got an idea how to serialize separetly.. to make members = serializers.SerializerMethodField() but I cannot find out how to separate Boy from Girl
Actually as a result I want to receive something like:

# family object
{
  members: [
    { # a boy
      name: "Karl",
      favourite_car: "Dodge"
    },
    { # a girl
      name: "Ann",
      favourite_doll: "Barbie"
    }
  ]
}

cerulean pendant Mar 30, 2020, 4:42 PM

#

it seems easy to put up a test script for that

lime bough Mar 30, 2020, 5:33 PM

#

Does anyone know a good resource on learning how to get data from a database into your program?

clever topaz Mar 30, 2020, 5:35 PM

#

@lime bough It's very easy and there are plenty of resources. What database?

lime bough Mar 30, 2020, 5:35 PM

#

mysql

#

or sql

clever topaz Mar 30, 2020, 5:36 PM

#

Pre-existing database?

lime bough Mar 30, 2020, 5:36 PM

#

I think I'm going to be creating my own

cerulean pendant Mar 30, 2020, 5:36 PM

#

the question is too broad, you should narrow it

lime bough Mar 30, 2020, 5:36 PM

#

Sorry I am pretty new to this

clever topaz Mar 30, 2020, 5:37 PM

#

https://stackoverflow.com/questions/372885/how-do-i-connect-to-a-mysql-database-in-python

lime bough Mar 30, 2020, 5:38 PM

#

Okay, so I have gotten that far, what about making calls to the database?

#

That part, I'm having trouble understanding :/

cerulean pendant Mar 30, 2020, 5:38 PM

#

ok so you know the sql language right?

lime bough Mar 30, 2020, 5:38 PM

#

Yes

cerulean pendant Mar 30, 2020, 5:39 PM

#

after your code connects to the database, you use cursor.execute() commands to execute sql code in the database

#

that's how your database interaction gets done

lime bough Mar 30, 2020, 5:39 PM

#

okay, so I basically write sql query into a a python method, how does it retreive and store the infor that I want?

cerulean pendant Mar 30, 2020, 5:40 PM

#

if the sql command is a SELECT , you can use cursor.fetchone() and/or cursor.fetchall() methods to get the results

#

fetchall() will return a list with all the resulting rows, fetchone will return a single row if you need to go one by one

lime bough Mar 30, 2020, 5:40 PM

#

ohh

#

Thank you so much! I appreciate your help

midnight sigil Mar 30, 2020, 10:02 PM

#

if the sql command is a SELECT , you can use cursor.fetchone() and/or cursor.fetchall() methods to get the results
@cerulean pendant nice to read that

carmine totem Mar 31, 2020, 9:54 AM

#

hey what is the best way to connect python code to msql DB ?

#

for login system

carmine edge Mar 31, 2020, 10:35 AM

#

Best way to connect a SQL db with python. Proves very helpful for analytics as we can apply queries and pull data directly from a db.

https://mostlypy.com/python/connect-to-a-mysql-database-in-python/

Mostly Python

admin

Connect to a MySQL database in Python - Mostly Python

How to connect to a MySQL database (or any SQL database) in python with the help of PyMySQL. Also learn about why would you need a connection to a database.

fair crescent Mar 31, 2020, 10:52 AM

#

Can someone help me with a issue, I'm trying to find dates less than a specific one using this SELECT tv.statName FROM ca_tvstation tv INNER JOIN ca_broadcast ar ON ar.statID = tv.statID JOIN CA_Programme ON CA_Programme.progID = ar.progID JOIN CA_ActorRole ON CA_Programme.progID = CA_ActorRole.progID JOIN CA_Actor ON CA_ActorRole.ActorID = CA_Actor.ActorID where CA_Actor.DOB < '1980-01-01';

#

but it keeps giving me an error

#

I think the issue is the last line, how do you find dates less than a specific one in SQL?

rain wagon Mar 31, 2020, 10:53 AM

#

Is DOB defined as datetime?

#

or date

fair crescent Mar 31, 2020, 10:54 AM

#

date

rain wagon Mar 31, 2020, 10:55 AM

#

Then this should work. What is the error?

fair crescent Mar 31, 2020, 10:55 AM

#

ORA-01861: literal does not match format string

rain wagon Mar 31, 2020, 10:56 AM

#

freaking oracle at it again huh?

#

Try TO_DATE('1980.01.01')

fair crescent Mar 31, 2020, 10:57 AM

#

Like this ? where CA_Actor.DOB < TO_DATE('1980.01.01');?

rain wagon Mar 31, 2020, 10:57 AM

#

yeah

fair crescent Mar 31, 2020, 10:58 AM

#

yeah I still get the same error

#

bummer

#

maybe it's the join?

rain wagon Mar 31, 2020, 10:59 AM

#

no,it's the date

fair crescent Mar 31, 2020, 10:59 AM

#

should I post my tables?

#

alright narrows it down then

rain wagon Mar 31, 2020, 11:00 AM

#

ok, Oracle seems to have this NLS_DATE_FORMAT which is dtermined by NLS_TERRITORY

#

So, basically, it does not like your formatting

#

Try DD-MM-YYYY

#

https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams122.htm#REFRN10119

#

https://stackoverflow.com/questions/22542882/sql-error-ora-01861-literal-does-not-match-format-string-01861/22543299

Stack Overflow

SQL Error: ORA-01861: literal does not match format string 01861

I am trying to insert data into an existing table and keep receiving an error.

INSERT INTO Patient
(
PatientNo,
PatientFirstName,
PatientLastName,
PatientStreetAddress,
PatientTown,

fair crescent Mar 31, 2020, 11:02 AM

#

I put this in WHERE CA_Actor.DOB < TO_DATE('01.01.1980'); and it said not a valid month, getting somewhere now

rain wagon Mar 31, 2020, 11:02 AM

#

SELECT value
FROM   nls_session_parameters
WHERE  parameter = 'NLS_DATE_FORMAT'```

#

Running this should give you the format Oracle expects from you

fair crescent Mar 31, 2020, 11:04 AM

#

DD-MON-RR

#

kinda confused, so it's day month year?

#

but three 3 digits for month?

rain wagon Mar 31, 2020, 11:04 AM

#

No, abbrevations

#

FEB, DEC

#

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';``` Will give you the format you actually wanted

#

I think this needs to be executed every time you connect

#

however, I am not sure if this is db wide

#

not using oracle here

#

so, make sure to save the old format in case it breaks something

fair crescent Mar 31, 2020, 11:06 AM

#

Will do, I think I got it working though just using the abbrevations

#

replacing MON with FEB, JAN etc

#

thanks for the help was stuck on that for a hour

rain wagon Mar 31, 2020, 11:07 AM

#

no problemo

fair crescent Mar 31, 2020, 11:21 AM

#

I have another question if it's alright, I'm trying to get the genre of a program with the lowest ID and show the actors in the program, I'm just wondering if I'm close using this SELECT CA_Programme.Genre,CA_Actor.ActorName FROM CA_Programme JOIN CA_ActorRole ON CA_Programme.progID = CA_ActorRole.progID JOIN CA_Actor ON CA_ActorRole.ActorID = CA_Actor.ActorID WHERE progID = (SELECT MIN(progID) FROM CA_Programme);

#

I get this error ORA-00918: column ambiguously defined

thick thicket Mar 31, 2020, 11:36 AM

#

maybe progID exists in more than 1 table

#

set tablename.progID

#

after the "WHERE

fair crescent Mar 31, 2020, 11:39 AM

#

Yeah it exists in two different tables, I don't know you mean by set tablename.progID

#

Something like this?

#

CA_Programme)
set tble.progID;```

thick thicket Mar 31, 2020, 11:39 AM

#

yes

#

no

#

like this:

#

SELECT CA_Programme.Genre,CA_Actor.ActorName
FROM CA_Programme
JOIN CA_ActorRole
ON CA_Programme.progID = CA_ActorRole.progID
JOIN CA_Actor
ON CA_ActorRole.ActorID = CA_Actor.ActorID
WHERE tablename.progID = (SELECT MIN(progID) FROM
CA_Programme);

#

write the correct table instead tablename

fair crescent Mar 31, 2020, 11:41 AM

#

I think they may have got it

#

lemme just check over it a few times

thick thicket Mar 31, 2020, 11:41 AM

#

for example - CA_Programme.progID

#

or CA_ActorRole.progID

fair crescent Mar 31, 2020, 11:42 AM

#

Yeah I just thought that since it's joined I didn't need that

#

thanks that got it

thick thicket Mar 31, 2020, 11:42 AM

#

👍

fair crescent Mar 31, 2020, 11:42 AM

#

Really appreciate the help

#

🙏

hollow mango Mar 31, 2020, 1:05 PM

#

📎 image0.png

#

How do i fix this

#

And why has the table renamed itself

vast parrot Mar 31, 2020, 1:19 PM

#

have someone ever used firebase

lime bough Mar 31, 2020, 3:52 PM

#

I have a small database - basically an employee database with basic info, can anyone recomend a simple/fast database for this kind of info?

rain wagon Mar 31, 2020, 4:15 PM

#

@lime bough How small?

#

Could just go with Access or dbase from Open Office for this kind of stuff

lime bough Mar 31, 2020, 4:16 PM

#

The biggest table is around 160 records

rain wagon Mar 31, 2020, 4:16 PM

#

Yeah, I'd set this up in Access

#

gives you a nice interface to work with and forms aren't very hard to do

#

or, if you don't have MS Office, dBase

lime bough Mar 31, 2020, 4:18 PM

#

Thank you @rain wagon 😄 I will try that

carmine edge Mar 31, 2020, 5:31 PM

#

Best way to connect a SQL db with python. Proves very helpful for analytics as we can apply queries and pull data directly from a db.

https://mostlypy.com/python/connect-to-a-mysql-database-in-python/

Mostly Python

admin

Connect to a MySQL database in Python - Mostly Python

How to connect to a MySQL database (or any SQL database) in python with the help of PyMySQL. Also learn about why would you need a connection to a database.

spice tide Mar 31, 2020, 6:11 PM

#

Hey @everyone how can i link my python program with database i just wanna link it with my school database how ?

solid void Mar 31, 2020, 6:28 PM

#

do you have access to your school database? (server ip/name, db user, password?) if so, you can most likely use a python library to connect to it

gentle sparrow Mar 31, 2020, 9:08 PM

#

Can anyone guide me with making a signup and login database for a mobile app

#

the frontend language is flutter

faint prairie Apr 1, 2020, 2:19 AM

#

You probably shouldn't connect to the database directly from the frontend unless it's a database on the phone

stone dirge Apr 1, 2020, 6:46 AM

#

Not exactly what was requested, but still interesting nonetheless http://inloop.github.io/sqlite-viewer/

(Since there's a distinct lack of a wow factor with that preview, it's just a simple viewer for .db files. I don't know if it's a popular thing or not, but I loved it.)

SQLite Viewer

sqlite file viewer

paper sluice Apr 1, 2020, 1:25 PM

#

Hi all I have a weird one, I have an encrypted string in Latin1-General-CS-AS which SQLAlchemy is reading, presumably it is converting it to UTF-8, The crypto iterates over the symbols in the string and retrieves their Code point values and uses that number to decrypt the string. This is if I use ord to do this for some characters it returns say 76 rather then 140. Is there a variant of ord that handles Latin-1 ?

sick nacelle Apr 1, 2020, 4:24 PM

#

Anybody here ever tried to do a SELECT * based on the radius for latitude and longitude?

full fulcrum Apr 1, 2020, 4:57 PM

#

@sick nacelle can you elaborate a bit?

#

what are you trying to do, on which database, etc? 🙂

sick nacelle Apr 1, 2020, 5:05 PM

#

@full fulcrum Soo I have a single table in mySQL. And I'm trying to do a select *, which will fetch all the rows based on the radius of my input for latitude and longitude. I was trying to follow an example online but I'm not quite sure if this looks right. Also first time using mysql

@app.route('select_by_filter ', methods=["POST"])
def select_by_filter():
    data = request.get_json()
    center_lat = data['latitude']
    center_lng = data['longitude']
    start_date = data['start_date']
    end_date = data['end_date']
    radius = data['radius']
    mycursor = mydb.cursor()
    sql ="""SELECT  *  ( .5 * acos( cos( radians($center_lat) ) 
                        * cos( radians( lat ) ) * cos( radians( lng )
                        - radians($center_lng) ) + sin( radians($center_lat) ) 
                        * sin( radians( lat ) ) ) ) AS distance FROM table 
                        ORDER BY distance LIMIT 0 , 10
        """
    mycursor.execute(sql)

Heres a link to the table structure:
https://cdn.discordapp.com/attachments/668636896897007627/694946130404048986/sqltable.png

pulsar timber Apr 1, 2020, 5:06 PM

#

hello, I'm sorry if this is too broad to ask, but how might I go about programming a persistent storage system for my discord bot using sqlite? I've learned a bit of SQL on sqlbolt the past few days, but still am not sure how I could implement this.

full fulcrum Apr 1, 2020, 5:10 PM

#

@sick nacelle did you try any GIS extension for Mysql? that's exactly the kind of tool for this kind of query

#

I know there are GIS extensions for postgres, but I'm not using mysql so I can't really tell

sick nacelle Apr 1, 2020, 5:11 PM

#

@full fulcrum got it thanks! ill look into it

full fulcrum Apr 1, 2020, 5:13 PM

#

@sick nacelle found this: https://itnext.io/playing-with-geometry-spatial-data-type-in-mysql-645b83880331

#

you may have to do some conversion work to get the coordinates from your current DB into something GIS can work with

#

but I think it will solve some headaches down the line 😅

#

@pulsar timber sqlite is a good database engine, so it should work for the need you describe

#

you can either learn more SQL (will never hurt)

pulsar timber Apr 1, 2020, 5:15 PM

#

yeah I saw it suggested in the d.py server

#

I definitely will learn more

full fulcrum Apr 1, 2020, 5:15 PM

#

or look into SQLAlchemy, which abstracts the database gory details behind python objects

#

but you'll quickly run into SQL-related errors even when using SQLA

#

so at least get a good grasp of what are databases, tables, connections, columns, column types and queries

sick nacelle Apr 1, 2020, 5:17 PM

#

@full fulcrum Awesome! will take a look into this now

full fulcrum Apr 1, 2020, 5:17 PM

#

and then you can use SQLA to handle that for you once you know what it's doing

pulsar timber Apr 1, 2020, 5:29 PM

#

ok thanks! I think I'll probably try to learn more SQL and try to use SQLite.

#

also, sorry if this is kind of a newbie question, but how can I increase a number in a database by 1?

rain wagon Apr 1, 2020, 5:35 PM

#

Example: UPDATE table SET value = value + 1 WHERE id=1234

pulsar timber Apr 1, 2020, 5:36 PM

#

thank you!

gentle sparrow Apr 1, 2020, 7:32 PM

#

Can anyone explain to me what the models.py file does in Django

#

What exactly is a model?

#

"models.py — This file holds all the models of the app."

#

What exactly does this mean ^

edgy needle Apr 1, 2020, 7:45 PM

#

Guys this is going to be a blunt question that hasn't had a definitive answer yet. What is stopping people from using dictionaries instead of database in Python?

boreal glen Apr 1, 2020, 8:09 PM

#

Hi i am a begginer when i comes to programming and I descided that the first language I try is python. I completed a begginers course and now know the basics but I cant seem to find a project that suits my knowlage. I am interested in ai and machine learning. I will be very greatefull if you could suggest something I can work on.

edgy needle Apr 1, 2020, 8:17 PM

#

Get in here, grab your courses (register and login), then checkout the "Traditional Face Detection With Python" I have not done too much stuff with machine learning, but this one seems to be good. 🙂 https://realpython.com/free-courses-march-2020

Enjoy Free Courses, On Us – Real Python

Hi there, since these are scary times and many of us are stuck at home, I wanted to offer you some distraction. Here's a gift code for a bundle of our members-only courses on Real Python.

#

@boreal glen

boreal glen Apr 1, 2020, 8:26 PM

#

Thank you i will try it!

edgy needle Apr 1, 2020, 8:31 PM

#

Also it is a good idea to get the hang of linear algebra and probability because machine learning has a lot of mathematics in it. The code is relatively simple. 🙂

rich trout Apr 1, 2020, 10:07 PM

#

What is stopping people from using dictionaries instead of database in Python?
Databases are persistent--they don't get deleted when your code finishes, and they are more efficient in many ways. You generally cannot do complex queries on dicts the same ways as databases. Regardless, if you do implement all the capabilities of a database using python dicts (selections, shared query language, persistence) you'll end up with... another database.

#

Also, dicts must be completely in memory and databases do not

pulsar timber Apr 1, 2020, 11:05 PM

#

sorry if this is a newbie question, but how would I print a value from one of my databases?

long zephyr Apr 2, 2020, 3:22 AM

#

should i be connection.close() after each connection.commit()

#

?

#

using sqlite3

#

@ me if anything

#

Im iterating tru all the members of a guild and loading them to the db

hallow cove Apr 2, 2020, 7:40 AM

#

@long zephyr first off, no. close() closes the connection to your database, so that the next time you call it you would need to create a new connection. this is a lot of work that is unnecessary and time consuming.
second, the best practice, imo is to "cache" your transactions if you know they will be in bulk. for example ```py
for member in guild.members:
cursor.execute('some statement')

Then when you are finished -->

connection.commit()

No closing``` essentially, when you call `commit()` you lock the database, write the change, then unlock it. so its marginally (and i mean marginally) better to avoid that if you will be doing it in certain scenarios (like yours).

long zephyr Apr 2, 2020, 7:41 AM

#

Hey thanks so much for the feedback

hallow cove Apr 2, 2020, 7:41 AM

#

sorry it was so late

long zephyr Apr 2, 2020, 7:41 AM

#

I managed to get everything working now

#

Its all good

#

Thanks, ill consider eveything u said

long zephyr Apr 2, 2020, 8:01 AM

#

@hallow cove So one question

#

Or not question

#

But can you see this for me real quick

#

It is working

#

Its a bit messy tbh

hallow cove Apr 2, 2020, 8:01 AM

#

no worries

long zephyr Apr 2, 2020, 8:01 AM

#

    async def load_data_base(self, ctx):
        count = 0
        for member in ctx.guild.members:
            count = count + 1
            add_all_users(str(member), member.id, 0)
        await ctx.send(f'Data base finished loading all the users after {count} iterations')

Thats my command to call this function

#


def add_all_users(user_name, user_id, warn_amounts):
    if check_exist(user_id) == False:
        cursor.execute('INSERT INTO warns(user_name, user_id, warn_amounts) VALUES(?, ?, ?)', (user_name, user_id, 0))
        print(f'Added {user_name}')
        connection.commit()
    else:
        pass

#

That adds the user

#

and check_exist is this

#

def check_exist(user_id):
    cursor.execute('SELECT * FROM warns WHERE user_id = ?', (user_id,))
    for row in cursor.fetchall():
        if row:
            return True
        else:
           return False

#

Im using check exists in a different command thats why its in a different function

hallow cove Apr 2, 2020, 8:03 AM

#

okay

long zephyr Apr 2, 2020, 8:03 AM

#

Is it bad then to commit every time theres an iteration?

hallow cove Apr 2, 2020, 8:04 AM

#

it depends on how badly you want your time to go down

#

if it isn't a problem, i wouldn't stress

long zephyr Apr 2, 2020, 8:04 AM

#

It was fairly fast when it loaded 3k almost 4k members on it

#

Its not really causing a problem to me, and its working im just wondering if i should be trying to avoid some habits

hallow cove Apr 2, 2020, 8:10 AM

#

one thing i would suggest would be to rid yourself of the for loop in check_exist

def check_exist(user_id):
  return len(cursor.execute('INSERT QUERY HERE', (user_id)).fetchall()) > 0

as far as bad habits, i wouldn't necessarily say that it is going to be an issue unless you are working within a resource limit (like on hosted servers) or with massive amounts of data.
also with SQLite, you have the IGNORE keyword. so you would eliminate the need for check_exist() in this usage provided that user_id is a unique column

#

using which would cut down even more time

#

@long zephyr

long zephyr Apr 2, 2020, 8:12 AM

#

ohh yeah thats neat

#

didnt think of that tbh

#

and whats the ignore thingy

#

didnt quite get it

hallow cove Apr 2, 2020, 8:13 AM

#

and count += 1 is the same as count = count + 1 just a whole lot cleaner

#

so essentially, provided that there is a unique column in your database (i would suggest making it the user id since that is guaranteed to be unique), you can do sql INSERT OR IGNORE INTO table VALUES (?, ?, ?) so that when it encounters a insert it will automatically ignore it. instead of you having to check every single user

#

if you want to reset them to 0 instead, you can use the REPLACE keyword

long zephyr Apr 2, 2020, 8:19 AM

#

Wait but i have 3 columns actually, is it bc imonly looking for one specific column?

hallow cove Apr 2, 2020, 8:19 AM

#

afaik only one column has to be unique

#

when you try to insert a duplicate user id, it should trigger the conflict handler (in this case IGNORE) and skip it

long zephyr Apr 2, 2020, 8:22 AM

#

I started sqlite like a day ago

#

I havent read or searched much into it

#

I will def take that into acc

#

And probably change my check_exist into what u shared

#

thanks!

hallow cove Apr 2, 2020, 8:23 AM

#

np, for refrence, the docs are at http://www.sqlite.org

undone roost Apr 2, 2020, 8:39 AM

#

How to update or delete an item in sqlalchemy ?

full fulcrum Apr 2, 2020, 8:56 AM

#

db.session.delete(myobject)

undone roost Apr 2, 2020, 9:07 AM

#

And update ?

full fulcrum Apr 2, 2020, 9:12 AM

#

just alter your object attribute and don't forget to commit your session

undone roost Apr 2, 2020, 9:19 AM

#

How to alter it .. That's whta i need to know

#

I accepted an item from user with changes made to previous item now i need to put this item instead of the old one with same primary key or apply changes to the old item

rain wagon Apr 2, 2020, 9:38 AM

#

@undone roost Select it from the database, update the fields, then commit it again

#

Here is an example from my code for that:


            query = db.session.query(TAlliance).filter(TAlliance.alliance_id == self.__allianceId)
            if not query.first():
                db.session.add(TAlliance(**self.content))
            else:
                query.update(self.content)
            db.session().commit()```

#

Since I am using dicts with the column names as keys, I can just send that to sqlalchemy as it is

#

This either adds the entry (db.session.add) or updates it if it exists

undone roost Apr 2, 2020, 11:06 AM

#

def update_item(db: Session, item: schemas.ItemCreate, item_id: int):
    db_item = db.query(models.Item).filter(models.Item.id == item_id).first()
    
    db.commit()

    db.refresh(db_item)
    return db_item

#

this is my code i got the item i want to edit ...the new data is passed in as item

clever topaz Apr 2, 2020, 11:21 AM

#

@undone roost Is that the whole function?

undone roost Apr 2, 2020, 11:22 AM

#

running db_item.update() is giving an error saying it doesn't have any attribute named update

clever topaz Apr 2, 2020, 11:22 AM

#

And what is/isn't happening?

undone roost Apr 2, 2020, 11:23 AM

#

i am working with fastApi and this update_item function i am making to update an item in the list

#

but i can't figure out how to update an item

#

i can get that item in db_item but idk how to make modifications to it

clever topaz Apr 2, 2020, 11:23 AM

#

I assume your code has changed? Can you post the relevant function's code?

undone roost Apr 2, 2020, 11:24 AM

#

as the modifications are in form of a whole new item iteself

#

it's not complete

#

i need to fill in between lines

#

where i update the item

#

which i retrieved

#

i tried going other way around by deleting the retrieved item and creating new and adding it to database that worked but it changed the primary key

#

but i need to update the item i guess

clever topaz Apr 2, 2020, 11:26 AM

#

Yeah, don't do that. Updating isn't too difficult, but it isn't easy to help without code.

undone roost Apr 2, 2020, 11:26 AM

#

def create_item(db: Session, item: schemas.ItemCreate):
    db_item = models.Item(**item.dict())
    db.add(db_item)
    db.commit()
    db.refresh(db_item)
    return db_item

#

this is create item function

#

that's all i got ...i am not able to use the update command

clever topaz Apr 2, 2020, 11:28 AM

#

OK, to update you need to have an if to check if the item is already present in the DB.

#

Can you do that yourself?

undone roost Apr 2, 2020, 11:28 AM

#

sure

#

a min

#

like this?

#

    if db_item:
        pass
    else:
        pass

clever topaz Apr 2, 2020, 11:30 AM

#

There is no query

#

The second is closer.

#

But you need to query the item model, probably with the PK.

#

And see if there is an item with that PK already.

#

(Primary Key)

undone roost Apr 2, 2020, 11:30 AM

#

i queried

#

wait

#

def update_item(db: Session, item: schemas.ItemCreate, item_id: int):
    db_item = db.query(models.Item).filter(models.Item.id == item_id).first()
    if db_item:
        pass
    else:
        pass

    db.commit()
    db.refresh(db_item)
    return db_item

clever topaz Apr 2, 2020, 11:31 AM

#

Correct!

#

So now in the first case you update and in the second (where db_item is None), you create the item.

undone roost Apr 2, 2020, 11:32 AM

#

gotcha

#

now ..how to update

clever topaz Apr 2, 2020, 11:33 AM

#

SQLAlchemy, right?

undone roost Apr 2, 2020, 11:33 AM

#

yes

clever topaz Apr 2, 2020, 11:33 AM

#

Surely you could simply just do db_item.<field> = ... to change a field.

#

But you have to refresh before committing.

#

If that doesn't work I'll suggest something else.

undone roost Apr 2, 2020, 11:34 AM

#

No wait...i have data in json format

#

Did you check the create item function above

clever topaz Apr 2, 2020, 11:35 AM

#

db_item = db.query(models.Item).filter(models.Item.id == item_id).first() returns an item, I'm almost certain.

#

Try print(db_item) below to see what it looks like.

undone roost Apr 2, 2020, 11:39 AM

#

{"item":"string","description":"string","status":true,"id":6}

#

like this

clever topaz Apr 2, 2020, 11:40 AM

#

That was the output?

#

Which DB?

undone roost Apr 2, 2020, 11:40 AM

#

maria(SQL)

#

it was the output from a get_item function as update_item will still give the error

#

but the query is same

#

db_item = models.Item(**item.dict())

clever topaz Apr 2, 2020, 11:42 AM

#

Right in that case you should be able to do:

db_item['description'] = '...'

Or whatever key you want to change.
Then refresh item in DB before commiting.

#

https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_orm_updating_objects.htm

#

I don't think you even need refresh.

undone roost Apr 2, 2020, 11:42 AM

#

can't i replace the whole item?

#

without replacing the primary key

clever topaz Apr 2, 2020, 11:43 AM

#

Yup. You just make sure to not touch id or set id back after replacing item.

#

Like this:

item_id = db_item['id']
db_item = another_item
db_item['id'] = item_id

undone roost Apr 2, 2020, 11:44 AM

#

i mean there can be a lot of columns which are changed and a few are not so it would be better if i could just change the whole item with same PK

#

let me try that

#

def update_item(db: Session, item: schemas.ItemCreate, item_id: int):
    db_item = db.query(models.Item).filter(models.Item.id == item_id).first()

    if db_item:
        up_item = models.Item(**item.dict())
        og_itemId=db_item['id']
        db_item=up_item
        db_item['id']=og_itemId
    else:
        db_item = models.Item(**item.dict())

    db.commit()
    db.refresh(db_item)
    return db_item

#

error: item object is not subscriptable

#

in second line after if

clever topaz Apr 2, 2020, 11:49 AM

#

Should be:

def update_item(db: Session, item: schemas.ItemCreate, item_id: int):
    db_item = db.query(models.Item).filter(models.Item.id == item_id).first()

    if db_item:
        og_itemId=db_item['id']
        db_item=up_item
        db_item['id']=og_itemId
    else:
        db_item = models.Item(**item.dict())

    db.commit()
    db.refresh(db_item)
    return db_item

#

No, wait.

#

def update_item(db: Session, item: schemas.ItemCreate, item_id: int):
    db_item = db.query(models.Item).filter(models.Item.id == item_id).first()

    item['id'] = db_item['id']
    up_item = models.Item(**item.dict())

    if db_item:
        og_itemId=db_item['id']
        db_item=up_item
        db_item['id']=og_itemId
    else:
        db_item = up_item
        db.add(db_item)

    db.commit()
    return db_item

undone roost Apr 2, 2020, 11:52 AM

#

not subscriptable it says

#

in line 5 ..it will give error again

#

yea ..the same error

clever topaz Apr 2, 2020, 11:54 AM

#

Line 5?

#

up_item = models.Item(**item.dict())?

undone roost Apr 2, 2020, 11:55 AM

#

4 sorry

clever topaz Apr 2, 2020, 11:55 AM

#

^ Is it that line?

undone roost Apr 2, 2020, 11:57 AM

#

no no ..line 4

 item['id'] = db_item['id']

clever topaz Apr 2, 2020, 11:58 AM

#

def update_item(db: Session, item: schemas.ItemCreate, item_id: int):
    db_item = db.query(models.Item).filter(models.Item.id == item_id).first()

    item_dict = item.dict()
    item_dict['id'] = db_item['id']
    up_item = models.Item(item_dict)

    if db_item:
        og_itemId=db_item['id']
        db_item=up_item
        db_item['id']=og_itemId
    else:
        db_item = up_item
        db.add(db_item)

    db.commit()
    return db_item

undone roost Apr 2, 2020, 11:58 AM

#

wont work sir

#

as the error is in db_item['id'] part

#

that isn't subscriptable

clever topaz Apr 2, 2020, 12:00 PM

#

def update_item(db: Session, item: schemas.ItemCreate, item_id: int):
    db_item = db.query(models.Item).filter(models.Item.id == item_id).first()

    item.id = db_item.id
    up_item = models.Item(**item.dict())

    if db_item:
        og_itemId=db_item.id
        db_item=up_item
        db_item.id=og_itemId
    else:
        db_item = up_item
        db.add(db_item)

    db.commit()
    return db_item

#

?

undone roost Apr 2, 2020, 12:07 PM

#

not giving any error but not committing to database

#

the response is 200

clever topaz Apr 2, 2020, 12:12 PM

#

@undone roost It seems that it isn't commiting after update.

#

I'll test a few things and see what works.

#

@undone roost Try this:

def update_item(db: Session, item: schemas.ItemCreate, item_id: int):
    db_item = db.query(models.Item).filter(models.Item.id == item_id).first()

    if db_item:
        db.update(item.dict())
    else:
        db_item = models.Item(**item.dict())
        db.add(db_item)

    db.commit()
    return db_item

BTW, does item have an id preset or not? You can check this by using print(item, item.dict()). It'd help if you could also give me that output so that I can understand what we're working with.

wary sentinel Apr 2, 2020, 4:24 PM

#

Can I ask help here?

delicate fieldBOT Apr 2, 2020, 5:03 PM

#

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

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

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

undone roost Apr 2, 2020, 5:19 PM

#

@clever topaz it's weird but it says Session object has no attribute update

#

and i think item have an id preset ..i will show you the output

#

wait i am unable to get the output since the documentation doesn't gives any output and the link wont open since it's internal server error.

#

@wary sentinel yes you can ...just ask and not ask to ask.

clever topaz Apr 2, 2020, 5:26 PM

#

Oh sorry.

#

I know what the issue is.

#

def update_item(db: Session, item: schemas.ItemCreate, item_id: int):
    db_item = db.query(models.Item).filter(models.Item.id == item_id).first()

    if db_item:
        db_item.update(item.dict())
    else:
        db_item = models.Item(**item.dict())
        db.add(db_item)

    db.commit()
    return db_item

It was supposed to be db_item.update not db.update.

#

@undone roost

undone roost Apr 2, 2020, 5:37 PM

#

geez right

#

still shows the error Item object has no attribute update

#

def update_item(db: Session, item: schemas.ItemCreate, item_id: int):
    print(item,item.dict())
    db_item = db.query(models.Item).filter(models.Item.id == item_id).first()

    if db_item:
        db_item.update(**item.dict())
    else:
        db_item = models.Item(**item.dict())
        db.add(db_item)

    db.commit()
    return db_item

#

and it shows in first line after if

#

where Item isnt even used

clever topaz Apr 2, 2020, 5:46 PM

#

def update_item(db: Session, item: schemas.ItemCreate, item_id: int):
    print(item,item.dict())
    db_item = db.query(models.Item).filter(models.Item.id == item_id).first()

    if db_item:
        db_item['description'] = 'test'
    else:
        db_item = models.Item(**item.dict())
        db.add(db_item)

    db.commit()
    return db_item

Does that work (note it will modify the item in the DB)?

undone roost Apr 2, 2020, 5:52 PM

#

item object does not support item assignment

#

the item object is same as previous

#

wait maybe we first need to convert it to dictionary

#

not able to 😛

undone roost Apr 2, 2020, 6:18 PM

#

it is committing the else part when i enter a new id...that means putting db_item=up_item is not working...as per the following code

def update_item(db: Session, item: schemas.ItemCreate, item_id: int):
    db_item = db.query(models.Item).filter(models.Item.id == item_id).first()
    if db_item:
        item.id = db_item.id
    up_item = models.Item(**item.dict())

    if db_item:
        og_itemId=db_item.id
        db_item=up_item
        db_item.id=og_itemId
        db.commit()

    else:
        db_item = up_item
        db.add(db_item)
        db.commit()

    return db_item

clever topaz Apr 2, 2020, 6:33 PM

#

@undone roost Hmm that's very annoying. This short scipt worked for me:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80))
    email = db.Column(db.String(120))

    def __repr__(self):
        return '<User %r>' % self.username


db.create_all()
john = User(username='jlennon', email='john@example.com')
paul = User(username='pmac', email='paul@example.com')
george = User(username='ghar', email='george@example.com')
ringo = User(username='rstarr', email='ringo@example.com')

db.session.add_all([john, paul, george, ringo])
db.session.commit()

user_three = User.query.get(3)
user_three.username = 'georgeh'
db.session.commit()

for i in User.query.all():
    print(i.id, i.username, i.email)

#

Now to test it with a query like yours...

#

Also works:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

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


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80))
    email = db.Column(db.String(120))

    def __repr__(self):
        return '<User %r>' % self.username


db.create_all()
john = User(username='jlennon', email='john@example.com')
paul = User(username='pmac', email='paul@example.com')
george = User(username='ghar', email='george@example.com')
ringo = User(username='rstarr', email='ringo@example.com')

db.session.add_all([john, paul, george, ringo])
db.session.commit()

user_three = User.query.filter(User.id == 1).first()
user_three.username = 'georgeh'
db.session.commit()

for i in User.query.all():
    print(i.id, i.username, i.email)

Hmm, the only difference is you are using an ItemCreate object which maybe is causing the problem.

undone roost Apr 2, 2020, 6:38 PM

#

def update_item(db: Session, item: schemas.ItemCreate, item_id: int):
    print(item,item.dict())
    db_item = db.query(models.Item).filter(models.Item.id == item_id).first()

    if db_item:
        db_item['description'] = 'test'
    else:
        db_item = models.Item(**item.dict())
        db.add(db_item)

    db.commit()
    return db_item

#

this worked

#

we were using []

#

we had to use .description

#

just like you used in above example

clever topaz Apr 2, 2020, 6:38 PM

#

Wait which was correct? db_item['description'] = 'test'?

undone roost Apr 2, 2020, 6:39 PM

#

db_item.description='test'

clever topaz Apr 2, 2020, 6:39 PM

#

Now to fix it for multiple attrs.

undone roost Apr 2, 2020, 6:39 PM

#

replaced

#

we use a for loop

#

right?

clever topaz Apr 2, 2020, 6:39 PM

#

Yup. But I need to work out how to get the list of fields for an item.

#

Unless you know?

undone roost Apr 2, 2020, 6:40 PM

#

for i in db_item.keys() :

#

this wont work?

clever topaz Apr 2, 2020, 6:40 PM

#

Nope.

undone roost Apr 2, 2020, 6:41 PM

#

dont think so as it is not treated like a dict

#

that's the whole problem

#

new_foobar = {'name': 'New Foobar Name!'}
old_foobar = session.query(FoobarModel).get(foobar_id)

for property in new_foobar:
  old_foobar[property] = new_foobar[property]

session.commit()
session.flush()

#

got this on stack overflow

#

similar problem

clever topaz Apr 2, 2020, 6:42 PM

#

Hmm, maybe. I'll try something like it.

#

Link?

undone roost Apr 2, 2020, 6:42 PM

#

and also a setattr method but i didn't get that one

clever topaz Apr 2, 2020, 6:43 PM

#

setattr will probably work.

undone roost Apr 2, 2020, 6:43 PM

#

https://stackoverflow.com/questions/47735329/updating-a-row-using-sqlalchemy-orm

Stack Overflow

Updating a row using SQLAlchemy ORM

Note: I am not using Flask-SQLAlchemy here, I'm using SQLAlchemy ORM

I've seen several answers on StackOverflow saying that this is a way to update a record:

session.query(FoobarModel).get(foobar...

clever topaz Apr 2, 2020, 6:43 PM

#

It was what I was going to use.

undone roost Apr 2, 2020, 6:43 PM

#

i dont know about setattr

clever topaz Apr 2, 2020, 6:43 PM

#

Yup, just like that.

#

>>> class x():
...     a = 0
... 
>>> x_inst = x()
>>> x_inst.a
0
>>> x_inst.a = 1
>>> x_inst.a
1
>>> setattr(x_inst, 'a', 3)
>>> x_inst.a
3

#

Simply sets an attribute, it's an equivilent to x_inst.a = 3.

undone roost Apr 2, 2020, 6:47 PM

#

okay so how to use it here

#

he has defined a dict first

clever topaz Apr 2, 2020, 6:49 PM

#

Yup - that would be your item.dict()

#

Substitute that and it should work just fine.

undone roost Apr 2, 2020, 6:49 PM

#

cool

#

wooohooo

#

it worked

clever topaz Apr 2, 2020, 6:52 PM

#

Done?

#

Nice!

#

Sorry it took so long!

undone roost Apr 2, 2020, 6:52 PM

#

thanks a lot for your precious time

#

🙂

clever topaz Apr 2, 2020, 6:52 PM

#

SQLAlchemy is quite confusing

#

at times.

#

That's why...

undone roost Apr 2, 2020, 6:52 PM

#

yea

#

at flask it was different

clever topaz Apr 2, 2020, 6:52 PM

#

https://github.com/pudo/dataset

GitHub

pudo/dataset

Easy-to-use data handling for SQL data stores with support for implicit table creation, bulk loading, and transactions. - pudo/dataset

#

We have dataset.

undone roost Apr 2, 2020, 6:52 PM

#

what's that?

clever topaz Apr 2, 2020, 6:53 PM

#

https://dataset.readthedocs.io/en/latest/

#

Makes it a lot easier to work with databases by abstracting a lot of detail.

#

Though not sure how it would work in an ORM context.

undone roost Apr 2, 2020, 6:53 PM

#

cool ..thanks

clever topaz Apr 2, 2020, 6:53 PM

#

No problem.

#

@undone roost Check out gino too - https://github.com/python-gino/gino

GitHub

python-gino/gino

GINO Is Not ORM - a Python asyncio ORM on SQLAlchemy core. - python-gino/gino

#

Looks quite cool

#

Though I haven't used it myself.

opal dawn Apr 2, 2020, 7:09 PM

#

Im stuck on what I should do. Use an API directly, or store the data I need in a database for my app. What is the recommended approach?

torn sphinx Apr 2, 2020, 7:59 PM

#

If the data comes from an external source and changes then use an API

proven wagon Apr 2, 2020, 8:22 PM

#

Hey, I'm currently looking at PostgreSQL Relationships / Foreign Keys.

I see that you can reference one int to another int, but can you make a array of ints referencing another tables row?

#

Bad explanation? idk

solid void Apr 2, 2020, 9:07 PM

#

@proven wagon i don't think so, but as far as i understand, using arrays is not encouraged, especially for relationship, normalization is better achieved by having each value in its own row

proven wagon Apr 2, 2020, 9:15 PM

#

Any alternative ways to do this?

rain wagon Apr 2, 2020, 9:15 PM

#

@proven wagon Be more specific. What exactly do you want to do?

#

A FK is not necessarily always a reference to an int

#

it is a pointer to another table

#

To the PK of said table to be precise

#

That PK can be an int, but it can be anything

proven wagon Apr 2, 2020, 9:16 PM

#

I want to have one table of users, and one of roles

rain wagon Apr 2, 2020, 9:17 PM

#

So one user can have multiple roles?

proven wagon Apr 2, 2020, 9:17 PM

#

a user in the users table can have multiple roles

#

yes

rain wagon Apr 2, 2020, 9:17 PM

#

That is a 1:n relation

#

What you want is a table where each role is listed for that user in a seperate column

#

CREATE TABLE(
  id int PrimaryKey
  userID int ForeignKey
  roleID int ForeignKey
);```

#

Pseudo SQL

#

but you get the gist

proven wagon Apr 2, 2020, 9:19 PM

#

the id in that table would be irrelevant right

rain wagon Apr 2, 2020, 9:19 PM

#

kind of, could do a composite key which was my first thought

#

but eh

#

you need a primary key though

#

how you do that in this case is debatable

#

but an array would violate NF1

#

and you don't want to violate any of the first three NF

proven wagon Apr 2, 2020, 9:22 PM

#

NF? think_dumb

Looks like I am out of my depth 😅

rain wagon Apr 2, 2020, 9:22 PM

#

Database normal forms

#

google it

#

But NF1 means atomic data

#

one column, one value

#

so a database storing "Joe Miller" in one field, violates NF1

#

it's 2 fields, first_name and last_name

proven wagon Apr 2, 2020, 9:24 PM

#

Well

#

Thank you for trying 😅

opal dawn Apr 2, 2020, 10:21 PM

#

@rain wagon I'm guessing NF2 would mean the same but with data that belongs in separate tables?

rain wagon Apr 2, 2020, 10:28 PM

#

A relation is in the second normal form if it fulfills the following two requirements:

It is in first normal form.
It does not have any non-prime attribute that is functionally dependent on any proper subset of any candidate key of the relation. A non-prime attribute of a relation is an attribute that is not a part of any candidate key of the relation.

#

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

Second normal form

Second normal form (2NF) is a normal form used in database normalization. 2NF was originally defined by E. F. Codd in 1971.A relation is in the second normal form if it fulfills the following two requirements:

It is in first normal form.
It does not have any non-prime attribu...

opal dawn Apr 2, 2020, 10:39 PM

#

I didn't learned sql formally, so my understanding of it is guided by OOP concepts.

#

at least in regard to object properties

rain wagon Apr 2, 2020, 10:40 PM

#

In the second normalform, you cannot have any entry being dependant on the other

#

So, if you would make table that contains the full name, address and an order, that violates NF2

#

correct way to do it is to use a seperate table for the order, for each item one row and a third table to connect that order to the customer id

#

which essentially resolves the 1:n relation between the order items and the customer

opal dawn Apr 2, 2020, 10:43 PM

#

that makes sense

rain wagon Apr 2, 2020, 10:43 PM

#

you'd actually connect those items to the order first, then that order to the customer, but it was just an example

#

Third normalform is primary keys and referential data integrity, which means, if a PK is still in use, you cannot delete the source data

#

It also says you need to make sure to have no duplicates

#

I haven't seen any other form actually being used, I think total is 6 normal forms, but most only use the first three

#

so those are the ones to remember and abide by

opal dawn Apr 2, 2020, 10:52 PM

#

would it be right to say that property inheritance in rdms is flat, whereas in oop it's deep?

rain wagon Apr 2, 2020, 10:53 PM

#

not sure what you mean with inheritance there

opal dawn Apr 2, 2020, 10:55 PM

#

right, I meant to say composition

upbeat lily Apr 2, 2020, 10:55 PM

#

inheritance is from the ERD perspective - I'm not sure what you mean exactly by flat/deep

opal dawn Apr 2, 2020, 11:00 PM

#

deep inheritance heirarchies aren't encouraged anyway

rain wagon Apr 2, 2020, 11:01 PM

#

There isn't really any inheritance in databases, at least not in the formal sql

#

if you query a table with a foreign key, all you get is an integer

#

you need to actively resolve it with e.g. INNER JOIN to get the data behind it

opal dawn Apr 2, 2020, 11:02 PM

#

yes. query language. got it.

#

inheritance wouldn't make sense in sql

upbeat lily Apr 2, 2020, 11:06 PM

#

In the theory of databases - mainly enhanced entity relationship diagrams, there is inheritance

earnest knoll Apr 3, 2020, 11:27 AM

#

How can I update the field in my MongoDB database that is highlighted red using PyMongo?

📎 Screenshot_2020-04-03_12-58-59.png

#

I have the ID of that invite (in green)
invites is an array

earnest knoll Apr 3, 2020, 11:43 AM

#

ok found a solution https://stackoverflow.com/questions/28828825/updating-an-object-inside-an-array-with-pymongo

sick nacelle Apr 3, 2020, 4:40 PM

#

Hey guys, quick question, for those of ya'll who've worked with mysql. How would I put input variables in my sql statement with python? So that way when someone puts in an input, the sql statement queries for that input

rain wagon Apr 3, 2020, 7:25 PM

#

by using bound statements

#

connection.execute("INSERT INTO table (column1, column2) VALUES(?,?)", (value1, value2))

wraith flicker Apr 3, 2020, 8:04 PM

#

How is fnac database system organized?

#

or how it possibly is

#

in terms of actors, use cases, entities, attributes

#

at least actors and use cases

brisk crest Apr 3, 2020, 8:50 PM

#

Ive been sent here

#

To ask about remote_side in flask_sqlalchemy

#

Anybody can explain it to me properly?

astral salmon Apr 3, 2020, 9:54 PM

#

Is there a way to check if a value is in particular column of a dataframe(pandas)

rigid dove Apr 3, 2020, 10:31 PM

#

@astral salmon value in df[column].values

astral salmon Apr 3, 2020, 10:32 PM

#

Thanks. I will let you know later if it works ✌️

astral salmon Apr 4, 2020, 9:00 AM

#

Thanks, it works perfectly fine

torn sphinx Apr 4, 2020, 12:00 PM

#

how do I get the field names to show when I'm doing a select

#

it usually shows in other db's.. but I'm using sql lite and it only shows the results without field names

rain wagon Apr 4, 2020, 1:34 PM

#

use the dict factory

#

@torn sphinx

torn sphinx Apr 4, 2020, 1:35 PM

#

I dont understand

#

also.. what's the difference between char, varchar and varchar2

rain wagon Apr 4, 2020, 1:36 PM

#

If you use the dict factory of sqlite python, you can access columns like result["columnname"]

torn sphinx Apr 4, 2020, 1:36 PM

#

Oh I meant.. when doing a select for everything, it wasn't showing me the field names

rain wagon Apr 4, 2020, 1:36 PM

#

con = sqlite3.connect(":memory:")
con.row_factory = dict_factory```

#

That makes no sense

#

select shows you the data

#

or do you mean on the sqlite console?

torn sphinx Apr 4, 2020, 1:38 PM

#

on the console yeah

#

yes, I see the data but I don't see the column names

rain wagon Apr 4, 2020, 1:38 PM

#

sqlite> .header on
sqlite> .mode column

torn sphinx Apr 4, 2020, 1:38 PM

#

ahh ok

#

what's .mode column for

rain wagon Apr 4, 2020, 1:39 PM

#

also.. what's the difference between char, varchar and varchar2
@torn sphinx I don't think there is a varchar2

torn sphinx Apr 4, 2020, 1:39 PM

#

I only did header on

#

ok.. maybe varchar2 is an oracle thing..

#

so what about char and varchar

rain wagon Apr 4, 2020, 1:40 PM

#

There is no such thing

#

in sqlite

#

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

#

Read this

torn sphinx Apr 4, 2020, 1:40 PM

#

oki

rain wagon Apr 4, 2020, 1:40 PM

#

Any char just gets converted to TEXT

#

basically

torn sphinx Apr 4, 2020, 1:40 PM

#

got it

#

hmm let me pick one dialect and use that

#

maybe sqlite is not the way to go for me

rain wagon Apr 4, 2020, 1:41 PM

#

it's the only option for a local, serverless db

upbeat lily Apr 4, 2020, 1:41 PM

#

SQLite is pretty happy to let you jam any type anywhere

rain wagon Apr 4, 2020, 1:41 PM

#

yeah, that is why I linked the article

#

it contains a table with the type mapping

torn sphinx Apr 4, 2020, 1:41 PM

#

I'll go with postgres for now

upbeat lily Apr 4, 2020, 1:41 PM

#

oh - I'm blind - I didn't see that link

rain wagon Apr 4, 2020, 1:41 PM

#

But strictly speaking, it just maps it to TEXT

torn sphinx Apr 4, 2020, 1:42 PM

#

better to stick with a dialect I'm more used to I think

rain wagon Apr 4, 2020, 1:42 PM

#

Using such statements isn't too bad of an idea though, it makes it easier to swap out the backend if needed

jade blaze Apr 4, 2020, 2:17 PM

#

Heya 🙂 Any idea on how mongodb async queries compare to postgresql async queries in CPU usage? Looking to convert part of my infrastructure to PostgreSQL but I'm not sure if it'd be worth it (Mongo's flexibility has saved my butt a few times)

Also I saw a fair few async PostgreSQL libraries, which oners would you recommend if you use asyncio a lot? (currently leaning most towards asyncpg")

sick nacelle Apr 4, 2020, 3:26 PM

#

Anybody here know how to input user defined variables with mysql and python? I have a flask route here thats supposed to return the number of items or incidents based on whats within the radius of the user's location. Does this look right?

@app.route('/select_by_filter', methods=["POST"])
def select_by_filter():
    data = request.get_json()
    center_lat = data['latitude']
    center_lng = data['longitude']
    start_date = data['start_date']
    end_date = data['end_date']
    radius = data['radius']
    cursor = db.cursor()
    sql = f"""
    SELECT
        *, ((radius) * ACOS( COS(RADIANS(%s)) 
        * COS(RADIANS(latitude)) * COS( RADIANS(longitude) - RADIANS(%s)) 
        + SIN(RADIANS(%s)) * SIN(RADIANS(latitude)))) 
    AS DISTANCE
    FROM
        `311`
    WHERE
        created_date between (%s) and (%s)
    ORDER BY `311`.`created_date` ASC 
    
    """
    values = [center_lat, center_lng, center_lat, start_date, end_date]

    cursor.execute(sql, values)
    row_headers=[x[0] for x in cursor.description]
    rv = cursor.fetchall()
    json_data=[]
    for result in rv:
        json_data.append(dict(zip(row_headers,result)))
    return jsonify(json_data)

solid void Apr 4, 2020, 4:17 PM

#

@sick nacelle https://www.python.org/dev/peps/pep-0249/#paramstyle documents the various way a db-api compliant library can allow that, your execute method should support at least one of it

Python.org

PEP 249 -- Python Database API Specification v2.0

The official home of the Python Programming Language

#

the named and pyformat ones are nicer imho, but the qmark is certainly the most commonly supported one

#

(i don't have source for that claim, it's just something i believe)

opal dawn Apr 4, 2020, 6:31 PM

#

Is it better to load SQL table data into a panda's dataframe for processing, or just run SQL queries directly on the table?

orchid matrix Apr 4, 2020, 7:03 PM

#

@opal dawn It will be easier to use and visualize in pandas.

rigid dove Apr 4, 2020, 7:10 PM

#

@opal dawn It depends. Let's say you have a db on some server which is not doing much work, you might prefer to perform some aggregation on the server, and then send over the value(s) for analysis, in order to avoid transferring large amounts of data, possible also loading it all into memory in a pandas dataframe. Also, some calculations might be easily done or must faster with a SQL query but not in pandas or vice versa.

opal dawn Apr 4, 2020, 7:33 PM

#

okay, these are good factors to consider

jade blaze Apr 4, 2020, 9:26 PM

#

Heya, I'm using MongoDB to store a lot of stuff asynchronously but have noticed a big spike in my CPU usage, it's not uncommon that I even hit 100% CPU usage anymore and I'm really baffled, my server is running with 1 vCore & 4GB of RAM but it just seems like something is definitely not right. Already optimised a lot of my scripts to try and prevent any issues but it doesn't seem to be helping, tried interpreting results with mongotop & mongostat but it's proving to be a bit of a headache.

Could anyone point me in the right direction to help me figure out what I might possibly be doing wrong?

I ran mongotop with a 30 second interval and spotted this:

📎 unknown.png

#

now I might be wrong but 142389ms for the first one seems... very excessive if I'm interpreting this correctly

#

Thanks to a lot of help in the #async-and-concurrency channel I managed to optimise a whole lot and some recommended using postgres instead, I'm looking into that but will have to learn a bit / spend some time on it, not sure if Mongo would cause this though

#

Mongostat looks a bit like this:

📎 unknown.png

#

but when I do htop

📎 unknown.png

solid void Apr 4, 2020, 10:05 PM

#

the problem with mongo is that it doesn't really force you to think about how to model your database, so design problems can creepe up and cause both performance and consistency issues, well, that's the main problem anyway, others like lack of actual transaction, atomicity of operations, let alone a terrible query langage, are certainly to consider as well. Yeah, i don't like it very much, and postgres is certainly better for almost everything, maybe even everything.

jade blaze Apr 4, 2020, 10:20 PM

#

I particularly liked it for its flexibility, and to me it as very fun to use honestly
At the start I used SQLite and that was such a pain, especially if you forgot a certain table when setting things up or something changed over time as you wanted to expand... that cause me many headaches, then I found Mongo and it seemed awesome

#

Now I'm not sure, I'll still give postgres a shot but I still feel like something is wrong, in the way I've done things I guess

solid void Apr 4, 2020, 10:46 PM

#

in my previous company, they did also like the flexibility mongo provided, at least they liked it in the beginning, when i arrived, the company was 4 years old, and they hated it, it had long outlived and outsized it's area of comfort, the database had a lot of inconsistencies, and performances where very hard to get right in some case, while the data wasn't that big. I spent about half the year porting collections to sql tables, adjusting code, it was not fun, but it was the way to go, and people couldn't wait to be there.

jade blaze Apr 4, 2020, 11:15 PM

#

I see, very good input! Do you think converting it all to a postgres db would solve like 90% of my issues? Seeing I tried a whole lot

#

have optimised a ton thusfar, but the optimisations have only had a very minor impact on the server's performance

#

porting it all to postgreSQL will be quite a task but should be doable, just takes some time

solid void Apr 4, 2020, 11:42 PM

#

i can't promise it'll solve your problems, but i'm quite confident designing your DB with the constraint of a rational system, and taking care about normalization, should have a lot of effect on your performances yes.

wicked iris Apr 4, 2020, 11:56 PM

#

Hello everyone, I am new to SQLite and I want to use it in an app that will rely on OOP. Could anyone recommend me some good literature, practices, etc?

#

I don't really want to improvize since I have limited time (school project). I would like to see some examples like project structure, database methods, and so on. Thanks!

torn sphinx Apr 5, 2020, 12:02 AM

#

Are you asking for ORM or do will you write queries directly in SQL?

#

Regardless, SQLAlchemy is probably what you are looking for

jade blaze Apr 5, 2020, 12:09 AM

#

@solid void sorry for the ping & let me know if you'd rather not get pinged, but there's currently a lot of async queries, what's the impact of async queries like with PostgreSQL?

solid void Apr 5, 2020, 12:11 AM

#

there are some async enabled connectors for postgres, i tested asyncpg and it works, i'm sure there are other good options

jade blaze Apr 5, 2020, 12:12 AM

#

Yeah that's the one that looked most appealing to me, was just wondering about CPU impact

solid void Apr 5, 2020, 12:16 AM

#

i didn't have enough load on my test project to mesure that, but i'm not worried, the point of async is to make that more efficient

#

allow to cram more work on the cpu while you wait on IO

jade blaze Apr 5, 2020, 12:21 AM

#

ahh see that's the issue in my specific scenario, my cpu just can't handle all this

#

I might just have to upgrade my server honestly

solid void Apr 5, 2020, 12:35 AM

#

yeah, it's worth a try, can be a way cheaper solution than porting your app to a different DB

jade blaze Apr 5, 2020, 1:32 AM

#

Thanks, will give it a shot, asking my server provider if I can get a refund before upgrading as I literally paid for a year less than a month ago 😅

#

Going to look into using Postgres regardless as it does seem fairly nice

tropic kayak Apr 5, 2020, 1:50 AM

#

i got this traceback when pymongo was trying to connect to my mongodb database
https://hastebin.com/guwopesiyo.rb
the error doesnt tell anything at all so im a bit confused could someone explain

finite oyster Apr 5, 2020, 3:23 AM

#

Hi All, new programmer here. I asked in #python, and it was suggested I post here, Having an issue with Python/MySQL. Have a MySQL database (backend for a PHP application called WebCalendar) that stores a time value as an integer. Within my python application, I'm trying to convert integer values to time values. My issue is that although the time value is presented as HHMMSS within the PHP application, in the MySQL database, because the field is an integer, I have values that range from 0 (zero) to 235959, so there are no leading zeros for time values before 10:00:00. I can't change the PHP application/MySQL to use a character.
Any thoughts, suggestions or links to relevant webcontent would be very, very much appreciated!!

acoustic silo Apr 5, 2020, 3:30 AM

#

i'd suggest storing the time value as a string in your python app

#

>>> t = 2356
>>> f'{t:06d}'
'002356'

#

when you query and receive the integer, you can format it and pad with leading zeros like so

#

@finite oyster

latent berry Apr 5, 2020, 4:10 AM

#

hey all, working on a project and it's my first time using postgres.
the plan is, that a user will have a dictionary which contains words, note the plural.
the dictionaries table's schema is:

CREATE TABLE dictionaries (
dictionary_id SERIAL PRIMARY KEY,
words INTEGER,
owner INTEGER,
FOREIGN KEY (words) REFERENCES words(word_id),
FOREIGN KEY (owner) REFERENCES users(user_id)

since a dictionary will have multiple words, should i create multiple records sharing the same owner id, but a different word?
or should i use an array and keep it to one record?

#

or a different way altogether?

finite oyster Apr 5, 2020, 5:48 AM

#

Thank you very much @acoustic silo!!!! I'll give that try.

wicked iris Apr 5, 2020, 6:30 AM

#

Hi everyone! I am a noob when it comes to databases (sqlite) and I need some help.
I have two tables, doctors and patients. Both have id column which is the primary key. I want to make a relationship between these tables so one record in doctors can be linked to multiple records in patients, in clients column in doctors for example. I could just enter patients' ids but I am sure there is a better practice for making such relationships. Any suggestions?

fresh zephyr Apr 5, 2020, 8:22 AM

#

This is where foreign keys come in help

#

What you have there is a one to many relationship (one doctor to many patients)

#

In order to solve this, you can add a column named treating_doctor (or whatever) to the patients table, that will contain the id of the doctor that has treated the patient

#

This will be a foreign key to the doctor id in the doctors table

#

@wicked iris

#

On a sidenote, I also need some help: I'm making a PyQt5 Gui for a sqlite database.

PyQt works on oop principles, so in order to connect and use a sqlite connection and cursor object, I have to pass them to the class that I instantiate

#

Something like this:

self.cur = self.con.cursor()
books_widget = Books(self.cur, self.books_ui)

#

I also pass that cursor to other dialog classes that I instantiate in the Books class

#

Now, I want to be able to change the sqlite database that I connect to

#

Which, I can easly do by:

self.con = sqlite3.connect(other_db_path) etc```

#

The problem is that this doesn't propagate to the classes instantiated in the Books class (the connection and cursor object remain the same, they don't update to the new ones that I create)

#

Any way I can change this?

solid void Apr 5, 2020, 8:59 AM

#

@latent berry multiple records sharing the same dictionary id, and multiple dictionaries sharing an owner id.

#

@fresh zephyr id'd make a DB class that holds the cursor, and pass that one to everything, so if you change the cursor in it, everything has the updated version through that object

latent berry Apr 5, 2020, 9:03 AM

#

beautiful, thanks!

solid void Apr 5, 2020, 9:03 AM

#

Almost every problem in program design can be solved by adding a level of indirection 😋

fresh zephyr Apr 5, 2020, 10:00 AM

#

@solid void So I just instantiate that DB class every time I need a cursor object?

#

The current DB path is stored in a Qsettings class, which stores it on the hard drive, so I can access it from anywhere I want

solid void Apr 5, 2020, 10:31 AM

#

no, you instanciate that DB class once like you do with your cursor now, and you pass that object to everything instead of the cursor

#

on the other hand, it might be a better idea to actually create/use/close the cursor everytime you need it

fresh zephyr Apr 5, 2020, 11:37 AM

#

Could you give a little code example?

solid void Apr 5, 2020, 11:53 AM

#

class DB:
    con = None
    def open(url):
        if self.con:
            self.con.close()
            self.cur = None
        self.con =  sqlite3.connect(url)

    def close(self):
        if self.con:
            self.con.close()
            self.con = None

    @property
    def cursor(self):
        # maybe even build a contextmanager to automatically commit/rollback depending on exeptions or not
        if not self.con:
            raise Exception("no db open")
        return self.con.cursor()

then later ```py
self.db = DB()
self.db.open(windows_db_path)
cur = self.db.cursor()

do stuff

cur.commit()

#

this way you can pass the db object around, and if needed, tell it to open another db, and as every reference point to the db object you created at the start, everything with that reference will have a connection to the correct db

hard notch Apr 5, 2020, 11:56 AM

#

I'm using sqlalchemy and want to abstract a relationship from several models into a mixin, but I can't figure out how the mixin class will know where to point the relationships to because I can't seem to pass a parameter from each of the models back to the mixin class. Any ideas?

fresh zephyr Apr 5, 2020, 11:56 AM

#

Right, thanks @solid void

solemn ridge Apr 5, 2020, 1:08 PM

#

If this is a method in a db model

#

Would the following work:

    def update(self):
        billing_agreement = BillingAgreement.find(self.subscribe_token)
        discord_id = int(billing_agreement.description.split('ID:')[1])
        self.subscribe_date = int(dateutil.parser.parse(billing_agreement.start_date).timestamp())
        db.session.add(self)
        db.session.commit()

clever topaz Apr 5, 2020, 1:13 PM

#

@solemn ridge Try it. Not sure if the self updating will work - it should. However, that's not the correct way to update objects in a DB.

solemn ridge Apr 5, 2020, 1:14 PM

#

Why not though?

#

I would do

current_user = User(attributes here)
#change w/e I want
db.session.add(current_user)
db.session.commit()

#

I dont find how that is any different from just changing everything I want

#

and then just doing

#

current_user.update()

clever topaz Apr 5, 2020, 1:17 PM

#

Updating after it's added to the DB?

solemn ridge Apr 5, 2020, 1:17 PM

#

Yea

clever topaz Apr 5, 2020, 1:19 PM

#

Updating is different.

solemn ridge Apr 5, 2020, 1:19 PM

#

I usually do:

current_user = Users.query.filter_by(filterhere).first()
current_user.name = ' a change here'
db.session.add(current_user)
db.session.commit()

When I could do

current_user = Users.query.filter_by(filterhere).first()
current_user.name = ' a change here'
current_user.update()

clever topaz Apr 5, 2020, 1:19 PM

#

No. Django or Flask?

#

/SQLAlchemy?

solemn ridge Apr 5, 2020, 1:19 PM

#

SQLAlchemy actually

clever topaz Apr 5, 2020, 1:20 PM

#

Should be:

    def update(self):
        billing_agreement = BillingAgreement.find(self.subscribe_token)
        discord_id = int(billing_agreement.description.split('ID:')[1])
        self.subscribe_date = int(dateutil.parser.parse(billing_agreement.start_date).timestamp())
        db.session.commit()

No need to add.

#

Because it's already present in the DB.

solemn ridge Apr 5, 2020, 1:20 PM

#

Hmm

#

Sure I guess

clever topaz Apr 5, 2020, 1:21 PM

#

Can you try it?

solemn ridge Apr 5, 2020, 1:21 PM

#

Ofc

#

Works

clever topaz Apr 5, 2020, 1:24 PM

#

Great!

solemn ridge Apr 5, 2020, 1:24 PM

#

Thanks mate

clever topaz Apr 5, 2020, 1:25 PM

#

No problem. Very interesting way of updating!

solemn ridge Apr 5, 2020, 2:36 PM

#

Anyone know how this is possible? user.subscribe_date prints correctly but what gets saved in the db is different

#

@app.route('/paid')
def user_paid():
    token = request.args['token']
    billing_agreement_response = BillingAgreement.execute(token)
    billing_agreement = BillingAgreement.find(billing_agreement_response.id)
    discord_id = int(billing_agreement.description.split('ID:')[1])
    user = User_Model.query.filter_by(discord_id=discord_id).first()
    user.subscribed = 1
    user.subscribe_date = time.time()
    print(user.subscribe_date)
    user.subscribe_token = billing_agreement_response.id
    db.session.commit()
    return redirect(url_for('home'))

#

This is what gets in the db

#

📎 unknown.png

#

📎 unknown.png

#

What gets printed

clever topaz Apr 5, 2020, 3:21 PM

#

@solemn ridge If the type is int then try int(time.time())

solemn ridge Apr 5, 2020, 3:21 PM

#

Ok

#

Ty

clever topaz Apr 5, 2020, 3:35 PM

#

@solemn ridge Did that work?

solemn ridge Apr 5, 2020, 3:36 PM

#

No unfortunately. I am saving it a string and then parsing it as an int afterwards

rapid bluff Apr 5, 2020, 7:19 PM

#

I'm working on a django IOT related project, I'm having a few database scaling questions. Lets say that I have 30 ardunios or raspberry pi's sending their data to a django backend via a post request. I want to retrieve the latest data recorded by the devices, what would be a appropriate database design for such a situation

solid void Apr 5, 2020, 8:28 PM

#

well, it depends on how often they send data, how big the data is, and how much history you want to keep

torn sphinx Apr 6, 2020, 1:05 AM

#

hi

#

im using asyncpg and i having a little problema here

#

i have a table with a column named extra, which is jsonb type

#

every row of this column has args key

#

i wanna select the rows which have an specific value inside that json key

#

how can i do that?

#

ping me if someone respond

torn sphinx Apr 6, 2020, 4:25 AM

#

hi.. is Fetch first n rows better than Limit in terms of performance?

cerulean pendant Apr 6, 2020, 4:39 AM

#

which database?

torn sphinx Apr 6, 2020, 4:39 AM

#

postgresql

cerulean pendant Apr 6, 2020, 4:39 AM

#

limit should be better

torn sphinx Apr 6, 2020, 4:40 AM

#

ok.. also, I am having trouble understanding how to form queries when handling string type that's representing days of the week

#

for example, how do I find interval between two columns when they represent days like Monday and Thursday

alpine ibex Apr 6, 2020, 5:10 AM

#

Anyone have experience with stock data libraries?

#

Im tryna work on some pattern recognition with stock and Forex data.

drowsy grove Apr 6, 2020, 8:35 AM

#

Hello, I'm trying to apply the VACUUM method on my db file located in /<home>/plugins/dynmap/dynmap.db on my ftp server which has the port 21, but I'm not sure how to do that. I tried:```Python
import sqlite3 as sq
from ftplib import FTP

with FTP("fr61.server.pro", "41016", "my password", 21) as ftp:
ftp.cwd("/plugins/dynmap/")
with sq.connect("dynmap.db") as db:
c = db.cursor()
c.execute("VACUUM")
db.commit()```But that only creates a new db file in my script's folder. By the way, the file dynmap.db is very big (9Go), I don't know what is the appropriate method for such a file.

glad bobcat Apr 6, 2020, 10:31 AM

#

Hello friends. I have a question for SQLAlchemy wizards.
I have a field in an SQLAlchemy class that’s defined as a non-enforced relationship:

game = relationship('Game', foreign_keys=[game_id, platform_id],
                    primaryjoin='and_(Game.game_id == EsportsGame.game_id, '
                                ' Game.platform_id == EsportsGame.platform_id)')

This makes it so the child doesn’t require a foreign key.

But when I try a session.merge(), this crashes because it handles duplicates poorly.
As I actually don’t want to check this object, is it possible to only merge on specific fields in SQLAlchemy?

solemn ridge Apr 6, 2020, 3:50 PM

#

Is there a way I can get all rows in a table sorted based on a column?
Basically I have this:
A table which has 3 columns id which is the default key, username, points. I want to get all rows sorted by points

#

How could I do that?

solemn ridge Apr 6, 2020, 4:31 PM

#

Apparently they are treated as a list of objects.this did the trick:

        new_users = sorted(users, key=lambda k: k.points, reverse=True)

torn sphinx Apr 6, 2020, 4:43 PM

#

hi

#

what's wrong with my query

#

select city, len(city) 
from station
where len(city) IN
(
select max(len(city)) as max_len, min(len(city)) as min_len
from station
)

torn sphinx Apr 6, 2020, 5:13 PM

#

I had to rewrite it like this to make it work:

#

(SELECT city, length(city) FROM station ORDER BY length(city), city
limit 1)
UNION
(SELECT city, length(city) FROM station ORDER BY length(city) DESC, city
limit 1)

#

but I don't understand why my previous one didn't work.. with the WHERE IN

torn sphinx Apr 6, 2020, 5:32 PM

#

Can anyone here help me with PonyORM ?

delicate fieldBOT Apr 6, 2020, 5:33 PM

#

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

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

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

willow raptor Apr 6, 2020, 6:09 PM

#

anyone here is good w ith SQL?

#

        self.cursor.execute(
            'UPDATE server SET no_active_voice =:time WHERE userID = :user_id AND serverID =:server_id;',
            data)

        # will be used to calculate how long the user has been in call and not muted
        self.cursor.execute(
            '''UPDATE server SET total_voice_time =(no_active_voice-active_voice)+total_voice_time, no_active_voice =0,active_voice=0
            WHERE userID = :user_id AND serverID =:server_id;''',
            data)

        self.cursor.execute('''UPDATE server SET 
                streaked = CASE WHEN total_voice_time >= voice_threshold THEN 1  ELSE 0 END
             WHERE userID = :user_id AND serverID =:server_id;
        ''', data)

        self.cursor.execute('''UPDATE server SET
        lastStreakDay = CASE WHEN streaked = 1 THEN :date ELSE lastStreakDay END
             WHERE userID = :user_id AND serverID =:server_id;

        ''', data)

#

i got this

#

i got 4 different execute

#

was wondering if it's possible to reduce to 1 or 2

#

basically i need it to update the table first

#

then read that value to compute for next condition

warped frigate Apr 6, 2020, 6:24 PM

#

@willow raptor oh hi yes I'm pretty good with SQL ORLY

willow raptor Apr 6, 2020, 6:25 PM

#

loool

warped frigate Apr 6, 2020, 6:26 PM

#

(to anyone else, I'm joking because Hunter has been teaching me SQL)

willow raptor Apr 6, 2020, 6:28 PM

#

GWcmeisterPeepoLove

hard notch Apr 6, 2020, 7:30 PM

#

I want to separate my large models.py into separate model-specific files, but don't know how to structure it in a way to prevent circular imports due to some models working with other models.

Googling only gets me info about trying to deal with app or db instance imports. Can someone point me in the right direction?

calm cave Apr 6, 2020, 7:41 PM

#

I am getting A Database Locked error. I am using Sqlite3 . I don't want to change Databases . Would like to know a way to deleted old entries .

gentle sparrow Apr 6, 2020, 8:02 PM

#

Can someone tell me the core elements of a modern authentication system for a social media

#

And also making it easier to find users in your database with not just their username

#

But their id number

torn sphinx Apr 7, 2020, 12:28 AM

#

hi

#

I have trouble understanding why there's IN here

#

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM Employee
INNER JOIN Department ON Employee.DepartmentId = Department.Id
WHERE (DepartmentId , Salary) 
    IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
 )

#

isn't the inner select enough to get the result

#

ok I see it now, Where isn't necessary, they could've joined the department on id, and just did the select

wicked iris Apr 7, 2020, 7:28 AM

#

Hi, I need some help with making a trigger in SQLite.

My tables look something like this:

ITEMS
id   total   available
1    100     50
2    200     180
3    300     240

ITEMS_IN_USE
item_id   amount
1         30
1         20
2         15
2         5
3         60

I want my available field to be updated everytime an item is added or removed from ITEMS_IN_USE. Sum should be calculated for every item in ITEMS_IN_USE table, and then substracted from its total field, and the result should be put in available field. I tried with grouping and joins but I am a noob and it gets messy really fast. Any suggestions?

karmic zealot Apr 7, 2020, 10:30 AM

#

I want to store images in a MySql database. Right now I am storing the URL of the images. But I want to store the images it self as a BLOB. Is it a bad idea from performance point of view?

#

I am hosting it on a raspberry pi

#

I want to know what is the general practice in a real world application scenario.

clever topaz Apr 7, 2020, 10:37 AM

#

@karmic zealot Not good for many reasons. Some interesting ones outlined here by someone with proper experience:
https://stackoverflow.com/a/3751

Stack Overflow

Storing Images in DB - Yea or Nay?

So I'm using an app that stores images heavily in the DB. What's your outlook on this? I'm more of a type to store the location in the filesystem, than store it directly in the DB.

What do you thi...

solemn ridge Apr 7, 2020, 10:57 AM

#

@karmic zealot You could save them in base64

hard notch Apr 7, 2020, 11:13 AM

#

What's a better pattern, creating and committing new records from the view, or using a Model.create_new() type of static method and leave the db stuff all in the model? (Or some third option?)

torn sphinx Apr 7, 2020, 3:03 PM

#

how can i select a table row if a jsonb column has a value inside of a specific key?

glacial field Apr 7, 2020, 7:02 PM

#

connection = cx_Oracle.connect("student_ps", "oracle", "xxxxxx.20/oracle")

v_profesia = input("Dati profesia: ");

query="""SELECT stare_civila, profesia, sum(suma_solicitata) Total_suma_solicitata 
 FROM t_clienti_leasing where lower(profesia) like :p_profesia group by stare_civila, profesia """
p_profesia = '%' + v_profesia.lower() + '%'

df = pd.read_sql(query, con=connection)
print(df)

#

pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT stare_civila, profesia, sum(suma_solicitata) Total_suma_solicitata 
 FROM t_clienti_leasing where lower(profesia) like :p_profesia group by stare_civila, profesia ': ORA-01008: not all variables bound

#

How can i solve this error?

echo prawn Apr 7, 2020, 11:35 PM

#

Hello, I'm hosting an online event called "As a developer, how to choose the right database?" - From my experience as an SRE and backend developer, I've noticed that choosing a database is not necessarily easy for everyone. With my colleague Pierre, we propose a small webinar to give you an overview of what exists in SQL and NoSQL databases. We will cover MongoDB, PostgreSQL, Cassandra, Elasticsearch, Redis and others... If you are interested in participating you can register here: https://qovery.typeform.com/to/nL4kIs

How to choose the right database

Turn data collection into an experience with Typeform. Create beautiful online forms, surveys, quizzes, and so much more. Try it for FREE.

craggy pawn Apr 8, 2020, 6:15 AM

#

anybody happen to know if its possible to not print the [50 rows x 5 columns] part when using pandas?

vital jay Apr 8, 2020, 10:04 AM

#

What's generally considered the best way to store numpy arrays in databases?

#

I'm currently storing them in mongodb as arrays

torn sphinx Apr 8, 2020, 1:22 PM

#

can you show an example

#

@vital jay

vital jay Apr 8, 2020, 2:50 PM

#


class mongo_tensor_loader(Dataset):
    @staticmethod
    def create_from_config(config):
        return mongo_tensor_loader(config["Client"],config["Database"],config["Collection"],config["Datatype"],parameters = config["Parameters"])
    def __init__(self,Client,Database,Collection,DataType,parameters = None):
        self.Client= pymongo.MongoClient(Client)
        self.Database = self.Client[Database]
        self.Collection = self.Database[Collection]
        self.DataType = DataType
        if not DataType in DataType_Dict:
            raise ValueError('Datatype does not exist, cannot load')
        if parameters is None:
            self.parameters = DataType_Dict[self.DataType]['default']
        else:
            self.parameters = parameters
        self.KeyValue = DataType_Dict[self.DataType]['str_template'].format(*self.parameters.values())
    def __getitem__(self,idx):
        document = self.Collection.find_one({'idx':idx})
        entry = document[self.DataType][self.KeyValue]['Local']
        tensor = torch.tensor(entry)
        if DataType_Dict[self.DataType]['transpose_bool']:
            tensor.t_()
        return tensor
    def __len__(self):
        return self.Collection.count_documents({})

#

This is an attempt at making a mongodb dataloader in pytorch

#

it's slow though

#

I'm unsure why

#

the data in the mongodb database is arrays

#

I imagine it's because I'm using "find" instead of some sort of vectorized index but I do not know how to do that

vital jay Apr 8, 2020, 3:14 PM

#

nvm I apparently needed to index the idx entry which was trivial to perform in compass

torn sphinx Apr 8, 2020, 5:00 PM

#

I meant.. show the data, but ok

#

lol

torn sphinx Apr 8, 2020, 6:25 PM

#

I have a table that stores the skills of characters. Now, each skill has upgrades, so I thought of separating those upgrades in a separate row, something like:

skill_id
attack
upgraded_id

Now, if I were to query a skill and I want to query also it's upgraded version, how can I get them both? (I'm returning the results as JSON data)
In "pseudo-code" it'd be: fetch skill, if skill has an upgraded version (upgraded_id) then grab that too

solid void Apr 8, 2020, 7:07 PM

#

i think postgres has an extension to use self-joins to build trees like this, when a row can refer to another row (and so on) and you want the list of them.

#

otherwise maybe it's possible to build that with a recursive CTE.

torn sphinx Apr 8, 2020, 7:29 PM

#

    def get_xchat_banned(self, guild: discord.Guild) -> Tuple[List[discord.Member], List[discord.Member]]:
        guildid = guild.id
        lst = self.cursor.execute("select * from crosschat where guild=?", (guildid,)).fetchall()
        glob = self.cursor.execute("select * from crosschat where guild=?", (guildid,)).fetchall()
        guild_banned = [guild.get_member(i) for i in lst if i]
        global_banned = [guild.get_member(i) for i in glob if i]
        return [guild_banned, global_banned]

    @commands.command(aliases=["gxcb"])
    async def getxcbanned(self, ctx: commands.Context):
        """
        Get people banned from crosschatting in the current guild
        """
        gu, gl = self.db.get_xchat_banned(ctx.guild)

        embed = discord.Embed(title="People banned from using crosschat")
        print(gl)
        print(gu)
        embed.add_field(name="Server banned",
                        value=[u.mention for u in gu] if gu != [] else "None")
        embed.add_field(name="Global banned",
                        value=[u.mention for u in gl] if gl != [] else "None")
        await ctx.send(embed=embed)

#

i'm using sqlite

#

📎 unknown.png

#

there is data in the db

#

and the print statements are both [None]

#

if i do this for a guild that has no entries in the database, it does the expected and gu and gl are None

torn sphinx Apr 8, 2020, 9:10 PM

#

Please help me MongoDB users, How can i remove {"test1": 0} from the list? (Note that, the value 0 can be any number:
Database:

{
  "_id": 59127491824124124,
  "thelist": [{"test": 0}, {"test2": 2}, {"test1": 0}]
}

i used database.update_one({"_id": 59127491824124124}, {"$pull": {"test1"}}) but it doesn't work

torn sphinx Apr 8, 2020, 10:18 PM

#

@solid void https://www.postgresqltutorial.com/postgresql-self-join/ This?

PostgreSQL Self-join By Practical Examples

This tutorial shows you how to use the PostgreSQL self-join technique to join a table to itself.

gentle sparrow Apr 8, 2020, 10:21 PM

#

Does anyone know if it would be practical for me to take the admin authentication system for django and use that authentication system for my actual users

#

Instead of just admins

#

Obviously I would be making tweaks but yea

solid void Apr 8, 2020, 10:34 PM

#

@torn sphinx no, that's pretty normal stuff, i meant https://www.postgresql.org/docs/12/ltree.html

wet sable Apr 9, 2020, 6:48 AM

#

I'm not sure if this is the right channel to ask but!! What data visualization tool can I use if I want to graph a graph with nodes and edges? Basically I have a few different types of companies and their addresses and I want to see which companies have the same address visually. I already have a list of edges (addresses that match) on an excel sheet :o

(preferably interactive as in, when I click on a node it tells me what that is)
(also preferably lets me to color the nodes so i can differentiate between the diff types of companies)

prime steppe Apr 10, 2020, 12:39 AM

#

@wet sable Graphviz

magic solstice Apr 10, 2020, 1:51 AM

#

@gentle sparrow You can do whatever fits your requirement. I have done this in a couple projects earlier (using flask/Flask-admin and Django).

fossil swan Apr 10, 2020, 2:39 AM

#

Tryna get started with databases. Is there easy module in python 3 where you can store data on a server? Kind of a bad question but if anyone could help that would mean the world.

gentle sparrow Apr 10, 2020, 2:57 AM

#

Alright thanks pydev

torn sphinx Apr 10, 2020, 8:32 AM

#

    "_id": 1359185710985371235,
    "main": 2,
    "streamers": [{"name": "me", "count": 1},{"anothername", "count": 0}]
}

hey, I have a problem with mongodb and pymongo so basically i want to edit "count" inside of "streamers" . like i want to change count of "name": "me", "count": 1 to "name": "me", "count": 3How can i do it?

#

Please answer if you know mongodb because it is possible in json and others but it seems like it is impossible for mongodb

#

or if it is possible, please tell with console command

supple fox Apr 10, 2020, 8:52 AM

#

Im building a table of quotes for a discord bot in Postgresql.
I want users to be able to retrieve the quotes via a index.
But im having bit of a problem with that.
The easiest solution is to use the built in serial to keep a index of the the quotes.
However then i get problems with gaps in the index if i want to remove a quote from the table.
What's the best way to go about this?
Should i loop trough the entire table if i remove a row updating the entire table row by row by counting the rows and then resetting the serial?
Or should i just count them whenever i need to retrieve them by index?
Or is there a better way to do this?
The tables is probably not going to have more than 1k rows.

clever topaz Apr 10, 2020, 9:25 AM

#

@supple fox If you simply want to get the nth quote where n is the index that the user entered then you can select by row number rather than id - https://rextester.com/QDF48741

fresh zephyr Apr 10, 2020, 12:54 PM

#

Yo, what would be the correct approach: getting all the rows from a table and filtering them using python functions vs getting filtered data from a table, using the query: "SELECT * from table WHERE id = something"?

#

Right now I implemented the second one, but it kinda became troublesome, because I have to write lots of queries, if I apply multiple filtering parameters

#

This is an example:

#

            if self.date_check_box.isChecked():
                if student_id == '' and book_id == '':
                    self.cur.execute("SELECT * FROM borrows WHERE date LIKE ?", (date,))
                elif student_id == '':
                    self.cur.execute("SELECT * FROM borrows WHERE book_id = ? AND date LIKE ?", (book_id, date))
                elif book_id == '':
                    self.cur.execute("SELECT * FROM borrows WHERE student_id = ? AND date LIKE ?", (student_id, date))
            
            else:
                if student_id == '' and book_id == '':
                    self.cur.execute("SELECT * FROM borrows")
                elif student_id == '':
                    self.cur.execute("SELECT * FROM borrows WHERE book_id = ?", book_id)
                elif book_id == '':
                    self.cur.execute("SELECT * FROM borrows WHERE student_id = ?", student_id)```

#

            if self.date_check_box.isChecked():
                if student_id == '' and book_id == '':
                    self.cur.execute("SELECT * FROM borrows WHERE date LIKE ? AND status = ?", (date, 0))
                elif student_id == '':
                    self.cur.execute("SELECT * FROM borrows WHERE book_id = ? AND date LIKE ? AND status = ?", (book_id, date, 0))
                elif book_id == '':
                    self.cur.execute("SELECT * FROM borrows WHERE student_id = ? AND date LIKE ? AND status = ?", (student_id, date, 0))
            
            else:
                if student_id == '' and book_id == '':
                    self.cur.execute("SELECT * FROM borrows WHERE status = ?", (0,))
                elif student_id == '':
                    self.cur.execute("SELECT * FROM borrows WHERE book_id = ? AND status = ?", (book_id, 0))
                elif book_id == '':
                    self.cur.execute("SELECT * FROM borrows WHERE student_id = ? AND status = ?", (student_id, 0)) ```

topaz bison Apr 10, 2020, 4:03 PM

#

If possible utilize the database query system as it will be much faster. For most purposes it will be easier to write the queries using an ORM instead of pure SQL

#

Isnt that possible? @fresh zephyr

fresh zephyr Apr 10, 2020, 4:51 PM

#

Woooah, I didn't even know ORMs existed and that they're so cool

#

God this makes my job easier

#

Any great tutorials to start out? @topaz bison

#

I'm literally in the process of refactoring my whole pyqt5 app, because of not respecting solid principles and not knowing about ORMs (yay)

#

Guess SqlAlchemy is good enough for a local sqlite db, right?

topaz bison Apr 10, 2020, 4:56 PM

#

Any great tutorials to start out? @topaz bison
@fresh zephyr just Google sqalchemy

fresh zephyr Apr 10, 2020, 4:56 PM

#

Aight, thanks

soft maple Apr 10, 2020, 5:02 PM

#

I am new with python dbs, but I need to remove all instances with 0 in them from sqlite database. Where should I start?

supple fox Apr 10, 2020, 6:08 PM

#

@clever topaz Thank you for the help. That perfectly solved my problem. The example was very helpful.

craggy shore Apr 10, 2020, 6:35 PM

#

CREATE TABLE IF NOT EXISTS pygear.orders (
    order_id    SERIAL          PRIMARY KEY,
    user_id     BIGINT          NOT NULL,
    usd         SMALLINT        NOT NULL CHECK (usd > 0),
    description VARCHAR(120)    NOT NULL
);

CREATE TABLE IF NOT EXISTS pygear.payments (
    order_id    SERIAL          PRIMARY KEY,
    user_id     BIGINT          NOT NULL,
    usd         SMALLINT        NOT NULL DEFAULT 0
);

So I have 2 tables here where I want to sum the orders table's usd grouping by user_id, and subtract the payments grouped by id and I'm not sure how to do that

#

SELECT
    pygear.orders.user_id,
    SUM(pygear.orders.usd - COALESCE(pygear.payments.usd, 0)) as total
    FROM pygear.orders
        LEFT JOIN pygear.payments ON pygear.orders.user_id = pygear.payments.user_id
    GROUP BY pygear.orders.user_id;

#

I made this and I've been playing with it for a few hours now and it seems to be subtracting the same values multiple times

#

So I basically want to subtract these 2:

SELECT user_id, sum(usd) FROM pygear.orders GROUP BY user_id;
SELECT user_id, sum(usd) FROM pygear.payments GROUP BY user_id;

solid void Apr 10, 2020, 8:11 PM

#

i would do either a nested query, or a common table expression, to first compute these two values, for each user_id, and then compute the substraction of them (using a join on user_id)

#

WITH total_orders AS (SELECT user_id, sum(usd) as total FROM pygear.orders GROUP BY user_id),
     total_payments AS (SELECT user_id, sum(usd) as total FROM pygear.payments GROUP BY user_id)
SELECT
    total_orders.user_id,
    total_orders.total AS to_pay,
    total_payments.total AS paid,
    total_orders.total - total_payments.total AS due
FROM total_orders
JOIN total_payments
ON total_orders.user_id = total_payments.user_id;

#

(that's the CTE version)

#

@craggy shore

#

(added the two intermediate sums just for clarity, it's not necessary to select them, of course)

solemn ridge Apr 10, 2020, 8:48 PM

#

Hello, can someone explain how I can use pool from sqlalchemy.pool to prevent the MySQL server has gone away error?

#

I am having a problem maintaining long connections, I am reading the docs but they are not really explaining what pool_size and max_overflow

serene pawn Apr 11, 2020, 7:38 AM

#

I'm looking for someone experienced in mysql and apple text services, i am looking to learn/work on a project and could offer $10 per hour for help, i am looking for an epirenced mysql person and want to learn while they help me. Message me for more info, thanks. Zach.

sterile raft Apr 11, 2020, 5:48 PM

#

Ignoring exception in command tournament create:
Traceback (most recent call last):
  File "C:\Users\Aidfas\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\Aidfas\Desktop\Discord projects\Tournament manager\cogs\main.py", line 239, in create
    c.execute("INSERT INTO tournaments VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6})".format(name, teamCount, dateStr, channelid, prize, description, PerTeamInt])
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.``` What does this mean?

#

Like, i went through SO but i didn't find an answer...

timber dirge Apr 11, 2020, 5:51 PM

#

What's the type of name?

sterile raft Apr 11, 2020, 5:53 PM

#

date

#

I mean, i didn't understood your question

thick berry Apr 11, 2020, 5:54 PM

#

When you created the table, what 'type' did you give to your 0th variable (i.e. name)

timber dirge Apr 11, 2020, 5:54 PM

#

^

sterile raft Apr 11, 2020, 5:54 PM

#

text

timber dirge Apr 11, 2020, 5:54 PM

#

so you're trying to insert a date into a column specifically meant for text?

sterile raft Apr 11, 2020, 5:54 PM

#

No.

thick berry Apr 11, 2020, 5:55 PM

#

can you post the whole code?

sterile raft Apr 11, 2020, 5:55 PM

#

📎 unknown.png

#

These are the collumns

timber dirge Apr 11, 2020, 5:56 PM

#

you may have to convert whatever you have in name to the TEXT datatype, or sqlite won't accept it as a parameter

sterile raft Apr 11, 2020, 5:57 PM

#

I see, i mean people said date will work

#

Okay i'll try

thick berry Apr 11, 2020, 5:57 PM

#

@sterile raft can you post the surrounding code to line 239

sterile raft Apr 11, 2020, 5:57 PM

#

Sure.

#

if msg10.channel_mentions:
                s = msg10.channel_mentions
                channelid = [channel.id for channel in s]
                conn = sqlite3.connect("db/database.db")
                c = conn.cursor()
                c.execute("INSERT INTO tournaments VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6})".format(name, teamCount, dateStr, channelid, prize, description, PerTeamInt])
                conn.commit()
                conn.close()
                mchannel = self.bot.get_channel(channel)
                embed = discord.Embed(title=name, description="This information will be updated in 5 minutes or less.", color=0x000000)
                await mchannel.send(embed=embed)
                await ctx.send(f"Tournament has been successfully started! \U00002705 \nInformation about the tournament will get updated every 5 minutes in {mchannel.mention}")```

#

I don't think that's neccesary

#

But okay

#

Anything wrong?

thick berry Apr 11, 2020, 5:59 PM

#

why is there a ']' with perteamint

timber dirge Apr 11, 2020, 5:59 PM

#

okay @sterile raft i want you to try something real fast

sterile raft Apr 11, 2020, 6:00 PM

#

Forgot to remove, but that prooves that i tried a few methods from SO.

#

Sure, what?

timber dirge Apr 11, 2020, 6:00 PM

#

try replacing
c.execute("INSERT INTO tournaments VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6})".format(name, teamCount, dateStr, channelid, prize, description, PerTeamInt])
with
c.execute("INSERT INTO tournaments VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6})".format(str(name), teamCount, dateStr, channelid, prize, description, PerTeamInt])

sterile raft Apr 11, 2020, 6:00 PM

#

Nono, name is a string

thick berry Apr 11, 2020, 6:01 PM

#

is name None before you are passing it?

sterile raft Apr 11, 2020, 6:02 PM

#

Uh, what?

#

No it's not a none

#

If it would be None it would not get to this part.

#

I mean, you can't send a discord message which is None.

thick berry Apr 11, 2020, 6:05 PM

#

c.execute("INSERT INTO tournaments VALUES (?,?,?,?,?,?,?)",(str(name), int(teamCount), str(dateStr), int(channelid), str(prize), str(description), int(PerTeamInt)))

#

try this ^

sterile raft Apr 11, 2020, 6:06 PM

#

This was the thing that i tried except for the str int's etc.

#

Okay let me try it real quick

#

Also after int(PerTeamInt) i'm pretty sure you need a comma

#

Else it will give a syntax error

#

I'm not sure about it, but that's what i remember

thick berry Apr 11, 2020, 6:11 PM

#

Also there is no date type in sqlite3

sterile raft Apr 11, 2020, 6:12 PM

#

Okay, that's weird. you apperently can't use function objects

#

Like message.content

#

AttributeError: 'function' object has no attribute 'content'

#

That doesn't work, so let me fix up real quick

#

I'll mention you if something still not works, if you're fine.

thick berry Apr 11, 2020, 6:13 PM

#

yeah sure

sterile raft Apr 11, 2020, 6:22 PM

#

Thank you, it works now.

#

I'm just not sure if it will save.

#

Cause it seems everytime i restart my computer the data just doesn't exist anymore.

#

Oh look it saved!

thick berry Apr 11, 2020, 6:26 PM

#

Congrats!

sterile raft Apr 11, 2020, 6:26 PM

#

Thank you alot, i was tryin got fix the errors with SQLite for atleast 2 days.

#

Finally, oh my damn god.

#

@thick berry Also i got one question, how do i iterate through a fetched result?

#

I forgot.

#

Oh yes, i remembered.

#

i think.

#

You have to loop over rows, right?

thick berry Apr 11, 2020, 6:31 PM

#

for row in c:

#

iirc

sterile raft Apr 11, 2020, 6:31 PM

#

Okay

#

Thanks

thick berry Apr 11, 2020, 6:31 PM

#

np

sterile raft Apr 11, 2020, 6:32 PM

#

Will it iterate through the loop by order?

#

Like name then teamint

#

etc

thick berry Apr 11, 2020, 6:34 PM

#

row is just a tuple

sterile raft Apr 11, 2020, 6:34 PM

#

Yes i understand.

#

I'm using a loop to get information out of the tuple

#

And appending it into a list

thick berry Apr 11, 2020, 6:35 PM

#

you could append the whole row

sterile raft Apr 11, 2020, 6:36 PM

#

You misunderstood me, I meant will it go in order as in the tuple?

#

Okay, nevermind i found out

raw geyser Apr 11, 2020, 10:27 PM

#

hello

#

Any SQLite users?

clever topaz Apr 11, 2020, 11:39 PM

#

@raw geyser Yes. Go ahead.

raw geyser Apr 12, 2020, 12:05 AM

#

do you pref using sqlalch or sqlite3 when calling in py

#

have yet to use either

#

want to start with whatever is the most dynamic

torn sphinx Apr 12, 2020, 2:16 AM

#

What do you mean with "dynamic"? sqlalch is an ORM which can use multiple db-backends, sqlite being one of them. sqlite3 is not an ORM, but gives you mostly a raw sql interface to only sqlite databases.

#

If you describe your use-case more precisely, we can help.

raw geyser Apr 12, 2020, 4:19 AM

#

I am trying to do a lot with it long term - but right now - I am really just using it as a call to import data to my pandas DF, so I can do reporting/analytics in pandas.

torn sphinx Apr 12, 2020, 10:07 AM

#

    idx = buf.index(end)
ValueError: subsection not found

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/kali/.local/lib/python3.8/site-packages/mysql/connector/connection.py", line 105, in _do_handshake
    handshake = self._protocol.parse_handshake(packet)
  File "/home/kali/.local/lib/python3.8/site-packages/mysql/connector/protocol.py", line 196, in parse_handshake
    (packet, res['auth_plugin']) = utils.read_string(
  File "/home/kali/.local/lib/python3.8/site-packages/mysql/connector/utils.py", line 274, in read_string
    raise ValueError("end byte not present in buffer")
ValueError: end byte not present in buffer

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "battlebot.py", line 26, in <module>
    char_db = mysql.connector.connect(
  File "/home/kali/.local/lib/python3.8/site-packages/mysql/connector/__init__.py", line 179, in connect
    return MySQLConnection(*args, **kwargs)
  File "/home/kali/.local/lib/python3.8/site-packages/mysql/connector/connection.py", line 95, in __init__
    self.connect(**kwargs)
  File "/home/kali/.local/lib/python3.8/site-packages/mysql/connector/abstracts.py", line 716, in connect
    self._open_connection()
  File "/home/kali/.local/lib/python3.8/site-packages/mysql/connector/connection.py", line 207, in _open_connection
    self._do_handshake()
  File "/home/kali/.local/lib/python3.8/site-packages/mysql/connector/connection.py", line 107, in _do_handshake
    raise errors.InterfaceError(
mysql.connector.errors.InterfaceError: Failed parsing handshake; end byte not present in buffer

#


@bot.command()
async def characters(ctx):
    char_db_cursor.execute('SELECT COUNT(*) FROM characters')
    result = char_db_cursor.fetchall()
    await ctx.send(f'Total Characters: '+ result)

#

Please someone help me it's 2 days that i'm looking for a fix

#

i'm getting mad about this

void otter Apr 12, 2020, 11:15 AM

#

i have event object which represents events in discord server(msg edits, msg deletions, invitations being sent...)
and i have to reference the user who is executing them
what would be more logical, event.creator or event.author ?

lapis belfry Apr 12, 2020, 3:16 PM

#

I'd go for author, Idk why but seems better

copper hornet Apr 12, 2020, 4:09 PM

#

I need help -.-

#

if record == "None":
                mydb = connectdb()  
                cursor = mydb.cursor()
                cursor.execute("INSERT INTO Users (UserID, Money, Xp, Status) VALUES ('%s', '0', '0', '0');" % ctx.author.id)
                record = cursor.fetchone()
                print(record)
                cursor.close()
                mydb.close()

#

this doesn't work 😄

clever topaz Apr 12, 2020, 5:15 PM

#

@BoneClaw#9332 You are trying to add a string and rows (a list of tuples IIRC), which cannot be done.

#

@copper hornet What do you mean by "doesn't" work? Gives an error or just does nothing?

jade blaze Apr 12, 2020, 5:20 PM

#

I'm trying to work with PostgreSQL (Psycopg2) and I wonder if I'm doing something wrong, when I cur.fetchone() I get back a tuple of tuples, which seems a bit odd to me
Figured it would give me back a tuple, not a tuple of tuples

This is the query: cur.execute("SELECT (player, achievement, date) FROM Achievements WHERE date::date >= %s;", (datetime_now_minus_10,))

#

Is this normal behaviour or not really?

#

It's not really an actual problem but it seems a bit annoying if I have do to:

row = cur.fetchone()
    while row:
        print(row[0])
        user = row[0][0]
        
    ``` for example

#

Oh I see, it has to do with my SELECT query, I could just do SELECT player, achievement, date

#

instead of putting brackets around them, writing it out helped my brain lol

#

thanks regardless 😅

jade blaze Apr 12, 2020, 5:41 PM

#

Must say either I'm blind or the docs aren't too great, I keep having to find a random website where something is described to see what some posibilities are
Chances are very high it's just me

copper hornet Apr 12, 2020, 7:55 PM

#

@clever topaz it just doesn't do anything.

clever topaz Apr 12, 2020, 7:58 PM

#

@copper hornet Perhaps record isn't "None"? I think you meant:

if record == None:

None is an object - and does not equal "None", a string.

torn sphinx Apr 12, 2020, 8:19 PM

#

@raw geyser Well, then just use whatever you feel okay with for now and refactor later depending on your needs. Don't overthink small projects

lament heath Apr 12, 2020, 8:51 PM

#

whats up guys .... Im trying to create a sign up page

#

but this error is keep showing up sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: user.name [SQL: INSERT INTO user (name, email) VALUES (?, ?)] [parameters: (None, None)] (Background on this error at: http://sqlalche.me/e/gkpj)

#

here's the db class

#

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

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

#

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

        else:
            
            user= User(form.username.data,form.email.data,form.password.data)
            print(user)
            db.create_all()
            db.session.add(user)
            db.session.commit()
            print("Saved")
            #flash('Thanks for registering')
            return redirect(url_for("home"))```

#

and the error is happening when db.session.commit()

#

is executed

#

Im using flask sqlalchemy

lament heath Apr 12, 2020, 9:14 PM

#

It worked

#

the problem was that there's a wrong on the db file path

#

sqlite:////tmp/main.db

#

so i changed to this sqlite:///main.db

lament heath Apr 12, 2020, 9:53 PM

#

How to see the data inside the data bases file ?

#

using flask

solid void Apr 12, 2020, 10:45 PM

#

since it's an sqlite db, you can just open it with the sqlite3 command line tool, and run queries directly, you can test your sql code easily this way

upbeat lily Apr 13, 2020, 12:11 AM

#

If you're using pycharm pro, you can add it as a "data source" (or something, don't remember the exact name), and it gives you a nice GUI for seeing query results and a window where you can write/run SQL

vague hawk Apr 13, 2020, 2:40 AM

#

Hey, I was hoping someone could point me in a direction for a project I am doing for my job. I am trying to replace our old timeclock with a python program that will allow us to clock, in, out, and log our lunch. What would be the best way of doing this? I am in Code/Help VC

#

I have a lot coded already but theres one key thing I'm missing

celest zodiac Apr 13, 2020, 2:41 AM

#

OK

#

what's missing?

celest blaze Apr 13, 2020, 2:44 AM

#

it almost certainly needs to be a web app

vague hawk Apr 13, 2020, 2:45 AM

#

Well, I have a lister of usernames that can then get to the functionality of loggin in, out and lunch, but, what is the best way if keeping track? in other words, when they go to log in again, I need to reference where they have logged in before and say youve logged in before, or if they try to log out, but havent logged in, i need to have that captured too, Im currently using a text file and adding to each line after i add their username to a line, but this is proving to be HORRIBLE I'm thinking about dictionaries next, but I'm not familiar with how to save them/load them, besides pickle.

celest zodiac Apr 13, 2020, 2:45 AM

#

A database is the best next step

#

For something where you don't have a whole lot of concurrent use, SQLite will do fine

#

and yes, a web interface for it will make it easier for people to connect with it and clock in/out

vague hawk Apr 13, 2020, 2:46 AM

#

sqlite? is that a module?

celest zodiac Apr 13, 2020, 2:46 AM

#

SQLite is a database that comes as a module with Python

#

It's also its own thing, but Python includes it by default

vague hawk Apr 13, 2020, 2:46 AM

#

https://gist.github.com/guitarbenjamin/c2bbb3114e833e2def2352369f781f59

Gist

timeclockforwork1

GitHub Gist: instantly share code, notes, and snippets.

#

that is my code so far, and it is just getting rediculous trying to reference a text file lol

#databases

Then when you are finished -->

No closing``` essentially, when you call commit() you lock the database, write the change, then unlock it. so its marginally (and i mean marginally) better to avoid that if you will be doing it in certain scenarios (like yours).

do stuff

No closing``` essentially, when you call `commit()` you lock the database, write the change, then unlock it. so its marginally (and i mean marginally) better to avoid that if you will be doing it in certain scenarios (like yours).