databases

[rigid yacht]

hello everyone good morning

[jaunty fulcrum]

!close

[torn sphinx]

I currently use sqlite for my discord bot and am planning on starting to use tortoise, what would be the best database to use with tortoise to get the most out of its features? I'm seeing stuff like select_for_update which looks really nice bc it locks the relevant table until the update is done, but idk if sqlite supports that

[fading patrol]

https://dev.mysql.com/doc/refman/8.0/en/backup-and-recovery.html

[fading patrol]

IDK either, but if you do need a fully featured SQL db, Postgres is a good bet. You can spin up a free one on Heroku up to 250mb

[simple robin]

Should I use the connect() function everytime I want to add something then close(), or just put one connect() at the start of my code ? (I'm using SQLite3)

[proven gyro]

i am trying to sort tags based on how many times a tag has been used, this is what i came up with

>>> a = ExperiencePageTag.objects.values_list('tag_id').annotate(tag_count=Count('tag_id')).order_by('-tag_count')
>>> [Tag.objects.get(id=i[0]) for i in a]
[<Tag: themselves>, <Tag: fact>, <Tag: about>, <Tag: because>, <Tag: scene>, <Tag: sing>, <Tag: image>, <Tag: need>, <Tag: box>, <Tag: since>, <Tag: force>, <Tag: management>, <Tag: recently>, <Tag: them>, <Tag: ago>, <Tag: question>, <Ta
g: help>, <Tag: training>, <Tag: affect>, <Tag: fear>, <Tag: q>]

is this good practice ?

[fading patrol]

Cursors are disposable, connections you usually keep open until you're done

[simple robin]

Okay so Im gonna put one connect() at the start and call it done

thanks !

[manic shale]

Hi everyone, I am struggling with a race condition in a python script that archives database rows.

This is the query:

"SELECT * "
 f"FROM `{table_name}` "
 "WHERE "
 f"start_time >= '{start_time}' "
 f"AND start_time <= '{finish_time}' "
 "AND is_billed = 1 "
 "AND is_invoiced = 1 "
 "ORDER BY resource_id "
 f"LIMIT {offset:d}, {limit:d}"

Here is the relevant code:

offset = 0
total_archived = 0
while True:
       to_archive_query = self.get_metrics_rows_to_archive_query(
               ...
               offset=offset,
               limit=self.mysql_client.mysql_cleanup_limit,
        )

        res = self.mysql_client.run_mysql_query(to_archive_query)

        found_rows = res.rowcount
        total_archived += found_rows

        insert_values, delete_values = self.concat_all_row_values(res)

        if found_rows == 0:
            break

        if insert_values:
            self.run_insert_values_query(...)

        if delete_values:
            self.run_delete_values_query(...)

        offset += found_rows

The race condition happens when the offset becomes larger than the number of rows left in the table, the SQL statement returns no rows.

Step-by-step example:

Offset Number of rows left
0 50 000
10 000 40 000
20 000 30 000
30 000 20 000

Offset is at 30 000, while there are only 20 000 rows left, therefore the SELECT statement returns no rows and the script ends.

So, every time my script runs, it leaves some rows untouched, when they really should be archived. Does anyone have a suggestion for a fix ?

[potent kayak]

hi i am new to SQL and MySql
can you guys suggest me some text editors to go with
moreover, i tried using PopSql byt facing issues so if anyone here have used it please help me out
Thanks (:

[torn sphinx]

for mySQL I think it's best to just use the mySQL workbench, have you tried it ?

[kindred spear]

I generally use DBeaver

[rapid knoll]

kk

[torn sphinx]

I use db browser for sqlite

[storm knot]

?help

$help

[real crow]

​No Category:
  help Shows this message

Type $help command for more info on a command.
You can also type $help category for more info on a category.

[storm knot]

m!help

[potent kayak]

hi i have once installed .msi file of MySql but it didnt installed Workbench idk why
and to install that i have to re install MySql i think i might lose all my databases if i do that

[grim vault]

That's not how it works, you have a offset and a limit in the LIMIT

Offset | Limit | TableRows
-------+-------+--------------
     0 | 10000 |     1 - 10000
 10000 | 10000 | 10001 - 20000
 20000 | 10000 | 20001 - 30000
 30000 | 10000 | 30001 - 40000

The problem is most likely that you delete rows (looking at your code) so the offset is wrong.

If you've selected 0 - 10000 rows and delete 5000 rows in that range your next select with 10001 - 20000 will miss out on 5000 rows.

[lunar pier]

Hello, anyone there? Can someone help me understand, why row_factory returns me list, not row objects?

[brave bridge]

Can you print what it returns?

[lunar pier]

It returns me list of tuples, like [(1, 2, 3), (2, 4, 6)]

With items from database, but i want to get row objects, to access columns by names

[brave bridge]

I think you need to set the row_factory before creating the cursor

[lunar pier]

Hmm, thanks. I can create second cursor when this function called and cursor.close() before return :)

[fallen vault]

So I’m working on a login. I generate a 32 character salt and add it to the users password then encrypted it using sha256. Problem is when I added it to my database it turned it into a blob and I can compare now. What did I do wrong lol.

[slender atlas]

Probably because your encryption function returns a byte-string

[fallen vault]

How would I convert the blob back to I guess byte-string?

[snow niche]

getting this error.. used in a discord bot

A weird error occured:
Command raised an exception: TypeError: 'coroutine' object is not subscriptable 

code: https://github.com/DTS-11/PizzaHat

ping on reply thanks

[agile sinew]

What line?

[golden vector]

Can I use .pgpass for a connection to my postgresql db with psycopg2?
Or is using another file "valid/secure enough"?

[formal cape]

Hello, I have an existing database in MongoDB with an existing collection

I'm trying to insert data into it via pymongo

Once I connect to the client and open the desired collection, I get this error when trying to do anything with it (like insert_one):

pymongo.errors.OperationFailure: bad auth : Authentication failed., full error: {'ok': 0, 'errmsg': 'bad auth : Authentication failed.', 'code': 8000, 'codeName': 'AtlasError'}

[hidden osprey]

I use db browser for SQlite there is a way to export as json. file -> export -> export as json. Im trying to do that every week on a schedule and i need to do it with code can someone help. I use aiosqlite.

[fallen vault]

Aren’t json similar to a dict? Couldn’t you loop through a fetchall to create a json file.

[hidden osprey]

db = sqlite3.connect('data/database.db')
cur = db.cursor()
print(cur.fetchall())```Like this?

[fallen vault]

You’ll need a cut.execute but yeah.

Then a for loop. Something like for entry in list

[hidden osprey]

thank you i got it

[grim vault]

There is also .iterdump() if you want to make some kind of restoreable backup.

[torn sphinx]

python

hi

I'm new to Postgresql and python

I'm looking to create a website with flask and postgres

Where exactly am I suppose to install postgresql?

A remote computer like a raspberry pi, or my personal computer?

I thought that installing the database on my personal computer wouldn't make sense considering it's not a long term project (more than a year), and it's more of a project that I make once, then give it to the client (will no longer be in contact with the client after the project is done, so it wouldn't make sense to keep it on my computer right?)

[nova cove]

https://www.postgresql.org/

[torn sphinx]

yeah thanks

but

I'm asking more about the location

Remote computer vs PC?

[nova cove]

like when you deploy it?

[torn sphinx]

Not exactly sure what I should tbh

Do you recommend anything?

I

[nova cove]

you install postgres on your pc..

[torn sphinx]

Yeah but I'll be done with the project, and will move away

to a different part of the country (so I don't want to keep it on my system)

So it would make sense to install it on a AWS instance or a raspberry pi correct?

[nova cove]

thats not how it works. if you are just testing your db, you wouldn't test it on AWS

[torn sphinx]

I'm not testing the database

I will be using it

[nova cove]

download pgAdmin to manipulate your db

[torn sphinx]

You still think I should use install it on my computer?

[nova cove]

yes you have to..

[torn sphinx]

Doesn't really make sense considering my situation

[nova cove]

maybe i dont quite undestand

oh wait you are moving

[torn sphinx]

Are you familiar with the idea of remote SSHing into another computer

Yeah

[nova cove]

so you wont be taking your setup with you?

[torn sphinx]

Exactly

And there will be new project maintainers

So I was looking for alternatives

[nova cove]

ahh ok

well if you aren't deploying the app/making it public you wouldn't have to store it on AWS or a raspberry pi

but

[torn sphinx]

I am deploying it haha

[nova cove]

if you are working on it before your move

[torn sphinx]

Once it's ready ofc

[nova cove]

then it makes sense

as you wont want to lose anything

[torn sphinx]

So are you saying I should install it on a raspberry pi?

[nova cove]

well if you have done work prior to the move, then yes you could store it somewhere like a raspberry pi where you would be able to access it elsewhere on another system

im so sorry if im not giving u a striaght answer

[torn sphinx]

Well I will be completing the work before the move

And then deploying it

Then I leave and move

[nova cove]

ahh ok then yu could just deploy it and store it somewhere that can be accessed on other system

[torn sphinx]

Such as?

[nova cove]

maybe make a git repo that has your code and everyuthing

[torn sphinx]

Where will the database reside?

But yeah I do have a git repo so my other teammates can collaborate with me

[nova cove]

you could prolly deploy the db to like aws

idk how it works though

https://aws.amazon.com/rds/?nc2=h_ql_prod_fs_rds

[torn sphinx]

Any other options are you aware of?

[nova cove]

there might be options on Azure and GCP.

i have minimal experience on cloud services which i am working on gaining experience in

[torn sphinx]

So I don't really need a raspberry pi correct? I was initially thinking about using it to store and access the database via SSH

If cloud services are better then I will use them instead

[nova cove]

ehh you could prolly. i also don't have raspberry pi experience

[torn sphinx]

Ah I see. So generally you've worked with databases and your code on 1 system correct?

[nova cove]

ye

i have deployed sqlite dbs to vps

but thats molstly it

[torn sphinx]

what vps did you use?

[nova cove]

vultr

[torn sphinx]

oh alright never heard of it

[round elk]

@torn sphinx you prob want a remote linux server with apache installed and postgresql installed. you can use python apps to interact with it with a db connection

[torn sphinx]

I have a raspberry pi that I can ssh into

Would that work?

[round elk]

a pi may work if there are compatible packages, which i'm fairly sure there are

[torn sphinx]

Okay so once I can safely destroy my local database once I move right and the new project maintainers can maintain the database on the raspberry pi?

[round elk]

sure, just have a backup solution for the pi!

[torn sphinx]

ohh great

[round elk]

you have some steps before you get there though

[torn sphinx]

What do you mean

[round elk]

setting up the pi and software

[torn sphinx]

Oh yeah it's setup

I setup port forwarding

[round elk]

nice, you are good to go then, backup backup backup

[torn sphinx]

And I can ssh or I can also hook up some peripherals

So my teammate who will be working on the database as well can just ssh into the pi

and manage the database?

[round elk]

just backup the db if you are working it off the pi

script to email it, or upload to google drive hourly or when changes are made

depending on size

[torn sphinx]

can backups be made to a repository (git repository)?

[torn sphinx]

what about this?

[round elk]

idk, don't think it can be used that way, but maybe small size db

[torn sphinx]

sorry for the noob questions btw

okay alright will use google drive to backup

[round elk]

pi's are solid, but the sd cards not so much

[torn sphinx]

right haha

Is it recommended to manage databases over ssh, or should I should some sort of software instead?

[round elk]

i'd write a custom app

[torn sphinx]

what?

[round elk]

a python app to interface to db

django mabye ran off the pi

[torn sphinx]

Oh well I'd rather focus on actually building the website

[round elk]

web interface

gotcha

[torn sphinx]

Yeah I'm super confused right now

Some people are suggesting it's easier to have the database on my system

Although like I said, I won't be keeping it on there anywhere once I deploy the website

[round elk]

lol, you want to make a website that accesses a db that you and others are creating?

[torn sphinx]

Ok so

I need to make a website for a client with flask and postgres. Since I won't be maintaining the project after I finish my role, and will be uninstalling postgres and whatnot off my system (the python code will be held in a git repository), I am wondering what is an easy solution so I can do that, all while others can manage the database too

a client/customer

I also need to decide what host provider I should use.

[round elk]

oof, ok, so like others said, cloud, aws, digital ocean, vps with db installed

or a pi setup hooked to the clients network, but that's kind of hacky for professional

[torn sphinx]

okay so no pi

What's the easiest solution to hold the database then?

[round elk]

lol, they don't have a server?

[torn sphinx]

Sorry, I meant I won't use the raspberry pi as the computer to hold the database

After what you said

I have the pi though, not the client haha

[round elk]

if they have a server onsite, you could just install it there, a cheap server will handle way more than a pi

[torn sphinx]

No server I don't think

So I guess pi and the onsite server are off the list

I guess that leaves me with using cloud to hold my database right?

[round elk]

for always on access and prob some built in backup, yea

[torn sphinx]

Okay so everyone who will manage the database will work on the cloud right?

[round elk]

if it's not huge db you can prob find a reasonable priced plan

[torn sphinx]

Do our flask code need to be on there too? Or is there a way to connect our code that's on our Personal Computers to the cloud

where the database is

[torn sphinx]

yeah idk about the size of the db

[foggy lake]

can anyone tell me a good resource for learning sqlite3

not a course but a good documentation would work

i know sql already

[cedar lichen]

for i in range(10000):
    f = db2.find_one({"_id":i})
    f["antiher"] = "lol"+str(i)
    id = f["_id"]
    db2.update_one({"_id":id},{"$set":f})``` I want to update all items, but the for loop does not iterate 10k times I guess, it stops at 6k, is there any other way or can anyone tell me how to fix this? thanks!

Its mongo db btw

[burnt turret]

https://docs.python.org/3/library/sqlite3.html

[foggy lake]

already saw this one , a bit more comprehensive would be good

nvm i will work with that

[torn sphinx]

anyone knows MySQL and python connection?
I want to get every possible COMBINATION of 2 columns
is it possible
(i do not want to create new tables for combinations, just use the stuff, and put it aside)

[torn sphinx]

is it possible to edit a sql file in a google drive or cloud storage using their api?

[thorny monolith]

hi

[ivory moat]

ReferenceError: weakly-referenced object no longer exists how to correct this error

[snow niche]

idk I executed a warn command which is in cogs/mod.py and creation of table is in cogs/events.py

[agile sinew]

Idk that much sqlite3 but try changing the """ to only "

and import it i guess

[agile sinew]

...

[snow niche]

it's Postgres.. and in bot.py I have it as self.db so I can access it anywhere in my bot

[agile sinew]

so change wat i said too i gues

[snow niche]

I don't think that has to do with anything

[plain moat]

How can one get all the duplicated records using SQL?

[fading patrol]

That's a Google question: https://learnsql.com/cookbook/how-to-find-duplicate-rows-in-sql/

[plain moat]

sorry I will rephrase, all the columns of duplicated records

[fading patrol]

Show an example?

[snow niche]

error: ```py
Traceback (most recent call last):
File "/workspace/cogs/mod.py", line 372, in warn
count = len(data[3])
IndexError: list index out of range

code: https://github.com/DTS-11/PizzaHat/blob/main/PizzaHat/cogs/mod.py

[brave bridge]

Do you understand what the error means?

!e
You're trying to index a list too far:

xs = [1, 2, 3]
print(xs[5000])

[delicate field]

@brave bridge :x: Your eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 2, in <module>
003 | IndexError: list index out of range

[snow niche]

so how do i fix it

change data[3] to data[2] or smth?

[brave bridge]

Well, you'll need to find out why the list you're getting from warn_log is too small. You can either use a debugger or log the list that you're getting.

Why do you return an empty list from warn_log if no entry is found? Do you handle that case when calling it?

[snow niche]

no

[brave bridge]

right

[snow niche]

ok ill now tell the truth

im new to pg and i watched a vid with warn command sets with pg

and i followed it

[brave bridge]

so what's going on is that your query doesn't find anything

[snow niche]

ok

[snow niche]

so i should do:

if data:
    return data # ?

[brave bridge]

I don't know what you need to do. You need to think in which cases the warn_log for a user is not found, and what to do in that case if you want to warn them

I think it's a bad idea to just copy the code from a tutorial into a real application. That's not how programs are normally developed.

Usually you start with something simple - a core that you'll build on later. For example, don't save the warns to a database, just keep them in memory. When you're done with that simple version of the feature, you can make it more involved: save the state to a database, or perhaps account for some edge cases (like warning a bot or warning yourself)

[snow niche]

okay thanks

[brave bridge]

It seems like in your case, if the user doesn't have any previous warn, the warn_log table doesn't have an entry for them.

I don't see why you need to fetch that information before warning the user, though.

[snow niche]

if not data:
    print("No data")
else:
    return data
```this might fix it?

[brave bridge]

where do you want to put that?

[snow niche]

in warn_log

i replaced return [] with that

[brave bridge]

Well, now it will just return None instead of [] if there's no entry yet

[snow niche]

yeah

[brave bridge]

that doesn't help

[snow niche]

still says prblem with line 373

[brave bridge]

Why do you need to fetch the warn log entry before you warn the user?

i.e. why are you calling warn_log on line 372?

[snow niche]

uhh

[snow niche]

so instead of warn_log it should be warn_entry?

also to get the warn count

[brave bridge]

Why do you need the warn count as of before issuing the warning?

[snow niche]

so i should first call warn_entry then warn_log to get count.. is that right?

[brave bridge]

maybe

[sly pivot]

any knows any api that can provide gaming news or anime news

[silver hemlock]

https://github.com/PyMySQL/mysqlclient/blob/main/MySQLdb/constants/FIELD_TYPE.py

Do these numbers have any significant meaning ?

are they related to MySQL itself ? or are they just numbers chosen by the creator of this project?

If they are related to MySQL, where can i find a documentation for these numbers for MySQL and Mariadb

[fading patrol]

As it says there
​These constants represent the various column (field) types that are ​supported by MySQL.

The key word is represent. The piece that comes from MySQL itself is the field types

https://dev.mysql.com/doc/refman/8.0/en/data-types.html

The constants used by PyMySQL are arbitrary

[torn sphinx]

anyone?

[harsh mortar]

Is it good practice to commit after executing a SELECT?

q = select([text('*')]).select_from(User)
res = session.execute(q).fetchall()
session.commit()

Or can you execute without commit?

[unkempt arrow]

!d itertools.combinations

[delicate field]

itertools.combinations

itertools.combinations(iterable, r)```
Return *r* length subsequences of elements from the input *iterable*.

The combination tuples are emitted in lexicographic ordering according to the order of the input *iterable*. So, if the input *iterable* is sorted, the combination tuples will be produced in sorted order.

Elements are treated as unique based on their position, not on their value. So if the input elements are unique, there will be no repeat values in each combination.

Roughly equivalent to:

[torn sphinx]

ty

[unkempt arrow]

SELECT is read only iirc, for .commit you use it for writing and save it. you probably want to close the session though

[harsh mortar]

Okay that makes sense. Thanks

[unkempt arrow]

I found some situations on SO where you do need to use commit after select
https://stackoverflow.com/a/14436616
take a look :D

[hidden osprey]

i did fetchall for a table then iterated through and slapped it in a list then made a json file. looked exactly like DB Browser for SQLite

[harsh mortar]

Wow thank you for the extra research

[blissful finch]

Hi, so I am basically trying to plot a series of graph stored in form of txt files saved from a software connected to a spectrophotometer (sorry long sentence). And I would like first to convert those txt files to numpy array. The first column is wavelength, the second one is intensity. Now I have found the module numpy.loadtxt. The thing is I think my data base isn't suit for english programming languages simply because instead of using dots to indicate decimals (i.e 12.672 = 12 + 0.672), the txt file uses ,. And I think that messes up everything because i get this error : could not convert string to float probably because numpy doesn't understand why there are so many ,. I'm not sure at all but I think that could be source of problems, including the one quoted above. So do you think I should either convert those txt files to csv, or find a way to replacethe , by . (I've never done that before, like manipulating files in folders and so on so this is new to me). Thanks in advanced

[delicate field]

Hey @blissful finch!

You either uploaded a .txt file or entered a message that was too long. Please use our paste bin instead.

[blissful finch]

ho oops

Link to one of the text files : https://paste.pythondiscord.com/ubucutiqux.apache

Because other than that I don't have any problem, I just load my data base, use matplotlib to plot it and that's it

f1 = open('I0.txt', 'r')
f2 = open('I0 - Copie.txt', 'w')
for line in f1:
    f2.write(line.replace(',', '.'))
f1.close()
f2.close()```
Well I actually found a way nevermind !

[blissful finch]

import glob
import shutil

txtfiles = []
for file in glob.glob("*.txt"):
    txtfiles.append(file)

string = "_copy"
txtfiles_copy = [x[:-4] + "_copy.txt" for x in txtfiles]

n = len(txtfiles)    

for k in range(n):
    file = txtfiles[k]
    shutil.copy(file, txtfiles_copy[k])

for k in range(n):
    f1 = open(txtfiles[k],'r')
    f2 = open(txtfiles_copy[k],'w')
    for line in f1:
        f2.write(line.replace(',','.'))
    f1.close()
    f2.close() ```
If anyone is interested, this allows me to copy the name files from my folder, to then copy the actual files with the given name with "copy" at the end, so I can rewrite those files without destroying my original set

[umbral yoke]

what's the simplest way to refresh data on a sqlite db in the background? (so people entered a eth address and it automatically set their amount, now i wanna try to refresh it every day or week)

[nova cove]

you want it to update right when a query is executed?

in that case use db.commit()

You can prolly schedule db jobs with a cron job

!d sqlite3.Connection.commit

[delicate field]

sqlite3.Connection.commit

commit()```
This method commits the current transaction. If you don’t call this method, anything you did since the last call to `commit()` is not visible from other database connections. If you wonder why you don’t see the data you’ve written to the database, please check you didn’t forget to call this method.

[umbral yoke]

i want to update it every day.
so let's say a person has 1 eth when it enetered the db and now the person has 2eth. how do i tell the db in a simple way that something has changed?

[nova cove]

wdym tell the db something has changed

the db updates when it knows something changed

[umbral yoke]

and what's a simple code to let the db know that?

[nova cove]

if you execute something you tell the db connection to insert, remove, do something, etc

you don’t need any code

!d sqlite3.Connection.execute

[delicate field]

sqlite3.Connection.execute

execute(sql[, parameters])```
This is a nonstandard shortcut that creates a cursor object by calling the [`cursor()`](https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.cursor "sqlite3.Connection.cursor") method, calls the cursor’s [`execute()`](https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.execute "sqlite3.Cursor.execute") method with the *parameters* given, and returns the cursor.

[umbral yoke]

yh that's what i know, but let's say there are 1000 people in that database and i want to let a program refresh that db automatically

[nova cove]

commit saves all changes made to the database

[umbral yoke]

any other people in here that understand what i mean?

[nova cove]

bro I’m literally helping you. commit() will instantly save the changes made to the database without having to refresh

[spare moth]

Any numpy / pandas pros in here?

[nova cove]

Don’t think this is the right channel

[warped compass]

Hey guys, dont really use this discord much but would I be allowed to ask SQL questions or does that not fly here?

[nova cove]

This is the right channel for such questions

[warped compass]

Ok, not sure how good everyone is here is different languages but Im sure yall are better than me lol. I am working on a query (again, using ServerSQL and not python) and I basically have to make one script be able to switch between two queries. There may be hundreds of ways to do that but the way I am doing it involves using cases with variables and making a switch. My problem is that when I am declaring my variable, which uses SELECT INTO FROM WHERE AND, I get a syntax error at my into. I have the entire query in parenthesis as a variable but its still giving me a syntax error at INTO. I can provide sample code if need be but maybe Im thinking about it all wrong in the first place. Thanks in advance

[nova cove]

ServerSQL?

SQL server?

[warped compass]

I meant the SQL flavor, Im pretty sure thats te generic SQL right?

[nova cove]

Do you mean MS SQL server?

[warped compass]

Like, not MySQL, ServerSQL

or just SQL

[nova cove]

SQL is the language. Things like MySQL are the relational database systems

Do you mean Microsoft SQL server

never heard of the server before sql

[warped compass]

Wait, Im dumb. Yes SQL Server

Today has been a long day

[nova cove]

lol

Do you have any code

[warped compass]

Yeah, I can get it. Its on my work laptop but I can type it up real quik

Ill be a minute

[nova cove]

ok

[warped compass]

Do you know if discord allows you to change the format of text?

[nova cove]

!code

[delicate field]

Here's how to format Python code on Discord:

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

These are backticks, not quotes. Check this out if you can't find the backtick key.

[nova cove]

replace py with sql or whatever lang u are posting code for

[warped compass]

Ahh

Thanks

DECLARE @x AS varchar
SET @x = (SELECT [NAME]
                ,[AGE]
                ,[HOBBIES]

INTO MyTable

FROM MyDatabase

WHERE [NAME] LIKE 'john.doe'
AND [AGE] LIKE '21'

Basically, Ive been getting a syntax error near INTO and Ive been able to get around that but then it said I need an EXISTS statement or something

[nova cove]

where does it say the error occurs

[warped compass]

Near "INTO"

[nova cove]

That’s where it says you need an IF EXISTS?

[warped compass]

Well, Ive gotten past that one, the error m getting now is Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

But I only get that error if I comment out the INTO statement

[nova cove]

With this code here, it says thing about if exists but commenting out into gets the expression error, correct

[warped compass]

The exists and expression error are one in the same. With the INTO included, I get a syntax error near into error. With the INTO commented out, I get the only one expression can be specified in the select list when the subquery is not introduced with EXIST

Well, if it makes any difference, I have aliases for the columns im pulling

[rare vale]

Is there someone who knows how to cite vertabelo rdbms in a paper? I’m also still looking for the latest version of vertabelo

[nova cove]

I’m sorry but this might be beyond my current knowledge of SQL and I do not have any experience with MS SQL Server, so idk if there is anything different from Postgres or SQLite

[warped compass]

No problem, thanks for trying. I honestly should be worrying about this on work time lol but sometimes I get a fun issue and cant wait till the next day

[nova cove]

lol

ye I also get the thrill from programming

failing to help others just makes me wanna learn more

so now I’m going to get an SQL book, which I have been thinking about.

[warped compass]

Right, lol. Its funny because my job doesnt even do SQL, we all do Powershell but we have a tool that uses SQL and Im kinda running with it so Im trying to do something that will help the others guys.

[nova cove]

ye well good luck with that

[warped compass]

Thanks

[nova cove]

Hopefully someone else can help you. I’ll have to go now

[abstract flax]

hi everyone! I'm trying to insert a number into a sqlite database and it always makes it wrong. the number is discord user id which is 18 digits long. the problem, it appears to replace the last two digits with 0? I'm not sure why this is happening?

[fading patrol]

Not sure but maybe this is what you need: https://stackoverflow.com/questions/8672473/is-there-type-long-in-sqlite/8672613

[unkempt arrow]

I recommend storing these in string instead

you can convert it back to integer in python later

[toxic berry]

Can anyone recommend best book for python GUI&mysql database please

[twin jewel]

hi

[finite stirrup]

im struggling with it on phpmyadmin, is it any differnet at all to the python module sqlite3?

[finite stirrup]

nvm fixed it

[thorny wadi]

if anyone knows mysql, i need to write a trigger that lets me create a copy table of the current table but im getting this error

ERROR 1422: Explicit or implicit commit is not allowed in stored function or trigger.

i know what it means but not sure how to get around it

i figured it out

somewhat

[torn sphinx]

Hey guys I have a array in mongodb which basically stores role ids, I want to check if a user when leaving has any of the roles in the array and if he does, then add a document. This doesnt work why?

    @commands.Cog.listener()
    async def on_member_leave(self, member):
        s = []
        for role in member.roles:
            if role.id in self.coll.find_one({"unique": "1"})["role_id"]:
                s.append(role.id)

        await self.coll.insert_one({"member_id": member.id, "role_id": s})

This is how my document looks like

[abstract flax]

that's what I ended up doing actually. thanks for the help, I see now that it's ok

[wise spade]

cant find something in instructions

stuck in here

[ionic smelt]

would you read

[wise spade]

yes

[ionic smelt]

and not jump straight to the practices

[wise spade]

SQL Lesson 1: SELECT queries 101
To retrieve data from a SQL database, we need to write SELECT statements, which are often colloquially refered to as queries. A query in itself is just a statement which declares what data we are looking for, where to find it in the database, and optionally, how to transform it before it is returned. It has a specific syntax though, which is what we are going to learn in the following exercises.

As we mentioned in the introduction, you can think of a table in SQL as a type of an entity (ie. Dogs), and each row in that table as a specific instance of that type (ie. A pug, a beagle, a different colored pug, etc). This means that the columns would then represent the common properties shared by all instances of that entity (ie. Color of fur, length of tail, etc).

And given a table of data, the most basic query we could write would be one that selects for a couple columns (properties) of the table with all the rows (instances).

Select query for a specific columns
SELECT column, another_column, …
FROM mytable;
The result of this query will be a two-dimensional set of rows and columns, effectively a copy of the table, but only with the columns that we requested.
If we want to retrieve absolutely all the columns of data from a table, we can then use the asterisk (*) shorthand in place of listing all the column names individually.
Select query for all columns
SELECT * 
FROM mytable;
This query, in particular, is really useful because it's a simple way to inspect a table by dumping all the data at once.
Exercise
We will be using a database with data about some of Pixar's classic movies for most of our exercises. This first exercise will only involve the Movies table, and the default query below currently shows all the properties of each movie. To continue onto the next lesson, alter the query to find the exact information we need for each task.

[ionic smelt]

theres literally guides and instructions, and examples

read.

[wise spade]

Find the title of each film

[oak oyster]

then read?

[wise spade]

SQL Lesson 1: SELECT queries 101

[ionic smelt]

no like

actually read the stuff on there

a key to being a dev is to read

[wise spade]

it says find title and there is no title i have seen in the web page

https://sqlbolt.com/lesson/select_queries_introduction

[ionic smelt]

bruh

[wise spade]

here i am

ppl be like how can a person be so dumb

hey?

[weak yoke]

Looks like you forgot to await find_one

[umbral yoke]

when i update my sqlite db with a bunch of info, some isn't new and some is, can i somehow print out what info has changed?

[rigid mica]

how do I make a connection from my raspberry pi to the db I made on my windows pc?

[fading patrol]

What kind of db? If SQLite, don't do that, just move the file

[rigid mica]

postgresql

[fading patrol]

I think you need to log the changes with a trigger https://stackoverflow.com/questions/14757671/sqlite-creating-an-audit-using-triggers

[rigid mica]

I really need to get it, cause all my prefixes are in there...

[fading patrol]

How do you want to connect? Psql, psycopg2 or what? In any case should be straightforward, just point it to the right IP

[rigid mica]

asyncpg

await asyncpg.create_pool(database = "Mydatabase", user = "Myuser", password = "Mypassword")

this doesnt work anymore

Traceback (most recent call last):
File "/home/pi/AtraBot.py", line 27, in <module>
bot.load_extension(extension)
File "/home/pi/.local/lib/python3.9/site-packages/discord/ext/commands/bot.py", line 732, in load_extension
self._load_from_module_spec(spec, name)
File "/home/pi/.local/lib/python3.9/site-packages/discord/ext/commands/bot.py", line 677, in _load_from_module_spec
raise errors.ExtensionFailed(key, e) from e
discord.ext.commands.errors.ExtensionFailed: Extension 'cogs.warns' raised an error: InvalidAuthorizationSpecificationError: Peer authentication failed for user "postgres"

[fading patrol]

Did you set an IP or hostname somewhere? It needs to know how to find the other computer over the network

[rigid mica]

I dont think I did

yk how to?

[fading patrol]

Check the docs, I'm not familiar with asyncpg

[rigid mica]

are you familiar with other librarys?

I dont mind switching

@fading patrol

I might try to connect using a link

[fading patrol]

Do you have something like asyncpg.connect('postgresql://postgres@localhost/test') somewhere? Change localhost to the IP of the computer with the DB running

[rigid mica]

I dont use a link till now

@fading patrol

connection refused

ConnectionRefusedError: [Errno 111] Connect call failed ('192.168.0.11', 5432)

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

Traceback (most recent call last):
File "/home/pi/AtraBot/AtraBot.py", line 27, in <module>
bot.load_extension(extension)
File "/home/pi/.local/lib/python3.9/site-packages/discord/ext/commands/bot.py", line 732, in load_extension
self._load_from_module_spec(spec, name)
File "/home/pi/.local/lib/python3.9/site-packages/discord/ext/commands/bot.py", line 677, in _load_from_module_spec
raise errors.ExtensionFailed(key, e) from e
discord.ext.commands.errors.ExtensionFailed: Extension 'cogs.warns' raised an error: ConnectionRefusedError: [Errno 111] Connect call failed ('192.', 5432)

[fading patrol]

If the IP and port and related syntax are all correct and the database is running, I have no idea

[rigid mica]

Traceback (most recent call last):
File "/home/pi/.local/lib/python3.9/site-packages/discord/ext/commands/bot.py", line 672, in _load_from_module_spec
setup(self)
File "/home/pi/AtraBot/cogs/warns.py", line 188, in setup
bot.add_cog(warns(bot))
File "/home/pi/AtraBot/cogs/warns.py", line 22, in init
loop.run_until_complete(create_db_pool())
File "/usr/lib/python3.9/asyncio/base_events.py", line 642, in run_until_complete
return future.result()
File "/home/pi/AtraBot/cogs/warns.py", line 9, in create_db_pool
db = await asyncpg.connect('postgresql://postgres@/Bertie')
File "/home/pi/.local/lib/python3.9/site-packages/asyncpg/connection.py", line 2085, in connect
return await connect_utils._connect(
File "/home/pi/.local/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 895, in _connect
raise last_error
File "/home/pi/.local/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 881, in _connect
return await _connect_addr(
File "/home/pi/.local/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 781, in _connect_addr
return await __connect_addr(params, timeout, True, *args)
File "/home/pi/.local/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 825, in __connect_addr
tr, pr = await compat.wait_for(connector, timeout=timeout)
File "/home/pi/.local/lib/python3.9/site-packages/asyncpg/compat.py", line 66, in wait_for
return await asyncio.wait_for(fut, timeout)
File "/usr/lib/python3.9/asyncio/tasks.py", line 481, in wait_for
return fut.result()
File "/home/pi/.local/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 691, in _create_ssl_connection
tr, pr = await loop.create_connection(
File "/usr/lib/python3.9/asyncio/base_events.py", line 1056, in create_connection
raise exceptions[0]
File "/usr/lib/python3.9/asyncio/base_events.py", line 1041, in create_connection
sock = await self._connect_sock(
File "/usr/lib/python3.9/asyncio/base_events.py", line 955, in _connect_sock
await self.sock_connect(sock, address)
File "/usr/lib/python3.9/asyncio/selector_events.py", line 502, in sock_connect
return await fut
File "/usr/lib/python3.9/asyncio/selector_events.py", line 537, in _sock_connect_cb
raise OSError(err, f'Connect call failed {address}')
ConnectionRefusedError: [Errno 111] Connect call failed ('', 5432)

@fading patrol this might be more usefull

[fading patrol]

URL on line 9 of warns.py is definitely wrong there. There's no IP or hostname

[rigid mica]

there is an ip

I removed it

and hostname is postgre if im right

how do I add a hostname

@fading patrol u got a good example? Example on the docs is really really bad

[grim vault]

Have you configured your postgresql server to allow remote access?

[rigid mica]

hey, I just managed to connect

but now I get this error and the bot doesnt run

asyncio.exceptions.TimeoutError

[cerulean ledge]

how can I loop through all of the rows in a sql table in python?

[nova cove]

if you are using aiosqlite you could do async for row in cursor

This is from the docs

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

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

I believe that should work 🗿👍

[cerulean ledge]

thansk bro

[spiral knoll]

why is SQL microsoft take so long to download?

[formal drift]

hello all! I am working on a small project and am hitting my head against the wall just a bit on how to best store a range of dates in my sqllite DB. What I am trying to accomplish is to present some data to a user showing when to plant a vegetable. So like say Brocolli's transplant window is say 3/15 to 4/25

[nova cove]

MS SQL)

[formal drift]

would I be better off making two DB columns, one with the start date and the other with the end?

[nova cove]

your bandwidth might be low

[fading patrol]

I would definitely use two columns, and store them in the proper date format (https://www.tutlane.com/tutorial/sqlite/sqlite-date-function#divsqlitedatesyn)

[formal drift]

Awesome thank you!

[versed sun]

Hey everyone!
I am trying to build a project which will calculate the gas vehicle consumption
from points A to B on a map (in the real world).
I want to implement a short path algo myself so I need a proper dataset
to run the algorithm on it.
The problem is I just can't find it. I am searching for a dataset,
which returns vertexes/edges or something similar and the more details,
the better (height, off/on-road, traffic jams, etc... ) -> Preferable language is Python
but anything that will work is ok

[earnest oriole]

Hi, i want help in forming the query:
i have two tables:
comments {comment_id(int, PK), content(text), user_id(int, FK)}
votes {vote_id(int, PK), comment_id(int, FK), user_id(int, FK), vote_type (varchar, "upvote"/"downvote")

now i want a table with the columns:
comment_id, content, user_id, upvote_count, downvote_count
can anyone help me writing the sql query?

[slender atlas]

Use ALTER TABLE and COUNT

[earnest oriole]

select 
    c.comment_id, 
    c.content, 
    c.user_id, 
    sum(case when v.vote_type = 'upvote' then 1 else 0 end) as upvote_count, 
    group_concat(case when v.vote_type = 'upvote' then v.user_id end separator ', ') as upvote_users_id,
    sum(case when v.vote_type = 'downvote' then 1 else 0 end) as downvote_count,
    group_concat(case when v.vote_type = 'downvote' then v.user_id end separator ', ') as downvote_users_id
from comments c 
    left join votes v on c.comment_id = v.comment_id
group by 
    c.comment_id, 
    c.content, 
    c.user_id

this worked

[green fox]

Find the error

[pure sleet]

you want people to find the error for you? are you kidding me?

[nova cove]

💀

[green fox]

no thank you fro reading this
kindly say i don't have a skill for that

[pine kettle]

which would be better? mongodb or repldb?

pls ping on answer

[nova cove]

mongo 100%

don’t use anything affiliated with replit

[pine kettle]

pls ping on answer

but thanks

[nova cove]

np

[acoustic thunder]

ok so this is going to be long but i need help normalizing my data. i have a ticket system that when someoine interacts with the persistent button it makes a channel aka the ticket chennal, i want it to be aqble to have commands to show open tickets. i was thinking of making a dict of dicts with the dict key being the channel id and thebn the rest of the info in a dict but im not sure if its good. how should i do it?
list of dicts? dict of list?
im just not sure

sebt here bc its a mix

[sterile pelican]

Relational database is the best for this case

Well it really depends on your project

Personally I would use a custom class for all that handling

[sterile pelican]

That is possible as well, but I meant class with the connection as one of its attributes

It really depends on type of your project as said

[torn sphinx]

Ignoring exception in on_ready
Traceback (most recent call last):
  File "C:\Users\PC\AppData\Local\Programs\Python\Python39\lib\site-packages\nextcord\client.py", line 415, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\PC\Desktop\testing\bot.py", line 67, in on_ready
    await cursor.execute("CREATE TABLE IF NOT EXISTS channels (id INT PRIMARY KEY AUTOINCREMENT, channel_id TEXT)")      
  File "C:\Users\PC\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\cursor.py", line 37, in execute   
    await self._execute(self._cursor.execute, sql, parameters)
  File "C:\Users\PC\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\cursor.py", line 31, in _execute  
    return await self._conn._execute(fn, *args, **kwargs)
  File "C:\Users\PC\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 129, in _execute   
    return await future
  File "C:\Users\PC\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 102, in run        
    result = function()
sqlite3.OperationalError: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

but i have int primary key ```sql
CREATE TABLE IF NOT EXISTS channels (id INT PRIMARY KEY AUTOINCREMENT, channel_id TEXT)

[sterile pelican]

As for discord bot, you typically assign a connection to new attribute of your bot

And it is recommended to use async libraries

Aiosqlite is async analogue of sqlite3 but is complicated as heck if you ask me

In terms of data fetching

For discord bot I mean if you are not subclassing it just do

bot = commands.Bot(...)
bot.db = sqlite3.connect(...)```

And then you can do whatever you want with that connection

Yeah

It has a close method. You can subclass commands.Bot and overwrite its close method where you will close the connection and then await super().close()

I just wanted to show you how I've done it in my bot but seems like I forgot the super().close() lol

In case you still want to see, here it is with a bunch of overwritten methods and aiosqlite connection. But I forgot the super().close() https://github.com/BobuxBot/BobuxAdmin/blob/master/tools/bot.py

[torn sphinx]

Ignoring exception in on_ready
Traceback (most recent call last):
  File "C:\Users\PC\AppData\Local\Programs\Python\Python39\lib\site-packages\nextcord\client.py", line 415, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\PC\Desktop\testing\bot.py", line 72, in on_ready
    await db.commit()
  File "C:\Users\PC\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 162, in commit     
    await self._execute(self._conn.commit)
  File "C:\Users\PC\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 129, in _execute   
    return await future
  File "C:\Users\PC\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 102, in run        
    result = function()
sqlite3.OperationalError: database is locked

[pine kettle]

what could cause this error? ```Command raised an exception: ServerSelectionTimeoutError: cluster0-shard-00-00.hpqhe.mongodb.net:27017: connection closed,cluster0-shard-00-02.hpqhe.mongodb.net:27017: connection closed,cluster0-shard-00-01.hpqhe.mongodb.net:27017: connection closed, Timeout: 30s, Topology Description: <TopologyDescription id: 6208a8d308b1f7e64216faa4, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('cluster0-shard-00-00.hpqhe.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-00.hpqhe.mongodb.net:27017: connection closed')>, <ServerDescription ('cluster0-shard-00-01.hpqhe.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-01.hpqhe.mongodb.net:27017: connection closed')>, <ServerDescription ('cluster0-shard-00-02.hpqhe.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-02.hpqhe.mongodb.net:27017: connection closed')>]>

code: ```python
cluster = MongoClient(key)

async def open_account(user: discord.Member):
db = cluster['BotSwag']
cltn = db['Accounts']

if user.id not in list(cltn.find({"_id": str(user.id)})):
post = {"_id": str(user.id), "Balance": 0}
cltn.insert_one(post)
else:
pass

@client.command(aliases=["bal"])
async def balance(ctx):
await open_account(ctx.author)
db = cluster['BotSwag']
cltn = db['Accounts']

for account in cltn.find({"_id": str(ctx.author.id)}):
await ctx.send(account['Balance'])```

pls PING ON RESPONSE

[torn sphinx]

say im using SQL, got a table A and a table B with a foreign key referencing A, i want to enforce that there can only be 100 B rows referencing a single A at any moment, if an element 101 appears i wanna remove element 1 , etc. so that i only ever have a max of 100 B rows referencing a single A. Is there a way to enforce this via SQL or should i do it myself when inserting?

[lunar pier]

Is it hard to check database for changes?

Maybe there's some timestamp when db was changed?

[haughty flame]

WITH TT AS (SELECT user_id, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY guild_id) AS row_id FROM submissions)

DELETE FROM TT WHERE row_id = 2```
Help I'm stuck. I don't know how to delete from `submissions`

It keeps giving me errors.

[hollow pike]

anyone have experience doing backups/restore using postgres?

[open finch]

Off the top of my head I can't think of a SQL way of enforcing this. The first thing that comes to mind is to build a 100 element FIFO to keep track of record numbers inserted with similar foreign key references. When an element "falls out" of the FIFO, delete that record. You could also run a query after each insert to see how many records with the same foreign key references there are. If there are > 100 delete the one with the oldest timestamp. Both methods are kind of crude, but should work.

[torn sphinx]

m!help

m?help

m/help

$help

[real crow]

​No Category:
  help Shows this message

Type $help command for more info on a command.
You can also type $help category for more info on a category.

No command called "a" found.

No command called "aa@​everyone" found.

[trim lintel]

You can use a trigger

[open finch]

Ahh. Great idea! A friend of mine uses triggers a lot. Don't know why I don't think of them or use them more.

[trim lintel]

True same as me, I generally like to keep this kind of business logic especially what the question asked in the application code.

[spiral knoll]

@delicate field

[finite stirrup]

i know this isnt python related but no other place answers, and its about databases

[nova cove]

this is the right place

[finite stirrup]

<?php

$username = $_POST["username"];
$password = $_POST["pass"];

$username = stripcslashes($username);
$password = stripcslashes($password);
$username = mysql_real_escape_string($username);
$password = mysql_real_escape_string($password);

mysql_connect("server", "user", "pass");
mysql_select_db("db");


$result = mysql_query("SELECT * FROM dbname WHERE username='$username' and password='$password'")
    or die("Failed to query database".mysql_error());
$row = mysql_fetch_array($result);
if ($row['username'] == $username && $row['password'] == $password){
    echo "Welcome".$row['$username'];
}else{
    echo "Login invalid"
}

i dont know why this doesnt work

[nova cove]

uhh this is php and i do not know php

but

are username and password valid columns

[finite stirrup]

yes

[nova cove]

maybe use placeholders for the query?

"SELECT * FROM dbname WHERE username = ? AND password = ?", $username, $password??

idk if it works the same with php

[finite stirrup]

ill try it

i get this

[nova cove]

ye idk then

[finite stirrup]

its weird php i swear

[torn sphinx]

can I update an sql file in mega storage using their api?

[dry hawk]

i would doubt it, i dont think there would be any form of live updating file system with mega due to the resource intensity

i would recommend using smthng like anonfiles as it might have a much simpler api that could be more interactive

however, i am not knowledgeable in that area

[blissful basalt]

How can I connect to a mysql database?

[nova cove]

!pip mysql-connector

[delicate field]

mysql-connector v2.2.9

MySQL driver written in Python

[blissful basalt]

thanks, where can I find the documentation for the new one? It says the old one is deprecated

[nova cove]

https://dev.mysql.com/doc/connector-python/en/

[blissful basalt]

Thanks alot!

[nova cove]

np

[valid needle]

hey all, i'm considering using something such as sqlite web which is a web interface that will allow me read my sqlite database on a browser

however, im planning on making it localhost only (127.0.0.1)

how would i be able to access the 127.0.0.1/vps localhost address if it's hosted on a vps? maybe i can use a vpn and access 127.0.0.1 from there? would that be possible?

or maybe i can use an alternative to sqlite that will allow me to access the db through dbeaver from my pc? would i need a db server for that?

[open finch]

My favorite is pymysql. I usually configure the connection to return queries in dictionary format. Very handy. I believe you can do that with mysql-connector too.

[brazen charm]

the vpn option would work, or you could use something like a SSH tunnel

[spiral knoll]

why is microsoft ssms not familiar with emp?

it gives me an error when i write emp

[swift crater]

Anyone able to help me get the right command for creating a sequence of numbers for an existing table using row_number?

I have a table "Selection_Groups" with a column "Selection_Number" and I'd like to be able to set a sequence of numbers (1, 2, 3, 4, 5...) for the Selection_Number Column (sorted by that column)

I have tried the following but it doesn't appear to be working cursor.execute('select SELECTION_POSITION, row_number() over (order by SELECTION_POSITION) AS row_number FROM SELECTION_GROUPS ORDER BY SELECTION_POSITION;')

[fluid glen]

Okay so, I created a table with a column "cardamount" that is an integer , cardamount INTEGER)''')
but now, when I select and try to print it, I get this error: ```discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: can only concatenate str (not "int") to str

here is the select and print bit of it

    await ctx.send("```Becka's Boutique Gift Card Info:\n--------------------------------\n Name: " + row[0] + "\n AccountID: " + row[1] + " \n Account Balance: " + row[2] +"ni```")

formatting is fucked

[swift crater]

try a str() over the int maybe

so like str(row[0])

[fluid glen]

did not work :P

[swift crater]

each one?

[fluid glen]

to each.... row[]?

[swift crater]

yeah

[fluid glen]

I'm not sure how... but it worked o.o

Thank you ~

[swift crater]

you're welcome

[grim vault]

Looks good to me except Selection_Number != SELECTION_POSITION

[swift crater]

selection_position is just my column name to store the sequential values, not sure what you mean

[grim vault]

You mentioned ...column "Selection_Number"... but used a different name in the SQL statement.

!e

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table SELECTION_GROUPS(SELECTION_POSITION int)")
cur.execute("insert into SELECTION_GROUPS VALUES (1), (17), (3), (31), (15), (4), (72), (19)")

for row in cur.execute('select SELECTION_POSITION, row_number() over (order by SELECTION_POSITION) AS row_number FROM SELECTION_GROUPS ORDER  BY SELECTION_POSITION;'):
   print(row)

[delicate field]

@grim vault :white_check_mark: Your eval job has completed with return code 0.

001 | (1, 1)
002 | (3, 2)
003 | (4, 3)
004 | (15, 4)
005 | (17, 5)
006 | (19, 6)
007 | (31, 7)
008 | (72, 8)

[swift crater]

wait, does it need to be looped for it to work or is that just for print out?

[grim vault]

You need to fetch it somehow, I just used the loop. You can .fetchall() or similar.

[swift crater]

here's my test data

basically trying to overwrite the selection_position column if it detects that it's out of order

I am running

                
cursor.execute('SELECT * from SELECTION_GROUPS')
print(cursor.fetchall())```

and get
[(3, 'ASDasd', 8), (4, 'CXDFSG', 7), (6, 'DFXD', 5), (7, 'CXVXCLKJ', 5), (8, 'ASDFASDFS', 4), (9, 'asdfasdfafds123123', 4), (10, '32453453245', 2), (11, 'asdfasdf', 66)]

[grim vault]

These are two separate selects and you only show the second one.

[swift crater]

ok, so the select isn't actually changing the data with the row_number() function, or would I have to commit changes or the like

[grim vault]

Only a UPDATE will change values, a select will not.

[swift crater]

gotcha, sorry, still new to sql

so just have to figure out how to use update, well, in the correct place/order

so would it be an update join then?

[fluid glen]

am running into error again lol await ctx.send("This has updated an already existing account.") for mon in cur.execute("SELECT cardamount FROM bbgc WHERE playerid = (?)", [beans[0], ]): #absolutely no idea what the CAST part is, of "SELECT CAST", and I'm afraid of what might happen if I change it. total = mon[0] + int(amount) print(total) print(beans) cur.execute("UPDATE bbgc SET cardamount = ? WHERE playerid = ?", [total, (str(beans))]) await ctx.send("Card amount has been updated.") con.commit()

this is a little unorganized, but basically after running cur.execute("UPDATE bbgc SET cardamount = ? WHERE playerid = ?", [total, (str(beans))]) it doesn't save the new value that I just assigned, I tried a bunch of different things but it just won't work

[grim vault]

You are using beans[0] for the select but str(beans) for the UPDATE?

[grim vault]

I don't think you can use window functions in an update statement. You'll need to do it another way.

[swift crater]

gotcha, guess I'll go back to the trusty for loop lol

either that or if I can take out the map/values

is it possible to insert a list into a table column?

[fluid glen]

I love you

that fixed the problem

thank you ~

[grim vault]

Some databases do support an array like datatype.

[swift crater]

I'll look into it

[carmine arch]

Hey yall I'm pretty new to any type of db design but I'm working in django trying to setup my models and I can't seem to solve how I would setup a relation ship between two objects. I have laid out the general setup for the model below I'm having trouble with the relationship between the image and the user in regards to the users who like the image. Should I use a many to many? When trying that I get an error about a reverse query name?

Object User
-Username
-Password

Object Image

• Owner(User)
• LikedBy(List of User)???

[fading patrol]

Many to many is correct, but the way those are implemented is with an intermediate table. In this case you should ad ID columns for each of those tables you have so far, and then your likes table would have a column for each, so each row of your likes table would have a user Id and an image id. This is the basic idea of many-to-many in normalized DBs

[carmine arch]

Is there any downside to using the id approach?

[fading patrol]

You should have IDs anyway, but an intermediary table is really the only way to properly implement a many-to-many relationship AFAIK. Listing usernames in your image table might be fine for some limited purpose but it won't work for SQL queries

[carmine arch]

I'm not handling the sql queries django is really handling everything so I assume in the db there is actual ids

[fading patrol]

I haven't implemented models in Django but check the docs if you haven't already (https://docs.djangoproject.com/en/4.0/topics/db/examples/many_to_many/) and share your code / error of you're stuck and I'm sure someone else can help

[carmine arch]

Thanks for the help. I'm gonna go to sleep and try again at this tomorrow after reading the docs in fullish.

[elder elk]

I'm trying to pass NULL if query params is equal to x

but getting error

[Mon Feb 14 09:50:10.394841 2022] [wsgi:error] [pid 11536:tid 1420] [client 104.211.153.215:55961] psycopg2.errors.UndefinedColumn: column "none" does not exist\r
[Mon Feb 14 09:50:10.394841 2022] [wsgi:error] [pid 11536:tid 1420] [client 104.211.153.215:55961] LINE 10:       AND (None IS NULL OR activity_group_id = None);\r
[Mon Feb 14 09:50:10.394841 2022] [wsgi:error] [pid 11536:tid 1420] [client 104.211.153.215:55961]                     ^\r
[Mon Feb 14 09:50:10.394841 2022] [wsgi:error] [pid 11536:tid 1420] [client 104.211.153.215:55961] \r```

following is the function

  """PARAMS  
  activityId =
    if :"All" then it will give activity_group_id for all village and all activity group id
    else "activity group id " pass then it will give activity_group_id for group id.
  
  attribute = DBT category ('Farmer','FPC',NRM)
  villageCode = Village Code of requested Village
    """
  attribute = request.args['attribute']
  activityId = request.args['activityId']
  if activityId =='All':
      activityId=None
  villageCode = request.args['villageCode']

  mainQuery =f"""SELECT DISTINCT activity_group_id,
  act."ActivityGroupCode" AS activity_group_code,
  CONCAT(act."ActivityGroupID", '_', act."ActivityGroupCode") AS "legend_id",
  activity_group_name,village_code
      FROM dbt.dbt_point_primary pnt_prm
          JOIN dbt.activity act
              ON pnt_prm.activity_group_id = act."ActivityGroupID"
                  WHERE village_code='{villageCode}' 
                  AND attributes = '{attribute}'
                  AND ({activityId} IS NULL OR activity_group_id = {activityId});"""

  with get_conn() as conn, conn.cursor() as cur:
    cur.execute(mainQuery)    
    # cur.execute(query)
    if cur.rowcount > 0:
      result = cur.fetchall()
      activity = []
      for row in result:
        activity.append({
          "activity_group_id":row[0], "activity_group_code":row[1],
          "legend_id":row[2],"activity_group_name":row[3],
          "village_code":row[4],
          })
      return flask_json.jsonify({
        'activity':activity,
        })
    else:
      return flask_json.jsonify({'err':'NO DATA FOUND'})```

[grim vault]

You want something like:

  ...
  mainQuery = """SELECT DISTINCT activity_group_id,
    act."ActivityGroupCode" AS activity_group_code,
    CONCAT(act."ActivityGroupID", '_', act."ActivityGroupCode") AS "legend_id",
    activity_group_name, village_code
        FROM dbt.dbt_point_primary pnt_prm
        JOIN dbt.activity act ON pnt_prm.activity_group_id = act."ActivityGroupID"
       WHERE village_code = %s
         AND attributes = %s"""
  mainParams = (villageCode, attribute)

  if activityId is not None:
    mainQuery += " AND activity_group_id = %s"
    mainParams += (activityId,)

  with get_conn() as conn, conn.cursor() as cur:
    cur.execute(mainQuery, mainParams)
    ...

And I'm not sure if the execute is enough to set the cur.rowcount or if you need to fetchall first.

[elder elk]

@grim vault Thank you so much for quick reply

this will work but can we do it in query itself means passing null

this works on geoserver, but why not in python

[grim vault]

That's because f"{var}" will be None and not NULL if var is None. I guess if you assign it "NULL" instead of None it will work. But f-strings is bad for SQL values.

!sql-f-strings

[delicate field]

SQL & f-strings
Don't use f-strings (f"") or other forms of "string interpolation" (%, +, .format) to inject data into a SQL query. It is an endless source of bugs and syntax errors. Additionally, in user-facing applications, it presents a major security risk via SQL injection.

Your database library should support "query parameters". A query parameter is a placeholder that you put in the SQL query. When the query is executed, you provide data to the database library, and the library inserts the data into the query for you, safely.

For example, the sqlite3 package supports using ? as a placeholder:

query = "SELECT * FROM stocks WHERE symbol = ?;"
params = ("RHAT",)
db.execute(query, params)

Note: Different database libraries support different placeholder styles, e.g. %s and $1. Consult your library's documentation for details.

See Also
• Extended Example with SQLite (search for "Instead, use the DB-API's parameter substitution")
• PEP-249 - A specification of how database libraries in Python should work

[sonic hinge]

i havew a file in the path of Misc/Keys.json and its empty, and i am tryna make a function that adds the Key variable to the json so i have a log of all the keys that have been used could someone help me?

[nova cove]

JSON files are dictionaries so regular dict methods should work

to open the file use a context manager

you prolly will need these methods

!d json.load

[delicate field]

json.load

json.load(fp, *, cls=None, object_hook=None, parse_float=None, parse_int=None, parse_constant=None, object_pairs_hook=None, **kw)```
Deserialize *fp* (a `.read()`-supporting [text file](https://docs.python.org/3/glossary.html#term-text-file) or [binary file](https://docs.python.org/3/glossary.html#term-binary-file) containing a JSON document) to a Python object using this [conversion table](https://docs.python.org/3/library/json.html#json-to-py-table).

*object\_hook* is an optional function that will be called with the result of any object literal decoded (a [`dict`](https://docs.python.org/3/library/stdtypes.html#dict "dict")). The return value of *object\_hook* will be used instead of the [`dict`](https://docs.python.org/3/library/stdtypes.html#dict "dict"). This feature can be used to implement custom decoders (e.g. [JSON-RPC](http://www.jsonrpc.org) class hinting).

*object\_pairs\_hook* is an optional function that will be called with the result of any object literal decoded with an ordered list of pairs. The return value of *object\_pairs\_hook* will be used instead of the [`dict`](https://docs.python.org/3/library/stdtypes.html#dict "dict"). This feature can be used to implement custom decoders. If *object\_hook* is also defined, the *object\_pairs\_hook* takes priority.

[nova cove]

!d json.dump

[delicate field]

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](https://docs.python.org/3/glossary.html#term-file-like-object)) using this [conversion table](https://docs.python.org/3/library/json.html#py-to-json-table).

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

The [`json`](https://docs.python.org/3/library/json.html#module-json "json: Encode and decode the JSON format.") module always produces [`str`](https://docs.python.org/3/library/stdtypes.html#str "str") objects, not [`bytes`](https://docs.python.org/3/library/stdtypes.html#bytes "bytes") objects. Therefore, `fp.write()` must support [`str`](https://docs.python.org/3/library/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.

[nova cove]

!d dict.update

[delicate field]

dict.update

update([other])```
Update the dictionary with the key/value pairs from *other*, overwriting existing keys. Return `None`.

[`update()`](https://docs.python.org/3/library/stdtypes.html#dict.update "dict.update") accepts either another dictionary object or an iterable of key/value pairs (as tuples or other iterables of length two). If keyword arguments are specified, the dictionary is then updated with those key/value pairs: `d.update(red=1, blue=2)`.

[sonic hinge]

@nova cove i cant figure it out, imma just use github copilot to figure it out

[nova cove]

then you won't learn

its not that hard to write to a json file

[sonic hinge]

i know python besides json editing

i can never get the hang of jsons

[nova cove]

https://docs.python.org/3/library/json.html

docs exist

[sonic hinge]

ive tried with the docs

idk what it is ab them, i cvant figure them out

[dawn moss]

Can json be considered a database?

Like shouldn't it meet certain rules to be considered one

[sonic hinge]

well i mean some people store json's in databases

likme with the user's info

so i just thought this would be the place

[keen minnow]

json is a format. It does not define anything else

You would be missing parts about how to access and update data

[nova cove]

no. it shouldn't be used as one

its mainly used for serializing data in APIs

[dawn moss]

lets suppose I am storing channel IDs and for such a small task isnt it just better go with json file format?

like when do u need to go for postgres etc?

postgres is relational database

[nova cove]

its a RDBMS

[dawn moss]

isnt that the same thing , eh

[nova cove]

RDBMS is the proper name but ye pretty much'

[dawn moss]

yea so I was saying json has its use cases as well.. can you give some examples

cuz

I am really confused when to use it or not

[nova cove]

it does but not for databases

as i said its used for data transmission in web APIs

[dawn moss]

yes true. But lets take a very specific example.. lets say you have a client and they want to access data like stored channel IDs in a way they can manually edit and since json is human readable .. isnt it just fine to use that for getting data like that. Ik thats not its use case but it does the task so why not.. Idk why am I even discussing it tho xd

[nova cove]

a database can be understood by a human also.

[dawn moss]

yes true I guess I will just say I am very lazy to create another database ;-;

anyways thnx for the discussion lol

[nova cove]

check out YAML

its a markup language that is very human readable

[dawn moss]

nono I mean I will always prefer to go for postgres

but uk sometimes clients request a product and you know you can do it easily with json but that will be considered a bad product probably hmmm

I mean its a small product anyways

like I personally think database is best because you can easily query it and you can store large amount and permanent data etc.. I think json is better if you dont have to query it and its small/temporary data

its just my opinion tho.

[nova cove]

json should never be used as a database

no matter the size of the data

[dawn moss]

wait yea you are right. I thought about it and realised its the same thing (storing data and database).. exchanging data is not storing data

ok I think I will use it for getting data from api etc as its intended use case 👍

[elder elk]

@grim vault Thank you for your help bro

[frosty tundra]

I have users and users_old

how do I move everyones coins from users_old to users

without modifying anything else

would I need a for loop or what?

nvm figured it out

[brave bridge]

It is fine if you have 10 users and a megabyte of data

[gloomy spindle]

Is there a way to have something like serial but with a composite primary key?
for example like, i have a table guild_id BIGINT, case_id SERIAL
Then I insert into it some values: (123, DEFAULT) (twice) and (456, DEFAULT) (twice)

How'd I make it automatically be like:
123 | 1
123 | 2
456 | 1
456 | 2
And not:
123 | 1
123 | 2
456 | 3
456 | 4
Like have the serial associated to the other primary key?

this is postgresql btw

[nova cove]

🗿

[brave bridge]

What's wrong?

[nova cove]

nothin

[brave bridge]

The only issue I see is that you have to take care of concurrency issues. But, well, if you don't understand them, you will have problems with any other storage

[nova cove]

is that for the JSON or @gloomy spindle ?

[brave bridge]

For the JSON

[nova cove]

oh ok

yeah thats true

[gloomy spindle]

damn I got excited by the ping

[nova cove]

there might be a way to do what you want @gloomy spindle , and if there is, its prolly beyond my current knowledge

[gloomy spindle]

🗿

thanks?

[nova cove]

i don't a specific piece of a data in a row can have a DEFAULT value

you might want to look over your database design again

[gloomy spindle]

SERIAL type in postgres, when inserting DEFAULT it will auto increase by 1 but it's like a global/general increase

[nova cove]

yes

[gloomy spindle]

I wanted for it to increase depending on the other composite pkey. for each count up to be "separate" XD

without manually doing that fuckery myself xD

[nova cove]

lol

[cerulean ledge]

How could I put a str datetime to a date sql format?

[fading patrol]

What DB? If SQLite there is no date type but there is a 'Date()' function to handle strings as dates

[cerulean ledge]

There is a data type "DATE" in sql

no?

[fading patrol]

Again, what DB? Not SQLite but in others, yes

[cerulean ledge]

i use an sql db

https://www.w3schools.com/sql/sql_dates.asp

[fading patrol]

It depends on MySQL vs Postgres vs Microsoft so read the docs for whatever you are using

[cerulean ledge]

like.. i just have an .db file I store my stuff in

[fading patrol]

And how do you do that? With sqlite3 in Python?

[cerulean ledge]

aiosqlite, so yes, just in an async way

[storm mauve]

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. 

Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.
from https://www.sqlite.org/datatype3.html

[cerulean ledge]

Huh, I didn't know that, I got the info from: https://www.w3schools.com/sql/sql_dates.asp

okay sorry for the confusion then, thank you both

[storm mauve]

w3 is sometimes unreliable

[nova cove]

^

[storm mauve]

that page in particular doesn't mentions sqlite3 though

just MySQL and SQL Server, which are very different

[cerulean ledge]

i see i see

thanks for the info and again sorry for the confusion

[unkempt arrow]

offtopic but: w3 != w3schools, w3schools took some similar names

[brave bridge]

Yeah, w3schools is exploiting the w3 name

W3C asked them to change it IIRC but they refused

[nova cove]

lmao

[pearl adder]

Hi is this where I can ask about sql

I have a database with an auto_incremented field

is there a way I can search for the largest value in the table at that time

[brave bridge]

Why do you want that?

[pearl adder]

because i using sqlite in python and I need to a way to validate that the user input is in the range of that field

[brave bridge]

So you want to know if the row with the user provided ID exists?

You can't know that just by knowing the largest ID

[pearl adder]

whys this?

oh because data could be deleted

[brave bridge]

1. A row could be deleted
2. A transaction could roll back but still increment the counter

[pearl adder]

ah ok

so is there a way to check a field exists without getting an error?

[brave bridge]

You can just fetch the row by id

[pearl adder]

but if that id doesnt exist wouldnt that cause an error?

[brave bridge]

If you don't get a row back (whatever way you're using), then it didn't exist

[pearl adder]

oh ok

thank you

[lunar cargo]

hey guys, im stuck with this problem

this is how my database looks

and question is

I want to delete role with expired timestamp but firstly I need to delete row from assotiate table

@tasks.loop(seconds=10)
    async def check_role_timestamp(self):
        connection = create_session()
        query = connection.query(Roles).where(Roles.expired_at < datetime.datetime.now() - datetime.timedelta(minutes=1)).all()
        print(query)
        for role in query:
            user_with_expired_role = connection.query(User).filter(User.roles.id == role.id).all()
            print(user_with_expired_role)

        connection.commit()

        print('done')

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @torn sphinx until <t:1644961014:f> (9 minutes and 59 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

[lunar cargo]

I tried to do it with .join

but still no results

dumb me

Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with User.roles has an attribute 'id' got this error

I need to do some stuff with .join I guess

[nova cove]

do you know SQL DELETE statement?

what type of database is this anyways? The picture makes it seem like an RDBMS, but which aRe you using

and also I would rename the role_id table to role since a column in the other table is role_id

[nova cove]

have you reloaded the database

or closed and reopened the app

right click and press reload I believe

on the database

if you are writing SQL code, DROP TABLE table_name

But from pgadmin I believe right click on the table and press drop table

np

users table and make columns for different parts of a user

use asyncpg for interacting with a Postgres db

no

it depends on the project

!pip asyncpg

[delicate field]

asyncpg v0.25.0

An asyncio PostgreSQL driver

[nova cove]

don’t think so but idk if it’s blocking

!d asyncpg.connection.connect

[delicate field]

asyncpg.connection.connect

async connect(dsn=None, *, host=None, port=None, user=None, password=None, passfile=None, database=None, loop=None, timeout=60, statement_cache_size=100, ...)```
A coroutine to establish a connection to a PostgreSQL server.

The connection parameters may be specified either as a connection URI in *dsn*, or as specific keyword arguments, or both. If both *dsn* and keyword arguments are specified, the latter override the corresponding values parsed from the connection URI. The default values for the majority of arguments can be specified using [environment variables](https://www.postgresql.org/docs/current/static/libpq-envars.html).

Returns a new [`Connection`](https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.connection.Connection "asyncpg.connection.Connection") object.

[nova cove]

one column can only hold a specific data type

By making multiple columns

?

That’s how a database works

CREATE TABLE ‘users’ (
    user_id BIGINT PRIMARY KEY,
    guild BIGINT,
    …
);

Example of a table for storing discord users

make a table for each user?

that isn’t good database design

imagine you have millions of users on your service

a row is for a specific entry

wdym store more than one value

for one user? That’s what a row is

user_id | guild_id
—————————
| 28271 | 281837 |

In this case our primary key is user_id. A PK uniquely identifies all the rows in a database table. Rows correspond to a specific user here while the column represents what the data represents

rows could also be called records

create the table with the columns for color, user id, etc

No

No

[nova cove]

Here

The table name is users

Where it has columns that represent data a user would have

A row represents a record of a specific user

rows are created when you insert data

when a table is created you have no rows

You insert using the INSERT INTO statement

search up a sql tutorial

I feel my explanation is sufficient and answers your questions, but if it isn’t helping go find another source

learn it then

it isn’t necessarily directly connected but when you make queries to a database using dpy commands, you can specifically say in the query to edit something of a user’s record where the user id is the id of the ctx.user

create tables when the bot is ran and edit those tables in the command

CREATE TABLE IF NOT EXISTS ensures the table will ONLY be created if it doesn’t exist already

and you won’t delete the database each time you stop the bot

so

Just learn some SQL

And you’ll be good

async with asyncpg.connect(‘…’) as db:
    db.execute(‘’’
        CREATE TABLE IF NOT EXISTS table_name (
        …
        );
    ‘’’)

    …

Indentation might be shit. I’m on mobile

you can also make it execute an external sql file

you can make a function in your bot class that connects to the db or a separate db class

and if you do the first option, make sure to call said function in the on_ready

Because it has to be in an async function

learn sql before you do any of this

and asyncpg has docs

[torn sphinx]

Hey, I'm using psql and I want to fetch 2 rows to be a json object, how can I do this?

Like fetching (person_id, name)

I dont want it like {"person_id": person_id, "name": name} but as {person_id: name}

is this possible?

(please ping if you reply )

[sonic hinge]

raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ServerSelectionTimeoutError: whitelist-shard-00-00.kpunc.mongodb.net:27017: connection closed,whitelist-shard-00-01.kpunc.mongodb.net:27017: connection closed,whitelist-shard-00-02.kpunc.mongodb.net:27017: connection closed, Timeout: 30s, Topology Description: <TopologyDescription id: 620c49b4da0eda450756091e, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('whitelist-shard-00-00.kpunc.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('whitelist-shard-00-00.kpunc.mongodb.net:27017: connection closed')>, <ServerDescription ('whitelist-shard-00-01.kpunc.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('whitelist-shard-00-01.kpunc.mongodb.net:27017: connection closed')>, <ServerDescription ('whitelist-shard-00-02.kpunc.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('whitelist-shard-00-02.kpunc.mongodb.net:27017: connection closed')>]>

does this error mean that the mongodb password is wrong or the username?

if u need, il;l send the code for it

[thorn reef]

did u whitelist ur ip?

[sonic hinge]

idk

im hosting this on a repl.it

[thorn reef]

well go to ur dashboard and whitelist ur ip (or add 0.0.0.0 for all ips)

[thorn reef]

i dont think that matters much

[sonic hinge]

lemme try

[open bolt]

I have a question.
I am a postgresql user, but there is a difficult problem now.

[thorn reef]

did it work?

[open bolt]

I created a table space here, and I tried to create a database, but it didn't work, so I created it as a sql shell.

First off, it's been created.
But the problem is that the file location is completely different.
I can't see it from there and I can't even execute it.
The location of that file is C:\cscoding\project.
postgresql, so sql shell...
I think I only recognize C:\Program Files\PostgreSQL\14\data.

The file I need is not C:\Program Files\PostgreSQL\14\data.
It's a data file in C:\cscoding\project (that's what's in that folder on the screen).I don't think it recognizes this. I don't know what to do.
Do you think I got the wrong way?

The list of data recognized by the screen you're looking at is in C:\Program Files\PostgreSQL\14\data.
This is a list of data provided by default when installing.

I'm a Windows 10 user.

[sonic hinge]

Ye

[unreal smelt]

im working on a table with two attributes, id string, hours integer
there are duplicates of the id field and i want to add the hours field of each of the duplicates

[open bolt]

If you have a postgresql user, please help.

[violet token]

\

[old wave]

hello guys i just learned basic python and know front end web dev

can i connect databases through python if yes then what should be easiest to start with

thankyou

[pure mason]

Mysql connecter

[old wave]

wow never heard of it. i mostly heard of django

[grim vault]

It's not required but you normally want one unique identifier for the entry which is called the PRIMARY KEY of that table and only one entry is allowed with that value. A SERIAL (in postgresql) is just an auto increment column. If you use a unique value you don't need a serial, just an integer (32bit) or (now comes your other error) a BIGINT for 64bit values. the userID and guildID on discord are 64bit, so you need a BIGINT.

CREATE TABLE IF NOT EXISTS members (member_id BIGINT PRIMARY KEY NOT NULL)

Sorry, I have to leave.

[nova cove]

PRIMARY KEY represents the column that uniquely identifies every record in the table. SERIAL is a sequential integer type

Is what necessary

You can do DEFAULT NULLif you want it to be null by default

But how it is rn I don’t think it will work

you can also say NOT NULL for it never to be null

What you have is good

Just remove NULL

And prolly set a PK

Primary key

PRIMARY KEY represents the column that uniquely identifies every record in the table

Don’t learn off w3

Get a book or something

something like a user id would be a primary key

Since every user id is different

mhmm

or at the end of the table you can do PRIMARY KEY (user_id)

should be yes

instead of TEXT you can use VARCHAR(number) to specify a certain amount of characters

but text prolly be better here

In raw sql you can’t, but if you use something like asyncpg you can use the execute function to specify the command, use placeholders and fill in that placeholder with a tuple of values that respectively corresponds to the placeholders in order from left the right

!d asyncpg.connection.Connection.execute

[delicate field]

asyncpg.connection.Connection.execute

coroutine execute(query: str, *args, timeout: Optional[float] = None) → str```
Execute an SQL command (or commands).

This method can execute many SQL commands at once, when no arguments are provided.

Example...

[nova cove]

!d asyncpg.pool.Pool.execute

[delicate field]

asyncpg.pool.Pool.execute

coroutine execute(query: str, *args, timeout: Optional[float] = None) → str```
Execute an SQL command (or commands).

Pool performs this operation using one of its connections. Other than that, it behaves identically to [`Connection.execute()`](https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.connection.Connection.execute "asyncpg.connection.Connection.execute").

New in version 0.10.0.

[nova cove]

you don’t but two sql queries in one execute

Why make it a string if id holds BIGINT data type

?

Did it not insert

And what was the error

asyncpg placeholders are $1, $2, $3, and so on

old reviews prolly

use asyncpg

asyncpg has performance benefits over psycopg and implemets psql binary i/o protocol

idk how psycopg works, so probably can't

docs tell me that psycopg placeholders are %s

if you want to delete a recordf

DELETE FROM table_name WHERE id = id_you_want_to_delete_its_record

uhh

con = con =?

and are you connecting to the database each time you do a command

uhh

not good

you should make a function that connects to the db and call that in like a custom connect method or in on_ready

are you subclassing when you create your Bot

ok

put it in one line

and don't do str(inter.author.id)

also instead of doing cur.close and con.close just commit the changes to the db

it closes it for you

ah yeah because you changed it to TEXT

https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries

[torn sphinx]

Hey, I'm using psql and I want to fetch 2 rows to be a json object, how can I do this?
Like fetching (person_id, name)
I dont want it like {"person_id": person_id, "name": name} but as {person_id: name}
is this possible?
(please ping if you reply )

[burnt turret]

https://www.db-fiddle.com/f/oytnQGwbXekGZ5o9AzFzk6/0 is this what you're looking for?

[torn sphinx]

Yes perfect, thanks!

[alpine raft]

How can I avoid deadlock(mysql) in python multithreaded script?

[gentle warren]

Make a Python program to match the plan shown above.The program will use a counter loop.It will add 10 numbers to make a total.

can someone help me with this?

[nova cove]

The error is clear

[nova cove]

deadlock happens when two transactions are working on the same row

show me where you are doing the transactions

[river matrix]

Hey, are we allowed to ask DB questions on this channel or is it just in the 'help-channels' where we get to ask?

[nova cove]

this channel is named databases so ask away'

[sonic hinge]

def get_data_from_mongo_by_arg(collection_name, arg1):
  
    client = cluster["RegisterableKeys"]
    db = client.test_database
    collection = db[collection_name]
    data = collection.find_one(arg1)
  
    return data

this is for pymongo and its outputting "None" when i run the function upder these args:

inf = {"Key": "ed7fd4f4-6e2d-4d2e-80c1-4e5379aa6bc1"}

print(get_data_from_mongo_by_arg("Info", inf))

y is that

[slender snow]

Trying to import a speciffic data point from an api into a cell in google sheets. Anyone know hot to get just 1 data point? Its importing the entire data and not separating the name from the number. Anyway to just import the data point called flor_price?

=INDEX(IMPORTDATA("https://api-v2-mainnet.paras.id/collection-stats?collection_id=clapes-by-clapesnear"))

[sonic hinge]

fixed it

[torn sphinx]

Local json files are the best databases
SQL and Mongo are ew

No u

[glad talon]

Are u trying to start world war 3?

[torn sphinx]

Sqlite3>json 🏃

[torn sphinx]

yes no

[torn sphinx]

Shut up json is the best

[glad talon]

Hb csv

[torn sphinx]

Json >>>

[torn sphinx]

🤲

[glad talon]

.txt?

[torn sphinx]

it's not even a database

[torn sphinx]

Json

[glad talon]

Json isn’t either

[torn sphinx]

Json files* are best

True

[torn sphinx]

No, .bin files

[torn sphinx]

.msi then

[glad talon]

Yes, for object notation

[torn sphinx]

No, json files best db, so uwu simplicity

Let's not make this chat the next #discord-bots , I'm out.

[violet token]

but im not

trying to get something

from coloumn a

ive written my suggestion as a tho

[violet token]

?

[burnt turret]

!sqlite-fstring this is the issue

uh

!sql-fstring

[delicate field]

SQL & f-strings
Don't use f-strings (f"") or other forms of "string interpolation" (%, +, .format) to inject data into a SQL query. It is an endless source of bugs and syntax errors. Additionally, in user-facing applications, it presents a major security risk via SQL injection.

Your database library should support "query parameters". A query parameter is a placeholder that you put in the SQL query. When the query is executed, you provide data to the database library, and the library inserts the data into the query for you, safely.

For example, the sqlite3 package supports using ? as a placeholder:

query = "SELECT * FROM stocks WHERE symbol = ?;"
params = ("RHAT",)
db.execute(query, params)

Note: Different database libraries support different placeholder styles, e.g. %s and $1. Consult your library's documentation for details.

See Also
• Extended Example with SQLite (search for "Instead, use the DB-API's parameter substitution")
• PEP-249 - A specification of how database libraries in Python should work

[opal trellis]

does INSERTING a record while using mysql.connector in python not save it in the table

i have to execute it every time ,else i dont anything from selecting it

\🤔

[burnt turret]

you have to call conn.commit after the operation

[outer parrot]

why doesnt it work

D:

[opal trellis]

alr , everytime while updating a tuple ?

[burnt turret]

After any operation that should write to the database I'd say 🤔

[burnt turret]

#databases message

[opal trellis]

but it does update for ddl commands

like creating a table works without any commit stuff

[empty hinge]

It depends on your database, settings, and settings on the connection and cursor you’re using @opal trellis

Read the database documentation thoroughly so you understand when commits happen automatically vs when they do not, and what a commit actually means

There is no simple answer

The default behavior of a lot of databases on default settings for simple projects is “it just works”, but this hides a ton of complexity

[opal trellis]

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-autocommit.html

alr so the connection object also got a autocommit flag which defaults to false ,so reassigning it as truthy works 👍

[outer parrot]

how do i fix this

i want it to store 2 variables mcname and dcname

[trim lintel]

Because your value for the string is not in quotes.

To fix you should use the safe and recommended way to make the operation

[outer parrot]

and that is

[trim lintel]

See the pinned messages

The documentation should also show this

[outer parrot]

which one

[outer parrot]

.

[trim lintel]

I think this one is close to sqlite which you are using I think. #databases message

Other one is for Postgres

[outer parrot]

so instead of using .format i should use ? and define it after

[trim lintel]

Yes

[outer parrot]

so this should work

[trim lintel]

Yes

[outer parrot]

ty

[opal aspen]

if I got some questions on SQL, is this the right place to ask?

[brave bridge]

yes

[opal aspen]

I am trying to add new records to this table

but it's throwing me an error

this one

[harsh pulsar]

that is a highly questionable schema

!code also in the future it's a good idea to post your code and error messages as text, not a screenshot. see below: 👇

[delicate field]

Here's how to format Python code on Discord:

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

These are backticks, not quotes. Check this out if you can't find the backtick key.

[harsh pulsar]

i recommend removing all of the fields in that table except sid and subid:

CREATE TABLE subsuppliers (
  sid INTEGER,
  subid INTEGER,
  FOREIGN KEY ("sid") REFERENCES "suppliers" ("sid")
)

...at which point i question the value of having this table at all

as for the actual error, i think it's because one of the fields in that table does not match any corresponding foreign field value

[opal aspen]

oh actually the sid is the foreign key here

not the subid

[harsh pulsar]

okay

and what is the primary key? the sid, subid pair?

[opal aspen]

ya

[harsh pulsar]

if you really do need a table like this, it sounds like you need a view

or you can save the output of the join to a separate table, but without foreign key constraints (because the table should be computed by software and never appended to with "live" data)

sometimes that is called an "analytics" table, there might be other standard names for it

or a "flattened" table maybe

you might want to read about database normalization

[opal aspen]

Ah i see. I am quite new to this. But let me look into your suggestions

[harsh pulsar]

https://database.guide/what-is-normalization/

[opal aspen]

this is the schema for your ref

[harsh pulsar]

is this a requirement for some assignment?

[opal aspen]

Not an academic. For tutorial practice

[harsh pulsar]

but the instructions actually said to make this table with both s and sub columns?

[opal aspen]

ya

[unreal tartan]

If a supplier has subsuppliers. Isn't it normal to have a table like this (with the reduced columsn at least) instead of a view?
How would you store this otherwise?

suppliers: [
  {
   "id": 1,
   "subsuppliers": [{"id": 1}, {"id": 2}]
  }
  {
   "id": 2,
   "subsuppliers": [{"id": 1}, {"id": 2}]
  }
]

[harsh pulsar]

well i find that weird, especially in an assignment where you don't need to optimize for specific queries and lookups

[harsh pulsar]

read the link about database normalization that i posted 🙂

the big benefit of relational databases is that the tables are collections of "tuples" (aka. "records") which are related according to their unique identifiers

so a supplier is a tuple of: supplier id, address, name, etc

a subsupplier should just be a tuple of supplier id and subsupplier id

[unreal tartan]

ahh, I see, you treat the subsuppliers also as suppliers.

I suppose

[harsh pulsar]

yes, that seems to be the requirement

if you have truly "nested" data, then yes you might want to consider something more like a document database, or storing json-like things as individual database fields

but even then, it is often beneficial to apply database normalization

in which case the "subsupplier" might actually have its own attributes other than just sid, subid

consider that your nested data is a tree, and a tree is a special case of a graph, and a graph can be represented as an adjacency list

[unreal tartan]

So I assume that with the 'view' suggestion you must take the assumption that a subsupplier is bound to 1 supplier right. Otherwise you'd need the table with 2 IDs for the many2many

[harsh pulsar]

and a table of parent, child tuples is... literally an adjacency list

[harsh pulsar]

i am kind of guessing here given the limited information. but it seems like one supplier can have many subsuppliers

so Supplier 3787 can be a sub-supplier of any other supplier

but the relationship is unidirectional

this is where i have no idea what the actual problem calls for (and might be underspecified in the assignment)

for example in the USA on construction jobs, you typically have a "general contractor" who hires many "subcontractors" that handle specific tasks like glass, electrical, etc

that would be a unidirectional relationship

but the contractor-subcontractor relationship is on a per job basis

so you'd have tuples like (job_id, contractor_id, subcontractor_id)

whereas over time and space, i guess it's possible that Supplier 3787 could be a sub-supplier of Supplier 2890 today, but tomorrow Supplier 2890 could be a sub-supplier of Supplier 3787

in which case you'd maybe need some additional information to figure things out

but in this case, you'd have two separate records, sid=3787, subid=2890 and sid=2890, subid=3787

in a sense, the sub-supplier table is a "many-to-many" table, but both sides of the relation are referencing the Suppliers table

it's many-to-many between suppliers and suppliers

[unreal tartan]

Yea I think I'm with you now.
When I first read

if you really do need a table like this, it sounds like you need a view
I interpreted is as there wouldn't have to be any extra table, but that would be impossible, you need that (job_id, contractor_id, subcontractor_id) (or similar to link suppliers with other suppliers) table.

I assume what you meant with the view, was to display the address and other data of the subsuppliers (subcontractors)

[opal aspen]

I am very new to this, do u mind explaining what is view and why it's being used

Like why essentially that was your 1st hunch

@unreal tartan

Ya I agree @harsh pulsar I think for this particular ques, we have to assume that the partnership happens within the suppliers. So the subsuppliers would not be anything external

[harsh pulsar]

I assume what you meant with the view, was to display the address and other data of the subsuppliers (subcontractors)
yes, that's what i meant. i could have been clearer

[harsh pulsar]

the problem is that this schema is duplicating information in an unnecessary way

...which causes problems

[opal aspen]

Oh what would be a optimal way then

[harsh pulsar]

read the article i posted about database normalization!