#databases | Python | Page 73

harsh pulsar Sep 25, 2019, 2:46 PM

#

which?

torn sphinx Sep 25, 2019, 2:46 PM

#

no wait

harsh pulsar Sep 25, 2019, 2:46 PM

#

it might actually not add

torn sphinx Sep 25, 2019, 2:46 PM

#

the umm ID and member id stuff

harsh pulsar Sep 25, 2019, 2:46 PM

#

you might need a transaction

torn sphinx Sep 25, 2019, 2:47 PM

#

let me get my new code

#

https://paste.pydis.com/otupegahof.py

#

there!

#

📎 unknown.png

#

It did not even add the things to the table :d

#

I do not know what a 'transaction' is :d

harsh pulsar Sep 25, 2019, 2:55 PM

#

@torn sphinx do you see the data in db browser?

torn sphinx Sep 25, 2019, 2:55 PM

#

YES

#

EMPTY

#

BUT

harsh pulsar Sep 25, 2019, 2:55 PM

#

ok

torn sphinx Sep 25, 2019, 2:55 PM

#

the '?'s are added

harsh pulsar Sep 25, 2019, 2:55 PM

#

did you use the latest code i sent?

#

it shouldnt add ?s

torn sphinx Sep 25, 2019, 2:55 PM

#

You added them..

harsh pulsar Sep 25, 2019, 2:55 PM

#

no

#

hold on

torn sphinx Sep 25, 2019, 2:56 PM

#

No I mean

#

fck

harsh pulsar Sep 25, 2019, 2:56 PM

#

back up

torn sphinx Sep 25, 2019, 2:56 PM

#

wait

harsh pulsar Sep 25, 2019, 2:56 PM

#

what do you see in the database

torn sphinx Sep 25, 2019, 2:56 PM

#

upsert_query = "insert into Staff_List (Server_ID, Member_ID) values(?, ?)"

#

this is not updated

#

is what I meant

harsh pulsar Sep 25, 2019, 2:56 PM

#

stop

#

pause

torn sphinx Sep 25, 2019, 2:56 PM

#

yes,sir

harsh pulsar Sep 25, 2019, 2:56 PM

#

delete your old database file

#

start over

torn sphinx Sep 25, 2019, 2:56 PM

#

I did

harsh pulsar Sep 25, 2019, 2:56 PM

#

ok

torn sphinx Sep 25, 2019, 2:56 PM

#

harsh pulsar Sep 25, 2019, 2:56 PM

#

what do you see when you open it in db browser

torn sphinx Sep 25, 2019, 2:57 PM

#

I see this,sir:

#

📎 unknown.png

harsh pulsar Sep 25, 2019, 2:57 PM

#

ok good

#

let me try a quick fix

torn sphinx Sep 25, 2019, 2:57 PM

#

Appreciated

harsh pulsar Sep 25, 2019, 2:58 PM

#

a "transaction" is something like a collection of actions

torn sphinx Sep 25, 2019, 2:59 PM

#

🤔

harsh pulsar Sep 25, 2019, 2:59 PM

#

you need to commit the transaction in order for them to be saved to the database

torn sphinx Sep 25, 2019, 2:59 PM

#

oh

harsh pulsar Sep 25, 2019, 2:59 PM

#

i forgot that sqlite3 in python does not automatically commit inserts

torn sphinx Sep 25, 2019, 2:59 PM

#

yes

harsh pulsar Sep 25, 2019, 2:59 PM

#

https://paste.pydis.com/avunabotov.py

torn sphinx Sep 25, 2019, 2:59 PM

#

Oh,I did too 😂

harsh pulsar Sep 25, 2019, 2:59 PM

#

see the change on lines 39-40

#

i added it on 162 as well, but i think it's unnecessary

#

since the table is created correctly

#

so you can probably delete line 162 and un-indent the stuff that was inside the block

#

but i think the change on line 39 was necessary

#

i've made that mistake before too (forgetting to commit)

torn sphinx Sep 25, 2019, 3:00 PM

#

There is no 'commit' on line 39 🤔

#

or 40

#

did you send my the old code?

harsh pulsar Sep 25, 2019, 3:03 PM

#

no

#

the extra with

torn sphinx Sep 25, 2019, 3:03 PM

#

oooh

harsh pulsar Sep 25, 2019, 3:03 PM

#

https://docs.python.org/3/library/sqlite3.html#using-the-connection-as-a-context-manager

torn sphinx Sep 25, 2019, 3:04 PM

#

that makes much sense

#

now that I know ..

#

cool

#

SUCCESS!!!!

#

WE LANDED ON THE MARS

harsh pulsar Sep 25, 2019, 3:08 PM

#

it worked?

torn sphinx Sep 25, 2019, 3:09 PM

#

yes!

#

now how do I delete? I am confused,but I have already one code?

#

updelete_query = "delete from Staff_List (Server_ID, Member_ID) values(?, ?)"

#

This should delete the row,right?

#

or updelete_query = ("delete from Staff_List where Server_ID = ?, Member_ID = ?")

harsh pulsar Sep 25, 2019, 3:23 PM

#

oh

#

the latter

#

also you will need to do the with db thing again

#

i think insert and delete need to have a transaction

lusty igloo Sep 25, 2019, 4:10 PM

#

There shouldn't be much trouble if I use formatted strings while updating database?

torn sphinx Sep 25, 2019, 4:45 PM

#

thanks

#

I shall try that now

#

YEEEEE

#

Oh my Lord,I am so happy

#

But there is a bug.. here the code

#

https://paste.pydis.com/nutuvexuge.py

#

When I try to remove the member after I removed it after it already being in the database

#

I get the same message again:
The member has been removed from the Staff list.

#

Instead of:

This member is not yet on the list.```

#

Everything else works perfectly! 😄 ❤

#

Maybe the exception type is wrong :/

proven wagon Sep 25, 2019, 5:02 PM

#

I dont know the terminology around this but, does this look correct? py await pool.execute('TRUNCATE tablename WHERE id = $1', id)

torn sphinx Sep 25, 2019, 5:02 PM

#

sorry bud,me = sqlite3 here :/

proven wagon Sep 25, 2019, 5:03 PM

#

ok well does that stop everyone else from being able to reply? ❤

torn sphinx Sep 25, 2019, 5:04 PM

#

No 😂

proven wagon Sep 25, 2019, 5:09 PM

#

Should i use Truncate or Delete?

harsh pulsar Sep 25, 2019, 5:26 PM

#

@proven wagon delete

#

truncate is specifically for clearing the whole table

#

delete is for deleting records

proven wagon Sep 25, 2019, 5:26 PM

#

so "DELETE FROM table WHERE id = $1", id

harsh pulsar Sep 25, 2019, 5:26 PM

#

yeah

proven wagon Sep 25, 2019, 5:26 PM

#

Alright thx

harsh pulsar Sep 25, 2019, 5:26 PM

#

well

#

 pool.execute('TRUNCATE tablename WHERE id = $1', (id,))

#

dont forget to wrap your parameters in a tuple or list

proven wagon Sep 25, 2019, 5:27 PM

#

I have never needed to do that? 👀

#

Just done this all the time py query = """INSERT INTO infractions (id, mod, target, command, reason, time, extra) VALUES ($1, $2, $3, $4, $5, $6, $7)""" await pool.execute(query, self.id, self.mod, self.target, self.command, self.reason, self.time, self.extra)

harsh pulsar Sep 25, 2019, 5:36 PM

#

oh

#

what library is this

#

asyncpg?

#

asyncpg has a nonstandard API

#

most other database libraries would require that you write

pool.execute(query, (self.id, self.mod, self.target, self.command, self.reason, self.time, self.extra))

#

frankly i like how they broke that standard

#

for all the things i do like about asyncpg

torn sphinx Sep 25, 2019, 6:04 PM

#

https://discordapp.com/channels/267624335836053506/342318764227821568/626461727822839809
Ples help,I = dumb

#

It make no sense :d it should work

proven wagon Sep 25, 2019, 6:06 PM

#

Late reply but yes, asyncpg

#

@torn sphinx before removing the member check if he actually is in the database

torn sphinx Sep 25, 2019, 6:08 PM

#

Why did I forget how to do that 🤦🏻 thanks

#

But this code so advance :d

#

No,I am dumb again

torn sphinx Sep 25, 2019, 6:49 PM

#

I fixed it finally 😄 I = has brain

long stream Sep 25, 2019, 7:25 PM

#

I'm trying to get better at databases and SQLAlchemy. I'd like to know how people tend to use an ORM like that found in SQLAlchemy. To me it seems rational to create a class that abstracts all the DB queries away from the client program completely. Is that a strange way or a common way to use an ORM?

#

As an example, imagine a DB that's modeling an Excel spreadsheet. I imagine you'd write an object that handles all the DB queries and sessions itself, and then lets you reference cells and their properties as if they were regular Python objects - maybe something like cell.row_idx. Is that how ORMs are used, or do client programs tend to issue queries to get what they want?

ember mist Sep 25, 2019, 8:33 PM

#

can website made in php and software made in python share same database? i mean on local server?

harsh pulsar Sep 25, 2019, 9:29 PM

#

sure

#

but having two applications sharing a database gets messy

#

regardless of what langauge they are using

unborn coyote Sep 25, 2019, 10:31 PM

#

when is it better to use PyMongo vs Mongoengine

torn sphinx Sep 25, 2019, 10:34 PM

#

hi

#

I have a question..

#

are database administrators like a thing..

#

I'm handling some data engineering aspects and need to treat some databases as sinks with auto-scaling and failover..

#

I'm wondering if we need DBA's for those.. because those sort of enterprise concepts are not clear to me

harsh pulsar Sep 25, 2019, 10:36 PM

#

yeah

#

that's specialized knowledge

#

maybe you dont need it full-time, but it's not something i'd expect a typical data engineer to know how to do

torn sphinx Sep 25, 2019, 10:43 PM

#

I dont need to know how to do it.. I'm just wondering if we need those people at all

#

like, if I deploy my solution to use db resources, do I need DBAs to monitor their health and handle incidents?

harsh pulsar Sep 25, 2019, 10:46 PM

#

eh

#

depends

#

how bad is it if the database goes down on saturday at 3 PM

torn sphinx Sep 25, 2019, 10:48 PM

#

hmm..

#

would a better solution be an active active kafka cluster that constantly replicates.. so if one fails, there's still the other one to access data from

#

I'm really not familiar with traditional analytical databases.. so not sure why there is a need to persist to a db, instead of just maintaining data on the cluster

harsh pulsar Sep 25, 2019, 10:55 PM

#

idk man

#

you know more about this than me, i will shut up

torn sphinx Sep 25, 2019, 11:35 PM

#

lol.. I really dont T.T

#

I have to get from point A to B.. and all my understanding and skills are fragmented..

#

I know streaming pipelines but not traditional db.. so not sure what to do.. I guess I have to find someone who knows both..

naive agate Sep 26, 2019, 1:00 AM

#

In pyspark, if you have a rdd of x number of tuples, how can you filter it, so its only n number of tuples

harsh pulsar Sep 26, 2019, 1:01 AM

#

@naive agate you just want the first n elements of an rdd?

naive agate Sep 26, 2019, 1:01 AM

#

yess

harsh pulsar Sep 26, 2019, 1:01 AM

#

my_rdd.take(n)?

naive agate Sep 26, 2019, 1:01 AM

#

but keep it in an rdd

harsh pulsar Sep 26, 2019, 1:01 AM

#

oh

naive agate Sep 26, 2019, 1:01 AM

#

like rdd2 =

#

anyway you know of?

harsh pulsar Sep 26, 2019, 1:02 AM

#

let me see

naive agate Sep 26, 2019, 1:02 AM

#

kk, ty

harsh pulsar Sep 26, 2019, 1:02 AM

#

ah

#

n = 1000

rdd2 = rdd1 \
    .zipWithIndex() \
    .filter(lambda _, i: i < n)

#

try that

naive agate Sep 26, 2019, 1:05 AM

#

ok, will try, what is the underscore doing in front of the comma

#

Its saying it takes 2 arguements but only 1 given

#

I got it working doing something like filter (lambda x: x[1] < n)

#

thank you @harsh pulsar

harsh pulsar Sep 26, 2019, 1:27 AM

#

oh my bad yeah

#

this isnt scala

stable pilot Sep 26, 2019, 2:25 AM

#

hey guys

#

im using aiomysql

#

and its really starting to piss me off

#

its way too different from mysql imo

#

and it takes more to get it

#

but i guess i have to use it for my discord bots

#

i have this code

#

async def restart(ctx):
    conn = await get_conn()
    async with conn.cursor() as cur:
        await cur.execute("INSERT INTO status FROM serverrestart WHERE status = 1")
    await conn.close()```

