databases

[civic crypt]

ill try that tho

it might work

[alpine orchid]

everything accessing the connection has to stay under with

[civic crypt]

oh

ill just keep it how it is now then

i would have everything indented like 4 times

its already 2 with my code

[torn sphinx]

I have an error

[brave bridge]

What's the error?

Also, what is Data?

[torn sphinx]

yeah

I followed a tutorial

but the problem is

I saw " Data "

and I tried to search what's data and else

but like I made a command to activate a module but I need to make a database to make it work

I made the database

but

I need to make my SQL connect

to the command

but I don't know how to make it

here's my table for it

@brave bridge

so anybody can help me ?

[torn sphinx]

which sql engine you're using?

[torn sphinx]

SQLite

💀never used it sowwy..

but wait ima search it up on gogole lmfao

[torn sphinx]

import sqlite3

try:
    sqliteConnection = sqlite3.connect('SQLite_Python.db')
    cursor = sqliteConnection.cursor()
    print("Successfully Connected to SQLite")

except sqlite3.Error as error:
    print("Error while connecting to sqlite", error)

[torn sphinx]

how I use this

...

bro it's py code

and that thing inside sqlite3.connect should be the name of your sqlite db

[torn sphinx]

so I put it on top @torn sphinx ?

like on first lines ?

after import things

[torn sphinx]

depends on how u want to do..

[torn sphinx]

oh wait no I put it when the bot connects ?

wait..

idk if sqlite should be used for bto development

bot*

[nova cove]

It can be

But it doesn’t scale well and doesn’t support high levels of concurrency

So for larger bots I would use Postgres or some other sql flavor

You can create the db connection in an overridden start or connect method

make sure to do the connection procedurally though because using a context manager will close the db connection after the queries you specify to be executed on connection to the db finish

[frank cloak]

so I am making a psql table

I have a couple of questions though

CREATE TABLE TAGS(id BIGSERIAL, 
name text,
user_id bigint,
guild_id bigint,
points_to bigint REFERENCES TAGS(id) ON DELETE CASCADE,
content text,
created_at timestamp with timezone,
global bool default false,
uses bigint not null default 0,
UNIQUE (name, guild_id),
PRIMARY KEY (id))

the only issues I can think about right now about doing this, is dm tag conflicts and global tag conflicts

if I can somehow prevent them here, then I am good

[half forum]

{
    "947688133200338985": [
        "B777",
        "B666"
    ]
}```
sorry I was doing it from my phone

[ocean stream]

a question: I'm working with a MySQL database. I'm thinking of implementing a get_or_create helper function, analogous to what Django provides with its ORM - for a given id, it either gets an existing entry or creates a new one. Has anyone implemented something similar before with MySQL, and any pros/cons with that approach?

[left meteor]

I Am stuck with the having count command. How does that help to figure out the nth highest salary ? Is there any easier way to do this ? Since I am new in sql , I feel I am not understanding the solution

[lunar pumice]

Does anyone know how to get the results of "EXPLAIN ANALYZE INSERT INTO" with psycopg3? I found examples of "EXPLAIN ANALYZE SELECT" but not with "EXPLAIN ANALIZE INSERT INTO".

[carmine ferry]

can we add 2 or more cities and 2 or more postal code in a single user date

like this

[clear stirrup]

I ended up migrating a small discord bot from tortoise orm to prisma last night and some of today

I'm really used to the active record method of interacting with the db though so it was a bit weird to change my thinking. But the autocompletion did work well.

[grizzled wadi]

Ooooooh exciting! How did you find the migration process?

[grizzled wadi]

Glad to hear that worked for you! Yeah most other ORMs go by active record so I'm considering adding support for it as well although the only concern I have is making it even more confusing if there's multiple methods for doing the same thing

[clear stirrup]

It was fairly painless. The schema being written in its own schema file is pretty cool. I had a mind blown moment when I saw the docstrings for each model's methods referencing my specific schema

[grizzled wadi]

Yeah the beauty of codegen :)

I'm glad you liked that, I haven't had anyone mention it since adding them

[clear stirrup]

I personally would feel more comfortable with having the active record option but I'll spend some time doing it this way and see how it feels.

[grizzled wadi]

Yeah I think lots of other people would too, it does make it much easier for update queries

[clear stirrup]

also having a queryset manager and saved queries on the models is something nice that I'm missing

now I just have a file with helper functions

but they're not well organized

is there a good way to go about saving queries and modifying them?

[grizzled wadi]

Yeah I realised that before but it's a difficult API to design in a type safe manner

[grizzled wadi]

As the queries are just standard dictionaries you can pass the dictionary around although that isn't ideal

[clear stirrup]

that's true...

[grizzled wadi]

Something that might help is that you could subclass the models and add helper methods there

[clear stirrup]

yeah, I was actually doing that at first

I'm not sure why I stopped

[grizzled wadi]

If you still want autocomplete you'll have to annotate variables to types like prisma.types.UserCreateInput which is a bit annoying but I'm not sure what a better solution would look like

[clear stirrup]

I was struggling with that a little bit. I'm not well versed in python's type hinting and still need to figure out how to tell python that a dictionary is a certain shape.

I kept trying to type hint it things with prisma.types.something and then also unpacking dictionaries

[rough viper]

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost:3306' (10061)

[rough viper]

what to do now?

[clear stirrup]

into other dictionaries. pyright wasn't happy about it

[grizzled wadi]

Yeah that is definitely a barrier for entry for some people, it is difficult to learn

[clear stirrup]

all good though, I'm gonna keep using it. Nice library!

[grizzled wadi]

Yeah TypedDicts are quite strict and are more difficult to work with than normal dictionaries :/

[grizzled wadi]

Thank you! I really appreciate it :)

If you have any other questions about types I'm more than happy to help :)

[rough viper]

help me guys too!

Robert ^

[clear stirrup]

I'm sure I'll ping you again

[rough viper]

b r i ^

[grizzled wadi]

Is your MySQL server running?

[rough viper]

no

how can i start that?

[harsh pulsar]

i didn't realize how much of a full orm prisma-client-python was. makes me more excited to try it, i just haven't had a need for an orm in a personal project in a long time

[grizzled wadi]

I'm not sure, I'm not familiar with MySQL on windows

[rough viper]

since i unchecked the option in installation like 'run server at startup'

[rough viper]

can you pls find something similar on google, since i can't find anything relevant

[grizzled wadi]

Yeah even though it's new it's much more fully fledged than other new ORMs as the core functionality has already been implemented by Prisma

[harsh pulsar]

yeah that's really cool

have you talked to the prisma core devs at all? their website doesn't let on at all that prisma could be used cross-language

and there aren't (that i'm aware of) prisma clients/parsers in other languages beyond python and the original

[grizzled wadi]

I suggest reinstalling and setting that option, that's what I've found on google

[grizzled wadi]

Yeah I've talked a lot with the Prisma core Devs, I have a direct line with them so I can contact them if I need anything

[grizzled wadi]

Yeah they're focusing on their TS client at the moment

[harsh pulsar]

ah nice, so at least they aren't opposed to what you're doing

[rough viper]

but isn't there any option for manual startup since i not always uses this

[grizzled wadi]

They had a Go client but that was deprecated because they weren't receiving enough usage

[harsh pulsar]

right i think you said that / i saw it somewhere

[grizzled wadi]

Yeah definitely not, they are very generously sponsoring me as well

[harsh pulsar]

i was going to say that sounds like something a Go dev could adopt for fame & glory

[clear stirrup]

https://dev.mysql.com/doc/refman/8.0/en/windows-start-command-line.html

figure out where you installed it

find mysqld

[harsh pulsar]

how hard is the schema language to parse? might be an interesting project to write a parser for it in some other language

[grizzled wadi]

If you're just wanting to parse the schema, they've written their parser in rust so all you'd really need is FFI bindings

[harsh pulsar]

ahhh that is excellent news

so you can use that to do static code gen for pretty much any language already

[grizzled wadi]

Sort of, their whole generator concept is written in Node though unfortunately

But you can generate code for any language

[harsh pulsar]

yeah, that's fine for a first pass. it's the same approach that tree sitter took (much to some people's chagrin)

[grizzled wadi]

Yeah their whole CLI is written in Node unfortunately

I don't know if they have any plans to switch to Rust but it'd be a massive change so I'm doubtful

Tree sitter rings a bell but I can't remember, what is it?

[harsh pulsar]

it's a parser generator that uses some kind of super efficient incremental parsing algorithm

you specify the grammar in javascript (or compile some other language to javascript if you want), then the tool generates a single c file with no dependencies other than tree sitter itself

[grizzled wadi]

Ooooooh okay that sounds pretty cool

[clear stirrup]

I had terribly slow syntax highlighting in vue files before I switched to treesitter in neovim

idk how it works

[harsh pulsar]

neovim has been starting to use it for syntax highlighting, name lookups, etc.

https://tree-sitter.github.io/tree-sitter/

it also has its own query language

[clear stirrup]

just thank god

[harsh pulsar]

yeah, it's been a really big deal

it's equivalent to LSP in terms of how useful it is, it solves another NxM problem

https://tree-sitter.github.io/tree-sitter/playground here you can mess around with it and see what syntax trees it generates.

the docs aren't great, but here is their attempt to describe the query syntax https://tree-sitter.github.io/tree-sitter/using-parsers#pattern-matching-with-queries

anyway this is off-topic for #databases and i hope your project continues to be useful and successful

[grizzled wadi]

Thank you :)

[strong gate]

hi guys, would need some help on sql query and i am totally new on that, really appreciate if you can give some advise.🙏

I would like to verify a table 'account trade fee' columns field are correctly recorded based on another table 'exchange trade'.

Since I am really new to coding, would like to seek some help from yours.

Give background is now there is table account trade fee, as you see below there are column name 'fee_currency','maker_fee' and 'taker_fee'

account_trade_fee
"account_uuid","fee_currency","maker_fee","taker_fee"
c3f94bdf-8854-49d2-833d-0690eb821aa1,BTC,0.0002,0.0002
c3f94bdf-8854-49d2-833d-0690eb821aa1,USDT,0.02,0.02
c3f94bdf-8854-49d2-833d-0690eb821aa2,CRO,0.0002,0.0002
c3f94bdf-8854-49d2-833d-0690eb821aa2,USDT,0.02,0.02.

`exchange_trade
"account_uuid","buy_fee_scale","buy_fee_value","buy_fee_currency","sell_fee_scale","sell_fee_value","sell_fee_currency","taker_side
c3f94bdf-8854-49d2-833d-0690eb821aa1,10,400000,BTC,8,400000,USDT,SELL
c3f94bdf-8854-49d2-833d-0690eb821aa1,10,400000,BTC,8,400000,USDT,SELL
c3f94bdf-8854-49d2-833d-0690eb821aa1,10,400000,BTC,8,400000,USDT,SELL
c3f94bdf-8854-49d2-833d-0690eb821aa1,10,400000,BTC,8,400000,USDT,SELL
c3f94bdf-8854-49d2-833d-0690eb821aa1,10,400000,BTC,8,400000,USDT,SELL
c3f94bdf-8854-49d2-833d-0690eb821aa1,10,400000,BTC,8,400000,USDT,BUY
c3f94bdf-8854-49d2-833d-0690eb821aa1,10,400000,BTC,8,400000,USDT,BUY
c3f94bdf-8854-49d2-833d-0690eb821aa1,10,400000,BTC,8,400000,USDT,BUY
c3f94bdf-8854-49d2-833d-0690eb821aa1,10,400000,BTC,8,400000,USDT,BUY
c3f94bdf-8854-49d2-833d-0690eb821aa1,10,400000,BTC,8,400000,USDT,BUY

