databases

[stone lava]

hi my question is simple

st_id INT AUTO_INCREMENT PRIMARY KEY

this is not adding id like 1 2 3 whats wrong in this

this should add 1 2 3 as i add a name in table ryt ?

[pure cypress]

When you perform an insertion into that table, the std_id column will be set automatically.

Yes, the ID will be a sequence like 1, 2, 3 i.e. the first record will get ID 1, the second ID 2, and so on.

If you're not observing that behaviour, then what ID do you see when you do an insertion? Make sure you're not providing your own ID when doing the insertion.

[stone lava]

@pure cypress

i want 1 2 3 at the place of none

@pure cypress```py
import sqlite3
conn=sqlite3.connect("sqlite.db")
conn.execute('''
create table student (
st_id INT AUTO_INCREMENT PRIMARY KEY,
st_name VARCHAR(50),
st_class VARCHAR(10),
st_email VARCHAR(30)
)
''')
conn.close()

import sqlite3
conn=sqlite3.connect("sqlite.db")
ins='''
    insert into student (st_name,st_class,st_email) 
    VALUES('jake','11th','[e-mail]')
'''
conn.execute(ins)
conn.commit()
conn.close()
``` my insert query

import sqlite3   
conn=sqlite3.connect("sqlite.db")

data=conn.execute("SELECT * FROM student")
print("STUDENT ID" , "STUDENT NAME","STUDENT CLASS","STUDENT EMAIL")
for n in data :
    print( n[0],"    ",  n[1],"    ", n[2],"    ",     n[3])  
``` my select query

[pure cypress]

For sqlite, it needs to be st_id INTEGER PRIMARY KEY AUTOINCREMENT

Notice the datatype is INTEGER rather than INT, and AUTOINCREMENT has no underscore, and is placed at the end

[opaque gale]

You can set up your tables to do cascade on delete. So, if a parent row is deleted it will cascade to all children associated with it.

I, personally, would offload it to the database using cascade instead of trying to do it in code...to much margin for error in code

[turbid tendon]

https://github.com/0xnlldev/SQLiteFactory

[turbid tendon]

it seems very unnecessary for now but the initial foundation has been laid, the developer says it will develop more

[serene sphinx]

Hi guys,

I can use the following (first) query to extract data from an API:

data['longName']

But, when I make a dictionary like this:

dictionary = {
"longName": ["data['longName']", 44, "lname", "LN"],
"shortName": ["data['shortName']", 45, "sname", "SN"], ...}

and construct an 'algorithmic query' from the dictionary:

list(dictionary.values())[0][0]

because

list(dictionary.values())[0][0] == "data['longName']" # True

But unfortunately I cannot use the algorithmic query to get data from the API because the latter is a string. Is there a way to convert my 'algorithmic query' string to 'act like the first query'?

[fading patrol]

It's not clear to me what you're trying to accomplish here. You might need to provide more code and context. But the one thing I notices is that you are storing "data['longName']" in your list as a string when you seem to maybe want data['longName'] (not a string, whatever it is)

[gray hollow]

Can I have multiple ON_CONFLICT clauses in the same query? Such as

INSERT INTO my_table
VALUES (...)
ON CONFLICT (constraint_one) DO UPDATE
    SET x = y
ON CONFLICT (constraint_two) DO UPDATE
    SET a = b

(The above doesn't actually work, but I think it shows what I want)
Using postgreSQL

[sturdy lily]

I am trying to read from MongoDB with pyspark but when i am supplying MongoDB aggregation pipelines then i am getting this error
It should be a list of pipeline stages (Documents) or a single pipeline stage (Document)
can someone help me?

[hazy mango]

If you're doing a contraint name instead of a record name, I believe you need ON CONFLICT ON CONSTRAINT constraint_one

(i.e. add the ON CONSTRAINT)

[grim vault]

I think postgresql only allows one ON CONFLICT statement. SQLite allows multiple but only the first match is executed.

[west hill]

a question here can we stored dictionary in a postgresql column?

without making it a strings and doing json.loads

[brazen charm]

not really

SQL Db columns generally are designed to hold small single values rather than potentially big and bulky values like a dictionary can be

[brazen charm]

missing a comma at the end of your newToken var. (newToken) isnt actually a tuple. it just gets converted to newToken. You need to have (newToken,) instead.

[upper rampart]

so this?

sql = ("""INSERT INTO unusedkeys(storage) VALUES("%s")""", (newToken,))

[brazen charm]

looks alright to me

[upper rampart]

thanks now im just getting table doesn't exist i think i can fix it from here on out thanks very much tho

[torn sphinx]

is it possible to make tables within tables with sqlite

[grim vault]

lose the quotes around %s

sql = ("INSERT INTO unusedkeys(storage) VALUES(%s)", (newToken,))

[civic cargo]

Postgres provides 2 types for storing JSON, but you cannot store a Python dictionary without converting it to a JSON string

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

If you use some ORM though then it's possible that it will do the JSON-ification for you, making it seem like you are storing and retrieving dicts (but the contents of those must be serialisable to JSON)

[quasi mirage]

Hey. I have a postgres db and I created some SQLALchemy data models to match my tables so I can do some thing programatically.

I have two tables tied together by an FK. It's a one-to-many relationship. I am using sqlalchemy's session to select against my models as a test and it works / relationship works.

Is it possible to use pandas to do an insert for both tables at once? Like insert into table 1, grab latest id, then insert into table 2 and associate the id with these rows that are being inserted?

I have an xlsx file and I am using Pandas to pull it in. First 4 columns are for table A and last 3 are for Table B. Anyway to insert them into both tables using sqlalchemy?

[quasi mirage]

I guess it is impossible to insert one to many using sqlalchemy. TIL

[unkempt prism]

Makes sense to break it up into 2 steps to upsert the many table get its primary key and then insert the one table using the correct foreign key.

I've just found a library that helps upserting with pandas https://github.com/ThibTrip/pangres/wiki/Upsert that may help.

[quasi mirage]

The one table has no fk.

Onlt the many has an fk.

only

[coral briar]

Can someone please try help me in #help-potato, it has to do with Java Script and Flask.

[fading patrol]

No, but you can use a foreign key to represent a row on a different table

[fading patrol]

This is why nosql dbs like Mongo exist. You can store the key to the relevant document as a string in Postgres. Having both structured and unstructured databases working together like that can be worth it, depending on your use case

[knotty valley]

hello. In postgresql, is there a way to avoid redundant upserts like this where j is already zero ```postgresql
insert into t(i,j) values (1, 43)
on conflict (i) do update set j = 0;

[paper flower]

You can store json data in postgres pretty efficiently, making calls to separate database would be just more work for you

[paper flower]

I don't think it's something you should optimize

[torn sphinx]

does anyone know how to let just my db show up?

[pseudo plinth]

Uhmm "SHOW DATABASES;"

[torn sphinx]

where?

[delicate field]

Hey @tranquil shoal!

It looks like you tried to attach file type(s) that we do not allow (.db). We currently allow the following file types: .gif, .jpg, .jpeg, .mov, .mp4, .mpg, .png, .mp3, .wav, .ogg, .webm, .webp, .flac, .m4a, .csv, .json.

Feel free to ask in #community-meta if you think this is a mistake.

[fading patrol]

I was taught otherwise but you got me reading up on this and I think you're right that pure Postgres is the way to go. Thanks for the tip!

[paper flower]

I think so too, if your data is related it would be easier to store it in postgres

[ionic breach]

Question guys, how to keep connection with Microsoft SQL server while backing up? Because, when you backup the data, the SQL server stops or looses connection, how to keep it constant? While backing up? Appreciate the help!!

[severe arch]

hello all, can anyone can provide me clear roadmap for backend development thorugh python/django

[fading patrol]

https://roadmap.sh/backend

[minor ruin]

MSSQL backups are live.

[hoary wharf]

On the left is DBeaver against a Postgresql-database, and on the right is Python with either Psycopg2 or Sqlalchemy. What is the correct way to configure time zones so the queries shows the... same and correct for where I am?

[quasi mirage]

How could you use Pandas iterrows() or something similar to perform a raw insert for each line in a dataframe?

I understand to_sql() is a thing, but I am wanting to know how you could use something like iterrows() to loop through a dataframe row by row and perform a raw insert for the values in each row.

[hazy hazel]

anyone know how this ? : django.db.utils.ProgrammingError: column Occupier_occupier.first_name does not exist LINE 1: ..._occupier"."dob", "Occupier_occupier"."password", "Occupier_...

[torn sphinx]

cant seem to find my error here, anyone care to look to see why im getting invalid syntax

movies_df.iterrows():
if movies_df.loc[index, 'duration_minutes'] > 120:
movies_df.loc[index, 'length_category'] = 'long'
elif movies_df.loc[index, 'duration_minutes'] <= 30:
movies_df.loc[index, 'length_category'] = 'short'
else:
movies_df.loc[index, 'length_category'] = 'average'
movies_df

[fading patrol]

Assuming you have no control over how the DB is configured, I would use the pytz library to tell Python to use the "correct" TZ.

[primal notch]

I want to log message, reaction and other activity data with my discord bot to compose stat reports and plots. The messages can be edited or deleted and reactions are removable, so the data is mutable, but there still will be much more writes than edits. Would clickhouse be a decent fit for this case?

[paper flower]

I think you can use any SQL database and you don't really need a clickhouse here

Discord already handles reaction uniqueness, so you only have to update how many people voted for a certain option

And since clickhouse is slower in handling writes it wouldn't be a great choice I think?

[keen minnow]

Unless you plan on having dozens of millions of users per day, I would default to mysql/postgres.
Would there be any reason they don't fulfill your needs?

[paper flower]

Still, there's nothing to aggregate really (I think clickhouse is mainly used for aggregation/analytics/any complex quieries?)

[keen minnow]

agreed

[paper canyon]

I am new to python - my virtualenv is not working. I obviously have not tried everything buy my scope of knowledge is limited atm. Does anyone have a few minutes to hop on a call?

[keen minnow]

Hi and welcome aboard! People are more than happy to help you, but this is a channel dedicated to databases. You may want to check out #❓｜how-to-get-help

[primal notch]

They do, I just want to try something new, especially if it would outperform Postgres. Anyway, thanks for the input @keen minnow @paper flower

[keen minnow]

It wouldn't outperform postgres for this use case.
Good luck!

[paper canyon]

Sorry, I figured my wanting to use Dgango was part of the database channel

[olive reef]

Should I approach using sqlite for my Discord bot? I consider that it would suit my small discord bot that won't surpass the 300~ servers if we are optimistic.

I also don't see why to use Firebase, since I don't need that much of a scalability capacities. And using MySQL would make me have to afford a server.

I just want someone that knows about dbs to guide me in what engine to use since im relatively new to this

[primal notch]

You'd want a server for your bot either way
SQLite is a good start, yes

You could also try Postgres, it's not much harder to use

[celest relic]

Hey!
I was running a query in mysql using python mysql connector

f"SELECT name, kills FROM {self.table_name} ORDER BY kills DESC LIMIT 10"

and got into a issue where the most number of kills which is 1428 but for some reason the topmost kills is showing as 99

[jaunty linden]

add a semicolon just to be safe in case there are further queries ahead

[celest relic]

alright ill try that

[celest relic]

same thing

[jaunty linden]

just to be sure you added the semicolon inside the f-string right?

[celest relic]

yess

[celest relic]

f"SELECT name, kills FROM {self.table_name} ORDER BY kills DESC LIMIT 10;"

[jaunty linden]

nothing seems to be wrong with the syntax.. maybe the data inside is 99

[celest relic]

nope, i have checked the data. The topmost amount of kills is 1428 and uhh when i print the whole set of data without LIMIT 10

it is present there

not in descending order

[jaunty linden]

this is a shot in the dark but try f"SELECT name, kills FROM {self.table_name} LIMIT 10 ORDER BY kills DESC;"

[celest relic]

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ORDER BY kills DESC' at line 1

[jaunty linden]

just changing the order in the syntax gives us a diff behavior.. . atleast this tells us that sql processes commands sequentially instead of as a whole... could be insightful

[celest relic]

hmm

[jaunty linden]

could be something in the f-string... why not try the conventional .execute way

using %s

this is because f-string has been known to cause state mutations in objects

[celest relic]

its nothing to do w python

i checked w mysql workbench

somehow 99 is greater than 1428

[jaunty linden]

what data type is the column?

[gray halo]

Hello! I'm using Firebase for a production level project and was wondering how I should write my security rules. The way I am doing auth on my project is I am verifying if the user can get the requested data from the database using sessions and log in information from the frontend. Because in my backend routes I know if a user has access to the data, does that mean my security rules can allow anyone to read data? Thanks!

[jaunty linden]

why not use a foreign key to link the data table to the users table

[gray halo]

Yeah, I'm planning on doing that. But I'm not sure I understand how that will help with my security rules. With this implementation, a user will sign in and I will check their session each time they make a request which will store their userID, then I will use the userID as a foreign key to access their data. However, does this mean I can allow any user to read data? Because they would only get access to the database if they were logged in right?