#

im trying to set status from serverrestart to 1

#

when i use this command

#

but idk how to do it

#

very simple

#

but hard to do

#

can anyone help me please

pure cypress Sep 26, 2019, 2:37 AM

#

This doesn't seem like a library issue. Your query just looks wrong

#

Typically insert into is INSERT INTO ... VALUES ...;

#

I think what you want it UPDATE, not INSERT INTO

#

The former edits an existing record. The latter inserts a new record

harsh pulsar Sep 26, 2019, 3:13 AM

#

maybe you want insert into status select * from serverrestart where status = 1?

torn sphinx Sep 26, 2019, 9:03 AM

#

I learned so much cool stuff from salt rock lamp

#

They are so cool

#

Keeps me motivated.. :)

sturdy dust Sep 26, 2019, 9:42 AM

#

What Library should i use to program python and Store on a Excel

#

I Have Made a DataBase to Store Info , But i whant it organized

plain radish Sep 26, 2019, 9:59 AM

#

excel isn't a database sir

#

you will want to use something a bit easier to organise

sturdy dust Sep 26, 2019, 10:12 AM

#

"Sir" i just said i made a data base , i just whant a library to organise my DataBase , and i love the ideia of excel , i will try OpenPyXL

plain radish Sep 26, 2019, 10:14 AM

#

you can try it, but i'd highly suggest a local data storage instead if you can

#

since you avoid a lot of the negatives

#

such as csv, json, sqlite

sturdy dust Sep 26, 2019, 10:16 AM

#

Tnks a Lot

#

What do you recomend to do a Local Data Storage

plain radish Sep 26, 2019, 10:17 AM

#

it depends on the use case usually

#

json is handy if key:value makes sense

#

sqlite is handy if you're often needing relationships

#

a full SQL db makes sense if you're needing access permissions

#

csv is handy if you're wanting to export from an excel or google sheet

#

each of these have their own libs to process the data files and formats

sturdy dust Sep 26, 2019, 10:18 AM

#

I made a python program to track what im doing , like youtube, games , etc...
I Just whant one to storage the times and names

#

i will try the SQL

plain radish Sep 26, 2019, 10:18 AM

#

see how you go 🙂

#

the more you play with things the more you'll learn what works best for you

sturdy dust Sep 26, 2019, 10:19 AM

#

lite*

#

tnks

torn sphinx Sep 26, 2019, 10:29 AM

#

how do you mean sqlite as negatives? 🤔

#

Whatever,I am not sure if I want to know 😂

lusty igloo Sep 26, 2019, 5:36 PM

#

Is there any way to check if a column exists and if it doesnt, create one in asyncpg ?

frozen fossil Sep 26, 2019, 6:12 PM

#

can anybody help me import the mysql connector

ivory turtle Sep 26, 2019, 6:39 PM

#

@lusty igloo I'd guess you'd just try selecting * from column, and if it returns null you create it?

#

Actually nvm, that just implies the column is empty.

lusty igloo Sep 26, 2019, 6:40 PM

#

I just figured it gives error so just might try that

ivory turtle Sep 26, 2019, 6:40 PM

#

https://stackoverflow.com/questions/133031/how-to-check-if-a-column-exists-in-a-sql-server-table

Stack Overflow

How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false:

IF EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHE...

#

?

#

Don't know if this specifically applies to you, but they're checking col lenghts, and if they're NULL they don't exist

#

(afaict)

lusty igloo Sep 26, 2019, 7:00 PM

#

I figured if I try to do actions on columns I get asyncpg.exceptions.UndefinedColumnError so might just try to catch that

#

Is it actually smart move to use json in postgresql

#

As some sort of mixup of both postgresql and json database

harsh pulsar Sep 26, 2019, 9:15 PM

#

json in postgresql is usable yes

#

that said you can query the table info from the postgres system tables

#

https://dba.stackexchange.com/a/22368

Database Administrators Stack Exchange

How do I list all columns for a specified table

I'm looking for a precise piece of information in a database which I have no knowledge about.

It's a 3rd party product, they are slow on answering some questions, and I know the data is lying ins...

ivory turtle Sep 26, 2019, 9:16 PM

#

Guys, how do I make sure that after a db query I only get a result if I get an exact match? (Using SQLAlchemy)

event_check = session.query(KarmaEvents).filter(KarmaEvents.user_giving_id == giving_user.id and KarmaEvents.user_receiving_id == receiving_user.id).one_or_none()

the id given are 24 and 25

#

And no entry with those two matching id's exists... I get returned one that's 24 x 27

#

I don't know why it returns an object at all

harsh pulsar Sep 26, 2019, 9:18 PM

#

use and_() not and

#

the built-in python and is special

ivory turtle Sep 26, 2019, 9:19 PM

#

oO

#

Can you explain?

#

also and_() is an illegal expression

#

Requires an import?

harsh pulsar Sep 26, 2019, 9:20 PM

#

from sqlalchemy import and_

#

KarmaEvents.user_giving_id == giving_user.id and KarmaEvents.user_receiving_id == receiving_user.id

is equivalent to

bool(KarmaEvents.user_giving_id == giving_user.id) and bool(KarmaEvents.user_receiving_id == receiving_user.id)

which i think just evaluates to True and True because these are SQLAlchemy expression objects and not really "data"

#

so that's a filter that's doing no filtering at all

ivory turtle Sep 26, 2019, 9:21 PM

#

Ah.

#

Well.

harsh pulsar Sep 26, 2019, 9:21 PM

#

and behavior cannot be overridden by custom classes

#

however & can be overridden

#

and it looks like from the docs you can use & instead of and_()

ivory turtle Sep 26, 2019, 9:21 PM

#

Also post import, and_() still isn't accepted

#

lemme try that

harsh pulsar Sep 26, 2019, 9:21 PM

#

try this:

(KarmaEvents.user_giving_id == giving_user.id) & (KarmaEvents.user_receiving_id == receiving_user.id)

#

the () are required because of the high precedence of &

ivory turtle Sep 26, 2019, 9:28 PM

#

Thanks man

#

Works perfectly now

#

Still confused why a normal and bugs out

#

But this is good enough 🙂

harsh pulsar Sep 26, 2019, 9:31 PM

#

@ivory turtle SQLAlchemy objects override behavior or ==, &, and |

#

instead of immediately making a comparison, it "captures" the fact that you made the comparison and later translates it into SQL code

#

classes cannot override and and or

#

so python will intervene and call bool() directly on these SQLAlchemy objects

#

which are just dumb containers for essentially a syntax tree

ivory turtle Sep 26, 2019, 9:32 PM

#

Ah... I see

#

that's... kinda unexpected.

#

Thanks man

harsh pulsar Sep 26, 2019, 9:45 PM

#

it's like that for a reason

#

so that and and or can short-circuit properly

#

& however must evaluate both sides regardless of true or fale outcome

neat reef Sep 26, 2019, 10:26 PM

#

do i really need an async version of sqlite?

#

or can i use just the builtin version?

harsh pulsar Sep 26, 2019, 11:35 PM

#

@neat reef what are you using it for

stable pilot Sep 27, 2019, 4:10 AM

#

i have this code