There is equation of calculating the maker fee and taker fee from exchange trade table. If from below column 'taker_side':

if taker_side = 'BUY' refers to a maker fee, BTC would be the buy_fee_currency. which the formula would be buy_fee_value/pow(10,buy_fee_scale) = 0.00004. Since there is total five trade from BTC_USDT pair are BUY side. So the final maker fee would be 0.00004 * 5 = 0.0002 for BTC currency, which shows the same result from account trade fee above.

As BTC would be the buy_fee_currency, then USDT would be the sell_fee_curreny so then it will calculated as taker_fee and formula is like sell_fee_value/pow(10,sell_fee_scale) = 0.004 . Since there are total five trade are "SELL" side, times 5 would equal to 0.004 * 5 = 0.02

vice versa to the case if taker_side is "SELL" BTC is sell_fee_currency. USDT is buy_fee_currency.

I could only manage to write some sql query like below , but it couldn't generate the expected sum as I don't know how to control the calculation with regard to maker side or buyer side.In order to verify if the logic apply correctly, I would like to write a query to check if the sum of each currency is correctly display in maker_fee and taker_fee. Could any one kindly advise how to do so?

select sum((buy_fee_value/power(10,buy_fee_scale))) maker_fee, sum((sell_fee_value/power(10,sell_fee_scale))) taker_fee, taker_side,buy_fee_currency, sell_fee_currency ,account_uuid from exchange_trade et group by buy_fee_currency, sell_fee_currency,taker_side ,account_uuid order by account_uuid

"maker_fee","taker_fee","taker_side","buy_fee_currency","sell_fee_currency","account_uuid"
0.0002,0.02,BUY,BTC,USDT,c3f94bdf-8854-49d2-833d-0690eb821aa1
0.0002,0.02,SELL,BTC,USDT,c3f94bdf-8854-49d2-833d-0690eb821aa1
0.0002,0.02,BUY,CRO,USDT,c3f94bdf-8854-49d2-833d-0690eb821aa2
0.0002,0.02,SELL,CRO,USDT,c3f94bdf-8854-49d2-833d-0690eb821aa2

[small narwhal]

How can I geo Index more than one field in mongodb which are in a nested array???

  "firstName": "Lillian",
  "polyclinic": [
    {
      "polyclinicName": "lo",
      "location": {
        "longitude": 56.12623,
        "latitude": 0.88207
      }
    },
    {
      "polyclinicName": "jantascuz",
      "location": {
        "longitude": 150.48677,
        "latitude": -77.29458
      } 
    }
  ],
  "hospital": [
    {
      "hospitalName": "duh",
      "location": {
        "longitude": -91.90766,
        "latitude": -5.96207
      }
    },
    {
      "hospitalName": "na",
      "location": {
        "longitude": -170.75445,
        "latitude": -82.66879
      }
    }
  ]
}``` this is the mongodb schema

this query is not working ```db.docgeo.find(
{
hospital:
{ $near:
{
$geometry: {type: "Point", coordinates: [-8.9667, 40.78 ] },

        $maxDistance: 50000
      }
   }

}
)```

btw it is cosmos api for mongo db

[violet grove]

hi, so i tried to change password into passwd but nothing changes, I don't know what i am supposed to do
here is my code

import mysql.connector

mydb = mysql.connector.connect(
    host="z84xh.myd.infomaniak.com",
    user="username",
    passwd="password",
    database="mydb"
)

print(mydb)

here is my error

Traceback (most recent call last):
  File "C:\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 236, in _open_connection     
    self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: Can't connect to MySQL server on 'z84xh.myd.infomaniak.com:3306' (10060)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "g:\Mon Drive\prog\parser\test.py", line 3, in <module>
    mydb = mysql.connector.connect(
  File "C:\Python310\lib\site-packages\mysql\connector\__init__.py", line 272, in connect
    return CMySQLConnection(*args, **kwargs)
  File "C:\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 85, in __init__
    self.connect(**kwargs)
  File "C:\Python310\lib\site-packages\mysql\connector\abstracts.py", line 1028, in connect
    self._open_connection()
  File "C:\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 241, in _open_connection
    raise errors.get_mysql_exception(msg=exc.msg, errno=exc.errno,
mysql.connector.errors.DatabaseError: 2003 (HY000): Can't connect to MySQL server on 'z84xh.myd.infomaniak.com:3306' (10060)

I repeat what i said if someone read that, one of my friend success to connect with a c# script but he failed with python

[harsh pulsar]

https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html password and passwd should both be valid. do you know if your server uses a nonstandard port? the default is 3306, which is the default port for mysql

and check for typos

@green sorrel see the link i posted above, both password and passwd are valid

personally i don't like mysql-connector for "casual" use. mysqlclient seems more user-friendly

i've used mysql-connector before (years ago) and i remember it was very un-ergonomic. maybe it has better performance for "industrial strength" applications, hard to say

https://pypi.org/project/mysqlclient/

if anything it looks like mysql-connector was slower than mysqldb back in 2015 http://charlesnagy.info/it/python/python-mysqldb-vs-mysql-connector-query-performance

(mysqlclient is a fork of mysqldb)

and since everyone wants to write discord bots nowadays, you should probably use aiomysql anyway if you are writing a discord bot

[violet grove]

no, it's a db for a website, i wan't to automate something haha

i make my discord bot with mongodb, in js

Traceback (most recent call last):
  File "g:\Mon Drive\prog\parser\test.py", line 2, in <module>
    myConnection = MySQLdb.connect( host="z84xh.myd.infomaniak.com", user="*****", passwd="********", db="z84xh_dbSGCTests" )
  File "C:\Python310\lib\site-packages\MySQLdb\__init__.py", line 123, in Connect
    return Connection(*args, **kwargs)
  File "C:\Python310\lib\site-packages\MySQLdb\connections.py", line 185, in __init__
    super().__init__(*args, **kwargs2)
MySQLdb._exceptions.OperationalError: (2002, "Can't connect to server on 'z84xh.myd.infomaniak.com' (10060)")

with mysqlclient

i think it's the db, but i tried many different ports...

[harsh pulsar]

are the username and password correct?

have your friend share their C# code that worked

[violet grove]

yes, I copy and paste them from phpmyadmin

[violet grove]

i search it one moment

// Librairie
using System.Data;
using System.Data.Common;

namespace Test01
{
    /// <summary>
    /// Logique d'interaction pour MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        DbProviderFactory dbPf;
        DbConnection dbConn;
        bool bEtatConn;
        DbCommand dbCmd;
        string sLigne;

        public MainWindow()
        {
            InitializeComponent();
            dbPf = DbProviderFactories.GetFactory("mySQL.Data.MySqlClient");
            dbConn = dbPf.CreateConnection();
            dbConn.ConnectionString = "Server= z84xh.myd.infomaniak.com; DATABASE=db ; UID=username; PASSWORD=pw";

            bEtatConn = true;
            DbDataAdapter dbAdapter = dbPf.CreateDataAdapter();
            DbDataReader drInfos;

             if (dbConn.State != ConnectionState.Open)
             {
                 dbConn.Open();
             }

            // Écrire la commande SQL simple
            dbCmd = dbConn.CreateCommand();
            dbCmd.CommandText = "SELECT * ";
            dbCmd.CommandText += "FROM tblcra; ";

            // Lancer la requête crée précédemment
            drInfos = dbCmd.ExecuteReader();

            // Affichage de la requête dans la DataGrid
            //dtgInfo.ItemsSource = drClients;

            // Afficher dans une ListBox
            while (drInfos.Read())
            {
                sLigne = drInfos["craId"].ToString() + " " + drInfos["craNom"].ToString();
                lstbRes.Items.Add(sLigne);
            }

            dbConn.Close();

        }
    }

here it is

[nova cove]

C# 😍

[violet grove]

i'm not sure about that xd

[nova cove]

I can’t even read the code I’m on phone lmao

[violet grove]

np

[harsh pulsar]

and you are 1000% sure you have the right username and password?

it works in C# but not in python, on the same computer?

i think i remember having problems like this once when i used mysql, but it almost always was due to a typo that i missed

[violet grove]

my friend tried his c# script and a python script on the same computer, juste the c# script worked

[harsh pulsar]

does it have to do with how the mysql server is configured?

Connector/Python supports authentication plugins available as of MySQL 5.6. This includes mysql_clear_password and sha256_password, both of which require an SSL connection. The sha256_password plugin does not work over a non-SSL connection because Connector/Python does not support RSA encryption.

The connect() method supports an auth_plugin argument that can be used to force use of a particular plugin. For example, if the server is configured to use sha256_password by default and you want to connect to an account that authenticates using mysql_native_password, either connect using SSL or specify auth_plugin='mysql_native_password'.

this is getting beyond my level of expertise with mysql though

[violet grove]

there is something special on his c#, there is a space before the host

it's the only difference

but if i put a space on the string of the host in python, it can't found the host

[harsh pulsar]

i'm not sure if that's relevant, it should work without the space i think

[rough viper]

i want to ask that suppose i making this program to connect to mysql but how would it connect to database when i will send and run my program in another pc since it may not contain mysql or root user like me with same username and password. so how can i do this task?

[kindred pagoda]

what to do if the bot does not add discord.py sqlite3 to the database?

[shadow fossil]

Hey guys, I'd like to vizualize my datas, do u have a link to install sqlite3, bc I don't know what is the "right" sqlite3

please mention me

[torn sphinx]

I don't quite understand what you're trying to say, but im guessing you want to access a SQL database from another server?

[fading patrol]

If your DB is remote you need to specify hostname at minumum. https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html

[warped imp]

@bot.command()
@commands.has_any_role(692760082085183519, 940008547993927691, 863878825376743475, 902292952381001779, 863879069253894166, 902292952381001779, 863879304110276668, 940008547993927691)
async def пред(ctx, member: discord.Member = None, *, reason = None):
    print(45)
    cursor.execute("""CREATE TABLE IF NOT EXISTS warning(guild_id BIGINT, user_id BIGINT, warn INT, count INT, moderator_id BIGINT, reasons VARCHAR)""")
    base.commit()
    print(1)
    
    warnings = cursor.execute("SELECT * FROM warning WHERE user_id = ?", (member.id,)).fetchone()
    print("работает")
    

    if member is None:
        await ctx.send("Выберите участника")
        return
        
    if reason is None:
       try:
           cursor.execute('INSERT INTO warning(guild_id, user_id, warn, count, moderator_id, reasons) VALUES(?, ?, ?, ?, ?, ?)', (ctx.guild.id,member.id,0,1,ctx.author.id,'Отсутствует'))
           base.commit()
       except Exception as e:
           print(repr(e))
       print(2222)
       cursor.execute(f'UPDATE warning SET warn = {warnings[2] + 1} WHERE user_id = ? AND guild_id = ?', (member.id, ctx.guild.id))
       base.commit()
       await ctx.send(f"**{ctx.author.name}** Выдал предупреждение # {member} (случай # )")
    else:
       cursor.execute('INSERT INTO warning (guild_id, user_id, warn, count, moderator_id, reasons) VALUES(?, ?, ?, ?, ?, ?)', (ctx.guild.id,member.id,0,1, ctx.author.id, reason))
       base.commit() 
       cursor.execute(f'UPDATE warning SET warn = {warnings[2] + 1} WHERE user_id = ? AND guild_id = ?', (member.id, ctx.guild.id))
       base.commit()
       await ctx.send(f"**{ctx.author.name}** Выдал предупреждение # {member}, причина {reason}. (случай # )")

error:``` OperationalError: no such column: user_id

How to fix?

[harsh pulsar]

!sql-fstring

[delicate field]

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

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

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

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

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

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