[jaunty linden]

if you want limited access to the db you should add the jwt token holder in the APIs

[gray halo]

Thanks! can you elaborate?

[jaunty linden]

depending on what framework you use... you could create an oauth file to create (and verify) the access token.. and then in your apis you could pass in that as an argument to , e.g. your get_request(...)

[gray halo]

would the oauth file be on the frontend?

[jaunty linden]

for example.. i am currently working on a fastapi model

def get_posts(
    db: Session = Depends(get_db),
    current_user: int = Depends(oauth2.get_current_user),
):
    all_posts = db.query(models.Post).all()
    return all_posts

here i used current_user = Depends(oauth2.get_current_user) to limit get requests would only be made by logged in users

[gray halo]

alright, i think i get it. i think i'm doing the same thing with sessions right? im making sure that the user is logged in on each request based on what the frontend is passing as the cookie

or am i understanding something wrong?

[jaunty linden]

yes .. the client sends the generated token to the API along with their request .. the API verifies the token is the same as the one it generated and then sends response

[gray halo]

yep! thank you! so how should i model my security rules in firebase for this scenario?

[jaunty linden]

i've never used firebase but you could see its documentation section on security... or your framework's docs on security for that

for example i'm using postgresql with the fastapi framework and i refer to the fastapi docs on security

[gray halo]

thank you! i will take a look

[jaunty linden]

yeah i've only given you an idea of how to create the security in the API layer rather than the db itself

[hazy hazel]

can someone help me with this error please : django.db.utils.IntegrityError: insert or update on table "authtoken_token" violates foreign key constraint "authtoken_token_user_id_35299eff_fk" DETAIL: Key (user_id)=(1) is not present in table "Occupier_occupier".

[manic willow]

Hi Sam, you're trying to save a Token from your authtoken app bu it needs a related object named Occupier to be saved as well. How did you create the Token?

[paper flower]

Two applications (discord bot and database) can run on the same server

[hazy hazel]

oh, authtoken isn't even my app name but, i made token in models.py

[slender ridge]

Hey I am trying to process a mongoDB cursor. I am using multiprocessing.map(func, cursor) and that will make multiple pools for one iteration of the cursor. Is there any way to slice the cursor or do something where my processes would be able to receive a chunk of the cursor?

Process (func, cursor[0:100])

Process (func, cursor[100:200]) and so on

[tiny eagle]

What's the convention for using indexes in Postgres, do you just apply a B-Tree to any column that you see yourself using as a select constraint, and leave the other ones alone? Of course the B-Tree can change to any other algorithm that fits the datatype, though I take it B-Tree is best for any number or text?

[keen minnow]

It's a trade off.
More indexing means more overhead to manage them or insert data.
The type of index also matters depending on the access patterns.

When it comes to performance, the say is "you can't improve what you can't measure". So make sure you have plenty of information with regards to the performance of your queries and their nature

[tiny eagle]

Ah, so to figure out where, and which index to add, I'd first take a look at if the column is being used in a select, and how often, if its enough to warrant adding the index, I'd read through Postgres' index algorithms and choose the one that best fits my datatype? e.g. B-Tree for numbers and text, GIN for JSONB and so on?

[keen minnow]

basically, yes

also hash indexing is useful for look ups

[tiny eagle]

ooo, that one would be very useful for every single instance of a Discord ID?

[keen minnow]

yep. You may want to read up https://www.postgresql.org/docs/current/indexes.html

[tiny eagle]

Alrighty, thank you so much! And just before I head off, you can sort of mishmash these, and use multiple indexes, of different types for multiple columns in a single table, and get the combined benefits of each in your select, assuming you're using that column in the select's constraints?

[keen minnow]

yep, it's also detailed in that link

[brazen charm]

bearing in mind a discord id should likely be your primary key

which is automatically indexed by postgres

[tiny eagle]

Ah, there are instances where I might have multiple ids, or have an actual ID column, for e.g. https://mystb.in/CasinoWinesRepresentatives.sql

[rancid raven]

Hello everyone, a newbie here!
I'm developing a simple inventory manager with python, but I want the database to be stored in the cloud so it can be accessed at the same time by several clients, I don't start yet to develop the communication with the database but I know there is the problem of two users trying to write at the same time the same field in the database.
Or that a client asks for information to modify it, but just after reading it, another client modified it and therefore a part of the information was lost (I hope I am making myself understood).

I come here with the intention that someone can guide me, with ideas of what is the best way to solve this and approximately how it would be done.

I am not a native English speaker, so please excuse me if I made any spelling mistakes.

Thanks :)

[pliant forge]

You should research isolation levels

Locks and blocks

[rancid raven]

I just did some research and it looks like an interesting option, can you tell me how I should implement it? I mean, I see that the best lock is "serializable" as it forces the requests to be executed serially, but, should I just tell the database to use that lock and then I can query the database without any problem? Or should I do something else for this to work?

[pliant forge]

Entirely depends on usedasw

Use case

[rancid raven]

What I am doing is a desktop application that will be an inventory manager, I want to save that information in a database in the cloud so it can be used by several machines (customers) at the same time, it is simple, the application must be able to display the database all the time updated and sometimes make modifications, such as deleting a row, or alter the quantity of a product. I just want to know how I should make the requests to the database.

I have in mind to use the free PlanetScale service.

Can you please help me? I can't find precise information on the Internet (probably I'm not looking for it correctly).

[pliant forge]

This is something that requires alot of testing

[keen minnow]

fyi, in general, customers don't connect directly to the databases but to an application with an API (ex: flask)

[pliant forge]

Customer will always see data that’s x amount of time old I think, not 100% live

[rancid raven]

So I need to develop the API in Flask first to make the connection?
From it I should manage how requests are routed to the database?

[keen minnow]

For privacy, security, management and loose coupling, yes, customers should never have a direct access to the database

It still doesn't change the fact you may have multiple application servers at the same time doing conflicting requests though

[rancid raven]

Ok, I understand, so how can I fix it? And, in case of using a flask API, where could I host it (ideally without paying), I'm thinking about Heroku, but I don't know if it has the capability to do so.

[keen minnow]

they all do. In terms of locking, you may want to look at select for update

[rancid raven]

Sorry, but I don't understand what you are saying, my English is not very good😅

[keen minnow]

https://www.cockroachlabs.com/docs/stable/select-for-update.html

[keen minnow]

You may also want to check out https://www.manning.com/books/microservices-patterns. It describes pretty well the various ways to design and architecture a system.
It's focused on java but it remains applicable to python.

Some folks brought up https://www.cosmicpython.com/ in a previous related discussion as another book more focused on python but I haven't read it, so can't comment further on it.

[broken gazelle]

Hi hi! Could someone please help me with what's going wrong here?

script = f"UPDATE host_scores SET 4star = 4star + 1 WHERE host_id = '401317789769531402'"
        await self.bot.pg_con.execute(script)```
...throws this error:
```py
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: PostgresSyntaxError: syntax error at or near "4"```

Here's what my table looks like:

[mystic blade]

@dusty island

yo accept my fR its me

[grim vault]

Most likely your column names starting with a number was not the best choice. Use double quotes to use them as sql identifiers:

script = 'UPDATE host_scores SET "4star" = "4star" + 1 WHERE host_id = ?'
await self.bot.pg_con.execute(script, (401317789769531402,))

You should also use placeholders instead of f-strings (depending on your db-module it might use a different placeholder than ?).

[hoary gate]

pls any1 im about neck myself

[zealous spire]

green is string. note where it stops being green. the opening ' does not correspond to the last one, as the 's need escaping (or swapped with "s). admittedly, the error text is not the best

[torn sphinx]

How do I set auto_increment in tortoise int field

[thorn nymph]

hey guys, im trying to learn mongo db

and i have an error

always when i try to connect

i get mongo server selection error: connection <monitor> closed

anyone knows why?

[paper flower]

Using versioning could be a better option, because select for update would lock the rows and other users won't be able to access it

I think select for update is a good option if you select an entity and commit as soon as possible to release the lock

Also it doesn't really help with stale data, since it's sent to the client and could be stale when it makes a modification

[fading patrol]

how exactly are you trying to connect and what is the full error? did you google it? without any further details my first guess would be network connectivity.... are you using the right port and is it open, etc.

[thorn nymph]

mongosh "mongodb+srv://cluster0.t38ppqi.mongodb.net/sample_airbnb" --apiVersion 1 --username ******

[hollow garnet]

Connecting to a database with python jaydebeapi and jpype. So I wasn't really thinking about it but I I started the JVM with jpype each time I ran the code...unfortunately I also didn't stop it after the start. It has since crashed and I can't get it to start back up again. Getting error: JVMNotRunning: Java Virtual Machine not running

Already modified the code to shut down jvm each time but not sure how to get it running again.

I am running the code on a Linux VM
Please @ me

[sage warren]

Hey guys im having issues connecting MySQL to python, both are coming up when i check through cmd but when i try importing mysql.connector im getting a module not found error. Mysql and connector both show up in cmd though. I tried setting up a venv and still getting same error.

Any recommendations on troubleshooting?

[fading patrol]

Did you install the relevant modules in your venv (using pip)?

[sage warren]

Yes and when i check through cmd they show up

[inland forge]

I'm using aiosqlite. how do I set/use default values?

if possible, ofc

[fading patrol]

It doesn't matter if they're installed for your os, they need to be installed in your venv to be imported. You can use pip with your venv active to confirm which version of each package is installed (or not)

[hallow rover]

so im making a leaderboard system, but i also wanna make it so that users can check their own rank based on how many points they have without selecting everything and iterating through the entire database to find one user, and i dont know how to go about this, im using postgresql btw. rn i have one column representing users and another storing how many points they have

[sage warren]

Thank you, i will double check after work.

[fading patrol]

https://www.postgresqltutorial.com/postgresql-window-function/postgresql-rank-function/

[empty blade]

does anyone know how to dump regex matches to a json file?

[paper flower]

What's your problem?

[empty blade]

I have a regex loop and I want the results to be put in a json file

[paper flower]

Just add them into array and dump it into file

[empty blade]

uhm can you give me an example code for that?

[paper flower]

Do you know how to work with files?

[empty blade]

kinda I think, I know the basics of reading and writing

[paper flower]

Use json.dump to write your object to a file

[empty blade]

oh I see thanks

[red beacon]

hey guys i am making a blog site and i am currently making a email sign up form, to get email addresses and then frequently send marketing emails to my users, how would i store the data taken from this (the emails)? in a text file using js? on a database? how else? what is the best way? for a beginner blog site?

thanks for anyone attempting to answer really appreciate it

[soft gorge]

Can I run a python function on a trigger event in mySQL with python?

[void lodge]

i recently created a comment section in my Django project and i want to redirect users to that same post they just commented on after they posted the comment. right now it redirects them to the home page. here is the views.py file: class AddReviewView(CreateView): model = Review form_class = ReviewForm template_name = 'blog/add_review.html' def form_valid(self, form): form.instance.post_id = self.kwargs['pk'] return super().form_valid(form) success_url = reverse_lazy('blog-home')
here is the models.py:

    post = models.ForeignKey(Post, related_name="reviews", on_delete=models.CASCADE)
    name = models.CharField(max_length=255)
    body = models.TextField()
    date_added = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return '%s - %s' % (self.post.title, self.name)``` and here is the urls.py file: ```urlpatterns = [
    path('', PostListView.as_view(), name='blog-home'),
    path('marketplace/', MarketplaceView.as_view(), name='blog-marketplace'),
    path('freelancers/', FreelancersView.as_view(), name='blog-freelancers'),
    path('user/<str:username>', UserPostListView.as_view(), name='user-posts'),
    path('post/<int:pk>/', PostDetailView.as_view(), name='post-detail'),
    path('post/new/', PostCreateView.as_view(), name='post-create'),
    path('post/<int:pk>/update/', PostUpdateView.as_view(), name='post-update'),
    path('post/<int:pk>/delete/', PostDeleteView.as_view(), name='post-delete'),
    path('about/', views.about, name='blog-about'),
    path('search-posts/', views.search_posts, name='search_posts'),
    path('post/<int:pk>/Review/', AddReviewView.as_view(), name='add_review'),
]```
 pleae help me!!
note: i want to change the success_url = reverse_lazy('blog-home') to redirect to the page they just commented on

[sage warren]

Please see response from pip list -it looks like they are installed in the virtual environment but i still get that module not installed error when i try to import it in VS Code. Do i need to specify the virtual environment in VS Code?

[fading patrol]

Yes, if VS Code is using the wrong environment, that would explain it

[sage warren]

i think that got it, i changed the path to python in VSCode and it doesn't show an error now, let me mess with it to verify. Thanks!

[broken gazelle]

thanks so much!

[tiny eagle]