async def giveglobalitem(ctx, itemid: int, quantity: int):
    conn = await get_conn()
    sql = "UPDATE giveglobalitem SET (itemid, quantity, eventSet) = (%s, %s, 1)"
    val = (itemid, quantity)
    async with conn.cursor() as cur:
        await cur.execute(sql, val)
        await conn.commit()
        print('message sent to database changing 0 to 1 for server on')
    conn.close()```

i want to set the itemid with the int im inputing same with the quantity
and whenever this command is ran i would like eventSet to = 1

patent glen Sep 27, 2019, 5:25 AM

#

if I were writing a framework like SQLAlchemy I'd make the expression objects refuse to convert to bool, like how numpy arrays do

torn sphinx Sep 27, 2019, 10:41 AM

#

how can I do a default language condition on psql? for example im querying for a specific language (lets say chinese) but there's no data for it, i want to return the data in english instead rather than returning nothing

neat reef Sep 27, 2019, 11:16 AM

#

@harsh pulsar a discord bot

harsh pulsar Sep 27, 2019, 11:18 AM

#

You don't need async if the load will be low. SQLite writes are fast enough that even if they block, they will only block for a very short amount of time.

#

If you need high performance and have a lot of database i/o then it might become a bottleneck, and you will need async

neat reef Sep 27, 2019, 11:30 AM

#

I see thanks

uncut nova Sep 27, 2019, 5:09 PM

#

Hi,
So, I've been working on an economy game in my discord bot, and I haven't figured out how I save data of a specific user.
For example, I execute the &bal command to show my balance. But when another executes the &bal command, my bal and that user's bal are shared. Is there any way I could implement saving data of a specific user?

ivory turtle Sep 27, 2019, 5:14 PM

#

By storing it in a database, json file, etc.

#

For small servers/low member population normally a json/text file of sorts is recommended

#

Databases however are the more 'proper' way to do this (imo)

uncut nova Sep 27, 2019, 5:15 PM

#

I asked at the right place, it seems

#

thanks, I'll look into the JSON file

tawny sail Sep 27, 2019, 5:27 PM

#

Isn't writing to files blocking?

uncut nova Sep 27, 2019, 5:45 PM

#

hopefully not lol

#

but I'll find another solution if it is blocking

quiet ermine Sep 27, 2019, 10:38 PM

#

aiosqlite

#

Or redis if you want it really compact and fast

uncut nova Sep 27, 2019, 11:29 PM

#

gotcha

uncut nova Sep 28, 2019, 12:59 AM

#

Hi,
So I used the aiosqlite and I ran into an issue,
Here is my code :

@client.command(aliases=['bal'])
async def balance(ctx):
    global cash
    cash_1 = str(cash)
    db = sqlite3.connect('main.sqlite')
    cursor = db.cursor()
    cursor.execute()
    msg = ctx.message.author.name
    msg1 = msg + "'s balance :"
    msg2 = "$" + cash_1
    cursor.execute(f"SELECT NAME FROM balance WHERE NAME = {msg}")
    user_balance_name = cursor.fetchone()
    if user_balance_name is None:
       sql = {"INSERT INTO balance(NAME) VALUES(?,?)"}
       val = {msg}
    elif user_balance_name is not None:
       sql = {"UPDATE balance SET NAME = ? WHERE NAME = ?"}
       val = {msg}
    cursor.execute(sql, val)
    db.commit()
    cursor.close()
    db.close()

    embed = discord.Embed(
        color = discord.Color.blue()
    )
    embed.add_field(name=msg1, value=msg2, inline=False)
    await ctx.send(embed=embed)

tawny sail Sep 28, 2019, 1:20 AM

#

What is balance

#

It's db.cursor()

uncut nova Sep 28, 2019, 1:28 AM

#

a new error appeared after fixing it
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: function takes at least 1 argument (0 given)

proven grove Sep 29, 2019, 3:27 AM

#

@uncut nova actually, sorry for the late reply, but yes any file operation is blocking and the library you’re using for SQLite is too

#

You should send the whole traceback, but before that you might want to read it as it tells you exactly what the error is :/

#

Your empty cursor.execute() function call takes an expression argument which you never passed

uncut nova Sep 29, 2019, 3:35 AM

#

Hi,
Thanks for your replies, I'll keep the information in mind, also I'm still new to sqlite, so I may make alot of mistakes

proven grove Sep 29, 2019, 3:36 AM

#

Also as Ender said, using f-strings or any other form or formatting results in the query being vulnerable to SQL injections, to prevent this you should use safe queries/parameterized queries. This stackoverflow page has useful information regarding that: https://stackoverflow.com/questions/7929364/python-best-practice-and-securest-to-connect-to-mysql-and-execute-queries

Stack Overflow

Python best practice and securest to connect to MySQL and execute ...

What is the safest way to run queries on mysql, I am aware of the dangers involved with MySQL and SQL injection.

However I do not know how I should run my queries to prevent injection on the vari...

uncut nova Sep 29, 2019, 3:37 AM

#

Thanks, I'll check this out

proven grove Sep 29, 2019, 3:38 AM

#

It’s worth mentioning that is fine using unescaped formatting when your data is not able to be manipulated from a user. Good luck

harsh pulsar Sep 29, 2019, 5:01 AM

#

It's still fussy and error prone, I don't recommend it unless you desperately need it for some weird reason

torn sphinx Sep 29, 2019, 5:16 AM

#

Can someone please identify any issues with the tables in this entity diagram that inhibit it from being 3NF?

📎 restaraunt.png

pure cypress Sep 29, 2019, 5:46 AM

#

Perhaps the supply stuff in the delivery table but I think that's a bit of a stretch. I'm not seeing what would prevent 3NF

#

If the description field describes the supply in general rather than describes that specific delivery, then I believe that breaks 3NF

#

@torn sphinx

torn sphinx Sep 29, 2019, 5:51 AM

#

Thanks so much for the answer @pure cypress

#

Yes, I actually removed the description field to fix that

#

that is the only (potential) discrepancy I could find

#

If anyone else potentially sees an issue please
@ me

#

I was also concerned with the Staff tables relying on foreign key relationships from the employees table

#

but as I understand it, they are a one-to-one relationship with no chances of not being unique

#

so it isnt an issue

pure cypress Sep 29, 2019, 5:56 AM

#

You could just add columns to the employee table for those staff tables

#

like simple boolean columns

#

I was never sure which way is better

torn sphinx Sep 29, 2019, 6:01 AM

#

ok thanks

primal valve Sep 29, 2019, 2:47 PM

#

i need some help with setting up my db

#

pretty sure i installed everything right, im on my phpmyadmin page now and im confused

#

ive never worked with dbs before

harsh pulsar Sep 29, 2019, 2:55 PM

#

@edgy juniper you probably wont find a lot of php expertise here but we can help with the db itself

#

What db engine is this

#

And what problem are you having

primal valve Sep 29, 2019, 2:57 PM

#

wrong felix

#

im using mariadb i think

#

on my pi

#

and im not even sure what kind of problem im having, i just dont know what to do

harsh pulsar Sep 29, 2019, 3:13 PM

#

ok

#

so what do you want to do

#

ultimately

#

you have a specific application you want to build? or youre just messing around

primal valve Sep 29, 2019, 3:14 PM

#

im making a discord bot with this, i want to store warns and stuff

harsh pulsar Sep 29, 2019, 3:14 PM

#

ok

#

fwiw i think you can get along just fine with sqlite but if you want to learn mysql/mariadb this is a good project to start with

#

are you using php for it? or python?

primal valve Sep 29, 2019, 3:15 PM

#

py

harsh pulsar Sep 29, 2019, 3:15 PM

#

so where does php come in here?

primal valve Sep 29, 2019, 3:15 PM

#

phpmyadmin

harsh pulsar Sep 29, 2019, 3:15 PM

#

oh its a mysql admin tool

#

right

primal valve Sep 29, 2019, 3:15 PM

#

just a gui if i understood that correctly

harsh pulsar Sep 29, 2019, 3:15 PM

#

yeah

#

do you understand how databases like mysql/mariadb work generally?

primal valve Sep 29, 2019, 3:15 PM

#

kinda

harsh pulsar Sep 29, 2019, 3:16 PM

#

ok, go ahead and share it

#

then i can fill in the gaps

primal valve Sep 29, 2019, 3:16 PM

#

you mean like

#

how tables and columns work

#

you have a table, and you can add columns to it

#

and the row actually holds the values

#

right?

harsh pulsar Sep 29, 2019, 3:23 PM

#

yeah basically

#

for your purposes you dont need to care so much about the technical side of things

#

but the basic idea is that mysql is tabular data storage (like you described) plus users and accounts

#

you should already have some kind of admin user

primal valve Sep 29, 2019, 3:24 PM

#

yup

harsh pulsar Sep 29, 2019, 3:25 PM

#

you'll need a user for the discord bot

primal valve Sep 29, 2019, 3:25 PM

#

is that an admin account too?

harsh pulsar Sep 29, 2019, 3:25 PM

#

no, not usually

#

it's generally good practice to restrict the user permissions to only what they need and nothing more

#

that's obviously good for security, but it also helps prevent making serious mistakes

#

like accidentally truncating a table

#

("truncate" = "delete all data in the table")

primal valve Sep 29, 2019, 3:26 PM

#

yeah that would be kinda bad

#

currently doing the sql course on sololearn

harsh pulsar Sep 29, 2019, 3:32 PM

#

yep

#

ok so

#

the basic idea is, you create a table or a few tables

#

each table has a schema

#

the schema describes the structure of the table

#

generally you're not expected to change the schema

#

that remains fixed throughout your application

#

so what you need to do is:

decide how your database should be laid out (you can change it later but you at least need something to start with)
create tables accordingly
create a non-admin account for the bot
start writing your python code based on the tables you created

primal valve Sep 29, 2019, 3:47 PM

#

pretty sure i need to use aiomysql

harsh pulsar Sep 29, 2019, 3:48 PM

#

interestingly, you dont

#

you can

#

but you dont

#

async in python is weird -- it's still single-threaded, running operations sequentially

#

so you can use non-async code with async code

#

however, the non-async code will block

primal valve Sep 29, 2019, 3:49 PM

#

but its blocking then

#

yup

harsh pulsar Sep 29, 2019, 3:49 PM

#

right. but usually database writes are really fast

#

if you're already comfortable with async, then go ahead and use aiomysql

primal valve Sep 29, 2019, 3:49 PM

#

id still prefer async

harsh pulsar Sep 29, 2019, 3:50 PM

#

yep. just giving you the option -- you can use an existing database library and usually not notice, single-row inserts and lookups are generally very fast

#

if you're doing bigger queries then yes you'll want async

#

fortunately a database like mysql usually has its own internal locking so you should be pretty safe as far as database integrity

primal valve Sep 29, 2019, 3:50 PM

#

this is running on a pi zero w, are they really that fast?

harsh pulsar Sep 29, 2019, 3:51 PM

#

fast enough

#

but not worth taking chances on a zero

#

might as well use aiomysql since it already exists

primal valve Sep 29, 2019, 3:51 PM

#

so aiomysql it is?

harsh pulsar Sep 29, 2019, 3:51 PM

#

yeah

#

that said, you will probably want to set up some kind of connection pool in your bot

#

so you don't make a new connection every request

#

and so you don't share 1 connection for all requests which can get messy

primal valve Sep 29, 2019, 3:52 PM

#

i think ill just connect once on startup

#

not sure

harsh pulsar Sep 29, 2019, 3:52 PM

#

https://stackoverflow.com/a/44939188/2954547

Stack Overflow

How can I use connection pool in aiomysql

I just don't know what to do to reuse aiomysql connection pool by reading the aiohttp examples or by google.
Here is my code

import aiomysql
import asyncio
async def select(loop, sql):
pool =...

primal valve Sep 29, 2019, 3:53 PM

#

i can access the db from my network right?

#

so i can use it from my main machine on the same network

harsh pulsar Sep 29, 2019, 3:54 PM

#

yes

primal valve Sep 29, 2019, 6:51 PM

#

@harsh pulsar what permissions would my bot need?

#

i created a user, idk what permissions to give it

#

you said i shouldnt give it all perms

harsh pulsar Sep 29, 2019, 6:51 PM

#

well, what does the bot need to do?

primal valve Sep 29, 2019, 6:51 PM

#

probably read and write or something

#

not sure

harsh pulsar Sep 29, 2019, 6:52 PM

#

its a good idea to figure out how the bot will interact w/ the database before you set it up

#

presumably you will need to write to a "warnings" table

#

and read from that table

primal valve Sep 29, 2019, 6:52 PM

#

not even sure how i should structure it

harsh pulsar Sep 29, 2019, 6:52 PM

#

youre just tracking warnings?

primal valve Sep 29, 2019, 6:52 PM

#

yeah

#

they have a user_id and a reason

harsh pulsar Sep 29, 2019, 6:53 PM

#

you could just have a warnings then

#

with that content in it

#

could have a timestamp, a warning id

#

the person who issued the warning

primal valve Sep 29, 2019, 6:53 PM

#

not sure how id make warns server specific tho

harsh pulsar Sep 29, 2019, 6:53 PM

#

and maybe the channel too depending on if that makes sense

#

ahh yeah

#

you can do a few things

primal valve Sep 29, 2019, 6:53 PM

#

i dont think i can nest tables

harsh pulsar Sep 29, 2019, 6:53 PM

#

you could put it all in 1 big table and have a server id

primal valve Sep 29, 2019, 6:53 PM

#

oh right

harsh pulsar Sep 29, 2019, 6:54 PM

#

im not sure what best practice is for sharing 1 database server across different instances of the same application

primal valve Sep 29, 2019, 6:55 PM

#

why would i run my bot more than once at a time

#

i create users with create user 'username'@'localhost' identified by 'password'; right?

#

and what is partitioning?

harsh pulsar Sep 29, 2019, 7:08 PM

#

i think you can create users in the admin view

#

as for the table setup, i think lots of people just store the server_id in their table

#

so you can have multiple instances of the same bot all sharing 1 database

primal valve Sep 29, 2019, 7:10 PM

#

you mean the bot can be on multiple servers?

harsh pulsar Sep 29, 2019, 7:12 PM

#

yeah

#

and if you store the server_id it will keep warnings separate across servers

primal valve Sep 29, 2019, 7:15 PM

#

exactly

#

what port do i need?

harsh pulsar Sep 29, 2019, 7:15 PM

#

port?

#

use the default

primal valve Sep 29, 2019, 7:15 PM

#

yeah

#

aiomysql asks for a port

harsh pulsar Sep 29, 2019, 7:17 PM

#

get that from phpmyadmin

primal valve Sep 29, 2019, 7:17 PM

#

it says localhost:3306 and the stackoverflow page you sent me uses 3306 too

#

text is the db equivalent of a string right?

#

and what is partitioning?

primal valve Sep 29, 2019, 8:22 PM

#

@harsh pulsar

harsh pulsar Sep 29, 2019, 10:18 PM

#

@primal valve yes "text" is fine for text data

#

i.e. strings

#

partitioning, don't worry about it

#

it changes how the data is stored

primal valve Sep 29, 2019, 10:20 PM

#

got the table set up, i just cant connect now

#

Can't connect to MySQL server on 'myip'

#

error code 2003

torn sphinx Sep 29, 2019, 10:26 PM

#

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'posts.owner_id' could not find table 'users' with which to generate a foreign key to target column 'id'

#

trying to use flask with postgres

#

I have __tablename_ = 'users'``````posts = db.relationship('PostModel',backref='users',lazy=True)

#

and owner_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)

long stream Sep 30, 2019, 2:51 PM

#

@torn sphinx your code only shows one underscore after tablename. You have __tablename_ instead of __tablename__

torn sphinx Sep 30, 2019, 2:56 PM

#

Oh my god

#

That error kept me stuck for a while day

#

Thank you so much @long stream

long stream Sep 30, 2019, 3:05 PM

#

@torn sphinx no problem! That kinda thing can be tough to find. No matter how many times your eyes pass over that sequence of characters, your brain sees what it expects to see instead of what's there 😂

tiny otter Sep 30, 2019, 5:02 PM

#

so i have a sqlite3 db file hosted on a website, how would i view make that db viewable in my python code

sturdy dust Sep 30, 2019, 5:30 PM

#

i Cant break a line in a json , i need help

long stream Sep 30, 2019, 6:27 PM

#

@tiny otter does the Python code run on the same server that contains the SQLite DB file?

harsh pulsar Sep 30, 2019, 6:40 PM

#

@sturdy dust what do you mean "break a line"?

sturdy dust Sep 30, 2019, 6:40 PM

#

LIKE \N

harsh pulsar Sep 30, 2019, 6:42 PM

#

can you give some more context

#

maybe you want to print json with line breaks, instead of all on a single line?

#

like

{
  "a": 1
}

instead of

{"a": 1}

?

silk summit Sep 30, 2019, 7:53 PM

#

Fuckin help me

harsh pulsar Sep 30, 2019, 7:56 PM

#

@silk summit it would help if you stated what you need help with

#

we have a guide for asking good questions:

#

!ask

delicate fieldBOT Sep 30, 2019, 7:56 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.
• 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
• Keep your patience while we're helping you.

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

tiny otter Sep 30, 2019, 8:02 PM

#

in sqlite how do i query every table for the same string in a certain column. so i want to find all entries on all tables that are assigned to a given property? eample i have 3 tables, cameras, DVRs, and routers and they all have the column property that has numeric values that are property numbers. how would i find all devices that are say assigned to property 1152

sturdy dust Sep 30, 2019, 8:16 PM

#

@harsh pulsar exactly

#

something more organizes

#

organized*

harsh pulsar Sep 30, 2019, 8:17 PM

#

!e

import json
data = {'a': 1, 'b': 2}
print(json.dumps(data))
print(json.dumps(data, indent=2))

delicate fieldBOT Sep 30, 2019, 8:17 PM

#

@harsh pulsar Your eval job has completed with return code 0.

001 | {"a": 1, "b": 2}
002 | {
003 |   "a": 1,
004 |   "b": 2
005 | }

harsh pulsar Sep 30, 2019, 8:17 PM

#

indent= is what you want

#

!d g json.dump

delicate fieldBOT Sep 30, 2019, 8:17 PM

#

`json.dump`

json.dump(obj, fp, *, skipkeys=False, ensure_ascii=True, check_circular=True, allow_nan=True, cls=None, indent=None, separators=None, default=None, sort_keys=False, **kw)```Serialize *obj* as a JSON formatted stream to *fp* (a `.write()`-supporting [file-like object](../glossary.html#term-file-like-object)) using this [conversion table](#py-to-json-table).

If *skipkeys* is true (default: `False`), then dict keys that are not of a basic type ([`str`](stdtypes.html#str "str"), [`int`](functions.html#int "int"), [`float`](functions.html#float "float"), [`bool`](functions.html#bool "bool"), `None`) will be skipped instead of raising a [`TypeError`](exceptions.html#TypeError "TypeError").

The [`json`](#module-json "json: Encode and decode the JSON format.") module always produces [`str`](stdtypes.html#str "str") objects, not [`bytes`](stdtypes.html#bytes "bytes") objects. Therefore, `fp.write()` must support [`str`](stdtypes.html#str "str") input.

If *ensure\_ascii* is true (the default), the output is guaranteed to have all incoming non-ASCII characters escaped. If *ensure\_ascii* is false, these characters will be output as-is.... [read more](https://docs.python.org/3/library/json.html#json.dump)

sturdy dust Sep 30, 2019, 8:18 PM

#

But i whant it to be writed like this already on the json file , is it possible?

#

@harsh pulsar

harsh pulsar Sep 30, 2019, 8:19 PM

#

if you're saving the data to json, you control the format

#

im not sure what youre asking

#

do you know how to save data to json in python

sturdy dust Sep 30, 2019, 8:20 PM

#

i whant to know if i can write a json but be idented on the json file

#

is it possible?

harsh pulsar Sep 30, 2019, 8:20 PM

#

i just said, use the indent= keyword argument

#

and i posted a link to the docs where that is described

sturdy dust Sep 30, 2019, 8:20 PM

#

it python of corse

harsh pulsar Sep 30, 2019, 8:20 PM

#

read the documentation page and look for indent=

sturdy dust Sep 30, 2019, 8:21 PM

#

oh didnt see it my bad

harsh pulsar Sep 30, 2019, 8:21 PM

#

and look at the example i just posted

sturdy dust Sep 30, 2019, 8:21 PM

#

tnks a lot

harsh pulsar Sep 30, 2019, 8:22 PM

#

@tiny otter you would use a "query parameter" to inject the number 1152 into your query.

for example, using sqlite3 it might could look like this:

cursor = connection.execute('select * from cameras where property_number = ?', [1152])
cameras = cursor.fetchall()

cursor = connection.execute('select * from dvrs where property_number = ?', [1152])
dvrs = cursor.fetchall()

cursor = connection.execute('select * from routers where property_number = ?', [1152])
routers = cursor.fetchall()

tiny otter Sep 30, 2019, 8:23 PM

#

thanks!

primal valve Oct 1, 2019, 1:39 AM

#

damn i finally got my db up and working after like 2 days

noble kite Oct 1, 2019, 2:19 AM

#

Hey all. Could anyone point me to some beginner-friendly literature on SQLAlchemy?

#

I was reading the docs, but I'd like to see some real world examples

tawny sail Oct 1, 2019, 2:50 AM

#

https://github.com/The-Tavern-Bot

GitHub

TTB Dev Team

TTB Dev Team has one repository available. Follow their code on GitHub.

#

One of my project, we use aiosql alchemy

#

To connect to dbsqite

#

U can have a look into utils/database or cogs for some examples

#

@noble kite

noble kite Oct 1, 2019, 2:51 AM

#

Oop! Thank you 🙂

tawny sail Oct 1, 2019, 2:52 AM

#

https://github.com/The-Tavern-Bot/TheTavernBot

GitHub

The-Tavern-Bot/TheTavernBot

Contribute to The-Tavern-Bot/TheTavernBot development by creating an account on GitHub.

#

Actual repo

#

@noble kite

noble kite Oct 1, 2019, 2:53 AM

#

This is a pretty wicked project, by the way

tawny sail Oct 1, 2019, 2:55 AM

#

Lol

#

Sqlalchemy helps u connect to a database and write queries to it

#

Same code for all dbs, just need to change the config a lil bit

noble kite Oct 1, 2019, 3:00 AM

#

Mhm. I'm using it to build out a blog for my coworkers. Getting my head around models is a bit mind-boggling

tawny sail Oct 1, 2019, 3:01 AM

#

Website?

#

Which framework

noble kite Oct 1, 2019, 3:01 AM

#

Flask

#

I'm using flask-SQLAlchemy

tawny sail Oct 1, 2019, 3:02 AM

#

Oh

noble kite Oct 1, 2019, 3:02 AM

#

class DevelopmentConfig(Config):
    DEBUG = True
    SQLALCHEMY_DATABASE_URI = 'sqlite:///test.db' or \
        'sqlite:///' + os.path.join(basedir, 'test.db')

tawny sail Oct 1, 2019, 3:02 AM

#

Maybe u can try #web-development for a better response?

noble kite Oct 1, 2019, 3:02 AM

#

and my models are uh- a mess

#

hah

#

Oh no your example was very helpful

tawny sail Oct 1, 2019, 3:03 AM

#

😅I haven't worked with flask sqlalchemy, xD

noble kite Oct 1, 2019, 3:03 AM

#

No worries!

torn sphinx Oct 1, 2019, 5:53 AM

#

what are connections to a db

#

like, when we say the max number of connections is 5000 for mongodb

snow slate Oct 1, 2019, 8:30 AM

#

What can you say about our sqlite module?
https://github.com/crabdudesdev/bonny-biboni-bot/blob/19.200/wip/autumn/Core/sql.py

#

Any suggestions?

unreal tartan Oct 1, 2019, 8:43 AM

#

Close your connections when you're done using them.
And in that case a contextmanager would seem ideal. So you could use it like

with db_connection("Data/servers.data") as connection:
    # use connection
# closed automatically

snow slate Oct 1, 2019, 8:44 AM

#

Ok

light zinc Oct 1, 2019, 11:39 AM

#

I use sqlalchemy with postgresql.

I have database of products. Each product can be in multiple stores. Prices have historical data. (so 3 tables connected with one
intermediate table)
I want to query for all the prices of products that match my filters.
So a example response would look something like this for filter "Milk":

  Name     Volume         Prices
  Milk     500ml          cursor to some tmp table

Prices tmp table:
Store        Price   Quantity
GoodWill     200     19
BigHand      150     5
Wallmart     100     22

I am able to get the result in two queries. But if I want prices for 10 products I have to make 11 queries (one for finding the products and then one for each product to get their price)
Can this be done with one sql query or with a db function?

rich trout Oct 1, 2019, 11:45 AM

#

Yes

#

It's called "joining"

#

q = (Session.query(User,Document,DocumentPermissions)
    .filter(User.email == Document.author)
    .filter(Document.name == DocumentPermissions.document)
    .filter(User.email == 'someemail')
    .all())

#

is an example

light zinc Oct 1, 2019, 12:22 PM

#

Translating sql to python is hard :)

But it still works like regular sql join.
I get result like this:

[
    [Milk, 500ml, GoodWill, 200, 19],
    [Milk, 500ml, BigHand, 150, 5],
    [Milk, 500ml, Wallmart, 100, 22],
    [Milkshake, 250ml, GoodWill, 200, 11],
    [Milkshake, 250ml, BigHand, 200, 42],
]

It will most likely take me longer to process the results than it would for db to gather correct results right away.
I want a result that is something like this:

[
    [Milk, 500ml, {
                    [500ml, GoodWill, 200, 19],
                    [BigHand, 150, 5],
                    [Wallmart, 100, 22]
                }
    ],
    [Milkshake, 250ml, {
                    [GoodWill, 200, 11],
                    [BigHand, 200, 42],
                     }
    ],
]

unreal tartan Oct 1, 2019, 12:24 PM

#

sql databases return rows, not trees. you will always have results like the first one.

light zinc Oct 1, 2019, 12:33 PM

#

Guess that's why i didnt find much about that from google.
But functions can return tables.
I remember that oracle functions can return regular values + cursors but didn't find anything about that for postgres. Is it possible?

#

nvm, found something about postgres functions. Will try it

light zinc Oct 1, 2019, 12:59 PM

#

Ok, finally got it. Thanks for all the help.
This is the main part of the query:

select p.id, json_agg(json_build_object('store', s.name, 'price', pp.price))
from product p
         inner join store_product sp on sp.product_id = p.id
         inner join product_price pp on sp.product_price_id = pp.id
         inner join store s on sp.store_id = s.id
where p.title like 'Milk%'
group by p.id;

#

Now just the small task of translating it to python 😄

pure scroll Oct 1, 2019, 1:16 PM

#

you don't really have to, you still can use raw sql

light zinc Oct 1, 2019, 1:28 PM

#

Already done 😛 It's quite readable and the rest is in python also

pure scroll Oct 1, 2019, 1:29 PM

#

okay, but keep in mind that ORMs in some cases are building inefficient queries

#

since they abstract DB layer they tend to build a query in a way that it's engine agnostic, whenever this is possible

light zinc Oct 1, 2019, 1:40 PM

#

ooh, got to test it then

harsh pulsar Oct 1, 2019, 2:39 PM

#

you can use raw sql in python

#

query = """
select p.id, json_agg(json_build_object('store', s.name, 'price', pp.price))
from product p
         inner join store_product sp on sp.product_id = p.id
         inner join product_price pp on sp.product_price_id = pp.id
         inner join store s on sp.store_id = s.id
where p.title like $1
group by p.id;
"""

cursor.execute(query, ("Milk%",))
cursor.fetchall()

more or less the same depending on your database library

primal valve Oct 1, 2019, 3:00 PM

#

need some help

#

im trying to use bit types, i cant figure out how tho

#

i have a column with bit as type, if i try to insert a 1 into it it doesnt work

#

column "bit_col" is of type bit but expression is of type integer

harsh pulsar Oct 1, 2019, 3:14 PM

#

try True/False?

primal valve Oct 1, 2019, 3:15 PM

#

ERROR: column "bit_col" is of type bit but expression is of type boolean

#

is boolean even a thing in sql?

harsh pulsar Oct 1, 2019, 3:16 PM

#

huh

#

what library are you using to connect

primal valve Oct 1, 2019, 3:17 PM

#

asyncpg

harsh pulsar Oct 1, 2019, 3:17 PM

#

oh this isnt mysql

primal valve Oct 1, 2019, 3:17 PM

#

not anymore

#

im doing this from the terminal rn tho

harsh pulsar Oct 1, 2019, 3:17 PM

#

so directly in postgres? no python?

primal valve Oct 1, 2019, 3:17 PM

#

yup

harsh pulsar Oct 1, 2019, 3:18 PM

#

https://www.postgresql.org/docs/current/datatype-bit.html

#

INSERT INTO test VALUES (B'10'::bit(3), B'101');
SELECT * FROM test;

#

so it looks like B'1' and B'0' is what you need

primal valve Oct 1, 2019, 3:20 PM

#

looks like it works, thanks

sturdy dust Oct 1, 2019, 3:21 PM

#

you

#

sorry wrong channel

primal valve Oct 1, 2019, 4:04 PM

#

so what is this $1, $2 stuff?

#

pretty sure its some kind of variable thing

harsh pulsar Oct 1, 2019, 4:15 PM

#

query placeholders

#

every database engine uses different syntax, the dbabpi specification lets library authors decide

#

the placeholder lets the library correctly cast the data and interpolate it with correct syntax and escaping

#

which can be very very messy by hand

#

and will also lead to sql injection attacks if this data is being taken from untrusted sources

primal valve Oct 1, 2019, 4:18 PM

#

so almost like f strings but cooler

#

and sql

harsh pulsar Oct 1, 2019, 4:19 PM

#

kind of

#

maybe more like .format

primal valve Oct 1, 2019, 4:20 PM

#

yeah

#

cause you put the formatting stuff after the query

#

i think

primal valve Oct 1, 2019, 5:53 PM

#

so how do i actually use this? idk how its called so i cant look it up

harsh pulsar Oct 1, 2019, 5:54 PM

#

"query parameters"

primal valve Oct 1, 2019, 5:59 PM

#

so for example i can do db.fetch("insert into table values ($1, $2)", "This replaces $1", "This replaces $2")

#

right?

harsh pulsar Oct 1, 2019, 6:04 PM

#

thats the idea yes

#

note that asyncpg's API is somewhat nonstandard

primal valve Oct 1, 2019, 6:06 PM

#

elaborate

harsh pulsar Oct 1, 2019, 6:06 PM

#

there is a specification for how python database libraries should behave

#

asyncpg deviates from it in a few places

#

as long as you pay attention to the docs and ignore random tutorials online you wont have problems

civic marten Oct 1, 2019, 6:58 PM

#

If I have two tables, one that is the main table with static data and one with user inputted data. The user data has id's but they can be duplicates.. Is there a way I can give the user data a prefix to give them all unique id's and somehow select against the static data and return each instance of the user data?

harsh pulsar Oct 1, 2019, 7:05 PM

#

you could have something like

static_id | rep_id | ...

where rep_id is the number of times that static id was repeated

#

or give each user input a separate unique id

#

in either case, static_id would be a foreign key out to the static table

proven wagon Oct 1, 2019, 7:41 PM

#

Is writing a system with classes worth it for a postgresql (asyncpg) database?

I want to write something that can help me simplify how i use my database, but i dont know where to start (or look)

harsh pulsar Oct 1, 2019, 7:47 PM

#

@proven wagon you might be interested in an ORM like sqlalchemy

#

it's a bit of a learning curve though

proven wagon Oct 1, 2019, 7:48 PM

#

So i would use asyncpgsa

harsh pulsar Oct 1, 2019, 7:49 PM

#

oh right youre doing async

#

hm

proven wagon Oct 1, 2019, 7:49 PM

#

mhm

harsh pulsar Oct 1, 2019, 7:49 PM

#

i think there is an async ORM out there

#

however there is "sqlalchemy core" which is basically a query builder

proven wagon Oct 1, 2019, 7:49 PM

#

yeah asyncpgsa

harsh pulsar Oct 1, 2019, 7:49 PM

#

asyncpgsa is not an ORM

#

asyncpgsa is a compatibility layer for the SQLalchemy "core" query builder

proven wagon Oct 1, 2019, 7:49 PM

#

its a asyncpg wrapper for use with sqlalchemy

#

well you know this better than me

harsh pulsar Oct 1, 2019, 7:50 PM

#

Currently this repo does not support SA ORM, only SA Core.

#

i know of at least 2 async-capable ORMs

#

https://github.com/fantix/gino
https://github.com/encode/orm
however both are pretty new

GitHub

fantix/gino

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

GitHub

encode/orm

An async ORM. 🗃. Contribute to encode/orm development by creating an account on GitHub.

#

sqlalchemy is very well supported

#

as i probably mentioned a while ago, unless you have lots and lots of queries happening you probably dont need an async database connection

#

database reads and writes are pretty fast. unless you're trying to do aggregates and analytics, queries that take > 1 second, or you have lots of queries happening, you probably can just use a blocking/synchronous connection with sqla

#

that said, sqla core might be good enough

#

so try asyncpgsa + sqla core and see how you feel about it

proven wagon Oct 1, 2019, 7:54 PM

#

the gino readme looks like its exactly what ive been looking for

pure scroll Oct 2, 2019, 7:40 AM

#

as i probably mentioned a while ago, unless you have lots and lots of queries happening you probably dont need an async database connection
if you are running a fully async program then you do, since you cna't use any blocking calls

harsh pulsar Oct 2, 2019, 11:45 AM

#

You can use blocking calls from async code, it just blocks

pure scroll Oct 2, 2019, 12:13 PM

#

exactly, and the whole point of using async is gone 🙂

harsh pulsar Oct 2, 2019, 12:49 PM

#

sometimes you use async because you have to

#

e.g. your discord bot requires async

#

but you're writing it for 5 people to use and theres no added value

#

alternatively, as i said above, if the blocking call takes 10ms you don't often care about that extra 10ms latency

torn sphinx Oct 2, 2019, 12:54 PM

#

sqlite3
how do I select a column from all the users and ORDER them?

#

wait is it maybe just normal select but fetchall? 🤔 but they wouldn't be sorted..

#

nvm

#

I just found out how to get it

#

I just ordered by exp desc

blazing stag Oct 2, 2019, 4:12 PM

#

Which sql database should I use if I’m just starting to learn databases

torn sphinx Oct 2, 2019, 4:45 PM

#

I am using sqlite3 - you pick up quickly

#

And also there is a database browser that I recommend using if you never did use one.

torn sphinx Oct 2, 2019, 5:08 PM

#

for example I made my own leveling system + a leaderboard and some other things for my own discord bot,and I tell you - I am in beginner as in databases,so in python,and if I did it,it is doable 😄

harsh pulsar Oct 2, 2019, 5:22 PM

#

i second the recommendation for sqlite

#

much easier than setting up a server for postgres or mysql

neat reef Oct 2, 2019, 5:52 PM

#

sqlite is pretty much enough, built-in in python and has a lot of documentation and examples

tawny sail Oct 2, 2019, 6:21 PM

#

You can use sqlalchemy if you plan on using a different dB later on

torn sphinx Oct 2, 2019, 6:40 PM

#

how many databases can you have>

#

Well they kinda are your files on your pc,right? So basically a lot of them

#

I just can not describe how cool databases are

#

You can SO.MUCH.THINGS. with them..

tranquil torrent Oct 2, 2019, 8:19 PM

#

who uses mysql and python connector

harsh pulsar Oct 2, 2019, 10:12 PM

#

I have used it in the past

rustic carbon Oct 3, 2019, 12:07 AM

#

I want a command to add the ID in the database only once, and if it uses again make a check on the database and see if his ID is there to do?

torn sphinx Oct 3, 2019, 1:38 AM

#

what advantages do server'd databases have over physical file ones (for local projects)?

#

It seems like if you want to use anything other than sqlite, you have to set up a server

rich trout Oct 3, 2019, 1:51 AM

#

They tend to handle larger and more complex loads, multiple clients at the same time, and other things that it doesn't make sense for a single-application database to handle

proven wagon Oct 3, 2019, 8:26 PM

#

https://hasteb.in/iwecebux.sql 🙄

#

ID in database is of type BIGINT

#

yet exception says expected str

light zinc Oct 3, 2019, 9:14 PM

#

sqlalchemy, default config

If I make new object it is automatically added to session.
So if i try to query something it is also returned among the results.
(But saved only if I commit (or flush i guess))

ie:

db.session.query(Car).filter(Car.colour == "Blue")  # no resulsts
new_car = Car(colour="Blue", name="Jemmo")
db.session.query(Car).filter(Car.colour == "Blue")  # one result, a blue car named Jemmo

How can I make it so that i have to use add before i see it in session

db.session.query(Car).filter(Car.colour == "Blue")  # no resulsts
new_car = Car(colour="Blue", name="Jemmo")
db.session.query(Car).filter(Car.colour == "Blue")  # no resulsts
db.session.add(new_car)
db.session.query(Car).filter(Car.colour == "Blue")  # one result, a blue car named Jemmo

light zinc Oct 3, 2019, 10:22 PM

#

Found workaround that makes bit more sense also

fringe tiger Oct 4, 2019, 12:27 AM

#

can someone gimme example/explanation on how to best organize sql queries?
Like do I store them in a variable as string, maybe in methods? Currently I have a class database_handler that handles all db stuff and queries are stored as strings inside methods, so I just call a method and voila. I have a lot of them and it's beginning to be ugly and will be ugly even after I do planed refactor

#

Just looking for a idea here, already have few in mind, ping for reply

marsh sun Oct 4, 2019, 4:42 AM

#

I tend to have all non trivial queries stored as .sql files.

candid night Oct 4, 2019, 5:34 AM

#

https://mystb.in/xifusaforu.py
https://mystb.in/ozakoziliq.sql
I'm trying to make a simple contact book for a project as I'm bored and thought why not

Mystbin

A fast intuitive paste service.

Mystbin

A fast intuitive paste service.

quiet ermine Oct 4, 2019, 3:03 PM

#

Ooh another hastebin clone

harsh pulsar Oct 4, 2019, 3:12 PM

#

it might actually be hastebin on another domain @quiet ermine

#

seeing as haste is open source

quiet ermine Oct 4, 2019, 3:13 PM

#

So a clone

neat reef Oct 4, 2019, 3:31 PM

#

Someone said clone?

proven wagon Oct 4, 2019, 4:23 PM

#

https://hasteb.in/iwecebux.sql 🙄
ID in database is of type BIGINT
yet exception says expected str

harsh pulsar Oct 4, 2019, 4:51 PM

#

i saw you posted that a while ago. are you 110% sure it's bigint?

proven wagon Oct 4, 2019, 4:52 PM

#

yes

#

It seems to be the sql statement

#

I was told not to use formatted statements though, how can i fix this?

#

'SELECT * FROM Members WHERE $1 = $2 LIMIT $3'

harsh pulsar Oct 4, 2019, 4:58 PM

#

oh

#

what is $1?

#

is that a column name?

#

query parameters wont interpolate column and table names

#

only values

#

do you really need to query on a dynamic column name?

#

so you will have to either hard-code or string-format the column name

#

@proven wagon ^

#

psycopg2 has a facility to interpolate column and table names, but as nice as it is, it's nonstandard

proven wagon Oct 4, 2019, 5:03 PM

#

Yes id very much like it to be dynamic

#

Anyways it works now with formatted query

harsh pulsar Oct 4, 2019, 5:04 PM

#

fortunately its very rare that user input is given as a table name

#

and also quoting/escaping is much less sketchy

#

(usually you shouldnt have to escape or quote a column name at all)

#

so @proven wagon what was happening in that error was, it was trying to compare a string literal (what you thought was the column name) to a bigint

#

which was failing

proven wagon Oct 4, 2019, 5:06 PM

#

ah

harsh pulsar Oct 4, 2019, 5:06 PM

#

https://github.com/MagicStack/asyncpg/issues/208#issuecomment-335498184

Unlike psycopg2, asyncpg does no query rewrites. The query text is passed to the server as-is, so only valid PostgreSQL syntax will work. This is a deliberate design approach, chosen for simplicity and performance, and this is unlikely to change. The correct solution in this case is to generate an appropriate query in every case.

GitHub

Question: how to pass a column name as a query param · Issue #208...

Haven't found any information in the docs about this use case For query update table set date_column = $1 where x_column = $2 in some cases I need to leave the original value in a column da...

#

the server does the query parameter interpolation, not python

#

at least in the case of asyncpg

undone roost Oct 4, 2019, 6:12 PM

#

Hey ....how do i use column of one table in function of another table ....in sqlalchemy ?

candid night Oct 4, 2019, 8:13 PM

#

https://mystb.in/xifusaforu.py
https://mystb.in/ozakoziliq.sql
I'm trying to make a simple contact book for a project as I'm bored and thought why not and im getting this error (In one of the hastebins)

Mystbin

A fast intuitive paste service.

Mystbin

A fast intuitive paste service.

potent yarrow Oct 4, 2019, 11:23 PM

#

how would i store an object in a database?

#

as in what type

autumn jackal Oct 4, 2019, 11:50 PM

#

json string?

harsh pulsar Oct 5, 2019, 12:27 AM

#

Or proper json if youre using postgres

candid night Oct 5, 2019, 7:15 PM

#

Ignoring exception in on_message
Traceback (most recent call last):
  File "C:\Users\Haris\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\client.py", line 270, in _run_event
    await coro(*args, **kwargs)
  File "C:\Users\Haris\Desktop\Backup\Code\Bots\Discord\The Fire\Cogs\Modmail.py", line 16, in on_message
    SERVER = Config.COLLECTION.find_one({"_id": "important information"})
  File "C:\Users\Haris\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymongo\collection.py", line 1273, in find_one
    for result in cursor.limit(-1):
  File "C:\Users\Haris\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymongo\cursor.py", line 1156, in next
    if len(self.__data) or self._refresh():
  File "C:\Users\Haris\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymongo\cursor.py", line 1050, in _refresh
    self.__session = self.__collection.database.client._ensure_session()
  File "C:\Users\Haris\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymongo\mongo_client.py", line 1807, in _ensure_session
    return self.__start_session(True, causal_consistency=False)
  File "C:\Users\Haris\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymongo\mongo_client.py", line 1760, in __start_session
    server_session = self._get_server_session()
  File "C:\Users\Haris\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymongo\mongo_client.py", line 1793, in _get_server_session
    return self._topology.get_server_session()
  File "C:\Users\Haris\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymongo\topology.py", line 482, in get_server_session
    None)
  File "C:\Users\Haris\AppData\Local\Programs\Python\Python37-32\lib\site-packages\pymongo\topology.py", line 205, in _select_servers_loop
    self._error_message(selector))
pymongo.errors.ServerSelectionTimeoutError: connection closed,connection closed,connection closed

edgy helm Oct 6, 2019, 2:19 AM

#

I hope this question is not asked too frequently. I have finishied the Intro to Relational Databases course on Udacity, any recommendatios for a resource ( course or book ) that I should take next?

crisp tundra Oct 6, 2019, 3:04 AM

#

So I have to make a small databases of countries atm
What concerns this question is that some of them can have the same exact data
Using postgresql, would it be better to make a duplicate row with all the same info
Or change the datatype to accept multiple items?
This happens like 40 times in 733 rows

rich trout Oct 6, 2019, 3:07 AM

#

"some of them can have the same exact data", what exactly do you mean by that?

#

Generally, you want to separate out things that depend on each other, so that the same data isn't present in multiple places. For example, if multiple counties have the same post office, post offices should be separate, and each county should point at the same post office. That way if you change the post office's name or address it's only in once place it it can't be internally inconsistent

#

Generally, you want to separate out things that depend on each other, so that the same data isn't present in multiple places. For example, if multiple counties have the same post office, post offices should be separate, and each county should point at the same post office. That way if you change the post office's name or address it's only in one place it it can't be internally inconsistent

crisp tundra Oct 6, 2019, 3:31 AM

#

With exact same data, I mean that two rows would be exactly the same

#

Except for the country that identifies that row

#

Repetitions occur to at most 1-4 times, for a total of 40 lines

#

Also, I know that the information absolutely wont change

#

So basically, what matters here is the index per se

#

From what you said, I think I'll leave it repeated since it's not much

undone roost Oct 6, 2019, 7:08 AM

#

anyone with flask sqlalchemy knowledge here?

rich trout Oct 6, 2019, 7:16 AM

#

!ask

delicate fieldBOT Oct 6, 2019, 7:16 AM

#

ask

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.
• 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.
• Keep your patience while we're helping you.

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

rich trout Oct 6, 2019, 7:17 AM

#

I'll respond here, from the code you dropped in #web-development

#

You likely need to specify "back_populates" on each relationship

#

What exactly is the error?

#

https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html#one-to-many is a good baseline for relationships

undone roost Oct 6, 2019, 7:22 AM

#

hey thanks

#

i ma having problems in entering data into it

#

You likely need to specify "back_populates" on each relationship

#

i will do that

#

back_populates is the keyword?

rich trout Oct 6, 2019, 7:22 AM

#

yes

undone roost Oct 6, 2019, 7:23 AM

#

and idk how to do that

#

did you check it's service code?

rich trout Oct 6, 2019, 7:23 AM

#

I'd also suggest not having things such as "num_of_audi"--that's not actually something you need to store

undone roost Oct 6, 2019, 7:23 AM

#

ohh right

rich trout Oct 6, 2019, 7:23 AM

#

You'd want to get a count(theater.audi), for example, instead of storing the number, so it doesn't get out of sync

#

Same with audi.bp

#

access that as audi.theatre.bp

undone roost Oct 6, 2019, 7:24 AM

#

ohh

#

i didn't know i can do that

#

in case of bp also?

#

can i use it like theatre.base_price

#

and i don't need to import them as foreign key right?

rich trout Oct 6, 2019, 7:25 AM

#

right, although you need to define the relationship instead of a foreign key id field

#

also, there's a typo, theare.id

#

This is the given example, so you should make sure your 3-part model matches its blueprint

#

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", back_populates="parent")

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    parent = relationship("Parent", back_populates="children")

undone roost Oct 6, 2019, 7:26 AM

#

i have this code

#

but i didn't understand what you meant by define the relationship

rich trout Oct 6, 2019, 7:26 AM

#

That is, theatre on your audi should be a reliationship, and it should also have a theatre_id that's a foreignkey

undone roost Oct 6, 2019, 7:27 AM

#

wait wait

#

i am confused

rich trout Oct 6, 2019, 7:28 AM

#

Your theatre has a audi variable, with a relationship to "Audi"

#

which is good

undone roost Oct 6, 2019, 7:28 AM

#

parent = relationship("Parent", back_populates="children") in my code this line will be theatre=relationship("Theatre", back_populates="Audi")?

rich trout Oct 6, 2019, 7:28 AM

#

back_populates points to the attribute on the other end of the relationship

#

so, in this case, back_populates="theatre"

undone roost Oct 6, 2019, 7:29 AM

#

damn

#

wait

#

first tell me one thing

#

how can i use column of one table in another table

#

?

rich trout Oct 6, 2019, 7:30 AM

#

class Theatre(Base):
    id = Column(Integer, primary_key=True)
    auditoriums = relationship("Auditorium", back_populates="theatre")

class Auditorium(Base):
    id = Column(Integer, primary_key=True)
    theatre_id = Column(Integer, ForeignKey('theatre.id'))
    theatre = relationship("Theatre", back_populates="auditoriums")

undone roost Oct 6, 2019, 7:30 AM

#

like i want to use base_price of theatre in seat class

rich trout Oct 6, 2019, 7:30 AM

#

Seat.auditorium.theatre.base_price

undone roost Oct 6, 2019, 7:31 AM

#

ohh just like that

#

okay

#

one more thing

#

if there is a column like theatre_id in above code and we are initializing it using the service code i made....so how do i enter the data there...i mean other data is being entered by user ...but this data is already present such as theatre_id

#

check my service code

#

part of it need to be changed

#

a few parts

rich trout Oct 6, 2019, 7:34 AM

#

It would need to, yes

undone roost Oct 6, 2019, 7:34 AM

#

just check the function save new audi

rich trout Oct 6, 2019, 7:34 AM

#

You'd provide a theatre object as the theatre value during init, or I believe a theatre id as theatre_id would do just fine

undone roost Oct 6, 2019, 7:35 AM

#

okay thanks for the help

#

i will correct my code and will show

#

in some time

#

bp = theatre.base_price so if i use this ....is fine?

#

in audi class

rich trout Oct 6, 2019, 7:40 AM

#

Not sure what you mean

#

If you've got an audi and want the base price, audi.theatre.bp should work

#

see here

#

https://docs.sqlalchemy.org/en/13/orm/tutorial.html#working-with-related-objects

undone roost Oct 6, 2019, 7:51 AM

#

and don't we need to define tablename?

rich trout Oct 6, 2019, 7:52 AM

#

I suppose you do, I'm more used to other orms where you don't, apologies

undone roost Oct 6, 2019, 7:52 AM

#

okay...so in this line theatre = relationship("Theatre", back_populates="auditoriums")......auditorium is my table name right?

#

auditoriums*

#

and Theatre is the class name of parent class

rich trout Oct 6, 2019, 7:54 AM

#

No, auditorium/s is the name of the related property on the theatre class

#

Yes, theatre is the class name of the parent class

undone roost Oct 6, 2019, 7:55 AM

#

ohh got it

#

class Theatre(db.Model):

""" Model for storing Theatre related details """
__tablename__ = "theatre"

id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(50), nullable=False)
public_id = db.Column(db.String(100), unique=True)
num_of_audi=db.Column(db.Integer)
description=db.Column(db.String(255))
base_price=db.Column(db.Integer,nullabe=False,default=200)
audi= db.relationship("Audi", back_populates="theatre")
def __repr__(self):
    return "<Theatre '{}'>".format(self.name)

class Audi(db.Model):

""" Model for storing auditorium related details """
__tablename__ = "audi"

id=db.Column(db.Integer, primary_key=True,autoincrement=True)
audi_name= db.Column(db.String(20),nullable=False)
rows = db.Column(db.Integer, nullable=False)
columns = db.Column(db.Integer, nullable=False)
public_id = db.Column(db.String(100), unique=True)
theatre_id = Column(Integer, ForeignKey('theatre.id'))
theatre = relationship("Theatre", back_populates="audi")
seat=db.relationship("Seat")

#

thanks god not muted this time

rich trout Oct 6, 2019, 7:56 AM

#

That seems right

undone roost Oct 6, 2019, 7:56 AM

#

like this

rich trout Oct 6, 2019, 7:56 AM

#

!codeblock

delicate fieldBOT Oct 6, 2019, 7:56 AM

#

Discord has support for Markdown, which allows you to post code with full syntax highlighting. Please use these whenever you paste code, as this helps improve the legibility and makes it easier for us to help you.

To do this, use the following method:

```python
print('Hello world!')
```

Note:
• These are backticks, not quotes. Backticks can usually be found on the tilde key.
• You can also use py as the language instead of python
• The language must be on the first line next to the backticks with no space between them

This will result in the following:

print('Hello world!')

rich trout Oct 6, 2019, 7:56 AM

#

Could be helpful

undone roost Oct 6, 2019, 7:56 AM

#

okay thanks man...that was a great help

#

i was stuck since morning

#

one more thing

#

i haven't inheriteddeclarative base

#

but db.model

rich trout Oct 6, 2019, 7:57 AM

#

I didn't notice that, db.model is from django isn't it

#

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

#

and then use that Base in your models

undone roost Oct 6, 2019, 8:00 AM

#

no no

#

it's flask

#

db = SQLAlchemy()

#

so i need base for inheritance and db wont work

#

do i need to change all my columns too?

rich trout Oct 6, 2019, 8:11 AM

#

Oh, then what you have should be fine

#

since db.Model is what flask-sqlalchemy uses

#

You may have to adjust your relationships to this kind of pattern

#

        backref=db.backref('posts', lazy=True))

#

instead of back_populates

undone roost Oct 6, 2019, 8:17 AM

#

ohh damn

#

i changed it all lol

#

will change it back

#

backref=db.backref('posts', lazy=True)).....posts is tablename of the relating class?

