databases

[dense belfry]

use a backslash

[cedar trellis]

create table coep (
student_id int,
f_name varchar(50),
branch varchar(40),
city varchar(20)
);
select * from coep;  # to view the table with all data

#add the values in table using insert into
insert into coep (student_id,f_name,branch,city)
values(1,'Ram','cs','Nashik')

#add multiple records
insert into coep (student_id,f_name,branch,city)
values
(2,'Shyam','mech','pune'),
(3,'gita','civil','satara'),
(4,'laxmi','cs','mumbai'),
(5,'karan','mech','solapur')

select * from coep # all records
select f_name from coep # to show only f_name
select f_name,city from coep # to show only f_name and city

#insert into without column name and with value
insert into coep
values(6,'varun','entc','chakan')

#it will throw an error
insert into coep 
values(7,'rohit','entc')

#city will be null
insert into coep (student_id,f_name,branch)
values(7,'rohit','entc')

#where
select * from coep
where branch='entc'

select f_name,branch from coep
where branch='entc'    
``` whats wrong in this guys

[proven arrow]

Syntax looks fine, what makes you say it’s wrong?

[cedar trellis]

its showing error while executing

[proven arrow]

Well yeah your insert statement will throw an error and you already added a comment that says this so you should know to remove that.

[cedar trellis]

still same

[proven arrow]

Then show the error

[proven arrow]

Why do you have random numbers between lines?

Clearly what you sent is different from what your entering. You should be able to see that? Not sure what to say

[proven arrow]

Error says database already exists

[cedar trellis]

i will create a new data base and show it you still same error wait

[proven arrow]

Let me just show you it works, https://www.db-fiddle.com/f/dkUCUjdS2qjUBkqLHQhma3/0

[cedar trellis]

ya its running now

[cedar trellis]

done bro got the error thanks

[mortal light]

I'm looking to add database to a project. I already have a windows ec2 instance that's running FTPS server on it.
I learned mongodb last year for couple of days, so I'm pretty much new to databases
We expect 5,000 writes and 60,000 to 120,000 reads a day

Are there any recommendations which database to start with and things to watch out for. Happy to go through any articles if someone can link me to them. Also are reads cheaper than writes?

[cedar cargo]

data = sqlite3.connect("../FILES/database.db")
is it correct?

[proven arrow]

If the file exists at that path then yes 👍🏻

[proven arrow]

What’s the project? When you say database what kind of database are you looking at? Relational or not relational

[cedar cargo]

the problem is that the file exist but python give this error: sqlite3.OperationalError: unable to open database file

[proven arrow]

Because it can’t find it at that location 🤷‍♂️

[cedar cargo]

where should i put it?

[proven arrow]

Wherever you want? Just make sure the path is correct

[cedar cargo]

ok thank you

[eager igloo]

is there a way to find out if a deleted invite was deleted due to it expiring via max_age

wrong channel

[mortal light]

Project currently makes request to API.

Data (3 columns) for now will be stored in excel - But I am looking to instead store it in the database for 24 hours and then we read from this database each hour for the next 24 hours.

After 24 hours program will drop the table, make 5k request to API and then store them in the database.

Relational and non-relational doesnt matter. But below are somethings I'm looking for:

1. Database that other companies also use.
2. Cheap
3. Good Documentation
4. Available on aws
5. Optional - easy to set up.

[proven arrow]

What kind of data is it? Logging data or what?

[mortal light]

brand name, sku, and part number

[proven arrow]

Do you query the data for analytics or anything whilst its stored? And what are future plans?

It doesn't look like you are doing much by storing three columns. And the points you list, can be fulfilled by nearly all the mainstream databases.

General advice is to pick something that you can always build on if your project evolves.

[mortal light]

There's no analytics involved. We dont want to exhaust the API limit. So I figuered, it could be stored in a database and then read from it.

[mortal light]

What are some mainstream databases, SQL and PostgreSQL? Any other databases?

[proven arrow]

Mysql, postgres, mongodb

And some others but you get the idea

[mortal light]

Is mongoDB mainstream?

[proven arrow]

Its commonly used as a nosql solution, and in industry.

What your attempting, can be done in any kind of data store. Even somehting like elasticsearch would work.

Not sure how mongodb does pricing or if its free, but you might want to take that into consideration if you were to pick it.
Personally i would go with something like mysql/postgres. Its easy enough to setup for what you need. Support and documentation is widely available. It can more than handle what your requirements are, and filtering through the data would be easy enough to do as well.

[mortal light]

Yeah, I have heard good things about postgres and that their documentation is good.
Does postgres integrate well with Python?

[proven arrow]

Yes it does

[mortal light]

And also are reads cheaper than writes?

[proven arrow]

Depends on your structure.

Writes start to become expensive when you have a lot of indexes as the indexes needs to be reupdated. But indexes make reading faster.

You normally would decide what is more important in your application, between Reading or writing and then find a balance.

[mortal light]

So if you have to do updates several minutes later and you have to traverse from index 0 to index 2,000 and then 5,000 that starts to get expensive?

[cedar cargo]

what is the column type i have to use to insert a discord guild id?

[mortal light]

ok so based on this:
https://aws.amazon.com/rds/postgresql/pricing/
It would cost 0.018 per hour for a micro instance(I think this would be fine). This comes out to $12.96 a month.

But the cost could vary depending on reads and writes?

Or would it be fixed?

I mean of course, using more space would cost more.

[proven arrow]

Not sure what you mean but at 5000 rows thats literally no work for a database.
I would not worry about that so much. Just make sure to follow the basic rules, and the database query planner and optimizer will pick the most appropriate plan whilst searching.
Im currently on a project for a client which has around 400+ tables. The site is for multiple markets, with many millions of reads per day, and probably a million write per day. We use mysql, elasticsearch, redis, mongo (i think for some parts). And some of these tables are fu***d with the indexes and stuff, but its still fine and pretty quick. And a database can handle way more than these numbers too.

[proven arrow]

Not sure how aws pricing works, but i need to go now, so good luck in your project.

[mortal light]

Hey thanks for all your help!!!

[cedar cargo]

what is the column type i have to use to insert a discord guild id?

[crisp spade]

How do you construct a database, and store everyone’s data on it, and then people can load their progress in a python game from their computers while the database is on mine?

so in other words, how do i program it so that people can load their progress from my database once they run the python program?

[cedar cargo]

with the library sqlite3

!d sqlite3

[delicate field]

sqlite3

Source code: Lib/sqlite3/

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

The sqlite3 module was written by Gerhard Häring. It provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249.

To use the module, you must first create a Connection object that represents the database. Here the data will be stored in the example.db file:

[crisp spade]

Yeah but how do I load it from an external computer

like load data

[cedar cargo]

you will need to host your game

for example you can host the game with heroku

then every times you modify the database with your game it will modify the database on the github repository

[crisp spade]

Ah ok

[cedar cargo]

now can someone help me?

[torn sphinx]

how do i learn

sqlite

3

[proven arrow]

Integer

@cedar cargo

[cedar cargo]

Ok thank you @proven arrow

[torn sphinx]

im not sure if this is in the theme of this chanel (if not i will post in the chanel it is the theme), i am working on a programm that gives you chanel stats that all fine but i need a bit help with the api, i am using googles youtube data api v3 and i cant get the stats of some chanels and i have no clue why or how i can fix it maybe someone can help me, in a help chanel i opened no one awnsered :sad: i rly hope someone can help me

like this should be the response if i enter a valid youtube user name: html { "kind": "youtube#channelListResponse", "etag": "R_zl83uUDFpjvbcf27L54MfmRr8", "pageInfo": { "totalResults": 1, "resultsPerPage": 5 }, "items": [ { "kind": "youtube#channel", "etag": "SZX06KQUwMK0TTjAKww39NaJoj0", "id": "UC-lHJZR3Gqxm24_Vd_AJ5Yw", "statistics": { "viewCount": "27467796238", "subscriberCount": "110000000", "hiddenSubscriberCount": false, "videoCount": "4358" } } ] }

but for most channels (my channel including) im geting this response: html { "kind": "youtube#channelListResponse", "etag": "RuuXzTIr0OoDqI4S0RU6n4FqKEM", "pageInfo": { "totalResults": 0, "resultsPerPage": 5 } }

to check if it works i used this api request (i used like i said the youtube data api v3): www.googleapis.com/youtube/v3/channels?part=statistics&key=<api key here>&forUsername=<username here>

[proven arrow]

@torn sphinx Thats a youtube api issue. Make sure your making the correct request

[torn sphinx]

like i said i used this: www.googleapis.com/youtube/v3/channels?part=statistics&key=<api key here>&forUsername=<username here> to check bc some chanells are working and they are working in my code too

[proven arrow]

@torn sphinx Well its not a database issue, and the response shows "totalResults": 0, so channel cant be found. Maybe invalid request body

[raw blade]

is there an async equivalent to mongomock that's well maintained? pytest-async-mongodb looks a little suspect...

[torn sphinx]

hm... i put my youtube chanel name in there

btw to that point its the first time im working with apis do i have to put my name in there like that: Defence Terrial or like that: defence terrial or other?

[outer crypt]

if I want to build a notification system where a user can subscribe to a channel from a newsfeed, is using a database the best approach for this? Like have a table where its a mapping from users to channel id values which they subscribe too (and vice versa) and another table where its a mapping of id values to channels (same for users) ?

i was just concerned since this idea doesn't seem too scalable to me since the tables are basically constantly growing?

[proven arrow]

Yeah that

[outer crypt]

my concern is theres always new channels the number of channels is always growing.

so for example if i do a table mapping of a user to id values of channels they subscribe to (each col id corresponds to a channel and has a boolean value) the number of columns is then always growing as well which doesn't seem very feasible

im not too familiar with table organization for the pub/sub model so i was wondering whats the best common practice for these things

[proven arrow]

You don’t need to have a column per channel. That’s a very messy way to do it.

[outer crypt]

yeah i agree that its a very messy approach, what would be a better way to do it?

i could also store it as a list in a single column but that also has a limited number of bytes and gets messy as well.

[proven arrow]

You want a many to many relation.
This involves 3 tables:
users - (id, name, email, …)
channels - (id, name, link, …)
user_channel - (id, user_id, channel_id)
You should look this relation type up for a better understanding/explanation.

The user_channel is where you store the mapping of which user is subscribed to which channel. And the user_id/channel_id are just foreign keys referencing the other tables.

[outer crypt]

ooooo 👍 got it yeah ill do a bit of reading on that first

thanks!

[plush sphinx]

Someone mentioned me? Twice?

[torn sphinx]

is anyone here familiar with mongodb, im running into an error starting my bot

[simple valley]

I'm trying to use sqlite3 to store data on members for my bot (this is my first ever attempt at using it, only just found out that python has a built-in database module) and when I try to run this code to make a table on my users and another table storing my member's purchased colors I get an error saying cursor.execute(makeColorTable) sqlite3.OperationalError: near "FOREIGN": syntax error. Any ideas?

Here is my code for my database:

cursor = connection.cursor()
makeUserTable = """CREATE TABLE IF NOT EXISTS
tblUser(userId INTEGER PRIMARY KEY, balancePrivate INTEGER, embedColor INTEGER, holiness INTEGER, level INTEGER, lifetimeHoliness INTEGER, messagesSent INTEGER, nextLevel INTEGER, timeInVc INTEGER, vcJoinTime REAL, wordsSent INTEGER)"""
cursor.execute(makeUserTable)
makeColorTable = """CREATE TABLE IF NOT EXISTS
tblColor(FOREIGN KEY(userId) REFERENCES tblUser(userId), color INTEGER)"""
cursor.execute(makeColorTable)
cursor.execute("INSERT INTO tblUser VALUES(414181111737090048, 1, 16777215, 0, 0, 0, 0, 100, 0, 0, 0)")
cursor.execute("INSERT INTO tblColor VALUES(414181111737090048, 16777215)")
cursor.execute("SELECT * FROM tblUser")
cursor.execute("SELECT * FROM tblColor")```

[grim vault]

It's

CREATE TABLE IF NOT EXISTS tblColor(
  userId INTEGER REFERENCES tblUser(userId),
  color INTEGER
)```
or
```sql
CREATE TABLE IF NOT EXISTS tblColor(
  userId INTEGER,
  color INTEGER,
  FOREIGN KEY(userId) REFERENCES tblUser(userId),
)

[simple valley]

Thanks a bunch, I did this and now it looks like once my program terminates, the database and it's tables also get deleted... I don't need to manually save to a database do I?

[grim vault]

Did you commit your changes? The database shouldn't get deleted.

[simple valley]

Sorry I'm very new, started all of this like 30 mins ago. What is committing?

[grim vault]

I guess you should do a SQL tutorial then. For now do a connection.commit() after you change something.

[simple valley]

Okay, do you recommend any tutorials? I came here from the 5 minute Kite video and thought "Well that's better than dictionaries inside dictionaries"

[grim vault]

Sorry, I don't know any.

[simple valley]

That's alright. Thank you so much for your help :)

[grim vault]

Take a look at the pins, there are some links.

[crude shard]

nvm just remembered

lol

[simple valley]

I'm watching Sentdex like I usually end up doing lol

[covert bane]

It's SQL but, if someone can tell me why this runs instantly

SELECT players.* from players join (SELECT complete_name FROM players GROUP BY complete_name having count(complete_name) > 1) b ON players.complete_name = b.complete_name;

But this takes forever

SELECT players.* from players join (SELECT complete_name FROM players GROUP BY complete_name having count(complete_name) > 1) b ON players.complete_name = b.complete_name ORDER BY players.complete_name ASC LIMIT 0, 1000;

@vernal spade

[vernal spade]

I think it may be due to the fact that you're ordering it

How many entries are there? Would you consider ordering them with python instead?

[covert bane]

200K. 16K in the query result

[vernal spade]

It's a MySQL DB, right?

[covert bane]

Yes

[vernal spade]

where is it hosted?

[covert bane]

local host

Right now I'm just running the query in the console

So not related to python

[vernal spade]

How much RAM is allocated to the process? That can sometimes have an impact

[covert bane]

I'm running it locally. I've 16 GB RAM. And at least 8 GB is free at all times

Everything else runs fine.

[grim vault]

Have you tried a different approach? like

SELECT players.*
  FROM players
 WHERE players.complete_name IN (SELECT complete_name
                                   FROM players
                                  GROUP BY complete_name
                                 HAVING COUNT(*) > 1)
 ORDER BY players.complete_name ASC LIMIT 0, 1000;

Does players.complete_name has an index?

[covert bane]

This takes forever even without the order by

[covert bane]

No. Should I add one?

I'll try adding index to players.complete_name

Haha. Adding an index solved it. Thanks.

Thanks @grim vault

[storm wind]

