databases

[harsh pulsar]

show your code. you need to "commit" after writing data to the database

that is, when you do an INSERT or CREATE TABLE, the data is not actually written until you invoke the .commit() method on the database connection object

or you can use with to commit automatically at the end of the block:

conn = sqlite3.connect('mystuff.db')
with conn:
    conn.execute('CREATE TABLE ...')
    conn.execute('INSERT INTO ...')

[sly pivot]

getting no such table error but the exist

username="tal"
conect=sqlite3.connect("system_roles.db")# connects to databse
c=conect.cursor()# connects to databse
ban_email=c.execute("SELECT email FROM unbanned_user WHERE username=?", (username,))
ban_username=username
ban_PWD=c.execute("SELECT PWD FROM unbanned_user WHERE username=?", (username,))
ban_full_name=c.execute("SELECT full_name from unbanned_user WHERE username=?", (username,))
ban_roles=c.execute("SELECT roles FROM unbanned_user WHERE username=?", (username,))
print(ban_email)
print(ban_username)
print(ban_PWD)
print(ban_full_name)
print(ban_roles)```

[celest fractal]

@sly pivot Do lines 1-5 run without issue?

[sly pivot]

@celest fractal line 5 is the problem

i dont see the problem

[celest fractal]

Does a SELECT * FROM table work?

[sly pivot]

ussualy yes

[celest fractal]

does it work if you do system_roles.tablename?

[harsh pulsar]

did you input this data manually in dbbrowser? if so, make sure you actually saved it

[twilit marlin]

I have a table called EarlyAccessUser. It has the following schema. I want to change the positionInQueue column to a generated column such that it is "the number of users whose virtualPosition is less than the current record's". I wrote the following SQL for it but I am getting an error saying SELECT is invalid at this position.

Using Postgres 14.

What am i getting wrong here?

[silent violet]

How to I store a queue in a database ?

I want to make a library management system where there can be multiple copies of a single book

I want something like:

BOOK COPIES BOOKED BY

bk1 5 P1, P2, P3, P4

[twilit marlin]

use an array https://www.postgresql.org/docs/current/arrays.html

[silent violet]

is it possible in sqlite?

[twilit marlin]

no. you will have to use foreign keys for that i suppose

[silent violet]

okk

[paper flower]

In most cases when dealing with relational data it's better to use foreign keys

[silent violet]

yes. But In this case, since there will be multiple copies of multiple books, wont the number of tables created be huge?

[paper flower]

Not really

You'd just have books bookings and users (if you have users) tables

[main hedge]

Hi

I'm developing a cinema ticketing system

and I am having issue designing the database can someone help me out?

[paper flower]

Sure

[main hedge]

@paper flower So

here's my ER diagram

[severe coral]

So tiny lmao

[main hedge]

ok sorry

let me ss

[paper flower]

Why is there email in Booking?

[main hedge]

so that I can keep track of the number of tickets

the customers purchase

[paper flower]

Ah, right, you use Email as pk

[main hedge]

I need a form to add a new order with the email address of a custome and the number of tickets being ordered (1 by default).

I am not sure how to do it

I m developing on flask

Are u familiar with that?

[paper flower]

Did you consider that different viewings and seats might have different prices?

Depends on time and seat

[main hedge]

The assumption is

one viewing has all the same price

[paper flower]

No, i mostly use FastAPI

To be honest looks ok to me, maybe just fix column naming, that's all

[main hedge]

I dont know how to do a form

[main hedge]

hi

[torn sphinx]

yooo @main hedge u can dm if u want, i have some experience with flask and with sql

[inner hinge]

what is the best practice way to write sqlite queries in python code?
I mean this work well:

def read_sql(self) -> tuple:
       conn_lite = sqlite3.connect(self.db_name)
       lite_cursor = conn_lite.cursor()
       lite_cursor.execute("SELECT * FROM notification_data")
       rows = lite_cursor.fetchall()
       for row in rows:
           return row```

but what if i got long queries like this:
```py
"""CREATE TABLE IF NOT EXISTS notification_data(ID, date, status, inspection, order_number,
    qualification, tester, maschine, commission, notification_status, UNIQUE(ID))"""

Its looking terible at me, and i think there is a more praticle way to do this? 😄

[torn sphinx]

`import sqlite3 as lite

con = lite.connect('user_data.db')

with con:

cur = con.cursor()

cur.execute("CREATE TABLE user_data(UserID TEXT, Cash INT,)")
cur.execute("INSERT INTO user_data VALUES('12031909',10)")`

i have the database created

but how to view the table?

[inner hinge]

Thats very easy, i littlerly showd it one message above.

you need to code something like this:

cur.extecute("SELECT * FROM user_data")
rows = cur.fetchall()
for row in rows:
  print(rows)

i write this in discord, i hope you get it 😄

[torn sphinx]

ok thx

`import sqlite3 as lite

con = lite.connect('user_data.db')

with con:

cur = con.cursor()

cur.execute("CREATE TABLE user_data(UserID TEXT, Cash INT)")
cur.execute("INSERT INTO user_data VALUES('12031909',10)")
cur.execute("INSERT INTO user_data VALUES('51511111',10)")
cur.execute("INSERT INTO user_data VALUES('8841214',10)")`

if user id 12031909 is in table change the specific user cash by +1. how to do this?

[unkempt prism]

Thats just a update statement. If you want to insert though update when the unique constraints conflict that is called a upsert. See https://www.sqlite.org/lang_UPSERT.html

[normal oyster]

!cban 722493336908464180 nsfw

[delicate field]

:incoming_envelope: :ok_hand: applied ban to @solemn pawn permanently.

[main hedge]

Hi

Anyone can help with creating a flask form with conditional validator

[crimson needle]

my company collect lot of data from the field, and with each project this data keeps growing more and more each day
we're thinking of building a simple platform to view all this data,
we want to be able to view all of it, maybe sometimes just select few fields, basically filter out the results as needed
this data includes normal json data and images/videos as well
all the data currently is stored in gcp buckets

now what should we use as a database to store all this data properly so that we can query it from a webapp?
should it be sql db, firestore, fauna-db, I'm really confused on this.
Can someone please advice anything for this 🙏

[drifting monolith]

I don't know if it's best practice but I put all my sql scripts into a variable so it's in a readable format and then use that variable in the execute().

[inner hinge]

Same

[main hedge]

hi

When the form is submitted, the application must check that the customer does exist and the number of the tickets does not exceeds the available capacity for the viewing. If
both conditions are met, the order is registered in the system, otherwise an error message is displayed
to the user. If the customer has already ordered some tickets for the viewing, the number of tickets
specified in the form will be added to their existing order

how do I implement this

[torn sphinx]

        `if message.content.lower() == '>register':
            con = lite.connect('user_data.db')

            with con:
                cur = con.cursor()
                cur.executescript("""
                    DROP TABLE IF EXISTS user_data;
                    CREATE TABLE user_data(UserID TEXT, Cash INT);
                    INSERT INTO user_data VALUES(message.author.id,0)
                    """)```

anyone can help with the message.author.id part? it doesnt work :/

[minor plover]

in postgresql is there a way to get a "list" of an average for a column with a specific filter?
for example if i had:

col1 col2
a    2
a    4
b    3
c    6
c    7
b    4

i want to get this returned:

col1 col2
a    3
b    3.5
c    6.5

[granite stump]

help?

Find the capital and the name where the capital includes the name of the country.

[patent portal]

Hello. I'm trying to design an ERD for a simple password manager. So far there's 2 tables:

1. masterTable
   Columns: email (Primary Key), password

2. userTable
   Columns: email (Foreign Key referencing masterTable), siteName, siteUsername, sitePassword

So my question is, does it make sense to have email as the Foreign Key on userTable?
Cause I realized it looks super redundant to have the same email being shown multiple times when there's a new record.

[oak oyster]

loop through the collection and check if a key is "SOMETHING"

[paper flower]

Primary keys could be anything, people mostly use surrogate keys (numeric ids, snowflakes, uuids)
But it looks like your users wouldn't be able to have multiple passwords?

Since in your passwords table email is a pk

[main hedge]

Hi

When the form is submitted, the application must check that the customer does exist and the number of the tickets does not exceeds the available capacity for the viewing. If
both conditions are met, the order is registered in the system, otherwise an error message is displayed
to the user. If the customer has already ordered some tickets for the viewing, the number of tickets
specified in the form will be added to their existing order
how do I implement this

[runic gulch]

I connect database with input in my html code. I want it to save as a new record the first time and then update it. This is my code

@app.route("/dashboard/<idserwera>/save", methods=['POST', 'GET'])
def save(idserwera):
    db = sqlite3.connect("data.db")
    cursor=db.cursor()
    text = request.form.get('prefix')
    cursor.execute(f"SELECT * FROM prefix WHERE id_serwera = {idserwera}")
    prefix = cursor.fetchall()
    cursor.execute("INSERT INTO prefix(prefix, id_serwera) VALUES(?, ?)",(str(text), int(idserwera)))
    db.commit()
    cursor.close()
    db.close()
    return redirect(f"/dashboard/{idserwera}") 
  ```

[minor plover]

in postgresql is there a way to get a "list" of an average for a column with a specific filter?
for example if i had:

col1 col2
a    2
a    4
b    3
c    6
c    7
b    4

i want to get this returned:

col1 col2
a    3
b    3.5
c    6.5

[lean plover]

Does my many-to-many join between these two models look correct? This is using flask_sqlalchemy```py
class Person(db.Model):
tablename = 'document_review'
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String, nullable=False)
last_name = db.Column(db.String())
email = db.Column(db.String(), nullable=False)
project_role = db.Column(db.String(), nullable=False)
review_role = db.Column(db.String(), nullable=False)
username = db.Column(db.String(), unique = True)
password = db.Column(db.String(60))
document_reviews = db.relationship('DocumentReview', secondary = 'review_person_join') #this line

def __repr__(self):
    return f"Person('{self.first_name}', '{self.lastname}', '{self.email}', '{self.project_role}'," \
           f" '{self.review_role}', '{self.username}')"

class DocumentReview(db.Model):
tablename = 'document_review'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String())
project = db.Column(db.String(), nullable=False)
type = db.Column(db.String(), nullable=False)
date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
review_end = db.Column(db.DateTime, nullable=False)
participants = db.relationship('Person', secondary = 'review_person_join') #this line
submittal_number = db.Column(db.String())
submittal_revision_number = db.Column(db.String())
notes = db.Column(db.Text)
files = db.Column(db.Integer)

def __repr__(self):
    return f"Submittal('{self.submittal_number}', '{self.project}', '{self.name}', '{self.project}')"

class ReviewPersonJoin(db.Model):
tablename = 'review_person_join'
review_id = db.Column(db.Integer, db.ForeignKey('DocumentReview.id'), primary_key = True)
person_id = db.Column(db.Integer, db.ForeignKey('Person.id'), primary_key = True)

Also if I want to add the reviewers to the DocumenReview repr definition, how would I do that?

[paper flower]

Use a group by and avg

[minor plover]

yep got that figured out, but thank you

[earnest maple]

Anyone has tried to create an user defined function in SQL server using pyodbc? I’m trying to create a simple function but nothing happens!

[main hedge]

Hi

Hi, I’m halfway implementing a flask sql project and having difficulty with this

When the form is submitted, the application must check that the customer does exist and the number of the tickets does not exceeds the available capacity for the viewing. If
both conditions are met, the order is registered in the system, otherwise an error message is displayed
to the user. If the customer has already ordered some tickets for the viewing, the number of tickets
specified in the form will be added to their existing order
how do I implement this

[bitter pond]

has anyone tried

supabase

and

firebase

?

whats your opnion on them

[torn sphinx]

I used this one, I liked it easy to set-up and works well

[main hedge]

Hi

[bitter pond]

ok

tahts

interetesitng

[main hedge]

Hi, I’m halfway implementing a flask sql project and having difficulty with this

When the form is submitted, the application must check that the customer does exist and the number of the tickets does not exceeds the available capacity for the viewing. If
both conditions are met, the order is registered in the system, otherwise an error message is displayed
to the user. If the customer has already ordered some tickets for the viewing, the number of tickets
specified in the form will be added to their existing order
how do I implement this

[blissful finch]

Hi there, I have a question about structures. I'm not exaclty looking for code, although it might help as well. This schema made on paint is what I would like to do. I currently have one Main table with several columns. From that I'd to create two new tables, one that would take directly from there to create relationship between name, and a second one that would give me information about the relationship (it would be a family tree, so it's more about the style of the arrow and connections), but I'm not sure if I am supposed to simply create two brand new table or can I make that inside the Main file I already have

[main hedge]

Hi anyone can help me

[blissful finch]

You could do a SELECT statement, with the columns you need, and then a result = cursor.fetchall(). So if the customer doesn't have any tickets yet the result would be empty, so if that's empty you can create a profile with an INSERT, otherwise, you just UPDATE the value and then db.commit() to commit the changes to the database

[main hedge]

@blissful finch Can u help?

[blissful finch]

you could also do if you need the total number of tickets, fetch all the customer's ids with their tickets number, fetchall, so you have a list of tuples, check the total number of tickets by doing something like:

total = 0
for e in result:
  total += e[1]

So I used the index 1 but it depends how you selected your row, basically the fetchall returns a list of tuples, and those tuples contain the column value, so if you did SELECT customer, tickets the tickets are in position 2 so the index is 1 just like a list

[main hedge]

@blissful finch Do u have an hour or smth

[blissful finch]

no I am already working on something

[main hedge]

I have been stuck for 2 days

I have a code

[blissful finch]

I just gave you a way to do that

[main hedge]

but idk why it doesnt work

I did that

but the form doesnt shwo up

[blissful finch]

???

[cerulean yacht]

wouldnt be easier to fetch count() from DB rather than ask python to do this? would reduce overhead

[main hedge]

@cerulean yacht U have time to look thru my code?

[blissful finch]

well I don't know all the functions so Yeah if that exists yes

[blissful finch]

can't you send it here ?

[main hedge]

Yeah but its v long

[blissful finch]

!hastebin

[main hedge]

This my schema

https://www.toptal.com/developers/hastebin/uhutepemun.py

[cerulean yacht]

i could have a quick look

[main hedge]

https://www.toptal.com/developers/hastebin/agalifizon.js

This is my html

[cerulean yacht]

um

[main hedge]

U have an hour or smth @cerulean yacht

[cerulean yacht]

credentials etc

[main hedge]

It's gonna take some time

[cerulean yacht]

dotn post them

you just posted your secret key

in that snippet

[main hedge]

It's ok

[cerulean yacht]

change it

[main hedge]

It's just a practice

I m not gonna deploy it or anything

it's just my own personal proejct after learning from Udemy

[cerulean yacht]

and whats the problem, form doesnt show after deployment?

[main hedge]

yeah

I m just using local host

the form doesnt show

and anyway what I want to do is

When the form is submitted, the application must check that the customer does exist and the number of the tickets does not exceeds the available capacity for the viewing. If
both conditions are met, the order is registered in the system, otherwise an error message is displayed
to the user. If the customer has already ordered some tickets for the viewing, the number of tickets
specified in the form will be added to their existing order

@cerulean yacht u thr?

[cerulean yacht]

im at work, bare with me

need to have a look at html now

[main hedge]

ok thanks

I have been strugging for 2 days

😦

[cerulean yacht]

honestly the way you structure your code is so weird, i mean you really have just one file for that python code and this is really hwo the html is structured? its soo weird and makes it more difficult to follow everything

also, why are you using a table for that booking ticket?

[main hedge]

The goal is

to have a table

[cerulean yacht]

my suggestion would be to have a look at a framework like bootstrap for instance, it gives you ready to work with sollutions to create forms, buttons etc and everything works like magic

[main hedge]

and then below the table to have a form

Yeah but I want to settle the functionality before using bootstrap

Feel free to restructure

Can u help me with that? Just show me how u will use boostrap to do the form and the validation

[cerulean yacht]

let me put it this way, i dont have time to do this for you unfortunatelly, however, if you just google bootstrap 5 or bootstrap and flask, you will get plenty of resources and guides on how to do this

gtg

good luck

[halcyon cobalt]

What's the Difference Between Top Down Approach and Bottom Up Approach In Database SQL ?

I looked On google but I am still not Understanding the details.

Could anyone please Explain in detail ?

[blissful finch]

doesn't it have to do with binary trees ?

I think I saw that in computer science last year

[main hedge]

Hey

[median wave]

how does one manually add records to a sql3 table?

[devout pilot]

How do I delete a role if two columns are the same as two variables

[harsh pulsar]

DELETE ... WHERE ...?

[harsh pulsar]

this is db browser? there should be a button in the toolbar, maybe the one with the "+" in a green circle

[devout pilot]

I mean like two conditions are met, if you get what I mean

[harsh pulsar]

right. DELETE allows you to specify arbitrary conditions with WHERE

[devout pilot]

Would it be smth like this

cursor.execute("DELETE FROM table WHERE id = 6, name = john")

[median wave]

i found it thank you

[torn sphinx]

Ive made it so when a user renames a ticket to {user}-done my database updates their count by 1, however, i recently reset my bots token and now that doesnt work. But, other parts of the database works, like adding users to the database and so forth. Here's my code:

users = sorted(users.items(), key=lambda x: x[1], reverse=True)
for i in users:
  mycursor.execute(f"SELECT * FROM logs_info")
  for i2 in mycursor:
    if str(i2[1]) == str(i[0]):
      NewCount = int(i2[2])+int(i[1])
  mycursor.execute(f"UPDATE logs_info set count = '{NewCount}' WHERE username = '{i[0]}'")
  db.commit()
mycursor.close()
db.close()

[main hedge]

hI

any kind soul can help with flask form validation?

[harsh pulsar]

use AND, not , -- it's the same as SELECT ... WHERE

[devout pilot]

oh ty

[harsh pulsar]

that is, WHERE accepts one single expression

so you can use the usual AND and OR boolean operators

[devout pilot]

makes sense

[devout pilot]

i have

cursor.execute(f"DELETE FROM apps WHERE id = {userid} AND role = Hoster")

and i get the error

sqlite3.OperationalError: no such column: Hoster

i don't understand why i get the error

[harsh pulsar]

!sql-f-strings

[delicate field]

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

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

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

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

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

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

[devout pilot]

i see ty

[lean plover]

Does my many-to-many join between these two models look correct? This is using flask_sqlalchemy```py
class Person(db.Model):
tablename = 'document_review'
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String, nullable=False)
last_name = db.Column(db.String())
email = db.Column(db.String(), nullable=False)
project_role = db.Column(db.String(), nullable=False)
review_role = db.Column(db.String(), nullable=False)
username = db.Column(db.String(), unique = True)
password = db.Column(db.String(60))
document_reviews = db.relationship('DocumentReview', secondary = 'review_person_join') #this line

