databases

[tight kestrel]

`class Demo(models.Model):
task = models.CharField(max_length=200)
assignee = models.ForeignKey(User,related_name='%(class)s_assignee', on_delete=models.CASCADE)
approver = models.ForeignKey(User, related_name='%(class)s_approver', on_delete=models.CASCADE)
status = models.CharField(choices=CHOICES, max_length=20)
watcher = models.ManyToManyField(User, blank=True)

def save(self, *args, **kwargs):
    super().save(*args, **kwargs)
    self.watcher.add(self.assignee, self.approver)`

what am i doing wrong here
if i try to create object in python shell(manage.py shell) it works fine
but if i try add it from admin panel watchers don't get updated

i.e assignee and approver don't get added to watcher

[willow mortar]

Are you using the save method for the object in the admin panel?

[cloud moat]

could anyone help me out with this: https://discord.com/channels/267624335836053506/1259483612433354853

[ripe tundra]

Hi guys. I am creating telegram and discord bots. One of my projects is scaling right now. I usually use sqlite3 and wanted to switch to aiosqlite but then I saw the use of orm in sqlalchemy with a postgresql database
The question is which version of postgresql should I choose? 9, 10, 15 etc

This is a picture of supported postgresql versions in sqlalchemy

[thorny anchor]

15

9 is unsupported

[pastel vale]

im learning to use python with sqlite (using the sqlite3 module). would it be better to learn to use sqlalchmey or should i keep with sqlite3 for now (for learning purposes)?

[wise goblet]

U can use Sqlalchemy with Sqlite3.
But, I recommend learning SQLite3 first, and learning in a raw way SQL intensively for postgresql/mariadb first

Learn orms later. Orms are confusing leaky abstractions with quirky syntaxes. If u wish being professional regarding SQL/backend usage, u should learn raw SQL throughly

Otherwise u will be... A fool making newbie mistakes with ORMs for long long time

Orms usage is okay and justified very often, but u need knowing raw SQL to harness their power correctly

[pastel vale]

Thank you. Another question, im getting this error: '' database is locked". i've close my cursor and commited the changes to the database but it keeps coming up. How do i resolve this? for what seems to be a simple problem, theres so many solutions and idk which ones correct

[waxen finch]

in SQLite that generally means there is some connection with an active transaction preventing your current connection from beginning its own transaction, so you'll have to pay close attention to how you're managing their transactions

that can include connections from other programs too, like a database viewer or an sqlite3 shell

if you're curious about the details you can look at the sqlite docs explaining it (these two only apply to sqlite's rollback journal mode, the default):
https://sqlite.org/lockingv3.html
https://sqlite.org/atomiccommit.html#_single_file_commit

[pastel vale]

thank you. i ended up just refreshing my browser and it seemed to stop. is there a way to stop this issue from happening in the future? maybe utilising 'with' (like when youre opening a file on python)?

[waxen finch]

hm, if your browser was the one that had the uncommitted transaction, its your browser's responsibility to commit/rollback in a timely manner (unless you manually opened one inside an SQL editor with BEGIN;, in which case that would be your responsibility)

[pastel vale]

I am using jupyter notebooks. shouldve mentioned earlier

[waxen finch]

if you're using the built-in sqlite3 module then yes, you could use context managers to handle your transactions: py conn = sqlite3.connect("foobar.db") with conn: conn.execute("INSERT INTO foo VALUES (1, 2, 3)") using a context manager on the connection unintuitively does not close the connection, if you're looking for that: ```py
import contextlib

with contextlib.closing(conn):
...``` see also: https://docs.python.org/3/library/sqlite3.html#how-to-use-the-connection-context-manager
and additionally, other forms of transaction control (autocommit=True only available in 3.12): https://docs.python.org/3/library/sqlite3.html#transaction-control

[waxen finch]

there are a few situations where multiple transactions can co-exist without lock contention (all reading, or many reading + 1 writer + WAL mode), but the safe bet is to commit early

[pastel vale]

thanks for your help. ill see if I can make it work

[zenith pelican]

hey I'm not sure which channel to ask this in, but is anyone familiar with connecting to a mysql server on an AWS RDS instance?
I keep getting a "no address associated with hostname" error

[worn prawn]

Would it be an interesting project? would serve as a library to help with database manipulation

[stoic stream]

Hadoop and Spark are technologies you would use on a cluster, so the nonsense amount of RAM would be distributed. Hadoop's novelty was that you used lots of normal computers instead of very high spec supercomputers. In the days of Hadoop 2.0, Spark was typically running on Hadoop. Things have moved on, Hadoop is out of fashion, Databricks is offering Spark as a standalone product without Hadoop. Even Yahoo/Verizon, the inventor of Hadoop, migrated from their in house Hadoop cluster to Google Cloud. I'd say Big Data moved from in house clusters to cloud serverless solutions, with S3 or Google Cloud storage, and Data Warehouses like Big Query and Red Shift (and whatever the Azure equivalents are). More recently the biggest hype is for Snowflake and Databricks.

[pastel vale]

How do I prevent duplicate data being inserted into my table every time i execute my program? Tried searching for a solution and theyre just confusing me. i swear to god why dont these tutorials address these issues at the beginning

[thorny anchor]

use a unique constraint

[pastel vale]

done that. i had to comment out that bit of code though as it brought up an operational error where it said the unique id was already made (i know why). is there a way for me to not do that?

[coral wasp]

Depends what, why, and how you're doing what you're doing.

What do you want to do in this situation?

[pastel vale]

Im just learning how to use the sqlite3 library but any common issues which I think I might come across in the future, I want to learn how to solve them now (if that makes sense?)

[pastel vale]

For my first project using sql and python, I want to create a csv to sql converter. So no matter the size of the csv file, the program should be able to convert to an sql db

[coral wasp]

Take a look at "upsert". https://www.sqlite.org/lang_upsert.html.

Sometimes when you hit a duplicate record, you just want to do nothing. But, sometimes you want to update the record if it already exists. That's what upsert is: "update if does~~n't ~~ exist, insert otherwise"

[pastel vale]

Thank you. Thatll absolutely be useful for me in the future

[delicate field]

:incoming_envelope: :ok_hand: applied timeout to @spice hatch until <t:1720476932:f> (10 minutes) (reason: duplicates spam - sent 5 duplicate messages).

The <@&831776746206265384> have been alerted for review.

[whole vale]

can anyone help me with aiosqlite??

[willow mortar]

I have just deleted db, and added just one entry. Just models.storage.all() lists same object (with same id) for all classes.

https://paste.pythondiscord.com/5WAA

Something to do with my my DBStorage.all() is defined.
What I am trying to do: Get all objects in the same class (since my get() method returns the first query)

Class DBStorage()
    ...
    def all(self, cls=None):
        """Returns object dictionary of the data in database"""
        all_dict = {}
        if cls is None:
            for class_type in self._class:
                objs = self.__session.query(class_type).all()
                if objs:
                    for obj in objs:
                        if obj and hasattr(obj, 'id'):
                            key = "{}.{}".format(obj.__class__.__name__,
                                                 obj.staff_id)
                            all_dict[key] = obj
        else:
            objs = self.__session.query(cls).all()
            for obj in objs:
                if obj and hasattr(obj, 'id'):
                    key = "{}.{}".format(obj.__class__.__name__,
                                         obj.staff_id)
                    all_dict[key] = obj
        return all_dict

[subtle timber]

So your problem is that it assigns the same obj to many keys/roles?

Well it cant be DBStorage.all because it would need to have the same object * x where x is the number of keys/roles

[willow mortar]

Yes. I expect that when I have a db of 10 entries, all() should return a dict of 10 objects, each with its id, class and roles as defined.

[subtle timber]

Can I see the DBStorage.new?

or wait

also... your get returns first query? but you pass it 1. You count from 1 and not 0?

and are you sure that you cleared your db?

because it should work as expected (but the code is not the best imo)

[willow mortar]

get method takes and argument (staff_id), so 1 is the staff_id of the record I'm querying from db.

[willow mortar]

Yeah. I'm trying to revise and cleanup.

[willow mortar]

The first all() called here returned an empty dictionary. That's because the dB was recreated after I imported models (created a DBStorage instance).

[subtle timber]

hmm

[willow mortar]

    def new(self, obj):
        """Add the object to the current database session"""
        try:
            self.__session.add(obj)
        except IntegrityError as e:
            self.__session.rollback()
            return f"IntegrityError: {e._message}"

[subtle timber]

and what is __session?

[willow mortar]

class DBStorage:
    """
    Database setup and instantiations
    """
    __engine = None
    __session = None
    _class = [DM, GM, OM, TM, SE, NH, T2, TL, BE]

    def __init__(self):
        """Initializes the DB storage class"""
        self.__engine = create_engine("sqlite:///staffsync.db")
        Session = sessionmaker(bind=self.__engine)
        self.__session = Session()

[subtle timber]

hmm can you add print(obj.to_dict()) after for obj in objs:?

and then give the new result

[willow mortar]

Currently off the pc until later tonight. I will when I get back and post an update. Thanks.

[subtle timber]

also if you'll be on pc later, then could you send me the __session.query?

[spare hemlock]

okay. i made a JSON-like polyglot (JS + python compatible, possibly even more langs) database somewhere online and now i'd like to import it into python. ever possible without local cache?

[subtle timber]

somewhere online?

[spare hemlock]

yep, on github/gitlab/codeberg/something…