What's the difference between (PSQL)sql CREATE INDEX test2_mm_idx ON test2 (major, minor);and sql CREATE INDEX test2_major_idx ON test2 (major); CREATE INDEX test2_minor_idx ON test2 (minor);

[sterile wyvern]

isnt the first a multicolumn index, the latter two indexes?

[tiny eagle]

Yeah, but I'm not sure what the difference between them is, and when to use each one

[buoyant hemlock]

hi

[paper flower]

It would all depend on how your search/sort your data, for example if you want to order by or look up something by minor column you won't be able to with composite index

[astral jacinth]

Hello, if i have a table like this
name | parent
-----------+--------
Top |
Child1 | Top
Child2 | Top
Child3 | Top
SubChild1 | Child1
SubChild2 | Child1
how can i get a result like this?
name | child
--------+-----------
Top | Child1
Top | Child2
Top | Child3
Child1 | SubChild1
Child1 | SubChild2
without having to do this manually for each parent?

[tiny eagle]

If you have a table called guilds that has one column - id, that stores every guild the bot is in, with it configured, and add a hash index to it, would every other table that uses it as a foreign key benefit from this hash index whenever that id is used as the only constraint in a WHERE clause?

Or would I have to add that hash index to every single column, even though it is a foreign key

[hearty ingot]

guys can anyone help me? what am i supposed to change here? note that i have mysqlworkbench installed on my pc

[fading patrol]

You're just changing the name and order of the columns, or am missing something? If that's all, it's a simple select query

[fading patrol]

Probably not, is there any problem you forsee with a simple join?

[fading patrol]

The error message seems straightforward... Are those users actually set up on your DB without passwords?

[sage warren]

How can I use a variable in MySQL queries? Trying to get user input into a variable that will create my query. Please see sample code:

import mysql.connector
from tabulate import tabulate
db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="pw1",
    database="test_database"
)
user_size = input("Please input size needed: ")
user_rating = input("Please input rating needed: ")
mycursor = db.cursor()
mycursor.execute("SELECT * FROM test_db_table1 WHERE f_size='user_size' AND f_rating='user_rating")

myresult = mycursor.fetchall()

[fading patrol]

https://pynative.com/python-mysql-execute-parameterized-query-using-prepared-statement/

Very similar with other database adapters like SQLite3 and psycopg/Postgres

[sage warren]

thank you sir, will read

[olive reef]

Should I store a discord id as an integer or as a string in sqlite?

I had a problem where Firebase would modify the ID if it was saved as an integer, i dont know if that applies to sqlite

[fading patrol]

I don't know anything about firebase and why it would do that but integer should be fine and more efficient then string

[sage warren]

this was perfect, thank you

[olive reef]

Why would this query work with f-strings but would throw an error when using placeholders?

[torn sphinx]

# add data to mariadb
def add_data(artist_name):
    try:
        statement = "INSERT INTO artists (artist_name) VALUES ('%s')"
        data = artist_name
        cursor.execute(statement, data)
        cursor.commit()
        print("Successfully added entry to database")
    except database.Error as e:
        print(f"Error adding entry to database: {e}")


add_data("Aerosmith")

this returns:

Error adding entry to database: Could not process parameters: str(Aerosmith), it must be of type list, tuple or dict

Why does it have to be a list, tuple or dict? How do I just insert 1 row of data

[late canyon]

here i want to see how many cds are there which have NULL price or no price entered i am writing the query but it is showing error can anyone help me with that?

[torn sphinx]

im suprised it let you use the word "name" as a column in mysql.. usually that's a reserved word

select count(name) 'CDs' <--- "CDs" doesn't look like it goes there

if name of your table is library it would be more like "select count(name) from library .. .etc. etc

[late canyon]

then what should i do

[late canyon]

thanks, my bad

[torn sphinx]

probably because you have the f at start of line 29

[polar isle]

i forgot how this thing is called when you provide smth like %s or $1 into query and then give it as and arg, and i dont remember how do i do it in mysql, with %s? Like this?py query = """SELECT * FROM guild_mod_config WHERE id=%s;""" cursor = self.bot.connection.cursor() record = cursor.fetchone(query, guild_id)

[grim vault]

It's called using placeholders / binding parameters and yes, the mysql-connector uses %s.
Two errors in your example:

• You need to execute before you can fetch
• the second parameter to the execute must be a iterable (tuple or list) with entries for each placeholder.
  So your example would be:

        query = "SELECT * FROM guild_mod_config WHERE id = %s"
        cursor = self.bot.connection.cursor()
        cursor.execute(query, (guild_id,))
        record = cursor.fetchone()

[polar isle]

thanks

[olive reef]

I don’t use f-string with placeholders

[grim vault]

What's the error and how does the code look? Placeholder are also db-module dependent, so sqlite3 / mysql / postgres are using different styles.

[acoustic cave]

What relation do I make between a user and a group
The user can have many groups and the group can have many users.
Do I store the users in the group table only or do I also store the groups a user is in in the user table as well?
Thanks in advance!

[grim vault]

You store the user and group combination in a third table.

Pseudo:

table user:
  user_id PK,
  ...

table group:
  group_id PK,
  ...

table user_group:
  user_id FK,
  group_id FK
  PK(user_id, group_id)

[acoustic cave]

PK is a primary key, right ?

[acoustic cave]

and also wouldn't data get duplicated this way by having a user or a group more than one time

[grim vault]

Yea, PK primary key, FK foreign key.
And no data gets duplicated. You need the id to reference the data, so the id gets used but that's just how it works.

[acoustic cave]

Thanks!

[tiny eagle]

Postgres does not support multicolumn hash indexes, does it?

[paper flower]

Postgres supports multicolumn indexes, what do you mean by "hash index" though?

[tiny eagle]

Something like sql CREATE INDEX ON positions USING HASH (guild_id, config);

[paper flower]

Why not use standard btree?

[tiny eagle]

Hash indexes are faster for equality comparisons, and there's no other comparison I'd make for a Discord ID and a boolean

[paper flower]

I don't think you'd have that many rows in that table for it to matter

[tiny eagle]

From what I've heard its a 20-30% increase in queries, so why not?

But Postgres docs don't mention Hash indexes in the multicolumn index page, but I'm not quite sure as they used another term.

[ebon skiff]

If I use execute on sqlite like an update do I need to close that execute?

[brazen charm]

really depends on the size of the DB

but also, it really doesnt matter for most people doing bots

[olive reef]

Im not in my pc rn but it was some sort of value error related with types. I was using ? for placeholders

[sage warren]

hey guys, i'm trying to print results from a query into a csv file and its working but the data begins with ( and ends with ). I'm using the following code, anybody know why or how i can eliminate the "(" at the beginning and end of the data?

import mysql.connector
from tabulate import tabulate
db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="yesyes",
    database="flange_database"
)
user_size = input("Please input flange size needed: ")
user_rating = input("Please input rating needed: ")
mycursor = db.cursor()
para_query = "SELECT * FROM flange_db2 WHERE f_size=%s AND f_rating=%s"
tuple1=(user_size, user_rating)
mycursor.execute(para_query, tuple1)

myresult = mycursor.fetchall()

print(tabulate(myresult, headers=['Size', 'Rating', "Bolt QTY", "Bolt Dia", "Bolt Length", "RTJ Bolt Length"], tablefmt='psql'))

with open('querydata3.csv', mode='a', encoding='utf-8') as f:
    f.writelines(str(i)+'\n' for i in myresult)
mycursor.close()
db.close()

Attached a snippet of the results.

Thank you for the help.

[west hill]

fetchall returns a tuple if u want to remove the parantheses u need to unpack the tuple

u can do they by adding a * in front of myresult

[tiny eagle]

Right, though what's the point of not learning something new, and something that will benefit me

[torn sphinx]

I made a command that adds the member's id to the database. But how do I read all the ids that are already added?

Im using PostgreSQL

[civic cargo]

Use a select query, e.g. SELECT * FROM member; if your table is called member

[torn sphinx]

I've done this. The problem is that it only reads one line instead of reading them all down

data = await self.bot.db.fetchrow("SELECT users FROM antiraid WHERE guild_id = $1", ctx.guild.id)```

[civic cargo]

You are using fetchrow - look for something like fetchmany, fetchall, or just fetch? Not sure which DB driver you are using

e.g. with asyncpg: https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.connection.Connection.fetch
notice fetchrow below is documented to only return the first row of the result

[sage warren]

Thanks, i'll try that.

[torn sphinx]

got it. thank you for your help

@civic cargo Do you know how I can make these symbols not appear, only the ids appear?

[prime hollow]

guys any sql free ide?

i instaledd some but its a 30 days trial

in eed it for a discord bot btw

[torn sphinx]

What you mean by free ide?

[torn sphinx]

Better question, what database are you using?

[prime hollow]

what do u mean by that?

[torn sphinx]

Is it MySQL, MariaDB, postgres, mongo or is it sqlite??

[prime hollow]

sqlite

and i found a free version of the program iam looking for

ty anys

[inland forge]

async def fetch_all_weapons(database_cursor: aiosqlite.Cursor, user: int | discord.User) -> list | aiosqlite.Row:
    await database_cursor.execute('SELECT * FROM weapons WHERE user_id = :user_id',
                                  {'user_id': user})
    info = await database_cursor.fetchone()
    return [i for i in info if i != user]

I have this aiosqlite function for my bot. is there any way to get the columns names and make the function return a dictionary that looks like {column_name: column_value} and so on for all the columns?

[crimson tangle]

hello

any mongodb user ?

how can i create a count atribute ?

[fading patrol]

I recommend DBeaver FWIW

[uneven stream]

#help-cupcake ?

[hollow marten]

await cursor.execute("""CREATE TABLE buy_orders (
                                OrderID INTEGER PRIMARY KEY, 
                                OrderTime TIMESTAMP NOT NULL DEFAULT TIMESTAMP(), 
                                OrderAuthor TEXT, 
                                BuyingOrg TEXT, 
                                BuyingItem TEXT, 
                                BuyingQuantity INTEGER, 
                                CompensationItem TEXT, 
                                CompensationQuantity INTEGER);
                                """)

Using asqlite (asynchronous wrapper for sqlite3), and I get this:

Exception has occurred: OperationalError
near "(": syntax error

What am I missing here? (ping when responding)

[red oasis]

https://paste.pythondiscord.com/zirepolani - error
https://paste.pythondiscord.com/ezakisiyij - error coming from line 8 and 16
if i remove those the database is going to init...
this error happens when i init the database using aerich init-db beside that why im doing this is because i will have to create 2 different lists for the whitelisted users per each guild..
that's the reason why im calling usermodel twice

[analog totem]

heya, where can I find documentation on how to connect/find if data is in a database?

[sterile spruce]

Use SQL sanitation

Please

[fading patrol]

A quick Google search will do. If you're having trouble, be more specific (like what database for starters)

[analog totem]

mysql but yeah it's fine just got through reading some mysql.connector documentation

thanks tho

[hollow marten]

anyone?

[fading patrol]

I would try something like Ordertime DATETIME DEFAULT CURRENT_TIMESTAMP

I done think TIMESTAMP() with parens is valid here but not sure

[hollow marten]

thanks, that worked

[tight junco]

what all stuff do i need to change to convert my mysql code to postgresql?

[keen minnow]

depends on your existing stuff on mysql.
If there isn't much, it may just be minor syntax

[tight junco]

i have subclassed databasse modal, with exec_write_query, fetchone, fetchmany, fetchall functions

then i have subclassed table to read create and delete

[keen minnow]

Sounds like there wouldn't be too much. Your best bet is to try though

[tight junco]

and i create pool like this

[tight junco]

yeah, what all do i need to replace with what? i honestly have no idea about how postgresql works

[keen minnow]

it works literally the same way than mysql 🙂
It's a server and thus require a host, user, password, db, etc.

[tight junco]

so i just change the credentials here?

[keen minnow]

I would refer you to the doc of the orm you use (alchemy?)

obviously, aiomysql wouldn't work with other DBs than mysql

[tight junco]

no dunno alchemy either

[tight junco]

oh then?

[keen minnow]

either look for an equivalent of aiomysql for postgres or use an ORM like sqlalchemy which abstracts you from some of these details

[tight junco]

so i just need to change the database_pool here right?

[keen minnow]

in that case, there would be more changes since the APIs would be slightly different than aiomysql

but they would have the same spirit

[timid latch]

related to .json databases

#discord-bots

help pls

[remote geyser]

Hi guys I'm having a problem trying to use mysql for excel

for some reason i cant get it to work

it always shows Connection attempt failed the given key is not present in the dictionary

could someone help me out with this i'm stuck here

[austere portal]

How can I check if a column has a value in sqlite? ```sql
sqlite> select result from mytable where id='1234';

sqlite> ```

the result is empty

select result != null ... doesnt give an output either

and how can I use it in a where clause ```sql
sqlite> select * from mytable where user1_id=1 or user2_id=1 and type='abc' and result != null;

[fading patrol]

First result on Google: https://www.techonthenet.com/sqlite/is_not_null.php