For folks who use MongoDB, what performs better for doing joins: Creating an aggregation in Mongo, or doing the join through a couple pymongo queries in python?

[torn sphinx]

Can someone help me with this error at db ?

[pale jay]

sqlite?

[torn sphinx]

yes

[pale jay]

https://stackoverflow.com/questions/3172929/operationalerror-database-is-locked/39449185

[torn sphinx]

i switch it, and i rewrite, the last one i don't know what does it mean

[pale jay]

@torn sphinx in your connection set a timeout

[torn sphinx]

how i do it ?

[pale jay]

sqlite3.connect(timeout=30)

[torn sphinx]

ok, and i should put it in the command file or in main file ?

[pale jay]

where you setup the connection

you connected it earlier right?

just add timeout=30 there

[torn sphinx]

Ok then thanks

@pale jay

[pale jay]

@torn sphinx where did you connect the db? like sqlite3.connect('sample.db')

[torn sphinx]

At the command file

[pale jay]

then set the timeout=30 inside there and remove this one

it should look like sqlite3.connect('sample.db', timeout=30)

[torn sphinx]

i have db = sqlite3.connect('main.sqlite', timeout=30)

it is okay ?

@pale jay

[pale jay]

yup

[torn sphinx]

and if i have more line like that "db = sqlite3.connect('main.sqlite')" i need to put timeout at all ?

or just at the first one ?

[thorn geode]

pstttt sqlite is blocking. Use aiosqlite or asqlite instead

[pale jay]

wait why do you need 2 connections?

[torn sphinx]

what is that ?

[thorn geode]

basically, discord.py is an asynchronous library

Sqlite3 is a synchronous library

You'll find that your bot will stop when you do stuff like committing your data

[torn sphinx]

no, i mean i have this in the code

[pale jay]

you don't need that

[thorn geode]

You can do it once

[pale jay]

you can just have one and use that

[thorn geode]

With a bot variable.

[torn sphinx]

kk

[pale jay]

but if this is what you are doing then you need to add timeout there too but yea as mesub said using async lib would be better

[torn sphinx]

And you mean that it will be better to change the data base?

[torn sphinx]

ok

[thorn geode]

Using sqlite is fine!

It's just the library

You don't need to change the actual database file at all

[torn sphinx]

Then?

[thorn geode]

!pypi aiosqlite

[delicate field]

aiosqlite v0.17.0

asyncio bridge to the standard sqlite3 module

[thorn geode]

Or https://github.com/Rapptz/asqlite

Up to you.

[torn sphinx]

What would you use for a leveling system ? @thorn geode

or is the same ?

[thorn geode]

All you'll need to do in your code (once you've downloaded it), is import it and then change anything that says sqlite to a(io)sqlite

[torn sphinx]

kk

[thorn geode]

I personally haven't built a levelling system, but if you're bot is small then sqlite should be fine

If you have a large bot (few hundred guilds or more) then I would consider PostgreSQL

[torn sphinx]

For the moment i have it just in one server

@pale jay

why i have this error?

[thorn geode]

ghostping?

[torn sphinx]

@thorn geode me

[torn sphinx]

.

[pale jay]

uh

just use aiosqlite or something

[thorn geode]

You're going to need to unlock it somehow

[torn sphinx]

ok

[thorn geode]

Stop the program and close your editor

[torn sphinx]

restart the app ?

[thorn geode]

and then open task manager and stop any python processes

no don't restart it yet

[torn sphinx]

kk

@thorn geode i stop it

now?

[thorn geode]

open task manager

[torn sphinx]

ok

And what i do now ? @thorn geode

[thorn geode]

assuming you clicked the more info button at the bottom, check to see if there's any Python instances.

If there are, end them.

[torn sphinx]

I end all processes of python

@thorn geode

[thorn geode]

ok, now reopen your editor and start your bot again.

[torn sphinx]

same thing @thorn geode

[thorn geode]

Oh wait you've got 2 connects don't you

remove one of them.

[thorn geode]

ok

[remote plinth]

code:

self.database_connection = self.loop.run_until_complete(sql.connect(**self.configurations))
self.cursor = self.loop.run_until_complete(self.database_connection.cursor())

error:

  File "...", line 30, in __init__
    self.cursor = self.loop.run_until_complete(self.database_connection.cursor())
  File "...", line 642, in run_until_complete
    return future.result()
  File "...", line 13, in send
    return self._coro.send(value)
AttributeError: '_asyncio.Future' object has no attribute 'send'

uhh

idk if its the right channel tho

but i have this error in aiomysql

[dusky valve]

https://stackoverflow.com/questions/68136596/how-to-write-to-a-sqlite3-file-in-github-from-heroku

[torn sphinx]

Hey, my friend is hosting my web server in python on his server (he uses the pterodactyl pannel to manage his server). I want to use the mysql databases hosted by its server but I cannot connect to it. I already try to regenerate the password and the user and the database is on the same machine. I first used mysql-cconnector then pymysql but I can never connect to it. Here is the code as well as the error:
(alse, I can use db with phpmyadmin)

import pymysql as mysql
db = mysql.connect(
    host="127.0.0.1",
    port=3306,
    user="userGenerated",
    password="passwordGenerated"
)