#

i think backpopulates is right here

#

To establish a bidirectional relationship in one-to-many, where the “reverse” side is a many to one, specify an additional relationship() and connect the two using the relationship.back_populates parameter:......this is given in documentation of sqlalcehmyorm

#

my case is same ...reverse side is many to one

undone roost Oct 6, 2019, 11:24 AM

#

https://paste.pydis.com/simufeleve.py check the model @rich trout

rich trout Oct 6, 2019, 11:37 AM

#

Seems about right

undone roost Oct 6, 2019, 11:43 AM

#

thanks

crisp tundra Oct 6, 2019, 4:40 PM

#

await conn.execute('''INSERT INTO idea_names (one, two, three, four, five, six, seven)
VALUES ($1, $2, $3, $4, $5, $6, $7) WHERE ''', z[0], z[1], z[2], z[3], z[4], z[5], z[6])

#

How would I add the variable for WHERE?

#

Right after, z[6]?

undone roost Oct 6, 2019, 4:53 PM

#

i think where z[0]=value1,z[1]=value2.....and so on

crisp tundra Oct 6, 2019, 9:41 PM

#

Thanks!

slim barn Oct 7, 2019, 12:25 AM

#

I'm trying to get started with SQL, but what I can see online is rather confusing as to how to use it in python. From what I can tell, things like mysql, sqlite3, etc are all places that host the table, rather than actually being required to use one. If this is the case, is there a way to do it without online hosting and do I need modules to do this?