[sage warren]

Not getting it to work, can you give a bit more detail? I tried adding the * in front and it gives me an invalid syntax error.
If i add the * in front of (i) i still get an error.

    f.writelines(str(*i) for i in myresult)
TypeError: str() takes at most 3 arguments (6 given)

[stoic finch]

im trying to use redis with difflib to get the closest match but it doesn't seem to be work (with discord)

        REDIS_CLIENT = redis.Redis(host="...", port="...", username="...", password="...", charset="utf-8", decode_responses=True)
        COLLECTIONS = REDIS_CLIENT.lrange("collections", 0, -1)

        similar = difflib.get_close_matches(collection_name, COLLECTIONS, n=4, cutoff=0.6)
        for name in similar:
            options.append(nextcord.SelectOption(label=name, description="Magic Eden collection"))
        super().__init__(placeholder="Collections", min_values=1, options=options)

before when i was storing inside a .txt file it worked fine?

[grim vault]

You could just use the csv module:

import csv
...
with open('querydata3.csv', 'a', encoding='utf-8', newline='') as f:
    writer = csv.writer(f)
    writer.writerows(myresult)

[sage warren]

thank you sir, i will give that a try. I found a way around it using this but its dirty:

myresult = str(mycursor.fetchall())
mynewresult = myresult.replace("(", "").replace(")", "").replace("[", "").replace("]", "")

[sage warren]

Your suggestion worked. Thank you very much.

[quasi mirage]

Knew it was a longshot anyone knew SA Thanks

[keen minnow]

Where is your question?

[quasi mirage]

I am trying to achieve the following. I do have data models set up and working with SA including relationships.

select
count(j.joke_id)
from joke j
left join tag t on j.joke_id = t.joke_id
group by t.tag```

So I am basically looking for these results (not literally - just conveying what I am trying to get)

* Tag1 54
* Tag2 56
* TagZ 45
* TagB 23

This is what I have so far, but it isn't quite working to achieve the above.

```Python
with Session(config.connect()) as session:
    test = session.query(Joke, func.count(Joke.joke_id).label('Jokies')).outerjoin(JokeTag).group_by(JokeTag.tag, Joke.joke_id)
    result = session.execute(test).fetchall()
    for c in result:
        print(c)```

How can I alter my code or re work it to achieve the above query?

[keen minnow]

your select is missing the tag you are grouping on

[quasi mirage]

I'm not selecting on any single tag.

[keen minnow]

if you want :

* Tag1 54
* Tag2 56
* TagZ 45
* TagB 23

You would need to know the tag, wouldn't you?

[quasi mirage]

Thats an example. See between ()

The key element is the example raw sql query. That is the end goal

[keen minnow]

and I am saying that raw query is wrong

[quasi mirage]

It's not literal

[keen minnow]

it should be more like select count(j.joke_id), t.tag ...

[quasi mirage]

You don't have to select on what you group on in sql.

The only real problem with the raw sql is I didn't add joke_id to the grouping. In the real world that would cause an error

[keen minnow]

so let's get back to the basics:

• What do you observe?
• What were you expecting to observe?

[quasi mirage]

I am expecting,

Tag1 43 jokes
Tag2 23 jokes
Tag3 65 jokes

But I am getting too many records back so something is off with the python

It's like it isn't grouping

[keen minnow]

what do you mean by too many records?

[quasi mirage]

I think I might see it

[keen minnow]

I also would not group by joke id if they are their unique identifier

(and you would still be missing which tag is for which count)

[quasi mirage]

You have to group by what is in the select when counting. I think I see the issue. I just need to figure out how to count on something else and how to display the queried results properly. Googling something now.

Yeah I sort of see what's wrong. The problem is going to be figuring out how to best achieve. I may be able to flip it and right join it. The underlying trouble point is - I could solve this right now if my data schema has a tag id in Joke. That's be easy. The problem I have is I don't have an FK from tag to Joke. Instead, I have three tables, Joke, Tag, JokeTag. JokeTag ties back to both tables. Because of that I can't follow normal left join logic because it wouldn't count properly. So my best bet is to do a right join. Thanks.

I did get it counting at least and do see my path forward. Thanks again.

[keen minnow]

if you only care about the count of jokes per tag, why do you even need a join? Why not just using the JokeTag table since I assume it contains a map of joke -> tag

[quasi mirage]

I'm not sure I follow you. Are you saying use the relationship? I'm not sure how I would do that.

JokeTag to joke is a many to one.

opposite is one to many

I do have reverse relationships set up

[keen minnow]

What's the schema of your joketag table?

[quasi mirage]

Just joketag_id, joke_id, tag_id

The last two are FKs to Joke and Tag.

[keen minnow]

So if you want the number of jokes with a specific tag, then grouping all the rows in joketag by tag_id would give you a number of jokes for each different tag (considering also that one joke may have multiple tags)

[quasi mirage]

Possibly but I tried that first and the orm didn't like it.

Let me try again

I think I got it, but how do you show the label / count?

[keen minnow]

#databases message

[quasi mirage]

No, that isn't orm

[keen minnow]

works the same way

[quasi mirage]

That's not the point. ORM leverages the relationships. I got it. It's still a little tiny bit off but it's there.

Thanks

[keen minnow]

I mean, that's exactly the point though. The orm will sit on top of it

[quasi mirage]

I think you're missing that Joketag has an FK to Joke rather than the opposite. Typically you have the opposite of what I have but my use case is sort of niche. So select count(j.joke_id), t.tag isn't going to quite do it. You were right in your thoughts on using JokeTag and counting there. In theory, grouping on tag_id should do it. Since no joke has the same tag multiple times. So however many times a single id occurs should give me the count. Which I got. Thanks again.

This solves it

test = session.query(JokeTag.tag, func.count('JokeTag.tag').label('Jokies')).group_by(JokeTag.tag)
result = session.execute(test).fetchall()

[keen minnow]

Aren't tags uniquely identified by their tag id?

[quasi mirage]

Yes, but my shcema isn't typical. It's a schema specifically for tags. Ordinarily Joke would have a column tied back directly to Tag. I have a table in the middle to make certain things easier. Then Tag's PK is literally the tag field so that I don't have to join and can just see the tags in both tables plus the db ensures there are no dups. The above solves it. I've got to go but thanks for helping me re think

[keen minnow]

join tables as such are pretty standard. I think something is being overthought here. But as long as your query works for you, go for it!
Good luck

[wise goblet]

What is the way to filter this query by
is_online == True?

SELECT players.id AS players_id, players.name AS players_name,
players.region AS players_region, players.system AS players_system,
players.time AS players_time, players.timestamp AS players_timestamp,
max(players.timestamp) = players.timestamp AS is_online 
FROM players
GROUP BY players.id 

my current still working query

[torn sphinx]

What is this database for?

[wise goblet]

for table of player data you can see in a picture

[torn sphinx]

Oop yea there

Cool

[keen minnow]

if you move max(players.timestamp) = players.timestamp to a WHERE, wouldn't that already limit the results to that case?

[wise goblet]

when I try adding WHERE or HAVING is_online, it says it does not exist

[wise goblet]

aggregate functions aren't allowed in WHERE

as HAVING it works though

[keen minnow]

right

I should pay more attention

[wise goblet]

ergh. it is acceptable solution. i thought i could reuse alias though

[keen minnow]

I mean, either way, if you limit your results to that, is_only would always be true anyway

[wise goblet]

that's strange. I expected to get only record with maximum players_timestamp. Strange that different timestamped records are in output

The expected rule for is_online: if player has timestamp of latest datetime, then it is True, otherwise False

maximum = SELECT max(players.timestamp) FROM players

SELECT players.id AS players_id, players.name AS players_name,
players.region AS players_region, players.system AS players_system,
players.time AS players_time, players.timestamp AS players_timestamp,
'2022-07-31 01:11:11.178989' = players.timestamp AS is_online
FROM players

Simple solution to achieve. Just in two SQL requests 🤔

oh, actually i can combine it into one. It is still two, but who cares

SELECT players.id AS players_id, players.name AS players_name,
players.region AS players_region, players.system AS players_system,
players.time AS players_time, players.timestamp AS players_timestamp,
(SELECT max(players.timestamp) FROM players) = players.timestamp AS is_online
FROM players

dammit. It is still not working

SELECT players.id AS players_id, players.name AS players_name,
players.region AS players_region, players.system AS players_system,
players.time AS players_time, players.timestamp AS players_timestamp,
(SELECT max(players.timestamp) FROM players) = players.timestamp AS is_online
FROM players
WHERE (SELECT max(players.timestamp) FROM players) = players.timestamp

well. this works.

[keen minnow]

If you want online players, wouldn't you want instead players where the timestamp >= now() - X minutes?

[wise goblet]

ergh. this is data from another API. essentially last timestamp gives last batch of players who share same timestamp, last time where online

yeah we could be checking now() stuff anyway... but for now i am satisfied with rule, last time gotten

[hollow marten]

Using asqlite, but I get this error when trying to connect; what's happening?

conn = asqlite.connect("uc_transactions.db")
cursor = conn.cursor()

Exception has occurred: AttributeError
'_ContextManagerMixin' object has no attribute 'cursor'

Mention or reply when responding, please

[fading patrol]

Try following the example in the readme

[hollow marten]

This is part of a Discord bot I’m making, and it doesn’t recognize commands when it’s inside all of that. I’m probably missing something obvious to fix it here; sorry, I’m still relatively new to databases and async

[grim vault]

It's the async version. You need to await the result of the functions:

conn = await asqlite.connect("uc_transactions.db")
cursor = await conn.cursor()

[autumn minnow]

Hi, I am using flask-sqlalchemy:

I have 2 tables like this:

class Ride(db.Model):
  finish_position = db.Column(db.Integer, index=True)
  ...
  forms = db.relationship('Form',backref='ride', cascade="all,delete", lazy='dynamic')


class Form:
  ...
  ride_id = db.Column(db.Integer, db.ForeignKey('ride.id'))

I wanted to query on Form table with its parent table attribute finish_position directly from Form class.

So I added hybrid_property like this following docs. it looks like this.
docs link: https://docs.sqlalchemy.org/en/14/orm/extensions/hybrid.html#join-dependent-relationship-hybrid

class Form(db.Model):
  ...

  @hybrid_property
  def ride_finish_position(self):
    return self.ride.finish_position


  @ride_finish_position.setter
  def ride_finish_position(self, value):
    self.ride_finish_position = value


  @ride_finish_position.expression
  def ride_finish_position(cls):
    return Ride.finish_position

but it is returning something like this:

>>> Form.query.filter(Form.ride_finish_position == 1).count()
<string>:1: SAWarning: SELECT statement has a cartesian product between FROM element(s) "ride" and FROM element "form".  Apply join condition(s) between each element to resolve.
2878330

can any one help me so that I can query directly like that?

[uneven stream]

I need to save a list of names
do I just create a table with one column?

[elder marsh]

You also need a primary key

[paper flower]

Did you make a relationship between these two tables?

[autumn minnow]

Yes on ride column

[paper flower]

Can you share full models?

[crimson tangle]

How can I connect to mongodb vai phyton ?

Any contact function?

[fading patrol]

Pymongo among others... BTW Google is a lot faster than Discord for a question like that ;)

[crimson tangle]

hm got is btw which is batter pymongo or motor ?

like i am creating a dc bot !!

[fading patrol]

I haven't tried motor

[crimson tangle]

ohh thanks 💙

[wise goblet]

best option to write query like
tags = ['AWES', 'Aiv']

SELECT * FROM players
WHERE name in tags?

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
   OR column1 LIKE '%word2%'
   OR column1 LIKE '%word3%'

this look like an option 🤔

[fading patrol]

It's telling you that you need a series or df instead of a string, so... Maybe something like row = pd.Series(row) might do it

[quasi mirage]

I'll try

[keen minnow]

That sounds expensive. May want to separate that in two tables?

[wise goblet]

SELECT player.nickname FROM players
WHERE nickname LIKE '%word1%'
   OR column1 LIKE '%word2%'
   OR column1 LIKE '%word3%'

can't really imagine how i can this data transform into anything else.
I am checking that player nicknames match against list of tags

[keen minnow]

nicknames or tags?
I can see why nicknames can't be split off, but tags could be

[wise goblet]

tag is a partial match of nickname

[keen minnow]

oh like team name?

[wise goblet]

that's included.
player nicknames have like
[AWES]-JohnSmoth-SomethingElse

prefix in nickname is their group name yeah

i wish that even if filter tag was asked as AWES, it would still match [AWES] and whole player nickname
there are no clear rules about what is the limit of prefix group name, except it being part of name

[keen minnow]

I would probably still look into:

• Keeping a raw version somewhere
• A processed table(s) where the group/team are separate

Anything with LIKE sound painful and smell like table scans

[wise goblet]

i estimate size of table around 5000 records at maximum

it should be fine

[keen minnow]

depends on the query per sec, but yeah

[wise goblet]

Plus... the data that is queried, acceesed in a background task in celery

i will not care even if it takes 10 seconds to query

as long as it would be queried within one http request without its timeout errors, i am ok

[prime hollow]

i was thinking of creating a table that has 1 item for every column but that means if i want to add 50 items to the game that means 50 columns in the table which will make the table too long in a ridiculous way and the table inside the database will look sth like this :
user_id | sword| shield| potion| etc...
10 | x 1 | x1 | x5 | ..
is there any short way of doing it?

[storm mauve]

you could store it like ```py
user_id | item_id | count
10 | 'sword' | 1
10 | 'potion' | 5