def __repr__(self):
    return f"Person('{self.first_name}', '{self.lastname}', '{self.email}', '{self.project_role}'," \
           f" '{self.review_role}', '{self.username}')"

class DocumentReview(db.Model):
tablename = 'document_review'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String())
project = db.Column(db.String(), nullable=False)
type = db.Column(db.String(), nullable=False)
date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
review_end = db.Column(db.DateTime, nullable=False)
participants = db.relationship('Person', secondary = 'review_person_join') #this line
submittal_number = db.Column(db.String())
submittal_revision_number = db.Column(db.String())
notes = db.Column(db.Text)
files = db.Column(db.Integer)

def __repr__(self):
    return f"Submittal('{self.submittal_number}', '{self.project}', '{self.name}', '{self.project}')"

class ReviewPersonJoin(db.Model):
tablename = 'review_person_join'
review_id = db.Column(db.Integer, db.ForeignKey('DocumentReview.id'), primary_key = True)
person_id = db.Column(db.Integer, db.ForeignKey('Person.id'), primary_key = True)

Also if I want to add the reviewers to the DocumenReview repr definition, how would I do that?

[finite cedar]

pymongo.errors.ServerSelectionTimeoutError: alethe-shard-00-02.gsbd5.mongodb.net:27017: connection closed,alethe-shard-00-00.gsbd5.mongodb.net:27017: connection closed,alethe-shard-00-01.gsbd5.mongodb.net:27017: connection closed, Timeout: 30s, Topology Description: <TopologyDescription id: 6261cda540327c72d529e0e8, topology_type: ReplicaSetNoPrimary, servers: [<ServerDescription ('alethe-shard-00-00.gsbd5.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('alethe-shard-00-00.gsbd5.mongodb.net:27017: connection closed')>, <ServerDescription ('alethe-shard-00-01.gsbd5.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('alethe-shard-00-01.gsbd5.mongodb.net:27017: connection closed')>, <ServerDescription ('alethe-shard-00-02.gsbd5.mongodb.net', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('alethe-shard-00-02.gsbd5.mongodb.net:27017: connection closed')>]>

What does this mean when I try to call my MongoDB collection over Django? I used Flask and it was the same error.

[quick spruce]

Hey guys I want to make a python bot to get the links to the top 100 videos on the youtube trending page, then extract the title and keywords data for each of those videos. My knowledge of python is relatively limited but I think I could be able to do this. The thing I really need help with is the top 100 videos part. Any help appreciated

[torn sphinx]

You could probably use bs4 to scrape the data and then fetch certain tags

Look at the bs4 documentation

[quick spruce]

beautiful soup 4.9?

that it?

[torn sphinx]

Yeah

[quick spruce]

Thanks I'll look into it

[torn sphinx]

Yeah no problem you might need to use some user-agent and a proxy cause YouTube probably blocks web scraping

But you can try without and tell me what happens

[quick spruce]

nah it has its own api

you can access the data free

tried with c# but it was a pain

[torn sphinx]

Oh yeah true

[quick spruce]

oh also nice profile can't wait til it actually exists in europe

[torn sphinx]

Yeah they need to bring it to Europe

[quick spruce]

wait is crummy. com the website i should be on for the bs4 documentation?

it just looks a bit sketch

[remote latch]

cursor.execute(f"INSERT INTO player VALUES({message.author.id},start,{relations})")```
why does this give the error sqlite3.OperationalError: unrecognized token: "{"

[main hedge]

Hi
how can i connect sqllite file using sqlachemly

[austere veldt]

This feels like it should be a simple SQL problem, but I don't seem to know the tools to do it. I'm using Access (which has extra tools but also can just execute arbitrary SQL).

I have this table with data of the weights of some part in different materials and sizes. The information that I want is the heaviest possible and lightest possible weight for a given size.

I know how to make a calculated column but I'd need the table to have the rows and columns swapped for that to work

Conceptually, I want a couple of calculated rows?

[round valley]

Anyone know if it's possible to use SQLAlchemy with mariadb+mariadbconnector and allow for Windows authentication to be the auth method? Our operations team have set MariaDB using GSSAPI for AD credentials. Just wanting to connect now without needing to provide credentials.

Alternatively, is it possible to connect using pyodbc so that we can use Trusted_Connection=yes?

[nova forge]

Any1 using pooling in aiomysql?

[main hedge]

Hi

How do I connect my sqlite file using sql alchemy?

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SECRET_KEY"] ="-"
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'
db = SQLAlchemy(app)

from cenema import routes

This is what I have
my sqlite file is called
test.sqlite3
How can I query a table from my db?

[ebon skiff]

Is making a pool recommended?

Using pg + discord bot

[gilded onyx]

so

[devout pilot]

!sql-f-strings

[delicate field]

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

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

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

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

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

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

[nova forge]

Any1 using pooling in aiomysql?

[mild merlin]

hey i have this code and get this error. ```
mycursor.execute(f"SELECT name FROM users WHERE name = %s", (user.name,))
xd = mycursor.fetchone()
lolo = int(xd[0])
print(lolo)

lolo = xd[0]
TypeError: 'NoneType' object is not subscriptable

[grim vault]

.fetchone() returns None if the select has no result. In this case it means the user is not found. Also, looking at the code you select the column name where it is equal to user.name. The result can only be the name you already know or None.

[tender salmon]

@bot.command()
@commands.has_any_role(692760082085183519, 940008547993927691, 863878825376743475, 902292952381001779, 863879069253894166, 902292952381001779, 863879304110276668, 940008547993927691)
async def пред(ctx, member: discord.Member = None, *, reason = 'Отсутствует'):
    print(45)
    cursor.execute("""CREATE TABLE IF NOT EXISTS warning(guild_id BIGINT, user_id BIGINT, warn INT, count INT, moderator_id BIGINT, reasons VARCHAR)""")
    base.commit()
    print(1)
    
    warnings = cursor.execute("SELECT * FROM warning WHERE user_id = ? AND guild_id = ?", (member.id,ctx.guild.id)).fetchone()
    print("работает")
    
    
    if member is None:
        await ctx.send("Выберите участника")
        return
        
    if warnings is None:
        cursor.execute('INSERT INTO warning(guild_id, user_id, warn, count, moderator_id, reasons) VALUES(?, ?, ?, ?, ?, ?)', (ctx.guild.id,member.id,0,1,ctx.author.id,'Отсутствует'))
        base.commit()
        print(2222)
        cursor.execute(f'UPDATE warning SET warn = warn + 1 WHERE user_id = ? AND guild_id = ?', (member.id, ctx.guild.id))
        base.commit()
        await ctx.send(f"{ctx.author.name} Выдал предупреждение #{warnings[2]} {member} (случай # ) {reason}")
    else:
        cursor.execute(f'UPDATE warning SET warn = warn + 1 WHERE user_id = ? AND guild_id = ?', (member.id, ctx.guild.id))
        base.commit()
        await ctx.send(f"{ctx.author.name} Выдал предупреждение #{warnings[2]} {member} (случай # ) {reason}")      

How to make it so that if the reason was not specified, then Отсутствует was not written at the end, and if the reason was specified, then this was entered into the database and the reason was shown in the chat. Help me plz.

[late tinsel]

What would be the best way to store dynamic form data in sql server? In essence, I was thinking of doing EAV, but I feel like this can get very messy very quick, especially if I want to store a list. For example, the one form may have 5 fields, where 1 is a list of rows that are added to the form. So lets say I allow them to edit 1 row, and save just that update, i would need to store some form of identifier and calculate it. I can also do a json column, to store all the fields as realistically no form should be larger than the limit of navchar(max) anyway, however then I am struggling to see how I would define in sqlalchemy to lets say only edit one value in the json, without having to replace the whole json every time if that makes sense. Would anyone have any docs/tutorials they could recommend for this?

[trim lintel]

EAV would be better solution for this over json Column

[hot trellis]

Hello, I was wondering if someone could help me, I am looking to start using databases because I have been using json files as dbs before this since it was the only way I know how to do things (yes, I know how bad and whatever this is) so I was looking for some very easy/beginner dbs I could use, as of now, all I need it for is storing discord bot data, more specifically, dictionaries/groupings, integers and strings, if anyone has any suggestions, please ping reply me any that you feel are good (also, I am pretty broke/poor, so if it's free, bonus) And one last thing. I need this to work with ssh and stuff (I have a server and obv this computer that will need to access this db)
Example of the data I am trying to store:```json
{
"000000000000000000": {
"000000000000000000": {
"Author": 000000000000000000,
"Content": "test",
"Out Message ID": 000000000000000000
}
},
}

[paper flower]

Just look for any sql tutorials 🤔

[ionic smelt]

#discord-bots message
@haughty tree what's your schema

[haughty tree]

hi, im currently using sqlite3 and am having some troubles with this inner join, it displays the member_id twice in the output. Can anyone help?

https://cdn.discordapp.com/attachments/343944376055103488/967235663491629156/unknown.png

[waxen finch]

it seems that if you join with the ON clause, sqlite won't know to select only one of the member_id columns from both tables; try the USING clause instead

[paper flower]

You're selecting from both tables and you're selecting a single row only

since you're using fetchone

And member_id is present twice in your result because there's member_id in both tables

Otherwise you wouldn't be able to join them

[neat parrot]

Hello, I would like your opinion on my situation
I have a problem, I can't manage the none of the api, sometimes I have a registration + the model of the aircraft and others one out of two or neither and on top of all that I have this kind of thing:

Thanks in advance

@api.route('/azure', methods=['POST'])
def write():
    db = connect()
    cursor = db.cursor()
    req = request.json["response"]
    # cursor.executemany("INSERT INTO `plane` (aircraft_icao, reg_number) VALUES (%s, %s);", req)
    
    for i in req:
        print(i)
        try:
            plane = "INSERT INTO `plane` (aircraft_icao, reg_number) VALUES ('"+i["aircraft_icao"]+"','"+i["reg_number"]+"');"
            cursor.execute(plane)
        except Exception as e:   
        # except Error as e:
            print(e)

[main hedge]

anyoen can help me out help-mushroom TypeError: 'ProgrammingError' object is not subscriptable

[paper flower]

1. I would use a context manager for your connection (presumably db = connect())
2. Don't use string interpolation/concatenation with sql, you've just created a sql injection vulnerability in your code! Use parameter substitution, it's described in sqlite documentation: https://docs.python.org/3/library/sqlite3.html#module-sqlite3

Also i would probably not wrap your operation into try-except to allow it to fail and return 500 code

[neat parrot]

Thanks for your answer I'll apply this, but in my case How can I avoid outbound properties?

[paper flower]

What's an outbound property?

[neat parrot]

I don't know what to call it, I have fields on the outside like in the screenshot

[paper flower]

I'm not sure, seems like request was sent in such format?

[neat parrot]

yes

I made an api request and I received the data and I would like to put them in my azure database

[paper flower]

You basically just need some validation, i mostly use FastAPI, it integrates with pydantic:

class PlaneCreate(BaseModel):
    reg_number: str
    aircraft_icao: str


@app.post("/azure")
def plane_create(planes: list[PlaneCreate]):
    for plane in planes:
        ...

Never really worked with flask

[forest gale]

    con = sqlite3.connect('main.db')
    cur = con.cursor()
    cur.execute("SELECT * FROM main")

    for i in cur.fetchall():
      if after.name == "test-done":
        test = i[0]
        test += 1
        await after.delete()```
Is this correct? Because it is not working

[paper flower]

I'm not sure what you're doing there

[forest gale]

well isn't it correct tho?

its just a tracker

[neat parrot]

thx very much I don't find in the doc of how do this with fastapi:

req = request.json["response"]

api = FastAPI()

def connect():
    conn = sqlite3.connect(database='', port=, host='ysql.database.azure.com', user='', password='',ssl_ca="{ca-cert filename}", ssl_disabled=True)
    log.basicConfig(level=log.DEBUG, format='%(asctime)s %(levelname)s:\n%(message)s\n')              
    print("Connexion réussie")
    return conn

class PlaneCreate(BaseModel):
    reg_number: str
    aircraft_icao: str

@api.post('/azure')
def plane_create(planes: list[PlaneCreate]):
    conn = sqlite3.connect(database='')
    cursor = conn.cursor()
    for plane in planes:
        print(plane)
    try:
        p = ('"+plane["aircraft_icao"]+"','"+plane["reg_number"]+"')
        cursor.executemany('INSERT INTO `plane` VALUES (?,?)', p)
        # except Exception as e:
    except Error as e:
        print(e)


    req = request.json["response"]
    
   # return jsonify(req)  

[neat parrot]

@paper flower
in my scrapper.py

def response_s(self):
       response = requests.post(self.urlConnectStr, json = json.loads(self.req.content))
       

[torn sphinx]

does json count as database?
I mean can I ask json questions here?

[tender salmon]

@bot.command()
@commands.has_any_role(692760082085183519, 940008547993927691, 863878825376743475, 902292952381001779, 863879069253894166, 902292952381001779, 863879304110276668, 940008547993927691)
async def пред(ctx, member: discord.Member = None, *, reason = 'Отсутствует'):
    print(45)
    cursor.execute("""CREATE TABLE IF NOT EXISTS warning(guild_id BIGINT, user_id BIGINT, warn INT, count INT, moderator_id BIGINT, reasons VARCHAR)""")
    base.commit()
    print(1)
    
    warnings = cursor.execute("SELECT * FROM warning WHERE user_id = ? AND guild_id = ?", (member.id,ctx.guild.id)).fetchone()
    print("работает")
    
    
    if member is None:
        await ctx.send("Выберите участника")
        return
        
    if warnings is None:
        cursor.execute('INSERT INTO warning(guild_id, user_id, warn, count, moderator_id, reasons) VALUES(?, ?, ?, ?, ?, ?)', (ctx.guild.id,member.id,0,1,ctx.author.id,'Отсутствует'))
        base.commit()
        print(2222)
        cursor.execute(f'UPDATE warning SET warn = warn + 1 WHERE user_id = ? AND guild_id = ?', (member.id, ctx.guild.id))
        base.commit()
        await ctx.send(f"{ctx.author.name} Выдал предупреждение #{warnings[2]} {member} (случай # ) {reason}")
    else:
        cursor.execute(f'UPDATE warning SET warn = warn + 1 WHERE user_id = ? AND guild_id = ?', (member.id, ctx.guild.id))
        base.commit()
        await ctx.send(f"{ctx.author.name} Выдал предупреждение #{warnings[2]} {member} (случай # ) {reason}")       

How to make it so that if the reason was not specified, then Отсутствует was not written at the end, and if the reason was specified, then this was entered into the database and the reason was shown in the chat. Help me plz.

[opaque surge]

Hi, I am trying to install postgrel in my laptop, Windows 10
but i keep getting the "Unable to write inside TEMP environment variable path"
I looked up online and found solutions saying that i should enable a file inside HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows Script Host\Settings
i did but the issue is still there

[hot trellis]

Could anyone help me, I have never used any databases before, I have just just just set up mysql and a database (after an embarrassingly long time) and I need to convert my json files to the database. Basically, I need this database to be split so that there are 2 sections, one for storing simple info (a unique user id, points, coins, medals, etc) then I will need one to store stuff to this degree:```json
{
"000000000000000000": { //unique channel id
"000000000000000000": { //unique (I think) message id
"Author": 000000000000000000, // Author (not-so-but-still unique id
"Content": "test", //message content (string up to 4000 characters)
"Out Message ID": 000000000000000000 //unique (I think) message id
},
"000000000000000000": { //unique (I think) message id
"Author": 000000000000000000, // Author (not-so-but-still unique id
"Content": "test", //message content (string up to 4000 characters)
"Out Message ID": 000000000000000000 //unique (I think) message id
}
},
"000000000000000000": { //unique channel id
"000000000000000000": { //unique (I think) message id
"Author": 000000000000000000, // Author (not-so-but-still unique id
"Content": "test", //message content (string up to 4000 characters)
"Out Message ID": 000000000000000000 //unique (I think) message id
}
}
}

If anyone could help me just set up the database in this way, I would be so so so appreciative, but, any help is appreciated

[rare dew]

hi

[paper flower]

Anything counts as database, my sheet of paper can be a database however neither of them is scalable or really useable

[warm locust]

why isnt that aligned?

[main hedge]

any kind soul familiar with SQL and Flask can help me debug, I'm making a movie booking system and things r wrong somewhere need a pair of eyes to help me out

[willow tendon]

in a sqlite3 file, ive seen it sometimes be named as .db and sometimes as .sqlite3
which one should be used?

[paper flower]

Any file extension could be used

.my_cool_database for example 🙂

Though .db and .sqlite3 are more common

[willow tendon]

would a sqlite3 database work as normal when deployed for a webapp on a vps like linode?

[paper flower]

If you host only one instance of your app - yes

I host multiple instances of my applications using docker swarm, so they need a different db (we use postgres) since they're located on different machines

[willow tendon]

what do you mean by multiple instances of an app?

[paper flower]

You just run multiple instances of your application so they could handle more requests 🙂

[willow tendon]

ok thanks

[paper flower]

I'd recommend using something like postgresql though

But i have heard that sqlite can handle a lot of queries too

[haughty tree]

eyy got it working now. Thanks so much!

[tight breach]

What's happening is in box office each row carries a relationship to a movie in the box office.movies_id and then that has the domestic sales and international sales column associated with the particular movie.

SQL isn't actually just checking "from movies" alone
Because of the "join" statement, it's also checking box office for those columns by joining the box office table and the movie table using the movie ID.

I don't know if this explanation is clear enough

[plucky shoal]

can anyone help me with sql

[thorny sluice]

hi guys. how could I describe a variable which represents the subsequent items of its parent ?

e.g subInstallationSite -> installationSite

just 'sub' maybe a little simple or not graceful I think.

I'm not a native English speaker so I'm kind of confused.

[main hedge]

Anyone with flask and SQL experience can help me debug, I'm building a movie booking system and it is 90% done but am having minor issue and I cant seem to find what's wrong #☕help-coffee

[torn sphinx]

    @welcome.group()
    async def test(self, ctx, text):
        db = sqlite3.connect('cupid.db')
        cursor = db.cursor()
        cursor.execute(f"SELECT message FROM welcome WHERE guild_id = {ctx.guild.id}")
        data = cursor.fetchone()
        if data is None:
            await ctx.send('there is no welcome message setup')
        if data:
            

i want to grab the message from the database and send it as a ctx.send(message), how do i do this?

[paper flower]

Your code seems to be ok, except one thing: don't use f-strings with sql

What's the problem?

[torn sphinx]

already fixed this problem, still thanks, currently working on a different problem tho.

if u could help me with it, that would be super

    @welcome.group()
    async def add(self, ctx, channel, *, text):
        db = sqlite3.connect('cupid.db')
        cursor = db.cursor()
        cursor.execute('SELECT VALUES(?, ?) FROM welcome WHERE guild_id = ?', (channel.id, text, ctx.guild.id))
        data = cursor.fetchone()
        if not data:
            cursor.execute('INSERT INTO welcome(guild_id, channel_id, message) VALUES(?, ?, ?)', (ctx.guild.id, channel.id, text))
            await ctx.send(f'**welcome message** created for {channel.mention} using a **text message** 👍')
        if data:
            await ctx.send(f'there is already an **existing message** for {channel.mention}')

im trying to make it so i can do ,welcome add #channel welcome message

[torn sphinx]

already fixed the fstring thing, also purely because it doesn't look as nice haha

[paper flower]

You probably want to use context manager with your connection tho

Also you can reduce nesting a bit by processing the outlying case first (in your case if welcome message already exists) and adding early return

    @welcome.group()
    async def add(self, ctx, channel, *, text):
        db = sqlite3.connect('cupid.db')
        cursor = db.cursor()
        cursor.execute('SELECT VALUES(?, ?) FROM welcome WHERE guild_id = ?', (channel.id, text, ctx.guild.id))
        data = cursor.fetchone()
        if data:
            await ctx.send(f'there is already an **existing message** for {channel.mention}')
            return
        cursor.execute('INSERT INTO welcome(guild_id, channel_id, message) VALUES(?, ?, ?)', (ctx.guild.id, channel.id, text))
        await ctx.send(f'**welcome message** created for {channel.mention} using a **text message** 👍')

[torn sphinx]

wdym

[torn sphinx]

also the problem i keep running into with my code is: ```
Ignoring exception in on_command_error
Traceback (most recent call last):
File "C:\Users\ego\anaconda3\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
ret = await coro(*args, **kwargs)
File "C:\Users\ego\Documents\Development\Discord\murder\cogs\configuration.py", line 90, in add
cursor.execute('SELECT VALUES(?, ?) FROM welcome WHERE guild_id = ?', (channel.id, text, ctx.guild.id))
AttributeError: 'str' object has no attribute 'id'

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

Traceback (most recent call last):
File "C:\Users\ego\anaconda3\lib\site-packages\discord\client.py", line 343, in _run_event
await coro(*args, **kwargs)
File "C:\Users\ego\Documents\Development\Discord\murder\cogs\events.py", line 40, in on_command_error
raise error
File "C:\Users\ego\anaconda3\lib\site-packages\discord\ext\commands\bot.py", line 939, in invoke
await ctx.command.invoke(ctx)
File "C:\Users\ego\anaconda3\lib\site-packages\discord\ext\commands\core.py", line 1348, in invoke
await ctx.invoked_subcommand.invoke(ctx)
File "C:\Users\ego\anaconda3\lib\site-packages\discord\ext\commands\core.py", line 1342, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\ego\anaconda3\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'str' object has no attribute 'id'

[paper flower]

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

Ah, it doesn't even automatically close the connection

[torn sphinx]

It just automatically commits, not closes

[paper flower]

I guess channel or ctx.guild is a string here

[grim vault]

for a bot an async version would also be better.

[torn sphinx]

I already did channel: discord.Channel, but now it's not updating my db

    @welcome.group()
    async def add(self, ctx, channel: discord.TextChannel, *, text):
        db = sqlite3.connect('cupid.db')
        cursor = db.cursor()
        cursor.execute('SELECT channel_id, message FROM welcome WHERE guild_id = ?', (ctx.guild.id,))
        data = cursor.fetchone()
        if not data:
            cursor.execute('INSERT INTO welcome(guild_id, channel_id, message) VALUES(?, ?, ?)', (ctx.guild.id, channel.id, text))
            await ctx.send(f'**welcome message** created for {channel.mention} using a **text message** 👍')
        if data:
            await ctx.send(f'there is already an **existing message** for {channel.mention}')

any idea?

[paper flower]

commit

[grim vault]

db.commit()

[torn sphinx]

right

thank you

[grim vault]

you can also use else: instead of if data: because it's the opposite of if not data:

[paper flower]

^

[torn sphinx]

i know, but i like it like this more because then i know where i need to look if i need to fix something, because this is more clearer for me

did this with a reason, still thanks though!

[main hedge]

Anyone with flask and SQL experience can help me debug, I'm building a movie booking system and it is 90% done but am having minor issue and I cant seem to find what's wrong

Anyone with flask and SQL experience can help me debug, I'm building a movie booking system and it is 90% done but am having minor issue and I cant seem to find what's wrong #help-lemon

[worn heart]

TypeError: index 'whitelisted' cannot be applied to Cursor instances
``` mongoDB

[compact warren]

Im currently working on a application which contains multiple services using a sqlite3 database, theyre all using the same conn object. Should i use Sqlalchemy or sqlite3 (which i normally use)? Whats the better practice?

[paper flower]

They're different things, you can use sqlalchemy with sqlite

[compact warren]

What is the difference?

[paper flower]

sqlite is a database (as people usually call them) or relational database management system (rdbms)

Sqlalchemy is a sql toolkit and orm, it allows you to use sql databases easier

And it supports other databases for example mysql and postgresql

[hot trellis]

is there a plugin on pycharm that can have mysql syntax highlighted?

[compact warren]

thx :)

[vernal spade]

Hi, I'm doing some practice on nested SQL queries and I'm having trouble with the following problem -

"Write a nested SQL query to find the names of doctors who work in the clinics specified as follows -
The clinics must have at least 5 patients who live in the same city as the doctor works."

Doctor( Dr_ID, Dr_Name, D_City, C_ID* )
Clinic( C_ID, C_Name, C_Addr )
Patient( P_ID, P_Name, P_City, C_ID*, Dr_ID* )

My attempt so far

SELECT DISTINCT Dr_Name
FROM (
    SELECT a.*, b.*
    FROM Doctor AS a
    LEFT JOIN Patient AS b
    ON a.D_City = b.P_City
    )
WHERE COUNT(Dr_Name) >= 5;

I believe this would only show doctors who live in the same city of at least 5 patients, but it shouldn't include patients that go to a different clinic to the doctor. So I'm wondering how to include matching their clinic id's as well.

[main hedge]

Anyone with flask and SQL experience can help me debug, I'm building a movie booking system but the problem is

When customer try to make a new booking, it doesn't reflect in the "My Booking" page

Potential Reason: My new_booking function doesn't insert a new record #help-mango

[tender matrix]

Gg

[worn heart]

TypeError: index 'whitelisted' cannot be applied to Cursor instances
``` mongoDB

any help??

[junior bone]

has anyone had problems with sqlite fetching data that was deleted? I had this happen and im not sure why it is occurring...

[honest jetty]

hello,

def test_factories(database_environment):
    worker_factory = WorkerFactory()
    transaction_factory = TransactionFactory()

    # Create worker
    worker_1 = worker_factory.create_worker(name="Worker 1")

    # Create transaction
    transaction_1 = transaction_factory.create_transaction(worker_id=worker_1.id, quantity=5, price_per_unit=2, paid=False)

    # Delete worker
    assert worker_factory.delete_worker(worker_1.id) == worker_1

    # Create new worker
    worker_2 = worker_factory.create_worker(name="New worker", group_id = group_1.id)```

When I delete worker_1 from my SQL database and then create a worker_2, the new worker has the same worker_id == 1

the problem is that each Transaction has a worker_id
for example, I had a transaction of $10 dollars for worker_1
but then I delete this worker_1
create a new worker_2
the $10 dollars transaction is pointing to the new worker_2, because SQL is reusing the ID of deleted worker_1

how can I forbid SQL to reuse the same ID of a deleted row?

I am using SQLModel (pydantic + SQLAlchemy)

from typing import Optional
from sqlmodel import Field, SQLModel

class Transaction(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    worker_id: int = Field(index=True, foreign_key="worker.id")
    quantity: float 
    price_per_unit: float
    paid: bool = Field(default=False, index=True)

class Worker(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)```

[torn sphinx]

SELECT
l.name AS league,
-- Select and round the league's total goals
ROUND(avg(m.home_goal + m.away_goal), 2) AS avg_goals,
-- Select & round the average total goals for the season
(SELECT ROUND(avg(home_goal + away_goal), 2)
FROM match
WHERE season = '2013/2014') AS overall_avg
FROM league AS l
LEFT JOIN match AS m
ON l.country_id = m.country_id
-- Filter for the 2013/2014 season
WHERE season = '2013/2014'
GROUP BY league;

Why does the main query repeat code that’s already in the sub query. I know the sub query gets parsed first

[maiden vale]

Hi

I am trying to insert some data into MySQL database

but I am getting this error

mysql.connector.errors.DataError: 1292 (22007): Incorrect date value: '1994' for column customer_289803_ralgo.SERVER.startdate at row 1

nvm

solved

[crystal temple]

Hi, i'm using SQLite with tkinter (library not cmd line), how do I delete a record based off of its variable name?
its a bill tracker app, and there are 2 columns.. bill_name and cost
right now I have something like:

def delete_bill_func():
get_selected = tree.selection()
tree.delete(get_selected)

c.execute("DELETE FROM bills WHERE bill_name=get_selected")

the conn.commit() comes later in the main body of the code

[jade wing]

always use bind variables/placeholders (the ? character) when doing SQL and having any input to the SQL at all, like:

c.execute("DELETE FROM bills WHERE bill_name=?", (get_selected))

[uncut acorn]

Hi, I am using sqlite3 and i am trying to export an imessage conversation to a .txt file and was wondering if anyone could help me. I am currently using this walkthrough but it produces a blank .txt file https://apple.stackexchange.com/questions/414042/download-entire-imessage-history-with-one-person-as-plain-text

[crystal temple]

hmm i tried this but it didn't work. I'm thinking get_selected isn't returning the right data type.. i have it as 'get_selected = tree.selection()' which should be whatever is selected in the treeview

[mighty glen]

are nonpythonic sql questions allowed here

[plucky shoal]

i need help with sql bad

dm me asap

[paper flower]

Sure 😃

[mighty glen]

So I have... this monstrosity.

CREATE TYPE Analytics_Entry AS (
    name TEXT,
    accessed BIGINT
);

CREATE TABLE analytics (
    domain TEXT,
    path TEXT,
    name Analytics_Entry[],
    category Analytics_Entry[],
    operating_system Analytics_Entry[],
    operating_system_version Analytics_Entry[],
    browser_type Analytics_Entry[],
    version Analytics_Entry[],
    vendor Analytics_Entry[],
    PRIMARY KEY (domain, path)
);

Whenever someone makes a request, i need to:
Insert the domain and path, provided they don't exist create them and set analytics entry to be empty, then
Add an item to each column based on what the string is- if the name already exists, create it and set accessed to 1, if it doesn't, increment accessed

[paper flower]

Can't you have accessed column on analytics table?

Or you need to track all columns individually?

[mighty glen]

note that analytics entry is always in an array

there are many possible things that Category could be

[paper flower]

Could you explain what task you're trying to solve?

Maybe you can structure your data differently

[mighty glen]

whenever someone makes a request to my server, i have to log it. I have two keys that are unique, which i should be able to look up easily. I then have a bunch of data from my request, which can be anything but is likely to reoccur, which is split into columns, but it's not possible to have a column for each unknown possibility.

[mighty glen]

this actually doesn't work, as my SQL library doesn't support custom types

so i need a slightly smarter solution

[paper flower]

You can have multiple version or vendor properties on single request?

or name, category, etc

[paper flower]

Which library are you using?

[mighty glen]

I did stipulate it's not Pythonic.

[mighty glen]

No, but i can have multiple for the same domain-path combo

someone might use Mozilla Firefox 50 and someone else might use Google Chrome 100, then one of the versions would be 50 and the other 100, and one vendor would be Mozilla and the other Google

[paper flower]

Well, i see

[] is an array?

[mighty glen]

Yeah

[paper flower]

CREATE TABLE analytics (
    id integer primary key,
    domain TEXT,
    path TEXT,
    name varchar,
    category varchar,
    operating_system varchar,
    operating_system_version varchar,
    browser_type varchar,
    version varchar,
    vendor varchar,
);

Why not do it like that?

[mighty glen]

this is Launchbadge's SQLx

[mighty glen]

How would i store the number of requests for each of them?

[paper flower]

count?

[mighty glen]

would id just be an auto-increment value?

[paper flower]

Yep

[mighty glen]

and some of the varchars would be null

hmm

[paper flower]

Make them null then

[mighty glen]

interesting

[paper flower]

How many requests per second would you have?

[mighty glen]

my only concern is that it might violate GDPR, as all the data for one request is grouped

few enough that hitting the database every time isn't a huge problem. I also charge more for the one that hits every time 😅

[mighty glen]

If i changed this to just count requests with a certain type, it would both align to the data I send out and help me comply with GDPR

[paper flower]

Can you use https://docs.sentry.io 🤨

[mighty glen]

it doesn't seem to have compile-time type checking or Tokio support, but that's out of the scope of the python discord

[paper flower]

Hm? It's a separate service

You can self-host it too, it would collect all exceptions and metrics from your application

[mighty glen]

oh its metrics

what does it have to do with sql?

[paper flower]

Why do you need to store that data in you database in the first place?

[mighty glen]

Analytics.

[paper flower]

Maybe there are other solutions

like sentry 🙂

[mighty glen]

i... really don't want to use a third party service to handle the only user data i collect

[paper flower]

You an self-host it

[paper flower]

Anyway, i think this should do

You can later aggregate your events by domain or path

[mighty glen]

I'm already doing that though

just without the ID

[paper flower]

Yeah, because you were storing everything in array

So you'd have 1 row per each path?

[mighty glen]

No. That's not implemented yet

[paper flower]

Don't use arrays here really

[mighty glen]

CREATE TABLE analytics (
    domain TEXT,
    path TEXT,
    name TEXT,
    category TEXT,
    operating_system TEXT,
    operating_system_version TEXT,
    browser_type TEXT,
    version TEXT,
    vendor TEXT
);

This is the old one... it's almost exactly the same

[paper flower]

Also add a created_at column, seems useful in this situation

[paper flower]

And it's fine

[mighty glen]

that i probobaly will do

[paper flower]

Also i wouldn't use TEXT here

You can put anything into UserAgent header ...

So use varchar with limit, e.g. varchar(80)

[mighty glen]

It's parsed out by another library, i did not do it myself

so i would hope it implements limits. I will make it varchar though

[paper flower]

I'm just saying that it's still possible to send giant UserAgent strings

[mighty glen]

yeah

so that's a good idea

[paper flower]

Pretty much

[mighty glen]

i just really feel that this violates GDPR

[paper flower]

🤔

It's not a personal information though

[mighty glen]

all of this data together "could" be traced to one person

GDPR is absurdly broad and apparently this does count

but it's also just less space efficient

[paper flower]

I'm not sure if it violates gdpr but it looks quite hard to track 🤔

You're not associating it with users directly, there's only OS and browser data

[mighty glen]

I do plan to add country data at some point

so i think that would hurt

[paper flower]

Yep, unless you don't associate it with this table

[mighty glen]

but it would be associated

[paper flower]

So ...

Don't do that

😅

[mighty glen]

well.. the best way would be something like the custom type

which is still complex

[paper flower]

I don't understand why you need a custom type here

[mighty glen]

because then, whenever i get a hit on something from a user agent, rather then storing it i can just increment an integer

[paper flower]

What if multiple users have same user agent?

What if they update their browser?

What if someone spams you with user agent 1, 2, 3 ...

[mighty glen]

that's when we increment the integer. if they're different, we create a new item in the list

[paper flower]

I'd say it's easier to create a new row for each request

[mighty glen]

Ideally there will be ratelimiting. I'll have to figure that out.

[paper flower]

Sentry does that 🤔

[mighty glen]

what is right and what is easy are not always the same thing

i'd like to have a column for each request, but i've been told that's not OK

and i can't afford a lawyer to tell me one way or the other

[paper flower]

For each request? 👀

Wdym

[mighty glen]

not a column, a row

i mistyped

[paper flower]

Right, because you would associate your browser with os version

I'm not sure if that's against gdpr, so i can't tell really

You might have to split your table into multiple

[mighty glen]

then we get into doing 10x the number of inserts, which would slaughter performance

[paper flower]

e.g. analyticts_useragent, analytics_os, etc

[mighty glen]

I have explored that

[paper flower]

You don't have to collect all events

you can collect 50, 20, 10% of them

[mighty glen]

actually... i could just check if the field is the None variant

[paper flower]

I think having a giant array of user agents and other data is worse than having multiple tables

[mighty glen]

yeah i'd agree

i'll cut down on the data prob and then just suck up the multiple inserts

[paper flower]

what kind of metrics you want to collect though?

and for what purposes

e.g. performance monitoring

[mighty glen]

Analytics that would look something like https://simpleanalytics.com/simpleanalytics.com

[paper flower]

I see, having a single page would obviously be easier but it might violate gdpr

I think go with multiple tables

[mighty glen]

i could also use mongodb /j

[paper flower]

nosql isn't the best for aggregations iirc

[mighty glen]

Simple, i'll just write my own database

[paper flower]

json file is a database too ...

[mighty glen]

there isn't really a database for aggregations that i know of

[mighty glen]

yeah but loading the whole thing can get inefficient. I've used Bincode in past projects

[paper flower]

most sql databases are ok for aggregations

@mighty glen You can also check what they collect https://docs.simpleanalytics.com/what-we-collect?ref=simpleanalytics.com

[sharp crown]

Could someone help me with django many to many relationships and serializers? https://stackoverflow.com/questions/71993201/how-to-add-serializer-for-django-with-manytomanyfield-with-through

[jade wing]

i've heard a lot of good things about https://github.com/VictoriaMetrics/VictoriaMetrics, and it's metrics you are doing, which is aggregation intensive

[torn sphinx]

Hi

im stuck in this question

[graceful widget]

SELECT TOP i guess 5

[trim wave]

hey where are you taking this course? I am trying to learn sql

[torn sphinx]

you would need to learn sql from here: https://sqlbolt.com/ and then use aiosqlite: https://pypi.org/project/aiosqlite3/ and then if you want to view data with an api use the sqlite db browser: https://sqlitebrowser.org/

[graceful widget]

i googled that btw

[torn sphinx]

okimmi gived me

[graceful widget]

im not sure

[trim wave]

thanks

[torn sphinx]

SELECT TOP?

[torn sphinx]

goto sqlbolt first

[trim wave]

k

[torn sphinx]

the 1 link

[graceful widget]

i googled it

[torn sphinx]

wait let me try

[graceful widget]

how to get the first 5 item in sql

this is what i google

[torn sphinx]

doesnt work

[graceful widget]

gg

SELECT * FROM YEAR. LIMIT 5

@torn sphinx

[torn sphinx]

OK

[graceful widget]

its working?

[torn sphinx]

NO

[winged moth]

yea?

[torn sphinx]

.

[winged moth]

SELECT * FROM movies LIMIT 5

[graceful widget]

gg

[torn sphinx]

yay worked :D

[paper flower]

You need to use order by and limit

[torn sphinx]

i used limit

[paper flower]

e.g.

select name from person
order by birth_date
limit 5

[torn sphinx]

ryuga helped me

[paper flower]

You have to use order by too

[torn sphinx]

im learning now

[paper flower]

Order is not guaranteed by default

[torn sphinx]

and i didnt found that for now

[paper flower]

If you do

select name from person
limit 5

then you're not guaranteed to receive rows in order you want

[torn sphinx]

oh

[paper flower]

So you need to add order by

[torn sphinx]

okay

[winged moth]

ah yes, u need to order them too, they happened to be sorted in that example

[paper flower]

def get_weekday():
    return "Monday"

Just so happens to work today

[winged moth]

i didnt mean it that way but ok

[paper flower]

Data was sorted, so simple select with limit works

[paper flower]

That's kind of the same ^

[torn sphinx]

SQL IS EASY

[jade wing]

for the basic parts it is...
when you are doing complex joins and subqueries things can get a little bit more complicated
then there is the whole issue of data structure in the database, what to normalize and to what degree
and optimizations like indexes, partitioning of tables, views and using correct data types everywhere
then you got real dba level knowledge like tablespaces and how to configure them correctly

[torn sphinx]

😳

[grim vault]

The mandelbrot ascii gfx or the sudoku solver (in the sqlite docu) still boogles my mind

https://www.sqlite.org/lang_with.html#outlandish_recursive_query_examples

[marble osprey]

!pastebin

[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.pythondiscord.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.

[marble osprey]

https://paste.pythondiscord.com/erijapurul
I am not sure how to get this update function fixed
it is giving me this error when just above it in the code it is working
cursor.execute("UPDATE Quote SET QuoteID = ?, JobType = ?, HandymanID = ?, ClientID = ?, EstimatedCost = ? WHERE QuoteID = {str(tree.item(selectedRecord, 'values')[0])}",(QUOTEID, JOBTYPE, HANDYMANID, CLIENTID, ESTIMATEDCOST ))
sqlite3.OperationalError: unrecognized token: "{"

[torn sphinx]

how do i fill null values

it's not working

[crystal temple]

any sqlite pros? I'm still having trouble deleting a record xD I'll link my code this time

[delicate field]

Hey @crystal temple!

It looks like you tried to attach a Python file - please use a code-pasting service such as https://paste.pythondiscord.com

[crystal temple]

https://paste.pythondiscord.com/uwivomafov <- my code. Please look at line 66 at delete_bill_func to see if the syntax is correct. Error I'm getting:

File "C:\Users\Oliver\AppData\Local\Programs\Python\Python37-32\lib\tkinter_init_.py", line 1705, in call
return self.func(*args)
File "bill_tracker.py", line 71, in delete_bill_func
c.execute("DELETE FROM bills WHERE selected = ?", (selected,))
sqlite3.OperationalError: no such column: selected

[torn sphinx]

im in confusion with INNER

can anyone explain it well

[jade wing]

can you run the commands in the terminal on the server (i.e ssh access)?

[crystal temple]

i don't have sqlite cmd line installed

[crystal temple]

unless you're just talking about access to the windows cmd, which i do have

[jade wing]

any chance you can install the sqlite3 command on the server?
or download the database if it's not too big and run the sqlite3 command locally on your own computer?

[crystal temple]

yeah i can

[jade wing]

which one? 🙂

[crystal temple]

i can download the db locally

to local

i did get DB browser for sqlite

but it shows what i already know :d

[lofty sand]

Hi everyone. I'm new in python developping

I have a question

[crystal temple]

I guess I can execute SQL from DB Browser, but i do want to do it through python

[jade wing]

just wanted the output of one command as a link if you can, it's the .schema command so that i can know how the database tables look like

[crystal temple]

ooh

its a one table DB with 2 columns

[lofty sand]

When I want to replace some characters in string python replaces already replaced ones

[crystal temple]

its all in my code CREATE TABLE command

[jade wing]

okay, i'll check that part then

[lofty sand]

hasd.replace("а","б").replace("ц","я").replace("я", "л")

[crystal temple]

and i think for sqlite the primary key is assumed so i didn't define it. it works when i query for oid

[crystal temple]

i'll link it:
c.execute("""CREATE TABLE bills (
bill_name text,
cost integer
)""")
and i think for sqlite the primary key is assumed so i didn't define it. it works when i query for oid

i made it as simple as possible to avoid issues

lol

[jade wing]

then c.execute("DELETE FROM bills WHERE selected = ?", (selected,)) will not work
you need something like c.execute("DELETE FROM bills WHERE bill_name = ?", (selected,)) instead

[jade wing]

because i'm guessing you are going to search for an exact match of what ever is in the selected python string variable and match it against the bill_name column in the bills table

[crystal temple]

ahhhh i thought the argument after the SQL statement had to be the same as the column name lol. that makes sense

[crystal temple]

hmm this goes into my poor design lmao. I haven't actually thought of how to delete data from the db until now. I was just going by what can be selected in the treeview

thanks!! 🙂

[jade wing]

i would suggest you don't use * (asterisk) but instead name the columns in your SELECT queries
you can also add a "magic" column that sqlite3 has to those queries, the column name is rowid and is automatically created for you unless you create the table as WITHOUT ROWID, which you probably shouldn't do
so you can use SELECT rowid, bill_name, cost FROM bills to get that column and you can use it as hidden data in the gui
later you can do things like c.execute("DELETE FROM bills WHERE rowid = ?", (selected_id)) and such

[crystal temple]

ah, I was trying to find a way to reference the rowid instead of the name itself

[crystal temple]

so with SELECT rowid, bill_name, cost FROM bills I can display something like "1 internet 120" and delete it based on the rowid? Assuming I explicitly state to delete that id by typing it in

[jade wing]

yeah, you don't even strictly have to display the rowid, but you might as well do that too if you want to, for the situation where you have several bills with the same name and maybe even amount
you might want a invoice_date or something for when the bill was produced and maybe a due_date as well to easier differentiate the bills if you have too many that are alike

[gusty mulch]

Hi so I'm getting this errorpy ERROR:cogs.error_handler:DataError: invalid input for query argument $2: 607652789304164362 (expected str, got int) (In blacklist) Traceback: File "/usr/local/lib/python3.8/dist-packages/discord/ext/commands/core.py", line 85, in wrapped ret = await coro(*args, **kwargs) File "/home/modmail/cogs/core.py", line 358, in blacklist blocked = await self.get_blocked(member.id, ctx.guild.id) File "/home/modmail/cogs/core.py", line 340, in get_blocked return await conn.fetchrow( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 679, in fetchrow data = await self._execute( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1659, in _execute result, _ = await self.__execute( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1684, in __execute return await self._do_execute( File "/usr/local/lib/python3.8/dist-packages/asyncpg/connection.py", line 1734, in _do_execute result = await executor(stmt, timeout) File "asyncpg/protocol/protocol.pyx", line 183, in bind_execute File "asyncpg/protocol/prepared_stmt.pyx", line 197, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msgwhen trying to write data to my db. The table looks like this and I'm passing the db a int so i'm not sure why it's having issues. it's like it's trying to write ctx.guild.id to the reasons column

[grim vault]

How does the code look around File "/home/modmail/cogs/core.py", line 340

[gusty mulch]

lines 334 - 343```py
async def get_blocked(self, member, guild):
async with self.bot.pool.acquire() as conn:
res = await conn.fetchrow("SELECT * FROM blocked WHERE member=$1 and guild=$2", member, guild)
if res:
return res

    return await conn.fetchrow( # This is ln 340
        "INSERT INTO data VALUES ($1, $2, $3) RETURNING *",
        member, guild, None
    )```

[grim vault]

You select from blocked but insert into data, Is that right?

[gusty mulch]

aaah, no that's not right

thanks for pointing that out

[gusty mulch]

okay thanks for the help I'm not getting any errors now, however I'm not getting any response from this section of code at all, an it's supposed to respond once it's gathered data from blocked so this is strange

[novel oak]

Hi, I am having this issue while starting the mysql server in ubuntu: su: warning: cannot change directory to /nonexistent: No such file or directory

I've seen several answers and possible solutions in stack overflow, but they said could imply security problems. Any solutions for this?

Thx in advance

[twin torrent]

I can post a code snippet if necessary but has anyone dealt with sqlalchemy engine losing connection in a redis worker?

[round valley]

Anyone know the easiest way to connect to a MariaDB/MySQL database using SQLAlchemy while allowing for the logged-in user's credentials to be used on Windows? Basically don't want to code our apps with credentials or variables for credentials, just have them run based on current user's credentials. We have this working already with MSSQL using pyodbc and the Trusted_Connection=yes; connection string.

[jade wing]

sounds like kerberos (sometimes called gssapi) authentication is used with mssql odbc trusted connections, mysql and mariadb should be able to support that too, might however need addons/plugins/extensions for that

[round valley]

Yeah, the Ops guys mentioned it might need to be kerberos. They already have GSSAPI auth set up on the server, but not kerberos. Looking into potential ODBC drivers that might do the job, but may need a bit of additional work 😦

Thanks for reaffirming this hunch. Will have a chat to them.

[torn sphinx]

Hello 👋, i have problems with graphql, I need to parse custom Scalar (Ethereum address to string) but I can’t do it. Can anyone help me (I use gql package)? Thanks

[radiant kestrel]

What is the diffrence between SQL and SQL lite?

[jade wing]

SQL is the language, SQLite is a database engine, a specific implementation

[jade wing]

there is a lot of different SQL databases out there

[radiant kestrel]

Ok

I was thinking of building a project with python and a database, I learned mySQL, but I saw many use SQL lite, and I was wondering is there is any diffrence

[jade wing]

you can se a [incomplete] list over at https://db-engines.com/en/ranking
but it lists a lot of databases that isn't SQL based as well

[radiant kestrel]

Interesting

[jade wing]

almost every SQL database also has their own dialect of SQL unfortunately, but also necessary to support different features they add or modify compared to the standard

[jade wing]

just a note for future references, the product is named "SQLite" or "sqlite" (one word where they reuse the "L" in there), not "SQL lite"

[radiant kestrel]

Oh, got it

Thanks

[jade wing]

what is very different with sqlite is that you don't need a running standalone database engine as a daemon or server
sqlite is what is known as an embedded database, it's just a code library that builds a database in a file in the file system
and it's "lite" in the sense that it doesn't include all of the usual features that you might be used to, but still a whole lot of features, many times enough for really small projects or PoC:s/prototypes 🙂

[gusty mulch]

Hey so a discord bot that I make pr's for has a table for all of the guild settings, in this table they have a list/array of ppl who the guild admins have decided can't use the bot (bc they spammed it or something). I decided to pr a change which would allow admins to give a reason to why they were 'blacklisting' that user, My proposed implementation is to create a new table in the db which has three columns, the member, the guild and a reason however a fellow contributor says that he thinks it would be better to just add a new column to the existing table and make it a json datatype.

which implementation do you think would be best?

[paper flower]

I'd go for separate table

class BotBlockList(Base):
    __tablename__ = ...

    user_id = Column(BigInteger, primary_key=True))
    guild_id = Column(BigInteger, primary_key=True))
    reason = Column(String(150))

[lean pollen]

Can anyone tell me why this;

SELECT
    regexp_extract(col_value, '^(?:([^,]*)\,?){1}')
from tbl_vac;

Works fine but this;

SELECT
    regexp_extract(col_value, '^(?:([^;]*)\;?){1}')
from tbl_vac;

Doesn't do the same thing and select split by semi colon?

Im getting this error;

Error while compiling statement: FAILED: ParseException line 2:28 cannot recognize input near '^' '(' '?' in select expression [ERROR_STATUS]

[bronze coral]

yo guys i have something to ask is there any bot that i can use to predict a numbers by the given old ones

[lean pollen]

Wait...might have figured out a work around

[paper flower]

Sure, share your tables

[keen widget]

I have live data coming and I need to gather them and process the data points. What is the most efficient way of storing that data?

[jade wing]

i would go with the separate table as well
then that list/array in the other table is not even needed anymore, just make a lookup in the new "denylist" table
in the new table you might want to have a column with a forging key pointing to the user_id in the users table of the admin that added the block, and another column for a timestamp for when this happened
using a separate table really makes this kind of extensions trivial

[paper flower]

I don't think it's even in 2nf 🤔

[kind sigil]

Hi, I have a problem I have to make a project for my school but I can't do it I've been taking my head for a week I don't understand anything, could someone do it and send it to me please, it's about an exploitation of a database to make a bar graph thanks to python. please send me a private msg if it is possible

[gusty mulch]

yeah someone else I asked said basically the same thing, using a new table adds in so much more flexibility for future updates, a suggestion that the dev has received and accepted is the ability to temp block ppl so using a new table now will help down the road so it makes sense. But thank you for your opinion, it's much appreciated

[gusty mulch]

!rule 8

[delicate field]

Rules

8. Do not help with ongoing exams. When helping with homework, help people learn how to do the assignment without doing it for them.

[gusty mulch]

send us a short explanation on what you've been asked to and what you think you need to do. Code examples can be useful, even if it's just pseudocode. We can help you understand the question but we won't do it for you.

wow blind must be a super long msg

[gusty mulch]

someone by the name blind had been typing for ~half an hour

[jade wing]

why use an ide when you have the discord message input field? 🤪

[gusty mulch]

lol yeah, and @delicate field's eval

[radiant kestrel]

I get it know, thanks

[brittle bolt]

What are you referring to me?

[gusty mulch]

you were typing in this channel for about half an hour on and off with no msg sent

or at least discord thought you were typing in this channel

[brittle bolt]

I was interrupted by phone call half way through a post. But it shouldn't prevent others from posting?

[wraith yoke]

Hello, I needed to create a search function based on multiple fields. I also need to make sure it case insensitive which I believe it is. However I dont know how I can implement unaccent search as well

addresses_table.find({'$or':[
            {'city':{'$regex':keyword, '$options':'i'}},
            {'street':{'$regex':keyword, '$options':'i'}},
            {'street_number':{'$regex':keyword, '$options':'i'}}]})

[gusty mulch]

oh no it wasn't, I was just commenting on how it seemed you were writing a really long msg #databases message

[wraith yoke]

lets say If i search for helen, Hélène Joy containing object should also be included in the result

How can I achieve that?

[brittle bolt]

What database are you using?

[wraith yoke]

mongo db and pymongo lib

[brittle bolt]

I just watched a video that would really help you. I'm going to dig through my browser history to find it.

[wraith yoke]

I would be thankful

[simple grove]

from contextlib import closing
from sqlite3 import IntegrityError, connect

with connect("login.db") as db:
  with closing(db.cursor()) as cursor:
    try:
      cursor.execute(
        "create table if not exists users (user_id int primary key, fname text, lname text, email text)"
      )
      cursor.execute(
        "insert into users values (?, ?, ?, ?)",
        (1000, "Tom", "Brown", "[e-mail]"),
      )
    except IntegrityError:
      pass
    user_id = input("Enter user_id? > ")
    for user in cursor.execute(
      "select fname, lname, email " "from users " "where user_id = ?", (user_id,)
    ).fetchall():
      fname, lname, email = user
      print(
        f"Found user_id record: {user_id}",
        f" - fname:  {fname}",
        f" - lname:  {lname}",
        f" - email:  {email}",
        sep="\n",
      )```
any thoughts to improve / simplify / shorten this sqlite3 boilerplate ?

[brittle bolt]

I'm not sure if this going to answer your question directly but watching the video will certainly help you understand how to make your search more performant.
https://www.youtube.com/watch?v=2NDr57QERYA

[wraith yoke]

thanks a lot!

[brittle bolt]

There is also this one which likely more specific:
https://www.youtube.com/watch?v=mHeP5IbozDU&list=PL4RCxklHWZ9uluV0YBxeuwpEa0FWdmCRy&index=2

[mighty glen]

in Postgres, how does one select or insert if it doesn't exist?

[patent raptor]

what database should i use?

[burnt turret]

you can get all columns with SELECT *, or you can even list multiple columns like SELECT characterName, characterLevel, .... FROM incremental ... and do this with a single query

[atomic jewel]

hi

Please help

Select top(10) Amount, Date, datepart(week, Date) AS WK
FROM (
SELECT * FROM sourcetable
WHERE Amount LIKE '1%' AND Date LIKE '2022%'
) T1
GROUP BY WK```

my group doesn;t work. But order by works here

[atomic jewel]

hello

[wraith yoke]

Hello does anyone know how can I apply custom sort function in pymongo?

[paper flower]

select 
  characterName, 
  characterLevel, 
  characterXp, 
  characterXpRequired, 
  characterEnergyStones
from
  incremental
where
  userId = ?

[paper flower]

I'd also rename incremental to character, since it seems to store character info?

And you could remove character prefix from all of your fields then

[brittle bolt]

What do you mean by "custom sort"?

[jade wing]

and with the python sqlite library with sqlite3.connect('DB Storage/essence.db') as db: doesn't do what most people think it does, or expect it to do
spoiler: db will still be defined and connected to the database and is still usable outside of the context manager (the with block)

to fix this, do db.close() when you are done using it if that was your intent with using the context manager like that in your code
the context manager will instead create a database transaction, the docs has this to say about it: https://docs.python.org/3/library/sqlite3.html#using-the-connection-as-a-context-manager

Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed:

[harsh pulsar]

and if you do want the "with closing" behavior, you can use contextlib.closing

from contextlib import closing

with closing(sqlite3.connect('things.db')) as db:
    ...

it's a pretty annoying api design, i wish they would have given the connection context manager the usual "closing" behavior, and used something like with db.transaction(): for commit

[jade wing]

yeah, that would have been so much better and intuitive as it would follow the normal behavior of with

[swift saffron]

can someone help me create a relational schema from er-diagram?

[wraith yoke]

lets say I have latitude and longitude in each collection object, and I need to pick up the object with minimum distance between two coordinates one of them from db and one of them user input

[brittle bolt]

For geo-spatial data you should use MongoDB's builtin geo-spatial indexes. See here:
https://www.mongodb.com/docs/manual/geospatial-queries/
You can then use "$geoNear" aggregation operator to find the data directly.

[wraith yoke]

I'm using pymongo, is it supported in pymongo too?

[brittle bolt]

Yup

[wraith yoke]

By the way I couldnt make the unaccent search work ((

[mint dust]

should I make a class for all database interactions or just query directly? like so

class Database:
    async def get_user_by_id(self, _id:int):
        value = await self.conn.fetch("SELECT * FROM USERS WHERE id = $1", (_id,))
        return value
```vs 
```py
async def update_loop():
    conn = await asyncpg.connect(**config)
    while True:
        value = await self.conn.fetch("SELECT * FROM USERS WHERE id = 4")
        # do something with data

[brittle bolt]

Unfortunately I don't have much experience with that type of data, I'm typically dealing in numerical"ish" values. But my understanding is that you need to use collation.

[wraith yoke]

return addresses_table.find({'$or': [
                        {'city': {'$regex': keyword, '$options': 'i'}},
                        {'street': {'$regex': keyword, '$options': 'i'}},
                        {'street_number': {'$regex': keyword, '$options': 'i'}}
                    ]
                }
        ).collation(Collation(locale='az'))

i tried collation but it doesnt seem to work

[harsh pulsar]

you can do it either way. i prefer to avoid making a class until it's obvious that i want a class

in this case there's not much benefit to the abstraction of a class that i can see in your example, so i'd avoid it

you can write a get_user_by_id function instead

async def get_user_by_id(conn, _id: int):
    return await conn.fetch_one("SELECT * FROM users WHERE id = $1", (_id,))

that said, you might want to consider using a library that will help you "deserialize" the raw database output to some structured representation

for example:

import attrs

@attrs.define
class User:
    id: int
    username: str
    auth_id: int

async def get_user_by_id(conn, _id: int) -> User | None:
    row = await conn.fetch_one("SELECT * FROM users WHERE id = $1", (_id,))
    if row is None:
        return None
    return User(*row)

there are packages that do this in a more integrated fashion, e.g. sqlmodel, or a true "ORM" like sqlalchemy

[brittle bolt]

One solution that was proposed in the video I linked to yesterday (I think), was to create a "sanitized" field, where you remove any special character and use lower case characters for the value. So for example for city:

{ "city": "Abşeron", "city_sanitized": "aberson"}

To "sanitize" the data you can use a python package like unidecode. It will transform any special characters into a standard "English" equivalent.

So when the user requests a city, you run the value through unidecode, take the value and then use the "sanitized" value in the mongoDB query on the "city_sanitized" field and project the "city" field to return the value as the user expects.

!pypi unidecode

[delicate field]

Unidecode v1.3.4

ASCII transliterations of Unicode text

[dreamy zealot]

Greetings, I am building a flask application using sqlalchemy. When defining the models.py and the methods for each object/ table of the dabase, is it good practice for methods to do any kind of database querying, or should I import the query result from the database in the argument?

@true hatch

[true hatch]

hey, good question. let me see what I have done in my past flask projects..

To be frank, The only methods I used within my models was the repr method for readability. As far as querying, adding entries; i kept that logic wihtin my routes.py file

class ScheduledMeds(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    dose = db.Column(db.String(10))
    frequency = db.Column(db.String(50))
    times = db.Column(db.String(40))

    def __repr__(self):
        return f"<{self.name}>"

as far as best practice, im not sure.

[dreamy zealot]

Yeah thank you! Though I need some complex methods. Let me frame this as other problem. Should unit tests in flask apps envolve database operations?

Or is that a functional test?

[true hatch]

that question is beyond my scope, admittedly, i did not do any testing with my flask apps..

i wish I could be more of use to you! sorry

[dreamy zealot]

No problems! Thank you very much for the effort

[paper flower]

I'd move all querying operations into separete object, keep your models simple and responsible just for db and relationship definitions

[dreamy zealot]

Thank you very much!

[paper flower]

Also it's ok to test your application alongside with db

Also it's just a personal preference, but I'd move from flask-sqlalchemy to sqlalchemy

First one uses legacy query api that would be deprecated in sqlalchemy 2.0

[dreamy zealot]

Thanks, that makes total sense. I am a little late for that but I will see what I can do

[paper flower]

Are you creating an api?

[dreamy zealot]

yes

[paper flower]

Use Fastapi instead of flask, it's just better

[dreamy zealot]

I think I am just using flask_sqlalchemy for this

from flask_sqlalchemy import SQLAlchemy

instantiate the db

db = SQLAlchemy()

[dreamy zealot]

I will take this into heart next time I make an API

Right now I am waay to far to develop in other framework

[paper flower]

They are quite similar

[dreamy zealot]

But for what I am doing it is enough

[paper flower]

Fastapi just has more features that are generally desired when building an api:
Built in api documentation generation
Request validation via pydantic
Async support

Dependency injection is nice to have

[dreamy zealot]

@paper flower Is it best practice to check if variables are valid before object is created or between the object is created and is commited to the database?

And if it is between I can have object methods returning true/ false for validation

Thank you for your attention and time

[tacit cloud]

that's up to you. probably best to have constraints on the database itself so you get an error whether you're writing bad data from sqla or from your cli sql shell directly

[half slate]

I do not know if TensorflowHub is considered a database, but I still have a question about it.

Can someone help explain how to import a tensorflow hub model, and which variables I need to enter? (I am specifically looking at this one: https://tfhub.dev/deepmind/i3d-kinetics-600/1)

[grim vault]

.. ORDER BY 2 DESC, 3 DESC LIMIT 10

[jade wing]

how is you code coming along, has it come a long way from this by now?

[jade wing]

it sounds like you have a lot going on in the same table
do you have any normalization going on in your database?

[tacit cloud]

@lime elbow basically, not repeating data in any rows. in the example on https://en.wikipedia.org/wiki/Third_normal_form#"Nothing_but_the_key", not specifying al fredrickson's birthday twice

[jade wing]

that ☝️
but one does not necessarily need to follow all the rules of one specific level or aim for a high level of normalization, just that anything is better then nothing, at least one should be aware of it when doing database design

here is also a more general rundown of the different levels and what they are about: https://en.wikipedia.org/wiki/Database_normalization

[jade wing]

are there any other tables in that database or is it all in that table?

[coarse lantern]

Hey guys would anyone help me with an eer diagram, i am finding it really hard to follow, i have made a rough diagram but i think it's wrong.

https://drive.google.com/file/d/1jiUKne92Rg0yYJHoRLRIWN1-z6LCjGUa/view?usp=drivesdk
Right now i only have sketched out the regular entities

[tacit cloud]

normalization seems fine, but it seems weird to me to store level, xp required, and max hp. you can calculate all of those from the character xp

[coarse lantern]

he third point confuses me
it says that these "reports" are often triggered by the events
so do i make reports as an entity which is the union of SOS and inspection reports
and an entity for client that would request for the reports with a consultant
i finding it hard to understand how all these relate together
and then there is also an owner which needs to give the consent for these reports
and he must be notified the outcome of the reports as well

[jade wing]

i see

[tacit cloud]

can you not calculate the xp requirement based on the current level and the world?

storing the HP as a percentage seems pretty dicey. you have to think about floating point shenanigans. I'd recommend storing it as an int and calculating the percentage as needed

[paper flower]

Depends on the validation you need

Basic validation (types, length, number values (greater than, less than) - i'd use pydantic for that
For complex rules that would need to query database i'd move that to separate function/service

Don't put anything of that into model itself 🤔

But adding constraints that benefit your data consistency (e.g. adding unique constraint on user username) should be added.

[worn heart]

Would anyone here have any idea why my its only inserting 1 thing of my data to my db on MongoDB

for guild in bot.guilds:
        bye.insert_one({"_id": guild.id,
                        "channel": None,
                        "message": None,
                        "embed": True})
``` code

[brittle bolt]

There are only to reasons that it shouldn't work that jump out at me.
1- is len(bot.guilds) > 1?
2- is guild.id unique?

[brittle bolt]

One possible piece of advice, that may or may not apply to your use case:
Setting keys to None is not really done in MongoDB due to its flexible schema. If as per your example "channel" is None simply omit it, then if for another document it is not None then included it. If you need to query for documents where channel is None or in this case does not exist in the document you can use the $exists operator.

[dreamy zealot]

Thanks a lot !

[narrow saffron]

When drawing an ER diagram with an entity that has a sort of relationship where it can have it with Entity A or Entity B, how else can I write it except connecting them in separate binary relationships https://gyazo.com/f975a8c88a475a1e4c683b2fd180e970

In this case, the Salesperson can Manage other Salespersons or can manage a Customer, is this the best way to write it

[stoic finch]

im making a multiguild bot, am i best using MongoDB or MySQL fo sorting all the data? im using MongoDB atm

[oak oyster]

Multiguild?

[stoic finch]

discord bot sorrys, need a database to store diferent server info

[jade wing]

i like postgres but have never developed any discord bots

[pure sleet]

if it's relational data use MySQL

[lone fractal]

I have an api that gets Metadata from a sql server database and queries an oracle database based on that Metadata. The Metadata includes table and column names.

I'm using cx_oracle, and trying to do the following using the Metadata that I get from the sql server db:

cursor.execute('select * from :table where :column = :value', (table_name, column_name, value))

But, when I try doing that I get an error because the table and the column can't be bind variables. Is there a way to do this safely?

[analog ember]

why am I getting this error when importing AsyncIOClient from motor.asyncio

ModuleNotFoundError: No module named 'pymongo.mongo_replica_set_client'

[grim vault]

You'll need an f-string for table/column names. To make it safe you eg. check against an allowed list, see https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html#binding-column-and-table-names

[chilly canyon]

I'm new to databases so hopefully this is simple. I have a list of users with a bunch of values that get assigned over time, and each user has one title, but that title can change over time. I want to be able to aggregate the values for each user based on their current title.

I'm using sqlite3 at the moment, and just want to check if this is an acceptable use for it. Also open to suggestions on ways to set this up, potential pit falls or issues anyone might be able to see that I can't, best practices, etc

[tacit cloud]

@chilly canyon sounds possible, though your question is really light on details. sounds like you want to select count(*), title from users group by title or something. you can just write a little schema, import it using the sqlite cli, and then play around in the shell to see if you can get what you want

[chilly canyon]

Thanks, playing around a bit now. I didn't want to go super deep into details, but the table will have something like

user    | title     | pay  | date
joe     | gardener  | 75   | 2022/28/4
joe     | gardener  | 25   | 2022/26/4
joe     | painter   | 50   | 2022/21/4

And I'd want to find Joe's pay as a gardener = 100. It seems simple, but I'm just jumping into sql & python really (after a lot of excel) and hearing about primary keys & relational databases made me second guess if this is a good approach or not

[royal pivot]

hey there, im new to databases, i just downloaded postgresql and i know a bit of sql, but im not sure how to set up everything, and how to connect my python program to it.
what do i put here?

[tacit cloud]

@chilly canyon http://sqlfiddle.com/#!5/a62e4/1/0

[chilly canyon]

Amazing, ty!

[tacit cloud]

@royal pivot looks like you've got a psql cli shell so you can start running random sql commands. to connect from python, you need to use a driver (probably psycopg2)

[tacit cloud]

@chilly canyon I should mention that you probably want to normalize title out into another table

[chilly canyon]

So do start messing with primary keys and that side of things? Was hoping I could put that off for later 😆

Why's that necessary?

[tacit cloud]

see my link in the message I'm replying to

[chilly canyon]

The sqlfiddle link? I'm not following why the title would need to be normalized (sorry, am noob)

[tacit cloud]

no, in my message about normalizing, I'm replying to myself. see the link to wikipedia in that message

[chilly canyon]

Ahhh K i'll read that then

[tacit cloud]

here's title normalized: http://sqlfiddle.com/#!5/7332dd/2/0

[chilly canyon]

Cool, ty. So having a little error, I'm trying to insert the current date into a record

def new_record(discord: str, date, category: str, item: str, amount: int, caller: str):
    cur.execute('''INSERT INTO PC VALUES (?, ?, ?, ?, ?, ?)''', discord, date.today(), category, item, amount, caller)
    con.commit()   

But that gives

AttributeError: 'str' object has no attribute 'today'

Easy fix or should I go to a help channel?

How should I go about automating a current date insertion when this is called?

[tacit cloud]

date is a str, not the datetime.date class

[chilly canyon]

Ohh should I do

def new_record(discord: str, date = date.today(), category: str, item: str, amount: int, caller: str):
    cur.execute('''INSERT INTO PC VALUES (?, ?, ?, ?, ?, ?)''', discord, date.today(), category, item, amount, caller)
    con.commit()   

[tacit cloud]

no, you should stop taking a date param at all since you're ignoring it anyway

and use datetime.date.today(), though that actually is the today in your system's local timezone

I would use datetime.datetime.now(datetime.timezone.utc) (and store the whole time rather than just the date)

[chilly canyon]

K will do

I tried things like

cur.execute('''INSERT INTO PC VALUES (?, date.today(), ?, ?, ?, ?)''', discord, category, item, amount, caller)

But that's not working. I'm sure there's a way to insert a date stamp when calling an insertion

[tacit cloud]

I mean you can use date() (https://www.sqlite.org/lang_datefunc.html) but same caveat wrt local timezone

[chilly canyon]

No I'll use yours, was just typing up this version when you made the recommendation

[torn sphinx]

does anyone happen to know how to use geopandas via jupyter hub?

[lone fractal]

Cool! I'm not sure that would working for what I'm doing though because there are multiple databases to potentially query from (based on the Metadata) and each db could have hundreds of tables... so I can't really list out all the allowed table names

[jade wing]

as you can't use bind variables for that you expose your self to security vulnerabilities such as the notorious sql injection attacks, at the very least you could run a query that list the tables and use that list for validation, probably also match that list against dictionaries of tables that should never be allowed or better still have some kind of pattern matching for what should be allowed so that anyone can't access just about any table in the database

[gusty mulch]

so I'm trying to figure out how I should structure my tags table. I'm thinking each tag should have a random ID (thanks to SQL Serial) as well as a name that ppl can call it by. Should I make the name the prim key, the ID the prim key or both the prim key? I'm learning more towards the second one however I do also need the name to be unique (maybe?, or maybe when I'm querying the name if there's multiple by that name then it returns all of them by that name 🤔)

also if I wanted to store the bytes of a file as outputted from io.StringIO I would use the bytea datatype for postgres right?

[jade wing]

i would go with the id as the primary key and probably make the name unique

[jade wing]

and would not store files in the database
i would rather store them in a file system or an object store such as aws s3 and then have a path or url to the file stored in the database instead

[gusty mulch]

What about a mongodb document as a place to store the files and then I store the mongodb document key in my SQL db

[jade wing]

could work, i'm no expert on mongodb but knows it handles file much better than most rdbms, retrieval might be an issue instead then if it would be a really busy service

[fluid lava]

Is there anyway to use pandas.to_sql to append only unique rows?

[unkempt prism]

Can you filter your data frame down to suite your condition of “only unique rows” and only then use to_sql

[regal wyvern]

I'm trying to understand the structure of a database,
can someone explain to me these things I'm pointing at with arrows, for example is the red arrow the actual database?
if so what are the blue arrows are pointing at? "what is **HR** and **PUBLIC**?
I know the green arrow is pointing at a folder that contains the tables

[paper flower]

They're schemas, one database can contain multiple schemas and these schemas can contain different objects too

You can organize your database into different namespaces using them

[regal wyvern]

@paper flower So the Red arrow is the actual database? and the blue arrows are pointing to schemas?

[paper flower]

Yep

[regal wyvern]

@paper flower So inside the HR schema there are 4 'things', what you call these in Databases terms?

[paper flower]

They're just grouped objects inside of your schema, they already have names

tables, views ...

[regal wyvern]

Ok I'm starting to get everything together

Really appreciate you

[paper flower]

You really only care about tables and views, you'd rarely touch sequences

you can create custom functions/routines but it depends on your app, you can do a lot of things quite performantly without them

But views can be really nice if you need some performance

[regal wyvern]

@paper flower Sorry for bothering you, if for example, I'm trying to build a music app database, in the picture I posted earlier, instead of HR it should be MUSIC APP?

[paper flower]

You can name it whatever you want

I usually create a separet db's for my projects

Single database host (e.g. postgresql) can have multiple databases

[regal wyvern]

And how do you name ur schemas, can u give an example 🙏🏻

@paper flower

[paper flower]

For most projects having a simple public schema is fine 🙂

But it depends on the size of your project

public schema is there by default, no need to create it manually

[regal wyvern]

Aha so you usually have ur tables, views, sequences and so on in ur public schema

[paper flower]

yep

If multiple applications would have to use same database you can split them into schemas
Or if one application becomes too big it needs a separate schema for some of it's tables

It's usually down to your preferences and db organization

[regal wyvern]

You're amazing, this really helped me understand the structure fundamentally 🙏🏻 🍰

[paper flower]

I'd say it's not that important for most of the use cases, just start by modelling some kind of system/relationships using tables

[regal wyvern]

How can I create a new schema inside my oracle db? I don't see any option for that

[paper flower]

Right click on database itself, then select New, then Schema

[regal wyvern]

Where can I find the database itself?

[regal wyvern]

I don't see that option

[paper flower]

I didn't really use oracle db, you can try creating schema using sql

[weak pier]

hi there =), im a student at an uni, saldy this uni is not good. they teach us basics and when the exams start everyting is complicated. i was wondring how can i learn python data manipulation with sqlite fast?

i tried youtube but evey course is simple and does not cover what i need to know

[regal wyvern]

How to view a description of a table --> columns description basically?

[paper flower]

rmb -> edit source

on table

[jade wing]

hi, for data manipulation within the database you should probably concentrate on sql as a language in general rather sqlite as a specific database implementation
for sqlite specific dialect things i think the offical sqlite docs are quite good
if it's manipulation of data in python after you have fetched the data from the database you can mostly ignore that the source of the data was from a database and concentrate on how to do the data manipulations with general python code

[paper flower]

@jade wing You work with db's often?

What do you prefer to use?

[regal wyvern]

which option is that?

[paper flower]

Go to DDL

[regal wyvern]

I don't want the DDL creation statement, I want to open a tab similar to this one that I don't know how I opened

[jade wing]

i use to, when i was a primary a developer by trade
i still do quite a bit of programming, but now it's mostly for automation of tasks and there is still some database interaction involved even if it's not as heavily as before

[jade wing]

it depends on the project, for more serious projects i mostly go for postgres
and for really small things and PoCs i might sometimes go for something simple like sqlite

same goes for personal projects on my free time

[paper flower]

And on python side?

[jade wing]

i must confess i was never much for ORMs, probably because i have never taken the time to learn to like them enough 🤷
instead i always liked to have control of exactly what is happening towards the database

[paper flower]

I'd say ORM's like Django ORM are really hard to use for complex queries, sqlalchemy provides you exactly the same interface as sql

[jade wing]

back when i was primarily a developer, the databases we connected to where mostly oracle and some mysql/mariadb and the occasional postgres

[paper flower]

So you can imagine how something like this would get transformed into sql:

stmt = (
    select(func.count(Client.id), Region)
    .join(Client.region)
    .group_by(Client.region_id)
    .order_by(func.count(Client.id).desc())
)

[jade wing]

yeah, it's the traditional ORM's that reminds me of the way back in the day when using ODBC on the micro$oft platform that i'm not so fond of

[paper flower]

Most active records orm's are somewhat hard to use

[jade wing]

yeah, very familiar but probably more portable that way 👍

[paper flower]

You can dynamically build queries too

sqlalchemy is very powerful

[jade wing]

or easy to use for basic stuff for a beginner that knows absolutely no sql and nothing about databases
but horrible when you what to do more complex stuff och need to tune performance of queries
at least that's my experience and how i have been viewing it

[dense jackal]

hello everybody. I don't know why i can't import routes module . Please help

[whole pebble]

try from shop.admin.routes import <name of blueprint>

[dense jackal]

name of blueprint what mean? i think i don't know

[whole pebble]

could you please show your routes.py file in admin folder?

[jade wing]

!code

[delicate field]

Here's how to format Python code on Discord:

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

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

[jade wing]

@dense jackal it's easier to help you, and for you to get help, if you use that instead of screenshots

[weak pier]

thanks

[whole pebble]

hey guys
what is the ORM equivalent of variable = session.query(Tablename).all()?
I have a table called "Product" and in it a column named "category"

I wanted to get all rows only from this "category" column
so SELECT category FROM Product; would look like this:
variable = session.query(Product.category).all()

but how would it look like in ORM syntax?
I tried:
variable = Product.query(Product.category).all()
and:
variable = Product.query(category).all()
but they give back errors

documentation and other tutorials are silent on that

[jade wing]

depending very much on which ORM you are using as far as i understandit , then again, i'm not an ORM person myself

[whole pebble]

I'm using it in flask

[paper flower]

You want to select categories of specific product?

[whole pebble]

yes, sort of
I wanted to select all of the available categories in this table

[paper flower]

Then why not just select categories?

[whole pebble]

that's why I'm doing
variable = session.query(Product.category).all()

I just wanted to know what does it look like in ORM syntax

[paper flower]

why Product.category and not Category?

Assuming it's a separate table

[whole pebble]

no no, it is a column in Product table

[paper flower]

query is legacy way to query in sqlalchemy

You can try using select, it works with columns fine

class Product(Base):
    __tablename__ = "product"

    id = Column(Integer, primary_key=True)
    category = Column(String)


stmt = select(Product.category)
print(stmt)
"""
SELECT product.category 
FROM product
"""

[whole pebble]

I suppose
I just started a project using Product.query.~~ etc notation
and with it, querying my categories column does not work
hence I needed to use db.session.query(Product.categories)

it works now fine, it just looks weird having two different ways of querying

[paper flower]

I'd use vanilla sqlalchemy instead of flask-sqlalchemy tbh

[whole pebble]

yeah, I think I will do so with my next project

for example, I'm querying users by user = User.query.filter_by(email=form.email.data).first()

it looks inconsistent next to variable = session.query(Product.category).all()

but oh well 😛

[paper flower]

user = session.scalar(select(User).filter_by(email=form.email.data))

[whole pebble]

thanks!

[crystal vapor]

('1','2','','','')''') ``` is this where i would add number values?

[slow grail]

what should I do for user ids in sqlite3? primary key doesnt allow the data to be reset (even if it gets removed)
and auto increment doesn't work

[crystal vapor]

('1','2','','','')''') ``` is this where i would add number values?

[oak oyster]

yes

[oak oyster]

why did you repost

[grim vault]

# 1 row, 5 columns, table needs to have 5 columns
cur.execute("INSERT OR IGNORE INTO AGE VALUES ('c1','c2','c3','c4','c5')")

# 5 rows, 1 column, table needs to have 1 column
cur.execute("INSERT OR IGNORE INTO AGE VALUES ('r1'),('r2'),('r3'),('r4'),('r5')")

# 2 rows, 2 columns, table needs to have 2 columns
cur.execute("INSERT OR IGNORE INTO AGE VALUES ('r1c1', 'r1c2'),('r2c1', 'r2c2')")

[fluid lava]

Is there a way to suppress the scientific notation created from pandas.to_sql? I'm using sqlalchemy/postgres underneath.

[torn sphinx]

@bot.command()
@commands.has_any_role(692760082085183519, 940008547993927691, 863878825376743475, 902292952381001779, 863879069253894166, 902292952381001779, 863879304110276668, 940008547993927691)
async def пред(ctx, member: discord.Member = None, *, reason = 'Отсутствует'):
    print(45)
    cursor.execute("""CREATE TABLE IF NOT EXISTS warning(guild_id BIGINT, user_id BIGINT, warn INT, count INT, moderator_id BIGINT, reasons VARCHAR)""")
    base.commit()
    print(1)
    
    warnings = cursor.execute("SELECT * FROM warning WHERE user_id = ? AND guild_id = ?", (member.id,ctx.guild.id)).fetchone()
    print("работает")
    
    
    if member is None:
        await ctx.send("Выберите участника")
        return
        
    if warnings is None:
        cursor.execute('INSERT INTO warning(guild_id, user_id, warn, count, moderator_id, reasons) VALUES(?, ?, ?, ?, ?, ?)', (ctx.guild.id,member.id,0,1,ctx.author.id,'Отсутствует'))
        base.commit()
        print(2222)
        cursor.execute(f'UPDATE warning SET warn = warn + 1 WHERE user_id = ? AND guild_id = ?', (member.id, ctx.guild.id))
        base.commit()
        await ctx.send(f"**{ctx.author.name}** Выдал предупреждение #{warnings[2]} {member} (случай # ) {reason}")
    else:
        cursor.execute(f'UPDATE warning SET warn = warn + 1 WHERE user_id = ? AND guild_id = ?', (member.id, ctx.guild.id))
        base.commit()
        await ctx.send(f"**{ctx.author.name}** Выдал предупреждение #{warnings[2]} {member} (случай # ) {reason}")       

How to make it so that if the reason was not specified, then Отсутствует was not written at the end, and if the reason was specified, then this was entered into the database and the reason was shown in the chat. Help me plz.

[ashen flame]

yeah

[rocky arch]

Hey,

I created a Table :

cursor.execute("CREATE TABLE test(id int PRIMARY KEY AUTO_INCREMENT, time DATE NOT NULL, amount INT DEFAULT 0, user BIGINT NOT NULL)")

And want to save some dates with ('%Y-%m-%d %H:%M:%S').
But It seems like it only saves ('%Y-%m-%d). How can I change this while creating my Table?

[unkempt prism]

Do you mean ints that are now floats?

That happens when the column is nullable and the default int dtype can’t handle it. Pandas have a new Int64 (with capital I) dtype that can.

The convert_dtypes() function is pretty good at fixing this up.

[unkempt prism]

Don’t use the type date and use timestamp or datetime depending on you sql server.

[rocky arch]

I use mysql. How do I create a table with datetime? is it just "time DATETIME"?

That was simple, thanks ✌️

[chilly canyon]

I want to build a project using sqlite & discord, and have it all stored online. Is this possible? I think I read something that sqlite only works locally? I saw something about that being an issue since it's serverless, but I'm new to sql so really not sure if this can be done or not

[tacit cloud]

you need to store the data somewhere, yes. "stored online" is kinda nonsense

[chilly canyon]

So having it on google drive isn't possible?

Or any other cloud option?

[tacit cloud]

that would be pretty horrible and defeat the purpose of using a database

[chilly canyon]

Could you elaborate? Why's it horrible to keep it accessible to the web instead of my own disks, and how does that defeat the purpose of a database? I just need something to store data

I don't see what the issue is with the location being on the cloud vs my PC

[crystal vapor]

Sorry my discord was glitching out for a sec

[tacit cloud]

@chilly canyon the point of a DB is you can update it efficiently. if every write also requires replacing a file in a file store like gdrive, you might as well just just a CSV file

[chilly canyon]

You can't just edit it if it's on the cloud?

[tacit cloud]

let's be specific. gdrive is a file store. if you have block storage in the cloud (AWS EBS, for example), you can write individual blocks rather than entire files

[chilly canyon]

I see. I just need something really basic, I can see why that'd be useless for probably most db uses. This is just a project to practice and learn with

[tacit cloud]

don't bother with cloud services if you want something basic

[chilly canyon]

My idea was to have a discord bot connected to the db online somewhere, and I could send commands in discord & update the db easily that way

Do you have any suggestions on a better way to do this?

[tacit cloud]

how are you going to run the discord bot?

wherever you run the bot, you might as well just stick the db there

[chilly canyon]

It's in python, using nextcord & it's hosted on heroku at the moment (I know it's not ideal, but works for what I need)

[tacit cloud]

ok, stick the db in heroku

[torn sphinx]

postgres add-on

of heroku

~~wrong channel, sorry ~~

[chilly canyon]

Would that just entail including the db in the heroku git I use for the bot's code? This is all entirely new to me & I'm very noob

[tacit cloud]

https://www.heroku.com/postgres#

[exotic mirage]

@client.command(hidden=True)
async def prefix(ctx, prefix=None):
    if prefix is None:
        return
    async with aiosqlite.connect('prefixes.db') as db:
        async with db.cursor() as cursor:
            await cursor.execute('SELECT prefix FROM prefixes WHERE guild = ?', (ctx.guild.id,))
            data = await cursor.fetchone()
            if data:
                await cursor.execute('UPDATE prefixes SET prefix = ? WHERE guild = ?', (prefix, ctx.guild.id,))
                await ctx.reply(f'Updated prefix to `{prefix}` successfully.')
            else:
                await cursor.execute('INSERT INTO prefixes (prefix, guild) VALUES (?, ?)', (',', ctx.guild.id,))
                await cursor.execute('SELECT prefix FROM prefixes WHERE guild = ?', (ctx.guild.id,))
                data = await cursor.fetchone()
                if data:
                    await cursor.execute('UPDATE prefixes SET prefix = ? WHERE guild = ?', (prefix, ctx.guild.id,))
                    await ctx.reply(f'Updated prefix to `{prefix}` successfully.')
                else:
                    return
        await db.commit()

I am using this to change prefix and it changes but still doesnt work for new prefix and works for , only
https://media.discordapp.net/attachments/865884512692207636/969810961559080990/unknown.png

async def getprefix(client, message):
    async with aiosqlite.connect('prefixes.db') as db:
        async with db.cursor() as cursor:
            await cursor.execute('INSERT INTO prefixes (prefix, guild) VALUES (?, ?)', (',', message.guild.id,))
            data = await cursor.fetchone()
            if data:
                return data
            else:
                try:
                    await cursor.execute('INSERT INTO prefixes (prefix, guild) (?, ?)', (',', message.guild.id,))
                    await cursor.execute('SELECT prefixes SET prefix = ? WHERE guild = ?', (message.guild.id,))
                    data = cursor.fetchone()
                    if data:
                        await cursor.execute('UPDATE prefixes SET prefix = ? WHERE guild = ?', (',', message.guild.id,))
                except Exception:
                    return ','

I believe here is some error

[oak oyster]

await cursor.execute('SELECT prefixes SET prefix = ? WHERE guild = ?', (message.guild.id,))
you forgot to pass the prefix here

[grim vault]

That's also a syntax error, you can't SET anything with an SELECT.

Also, the function is called getprefix() but starts with an INSERT? What's that about?

[compact marlin]

how could i make a leaderboard command for my discord.py economy bot using aiosqlite?

[narrow saffron]

get all the records, order them in descending order, then display it in an embed

[compact marlin]

yes but like how do i get all the record and how do i sort them?

[narrow saffron]

Use the SELECT statement, and you can sort them once you've fetchall, with either list.sort() or sorted(list) and then use the reverse keyword

[grim vault]

Or use ORDER BY in the SELECT statement to get a sorted list.

[compact marlin]

Well i know How to select a single user But i have No idea How to select everyone

[narrow saffron]

When you select a single user, I assume you're using a WHERE clause -- you can use the select statement without it to select every record

[remote dock]

how do i make it get the user object using the user_id if i do !get 177MR ?

i have this but

@commands.command()
  async def accept(self, ctx, appid):
    db = await aiosqlite.connect("appl.db")
    cursor = await db.cursor()
    
    await cursor.execute("SELECT app_id FROM applis")
    apples = await cursor.fetchone()

    if appid in apples:
      await cursor.execute("SELECT user_id FROM applis WHERE app_id = ?", (appid,))
      userid = await cursor.fetchone()
      user1 = await self.bot.fetch_user(userid)
      await ctx.send(f"{user1} test")

error:

raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: HTTPException: 400 Bad Request (error code: 50035): Invalid Form Body
In user_id: Value "(864825430350626826,)" is not snowflake.

[grim vault]

A select always returns a tuple even if you only select one column, so you need userid[0]. You can alos do this in one go, like:

  @commands.command()
  async def accept(self, ctx, appid):
    db = await aiosqlite.connect("appl.db")
    cursor = await db.cursor()
    
    await cursor.execute("SELECT user_id FROM applis WHERE app_id = ?", (appid,))
    sel_user = await cursor.fetchone()
    if sel_user is not None:
      user = await self.bot.fetch_user(sel_user[0])
      await ctx.send(f"{user} test")

Also, this:

    await cursor.execute("SELECT app_id FROM applis")
    apples = await cursor.fetchone()

    if appid in apples:
      ...

doesn't work because .fetchone() will just return one row at random and not the complete list. apples will just be a tuple with one entry only which may or may not be the appid you are looking for.

[grim vault]

^^

[remote dock]

oh, thank you!

[compact marlin]

Oh damn That’s cool. And then i just sort it using the statements u guys recomended before. Thank u man

[narrow saffron]

you're welcome!

[crystal vapor]

con = sqlite3.connect('example.db')
cur = con.cursor()

cur.execute('''CREATE TABLE IF NOT EXISTS AGE
(number interger)''')

cur.execute('''INSERT INTO AGE VALUES 
('r1'),('r2'),('r3'),('r4'),('r5'),('r6'),('r7'),('r8'),('r9'),('r10'),('r11'),('r12'),('r13'),('r14'),('r15'),('r16'),('r17'),('r18'),('r19'),('r20')''')


con.commit()``` Is this right? i am trying to store number data into the db

[grim vault]

Ah, no. If you want to store a number just use the number, the r1 and so on was just an describing example for a value.

[crystal vapor]

Ok so just the number with out the r

?

[grim vault]

Yes and no quotes.

[crystal vapor]

Ok and that will save all the values right?

[grim vault]

Yes.

[crystal vapor]

One last question it will show up on here right hold upo

@grim vault

[grim vault]

I don't understand? Your example just saves the numbers 1 to 20 into a table named AGE.

[crystal vapor]

Well i am making a profile command and i want the age values to show up on the profile embed

[grim vault]

You'll need to store a profile for each user. The age should be calculated based on a the date of birth.

[crystal vapor]

right. u got any examples of this. cuz i dont know how?

[crystal vapor]

Is there someone who can help me with that?

@grim vault

[peak thistle]

Hi.

...

[tacit cloud]

migrations in sqlite are pretty tough because, as it says, it doesn't support ALTER

do you care about the data in the database? if not, consider just dropping the DB and starting fresh with the new schema

you should probably consider switching to postgres if you want to use flask_sqlalchemy to do migrations

[fair girder]

I have to do something in mysql -- which would be 1'' of my time in python, but I have no idea how the solution looks like in sql.. Can anyone offer a pointer? In the same table, there is an attribute x that takes 2 values and another attribute y with float values. How do I go about getting the difference of the total y per x ?

[tacit cloud]

by "attribute" do you mean column/field?

what do you mean "takes 2 values"?

if you're asking for help writing sql, show us a schema

[fair girder]

hmm ok, let's say attribute x takes values 'Deposit' and 'Withdrawals' and y is a double showing the amount of money e.g. 500.43
these are in the same table and I want to write a bit in sql that outputs net revenue, where net revenue would be deposits - withdrawals

[tacit cloud]

easiest way to do this is to calculate the revenue yourself after getting sum of income/expenses

select sum(y), x from ... group by x

otherwise, you need a cte/subquery/union

[fair girder]

cte/subquery/union can you provide any pointers?

or the logic

is it possible on the select statement to do something like this?
SELECT ... SUM(y if x = deposit) as deposits, SUM(y if x = withdrawal) as withdrawals ...
and then get their difference??

or double sum? SUM(SUM(y if x = deposit) - SUM(y if x = withdrawal))

ok i think I figured it

thanks

[torn sphinx]

how do u select an user from a specific table a change a a value on a certain field?

[tacit cloud]

only if you don't have access to psql :P why would you switch to mysql?

[jade wing]

i think you're looking for this: https://www.sqlite.org/autoinc.html

sqlite will not fit the bill for any site that is even moderately "large", if you want scalability then postgres is better than mysql/mariadb

[dawn loom]

Hello guys , I'm just wondering why my VSCode is not helping me typing mysqli functions? is there any extensions i need to add ?

[tacit cloud]

@clever gate you're already using sqlalchemy... just change the dialect

[jade wing]

i think a lot of people end up using psycopg2 to connect to postgres
here is a bit of authoritative documentation about how to use postgres with sqlalchemy:
https://docs.sqlalchemy.org/en/14/core/engines.html#postgresql
https://docs.sqlalchemy.org/en/14/dialects/postgresql.html

[jade wing]

even if it's database related it's probably more vs code specific, so i hope someone in #editors-ides might be able to help you with that

[opal lion]

I'm trying to find the correct database for my business project. I am making a program that takes user input for money in your bank account, your weekly salary, and returns a spending budget according on events you have planned to spend money on in the future. Where can I find a database similar to this?

[keen minnow]

Any SQL db would do the trick

[regal wyvern]

How to represent an aggregation function query in relational Algebra?
For example, how would I go about expressing this statement:
"retrieve the maximum salary value from the EMPLOYEE relation"

in a relational algebra expression?

[keen minnow]

What do you have in mind so far? Or what have you tried?

[regal wyvern]

I'm asking because I'm so confused by the slides that I'm studying,
it literally says "Aggregate queries cannot be represented in relational algebra"

However!!! 2 slides ahead I see this:

[keen minnow]

I mean, you are right, that's what MAX is for. Not sure what your teacher has in mind specifically

[regal wyvern]

I waste a lot of time on details like this, it's ruining me very harshly
I think I have perfectionism syndrome

[keen minnow]

It has also been a while since I went through that type of class.
So in such case, I would probably go back to the definition(s) given by the teacher and see how Max fits (or not) in that situation

[spiral mason]

You need help

[regal wyvern]

Thank you 🍰

[keen minnow]

either mysql or postgres would do great.
They are dedicated services and thus do require connection(s) to them.

Their docs are also pretty exhaustives and there are tons of tutorials online about them. There isn't a specific one I would recommend though.

[spiral mason]

Your errors and what?

[paper flower]

If you need something low-level - use psycopg2 or asyncpg, if you need an orm/query builder - use sqlalchemy

I'd recommend sqlalchemy, you still can execute raw sql with it if you need to

[keen minnow]

SQLAlchemy is a toolkit to access databases.
It's not a database itself.

If you could describe your problem more in details, it may be easier to help you

I don't use sqlite, but the error message seems rather explicit: No support for ALTER of constraints in SQLite dialect

in SQL, there are some ALTER commands to modify stuff

it says that SQLite (or its driver), does not support it

no sure if it helps, but from a quick google search: https://stackoverflow.com/questions/30378233/sqlite-lack-of-alter-support-alembic-migration-failing-because-of-this-solutio

[spiral mason]

Idk why ppl use SQL and say they wanna scale it in future...
I'm like just use mongoDB it just works like a charm

[paper flower]

I'd use postgresql instead

[keen minnow]

I have no idea about your requirements or problem you are trying to solve other than you having a problem with some migration

[paper flower]

sql is better for relational data than mongo 😉

[keen minnow]

not sure why one would start a project with mongo. There aren't any benefits to it

[spiral mason]

I don't disagree but if you know you wanna scale the project in future why use relational at all