harsh pulsar Oct 7, 2019, 1:11 AM

#

@slim barn that's true for most databases, but sqlite3 is the exception. sqlite3 is a file format and C library, and python has a built-in library for interacting w/ sqlite3 database files

#

so you can use sqlite3 databases without external dependencies or running a separate server

slim barn Oct 7, 2019, 1:21 AM

#

Do you know anywhere online I can find tutorials on it? My searches have been coming up lackluster. Specifically on the initial construction

harsh pulsar Oct 7, 2019, 1:29 AM

#

i dont know of any good ones off the top of my head... if i fine one i will ping you

slim barn Oct 7, 2019, 1:34 AM

#

thanks!

edgy helm Oct 7, 2019, 3:29 AM

#

I finished the intro to relational databases course in Udacity, they show how to build a simple database with postgreSQL.

#

I would be interested in kwnowing about learning resources too.

undone roost Oct 7, 2019, 8:02 AM

#

@crisp tundra did that work ?

tender helm Oct 7, 2019, 11:34 AM

#

Hey, i'm building a web scrap that scrap articles from different sites. The scraper will run 4 processes at the same time each processes scraping one site.
Before a process can insert an article into the database (Postgresql) it needs to check for duplicates in the table. I need a way to make sure that the
select + insert transactions are done one by one. Setting the transaction level to SERIALIZABLE seems like it would just cause a lot of serialization_failure
errors (which would need to be handled by retires)