*disclaimer: item_id should probably be a number (and a Foreign Key to some item table with more info about the items), not just a string, but do not worry about that now

[civic vault]

@prime hollow, in audition to @storm mauve , his post is about Data Modeling, a great way to organize all data that we need in Data Models.

Data models are made up of entities, which are the objects or concepts we want to track data about, and they become the tables in a database.

[sage warren]

hey guys, need a bit of help getting data from tkinter entries and using them for a query in MySQL. I've tried using .get() and even defined a function to .get() and assign text value to a label but i'm still having trouble. Is it possible for me to use .get() within para_query? Any help would be greatly appreciated. Thanks.

e_size_label = Label(root, text="Enter Size: ").grid(row=2,column=0,sticky='e')
e_size = Entry(root, border=5)
e_size.grid(row=2,column=1,padx=5,pady=5)

e_rating_label = Label(root, text="Enter Rating: ").grid(row=3,column=0,sticky='e')
e_rating = Entry(root, border=5)
e_rating.grid(row=3,column=1,padx=5,pady=5)

mycursor = db.cursor()
para_query = "SELECT * FROM flange_db2 WHERE f_size=%s AND f_rating=%s"
tuple1=(e_size.get(), e_rating.get())
mycursor.execute(para_query, tuple1)

myresult = mycursor.fetchall()

def myClick():
    with open('querydata3.csv', 'a', newline="") as f:
        writer = csv.writer(f)
        writer.writerows(myresult)

myButton = Button(root, text="Get info & save", command=myClick).grid(row=4,column=1,sticky='e'+'w',padx=5,pady=5)

[north trellis]

what does this mean in sql?
UNIQUE (user_id, item_id)

does it mean that their combination is unique or each of them is unique? and if it's the former, how do i translate it into something sqlalchemy will understand?

[fading patrol]

Each of them is unique https://www.w3schools.com/sql/sql_unique.asp

[versed quiver]

That they are a unique combination. Not that each is itself a unique value.

[versed quiver]

the former

[tiny eagle]

Do SELECT * queries take more time than a SELECT x, y or whichever columns you actually need?

[hidden wedge]

This will become very exspensive on your end due to maintance and managing the databse, but if thats what you want all power to you.

[acoustic cave]

Hey, how do I delete a row with a foreign key and all of the rows that have that foreign key at once, SQL ofcourse

[dusk zealot]

Hello guys

i need some help on postgress

sql

