databases

[torn sphinx]

-_- yeah!! i lack sense thank you
i know you had replied to me with the question its just that today i tried to search my own question but didnt found thats why asked again

if you are helping pls help kindly man
anyways you tried to make me feel sorry and bad but

[brittle gorge]

Hi I am trying to create a search functionality in python, that would search the postgres database based on the given input. After numerous attempts, i still get similar errors. Would anyone be able to help me so that I can share portion of the code, where I am troubling in.

def search(car_make="",car_model="",year="",first_owner="",vinnumber=""):
conn=psycopg2.connect("dbname='Car_Inventory_db' user='postgres' password='password' host='localhost' port='5432'")
cur=conn.cursor()
sql="SELECT * FROM carInventory WHERE car_make=? OR car_model=? OR year=? OR first_owner=? OR vinnumber=?"
addr= (car_make,car_model,year,first_owner,vinnumber)
cur.execute(sql, addr)
rows=cur.fetchall()
conn.close()
return rows

connect()
#insert("1002","Honda","Civic","2003",'true',"3sdfSKHAN")
#print(view())
print(search(car_make="Honda"))

Traceback (most recent call last):
File "C:\Users\14168\Dropbox\PC\Desktop\Car_Store\backend.py", line 47, in <module>
print(search(car_make="Honda"))
File "C:\Users\14168\Dropbox\PC\Desktop\Car_Store\backend.py", line 38, in search
cur.execute(sql, addr)
psycopg2.errors.SyntaxError: syntax error at or near "OR"
LINE 1: SELECT * FROM carInventory WHERE car_make=? OR car_model=? O...

[grim pier]

H ey can someone help out with some advice? Im trying to store an entire chat history from a discord channel for a ticket system

What would be the best way to structure the DB?

This is the structure i have at the minute.

Chat_history i was just going to dump the entire chat in? Is this an effective way? or is there a better way you could suggest to do it?

[fading patrol]

Pretty sure their free Postgres is persistent and has automatic backups so unless you really need SQLite, maybe use that instead.

[fading patrol]

I think it's the question marks, that's not how psycopg2 works. https://stackoverflow.com/questions/8671702/passing-list-of-parameters-to-sql-in-psycopg2

It's really not a lot harder to learn. Heroku makes it so easy to spin up and the syntax is barely different

What is "normal Postgres"? Running Postgres on Heroku is basically the same as running it locally if that's what you mean

[jade horizon]

is there any way to prevent database locking on discord bots due to async?

[pure cypress]

Did you mean blocking, as in blocking the asyncio event loop?

If you use an async library for interacting with the database, then it will not block while waiting for i/o

[torn sphinx]

hi all i dont know much about this stuff so
are mysql databases private or can oracle check your database

[gloomy spindle]

CREATE TRIGGER on_account_delete AFTER UPDATE OF deleted OR DELETE 
  ON economy
  FOR EACH ROW
  WHEN NEW ISNULL OR NEW.deleted = TRUE 
  EXECUTE PROCEDURE purge_inventory(OLD.user_id)

Raises asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "NEW"
Help

[fallow bluff]

[gloomy spindle]

shush hay ‼️

[gloomy spindle]

im headed to sleep plz ping if reply thanks

[grim vault]

parenthesis? WHEN (NEW ISNULL OR NEW.deleted = TRUE)

[gloomy spindle]

let me try

[frigid glen]

Could be worth your while to use a library like pydbantic to handle your db sessions with async - https://github.com/codemation/pydbantic - which can help you avoid locking, also nice for migrations

[grizzled wadi]

https://github.com/RobertCraigie/prisma-client-py might be a good bet for you as the actual connections are managed in a rust event loop

[frank gorge]

can we add image in SQL database using Python

[proven arrow]

You can add using the blob type. Or alternatively to store the image on some file system and then store the path to that file in the database.

[frank gorge]

for eg , i have a program , thats shows ur image when u type ur name , so can we add an image using the program python

[proven solstice]

Hey everyone, I'm working on a hobby project where I would like to use mongodb as database with mongoengine as ORM. I'm looking for someone who could take a look at the definition of collections/documents and tell me if I set it up in a good way for the type of queries I would like to perform later. I can offer monetary compensation via Paypal for the support. DM me if interested.

[torn sphinx]

Anyone has idea of making cashflow that can import data from different excel / csv files

I was thinking of taking data from different excel / csv files, making it into json and then making new spreadsheet with all of it

it needs to have daily incomes and outcomes from different companies

Ill have to use libreoffice too cuz Im broke lol

[rigid dock]

anyone uses dbeaver coummunity db here?

[pure sleet]

i use the database tool, whats up?

[rigid dock]

im unable to connect the db ?

i don't why

[fading patrol]

Is it running? Is that the correct port?

[fading patrol]

This is pretty basic stuff, a good beginner project. Maybe read the book How To Automate The Boring Stuff if you don't know where to begin. You could start with JSON but SQLite might be better

[torn sphinx]

Im going through some pandas data science tutorial with reading csv / excel files etc.

[rigid dock]

nope , it shows connection failed and 3306 is the default port for mysql

and its showing the same error for all the other db's

[heady merlin]

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

how to create a command to transfer money in a discord bot?

[torn sphinx]

youtube_dl is against #rules 5

[heady merlin]

where

[fading patrol]

You mean you are randomly trying different db types? If you have a db in the first place you will know what type it is.

[coarse goblet]

Hi there, im having an issue with pysondb the error is the following. i have attempted a reinstall.

Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/flask/cli.py", line 235, in locate_app
    __import__(module_name)
  File "/home/pi/Shelf/app.py", line 3, in <module>
    from pysondb import db
  File "/home/pi/.local/lib/python3.7/site-packages/pysondb/__init__.py", line 1, in <module>
    from .db import getDb
  File "/home/pi/.local/lib/python3.7/site-packages/pysondb/db.py", line 11, in <module>
    from pysondb.errors.db_errors import IdNotFoundError
ModuleNotFoundError: No module named 'pysondb.errors'

[latent bone]

11/12/2021 10:55:03 AM ERROR Resetting connection with an active transaction <asyncpg.connection.Connection object at 0x0000021718B27CA0> can someone tell me what this is

every time I make a request to my DB or I try to push some data to it this pops up

and I don't think the data saves

[lime echo]

Is there anyway I can check if the database requires commit() or not?

I can check the SQL code if it starts with "INSERT, DELETE or UPDATE" but I am trying to see if I can check it directly from db = psycopg2.connect() or from the cursor.

[ionic pecan]

there's no builtin way to do it

either use autocommit or commit manually

[ionic pecan]

did you commit?

[mystic sparrow]

[latent bone]

I use tortoise orm

[vale heart]

Hello. I don't know if I should use SQL or not. Let me explain the type of data I am getting. I have an app that will send me images. For each image, I have a pose file, intrinsic file, pointcloud file, and gps file associated to it.

Each of those files has certain attributes. Right now, I just store all the files in separate folders as json and access them like that. However, I want to use SQL but I don't know if it is a good idea to store data like mine in a SQL format.

Just to give an example: I receive image-0.jpeg, ..., image-100.jpeg, pose-0.json, ...m pose-100.json, int-0.json, ...m int-100.json, pointcloud-0.json, ..., pointcloud-100.json, gps-0.json, ..., gps-100.json. The number could be anything (100, 200 etc.)

[fading patrol]

If your data will fit comfortably in tables (columns and rows) then SQL is good. But if your data is currently in JSON it might be easier to use a NoSQL database like MongoDB

[vale heart]

Here is a situation. I have N image files, N pose files, and N int files. Pose file I and int file I are associated with image file I. Each pose file has J attributes and each int file has K attributes. How would I store this information? J** **and K are fixed while N can be any number between 1 and infinity. Should I use SQL for this or should I use NoSQL. Isn't my example for a relational database (pose and int files associated to an image file)

[neon marten]

Can someone help me with the query for this

For the Salay schema : employee_Id, company_id, salary are the columns

[fading patrol]

If you're going to use SQL, you need to design a schema that works for you, but to me this really sounds like exactly what schemaless DBs are for

[fading patrol]

What have you tried so far?

[neon marten]

Yea

It’s unsolved over the internet too

[fading patrol]

Or maybe it's easy enough for you to dump all this in a few tables, it's up to you. If you do use SQL, don't store images in the DB as blobs, better to store filepaths or something. And similarly while you can store JSON inside SQL, that's generally not recommended either

[neon marten]

Time sensitive

Its on stack overflow

[vale heart]

what?

[neon marten]

Unsolved

[neon marten]

I’m finishing up a challenge here

[vale heart]

lol

and you want us to give you the solution?

that's not how this works mate, you need to show us what you tried and then we can try to guide you towards the right answer

[neon marten]

Lol I’m asking help and the query I wrote isn’t working

Its upto you weather to give it or not

[neon marten]

??

[fading patrol]

And what is the error or result you get?

[devout olive]

hi people

if I post my codes, would you mind reviewing it?

I can share my repository

I don't know if it allowed here tho

[fading patrol]

Sharing a link to a repo should be fine, you are you just looking for general feedback or you have a specific question?

[devout olive]

general feedback

and I am very new to SQL

so it is like a very "ugly" code

https://github.com/boughtatthetop/2021mypublicsqldatabase.git

Thank you in advance if you look into it

[fading patrol]

I would start by documenting the purpose of your API and how to use it, in the readme file

Have you considered using an ORM like SQL Alchemy? I would recommend it. It's much easier to change and manage models that way then to work with raw SQL embedded in Python

[devout olive]

is it a extension on VSCode?

how many types of SQLs are there?

when I search google I see mysql, I believe there are different versions?

[neon marten]

I was able to correct it and finish it

[pure sleet]

you dont even show the salary schema

[neon marten]

Lol its not about knowing it or not, I’m doing this challenge in a last minute and was not sure about it. But then thought it out for a second and finished my query.

[pure sleet]

how are you gonna ask for help on something when you've omitted critical information though?

[grim vault]

The SALARY table is described in the sentence after the picture. It was all there to solve.

[pure sleet]

it's not fully visible in the picture shared above

that's basically what i'm saying

[grim vault]

But the descriptions was added as text, is what I'm saying.

[pure sleet]

oh, i missed that, my bad

[glacial mauve]

is there somethign about python functions that makes it so an Update statement won't work or am I crazy

[delicate field]

@peak pilot Please don't try to ping @everyone or @here. Your message has been removed. If you believe this was a mistake, please let staff know!

[mellow delta]

Is it possible to grab data from mongo db to a website such as weebly

[steel rover]

so im trying to switch from sqlite3 to mongodb for my discord bot, and im using pymongo for it. here, message.guild.id keeps changing, so ive used an f string for it. will this work, or is there any other way to get values that change, like in sqlite you use the ? in the syntax:
prefix = pcol.find_one({"prefix"}, {"guild_id": f'{message.guild.id}'})

[opaque prawn]

just use ctx.guild.id ?

why message?

[steel rover]

its not in a command

ctx is only for commands

[formal coral]

i kinda just want a suggestion,
how do we store/persist 1-2 key values? For example if I am making a small web-app just for myself, in which there will be in-app currency. In a normal app, I would make a table/collection to store user data, with a balance column for their balance, but what if its just 1 person

• json files not possible as the entire app would be serverless with static content on cdn and dynamic controlled via serverless funcs

[pure sleet]

uhm, environment variables?

[torn sphinx]

Hello guys, I have some question about a postgresql query. This one:

DROP TABLE IF EXISTS person;
CREATE TABLE person(
   sys_uuid uuid DEFAULT uuid_generate_v4 ()
  ,sys_category    varchar(50) DEFAULT 'N/A'
  ,PRIMARY KEY (sys_uuid)
  ,CONSTRAINT fk_category FOREIGN KEY (sys_category) REFERENCES category(sys_name)
);