Is there any other way to do this at the database level?

Doing it in python I would set up a queue that processes can add items to and one processes that dequeue items and add them to the database synchronously.
Would that be a good way of doing it if it's not possible via the database?

Sorry for the long question.

rich trout Oct 7, 2019, 12:12 PM

#

Make the relevant columns UNIQUE, and the database will check for you

tender helm Oct 7, 2019, 12:14 PM

#

ah sorry i forgot to mention that there is no real unique value. I get close matches from the database and then i need to do the last check inside the python code

rich trout Oct 7, 2019, 12:20 PM

#

https://www.postgresql.org/docs/9.1/transaction-iso.html might be good for reading, but in your case a queue and a synchronous worker for the database does sound like the best solution

tender helm Oct 7, 2019, 12:23 PM

#

Will take a look at that, thanks for the feedback! 👍

primal valve Oct 7, 2019, 4:09 PM

#

im using asyncpg, is there a way to compress this into 1 db call? ```py
async def untrust(self, ctx, member: discord.Member):
result = await self.bot.db.fetch('SELECT FROM sc_trusted WHERE user_id = $1', member.id)
if not len(result):
await ctx.send('User is not trusted')
else:
await self.bot.db.fetch('DELETE FROM sc_trusted WHERE user_id = $1', member.id)
await ctx.send(f"Untrusted {member.mention}")

fluid lantern Oct 7, 2019, 4:14 PM

#

I'm not super familiar with pg, but you might be able to just run the deletion and retrieve the number of affected rows. Is it 0, the user was never in there to begin with.

primal valve Oct 7, 2019, 4:14 PM

#

im not either

#

how would i do that?

#

the deletion doesnt return anything

rich trout Oct 7, 2019, 4:28 PM

#

If you use execute() instead of fetch(), it will return the execution result

#

https://magicstack.github.io/asyncpg/current/api/index.html?highlight=fetch#asyncpg.connection.Connection.execute

patent glen Oct 7, 2019, 4:34 PM

#

postgresql also supports a "returning" clause on delete which lets you fetch normal results like a select from a delete statement

#

this isn't portable to other forms of sql though, whereas using the result count generally is

primal valve Oct 7, 2019, 4:48 PM

#

    async def untrust(self, ctx, member: discord.Member):
        result = await self.bot.db.execute('DELETE FROM sc_trusted WHERE user_id = $1', member.id)
        if result == 'DELETE 1':
            await ctx.send(f'Untrusted {member.mention}')
        else:
            await ctx.send('User not trusted')
``` this worked