[harsh pulsar]

@warped imp ☝️ that applies to SET too. hard to say if that's your problem, but you should at least try to rule it out

note that you should not re-use database cursors

one cursor per query, then the cursor is "dead"

you get unpredictable behavior otherwise

[warped imp]

🤔

[warped narwhal]

Someone knows how to install SQLAlchemy 2.0 in a conda env? I only seem able to get 1.4

[harsh pulsar]

did you try conda-forge?

you can use pip if it's not in either the default or conda-forge channels

actually sqlalchemy is still 1.4 in pypi too

is 2.0 even released yet? looks like it isn't

[warped narwhal]

Yeah tried conda install -c conda-forge/label/prerelease sqlalchemy too which also installs 1.4.32

I was going off this page, I thought you could use it with pip install --pre SQLAlchemy but maybe I misunderstood it

https://docs.sqlalchemy.org/en/20/intro.html#installation-guide

But do I just use the latest 1.4 if I want to implement SQLAlc with 2.0 syntax?

[fluid lava]

Any recommendations on migrations tools? I'm using sqlalchemy but not a fan of Alembic.

[harsh pulsar]

yeah maybe it's just not in conda-forge yet

[harsh pulsar]

i'm not sure, you can check the 2.0 migration guide to see what 1.4 supports

[warped narwhal]

I think this is what I'm looking for

https://docs.sqlalchemy.org/en/14/orm/session_basics.html#querying-2-0-style

"In the new 2.0 style of working, fully available in the 1.4 release"

[errant knoll]

In Postgres is there a way to specify all columns in a table to have the NOT NULL constraint without putting it on every column individually?

[trim lintel]

no, just add it on every column. Does not take that long does it to type few words out 🙂

[errant knoll]

no just thought there might be a dedicated way or something

[grizzled wadi]

You could try using Prisma Migrate

You don't need to use the client or anything, you would just have to write a minimal schema file defining your database connection and then run prisma db pull to get your current database schema in

The downside to this is that you'll then have two sources of truth, the Prisma Schema and your SQLAlchemy models

The solution to that is to have a custom Prisma Generator to create the SQLAlchemy models but that would be a decent amount of work

[remote latch]

def additem(databasename,items):
  cursor.execute("INSERT INTO {databasename} VALUES({items})")```
Anybody know what I'm doing wrong?  trying to set up a sqlite3 function

[nova cove]

you are missing f before the string

but

!sql-fstrings

[delicate field]

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

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

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

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

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

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

[remote latch]

ooooooh

so it'd be

def additem(databasename,items):
  cursor.execute(f"INSERT INTO {databasename} VALUES({items})")```

[nova cove]

i'm not sure this function is a good idea though

[remote latch]

why not?

[nova cove]

execute does it for you. it is redundant. what even is the point of the function?

[remote latch]

so it's easier to do and I can do it in one line since I have to commit it as well

easier to just type "additem" than to go and copy/paste execute

[nova cove]

well i dont think what you have rn will suffice. you might need to make tons of different helper functions for specific use cases

and take into account placeholders and parameters

an extra line for committing doesn't hurt

[remote latch]

yeah I'll have to add more complexity in the future but for now it's all I need

[fallow bluff]

Other than more datatypes, hosted, faster & bigger, are there any more benefits of PostgreSQL when compared to something like SQLite?

[harsh pulsar]

postgresql is also a lot more programmable, with several procedural languages: pl/pgsql, pl/python, etc.

pg has user accounts with detailed access controls, and the ability to define "schemas" which are groupings of tables within a database.

pg has several more index types, which gives you more precise control over performance properties

the sql query language itself in postgres has more features such as LATERAL

[fallow bluff]

ah okay thank you

[oak oyster]

@violet marten Go to https://mongodb.com/ and create an account

[violet marten]

creating mongo acc free?

[oak oyster]

yes

[violet marten]

aight

[violet marten]

do i need to select anything specific ?

[oak oyster]

Not really, just click "build a new application"

[violet marten]

these are ok?

[oak oyster]

yes

[violet marten]

now

[oak oyster]

hm

just click the free plan

[violet marten]

i am from asia near india

[oak oyster]

just enter something

then click Database

don't do that yet

[violet marten]

oh

[violet marten]

created user

[oak oyster]

Do this

[violet marten]

this?

[oak oyster]

Click browse connections

[oak oyster]

yep.

[violet marten]

done

now

[oak oyster]

Add my own data

[violet marten]

collection name?

information ?

[oak oyster]

Anything, I prefer naming it discord or bot

[shrewd crypt]

@oak oyster im so sorry, ive hit a wall in my knowledge even when its the simplest thing.

@client.event
async def on_member_join(member):
    embedVar = discord.Embed(title="HELP", description="tEXT", color=0x619491)
    await client.message.send(embed=embedVar)

which part of this is incorrect? it doesnt seem to be sending to new users

[oak oyster]

await member.send(...)

name it bot (database name)

[violet marten]

done now

[shrewd crypt]

still doesnt seem to be working..

i need it to DM them, i feel like im missing a line of code

oh wow, wrong channel i ujst realised. apologies

[violet marten]

next?

[oak oyster]

Click database access and show me what you see

hm show "Network Access"

[oak oyster]

Click add ip

[oak oyster]

Click allow access from anywhere

[violet marten]

comment ?

[oak oyster]

Click confirm

[oak oyster]

Click database > connect

[oak oyster]

Click "connect your application"

[violet marten]

i think i have to choose python there?

[oak oyster]

yea 3.6+

brb

[violet marten]

ok

[oak oyster]

copy the link, replace <password> with the password u entered earlier

[violet marten]

Then

[oak oyster]

install motor & dnspython

[violet marten]

where

in my bot.py file?

[oak oyster]

!pypi motor

[delicate field]

motor v2.5.1

Non-blocking MongoDB driver for Tornado or asyncio

[oak oyster]

!pypi dnspython

[delicate field]

dnspython v2.2.1

DNS toolkit

[oak oyster]

no, in your shell

[oak oyster]

uhm

just do pip install motor

[violet marten]

done both

[oak oyster]

hm

@violet marten

are you using d.py 2.0?

[violet marten]

wait

[oak oyster]

i mean discord.py

[violet marten]

i forgot

[violet marten]

how can i check?

[oak oyster]

pip show discord.py

[oak oyster]

now in your main file do ```py
from motor.motor_asyncio import AsyncIOMotorClient

bot.mongo = AsyncIOMotorClient(THE_URL_FROM_EARLIER)

[violet marten]

isnt it client.mongo?

[oak oyster]

Done. You can now insert/delete/update stuff inside your db ```py
col = bot.mongo["discord"]["test"]
await col.insert_one(...)

[oak oyster]

ye

just change client to mongo

I mean bot to client

[oak oyster]

Show line 19

[oak oyster]

Make the url a string, and put that below your client variable

[oak oyster]

put that below your client variable

[violet marten]

eh i am confused

[oak oyster]

put the variable

below your client variable

[violet marten]