i am having error cur.execute("""
IndexError: tuple index out of range

does anyone know what it is

[grim vault]

More placeholders than parameters maybe.

[copper pilot]

I'm using SQLAlchemy as my interface between code and MySQL. I have a dataset that needs to be upsert, but I'm coming up blank for an effective way to achieve this with SQLAlchemy and no actual queries in code. Any recommendations?

[fading patrol]

Here I Googled it for you. I'm surprised it's this complicated, but there are a multiple way, also answers on SA as well https://www.educba.com/sqlalchemy-upsert/

[copper pilot]

I googled it too, but found so many different results that I couldn't tell what the best option was.

[fading patrol]

"Best" is almost always context dependent, do whatever works

[copper pilot]

That one also looks to be for Microsoft SQL Server and not MySQL

[fading patrol]

Hopefully won't matter, that's part of the point of using an ORM like SQL Alchemy instead of direct queries

[copper pilot]

... and it has the SQL query in the code, which I wanted to avoid. Is this unavoidable?

[fading patrol]

Ung, I hope not.. I didn't look through all the options

[copper pilot]

It's looking like this approach DOES include the query. Back to the drawing board I guess...

I really, really hate including SQL syntax in my code. I know it's harmless, but considering I'm using a wrapper that SHOULD handle this I was hopeful I could avoid it.

And so far, from what I can tell, SQLAlchemy doesn't support upserts or duplicates handling for MySQL natively. Most answers I'm seeing either apply to a different DBMS entirely or are some kind of slapped together solution to achieve the objective.

[copper pilot]

May have found the solution.. just need to figure out how to get this to accept bulk data

https://docs.sqlalchemy.org/en/14/dialects/mysql.html#insert-on-duplicate-key-update-upsert

[paper flower]

Insert accepts a list of data too, you could look it up in docs

[dusk zealot]

hello

i am trying to copy the data from api to postgresql and it is giving me an error Tuple index is out of range ?

cur = conn.cursor()
products = data["entiteteTeReja"]["artRi"]
for artRi in products:
ID = artRi["ID"]
PERSHKRIMARTIKULLI = artRi["PERSHKRIMARTIKULLI"]
KODARTIKULLI = artRi ["KODARTIKULLI"]
PERSHKRIMIANGARTIKULLI = artRi ["PERSHKRIMIANGARTIKULLI"]
ISHITSHEM = artRi ["ISHITSHEM"]
KODNJESIA1 = artRi ["KODNJESIA1"]
KODNJESIA2 = artRi ["KODNJESIA2"]
KOEFICENTARTIKULLI = artRi ["KOEFICENTARTIKULLI"]
MAGAZINA = artRi ["MAGAZINA"]
AKTIV = artRi ["AKTIV"]
KLASA = artRi ["KLASA"]
GRUPI = artRi ["GRUPI"]
NENGRUPI = artRi ["NENGRUPI"]
TVSHKODI = artRi ["TVSHKODI"]
VENDODHJEARTIKULLI = artRi ["VENDODHJEARTIKULLI"]
PERSHKRIMI = artRi ["PERSHKRIMI"]
IMAZH = artRi ["IMAZH"]
VLERATVSH = artRi ["VLERATVSH"]
KODIDOGANORARTIKULLI = artRi ["KODIDOGANORARTIKULLI"]
ARTREF = artRi ["ARTREF"]
KODIFIKIMARTIKULLI = artRi ["KODIFIKIMARTIKULLI"]
KODIFIKIMARTIKULLI2 = artRi ["KODIFIKIMARTIKULLI2"]
KODIFIKIMARTIKULLI3 = artRi ["KODIFIKIMARTIKULLI3"]
DTMODIFIKIM = artRi ["DTMODIFIKIM"]
IDSTATUSDOK = artRi ["IDSTATUSDOK"]
PERPESHORE = artRi ["PERPESHORE"]
LLOJGARANCIA = artRi ["LLOJGARANCIA"]
GARANCIA = artRi ["GARANCIA"]
ORIGJINEARTIKULLI = artRi ["ORIGJINEARTIKULLI"]
NRPASURIE = artRi ["NRPASURIE"]
TEDHENATEKNIKE = artRi ["TEDHENATEKNIKE"]
SHASIA = artRi ["SHASIA"]
ZONAKADASTRALE = artRi ["ZONAKADASTRALE"]
MARKA = artRi ["MARKA"]
VITPRODHIMI = artRi ["VITPRODHIMI"]
LLOGARITKOMISION = artRi ["LLOGARITKOMISION"]
LLOGARIKOMISIONI = artRi ["LLOGARIKOMISIONI"]
NR_RRESHTI = artRi ["NR_RRESHTI"]

cur.execute("""
INSERT INTO artikull VALUES(
%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);""",
(NR_RRESHTI, ID,PERSHKRIMARTIKULLI,KODARTIKULLI,PERSHKRIMIANGARTIKULLI,
ISHITSHEM,KODNJESIA1,KODNJESIA2,KOEFICENTARTIKULLI,MAGAZINA,AKTIV,KLASA,GRUPI,
TVSHKODI,VENDODHJEARTIKULLI,PERSHKRIMI,IMAZH,VLERATVSH,KODIDOGANORARTIKULLI,ARTREF,KODIFIKIMARTIKULLI,KODIFIKIMARTIKULLI2,
KODIFIKIMARTIKULLI3,DTMODIFIKIM,IDSTATUSDOK,PERPESHORE,LLOJGARANCIA,GARANCIA,ORIGJINEARTIKULLI,NRPASURIE,TEDHENATEKNIKE,SHASIA,
ZONAKADASTRALE,MARKA,VITPRODHIMI,LLOGARITKOMISION,LLOGARIKOMISIONI))
conn.commit()
cur.close()
else:
response.raise_for_status()

this is the code

[grim vault]

as i said here: #databases message
There are 38 %s (placeholders) but only 37 parameters.

@dusk zealot ^^

[dusk zealot]

thansk

thanks

i did not check that

i thought i did it right

[torn sphinx]

Guys

I ve got a question

How’s better to program in sql

Ssms or use a extension in vs code?

[polar isle]

wth, I literally did IF NOT EXISTS and literally got table already exists, how is this possible

[copper pilot]

So I did just that, but their documentation is confusing at best and incomplete at worst.

I ended up playing with it a bit last night and got closer to my objectives by passing to on_duplicate_key_update a tuple containing column names to update and the values they should be updated with using the insert statement.

[copper pilot]

The end result ended up looking something like this:

insert_statement = insert(table_obj).values(dataset)
update_statement = insert_statement.on_duplicate_key_update(
column_name_a=insert_statement.inserted.column_name_a,
column_name_b=insert_statement.inserted.column_name_b
)

I don't think that's entirely right, but this iteration was the first time it didn't error for me.

Was really hoping this function was a bit more dynamic so I didn't have to write an upsert function for each different model I have.

[paper flower]

https://docs.sqlalchemy.org/en/14/core/dml.html#sqlalchemy.sql.expression.Insert.values.params.*args

[copper pilot]

That's for an insert

Not an update

[paper flower]

You're doing upsert

It's using insert statement

[copper pilot]

Right right, but defining the data in the insert statement doesn't roll over the duplicate function

You have to redefine things in the update statement.. that is, redefine what's going into the database. An insert is going to insert into ALL columns, even uniques. Updates have to insert only into non-uniques using uniques as filters.

[paper flower]

insert_statement = insert(table_obj).values(dataset)
insert_statement = insert_statement.on_duplicate_key_update(
column_name_a=insert_statement.inserted.column_name_a,
column_name_b=insert_statement.inserted.column_name_b
)

[copper pilot]

Yeah that's what I have.. and I'm confused. Either I'm missing something or that's some pretty flimsy code.

That would mean I have to write a single upsert for every single model I have.

[paper flower]

You don't have to create second statement

You should use same variable and execute that statement

[copper pilot]

That's true

But I think what the disconnect here is that you have to explicitly define column names in the duplicate function

As far as I'm aware, that's breaking a lot of "best practices" for code reusiability.

Or maybe my entire code structure is just trash.. I dunno really.

Also - not trying to be difficult here, hope it's not coming off that way. I'm just genuinely confused here.. SQLAlchemy is used in a lot of places, but it seems to be not the best at what it's doing.

[torn sphinx]

I want to ask to a MySQL dev

Anyone here?

[dusk zealot]

I do not know what kind Data type can work for Postgresql this " Product 1.750-" that has letter and numbers

any help ?

[copper pilot]

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

Looking at that now...

[paper flower]

There should be a shorthand property for that i think 🤔

Searching for it atm

[copper pilot]

I've been digging into this since last week and can't find any indication of ithere being any shorthand. Hope I'm wrong though.

[torn sphinx]

I want to ask to a MySQL dev, i am learning sql and i have some simple queries but i want to know what MySQL data analyst.

[copper pilot]

If the case ends up that SQLA can't achieve this that's fine, just want to make sure I'm thoroughly investigating the cases. Thought I'd found my holy grail of "not putting SQL queries in my Python code".. but maybe not lol

[copper pilot]

I'm not a SQL dev, nor analyst, but if it's a straight forward question I can answer. I've done light SQL dev/analyst work as an admin/engineer over the years.

[copper pilot]

Also - just ask the question instead of asking for a pro. Most times you'll get an answer.

[torn sphinx]

All i want to know is what MySQL data anlyst do.

[copper pilot]

Oh that's easy

[polar isle]

help?

[copper pilot]

So it really depends on where you work, but the gist of it is that you're basically a data and data structure curator. Dig through data, find ways to optimize, find patterns.. just overall better the data and data structures inside a data base

[paper flower]

Maybe **dict(stmt.inserted)? 🙂

[torn sphinx]

Mhmm

[copper pilot]

... hot damn that's smart.

I'll give that a try after breakfast. Thank you 🙂

[torn sphinx]

So what if i for example made a database about all the employees i inside my company, whtwould i ask a MySQL data analyst to do?

[copper pilot]

That's incredibly vague lol

[copper pilot]

You don't do much with a single table. It's about analyzing relationships across the entire database.

[torn sphinx]

I very much agree

[torn sphinx]

Hmm yes

hi how can i create 2 tables (1 storing users, and second storing channels connected to many users) 1 user can be in many channels and there can be 3 users in 1 channel i thought about creating other table for users and connecting the id to the channels table how can i connect them do db knows? using aiosqlite

await cursor.execute("CREATE TABLE IF NOT EXISTS users (id INT PRIMARY KEY NOT NULL, uuid varchar(32) NOT NULL)")

await cursor.execute("CREATE TABLE IF NOT EXISTS channels (id TEXT NOT NULL, user INT NOT NULL)")

thats what i did so far

Like this database for example right?

They simply relate to each other

[copper pilot]

Simple way to think about it - visualize yourself on a line in a factory, everyone has a job. One guy creates a table, one guy fills it with data, another guy automates filling it with data. Then you show up to review that data, that automation, and that table to figure out how it could be done better in the future.

[copper pilot]

At this point I'm starting to feel like your question is specific to one problem that isn't quite "what does an analyst do"

Is this homework or something?

[torn sphinx]

No

I just want to know ab what data anylist do.

Nothin much

So they just basicly organise databases?

[copper pilot]

I've pretty much given you a general overview, at least as much as I can offer. You're most likely better off asking this question in a more job-focused channel. THere's actually a discussion for career-based questions like this in this channel. Discussion -> Career-Discussion

[copper pilot]

This yes, and a bit more. Really depends on the position and company.

Google/YouTube would be great friends of yours for this kind of question.

This channel is more used for pointed, code-related questions.

[torn sphinx]

Oooffcourse

Why didnt i think o that

[copper pilot]

Being snarky isn't going to help you get anywhere bud

[willow tendon]

should i use a relational or non-relational database for a web app?

[copper pilot]

Depends on the web app purpose, but I'd say most likely relational.

[willow tendon]

which databases are supported by django?

[copper pilot]

Depends on your code.

[copper pilot]

django is an extension of python, python supports anything*

• • anything with a library written for it, either by yourself or someone else

[willow tendon]

i have previously used flask with sqlite3
the database was on the vps itself
however, if i use a database with MySQL or something else, how is the data accessed in that case?

[copper pilot]

You create a MySQL server somewhere - either on your localhost or a remote server - and load the data there.

[copper pilot]

sqllite is just a very stripped down, very basic, very generic database that runs locally and exists exclusively in a .db file.

MySQL (and other engines) are full software suites

[willow tendon]

so on a remote server, the data would be accessible with an http request?

[copper pilot]

No, with a SQL request.

[willow tendon]

can sql requests be made from any programming language?

[copper pilot]

https://www.hostinger.com/tutorials/what-is-mysql#:~:text=MySQL creates a database for,appear on the clients'%20side.

Yes - this is definitely a google question.

https://www.google.com/search?q=what+kind+of+relational+database+management+systems+are+there&oq=what+kind+of+relational+database+management+systems+are+there&aqs=chrome..69i57.6474j0j4&sourceid=chrome&ie=UTF-8

https://www.google.com/search?q=how+to+interface+with+a+relational+database+management+system&ei=TgjpYquXGKWy5NoP19i-wAs&ved=0ahUKEwir9vjXhKj5AhUlGVkFHVesD7gQ4dUDCA4&uact=5&oq=how+to+interface+with+a+relational+database+management+system&gs_lcp=Cgdnd3Mtd2l6EAMyCAgAEB4QDxAWOgcIABBHELADOgQIIRAKOgUIABCRAjoRCC4QgAQQsQMQgwEQxwEQ0QM6CwgAEIAEELEDEIMBOggILhCxAxCDAToFCAAQgAQ6CAgAELEDEIMBOggILhCABBCxAzoICAAQgAQQsQM6BQgAEIYDOgYIABAeEBY6BwghEKABEAo6BQghEKABOgUIIRCrAjoICCEQHhAWEB06CgghEB4QDxAWEB1KBAhBGABKBAhGGABQxgdYoUNgsURoBXABeAGAAY8BiAGeMJIBBTU1LjE2mAEAoAEByAEIwAEB&sclient=gws-wiz

There are dozens, if not hundreds of ways to achieve what you're asking. It depends on your specific use-case.

[paper flower]

If your web application interacts with database - yes

In most cases you don't expose your sql database directly, but do that via API

[copper pilot]

I guess I misunderstood.

To clarify - user makes HTTP request to the application, the application makes a SQL request to the DBMS, the DBMS responds to the SQL request, the Application parses the response and displayes it on the page.

[paper flower]

Yep, but you'd be dealing with API's in most cases nowadays 🙂

[copper pilot]

And this is all stuff you mostly have to code yourself, which is why it's imperitive you know how the entire communication chain functions.

It's not just user makes HTTP request, data comes back.. you have to tell the application what to do with the request and how to translate it to the language your DBMS understands.. and vice versa so the webpage shows the data in a way you want.

[paper flower]

From outside perspective it's just an http call, you don't care about internals

[copper pilot]

Right. Though I can't tell what perspective is being asked. I assume from a code perspective considdering we're in a python channel lol

[copper pilot]

And this again depends on use-case BTW...

You don't want to write a web-app that entirely depends on the API's that other people manage. That would render your app useless at the whims of those APIs. In almost every case, you want to keep remote API communications separate from your app, loading that data into a database under your control, so your app doesn't shut down if a remote API ceases to function.

[paper flower]

Though a lot of apps do that

There's nothing wrong with it

[copper pilot]

Meh, I guess difference of opinion. Personally, I'd never sign off and support that kind of deployment myself due to what I said above.

[paper flower]

Payment processing is a popular example

You really want to do that yourself? No

[copper pilot]

Good point, but that's a super specific use-case where the loss of a remote API renders the entire functionality broken anyhow.. with or without localized data

[paper flower]

Some apps just need access to 3rd party platforms because it's how they work

If i want to create an application that interacts with a different app i'd have to use their api

[copper pilot]

We're both right - it depends entirely on use-case.

[paper flower]

In some cases portion of my API might not work if their API is down, in some cases it could render it unusable

It depends

[copper pilot]

Which is why I mentioned use-case. We can't give a great answer to that question without more specifics. A generalized, broad answer is skipping over a ton of pertinent information.

Say I wrote a weather app that displays current and historical weather data cross-referenced with crop yields, both current and historical. Personally, I'd have all of that data pumped into a local database I manage so if the remote API's I'm pulling data from die I can still review historical data.

Creating that app by relying entirely on webapp -> remote API communications causes the entirety of the app to die if that API ceases to function for whatever reason.

[paper flower]

Makes sense, but your app would be partially unusable (no new date) if api you're using is down

[copper pilot]

50% is better than 0%

[paper flower]

I know 😉

[copper pilot]

Keep in mind my information is coming from the perspective of a 15+ year infrastructure architect/engineer, so my objective is uptime and functionality. May be different for a software dev, I dunno. But as the guy that has to find ways to keep things running at whatever cost, I'd entirely block a deployment that didn't consider these things sufficiently.

Mostly because I'd be the first one blamed if Billy-Bob's half-assed API died and our entire application depended on it for all functionality.

Hint Hint: This has happened to me.

[paper flower]

I completely agree with you on your example, some things are just hard/impossible to create on yourself, so you'd use a 3rd party api

If it's possible to pull data from it you just do that to achieve better reliability

If it's a payment processing there's nothing you can do if it's down

We needed access to currency rates in our microservices, we just wrote another service that would pull data from 3rd party api's into local db

[copper pilot]

Really glad I've never had to deal with payment processing, seems like a PITA, though I do understand the reliance on the API here.

In the dozen+ apps I've helped run/bring to market I've never allowed a remote API call go through unless it's been proven to me the data cannot be imported and used locally first.

[paper flower]

We've had a lot of external API calls since these were user actions 😵‍💫

So there's nothing you can import

[copper pilot]

Yikes... yeah, I've seen that trend growing more and more over the years. Honestly as soon as Kubernetes hit the market it's almost all remote API calls these days.

Really, really irks me. Thankfully I've moved into security/compliance and don't really deal with the architecture anymore. I'm just the guy that shows up to tell you your code's shit because it has 200 vulnerabilities.

It's exactly why I'm trying to teach myself how to properly dev code right now. Want to know why so many applications seem like 13th century plagues come to life.

[torn sphinx]

how can i add AUTOINCREMENT in aiosqlite?

[uneven stream]

is there a difference if I use TEXT instead of VARCHAR? I know what they are but just wondering if it really makes a difference

[fading patrol]

May depend on your data if it really matters that much, but generally TEXT is going to be less efficient if you don't need it, especially in terms of disk space

[uneven stream]

oh
thanks!

[latent crag]

What are the best things to use for databases

[copper pilot]

...what?

Probably a computer to start.

You could use a piece of paper too, but it may be slow to translate into code.

[latent crag]

You know like there is mysql mongodb and stuff, so which one is the best

[copper pilot]

There isn't one.

[latent crag]

What is a good one

[copper pilot]

All of them

I don't even think you know what you're asking

There are several different categories of databases - relational, time-series, hierarchical.. etc.

It's use-case specific.

[latent crag]

I want to store username and password which one do I use?

[copper pilot]

Microsoft Excel

Again - use-case specific

Username and password can be stored in all of those. It entirely depends on what your objective is and what you can support.

[latent crag]

It is for a website login

[copper pilot]

How familiar are you with writing code and working with databases?

[latent crag]

Not familiar with db

[copper pilot]

Use sqlite

It's relational and exists exclusively as a file on your local filesystem - no server or additional software required. However, it's very feature-light, insecure, and inefficient.

If you want something more powerful, MySQL, but it requires the management of the software it runs on.

[latent crag]

No I don't want it to be file based i want it to be hosted on a server somewhere, but not on my own computer

[copper pilot]

Yeah you may be in over your head here.

You could host on AWS/Azure/Google, but that'll again require some database work and management on your part.

[latent crag]

I have a discord bot hosted on AWS

[copper pilot]

I think you can get a free tier MySQL database running in AWS with a data cap of 5GB before being charged. However, with that comes the following requirements:

• Standing up the AWS account and RDBMS instance
• Creating your schema and necessary tables
• Creating and using a service account to access that data remotely
• Securing that RDBMS behind a security group so your app can access it without the world also being able to access it too
• Writing code that translates your application's input/output to SQL queries the RDBMS understands (and vice versa)
• Optimizing that data structure so your code doesn't get slower and slower as you add to it

@latent crag If you're this new to code i'd highly recommend running a local MySQL instance on the same host your Discord bot runs on. It simplifies the learning experience greatly.

[dusky lark]

[torpid slate]

is database related to data engineering? I'm new and I wanted to learn more

[prime hollow]

WHAT IS BETTER ?
11 columns and 10 rows OR 2 columns and 100 rows ??
(using sqlite)

[polar isle]

2 columns and 100 rows

[prime hollow]

i searched it on google and it said the max number of columns are 2000

but rows are unlimited

• does making more columns or rows like 10k rows make my pc or the bot crash?

[polar isle]

im not pro in this and cant explain it further but, just, no, you can have 50k rows and your PC won't crash, well unless it's a calculator working on sun batteries

[prime hollow]

so my 4 ram mf won't crash ?

good

[paper flower]

You should use your tables kind of like classes
You can have class person and multiple instances of them:

class Person:
    name: str
    age: int

It's far easier to manage compared to class with 1000 field, considering you can't add fields in the runtime

[prime hollow]

i use sqlite

what classes have to do with that

oooooo its an example

😮

[paper flower]

Classes are like tables, instances are like rows 🤔

You could think of them that way

[fading patrol]

Absolutely... Data Engineers are basically software engineers who specialize in moving data in and out of databases

[fading patrol]

Invite links are blocked here but there is a dedicated "Data Engineering" server

[analog sigil]

https://discord.gg/kkgNqre2

@fading patrol @torpid slate

[torn sphinx]

okay now i also have question how can i get list of all channels where user is registered?```sql
CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, uuid varchar(32) NOT NULL)
CREATE TABLE IF NOT EXISTS channels (id INTEGER PRIMARY KEY AUTOINCREMENT, channel_id TEXT NOT NULL)
CREATE TABLE IF NOT EXISTS linking (id INTEGER PRIMARY KEY AUTOINCREMENT, channel INTEGER NOT NULL, user INTEGER NOT NULL)

those are my current tables
so from table users i get id of the user in table linking i get list of all channel ids where it is used and in table channels i need to get channel_id from the id now how to do it best way

[pure frost]

just a question

why not use their discord id?

I guess its for discord?

instead of a custom user id?

all discord IDs are snowflakes

[torn sphinx]

only think that is from discord here is channel id

users arent actually discord users

[pure frost]

so linking.user is users.id?

[torn sphinx]

no its not discord related

but i have uuid value and i want to get list of all channels where its used

[torn sphinx]

yes

same for channel

[pure frost]

okay

and linking is where the user is "registered"?

[torn sphinx]

yeah thats how i called

[pure frost]

okay

SELECT
  DISTINCT(channel)
FROM
  linking
WHERE
  user=$1

You might not need DISTINCT, but since linking.channel isn't marked UNQIUE I'd add it

well, okay

you can'd make it unique by itself

you'd have to make the channel-user pair unique

[torn sphinx]

it cannot be unique

[pure frost]

CREATE TABLE IF NOT EXISTS linking (
  id INTEGER PRIMARY KEY AUTOINCREMENT, 
  channel INTEGER NOT NULL,
  user INTEGER NOT NULL,
  
  UNIQUE(channel, user)
)

this will work

not sure if its standard sql syntax

but if youre using postgres it works

ah I assume you are, since CREATE TABLE IF NOT EXISTS is a psql thing iirc

[torn sphinx]

its just called on startup

[pure frost]

if you change your table to my suggestion you can get rid of the distinct

[pure frost]

as in?

[torn sphinx]

?

[pure frost]

what's called on startup?

[torn sphinx]

those table creation lines

[pure frost]

yeah you should call it once on startup

[torn sphinx]

also its returning me nothing

[pure frost]

although if you already have the table rn, you'd need to either ALTER it or drop it so that the unique check is added

[torn sphinx]

i know i need to use join but dont know how

[pure frost]

wait

aha

okay now i get what u want

okay

• uuid => users.id => channel.id's => channel.channel_id's

so

[torn sphinx]

yeah

[pure frost]

WITH user AS (
  SELECT id FROM users WHERE uuid=$1
), user_linked_channels AS (
  SELECT channel FROM linking JOIN ON user=user.id
)
SELECT channel_id FROM channels JOIN ON id=user_linked_channels.id

try this

from the top off my head

ah well

what are you using? mysql?

psql?

sqlite?

[torn sphinx]

sqlite

[pure frost]

ah

[torn sphinx]

and it says syntax error

[pure frost]

where

[pure frost]

could u give me an example table

i mean, values

[torn sphinx]

can be screenshots?

or i can just copy it okay

channels
1 1003986268809601045

users
1 6500f9522334438080e6e5f39c73c774
2 58a3da4342a44c769616c3c9d4420a70

linking
1 1 2

[pure frost]

ty

CREATE TABLE  users (
  id SERIAL PRIMARY KEY, 
  uuid varchar(32) NOT NULL
);
CREATE TABLE  channels (
  id SERIAL PRIMARY KEY, 
  channel_id TEXT NOT NULL
);
CREATE TABLE  linking (
  id SERIAL PRIMARY KEY, 
  channel_id BIGINT NOT NULL,  
  user_id BIGINT NOT NULL
);


WITH usr AS (
  SELECT id FROM users WHERE id=1 /* change this to uuid= */
), user_linked_channels AS (
  SELECT channel_id FROM linking JOIN usr ON user_id=usr.id
)
SELECT channels.channel_id FROM channels JOIN user_linked_channels ON id=user_linked_channels.channel_id

I had to change a few column names because i couldn't get it to work

for me it hasn't returned any results, but try it I guess

[torn sphinx]

i think it should be where uuid=""

[pure frost]

yeah

I just tried it like his

[torn sphinx]

changed everything and dont know what to do with it

okay i just needed to put channel instead of channel_id

okay looks like it is working ill try to add more users to same channel and see

[pure frost]

WITH usr_with_uuid AS (
  SELECT id FROM users WHERE id=1
), user_linked_channels AS (
  SELECT linking.channel_id FROM linking JOIN usr ON linking.user_id=usr_with_uuid.id
)
SELECT channels.channel_id FROM channels JOIN user_linked_channels ON channels.id=user_linked_channels.channel_id

Cleared it up a bit more

well "cleared", made it more explicit

wait

[torn sphinx]

yep works

thank you

[pure frost]

WITH usr_with_uuid AS (
  SELECT id FROM users WHERE id=1
), user_linked_channels AS (
  SELECT a.channel_id FROM linking a JOIN usr_with_uuid b ON a.user_id=b.id
)
SELECT a.channel_id FROM channels a JOIN user_linked_channels b ON a.id=b.channel_id

I guess this should be more readable

[torn sphinx]

its fine

[pure frost]

I'm glad I could help you

I have an issue too

[torn sphinx]

im glad you wanted to i would never created it myself

[torn sphinx]

?

[pure frost]

hey, is there a way to select values by a key and group them?
like, lets say:

/* psql */
CREATE TABLE users (
  id SERIAL PRIMARY KEY
);
CREATE TABLE items (
  id SERIAL PRIMARY KEY
);
CREATE TABLE inventory (
  id SERIAL PRIMARY KEY,
  item_id BIGINT NOT NULL, /* items.id */
  user_id BIGINT NOT NULL /* users.id */
);

now, I'd like the output to be somehow like:

owner_user_id | items
1             | 1, 2, 3, 4
2             | 5, 6

values:

INSERT INTO users(id) VALUES (1), (2);
INSERT INTO items(id) VALUES (1), (2);
INSERT INTO inventory(item_id, user_id) VALUES (1, 1), (2, 2), (2, 1), (1, 2);

That is, somehow return an array of item_id's each user has

[torn sphinx]

im not really into sql

[pure frost]

well lets hope someone can help

[pure frost]

Okay it clicked.. SELECT user_id, ARRAY_AGG(item_id) FROM inventory WHERE user_id=ANY('{1, 2}'::bigint[]) GROUP BY user_id

neaaaat

[prime hollow]

how to get the column name in sqlite3?

here is my table :
id | sword |shield
12| 2 | 1
i want to send the user an embed containing :
sword : 2
shield : 1
i know already how to get the numbers of each item but i don't know how to get the column names (sword/shield) any help?

iam using aiosqlite python library btw

[ebon seal]

1      1
2      2
3      3
4      4
5      6
6```

and i want to be like this

1      1
2      2
3      3
4      4
5      NaN
6      6```

[marble igloo]

    @commands.command()
    async def addname(self, ctx, name):
        
        x = ctx.author
        y = name

        e.execute("INSERT INTO names VALUES (x, y)")
        conn.commit()

        await ctx.send("Registered")```

I'm using sqlite3

import sqlite3
conn = sqlite3.connect('data.db')
e = conn.cursor()```Those are the vars

I'm trying to store the ctx.author of the addname command along with the username he inputs after the command

@icy oyster

[icy oyster]

lets see 🙂

so first you should create a table

[marble igloo]

I already have

[icy oyster]

if you did, it should be names

ok

[marble igloo]

#     user,
#     name
#     )""")```

[icy oyster]

okay

so I would do something like

c.execute("""CREATE TABLE names (
     user TEXT,
     name TEXT
     )""")

[marble igloo]

user will be ctx.author.id

int

[icy oyster]

you want it as an int? then you can use INT, yeah

[marble igloo]

yeah

[icy oyster]

ok so continuing

[marble igloo]

ight I'll delete the existing db

alright so that's good

[icy oyster]

e.execute('''INSERT INTO YourTable
  (user, name)
  VALUES (?, ?);''',
  (ctx.user.id, ctx.user.name))
  conn.commit()

Try that ^^ 🙂

[marble igloo]

    load()
  File "C:\Users\llVll\Desktop\Projects\Lunar Vigil Bot\main.py", line 15, in load      
    bot.load_extension(f'cogs.{filename[:-3]}')
  File "C:\Users\llVll\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 678, in load_extension
    self._load_from_module_spec(spec, name)
  File "C:\Users\llVll\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\bot.py", line 606, in _load_from_module_spec
    spec.loader.exec_module(lib)
  File "<frozen importlib._bootstrap_external>", line 790, in exec_module
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "C:\Users\llVll\Desktop\Projects\Lunar Vigil Bot\cogs\utils.py", line 13, in <module>
    import youtube_dl
  File "C:\Users\llVll\AppData\Local\Programs\Python\Python39\lib\site-packages\youtube_dl\__init__.py", line 43, in <module>
    from .extractor import gen_extractors, list_extractors
  File "C:\Users\llVll\AppData\Local\Programs\Python\Python39\lib\site-packages\youtube_dl\extractor\__init__.py", line 9, in <module>
    from .extractors import *
  File "C:\Users\llVll\AppData\Local\Programs\Python\Python39\lib\site-packages\youtube_dl\extractor\extractors.py", line 601, in <module>
    from .line import (
  File "<frozen importlib._bootstrap>", line 1007, in _find_and_load
PS C:\Users\llVll\Desktop\Projects\Lunar Vigil Bot> py main.py
Ready
Ignoring exception in command addname:
Traceback (most recent call last):
  File "C:\Users\llVll\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\llVll\Desktop\Projects\Lunar Vigil Bot\cogs\utils.py", line 95, in addname
    e.execute('''INSERT INTO names
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

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

    @commands.command()
    async def addname(self, ctx, name):
        
        x = ctx.author
        y = name

        e.execute('''INSERT INTO names
        (user, name)
        VALUES (?, ?);''',
        (x, y))
        conn.commit()

        await ctx.send("Registered")```