quick hill Oct 7, 2019, 9:33 PM

#

can doing multiple inserts in one SQL execute be quicker than multiple executes

harsh pulsar Oct 7, 2019, 9:38 PM

#

slightly

#

especially if the network between you and the database server is slow

#

executemany is your friend

quick hill Oct 7, 2019, 10:21 PM

#

ok, it's becuase I got a website and every time something happens it's logged to a database but it gets inserted into so fast it errors becuase it's already mid execution, and I'm trying to think of a way around this

harsh pulsar Oct 8, 2019, 12:46 AM

#

What do you mean "it errors because it's already mid execution"

#

Are you using 1 connection asynchronously?

torn sphinx Oct 8, 2019, 1:51 AM

#

what does asynchronously mean..

#

I come across this term so often.. synchronous and asynchronous requests.. would like to understand it

long stream Oct 8, 2019, 1:58 AM

#

@torn sphinx here's a great write-up! https://realpython.com/async-io-python/

Async IO in Python: A Complete Walkthrough – Real Python

This tutorial will give you a firm grasp of Python’s approach to async IO, which is a concurrent programming design that has received dedicated support in Python, evolving rapidly from Python 3.4 through 3.7 (and probably beyond).

torn sphinx Oct 8, 2019, 10:15 AM

#

Hello coders! Recently, I swapped from SQLite3 to PostgreSQL and I am using asyncpg, since I use it for my Discord bot. And the question is, what do I do better?

Make a pool and close it when the bot disconnects

async def do_connect():
return await asyncpg.connect(**credentials)

async def do_close(con=None, cur=None):
if con is not None:
await con.close()
# print("[POSTGRES] Connection closed")

if cur is not None:
    await cur.close()
    # print("[POSTGRES] Cursor closed")

async def find_xp(guild_id, user_id):
con = await do_connect()
row = await con.fetchrow(
f"SELECT balance FROM public.xp WHERE user_id={str(user_id)} AND guild_id={str(guild_id)};")
await do_close(con)

if row is None:
    return None

return row['balance']

And I'm just really confused, should I use second one (which I'm using atm tho) or should I rewrite all that with using of pools? Would be really glad if someone explained me something about that :>

TAG: pool vs connect close

midnight verge Oct 8, 2019, 10:16 AM

#

@torn sphinx definitely make a connections pool, even with only one if that fits

torn sphinx Oct 8, 2019, 10:16 AM

#

And yeah, I call find_xp() at least two times on each message event

#

aha

midnight verge Oct 8, 2019, 10:16 AM

#

Yeah make a pool

torn sphinx Oct 8, 2019, 10:16 AM

#

but what's the difference between them?

#

the pool and con close

midnight verge Oct 8, 2019, 10:17 AM

#

The pool will not close connections, it will keep them in memory in order to reuse them and save the cost of establishing and closing a connection everytime you need to access the database

torn sphinx Oct 8, 2019, 10:17 AM

#

ooo, now I see

#

so imma definitely rewrite all that