print("--------- connection ------------")```
```Traceback (most recent call last):
  File "/home/container/.local/lib/python3.8/site-packages/pymysql/connections.py", line 613, in connect
    sock = socket.create_connection(
  File "/usr/local/lib/python3.8/socket.py", line 808, in create_connection
    raise err
  File "/usr/local/lib/python3.8/socket.py", line 796, in create_connection
    sock.connect(sa)
ConnectionRefusedError: [Errno 111] Connection refused

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/container/main.py", line 3, in <module>
    db = mysql.connect(
  File "/home/container/.local/lib/python3.8/site-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/home/container/.local/lib/python3.8/site-packages/pymysql/connections.py", line 664, in connect
    raise exc
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '127.0.0.1' ([Errno 111] Connection refused)")```

[torn sphinx]

who’s good with sql for discord py purposes

[dense barn]

how do i search for a certain sentence in fethcall?

embed = msg.embeds
embed2 = msg.embeds[0].fields
pc = embed2[0].value
hollo = msg.embeds[0].description
image = embed[0].image.url
mbcolor = embed[0].color
hol = random.randint(20000,30000)
hol2 = random.randint(10000,20000)

cursor2.execute("SELECT pc FROM pokesetspc WHERE user_id = ?",(ctx.author.id,))
                    results = cursor2.fetchall()
                    if results is None:
                        print(results)
                        cursor2.execute("INSERT INTO pokesetspc(user_id,pc) VALUES(?,?)",(ctx.author.id,pc,))
                        cursor.execute("UPDATE pokesets SET balance = ? WHERE user_id = ?",(total,ctx.author.id))
                        embed2=discord.Embed(description="The Pokemon was successfully caught with a ![pokeball](https://cdn.discordapp.com/emojis/856592973348995132.webp?size=128 "pokeball")`PokeBall!`",color=embcolor)
                        embed2.set_author(name=f"Congratulations, {ctx.author.name}!",icon_url="https://cdn.discordapp.com/attachments/856373686809788430/856619128726224916/671848138935500836.png")
                        embed2.set_image(url=image)
                        embed2.add_field(name="Card Name:",value=pc)
                        embed2.set_footer(text="═══════||Balls||═══════\nPokeball: {:,} | Greatball: {:,}\nUltraball: {:,} | Masterball: {:,} \n\nYou recieved {:,} CardCoins for catching a Holo Pokecard!".format(pb2[0],gb[0],ub[0],mb[0],hol))
                        await msg.edit(embed=embed2)
                    if f"('{pc}',)," not in results:
                        print(results)
                        if "**Holo**" in hollo:
                            total = int(hol)+int(bal[0])
                            cursor2.execute("INSERT INTO pokesetspc(user_id,pc) VALUES(?,?)",(ctx.author.id,pc,))
                            cursor.execute("UPDATE pokesets SET balance = ? WHERE user_id = ?",(total,ctx.author.id))
                            embed2=discord.Embed(description="The Pokemon was successfully caught with a ![pokeball](https://cdn.discordapp.com/emojis/856592973348995132.webp?size=128 "pokeball")`PokeBall!`",color=embcolor)
                            embed2.set_author(name=f"Congratulations, {ctx.author.name}!",icon_url="https://cdn.discordapp.com/attachments/856373686809788430/856619128726224916/671848138935500836.png")
                            embed2.set_image(url=image)
                            embed2.add_field(name="Card Name:",value=pc)
                            embed2.set_footer(text="═══════||Balls||═══════\nPokeball: {:,} | Greatball: {:,}\nUltraball: {:,} | Masterball: {:,} \n\nYou recieved {:,} CardCoins for catching a Holo Pokecard!".format(pb2[0],gb[0],ub[0],mb[0],hol))
                            await msg.edit(embed=embed2)
``` this is what ive tried so far but wont work, no errors either

[latent wren]

Okay, so I have a very simple API that just takes an SQL string, runs the query on a sqlite database, then returns the data in JSON format (ignore my CORS rules). It works perfectly fine locally, but when I deploy it on DigitalOcean App Platform, every query just says the table or tables do not exist. I'm not sure if it's a docker thing or what

from flask import Flask, request
from flask_cors import CORS, cross_origin
import sqlite3
import json

app = Flask(__name__)
cors = CORS(app)
app.config['CORS_HEADER'] = 'Content-Type'

@app.route("/", methods=['GET'])
@cross_origin()
def index():
    return "You are not supposed to be here.", 200

@app.route("/api/new", methods=['POST'])
@cross_origin()
def post_query():
    a, b = run_query(request.json)
    print(request.json)
    return a, b

def run_query(data):
    con = sqlite3.connect('data.db')
    cur = con.cursor()
    results = []
    try:
        for row in cur.execute(data["query"]):
            results.append(row)
        json_obj = json.dumps(results)
        print(json_obj)
        return json_obj, 200
    except sqlite3.Error as e:
        return str(e), 500

Also ignore the security implications of allowing anyone to run a SQL query on a database, it's not actually important data. This is just one small part of a site meant to hopefully impress a recruiter

And if someone drops the tables, I can just reload the app

Anyway, the only real difference that I know of is that it runs inside docker on App Platform. But as far as I'm aware, filesystem access should be the same in a container. Also, I'm not getting any errors on the con = sqlite3.connect('data.db') line, so I'm assuming it's successfully opening the database file. I've copied and pasted queries that I've successfully run in the sqlite3 console into the webpage, and it still just says the tables don't exist.

[unkempt prism]

Use the full path to the data.db

When you run locally using the debug app it likely to have a different working directory to how it’s deployed for production.

[elder vessel]

hlo

how can i put JSON data in a table in sqlite3

i used this but this is giving error ```py
the_db = {"info": 'This is a starting default key:value pair'}
cur.execute(f"insert into database (user,db) values ({u},'{the_db}')")

[torn sphinx]

Hello

I’m using a sqlite 3 for per server prefix

I’m getting an error parameters are of unsupported type

[mild garnet]

what it means- db = SQLALCHEMY(), they say it is for initializing, but what that means? and why we need it?

[torn sphinx]

Hi here is my code

    db = sqlite3.connect("main.sqlite")
    cur = db.cursor()
    cur.execute("SELECT prefix FROM main WHERE guild_id = ?", (message.guild.id))
    prefix = cur.fetchone()
    if len(prefix):
        prefix = prefix[0]
    else:
        prefix = "!" #return default prefix if guild not saved in database.
    db.close()
    return prefix```

I’m getting error `parameters are of unsupported type`

Pls ping me if there is a solution

[proven arrow]

Swap value with this (message.guild.id,)

[torn sphinx]

@proven arrow what do u mean?

[austere portal]

How do I run a SQL search query?

So SELECT FROM tablename WHERE title='test' I dont want the query to return where the title is exactly test, i want it to return the rows where the column title contains the string "test"

[proven arrow]

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

[austere portal]

Thanks

[cold quail]

Im currently using aiomysql and trying to update multiple columns

UPDATE levels SET timeVal = %s and time = %s WHERE guild_id = %s and user_id = %s

But it only updates the timeVal and not time. Am i doing something wrong or is this a limitation of the library?

[grim vault]

You need a comma to make it a tuple: (message.guild.id,)

[torn sphinx]

Oh makes sense thx 🙏 @grim vault

[austere portal]

with conn.cursor() as cursor:
    cursor.execute("SELECT * FROM Post WHERE title LIKE '%%s%'", "test")
    print(cursor.fetchall())``````shell
ValueError: unsupported format character ''' (0x27) at index 41```

[grim vault]

with conn.cursor() as cursor:
    cursor.execute("SELECT * FROM Post WHERE title LIKE %s", ("%test%",))
    print(cursor.fetchall())```

[supple seal]

Hello im tryna make a bot that has a point system

or counts and tallies points

if there is a guide ping

and someone said to come here for a database thing

so yes

[wet mural]

did you choose a database type for your project?

like sql / nosql

[remote plinth]

i've an error when defining the cursor() :/ please help (ping me)

AttributeError: '_asyncio.Future' object has no attribute 'send'

code:

self.conn = self.loop.run_until_complete(sql.connect(**self.configurations))
self.cursor = self.loop.run_until_complete(self.conn.cursor())

[thick moat]

SELECT * 
FROM `vehicles` v
LEFT JOIN `tbl_bases` base ON base.vehicle_id = v.id;

query taking forever, when I limit it to 500 records, it takes about 10 or 11 seconds. When I use RIGHT JOIN, no problem, it executes fast.

[remote plinth]

i fixed

but got a new error (1054, "Unknown column 'prefix' in 'field list'")

[untold ledge]

def deleteid(idarg):
    try:
        mycursor = conn.cursor()
        mycursor.execute("""DELETE FROM myusers where id = {}""".format(idarg))
        conn.commit()
        conn.close()
        print("statment executed successfuly...")
    except sqlite3.Error as e:
        print("error encoutered")
    finally:
        if conn:
            conn.close()
            print("connection is closed")

deleteid(1)

i was trying to run this code but it kees on running into errors

Here is my myusers table

and this is the terminal output:

error encoutered
connection is closed

guys help pls

ok so i did some debugging and found out that you needed to add id to my table wich i didnt have

now how do i update my table by adding id without hvaing to delete it??

[onyx pier]

"hey everyone i'v start learning programing now I am learning python any advice"

[remote plinth]

does conn.commit() close the db in aiomysql?

        async with self.bot._pool.acquire() as conn:
            async with conn.cursor() as cursor:
                #codes ...
                #codes ...
            await conn.commit()
            await conn.close()

conn.close() is an error can't use await expression in NoneType

when i remove it, it runs fine

[versed flower]

Hello ! I'd like to make a line break in an sql request but CHAR(13) doesn't work. So there's any other option ? (I'm using SQLite 3 and pyqt5) .. Thank you

[snow iron]

I think new line is char(10).

char(10) --> New line (\n)
char(13) --> Carriage return (\r)

In Windows system new line is \n\r in nix it only \n

[potent spoke]

is storing files in the binary form on a database the right way to store files?

[snow iron]

it will be stored as blob

what is the use case ?

[potent spoke]

storing pdf files

[snow iron]

You can easily store pdf in database and but if data is huge then performance will be impacted

[potent spoke]

store how? as a file or binary formate?

[snow iron]

which database you are using ? different database have different data type

For Ex : in postgres you can store in bytea

[potent spoke]

its a good practice ?

[snow iron]

Depends on use case. In my project, we save images in data base that are mapped to specific products

[potent spoke]

so how do you store them as a file?

i just need to store pdf mapped to user id

[snow iron]

insert into my_table (photo ) values ( bytea('<pic path on local OS>'))

create table my_table
(
user_id int , 
pdf_files bytea
); 

insert into my_table (user_id , pdf_files  ) values ( 1,  pg_read_binary_file('<pic path on local OS>')); 

Edit : function to convert to byte is pg_read_binary_file

[potent spoke]

i see

also i am using sql alchemy and when i shared it to my friend it gave attribute error

how do i fix that?

[snow iron]

Are you using the correct column name ?

Postgres field name are case sensitive

[potent spoke]

i am using sql alchemy

[snow iron]

sql alchemy is not a database but a tool to interact with database

[potent spoke]

ohh

it said attribute error: cant set attribute

[potent spoke]

yeah it should be right it works on mine

[snow iron]

with out seeing code snippet no one will be able to help. 🙂

[potent spoke]

wait let me find

heres the error

def login():
    if current_user.is_authenticated:
        if current_user.userType == "teacher":
            return redirect(url_for("uploadResult"))

        elif current_user.userType == "student":
                return redirect(url_for("studentReport")    
    form = LoginForm()
    if form.validate_on_submit():
        user = User.query.filter_by(username=form.username.data).first()
      
        if user and bcrypt.check_password_hash(user.password, form.password.data):
            login_user(user,remember = form.remember.data)
            flash("you are logged in ", "success")        
                    
        else:
            flash("Wrong username or password or the class, check again.", "danger") 
    return render_template("login.html", title='Login', form = form)

and the function, sql alchemy gives attribute error: Can't set attribute

can anyone help?

[remote plinth]

please

[jaunty galleon]

I am pretty sure it closes the connection, but when you are using a context manager, it closes itself using __exit__ or __aexit__

[unkempt prism]

The stacktrace should have the line number.

I'll guess Try to hard code remember=True or False

login_user(user,remember=True)

[remote plinth]

ok

[torn sphinx]

    @commands.command(name='command1')
    async def command1(self, ctx):

        if r.get(ctx.author.id).decode() == 1:
            await ctx.send('You are already in a command.')
            return
        else:
            r.set(ctx.author.id, 1)
            await ctx.send(r.get(ctx.author.id).decode())

        await asyncio.sleep(3)
        await ctx.send('In command1')

        r.set(ctx.author.id, 0)
        await ctx.send(r.get(ctx.author.id).decode())

Only trying to do something simple here, the idea is to toggle a key to True once a user is inside a function.
As the function ends, the key is toggled back to False.

At the top of the function there's a condition that checks if the user is "in a command". This for some reason is failing to work.

If I call the same command within the sleep period, it doesn't seem to account for the if statement.

When checking the output of the toggle, the value update is working fine.

Any ideas? Thanks

[radiant linden]

Can anyone help me with appending a value to database columns? Say I have a table "Users" Where I have a column for the user's name, a column for calories eaten, and a third column with the date. Is there a way to append values to the calories and date column for that user using sqlite3?

So far my guess is something like:
cur.execute("UPDATE Users SET calories=?, date=? Where User_Name=?, (cal_input, date_input, name))

But this obviously only updates the columns and doesn't append the values to already existing values.

Sorry, didn't format the code. Here is my guess so far:
cur.execute("UPDATE Users SET calories=?, date=? Where User_Name=?, (cal_input, date_input, name))

[pale socket]

Do you need to add the values together in the SET? the current value in the database + the new value?

It might be a little clearer using an fstring, but it would be like SET calories = calories + {cal_input}

[unkempt prism]

Using fstrings is not a great idea as you are not protected from using sql injection. so should stick to the binding syntax.

Saying that I'm unsure if the following works.

cur.execute("UPDATE Users SET calories=calories+?, date=? Where User_Name=?, (cal_input, date_input, name))

[pale socket]

Fair point! The other better option might be to have another table that tracks the calories at the grain of 1 row per entry, then be able to sum the calories for a user from that

[radiant linden]

So I want the values to be distinct, not added together. Looking into this more it looks like I may need to use a second table and link them together.

[unkempt prism]

You can implement the suggestion to

then be able to sum the calories for a user from that

by the 2) Creating a view with custom column names example -> https://www.sqlitetutorial.net/sqlite-create-view/

Its a great use case for a view and this example seems quite relevant.

[serene pivot]

Hello all! Desperately need some postgres help .
So, my postgres server shut down due to lack of disk space. I found out that a relation with oid 24386 was taking up 36 gigabytes of disk space in
/var/lib/postgresql/10/main/base/16385
Since this oid didn't correspond to any of my tables, I deleted all the files and tried to restart postgres. Unfortunately, postgres regenerated all the files whilst rebooting and threw this error. What can I do? How did this relation get so large?
https://cdn.discordapp.com/attachments/783393665417740318/858553576107671572/unknown.png

[white geode]

Any tips on NoSQL to SQL migrations..?

[untold ledge]

hey guys, is there a way you can add id to your table withoput deleting it and re creating it ??

[potent spoke]

it worked when i changed sqlAlchemy version to 2.5. but thanks

[sinful condor]

I am getting this error dns.resolver.NoNameservers: All nameservers failed to answer the query _mongodb._tcp.fireball-discord-bot.5qsqj.mongodb.net. IN SRV: Server 127.0.0.11 UDP port 53 answered [Errno 22] Invalid argument and because of that this error pymongo.errors.ConfigurationError: All nameservers failed to answer the query _mongodb._tcp.fireball-discord-bot.5qsqj.mongodb.net. IN SRV: Server 127.0.0.11 UDP port 53 answered [Errno 22] Invalid argument but it only happens sometimes without changing any code. I googled it and found some things and tried but they didnt help. Any Suggestions for Fixing it? Ping Me if you Respond

[remote plinth]

does LONGTEXT have a limit?

(mysql)

[proven arrow]

You can refer to thevalues at, https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-strings

[remote plinth]

is it enough for 2000 char?

[proven arrow]

yeah

[remote plinth]

good!

thanks

[raw mantle]

Could someone provide me a good resource to help me decide which dbms would be best for me to use? All the options are overwhelming, and a lot of the info I’ve found on google is contradictory.

[proven arrow]

@raw mantle You pick one that helps you solve the problem your working on. Not sure whats so overwhelming.

[raw mantle]

There are alot of options and to me they all seem to do the same thing just with a different name. Its overwhelming because there isn’t a clear best option to me.

I just want to take data from a csv > excel > database so I can use the data in my projects easily.

And later add automate for whatever can be automated

[proven arrow]

How much familiarity do you have with databases (setup etc.)? How much users/concurrency do you expect? And how often will you be reading/writing to the db?

[finite lily]

Hello everyone. Looking to setup a testing environment and just a few questions as its with databases and I'm a bit unfamiliar outside of some ORMs. I am using asyncpg and pytest.

As far as I know, it looks like I'll need to create a temporary database of some sort using a pytest fixture I'm assuming that asyncpg is used to connect to a database once its already made? What would I use to create the actual test database and use yoyo (migration tool) to run any migrations? Is that using pytest-postgresql?

Likewise (and maybe I'm wrong) but would it create this database on every test? Can I just spin up a main one to run on all the tests?

[raw mantle]

Basically no familiarity with databases. I’ll probably be the only user for now, it’s a project so I can learn the process of sql and database management. I plan to add most of the data to the database all at once. Although once I get better at flask and make a website, users will need to be able to read and write to the database. Also i could be using lingo incorrectly as I said I’m just getting started.

There’s a lot I still need to learn I just want to avoid a resource that teaches bad practices. I’ve had bad experiences when I first started learning Python

[proven arrow]

Sqlite would be a good choice. Its lightweight, easy to setup and its library comes packaged with python. Its different to the other server based databases like mysql, as sqlite sits as a file locally on your PC. But, it can still do the job and would work fine for what you described.
Although it may lack features of other popular databases, you probably wont even notice it if you are new to all this.

[remote plinth]

is it safe to make a connection for each command? (discord.py)

in mysql

[proven arrow]

You should try to avoid

Use connection pooling

[remote plinth]

i made a variable in the Bot subclass self.pool = aiomysql.create_pool(...)

i use ```py
async with bot.pool.acquire() as conn:

im asking if there's a better way

[proven arrow]

no

[remote plinth]

no for what

safe or not

[proven arrow]

What you have is good.

[remote plinth]

ok

[raw mantle]

Thanks I’ll start with SQLite. How transferable would experience with SQLite be to oracle, mysql, or Microsoft sql? My goal is to have a project to show to employers and SQLite isn’t mentioned in job postings nearly as much as the other 3.

[proven arrow]

Your knowledge and experience from it can definetly transfer over. They all use the SQL language, although different vendors have their own implementation of it. The main differences you would realise at first is the different data types that other databases have (sqlite only has a handful of storage types with its dynamic storage system). You should focus on understanding how to create a good structure for a database, normalisation rules, how to make some complex queries if you want to impress employers.

I wouldn't worry about oracle, because thats a real beast which can be quite complex, and companies will usually have dbas for that.

You mentioned websites earlier so Mysql is probably the most popular one, and then postgres maybe.

[raw mantle]

Thanks for helping me out

[cursive bolt]

Anyone hhere can help me in my database?

[tepid walrus]

whats the trouble?

[finite lily]

Hey all. This is sort of working, but I notice that the fixture is called each test so my data gets destroyed. I suppose there are a couple of approaches, one being loading the test data as a fixture itself and passing it into the main db function for the other tests. But isn't that inefficient and will cause the test runner to take longer? I understand its probably best to avoid any types of side effects which is why there is a setup/teardown for each function, but if I didn't care so much and wanted to focus on speed of the tests, is there a way to make it persist between each test?

import pytest
import asyncpg
from conf import settings
import yoyo

@pytest.fixture
async def db():
    dsn_base = f"postgresql://{settings.TEST_DB_USER}:{settings.TEST_DB_PWD}" + \
        f"@{settings.TEST_DB_HOST}:{settings.TEST_DB_PORT}"
    test_dsn = dsn_base + "/tmpdb"
    conn = await asyncpg.connect(dsn_base + "/template1")


    await conn.execute("DROP DATABASE IF EXISTS tmpdb;")
    await conn.execute(f"CREATE DATABASE tmpdb OWNER {settings.TEST_DB_USER};")
    await conn.close()
    backend = yoyo.get_backend(test_dsn)
    migrations = yoyo.read_migrations('../migrations')
    with backend.lock():
        backend.apply_migrations(backend.to_apply(migrations))
    backend.break_lock()

    pool = await asyncpg.create_pool(test_dsn)



    async with pool.acquire() as conn:
        yield conn

    await pool.close()


@pytest.mark.asyncio
async def test_db_connection():
    conn = await asyncpg.connect(settings.DSN)
    assert not conn.is_closed()
    await conn.close()

@pytest.mark.asyncio
async def test_db_server_insert(db: asyncpg.Connection):
    query = "INSERT INTO server (guild_id, delimiter) VALUES ('123456789', '/');"
    status = await db.execute(query)
    assert status is not None

@pytest.mark.asyncio
async def test_db_server_select(db: asyncpg.Connection):
    query = "SELECT * FROM server"
    records = await db.fetch(query)
    assert len(records) == 1

obviously my tests won't be on this stuff necessarily, but just using it to see how it was being created/destroyed. If I merge the select statement in the same test as the insert it sees the data so its definitely getting destroyed between tests

[finite lily]

the above is still outstanding. Definitely makes tests a bit longer so any insight is appreciated. Shifting focus more on design. And my apologies as I'm using this kind of exploratory learning

Trying to implement a DAO in python without a ORM just to work with SQL a bit more closely and learn some patterns. Regarding patterns, any suggestions on some repositories that show design of this? For instance let's say I have a Server class that acts as a container and exposes some functions that manipulate that table of the database. I have a main class called MyDal that has a collection of Server objects that represents a row. When I instanciate them, do I pass the connection to the server or is there a better way to share a connection? (or in this case a pool)

For example:

class AmeliaDAL():

    def __init__(self, pool: asyncpg.Pool):
        """
        Implements the DAL for Amelia Discord Bot
        """
        self.pool: t.Optional[asyncpg.pool.Pool] = pool
        self.servers: t.Dict[int, Server] = {}
        asyncio.ensure_future(self.initialize())

    async def initialize(self):
        self.servers = await Server.fetch_all(self.pool)


class Server:
    """
    Represents the Server table for Amelia and all settings
    """

    @classmethod
    async def fetch_all(cls, pool: asyncpg.pool.Pool):
        """
        Fetches all records from a Server attribute
        Returns
        -------
        List[Server]
        """
        async with pool.acquire() as conn:
            container = {}
            sql = "SELECT * FROM Server;"
            records = await conn.fetch(sql)
            for r in records:
                container[r['id']] = Server(r)
            return container



    def __init__(self, record: asyncpg.Record):

        self.id: int = record.get('id')
        self.delimiter: str = record.get('delimiter')

    async def set_delimiter(self) -> str:
        """
        Gets the command delimiter of a server
        Returns
        -------
        :class:str

        """
        # TODO: How to get the pool/conn object from the parent?
        pass

I suppose I could pass pool in the classmethod over to each instance and hold it there. Maybe I'm overthinking it but it kind of smells. Any advice on overall structure is great. Don't mind re-doing it. Just not looking to use any ORM

[proven arrow]

Is there a specific reason you want to keep the same data between tests? I would always start with an empty database/tables for each test otherwise you’ll end up getting strange failures where a test may pass on one run, but fail on another due to different data in the database.

If you use an sqlite memory database tests will be pretty quick.

But generally you want to test with the db you use in production and so for that I personally create the tables once, and then run each test in a transaction, where I rollback at the end of the test so that no data is committed to it.

For dao you can use something like a service class or repository pattern. As for the connection have a global connection through something like a singleton.

[sonic idol]

hi guys, any idea why I'm getting the error "incomplete input" when I try to run a search for log line content with this script?

import sqlite3

DIR_NAME = os.path.dirname(__file__)
db_path = os.path.join(DIR_NAME, "logreport.db")

print(db_path)

try:
    conn = sqlite3.connect(db_path)
except conn.DatabaseError:
    print("unable to open database")
    exit(0)

print("Database opened successfully")

c = conn.cursor()


def create_table():
    c.execute('CREATE TABLE IF NOT EXISTS attacks(id INTEGER PRIMARY KEY, attack_line TEXT NOT NULL)')


file = open('modsec_audit.log', 'r')
lines = file.readlines()
line = lines.pop(0)
while line:
    if "-B--" in line:
        url_line = lines.pop(0)  # found a "-B--", so get the next line with the url
        if "GET /hackademic/admin/ HTTP/1.1" or "GET /hackademic/admin/ HTTP/1.1”" in url_line:  # look for multiple
            # "attacks" here
            print("Attack found on line: %s" % url_line.strip())
            conn.execute("REPLACE INTO attacks (id, attack_line) VALUES (""-B--"", url_line.strip())")
            # PUT your insert sql statement lines here to insert url_line
    line = lines.pop(0)

conn.commit()
conn.close()

[stray fulcrum]

How to find the document with the highest "_id" in MongoDB?

[solar gale]

does anyone else use influxdb? i just started learning how to set it up via docker and im impressed that it comes complete with a web api that you can use to modify certain things on the fly

[finite lily]

Thanks. I got some reading up to do

[worldly dune]

I’ve used it before. Great time series db

Mostly used it to bridge kafka and AWS into grafana

[finite lily]

so did quite a bit of reading. So many examples with SQLAlchemy mixed in. But essentially looks like its just a class like:

class ServerRepository:
    """
    Repository for the Server entity
    """

    def find(self, id: int):
        pass

    def insert(self, server):
        pass

    def update(self, server):
        pass

    def delete(self, server):
        pass

    def save(self, server):
        pass

Makes me wonder though how you cache objects in this. I imagine that lives in the entity that you define. But is that a normal python class? Or you keep it as a dict? Where would the listeners for some type of LISTEN/NOTIFY segment live?

Gosh so much to learn

does that entity reside in the DAL? (I have it as a separate package) Or would that simply be the domain. Without interfaces how do you help prevent errors? Or is it just assuming it will have the right properties etc

[torn sphinx]

is mongodb better than sql

[torn sphinx]

can anyone help me with sql or a repl db

[solid summit]

they are a bit different and you cannot compare them they both have their use cases so do some research for what purpose sql will be right and for mongodb

[cedar trellis]

create table Iphone(
no int,
Model varchar(50),
Price int
);

select * from Iphone]

insert into Iphone(no,Model,Price)
values (1, 'X pro',80000),
(2,'se',30000),
(3,'mini',65000)

select * from Iphone 
update Model set='12 pro'
where no=1```

[cedar trellis]

why im getting an error

[loud crane]

I am getting this error when I try to get all data from the collection for my discord bot , my code works perfect with replit but now I hosted my bot on gcp and I use mongodb atlas , I am connecting through motor.motor_asyncio module

pymongo.errors.ServerSelectionTimeoutError: none:27017: [Errno -3] Temporary failure in name resolution, Timeout: 3
0s, Topology Description: <TopologyDescription id: 60d8305a313d96527dbf4a0e, topology_type: Single, servers: [<Serv
erDescription ('none', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('none:27017: [Errno -3] Temporar
y failure in name resolution')>]>

[proven arrow]

By default on a vps it will run on localhost as well

[untold ledge]

try:
    userdata = [username, password]
    mycursor = conn.cursor()
    mycursor.execute("SELECT rowid FROM myusers WHERE username = '{}'".format(userdata[0]))
    data = mycursor.fetchall()
    print(data)
    if len(data) ==0:
        insertuser("{}-{}".format(username, password))
        conn.close
        existbool = False
        print('account created succesfuly')
        mycursor.execute("SELECT * FROM myusers")
        print(mycursor.fetchall())
    else:
        while not password in data[0]:
            print('your password was wrong')
            password = str(input('please enter your password:\n'))
            mycursor.execute("SELECT rowid FROM myusers WHERE username = '{}'".format(userdata[0]))
            data = mycursor.fetchall()
            conn.close()
except sqlite3.Error as a:
    print("error encountered")
    print(a)
    conn.close()
finally:
    conn.close()

hi, im just asking if you need to reexecute at the while statement the search or it automaticly updates the data variable

help pls im new to python

[upbeat slate]

hello, I am having issues with a discord bot that is super slow, and i have been told that what is making it slow could me the way my code is written

(reading and saving wise)

can someone hopefully take a look and help please?

[digital wharf]

Is there code we can see..?

[lean geyser]

Hey

[wise ruin]

Hello ! I'm beggining on Python and I'm trying to make something that manage database, until now, everything's been okay but now that I want to make a treeview of it, I need a tuple array, wich I don't because I'm using a indexed array like that : {"John":"35","Nick":"43"} and I don't know how to Transform my array into a tuple / find a correct module that can handle it because every time it return J o N i (and sorry I don't use SQL)

[ionic vessel]

This is a django/database related question.
Lets say I have a database table for books with title, author, isbn etc. Now I want to save multiple links to book reviews to every single book entry. What would be a good way to do design this?
Normally this would be a OneToMany relationship but if I would create a new table for all links and the database grows how can I know which reviewlinks were those ones for book x and which for y, z ... ?
So the idea came up to give book table just a charfield entry and just put all links to different reviews with a separator character in it but I'm afraid this isnt a good practice.
So how would you actually handle this?

[digital wharf]

If your model has a relationship to the given book thats how you'd know which book they are related to?

[ionic vessel]

Yes, I know how it technically works. Lets say I created f.e. 10 books. Now I have 5 review links of book A, 3 of Book B, 2 for book C all in the table which holds the URLs, fine.
Now I have to go back to entry of book A and have too search the right 5 links that belong to book A to add them, same for Book B and so on. But if I have thousands of review links it would be like finding needles in a haystack for assigning the right links to the right books.

[proven arrow]

A database is designed to store many millions and billions of rows. Thousands of links it’s literally peanuts.

[wheat olive]

class MenuItems(enum.Enum):
    item_1 = "Steak"
    item_2 = "French Fries"
    item_3 = "Burger"

class Oder(Base): 
    __tablename__ = 'orders'
    chosen_item = Column(Enum(MenuItems))


def oder():
    my_in = input("We have Steak/French Fries/Burger; what would you like to order? ")
    o = Order(???)

When using SQLAlchemy how can I get the correct enum choices from an input that uses the value representation?
And while we are at it: how can i get a list of choices of an enum(preferably the values)?

[wise ruin]

Just found the solution and finished my database, so happy

[rapid ivy]

They are two different database.
MongoDB is a Document DB and SQL is a Relational databases.
Both of them are great at what they do.

[bright dune]

it's a category error. sql isn't a database, it's a way to ask a specific type of database for things. mongodb is a nosql db

[strange stream]

Hey can anyone who knows django well help me out with my question in #help-mango

[sharp tundra]

help

database.execute("UPDATE userdata SET cash = ? WHERE userid = ?", (valuetoinsert, useridentity,))
        con.commit()
``` why does this not update?

btw this is sqlite3

[glacial spindle]

Is database a cursor? If not, it should be database.cursor_variable.execute(..., ...)

[wheat olive]

@glacial spindle use triple backticks for code highlighting.
```sql
UPDATE inventory
SET
CASE :valid WHEN 0 THEN style_id = :style_id, product_title = :product_title END,
CASE :update_md WHEN 1 THEN last_sale = :last_sale, change = :change, lowest_ask = :lowest_ask, highest_bid = :highest_bid END
WHERE rowid = :row
```
works out to

UPDATE inventory 
SET 
    CASE :valid WHEN 0 THEN style_id = :style_id, product_title = :product_title END, 
    CASE :update_md WHEN 1 THEN last_sale = :last_sale, change = :change, lowest_ask = :lowest_ask, highest_bid = :highest_bid END 
WHERE rowid = :row 

[upbeat slate]

yes

late reply ik but i had to sleep yesterday 😅

would you like me to send in MP?

[digital wharf]

!paste

[delicate field]

Pasting large amounts of code

If your code is too long to fit in a codeblock in discord, you can paste your code here:
https://paste.pydis.com/

After pasting your code, save it by clicking the floppy disk icon in the top right, or by typing ctrl + S. After doing that, the URL should change. Copy the URL and post it here so others can see it.

[upbeat slate]

oki\

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

there

its mainly a bot to mess with my friends

but once there is reading and saving it gets so slow

[digital wharf]

Define slow

[upbeat slate]

basically

it takes time to save the files

i dont know about opening them

but when i run the command and look in visual studio

the file actually changes like 3 seconds later

and if many people use the same command at once it becomes a real mess

[digital wharf]

Do you use the spam command much? That's the only thing I see is your blocking code

[upbeat slate]

nope

[digital wharf]

Change time.sleep to asyncio.sleep and try re run it

[upbeat slate]

i rarely use it i dont think its cuz of it

[digital wharf]

That's a blocking call. Change it

[upbeat slate]

okay

did it

[digital wharf]

Try re run it

You could profile your code and see what the biggest issue is. Likely the only thing for it

[upbeat slate]

how can i do this

i am not good with terminology

[digital wharf]

Finding the video for it

[upbeat slate]

i tried using >trusted add

[digital wharf]

https://youtu.be/m_a0fN48Alw

Use that on your on message code

[upbeat slate]

okay

[digital wharf]

Then use the snakeviz part and send a screenshot of it's output

[crude shard]

guys so how can i reduce the number of database calls? this is mongodb

    skin1=collection.find({"_id":2},{"_id":0,"skin":1})
    champ1=collection.find({"_id":3},{"_id":0,"champ":1})
    cprice1=collection.find({"_id":8},{"_id":0,"cprice":1})
    cquantity1=collection.find({"_id":9},{"_id":0,"cquantity":1})
    image1=collection.find({"_id":6},{"_id":0,"image":1})
    availablity1=collection.find({"_id":10},{"_id":0,"fut":1})

[south spruce]

So this is part of my query which giving me biggest problem

db.getCollection('devices').aggregate([
{$match:{_id:"2934-F159-5172"}},
{$lookup:{from:"subscription",localField:"subscriptionIds",foreignField: "_id",as:"devicesSubscriptions"}},
{$lookup:{from:"dataplan",localField:"devicesSubscriptions.dataplanIds",foreignField:"_id",as:"subscriptionDataplans"}},
{$lookup:{from:"contracts",localField:"subscriptionDataplans.contractsId",foreignField:"_id",as:"dataplansContracts"}},
{$lookup:{from:"sims",localField:"dataplansContracts._id",foreignField: "contractId",as:"ReachableSims"}},
{$addFields:{dataplan:{
    $map:{ input:"$subscriptionDataplans",
        in: {
            _id: "$$this._id",
            allowRoaming: "$$this.allowRoaming",
            name: "$$this.name"
            }
        }}}},
{$project:{"_id": 0, "ReachableSims": 1, "dataplan":1}},
{$unwind:"$ReachableSims"},
{$unwind: "$dataplan"},
{$replaceRoot:{newRoot:{$mergeObjects:["$ReachableSims","$$ROOT"]}}},```
 
from subscription I reach 2 dataplans and when doing unwinds it's create non existatnt sims In true case there is only 2 sims. But because each sim has dataplan object inside which holds 2 records.
And I can't filter out which sims are real results and which are created by {$unwind: "$dataplan"}, line. Any ideas how to get this problem solved?

Bad results as you can see there is 2x with same object ID only difference is they hold different dataplans inside

And this is good result. As I should only get only 2 sims. But dataplans are in array and I dont know which one is needed for sim and which is not

[dreamy flint]

In PostgreSQL, how do I update the JSON in a field? Like do I need to get the JSON inside the field then change all of it or is it possible to use raw sql to change a field or add fields and dictionaries? If so how do I do that

[lone island]

i wanna backup my database in pgadmin but i get an error, idk what is the error can anyone help ?

[unkempt prism]

Not really without more info such as the error.

I used to use pgadmin to backup my dbs too. Though I learned to use the cmd line tools https://www.postgresql.org/docs/13/app-pgdump.html and pg_restore to backup and restore backups.

Give them a pg_dump a go and pg_restore it into a new seperate database to test it.

[lone island]

ty, also can u check #help-cake

i wanted to use pgadmin since it's quite easier, don't know if using cmd line tools gonna make any difference on the error tho @unkempt prism

[unkempt prism]

sure it might as pgadmin is hiding the output.

[crisp spade]

I'm making a google sheets db, and I'm wondering what would be the client email in this json file?
{"web":{"client_id":"543123354703-t8r4skmpm55qrjh4dqq1ohs9lq6shsu2.apps.googleusercontent.com","project_id":"python-rpg-bot-1234","auth_uri":"https://accounts.google.com/o/oauth2/auth","token_uri":"https://oauth2.googleapis.com/token","auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs","client_secret":"PHEwRf4g-jzLI9ysH6-S1-l1"}}

I can't find the email

oh wait nvm, is in another thing

[quiet lava]

about to say there is literally no @ lol

[crisp spade]

ye just figured out

[safe stump]

How do I set up db tables when there are several 1:N relationships for a the objects? I am trying to model valorant json match data

each game has 10 players and at least 13 rounds

each round has up to 12 kills

[minor ruin]

Game Table with Round Table that references along with Player Table and Kill Table

and SQL queries to generate data will involve joins

[south arch]

how do i get information from sql databases in aiosqlite?

[woeful plover]

I'm trying to run a Flask/SQLAlchemy application and getting Internal Server Error - details in #help-honey

[crude shard]

How can I copy values from one mongo database to another just by using ID

[woeful plover]

OK. About to shift into the phase of designing the DB(s) for a web application. I anticipate having users, articles (blogs - don't need to be associated with users but can be), saved objects, and forum Q&A.
Is it better to bring this all together if there's going to be relationships? Going to try SQLite3 as a first time project.
Also, I assume I can add security levels as a column to the DB and use that to assign specific security trimming in the app? Such as moderating posts etc.

Any recommendations on tutorials for SQLite3? Yes, I get it has single write limitations, and is good for 100k hits/day. This is a working prototype at this stage.

[tough cradle]

self.database.execute("insert into orders (id, buyer, paid_price) values (?, ?, ?)", (self.id, str(buyer), 0))

am i doing something wrong?

sqlite3 module in python btw

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 3 supplied.
constantly getting this error

[modest ledge]

any idea how to install mariadb lib on python3 (ubuntu vps)?
im getting this when trying to install it

        "mariadb_config not found.\n\nPlease make sure, that MariaDB Connector/C is installed on your system.\n"
    OSError: mariadb_config not found.

    Please make sure, that MariaDB Connector/C is installed on your system.
    Either set the environment variable MARIADB_CONFIG or edit the configuration
    file 'site.cfg' and set the 'mariadb_config option, which should point
    to the mariadb_config utility.
    The MariaDB Download website at <https://downloads.mariadb.com/Connectors/c/>
    provides latest stable releease of Connector/C.

[finite lily]

Hey all, if I was creating my own data layer where the goal is to not use Active Record, am I following the Repository pattern here somewhat with python? Wasn't sure what to do about the Domain entities. I assume those will not be in my Data Access Layer so I implemented them as an Abstract class that my main program will use for inheritance? Does this smell? The assignment of the properties looked a bit hackish.

class AbstractEntity(abc.ABC):

    def __init__(self, *args, **kwargs):
        self.id = kwargs.get('id')
        super(AbstractEntity, self).__init__()

class AbstractServerEntity(AbstractEntity):

    def __init__(self, *args, **kwargs):
        self.guild_id = kwargs.get('guild_id')
        super(AbstractServerEntity, self).__init__(*args, **kwargs)

class ServerRepository:

    def __init__(self, pool: asyncpg.Pool):
        self.pool = pool

    async def find(self, id: int) -> t.Optional[AbstractServerEntity]:
        query = f"SELECT * FROM Server WHERE Server.id = {id};"
        async with self.pool.acquire() as conn:
            conn:asyncpg.Connection
            record = await conn.fetchrow(query)
            result = AbstractServerEntity(**record) if record is not None else None
            return result

[torn sphinx]

hi, what's the best database and easy to use

[vernal spade]

async with await pool.Connection() as conn:
            async with conn.cursor() as cursor:
                    await cursor.execute(query)
``` I've done something really stupid in my code and am curious how to simplify it. My current code runs in a way that in every async function I run the code block above. Can't I define cursor at the top of my code to fix this?

[proven arrow]

You should avoid use a global cursor so it would not be good idea to define the cursor at the top of the code. If you want to simplify it then consider abstracting it into a function where the function takes the query, and parameters. Then call the function wherever you need to make the query.

[vernal spade]

makes sense

thank you

[vernal spade]

MySQL is pretty good

[vernal spade]

async def db(query):
    async with await pool.Connection() as conn:
        async with conn.cursor() as cursor:
            await cursor.execute(f"{query}")

How do I return fetchone and fetchall?

[median wave]

This is sqlite, and dumb question does anyone know how i can manually remove a row of data

[proven arrow]

Can you right click the row it should give option?

[median wave]

oh, i press set to null?

[proven arrow]

No

Not sure how that software works, but you should just do it with Sql.

[toxic vector]

I want to express the notion of a report as an arbitrary collection of references to test records, would this be a sane way to do it?

so if i wanted, say, a list of all tests assoc. with a particular report, I would query report-test for all records with the right report_id

[tame siren]

How to install MySQL in chrome os

Please help.

[calm grotto]

What's the context here?

[toxic vector]

an application that records data and can generate reports based on sets of that data.
project as a collection of test, test as a collection of reading
a report would be a subset of a project

where one test can be related to many report

I can't store a 'list' of references to tests as a field on a report, so I'm trying to work around it, although their relationship (appears) to not be as simple as the one-to-many between a project and all the tests that ref it

[modest ledge]

mysql sucks lmao

[knotty gyro]

aiomysql

[proven arrow]

Maybe your connection details are not matching

[knotty gyro]

this one

[proven arrow]

How so? It powers a lot of the web and used by many top companies. It’s curtains passed the test and has proven itself.

[calm grotto]

This seems sensible. I take it you shouldn't have a report related to a test that is not a related to the corresponding project? You might want to make this a constraint.

[toxic vector]

thanks! sure, so something like this? would this be an appropriate situation to make all three columns primary keys? how would i decide whether to use a composite primary key for a table vs the regular ROWID

[proven arrow]

You use a composite key when you need multiple columns to uniquely identify a row.

[grim vault]

The icon becomes available after selecting one or more records.

After deleting you'll need to click on Write Changes for commiting the change.

[median wave]

@bot.command()
async def perm_test(ctx):
    guild = ctx.guild
    db = await aiosqlite.connect("perms.db")
    async with db.execute("SELECT role_id FROM perms WHERE guild_ID = ?", (ctx.guild.id,)) as cursor:
        roles = await cursor.fetchall()
        allowed_roles = "\n".join([f"{role[0]}" for role in roles])


    for list_of_roles in allowed_roles:
        if list_of_roles in ctx.author.roles:
            await ctx.send("user has the role")
        else:
            await ctx.send("User does not have the role") 

Idk whats wrong with this, i have the role but for some reason it just spammed user does not have the role

[agile temple]

what exactly is for making databases? json or sql?

[vernal spade]

Why does almost every name-brand company use it then?

[torn sphinx]

asyncpg or psycopg2 for postgresql?

[indigo flare]

json isn't an database

[brazen charm]

They dont, MySQL is popular because it was one of the first and really only SQL databases around in early times, it's also the thing wordpress and PHP used considerably in the world of web so that basically shot it to the top popularity. As an extra note if you are using MySQL you might as well use MariaDB like most large systems still using MySQL so you're not dealing with some of the MySQL gotchas or Oracle's licensing shenanigans.

In recent times Postgres is actually the fastest growing database in the SQL world and generally for good reason, Postgres generally does anything MySQL can do while scaling better in large collections and often being much faster for large queries, combine that with events and notifications + the scripting language psql and close SQL spec standard and ability to plug and play 3rd party extensions with other languages like python, rust, c etc... it's very quickly become the SQL db of choice.

[woeful plover]

I'm currently learning sqlite3 and it's clear it won't be sufficient for this web application I'm building.
**Opinions on DB choices for a Flask web application: **

• I'm going to need user permission levels, storage of articles with user permissions (blogs), forum posts, and the storage of information in objects related to rendering graphical diagrams dynamically through the browser.
• I like how I can use jinja to display pages/buttons/links based on being logged in. I'd like to extend that to displaying by user role.
• I'd like to be able to display things like user tables and search to Administrators so that they can change security permissions.
• I'd like to be able to implement security groups, and use security groups identify to drive what is displayed (transcending just at-logged-in decorators)

Some other considerations:

• I'm familiar with DB design, with most of my experience using SharePoint/SQL. My interactions with SQL have been masked by SharePoint
• However, I am a couple of months into Python and get OOP. The idea of a ORM DB makes sense so far to me.
• Configuring tables and FlaskForm Forms in Flask has been intuitive to me. Complex Forms are going to be a huge part of this web application.
• In fact, it's the above experience coming together that has shown me sqlite3 will not meet my needs.

What DB would you recommend? I'm currently looking at postgresql.

[brazen charm]

generally depends on the context, if your code is all sync code and not async then there isnt much reason not to use psycopg2 other than some of it's weird behavioural patterns like execute many being slower than a for loop.

If it's async then absolutely asyncpg, if you want a very tidy and efficient driver then also asyncpg. AsyncPg doesnt support things like yugabyte though so that depends on your use also.

[torn sphinx]

thank you for help

i'll remember that

[vernal spade]

Good point. Sorry on my ignorant behalf

[proven arrow]

Use whichever db you feel comfortable with. Both mysql or postgres would do the job. You can even use sql server as thats what sharepoint uses.

[strange yacht]

Hi, I was wondering how you actually get a database up and running using postgres, any pointers/help appreciated

[pure cypress]

I'd use Docker to run an instance of postgres.

The page on Docker Hub for the postgres image has instructions on how to do some basic configuration and get it running

But yes, there is some added learning curve for using Docker, if you choose to use it.

[strange yacht]

Okay thank you, Ill take a look

[vernal tide]

sqlite3: is there a column type for bools? (using the sqlite3 module from python stdlib)

[vernal tide]

The big thing with using databases in docker, is to ensure you don't store your data/setup in the container - it will disappear on restart. (which is a feature for testing, you could say)

[proven arrow]

Sqlite3 column types don’t really mean anything as it’s dynamic, and just there for readability. But if you try to store Boolean (true/false) then it will be stored as an integer of 1/0

[south arch]

if i do

[vernal tide]

Sure, I knew something like that. But I'm trying to use datasette, which visualizes based on column type somehow. 1/0 is OK ish but booleans look nicer for visualization IMO!

[south arch]

await connection.execute(f'select jsontable FROM points_table where server_id={i};'

will it return a raw string with the contents/

or some random object

[vernal tide]

I don't think it returns anything

You need to get a cursor then e.g. cursor.fetchall() - see https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.execute (and executescript below it)

[vernal tide]

^ those were for you

[south arch]

ok

@vernal tide would fetchone() give a string?

[vernal tide]

@south arch not sure, TIAS is what I recommend!

@south arch playing around in ipython, for me it looks like you get one tuple for each row

[torn sphinx]

anyone know how to ping mongodb using pymongo

[winged saddle]

which rdms is the mostly used in analytics/data science

[brazen charm]

depends on the area

[winged saddle]

currently studying ms sql atm but i want to focus on 1 first rather that jumping to mysql/postgres/oracle

[winged saddle]

wdym area, by sector?

[brazen charm]

Big data / large volumes of write and very targeted reads generally look towards Scylla / Cassandra (timescale data etc...)

Postgresql / SQL in general is still very popular for all sorts, but can suffer as collections get bigger in terms of insert throughput etc.. (timescale data lacks)

Supersets like TimescaleDB, Apache spark SQL which are built off some existing back bone designed to be optimised for set things e.g. timescale data, analytics, data filtering...

[vernal tide]

I guess if you have your data in a database, that's the one you use, so it will depend on the org you're working in. So unless you want to be the guy building the database, general SQL SELECT query skills is useful; dbadmin skills for a given db might be less relevant in an analyst's role

[winged saddle]

so as a practioner i should first on the language itself rather than the the tools?

[crisp spade]

{"web":{"client_id":"543123354703-t8r4skmpm55qrjh4dqq1ohs9lq6shsu2.apps.googleusercontent.com","project_id":"python-rpg-bot-1234","auth_uri":"https://accounts.google.com/o/oauth2/auth","token_uri":"https://oauth2.googleapis.com/token","auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs","client_email":"[e-mail]","client_secret":"PHEwRf4g-jzLI9ysH6-S1-l1"}} This json doesn't work for my code

# Import module
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pprint import pprint
  
scope = ["https://spreadsheets.google.com/feeds", 'https://www.googleapis.com/auth/spreadsheets',
         "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive"]
  
  

creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
sheet = client.open("Python DATABASE").sheet1
  
  
  
# Display data
data = sheet.get_all_records()
row4 = sheet.row_values(4)
col2 = sheet.col_values(3)
cell = sheet.cell(5, 2).value

  
# Making Account
username = input("What username would you like your account to be?:")
password = input("And your password?:")
  
# Inserting data
insertRow = [username, password, [], 0]
sheet.insert_row(insertRow, 2)
print("\nAll Records after inserting new row : ")
pprint(data)
  
  
  
# Deleting data
sheet.delete_row(7)
print("\nAll Records after deleting row 7 : ")
pprint(data)

The error is mainly in the credentials.

[vernal tide]

@winged saddle it really depends what kind of work you want to do, for what kind of company. If you want to be the person performing data analysis, and the data is stored in a sql server, then sure.

[crisp spade]

sooo does anyone know?

[woeful plover]

Thanx. I'm going to stay away from SQL for the prototype. Don't need that kind of cost.
Appreciate the feedback. Will likely go with postgresql.

[candid owl]

What does "unread result" mean in the context of mysql?

[proven arrow]

Huh what? Not sure how you will manage that? Postgresql is still Sql it’s part of the name?

[proven arrow]

Means you asked for more rows from the db than you have actually read from the cursor

[fiery socket]

what's more useful, aiosqlite or sqlite3?

[proven arrow]

Both

They just for different purposes

[fiery socket]

I need resources for aiosqlite can someone help me out

[woeful plover]

Semantics 🙂
SQL is how I hear MSFT's version referred to. Which is why I am very specific with the name of the SQL engine

[pure cypress]

It's called "SQL Server", not just SQL. Well, maybe it is but I've never seen anyone shorten it to that. That would just make an ostensibly generic name even more vague.

[proven arrow]

Do you mean T-sql? Either way good luck with your project 👍🏻

[light forge]

Hello. I have a question for SQL Database

[delicate field]

Hey @light forge!

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.

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

[light forge]

Ohh my bad

SQL Database. Example I have a data like Civil Engineering, Software Engineering, Mechanical Engineering, and etc. Is there a command to show them all by including the VALUE="Engineering"?

[proven arrow]

Depends how data is stored. If it’s that value in one column you can use the IN which is basically short for multiple ORs

select * from table where column in (‘civil’, ‘mechanical’, ‘software’);

[light forge]

ohhh I'll try

[light forge]

Thanks a lot. It worked

[torn sphinx]

I have a jsonb[] Array, how will i get length of it? tried ARRAY_LENGTH() and JSONB_ARRAY_LENGTH()

Well, got it sorry

[acoustic abyss]

i already asked in #networks, but this might be a better place for it. with the auto-install feature in https://pypi.org/project/justuse now fairly solid, i'm looking into the possibility of integrating a p2p solution for package distribution in order to relieve pypi from its heavy burden. i'd like to start with a way to enable a search function and i was thinking of a distributed database, can anyone suggest a solution for this?

[frigid glen]

https://www.cockroachlabs.com/ - might be a good place to start

[acoustic abyss]

checking..

@frigid glen it looks interesting and promising, but the licensing looks like a pita

i'd probably need to ask them to relicense specific components

any alternatives?

[proven arrow]

You would want a FTS solution

Full text searching

Take a look at Elasticsearch, Algolia, meilisearch.

[acoustic abyss]

i'm listening 🙂

[proven arrow]

There will be others but I’ve only ever used the ones mentioned above

They will generally handle everything for you from fuzzy matching etc

[acoustic abyss]

can i deploy them as p2p or do i need dedicated servers for those?

[proven arrow]

Better to have a dedicated server for it.

[acoustic abyss]

then it wouldn't be a better solution than the one pypi already tried 😦

pypi took the search offline because of the heavy load

[frigid glen]

Sounds like you want a blockchain for pypi

[acoustic abyss]

@frigid glen i'm open to all suggestions at this moment, anything goes 🙂

also the solution doesn't have to be backwards compatible, i still can use pypi, conda etc. as fallback

so what's the most modern take on package distribution you can come up with?

the user needs to be able to search all available packages and to import something, they provide name, version and hash of the package to pull from anywhere

[pale jay]

I was using mysql-connector-python all this time (the official mysql one) I want to change to something else what's a good one

[burnt turret]

Think pymysql is pretty popular

!pypi pymysql

[delicate field]

PyMySQL v1.0.2

Pure Python MySQL Driver

[burnt turret]

why do you wanna switch though?

[bright dune]

for async there's aiomysql

[burnt turret]

their repo is a little dead though :/

[harsh pulsar]

IPFS?

[acoustic abyss]

ohai @harsh pulsar 🙂

what's IPFS?

[scenic zinc]

Interplanetary File System

[acoustic abyss]

never heard of that

[scenic zinc]

I heard of IPFS thanks to crypto

https://www.google.com/search?q=IPFS

[harsh pulsar]

https://ipfs.io/ content-addressed decentralized storage. you "pin" a file by hosting it, and as long as anyone anywhere in the world is hosting it, it's always accessible. and because it's content-addressed, you don't have to worry about checking the file hash, because the file hash is the address of the file.

i think some of the underlying tech is related to bittorrent? not sure how it works under the hood

[acoustic abyss]

how does it retrieve the location of files?

[scenic zinc]

For those who are fluent in SQLAlchemy. I have an error with a nested one-to-many relationship. The code can be found here: https://stackoverflow.com/questions/68106427/nested-one-to-many-relationships-in-sqlalchemy But for some reason when I try to access the child lets say program.courses it comes back as a RelationshipProperty

[scenic zinc]

Unique Addressing. As long as you have the address and the file is hosted anywhere you can always access it.

[acoustic abyss]

yeah, but how does client A know from which IP to pull the content? :p

there must be some kind of shared knowledge on where the files are actually located, no?

i gather the clients share that knowledge, but how do clients know about each other?

[scenic zinc]

If the underlying tech is a blockchain (which it most likely is) you do not need an ip address. Think of it like this you have a unique address that is known in the blockchain and all you need to do is to use that address to pull up the file.

Like you would with a crypto wallet

[acoustic abyss]

yeah, but you still need an ip address to download something..

hmmm

[scenic zinc]

Well you would be the client and the ip address would be the rpc endpoint you have to hit to communicate with the blockchain

Other than that the files are not stored on a specific computer

It is distributed across the network

But it gives a high level overview on the landing page

[acoustic abyss]

well, you still need a way to find other clients, no?

does ipfs provide a central entry point?

[scenic zinc]

As a developer you do not need to worry about finding where the file is stored. The nodes will ask each other if they have seen the file.

“When other nodes look up your file, they ask their peer nodes who's storing the content referenced by the file's CID. When they view or download your file, they cache a copy — and become another provider of your content until their cache is cleared.”

“A node can pin content in order to keep (and provide) it forever, or discard content it hasn't used in a while to save space. This means each node in the network stores only content it is interested in, plus some indexing information that helps figure out which node is storing what”

[acoustic abyss]

yeah, i'm just watching https://www.youtube.com/watch?v=Z5zNPwMDYGg

looks definitely promising, and the MIT-Apache license works, too

question though - how would full text search work for this?

searching for packages

maybe i need something like cockroachdb for one thing and IPFS for the other?

[frigid glen]

why not re-use pypi's search capablities, and cache results i.e packages, versions, hashes?

[acoustic abyss]

pypi deprecated their search

with no intention of getting it back up due to overload

i wished it was that easy..

what i'm thinking though, i could download the whole index of files once and put it on github to jumpstart

bootstrap

and then incrementally update it

maybe need to ask pypi to provide me with an api

problem is, i might need to ask conda etc. as well

i'd rather let users use the packages and have a "living db"

[frigid glen]

https://pypi.org/search/?q=fastapi - looks quite functional

[acoustic abyss]

pip search fastapi
ERROR: Exception:
Traceback (most recent call last):
File "/home/thorsten/anaconda3/lib/python3.8/site-packages/pip/_internal/cli/base_command.py", line 228, in _main
status = self.run(options, args)
File "/home/thorsten/anaconda3/lib/python3.8/site-packages/pip/_internal/commands/search.py", line 60, in run
pypi_hits = self.search(query, options)
File "/home/thorsten/anaconda3/lib/python3.8/site-packages/pip/_internal/commands/search.py", line 80, in search
hits = pypi.search({'name': query, 'summary': query}, 'or')
File "/home/thorsten/anaconda3/lib/python3.8/xmlrpc/client.py", line 1109, in call
return self.__send(self.__name, args)
File "/home/thorsten/anaconda3/lib/python3.8/xmlrpc/client.py", line 1450, in __request
response = self.__transport.request(
File "/home/thorsten/anaconda3/lib/python3.8/site-packages/pip/_internal/network/xmlrpc.py", line 45, in request
return self.parse_response(response.raw)
File "/home/thorsten/anaconda3/lib/python3.8/xmlrpc/client.py", line 1341, in parse_response
return u.close()
File "/home/thorsten/anaconda3/lib/python3.8/xmlrpc/client.py", line 655, in close
raise Fault(**self._stack[0])
xmlrpc.client.Fault: <Fault -32500: "RuntimeError: PyPI's XMLRPC API is currently disabled due to unmanageable load and will be deprecated in the near future. See https://status.python.org/ for more information.">

[frigid glen]

import requests
r = requests.get('https://pypi.org/search/?q=fastapi')
# do magic

[acoustic abyss]

yeah, i could crawl it, but that's not really nice for pypi 😉

quite the opposite of the intention

[frigid glen]

I think if you cache responses, its pretty safe on pypi, question is probably how easy it is to pull hash. Offloading the download would be the biggest win, IMO.

[acoustic abyss]

the hashes aren't a problem, they're provided in the json for each package distribution

i could just forget about the inline search and focus on the download side

how hard is it to integrate IPFS in python?

[frigid glen]

many small wins = big win

[acoustic abyss]

well, considering that the search would be an immediate big win for users, it really depends on who gets the focus 😉

but maybe integrating IPFS actually might be easier than the distributed DB for searches

[acoustic abyss]

thanks a bunch for the suggestions, i think i'll try to integrate IPFS first

unless something better presents itself

and when i'm done i think i should include @harsh pulsar as contributor 😉

[acoustic abyss]

one last bit of the puzzle though, which might be harder and not belong to this channel: if everything is version/hash-pinned, what would be the best (simple, safe) way to update stuff, most importantly security patches?

on another thought, the solution could lay in that distributed DB we talked about?

[scenic zinc]

Any sqlalchemly and pydantic professionals in this chat that is willing to answer a few questions?

[harsh pulsar]

ipfs doesn't handle discovery, there is some kind of domain-name-like system for it but i don't know how it works

so you'd still need something like pypi to find the packages, but instead of centralized http/ftp hosting the files themselves are hosted on ipfs

but this is kind of ambitious

you can't expect regular end-users to be comfortable with transparently sharing files across the internet, letting randos access data on their computers

basically what you're proposing is replacing pypi with something that isn't pypi, which i think could be a good idea, but is very beyond the scope of your original project i think

[acoustic abyss]

well, the main points are covered by now, and i have the idea of p2p distribution for a long time in my head

of course i'd communicate the start and serving of a p2p client openly and let people easily disable it, but having it as a default might make all the difference

auto-install works

[harsh pulsar]

for what it's worth, people plunking around in a notebook is probably a very small fraction of pypi traffic

constantly rebuilding containers and cloud things and such, all fetching endlessly from pypi

repl.it re-downloading everything for every single repl

there's zero incentive to host your own mirror and zero disincentive to just abuse pypi without donating a cent

justuse end-users seeding each other is nice but it's a drop in the bucket

[acoustic abyss]

which is exactly why "doing the right thing" for the community should be a default

well, it might be a drop in the bucket for now, but at least it would be scaling infinitely

the more people use it, the less strain on pypi, the more stable the whole ecosystem

[harsh pulsar]

speaking of which, i really should figure out how to use pip caching across docker images

need to read docs 😴

[acoustic abyss]

also i don't see it as a replacement of pypi, pypi would still serve for seeding and as backup of little requested packages, the most impact would be exactly those packages that are most popular

and if done right, the general download speed penalty of p2p networks could be solved that way

[scenic zinc]

You would think there is some level on encryption/ ensuring only the address holder can access the file. But I would have to do a deep dive.

[harsh pulsar]

I just mean in general. After all this is how bittorrent works, but it's still possibly a concern especially in corporate/professional environments

[scenic zinc]

You mean someone malicious could piece together that data and read the documents?

[harsh pulsar]

I'm not sure, I'm not a security professional, but I can imagine that there might be problems

[alpine ledge]

I have a database using mongodb that I created while using js code and now I wanna use my bot coded with Discord.py to ascess it after recieving a command and send out info in the database.
I use the cogs module for Discord.py and the commands are on other files.So do I need to like login everytime I run the cmd or I can do a connection and it will automatically connect whenever I need it.
My main code for bot: (main2.py)

from pymongo import MongoClient
login = dotenv_values(".env")
@client.event
async def on_ready():
    mongoclient = MongoClient(login["uri"])
    print(mongoclient["test"]["graphitems"])
    print(f"Ready!Login as {client.user}")
client.run(login["Bilitytoken"])

The file where I wanna connect to the db : (newcogs\gtb.py)

from main2 import MongoClient
    #some lines of code to define the command and imports
    async def itemlist(self, ctx):
        print(MongoClient.test["graphitems"])
        ctx.send("Retrieve Suscess")

When starting I got some message showing cnnection suscess somehow I get this when invoking the command

AttributeError: type object 'MongoClient' has no attribute 'test'

[scenic zinc]

Well I would have to take a deep dive into ipfs, but I am sure these are concerns that they have thought/thinking about. The document is broken into pieces and distributed across the entire ipfs blockchain.

[alpine ledge]

Sorry for lengthy code this is my first time coding with pymongo and I really hit a wall

[opaque tundra]

Np mate

[burnt turret]

when you're doing from main2 import MongoClient, it is importing the MongoClient class that is coming from pymongo itself (which is added to main2's namespace when you did from pymongo import MongoClient)
what you need is the instance of MongoClient that you made, which is the mongoclient in your on_ready function
generally, to share that connection across all your cogs, you would assign it to a bot variable, like so

@client.event
async def on_ready():
    client.mongoclient = MongoClient(login["uri"])

now, wherever the Bot instance (you've called yours client) is accessible, you can access the db connection as client.mongoclient

[alpine ledge]

Ok I'll try it out now

[burnt turret]

also, pymongo is blocking https://discordpy.readthedocs.io/en/stable/faq.html#what-does-blocking-mean , look into using motor

!pypi motor

[delicate field]

motor v2.4.0

Non-blocking MongoDB driver for Tornado or asyncio

[alpine ledge]

So if I use motor what do I need to change?

[harsh pulsar]

there are some clear examples in the docs

[woeful plover]

Thanks 🙂 It's an ambitious first project.
It looks like Postgres is the way I'm going. Now to move into the next phase of the build.
And thanks for your thoughts.

[torn sphinx]

hey, is it possible to do a flask-migrate without losing data? normally i do:

python -m app.manager db init
python -m app.manager db migrate
python -m app.manager db upgrade

but this only works with an empty db

[south arch]

would be searching for something in a python dictionary or searching from an sql query on the server be faster?

im wondering whether i should make a cache

[bright dune]

the dictionary will probably be faster, but why not just make it then profile it

that way you get concrete data

[frigid glen]

using async?

[south arch]

what do you mean

[frigid glen]

Are you using asyncio in your code, i.e comprised of coroutines and an event loop, async def methods, and await syntax

import asyncio

async def stuff():
    return f"stuff"

async def main():
    await stuff()

asyncio.run(main())

[south arch]

yes

[frigid glen]

perhaps have a look at https://easycache.readthedocs.io/en/latest/cache/ - for caching

[south arch]

@frigid glen why not just a dictionary?

@frigid glen ok i will use it

why dont you use the setitem function thing so you can do cache[x] = etc

what does cache = await EasyCacheManager.create(
'test'
)

do?

what does the 'test' mean?

[mortal light]

Why's sqlite3 a thing?

So people can just house data locally on their machine to test things?

[frigid glen]

'test' will be the persistent database behind the cache

[south arch]

its a database thing

so each time i want a new cache i do the two lines?

cache = await EasyCacheManager.create(
        'test'
    )

    test = await cache.create_cache('test')

[frigid glen]

One is a Database, the other is a table inside the database, think of it as a separated key/store for 'create_cache'

[frigid glen]

this is a limitation of asyncio, dunder setitem does not work with await syntax

[south arch]

oh

is this faster than a sql database on the pc?

[frigid glen]

reading from cache should always be faster than accessing data on disk

[south arch]

ok

@frigid glen does it work with all datatypes?

[frigid glen]

Data should be serializable, i.e capable of json.dump

[south arch]

doesnt json.dump only work with everything?

not everything

i mean json

like { x: x }

[frigid glen]

Well if you try on datatypes like datetime objects or custom classes, sets, tuples, you will encounter ... issues.

[south arch]

@frigid glen how do i define a cache in my program?

in a function

it says i cant await outside of a function but how do i allow the scope of my program to access it if it i put it in a function itll be out of scope

[mortal light]

I plan on integrating database to a project, would starting off with sqlite be the way?

So I can house data locally for now to test things. When I deploy the project, replace sqlite portion, so it uses database with a server.
And replacing sqlite with database with a server would be easier that way?

And would sqlite replace a csv or an excel file. That we created(write and download) and later used for reading?

[harsh pulsar]

would starting off with sqlite be the way... so I can house data locally for now to test things
reasonable.

would sqlite replace a csv or an excel file
yes.

When I deploy the project, replace sqlite portion, so it uses database with a server
questionable.

databases have a lot of distinct quirks

unless you use an ORM (and even if you use an ORM), you will almost always end up using database-specific-isms

if nothing else, the python client libraries will be different (unless you use ODBC, which you probably don't want to use)

that said, there's not much reason to use a separate database "server" over sqlite if your needs are minor, i.e. you just need 1 database for 1 application and it's all running on a single server.

if you think you need a "real" database server, just start with postgres

run it locally for testing, then host it somewhere for production

[mortal light]

So sqlite isnt hosted, it's local to a machine? And when someone needs to port over that data to some other machine or person, they just give them that data?

But a "real" database with a server is hosted, so anyone can access it

And a bit of tangent, but if someone worked with sqlite, would it be considered the same thing(in terms of skill set/career) as working with a database like SQL or postgres?

[proven arrow]

Depends what kind of skill set your referring too.

If it’s basic working knowledge of how to integrate, and do crud then sure. But generally jobs that require database skills will not give a crap about sqlite

These are mostly dba or data engineer jobs where they will require knowledge of a specific db platform.

[mortal light]

ok that makes sense.

Because if a company has data where a lot of people have read and write access from anywhere, the company would be looking for someone who has worked with a technology that fulfills that need which would be a hosted and deployed db like sql, postgres or nosql

[proven arrow]

Yes they would but how much they expect from the individual depends on what tools that company uses.

Many developer jobs you can get away without Sql knowledge, or how the database works. Especially know when there are orms.

However, my personal view on this is, “learn to love your database”. Because a well designed database will simplify programming and engineering of a software, and smoothes continuous operation and allows easier maintabiility. Learn that I/O is your bottleneck, and poor performance from a software will generally come from a poorly designed database.

[mortal light]

ok I see. Thanks this answered a lot of my questions.

[harsh pulsar]

So sqlite isnt hosted, it's local to a machine
a sqlite database is 1 single file. you interact with the database by using functions in the sqlite C library.

And when someone needs to port over that data to some other machine or person, they just give them that data
yes.

you could put a server in front of sqlite, and there is a piece of software that does this for you, called "sqlitening". their main website is an old-school phpbb forum, too, which is fun: https://www.sqlitening.planetsquires.com/index.php

however sqlite is not good with highly-concurrent writes compared to true client/server databases

[south arch]

why do i get this?

aiohttp.client_exceptions.ClientConnectorError: Cannot connect to host 127.0.0.1:8191 ssl:False [The remote computer refused the network connection]

[solid pewter]

k so @thorn geode I brushed over it rq, I'll go back for a more in depth version later, so how would I implement this into databases for discord.py storage?

[mortal light]

ok thanks!! I will spend some time and think through this!

[eternal blaze]

Today I created my first AWS relational database server (free tier). I began importing a 300 MB CSV file into the MySQL db, and it's still going after 4+ hours.

It's my first day working with any cloud services. Can someone give me advice on uploading data efficiently?

[dense barn]

I'm trying to use asioqlite but it makes my bot really slower, anyway I know why?

[thorn geode]

aiosqlite?

[thorn geode]

Cool!

I'm sure you're aware that discord.py is an async library, so your database library will need to be async as well

Now personally, I recommend one of two database flavours

sqlite(for smaller bots) or PostgreSQL (for larger bots)

Their async equivalents in python are aiosqlite/asqlite or asyncpg

[median wave]

im using SQLITE discord.py, and i get database is locked

how do i fix that?

[wise goblet]

There is WAL mode, try to enable

[median wave]

wdym?

also nvm it works now

[marsh tinsel]

How would i backup and restore 100+ mongodb databases

[harsh pulsar]

you can mongoexport all of them

i believe round-tripping data with mongoexport and mongoimport is pretty reliable, although you should be very careful to specify the right command line flags

check the docs, do a lot of tests runs

[marsh tinsel]

Thanks!

Wait isnt mongoexport for collections

i meant databases

each database has its own collections

[harsh pulsar]

@marsh tinsel i've done this with a script that creates a directory for each database and then a separate json file for each collection

but i don't know how well that scales as a "professional" backup solution

[marsh tinsel]

i mean technically it would be possible to just make a folder for each db in aws and just a backup file there

[oak furnace]

So when you use the SQLite library and query the database does it load the data base in memory or how does it handle it. The reason i ask is the project in working on uses a csv file but when I run the processes on 6 corses and 15 threads loading in that csv takes a crap ton of memory.

[south arch]

how do i start an sql server on my pc?

[young stirrup]

hey folks, off the top of anyone's head, does anyone have any idea to optimize this:

(SELECT transaction_hash AS txhash, topics[SAFE_OFFSET(1)] AS sender, address, data, block_number
    FROM public-data-finance.crypto_polygon.logs
    WHERE topics[SAFE_OFFSET(0)] = "0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef"
    AND topics[SAFE_OFFSET(2)] = "0x0000000000000000000000000000000000000000000000000000000000000000"
    AND DATE(block_timestamp) = "2021-07-02"
    AND block_number > 16408000) UNION ALL (SELECT transaction_hash AS txhash, topics[SAFE_OFFSET(2)] AS sender,
    address, topics[SAFE_OFFSET(3)] AS data, block_number
    FROM public-data-finance.crypto_polygon.logs
    WHERE topics[SAFE_OFFSET(0)] = "0xe6497e3ee548a3372136af2fcb0696db31fc6cf20260707645068bd3fe97f3c4"
    AND DATE(block_timestamp) = "2021-07-02"
    AND block_number > 16408000) ORDER BY block_number DESC

[stray moss]

guys I can't connect to mongodb..

Traceback (most recent call last):
  File "c:\Users\user\Desktop\Capitalism Bot\main.py", line 88, in <module>
    for document in cursor:
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\cursor.py", line 1207, in next
    if len(self.__data) or self._refresh():
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\cursor.py", line 1100, in _refresh
    self.__session = self.__collection.database.client._ensure_session()
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\mongo_client.py", line 1816, in _ensure_session
    return self.__start_session(True, causal_consistency=False)
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\mongo_client.py", line 1766, in __start_session
    server_session = self._get_server_session()
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\mongo_client.py", line 1802, in _get_server_session        
    return self._topology.get_server_session()
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\topology.py", line 496, in get_server_session
    self._select_servers_loop(
  File "C:\Users\user\AppData\Local\Programs\Python\Python39\lib\site-packages\pymongo\topology.py", line 215, in _select_servers_loop
    raise ServerSelectionTimeoutError(
pymongo.errors.ServerSelectionTimeoutError: localhost:27017: [WinError 10061]

[south arch]

why do i get sqlite3.OperationalError: near "SCHEMA": syntax error

when i think its right

[harsh pulsar]

well you could format it to be more readable 😉 but to my eye i don't see anything horrible. indexing for these WHERE conditions could help, if you need to do this query repeatedly and the cardinality is sufficiently high

(
  SELECT
    transaction_hash AS txhash,
    topics[SAFE_OFFSET(1)] AS sender,
    address,
    data,
    block_number
  FROM public-data-finance.crypto_polygon.logs
  WHERE
    topics[SAFE_OFFSET(0)] = "0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef"
    AND topics[SAFE_OFFSET(2)] = "0x0000000000000000000000000000000000000000000000000000000000000000"
    AND DATE(block_timestamp) = "2021-07-02"
    AND block_number > 16408000
)
UNION ALL
(
  SELECT
    transaction_hash AS txhash,
    topics[SAFE_OFFSET(2)] AS sender,
    address,
    topics[SAFE_OFFSET(3)] AS data,
    block_number
  FROM public-data-finance.crypto_polygon.logs
  WHERE
    topics[SAFE_OFFSET(0)] = "0xe6497e3ee548a3372136af2fcb0696db31fc6cf20260707645068bd3fe97f3c4"
    AND DATE(block_timestamp) = "2021-07-02"
    AND block_number > 16408000
)
ORDER BY block_number DESC

there might be specific optimization tricks for specific databases, not sure what those would be

[harsh pulsar]

because it's not right. show the code

[south arch]

no its fine i deleted it

[harsh pulsar]

it doesn't load the entire database in memory. that's part of the point of a database. it doesn't even load the entire query result into memory, unless you explicitly ask it to. otherwise you can load data in batches or even one row at a time

[south arch]

hi

why here:

async def get_point_rules(server_id, cache):
    server_id = int(server_id)
    if server_id in cache:
        return cache.get(server_id)

    async with aiosqlite.connect("core") as connection:
        answer = await connection.execute(f'SELECT ruletable FROM point_rules_table WHERE id={server_id};')
        query = await answer.fetchone()
        if query:
            result = json.loads(query)
            cache.set(server_id, result)
            return result
        else:
            return 0


async def insert_point_rule(server_id, points_dict, cache):
    server_id = int(server_id)
    cache[server_id] = points_dict
    async with aiosqlite.connect("core") as connection:
        cursor = await connection.execute(f'SELECT ruletable FROM point_rules_table WHERE id={server_id};')
        answer = await cursor.fetchone()
        if answer:
            await connection.execute(f"DELETE FROM point_rules_table WHERE id={server_id};")
        await connection.execute(f"INSERT INTO point_rules_table VALUES({server_id}, '{json.dumps(points_dict

async def main():
    await utils.insert_point_rule(5455, {5: 5}, rules_cache)

    print(await utils.get_point_rules(5455, rules_cache))

do i get 0 if the caching system doesnt work

[torn sphinx]

So, i'm trying to learn MongoDB. I'm trying to set it up

import os
import pymongo
from pymongo import MongoClient
from dotenv import load_dotenv

load_dotenv("./.env")
PWD = os.getenv('PWD')

cluster = MongoClient(f"mongodb+srv://Turtle:{PWD}@cluster0.m60hy.mongodb.net/myFirstDatabase?retryWrites=true&w=majority")
db = cluster["database"]
collection = db["test"]

post = {'_id': 0, 'name': 'Thomas', 'score': 5}

collection.insert(post)```this is what I have so far.
it's returning this
```Traceback (most recent call last):
  File "/home/turtle/mongo-practice/pymongo-practice.py", line 11, in <module>
    cluster = MongoClient(f"mongodb+srv://Turtle:{PWD}@cluster0.m60hy.mongodb.net/myFirstDatabase?retryWrites=true&w=majority")
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/mongo_client.py", line 639, in __init__
    res = uri_parser.parse_uri(
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/uri_parser.py", line 428, in parse_uri
    raise ConfigurationError('The "dnspython" module must be '
pymongo.errors.ConfigurationError: The "dnspython" module must be installed to use mongodb+srv:// URIs```
what is dnspython? how can I get rid of this error?

[jaunty galleon]

!pypi dnspython

[delicate field]

dnspython v2.1.0

DNS toolkit

[jaunty galleon]

Maybe try to install it?

[harsh pulsar]

@south arch dont' use f-strings to construct sql queries

#databases message

[torn sphinx]

did that. now it's returning this

  File "/home/turtle/mongo-practice/pymongo-practice.py", line 9, in <module>
    cluster = MongoClient(f"mongodb+srv://Turtle:{PWD}@cluster0.m60hy.mongodb.net/myFirstDatabase?retryWrites=true&w=majority")
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/mongo_client.py", line 639, in __init__
    res = uri_parser.parse_uri(
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/uri_parser.py", line 461, in parse_uri
    raise InvalidURI('Bad database name "%s"' % dbase)
pymongo.errors.InvalidURI: Bad database name "home/turtle/mongo-practice@cluster0"```

[dense barn]

how would i fetch the first 10 rows from postgresql? im good with sql3 and in that you do fetchmany(10), so is there anyway i can do that in postgresql?

[jaunty galleon]

LIMIT 10 in the query ig

[dense barn]

ah

[jaunty galleon]

SELECT * FROM table_name LIMIT 10```

[dense barn]

ok ty

[jaunty galleon]

np

[torn sphinx]

am I not allowed to name my database database?

[dense barn]

@jaunty galleon ok i did

id = await client.pg_db.execute("SELECT author_id FROM testdb LIMIT 1")

but then it sends SELECT 1 instead of my id, what am i doing wrong?

idk im new to psql

[jaunty galleon]

Use fetch

Not execute

[harsh pulsar]

home/turtle/mongo-practice@cluster0 this doesn't look like a valid database uri to me

if your password contains / then you need to URL-encode it

[torn sphinx]

it has a + and a : in it

[harsh pulsar]

i recommend using a proper url handling library like https://pypi.org/project/yarl or https://pypi.org/project/hyperlink to build the URI, or at minimum urllib.parse.urlunparse

[torn sphinx]

but no /

[dense barn]

ah lmfao

[dense barn]

uhh <Record author_id=675414248620294154>
thats what its sending how do i get only the id i tried [0] but didnt work

[jaunty galleon]

data = await conn.fetch('''SELECT col FROM table LIMIT 1''')
print(data[0]['col'])```

[jaunty galleon]

#databases message

[harsh pulsar]

from urllib.parse import quote as urlquote

import hyperlink  # pip install hyperlink


_mongo_username = 'Turt1eByte'
_mongo_password = 'as;ldfkjaw;oj(*$JRP0j'


def get_mongo_uri():
    return hyperlink.URL(
        scheme='mongo+srv',
        host='cluster0',
        path=['myFirstDatabase'],
        query={'retryWrites': 'true', 'w': 'majority'},
        userinfo=f'{_mongo_username}:{urlquote(_mongo_password, safe="")}',
    ).to_text(with_password=True)

[torn sphinx]

ah crap.

[harsh pulsar]

equivalent using yarl

from urllib.parse import quote as urlquote

import yarl # pip install yarl


_mongo_username = 'Turt1eByte'
_mongo_password = 'as;ldfkjaw;oj(*$JRP0j'


def get_mongo_uri():
    return str(yarl.URL.build(
        scheme='mongo+srv',
        host='cluster0',
        path='/myFirstDatabase',
        query={'retryWrites': 'true', 'w': 'majority'},
        user=_mongo_username,
        password=_mongo_password,
    ))

i think hyperlink is a little more "principled", but choose whichever you like

[torn sphinx]

I got a better idea

who do I change my password on mongodb 🤣

[harsh pulsar]

that's not a better idea imo

[hoary pagoda]

I’ve got an app I’m working on for work will have about 20 users that can update certain records .. can SQLite handle this or do I need to use MySQL

[wanton trout]

@harsh pulsar puts helmet on your head

[proven arrow]

You may want to hide the address if it’s your image 😂

[harsh pulsar]

sqlite should be OK as long as there isn't a lot of heavy concurrent writing to the database. and even then, it won't be bad, it just might not be super duper fast

[wanton trout]

oh whops :))

[proven arrow]

Letter was from one of our clients too ahaha 😂

[hoary pagoda]

That’s the only thing I was worried about was concurrent writing the data sets won’t be huge either

[harsh pulsar]

if you use async i wouldn't worry at all

[wanton trout]

Can you make a mistake in their "Database" and add a few extra 000s pls 🙂 @proven arrow

[hoary pagoda]

I’ll check that out … thank you!

[proven arrow]

20 users is not much especially when users won’t be making more than 1 req per second

@hoary pagoda you can have a read of this from their site which might give you a better understanding https://sqlite.org/whentouse.html

[hoary pagoda]

Ok I’ll check it out thanks!

[proven arrow]

I would not be here if I could do that

Would be on my island

[torn sphinx]

can you explain this a little bit to me before I copy and paste this without understanding this first?

[stray moss]

click connect on ur cluster, choose the 2nd option and choose python, then copy the url and fill the password you set for the project

[torn sphinx]

I... have

[stray moss]

u chose this

[torn sphinx]

yes

[stray moss]

then this?

did u copy it

[torn sphinx]

yes

@stray moss

import os
import pymongo
from pymongo import MongoClient
from dotenv import load_dotenv


load_dotenv("./.env")
PWD = os.getenv('PWD')

cluster = MongoClient(f"mongodb+srv://Turtle:{PWD}@cluster0.m60hy.mongodb.net/myFirstDatabase?retryWrites=true&w=majority")
db = cluster["database"]
collection = db["test"]

post = {'_id': 0, 'name': 'Thomas', 'score': 5}

collection.insert(post)```

[serene pivot]

How can I select an array of regexp matches from an entire column? I tried this

SELECT author_id,
(SELECT UNNEST(regexp_matches(content, '<a?:.+?:816463111958560819>')))
FROM messages
WHERE content ~ '<a?:.+?:816463111958560819>'
GROUP BY author_id, content;

But it returns a string of consecutive matches per column. Is there a way to get an array of all the matches or the count of all matches without unnecessary string parsing?

[stray moss]

u have a user there?

[stray moss]

u put the password of that in?

[torn sphinx]

yes. but I was told that some of my characters in the password is causing conflict. someone suggested doing something with url authentication or something?? idk

I suggested changing the password but they were like nah

idk why though

[stray moss]

that shouldn't happen... try making the url an environment variable

cluster = MongoClient(os.getenv("MONGO_CLIENT_URL")
something like this

at least that's mine.

[torn sphinx]

I don't think you understand

my password has a + and a : in it

do you still think that will work?

[stray moss]

just put the entire URL

inside the .env

let it process itself.

[torn sphinx]

wait, by URL...

This is my first day learning Mongo

I know close to nothing about mongo

[stray moss]

actually, it's not a url. just put "mongodb+srv://Turtle:<password>@cluster0.m60hy.mongodb.net/myFirstDatabase?retryWrites=true&w=majority"

cluster = MongoClient(os.getenv("MONGO_CLIENT_URL")

[torn sphinx]

gotchu

[stray moss]

in .env -

MONGO_CLIENT_URL=mongodb+srv://Turtle:<password>@cluster0.m60hy.mongodb.net/myFirstDatabase?retryWrites=true&w=majority

[stray moss]

also, as long as MONGODB allows it, I'm pretty sure they "make sure" the password won't result into an error.

i mean, mongoDB is better than rethink

Dank Memer says so

[torn sphinx]

  collection.insert(post)
Traceback (most recent call last):
  File "/home/turtle/mongo-practice/pymongo-practice.py", line 18, in <module>
    collection.insert(post)
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/collection.py", line 3293, in insert
    return self._insert(doc_or_docs, not continue_on_error,
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/collection.py", line 613, in _insert
    return self._insert_one(
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/collection.py", line 602, in _insert_one
    self.__database.client._retryable_write(
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1497, in _retryable_write
    with self._tmp_session(session) as s:
  File "/usr/lib/python3.8/contextlib.py", line 113, in __enter__
    return next(self.gen)
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1829, in _tmp_session
    s = self._ensure_session(session)
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1816, in _ensure_session
    return self.__start_session(True, causal_consistency=False)
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1766, in __start_session
    server_session = self._get_server_session()
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1802, in _get_server_session
    return self._topology.get_server_session()
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/topology.py", line 496, in get_server_session
    self._select_servers_loop(
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/topology.py", line 215, in _select_servers_loop
    raise ServerSelectionTimeoutError(
pymongo.errors.ServerSelectionTimeoutError: localhost:27017: [Errno 111] Connection refused, Timeout: 30s, Topology Description: <TopologyDescription id: 60df9da6920095d854c7f16f, topology_type: Single, servers: [<ServerDescription ('localhost', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('localhost:27017: [Errno 111] Connection refused')>]>```

it broked

[stray moss]

use insert_one

but the last error is not good

[torn sphinx]

insert_one?

[stray moss]

os.getenv might have returned "None"

[stray moss]

collection.insert_one(post)

tell me the error if there's any

[torn sphinx]

  File "/home/turtle/mongo-practice/pymongo-practice.py", line 18, in <module>
    collection.insert_one(post)
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/collection.py", line 698, in insert_one
    self._insert(document,
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/collection.py", line 613, in _insert
    return self._insert_one(
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/collection.py", line 602, in _insert_one
    self.__database.client._retryable_write(
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1497, in _retryable_write
    with self._tmp_session(session) as s:
  File "/usr/lib/python3.8/contextlib.py", line 113, in __enter__
    return next(self.gen)
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1829, in _tmp_session
    s = self._ensure_session(session)
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1816, in _ensure_session
    return self.__start_session(True, causal_consistency=False)
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1766, in __start_session
    server_session = self._get_server_session()
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1802, in _get_server_session
    return self._topology.get_server_session()
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/topology.py", line 496, in get_server_session
    self._select_servers_loop(
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/topology.py", line 215, in _select_servers_loop
    raise ServerSelectionTimeoutError(
pymongo.errors.ServerSelectionTimeoutError: localhost:27017: [Errno 111] Connection refused, Timeout: 30s, Topology Description: <TopologyDescription id: 60df9e7c37b7692ead6720b6, topology_type: Single, servers: [<ServerDescription ('localhost', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('localhost:27017: [Errno 111] Connection refused')>]>```

[torn sphinx]

I printed out os.getenv. it returned something. not nothing

[stray moss]

it's probably ur password then, try changing it. Because the code says ur using local host

[torn sphinx]

i'm not 100% sure what you mean by that but I will say that i'm not hosting this db locally

[stray moss]

Yeah, the error says ur hosting db locally. Meaning the mongodb thing u put in wasn't valid

[torn sphinx]

... wait i'm an idiot

I never put the mongo_client_url into cluster = MongiClient()

ah but mongo is warning me against that password anyway

so... new result is... nothing?

does this mean it's working?

ah. it does. thanks @stray moss

i'm now your number1 fan'

[stray moss]

lol

[harsh pulsar]

yarl and hyperlink both let you construct and manipulate URL "objects", which can then be turned back into strings, but with the assurance that they're correctly formatted and everything is encoded right. i wrote both of these by reading the docs for the two libraries (although i cheated because i already have used both of them before)

[harsh pulsar]

+1 for asking btw

in this case there isn't much to it

the names scheme, host, etc correspond to standard parts of a URL

[dense barn]

how would i delete something in the postgresql column within pgAdmin?

[wind smelt]

you want to remove a column from a table?

[dense barn]

yes

without using the ide

like delete it using pgadmin

wait

no

i mean something from a column

[wind smelt]

something as in a value inserted into a specific column?

[dense barn]

yes.

[wind smelt]

pgadmin has a query interface

UPDATE table_name SET column_name = NULL WHERE conditional_here

the conditional will specify which row update

[dense barn]

ah so i have to use code

i was hoping i could do it manually

[wind smelt]

i think you can right click on a table and let pgadmin run a SELECT query for you

which will then display the returned data in tables

you can then edit the column directly

[dense barn]

ok so i see a delete button there but it wont let me click it

also that it says read-only column

[crude copper]

is there any reason why Postgres is automatically converting a column I created as date into timestamp?
alter table table_name add column_21 date;
When I go back to check definition of the column, it has been converted to timestamp.
What may I be doing wrong?

alter table table_name alter column column_name type date using tmstamp::date;

Doesn't seem to do it as well.

[dense barn]

im doing this query where it inserts some data in their columns but i get an error
code:

await client.pg_db.execute("INSERT INTO pokesets(user_id,balance,pb,gb,ub,mb,date,catches,encounters,creds,creds2) VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)",(0,1000,50,30,20,2,timestamp,0,0,1,ctx.author.id))

error

Ignoring exception in command credits:
Traceback (most recent call last):
  File "C:\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\25dch\github\pythonbot\PokeSets.py", line 100, in credits
    await client.pg_db.execute("INSERT INTO pokesets(user_id,balance,pb,gb,ub,mb,date,catches,encounters,creds,creds2) VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)",(0,1000,50,30,20,2,timestamp,0,0,1,ctx.author.id))
  File "C:\Python39\lib\site-packages\asyncpg\pool.py", line 530, in execute
    return await con.execute(query, *args, timeout=timeout)
  File "C:\Python39\lib\site-packages\asyncpg\connection.py", line 299, in execute
    _, status, _ = await self._execute(
  File "C:\Python39\lib\site-packages\asyncpg\connection.py", line 1625, in _execute
    result, _ = await self.__execute(
  File "C:\Python39\lib\site-packages\asyncpg\connection.py", line 1650, in __execute
    return await self._do_execute(
  File "C:\Python39\lib\site-packages\asyncpg\connection.py", line 1697, in _do_execute
    result = await executor(stmt, None)
  File "asyncpg\protocol\protocol.pyx", line 183, in bind_execute
  File "asyncpg\protocol\prepared_stmt.pyx", line 129, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
asyncpg.exceptions._base.InterfaceError: the server expects 11 arguments for this query, 1 was passed
HINT:  Check the query against the passed list of arguments.

[burnt turret]

Asyncpg doesn't expect you to pass the parameters as a tuple

[dense barn]

wait what

ah i see

[soft rose]

why is this good practice

c.execute("INSERT INTO employees VALUES (?, ?, ?)", (emp_1.first, emp_1.last, emp_1.pay))

while this is "bad practice"

c.execute("INSERT INTO employees VALUES ('{}', '{}', {})".format(emp_1.first, emp_1.last, emp_1.pay))

emp_1 is just a class with these values

emp_1 = Employee('John', 'Doe', 80000)

[burnt turret]

The second one would be exposing you to SQL injection
#databases message

[soft rose]

oh ok

what about this method?

c.execute("INSERT INTO employees VALUES (:first, :last, :pay)", {"first": emp_2.first, "last": emp_2.last, "pay": emp_2.pay})
conn.commit()

[burnt turret]

That's doing basically the same thing as the one where you were using ? as placeholders

[soft rose]

oh ok

thank you

[dense barn]

How would I insert the current time in postgresql?

[young stirrup]

appreciate it!

[south arch]

wby do i get NoneType from fetchone() in Aiosqlite even though im sure its there in the database?

[austere portal]

Is it ok if I expose my local PostgreSQL uri?

[brazen charm]

I wouldnt recommend it unless you absolutely know what you're doing

If you want to access the remote DB on a server without exposing it directly I would probably recommend using a SSH tunnel

[lofty radish]

FROM "follows" f JOIN "person" n
on f.followeenickname = 'Isotiene' or f.followeenickname = 'Evesonel'
WHERE n.nickname = f.followernickname```
hi, can someone help me pls, i have this db with 2 tables follows table with followee and follower, followee is who the follower follows.
the second table is the user table, and i must simply get the users which follow in this case 'Isotiene' and 'Evesonel' with OR it works and it shows the people which follow 1 or the other, but with AND it doesn't work and idk why, i cant find the persons which follow them both

table follows is with:
followeenickname | followernickname

table person is with:

nickname | firstname | lastname

[unkempt prism]

Your on statement should be your where statement and vise-versa

[lofty radish]

doesn't work

SELECT n.firstname,n.lastname
FROM "follows" f JOIN "person" n
on n.nickname = f.followernickname
WHERE f.followeenickname = 'Isotiene' AND f.followeenickname = 'Evesonel'

gives me and empty table back with and

with OR it gives me all back, also with the double one that follows both

[unkempt prism]

hard to know without the data. thought at lease the on statement is correct now.

[lofty radish]

its 2 tables

[unkempt prism]

I read your discription again and understand your problem.

[lofty radish]

table person

[unkempt prism]

this sql is only ever going to refer to 1 row at a time and the same row can't have the nickname of Isotiene and Evesonel

[lofty radish]

with nickname firstname lastname

ok, i understand

how can i do then to refer to the entire column?

[unkempt prism]

There is a way to restructure your query likely using group.

What db engine are you using. Sqlite , postgres, mysql?

[lofty radish]

postgres

[unkempt prism]

my fav

[lofty radish]

XD

i used the group by in previous querys, but idk how i can put it into join thinghs here

[unkempt prism]

You can use the having clause.

SELECT 
  n.firstname,n.lastname, 
  array_agg(f.followeenickname) 
  FILTER (
    WHERE f.followeenickname in ('Isotiene', 'Evesonel')
  )
FROM "follows" f JOIN "person" n
on n.nickname = f.followernickname
group by 1, 2
having (
  array_length(
  array_agg(f.followeenickname) 
    FILTER (
      WHERE f.followeenickname in ('Isotiene', 'Evesonel')
    )
  )
) > 1

First cut without checking

I'm a bit rusty using this syntax

[lofty radish]

its says text > integer doesnt exist

its for the last line

[unkempt prism]

I probably forgot a closing brace )

[lofty radish]

no, i already added it

[unkempt prism]

I just added a braces around the whole Left hand side equation. It may help

[lofty radish]

its says array_lenght func doesnt exist

[unkempt prism]

my bad its array_length

[grim vault]

I would use a subselect:

SELECT n.firstname, n.lastname
  FROM "person" AS n
  JOIN "follows" AS f ON f.followernickname = n.nickname
 WHERE f.followeenickname = 'Evesonel'
   AND f.followernickname IN (SELECT followernickname
                                FROM follows
                               WHERE followeenickname = 'Isotiene')```

[lofty radish]

works

so u find first the ones with isotiene and after the ones that have evasonel in the isotiene "group"

ok thx for the help guys

[ripe matrix]

Hello, can anyone tell me how to remove the lock? I don't know why I can't edit using double clicking and I assume that's the reason

[solid tundra]

    @commands.Cog.listener()
    async def on_voice_state_update(self, member, before, after):

        category = discord.utils.get(member.guild.categories, name="STAFF SUPPORT")

        if before.channel is None and after.channel is not None:
            if after.channel.id == (ID):
                db = sqlite3.connect("main.sqlite")
                cursor = db.cursor()
                cursor.execute(f"SELECT channel_nb FROM SupportVCs WHERE guild_id = {member.guild.id}")
                result = cursor.fetchone()
                if result is None:
                    num = 1
                    sql = ("INSERT INTO SupportVCs(guild_id, channel_nb) VALUES(?,?)")
                    val = (member.guild.id, num)
                    print("Done")
                if result is not None:
                    sql1 = (f"SELECT MAX channel_nb FROM SupportVCs WHERE guild_id = {member.guild.id}")
                    sql1 += 1
                    sql = ("INSERT INTO SupportVCs(guild_id, channel_nb) VALUES(?,?)")
                    val = (member.guild.id, sql1)
                    print("Done")
                cursor.execute(sql, val)
                db.commit()
                cursor.close()

well this is supposed to get the Biggest number from all rows

but for some reason

I keep getting this error

Ignoring exception in on_voice_state_update
Traceback (most recent call last):
  File "C:\Users\irdio\AppData\Local\Programs\Python\Python38\lib\site-packages\discord\client.py", line 343, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\irdio\OneDrive\Υπολογιστής\The Lost RP\General_Commands\SupportV.py", line 28, in on_voice_state_update   
    sql1 += 1
TypeError: can only concatenate str (not "int") to str

[proven arrow]

does the table have a pk?