@icy oyster

[ebon seal]

anyone want to help me?

[marble igloo]

acc I figured it out

@icy oyster

    @commands.command()
    async def name(self, ctx, name: discord.Member):
        
        id = name.id

        e.execute("SELECT name FROM names WHERE user=id")```How do I make it so it says where user= the variable id

WHERE user=

[icy oyster]

sorry?

[marble igloo]

then the variable id

[icy oyster]

could you repeat?

[marble igloo]

I want it to select from names

where

[icy oyster]

ahh yeah

[marble igloo]

user is equal

to the id

[icy oyster]

thats okay

[marble igloo]

variable

[icy oyster]

WHERE is a thing in sqlite, yeah

[marble igloo]

yeah

[icy oyster]

give it a try 🙂

[marble igloo]

    @commands.command()
    async def name(self, ctx, name: discord.Member):
        
        id = name.id

        e.execute("SELECT name FROM names WHERE user=id")```

ye but how do I make user

== to id

i don't get it

@icy oyster

[icy oyster]

1 sec

curu.execute('''DELETE FROM userconfig WHERE user_id = ? AND symbol = ? AND timeframe = ?;''', (json_data["user_id"], json_data["symbol"], json_data["timeframe"],))

this is what I have in my code

want me to code it for you or you wanna test it yourself?

[marble igloo]

that's...

not what I want

also is there a way to make it so people can't duplicate inputs into the db

like make multiple rows with the same id for the name

[icy oyster]

yeah, you return a set and iterate over it, if the id matches one in the set you deny the append

[marble igloo]

alright

soooo

[icy oyster]

what do you want 🤷‍♂️

[marble igloo]

    @commands.command()
    async def name(self, ctx, name: discord.Member):
        
        id = name.id

        e.execute("SELECT name FROM names WHERE user=id")```this

all I want bro

is WHERE

user

is equal

to

ID

THE VARIABLE

[icy oyster]

well, change the delete with the select

🤷‍♂️

[marble igloo]

I don't understand

what u mean

I don't see a delete

@icy oyster

 @commands.command()
    async def name(self, ctx, name: discord.Member):
        
        id = name.id

        e.execute("""SELECT name FROM names WHERE user= ?;""", (id))
``` @icy oyster I did this

Traceback (most recent call last):
  File "C:\Users\llVll\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\llVll\Desktop\Projects\Lunar Vigil Bot\cogs\utils.py", line 108, in name
    e.execute("""SELECT name FROM names WHERE user= ?;""", id)
ValueError: parameters are of unsupported type``` @icy oyster

[icy oyster]

dont mass ping please

thanks 🙂

[marble igloo]

alright

well

can u

see what I did wrong

🤦‍♂️

[brazen charm]

your () aren't a tuple, it needs a comma inside of it. otherwise it'll just be de-sugared to id not (id,)

[marble igloo]

so what does that mean

[brazen charm]

comma* not command sorry

[marble igloo]

oh

where

[brazen charm]

otherwise it'll just be de-sugared to id not (id,)

[marble igloo]

where does the comma go

[icy oyster]

he/she said it in the message up there 🙂

[marble igloo]

got it

ight everything is sorted

ty

everyone

[rocky arch]

Hey Guys,

i use mysql with python. I have a Discord Bot and connect to my database once the Bot starts (So I can access it from anywhere). But after a while (like a day) the database will disconnect from the Bot and I have to reconnect the database.

I have a function and inside this function I try to reconnect to my bot.

def openconnection(): #DOES NOT WORK
           try:
               db = mysql.connector.connect(
               host = "...",
               user="...",
               password="...",
               database ="..."
               )
               cursor = db.cursor()
               return db #???
           except:
               print("!!!CANT REACH DATABASE!!!")
               return 0

But it does not work. I can access the database while I am inside this function, but once I exit it, it does not work anymore. (Which makes sense) How can I return the "db" correctly?

[torn sphinx]

whats the best orm for discord bots and
to find out if a row exists in sqlalchemy do you use len() or opencv count()

[marble igloo]

    @commands.command()
    async def deletename(self, ctx, name):
        
        x = ctx.author.id
        y = name

        e.execute("""DELETE name = ? FROM names WHERE user = ?;""", y, x)

        conn.commit()

        await ctx.send("Registered")```How do I do this?