My question is about the:

,CONSTRAINT fk_category FOREIGN KEY (sys_category) REFERENCES category(sys_name)

Returning the error:

ERROR:  there is no unique constraint matching given keys for referenced table "category"

I don't catch it, the sys_name column is well existing in my table category as you can see below:

DROP TABLE IF EXISTS category;
CREATE TABLE category(
   sys_uuid uuid DEFAULT uuid_generate_v4 ()
  ,sys_type        VARCHAR(50) NOT NULL
  ,sys_name        VARCHAR(50) NOT NULL
  ,PRIMARY KEY (sys_uuid, sys_type, sys_name)
);

Any idea ? 🙂

[grim vault]

Well, sys_name of the table category is not unique.

[torn sphinx]

Thank your for you return. well, it is set as primary key, is't it enough ?

[grim vault]

No, as only all three columns of the primary key combined are unique, and not each one alone.

The foreign key references sys_name alone, so it must be unique to work.

[torn sphinx]

oh I see you point, should I rather reference sys_uuid and make it unique ? I think it should be better

[grim vault]

That would be one way to solve it. I don't know your data structure, so can't comment on that.

[torn sphinx]

Okay, thank you @grim vault for your clear answer.

[formal coral]

I mean the value would change, I don't think we can change env vars from the code

it will be hosted on vercel if that helps

[pure sleet]

if you wanna store persistent values that can change, you're looking for some sort of database, don't know how else you can achieve that without it

[fading patrol]

SQL Alchemy is a Python library that handles the SQL for you so you can just write pure Python. Here's an example. There are other ORMs that do the same thing but Alchemy is a good one to know. https://www.jitsejan.com/creating-simple-rest-api-with-fastapi-and-sqlalchemy

[fading patrol]

Yeah, MySQL and Postgres are the two main free SQL servers. There's SQLite, Microsoft SQL Server, others. There are slight differences you need to know. One advantage of using an ORM like Alchemy is that it basically takes care of that for you and can work with any backend

[pure sleet]

isn't sqlalchemy like python specific?

[fading patrol]

Yes

[latent bone]

bump cuz I still don't know how to fix this :(

[harsh pulsar]

!paste post your code

[delicate field]

Pasting large amounts of code

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

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

[latent bone]

It happens in two scenarios that I've concluded, or maybe all, but first:

guild = (await Guild.get_or_create(discord_id=ctx.guild.id))[0]

OR

guild.changelog_channel = channel_id

await guild.save(update_fields=["changelog_channel"])
await guild.refresh_from_db(fields=["changelog_channel"])

Whatever attempt to request from my DB or maybe push data too it causes it

and I think especially for the latter where I have to save, I don't think it saves

I was testing it and the message sent but trying to fetch it right after returned a NoneType Object (meaning it never saved)

[proven arrow]

@latent bone What is guild.save() and guild.refresh_from_db? This is meaningless and gives no information on how you actually interact with the database through your code. Please try to all the relevant parts.

[latent bone]

guild.save() Creates/Updates the current model object

guild.refresh_from_db() Refreshes latest data from db.

The only other relevant parts of my DB is my init I guess since tortoise orm does most the work for me:

await Tortoise.init(tortoise_config.TORTOISE_CONFIG)

tortoise_config.TORTOISE_CONFIG

TORTOISE_CONFIG = {
    "connections": {"default": config["DATABASE_URI"]},
    "apps": {
        config["TORTOISE_APP_NAME"]: {
            "models": [config["BATABASE_MODEL_PATH"], "aerich.models"],
            "default_connection": "default",
        }
    },
    "use_tz": config["DATABASE_USE_TZ"],
    "timezone": config["DATABASE_TIMEZONE"],
}

config[] is a pyyaml parser

[untold sleet]

using pymongo how do i remove an element from a list field that matches a filter, but only 1 element (because $pull removes all that match)

[proven arrow]

@latent bone I am not familiar with what tortoise is so sorry can't help you much there. However, the following may help you. https://github.com/MagicStack/asyncpg/issues/652
https://github.com/MagicStack/asyncpg/issues/271

[latent bone]

this is an issue I'll have to make on the tortoise orm github

[short junco]

how to rerun a a piece of code if upon trigger?

like loop an entire script

[leaden lake]

Hello everyone 👋,
Please how to create a database in python
And thanks.

Moumen

[ember oar]

do you know what db you want to use

what python web framework you want to use

[leaden lake]

I want to use a json file

[ember oar]

that could mean a lot of things

https://www.youtube.com/watch?v=OOSl2jeAA5U

look at this

[obsidian jolt]

how to define c?

[pure cypress]

c is meant to be the cursor

How you obtain a cursor depends on which db library you're using

[obsidian jolt]

i'm using sqlite3

[pure cypress]

In that case you can call execute directly on the connection, which is obtained through sqlite3.connect(...)

That being said, you should consider switching to an async library like aiosqlite

[obsidian jolt]

already set up

[pure cypress]

You can make the connection an attribute of the bot so it can be accessed easily from most places

bot.db = sqlite3.connect(...)

[obsidian jolt]

so instead of c. i can just use bot.db?

[pure cypress]

Yeah

[obsidian jolt]

so i just change ''c = conn.cursor()''

to bot.db?

[pure cypress]

Assign the connection, not the cursor

[obsidian jolt]

i did..

[pure cypress]

Also, if you do this in on_ready, keep in mind the ready event can be dispatched multiple times during your bot's lifetime. This means you'll end-up creating new connections. You should have a check to see if you already have a connection so you can avoid that.

[vale heart]

I want to map a nested json to a class in one line. Is that possible? This is what my class looks like (the GeometricAttributes class is what I want to map the json onto):

class Orientation(db.EmbeddedDocument):
    x = db.FloatField()
    y = db.FloatField()
    z = db.FloatField()
    w = db.FloatField()


class Translation(db.EmbeddedDocument):
    x = db.FloatField()
    y = db.FloatField()
    z = db.FloatField()


class AxisLengths(db.EmbeddedDocument):
    x = db.FloatField()
    y = db.FloatField()
    z = db.FloatField()


class GeometricAttributes(db.EmbeddedDocument):
    axis_lengths = db.EmbeddedDocument(AxisLengths)
    translation = db.EmbeddedDocument(Translation)
    orientation = db.EmbeddedDocument(Orientation)

[bitter oak]