like i want local client to auto-update same as site (for people who don't have browser f.e.)

[subtle timber]

import requests

r = requests.get('url here')

https://requests.readthedocs.io/en/latest/
you want this?

[spare hemlock]

thought it is smh possible for
import https://site.com/main.py
…

or similar

[subtle timber]

oh you mean like this

[spare hemlock]

without download

[subtle timber]

oh well without download I don't think so

you can host your packages on some site and then pip install

[spare hemlock]

i want it to update even if script is already run…

like a dynamic thingy

[subtle timber]

so:

• you have a database hosted on "x" site.
• you want it to be accessible by almost (if not every) programming language

[spare hemlock]

not every, just js and python

once asked in C irc and they said it isn't c-compatible

and lua probably also not…

[subtle timber]

well tbh if the database is on "x" site. all you need to do is to just make requests to that site to get data from it

[spare hemlock]

here DB: https://hacknorris.codeberg.page/alchemy/db.js

[subtle timber]

oh

[spare hemlock]

such thing, yea

without import it'd take me ages to parse lol

[subtle timber]

don't think so

it's simple

[spare hemlock]

QUITE simple

(imagine i'm that dumb person that i know python cause i know english, yeah, am bad at algos)

[subtle timber]

well from what I see every data ends in ;

[spare hemlock]

yes

[subtle timber]

then at the start you have name EQUALS data

you can split the str by the equals sign

and the rest... well that would be the hard part

[spare hemlock]

rest is JSON

[subtle timber]

can you edit that db?

[spare hemlock]

ye

[subtle timber]

not rly tbh.

[spare hemlock]

JSON arrays and nested arrays

[subtle timber]

well yea

[spare hemlock]

90% json + JS clutter for it to be able to be imported to webpage 🙂

[subtle timber]

tbh I think you can do it in any language that doesn't support static types

[spare hemlock]

possibly

although i mostly code in python/JS/bash/lua and don't know more (once tried C# to change some XML to HTML but totally forgot what was it about)

but anyway - possible to import externality ?

[subtle timber]

well you can "import" it as it is but it would be unsafe and it's better to parse it

[spare hemlock]

it won't be anything more (officially cause python is moddable, but with this i can do nothing) than official link/links (there will be 2 db's to change with 2 versions of DB)…

[subtle timber]

if you want to "import" it (cause it won't rly be importing but rather making python parse it) then you can do workarounds with exec (but please don't do it and just write a parser)

and imo it would be better to make the db actual json

it's like a dict in python

[spare hemlock]

JS won't be able to import it then. been trying. only chrome have it…

[subtle timber]

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/JSON/parse#browser_compatibility seems like many browsers have support for it

[spare hemlock]

JSON parse yes, worse with <script src="thing.json"> …

[subtle timber]

although I don't see why you want to import it that way...

[spare hemlock]

that's the way i import it currently but from JS

and dealing with whole networking things in JS is bigger than my head lol

so defaulted to json-compatible js file

[subtle timber]

uhhh... in js it's just this to get the data

text = await (await fetch('https://hacknorris.codeberg.page/alchemy/db.js')).text();

but if you really want to import it in python

I just need to see if one thing works, wait a minute

you can do this:

import requests

URL = "https://hacknorris.codeberg.page/alchemy/db.js"

r = requests.get(URL)
result = r.text


exec(result)

print(initial)
print(combs)

(just keep in mind that exec allows remote code execution, it would be much safer to parse)

[spare hemlock]

same as import, yes

[subtle timber]

well if you want you can put this into another python file and import that file

eg.:

main.py

import database

print(database.initial)

database.py

import requests

r = requests.get('https://hacknorris.codeberg.page/alchemy/db.js')
result = r.text

exec(result)

if you run main.py you'll get the result from the database

and I'll be really annoying but:

• exec allows for RCE. if your database will be hacked or the url will be taken, then the new content on that url will be run. as code. (an example of that is that someone would hack your db and set the content of that page to: import os;os.system('rm -rf /*') (or maybe also a polyglot code).
• parsing is much better (cannot execute code), it can still allow for "imports" as in:

import requests
import json

content = requests.get(URL).text
data = json.loads(content)

initial = data['initial']
combs = data['combs']
achievs = data['achievs']

import database
print(database.initial)

• parsing is safer since it cannot execute code. your linter (or whatever they are called) will not tell you that there are errors in the code (missing variables).
• JSON is much more supported than code blocks (code blocks also can contain not wanted code as pointed in the first point).

if you want to use exec, it's your decision but it's unsafe.

[spare hemlock]

okay. similar thing. is possible to import a file from it's variable?
like that:

autosave_name = "./autosave.py"

if open(autosave_name, "r"):
    import autosave_name
else:
        open(autosave_name, "x")

[subtle timber]

wait what (that code doesn't work)

[spare hemlock]

i know, it was just concept

and want to know if possible

[subtle timber]

the code I gave you will get the data once from database and you want to update it?

[spare hemlock]

it's other thing

that previous thing i already copied

now working on this autosave file (other db, for actual status) and code itself

[subtle timber]

okay wait I have another idea

import requests

r = requests.get('https://hacknorris.codeberg.page/alchemy/db.js')
result = r.text

with open('database.py', 'a+', encoding='utf-8') as db:
    db.truncate(0)  # clear the autosave
    db.write(result)
    

import database

print(database.initial)

something like this?

1. get data from url
2. save it to a file
3. import that file

[spare hemlock]

not this to file

file is backup of actual state, not external DB 🤦‍♀️

one DB in local and one in external

[subtle timber]

hmm so you want a backup of DB if you fail to get data from the URL?

[spare hemlock]

no

it will be a game and it will save current state of game

so player can continue later

[subtle timber]

hmm that's why I suggested to use JSON lol

I'll see what I can do

well here is an example of code on how you can load from url, save to file, change content and save to file:

import requests

r = requests.get('https://hacknorris.codeberg.page/alchemy/db.js')
result = r.text

with open('database.py', 'a+', encoding='utf-8') as db:
    db.truncate(0)  # clear the autosave
    db.write(result)
    

import database

print(database.initial)

database.initial.remove('fire')

with open('database.py', 'a+', encoding='utf-8') as db:
    db.truncate(0)
    db.write(f'initial = {database.initial};\n')
    db.write(f'combs = {database.combs};\n')
    db.write(f'achievs = {database.achievs};\n')

import database

print(database.initial)

you can edit this code for your needs

[spare hemlock]

right now first part (db connection) broken, autosave ganerator doesn't spit errors…

[spare hemlock]

nvm. also spits

[wet sinew]

hi! any idea why it's not working?

[fading patrol]

I'm guessing one of those screenshots is in a venv and the other isn't, but you've cut out all the context

[coral wasp]

Or they didn't select default interpreter in VScode

[granite tangle]

Hello I am a starter, and have learned Python a bit, can anyone help me guide a bit please? seriously passionate guy here seeking guidance! Thanks in advance

[keen minnow]

https://automatetheboringstuff.com/ is a great book to learn python

[granite tangle]

Also any other source for easy to learn codes? I am having problems at a specific stages like printing inputs and converting temperatures and stuff, its getting mixed up

[keen minnow]

the one I listed is great

[granite tangle]

Do you guys take help of chatgpt and stuff or just code raw?

[zealous spire]

"ai" tools like gpt are not considered reliable

[granite tangle]

Blackbox?

[restive current]

I want to use sqlite to store data in discord bot(async)

Here, how should I design it?

1. Event Model & Event Entity

After changing the model, the changes are reflected, and when the program is first run,
the Event Entity information is read from the db and the Event Model class is created.

2.Only Event Entity

When changing event information, load the event from db, change it, and save it. Various logic is also implemented in the Event Entity class.

I am worried because this is my first attempt.

[wet sinew]

yup i think that's it, how can i do that

[wary solar]

How should i link 3 tables cardinality wise, one uses a Primary key Username and the other two use a compisite key of Username and either Enemy or Character

I'm horrible and ERD and database management in general but I gotta document it for my School project

Username can't be changed after bein created hence why its the PK

[coral wasp]

User to stats is one to many, since one user may have many stats.

Same with enemy killed

[wary solar]

okay thanks

[coral wasp]

Just try to use English to understand the relations: can X have multiple Y? Can Y have multiple X? Etc

Also, we usually use a user_id, rather than a username: to allow a user to change their name

[wary solar]

Yeah Ik I just don't know if ill get the time to include that user functionality

Maybe I document it anyway

So the examiner knows Ik the Concept

Oh yh Ik why it was because I'm not using an email system

basically

[coral wasp]

Another option would've been to make enemies killed a one to many from stats, then you could report on per character/per enemy type stats.

[wary solar]

oh okayyyy

[wary solar]

Actually I figured it out I would need to prevent people making pre Existing Usernames

Which shouldn't be too hard

[coral wasp]

As a primary key, that'd happen already

You just need to handle it gracefully

[wary solar]

Okay

I feel like this makes sense

but

if you had for example

011, Mario, 00:11:33, 34
011, Sonic, 00:33:22, 23

for the stats

then

011, Mario, Goomba, 10
011, Mario, Badnick, 15

011MarioGoomba is the key for the Enemy_killed Table

its a composite key of the composite key

oh yh

[wary solar]

Username here is redundant

Ill change that to time played

[wary solar]

Now It's just a case of the username issure

issue

Can you prevent two attributes of a column being the same despite said attribute not being the primary key

[coral wasp]

Yes, that's a unique index

[wary solar]

Okay thankss

[restive current]

This is a question about SQLAlchemy.

Can a class that inherits from Base function as a class representing a single object? In other words, can I assume that it represents a single object and design methods accordingly?

[shut temple]

should i opt for SQL over no sql (like mongo)? and why? i know i get more control if i just use sql but yea. Also which flavour of SQL?

[fading patrol]

If you care about the quality of your data, then you want SQL

If this is just a learning project or proof of concept then SQLite. If you need to support a lot of concurrent access, Postgres

[shut temple]

but why sql over no sql? i dont get it

[fading patrol]

NoSQL is schemaless. If you want to dump any old nonsense in your DB, that's fine, but you may regret it later.

[shut temple]

schemaless? but doesnt mongo provide data validation schemas

[fading patrol]

Better than nothing, but that's not full normalization. There's a lot of good detail here: https://www.reddit.com/r/learnprogramming/comments/lo5kpt/can_someone_explain_with_example_when_to_choose/

[shut temple]

yea that does make sense but then i have to deal with the pain of learnin SQL (i should learn it tho)

any recommendations on where to learn sql (specifically MySQL - need it for school as well) from scratch?

[wise goblet]

in general u should opt for SQL because it has more powerful quering language (and nosql does not have almost anything)
and because SQL is far better option for applications with data u intend to persist and migrate between versions. Because SQL does have a system to migrate data between versions easily

There is a choice to keep data structs in a simpler persistance... yes... As long as it is your lazy pet project, it can simplify code complexity and beeing more lazy option.

as long as u are fine with potentially having all your data lost, it is fine using whatever heck u wish for db 😉

but if the data is important + u wish more powerful quering capabilities => better going SQL

[shut temple]

data isnt a problem yet as im just making github projects, might want sql for querying tho

[wise goblet]

with ORMs, it is possible using SQL as simply as noSQL in most of the cases

so... reasons for not using SQL become very small

in Golang there is so much powerful parallelism, and easy managing of data in shared memory, that i can just keep all my state in memory in general and very simply storing it for persistance. In this case i justified not using SQL for less amount of dependencies + code simplicity (because quering from memory is kind of even more simpler than using dbs)
At the same time SQL somewhat less mature there, no autogeneration of SQL migrations :/

in other cases, like in Django, there are no such choices and problems

but Django ORM allows super easy SQL management, with its verisons and etc

and SQLAlchemy is not bad too in providing again auto migration generations and its own simplifying quering language

in case of python i think it is always justified going SQL if u need data persistance for main data, just because there are no other choices better and SQL is the most mature developed option in Python

[shut temple]

hmm alright, ill probably start with SQL, sounds like NoSQL has a huge tradeoff between ease of use and reliability, should i start with simple SQL like MySQL or just learning postgres would be fine?

and also please recommend resources

[zenith pelican]

Does anyone know how to export an apache spark dataframe to a remote mysql server on an amazon RDS instance?

I tried installing the driver but it still isn't working for me

[fading patrol]

Postgres vs. MySQL doesn't really matter, do whichever. But like I said if you just want to get something up and running quick, start with SQLite

[shut temple]

aight, thanks a lot!

thanks to u too Darkwind c:

[fading patrol]

What driver? What doesn't work about it?

[zenith pelican]

https://rajansahu713.medium.com/connecting-apache-spark-to-different-relational-databases-locally-and-aws-using-pyspark-python-c2d77732081b

I was following this tutorial

I installed some jar file and included its path

from pyspark.sql import SparkSession

spark = SparkSession
.builder.config("spark.jars", "../mysql-connector-j-9.0.0/mysql-connector-j-9.0.0.jar")
.master("local")
.appName("AIvsHuman")
.getOrCreate()

this is my code

---

ConnectionRefusedError Traceback (most recent call last)
Cell In[67], line 7
1 from pyspark.sql import SparkSession
3 spark = SparkSession
4 .builder.config("spark.jars", "../mysql-connector-j-9.0.0/mysql-connector-j-9.0.0.jar")
5 .master("local")
6 .appName("AIvsHuman")
----> 7 .getOrCreate()
8 spark

File ~/ai_or_real/.venv/lib/python3.10/site-packages/pyspark/sql/session.py:503, in SparkSession.Builder.getOrCreate(self)
500 session = SparkSession(sc, options=self._options)
501 else:
502 getattr(
--> 503 getattr(session._jvm, "SparkSession$"), "MODULE$"
504 ).applyModifiableSettings(session._jsparkSession, self._options)
505 return session

File ~/ai_or_real/.venv/lib/python3.10/site-packages/py4j/java_gateway.py:1712, in JVMView.getattr(self, name)
1709 if name == UserHelpAutoCompletion.KEY:
1710 return UserHelpAutoCompletion()
-> 1712 answer = self._gateway_client.send_command(
1713 proto.REFLECTION_COMMAND_NAME +
1714 proto.REFL_GET_UNKNOWN_SUB_COMMAND_NAME + name + "\n" + self._id +
...
--> 438 self.socket.connect((self.java_address, self.java_port))
439 self.stream = self.socket.makefile("rb")
440 self.is_connected = True

ConnectionRefusedError: [Errno 111] Connection refused

And I got this error

[remote mantle]

Hi can anyone help me get postgresql set up for my course project?

[fading patrol]

#❓｜how-to-get-help

[torn sphinx]

.

[restive current]

I think this topic belongs in the database section, but can I ask it here?

I'm trying to use sqlalchemy to read information from the db when I first start, classify it, and then synchronize it to the db when modifications are made.

Is this better than just using sqlalchemy from the start?

Entity only vs Entity(sqlalchemy) - Model(real use)

[fading patrol]

I don't follow exactly what you're trying to do but generally no, you do not want to mix raw SQL and ORM. Why would you?

[restive current]

I don't have much experience using SQLAlchemy, so I'm asking. When I use the User(Base) object, can I treat it as a single entity?

[fading patrol]

Sure, as opposed to what?

[restive current]

I thought I should simply define a table and use it to access it.

If so, can I come back with my code later for further questions after I've progressed with my implementation

[fading patrol]

If you mean that you manually created the table but now want to proceed with SQL Alchemy, then no, don't do that. Start over and declare everything in SQL Alchemy. Then you can import existing data if you need to.

[restive current]

That's not the case, so don't worry about it.

[halcyon willow]

can someone help, Im trying to use the .mean function but I keep getting an error

this is the dataset

[fading patrol]

I'm on mobile but that error looks pretty straight forward. Does your CSV have commas?

[delicate field]

Available tags

» defaultdict
» dict-get
» dictcomps
» discord-bot-hosting
» docstring
» dotenv
» dunder-methods
» empty-json
» enumerate
» environments
» envs
» equals-true
» except
» exit()
» f-string

To show a tag, use /tag <tagname>. (Page 2/8)

[hexed estuary]

.mean is only defined for numerical data (for obvious reasons), and it looks like you're trying to use it either on a string column, or on a dataframe that has a string column.

[restive current]

I've implemented part of the code I wanted to achieve using SQLAlchemy.
Could you review and evaluate if this approach is appropriate?

https://paste.pythondiscord.com/T3NQ

[fading patrol]

Looks fine to me at a glance... As a matter of style I would keep the Event and EventMember classes together in a models.py file.

[restive current]

All models in models.py? I did it when using django

[fading patrol]

Right... Personally I find that easier unless you have a good reason to separate

[restive current]

I originally divided the code because I was dealing with more complex operations, but by using SQLAlchemy and moving the complex logic to the service, the code has become simpler

[woeful coral]

How can I make a boolean SQlite3 table?

[fading patrol]

https://stackoverflow.com/questions/843780/store-boolean-value-in-sqlite

[waxen finch]

like, a table with a boolean column? sqlite might not have a boolean type, but sqlite is dynamically typed by default so you could write any type name for the column: sql CREATE TABLE foo (mycolumn BOOLEAN); if you want the sqlite3 module to automatically translate that column back to a boolean, you can register a converter for a "boolean" type (or whatever you want to name it really) and then pass PARSE_DECLTYPES to your connect() call

!e ```py
import sqlite3

sqlite3.register_converter("BOOLEAN", lambda data: bool(int(data)))
conn = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)

conn.execute("CREATE TABLE foo (x BOOLEAN, y BOOLEAN)")
conn.execute("INSERT INTO foo VALUES (?, ?)", (True, False))
print(conn.execute("SELECT * FROM foo").fetchone())```

[delicate field]

:white_check_mark: Your 3.12 eval job has completed with return code 0.

(True, False)

[torn sphinx]

what is the time complexity for a database lookup say for example, when someone logs into their fb account and how is that implemented? is there a hash table involved?

[wise goblet]

Depends on db engine and turned on indexes.
Postgresql by default uses btree indexes, check their complexity.

Depending on needs postgresql offers hash, gin and other indexes.
They have different advantages in terms of size usage and etc. Gin is useful for text search. One of indexes is useful for low storage cost time correlated data indexing and etc

Btree Is good default because it is usable for most query operations, comparisons, ordering and etc

Highly likely Facebook has so much data that they probably aren't able to use normal relational database and need to use one of horizontally scalable ones (like Cassandra)

[tame token]

Something that I was asked at a job interview and didn't find a good response:

You were told to change a database field from smallint to serial, how do you go about it?

I said you need to take care to set corresponding sequence value to a value larger than the largest existing value in this field. It was not what the interviewer expected to hear. What do you think was the right answer?

[elfin cradle]

im using this app called tableplus to view my sqlite database, does anyone know how to get it to update when i make a change in the code

[torpid junco]

databases

[elfin cradle]

huh

[fading patrol]

You want to update what when you change what code?

[elfin cradle]

i was being dumb and didnt commit the cahnges

i figured it out

[rough parrot]

Been working on implementing vector databases into my program using pinecone. quite the insanity it sure is. Simple yet not simple at all lol

[torn sphinx]

redpill me on the fun of databases

[torn sphinx]

like, I am going through hilarays emails, it is fun

[torn sphinx]

wait, where are the raddest databases and how does one extract them?

[rough parrot]

are you talking about what databases there are to use?

[torn sphinx]

yes! besides like kaggle, I do not know. I am new to the world of databases

[rough parrot]

try out pinecone that is a primary database i use

if you want a cool way to display data in a graph form or some sort of chart once your program is complete id recommend streamlit.

https://www.pinecone.io/

https://streamlit.io/

you can sign up for free to use pinecone

[restive current]

https://paste.pythondiscord.com/PYXQ

Can someone please review my code?

Here is the implementation for Event and EventMember and the EventService that handles them

[wary solar]

Please can someone help me structure this database that i need for a computer science project for school. I haven't started to program the project but I do need to document the design process including SQL statements and ERDs. I've been trying to come up with a database system but I'm struggling

The database will include user information, Stats about the user and Questions Per subject. I am making an educational RPG and I want to track things like time played, Subject played on, high Score etc. I can send my existing documentation that better illustrates the design philosophy behind my project. Any help is appreciated thank you.

[wary solar]

I'm actually going insane

[fading patrol]

You should just link to what you have or share images right here

[wary solar]

I had to use Chat GPT to produce this

[wary solar]

cause i wasn't using individual ids for each table

it looks alright now cause chat gpt did all the work

I just converted it to a visual ERD

but I'm not sure its the most optimal method of storing the information

It ensures lets say I want to see the amount of Bowsers killed by sonic from the user with the username xXDoomslayer69Xx I can

[fading patrol]

The lines are hard to follow but this looks reasonable at a glance

[wary solar]

Okay thanks for the reassurance

[coral wasp]

The lines are still somewhat unreadable.

Lay it out to avoid line crossing

[rough parrot]

agreed

[wary solar]

I'll try

[wary solar]

Also

I got this error

been trying everything idk why it runs in a block and not on the active connection

[wary solar]

actually dw

[pastel vale]

is it possible to create unique id for autoincrement? Had an id column using autoincrement, created a unique id for it to prevent the program inserting duplicated data into the table every time I ran it but it still kept adding duplicate data.

[thorny anchor]

duplicate in the autoincrement? or just duplicate other columns

[pastel vale]

other columns. even though i apply a unique id to the id column, it keeps duplicating the other columns every time i run the program. btw im still learning how to use sqlite with python. learnt the basics of sqlite and creating a project with it and i feel i might need to use an autoincrement id column

[fading patrol]

Show the code #❓｜how-to-get-help

[pastel vale]

ah I deleted it. thought you might have an idea what may be the problem. for the future, if i come up with this issue again, ill post it up here. apologies for wasting your time

[fading patrol]

Autoincrement should inherently be unique to that column.

It shouldn't normally matter that it's not unique to other columns, but it you really need an id that's unique and not just a simple integer for some reason, then use a UUID instead

[pastel vale]

Its likely I will need to use it for a small project (extracting data from a csv file to create a database from it) Im doing now. If I come across this problem, ill let you know. after sqlite, what should i progress onto and do you recommend any sites to learn database basics (something quick and easy to understand so I dont have to waste time; ill learn the complexities as I go by )

[fading patrol]

Postgres or MySQL, but don't underestimate what SQLite can do if you don't need major concurrency

Check the pins for beginner resources

[thorny anchor]

as long as the id column is unique, then that's ok as far as the database is concerned. you need to add other constraints if you want more uniqueness

[elfin cradle]

sqlite3.OperationalError: no such column: Chase```
bro why is it saying that like its not right there 😭

the name of the table is correct

[coral wasp]

!sql

[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

• Python sqlite3 docs - How to use placeholders to bind values in SQL queries
• PEP-249 - A specification of how database libraries in Python should work

[coral wasp]

Your problem is that Chase is interpreted as a column name, since you didn't quote it right, but you shouldn't be f stringing a sql query anyway (see above)

[elfin cradle]

i dont see the difference

am i tweaking

[coral wasp]

I'll tell you your exact error, but then tell you why you don't want to do this

[elfin cradle]

ik the f string but i did that cus it was throwing me a wierd error when i use param

[coral wasp]

The exact error is that you're saying: where x = y, not where x = 'y'

Without quotes, y is interpreted as a column name

[elfin cradle]

ohhhhhh

lemme try that

[coral wasp]

But: you need to use param.

Do not ever do what you're doing. Just never do it. Ever.

[elfin cradle]

cursor.execute("SELECT account_id FROM accounts WHERE account_name = ?", ("cura[0]",))

also whats the issue with f strining?

[coral wasp]

This is why: https://xkcd.com/327/

xkcd: Exploits of a Mom

Her daughter is named Help I'm trapped in a driver's license factory.

[elfin cradle]

is what i sent correct tho

[coral wasp]

Yes that seems right

[elfin cradle]

wait

can i f string the param

cus its gonna pass as literally "cura[0]"

[coral wasp]

Just remove the double quotes, you don't need to pass the quotes

Pass curoa[0]

[elfin cradle]

tried that

gave me error

one sec

nevermind

im dumb

[fading patrol]

Much better, but what's with the little loop between enemies and enemies killed?

[wary solar]

formating issue probably a glitch

I'll sort it

I get why you plan now

i've added like 4 tables and revomed 2

[restive current]

erdcloud is better than mermaid?
I use mermaid and like it but when I created many table in diagram, it became messy

[cloud moat]

help me out: https://discord.com/channels/267624335836053506/1261566171203768402

[solar pagoda]

yall, is a .env file good for database info or json?

[torn sphinx]

I have database with postgres and it's stored locally. How should I go about transferring the data I have stored in it to a vps?

[wide fable]

I have a database installed on localhost in mysql. I have made an application using this database, this application records and entries are made. But if I share this application to people, the registration and login parts will not be able to use the database, because the database is connected to localhost and people cannot enter their accounts using it in the application. I want to organize this database online so that everyone can access it. Do you have a chance to tell me which steps I should do in order. It would be very good if it is with examples. (For example, if you say rent a server, what do you recommend etc.)

[fading patrol]

https://stackoverflow.com/questions/1237725/copying-postgresql-database-to-another-server

[fading patrol]

If you get a VPS (Amazon EC2 free tier for example) you can host your whole application there including the DB. Or if you really want to you can just host the DB there.

[wide fable]

Thanks

I want to make a remember me feature and I want to connect it to the database, what should I write in the default value section?

[raven yoke]

If anyone has worked with the EPIC Eos api before, I need some help migrating how to get player data with a HTTPS call

[atomic tundra]

ngl

this is a good server

never thought i would join this

but like i needed help for python

also i don't get the voice verify thing

like why are we supposed to verify our voices??

[keen minnow]

• This ain't the channel to discuss this topic
• Trying to game the system will just get the mods on your back and reset all your efforts

[atomic tundra]

Game the system?

[keen minnow]

sending a bunch of messages to bump up your count

[atomic tundra]

Yeah nahh that is not what I'm doing

[fading patrol]

#❓｜how-to-get-help

[keen gulch]

Which database modeling tool do you use on MAC for MySQL?

MySQLWorkbench does not work for me on the Apple M processors.

[cursive pollen]

Could u tell me what does 25, 50, 75 trying to refer
Like 🤔 if 75% have room 4 then how can 50% have room 3

[midnight karma]

this?

[cursive pollen]

This

[midnight karma]

what is this anyway

[cursive pollen]

🙂☝🏻 a table

[midnight karma]

i have no idea

[cursive pollen]

Ok

[hexed wadi]

they are the "percentiles"

e.g., 4 at 75% means "75% of the data has the value <= 4"

50% is also known as the median -- there are equal number of data points in each side of this quantity

min and max are actually thinkable as 0% and 100% versions

these help you, e.g., have some idea about the distribution of the data, the skewness kind of stuff

[cursive pollen]

Skew symmetric matrix

So they refers to <=

75% houses have rooms <= 4

🤔can I use percentage here

Why percentile

Oob

Ooh

[long carbon]

hey did anyone made a project on firebase??

I just uploaded the data on Realtime database; but I can't retrive it on my site no matter how

[ashen crater]

I have a PostrgeSQL database hosted by ElephantSQL and my Flask app uses sqlalchemy to interface with it,
The issue is that ElephantSQL is discontinuing / sunsetting its service so I need to migrate off of it.
I plan on using SQLite locally on the server instead with hopes of decreasing query times as well.
How would I best proceed to migrate? What would be the ideal steps to achieve this seamlessly?

[fading patrol]

Downgrading from Postgres to SQLite doesn't seem like a wise way to improve performance. It would be better to host a Postgres DB on the servers' local network instead. Better performance and no need to mess with your data either.

That said you can find plenty of tools for migrating from one DB engine to another. Personally I've used DBeaver.

[ashen crater]

Since the database is relatively small and only grows really slow I made the decision to switch to SQLite because it reduces complexity and only requires a file instead of a database server

Also thank you for the recommendations I will take a look at them

[fading patrol]

I see. As long as you don't need much concurrency that might be fine.

Hopefully you're not using any fancy data types. SQLite doesn't even have a Boolean type so you may need to switch true/false to 0/1

[ashen crater]

Ah okay thanks for the info. So far I'm only using String and Iteger datatypes
Attributes are primary_key, unique and nullable

[halcyon pond]

helllo

i wanted to ask something

what is enum in postgres database

i have been given this

in the db diagram

i have to write an ingestion script

i do not understand what i have to do here ?

i am using pyspark

like the data is 21 which i have the dict which means categorical and thus using that we can like idk what am i supposed to do with these enums now ?

[fading patrol]

I googled it for you... It's basically like a tuple in Python. https://www.postgresql.org/docs/current/datatype-enum.html

[tulip birch]

that's not exactly right

[fading patrol]

Otherwise to me it sounds like you need to ask whoever assigned you this task for clarification. Or maybe we need more context. But the problem you need help solving isn't very clear

[tulip birch]

A tuple is storing multiple values

an enum is a defined set of possible values which you store one of

[halcyon pond]

are enums mapped to something ?
like 1 : "nugget"
coz that is how i use them mostly

[tulip birch]

class AddrKind(enum.Enum):
    v4 = 1
    v6 = 2

class Address(Base):
    __tablename__ = "addresses"
    id = Column(Integer, primary_key=True)

    # host this address belongs to
    host_id = Column(Integer(), ForeignKey('hosts.id'))
    host    = relationship('Host', back_populates='Host')

    # ipv4 or ipv6
    kind = Column(Enum(AddrKind), nullable=False)```
Here's an example I literally wrote 5 minutes ago

[fading patrol]

Good point, thank you for clarifying.

[tulip birch]

An IP address has two varieties, v4 and v6

In my table of IP addresses, I need to indicate which type an address is.

A naive approach would be to use an integer, or worse, a string

When a column can have more possible values than the set of possible values you actually want, it's bug prone

if I make it an Integer, it just creates the possibility for me to accidentally store a 5 in it

or a 99

or a 0

Since there are only two values this column should ever have, its type should reflect that.

So I make an enum type, with two values

v4 and v6

and this column will only ever have one of those two values in it.

[pastel vale]

why is it that despite applying a unique id to an id column, my program keeps duplicating the data every time I run my program? the only way to stop this shit from happening is if i manually create a unique for every row (178 rows). how do i make it so that each row is unique to each row (regardless if multiple rows have duplicate data)?

[fading patrol]

Show the code

[halcyon pond]

i have a question
can we even maintain relations in spark ?

hmm

[pastel vale]

# v1 = version 1 of the SQLite database maker
with closing(sqlite3.connect(filename1)) as v1_conn:
    with closing(v1_conn.cursor()) as v1_cur:
        # 2014 prices table
        v1_cur.execute('''
            CREATE TABLE IF NOT EXISTS SP500_2014
            (
                ID INTEGER PRIMARY KEY NOT NULL,
                Month INTEGER,
                Day INTEGER,
                Value_at_close FLOAT 
            )
        ''')
        v1_cur.execute('CREATE UNIQUE INDEX sp500ID ON SP500_2014(ID)')
        v1_cur.executemany('INSERT OR REPLACE INTO SP500_2014(Month, Day, Value_at_close) VALUES (?, ?, ?)', zip(month_insert, day_insert, prices_insert))```

[waxen finch]

perhaps you want a composite unique index on (month, day)?

[pastel vale]

but if i do, itll throw up an error

[fading patrol]

What thegamecracks said makes sense

[pastel vale]

ill have a look into it in the morning. want to fucking sleep

[tulip birch]

Ok I actually have a question. Trying to figure out how to specify this relationship in sqlalachemy

Say I have these three tables:```py
class HttpData(Base):
tablename = "http_data"
id = Column(Integer(), primary_key=True)

service_id = Column(Integer(), nullable=False)
service = relationship("Service", back_populates="service_data")

class SshData(Base):
__table__name = "ssh_data"
id = Column(Integer(), primary_key=True)

service_id = Column(Integer(), nullable=False)
service = relationship("Service", back_populates="service_data")

class Service(Base):
tablename = "services"
id = Column(Integer(), primary_key=True)

# generic identifier used to refer to this service
ident = Column(String(128), nullable=False)

# name of the protocol this service understands
proto = Column(String(128), nullable=False)

service_data_id = Column(Integer(), nullable=False)
service_data = relationship(?)```

Question is, how can I specify a foreign key relationship in Service that holds an id within one of the two other tables?

[fading patrol]

Isn't that what the "back_populates" does? https://docs.sqlalchemy.org/en/20/orm/basic_relationships.html#one-to-many

[tulip birch]

Only in one direction

I have specified the relationship from the child tables to the parent, but not the other way around. What I'm trying to fill is the question mark in the code

[fading patrol]

Isn't it just another back populates? After the documentation says"To establish a bidirectional relationship in one-to-many...." It has an example like that.

Or if you're trying to do many to many, that's explained too

[tulip birch]

It only shows this for a relationship with one table referencing one other table

The table of the foreign key in their example is hardcoded

service_data_id can either be a FK of http_data or a FK of ssh_data

For any given row

[fading patrol]

I think that's what this is supposed to be doing, but I'm not sure: https://docs.sqlalchemy.org/en/20/_modules/examples/generic_associations/discriminator_on_association.html

[tulip birch]

Interesting, thanks

This may be XY. I really just need a table that has a one-to-one relationship with a partner which can be of several different types

I did consider using multiple columns, each foreign keyed to a different service data table. But that's a very shit solution because only one will ever be in use in any given row

and every time I wanted to add support for a new service, I'd have to add another such column

[tulip birch]

better way: inheritance

[obsidian basin]

When I pytest the code I am getting a weird error in models.py. Does anyone have any idea what is causing this?
If needed I can show more code just let me know. Please ping on reply. Thanks

models.py
https://paste.pythondiscord.com/YHBQ
error link
https://paste.pythondiscord.com/IFMQ

[torn sphinx]

somehow this runs on a local computer that has a Django project. The command I use to get this to work is "redis-server" and it works with the redis server code, I am trying to get this to work through an AWS server, should I edit the redis code in the repo to work with the server?
CHANNEL_LAYERS = { 'default': { 'BACKEND': 'channels_redis.core.RedisChannelLayer', 'CONFIG': { "hosts": [("127.0.0.1", 6379)], }, }, }

[glacial current]

it might be that you defined username: and the api needs user: to be defined. but its not clear.

[obsidian basin]

@glacial current Thanks I managed to fix it I managed to fix a column or column that was screwed up in my other database. For some reason the other column was affecting this db

[torn sphinx]

how do you webscrape json files and then store them into a database?

[coral wasp]

Figure out the first part first, then worry about the database. For help with scraping, open a #❓｜how-to-get-help help thread or ask in #python-discussion

[hidden cave]

hi guys. i have program which gets data. i need to step away from csv's and store this in a database. what are some ways i could go about this?

i mean a db solution

[fading patrol]

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

[shut sage]

File "/root/main.py", line 15, in <module>
    db = mysql.connector.connect(
         ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/mysql/connector/__init__.py", line 173, in connect
    return MySQLConnection(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 102, in __init__
    self.connect(**kwargs)
  File "/usr/lib/python3/dist-packages/mysql/connector/abstracts.py", line 735, in connect
    self._open_connection()
  File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 250, in _open_connection
    self._do_auth(self._user, self._password,
  File "/usr/lib/python3/dist-packages/mysql/connector/connection.py", line 155, in _do_auth
    self._socket.switch_to_ssl(ssl_options.get('ca'),
  File "/usr/lib/python3/dist-packages/mysql/connector/network.py", line 427, in switch_to_ssl
    self.sock = ssl.wrap_socket(
                ^^^^^^^^^^^^^^^
AttributeError: module 'ssl' has no attribute 'wrap_socket'```

db = mysql.connector.connect(
    host = 'localhost',
    user = 'phpmyadmin',
    password = 'wwww',
    database='gat'
)```

Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
python3-mysql.connector is already the newest version (8.0.15-4).
The following packages were automatically installed and are no longer required:
  libantlr4-runtime4.10 libtinyxml2-10
Use 'apt autoremove' to remove them.
0 upgraded, 0 newly installed, 0 to remove and 7 not upgraded.```

Anyone know whats wrong with my code?

im on ubuntu 24.04

[bold ridge]

i have an azure postgres server deployed, but when i try to execute this command, it returns empty list, but i am able to see my database if i check databases and my app is able to read/write database without any issues

[fading patrol]

Is the CLI logged in to the right account?

[bold ridge]

Yes

[bold ridge]

i solved this issue

wanted to install timescaledb extension

[shut sage]

hey umm so im using mysql.connector and i get the rows in "sql"
i get them in to like ("hey", 2, "two")

how do i make r = "hey"

no = 2

num = "two"

?

[golden comet]

If anybody is around that could help me figure out what I'm doing wrong with this query that would be much appreciated. It's working if I do by day, but when I try to get the last hour of rows from a table it returns...nothing. No matter what table?

so sorry - the sql is:

WHERE
    table_name.created > now() - interval '1 day';```
the above works but the below does not - I need 1 hour but am unsure how to get that?

```SELECT created FROM table_name
WHERE
    table_name.created > now() - interval '1 hour';```

@shut sage if I understand you correctly you want to get the value of the sql output and currently it's in tulpe form correct? Maybe I'm misunderstanding

[shut sage]

yes

[shut sage]

did you try '1h'

[golden comet]

I usually use a dict cursor with mysql tables for easier formatting outputs but with the tuple(s) how many of them do you have? Just one or multiple rows? I'd do a for loop and I think you have to give names to each in the tuple as so:
row = ("hey", 2, "two")

r = row[0]
x = row[1]
y = row[2]

Does that make any sense?

[shut sage]

multiple rows

[golden comet]

ok so for that you'll need a for loop. Something like:

for row in listofsqlrows:
    row_col1 = row[0]
    row_col2 = row[1]
    row_col3 = row[2]

I'm certain there is a more pythonic or list compressive way to do it but I would have to go re-look that up. I usually use an ORM for auto class creation or use a dict cursor.

[shut sage]

oh ok ty ty

[golden comet]

I hope that makes sense -

[shut sage]

it does

and it worked

thank you

[golden comet]

Np 🙂

[pastel vale]

Anyone recommend some interesting database projects? The current project im doing (putting stock data into a database) feels kinda boring. I feel like Ive done enough shit with stocks. Want to move onto something else

[fading patrol]

What's interesting to you? You can collect data related to almost anything

Personally I work a lot with music data, mainly the Last.FM API

I've seen other people do projects related to sports, weather, and paintings at the Museum of Modern Art, flights.... You can look for datasets on Kaggle, r/datasets etc.

[pastel vale]

Idk. Tbh at one point I was heavily invested (no pun intended) in stocks but now Im just getting bored with looking at stock data. I want something that feels challenging but not too challenging where i smash my head on the table

I am into my martial arts (currently do Muay Thai) so maybe if theres a database related to that

[fading patrol]

You'll have to dig, for MMA I'm sure there are

[pastel vale]

Ill look around tomorrow. I might go more specific and look into creating a database of muay thai fighters. I think thatll pose an interesting challenge when you compare its popularity against mma

So many phenomenal fighters in the past but its hard to document a legitimate record

[hollow hamlet]

Hey, I have an issue with postgres sequences and how alembic is generating them. See https://paste.pythondiscord.com/CWLQ for 3 files, my model, the initial migration created from that, and then the subsequent migration generated after applying the first migration.

It seems alembic is detecting the sequence to have a default of nextval('post_sequential_id_seq'::regclass)" so then attempts to remove the ::regclass cast, even though it's the initial migration that adds that cast.

I've tried googling around for a solution but couldn't quite find anything, anyone have ideas?

Opened a help thread #1263941230971326464 message

[golden comet]

Anybody able to help me with a postgresql db query? I've tried this a couple of different ways and it works with interval day, but I need the last hour of a table?

WHERE
    table_name.created > now() - interval '1 hour';```

[grim vault]

What's the datatype of the created column? Did you check manually if there are data for the last hour?

[grim vault]

It should work: https://www.db-fiddle.com/f/hoeRiTCAF6bpDb3EkZgJa9/0

[golden comet]

It's datetime

or well here let me get the DDL or whatever it is for that if you need. But yah and this works just fine:

SELECT
    *
FROM
    table_name
WHERE
    table_name.created > now() - interval '1 day';```

Even if I put in created instead of * but for hour it gies me nothing. I think I'm maybe needing to use time or??? Something??

And so sorry it's:
created timestamp not null,

I just need the last hour from that table. It's something I do in my current script but I'm switching from PeeWee orm to asyncpg so re-doing all my sql query stuff

[grim vault]

Sorry, can't be of more help. I never used postgresql, juts tried it on db-fiddle (link above) where it just did what you wanted.

[golden comet]

No it's all good. I mean I even remember doing something like created::date >= interval '1 day' or something llike that somewhere - I wonder if since it's by the hour I need to figure out how to use the time thing (sorry I'm not very good with actual sql terms sometimes)

[golden comet]

Yah I just finished doing all of my main tables from mysql to postgres a few weeks ago.

Now I"m converting my code from orm style to actual postgres and....the datetime is different between mysql and postgres more than I thad though

[grim vault]

It can also be a timezone discrepancy. Your column is without time zone info and now() returns the timestamp with time zone.

[golden comet]

Yah I....bah while I do have timezone set in the database maybe that is it? I wonder how I would test that? I'm going to go try to re-do that examples/tests on postgresqltutorial again but I was having a hard time applying that they have/had to my query :\

[grim vault]

Try this and take a look:

SELECT created, now(), created > now() - interval '1 hour'
  FROM table_name
 ORDER BY 1 DESC LIMIT 10;

[golden comet]

Ok thanks I'll go do that now.

OMG ok so that workednow I guess just remove the limit 10 for all of them? Man that...that is going to help me a LOT! Longshot but do you know by chance how to get the average of seconds between created columns for say...an hour. I need to look at how many seconds on average it is between incoming comments and span in out by day/week/month/quarter

but...that...is a long shot and I know it 🙂 Just more I can actually do in the sql would be nice

[grim vault]

If you remove the limit you will get ALL rows of the table. Can't be of help for the other.

[golden comet]

wait.... hhmm taking off the limit messes it up oh crap is it looking for all of it. It's close toa million rows

[grim vault]

And what do you mean it worked? Did the third column have some true results? If yes, your where clause should also have worked.
And I have to go, all the best.

[golden comet]

thanks for trying and I'll work more with what you gave me

[halcyon pond]

does anyone use pyspark here ?

i am unable to save pyspark.sql.dataframe as csv

idk

i downled the winutils.exe

it is still not working

[visual nova]

any good courses for learning professional database structuring for commercial use

[wise wind]

https://youtu.be/GFQaEYEc8_8?si=9eEa-uzUn01VDn0m

[torn sphinx]

Anyone knowledgable in web scraping?

[fading patrol]

You should just ask your actual question, but be aware of group rules (don't ask for help violating ToS)

[torn sphinx]

Well I'm just wondering how I would set a custom user agent and proxy

[fading patrol]

It should be in the documentation for whichever library you are using.

[dull lance]

Hey anyone know how I can use Github Actions as trigger for firebase database. If anyone know please help me.

[fading patrol]

I googled it for you: https://github.com/marketplace/actions/firebase-trigger

[dull lance]

I already did it..just i want someone who has done this atleast once. I have some doubt in this

[fading patrol]

You should ask your actual question

[dull lance]

I did it means i googled it.. the main question is still not solved

[dull lance]

I have not done this one

[muted mango]

wrote this sqlite3 statement

and well

cursor.execute(
    "SELECT remove_event_triggered FROM ACTIVE_PUNISHMENTS WHERE id=? AND type=\"ban\"",
    (member.id,)
)

for some reason when i use cursor.fetchone()

it returns None even tho the column clearly exists

the id field is exactly the thing i expect and the type

actually i believe i am in a race condition bc of some async funcs

[fading patrol]

Does it matter if you use single quotes instead of escaping the doubles around 'ban'?

[muted mango]

meh

doesn't really matter

bingo

knew it

sorry for the inconvenience

now its fixed

[grim vault]

Yes, the SQL standard defines single quotes for string literals and double quotes for identifiers (eg column/table name).

[grim vault]

I would also use a parameter for the type:

cursor.execute(
    "SELECT remove_event_triggered FROM ACTIVE_PUNISHMENTS WHERE id=? AND type=?",
    (member.id, "ban")
)```

[muted mango]

oki dokie

[midnight laurel]

with asyncpg is there some way to pass a table name as a parameter in the case of something like

INSERT INTO $1(columns) VALUES ($2, $3, ...)

into a statement?

it currently just gives a syntax error for $1

[storm mauve]

nope, the best you can do is just ```py
if table not in {"allowed_x", "allowed_y", "allowed_z"}:
raise ValueError("Bad table")
query = f"INSERT INTO {table} VALUES ($1, $2, $2)"

Ideally you should format your database and code in a way such that you have no variables referring to tables - either have separate functions per table, or if they all store mostly the same data, use a column for what you're currently thinking of as the table name & concatenate them

[midnight laurel]

for this case, the database would need to have tables referred to by a variable to make it less obtuse unfortunately

i'll just use interpolation then

[prime kelp]

What database is used to store client information? Eg username, password, and other service related things

[storm mauve]

pretty much any database could work?
consider using SQLite or Postgres if you're getting started

Also, you should never store the password directly, you must salt & hash it first with something like hashlib.sha256 see Darkwind's message (or find a library that hashes it for you)

[wise goblet]

sha256 is not meant for passwords 🤔 because it does not offer to have one time salting.

bcrypt is safer default to recommend

[vestal mauve]

Hi guys,
One question, whats the best way to store information in database dinamically, I mean if I have a data base with a table for items, it has some columns but supose an item can change just adding a new column, whats the best to store that data? supose I can use postgres and redis

[fading patrol]

Adding columns and updating rows are just basic database operations. What's the issue?

[vestal mauve]

I know, but what i mean example one client wants add an item with name, price and description and another one wants add an item with name, price, description and status

[fading patrol]

Ok, and how is that a problem exactly? As long as status is nullable at the DB level, that sounds fine

[thorny anchor]

is this change happening dynamically, e.g., a user wants a status and the column will be added?

[vestal mauve]

That is an example could has more columns

[vestal mauve]

Yes, I want to know how handler that

I read 1 option can be using json

And save that json

[thorny anchor]

generally, you wouldn't. it's not how relational databases are designed to be used. json can be a good idea, depending on the access patterns of the data

[vestal mauve]

Thank, Ill use json

[tardy sorrel]

guys can anyone help me in getting into SQL?

i downloaded an app, called DataGrip

i dont know what to do next

help, please

[fading patrol]

If you want interactive tutorials, there are lots... Dataquest and DataCamp have some free content.

Move on quickly to actually doing something you're interested in. SQLite is the easiest engine to start with

[humble robin]

So I am not sure if I am doing something wrong or if the new sqlite3 module in python3.12 just works different then I expected, when I try to list the tables with .tables I get "OperationalError (SQLITE_ERROR): near ".": syntax error". Is that type of syntax not allowed in the module?

[waxen finch]

afaik those commands are specific to SQLite's official CLI, for programmatic access you'd have to implement it yourself by reading the sqlite_schema table
https://sqlite.org/schematab.html

[humble robin]

Hmm, okay. I was confused cause it uses .quit and .help but none of the others. Thanks for the help tho

[opal pilot]

guys what are the best ways that I can learn how to handle databases(let's just say mysql for the sake of it) in python?

[fading patrol]

By handling a MySQL databaee with Python.

There are loads of free tutorials a web search away.

Beyond that it depends on what exactly you're trying to learn

If you've never touched a SQL DB before, SQLite will be an easier engine to get going with

[opal pilot]

Alright, thanks!

[obsidian basin]

I am using flask-sqlalchemy but I am getting the error typeError: exceptions must derive from BaseException. How do I fix this?
Also I am not sure if I should put this under unit testing category.

Please ping on reply.

Thanks

functions.py
https://paste.pythondiscord.com/IOJQ

conftest.py

https://paste.pythondiscord.com/JSSA

test_functions.py

https://paste.pythondiscord.com/RSHA

models.py

https://paste.pythondiscord.com/PP7Q

errors
functions.py
https://paste.pythondiscord.com/IOJQ

conftest.py

https://paste.pythondiscord.com/JSSA

test_functions.py

https://paste.pythondiscord.com/RSHA

models.py

https://paste.pythondiscord.com/PP7Q

errors

https://paste.pythondiscord.com/YQJQ

[median marten]

Hi guys, i have this table

                CREATE TABLE IF NOT EXISTS players_data (
                    _id int PRIMARY KEY,
                    shots_fired int DEFAULT 0,
                    hits int DEFAULT 0,
                    misses int DEFAULT 0,
                    wins int DEFAULT 0,
                    loses int DEFAULT 0
                )

and this query INSERT INTO players_data (_id) VALUES (?) RETURNING _id

but the query is not returning the correct results for this query, for example i insert a new row with an _id of 126 and the query returns 4
i'm using aiosqlite, more specificelly the execute_insert method on the database object

[subtle timber]

don't you have to then SELECT _id

or smth like that?

https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL
seems like if you're using postresql you'll do it by INSERT INTO .... AS SELECT or smth along these words

[median marten]

I'm using SQLite as mentioned in the message

btw the returning is available in postgresql too so

[subtle timber]

https://stackoverflow.com/questions/6242756/how-to-retrieve-inserted-id-after-inserting-row-in-sqlite-using-python#:~:text=Note that the RETURNING clause works with cursor.execute but won't work with cursor.executemany. Python will complain that "executemany() can only execute DML statements."

u might also want to put the _id inside quotes maybe?

[grim vault]

!e it should work?

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("""
    CREATE TABLE IF NOT EXISTS players_data (
      _id int PRIMARY KEY,
      shots_fired int DEFAULT 0,
      hits int DEFAULT 0,
      misses int DEFAULT 0,
      wins int DEFAULT 0,
      loses int DEFAULT 0
    )
""")

curs = conn.cursor()
curs.execute("INSERT INTO players_data (_id) VALUES (?) RETURNING _id", (126,))
print("_id = ", curs.fetchone()[0])

[delicate field]

:white_check_mark: Your 3.12 eval job has completed with return code 0.

_id =  126

[grim vault]

Ah, I see the problem. The execute_insert returns the rowid and not what the insert statement is returning. The _id int PRIMARY KEY does not replace the rowid, you would need to define it as _id integer PRIMARY KEY, than it will work (even without the returning statement on the insert).

!e

import sqlite3

conn = sqlite3.connect(":memory:")
conn.executescript("""
    CREATE TABLE IF NOT EXISTS test_int (
      _id int PRIMARY KEY
    );
    CREATE TABLE IF NOT EXISTS test_integer (
      _id integer PRIMARY KEY
    );
""")

curs = conn.cursor()
for tab_name in ("test_int", "test_integer"):
    curs.execute(f"INSERT INTO {tab_name}(_id) VALUES (?)", (126,))
    for rowid, _id in curs.execute(f"select rowid, _id from {tab_name}"):
        print(f"{tab_name}: {rowid = }, {_id = }")

[delicate field]

:white_check_mark: Your 3.12 eval job has completed with return code 0.

001 | test_int: rowid = 1, _id = 126
002 | test_integer: rowid = 126, _id = 126

[median marten]

oh alright

ty

[pastel vale]

Are there any good database planners on ipad?

[midnight laurel]

what would be the proper way to insert a boolean into a query? i keep getting a boolean is required (got type str) even though i have the following:

INSERT INTO table bool_col VALUES ($1::boolean)

[midnight laurel]

i realized far too late i forgot to specify this was asyncpg

[thorny anchor]

show the python code you're using?

[coral wasp]

!sql

[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

• Python sqlite3 docs - How to use placeholders to bind values in SQL queries
• PEP-249 - A specification of how database libraries in Python should work

[coral wasp]

If the data type is a boolean that you're inserting, then it's your column type that you need to change.

'Describe tablename' to see the types

[pastel vale]

What good books do you lot recommend for learning about databases?

[midnight laurel]

the column type is boolean

[midnight laurel]

await conn.execute("""INSERT INTO table bool_col VALUES ($1::boolean)""", boolean_value)

[thorny anchor]

we would need all the relevant parts for this piece of code

[midnight laurel]

that's the relevant part for what's causing the issue

let me check, now that you mention that i may have just misunderstood something about the typing enforcement

[grim vault]

That doesn't look like valid SQL (the ... table bool_col ... part).

[midnight laurel]

yeah, table would be a reserved word but this is just an example for what is being done

[grim vault]

The columns must be in parentheses ... tablename(colname) ...

[midnight laurel]

oh, that's what you meant. i forgot about that, but i found out the issue

i had a class with a member that was type hinted to be boolean, but i was setting the value to be a string and assuming it stayed a boolean

thanks for the help

which was just me being dumba

apologies for assuming it was database related

[west blaze]

Hey everyone! Can anyone recommend a way to work with PostgreSQL migrations in a python project that does not use any frameworks?
Preferably something from your own experience, something that worked your you?

I've tried alembic, but that didn't work

[fading patrol]

The standard way to send SQL directly to Postgres in Python is psycopg2. But "alembic... didn't work" is not a good reason to skip using an ORM or web framework if your use case calls for one.

[solemn mountain]

im using sqlite3, and im having some issues with getting the rowid of an item, the second line was shown on the documentation but it for whatever reason doesnt work, the error im getting is, "sqlite3.OperationalError: unable to use function MATCH in the requested context" if anyone can help i would be grateful

for row in cur.execute("SELECT * FROM entries"):
    for i in cur.execute("SELECT rowid FROM entries WHERE username MATCH '"+row[1]+"'"):```

[solemn mountain]

found out the issue, it was the simplest fix 😭 all you need to do is replace match with ==

[waxen finch]

you should also use query parameters instead of string concatenation, it's less error prone when one of the strings contains a quote (or maliciously crafted SQL)

e.g. py cur.execute( "SELECT foo FROM bar WHERE ham = ? AND spam = ?", ["hello", "world"] )

[fervent charm]

Anyone use postgres with python? If so, do you use psycopg2 or sqlalchemy to interact

[coral wasp]

Comparing apples and oranges. Sqla provides a higher level abstraction, vs a dbdriver

Both approaches are popular, and a detailed answer requires an explanation of ORMs vs direct SQL

[fervent charm]

I used psycopg but switched to sqlalchemy because the query sanitization was easier with sqlalchemy (yes I still use direct SQL with sqlalchemy)

I didnt ask for a comparison of which is better or anything, I asked if people use one of them

[thorny anchor]

with sqla there's two parts, core and orm. you don't have to use orm

[fervent charm]

So I did some benchmarking with a dataframe that is 633mb big.. 😢

QuestDB:
Write total time: 4.696176528930664s
Read total time: 15.698245286941528s

DuckDB:
Write total time: 169.50076341629028s
Read total time: 9.032938480377197s

TimescaleDB:
Write total time: 164.03110218048096s
Read total time: 16.496233224868774s

Flat files:
Write total time: 1.039243459701538s
Read total time: 0.42358899116516113s

[grim vault]

You could do it in one go like:

for row in cur.execute("SELECT rowid, * FROM entries"):
    # now row[0] is the rowid

or, if you want it separated

for rowid, *row in cur.execute("SELECT rowid, * FROM entries"):
    # ...

[solar lichen]

Which is the best software for databases?

[fading patrol]

It obviously depends on what you're looking for. If you want a free desktop application that can connect with all kinds of databases I like DBeaver

[coral wasp]

Share code? That DuckDB number seems quite wrong to me.

[solar lichen]

I am making a note taking app

[cedar tiger]

sqlite probably

[solar lichen]

Okay

Thanks

[solar lichen]

What cloud service should I use?

[cedar tiger]

wdym?

you could try https://sqlitecloud.io maybe

I'm more of a Postgresql person

So I prefer Supabase or Neon

[solar lichen]

Is this free?

[cedar tiger]

If you took a minute to click it and check the Pricing page, you'll find that they do offer a free plan

[solar lichen]

They need credit card

[cedar tiger]

still free

[solar lichen]

But I dont have

[cedar tiger]

then you're stuck

[solar lichen]

any other?

[cedar tiger]

Why do you need an online database?

If you want it online, you have to be prepared to pay for it if you exceed the free plan

AWS, GCP and Azure has free tiers but they also need your card info

[solar lichen]

I will not probably

[cedar tiger]

If you can't get one, then you're stuck until you get one

[solar lichen]

OH

[fervent charm]

I really hope its wrong..

import os
import time

import pandas as pd
from questdb.ingress import Sender
from sqlalchemy import create_engine
from sqlalchemy import text

query = text(f"""
SELECT *
FROM data_table a
""")

aapl_filepath = f"C:/Users/{os.getlogin()}/Downloads/aapl.parquet"

quest_eng = create_engine("questdb://admin:quest@localhost:8812/qdb")
quest_conn = quest_eng.connect()
timescale_engine = create_engine('postgresql://postgres:password@localhost:5432/postgres?client_encoding=utf8')
duck_eng = create_engine("duckdb:///:memory:")

df = pd.read_parquet(aapl_filepath)

print("QuestDB:")
start = time.time()
with Sender.from_conf(f"http::addr=localhost:9000;") as sender:
    sender.dataframe(
        df,
        table_name='data_table',
        symbols=['symbol'],
        at='timestamp',
    )
end = time.time()
print(f"\nWrite total time: {(end - start):.2f}s")
start = time.time()
result = quest_conn.execute(text("select * from data_table"))
end = time.time()
read_time = end - start
start = time.time()
quest_df = pd.DataFrame(result.fetchall())
df.columns = result.keys()
end = time.time()
conversion_time = end - start
print(f"Read total time: {(read_time + conversion_time):.2f}s ({read_time:.2f}s + {conversion_time:.2f}s)")

print("\nDuckDB:")
start = time.time()
df.to_sql(name="data_table", con=duck_eng, if_exists="replace", index=False)
end = time.time()
print(f"Write total time: {(end - start):.2f}s")
start = time.time()
duck_df = pd.read_sql_query(query, duck_eng)
end = time.time()
print(f"Read total time: {(end - start):.2f}s")

print("\nTimescaleDB:")
start = time.time()
df.to_sql(name="data_table", con=timescale_engine, if_exists="replace", index=False)
end = time.time()
print(f"Write total time: {(end - start):.2f}s")
start = time.time()
timescale_df = pd.read_sql_query(query, timescale_engine)
end = time.time()
print(f"Read total time: {(end - start):.2f}s")

print("\nFlat files:")
start = time.time()
df.to_parquet(aapl_filepath)
end = time.time()
print(f"Write total time: {(end - start):.2f}s")
start = time.time()
flat_df = pd.read_parquet(aapl_filepath)
end = time.time()
print(f"Read total time: {(end - start):.2f}s")

print(f"\nQuestDB:\n{quest_df}")
print(f"\nDuckDB:\n{duck_df}")
print(f"\nTimescaleDB:\n{timescale_df}")
print(f"\nFlat files:\n{flat_df}")

[coral wasp]

Ugh, yah, that's bad. Pd.read_Sql thru DuckDB?

No, do: conn.sql(query).df()

And, you didn't need to load the df from parquet, you could've used: "select * from 'my.parquet'" in DuckDB and skipped that entire step.

And your test isn't good because you're doing this all in the same memory space. Run these tests fully independently

The one line test is: "duckdb.sql('select * from 'my.parquet').df()"

[brazen charm]

Also why use pandas even during those steps?

[hollow oar]

also you are not going to have a great time using timescale like that as well.
i think you are acting as if timescale is just a data storage mechanism instead of a proper time series database, where you can do OLAP-ish queries on time series data efficiently.

if you are curious why it takes so long to read from timescale, it's likely due to the query planning, as you are doing a SELECT * FROM ... without any time dimension, timescale will need to plan to query literally every chunk of your hypertable, (i am assuming you are actually using timescale hypertable here), you are likely to see improvement if you just add the time base filter (with the column that you chunked on of course.)

but again - using timescale like that is not really the point of timescale imo. doing a SELECT * FROM ... is fine for research purposes, but definitely not for any sort of live application.

[fervent charm]

@brazen charm The goal was to load it as a df

[coral wasp]

Is your goal to convert parquet to df?

[fervent charm]

The goal was to store a df (however that may be) and load it back in as a df

[coral wasp]

In that case, yah, you should be using DuckDB's sql(...).df()

[fervent charm]

btw I am still reading everyones input and applying changes

[coral wasp]

In general tho, load/export tests are deceptive and not super useful because it's the actual execution efficiency that's important.

I can make DuckDB look amazing at returning a df from a df.

Also: don't use df.to_sql. That's an extremely inefficient way to load any table. Arguably it's the worst possible choice

(For any db)

Here's the short version for duckdb: ```py

dummy dataframe

import pandas as pd
mydf = pd.DataFrame({"col1": [1,2,3,4]})

query against dataframe to create a new df

import duckdb
newdf = duckdb.sql("select * from mydf").df()

print(newdf)

[brazen charm]

Also gonna chuck out polars ™️

[coral wasp]

🐻‍❄️

[fervent charm]

What about writing it to store it? (Maybe I've found it, duckdb.sql("CREATE TABLE my_table AS SELECT * FROM my_df") from docs)

[fervent charm]

The table is created with the to_sql() call, so no its not a hypertable, apparently hypertables are not necessary if the data is under 2gb

A more realistic query I'd do is SELECT * FROM data_table WHERE symbol = '22' but that would be from a much larger table, this benchmark is using a mini table

[pastel vale]

Weird question (it is related to databases); for anyone whose a UFC enthusiast, will fighter always be in the same corner? E.g. if Petr Yan first fought from the red corner, will he always be in the red corner? I feel there may be an issue concerning duplicate data in multiple columns as the database Im working with has two columns: R_fighter, B_fighter

[coral wasp]

Just don't use to_sql, it's an extremely slow /bad way to load data

[coral wasp]

Yes, you can create a table. Unnecessary for your case.

[pastel vale]

So whats an alternative to 'to_sql'? Im still only using sqlite3 modile

[coral wasp]

In duckdb's case: You read directly from the dataframe

[brave bridge]

wondering if it follows the SQLite code of conduct

[fervent charm]

I'm trying to use duckdb for persistant storage so I think I do need to make a table

[coral wasp]

For some databases, you can use fast_executemany. I don't know which databases support it besides SQL Server https://docs.sqlalchemy.org/en/20/changelog/migration_13.html#support-for-pyodbc-fast-executemany

[coral wasp]

Then why use :memory:? Should be using a datafile.

[fervent charm]

Didn't really know what i was doing, copied from some source

[coral wasp]

Yah, so duckdb.connect(":memory:") creates a transient (in memory only) databases

You want duckdb.connect("somefilename.db")

https://naysan.ca/2020/05/09/pandas-to-postgresql-using-psycopg2-bulk-insert-performance-benchmark/ has some benchmarks for postgres:

The worst of which is to_sql.

[pastel vale]

How does ‘copy_from_string_io’ work and how would you implement it? Ive been using to_sql but if its recommended to not use it, I’d like to use a better alternative

[coral wasp]

Code is in that link

[pastel vale]

Why would creating a csv file, for the data you want to input into the database, and then copying said data into it be faster than execute methods?

[coral wasp]

Because databases have very efficient csv reading code

[pastel vale]

Ah makes sense. Still kinda feels like a 'lot of work' to copy data from a dataframe to a database (if you get what I mean)

[coral wasp]

Dealing with terabytes of data is usually a lot of work no matter what you do:)

[pastel vale]

Ngl, never thought databases would be this interesting. Im currently doing a project with a UFC database and already theres a lot of interesting challenges ahead to solve

[coral wasp]

Feel free to ask away, love this stuff

[pastel vale]

Absolutely. After nearly a year of restarting my programming journey, Ive finally began to see some progress. Im also delving into microcontrollers as well. Got a CPE from adafruit. Going to spend sunday playing around with it

[coral wasp]

Nice... I also always suggest watching some PyCon/Europython videos. Good way to see what other smart people are working on.

[fervent charm]

@coral wasp Was able to get this 😄

DuckDB:
Read total time: 0.56s
Write total time: 0.93s

def duck_benchmark():
    print("\nDuckDB:")
    conn = duckdb.connect("somefilename.db")

    start = time.time()
    df = conn.sql("SELECT * FROM data_table").df()
    end = time.time()
    print(f"Read total time: {(end - start):.2f}s")

    conn.execute("DROP TABLE IF EXISTS data_table")
    start = time.time()
    conn.sql("CREATE TABLE IF NOT EXISTS data_table AS SELECT * FROM df")
    end = time.time()
    print(f"Write total time: {(end - start):.2f}s")

Now, I wonder how much better I could get timescaledb if done optimally

[coral wasp]

Nice

[wind glacier]

supabase! or turso or tinydb or pocketbase

i personally use supabase for all my pet projects and theyre very generous with their free tier

(no credit card required 🙂

[solar lichen]

Okay thank you so much!

[dim hinge]

Hello I have a postgres database where I store my user's IPs. I use sqlalchemy and I'd like to filter by subnet with the >>= postgresql operator. I have no idea how such operators can be used from sqlalchemy, if anyone could help?

Here's my current code

async def get_by_ip(ip: str):
    async with connector.session_maker() as session:
        statement = select(UserIPRecord).filter(UserIPRecord.ip == ip) # Operator needs to be changed
        query = await session.execute(statement)
        return query.fetchall()

[fading patrol]

https://docs.sqlalchemy.org/en/20/orm/join_conditions.html#using-custom-operators-in-join-conditions

[coral wasp]

Oh wow, I was totally unaware of these. TIL

[fervent charm]

Duckdb was taking forever to write a df to a table, like so long I was never able to get it to finish. Removing the foreign key constaint and its done in under a minute.

Cascade is not supported anyways... so why not remove it I guess

[coral wasp]

Code?

Also duckdb has an active discord.

[fervent charm]

Wasnt aware, thanks

[fervent charm]

The df is millions of rows big, and without the chunking it just hangs. With the chunking, it writes the chunks super speedily at first but halfway through it starts taking 30+ seconds per df. ```py
query = """
CREATE TABLE IF NOT EXISTS ohlcv (
timestamp TIMESTAMPTZ NOT NULL,
ticker_id INTEGER NOT NULL,
open NUMERIC(8, 3),
high NUMERIC(8, 3),
low NUMERIC(8, 3),
close NUMERIC(8, 3),
volume UBIGINT,
trade_count UINTEGER,
VWAP NUMERIC(8, 3),
PRIMARY KEY (ticker_id, timestamp),
CONSTRAINT fk_ticker_id FOREIGN KEY (ticker_id)
REFERENCES tickers (id)
);
"""

duck_conn = duckdb.connect(str(Paths.DUCK_DB))

duck_conn.sql("DROP TABLE IF EXISTS ohlcv ")
duck_conn.sql(query )

print(f"Tables: \n{duck_conn.sql('show tables')}\n\n")
print(f"ohlcv: \n{duck_conn.sql('select * from ohlcv ')}\n\n")

df_full = pd.read_parquet(df_full_filepath)

n = 20_000
list_df = [df_full[i:i+n] for i in range(0,df_full.shape[0],n)]

sum = 0
for df in list_df:
print(df)
sum += len(df)
duck_conn.execute("INSERT INTO ohlcv SELECT * FROM df")

print(sum)

print(duck_conn.sql("SELECT * FROM ohlcv"))
print(duck_conn.sql("SHOW TABLES"))

[fading patrol]

Sounds like you're running out of RAM. What does the resource situation look like before and after it slows down?

Assuming that's right, Get more RAM or just don't use DuckDB

[coral wasp]

DuckDB can work with larger than memory datasets. But your code -first- loads all these df's into memory (via Pandas). Why?

You could just insert directly from the parquet: insert into mytable select * from 'my.parquet'

[covert pulsar]

Hey, I'm working on a project for school, but I have no clue how to properly approach creating a database to do what I need, and my teacher just tells us to ask ChatGPT.

Essentially I'm making a sport score tracker that just saves student scores across different events. What I want to be able to do is add a completely new event (like rowing or something for example) from within the app, but I'm not sure what the 'correct' way of going about this would be (or at least not the wrong way).

Currently I'm using SQL Alchemy and just have a single database with all the scores in rows and an "event" column to separate the events. This works, but just doesn't feel 'right', how is something like this normally achieved?

[fading patrol]

A single database makes sense, but it sounds like you have a single table in that database, which probably doesn't. Separate Event and Player tables would be more standard.

Beyond that, show us code and/or an ERD if you have specific doubts or questions about what you're doing.

The answer here provides a nice simple example of a relevant schema: https://stackoverflow.com/questions/43324622/schema-for-golf-tournament-scores

[coral wasp]

Also, i think there's a fix for the "dropping off" after successive inserts, if it's the issue I'm thinking of, altho I thought the fix was already released. You can try a pre-build, pip install duckdb --upgrade --pre, if you want. The issue I'm thinking of was checkpointing related.

[fervent charm]

I already have 64gbs, and it only reaches like 70% usage when this is going on - besides I've chunked each df to only be 20k rows big, so shouldnt be too hard on memory, more on cpu since the foreign key constraint causes a lot of extra checking to happen. It says in their docs that constraints hinder performance by a lot. Besides, it's not that big of a deal not to use a foreign key, the benefit of it was that if I update the id in one table it can cascade to all tables, but cascade is not even a feature in duckdb anyways.

Still, I'll try what you guys are suggesting, I upgraded to a pre build, skipped the intermediate step of loading into a df, and kept an eye on resources. It just uses some cpu and memory while it's running, nothing maxed out (I have maxed memory before with giant dfs but those were different situations). It's still running after 5 minutes and would already have been done if I didn't include the foreign key constraint

Appreciate the effort to make the query work but I think the foreign key is likely not worth the performance hit, even if we can get it to work. I was mostly just sharing findings about the big effect on performance that a fk can have.

[coral wasp]

I usually don't bother with foreign keys for analytical work, since it's usually a one time or infrequent load

[coral wasp]

The chunking makes no sense. Just read direct into the db. Avoid Pandas as much as possible when moving data.

[fervent charm]

Got it

[dapper juniper]

Just working with a pd df and trying to plot some stuff using seaborn but my plot y axis is all funky and im not sure whats wrong

Anyone have any Idea how I could fix the y axis labels (they should be number ranges)

plt.figure(figsize=(8, 6))  
sns.boxplot(x='PrimPropTypCalc', y='Site_EUI1', data=frank_subset)
plt.xlabel('Building Type')
plt.ylabel('Site EUI1')
plt.title('Comparison of Site EUI1 across Different Building Types')
plt.xticks(rotation=45, ha='right')  
plt.tight_layout(pad=2)
plt.show()```

[coral wasp]

What is data type of Y axes

frank_subset.dtypes

[dapper juniper]

its all objects @coral wasp

[coral wasp]

Convert the Y axis to a numeric type.

[dapper juniper]

you were right thak you

[cyan bay]

Anyone who works with ORMs recommend me a good tutorial?

Context: I want to learn to do more with SQLAlchemy than run basic queries against a schema through it. I'm hoping I can use ORM to create an interface to make underlying database relationships clear (Yes, the database lacks these relationships. Yes, that's bad. No, I cannot change it within my org.)

[cedar tiger]

The docs is pretty good. That's how I learnt it. https://docs.sqlalchemy.org/en/20/tutorial/index.html

[cyan bay]

Fair enough, thank you!

[paper flower]

I think sqlalchemy supports creating relationships without any foreign keys if that's what you need

[woeful cape]

Hi guys,
Does anyone know if sqlite3 in Python supports collate? From what I can find on Google, it should. But it doesn't seem to work?

1

(Pdb) cursor.execute(f"SELECT EXISTS(select 1 from flights WHERE location='brussels') COLLATE NOCASE;").fetchone()[0]
0

Anyone know what might be going wrong?

[woeful cape]

Omg

I just noticed it...

The bracket...

fml...

[storm mauve]

oh well too late but was already nearly done writing this so sending anyway```

py -m sqlite3
sqlite> CREATE TABLE test (name STRING);
sqlite> INSERT INTO test (name) VALUES ('world');
sqlite> INSERT INTO test (name) VALUES ('WORLD');
sqlite> SELECT * FROM test WHERE name="world";
('world',)
sqlite> SELECT * FROM test WHERE name="world" COLLATE NOCASE;
('world',)
('WORLD',)
py
import sqlite3
con = sqlite3.connect(':memory:')
statements = '''
CREATE TABLE test (name STRING);
INSERT INTO test (name) VALUES ('world');
INSERT INTO test (name) VALUES ('WORLD');
'''
cur = con.cursor()
for st in statements.splitlines():
cur.execute(st)

print(con.execute("SELECT * FROM test WHERE name='world';").fetchall())
print(con.execute("SELECT * FROM test WHERE name='world' COLLATE NOCASE;").fetchall())

[woeful cape]

I'm sorry for wasting your time etrotta! 😦

Dumb dumb me

Thanks anyways!

[jaunty sorrel]

hi, how to fix an (MySQL) error

it's my first time coding a database so please help I searched in youtube it told me to use (cmd) but also cmd keeps give me errors

[harsh meadow]

@jaunty sorrel When you select "View Logs" does it have any helpful information?

[jaunty sorrel]

is that mean something?

[harsh meadow]

Is the SQLserver running? Does it have the proper credentials(username/password?)

[jaunty sorrel]

it doesn't running but when I creat it I put username and password as well

there's my connection information but when I test it it;s keeo telling me that there're an error

[harsh meadow]

You cant connect to a database if it's not running

the SQL server must be running and available for it to connect

[jaunty sorrel]

so I have to download a MySQL app first?

[jaunty sorrel]

okay I download the MySQL app but it doesn't show me the welcome page idk why

[cedar tiger]

Get the Workbench, not the Installer, if you're following that Youtube video https://dev.mysql.com/downloads/workbench/

[night goblet]

Hi im trying to install mariadb package on my fedora, first i got errors, installed mariadb config and mariadb devel and got another error, then i upgraded pip, did dnf upgrade update, installed python3-devel and got another error:

      mariadb/mariadb_cursor.c: In function ‘MrdbCursor_execute_text’:
      mariadb/mariadb_cursor.c:1138:39: error: passing argument 2 of ‘PyBytes_AsStringAndSize’ from incompatible pointer type [-Wincompatible-pointer-types]
       1138 |         PyBytes_AsStringAndSize(stmt, &statement, (Py_ssize_t *)&statement_len);
            |                                       ^~~~~~~~~~
            |                                       |
            |                                       const char **
      In file included from /usr/include/python3.12/Python.h:50,
                       from ./include/mariadb_python.h:21,
                       from mariadb/mariadb_cursor.c:20:
      /usr/include/python3.12/bytesobject.h:56:12: note: expected ‘char **’ but argument is of type ‘const char **’
         56 |     char **s,           /* pointer to buffer variable */
            |     ~~~~~~~^
      error: command '/usr/bin/gcc' failed with exit code 1
      [end of output]
  
  note: This error originates from a subprocess, and is likely not a problem with pip.
  ERROR: Failed building wheel for mariadb
Failed to build mariadb
ERROR: ERROR: Failed to build installable wheels for some pyproject.toml based projects (mariadb)

Please help me

mariadb works i can create databases insert etc but cant install package

[fading patrol]

What exactly is causing the error, pip install mariadb? Are you in a venv?

[quartz mesa]

generally is there a reason to use implicit join
rather than normal join ?
can I just always use normal joins

[storm mauve]

the only reason you'd ever use anything implicit over the equivalent explicit way to do it is lazyness
you can always use normal joins

[pastel vale]

is there a version of copy_from for sqlite3?

[night goblet]

fixed

[quick jungle]

Makes sense, they prob made JOIN keyword for a reason

[obsidian basin]

I was googling join vs joinloaded in flask sqlachemy though this is in sqlachemy. Why even use joinload? I found this post https://stackoverflow.com/questions/41336474/inner-joins-vs-joinloads-in-sqlalchemy , but the only difference between join and joinloaded is
join = inner join
joinedload = outer join
.
So what is the point of just not using inner join and outer join rather then the weird syntax of joinedload and join?

[thorny anchor]

in sqla, join changes the result set. joinedload adds data to the fields of the same result set you'd get without using joinedload

[obsidian basin]

Sorry I am confused could you explain it again

[eager wagon]

I want to learn ER diagrams.

[torn sphinx]

hi im begginer

i wanna learn sql since

already know python

is there any resources?

thx

[mossy swift]

I've recently started learning SQL and I'm using sqlbolt.com
it teaches you SQL with interactive exercises
this channel also probably has some resources in the pinned messages

[pastel vale]

I just 'brute forced' learning sql 😂. Looked up a few tutorials on w3schools and immediately created a project. SQL is much more simpler than you expect it to be

[vivid cypress]

Hey guys im learning about binary and text files and starting csv files. Tell me a project that would help me strong these things . Can u suggest some?. Actually im new to these

[pastel tapir]

I tried to download PostgreSQL and everything thing worked fine the first day

On the second day I was not able to use psql terminal and now my git bash is completely broken

Git commands are not working and other commands also don’t work

Would anyone know how to solve this?

[coral wasp]

Open a help thread and paste the messages you're getting. #❓｜how-to-get-help

[pastel tapir]

Will do in a few thanks… currently on mobile

[foggy niche]

im working on a database too

[pastel tapir]

do I need to do this in the python-help?

the issue not related to python at all... just want to make sure that i'm doing this correctly

[coral wasp]

Should be fine there, a db or git question seems fine

[pastel tapir]

ok thanks

[torn sphinx]

ERROR:root:Error occurred: 'str' object has no attribute '_sa_instance_state'

can anyone help out with this

[ruby tusk]

try sharing your code in code blocks i.e. ```py <code> ```

as for your error, hold on

is this all of the code?

it usually comes up when you access an SQLAlchemy object as a string

based on this, you probably need to look at the User class

[torn sphinx]

there is more code but I feel like I know where the error is from but not able to fix it

it is the roles

[ruby tusk]

you're not using the roles variable

[torn sphinx]

    email = data['email']
    password = data['password']
    company = data['company']
    industry = data['industry']
    roles=['sponsor']```

[ruby tusk]

should you be using it in new_sponsor = on line 73?

new_sponsor = Sponsor( ...

[torn sphinx]

yeah when I do "roles = roles"

it throws that error

[ruby tusk]

should it be data['sponsor'] ?

[torn sphinx]

Idk what it should be

[ruby tusk]

did you create this code from scratch or copy paste it from somewhere?

[torn sphinx]

I created it

I did take help from chatgpt

[ruby tusk]

where are you getting roles from?

at the moment roles = ['sponsor'] is just creating a list with a single string in

as opposed to referencing a value from a key

can you upload your repo to github?

I can take a look at it now if it's soon enough, or tmw if not

[torn sphinx]

I can't it's my assignment

so it can't be out

would be great if u can help out with it @ruby tusk

[ruby tusk]

honestly this looks like it needs a bit of a redesign, I can show you tomorrow

[coral wasp]

You can also open a help thread: #❓｜how-to-get-help

[hot glen]

Do you recommend sqlite studio DB ?

[storm mauve]

SQLite: Yes, if you do not need to connect to the database over the internet nor need to have multiple apps interacting with it at the same time.
SQLite Studio: If you want you could use it, but I wouldn't really recommend it.

You might as well use a more popular tool like DBeaver

[orchid valve]

Hi there,

I'm looking into setting up an architecture with two databases and a load balancer in front. Could you advise on how to keep both databases in sync while maintaining isolation?

Thanks

[paper flower]

What kind of load balancing you want and why?

[orchid valve]

Mmmm i am not an expert but I would say round robin

If a database is off then it is balanced to the other

[paper flower]

Honestly I think you should look into replication

[orchid valve]

Do you have any resources ?

[paper flower]

What db are you using?

[orchid valve]

sqlite

[paper flower]

Ah, there's kind of no point in doing that with sqlite to be honest

[orchid valve]

So I d better use posgre ?

[paper flower]

Yep, it has built-in replication

There should be plenty of articles and documentation regarding that topic

[orchid valve]

and sql server ?,

[paper flower]

yep, if it has replication, I mostly use postgres

[visual stone]

Who knows the root of what I am currently facing?
When connecting to the memgraph database, I am using the gqlalchemy package, but I am getting some errors from the package import.
Currently, can't we use this package for the graph db?

[storm mauve]

which python version are you using?

[visual stone]

Python 3.11.3

[storm mauve]

and did you see this https://github.com/memgraph/gqlalchemy ?

[versed nebula]

I have 3 scripts:

1. Pulls data from an oracle db and returns a df
2. a script that uses the df from 1 and generates a pdf
3. script 3 that runs script 2 for every department in the org so each department has a report. (utilizes subprocess)

is there a better way I can do this? the problem is, I don't want script 2 to call the pull data function from 1 for every department, I want it so that the data is pulled once and saved in a pandas df. I can just save the data in a csv and call it everytime in script 2 but wouldn't that be a lot slower?

[coral wasp]

How large is the DF?

[versed nebula]

250k x 15

[coral wasp]

But, nonetheless, why isn't this all running in one process?

Get the DF, then generate PDF for each org

How long does the PDF generation actually take?

[versed nebula]

35-40 seconds

[coral wasp]

How many orgs?

[versed nebula]

29 but some orgs take 5 seconds

[coral wasp]

And, where is most of that time spent?

[versed nebula]

depending on size

[versed nebula]

generating the pdf and reading the csv

for each process

[coral wasp]

Reading the CSV is going to be pretty quick

Generating the PDF: how are you generating it?

[versed nebula]

thats the bad part

I'm using quarto

with python blocks

[coral wasp]

Oh, gotcha, so yah, it's going to be expensive

[versed nebula]

and when script 3 runs the qmd file (2) it auto generates a odf

is there a better way I can generate a pdf btw?

script 3 literally has a list of orgs and then runs the qmd (script 2) for each org in a for loop

basically

[coral wasp]

I just generate HTML and use chromium (via playright) to convert to PDF

The conversion can be spun out in async workers, so python does all the HTML generation and PDF generation is basically chrome print to PDF.

[versed nebula]

chrome print to pdf can be do automatically or do I have to do it?

[coral wasp]

automatically. Something like: "C:\Program Files\Google\Chrome\Application\chrome.exe--headless --disable-gpu --print-to-pdf=\mytempdir\output.pdf https:/ /www.chromestatus.com/

but replace the URL with your html file

[versed nebula]

oh gotcha that sounds nice

html is a lot faster eh

[coral wasp]

Well, it's more that it's easier to code and debug this... the PDF CSS is only a little different than our app CSS

And it's easier to get help, since it's not some weird stack like Quarto (sorry) or some of the lesser known PDF libs

[versed nebula]

gotcha okay thank you!

[pastel vale]

how do I join 2 tables using a third table as the conditional (e.g. i want to combine table1 and table2 based on a condtion from table3)

[inner heart]

Joining two tables based on a condition from a third table can be achieved using SQL JOINs. The concept is to use the third table as a bridge or mediator to establish the connection between the first two tables. Here's how you can do it:

Example Scenario
Let's assume we have the following tables:

table1 with columns: id, name
table2 with columns: id, description
table3 with columns: table1_id, table2_id
The goal is to join table1 and table2 based on the relationships defined in table3.

    t1.*,
    t2.*
FROM
    table1 t1
JOIN
    table3 t3 ON t1.id = t3.table1_id
JOIN
    table2 t2 ON t3.table2_id = t2.id;```

If you need more complex conditions, you can add WHERE clauses or additional JOIN conditions. For example, if table3 also had a condition column that you wanted to filter on, you could modify the query as follows:

    t1.*,
    t2.*
FROM
    table1 t1
JOIN
    table3 t3 ON t1.id = t3.table1_id
JOIN
    table2 t2 ON t3.table2_id = t2.id
WHERE
    t3.condition = 'some_condition';```

This query adds a WHERE clause to filter based on a condition in table3.

[pastel vale]

Thats exactly what I was trying to do. I figured it out in the end but I will still keep this example on hand as I will likely forget how it works. Thank you for the help

[elfin cradle]

@interest_group.command(name="apply", description="Apply interest to all accounts based on their type.")
    async def apply_interest(interaction: discord.Interaction):
        conn = sqlite3.connect('store_users.db')
        cursor = conn.cursor()
        
        cursor.execute("SELECT * FROM interest")
        interest_rates = cursor.fetchall()
        
        total_interest = 0.0
        applied_accounts = 0

        for account_type, rate in interest_rates:
            cursor.execute("SELECT account_id, balance FROM accounts WHERE account_type = ?", (account_type,))
            accounts = cursor.fetchall()
            
            for account_id, balance in accounts:
                interest_amount = balance * (rate / 100)
                new_balance = balance + interest_amount
                cursor.execute("UPDATE accounts SET balance = ? WHERE account_id = ?", (new_balance, account_id))
                
                await handle_transaction(bot, account_id, interaction.user.id, "Interest", "credit", "Interest", interest_amount)

                total_interest += interest_amount
                applied_accounts += 1

        conn.commit()
        
        embed = discord.Embed(
            title="Interest Applied",
            color=cb_color,
            description=f"Interest has been successfully applied to `{applied_accounts}` accounts.\n**Total Interest Applied:** `${total_interest:,.2f}`"
        )
        await interaction.response.send_message(embed=embed, ephemeral=True)
        
        conn.close()```

every time i do this i get a database locked error, can anyone help resolve this

[waxen finch]

"database is locked" with sqlite means you have a connection that hasnt yet committed their transaction

there's some nuance as to when a transaction can block another transaction (read vs. write transaction + journal mode), but in general you should commit and/or close connections early to avoid excessively locking your database

also keep in mind that other processes like an sqlite3 shell, database viewer, or another python script can lock your db file too, if you happen to have one of those open

[elfin cradle]

in my code all connections are open and closed within the command/funciton

so idk what other transaction would be blocking it

is there a way to debug?

i guess it could be my table viewer but thats not blocking anything else

yeah not the table viewer just closed it and tried

[waxen finch]

first, does the error still happen if you turn off your bot and run a separate script that uses the database? ```py
import sqlite3

conn = sqlite3.connect("store_users.db")
c = conn.execute("SELECT * FROM interest")
print(c.fetchall())```

[elfin cradle]

ill try real quick

no, specifying that script did work and printed

i also tried to vacuum it but it didnt do anything

idk what that does but chatgpt reccomended it

[waxen finch]

vacuum merely cleans up and compacts your database, it has nothing to do with locking

well, besides it needing to lock your database to rewrite it

[elfin cradle]

any other ideas

like possibly a way to debug this all

see whats blocking it

[waxen finch]

what's the full traceback of your error? does this happen for other commands? what does handle_transaction() do?

[elfin cradle]

  File "C:\Users\teddy\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\discord\app_commands\commands.py", line 842, in _do_call
    return await self._callback(interaction, **params)  # type: ignore
  File "c:\Users\teddy\OneDrive\Desktop\Chase Bank\Chase Bot\commands\interest.py", line 133, in apply_interest
    await handle_transaction(bot, account_id, interaction.user.id, "Interest", "credit", "Interest", interest_amount)
  File "c:\Users\teddy\OneDrive\Desktop\Chase Bank\Chase Bot\utils.py", line 33, in handle_transaction
    cursor.execute('''
sqlite3.OperationalError: database is locked

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

Traceback (most recent call last):
  File "C:\Users\teddy\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\discord\app_commands\tree.py", line 1248, in _call
    await command._invoke_with_namespace(interaction, namespace)
  File "C:\Users\teddy\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\discord\app_commands\commands.py", line 867, in _invoke_with_namespace    
    return await self._do_call(interaction, transformed_values)
  File "C:\Users\teddy\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\discord\app_commands\commands.py", line 860, in _do_call
    raise CommandInvokeError(self, e) from e
discord.app_commands.errors.CommandInvokeError: Command 'apply' raised an exception: OperationalError: database is locked```

[waxen finch]

oh, so it's opening a connection of its own?

[elfin cradle]

a connection opens in the handle_trasaction function

if thats what ur asking

should i pass the current connection into it?

instead of opening a new one

[waxen finch]

this error does not happen with other commands that access the database, right?

[elfin cradle]

no

[waxen finch]

then it's most likely that using two connections here is causing it to lock, because the connection in your command is in the middle of a write transaction (because of the UPDATE)

[elfin cradle]

that makes sense, just confused cus i do the same with with the transaction handling in othere places where theres already a connection

so should i pass the connection in?

[waxen finch]

does it make sense that these two actions should be part of the same transaction? in other words, would it be correct to undo all of your queries from your command and handle_transaction() if an error occurred?

if yes, re-using the connection would be correct

[elfin cradle]

so your saying i should pass the connection in

[waxen finch]

but do you understand the implications of combining them into one transaction?

[elfin cradle]

not really

also should i pass the connection in and make a new cursor or pass the cursor in

[waxen finch]

by making them use the same connection:

1. they share the same transaction, which means you won't be able to commit a specific change without also committing everything else before it

• hint: avoid conn.commit() inside handle_transaction()

2. one connection has to stay open for all of their work, which might lead to you locking your database for a longer period depending on what handle_transaction() does

• for example, if it needed to wait on a user's input and you didn't commit beforehand, your database would be left locked during that time, and other commands would fail

[elfin cradle]

!paste

https://paste.pythondiscord.com/MUQA

thats the handle transaction func

ik my sqlite is bad

so should i commit outside of the function, and pass a connection into there instead of creating a new one?

thats what im getting from what you said

[waxen finch]

so you're making an API request to message the user each time

[elfin cradle]

it basically just logs the transaction and notifies both accounts

[waxen finch]

for one, i wouldn't recommend doing that in your interest command because mass DMing can get your bot banned, but two, the time it takes to do that will add up and make your bot take longer to commit the transaction, which can cause other commands to fail with "database is locked"

[elfin cradle]

the bot is niche and in one server

its for a roleplay economy minecraft server kinda thing

is it auto banned?

or do people report it

anyways i gtg rn

ill eb back to view your response later

[waxen finch]

discord supposedly has its own filters to "quarantine" your bot, but of course im not aware of the exact threshold at which your bot will get blocked

i would strongly recommend just pinging everyone with one message that interest has been applied

but regardless, if you were to DM all your users, i suggest doing that after you've committed your transaction

i.e. calculate all their interest, update their balances and record the transactions, commit, then start DMing balance info afterwards

the first half should take very little time for your bot to do compared to making multiple API requests, so if you commit before DMing, your database won't get locked for too long

btw with asyncio + sqlite3 from stdlib, it's easier to accidentally cause deadlocks if you do something asynchronous while you have an uncommitted transaction (sending a message, responding to an interaction, waiting for a view, etc.)

the odds would be unlikely, especially with light usage, but regardless you can avoid those kinds of deadlocks by using an async library like asqlite or aiosqlite
https://pypi.org/project/asqlite/
https://aiosqlite.omnilib.dev/en/stable/ ```py
import asqlite

async with asqlite.connect("my.db") as conn:
await conn.execute("INSERT INTO xyz VALUES (?, ?, ?)", 1, 2, 3)

async with conn.transaction():
    await conn.execute("UPDATE xyz SET z = z + 1 WHERE y = 1")
    await conn.execute("DELETE FROM xyz WHERE y = 2")``` (asqlite is written by the same author as discord.py too)

[elfin cradle]

@interest_group.command(name="apply", description="Apply interest to all accounts based on their type.")
    async def apply_interest(interaction: discord.Interaction):
        conn = sqlite3.connect('store_users.db')
        cursor = conn.cursor()
        
        cursor.execute("SELECT * FROM interest")
        interest_rates = cursor.fetchall()
        
        total_interest = 0.0
        applied_accounts = 0

        for account_type, rate in interest_rates:
            cursor.execute("SELECT account_id, balance FROM accounts WHERE account_type = ?", (account_type,))
            accounts = cursor.fetchall()
            
            for account_id, balance in accounts:
                interest_amount = balance * (rate / 100)
                new_balance = balance + interest_amount
                cursor.execute("UPDATE accounts SET balance = ? WHERE account_id = ?", (new_balance, account_id))
                
                await handle_transaction(conn, bot, account_id, interaction.user.id, "Interest", "credit", "Interest", interest_amount, account_from=1)

                total_interest += interest_amount
                applied_accounts += 1

        conn.commit()
        
        embed = discord.Embed(
            title="Interest Applied",
            color=cb_color,
            description=f"Interest has been successfully applied to `{applied_accounts}` accounts.\n**Total Interest Applied:** `${total_interest:,.2f}`"
        )
        await interaction.response.send_message(embed=embed, ephemeral=True)
        
        conn.close()

!paste

https://paste.pythondiscord.com/NUOA

  File "C:\Users\teddy\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\discord\app_commands\commands.py", line 842, in _do_call
    return await self._callback(interaction, **params)  # type: ignore
  File "c:\Users\teddy\OneDrive\Desktop\Chase Bank\Chase Bot\commands\interest.py", line 131, in apply_interest
    cursor.execute("UPDATE accounts SET balance = ? WHERE account_id = ?", (new_balance, account_id))
sqlite3.ProgrammingError: Cannot operate on a closed database.

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

Traceback (most recent call last):
  File "C:\Users\teddy\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\discord\app_commands\tree.py", line 1248, in _call
    await command._invoke_with_namespace(interaction, namespace)
  File "C:\Users\teddy\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\discord\app_commands\commands.py", line 867, in _invoke_with_namespace    
    return await self._do_call(interaction, transformed_values)
  File "C:\Users\teddy\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\discord\app_commands\commands.py", line 860, in _do_call
    raise CommandInvokeError(self, e) from e
discord.app_commands.errors.CommandInvokeError: Command 'apply' raised an exception: ProgrammingError: Cannot operate on a closed database.```

@gamercr

@waxen finch the first bit is the command, paste is the handle_transaction, then the error

basically i just passed the connection into handle transaction instead of creating a new connection, but now its saying the database is closed, even though im pretty positive i didnt close it

[waxen finch]

the conn.close() line inside handle_transaction() is probably what did it

[covert pulsar]

Sorry for the very late ping, but thank you! The suggestion to use tables and that schema definitely helped me get on the right track.

[keen ravine]

Hey if I want to get going with spark, should I deploy all the spark, kafka, zookeeper etc in docker images? Someone once told me to always do kafka in docker never local, is it the same for others as well?
even the database?
I just want to know, I am not familiar very much with docker and I am a windows user so my docker is already 3X worse than your unix one. How can I make an img?

[pastel vale]

I have 2 select statements which are the same (apart from their condition). yet whilst one select statement outputs the correct results, the other select statement outputs the wrong data. why is this shit happeneing? i swear this did not happen yesterday. fucking hate this shit

[coral wasp]

Share

[pastel vale]

i just realised im a fucking idiot. ive got duplicate data in two tables. didnt look closely enough at the index

[coral wasp]

Classic pebkac

[pastel vale]

how can i link a jupyter notebook? just want someone to go over my database project and give me some feedback on what i can do to improve

[coral wasp]

Put it in GitHub

[pastel vale]

https://github.com/YourLocalPurveyorOfExplsoveGoods/MyDatabaseProjects tbh with you I do not know how to use GitHub. Literally just now, is the first time I've ever put something on GitHub

[coral wasp]

I'll look in a bit, but this is good: GitHub is a necessity

[pastel vale]

For now, I'll use github as a way for people on this server to help me give feedback on any projects I do.

[visual nova]

are there any good long database design video course on youtube? everything i find just talks about sql fundamentals and normalization forms which is quite basic information, i haven't seen much on professional design for real life examples

[coral wasp]

There's some Practice sites that give real world examples, which I think is more helpful. Sqlbolt and datalemur for instance

[cedar tiger]

System Design Primer comes to my mind. https://github.com/donnemartin/system-design-primer?tab=readme-ov-file#database

[near tapir]

is it ideal to usually use the data type timestamp instead of datetime?

[paper flower]

To use it where? 🤔

There's no datetime type in sql and there's no timestamp type in python

timestamp is datetime, just in db

[near tapir]

Sql

[near tapir]

There is datetime in sql

[paper flower]

Hm, in what standard/db? 🤔

Because I may be wrong but couldn't find them in postgres docs for example

[near tapir]

Ms sql

[paper flower]

Docs mention that datetime is kind deprecated and you should use datetime2 👀

I think it's the same as timestamp, and there doesn't seem to be a timestamp type in mssql unless I'm missing something

[indigo pond]

Should i use a sql database for a small project or just sqlite3?

[real jungle]

Does data need to be shared between users? if it does then sql might be the way, it doesn't? sqlite3 is more suitable for local storage (not shared)

[thorny anchor]

sqlite is an sql database 🤔

[indigo pond]

I mean if i should create a database with mysql workbench yk

Or if sqlite in a python project is enough

[indigo pond]

just want to test some things and learn python

[real jungle]

If it's for learning purposes using sqlite is good, requieres less management

[indigo pond]

alright thanks man

could i also launch a small website with it?

[real jungle]

Yeah a common architecture is having business logic (your code) to be pared from storage (the DB) so any db or storege form can work

[indigo pond]

thanks fam

[midnight ferry]

Anyone has any idea on google cloud spanner ? I am having some trouble trying to parameterize a query which goes like

So in python my variable be like

Spanner needs param_types ... And i m having hard time trying to pass param_type for this scenario

[near tapir]

1. Let R(A, B, C) be a relation with the functional dependency B ⇒ C. (The underlined attributes A and B build the composite key, and the attribute C is functionally dependent on B.) In which normal form is the relation R?

2. Let R(A, B, C) be a relation with the functional dependency C ⇒ B. (The underlined attributes A and B build the composite key, and the attribute B is functionally dependent on C.) In which normal form is the relation R?

My answer for number 1 is 1NF but I'm confused for number 2?

[maiden verge]

Guys please tell me is it alright

??

It's urgent please