[fading patrol]

SQL Alchemy is good, and check this:

https://stackoverflow.com/questions/7646173/sqlalchemy-exists-for-query/13336408#13336408

[torn sphinx]

ok

[quasi grove]

Hello everybody, i am making a word autofill fill program using python GUI docxtpl. But what I want to do is i want the GUI text boxes to have a drop down menu where i can select what I want to enter so it can be loaded into the place holders in the word document. My current database is on MySQL or Excel so I want to link the drop down options to either one of them. I tried linking this using tkinter but didn't reach anywhere. I tried recoding it through GUI.

Is there a way where i can add the drop down menu based on the information from the excel sheet or MySQL database

[acoustic cave]

Hey how do I delete a table row with all of it's foreign key constraints ?

[slim trench]

Hi, I want a column of items with list type(in Postgres with sqlaclhemy).
like this:

  [
        {
          "id": 0,
          "url": "hire-us",
          "title": "HIRE US",
          "items": None
        }
        ],

I wrote this(above)
When I tried to add these data I got this
What could be wrong? thanks.

[slim trench]

like this [{}, {}]

[paper flower]

But it wouldn't be a list of strings

[slim trench]

what do you mean?

[paper flower]

You declared a column to be Array(String), {} is not a string

[slim trench]

Ohh then what should I do?

just say Dict

instead of String?

[paper flower]

You could use built-in json type

or use relationships

[slim trench]

what kind of relationships?

[slim trench]

How can I achieve that?

can you explain this?

[delicate field]

Hey @hardy cedar!

It looks like you tried to attach file type(s) that we do not allow (.pdf). We currently allow the following file types: .gif, .jpg, .jpeg, .mov, .mp4, .mpg, .png, .mp3, .wav, .ogg, .webm, .webp, .flac, .m4a, .csv, .json.

Feel free to ask in #community-meta if you think this is a mistake.

[hardy cedar]

Need help to engineer a database schema. Right now i have this one but i treat with some scepticism for this kind of schema. Also i need to add constraint (one ConcretePortfolio row for one employee_id) for ConcretePortfolio tables can i do it without adding one to one reference ConcretePortfolio .employee_id - Employee.id

[rigid mica]

Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
postgresql is already the newest version (13+225).
yThe following package was automatically installed and is no longer required:
  libfuse2
Use 'sudo apt autoremove' to remove it.
Suggested packages:
  postgresql-doc-13
The following NEW packages will be installed:
  libpq-dev postgresql-client
0 upgraded, 2 newly installed, 0 to remove and 136 not upgraded.
Need to get 192 kB of archives.
After this operation, 539 kB of additional disk space will be used.
Do you want to continue? [Y/n] y
Abort.

any idea why it aborts?

[rigid mica]

could anyone check #help-croissant

[fading patrol]

Looks like you got past the install, but can't connect to remote DB? 1) Make sure you have right server details (IP, port, username, pw, database name). 2) use nmap to confirm connectivity to that port at that IP

If that all checks out, I'm probably not the one to help you, but those are the basics I would start with

[rigid mica]

I dont know how to get those

the IP and port

[fading patrol]

Then you have no business connecting to the remote server? Whose is it?

[rigid mica]

its mine

lol

I dont know how to find the ip and port of the database

i'm hosting my database on my raspberrypi

I wanna connect to it with my pc

[fading patrol]

You can check the IP on your Pi itself or on your router whichever is easier. Port should be default if you didn't change it (lookup default port for Postgres, I don't remember)

Are you following a specific tutorial

?

[rigid mica]

I was yeah

https://dataslinger.medium.com/setting-up-remote-postgresql-on-raspberry-pi-f9058b415768

this one

but I couldnt understand this step anymore

he does something with the ipv4 with 'trust'

I dont have that

[fading patrol]

It's telling you to add that line

[rigid mica]

I dont want the ip to be local

I wanna be able to access my db from all pc's

[fading patrol]

Like it says, you can put a whole CIDR block to allow your entire home network. If you need to access remotely I suggest you set up a VPN as the easiest way to do so securely

[delicate field]

Hey @tight junco!

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

[rigid mica]

even further, other houses

[tight junco]

ugh

[rigid mica]

its for an auth that I'm making and people need to be able to access it from different ip's

[tight junco]

i just switched from mysql to postgresql and got this hugeass error, how do i fix it?
https://paste.pythondiscord.com/epujalifom

[rigid mica]

add asyncio.set_event_loop_policy(asyncio.WindowsSelectorEventLoopPolicy())

[tight junco]

watt

whats tht

[rigid mica]

windows bug if im right

[tight junco]

oh

[rigid mica]

no idea, I had the same issue as u before and they gave me that

[tight junco]

can i remove it while hosting?

[rigid mica]

no idea either

[fading patrol]

You can allow those blocks one by one if you don't want to give them VPN access. Or is there any reason you're not doing this on the cloud? Heroku free tier Postgres is nice and easy

[rigid mica]

but it can be literally anyone from around the world

I just need to know the ip to access the database

[tight junco]

f

[fading patrol]

The private IP of your Pi? That's in the instructions

If you want to expose your Pi to the whole world, you can port forward, but make sure you understand the risk

[rigid mica]

they wont see the ip

its encrypted lol

[rigid mica]

isnt that the local one

[tight junco]

where am i supposed to add that at?

setup_hook?

[rigid mica]

at the top somewhere

nah just in the file

[tight junco]

right okay

ty

[rigid mica]

u got any more information?

I still dont know how to get it

the tutorial is only for ur home network

[fading patrol]

Get it working on your home network first. Then if you want to expose it to the world, allow all IPs and set up port forwarding. The details of port forwarding depends on your router

Again I would say if you have no good reason to host this on your Pi, don't. Use Heroku or something

[tight junco]

it's likely not that

TypeError: connect() got an unexpected keyword argument 'db'

[rigid mica]

dunno

[tight junco]

😔

[fading patrol]

Are you using SQL Alchemy? Did you see this? A few different suggestions there https://github.com/sqlalchemy/sqlalchemy/discussions/5994

[tight junco]

using postgresql

found the error, database is a kwarg and i used db because it was db in mysql

is running fine now, thanks

[fading patrol]

SQL Alchemy supports both MySQL and Postgres

[tight junco]

oop

[rigid mica]

I've tried, and I cant connect

asyncpg.exceptions._base.InterfaceError: pool is not initialized

please just tell me how to get the ip instead of telling me not to expose my ip to the world, i know what im doing

[fading patrol]

Follow the instructions you posted earlier or try the first answer in Google: https://raspberrytips.com/find-current-ip-raspberry-pi/

[rigid mica]

I tried this, and it didnt work

would this work?

    bot.db = await asyncpg.create_pool(database = "Bot",user = "bertie",password = "", host = " ip",port = "5432")

[rigid mica]

I managed to get in, asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "user"

    await db.execute('''CREATE TABLE IF NOT EXISTS licenses( license text, id bigint, user text, password text, days text, date text )''')

nothing wrong with this right??

[brazen charm]

cant call a column user

it's a reserved keyword iirc

[paper flower]

Wrap it in ""

create table if not exists licenses
(
    license text,
    id bigint,
    "user" text,
    password text,
    days text,
    date text
)

[torn sphinx]

soo i uploaded my bot on heroku, and it was working fine on when i was hosting it from my pc, but on heroku something went wrong with the db py discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ServerSelectionTimeoutError: cluster0-shard-00-02.j0dgg.mongodb.net:27017: connection closed,cluster0-shard-00-00.j0dgg.mongodb.net:27017: connection closed,cluster0-shard-00-01.j0dgg.mongodb.net:27017: connection closed, Timeout: 30s, Topology Description: <TopologyDescription id: 62ea8c7ca5e4e7d67f955073, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('cluster0-shard-00-00.j0dgg.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-00.j0dgg.mongodb.net:27017: connection closed')>, <ServerDescription ('cluster0-shard-00-01.j0dgg.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-01.j0dgg.mongodb.net:27017: connection closed')>, <ServerDescription ('cluster0-shard-00-02.j0dgg.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('cluster0-shard-00-02.j0dgg.mongodb.net:27017: connection closed')>]>

[torn sphinx]

how do i know that? 🤔

i mean probably not if idk..?

[tight junco]

how can i see postgresql tables in real time?

[torn sphinx]

is there an orm thats easy, good for discord bots and versatile

[fading patrol]

The best is definitely SQL Alchemy but if you want to try something simpler look at Peewee, should be good enough

[torn sphinx]

oh wow thats very very simple

tbh im sold if u can check if a row in a table exist

if im making a discord bot, should i create the engine when the bot is ready, and make a session in the command

[fading patrol]

Not real sure about that, but if there is any delay in creating the session you probably want to leave it open between commands

[torn sphinx]

what do you mean delay in creating the session

[torn sphinx]

.mappings() is awesome

[fading patrol]

If it's slow

[torn sphinx]

what are the fields in peewee

is there one for integers (or anything that can hold a discord id)

oh thats alot

[fading patrol]

I didn't count but at least 20 http://docs.peewee-orm.com/en/latest/peewee/models.html?highlight=table generation

[torn sphinx]

uhuh

[hollow marten]

I don't know why I would be getting this error, since I checked all the types and they seem to match.

await cursor.execute(  # Insert into transactions database
        """INSERT INTO buy_orders (
            OrderAuthor,
            BuyingOrg,
            BuyingItem,
            BuyingQuantity,
            CompensationItem,
            CompensationQuantity
            )
            VALUES
            (?,?,?,?,?,?)
            """,
            (ctx.author,
            org,
            item_to_buy,
            buying_quantity,
            item_to_pay,
            payment_quantity))

sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

Here's the table:

await cursor.execute(
                """CREATE TABLE buy_orders (
                                    OrderID INTEGER PRIMARY KEY, 
                                    OrderTime DATETIME DEFAULT CURRENT_TIMESTAMP, 
                                    OrderAuthor TEXT, 
                                    BuyingOrg TEXT, 
                                    BuyingItem TEXT, 
                                    BuyingQuantity INTEGER, 
                                    CompensationItem TEXT, 
                                    CompensationQuantity INTEGER);
                                    """

[red oasis]

https://hastebin.com/yerafaciri.rust - error

whitelisted = fields.ManyToManyField("Bot.UserModel", on_delete=fields.SET_NULL, null=True, related_name="whitelisted")
vanity_whitelisted = fields.ManyToManyField("Bot.UserModel", on_delete=fields.SET_NULL, null=True, related_name="vanity_whitelisted")

That error is coming from those 2 lines...

[gusty yarrow]

Why is it not recommended to use a JSON file as a database for a more serious project?

[ivory turtle]

because you can't edit parts of a json file, you can only serialize and deserialize entire objects

so it's extremely inefficient to load the object into memory, edit it a little, and write the entire thing back into the file

[untold dust]

is it possible to use fuzzy matching in aioredis?

[torn sphinx]

can someone help me with collection.update_many() function?

i can't figure out how it works

[fading patrol]

Seems like a straightforward Google question... If you're not finding anything the answer is almost certainly no

[torn sphinx]

does it update multiple posts or multiple fields in one post?

[fading patrol]

What is this, mongo?

[torn sphinx]

yess im using pymongo

[fading patrol]

There are examples here, the syntax will be slightly different for pymongo but it's worth understanding how the DB itself works https://www.mongodb.com/docs/manual/reference/method/db.collection.updateMany/

[sinful saffron]

When using mongo if I have to insert records in a list is it better to do it in a for loop and inserting one by one or to use insert many

I have to save thousands of them

btw what I have to insert is data taken from objects, so idk how would I do that with insert many

like logs.created_at, logs.user, etc

[torn sphinx]

how'd i know how many posts there are in my collection?

[untold dust]

redis syntax can do something like this GET %item% but i don't think its supported in aioredis

[fading patrol]

Just had a quick look at the docs... Maybe .iter() would work?

[fading patrol]

Probably depends on the details of your use case, I've done both but not sure what's best practice

[sinful saffron]

when I get home i'll send the function

[tight junco]

can i make a postgresql table without a primary key?

[harsh pulsar]

yes, and "postgres table without a primary key" turns up a lot of results in duckduckgo 🙂

[tight junco]

thanks

[slim trench]

How can I store dictionaries in one list, in sqlalchemy ?
like this:

[{}, {}, {}]

Thanks

[harsh pulsar]

in general there is no such things as storing "lists" and "dictionaries" in most databases. presumably you are using a database that supports json, so you really want to be asking "how do i store [{}, {}, {}] as json, using sqlalchemy"

[slim trench]

Yes