C:\Users\Programmer\Desktop\Database-Website\main.py", line 3, in <module>
    conn = pyodbc.connect("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};" + \
pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

I need help on this please

[proper crown]

mongodb

[violet stag]

When it comes to connecting to a postgres db using python, which method (sqlalch, pgpysco2, or other) is used the most in the corporate world? And is that answer also the most efficient?

[steel rover]

so im trying to move my discord bot from sqlite3 to mongodb, and right now im working on the prefix method but i get this error, and im not even able to find out WHERE this occurs:

[burnt turret]

show your query

[steel rover]

result = pcol.find_one({"prefix"}, {"guild_id": message.guild.id})

[burnt turret]

there's the issue, the first argument to find_one has to be a dictionary, you're passing a set {"prefix"}

consider reading the tutorial on the pymongo docs https://pymongo.readthedocs.io/en/stable/tutorial.html#getting-a-single-document-with-find-one

[steel rover]

i want it to retrieve the value of prefix, where the guild_id is message.guild.id, so what would be the correct query?

[steel rover]

actually nvm, i figured everything out and it works nicely now

[abstract pivot]

You should use an async wrapper of sqlite3 such as asqlite or aiosqlite because it's blocking

[earnest kayak]

If I wanted to create a verification system for a discord bit of mine and I wanted to store the IDs of each user, would it be better to use SQL, a structured database, or MongoDB, a non-structured database

[pure sleet]

do you have structured data?

[torn sphinx]

guys I have really weird issue

Im looping through spreadsheet

getting data out of it

the result I get:

transportstyrelsen trängselskatt [['2021-11-11', '']]
transportstyrelsen trängselskatt [['2021-11-11', '']]
transportstyrelsen trängselskatt [['2021-11-11', '']]

transportstyrelsen trängselskatt [['2022-01-03', '-657']]
transportstyrelsen trängselskatt [['2022-01-03', '-770']]
transportstyrelsen trängselskatt [['2022-01-03', '-296']]

now it works???

[devout olive]

which one is foreign key?

I'm on sqlite and drawing a database on drawsql website

[fading patrol]

Foreign key is only foreign relative to a given table. So I think that's why there is no symbol for it. It's not foreign to its own table

[devout olive]

Ok thank you!

[simple grove]

which software is that? just wondering

[devout olive]

drawsql website

do you mind if I post my database here you may comment on it?

[torn sphinx]

what's the code look like, looks like skipping columns or something

[devout olive]

is sqlite3 and mysql syntax different?

[thorny anchor]

they're very similar

[devout olive]

in this relational database

if I want to code an API

to prepare a quote for me

and the quote contains 3 things

prodcut name, currency code and price

should I make the Quote table with 3 foreign keys?

[fading patrol]

Yes, SQLite and other SQL databases are relational, and that's called an ERD (entity relationship disagram) that you are drawing

[devout olive]

I am really really struggling with this part

The platform enables companies to quickly create quotes for their customers. A quote
contains one or more line items, representing:
1. a subscription for a product or service,
2. a quantity and
3. a price, including its currency. (See below currency section)
All prices are always excluding VAT. The VAT (always 21%) should automatically be
calculated on the quote, showing a total excluding VAT, and a total including VAT. The
expected format of the quote is JSON.
Once a quote is accepted by the customer, companies can easily (via 1 API call) convert the
quote into active subscription. If a subscription is active, the customer gets automatically
invoiced on the last day of each month. For the scope of this project, customers need to call
the API to see if they have a pending invoice to proceed to payment. 

this is how far I've come

In the quote table there should be prodcut name + price + currency code

the currency code and price then later will be used to retrieve data from external API

[fading patrol]

Most of that will probably be done through application logic. I think your database may have what you need

The lines between product and invoice tables don't make sense to me but otherwise the rest of your diagram is clear

[devout olive]

yess I already went to code the API and saw that my database sucks

I came back and now modifying it

I haven't modified the infoice part yet

The platform enables companies to quickly create quotes for their customers.

is this a request from company

or a post from company

or a request from customer?

[fading patrol]

The other piece of the diagram I don't follow is what "subscription id" is and how it relates to a quote record

[devout olive]

so sorry still modifying it and on progress

Once a quote is accepted by the customer

trying to understand if I need one more table

"Subscription" table?

to record subscribed customer?

[fading patrol]

I'm guessing this is like the customer filling a shopping cart and seeing what the cost will be before they place an order

[devout olive]

so from an API stand point of view:

The payload for it needs to be

• prodcut name
• quantity
• Customer_Email

[earnest kayak]

Would a key and value format be structured?

[fading patrol]

I'm thinking you might need a many-to-many relationship, so an intermediate table "product-quote". There's lots of info online if you're not familiar with the concept of many-to-many

[fading patrol]

Customer email can be stored on the customer record, so you just need a foreign key connecting your quote to the customer, and you have that

[devout olive]

in a payload item

can I retrieve more than one item from a foreign key? (Product_Price, Product_CurrenyCode, Product_Quantity)

So if I am coding the table for Quote:

Should it include like Product_ID 3 times?

[fading patrol]

Sure, you can select any columns you want from as many tables as you want as long as their is one foreign key to connect each table relationship

[fading patrol]

If you have a product-quote table the columns would be product_id, quote_id, quantity. I think that's all you would need to query all the products and their quantities in a given quote

[fading patrol]

No, if all your data consists of key value pairs, you don't need a structured db

[earnest kayak]

So

MongoDB?

@fading patrol

[fading patrol]

Yeah

[devout olive]

thank you,
let me try and see

quote into active subscription.```

do you think I need another table for subscriptions?

or is it going to be done on the fly

since all the quote info - if customer accepts - = subscription info

I don't know how to code the "accept" or "reject" part

but trying to understand the whole process and build tables accordingly

[devout olive]

sorry so here

dbase.execute('''
    CREATE TABLE IF NOT EXISTS Quote(
        Quote_ID                    INTEGER PRIMARY KEY AUTOINCREMENT,
        Customer_ID                 INTEGER,
        Company_ID                  INTEGER,
        Product_ID                  INTEGER,
        Quantity                    INTEGER NOT NULL,
        TotalPriceVATE              FLOAT,
        TotalPriceVATI              FLOAT,
        Acceptance                  BOOLEAN,
        Active                      BOOLEAN,
        StartDate                   DATE,
        EndDate                     DATE,
        FOREIGN KEY(CustomerAccountID) REFERENCES CustomerAccount(CustomerAccountID),
        FOREIGN KEY(Company_ID)REFERENCES Company(Company_ID)
        FOREIGN KEY(ProductID) REFERENCES Product(ProductID),
        FOREIGN KEY(QuoteID) REFERENCES Quote(QuoteID)
    )    
    ''')

Note: table still not modified yet

1-
Product_ID INTEGER, do I need to add two more of these? or just one enough for the API interaction later on since I want to retrieve 3 data from this table

2-

        TotalPriceVATI              FLOAT,```
these are going to be calcualted on the fly right ? so do I delete them from my table?

[fading patrol]

I don't think I understand what a subscription is in this context, but if it's as simple as accept or reject, that sounds like a boolean value

[devout olive]

yep it is boolean

but does it require a table?

the boolean Active/Passive where it blongs ?

[fading patrol]

If there can be multiple products in a quote and multiple quotes can include the same product, then you want an intermediate many-to-many table like I mentioned earlier

[fading patrol]

Yeah, I don't think you need to store that VAT stuff in your table if it's always the same percentage on every product and every customer

[devout olive]

one prodcut can have multiple quotes

not the other way around

unless customer wants to buy multiple prodcuts

which isn't clearly said in the project

[fading patrol]

Store it wherever is logical. What entity is active or passive? Subscriptions? You may need a table for that

[fading patrol]

This is a common enough thing I would ask, but if it's definitely always going to be one product per quote, then you can store product_id and quantity on the quote table

[torn sphinx]

uh i dont know if this is the place to be asking for this kind of help but is there a fix? (sorry if it isnt)

[proven arrow]

@devout olive You might want to add customer details as stored values on invoice/quote, especially if customer details like name/email can change.

[devout olive]

If I want to do so, should I add Customer_Name, Customer_Surname, Customer_ID in the Quote table or just Customer_ID ?

[proven arrow]

You can still have the foreign key relation to customers table, but you should also keep the name, email or other required info you may need in the future for reporting and auditing on the invoice/quote table.

If you don’t do this and the customer account is to get deleted then you will lose the informazioni of the customer who made the quote. Likewise if the user changed their email then the quote/invoice is no longer like it was submitted.

[torn sphinx]

can i get some help ^ ?

[proven arrow]

You can login with the Postgres user

I don’t think Postgres has root like other dbs

[devout olive]

@app.get("/session_grades")
async def session_grades(payload: Request):
  values_dict = await payload.json()
  # Open the DB
  dbase = sqlite3.connect('tp10.db', isolation_level=None, check_same_thread=False)

  # Step 1: retrieve all the information about the session, underlying exams and students by joining the tables

  grades_query = dbase.execute(''' 
    SELECT Students.matricule, Exams.grade FROM Sessions
    LEFT JOIN Exams ON Exams.session_id = Sessions.id
    LEFT JOIN Students ON Exams.student_id = Students.id               
    WHERE Sessions.id  = {session_id}
    '''.format(session_id = str(values_dict['session_id'])))
  
  grades = grades_query.fetchall()

  # Step 2: clean the results

  # Close the DB
  dbase.close()
  return grades ```

I don't understand this part :

    LEFT JOIN Exams ON Exams.session_id = Sessions.id
    LEFT JOIN Students ON Exams.student_id = Students.id               
    WHERE Sessions.id  = {session_id}
    '''.format(session_id = str(values_dict['session_id'])))```

Students is another table

Exams is another table

but the code says SELECT ........................................... FROM Sessions

Is it syntax thing?

and I don't understand this line either

LEFT JOIN Exams ON Exams.session_id = Sessions.id

so we are creating a new header?

Session.id?

[proven arrow]

What is this? If you don’t understand then how are you writing all this?

[devout olive]

from lecture notes

[proven arrow]

Oh

[devout olive]

the previous one was my own code

[proven arrow]

Isn’t it holidays for people?

[devout olive]

this one is lecture notes

bruh

I've been studying and doing projects since like mid october

it never finsihes

untill semester finishes

[proven arrow]

Ok, I just assumed as when I was in college it finished around this time. Maybe changed over time

[devout olive]

around end of Jan

I am done

of course if I don't burn out

do you mind if you could look back to the code

[proven arrow]

What’s the issue?

[devout olive]

    LEFT JOIN Exams ON Exams.session_id = Sessions.id
    LEFT JOIN Students ON Exams.student_id = Students.id               
    WHERE Sessions.id  = {session_id}
    '''.format(session_id = str(values_dict['session_id'])))```
Students is another table
Exams is another table
but the code says SELECT ........................................... FROM Sessions
Is it syntax thing?
and  I don't understand this line either

it is an API GET call

which asks for session_id

[proven arrow]

Ok you went from relationships to sql code, to api call? I’m really confused.

Where does it say …..

[devout olive]

wait

you know the relationship between them right?

so we have a database

and we use sql to interact with it

we use python to interact with sql

we use python to create an API

we use API to interact with database

so in the code above

the session_id is a GET request

GET http://127.0.0.1:8000/session_grades HTTP/1.1
Content-Type: application/json

{ 
    "session_id": "1"
}

in the backend

the code does this:

@app.get("/session_grades")
async def session_grades(payload: Request):
  values_dict = await payload.json()
  # Open the DB
  dbase = sqlite3.connect('tp10.db', isolation_level=None, check_same_thread=False)

  # Step 1: retrieve all the information about the session, underlying exams and students by joining the tables

  grades_query = dbase.execute(''' 
    SELECT Students.matricule, Exams.grade FROM Sessions
    LEFT JOIN Exams ON Exams.session_id = Sessions.id
    LEFT JOIN Students ON Exams.student_id = Students.id               
    WHERE Sessions.id  = {session_id}
    '''.format(session_id = str(values_dict['session_id'])))
  
  grades = grades_query.fetchall()

  # Step 2: clean the results

  # Close the DB
  dbase.close()
  return grades ```

the part that I don't get is the sql part

    LEFT JOIN Exams ON Exams.session_id = Sessions.id
    LEFT JOIN Students ON Exams.student_id = Students.id               
    WHERE Sessions.id  = {session_id}```

this part

[proven arrow]

Ok well do you understand database joins?

[devout olive]

before that

first line

the JOINs are another headache of mine

I'll get to them soon 😄

So it selects matricule from Students

grade from Exams

but the code ends with FROM Sessions

here is how sessions look like in database:

dbase.execute(''' 
    CREATE TABLE IF NOT EXISTS Sessions (
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    
    course_id INTEGER NOT NULL,
    session_date DATE NOT NULL,
    
    FOREIGN KEY (course_id) REFERENCES Courses(id)
    ) ''')```

the Sessions table doesn't include any information from those written ones

[proven arrow]

Yes but it performs a join of the tables first, which combines the rows from the tables specified in the join. It then filters the data using the WHERE clause. Thereafter the final step is to SELECT from the end result set.

[devout olive]

wait the code runs backwards?

[proven arrow]

#databases message

Refer to that image, should help you.

Hope that answers your question, and helps your understanding. If you have further questions I can pick them up tomorrow.

[devout olive]

thank

you

cheers

[half knot]

Do ForeignKeys only work with Integers? I am trying to add another ForeignKey to a table and the localhost will not load when my last 'FK' is set as a string even though the original value is set Primary_key=True

[pure sleet]

do you just have a single table?

[tawdry kelp]

any one familiar with BigQuery or Sql in genral who might help my tired brain?

SELECT *,

PARSE_TIMESTAMP("%F %T", REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(BikeData.updateTime, "\\.0*", ""), "T", ""), "Z", "") ) /* This Fails with failed to parse input String: "updateTime" */
from  `TimestampsTable` as BikeData
Left Join  `DifferentTimestamps` as BikeState
on BikeData.vehicleId = cast(BikeState.vehicleid  as String) AND 
PARSE_TIMESTAMP("%F %T", REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(BikeData.updateTime, "\\.0*", ""), "T", ""), "Z", "") ) < BikeState.timestamp /* This does NOT retunr any error
/*where BikeData.updateTime = "updateTime"                        If uncommented this returns no results */ ```

[tawdry kelp]

Exactly that part reinforcec my confusion xD

[fleet ibex]

scrubbing data question, my return values from a process are in a generator, so I store those values in sqlite to reference which results are useable against a master_list, delete the ones that are not, and then pull the results back to work with it.. as this is done while multithreading.. is there a better way?

I ask only because im testing with 100 or so threads, and its working ok.. but I fear once in production.. if could be an issue, as I dont currently use locks on the db.. but might have to.. as 100 threads are running fine.. if I need locks in the future, would it be better to set a semaphore of 100 to the db access.. I dont k ow how I would do that yet, just theorizing.

[jade jacinth]

bro mongodb clusters take like 1,000,000,000 hours to make

atleast that's what it feels like

[torn sphinx]

Hey i have some data in a table in sqlite
i want to sort the table itself, not sort it when querying

[fleet ibex]

btw, I may have come up with a solution to my issue.. I didnt know about shared cache and connecting 2 dbs.. so I think I will fire off inmemory dbs in my threads, since at that time Ill only be reading from the master_list.. then either saveout the files to be queued later to be put in the db, or find a way to start a queue from my threading module to dump a queue back to my main thread.. or write another module to run concurrently as a queue thread and pass the threading module results to it as a queue to be processed by it running a single thread... getting so complicated.. :/

[torn sphinx]

anyone experienced with pandas and excel

I cant find any nice cheatsheet or something like that

[pure sleet]

what do you mean?

aren't records stored in the order you put them in?

[fleet ibex]

i think he means he has a table he wants to sort, so his queries comeout without needing to be sorted.. so, perhaps he isnt creating the db, or he has a process that inserts from threading and its being processed into the db out of order.

[fleet ibex]

a quick search reveals the command as alter table.. as a place for you to start

[torn sphinx]

thankyou

[torn sphinx]

simple, i have a table, i want it sorted
not the results sorted when we use "sort by"

[pure sleet]

bro, what's the purpose of that though?

you're still gonna query

[torn sphinx]

i put rows in the db and they r unsorted
i want to query the latest ones according to date
if i query first 10
they might be old while latest ones according to date might be beneath these 10

[pure sleet]

you could sort them first then select the top 10 in your query

[torn sphinx]

ok, so if i have like 10000 of them, i can sort all of them?

[pure sleet]

i mean sort in query

[torn sphinx]

whats the maximum ammount i can query without much...performance/memory loss?

[pure sleet]

a select of a select basically

[torn sphinx]

sorry, im not familiar much with dbs

[pure sleet]

in one statement

what are you using to run the queries if i may ask?

[torn sphinx]

yeah i want to display the data in the db on a dashboard

its webscrapped data so is in large quantity

[pure sleet]

yea, what do you use to run queries?

or you can basically select from a specific date range etc

but it's possible it might not return atleast 10 results

[torn sphinx]

im using python so:-

cur.execute("SELECT * FROM table")
row = cur.fetchone()

[torn sphinx]

no no its ok to have as much data

i just want to get the data sorted

[pure sleet]

alright. so you can run direct sql statements. great

so what I'm suggesting is basically sub queries

[torn sphinx]

SELECT * FROM table ORDER BY date ASC

is this good?

[pure sleet]

selecting from a select statement

[pure sleet]

you said you want first 10 results right?

[torn sphinx]

yes i want top 10 results which are the latest according to the date column

[pure sleet]

and this does not work for you?

your use case rather

[torn sphinx]

but the table is not sorted based on date

[torn sphinx]

it does, but i dont understand this

[torn sphinx]

i use this select command

does this load all the data from sql db into memory or something?

or does it just tell the db that from now on whene ever fetching find the lates rows based on date and return them?

because if i the db has say 100k rows
would select query call all of them?
it would be very memory intensive no?

[pure sleet]

aren't only the final results saved in memory on your variable?

[torn sphinx]

ummm.... so the SELECT ...SORT BY
what does it do actually?
does it just tell sql how to process when we call fetch functions?

[torn sphinx]

yes

[torn sphinx]

ok its working
but it interpretting
2020-4-10 < 2020-4-3

I had stored the dates as text type

[pure sleet]

https://stackoverflow.com/questions/2468202/how-does-a-sql-query-work maybe this might answer your questions

[pure sleet]

ascending as in increasing order

[torn sphinx]

ok so i put 2020-4-04
now this works

[pure sleet]

also use datetime if your saving dates if youre already not doing that

[torn sphinx]

thanks, will do that

[torn sphinx]

thanks for this too

[carmine anvil]

Any chance someone can look at an excel sheet and tell me if I'll need more than 1 table to just 1 with like 30 columns?

[proven arrow]

Describe your use case and what you are storing

[carmine anvil]

Ok well I'm trying to build webapp to replace a googlesheet. So to enter information in the sheet you add a record at the bottom of the log table like this

+-----------+--------+--------------+--------------------+----------+
| Vehicle # | Action | Date | Description | Location |
+-----------+--------+--------------+--------------------+----------+
| T-1 | Park | 10/10/15 | Busted headlight | |
| T-2 | Garage| 11/10/15 | Transmission | Garage |
+-----------+--------+--------------+--------------------+----------+

So depending on the action you select the information is populated into a Park/Garage table which displays the most recent data for each vehicle using the date entered in the log table. The Park/Garage tables have different columns, for example the Park table has no location field. So I guess my question is should I create one table that has all 30 columns (eg. 15 from Park and 15 from Garage) and then when I want to display each table on the webpage I just query the relevant columns.

[polar spoke]

cur.execute("SELECT user_id, user_name, score, guild_id, RowNum FROM(SELECT ROW_NUMBER() OVER(ORDER BY score DESC LIMIT ?) RowNum, user_id, user_name, score, guild_id FROM users) WHERE guild_id = ?",(limit,guild_id))

I am trying to add the "LIMIT ?"

but i am putting it in wrong place on query

[proven arrow]

Yes you should have more than one table.

[trail tapir]

Is there a package the helps load data using python to postgres as an http request?

[pure sleet]

can you explain what you mean here?

[fading patrol]

You want to build an API then? FastAPI or Flask are good for that

[trail tapir]

I want to push data to the DB with POST requests

It is an interal URL, there is not user and password

[fading patrol]

So yeah, a simple API. Here's an example tutorial. https://staskoltsov.medium.com/fastapi-crud-api-using-postgresql-and-docker-compose-ca434c662d40

[devout pilot]

how would i store the contents of a variable in a sqlite db

[carmine anvil]

anyone have a tool or set of rules used for designing tables and their relationships?

[proven arrow]

You should try to aim for a normalised database. Following the normal forms can help. https://en.m.wikipedia.org/wiki/Database_normalization

[torn sphinx]

can i connect to pgadmin using an ipv4 address?

[fleet ibex]

i was just looking that up today myself
https://docs.python.org/2/library/sqlite3.html

[fading patrol]

Yes

[fading patrol]

Just one variable? Is there a reason you think you need SQLite? If you don't need structured tables of data, a flat file (txt, CSV, JSON, pickle/shelve, etc ) will probably be easier

[torn sphinx]

can someone link me to open source web3 Operating systems
preferably one owned by a DAO i see so much decentralized stuff but not sure what is what tbh lol
metis vs dxos vs elastos vs whatever else i cant think of rn

[wild pelican]

        if a[0] is None:
            print('ur mom')``` How can I check if it doesn't exist in the table?

[torn sphinx]

oof

[open sentinel]

Is a database just like a formatted list/dictionary/set/tuple

[dusty maple]

hello

[fading patrol]

If it's an unstructured / noSQL database like MongoDB then you can dump that kind of data into it, but otherwise, no not really. Those are data structures and databases are something different.

[open sentinel]

What about like a json

[fading patrol]

JSON lets you store data in a flat file. If you need to change something in your data, you have replace the entire file. Databases are a solution to that problem, you can add or change whatever value you want, quickly, without have to read and rewrite the entire thing

[open sentinel]

Ah I see, I’m gonna need to start learning because a bunch of the projects I want to do involve some form of needing to store data

[fading patrol]

Here's something simpler than a database: https://docs.python.org/3/library/shelve.html

If you do need a database, I would start with SQLite3

[fleet ibex]

has anyone had issues with comparing macaddresses in sqlite? .. like the format im given with the macs is 0a-00-3e .. and I think possibly the dashes are not allow matches.. the columns are set to string.. and yet, I cannot do not exist as it will return nothing, but exist will return the entire db.. and I need to use not exist to scrub uneeded data.

ive tried simple quieries and subqueries.. both resulting in the same results.

[earnest kayak]

I guess so. I haven't made anything yet

[pure sleet]

doesn't need to be a sql database in my opinion then

but if you end up having some relationships between some data or tables, sql would be the way to go imo

[austere portal]

Hello I installed postgresql using brew in macos and I get this error when trying to enter the psql shell shell $ psql psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: database "user" does not exist

[fading patrol]

Looks like you need to use a different username. psql -u postgres might work by default I think

[austere portal]

Tried that it says shell failed: FATAL: role "postgres" does not exist

[fading patrol]

Ok, well I would look into whether homebrew sets a different user account or something

[austere portal]

maybe it was a problem in the brew installation

[austere portal]

Thanks, I'll check that

[fading patrol]

If you get stuck, I'd recommend either setting it up on Docker (setting your own password) or spinning an instance up on Heroku (super easy and they tell you the username and password but there's a bit of network lag compared to running local, and they will charge you over 256mb or something like that)

[devout pilot]

ye there is a reason to needing it. A flat file wouldn't do what i need

[crystal compass]

new = settings["Auto-Ban"]["names"] = ["hello", "test"]
guild_settings.update_one({"_id": 13123123}, {"$addToSet": new})

Error: pymongo.errors.WriteError: Modifiers operate on fields but we found type array instead. For example: {$mod: {<field>: ...}} not {$addToSet: [ "hello", "test"]}

[carmine anvil]

how is the transaction history of say an sqlite db normally handled? If I want to see every commit ever executed on the db how would I do that?

"the answer is triggers.."

[late canyon]

i am working with sqlite
and i m facing this problem please help where i have done wrong?

[earnest kayak]

ok thanks

[austere portal]

Thanks, all I had to do is to create a role name postgres, seems like the homebrew installation only creates a role under the logged in user's user name and doesn't create a role named postgres

[oak oyster]

how do i delete an observed dictionary in replitdb

[hoary wharf]

What's the best way to handle queries towards postgres using pandas where the results are in the millions?

I ran out of memory when using chunksize even

[silk glacier]

What do you need to do with the results?

[hoary wharf]

Store them in a csv-file, right now.

But I think I did it with a "COPY (query) TO STDOUT etc", which was blazingly fast.

[slender bear]

#help-apple

[devout olive]

I want to retrieve total number of subscribers for a company

Can I SELECT

• comapny.company id
• product.price
• prodcut.currency
• quote.qoute quantity
• subscription.subscription active
• customer.customer id

LEFT JOIN on company?

is it possible?

for example company and quote doesn't share a key

or shall I do it step by step?

1. select customers with active subs on product A

then from product A find the corresponsing company?

[desert wing]

I don't think mysql has a native range type so it would have to be janky workaround edit: postgresql does support range type

[fading patrol]

I'm pretty new to SQL too but you can have a select statement with multiple JOIN ... ON statements. As long as each pair or tables you join has a fk relationship I think it should work

[devout olive]

yeah if there is key, it is no problem

but have you seen my ERD?

if I want to select a subscription and find out the company I can't do it directly, right?

so it is better to add a fk to subscription or no?

[flat marlin]

hi anyone good with psycopg2?

[pure sleet]

can i ask what you used to make this diagram?

[devout olive]

drawsql

[pure sleet]

thanks.

[fading patrol]

I believe you can join company to product and product to subscription in the same query with two separate joins. I don't think you need a direct fk relationship between company and subscription

[torn sphinx]

How to use sqlite3 asynchronously?

[proven arrow]

There is a package called aiosqlite you can use

[potent basin]

Hey guys, anyone here familiar with pandas? I need help making a subset of a very large CSV file into another file to use.

[torn sphinx]

It has bugs with me

For example, fetching a row always returns none although there's a current existing row

sqlite3 doesn't have this bug

[slender atlas]

Did you forget await?

Or maybe you're using the functions differently

[torn sphinx]

I didn't forget await lol

await self.bot.cursor.execute('SELECT count FROM partnerships;')
result = await self.bot.cursor.fetchone()

result is None

i tried to use it with sqlite3, it returns (1,)

*sqlite3 not aiosqlite

[slender atlas]

try

async with self.bot.cursor.execute("SELECT count FROM partnerships") as cursor:
    result = await cursor.fetchone()

[torn sphinx]

What's the difference? And is cursor.execute context-wrapped?

[slender atlas]

It might be

[torn sphinx]

I'll try it

[slender atlas]

It seems as though all you had to do is self.bot.cursor = on the first line, since it returns a new Cursor object

But having stuff context managed doesn't hurt

[torn sphinx]

async with self.bot.cursor.execute('SELECT count FROM partnerships;') as cursor:
AttributeError: __aexit__

It does not have a context manager

[slender atlas]

Weird. I remember it had something.

[torn sphinx]

Probably I'll define the execute function and use the variable to fetch

[slender atlas]

🤔

I remember using async with myself

Maybe it only exists for Connection objects

[torn sphinx]

oh shit

i'll see

[slender atlas]

Nope, it also exists in Cursor objects

Are you sure you imported the right library?

[torn sphinx]

I did

[slender atlas]

What is the full traceback?

[torn sphinx]

Ignoring exception in on_message
Traceback (most recent call last):
  File "PATH/python3.8/site-packages/discord/client.py", line 352, in _run_event
    await coro(*args, **kwargs)
  File "PATH/circuit_utilities/cogs/partnerships.py", line 31, in on_message
    async with self.bot.cursor.execute('SELECT count FROM partnerships;') as cursor:
AttributeError: __aexit__

        cursor = await self.bot.cursor.execute('SELECT count FROM partnerships;')
        result = await cursor.fetchone()

This also did not work

Should i not use a global cursor?

[slender atlas]

Do

print("__aexit__" in dir(self.bot.cursor))

[proven arrow]

No

[torn sphinx]

Okay

>>> print(hasattr(aiosqlite.Cursor, "__aexit__"))
True

This is weird

[slender atlas]

I care specifically about the cursor your bot uses.

[torn sphinx]

async def start() -> None:
        async with aiosqlite.connect("bot_database.db", loop=loop) as database:
            async with database.cursor() as cursor:
                bot.database = database
                bot.cursor = cursor
                await database.commit()
                await bot.start(config.token)
                # database closes when bot is closed

It's a normal cursor

[slender atlas]

You should use a connection instead of a cursor, for a start

[torn sphinx]

Why?

A connection cannot fetch or execute, cursor can

[slender atlas]

A connection can execute (creates a new cursor for it automatically)

[torn sphinx]

and wouldn't creating too many cursors cause the database to be locked?

[slender atlas]

Not unless you manage it using with

If such thing happens, just make a new connection

[torn sphinx]

if i execute via connection, would the cursor be closed?

[slender atlas]

It will be when the .close() method is called or if used by a context manager

[torn sphinx]

I have +7 files using the database, I'll not change the code to be context-unwrapped

[slender atlas]

If they are context wrapped, just put async

Before with

[torn sphinx]

they are not, my bot has one global cursor that executes all, which being closed when the bot is closed

I found a solution, how to enable auto-commit?

It may be disabled or something, which updates the cursor

[slender atlas]

In your case, await self.bot.database.commit()

After every INSERT, UPDATE, DROP, CREATE or any of these sort of statements, you need to commit changes.

SELECTs don't need to have that

[raw saffron]

get rid of your async with database.cursor() as cursor:

database is a connection, assign that to your bot (bot.conn or whatever) and use bot.conn.execute('SELECT * FROM ...')

The context manager will take care of committing, but if your bot is long-lived with infrequent queries, you should create the connection when doing the queries instead.

[devout olive]

 query_customer='''
                SELECT Customer.Customer_ID, Customer.Customer_Name, Customer.Customer_Surname, Subscription.Subscription_ID, Product.Product_Name, Company.Company_ID 
                FROM Customer
                LEFT JOIN Company ON Company.Company_ID=Product.Company_ID
                LEFT JOIN Subscription ON Subscription.Customer_ID=Customer.Customer_ID
                LEFT JOIN Product ON Product.Product_ID=Subscription.Product_ID
                WHERE Subscription.Subscription_Active={active}
                '''.format(active=str(values_dict['Subscription_Active']))
  
  print(query_customer)
  test_query=dbase.execute(query_customer).fetchall()
  print(test_query)
  results = pd.read_sql_query(query_customer, dbase)

here customer_id is reference in subscription and not in company or prodcut

how do I join these?

I got it

lol

it was super fun

....

I am a nerd

(cries inside)

[proud stone]

Is the Django .raw() API much faster than using the standard query API

[torn sphinx]

It says Connection object has no attribute fetchone

[timid stratus]

hey if any1 is good with mongodb here lmk

using mongodb how do i use findbycustom for multiple fields
https://cdn.discordapp.com/attachments/621912956627582976/921174315171479612/Screenshot_2021-12-16_at_22.57.47.png
https://cdn.discordapp.com/attachments/621912956627582976/921174689370476594/Screenshot_2021-12-16_at_22.59.17.png

[torn sphinx]

what should i use aiosqlite or asqlite

[proven arrow]

what is asqlite? Do you mean sqlite?

[torn sphinx]

no the async wrapper danny made

https://github.com/Rapptz/asqlite

[proven arrow]

Who? Not sure i follow what that is

@torn sphinx I would use aiosqlite, which looks to be better maintained. https://github.com/omnilib/aiosqlite

[torn sphinx]

alr thx

True, i don't think asqlite is maintained

aiosqlite is

[grim pier]

H ey can someone help out with some advice? Im trying to store an entire chat history from a discord channel for a ticket system

What would be the best way to structure the DB?

This is the structure i have at the minute.

Channel_ID, Message_ID, Message_Author Date, Time, Chat_History

Chat_history i was just going to dump the entire chat in? Is this an effective way? or is there a better way you could suggest to do it?

[proven arrow]

What do you need to do with the chat history?

[grim pier]

@proven arrow basically its for a ticket system for discord to the closed chats can be downloaded at a later date

[proven arrow]

Who can download and how often?

[grim pier]

Just admins

Probably very rare

[proven arrow]

Well you can just store the history in a json column. In this case it would be ok since your main purpose is for archive, and not really reading/updating.

[grim pier]

can i use JSON in MySQL? its all pretty new to me

[proven arrow]

Yes it supports

[grim pier]

okay great, do you know of any examples and ill take a look 🙂

[proven arrow]

https://www.mysqltutorial.org/mysql-json/

That link should cover it from the db/sql point of view.

[grim pier]

awesome thanks for the advice

[ornate kayak]

Im wanting to make a backend in python for the first time, is Django or Flask all I need? I only have experience with Node, so i'm a bit confused on what the equivalent of models would be in Python. Is there somewhere I could see a python backend repository?

Sorry if this isn't the place to ask

[devout olive]

if anyone wishes to start some project on python+sql+API, my school project was really nice. It would be a nice starting point to start learning if you are interested

https://github.com/boughtatthetop/2021mypublicsqldatabase/blob/main/requirements page 1.png

https://github.com/boughtatthetop/2021mypublicsqldatabase/blob/main/requirements page 2.png

these two pages are the requirements

[fading patrol]

Django is a bit complex, you might want to start with Flask or FastAPI. You'll need to pick a database too, but together that should be what you need for a basic Python backend. If you have more questions specific to web frameworks try #web-development

[ornate kayak]

Thanks brother, I appreciate it

[bleak crown]

Hi, with SQLite how could i check if a value is in a column? Like, let's say we have a column called some_group_of_people and this column has datas like ```sql
some_group_of_people

|alice, jenny, something|
|klex, roie, nyghl|
|etc.|``` how could i get all of the rows that contains "nyghl"?

[verbal jetty]

hey guys!! I'm facing a creepy error in my bot code. If anyone knows sqlite3 then please DM me...

[bleak crown]

Huh?

[verbal jetty]

oh sorry, I thought your asking hot to get all values from nyghl. Sorry dude

[verbal jetty]

Idk how to check using SQlite but you can do this using pandas. create a dataframe of your SQlite file. And there's a method in pandas which returns the column and index no. of the specified value

[proven arrow]

@bleak crown what’s the column data type?

[bleak crown]

TEXT

I mean 😄 IN doesn't work unfortunately :S

[proven arrow]

Ok well not the best way to store list kind of data in a text column because your run into problems like your having now.

[bleak crown]

I found LIKE could be useful but

[proven arrow]

But you can use the LIKE operator

Yeah

[bleak crown]

Yp

Uh 👉 👈 how can i store more efficiently?

[proven arrow]

Normalise it

[bleak crown]

I can redesign my db in my purpose

This was to store the players of a game, each row represents a game and in it i just passed players via seperating with "," 😄

[proven arrow]

So you would make a new table, where each row is one of those people

[bleak crown]

Hmmm, yeah i can

How could i relation them btw?

It would be sick 😄

[proven arrow]

What sick?

[bleak crown]

Like, it automatically appears in the players of a game if a player in a seperate table has a game's id in it

[proven arrow]

What are you trying to model and store?

[bleak crown]

I have games, rpg games. They are stored in a table like: games and each game is stored in there with their own special ids, datas etc.

And there was a column called players too to store the players ids in it

[verbal jetty]

import sqlite3 as s

db = s.connect('<file location/path>')
cursor = db.cursor()

cursor.execute('SELECT * FROM Events')
data = cursor.fetchall()

rows = []

for row in data:
    if 'nyghl' in row:
        rows += [row]
    else:
        pass

print(f'Rows having "nyghl": {rows}')

[bleak crown]

If we are going to move players to a seperate db 😄 i just wanted to learn that could how i somehow make a relation between them

[verbal jetty]

@bleak crown this my help you with your query

[verbal jetty]

this query

[bleak crown]

Yeah :S but didn't want to do it with python at the start

[proven arrow]

There is more information that is missing to give you a good answer. So a each game can have many players? Each player can have many games or what?

[bleak crown]

Each game can have unlimited players and each player can have unlimited games too 😄

I was doing it with like: ```sql
game_ids | players

| Game 1 | nyghl, 38654 |
| Game 2 | nyghl, bunny_29, 38654 |```

But you said make players seperate

[proven arrow]

Ok so a simple one would be 3 tables. Games, Players, and game_players. Games table has data of each game (game_id, name, rating). Players table has info on just players (player_id, name, age, email).

And then in game_players you have (player_id, game_id) which are both foreign keys to the games and players table.

[bleak crown]

Oh

[proven arrow]

The above is a many to many relationship, however if it doesn’t make sense you should maybe read up on relationships and how to form them.

Also to normalise a database which you are trying to do here you can try to implement the normal forms. https://www.guru99.com/database-normalization.html

Hope that makes sense or helps, I know it was a lot to take in if you were not familiar with any of this. If you have any further questions let me know. Also the example I gave above is just a simple one and you may probably need other tables to store other info like player inventory etc.

[bleak crown]

Hmmm, for now going with LIKE solves the problems or at max i can add another table and store every player's games on there and query from there but it seems it would make 4x, 8x and maybe 20x the db's size

[fading patrol]

Did you try WHERE ?

[bleak crown]

Yeap, how would i try IN without WHERE

[carmine anvil]

can anyone help me with setting up an audit log with flask sqlalchemy?

[stray solstice]

someone know how can i do database for my kivymd app

[grizzled wadi]

Here's a very basic example that uses Prisma and KivyMD https://github.com/RobertCraigie/prisma-client-py/tree/main/examples/kivy-basic

[torn sphinx]

pymongo.errors.ServerSelectionTimeoutError: localhost:27017: [Errno 111] Connection refused, Timeout: 30s, Topology Description: <TopologyDescription id: 61bd084e7109d00071f42961, topology_type: Single, servers: [<ServerDescription ('localhost', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('localhost:27017: [Errno 111] Connection refused')>]>

i keep on getting this error when trying to do something to atlas mongodb

[silk glacier]

I guess it is trying to connect on port 27017 and can't. Do you have something else using that port?

[devout olive]

hi

import sqlite3
connect=sqlite3.connect('chinook (3).db', isolation_level=None)
c=connect.cursor()
query='''
        SELECT * FROM artists
        '''
print(query)
print(c.execute(query).fetchall())
print(connect.execute(query).fetchall())

what is the difference between these two prints ?

print(c.execute(query).fetchall())
print(connect.execute(query).fetchall())```

could you please explain me the concept of

c=connect.cursor()```

with analogies 🙂

[torn sphinx]

Idk

How do I find out

I'm using Ubuntu if that matters*

And that only happens with the recent projects that I'm creating

Wasn't happening before

And with the old ones it still works fine

I even made sure I did the exact same steps as before and yet that error always comes up to the new ones

[silk glacier]

A cursor usually is something that lets you iterate through the set of results instead of returning all at once. Maybe there are some other methods for cursors that sqlite3 provides, I'm not familiar

[devout olive]

thanks

do you mind if you could explain this:

what is the difference between these two prints ?

print(c.execute(query).fetchall())
print(connect.execute(query).fetchall())```

they print exactly same results

[silk glacier]

Right, because you're calling fetchall(). With the cursor you could call methods like fetchone(). Or something like fetchmany and pass the number of records you want to retrieve

Now as I said I'm not familiar with sqlite3. Maybe connect.execute(query) uses a cursor implicitly, I'm not sure. So you may be able to call stuff like fetchone through connect.execute as well

[proven arrow]

It is the same. Connection.execute() is just a shortcut that will create the cursor and call execute on it for you.

[devout olive]

dbase.execute("PRAGMA foreign_keys = 1")```

is this a line to be written in between every database open and close queries?

[silk glacier]

I've never seen that before myself. 'Pragma' typically indicates a hint to a compiler, linter, query planner, etc

[eternal hare]

someone knows pyDal ?

[rugged sedge]

Hello. Does anyone know the answer for this question? https://stackoverflow.com/questions/70394191/update-multiple-rows-in-orm-model-at-once

It my question on sqlalchemy

[pure sleet]

your update data is a list, use a loop to update 1 by 1

[rugged sedge]

It’s not efficient @pure sleet

[pure sleet]

how is it not efficient?

[rugged sedge]

If you’re going to use the loop you’ll be requesting many sql statements

[pure sleet]

you have another solution?

no matter how you look at, you're gonna have to query to update since you're using an ORM

[rugged sedge]

I know about bulk_update_mappings

[pure sleet]

if that works for you, go for it

[tawdry kelp]

Hello 👋 im asking you in time of need to give me advise. I have huge ass dataset, which i need to process. It includes an group by which is time intensive but can most likely not be replaced. Therefor the execution takes +6 hours which BigQuery doesnt allow. What be the proper workaround?

[silk glacier]

Do you mean that you run the query and then process the results which are returned? Would it be possible to run the query in smaller chunks?

[tawdry kelp]

I do only run the query at this point.
And yes im wondering if there is a way to run it in chunks.
Im just lost here how to split it up

[tawdry kelp]

What would be the best approach via python cl?

[soft sky]

Whic one I learn fistly? SQL or MySQL?

[thorny anchor]

why not both at the same time. you can't use mysql without learning sql, and learning sql with no database to interact with is useless

[fading patrol]

MySQL is (a flavor) of SQL, isn't it? Is there some plain default "just SQL" I'm not aware of?

[tawdry kelp]

https://www.dataquest.io/blog/sql-vs-mysql/ @soft sky

[soft sky]

I got it thanks for all info

[tawdry kelp]

Im gonna leave this one here in hopes someone might have an idea https://stackoverflow.com/questions/70405356/operation-timed-out-bigquery-optimizing-window-function

[silk glacier]

1 million rows is really nothing

do those tables have index set on them?

[tawdry kelp]

yes, my assumption aswell thats what it should be able to do, im not sure about index on the table got read up on it quickly

so no it does not have index set

so i gota partition the table inorder for it to have an index?

[tawdry kelp]

Thanks for the suggestion I partitioned the table now with Date Index by Day, giving it a go 👍

[tawdry kelp]

@silk glacier so after some digging i managed to establish that the Query for the partitioned Table wouldnt work as the partitioning by referenced values results in a non segmented window 😦

[tawdry kelp]

Any one around here mind poking me the right direction cause it seems to me that clustered - partitioned tables can not be joined onto eachother

[torn sphinx]

Bump

[long echo]

What are the commands for safely inserting arguments into an sql query?

Cant really look up previous projects right now Q.Q

[storm mauve]

depends on which library (driver) you are using

[fleet ibex]

just checking this, so for every connection to sqlite i make if I require this, I just add it as the 1st line after a connection... cursor.execute("PRAGMA foreign_keys=ON")

[harsh pulsar]

yes, if you want foreign key constraints to be enforced. you can skip it if you are only reading from the database and not writing

[rugged hornet]

Relational databases are called SQL. Mysql is a software of SQL. Like MongoDB is NOSQL. Like that.

[rugged hornet]

Sql is the language, and Mysql and PostgreSQL, Sqlite3 these are the software which runs sql. Like Python is the language but CPython or PyPy is the interpreter which runs Python.

[fallow mist]

async def create_database():
    async with asqlite.connect("test.db") as connection:
        async with connection.cursor() as cursor:
            await cursor.execute(
            '''
            CREATE TABLE IF NOT EXISTS animals
            ( user TEXT , animal TEXT
            '''
            )```

    await cursor.execute(
  File "C:\Users\sony\AppData\Local\Programs\Python\Python39\lib\site-packages\asqlite\__init__.py", line 210, in execute
    await self._post(self._cursor.execute, sql, parameters)
  File "C:\Users\sony\AppData\Local\Programs\Python\Python39\lib\site-packages\asqlite\__init__.py", line 85, in _call_entry
    result = entry.func(*entry.args, **entry.kwargs)
sqlite3.OperationalError: incomplete input```

i am sorry i am new

[wraith nymph]

you forgot another ")"

next to animal TEXT

[fallow mist]

oh k thanks

how to make a cursor in a command?

[wraith nymph]

@client.command()
async def report(ctx, user:discord.User, reason):
    db = sqlite3.connect("main.db")
    cursor = db.cursor()
    cursor.execute(f"SELECT channel_id FROM main WHERE guild_id = {ctx.guild.id}")
    result = cursor.fetchone()
    channel = get(result)
    await channel.send(f"{user} has been reported for {reason}")
    db.commit()
    cursor.close()

I printed the variable result at the report command and it gave me this: ('850356901232771085',)
error:

 raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'str' object has no attribute 'send'

can someone help me, this is sqlite3

and this is the table

[pure sleet]

channel = get(result)
await channel.send(f"{user} has been reported for {reason}")

channel is a string here

doesn't have the property send

[torn sphinx]

bump

[brave bridge]

https://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem-in-orm-object-relational-mapping 🙂

[fallow mist]

how to view sqlite database

[pure sleet]

use the sqlite cli

if you want a gui interface, I'd recommend db beaver

[fallow mist]

yes i want the gui interface

okay

[eternal hare]

someone used pyDBMS?

[tawdry grotto]

raise errors.NotSupportedError( mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported

[eternal hare]

i want a generic driver for mysql oracle sql and mssql any good one ?

[grizzled wadi]

https://github.com/RobertCraigie/prisma-client-py works for MySQL and Microsoft SQL Server

but not oracle

and it is an ORM not a driver, however you can ignore that and run raw SQL queries if you want to

[eternal hare]

I dont need and ORM, I dont control the source database, on some have really bad structure ...

I was going to use ODBC ....

but there is maybe better now than ODBC

[torn sphinx]

PS C:\Users\Stepan\Documents\GitHub\helix-rpg> & C:/Users/Stepan/AppData/Local/Programs/Python/Python38/python.exe c:/Users/Stepan/Documents/GitHub/helix-rpg/main.py
Traceback (most recent call last):
  File "c:/Users/Stepan/Documents/GitHub/helix-rpg/main.py", line 7, in <module>
    psql.create_connection()
  File "c:\Users\Stepan\Documents\GitHub\helix-rpg\static\psql.py", line 7, in create_connection
    connection = psycopg2.connect(
  File "C:\Users\Stepan\AppData\Local\Programs\Python\Python38\lib\site-packages\psycopg2\__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused (0x0000274D/10061)
        Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (::1), port 5432 failed: Connection refused (0x0000274D/10061)
        Is the server running on that host and accepting TCP/IP connections?

import os
import psycopg2


def create_connection():
    global connection
    connection = psycopg2.connect(
        host=os.environ.get("DB_HOST"),
        port=os.environ.get("DB_PORT"),
        user=os.environ.get("DB_USER"),
        password=os.environ.get("DB_PASSWORD"),
        database=os.environ.get("DB_DATABASE")
    )

    return connection

DB_HOST isn't localhost btw

any ideas?

[fading patrol]

Did you verify that DB_HOST is set correctly and is being read correctly by the function?

[torn sphinx]

i did not....

wait

why it shows me none, when i have it setted up in .env

https://cdn.creve.fun/icXvnbdS

[fading patrol]

I've not worked with .env files but at least we've identified the problem. To me it looks like your code is probably reading your Windows environment instead. You have to load those values from the .env file into your environment.

[torn sphinx]

i have another variable setted up too, and it reads it without any issues

[fading patrol]

I could be off base but this looks relevant: https://bennett4.medium.com/windows-alternative-to-source-env-for-setting-environment-variables-606be2a6d3e1

[torn sphinx]

wait, i think i got the issue

my .env file wasnt in the same dir like .py file.
Solution:

from dotenv import load_dotenv
from pathlib import Path


load_dotenv(dotenv_path=Path('./.env'))

[blissful tulip]

is it possible to make a list that groups the # uses when the creator ID is the same?

[fading patrol]

Of course that is possible. If you're asking how you should do it, we'll need more context. If that's a spreadsheet sheet use a pivot table, if it's SQL that's a simple GROUP BY query

[blissful tulip]

I'm using sqlite, so would it be GROUP BY ID_creator;?

[fading patrol]

Yeah maybe something like SELECT ID_creator, SUM(Num_uses) FROM your_table GROUP BY Num_uses I guess, depending on what exactly you are trying to do

[blissful tulip]

I want to add the number of uses for each ID_creator, and when ID creator is repeated, add it respectively and make a top of the ID creator with more num uses

[torn sphinx]

Bump 3

[coarse locust]

hello, is this normalized to 3rd form already?

[fading patrol]

In other words if id_creator doesn't exist insert a new record and if it does then increment num_uses? I would implement that logic in a Python function

[fading patrol]

Hard to say without any context but my guess is no. For example, why are crust and crust type on two separate tables?

[wraith nymph]

error:

Traceback (most recent call last):
  File "C:\Users\LENOVO\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:/Users/LENOVO/Desktop/Coding -Winson/Python/NotaroBotBackupCode/testing discord bot/BotTesting.py", line 52, in grantbetaaccess
    cursor.execute(sql,val)
ValueError: parameters are of unsupported type

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

Traceback (most recent call last):
  File "C:\Users\LENOVO\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\bot.py", line 939, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\LENOVO\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 863, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Users\LENOVO\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: parameters are of unsupported type

code:

@client.event
async def on_ready():
    db2 = sqlite3.connect("accessbeta.db")
    cursor = db2.cursor()
    cursor.execute(
        """CREATE TABLE IF NOT EXISTS accessbeta(
        guild_id TEXT
        )
        """
    )

    print("TESTING")
@client.command()
async def grantbetaaccess(ctx):
    if ctx.author.id == 466528033638055936:
        db2 = sqlite3.connect("accessbeta.db")
        cursor = db2.cursor()
        cursor.execute("SELECT guild_id FROM accessbeta")
        result = cursor.fetchone()
        if result == None:
            sql = ("INSERT INTO accessbeta(guild_id) VALUES(?)")
            val = (ctx.guild.id)
            cursor.execute(sql,val)
            db2.commit()
            cursor.close()

help

sqlite3

table:

[grizzled wadi]

Try changing it to val = (ctx.guild.id,)

[wraith nymph]

eyyy it workeddd, I missed 1 comma 😂

[grizzled wadi]

Happy to help :)

[cosmic sand]

Is sqlite a good starter database for a noob?

[oak oyster]

yes

[cosmic sand]

thanks

[wraith nymph]

@client.event
async def on_ready():
    db2 = sqlite3.connect("accessbeta.db")
    cursor = db2.cursor()
    cursor.execute(
        """CREATE TABLE IF NOT EXISTS accessbeta(
        guild_id TEXT
        )
        """
    )

    print("TESTING")

@client.command()
async def grantbetaaccess(ctx):
    if ctx.author.id == 466528033638055936:
        db2 = sqlite3.connect("accessbeta.db")
        cursor = db2.cursor()
        cursor.execute("SELECT guild_id FROM accessbeta")
        result = cursor.fetchone()
        if result == None:
            sql = ("INSERT INTO accessbeta(guild_id) VALUES(?)")
            val = (ctx.guild.id, )
            cursor.execute(sql,val)
            db2.commit()
            cursor.close()
            print("successful")

@client.command()
async def checkifbeta(ctx):
    db2 = sqlite3.connect ("accessbeta.db")
    cursor = db2.cursor()
    cursor.execute("SELECT guild_id FROM accessbeta")
    result = cursor.fetchall()
    print(result)
    if ctx.guild.id in result:
        print("Server is in Beta")
    elif ctx.guild.id not in result:
        print("Server aint in beta")
    db2.commit()
    cursor.close()

and this aint working as I thought xd, I dont get any errors
and when I run it I get this:

[('850356901232771082',)]
Server aint in beta

and my table:

wherein it should suppose to say "server in beta" since I did the command in that discord_server

so the problem is just gonna be at the command checkifbeta

[blissful tulip]

No, it will get the num uses for each ID_inv, but when the ID_creator is the same it will add it, example:
Creator ID: 1
Total num of inv:
4
(sum of all your invitations)
Creator ID: 2
Total num of inv:
1
(if there is only one invitation that uses those num uses)

[fleet ibex]

trying to understand what primary keys and foreing keys are for.. i get once the db is created it increases performance.. but why cant it be used to scrub data by only allowing data to be inserted that matches the condition?

[fading patrol]

You can apply conditions before inserting data, that has nothing to do with primary and foreign keys necessarily accept that those keys are what structure a structured database

Primary keys mean you can identify a row or record as the same even if all the data changes. And foreign keys link all the data from one table with all the data in another

[fleet ibex]

somome helped me with doing that, i was just curious.. I guess I had formed an usable method based on what I read in the docs.

like if it does this, it should also do this.. but obviously not :)

[orchid ember]

possible in sqlite that when u select a column it returns as a dict instead of tuple?

like

for i in cursor.execute("SELECT COLUMN FROM TEST"):
 #can i be a dict showing the column name as the key?

it would be like

{
 "COLUMN":[ /* list of values */ ]
}

[proven arrow]

@orchid ember You can use a dict cursor

My bad, dictcursor is from mysql lib. Looking at sqlite docs you have to make your own using the row_factory. https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.row_factory
As an example, https://docs.python.org/3/library/sqlite3.html#sqlite3.Row

[brave bridge]

yep, sqlite3.Row is great

it's a shame it's not the default

[hushed crow]

does anyone have experience with databases in replit? I want to create a new database for each user, but I don't see any info regarding setting multiple users up.

Some more info regarding the question is that I'm making a bot that is supposed to store several data values for each user from a discord server. I got told that I should add more databases as having them all access the same database is a bad idea.

[vale heart]

can I embed other sqlite objects into another sqlite object?

So, for example, I have an sqlite table with several properties

and one of those properties is actually another sqlite data object

I am using flask sql

[west furnace]

uhh, don't think so
you'd probably have a key that refers to another table

[vale heart]

yeah sorry, that's actually what I wanted to say but couldn't find the words. I am a newbie to sql

[vestal rain]

test

[hearty fox]

hey..why is exposing mysql server to the internet issue when u need a password to connect to the server..doesnt that make it safe ?

[fading patrol]

You can totally do that, but you want to be aware of the risks and manage them. Do you want script kiddies all around the world hammering you database with login attempts trying to brute force guess your password? Don't expose yourself to more risk then necessary, make sure your login is complex, etc

[hearty fox]

i see

thats the issue

is there free database i can use ?

i am using google sheets api right now

[digital bronze]

How can I force the sqlite3 module (or really aiosqlite) to use the newer sqlite3 version that I installed from source on my system?

[static zealot]

Hi, I see below error while inserting a row into sql via python

pyodbc.ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near ','. (102) (SQLExecDirectW)")

Doubly checked if either missed or added an extra , but everything looks good from my end.
BTW, same piece of code was able to add earlier 60+ rows

[fading patrol]

You mentioned MySQL, that's free. If you're looking for something cloud hosted and ready to go, Heroku has a nice free tier Postgres which I like

[fading patrol]

Share your code

[hearty fox]

how many requests per month ?

[pseudo cloud]

Hi all,

Hello all, I have been trying to connect MongoDB database hosted on DigitalOceans using Python, but I have been getting different errors. Can anyone help out with this, please?

I am working in both VSCode and PyCharm

[static zealot]

Strn.encode('utf8')
Resolved the issue

Pyodbc was failing for few special characters

[pseudo cloud]

import pymongo

from pymongo import MongoClient
import certifi
client = pymongo.MongoClient("mongodb+srv://supplysmartdb:[e-mail]/admin?retryWrites=true&w=majority")

db = client.test
db1 = client.sample_analytics
db2 = client.admin
col1 = db1.transactions
print(db, db1, db2, col1)
print(db2)
db = client["sample_analytics"] #get db
collection = db["transactions"] #get collection

db.sample_analytics.find_all
collection.count_documents
print(db, collection)#show

This is my code

[hearty fox]

can anyone recommand me a databse ?
i need a database as exchange point of data between my discord and several users . dont need much storage just need high request/s

[ripe gust]

Could someone help me, I am new to python and I would like to make a momentary app that makes me a sql query every x minutes and if a data is registered, send an email notifying the entry? Please can someone help me with some information?

[fading patrol]

If you host it yourself or on a VPS you have no limit on requests per month. You can use any database you want, but would recommend Postgres if you expect high volume

[hearty fox]

can i use fastapi + sqlite on vps ?

[proven arrow]

How is the data registered?

[ripe gust]

We have an incident registration system, every time an event is registered I want this solution to notify us by email

[proven arrow]

Then why can’t you send the email at the time of registering the event

[coarse locust]

   A. There are four types of Pizza

                                1. New

                                2. Specialty

                                3. Supreme

                                4. Lovers

 

   B. Each type can have sub-categories,

                                i.e. SPECIALTY=>              SUPER SUPREME

                                                                            MEAT LOVERS

                                                                            CHICKEN SUPREME

   C. Each Sub-category has Size & Crust

        i.e. Size => LARGE

                           REGULAR

 

             Crust=> Pan Pizza

                           Ham Stuffed Crust Pizza

                           Ultimate Cheesy Bite

                           Etc…..

    D. Each selection of subcategory will determine how much is to be added to the base price.

Like these are the descriptions given. And wdym by the base price? Is it the type or a default one?

[ripe gust]

is what I want to do just that

[fading patrol]

Yes absolutely, but you don't want SQLite if you're expecting high volume

[proven arrow]

So do it? Why do you then need to run a query every x minutes to check if it was registered? Just send it at the time of registration initially.

[ripe gust]

the truth is that as I indicated above I am new to python and I don't really know how to start doing this solution 😦

[proven arrow]

Your question is more related to “how do I send an email”. If you are using any frameworks you might want to take a look at what the framework provides first for sending emails and then look elsewhere if it doesn’t.

[hearty fox]

is $5/m vps enough

[fading patrol]

Even if you told me the specs I wouldn't know, you'll probably need to do some testing

[hearty fox]

ohhk thanks

[hushed crow]

I'm setting up some databases for a discord bot meant to store data from each user. I'm currently using replit, but should I switch to another library? I can't find any documentation about multiple databases on their website.

[west furnace]

replit is a database?

[hushed crow]

Well more so I'm importing a database from replit. To be honest I'm trying to make use of their ingrained database options but I don't see how I can set it up multiple times the way they have it explained. So it might just be that I'm trying to use it in an unintended way

[west furnace]

is there any particular reason you can't simply use shelve?

[hushed crow]

No it was mostly for convenience sake as I'm using their website as my code editor atm

[west furnace]

and it shows the db in GUI?

[hushed crow]

Yes

[west furnace]

I see

that's convenient

other DBs can do that too, however it'd require you to download the db files

seems like a small cost tbh

if that means you get a properly documented db

[hushed crow]

Yeah, it was convinient but not for my purpose, so I'll try another approach instead

[desert idol]

does sqlite work in replit

[oak oyster]

yes

im not sure actually as a person that uses replit

[fading patrol]

Not very well apparently https://replit.com/talk/ask/sqlite3/6599

[rain pike]

how do I get the first ids of the maximum values in the rating column?

[fleet ibex]

im sure i seen the docs have a max/min/avg you can add to the select statement

[pseudo notch]

I'm not completely across full data pipelines and need advice if this is the right ETL structure.

I'm learning to scrape websites. I'm going to store the scrapes into a NoSQL DB like MongoDB. I'm going to store the MongoDB instance in the cloud. I'm then going to feed it into a NLP model when I'm learning how to do all of that with a large enough dataset from the many many scrapings that I will do. I then want to feed that into a BI system like PowerBI or Tableau as my Uni course seem to want to use that quite a bit at the moment.

Everything that I have done this far has been done locally. But I don't think that it is helping me to learn how to scale this up if I want to get a job in it in the future.

1.) How do you run analysis against data in the cloud? Do you have to pull it down, or, like I would imagine, can you set up the Python script to run in the cloud to utilize cloud processing to really crunch the data?
2.) How do you connect the results of this database crunching, using Python and NLP, into a Dashboard BI like Tableau or PowerBI?

I have found a LOT of material on how to do all of this locally. But I cannot quite seem to find any material or resources that cover the full pipeline and how you would go from raw data to fully digested reports of NLP analysis.

[tropic fable]

Do you guys know the best library for SQLAlchemy migrations?

[shadow lance]

Has anyone here written a DB engine before? Haven't found that many projects in Python so I decided to start writing one tonight https://github.com/Rhysyrhysrhys/python-db

I want to make a transactional store that supports any object as key or value, a bit like levelsdb. Purely for learning purposes, not for any serious production use

Share your DB engine projects if you've got one! I'm currently scratching my head on how to implement transaction and state properly, then I'm going to work on changing the underlying transaction log and state to write to files

[fading patrol]

I think Alembic is the standard. Only ony I've used in any case.

[cosmic sand]

Does mongo have the ability to erase at a set time automatically?

Like on redis

So I push a value, then it erases after 5 seconds for example

[velvet ridge]

is this valid way to compare 2 values? ```py
"DELETE FROM reminders WHERE ? > expire and ? == user"

[harsh pulsar]

assuming ? is the correct placeholder for your database library, that looks correct

[velvet ridge]

Good to know, thx!

[harsh pulsar]

one thing i just noticed: sql usually uses = and not ==

[velvet ridge]

oh..

Thanks alot! it's working (I had just one missing db.commit())

[velvet ridge]

I'm thinking of changing the database from SQLite to smth server based, I have considered MondoDB but it was noSQL database which sounds a bit weird. What database would you suggest for a small discord bot? I'm looking for server based one, and SQL db would be nice. I would consider using noSQL database for simple tasks that just happens to need a server based DB, but I don't really know what that even means properly I have heard smth about json syntax

[vast fox]

mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

hi can anyone help me with this error

nevermind lads i fixed it

😁

[carmine anvil]

How do you typically handle datetime with sqlite? store as integer and then convert later?

[pure sleet]

store as datetime

sqlite has a datetime type

[carmine anvil]

ah ok

[pure sleet]

eh. nvm. apparently it doesn't

thought it did

[carmine anvil]

lolol

[carmine anvil]

different websites giving me different info

trying to set up a simple test and im running into a weird error

[fading patrol]

Spinning up a free PostgresDB on Heroku is a pretty easy option. It's true that if your app is currently designed with SQLite, you want to stick with SQL. Postgres isn't that different from SQLite, just more powerful

[pure sleet]

what error

[carmine anvil]

fixed it and datetime does seem to be working

sqlite should throw errors if u isnert an incompatible type into a column right?

[storm mauve]

I am not so sure about that... I might just have used the wrong data type, but still```pycon

con.execute("CREATE TABLE bar (name INT);")
<sqlite3.Cursor object at 0x000001786F651500>
con.execute('insert into bar values ('abc')')
<sqlite3.Cursor object at 0x000001786F6301F0>
for row in con.execute('SELECT * FROM bar').fetchall():
print(row)

('abc',)

[carmine anvil]

huh, good to know i guess

T_Number odometer service_date
0 t-1 123 2007-01-01 10:00:00
1 t-2 12 theh

inserted that into table

ValueError: Couldn't parse datetime string: 'thehe'

datetime is fine

[simple grove]

why is there a theh in the service_date column? 🤔

thinking it should be at least vaguely datetime-like ?

[elder elk]

please answer this question https://stackoverflow.com/q/70458483/17549908

[velvet ridge]

afaik postgres is like a full database compared to SQLite, is postgres easy to switch into and is it taking a lot of space?

[stark sparrow]

I'm makin an economy bot using MongoDB (PyMongo). My bot is taking too much time to get data from the database whereas other economy bots which use PyMongo are much faster. Any tips to increase the speed?

[velvet ridge]

Is it async package?

[coral yarrow]

@stark sparrow I'd reccomend motor, it's an async version of PyMongo.

[carmine anvil]

it was just to test if sqlite would throw an error with a type mismatch

[velvet ridge]

when inserting a datetime object like 2021-12-23 13:54:29.365765 database, and I want to compare it with another datetime object later. In what format I should store it in at?

Like STR or INTEGER or smth else? I'm using SQLite db

[raw saffron]

you can construct datetime objects from an iso date string or from an integer of unix milliseconds

[jagged frigate]

Is there a python library to work with asynchronous MySQL?

[grizzled wadi]

https://github.com/RobertCraigie/prisma-client-py works with MySQL and async

[jagged frigate]

Thx

[grizzled wadi]

Let me know if you have any questions! it does work a bit differently than other Python ORMs

[jagged frigate]

Alright thx for ur help!

[grizzled wadi]

happy to help

[fading patrol]

If you have a basic grasp of SQLite already, I don't think you'll have a lot of trouble moving to Postgres. The main difference is deployment but Heroku makes that part pretty easy. How much space depends on your data, I think free tier Heroku allows 250mb so unless your SQLite is approaching that I wouldn't worry

[stark sparrow]

is it faster?

[velvet ridge]

Hm what is iso date string?

[velvet ridge]

Great to know!

[grim vault]

It will not: https://www.sqlite.org/flextypegood.html

SQLite provides developers with the freedom to store content in any desired format, regardless of the declared datatype of the column. Some people find this feature troublesome. Some developers are shocked to discover that it is possible to insert text into a column marked INTEGER.

[velvet ridge]

how am I supposed to store datetime objects to db ;-; I was thinking of unix timestamp, but I can't compare them after in this way ```py
("SELECT guild_id, user_id, channel_id, message_id FROM reminders where ? > expire", (current_time_in_unix,))

[stark sparrow]

Wait it's even slower than PyMongo

[velvet ridge]

Then there is smth wrong in your code I guess

[fading patrol]

https://tableplus.com/blog/2018/07/sqlite-how-to-use-datetime-value.html

[velvet ridge]

Thank you 😄

[fading patrol]

Another good reason to try Postgres, it does handle various time and date formats ;)

[velvet ridge]

Would require a bit of rewrite which takes a while

[velvet ridge]

The first method was using a string, but how do I compare string with > when using WHERE

[rugged hornet]

Mongodb is pretty much good for discord bots unless you data has relationship to some other data.

[fading patrol]

Looks like you need to convert with datetime(), I haven't done it myself though

[rugged hornet]

It's better to use Redis where performance is the bottleneck in relational database.

[velvet ridge]

storing datetime with SQLite is complicated :/

[fading patrol]

I think the idea is that you store it as text and just read it with datetime ()

[velvet ridge]

One of my bots commands store the datetime and is able to use > for comparing but for other command it does not work

I'm currently debugging and trying to find the problem from somewhere, and thx alot!

[stark sparrow]

is reddis an online databse?

[rugged hornet]

Nope. A regular database.

[stark sparrow]

i need an online one

like mongo

but faster

[rugged hornet]

Redis is way more faster than mongodb.

Redis is popular for database caching.

[stark sparrow]

Can it be used without downloading? Like MongoDB?

[brazen charm]

if you mean have it hosted as a service by some provider then yes

but Redis is considerably different in how it behaves and what it's used for compared to mongo or things like Postgres

redis is a Key value store, it's handling largely just extends to what you could do with a dictionary

i.e no relations, etc...

[bitter osprey]

It's used as an in memory cache,

[bitter osprey]

redis is also use for event messaging for distributed tasks like available in celery https://docs.celeryproject.org/en/stable/getting-started/introduction.html

[coral yarrow]

It shouldn't be. Probably a problem with your code, your database, the server you're hosting your bot on, or any combination of the three.

I'm using motor on a mid-tier VPS, and the find_one, insert_one, and update_one calls are essentially instantaneous

And my bot is in 31,000 Discord servers so I know there's no scaling issue

[rugged hornet]

!pypi motor

[delicate field]

motor v2.5.1

Non-blocking MongoDB driver for Tornado or asyncio

[rugged hornet]

Worth checking it..

Using tornado or may be fastapi would be great to use with it.

[coral yarrow]

Works great with disnake, which is a discord library modeled after fastapi!

[rugged hornet]

I don't which fork would be better to use, disnake, dislash, pycord, discord-interactions...and much more.

[spark spear]

I finally got a db set up to store my data. Do you guys know of any libraries that can easily take db calls and turn them into a graph like a pie chart or bar graph?

I tried this once before with matplotlib and did it by hand and it was just barely usable but not very flexible.

[coral yarrow]

Disnake is the most promising and most well written

And has support from other Discord libraries like Statcord for logging

Pycord is discontinued and discord-interactions is messy in comparison

[stark sparrow]

Really? My one just takes soo much time to fetch and append data

[lost flint]

hi guys, can someone suggest me how to do the singleton model in sqlalchemy, thanks

[coral yarrow]

I think that might be a problem with the server you're hosting your bot on :/

Or a problem with your MongoDB setup

make sure you're starting mongodb with mongod

[earnest kayak]

What does the commit() method do in SQLite

Also, if I wanted to make a verification system in my discord bot, should I use SQLite or MongoDB?

[harsh pulsar]

it commits any open transaction

[harsh pulsar]

there is very little reason to use mongodb in most applications. also mongodb requires a separate server whereas sqlite is just a file and a library

sqlite is almost always a good safe choice for a small scale personal project

[wind rivet]

class Profile(models.Model):
      id = models.AutoField()
      user = models.OneToOneField(User)
class ProfileSerializer(serializer.Serializers):
      class Meta:
          model = Profile
          fields = '__all__'
class UserSerializer(serializer.Serializers):
      profile =serializer.SerializerModelField(read_only= True)

      class Meta:
           model = User
           fields = '__all__'

      def get_profile(self,object):
          profile = ProfileSerializer(object.profile,many=False)
          return profile.data

``` Not working why?

[fading patrol]

Tough to say based on an incomplete code snippet, no error message or stack trace. Where are these serializes declared and what's "not working"

[wind rivet]

the thing is that

i have a database where Settings is One to One field with Profile and Profile is One to One Field with User

but when serialize settings from profile i do SettingsSerializer(object.settings,many=False), it works

and when it is from User i serializer Profile ProfileSerializer(object.profile,many=False), it does not work

[gleaming herald]

lol

print('hello world')

print('hello world')

select * from some.database where x=y

[stark sparrow]

Is it good to use Google Fir base to make an economy bot database?

Google says it is

[gleaming herald]

sorry guys ignore my comments, i was showing a friend some commands

[earnest kayak]

Thanks

[wind rivet]

can we pass down kwargs through .save() method?

[coral yarrow]

I don't really reccomend firebase

Tbf I think serverless solutions in general are a bad idea

But if you have a very small budget, go with either mongodb's online hosting or firebase

[proven arrow]

Care to explain why? They have their perfectly valid use cases like in mobile applications. Things start to become a problem or a bad idea when you try to use something that is not right for the job.

[stark sparrow]

yeah i ama go with firebase

no other choice

fast and easy

and as it is made by google it is secured too

[mighty cave]

For a notice:

Just because this company created this does not mean it's secure. It also depends on how securely you decide to use its service.

[chrome delta]

why?

[coral yarrow]

1. Maybe in something like a mobile app, not a Discord bot
2. This is more in the principle of the matter, but you should always have control over your hosting & database. Google Firebase is especially bad, considering it's...Google. I wouldn't consider anything that Google touches safe or secure.

[frank gorge]

which database is good for python

[proven arrow]

It’s bad because it’s Google? What?

[proven arrow]

Majority of databases are good. Just pick the one that is right to solve the problem your working.

[frank gorge]

what is good with tkinter

also little voice command

[proven arrow]

Less to do with the framework you use. More to do with what you plan to use the database for.

[frank gorge]

for the patient data

for showing details of patient using voice command on desktop as desktop app

[proven arrow]

Please try to explain in more detail, rather than someone having to get the details out of you. This Patient data is what? Just a list of names of people?

[cedar pagoda]

Hey, I recently updated my SQL package and afterwards I got this error when I interact with my database:

  File "Z:\Coding\Discord Bots\CreedBot\lib\db\db.py", line 41, in field
    cur.execute(command, tuple(values))
sqlite3.OperationalError: disk I/O error

db.py: https://www.toptal.com/developers/hastebin/borebiqeze.py

Has somebody an idea why this happens?

[torn sphinx]

how can i fix my database's auto increment with mysqli ive tried ALTER TABLE users AUTO_INCREMENT = 24; but it wont do anything

[cosmic aspen]

i am trying to sort based on the tag column but it is a list. what could be the best approach for this??

[fading patrol]

What have you tried and what is the problem? Is that a Pandas df or what?

[cosmic aspen]

yes. it is pandas df. i tried df.apply() to get the data based on condition for example: if 'abstract' in x: return x

[fading patrol]

Have you tried df.sort_values()?

I haven't used Pandas in a while but I would expect that to be workable

[cosmic aspen]

no. it does not work TypeError: unhashable type: 'list'

[fading patrol]

You could convert the list to string I think. Here's a hint of you get stuck share your code. https://stackoverflow.com/questions/39900061/sort-lists-in-a-pandas-dataframe-column/39901889

[cosmic aspen]

here is csv file i am working at

[burnt cloak]

Does this server support for mysql, if yes so can i soft the results in mysql by limited, like with a column that only have numbers and i want to soft it limited in 1 - 10?

[cosmic seal]

Can one do something like this with with MySQL connector?

mycursor=mydb.cursor()

mycursor.execute(query1, where1)
mycursor.execute(query2, where2)
mycursor.execute(query3, where3)

mycursor.close

Or do I need to close and reopen the cursor for each query?

[harsh pulsar]

one cursor per query

[tight drift]

Command raised an exception: ServerSelectionTimeoutError: localhost:27017: [Errno 111] Connection refused, Timeout: 30s, Topology Description: <TopologyDescription id: 61c6d3224a33439c539198ad, topology_type: Single, servers: [<ServerDescription ('localhost', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('localhost:27017: [Errno 111] Connection refused')>]>
I want help in this error

[split ore]

did you start the mongod server?

[tight drift]

yes

same server is for other cogs

and they work

@split ore

[stark sparrow]

Hello, I am getting "error": "could not prase auth token" while getting data from my firebase database using pyrebase. My db URL, etc are just fine as it can add data in the database. Any help?

here is my code

    @commands.command()
    async def bal(self, ctx, user: discord.Member = None):
        if user == None:
            user = ctx.author

        bank_amt = database.child("users").child(ctx.author.id).get("bank_amt")
        wallet_amt = database.child("users").child(ctx.author.id).get("wallet_amt")
        bank_space = database.child("users").child(ctx.author.id).get("bank_space")

        embed = discord.Embed(title = f"{user.name}'s balance", colour = discord.Color.red(),
                                    description = f"**Wallet**: {wallet_amt}\n**Bank**: {bank_amt}/{bank_space} `({(bank_amt/bank_space) * 100}%)`",
                                    timestamp = ctx.message.created_at)
        embed.set_footer(icon_url = ctx.author.avatar_url)
        await ctx.reply(embed = embed)

  "error" : "Could not parse auth token."
}

full error

ok nvm i fixed it

[orchid ember]

possible to name a row in sqlite3?

[orchid ember]

what database is this