midnight verge Oct 8, 2019, 10:17 AM

#

It also allows you to optimize the max connections count according to your infrastructure, constraints, etc.

torn sphinx Oct 8, 2019, 10:18 AM

#

okie, thank you very much!

midnight verge Oct 8, 2019, 10:18 AM

#

👍

torn sphinx Oct 8, 2019, 10:19 AM

#

I've added small tag, so it'd be easier for someone else to search for it :>

proven wagon Oct 8, 2019, 5:27 PM

#

How do i add a not null column to a table when the table already has rows in it

#

__
__Also, can i update a column in a row without first getting the row?
get member -> update member
VS
just update member

proven wagon Oct 8, 2019, 5:44 PM

#

you passed the 3 arguments when executing right?

vocal moon Oct 8, 2019, 6:54 PM

#

@bot.command()
async def createFile(ctx):
    await ctx.send('`Enter your ROBLOX name.`')
    
    def check(m):
        return m.author == ctx.author

    robloxIgn = await bot.wait_for('message', check=check)

    await ctx.send("Enter your callsign")
    callsign = await bot.wait_for('message', check=check)

    c.execute("INSERT INTO this (roblox) VALUES (?)",
              str(robloxIgn))
    
    conn.commit()

#

This aint working.

#

I can't figure out why.

#

I'm getting:

Command raised an exception: ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 434 supplied.

proven wagon Oct 8, 2019, 7:35 PM

#

you are inputting the ~~message object~~ representation of a message object

vocal moon Oct 8, 2019, 7:53 PM

#

I dont understand @proven wagon

proven wagon Oct 8, 2019, 7:54 PM

#

robloxIgn is not the content of the message

#

its the message object

#

you want to input robloxIgn.content

vocal moon Oct 8, 2019, 7:54 PM

#

oh

#

ok

#

so would it be str(robloxIgn.content))

proven wagon Oct 8, 2019, 7:55 PM

#

.content is already a string

vocal moon Oct 8, 2019, 7:55 PM

#

ok

#

so i dont need to change it

#

ok thx

#

I'm still getting ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 5 supplied.

#

@proven wagon (Orange is sorry for the pings 😓 )

proven wagon Oct 8, 2019, 8:00 PM

#

you are using sqlite3 right

vocal moon Oct 8, 2019, 8:00 PM

#

Yeh

proven wagon Oct 8, 2019, 8:01 PM

#

then its supposed to be py c.execute("INSERT INTO this (roblox) VALUES (?)", (robloxIgn.content,))

vocal moon Oct 8, 2019, 8:01 PM

#

Oh

#

does it have to be the same line?

proven wagon Oct 8, 2019, 8:01 PM

#

Not afaik

quick hill Oct 8, 2019, 8:17 PM

#

            await connection.execute("INSERT INTO table(userid, datetime, status) VALUES ($1, $2, $3)", (id, datetime.datetime.utcnow(), status))
``` `asyncpg.exceptions._base.InterfaceError: the server expects 3 arguments for this query, 1 was passed`

proven wagon Oct 8, 2019, 9:17 PM

#

you passed a (1) tuple of arguments (remove the tuple and pass them as individual arguments)

#

            await connection.execute("INSERT INTO table(userid, datetime, status) VALUES ($1, $2, $3)", id, datetime.datetime.utcnow(), status)

#

@quick hill

harsh pulsar Oct 8, 2019, 9:53 PM

#

@quick hill if thats asyncpg their API is nonstandard

torn sphinx Oct 8, 2019, 11:08 PM

#

Attempting to use pymongo and get
query() got an unexpected keyword argument 'lifetime'
when trying to connect through
client = pymongo.MongoClient("mongodb+srv://{user}:{pass}@{ip}:27017")
{}'s are filled just removed to send here

torn sphinx Oct 9, 2019, 1:00 AM

#

fixed by removing +srv and putting all my shard ips

real timber Oct 9, 2019, 1:14 PM

#

how to get the average of all columns by a group in sql?

#

i thought that i could so

select avg(*) from blah group by something

#

can anyone suggest a programme that will enable me to run sql queries on a csv file using mac osx

rich trout Oct 9, 2019, 1:26 PM

#

It is that syntax

real timber Oct 9, 2019, 1:26 PM

#

@rich trout what?

rich trout Oct 9, 2019, 1:27 PM

#

SELECT AVG(User.views) FROM Users WHERE User.name LIKE "bob"

real timber Oct 9, 2019, 1:27 PM

#

what if you want all

#

not just user.views

rich trout Oct 9, 2019, 1:28 PM

#

What are you averaging?

real timber Oct 9, 2019, 1:28 PM

#

basically i want to aggregate a dataset on a variable

#

the lot, i want to aggregate on a variable as i'm only allowed that level of granularity locally

rich trout Oct 9, 2019, 1:28 PM

#

Define aggregate on a variable

real timber Oct 9, 2019, 1:28 PM

#

mean

rich trout Oct 9, 2019, 1:29 PM

#

What's your data format

#

and what's your desired output

real timber Oct 9, 2019, 1:29 PM

#

i'm not trying to average names

#

numbers

#

i'm expecting the average for each group?

rich trout Oct 9, 2019, 1:29 PM

#

Average names?

real timber Oct 9, 2019, 1:29 PM

#

no i said they're numbers

rich trout Oct 9, 2019, 1:29 PM

#

For a table formatted like so:

#

ID | NAME | VIEWS
0  | BOB  | 12
1  | Bob  | 13

real timber Oct 9, 2019, 1:30 PM

#

anyone know how to import csv into dbeaver? seems i don't have flat file options 🤔

rich trout Oct 9, 2019, 1:31 PM

#

Select AVG(User.views) from users.. will go down each user

#

and average the views column

#

Filtering on the user.name column

real timber Oct 9, 2019, 1:31 PM

#

what if you have 15 columns or so, and want to aggregate on one of them and get the averages for the rest

#

tbf i could just enter all the columns manually

rich trout Oct 9, 2019, 1:31 PM

#

You would probably have to

#

Select AVG(column1), SUM(column2), etc

real timber Oct 9, 2019, 1:32 PM

#

ok , i figured I was missing something really obvious

rich trout Oct 9, 2019, 2:01 PM

#

If you're looking to group rows by one value, and then average those groups, you can do a GROUP BY clause to do it

#

See here: https://www.w3resource.com/sql/aggregate-functions/sum-with-group-by.php

w3resource

SQL SUM() with group by - w3resource

SQL SUM() with GROUP by: SUM is used with a GROUP BY clause. The GROUP BY clause is required when using an aggregate function along with regular column data, otherwise the result will be a mismatch.

#

@real timber

real timber Oct 9, 2019, 3:03 PM

#

@rich trout yes - i said group by in my first comment

long stream Oct 9, 2019, 3:09 PM

#

I'm starting to learn Django, and for a first project I'm processing some company data from a large spreadsheet into a DB, and then using Django to show some metrics from the collected data in a browser. Where within the typical Django project directory structure would be most appropriate to put the code that deals with reading and storing data from the Excel files?

#

Just trying to get opinions on best practices so I don't start out making weird habits.

rich trout Oct 9, 2019, 3:10 PM

#

I appear to have missed that, apologies

#

Perhaps as a django command

#

https://docs.djangoproject.com/en/2.2/howto/custom-management-commands/

long stream Oct 9, 2019, 3:21 PM

#

@rich trout it looks like you intended that for me rather than the other person you're helping yeah? This looks like what I'm looking for I think.

rich trout Oct 9, 2019, 3:21 PM

#

yeah

#

It allows you to run scripts within your django environment, permitting model usage and whatever else you have. I've used it previously for things like data import, so that should probably fit what you're doing quite well

long stream Oct 9, 2019, 3:23 PM

#

Awesome, thanks!

unborn sentinel Oct 9, 2019, 3:24 PM

#

Minor style question for SQL code: is it usually cleaner to use CONCAT() or just use plus sign concatenation to combine strings?

rich trout Oct 9, 2019, 3:32 PM

#

They do not actually perform the same function, interestingly enough

#

This depends on your database

#

On mssql, CONCAT implicitly converts null values to empty strings

unborn sentinel Oct 9, 2019, 3:34 PM

#

Interesting

#

I believe the one I'm using is.... SQL Server 2017?

#

I think that's right?

rich trout Oct 9, 2019, 3:34 PM

#

When you work with strings with a null value, the result of the concatenation depends on the session settings. Just like arithmetic operations that are performed on null values, when a null value is added to a known value the result is typically an unknown value, a string concatenation operation that is performed with a null value should also produce a null result. However, you can change this behavior by changing the setting of CONCAT_NULL_YIELDS_NULL

#

This is for +

#

And this applies to 2017

#

IMO, the actual CONCAT function is clearer

unborn sentinel Oct 9, 2019, 3:36 PM

#

Hmm... it seems to be pitching a fit saying that ", " isn't a column name

#

So I must be doing something wrong

rich trout Oct 9, 2019, 3:37 PM

#

iiinteresting

#

Sounds like a mismatched quote

unborn sentinel Oct 9, 2019, 3:37 PM

#

Did that for both CONCAT() and +

rich trout Oct 9, 2019, 3:37 PM

#

what's your query?

unborn sentinel Oct 9, 2019, 3:37 PM

#

📎 unknown.png

#

Full query is:

SELECT emp.employee_id, emp.last_name + ", " + emp.first_name
FROM dbo.Employee as emp;

#

Just starting it, mind

rich trout Oct 9, 2019, 3:38 PM

#

Try single quotes

unborn sentinel Oct 9, 2019, 3:38 PM

#

Have a ways to go

#

Ah yep, single worked

#

That's just weird to me

rich trout Oct 9, 2019, 3:39 PM

#

Oh me too

#

But after spending a few months with someone who worked with actual visual basic in 2019, I figured it was worth a shot

unborn sentinel Oct 9, 2019, 3:40 PM

#

For sure, that would have driven me crazy. Thanks, Bast

rich trout Oct 9, 2019, 3:41 PM

#

It seems that someone has decided that " are used to group database object names with spaces and similar, whereas ' are just applied to strings

unborn sentinel Oct 9, 2019, 3:41 PM

#

Ohhhhhh that's right

rich trout Oct 9, 2019, 3:41 PM

#

And you can disable said feature with SET QUOTED_IDENTIFIER ON

unborn sentinel Oct 9, 2019, 3:41 PM

#

I feel like I remember encountering that when I was dealing with Crystal Reports

rich trout Oct 9, 2019, 3:41 PM

#

Probably

unborn sentinel Oct 9, 2019, 3:41 PM

#

And no, I'm not touching any settings on this thing

rich trout Oct 9, 2019, 3:41 PM

#

lol

unborn sentinel Oct 9, 2019, 3:41 PM

#

It's the server for our Accounting CS program

rich trout Oct 9, 2019, 3:41 PM

#

Good idea

unborn sentinel Oct 9, 2019, 3:41 PM

#

So any change might fuck EVERYTHING up

#

The account I'm using to access it is read-only

rich trout Oct 9, 2019, 3:42 PM

#

Yeah, that setting would uh, cause problems

unborn sentinel Oct 9, 2019, 3:42 PM

#

Rather not have a "Little Bobby Drop Tables" moment

rich trout Oct 9, 2019, 3:42 PM

#

lol

#

SELECT "user name" FROM `badly designed table` WHERE "user id" = 'Jill';

unborn sentinel Oct 9, 2019, 3:43 PM

#

That causes me all the hurt

#

Oh, interestingly

#

Nope never mind, the interesting thing was not actually a thing

rich trout Oct 9, 2019, 3:45 PM

#

Oh?