client.mongo = AsyncIOMotorClient
    str ('mongodb+srv://nizoki:<>@cluster0.zaut6.mongodb.net/myFirstDatabase?retryWrites=true&w=majority)```

i am confused

[oak oyster]

client = ...
client.mongo = ...

[oak oyster]

Dude

[oak oyster]

Define client.mongo below your client variable

[violet marten]

i am new in discord.py 😔

now?

[oak oyster]

No

[oak oyster]

Can you see your client variable bere?

[violet marten]

hm

[violet marten]

like client = discord.Client()

[oak oyster]

yes

[oak oyster]

put client.mongo UNDER that ```py
client = commands.Bot(...)
client.mongo = ...

[oak oyster]

yes

[violet marten]

now

[oak oyster]

don't forget to enter your password in the mongo URL

[violet marten]

how can i sent data into db

like

if i do ?staff name how can i add him in the staff list embed

[oak oyster]

you wanna add the name into the db?

[violet marten]

name and other informations too

like for mc server all there data etc etc

[oak oyster]

https://motor.readthedocs.io/en/stable/tutorial-asyncio.html#inserting-a-document

Read this.

[violet marten]

can you give a example of that

how can they store there data in db

[violet marten]

its being complicated for me

[slow stirrup]

Discord.py will be discontinued because of Discord's slash commands, use pycord lol

it's a fork of it

[uneven stream]

?
dpy is back
slash cmds were also added about a month ago

[slow stirrup]

oh lol, I guess I remembered an old source, haven't been using discord.py for a while sorry for that

[uneven stream]

oh
wouldn't have known either if it wasn't for the updates ping

[uneven stream]

    cursor = await db.execute('select name from cards where name like "%?%"', (card,))
  File "/home/ubuntu/.local/lib/python3.10/site-packages/aiosqlite/core.py", line 184, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "/home/ubuntu/.local/lib/python3.10/site-packages/aiosqlite/core.py", line 129, in _execute
    return await future
  File "/home/ubuntu/.local/lib/python3.10/site-packages/aiosqlite/core.py", line 102, in run
    result = function()
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.

[waxen finch]

sqlite uses the ? as the placeholder and will automatically handle putting quotes around it if the argument you pass is a string

[uneven stream]

oh

thanks!

[waxen finch]

oh wait if you are looking to do a text search, i think an fts table is preferable for this

[uneven stream]

?

used (f'%{card}%',)

[waxen finch]

full-text search https://www.sqlite.org/fts5.html

special table that indexes the content to make matching words a lot faster

[uneven stream]

o

so I just replace first line with
CREATE VIRTUAL TABLE cards USING fts5(x);

oh wait
I need to install it?

[waxen finch]

ive only read through the docs but hadnt attempted an implementation with fts

[uneven stream]

oh

[waxen finch]

though i think fts should be built in

[rough viper]

yes

[violet marten]

uh?

[remote latch]

is there a way to delete a sql table entirely or add more columns to it
like the table itself not records in the table
cause I didn't do mine right the first time

[harsh pulsar]

DROP TABLE to delete or ALTER TABLE to modify

consult the documentation for your specific database for usage details

[fluid lava]

Any great alternatives to Alembic? not a big fan since it's a bit clunky but I do use SQLAlchemy.

[native sable]

near "channels_id": syntax error``` hi! what is wrong with this?

[indigo valley]

DELETE cannot delete a single field.
Correct would be:
DELETE FROM logs WHERE guild_id = 956990653336784976;

It always deletes the whole database row.

[native sable]

ah makes sense

ty !

[indigo valley]

np

[indigo valley]

You could try Django (https://docs.djangoproject.com/en/4.0/topics/migrations/) which has built-in support for database migrations.
If you don't want the overhead of a complete framework, why not try EdgeDB (https://www.edgedb.com/docs/datamodel/index) which is based on PostgreSQL with a built-in Data Model including Migrations directly in the database.

[nova forge]

I have a database having more than 10k records on local pc how can I take its backup? I used to use php admin it was easy to export sql file there is there any way to get backup through cli too?

[torn sphinx]

which db yuo've in your local pc?

[nova forge]

Traditional mysql

[torn sphinx]

owo i see

[nova forge]

Is it possible?

[torn sphinx]

ok i believe that it should be possible

but, I've no experience with it, ||like i've used mysql, but never did backup stuff||. u can try this: https://dev.mysql.com/doc/mysql-backup-excerpt/5.7/en/using-mysqldump.html

[nova forge]

mysqldump -u [uname] -p db_name > db_backup.sql I got this query but will it work for like 10k records

[torn sphinx]

that's all i can do my friend.. I never tried to do backup stuff before :'/

let's hope someoen else will solve your question

:)

[nova forge]

Aye aye no worries thanks for help!

[torn sphinx]

:>

[uneven stream]

is there a better way to do this?```py
for i in range(1, 71):
cursor = await db.execute('select name from cards where loc = ? limit 1', (i,))
# some stuff with the result

[trim lintel]

You can use ‘where in (1,2,3…)’. Obviously the list would be generated by python so you don’t have to type all those values in.

But you avoid making the query in a loop

?

[uneven stream]

so it would return only 1 row from loc 1,2,3...?

[trim lintel]

No you will have to get the row you want from the list returned.

But this avoids the n+1 problem

[uneven stream]

oh

[brave bridge]

what database are you using?

[uneven stream]

aiosqlite

[brave bridge]

and what library

https://stackoverflow.com/a/1310001/10295729

wait

@uneven stream ```py
db.execute('select name from cards where loc >= 1 and loc <= 70')

[uneven stream]

oh
I needed only 1 row from each loc

[brave bridge]

?

wdym?

[brave bridge]

That's actually not a big problem with SQLite
https://www.sqlite.org/np1queryprob.html

[uneven stream]

select name from cards where loc = 1 limit 1
select name from cards where loc = 2 limit 1
select name from cards where loc = 3 limit 1
...

[uneven stream]

smthn like this?

[brave bridge]

why?

[uneven stream]

wdym why

I need one card from each loc

[uneven stream]

ig I used this, but if there's a better way I would like to know

[brave bridge]

!e

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE foo(id, bar)")
con.execute("INSERT INTO foo(id, bar) VALUES ('a', 1), ('b', 2), ('c', 3), ('d', 4), ('e', 5)");
con.commit()

cur = con.cursor()
cur.execute("SELECT id, bar FROM foo WHERE bar >= 2 AND bar <= 4")
print(cur.fetchall())

[delicate field]

@brave bridge :white_check_mark: Your eval job has completed with return code 0.

[('b', 2), ('c', 3), ('d', 4)]

[brave bridge]

this way, you can fetch all the information in one query

Or is your actual code using something other than a range to select the cards?

[uneven stream]

the cards are more like this```py

(loc, card)

[(1, 'a'), (1, 'b'), (1, 'c'), (1, 'd'),
(2, 'e'), (2, 'f'), (2, 'g'),
(3, 'h'), (3, 'i'),
(4, 'j'), (4, 'k')
(5, 'l'), (5, 'm'), (5, 'n')]

[brave bridge]

what's the difference?

[uneven stream]

and I would need```py
[(1, 'a'), (2, 'e'), (3, 'h'), (4, 'j'), (5, 'k')]

[brave bridge]

why (1, 'a') and not (1, 'd')?

[uneven stream]

I need the first card of loc 1

[brave bridge]

first based on what? because 'a' comes before 'd' alphabetically?

[uneven stream]

alphabetically, but since I have the table data in alphabetical order it would be the first card for each loc

[brave bridge]

rows are not guaranteed to be returned in any particular order.

[uneven stream]

inserted them in alphabetical order

[brave bridge]

rows are not guaranteed to be returned in any particular order, including insertion order

[uneven stream]

idk abt that, returns stuff in alphabetical order for me

[brave bridge]

Yes, it happens to do that in that particular case. But it's not guaranteed to do that in all cases, and it might change in a later version
https://dba.stackexchange.com/a/5775

[uneven stream]

oh
for now its fine ig

[waxen finch]

i think an aggregate function might be appropriate for this

e.g. sql SELECT loc, MAX(char) FROM cards GROUP BY loc; 1|d 2|g 3|i 4|k 5|n

in your case you'd use MIN() with whatever you named the column

[uneven stream]

thanks!

[winged moth]

create_tables = """\
CREATE TABLE IF NOT EXISTS stock (
    id INT,
    name VARCHAR(255) NOT NULL,
    amount INT,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS stock_price (
    id INT,
    stock_id INT NOT NULL,
    price INT,
    PRIMARY KEY (id),
    FOREIGN KEY (stock_id) REFERENCES stock(id)
);
"""
cur.execute(create_tables, multi=True)

why doesn't it create table?

[trim lintel]

I think you have to make call to function commit() after

[winged moth]

still doesn't

[trim lintel]

Show code

[winged moth]

from mysql.connector import connect

con = connect(host='localhost', user='root', passwd='', database='temp')
cur = con.cursor()

create_tables = """\
CREATE TABLE IF NOT EXISTS stock (
    id INT,
    name VARCHAR(255) NOT NULL,
    amount INT,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS stock_price (
    id INT,
    stock_id INT NOT NULL,
    price INT,
    PRIMARY KEY (id),
    FOREIGN KEY (stock_id) REFERENCES stock(id)
);
"""
cur.execute(create_tables, multi=True)
con.commit()

[trim lintel]

Then I don’t know lad 🤷‍♂️

Try single statements, not sure if it supports multi true

[winged moth]

i think its the multi

[winged moth]

yea that works

[grim vault]

wouldn't that be ... (5, 'l')?
and a window function works also:

select loc, name
  from (
   select loc, name, row_number() over win as "row_number"
     from cards
   window win as (partition by loc order by name)
  )
where row_number = 1

[uneven stream]

oh

[uneven stream]

edited msg later, but yup
thanks!

[grim vault]

With the window function you can also eg. get the 2nd or 3rd of the entries.

[uneven stream]

o

[fluid lava]

Does Pandas have a bulk update(not insert) functionality with sqlalchemy?

[torn sphinx]

How to make the warn system non-global? type that on other servers were different?
sql

[waxen finch]

in relation to discord? i would suggest a compound primary key like (guild id, warn id)

[grim vault]

!e

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE cards (loc INTEGER, name TEXT)")
con.execute("INSERT INTO cards VALUES (1, 'a'), (1, 'b'), (1, 'c'), (1, 'd'), (2, 'e'), (2, 'f'), (2, 'g'), (3, 'h'), (3, 'i'), (4, 'j'), (4, 'k'), (5, 'l'), (5, 'm'), (5, 'n')")
con.commit()

sel_stmt = (
    'select loc, name'
     ' from ('
      ' select loc, name, row_number() over win as "row_number"'
        ' from cards'
      ' window win as (partition by loc order by name)'
      ')'
    ' where row_number = ?'
)

cur = con.cursor()
for i in range(1, 5):
   print(i, cur.execute(sel_stmt, (i,)).fetchall())

[delicate field]

@grim vault :white_check_mark: Your eval job has completed with return code 0.

001 | 1 [(1, 'a'), (2, 'e'), (3, 'h'), (4, 'j'), (5, 'l')]
002 | 2 [(1, 'b'), (2, 'f'), (3, 'i'), (4, 'k'), (5, 'm')]
003 | 3 [(1, 'c'), (2, 'g'), (5, 'n')]
004 | 4 [(1, 'd')]

[harsh pulsar]

(why not use a triple-quoted string?)

[grim vault]

I don't know. I somehow have a dislike of triple quotes.

[brave bridge]

I realized that I've never seen a window function

any resources to learn about them?

[waxen finch]

you could read about them on the sqlite docs https://sqlite.org/windowfunctions.html
or here https://mode.com/sql-tutorial/sql-window-functions/#intro-to-window-functions

[fathom hawk]

Ignoring exception in command fish:
Traceback (most recent call last):
  File "C:\Users\leka\PycharmProjects\Amk\venv\lib\site-packages\discord\ext\commands\core.py", line 187, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\leka\PycharmProjects\Amk\fishergame_cog.py", line 15, in getfish
    await db.execute("UPDATE exp SET FISH = ? WHERE UserID = ?", ran, alpha)
  File "C:\Users\leka\PycharmProjects\Amk\db.py", line 48, in execute
    await cur.execute(command, tuple(values))
  File "C:\Users\leka\PycharmProjects\Amk\venv\lib\site-packages\aiosqlite\cursor.py", line 37, in execute
    await self._execute(self._cursor.execute, sql, parameters)
AttributeError: 'Result' object has no attribute 'execute'

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

Traceback (most recent call last):
  File "C:\Users\leka\PycharmProjects\Amk\venv\lib\site-packages\discord\ext\commands\bot.py", line 1234, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Users\leka\PycharmProjects\Amk\venv\lib\site-packages\discord\ext\commands\core.py", line 923, in invoke
    await injected(*ctx.args, **ctx.kwargs)  # type: ignore
  File "C:\Users\leka\PycharmProjects\Amk\venv\lib\site-packages\discord\ext\commands\core.py", line 196, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'Result' object has no attribute 'execute'

Im not sure what the Result object is(im pretty sure its the cursor) but im getting this error:

[torn sphinx]

How to make the warn system non-global? type that on other servers were different?
sql

[vapid arch]

how to insert a datatime value in a VALUES(' ') command ?

[fluid lava]

Curious, how computationally expensive is to merge values and pandas.to_sql('replace') vs sqlalchemy bulk update vs sqlachemy iterated update.

[waxen finch]

@torn sphinx answered you above

[waxen finch]

that depends on the library - if you're using sqlite3 then they automatically adapt datetimes into ISO strings when passed as a ? parameter, though you would need to connect to your database with the detect_types=sqlite3.PARSE_DECLTYPES kwarg to auto-convert TIMESTAMP columns back to datetimes (https://docs.python.org/3/library/sqlite3.html#default-adapters-and-converters)

[vapid arch]

thanks 🤗🤗

[still folio]

is it normal for fetching 388 objects which each contain 25 key-value data points to take around 1-2 min on mongoengine?
feel like that's a insane amount of time to fetch only 388 items, not scalable at all

[delicate mango]

Anyone know of a kind of data store that is specialized for time series data, similar to InfluxDB or Prometheus, but you can refeed it the same data series at multiple granularities and when you query it, it gives you the finest granularity it has available automatically and fills the gaps with the less granular data as a fallback?

[warped narwhal]

Can I ask for comment on my ER diagram here? https://i.imgur.com/XuDL5hs.png

It should let me store "dirty" data from scraped sources (scrapy; i.e. it follows crawling instructions from which HTTP responses are parsed into items (dicts really) with instructions and then into an output pipeline, say into JSON files or in this case into a postgre DB - using dicts as rows). The attribute/key values tho are not always explicitly pre-defined, so I store one target (from the response) as it's key and another target (or many, or whatever) as it's value. So I use an associations table matching scraped keys with proper keys representing a standard attribute, also with the item's parent's (companies for what I want) id. Tables unit, clean_field and the last three values of entry would be completed by a later pipeline.

Two things I'm unsure about are entry's PKs and the looping reference. For the PKs, does this look ok? For presenting I'll be using most recent (or date-targeted) entry for each combination as cells of a table (col: entry.clean_field, row: item.id).

For looping, it's something I learned to avoid back when I was taught SQL/databases. Is this kind of loop here an issue? Does it even count as such since entry.clean_field derived from a lookup of an existing value (entry.source_field) and should it be diagrammed differently?

Thanks !

[torn sphinx]

How to make the warns on other servers different, otherwise it issued a varn on one server, and it is shown on all?
Sql

[obsidian basin]

I am trying to install flask-migrate with flask-sqlalchemy but I can't update my database because the upgrade database folder is in the wrong location

When I type flask db migrate -m "Initial migration." in the versions folder there is no pyache file and there is no env.cpython-310.pyc. These files are in the migrate folder but not in the version folder.

Here is a picture.

https://imgur.com/a/Ri1r0bP

Here is how it should look.

https://imgur.com/a/uBW5ip0

Notice how versions is empty and I don't have the upgrade or downgrade function or the file.

Why is this happening?

I am following this tutorial.

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

Here is flask-migrate documentation

https://flask-migrate.readthedocs.io/en/latest/

Thanks

[hollow zinc]

anyone knows how to insert a data in mysql using python?

im having troubles :c

[obsidian basin]

You should probably clarify what you are using flask djanjo etc. This might get you started https://www.w3schools.com/sql/sql_insert.asp Or better yet https://www.freecodecamp.org/news/sql-and-databases-full-course/. If you need how to create the database I have other recommendations

[hollow zinc]

OH ty

fun fact

every time i make a question here

5 seconds later i fix my code

[obsidian basin]

Ha np

Half the time I make questions I am ignored

[hollow zinc]

damn

i would help but im really new at this hahaha

[obsidian basin]

NP

So you fixed the problem

[hollow zinc]

yep

and i dont have any idea how

[obsidian basin]

Are you using flask or djanjo

etc

[hollow zinc]

if being honest i dont know whats that

im using tkinter

in python

[obsidian basin]

Never heard of it

[hollow zinc]

tkinter is like a library of python i think so

is like to make a interface

so this is just the code

for the insert

haha

[obsidian basin]

But you know primary keys and foreign keys

[hollow zinc]

yeah i know that

[obsidian basin]

do you have a question currently

[nova cove]

You should probably indent the code that is meant to be the arguments for connect

[fluid lava]

Anyone experienced with SQLModel? If so, how do you feel about it?

[pure mortar]

my boss insta-fixed a query issue the other day, and it blew my mind

i was still trying to understand the problem the stakeholder was having

[severe coral]

What sort of fix

[pure mortar]

she was like [Rex], try putting these few lines after the where clause

id have to look at it again for the specifics but it was basically a subquery that did many things

and like the team that was working with the dashboard brought up a list of issues previously and i applied the new query

and basically

all the issues were resolved

[kind meadow]

Did anyone here worked on BigQuery?
i am uploading data from .dta file batchwise but not all the rows are getting uploaded

It gives error ike provided scheme does not match: field chaanged type from FLOAT to INTEGER

[torn sphinx]

How to make the warns on other servers different, otherwise it issued a varn on one server, and it is shown on all?
Sql

[potent sapphire]

Hi, I have a problem where I have an app that is way too big if calculation is to happen locally. Therefore I want to send a request to another place where calulations will happen. How should I approach this? So for example I want the app to send information - add 20 to 50 and get result back from the server -70. Is there any good tutorial explaining this?

[dreamy lichen]

Hey guys is it possible to write user input data to a .json file through a MDTextField? i presume it is but i cant find any material anywhere.

[torn sphinx]

When working with jsons, is there anything like .keys() that allows me to check if a certain value is attached to a key without iterating through the whole json?
Similar to me being able to do if "a" in json.keys()

[nova cove]

!e

import json
some_json = """{
  "id": 1,
  "name": "bob"
}"""

s = json.loads(some_json)
if "name" in s:
  print("Key 'name' exists with value: ", s["name"])
else:
  print("Invalid key")

[delicate field]

@nova cove :white_check_mark: Your eval job has completed with return code 0.

Key 'name' exists with value:  bob

[nova cove]

something like this will work, @torn sphinx. you can just do if "key" in var_holding_json_object

also if you are using JSON as a db, I would recommend using something else like an rdbms

[torn sphinx]

I’m actually attempting the opposite, like querying for “bob” and getting “name” without having to iterate through all keys and comparing their values against “Bob”

[torn sphinx]

It’s a pretty small thing, like 10 entries max

[nova cove]

ah ok

[gleaming wagon]

hi

[worn musk]

!e

[delicate field]

Missing required argument

code

Command Help

!eval <code>
Can also use: e

*Run Python code and get the results.

This command supports multiple lines of code, including code wrapped inside a formatted code block. Code can be re-evaluated by editing the original message within 10 seconds and clicking the reaction that subsequently appears.

We've done our best to make this sandboxed, but do let us know if you manage to find an issue with it!*

[worn musk]

!e x = 150 print(x)

[delicate field]

Hey @worn musk!

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

[worn musk]

!e

[delicate field]

Missing required argument

code

Command Help

!eval <code>
Can also use: e

*Run Python code and get the results.

This command supports multiple lines of code, including code wrapped inside a formatted code block. Code can be re-evaluated by editing the original message within 10 seconds and clicking the reaction that subsequently appears.

We've done our best to make this sandboxed, but do let us know if you manage to find an issue with it!*

@worn musk :x: Your eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 1, in <module>
003 | ModuleNotFoundError: No module named 'matplotlib'

[winter token]

I need some help with caching some data which I get from a database. So I have a database containing tracks (file_path.mp3, artist, album, etc.) and I want to my repository class to fetch tracks only once and then cache them, but in a slightly specific way. I want for the tracks to be available via key ("artist", "year", ...) and value ("Alan Walker", "2017", ...). The key and value would be a tuple which would be a key in my cached dictionary and tracks which have that value would be the value in my cached dictionary. I want the same to be done with different keys, for example "artist", and to cache tuples containing artist name and the number of songs by that artist, for example [("Alan Walker", 22), ("Coldplay", 7)]. I thought I can make that cached dict into an attribute of my repository class and to make it Singleton, so every instance would point to the same object in memory and will use the same attributes, but that only works for instances created within the same function, so once the function is finished all the instances are garbage collected and new ones will be made in the future, so this approach doesn't work. I've seen online that there exists @functools.cached_property decorator but I don't think it's the best way to solve this problem nor do I know how to solve it by using it. Is there an elegant solution to this problem?

from typing import Union, List, Tuple

from constants import *
from data_models.track import Track
from repositories.tracks_repository import TracksRepository
from utils import Singleton


class CachedTracksRepository(TracksRepository, metaclass=Singleton):
    def __init__(self):
        super().__init__()

        self.cached_track_groups = {}
        self.cached_counts = {}

    def get_tracks_by(self, key: str, value: Union[str, int]) -> List[Track]:
        tuple_key = (key.lower(), value)
        if tuple_key not in self.cached_track_groups:
            self.cached_track_groups[tuple_key] = super().get_tracks_by(key, value)

        return self.cached_track_groups[tuple_key]

    def get_track_counts_grouped_by(self, group_key: str) -> List[Tuple[str, int]]:
        if group_key not in self.cached_counts:
            self.cached_counts[group_key] = super().get_track_counts_grouped_by(group_key)

        return self.cached_counts[group_key]

    def cache_tracks(self):
        for group_key in GROUP_OPTIONS:
            for group_name, _ in self.get_track_counts_grouped_by(group_key):
                self.get_tracks_by(group_key, group_name)
            self.get_tracks_by(group_key, None)

    def delete_cache(self):
        self.cached_track_groups = {}
        self.cached_counts = {}

Here's the code, TracksRepository is what actually has the alghoritms of getting tracks from the database and GROUP_OPTIONS is a list ["Artist", "Album", "Year", "Composer", ...]

[idle cedar]

is it a bad idea to create a new cursor every time i query something?

[fading patrol]

Context may matter but generally I think cursors are meant to be disposable like that.

[idle cedar]

a small discord bot

im subclassing aiosqlite to add my own methods for convenience

in the initializer i wanna do self._cursor = super().cursor()

but heard its not a great idea

[paper flower]

Yep, just use cursor directly

also use context managers when working with cursors so you don't forget to close them 🙂

[harsh pulsar]

do not reuse cursors

one cursor per query

and yes use context managers

also doesn't aiosqlite allow you to invoke execute as a method on the connection itself, returning a new cursor?

[idle cedar]

yes but i was told not to do that in #python-discussion

more precisely, i was told not to directly invoke execute on a connection. perhaps, they didn't know that aiosqlite returns a new cursor

[idle cedar]

per query?

nvm it all makes sense now

[harsh pulsar]

Who told you that? it's fine as long as you understand that it's still a cursor being returned

[paper flower]

one cursor per query doesn't make sense, how would you manage transactions and such?

I'd say one cursor per operation, could be a bot command, api endpoint, etc

Hm, i thought cursor in dbapi represents connection but that's not the case
Do they store state? Why would you create new one each time?

[harsh pulsar]

yeah, transactions are normally invoked on the connection

[paper flower]

Yep, just read on that

[harsh pulsar]

and whether or not the cursor stores state i think depends a lot on the underlying library implementation and the database itself

i just know that i have seen a ton of weird problems arising from reusing cursors

ranging from mysterious runtime errors to data seeming to disappear from the database

[paper flower]

https://peps.python.org/pep-0249/#cursor-objects I think it does have some internal state?
You seem to be right, you should probably create individual cursors for your queries

[harsh pulsar]

dbapi required cursors i guess just as a compatibility layer

kind of annoying for queries that don't require cursors, but it is what it is

so this is why a lot of database libraries let you invoke execute directly on the connection object, to alleviate the boilerplate of creating a cursor just to call .fetchall()on it and then toss it away

i think technically you're supposed to close the cursor too, with .close(), but usually i don't bother with that unless the database library docs make a big point of it

[paper flower]

I usually use sqlalchemy orm/core and didn't use dbapi implementations like sqlite or psycopg 🤔

[harsh pulsar]

sqlalchemy wraps those libraries

[paper flower]

I know

Otherwise it wouldn't require them 😅

[harsh pulsar]

i dont think i understood what you meant then

[paper flower]

Just saying that i didn't use DB API directly

[harsh pulsar]

ah, yeah

[paper flower]

I don't usually write sql except for one-time query/update

Because stuff can and would suddenly break, but orm's are usually type-checkable (unlike raw sql queries), so it would break less often 😅

[harsh pulsar]

indeed, i like orm's a lot

i use raw sql for ad-hoc data processing scripts. for a real professional application i'd always want to use an orm if possible

if i need precise control over the query, i can always drop down to writing raw sql

[paper flower]

I don't like active records orms, for example django orm or tortoise 🤔
I used django orm a lot, but it's a chore to write something remotely complex

i feel like sqlalchemy allows to build queries easier and is just better overall

[harsh pulsar]

valid. those are good for small lookups of things, not big complicated queries

[paper flower]

I would usually use sqlalchemy for that, it allows to write anything

[harsh pulsar]

also true, sqlalchemy core is excellent

[paper flower]

Django on the other hand doesn't even support cte's out of the box 🤔

[harsh pulsar]

fwiw i've never needed a CTE in production code

[paper flower]

I needed a recursive cte because i have self-referential relationship in my project, but i ended up just using a for loop and a stack

[harsh pulsar]

i've used them to keep my sanity intact instead of writing 4-level-deep nested queries in some ETL code though. even though i know they're often an optimization fence i prefer the syntax

hm... i did have that problem once, but it was when i was a junior and didn't know about recursive cte's 😆

[paper flower]

i chose loop deliberately because i didn't want to write any sql

if you start writing raw sql in your project it suddenly spreads everywhere

brrr

[harsh pulsar]

ehhh idk about that in general, but i can see how it might happen

[paper flower]

Also query builder in django is just weird

https://docs.djangoproject.com/en/3.2/topics/db/queries/#spanning-multi-valued-relationships
Like this

[torn sphinx]

I have three tables with around 100 mil, 80 mil and 200k records, and I performed left join between them and it take 15hrs in ADF Pipeline. I tried using non clustered index and still it didn't have much impact. Can someone please suggest some other solution to speed up the performance.

[lunar pier]

Good day everyone! Is there any way to select only existing values from row using sqlite3 + python?
I have many rows = 0, and i need to get only rowname > 0 & not null

[torn sphinx]

How to make the warns on other servers different, otherwise it issued a varn on one server, and it is shown on all?
Sql

[green sorrel]

why am i getting this error

File "C:\Users\91834\Desktop\DBSM\page1.py", line 14, in <module>
    mycursor.execute("INSERT INTO Worker(ecode,name,design,plevel,doj,dob) VALUES (%s,%s,%s,%s,%s,%s),(%s,%s,%s,%s,%s,%s),(%s,%s,%s,%s,%s,%s),(%s,%s,%s,%s,%s,%s),(%s,%s,%s,%s,%s,%s)",
TypeError: execute() takes from 2 to 4 positional arguments but 7 were given```

mycursor.execute("INSERT INTO Worker(ecode,name,design,plevel,doj,dob) VALUES (%s,%s,%s,%s,%s,%s),(%s,%s,%s,%s,%s,%s),(%s,%s,%s,%s,%s,%s),(%s,%s,%s,%s,%s,%s),(%s,%s,%s,%s,%s,%s)", 
(11,"Radhe Shyam","Supervisor","POO1","2004-Sept-13","1981-Aug-23"),(12,"Chander Nath","Operator","P003","2010-Feb-22","1987-Jul-12"),(13,"Fizza","Operator","POO3","2009-Jun-14"),
(15,"Ameen Ahmed","Mechanic","POO2","2006-Aug-21","1984-Mar-13"),(18,"Sanya","Clerk","P002","2005-Dec-19","1983-Jun-09"))
db.commit()

[vapid arch]

whats the best data type to use in a table to insert Yes/No Values?

NVARCHAR?

[torn sphinx]

it depends on your dialect, PostgreSQL has Boolean True and False https://www.databasestar.com/sql-boolean-data-type/

[vapid arch]

using SQL with mssms

basic stuff

[torn sphinx]

that's T-SQL, you can use BIT https://www.mssqltips.com/sqlservertip/6447/sql-server-bit-data-type/

[vapid arch]

yea thats what i used

now i have to use Values 1 and 0

any way to change them to Yes or No

?

[torn sphinx]

ah if you literally need Yes and No then you indeed need a kind of string datatype, nchar is also okay, because you know beforehand how long it will be (maximum three chars)

[vapid arch]

ah aight

[foggy lava]

what is database

how to code database

[lunar path]

you are the first person I have ever heard ask this question والله

[foggy lava]

lmeo

so how

and what is that

[vapid arch]

its a base

that stores data

[waxen finch]

A database's main purpose is to... store data
However there are many formats that data can be stored in, and there are different systems that can be used as an interface to that database
One common database type is a relational database, i.e. an SQL database, where data is stored in tables
Here's a video explaining more about SQL is: https://youtu.be/zsjvFFKOm3c
And to practise writing SQL yourself you can check out this interactive tutorial: https://sqlbolt.com/
In python there's builtin support for the SQLite database using the import sqlite3 module
If you also want to look into other database types you can check out this video and then search up different database libraries for python, for example PostgreSQL, MySQL, or MongoDB (a NoSQL document-based database): https://youtu.be/W2Z7fbCLSTw

[foggy lava]

ohhhhhhhh

thanks

[vapid arch]

btw how can i select all the orders from only 2 locations and sort them using ASC? lets assume i have a Orders table with a Location field, and i want to select only orders from NY and LA ?

can someone help me writing this query?

[waxen finch]

you can use WHERE location IN ('NY', 'LA') to check if its one of those two

[vapid arch]

ohh

i didnt know u can use multiple "locations" in this case in tha command

i coded it to select from one

but was struggling where to add the second city xD

thanks tho

[waxen finch]

the other way is combining your two conditions with OR, e.g. WHERE location = 'NY' OR location = 'LA'

[vapid arch]

tysm

[haughty belfry]

implementing a scoreboard in my web game, using pymongo.
I want to find the data in my document with the highest number:

example:

{'word': 'crams', 'user': 'ethan', 'score': '19'}

{'word': 'ankle', 'user': 'ethan', 'score': '20'}

{'word': 'elate', 'user': 'ethan', 'score': '21'}

{'word': 'wagon', 'user': 'ethan', 'score': '22'}

{'word': 'clary', 'user': 'ethan', 'score': '23'}

{'word': 'darbs', 'user': 'ethan', 'score': '24'}

{'word': 'loggy', 'user': 'ethan', 'score': '25'}

{'word': 'bribe', 'user': 'ethan', 'score': '26'}

{'word': 'hempy', 'user': 'ethan', 'score': '27'}

I want to find the set of data with the highest score count.
how do I do that?

[paper flower]

What rdbms are you using?

[torn sphinx]

Azure sql

[paper flower]

I never worked with it, but, what your query looks like?

[torn sphinx]

Select with 2 left join

[paper flower]

could you send me query plan for it?

[burnt turret]

something like collection.find().sort("score", -1).limit(1) should work i suppose

[winter token]

does any1 have an idea?

[fervent yarrow]

#help-mushroom

[paper flower]

Hm, why do you need to cache them?

[marsh kayak]

firebase = pyrebase.initialize_app(config)
database = firebase.database()

author = ctx.message.author
now = datetime.now()

data = {
    'Submit by': f'{author.name}#{author.discriminator}',
    'Date': now.strftime("%d/%m/%Y %H:%M:%S CET")
}

database.child(str(username)).set(data)

Does anyone know how I'll able to get a list of all the child nodes in my realtime database?

So I'm trying to get a list that would look like [user1, user2, user3, user4].

[winter token]

I'm using it in gui and it needs to be really fast, otherwise there will be slight lag

I don't want everything to be calculated on button click because some of it are very long lists and it makes the gui freeze for a split second which is pretty ugly

[paper flower]

You mentioned that you tried to use singleton, it didn't work?

[winter token]

i doesn't work, once all the objects get destroyed, new one will be created and all of the attributes would reset, for example if i create an object in a function it will get destroyed once the function is finished so if I create it again in a new function it wouldn't point to the same object because the old one was destroyed so the new one will be created, but it would work if I create new objecta within that function although that's not what I want, I want to be able to access that object (cached data) from a lot of other methods and classes in different files

[paper flower]

Singleton shouldn't just die or be collected by python's gc

how did you implement it?

[reef shore]

So apologies, I am very much learning. I have a small script that builds a SQLite db to track case statistics. I can pass the input() variables into the database just fine, but I am sticking on the foreign key issue. I have two tables, a case table that tracks global data relevant to a case and then an items table that tracks individual data points to multiple items within a case.

I am having trouble finding a good video or article that describes how to generate/link a foreign key from the items table to a primary key of the case table.

since the PK is generated when a new case is inputted, how do I populate a foreign key with that number without knowing it? There has to be a way, I am just not googling the right phrasing, I think.

I'm sure at the size of the database I intend on building, I don't really need multiple tables... but I want to learn how to do it.

[orchid shadow]

#voice-verification

[glossy trout]

Hey guys, i'm not sure if this is the right place to ask but i'm trying to scrape about 900 pages of a website and i'm stuck, i'm always getting the result from the first page.

      url = f'https://www.capitoltrades.com/trades?page={pg}&pageSize=50'
      r = requests.get(url)
      soup = BeautifulSoup(r.text, 'html.parser')
      t_table = soup.find('table', class_ = 'q-table trades-table')
      print(t_table.text)```

What am i doing wrong?

[mint flower]

Whats error

[glossy trout]

No error, i just get the same table (t_table) on every iteration

[mint flower]

Is it constantly repeating printing the forst page.

[glossy trout]

yes

[mint flower]

Hmmm

from the looks of it. Theres nothing there to say for it to go to the next page

[glossy trout]

...and so on

url is formated with pg in range(1,905)

i then use requests.get(url)

shouldn't it jump?

[torn sphinx]

How to make the warns on other servers different, otherwise it issued a varn on one server, and it is shown on all?
Sql

[mint flower]

sorry my ipad dies.

died

I know for sure for i in range(number, number) automatically goes from the first number in the range. And changes i by +1 everytime the loop starts over again. But im not familiar with pg. im assuming its just page. Try using INSIDE of the for loop. pg += 1

[lunar pier]

is there any way to get column name + row value output if cell not empty?
Using sqlite3

[waxen finch]

row value output...? can you describe the table you have and the information you're looking to get from it

[timber sonnet]

hey guys, does pandas.read_html() only work on tables?

[fading patrol]

Looks that way: https://pandas.pydata.org/docs/reference/api/pandas.read_html.html

[gusty mulch]

Anyone mind popping over to #🤡help-banana to answer some questions about how I should layout my db tables for my logging events for my #discord-bots

[tribal light]

I have the same impression. There is also a jupyter-lab plugin called mito, which should help doing similar things.

I am using SQLAlchemy for not very large projects and not web applications, but I show another ORM framework, namely PonyORM: https://docs.ponyorm.org/. It looks rather intuitive, has anyone had more experience with that? Would you recommend something else, eg Django ORM? (I don't intend to build a web app though)

[jolly heron]

anyone help

i have all these arrays that come up when i run code in debug mode. but none of these arrays are in my code. i want to export values of one of these arrays to excel but whenever i do some code it says cannot find array.

[tribal light]

Those arrays are one-dimensional numpy arrays do you wanna export them one by one to Excel or a combination of them?

[jolly heron]

just one of the arrays

[jolly heron]

my coding knowledge is not good, i dont know how

[tribal light]

I would write something like

import pandas as pd
df = pd.DataFrame(calc2, columns='calc2')
df.to_excel(df, 'calc2.xls')

Check also here: https://saikatkumardey.com/building-pandas-dataframes-from-ndarrays

[jolly heron]

what is calc2 is it name of excel file

[tribal light]

I see in your screenshot that your variable is named "calc2"

[jolly heron]

oh i see

[tribal light]

So you convert this to a pandas DataFrame, named df and then you save to Excel

[jolly heron]

oh okay let me try one second

this is the code i wrote

[left meteor]

datamerged = df2.merge(df3, how="inner", on=["begin_date","end_date","company_id"], left_index=True) 

when I am running this command on my Jupiter notebook it runs and successfully merges two-time series datasets, but when I'm doing this on google colab it returns me the following error:

MergeError: Can only pass argument "on" OR "left_index" and "right_index", not a combination of both.

Apparently, there are different pandas version. May I ask how I can execute this on google colab ?

[jolly heron]

i will test now

says not defined

see i dont get this

[tribal light]

right, so before you were having a breakpoint right after print("hello"). Your code is not really there and you 'd better align it properly so that is get's executed after the print. Isn't that what you wanted?

[jolly heron]

oh so what shall i do - indent lines 285-7?

oh i think it may have worked!

can you check does this code do same as yours.

when i indented as you suggest it works

[tribal light]

I guess it does otherwise it gets ignored as an outer block of code

BUT

Do you really want this code to get executed there?

[jolly heron]

umm im not sure to be honest. wherever you think is best. i just wanted the numbers exported to csv. i can share with you the code

!pastebin

[delicate field]

Pasting large amounts of code

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

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

[jolly heron]

https://paste.pythondiscord.com/reyimoxeje

[tribal light]

It seems good where you put it at the end. The for loop you start at line 219, should first end at line 2183, where you have print("done") and then save the file, otherwise you will just save the data from the last loop iteration. I hope it makes some sense what I 'm saying

👍

[paper flower]

I think sqlalchemy is pretty good

[tribal light]

True, my main problem is that I get it for a bit, then I leave it for rather long and when I get back to it, it needs loong time and tutorial revisions to see how things should be done.

[jolly heron]

thank you so much for your help

it saved my life

[paper flower]

I think that apply to any library or framework

[tribal light]

It does SQLAlchemy is involved at some points, eg. relationships. Those pieces can often get complicated

[paper flower]

You'd have same relationships with any other orm if you choose to design your db in a certain way

[jolly heron]

hey anyone know how to plot a line on an image. i have a massive list of x, y coodinates on excel

[swift patio]

@jolly heron

• get x and y into a dataframe with pandas.read_excel
  or:
• get a list of x and a list of y with openpyxl/xlwings
  then: py import matplotlib.pyplot as plt plt.plot(x, y) plt.show() that's the short of it, it only gets tricky when you want to customise the graph

[jolly heron]

I did this to make a data frame when I exported values to excel. Do I have to do something else so that it reads the X and y values of column 1 and 2

@swift patio

Sorry I'm bad at coding

[swift patio]

when you use read_excel you should get the same dataframe you used to_excel with. Maybe some data types changed, but if you're only using numbers to plot it should be fine.

if you need more help with this you should open a channel for it

[jolly heron]

That's great. Noone else has been able to help. So first I will do the read excel stuff. Then I will do your code. I have many xy coordinates will it understand that I want each xy coordinates joined up

[swift patio]

SELECT INV FROM table WHERE ID = 1 ?

[cunning gate]

Does anyone know how to run and operate a redis server on windows?

[grim vault]

(varname) is not a tuple you need a comma at the end like: (varname,) for all the executes you have there.

cursor.fetchall() will return a list of tuples. You can't just use that as a input into the next select.

[ember wind]

Can I assign a variable for my for loop? So when I click on the variable it runs the loop

[manic zinc]

Hi all, ive been having trouble writing a query. I have 3 tables, Table 1 has Staff Members with their ids, Table 2 has jobs with their ids and Table 3 has all the ids from other tables related to eachother. Im trying to create what i presume is a left outer join of all staff that dont have a relating job id in table 3, but to no luck

Afaik EXCEPT doesnt seem to exist in my version of SQL

So far I have tried:

SELECT `WorkTable`.`staff_id`, `table3`.`job_id`, `staffTable`.`first_name`, `staffTable`.`last_name` FROM `WorkTable` LEFT JOIN `staffTable` ON `WorkTable`.`staff_id` = `staffTable`.`id` WHERE NOT EXISTS (SELECT `jobTable`.`id` FROM `jobTable` WHERE `jobTable`.`id` = `WorkTable`.`job_id`;

but it just returns blank

[grim vault]

How about

SELECT staffTable.id, staffTable.first_name, staffTable.last_name
  FROM staffTable
 WHERE NOT EXISTS (
    SELECT "X"
      FROM WorkTable, jobTable
     WHERE WorkTable.job_id = jobTable.id
       AND WorkTable.staff_id = staffTable.id
 );
-- or
SELECT staffTable.id, staffTable.first_name, staffTable.last_name
  FROM staffTable
 WHERE staffTable.id NOT IN (
    SELECT WorkTable.staff_id
      FROM WorkTable, jobTable
     WHERE WorkTable.job_id = jobTable.id
 );

[manic zinc]

Well, that seems to have actually done the trick

thanks for that :)

been racking my brain for that XD

[grim vault]

I'm not sure if you even need the jobTable for that.

[manic zinc]

OH

[grim vault]

It seems like the WorkTable is enough.

[manic zinc]

cause they wouldnt appear in the table if they didnt have a job

thanks again

[sinful osprey]

Hi ... anyone tried pulling data from an excel sharepoint using python. Sharepoint with Multifactor Authentication

?

[harsh pulsar]

is there a microsoft sharepoint api?

oh, you can mount a sharepoint folder as a network drive and just use the filesystem

i've done that before

i see, there is a sharepoint api as well https://martinnoah.com/sharepoint-rest-api-with-python.html

and mounting it as a network drive https://bauensolutions.com/2017/01/20/how-to-map-your-sharepoint-online-library-like-a-network-drive/

to work with xlsx data, you need pandas or openpyxl

[sinful osprey]

Thanks...i'll try the second one... I dont have access to sharepoint API

[gusty mulch]

anyone mind popping over to #help-potato rq, I have a question about naming text files from python

[torn sphinx]

I can help

but I don't know your question

[gusty mulch]

I think I got an answer to my question already, not exactly what I was looking for but an answer nontheless

[jolly heron]

hey

https://paste.pythondiscord.com/ifuleyejuq i cant remember if i feed in 512 pixel height image of 256 height

can someone tell from the code which would be right

[whole pebble]

hey guys
I'm trying to sum some records and group them by their label
my problem is that I don't know how to implement the sum function (I'm using sqlalchemy in flask)
I can already group my records by their label, but I only get the first instances of each label

this snippet:

month = Expense.query.filter_by(user_id = current_user.id).filter(extract('month', Expense.date_created)==4).filter(func.sum(Expense.label)).group_by(Expense.label).all()

and this snippet:

month = Expense.query.filter_by(user_id = current_user.id).filter(extract('month', Expense.date_created)==4, func.sum(Expense.label)).group_by(Expense.label).all()

are not working. I tried to figure them out after reading documentation, but I was not succesful.
How can I implement this sum function into this code? I get an error "invalid use of group function" with each of the lines. Documentation has a little bit different way of using the query

[harsh pulsar]

@whole pebble does this help? https://stackoverflow.com/a/25560881/2954547

[whole pebble]

I've been there
this has a different way of querying table

base_query = session.query(
Expense.date and so on

I don't know how could I rebuild my code into fitting this way

I followed some tutorials in order to get the knowledge to build some websites and they used that way of querying tables

I do not use "session" string in querying tables in my code

when I changed it to

month = Expense.query(Expense.label, func.sum(Expense.amount)).group_by(Expense.label).all()

I got "TypeError: 'BaseQuery' object is not callable"

[jolly heron]

can anyone hellp me

[elder oriole]

I need help with an error trying to start mysql in ubuntu server:

error:

-- The process' exit code is 'exited' and its exit status is 1.
Apr 06 19:12:07 server systemd[1]: mysqld.service: Failed with result 'exit-code'.
-- Subject: Unit failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- The unit mysqld.service has entered the 'failed' state with result 'exit-code'.
Apr 06 19:12:07 server systemd[1]: Failed to start LSB: start and stop MySQL.
-- Subject: A start job for unit mysqld.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- A start job for unit mysqld.service has finished with a failure.

[jolly heron]

can anyone help me

[torn sphinx]

Anyone here a wiz with bigquery python packages?
Keep getting an error that won't let me import bigquery from google.cloud

[upbeat brook]

Does importing tables into an sql database with python from a dataframe create a schema as well?

[storm mauve]

if you use df.to_sql, they generate the tables as well

!d pandas.DataFrame.to_sql

[delicate field]

pandas.DataFrame.to\_sql

DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)```
Write records stored in a DataFrame to a SQL database.

Databases supported by SQLAlchemy [[1]](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html#r689dfd12abe5-1) are supported. Tables can be newly created, appended to, or overwritten.

[storm mauve]

otherwise, probably not

[jolly heron]

!pastebin

[delicate field]

Pasting large amounts of code

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

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

[jolly heron]

https://paste.pythondiscord.com/jacehufubo

!pastebin

[delicate field]

Pasting large amounts of code

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

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

[jolly heron]

https://paste.pythondiscord.com/cucobukiqu

[vast bear]

Guys hope you get these people help with there scripts? im just into coding not scripting sorry!!!

im off of here

[knotty wharf]

I am using sqlite3 and I would like to understand how to disable auto_commit. I would like to manually do all the commit to have full control on my database. I tried to change isolation level but this is not working.
In the following example, there is an insertion into the database even though' I didn't make any commit.

connection = sqlite3.connect(path, isolation_level = None)

self.conn = self.connection(path)
self.cursor = self.conn.cursor()

try:
    self.cursor.execute("""INSERT INTO _Subject (subject_id, first_name, last_name, birthdate)
                    VALUES (?, ?, ?, ?) """, value)
    ID = self.cursor.lastrowid # get the core auto increment ID
    print(f'Lastrow inserted in Database: {ID}')
    return ID 
except SqlError as e:
        messagebox.showerror(title='Insert Subject Error', message=f"Error: {e}")

[fallen abyss]

Get or set the current default isolation level. None for autocommit mode or one of “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”. See section Controlling Transactions for a more detailed explanation.
https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.isolation_level

Does that help?

[grim vault]

But isolation_level = None IS the autocommit one.

[shrewd jetty]

Is there a way with heroku connected to a github repository to use database (Not mongodb)

[wide willow]

I need help making a database, where can i learn it or how do i start( its for a website )

[fading patrol]

Not sure I understand what you're asking but you can very easily spin up Postgres on Heroku

[torn sphinx]

Hey is it normal to ask SQL questions here?

[fading patrol]

What kind of database do you want and how do you plan to interact with it? Look for relevant tutorials on Postgres or maybe MongoDB if you want to go that route

[fading patrol]

yes

[torn sphinx]

I'm trying to merge/join two tables.
TABLE1 (left) is the main table i am trying to join on, it contains some values in TABLE2, others that are not in TABLE2 and null values (normal in my case)
I use the following command:

SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.UNIQUE_ID = TABLE2.UNIQUE_ID_OF_CUSTOMERS

The issue is that it drops all the rows that are not in TABLE2 and does a form of a weird join that I cannot understand how to fix

[fading patrol]

That's how LEFT JOIN is meant to work.... sounds like you want a FULL JOIN or something else

[river matrix]

Hey.

When is the best time to remove anomalies, redundancies and inconsistencies when normalizing medium size data sets.

?

[shrewd jetty]

Hm,I am not sure how to do that with discord.py. I want users to be able to set their own log channel

[storm mauve]

take a look at asyncpg

for that, you would just have a table like ```
GUILD_ID | LOG_CHANNEL
267624335836053506 | 342318764227821568
... | ...

for resources on SQL overall, check the pins

[vital rain]

Hi guys, Can someone help me out with this sql query on postgres which I have to write but I just can't come up with, I have tried my best to simplify the problem from 1 million records and more constraints to this, I know this looks easy, but I am still unable to resolve this somehow :-

Table_name = t
Column_1_name = id
Column_2_name = st

Column_1_elements = [1,1,1,1,2,2,2,3,3,]
Column_2_elements = [a,b,c,d,a,c,d,b,d]

Now I want to print to those distinct ids from id where they do not have their corresponding st equals to 'b' or 'a'.

For example, for the above example, the ouput should be [2,3] as 2 does not have corresponding 'b' and 3 does not have 'a'. [even though 3 does not have c also, but we are not concerned about 'c']. id=1 is not returned in solution as it has a relation with both 'b' and 'a'.

Let me know if you need more clarity.
Thanks in advance for helping.

[harsh pulsar]

these are two columns in the same table?

so you just need to filter the table to remove rows that have st in ('a', 'b'), and then get the distinct ids from the result?

try using SELECT .. WHERE and SELECT DISTINCT

[torn sphinx]

Nah just did a silly mistake of putting where instead of and at the end

[vital rain]

@harsh pulsar yes. But that would return this row too ->[1,c] and [1,d], and I don't want 1

[harsh pulsar]

would it? you can use SELECT DISTINCT with specific columns

you don't need to use select distinct *

[vital rain]

select distinct id from t where st not in ('a','b') this, right?

this would return 1 ^

[harsh pulsar]

try it

[vital rain]

@harsh pulsar

[torn sphinx]

Hey guys, this program enables me to create a Customers Table and a Products Table. How can I implement a "Buy" function that makes the user select a Client then Products and sends this data to a "Total Sales" table ? I'm using Tkinter and MySQL
Code here : https://pastebin.com/bpciapwe any help would be amazing 🙂

I have customers & products. A customer can buy products, then you want to receive the right prices, sum them up and add them to a bill associated with the customer

bill is sent to the Total Sales where all the bills are saved

INSERT INTO total_sales (customer_id, price)
VALUES (
$(customer_id),
SELECT SUM(price)
FROM products
WHERE id IN ($(', '.join(str(e) for e in product_ids)[0:-1]))
);

this is the SQL part

I am still puzzled as how I can implement this in the GUI

meaning selecting a client and associating products to him

[grim vault]

This should work:

select distinct id from t
 where id not in (
   select id from t where st = 'b'
      and id in (select id from t where st = 'a')
 );

[dense oar]

Looking for some help with a MongoDB aggregation - my goal is to add total_count to the end result, but preferably without having to do 2 queries

collections = db["data"]["collections"].aggregate(
        pipeline=[
            {"$skip": 0},
            {"$limit": 5},
            {"$project": {
                "_id": 0,
                "slug": 1,
                "website": 1,
                "domain_authority": 1,
                "page_authority": 1,
            }},
        ],
        allowDiskUse=True,
    )

collections = list(collections)

data = {
    "collections": collections,
    "count": len(collections),
    "total_count": db["data"]["collections"].count_documents({}),
}

How could I achieve the same, but without having to query again for the total_count (db["data"]["collections"].count_documents({}))? Perhaps using $facet?

[oak forge]

Hello,

Problem: When I try to import psycopg-binary it says ImportError: Module not found even though I pip installed it without any error message occurring. This is psycopg3, not 2.

My setup: MacOS Big Sur (not M1) with IDE PyCharm CE and project running in virtualenv with Python 3.9.7 using PostgreSQL 14.

Notes: I’ve already had so many bugs and issues with psycopg that i’m about to look for an alternative. Wanna ask here first though. Also, the google results mostly refer to psycopg2.

[tropic kelp]

Add total_count to your pipeline if that makes sense. So you want a single pipeline that does a total count, and does your other stuff.

[dense oar]

Yeah, I'm asking how do I do that. I want to count all the documents, but without limit/skip. It will be used for pagination, so I know how many pages do I have to generate. What I have above works as expected, but I'd rather want to do it via a single DB call and create the total_count on the DB's end

[tropic kelp]

oh

https://stackoverflow.com/questions/51029987/using-count-within-an-addfield-operation-in-mongodb-aggregation/51030473#51030473

that's the answer

but that's actually a very good question

how do you do skip and count in the same pipeline

The truth is what you've described is very tricky to do declaratively haha. But MongoDB is all about providing declarative solutions and the optimised happens in the background

[torn sphinx]

near every product I have a button that ADDs the product to the TOTAL SALES table

can someone help me with the SQL statement that grabs PRICE from the PRODUCTS table and then sums it in the TOTAL SALES TABLE ?

[fallow basin]

Can I expect same queries to behave exactly the same way in postgres and sqlite3

[brave bridge]

Some of them will do, others won't 🙂

SELECT 1; should work in both

Also depends on what driver library you're using. asyncpg has a very different query substitution syntax from sqlite3

[fallow basin]

I had a rather dummy idea to locally clone my remote database in sqlite3 for caching.
Such that the cache accepts sql

Sounds like a fun project

[oak forge]

nobody? 🥺

[runic basalt]

do u think its possible to make a username system and keep value in the username data

with json

[brave bridge]

Why do you need a cache over PostgreSQL?

[fallow basin]

Becasue my io bound database is slow

Despite setting up a shared buffer cache

[brave bridge]

Check if:

• you're creating a connection on every request instead of using a shared connection pool
• you're running into the N+1 problem: https://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem-in-orm-object-relational-mapping

can you show your code maybe?

[fallow basin]

I don't have anything slow per se, I just wanted it to be more performant rather.

I get fetch times of around ~50 ms on non-intensive queries, which is okay I believe

Wait lemme show you my idea, please hold on a sec

[clear stirrup]

are you sure this is what you're supposed to be importing?

not just psycopg ?

https://www.psycopg.org/psycopg3/docs/basic/usage.html

[brazen charm]

MMM My average database latency is < 1ms

But that depends on your DB and disks

[brave bridge]

they said it's a remote one. depends on how remote I guess?

[brazen charm]

true

You ideally want the DB to be able to go across your intranet or at least have the datacenter peer with the other

[brave bridge]

Wouldn't a cache over PostgreSQL reduce its own caching quality?

[brazen charm]

it would

[brave bridge]

at least for large queries

[brazen charm]

But postgres does rely largely on the OS' file system cache

so it depends

but yeah generally I'd say you're better off just configuring Postgres to try use as much of the resources you want it to than having it share with a Redis store for example

also because trying to cache stuff outside of the context the DB has is really hard to do right

[brave bridge]

also cache invalidation

[brazen charm]

thats what i mean xD

[brave bridge]

ah

[brazen charm]

If you have this really really big query, that takes in all these parameters that change on a per user basis, then you're probably not gonna save much

but yeah, i'd probably start with just configuring Postgres to use your machine first before thinking about an external cache

[torn sphinx]

where can i get some free db hosting servers? (preferably sql)
(ik that free server are gonna have their limits, but i'm okay with it.. just suggest me if u know any)
ping me when u reply

[fallow basin]

Railway is a okay one.
It is so cheap with a 5$ credit that it is essentially free

https://railway.app/

[dense oar]

Could someone help with this MongoDB aggregation?

[torn sphinx]

have u tried it? 👀

[fallow basin]

Yeah

[torn sphinx]

owo i see

so like.. that free one.. won't cost anything right? and that $5 is granted monthly?

[fallow basin]

If you have a little bit of money there are much better hosting options though.

[fallow basin]

You don't have to give in any payment info for service if you are worried

[torn sphinx]

hmm it's more like.. i don't have any of my 'own' money at least for two months

so yeah.. that's sorta issue

well, thanks for helping so far

[fallow basin]

I see, you don't have to pay anything really unless you exhaust your 5$ quota during the month.

[torn sphinx]

makes sense 🤔

[gusty mulch]

If I want to store a python datetime object in a postgres dB what data type do I need to set my column to? I've been reading up on working with postgres and python datetimes and know that I need to make the python dstetime aware not naive as postgres works with aware but what data type should my column be? And yes I want to store the date and time as one column (my reading of the postgres docs seemed to suggest I'd need a column for time and a column for date)

[torn sphinx]

hmm

if i understood it correct

then it's basic sql, "DATETIME" type data

not related to postgres or mysql.. it's just sql :'/

[gusty mulch]

I'm no longer at my laptop so I can't double check this but when I typed in date or time into a column type a singular 'DateTine' but I might have missed it

[torn sphinx]

i see

alr then

let's see when u check it

:'/

[deft flame]

when using beeline i can query my thriftserver and get results without issue, .. when using tableau/jdbc i can see tables and get fields returned, but all rows are null. .. what would cause this behavior?

[torn sphinx]

how much data can the mongodb 500mb hold?

[storm mauve]

500mb

if you're just using text and numbers, that's a lot
if you try to include images/sound files or other forms of media, not as much

[gusty mulch]

Hi I'm back now, these are the type that pgadmin is showing that postgres can support

[torn sphinx]

💀 ok i'm not gonna lie.. i haven't used postgresql yet.. i'm only familiar with mysql at the moment

but as per what i understood by ur question..it shuold be datetime

so.. try to find something similar around it ig?

:'/

[gusty mulch]

thanks for the help, it's much appreciated even tho I wasn't able to get a concrete answer and at least i now have a better idea of what I can google

[torn sphinx]

eh yeah :")

[gusty mulch]

so if I'm reading this right https://www.postgresql.org/docs/8.2/datatype-datetime.html then what I should be using is the timestamp with timezone for 'aware' datetimes

[grim vault]

Yes, but there is a space between time and zone timestamp with time zone

[finite cedar]

Hello guys, is it possible to upload images to MongoDB using GridFS but to upload those files under a specific collection, not under the default fs.files one?

[bold falcon]

Hello world, how can i save variables in my pygame project? Im new here

[knotty fox]

hi

[grizzled mulch]

#game-development

Not here

[stuck girder]

Anyone know why my SQL database wont sort in alphabetical order?

This is literally all I have

SELECT school, last_name
FROM teachers
ORDER BY school ASC, last_name ASC;

[storm mauve]

check if there's any leading whitespace?

[stuck girder]

Thanks for the response, I figured it out, it was a beginner error on my end

[craggy flume]

This is the database schema

These are the 2 queries I used and got this output(which is correct)

When I tried to use this query to find out the name of projects that were chosen by multiple people, it is giving me a blank output. Could someone assist me with this?

[torn sphinx]

Do you guys know how I can maybe store something like
Value : Proof : Time in mongo? Basically need to store this in an array (multiple donations) for each document so I can retrieve the amount donated, a link to the donation and the time it was done

[torn sphinx]

Ok after looking a little more I think I would need to insert an object for each donation? That can have all the details?

[harsh pulsar]

what does the top-level document represent?

[harsh pulsar]

why would the project id be in a table containing counts? also it is much easier to help if you post your code as text (in a codeblock) and not a screenshot

!code

[delicate field]

Here's how to format Python code on Discord:

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

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

[torn sphinx]

Basically I need to be able to store each donation. When its done, proof and its value. So now I think I might make objects named "Donation1", "Donation2" etc but still not sure how I would make it work..

How will I check if the previous exists, seems messy idk

[harsh pulsar]

i'm still not sure what you're asking about. you said something about multiple donations for "each document" - what is "each document"?

[torn sphinx]

Sec, ill show you a pic

Currently, I am storing a users id, their balance, perks redeemed, when it expires. I also now want to be able to store when they donated and how much they donated then

Each document is the donations information for 1 user

Now I would have to store it like that right?

If I wanted to store how much they donated in their first donation etc

[torn sphinx]

If they make another donation, ill have to have another object to be Donation2 and then add them again. But how would I check which donation object is alr made, Donation4, Donation5 etc.

Wondering if theres a better way to do this

[harsh pulsar]

definitely an array of donation objects is a better choice here

[torn sphinx]

Wait- you can have an object inside an array?

[harsh pulsar]

of course

you can have arbitrarily nested data in mongodb

that's like the one good reason to use it

[torn sphinx]

oh wow

[harsh pulsar]

otherwise it's just a super-shitty relational database

[torn sphinx]

Haha, Also, do you know if I can find any documentation for motor?

[harsh pulsar]

https://motor.readthedocs.io/en/stable/ like the official docs?

(hint: ignore all the "tornado" stuff)

https://motor.readthedocs.io/en/stable/tutorial-asyncio.html
https://motor.readthedocs.io/en/stable/api-asyncio/index.html

[torn sphinx]

I dont know, I cant ever find what I am looking for there

Still pretty new with databases, not really sure how I would insert a object in the array, read from iti etc

[harsh pulsar]

you might want to read the actual mongo documentation then

pymongo/motor map pretty closely to mongodb operations

that said, if you're new... why are you even using mongo?

it's going to teach you bad habits and non-transferable skills

[torn sphinx]

Actually, the base that I am using already uses mongo and I dont want to manage 2 databases so its easier this way

[harsh pulsar]

fair enough

to read, use find_one or find to fetch an object or objects

then you get a dict or cursor that returns dicts

to update, use update_one and the $push operation to append to an array

https://stackoverflow.com/a/64711255/2954547

also, make use of https://mongoplayground.net/ and MongoDB Compass

[craggy flume]

FROM projects
WHERE project_id IN(
   SELECT COUNT(current_project)
   FROM employees
   HAVING COUNT(current_project) > 1
);```

[harsh pulsar]

thanks... what did you expect this to do?

why would project_id be in the count of all current projects?

also you aren't doing any grouping or joining

[craggy flume]

Why not? what else could I go with, the project tab just has 4 columns and that's the only way I could perform sub queries

[harsh pulsar]

why would project_id be in here? SELECT COUNT(current_project)

that's a column of counts of things

and it's actually just the count of all projects since you aren't grouping or anything