databases

[pure iron]

@commands.command(
        name="daily",
        aliases=["24hr"]

    )
    async def user_daily(self, ctx):
        place_datetime = datetime.datetime.now()
        local_collection = collection['coins']
        data = local_collection.find_one({'id': ctx.author.id})
        balance = local_collection.find_one({"_id": ctx.author.id})["balance"]

        if not data:
            data = {}
        data['datetime'] = data.get('datetime', place_datetime - datetime.timedelta(days=1, seconds=60))
        if (place_datetime - data['datetime']).days < 1:
            await ctx.send(
                f"You can only collect the reward once every 1 day! Come via `{int((place_datetime - data['datetime']).seconds / 3600)}` hours")
        else:
            local_collection.update_one({'id': ctx.author.id},
                                        {'$inc': {'balance': balance + 50}, '$set': {'datetime': place_datetime}})
            await ctx.send(f"You took 50 coins! Come back in a day and pick up more!")

i made this cmd with cooldown, and i want put cooldown in mongo db, since i always restart my bot, but this dodenst work - i meam nothing put in db and also "balance" doesnt change, help me please

[obsidian vector]

Can anyone help in threaded data dumping with python

I want to dump millions of data from excel file to mssql database, need help as it's taking too much time.

[obsidian vector]

r u using airflow like tool for such task?

[pure iron]

idk whats that

[obsidian vector]

it's used for the automation purpose, you don't have to use these commands and all

[pure iron]

ig i dont use that

[obsidian vector]

just create dags and configure it, that's it

remove the Trusted_connections paramerter from this

[shell ocean]

you want a join

[deep venture]

whats the error raised with asyncpg if you try to fetch a value and is not in the db?

[noble trout]

Would anyone be able to explain what a DDL-script is ?

[rare valve]

@bot.command()
async def addxp(message, amount, user: commands.Greedy[discord.Member]):
    members = user or message.author
    for j in range(0, len(members)):

        cursor = await bot.db.execute("INSERT OR IGNORE INTO guildData (guild_id, user_id, exp) VALUES (?,?,?)",
                                      (message.guild.id, members[j].id, 1))

    if cursor.rowcount == 0:
        print(len(members), members)
        for j in range(0, len(members)):
            print(members[j].id)
            await bot.db.execute(
                f"UPDATE guildData SET exp = exp {str(amount[0])} {str(amount[1:])} WHERE guild_id = ? AND user_id = ?",
                (message.guild.id, members[j].id))

        cur = await bot.db.execute("SELECT exp FROM guildData WHERE guild_id = ? AND user_id = ?",
                                   (message.guild.id, message.author.id))
        data = await cur.fetchone()

        exp = data[0]
        lvl = math.sqrt(exp) / bot.multiplier

        if lvl.is_integer():
            for k in range(0, len(members)):
                member = members[k].id
                for i in range(len(level)):
                    if lvl == levelnum[i]:
                        await member.add_roles(discord.utils.get(member.guild.roles, name=level[i]))
                        embed = discord.Embed(description=f"{member.mention}. New role: **{level[i]}**!!!")
                        embed.set_thumbnail(url=member.avatar_url)
                        await message.channel.send(embed=embed)
    await bot.db.commit()


@bot.command()
async def xp(ctx, user: discord.User = None):
    member = user or ctx.author

    # get user exp
    async with bot.db.execute("SELECT exp FROM guildData WHERE guild_id = ? AND user_id = ?",
                              (ctx.guild.id, member.id)) as cursor:
        data = await cursor.fetchone()
        exp = data[0]

        # calculate rank
    async with bot.db.execute("SELECT exp FROM guildData WHERE guild_id = ?", (ctx.guild.id)) as cursor:
        rank = 1
        async for value in cursor:
            if exp < value[0]:
                rank += 1

    lvl = math.sqrt(exp) // bot.multiplier

    current_lvl_exp = (bot.multiplier * (lvl)) ** 2
    next_lvl_exp = (bot.multiplier * ((lvl + 1))) ** 2

    lvl_percentage = ((exp - current_lvl_exp) / (next_lvl_exp - current_lvl_exp)) * 100

    embed = discord.Embed(title=f"Stats for {member}", colour=discord.Colour.gold())
    embed.add_field(name="Level", value=str(int(lvl)))
    embed.add_field(name="Exp", value=f"{exp}/{int(next_lvl_exp)}")
    embed.set_thumbnail(url=ctx.author.avatar_url)

    embed.add_field(name="Level Progress", value=f"{round(lvl_percentage, 2)}%")

    await ctx.send(embed=embed)

Ignoring exception in command xp:
Traceback (most recent call last):
  File "C:\Users\wolf\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\wolf\Observant Force discord\main.py", line 77, in xp
    async with bot.db.execute("SELECT exp FROM guildData WHERE guild_id = ?", (ctx.guild.id)) as cursor:
  File "C:\Users\wolf\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\context.py", line 41, in __aenter__
    self._obj = await self._coro
  File "C:\Users\wolf\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 184, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "C:\Users\wolf\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 129, in _execute
    return await future
  File "C:\Users\wolf\AppData\Local\Programs\Python\Python39\lib\site-packages\aiosqlite\core.py", line 102, in run
    result = function()
ValueError: parameters are of unsupported type

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

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

[noble trout]

I tried to format it in a lot of ways

But it won't work, this is the format for date type I found on google

[hollow shoal]

await client.db.execute("CREATE TABLE IF NOT EXISTS todolist (user_id bigint PRIMARY KEY, content text, jump_url bigint, author bigint, uses int);")```

this is the table I am trying to create, I am getting error 
`asyncpg.exceptions.InvalidColumnReferenceError: there is no unique or exclusion constraint matching the ON CONFLICT specification`

[hollow shoal]

nvm

[digital tulip]

    print(result)
    print(result["-MnUSfSTI4t54I-8gLag"]["tagvalue"])```

in this code everything is fine except that unique id ```-MnUSfSTI4t54I-8gLag``` always doesnt remain same in all cases.

how can i solve this?

please help

quick

please

[oak swan]

I install postgres 14 on it , after sometime it stop working and throw tcp/IP connection refused . I change allowed host to * and try port change as well but still it stop after sometime.

[tawny fox]

Hello guys ,I need to complete a task using python and aws

Running short on time can someone experienced can guide on me for the same ?

[livid crypt]

Hi anyone know how to convert dateTime column into seconds

the dates are like this

[drowsy viper]

Hi if anyone here uses mongodb, can u tell me why html form data saves as null?

[fading patrol]

What database are we looking at and what is the current data type on the column?

[livid crypt]

Hi dowcet, the data is from a csv file

[livid crypt]

with these columns

[fading patrol]

That part looks self explanatory, you can't use strptime on a data frame.

There's probably a way to do that with Pandas but I don't know off the top of my head

And this is totally off topic, grab a help channel #❓｜how-to-get-help

[hardy berry]

When i want to import my database like this i have an error, "no module named database"

import discord
from discord.ext import commands

from database.database_handler import DatabaseHandler
database_handler = DatabaseHandler("database.db") 

[gusty path]

Command raised an exception: ConnectionError: PostgreSQL server at "host" rejected SSL upgrade

What is this error and how can I fix it

im using asyncpg just making a connection to the db

[rapid turret]

Hey guys! I made a MySQL Python library because I noticed that I had to use too much SQL queries in Python and that didn't seem right to me, so I wanted to make a library that will make MySQL more pythonic and easier to use(hence the name my_ezql).

Feel free to use this library freely and suggest any improvements that come to mind.

Different ways to contact me for any reason ( feel free =] )

linkedIn- https://www.linkedin.com/in/tony-hasson-a14402205/

GitHub- https://github.com/tonyhasson

Pypi- https://pypi.org/project/my-ezql/

Obviously you can contact me here as well 🙂

Thanks for reading and I hope you enjoy the library!

[shell ocean]

tbh

the interface doesn't really look Pythonic

or easy to use

and a cursory look through the source suggests it is vulnerable to SQL injection

but it's good that you're identifying problems and creating solutions

[lean walrus]

so, currently you can't do ("SELECT ?, ? FROM ?", ("column1", "column2", "table")) this. I can use f-string for it, but that will possibly make a potential of SQL injections. How to prevent it tho?

[pale tusk]

why do you need to parameterize the columns and tables of you query?

[lean walrus]

    def select(self, *col: str, fetch_all: bool = False,
               extra_condition: str = "",
               order_by: Literal["desc", "asc"] = "asc",
               order_by_param: str = "timestamp") -> Any:
        """Returns a selected column by given id."""
        # TODO: Resolve sql injection
        self.cur.execute(f"SELECT {', '.join(col)} FROM history WHERE "
                         f"acc_id=? {extra_condition if extra_condition else ''} "
                         f"ORDER BY {order_by_param} {order_by}", (self.id,))
``` to do this. I have a method named `select` to mass select some rows

[pale tusk]

why not just select all the possible rows in the query, then only return the needed data to the application?

there's no added computational overhead on the database, and there's no need to have a custom query for column specifications

[lean walrus]

instead of doing more work, I want to know if selecting column without f-string possible?

[pale tusk]

well the ? placeholder for SQL is traditionally used to fill in variables, not specify table or column names

so i can't say for certain that it won't work, but i can say that it probably isn't the intended use case

[fathom star]

not sure if you can use ? in certain areas, such as column/table names as that might (?) be a security flaw

[shell ocean]

seems like you're basically building an ORM

[lean walrus]

yeah

sort of

a small one though

[sharp dragon]

Hey guys, im building a database but i'm having trouble with some concepts

I need to create a relationship for N amount of tables (on the right, as an example CSTT/SRL/Client3) and create a composite foreign key in Datamatches so that datamatches has a unique ID for each ID for each client row

[rapid turret]

Thanks I appreciate the honest feedback 😀 I know there is a lot of room for improvement, and I'll work on modifying it accordingly.

[shell ocean]

atb 🙂 nobody was born a great SWE

you might wanna look into how Peewee/Django ORM design their interfaces

since it kind of looks like you're building an ORM

or equivalents in other languages, such as JOOQ (Java), Diesel (Rust), or even something like LINQ (C#)

of course, you don't have to follow the beaten path, and there are certainly libraries which innovate in other directions, such as Slick (Scala), but it's good to think about why they made the choices they did

[rapid turret]

yup I actually didn't know what ORM is until yesterday lol, I really appreciate the new knowledge that you provided me with, I'll make good use of it 😀

[sudden rivet]

does anyone know what causes this?
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: user

the models i'm using: https://paste.pythondiscord.com/yutaqeziwo.py

[maiden umbra]

DB question: (mysql) I am building a python script that cleans up high-memory tables (millions of rows) to manage the disk usage. After performing a delete query, I have to keep checking the used disk space to check if it passes a certain threshold and further delete is needed. In order to do that, I have to run OPTIMIZE TABLE after delete to update the usage, which can take a very long time. Any suggestions on how to approach this?

[dire elbow]

hello guys, im a windows user and i want to do a presentation on shipping and streaming replication

any ideas that how can i do it?

like coding and stuff

it would be great if you could show me a setup on pgadmin 4 as well

[open fern]

The only information the error tells me is the database hasn't created the table 'user'. Have you created the table 'user'?

[torn sphinx]

It's kind of obvious though. Read the error message. It says that there is no such table with the name assigned, 'user'.

[tall lake]

I'm trying to tie an sqlite table to a field(?) of another table using foreign keys. Pretty sure i'm pretty off with this but here's what I got so far
For making the "sub" table:

CREATE TABLE IF NOT EXISTS tags_table (tag_id INTEGER PRIMARY KEY, tags TEXT, FOREIGN KEY(tag_id) REFERENCES log_entries(PrimaryKeyField) ON DELETE CASCADE)

(this table has more columns but i don't think it matters for my question)

CREATE TABLE IF NOT EXISTS log_entries (
            name TEXT,
            date TEXT,
            time TEXT
            -- here i want a field called tags that corresponds to the other table. i'm basically doing this so i can have a list of tags for every entry in this "main" table

Basically just restating that comment at the end, but my goal is to have something in log_entries that corresponds to another table of tags, so that every log_entries can have a list of strings associated with it

by tags i mean like you'd have in a blog or something, just a list of words decided by the user for organization

[pale tusk]

you're doing it the opposite way. if you're trying to reference tag_id in log_entries reference tag_id, you need to create the constraint in log_entries

[tall lake]

ok i thought that might be the case but wasn't sure lol

so in log_entries i want to add

-- name TEXT, date TIME, etc,
FOREIGN KEY(???) REFERENCES tag_table(PrimaryKeyField) ON DELETE CASCADE

not actually sure what i'd put in place of the ??? after FOREIGN KEY

and is the only field needed for tags_table "tags TEXT"?

[pale tusk]

in ??? you put the field in your table

instead of PrimaryKeyField you can put the actual name of the primary key column

so tags_table(tag_id)

[tall lake]

oh yeah i wasn't sure about that

do i have to manually state tag_id like i did?

cause from what i read it seems like ids might automatically be added in incrementing order

[pale tusk]

you need to tell sqlite what you're trying to reference

so what table and what column

[tall lake]

one sec i'm still kinda confused

so could the tags_table just be this?

CREATE TABLE IF NOT EXISTS tags_table (tag_id INTEGER PRIMARY KEY, tags TEXT)

[pale tusk]

yeah

[tall lake]

what do you mean by this, sorry

[pale tusk]

iirc ```sql
FOREIGN KEY(tag_id) REFERENCES tag_table(tag_id)

or something liek that

i just realized that u want more than one tho

[tall lake]

right

[pale tusk]

so what i said is wrong for this case lol

you want a one-to-many relationship

[tall lake]

yep

i learned that phrase today 😎

[pale tusk]

you probably need to reference the log_entries id in the tags_table

rather than reference the tags_table in the logs table

[tall lake]

so what i was doing before kinda?

[pale tusk]

oh yeah whoops, except idk if PrimaryKeyField is the right column for this

[tall lake]

right

[pale tusk]

i think you might need a primary key on your logs_entries table

you can just do an autoincrement one i suppose

[tall lake]

that just happens automatically right?

if i don't set anything

in sqlite

[pale tusk]

you don't want to create the fk on tag_id tho

you probably want a separate log_id field

that reference the specific log

[pale tusk]

i don't think you should use the hidden row id on SQL implementations, but i may be wrong about taht

[tall lake]

hmm ok

idk i feel like you might've been right about it being backwards originally

or maybe i'm just getting really confused lol

[pale tusk]

no you were right

i misread

basically you want to map multiple tags to a log right

[tall lake]

yes

[pale tusk]

and this isn't too rigorous but bear with me

you want to reference log to tag1 and tag2, right?

in order to do that you'll need to indicate which log it's looking at

are your tags shared across logs as well?

[tall lake]

no

specific to each log

[pale tusk]

but do they have overlap

like can two logs have the same tag

[tall lake]

theoretically, yeah

the tags are just whatever you input

[pale tusk]

oh ok you might want to look at a many to many relationship

[tall lake]

ohh ok

[pale tusk]

if tags are just unique strings

[tall lake]

yeah they are

[pale tusk]

you can just create a new unique tag string in a tags table

then you can create a third table

to actually store the relation

https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de this might help

the SQL syntax might be a bit off from SQLite's tho

[tall lake]

yeah

i think i get the idea though

i may be thinking about this wrong

but it seems like in the many to many example the primary keys are tied

but i don't necessarily want the tags to be the primary key

[pale tusk]

they both have their own primary key

and u reference both in the relation table

[tall lake]

so i'd like

make a field in log_entries called tags right?

actually don't i want one to many

since i want one record from log_entries (say, "tags" for example) to correspond to many entries in tag_table?

[tall lake]

i'm gonna go to bed, might be able to get help from a friend tomorrow

[torn sphinx]

     async with asqlite.connect('example.db') as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(
                        f'''INSERT INTO data (id  ,isIO) VALUES ('{message.author.id}' , '{role}')''')
                    query = f'''UPDATE data 
                    SET  postsdenied = postsdenied + 1 
                    WHERE id = {message.author.id}'''
                    await cursor.execute(query)
                    await conn.commit()

i am using a sqlite wrapper called asqlite , i want to add a +1 to the current value of postsdenied , SQL queries for some reason returns null

[torn sphinx]

sqlite_insert_query = """INSERT INTO SqliteDb_developers
                          (id, name, email, joining_date, salary) 
                          VALUES (?, ?, ?, ?, ?);"""```

How would I change SqliteDb_developers into an fstring?

when using it this way

oh I see how the guy above me did it

[torn sphinx]

the guy above you needs help with his code

[burnt turret]

you should not use f-strings to substitute values in your query

if it's sqlite, you should be using ? to substitute variables https://docs.python.org/3/library/sqlite3.html

[river cipher]

how to update whole array in mongodb ?

[surreal flame]

I have app with fastapi/postgres/sqlalchemy how do i can create temporary database for testing? should I?

i have seen on pip package testing.postgresql but last updates were at 2016

[sudden rivet]

well that's the weird part, since i've created the db and the tables

i've searched around quite a bit on the error but I can't find any solution that works

I tried changing the DB to mysql instead of sqlite as well, same error

[open fern]

Did the tables get created automatically as part of your program through Flask/SQLAlchemy or did you use some other method?

[sudden rivet]

using flask-sqlalchemy, so I think they should be automatically created?

[open fern]

This stack overflow question seems to have the same issue. https://stackoverflow.com/questions/21766960/operationalerror-no-such-table-in-flask-with-sqlalchemy

Have you called db.create_all() at some point?

[sudden rivet]

yeah

heard something about it having to do with the specific db instance, so I tried a few methods of doing it so that i'm calling it on the same instance as the one im running in the app

hard to just import db since i'm running blueprints, won't be initialised if I dont run the app

[open fern]

The user in the forum found they had to modify a variable name and the location of the database out of memory.

[sudden rivet]

hm

table doesn't appear to exist, which is weird since i've created it

trying to run queries in popsql

[open fern]

Are you able to use a tool to inspect the db itself outside of your program and see if table 'user ' is there?

[sudden rivet]

yeah, just did that

I guess I could try manually creating the tables?

feels like a spaghetti solution though if I ever want to change stuff

[open fern]

Yeah, try manually creating it.

Can you see watchlist or stock tables as well? Is the db in memory only or in a file?

[sudden rivet]

can't see the other tables, the db runs on localhost

[open fern]

Localhost is the computer, is your engine pointing to memory or /some/specific/file/path?

If that doesn't make sense just let me know.

[sudden rivet]

well sqlalchemy just looks for this

appears to find it just fine I think

[open fern]

I haven't used Flask so that is why I have basic ?'s. Okay, thank you. Where is the code where you make the database, the create_all() call?

[sudden rivet]

currently doing it inside my main.py

from aktietracker import create_app, db
from aktietracker import models


app = create_app()


def main():
    create_db()
    # app.run(debug=True)


def create_db():
    db.create_all()


if __name__ == '__main__':
    main()

create_app just returns the app and initialises the DB

[open fern]

Sorry to ask again. What tool are you using to look at the db (outside of your program) and is the user table created?

Got it.

[sudden rivet]

used popsql to look at it

tables don't appear to exist, trying to create them

not too familiar with SQL so i'm trying to figure out the relationships

[open fern]

Flask and sqlalchemy are there to do 'magic' for you if you create classes in the right way. I don't think you are supposed to create raw sql statements. We'll have to figure out why the magic isn't working. Are you following a flask tutorial or something you can share?

[sudden rivet]

i've followed this https://www.youtube.com/watch?v=cYWiDiIUxQc&list=PL-osiE80TeTs4UjLw5MM6OjgkjFeUxCYH&index=4 before, but after I finished that one I went over to trying to create my own app

I copied over quite a bit of code as a starting point

when I replicated the app in that tutorial it worked fine

[open fern]

Can you try using a sqlite db? Some database systems have servers and whatnot you need to initialize and that might be the issue.

I'm really not sure why the tables aren't being made. Is your file structure the same as the tutorial?

[sudden rivet]

it roughly is

the file structure is the same as at the end of the tutorial

but he goes through some refactoring towards blueprints towards the end

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_bcrypt import Bcrypt
from flask_login import LoginManager
from aktietracker.config import Config


db = SQLAlchemy()
bcrypt = Bcrypt()
login_manager = LoginManager()
login_manager.login_view = "users.login"
login_manager.login_message_category = "info"


def create_app(config_class: Config = Config):
    app = Flask(__name__)
    app.config.from_object(config_class)

    db.init_app(app)
    bcrypt.init_app(app)
    login_manager.init_app(app)

    from aktietracker.users.routes import users
    from aktietracker.main.routes import main
    from aktietracker.errors.handlers import errors
    app.register_blueprint(users)
    app.register_blueprint(main)
    app.register_blueprint(errors)
    
    return app

my __init__.py

in that tutorial vid he imports db, but I can't really do that here since it's not initialised

[pale tusk]

you'll likely need to startup mysql

the database server is separate from your application

have you done that?

[sudden rivet]

yeah it should be running

yeah mysql is running

[pale tusk]

hmm ok not sure why it wouldn't be connecting/creating the table if you have a carbon copy of their setup

[sudden rivet]

huh, weird

changed back to sqlite

ran into RuntimeError: No application found. Either work inside a view function or push an application context. See http://flask-sqlalchemy.pocoo.org/contexts/. trying to run create_db

found this https://stackoverflow.com/questions/46540664/no-application-found-either-work-inside-a-view-function-or-push-an-application

and it worked

only problem now is that i'll want to use mysql eventually, but at least the app didn't crash when I went through the registration form

[open fern]

Nice job figuring that out at least.

[sudden rivet]

maybe the same solution works for mysql?

[open fern]

I don't inderstand why you cant manually do what the first tutorial vid does.

From yourfile import db.
Run create_all

[sudden rivet]

I tried it, didn't work

oh the same solution worked for mysql

[open fern]

Okay, I don't understand why, but woohoo!

[sudden rivet]

https://stackoverflow.com/questions/44941757/sqlalchemy-exc-operationalerror-sqlite3-operationalerror-no-such-table
this is the one I looked at when the tutorial way didn't work

"So basically my understanding is that the way to fix this is to run db.create_all() using the same instance of db that is being used to define the models. In other words, run db.create_all() from within models.py."

strange, but at least it works now

well mysql is saying this now, but at least it doesn't crash

[open fern]

The db variable in aktietracker (which you import and use to create the database) is completely separate from the db variable in models.py. The models db variable is what you want to use. It seems like a variable scope issue.

[sudden rivet]

I don't think i've set up adding new users to the db yet so maybe it's because of that

[sudden rivet]

yeah, that appears to be the case

adding with app.app_context(): before db.create_all() is what solved it

[open fern]

I'm not sure about the etiquette of ending discussion, are you done with me/this question?

[sudden rivet]

yeah i'm done

[bleak crown]

Hi, i want to look for players data that is their game_mode is "FFA" and count of matches they played is >= 20. I tried this but it returns nothing instead there are 8 people that should be returned. (Database engine is sqlite)

If i delete game_type expression it works fine but i don't want every game mode :/

[pale tusk]

i think you want a where before group by that has the game_type condition

[bleak crown]

Uh, okay thank you ❤️

I was trying to add where but it was failing

It seems i was adding it to the last of it 😄

[stable needle]

While installing json lib

[torn sphinx]

Hey, sql but i hope you guys can help.

For some reason multiple records cant have the same value in the value in a column.

eg

                     email               username

Record1 ..... steve

Record2 a@a steve

Even as a null value, when i try and set the same value it just deletes the record. Any potential ideas? Thanks,

[shell ocean]

honestly I still don't get what you mean

show SQL

[torn sphinx]

Ill try to explain better

So i have a record already

The 2 columns:

Whitelist: asdaksdjaksdj
HWID: null

when i try to add another record with the same HWID value it just deletes it.

Whitelist: iiooppioi
HWID: opasd -> null (this record gets deleted)

My sql is

UPDATE users SET hwid = null WHERE whitelist = "iiooppioi"

Sorry if that is hard to understand, im new to sql if you cant tell already

@shell ocean

[shell ocean]

that

doesn't seem right.

[torn sphinx]

Reset it

[shell ocean]

how do you know the record gets deleted

[torn sphinx]

i have an api endpoint to select * records, and it just gets deleted (well its just not there)

i can screenshare if you would like

maybe that will help show you

[torn sphinx]

Ill try that now

Yes

[shell ocean]

query directly with SQL

[torn sphinx]

Okay

Restarted

same issue

[torn sphinx]

ill try that now

[torn sphinx]

how do you set the value of something to something else in pymongo

"a":"1"
"a":"2"

[rigid mica]

Does SQLAlchemy (sqlite 3 db) treat empty string '' as NULL? I created a table with column as nullable=False but it still accepts empty string without error.

[torn sphinx]

want a query that finds a record with specified channel id

help pls

Can anyone help me with a query in sqlite3? I got 4 columns with values, and I want to fill in the last with python.

sqlite_insert_query = f"""INSERT INTO '{current_date}' (column1) VALUES (?);"""

cursor.execute(sqlite_insert_query)```

mongodb

?

[torn sphinx]

this

That doesnt help me at all

[torn sphinx]

#databases Can anyone help me insert into an empty column, and/or update an empty column that has NULL values?

[fading patrol]

Guess not then, did you try None instead of empty string?

[fading patrol]

Sounds like you're looking for UPDATE instead of INSERT?

Oh, the issue is you want to do one or the other as appropriate?

[torn sphinx]

@fading patrol Yeah I got it to work, but not im facing a new problem, If I have:

cursor.execute("UPDATE poop_table SET column1= 123456") # THIS WORKS
cursor.execute("UPDATE poop_table SET column1= abc123456") # THIS GIVES INVALID COLUMN NAME

sqlite3.OperationalError: no such column: abc123456

What is going on here?

[fading patrol]

What's the data type for column1?

[torn sphinx]

@fading patrol Now its char, ive tried string, varchar etc etc

no difference :S

[fading patrol]

Put the string in single quotes?

[torn sphinx]

oh

hold on

omfg

im so dumb

@fading patrol Thanks man that worked

this sqlite3 query system is so confusing man, especially when its combined with python

[rigid mica]

Yeah None did not work. So I guess they are treated differently. Thanks.

[torn sphinx]

@fading patrol Another question for you, do you know if it would be possible to use the SELECT DISTINCT as a form of duplicate input "blocker"? Like putting it right after the data is loaded into the database in order to stop duplicates? Or do you have another suggestion that would work better?

[fading patrol]

https://www.sqlitetutorial.net/sqlite-unique-constraint/

[torn sphinx]

@fading patrol Yes, I got one ID that is unique, however the problem is that if I by mistake insert the same data, it just gets a new ID, therefore making it unique again, so it never really works out. I was thinking like SELECT DISTINCT if 3 columns are identical, if_exists=FAIL kind of thing.

Or I could have select distinct, checking 3-4 columns and then export them to a new table I guess, at the end of the code.

[fading patrol]

Just use UNIQUE so duplicates won't be allowed in the first place

[harsh pulsar]

sql is a totally separate programming language. you are basically writing sql programs inside python strings, so that might be the source of some of your confusion. i recommend https://sqlbolt.com for learning sql

[torn sphinx]

Thanks

[torn sphinx]

Say I got 200 lines in a database, and I wanted to only update certain lines in that using this query: py cursor.execute("UPDATE test_table SET column1= 'testtest'")
How would I formulate this query?

say only line, 5-10 in column1?

[grizzled kraken]

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)

    def __repr__(self):
        return '<User %r>' % self.username```

can someone explain to me this part? def __repr__(self): return '<User %r>' % self.username

[fading patrol]

It's returning the username

[grizzled kraken]

im copying this doc exactly and my shell just returned me a billion errors

[fading patrol]

Please don't DM people like that, open a help channel #❓｜how-to-get-help

[grizzled kraken]

ah ok

[outer iron]

help
writes this

    conn = asqlite3.connect(':memory:')
AttributeError: partially initialized module 'asqlite3' has no attribute 'connect' (most likely due to a circular import)

code

import asyncio

import asqlite3

conn = asqlite3.connect(':memory:')

async def connection():
    async with conn:
        await conn.execute("CREATE TABLE table (plate INT)")
        await conn.execute("INSERT INTO table VALUES (5)")
    # connection is automatically closed

loop = asyncio.get_event_loop()
loop.run_until_complete(connection())

help

[harsh pulsar]

"partially initialized module" suggests that it either didn't load correctly or has a circular dependency

this library doesn't look very well maintained

maybe use aiosqlite instead

!pypi aiosqlite

[delicate field]

aiosqlite v0.17.0

asyncio bridge to the standard sqlite3 module

[tall lake]

(hopefully my explanation is good enough but if you need me to post more actual code lmk)

so i have a many to many relationship, 1 table that has a bunch of info (name, date, etc) and another table for "tags" (just strings) and i'm trying to associate a list of tags with each main table

so i have a join table that looks like this

CREATE TABLE IF NOT EXISTS log_tag_join (
        log_id INTEGER,
        tag_id INTEGER,
        FOREIGN KEY(log_id) REFERENCES log_entries(id),
        FOREIGN KEY(tag_id) REFERENCES tags_table(id)

hopefully that looks good but if not let me know

but what i think is going wrong is my SELECT statement

SELECT * from log_entries LEFT OUTER JOIN tags_table ON log_entries.id = tags_table.id

here's what i have for showing it but i don't think it's right

can anyone offer any advice on what to do for the select statement?

actually hold on i redid the select statement

not correct yet i think but i think i'm closer

SELECT * from log_entries
LEFT OUTER JOIN log_tag_join ON log_entries.id = log_tag_join.log_id
LEFT OUTER JOIN tags_table ON log_tag_join.tag_id = tags_table.id

[outer iron]

who knows what the error is 'AttributeError: module 'pymongo.collection' has no attribute 'count_documents'

[whole pendant]

Can we implement django with mangoDB database is it good choice or postgrsql will fine?

[fading patrol]

They're pretty different (SQL vs NoSQL), but I guess either or both could be fine depending on your use case

[whole pendant]

But there is no official support for mangoDB by Django

[craggy pawn]

I understand reading and writing to a database but I can't seem to wrap my head around reading a database and checking for changes, especially if it's a huge database

and by extension, checking an API for updates

like if I have a database somewhere that I need to read and check for updates in order to send changes to an api, is my only option to have the live database, an intermediary database that holds my last known data, and then a python process that looks for differences and updates the intermediary?

[fading patrol]

What makes you feel like you need some kind of intermediary database? I'm pretty new to this stuff myself but in every case I've seen so far, API queries database directly

[valid wind]

does anyone know how virtual assistants store data? What kind of databases they use?

[little stirrup]

Hey, so I'm getting this error: Circular dependency detected. in my code using the sqlalchemy lib. I believe it's to do with relationships but am not too sure. Can anyone see a good reason why it would throw an error?

Full error: CircularDependencyError: Circular dependency detected. (ProcessState(OneToManyDP(User.white_games), <User at 0x2936ddc0ca0>, delete=False), ProcessState(ManyToOneDP(User.last_game), <User at 0x2936ddc0ca0>, delete=False), ProcessState(ManyToOneDP(User.last_game), <User at 0x2936ddb0520>, delete=False), ProcessState(ManyToOneDP(Game.white), <Game at 0x2936ddc4340>, delete=False), SaveUpdateState(<User at 0x2936ddb0520>), SaveUpdateState(<User at 0x2936ddc0ca0>), ProcessState(OneToManyDP(User.black_games), <User at 0x2936ddb0520>, delete=False), SaveUpdateState(<Game at 0x2936ddc4340>), ProcessState(ManyToOneDP(Game.black), <Game at 0x2936ddc4340>, delete=False))

Code:

[warm kestrel]

does anyone know how to get the latency of a MongoDB collection?

[shell ocean]

what do you mean?

[warm kestrel]

to get the latency of a database, so how long the response time is

[shell ocean]

you mean the connection?

or

like for the reads/writes for a specific collection?

[warm kestrel]

uhm, well i just want the response time of how long it takes the database to do stuff, you know

[shell ocean]

then that would depend

on what stuff is being done

right now what you're saying sounds more like benchmarking

[warm kestrel]

thats the reason id like the latency

well yeah, i guess it is

[shell ocean]

because if you said "latency"

I think the average person would understand that to mean purely the time spent in transit

i.e. ping delay

[warm kestrel]

yes, thats what i meant..

[shell ocean]

then ping the server

[warm kestrel]

i dunno how to do that :/

[shell ocean]

what are you using?

pymongo?

[warm kestrel]

yes

[shell ocean]

https://stackoverflow.com/questions/42565304/is-it-possible-to-ping-mongodb-from-pymongo

[warm kestrel]

thanks :)

[shell ocean]

I believe

it should return only when the ping does (I think)

if so, then you just want to take the time before and after and subtract the two

I feel like this is a bit of a weird thing to do though 🥴

(pinging)

apart from for like healthcheck purposes

[warm kestrel]

okay..

@shell ocean okay so it returned {'ok': 1} and i was wondering how i can get milliseconds?

[shell ocean]

that's what I said here

I believe
it should return only when the ping does (I think)
if so, then you just want to take the time before and after and subtract the two

[warm kestrel]

uh

like using timeit?

[shell ocean]

I would just use time

[warm kestrel]

mhm?

[warm kestrel]

how?

[torn sphinx]

does anyone know of any non restful document oriented databases?

[shell ocean]

look into time.time()

[shell ocean]

that...is kind of a weird question

what do you mean "non-RESTful"

how does that apply to databases?

[warm kestrel]

okay cheers

[torn sphinx]

most (if not all) document oriented databases that I’ve seen use restful apis

Im looking for one that doesn’t

[shell ocean]

why do you say that?

like...are you talking about how the driver communicates with the database server itself?

[torn sphinx]

According to the wikipedia hub for document-oriented databases, every database available in python save for rethinkdb function through the use of restful APIs, which isn’t ideal for a project that I need to function offline. I looked into RethinkDB and it wouldn’t work because its client requires you to host it on your computer and connect through http requests.

https://en.m.wikipedia.org/wiki/Category:Document-oriented_databases

[shell ocean]

by "offline"

you're basically saying

you want to have a database

but

you want to be able to connect to it without Internet access?

[torn sphinx]

Yes, similar to sqlite3

[shell ocean]

okay.

I think

you are looking for the wrong thing.

this isn't about REST APIs at all

you just want to launch a local database server.

[shell ocean]

just like you can create a local instance of Postgres and talk to it from your (also local) app

or any database

if it is capable of communicating over the Internet

it must be able to also talk to something locally

[torn sphinx]

I mean, yeah, perhaps I just asked my question wrong

I’m looking for a library that can help me launch a document oriented local database server

[shell ocean]

you wouldn't do that

with a library

because these aren't Python packages

for example, MongoDB is just a program you run

you could Google, for example, "how to run mongodb locally"

something along those lines

on the Python side

all you do is connect to it

[crimson smelt]

Hey just a quick question. For my project I want to make an SQL DB. And I have a table that is "Portfolio". It has 2 FK's (User_id and Stock_id). So it will have a portfolio ID (the PK) and then the User that Portfolio belongs to and the stock that belongs to it. if I want to add many stocks to that portfolio, I will basically just have the same two rows with the stock_id being different. Is that good or should I do it another way?

[fading patrol]

If I follow, that sounds like the standard implementation of a many-to-many relationship. You might want to read up on that keyword, but you're probably on the right track

[crimson smelt]

Okay perfect, thanks. I knew i couldn't have been the first to make such a table. I'm gonna look that up. ❤️

[torn sphinx]

d

d

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @torn sphinx until <t:1636319653:f> (9 minutes and 59 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

[deep venture]

pymongo.errors.ServerSelectionTimeoutError: localhost:27017: [WinError 10061] No connection could be made because the target machine actively refused it

using pymongo, no motor. access on db is set to everyone, password and username are correct

[coarse patrol]

I have a ms sql server and a .bak file from a few months back. The client would like to compare the two, to make sure the db wasn't corrupted. Is there a logical way of doing this that doesn't include running a python script to query everything? Seems like MS should have a feature. I just haven't found it.

[digital storm]

I have a question about a MySQL issue I'm having, it seems really basic but I'm not sure what I'm doing wrong. This is my code:

with connect(host=DATABASE_HOST, user=DATABASE_USER, password=PASSWORD) as cn:
    with cn.cursor() as cr:
        a = (999,)
        cr.execute(INSERT_QUERY, a)
        cn.commit()```
and then I get this error: ```ValueError: invalid literal for int() with base 10: 'with cn.cursor() as cr:@LINE@'```

[fading patrol]

I would look into the answers here even if your DB is running locally https://stackoverflow.com/questions/24899849/connection-refused-to-mongodb-errno-111

[torn sphinx]

Hello,

would anyone have a suggestion on how to stop duplicates while using df.to_sql? I got this system that pulls it from email, but if I were to send the same file, it would have 2 duplicates, I got sometthing working where I used DISTINCT in sqlite3, but that also removed lines in the file that had the same properties, like value and so on.

Im out of ideas 😩

If I added a unique column every time, it would still be duplicates next time because it would have a new value each time even though it was the same line.

I have to take into account that mistakes will be made and sent more than once, so I am basically looking for a solution to stopping that from happening in the code. Im not asking for a full code or anything here, just ideas.

So my solution would work, if it wasnt for that some of the lines actually are duplicates in the same file.

And they are not duplicates per say, they are the same product with same value and weight

[fading patrol]

It sounds like you might be doing DISTINCT wrong, like on the wrong column. If you do it on the correct column, it doesn't matter if the other columns are duplicate

[torn sphinx]

ok? if there are 20 lines in each document, each line has 3 values, like weight, value, name. Is it not correct that I use these like so: ```py "cursor.execute("SELECT DISTINCT weight, value, name")

therefore they are not distinct

Am I doing this the wrong way?

I basically need a unique column already on the document I think...

[fading patrol]

Yeah, you may want a UNIQUE constraint on one of those columns so duplicates will not be created in the first place

Or, maybe you want to GROUP BY a duplicate field in your query. Or something else. It really depends on what you're trying to do

[hollow scarab]

How to remove an element from an array by index in mongodb with python?

[torn sphinx]

SELECT * FROM steam WHERE id NOT IN (SELECT steamid FROM users)```
I am trying to only select rows that arent claimed by ``steamid``

both id and steamid are Integer data types

ok and

not getting nothing back

steamid is blank

[torn sphinx]

this works fine for me in mysql, maybe not for sql server tho https://stackoverflow.com/questions/1406215/sql-select-where-not-in-subquery-returns-no-results

[harsh pulsar]

i prefer the "not exists" version

SELECT *
FROM steam s1
WHERE
  NOT EXISTS (
    SELECT *
    FROM steam s2
    WHERE s2.id = s1.id
  )

[pliant scarab]

Not sure if this is the correct channel, but does anyone know how to find the max number across multiple columns in SQL server? The proposed answers online don't seem to work

[harsh pulsar]

so you have columns a, b, and c, and you want the max of a, b, and c within each row?

[pliant scarab]

Yes I want to know whether a, b or c is the largest for that row

The best answer I could find is:

SELECT symbol,
(select MAX(numbers)
FROM (Values (tastyBPE1), (tastyBPE2), (tastyBPE3)) as TblNum(Numbers))
FROM OptionData.dbo.Opt_Data_11_08_21

But it doesn't seem to be working

[harsh pulsar]

does sql server have a greatest() function @pliant scarab ?

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

[pliant scarab]

Nope lol

This is why this has been so complicated

[harsh pulsar]

if it makes you feel better, you're not the first https://stackoverflow.com/q/33904499/2954547

if it's only 3 columns you could do it "by hand"

[pliant scarab]

Yeah I've seen that suggested, but I'm a complete beginner so not sure how it would work exactly

This is the closest I could find, but again doesn't seem to work:

select
case when tastyBPE1 > tastyBPE2 and tastyBPE1 > tastyBPE3 then tastyBPE1
when tastyBPE2 > tastyBPE1 and tastyBPE2 > tastyBPE3 then tastyBPE2
else tastyBPE3
end
from
OptionData.dbo.Opt_Data_11_08_21
where undlyPercentChange is not null and symbol = 'SPY_012023P450'

[harsh pulsar]

yeah i was going to suggest something like that

https://www.db-fiddle.com/f/pZfHEXBJCWe8VV4wsQ4XVi/2

create table hello (
  i integer primary key,
  a float,
  b float,
  c float
);

insert into hello (i, a, b, c) values
  (1, 2.5, 3.5, 1.5),
  (2, -2.0, 1.25, 0.12),
  (3, 1.6, 1.7, -3.2);

-- select greatest(a, b, c) from hello;
select
  case
    when a > b and a > c then a
    when b > a and b > c then b
    else c
  end as best
from hello;

[pliant scarab]

Ok I'll give that a try

@harsh pulsar
The example works for me, but when I try to integrate it into my use case I am still getting NULL. There may be something basic I am missing... To start with, tastyBPE1, tastyBPE2, tastyBPE3 are all Null until after the first Select statement is run. When I run the second Select statement, then is it not picking up the changes from the first Select statement?

select symbol, undlyMark, strikePrice, mark,
(((0.2 * undlyMark) - (undlyMark - strikePrice)) + mark) * 100 * 1 AS tastyBPE1,
((0.1 * strikePrice) + mark) * 100 * 1 AS tastyBPE2,
2.5 * 100 * 1 AS tastyBPE3
from OptionData.dbo.Opt_Data_11_08_21
where undlyPercentChange is not null and symbol = 'SPY_012023P450'

-- select greatest(a, b, c) from hello;
select
  case
    when tastyBPE1 > tastyBPE2 and tastyBPE1 > tastyBPE3 then tastyBPE1
    when tastyBPE2 > tastyBPE1 and tastyBPE2 > tastyBPE3 then tastyBPE2
    else tastyBPE3
  end as best
FROM OptionData.dbo.Opt_Data_11_08_21  
where undlyPercentChange is not null and symbol = 'SPY_012023P450' 

nvm I got it to work now, thanks for your help

[torn sphinx]

i have a column time with race time data (ex. 1:43:53) – is there a way to average each lap time? i tried using AVG(time) but that just gave me 1s lol. see below for reference.

SELECT raceId, driverId, time 
FROM laptimes
GROUP BY raceId;

[cunning knot]

I have a question. I have 2 table and i want to inner join from left to right

the left table contains a foreign key from the right table and it has his own primary key
so should i join with the primary key + foreign key from the left table since i got the foreign key which has a connection to the right table?´
like for example ON(left table primary key = left table foreign key)

But i can also use the left and the right table primary keys but what is then the different ON(left table primary key = right table primary key)

[bitter pulsar]

how database and pythin relate to each other?

[fading patrol]

Joining a primary key on a primary key isn't likely to work because they're not related. For an inner join left vs right don't matter, you can join the primary on one to the foreign on the other and should get the same result either direction

[fading patrol]

Many Python apps use databases. ORM packages like SQL Alchemy are often used to help integrate the two

[sullen token]

what websites do I use for online db, like for eg mongodb.com for mongodb

[fading patrol]

Not so clear to me what you're looking for... cloud providers that have serverless mongodb? There are the big three plus others. Heroku is great for Postgres

[urban otter]

i dont know if this is the appropriate channel but i have a csv file, how do i delete a row in that file with python??

[storm mauve]

I'm slightly sure that you would have to read the entire thing, parse it into a list, then write it back (or write it to another file as you read it, if it does (not) meets a certain criteria)

not sure though, the real "right way" would be to use an actual database instead (like sqlite3, which is part of the standard library)

[crisp meadow]

Hey, everyone. Could someone please help if there any way to sort this columns assets equity revenue profit , I was cleaning this df but got no idea how to deal with cleaning this columns. Because it has too much random data, and I cannot just drop it as I will lose big amount of my dataframe. The problem consists that we have different formats as : Kč 36.13 billion ( ), US $ 1,438.2 million , ₺6.800 billion, NOK 253 395 million, €1,062 million. I'm a newbie in data science and only practicing cleaning data, is it doable for me ?

[harsh pulsar]

You did not pick an easy data set to start with. You will have to parse and clean up all those money amounts. Also note that #data-science-and-ml is sometimes better for pandas questions

[crisp meadow]

Got you, thanks for help, and sorry for writing into this chat

[kindred thunder]

Where can I ask questions on structure of simple programs?

I am newer to Python

[analog sigil]

you might want to ask in #python-discussion or a help channel (See #❓｜how-to-get-help )

[kindred thunder]

Thanks!

[kindred thunder]

My buddies dad is in the hospital... I am making a program where he can type sentences with just 1 button using 1 click as a means of cycling through options... and a long press as a means of selecting through the cycled options... I need help with the structure of the data being stored mainly the alphabet... I have the code as an example I can share.
I am using vscode
M.J — Today at 3:55 AM

_1 = 'A'
_2 = 'B'
_3 = 'C'
_4 = 'D'
_5 = 'E'
_6 = 'F'
_7 = 'G'
_8 = 'H'
_9 = 'I'
_10 = 'J'
_11 = 'K'
_12 = 'L'
_13 = 'M'
_14 = 'N'
_15 = 'O'
_16 = 'P'
_17 = 'Q'
_18 = 'R'
_19 = 'S'
_20 = 'T'
_21 = 'U'
_22 = 'V'
_23 = 'W'
_24 = 'X'
_25 = 'Y'
_26 = 'Z'
#quaternary vars

_1st8 =  _1, _2, _3, _4
_2nd8 =  _5, _6, _7
_3rd8 =  _8, _9, _10
_4th8 =  _11, _12, _13
_5th8 =  _14, _15, _16, _17
_6th8 =  _18, _19, _20
_7th8 =  _21, _22, _23
_8th8 =  _24, _25, _26
#tertiary vars

_1st4 =  _1st8, _2nd8
_2nd4 =  _3rd8, _4th8
_3rd4 =  _5th8, _6th8
_4th4 =  _7th8, _8th8
#secondary vars

alpha =  _1st4, _2nd4, _3rd4, _4th4
#primary var

print(alpha) 

When I print this however it shows all the brackets and commas... How do I get it to print just the letters. ((('A', 'B', 'C', 'D'), ('E', 'F', 'G')), (('H', 'I', 'J'), ('K', 'L', 'M')), (('N', 'O', 'P', 'Q'), ('R', 'S', 'T')), (('U', 'V', 'W'), ('X', 'Y', 'Z')))

Furthermore, where do I go from here to get a single button when clicked (on a keyboard) to cycle through the sets and select the set with a long press?

[spring hazel]

Hi. I had this query done in Sqlite python and it worked just fine
(attached screenshot for better readability)

However, I decided to switch to MySql and get the following error:

MySQL server version for the right syntax to use near 'DEFAULT '',
                                                pref' at line 7```

Whatever I save as DEFAULT value for VARCHAR (except `NULL`) Seems to give the same error, even though it worked perfectly in Sqlite
IN THE IMAGE BELOW, FILTERED WORDS USED TO DEFAULT TO ""

[harsh pulsar]

unlike in other programming languages, strings are not just sequences of characters smashed together. in fact, python has no "char" datatype, they are all just length-1 strings. so you need to concatenate those strings together. the + operator does string concatenation, or you can use the ''.join() idiom for sequences of strings

!e ```python
a = 'a'
b = 'b'
c = 'c'

text1 = a + b + c
print(text1)

text2 = ''.join([a, b, c])
print(text2)

[delicate field]

@harsh pulsar :white_check_mark: Your eval job has completed with return code 0.

001 | abc
002 | abc

[harsh pulsar]

for keyboard input, you might want to look into the keyboard module

!pypi keyboard

[delicate field]

keyboard v0.13.5

Hook and simulate keyboard events on Windows and Linux

[harsh pulsar]

as for the layout of your data, there's nothing strictly wrong with it, although isn't it a bit confusing to encode 'A' as _1 as opposed to just something like _a?

[pseudo isle]

Is SQLAlchemy the most popular ORM in python? What are some good alternatives to it?

[devout yacht]

Hi everyone, i have an issue with sqlalchemy and load data local infile, the issue is i have 14K rows of csv, and its giving a time out

the code is below

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import pymysql
import pyodbc
engine = create_engine('mysql+pymysql://user:pwd@host:3306/zdb?local_infile=1',connect_args={'connect_timeout': 2000, 'pool_size':2000})
SessionMaker = sessionmaker(bind=engine)
smaker = SessionMaker()
from sqlalchemy import text
query = "LOAD data local infile '/root/Fitcode-Sorting-DS/norma_score.csv' INTO TABLE fitcode_fsa1_score fields terminated BY {} enclosed BY {} lines terminated BY {} ignore 1 rows;".format("\',\'", "\'\"\'", "\'\\n\'")
print(query)
smaker.execute(query)
smaker.commit()
smaker.flush()
engine.dispose()```


can anyone please help me why its happening

[next glade]

Anyone know where i can find documentation for sp_who3 stored procedure?

[torn sphinx]

Code: https://replit.com/@VulcanWM/jasonism-fork
So yesterday I was fiddling with the packager files and I deleted them and since them the code I used previously without any errors is showing an error on the line where I import pymongo
How do I fix this?
Thanks

[devout yacht]

  File "main.py", line 1, in <module>
    from app import app
  File "/home/runner/Ve61XSWABv7/app.py", line 3, in <module>
    from functions import getcookie, makeaccount, addcookie, getuser, gethashpass, delcookies, makeblockbigger, getquestion, addxpmoney, cupgame, flipcoin, rps, rolldice, mencalc, upgradeblock, randomword, shuffleword, words, getnotifs, clearnotifs, allseen, challengerps, denychallenge, getchallenge, acceptchallengefuncfunc, checkgambling, changeblockname, changedesc, addxpstats, checkxpstats, addlog
  File "/home/runner/Ve61XSWABv7/functions.py", line 9, in <module>
    import pymongo
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymongo/__init__.py", line 112, in <module>
    from pymongo.collection import ReturnDocument
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymongo/collection.py", line 20, in <module>
    from bson.code import Code
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/bson/code.py", line 18, in <module>
    from bson.py3compat import abc, string_type, PY3, text_type
ImportError: cannot import name 'abc' from 'bson.py3compat' (/opt/virtualenvs/python3/lib/python3.8/site-packages/bson/py3compat.py)``` is this the error

[normal root]

I need to figure out how to make my script faster

can someone help?

tag me if you can please

[devout yacht]

@normal root what do you mean?

can you please ping the code,

[normal root]

1 sec

!paste

[delicate field]

Pasting large amounts of code

If your code is too long to fit in a codeblock in discord, you can paste your code here:
https://paste.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.

[devout yacht]

please use ```py

[normal root]

https://paste.pythondiscord.com/exotogakiy.makefile

[torn sphinx]

yeah

[normal root]

@devout yacht

you have any idea?

it just sorts trough 45k of data in chunks of 1k

but it takes like 5-10 mins

[devout yacht]

i saw the code you are doing a select query from the table called companies_ljubomir

[normal root]

yes

[devout yacht]

and again updating companies_ljubomir table

why?

with name id form companies_ljubomir

[normal root]

becuase im taking data from 2 columns

1 for dirty names

1 for ids

and putting back the data in cleaned names

and again in ids

[devout yacht]

clear_special_letters

[normal root]

yes

[devout yacht]

this is another function

?

right

[normal root]

yes

def clear_special_letters(input_str):
    # ASCII е сет на карактери кој е подмножество на UTF-8 која е кодирачка шема
    # NFKD е форма на нормализација за компатибилно распаѓање на Unicode карактери
    cleared = cleanco(input_str)
    input_str = cleared.clean_name()  # Користенје на cleanco
    input_str = input_str.title()  # Капитализација на зборовите
    return normalize('NFKD', input_str).encode('ASCII', 'ignore').decode('UTF-8')

cleanco is a public module

that cleanes legal suffixes

like LTD Limited etc

[velvet ridge]

I have a problem that my rate limit system is not limiting properly.. This is my ratelimiter and it's used in __main__.py file. https://github.com/Nipa-Code/lemonAPI/blob/master/lemonapi/limiter.py

there is no any error messages

[kindred thunder]

import keyboard
import time

pygame.init()

alpha = ((('A', 'B', 'C', 'D'),
('E', 'F', 'G')), (('H', 'I', 'J'), ('K', 'L', 'M')),
(('N', 'O', 'P', 'Q'), ('R', 'S', 'T')),
(('U', 'V', 'W'), ('X', 'Y', 'Z')))

index = 0

# two clicks
index += 2

# long click selects that group
group = alpha[index]
print("Top level group after 2 clicks:", group)

index = 0

#no short clicks, just a long click
letter_group = group[index]
print("\nLetter Group after no clicks:", letter_group)

index = 0

# two clicks
index += 2

# long click to choose letter
print("\nChosen Letter after two clicks:", letter_group[index])

for event in pygame.event.get():
        if event.type == pygame.KEYDOWN:
            if event.key == pygame.K_SPACE:
                index = 1
            elif event.key == pygame.K_b
                index += 2
        

print(alpha[0]) #Prints the 0-th element 
 
print(alpha[5]) #Prints the 5-th element 
 
print(alpha[12]) #Prints the 12-th element```

I am trying to get 1 button to be able to type sentences and somebody said I needed to use indexes... Please help

[velvet ridge]

this is #databases, do you need help with databases or smth?

[kindred thunder]

aren't indexes a form of database?

Per google - An index is a database structure that you can use to improve the performance of database activity.

[velvet ridge]

are you using a database?

[kindred thunder]

I think so yeah. Did you see the code I pasted?

('E', 'F', 'G')), (('H', 'I', 'J'), ('K', 'L', 'M')),
(('N', 'O', 'P', 'Q'), ('R', 'S', 'T')),
(('U', 'V', 'W'), ('X', 'Y', 'Z')))``` This was part of it.

[velvet ridge]

that's not really a database, is it?

[normal root]

not a database persay

a database is a structure where data can be stored,taken from and inserted into bassicly

[velvet ridge]

I have a problem that my rate limit system is not limiting properly.. This is my ratelimiter and it's used in __main__.py file. https://github.com/Nipa-Code/lemonAPI/blob/master/lemonapi/limiter.py
there is no any error messages. It limits first 10 requests and after than everything is limited.

[velvet ember]

Guys, I'm making a bot that would create and control the done of daily tasks for all guild users (1 tasks for all, which are updated once at a certain time). For each user in each guild, I need to keep statistics of completed tasks at the moment. Please tell me how it will be better and more efficient to implement this in PostgreSQL (I'm interested in exactly how it is better to store data so as not to create a lot of records). I would be very grateful for your opinion

[proven arrow]

@velvet ember You can just aggregate and count the data for statistics.

[velvet ember]

can you please in more detail? how to aggregate it?

I don't understand a little what this means

[proven arrow]

What are you trying to do? How does this app of yours work?

[velvet ember]

Well, I'll try to explain point by point

1. Once in a certain time (8 | 16 | 24h), the bot updates 12 randomly generated tasks for each guild.
2. These tasks must be completed by guild members
3. You need to somehow save the statistics of completed tasks
   user for each guild.

I do not know how it is better to save a record with the completed tasks of the user

let's say the guild has 5000 members

[proven arrow]

How many people can complete a single task? One or or multiple?

[velvet ember]

Tasks are generated for all members of the guild, but they are performed by each member of the guild separately

[proven arrow]

So then have a 1-M relation, where a user has many tasks.

The tasks table needs to have a column, user_id where you store the identifier of the user completing the task.

[velvet ember]

and if I store it in a separate table this way, it won't take up much space?

I thought somehow to store it in the table for guilds

[proven arrow]

Guilds, tasks, users are all different entities, so why would they be in a single table?

[proven arrow]

databases are designed to store lots of data

[velvet ember]

ok, got it

thanks!

[pine viper]

Hi I'm from Germany

[upbeat marsh]

Hello! I'm working on a project that I need some "data fixtures" in the DB so when I run my tests there's already some data in the DB (like a user, etc..) What would be the right way to do this?

[fading patrol]

I've used Faker for this

[torn sphinx]

FactoryBoy does this as well and I've used it in Django apps.

[upbeat marsh]

I'm more looking for a tool to populate the DB before running the tests not the creation of the data, I know it sounds confusing, I apologise

[upbeat marsh]

never heard of it, I'm looking into it, thanks

[velvet ridge]

I have a problem that my rate limit system is not limiting properly.. This is my ratelimiter and it's used in __main__.py file. https://github.com/Nipa-Code/lemonAPI/blob/master/lemonapi/limiter.py
there is no any error messages. It limits first 10 requests and after than everything is limited.

[proud torrent]

hello, hope everyone is doing fine. I'm currently working on a small project which is essentially a kind of mini trello and I would like some advices on how to improve my models structure (the db architecture). There are 4 main elements to take into account here, a workflow, an activity, a project and a template. A wokflow is a set of activities organized in a certain order, a project represents a customer, a product and a workflow, a template is a set of activities, essentially a workflow with the difference that we are not supposed to change the state of it's activities(to completed or occupied for example), I hope that's understood so far. Currently what I'm doing is adding a boolean flag to my workflow model to specify whether or not it's a template, so when I have to create a new workflow, I create a copy of all the activities, create a new workflow ( is_template = False) and then associate it with the created activities. Do anyone have any suggestions on how to improve this? Thank you

[spring hazel]

Hi, i have a really really weird and annoying issue.
So i had a database in Sqlite and i decided to switch it to Mysql so i can host it elsewhere.
However, It seems that i cant set default value of a VARCHAR, as a VARCHAR?

Here's the screenshot of the syntax and error

[proven arrow]

Filtered words column data type is incorrect format

Should be varchar(n)

[spring hazel]

Oh, so its required?

how do i make it unlimited?

[proven arrow]

Also columns are nullable by default so no need for default null

[spring hazel]

ohhh ok

thanks

how do i make it unlimited though?

i mean how do i allow unlimited characters in VARCHAR

[proven arrow]

Use a different data type like text.

[spring hazel]

is it even in sqlite?

[proven arrow]

But why is it unlimited?

[spring hazel]

ehh, maybe a limit would be good actually lol

[proven arrow]

Yes

[spring hazel]

ohhh

yes, thanks!! it solved my problems!!

also

whats the max value allowed in VARCHAR(n)?

because there are some columns that store entire messages, and i think it would be good to have it as discord's limit (2000 i guess)

nevermind i searched it up

[proven arrow]

Varchar has quite big length. But there are other factors to consider like row size limit.

[spring hazel]

oh is there something like that?!

i'm new to sql sorry

[proven arrow]

You can use text types to overcome such limits.

[spring hazel]

so is it TEXT?

[proven arrow]

You can use text

[velvet ridge]

I have a problem that my rate limit system is not limiting properly.. This is my ratelimiter and it's used in main.py file. https://github.com/Nipa-Code/lemonAPI/blob/master/lemonapi/limiter.py
there is no any error messages. It limits first 10 requests and after than everything is limited. It needs to reset timer somehow and smth else as well but how would I reset the time?

[velvet ember]

Guys, in the SELECT * FROM ... query, "*" is the operand? or what can you call it?

[grim vault]

I would say it's a wildcard/joker/placeholder character.

[upbeat marsh]

I call it everything

[torn sphinx]

select 'all'

or call it asterisk i guess

[empty willow]

hey can anyone help me i lile to kmow how to to import the datas in a dat file to mysql database

[hearty lagoon]

also i have no idea how databases work

[elder elk]

can somebody help with regex format
I have number
1976655
need to covert 19.76655

on postgres

[honest cedar]

what data type should can I use to store HexBytes in postgresql table column

[mossy onyx]

any project idea for with database in it

[oak oyster]

is it possible for me to create a mongodb collection in code using pymongo

[small pasture]

how can i make a database

[torn sphinx]

Command raised an exception: ServerSelectionTimeoutError: localhost:27017: [WinError 10061] No connection could be made because the target machine actively refused it, Timeout: 30s, Topology Description: <TopologyDescription id: 618e9bb02350781bf6b5a1aa, topology_type: Single, servers: [<ServerDescription ('localhost', 27017) server_type: Unknown, rtt: None, error=AutoReconnect('localhost:27017: [WinError 10061] No connection could be made because the target machine actively refused it')>]>
i am very confused, im using mongodb and it was working very well for a while and this happened

[small pasture]

what are you doing

[torn sphinx]

its a discord bot, all its doing is just editing a collection with pymongo

1 sec

[small pasture]

ok

can you make a bot but noe for discor

[torn sphinx]

wdym

[small pasture]

not*

[small pasture]

make a bot

but nor for discord

[torn sphinx]

uh okay that will take a while

[small pasture]

ok can you help me out

[torn sphinx]

okay

[small pasture]

ok where dod we start

do*

[torn sphinx]

1 second i think i might know something

i found a solution, thanks.

[small pasture]

ok

[velvet ridge]

I was thinking of database for my API, what would be a good for it? Would MySQL be good?

[small pasture]

I gusse

[fading patrol]

Yeah basically whatever you want. If SQLite is good enough, that's probably easiest. I also like the free Postgres on Heroku

[velvet ridge]

I was thinking of putting the API to actual good host some day and SQLite might cause data losses because it has everything stored in file. But good to know that it does not matter that badly

[fading patrol]

Postgres is very scalable, Heroku'd Postgres is quite easy to set up and free up to 250mb I think

[velvet ridge]

I'm currently only using local hosting, next summer I will be nearly done with my whole API and I will get a domain + hosting services for it then

[unkempt nest]

just a question.
Is it preferable to use orm instead of plain sql connector?

not sure what's the convention. I just met someone who's insistent in using ORM despite sources saying orm is slower than sql

[olive yew]

Does anyone know of a way to log a connection pool in sqlalchemy when an exception gets thrown?

[proven arrow]

There is no convention. You take into account the experience of the devs on the project with sql, your cost on time and resources and then you decide. You can not use an orm and still have a slow app. Python is slower than some other languages, yet does not mean you stop using it.

[gloomy spindle]

How do I make a row have some sort of expiry time.
Like keep the things only for 30 or 60 days (PostgreSQL)

[unkempt prism]

A simple idea is to have a expiry timestamp field and a cron to come and delete all the expired rows every day.

[gloomy spindle]

yeah that is my approach atm

i guess there isn't a builtin way to do this. then again, this isnt redis lol soooo

¯_(ツ)_/¯

[torn sphinx]

i am trying to update my database using this but the value isnt getting added in the table

c.execute("INSERT INTO testing VALUES ('yehe','boiiii', 69420)")```

[sour nova]

hello i am making database for chatting application and need suggestions

i am thinking about the relationship table

relationship can be of 3 type like friend, pending, or blocked

create table is not exists relationship(
  id serial not null,
  creator_id bigint not null,
  created_for bigint not null,
  rtype int not null, 
  uuid uuid not null default uuid_generate_v4(),
  time timestamptz not null default now(),

  primary key (id, uuid)
);

i need to make it scalable

need suggestions if someone have more ideas like how else can it be

[lofty summit]

not really related but you should avoid using uuid except in cases where you specifically need them, (which does not look the case here since it's not even the primary key)

[pseudo isle]

I'm having trouble thinking about how the relationship is suppose to work for 1 on 1 private message.
You have a user table and a message table. Is that all you need? 1-m relation or is there more to it than that?

[sour nova]

yeah it will be a primary key i forgot to add it in

[sour nova]

it can be private message which is n - 1 and n-n (group) both

i already planed the rooms and conversation structure

i am on the part where people will send friend request and others accept or decline

ping me when someone is here

[feral spruce]

hi, can some please give me some tips about how i can parse value that is inside the table column. I wanna extract the value and pass it to the different table as multiple rows, depending on the number of value present inside the column. Lets say I've some value similar to this:

a:2:{i:0;a:1:{s:13:"_elements";a:2:{i:0;s:2:"20";i:2;a:1:{s:4:"name";s:16:"testing 3";}}}s:9:"undefined";a:1:{s:13:"_elements";a:2:{i:0;a:1:{s:4:"name";s:16:"testing 1";}i:1;a:1:{s:4:"name";s:16:"testing 2";}}}} ```

and do it in mysql

anyhelp would be much appreciated..

I wanna extract the testing 1, testing 2 and testing 3 using mysql

🤕

[subtle wave]

why is the mongodb database on the web sooo slow

It takes long for my react frontend to fetch the data from it

I am using this URL: mongodb+srv://<username>:<password>@cluster0.zybcs.mongodb.net/myFirstDatabase?retryWrites=true&w=majority

[alpine moon]

How can I prevent users from querying something like this?

[torn sphinx]

https://github.com/as-ideas/TransformerTTS i need help js data = self.stream.read(size) File "C:\Users\chary\AppData\Local\Programs\Python\Python39\lib\encodings\cp1252.py", line 23, in decode return codecs.charmap_decode(input,self.errors,decoding_table)[0] UnicodeDecodeError: 'charmap' codec can't decode byte 0x81 in position 2392: character maps to <undefined>

[rapid ginkgo]

What is the correct way to check if a person has an administrator role? Before that, I indicated which role is the administrator

[rapid ginkgo]

Please tell me how to make it transform into a role

[blissful tulip]

Why doesn't it recognize None?

[blissful tulip]

oops, forget it, I already found a way to fix it, good night

[pliant spire]

Hi guys, i'm using sqlite. I'm wondering how to have a CHECK ON CONFLICT REPLACE as a table constraint

CREATE TABLE IF NOT EXISTS {REGION}_entries (
                    customer_id text UNIQUE ON CONFLICT REPLACE,
                    entry_id text UNIQUE,
                    won integer,
                    wait_listed integer,
                    lost integer,
                    FOREIGN KEY(customer_id) REFERENCES accounts(customer_id)
)

This is what my create cmd looks like rn

The logic I want the table to follow is:

I have 3 values (won, wait_listed, lost):

class Entry(NamedTuple):
    customer_id: str
    entry_id: str
    won: bool
    wait_listed: bool
    lost: bool

Only one of these values can be true at any time. If any of the values is true, the rest must be changed to false.

[boreal estuary]

Hello, can someone define 'Datascience; aggregate vs migrate' in simple terms for me? Greatly appreciated in advance.

[weak yoke]

Maybe use an enum

[delicate wind]

Hi can anyone help me im trying to add rows to my DB and it raises the error: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%(emp_no)s, %(Nombre)s, %(Apellido)s, %(Sexo)s, %(Edad)s, %(GT)s)' at line 1

Code:

Add_DNI = ("INSERT INTO DNI_Data "
              "(Nombre, Apellido, Sexo, Edad, GT, Zone) "
              "VALUES (%(emp_no)s, %(Nombre)s, %(Apellido)s, %(Sexo)s, %(Edad)s, %(GT)s)")
Add_DNI = {
       'Nombre': "Adam",
       'Apellido': "James",
       'Sexo': "Masculino",
       'Edad': "24",
       'GT': "GT test"
       
     
     }     
cursor.execute(Add_DNI)
emp_no = cursor.lastrowid

[pliant spire]

Gratz on helper btw

But how would I use an enum here?

[weak yoke]

You'd have a state column that could either be 1 2 or 3

Then map it to a Python IntEnum in your app

[weak yoke]

class State(enum.IntEnum):
    NONE = 0
    WON = 1
    WAIT_LISTED = 2
    LOST = 3

class Entry(NamedTuple):
    customer_id: int
    entry_id: int
    state: State

[fleet ibex]

I'm really curious how this would be created in a db, I just cant see it in my head yet to start templating it.
any advice?

if you saw my post before I deleted it, I found a sql gui.. wow.. made it come together..

[pliant spire]

Ohh, yeah, alright then. I got you, thank you man

[weak yoke]

?

You'll note that bool is currently a cheaky enum on top of an int

Eg 0 is false and >0 is true I think

[tardy robin]

Hi guys,
first time with a NoSQL database I have a question, if I make a XXX collection with some data from a YYY collection, how to organize my data structure if I need to change some data in my collection YYY for it to be reflected in my XXX collection
Choice 1 :
Collection XXX : ```
{'name': 'TOTO', 'country_code': 'FRA', 'country': 'France' etc ...}

Collection YYY : ```
{'name': 'France', 'code': 'FRA' etc ...}

Choice 2 :
Collection XXX : ```
{'name': 'TOTO', 'country_id': 1 etc ...}

Collection YYY : ```
{'id': 1, 'name': 'France', 'code': 'FRA' etc ...}

I think about that because if I have to modify a data in case 1 I will have to change thousands of data while in case 2 only one but which is not in the ideology of NoSQL

So, what's the better choice ?

[hollow copper]

That sounds like something you would use foreign keys with cascading updates in a SQL database to solve but you could just update all the necessary documents in the NoSQL database

Until performance becomes an issue obviously. Dont know what standard NoSQL dbs have to solve something like that. Im sure others do..

[ionic smelt]

how do i use find_one_and_delete in mongodb?
so its an unblacklist command and i wanna erase the user's id along with the blacklist reason from the db (its in one document tho)

[faint blade]

You're missing VALUES

[nimble umbra]

What would be the proper way to store salt and hash into a sql database? I was thinking of using bcrypt

[faint blade]

Bcrypt is a proper way to store passwords yes. Having a bcrypt hash in a database is safe

[nimble umbra]

Is there any proper way to set the fields in the DB itself? Or i can just do Varchar and be done with it

[faint blade]

Depends on your SQL dialect

There could be a special type for it

[torn sphinx]

c.execute(f"UPDATE test SET balance = balance-{amount}, WHERE ID = {player.id}")```

syntax error highlighting "WHERE"
i tried runing the same code in the long form but it was still same error

[frigid glen]

Hey Guys, thought I would share a tutorial I wrote on Pydbantic, hope it makes you lives a little easier:
https://joshjamison.medium.com/an-introduction-to-pydbantic-a-single-model-solution-to-data-verification-storage-254cfe9e757f

[pure bough]

Hey, any idea streaming is supported by asyncpg for postgresql? Using this piece of code I'm waiting for several seconds until I'm able to process first results from it, there is basically no difference If I use stream or execute. I'd like to have my below function formed as async generator

        async with self.db_session() as session:

            stmt = (
                select(MpResultTable)
                .filter(
                    MpResultTable.camera_id == camera_id,
                    MpResultTable.created_at > datetime.fromtimestamp(since / 1000),
                )
                .limit(100)
            )

            mlp_results = await session.stream(stmt)

            async for result in mlp_results:
                yield result.to_dto()

Is anyone using stream with asyncpg successfully? Thanks

[grim vault]

Please use binding parameters (and there is a comma to much before the where):

# sqlite3:
c.execute("UPDATE test SET balance = balance - ? WHERE ID = ?", (amount, player.id))
# other db modules may use:
c.execute("UPDATE test SET balance = balance - %s WHERE ID = %s", (amount, player.id))

(the binding version depends on your used database module)

[heady elk]

What recommend sql libary for raspberry apache?

[torn sphinx]

im a basic python coder how can i write something in my heidisql table

[grizzled wadi]

hey, just wondering if anyone would be interested in an ORM I've created

https://github.com/RobertCraigie/prisma-client-py

It's fully type-safe and has support for auto-completing query arguments

[north pewter]

Unsure what I'm doing wrong with psycopg2 threaded connection pools

I have a context manager that grabs a conn from my pool with pool.getconn() then at exit, does pool.putconn(conn)

Sometimes, my putconn call is failing with the error "trying to put an unkeyed connection"

If anyone responds, ping me

[rapid ginkgo]

How to check if a collection is empty or not? (mongodb)

[pseudo isle]

I love using Prisma in JS projects, and soon enough their Go client should be ready as well! Great job making this available for Python.

[faint hill]

Need a db for discord bots

any suggestions

[solar hearth]

Whats the best practise to use .execute() with parameters? The best practise?

[slender atlas]

To avoid injections

[slender atlas]

Any asynchronous one will do

[faint hill]

like?

[slender atlas]

aiosqlite for SQLite or asyncpg for PostgreSQL

These are the only two I know of

[faint hill]

ok

ty

i will use aiosqlite

[grizzled wadi]

Thank you!

[grizzled wadi]

You could also use https://github.com/RobertCraigie/prisma-client-py for asynchronous SQLite access

[faint hill]

ok

[past pivot]

Hey, folks.
Got a question on MS SQL.. Could be views indexed ? Or Views only using indexes from original table.

[shadow oracle]

I already know MySQL then do I need to know Postgres in order to use it or MySQL commands work in postgres?

[shell ocean]

there are some syntactic differences

[faint blade]

In general you don't want to do that, index the things that the view queries

[shadow oracle]

other than that?

[shell ocean]

well, there are some functionality differences too

of course

so it depends on what you want to do

[sacred lotus]

help

Which db i can use for large scale banking application like gold loan, Daily loan, Monthly loan and also full fledged for multi logins and easily able to handle large data smoothly

[zinc scroll]

which database is easier?

[fading patrol]

Have you looked at SQLite?

[grand lantern]

in pymongo, is Collection.find_one_and_update atomic?

the mongodb docs say all single-document write operations are atomic, but this method seems like a read operation followed by a write operation

and i can't find anything that says one way or the other

[willow mesa]

where can i find information im new sorry

[harsh pulsar]

!resources information about what exactly? you can ask questions about this server in #community-meta. you might also want to read #❓｜how-to-get-help. for information about learning python, see our "resources" page 👇

[delicate field]

Resources

The Resources page on our website contains a list of hand-selected learning resources that we regularly recommend to both beginners and experts.

https://pythondiscord.com/resources

[willow mesa]

thanks dude

[harsh pulsar]

it looks like it uses a mongo command called findAndModify https://github.com/mongodb/mongo-python-driver/blob/c7d80802be949f951e09bde08baffc93909f0aa0/pymongo/collection.py#L2236-L2238

[delicate field]

pymongo/collection.py lines 2236 to 2238

cmd = SON([("findAndModify", self.__name),
           ("query", filter),
           ("new", return_document)])```

[harsh pulsar]

https://docs.mongodb.com/manual/reference/method/db.collection.findAndModify/ it does appear to be an atomic update, but only atomic for each document:

When modifying a single document, both db.collection.findAndModify() and the update() method atomically update the document.

[grand lantern]

what I'm worried about is the atomicity of the read-write combo

If all findOneAndUpdate() operations finish the query phase before any client successfully inserts data, and there is no unique index on the name field, each findOneAndUpdate() operation may result in an insert, creating multiple documents with name: Andy.
and this makes me think it might not be

[harsh pulsar]

oh, i see

yuck

big yuck

that's a good one to be careful of

[grand lantern]

specifically I'm worried about two findAndModify operations, where they both return the document pre-update
whereas I'd want to guarantee that one of them returns the document after the other's update

I have a unique index I can use in the query though

[harsh pulsar]

yeah i have no idea. let me know if you figure it out though, could be an issue in something i am doing for work

[grand lantern]

according to MongoDB in Action 2nd Ed. Chapter 7.3

Every update in MongoDB is atomic, but the difference with findAndModify is that it also atomically returns the document to you.

which I guess is good enough for me

[harsh pulsar]

thanks, good find

[grand lantern]

just realized I don't have a unique index to use

i might look into creating a unique compound index

[livid crypt]

Hi I'm trying to plot a binned scatter plot with a particular bin size/interval I also want to find the mean in each bin but it's not binning properly. any help is appreciated

[latent bone]

https://i.imgur.com/27Ka1bB.png

does anyone know what this is

[ionic smelt]

how do i delete all documents in a collection in mongodb in a command

what would the attribute be for that, e.g. to find its myCollection.find_one() so to delete all docs what would it be

[fading egret]

I've never used databases before but was looking to use one for my software design project for school.
I'm developing a chat app and need some form of database to store user data and messages. I was considering some setup like this:

Users

• ID
• Username

Chat History

• User1ID
• User2ID
• Messages

But I really don't know how to setup an efficient database that'd be easy to query

any help is appreciated, I'd really like a nudge in the right direction :)

[wise nimbus]

Dear @fading egret, based on your need for a "database", I would recommend using SQLite. Python has native standard library support for SQLite database reads and writes.

SQLite3 python module docs: https://docs.python.org/3/library/sqlite3.html
SQLite website: https://sqlite.org/index.html

Also, SQLite is highly embeddable and might already come installed on your computer

https://sqlite.org/aff_short.html

[fading egret]

Ah thanks! I'll look into it

[torn sphinx]

hey guys i have a problem. when i want to execute something in my query , i see this error in line 34 Traceback (most recent call last): File "f:\Desktop\test.py", line 34, in <module> cursor.execute("""UPDATE `accounts` SET `password` = 2""") sqlite3.OperationalError: no such table: accounts and it is my code```import mysql.connector
from colored import fg
import time
from playsound import playsound
import pymsgbox
import sqlite3
from sqlite3 import Error

blue = fg('blue')
red = fg('red')
green = fg('green')
yellow = fg('yellow')
white = fg('white')

try:
sqliteConnection = sqlite3.connect("gfg.db")
print(green + "Database Connected")
except Error as e:
print(red + "Error : " + str(e))

cursor = sqliteConnection.cursor()
User = {
"card_password" : ''
}

def wait(s):
time.sleep(s)

if User['card_password'] == '':
New_Password = input(white + "Enter A New Password : ")
cursor.execute("INSERT accounts SET password = " + str(New_Password) +"")

else:
Password = input(white + "Enter Your Password : ")

if len(Password) < 3:
print(red + "Problem")

if Password == User['card_password']:
print(green + "You Signed In")
else:
print(red + "Wrong Password")
wait(1)
exit()

print(white)

[nimble umbra]

What could be the reason that a SQL query enters 0000-00-00 as a date when i use the getdate() function?

date = 'GETDATE()'
INSERT INTO users (name, username, email, salt, hash, creation_date) values (%s,%s,%s,%s,%s,%s)
values = (name, username, email, salt, hashed, date)```
Everything else gets in fine
tried both lowercase and uppercase in getdate, didnt change much
data type of the field is date

[cerulean imp]

Hi! I am trying to find collection in my mongo db

But I am facing this error

line 38, in _raise_invalid_id
    raise InvalidId(
bson.errors.InvalidId: '851756211841925141' is not a valid ObjectId, it must be a 12-byte input or a
24-character hex string

My code

    author_id = message.author.id
    id = str(author_id) 
    objInstance = ObjectId(id)
    collection.find_one({"_id": ObjectId(id)})

[harsh pulsar]

why the ''? it probably thinks date is a string...

[nimble umbra]

Im using it through python

[harsh pulsar]

well yeah that won't work

[nimble umbra]

if i remove the ' ' it treats it like a python function 😦

[harsh pulsar]

that's the whole point of parameterized queries

the parameters are treated as data, for sure, 100%

never as "code"

whereas if you did the escaping all by hand, they might be

[nimble umbra]

What do you mean escaping by hand?

[harsh pulsar]

you'd have to do this:

query = '''
INSERT INTO users (name, username, email, salt, hash, creation_date)
VALUES (%s, %s, %s, %s, %s, getdate())
'''
cursor.execute(query, params)

[harsh pulsar]

if you used an f-string, and tried to put quotes around it or something

which is like the #1 thing you're not supposed to do in sql

[nimble umbra]

Ugh let me try to put getdate() instead of %s

[harsh pulsar]

i hope it's clear that %s can only ever be a placeholder for a string and not for "sql code"

that's by design

otherwise there'd be no point of parameterizing queries, and you'd be open to sql injection attacks/bugs with no recourse

[nimble umbra]

if i do it like sql sql = 'INSERT INTO users (name, username, email, salt, hash, creation_date) values (%s,%s,%s,%s,%s, getdate()' values = (name, username, email, salt, hashed)
I get ValueError: Could not process parameters

[torn sphinx]

Hello, anyone know an easy way to check if a variable/value is in my database? with an if/else condition?
something like this: ```py
if "SELECT * FROM database WHERE Ordernummer IN number_list:
bla bla

else:
Not bla bla```

I cant quite get it right

[harsh pulsar]

missing a trailing )

[harsh pulsar]

can you be more specific?

[nimble umbra]

Yeah sorry i pasted it wrong, same thing even with properly closed parenthesis

[torn sphinx]

number_list = [1,2,3,4]
I want to check if these numbers are in my database, if they are the code stops, if they are not I run the rest of my code and add them to the database(which already works)

If the values in the list is in the database, code stop

[harsh pulsar]

that's weird. unfortunately i don't have a copy of sql server i can test with

[harsh pulsar]

in the database how? what does it mean for a number to be "in" your database?

[nimble umbra]

No worries, thanks for trying to help!

[torn sphinx]

if the values exist in the specific column in the database

numbers_tuple = tuple(test_list)
    query = "SELECT * FROM database WHERE Numbers IN {};".format(numbers_tuple)```
This works, but its the other way around

I need to switch it around lol

basically

if numbers_tuple in Numbers

understand? @harsh pulsar

[harsh pulsar]

what do you mean "other way around"? also using string interpolation for sql is usually not a good idea

so you have a column x in the table table1, and you want to know if any row has x equal to any of the values in test_list?

[torn sphinx]

hey guys i have a problem. when i want to execute something in my query , i see this error in line 34

  File "f:\Desktop\test.py", line 34, in <module>
    cursor.execute("""UPDATE `accounts` SET `password` = 2""")
sqlite3.OperationalError: no such table: accounts```
 and it is my code
```import mysql.connector
from colored import fg
import time
from playsound import playsound
import pymsgbox
import sqlite3
from sqlite3 import Error

blue = fg('blue')
red = fg('red')
green = fg('green')
yellow = fg('yellow')
white = fg('white')



try:
  sqliteConnection = sqlite3.connect("gfg.db")
  print(green + "Database Connected")
except Error as e:
  print(red + "Error : " + str(e))
  
cursor = sqliteConnection.cursor()
User = {
  "card_password" : ''
}


def wait(s):
  time.sleep(s)

if User['card_password'] == '':
  New_Password = input(white + "Enter A New Password : ")
  cursor.execute("UPDATE  `accounts` SET `password` = " + str(New_Password) +"")
  
else:
  Password = input(white + "Enter Your Password : ")

if len(Password) < 3:
  print(red + "Problem")

if Password == User['card_password']:
  print(green + "You Signed In")
else:
  print(red + "Wrong Password")
  wait(1)
  exit()

print(white)```
 pls help me

[harsh pulsar]

@torn sphinx it would be safer to do something like constructing a tuple of placeholders for query parameters, instead of constructing the query with data directly in it:

def make_sql_tuple(size):
    """Construct a string like '(%s,%s,%s)'."""
    placeholders = ','.join('%s' for _ in range(size))
    return '(' + placeholders + ')'

def check_in_table1_x(test_list):
    """Check if any value in `test_list` is in `table1.x`."""
    query_tuple = make_sql_tuple(len(test_list))
    query = f'SELECT 1 FROM table1 WHERE x IN {query_tuple}'
    cursor = db.execute(query, test_list)
    return cursor.fetchone() is not None

[reef monolith]

How can I store the my tables and databases in a file
which will be in the main directory of my project

Since I am working with Mysql

[solar hearth]

Should I be using SQLAlchemy when working with a Flask project and a SQLite database? Or can I use the sqlite3 library that comes with Python?

[shell osprey]

So, I have a large amount of data that I am trying to save. Multiple GBs, can't load it in RAM. Will append chunks to an SQL database. Now my question is, sqlite being an in memory database I will have the same problem wouldn't I? What should I use instead?

Using Flask btw

[fading patrol]

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

[fading patrol]

I would generally say Alchemy is worth it but it's certainly possible to do it with SQLite directly if that's what your comfortable with and your project isn't too complex

[harsh pulsar]

sqlite is not in-memory unless you create an in-memory database.

a sqlite database is a file on your disk

[glossy pond]

Hello everyone, I would just like to ask a bit of advice with regards to using database in python. I am working to create an online game that uses a flask web server. I want to use a database on the back end for to keep track of everything (how much cash each player has etc.) and have been trying to use sqlalchemy for this but I keep getting errors like this when too many requests come in at once sqlalchemy.exc.InvalidRequestError: This session is in 'prepared' state; no further SQL can be emitted within this transaction. Is sqlalchemy even to best tool for the job? Any idea how I can add support for queuing up operations?

[shell osprey]

So... it's alright if I go with sqlite for storing 15 or so GB of data?

It won't try to load the entire 15GB data into RAM?

[torn sphinx]

how can i add text to a column?

[solar hearth]

Okay thanks for the answer. Which one is most secure you think? 🙂

[naive wing]

how you find a sql injection vulnerability on a network without a server?

[fading patrol]

That doesn't sound like a SQL Alchemy problem per se. Are you using SQLite? If so, don't

[fading patrol]

Not sure it matters as long as you do it right

[glossy pond]

Thank you for getting back to me, I am not using a sqlite database but a mariadb one instead - is that OK?

[fading patrol]

Yeah, that should be. You might need someone more advanced than I am to look at your code

[harsh pulsar]

no, that's the whole point of a database. although 15 gb is kind of big and you might want to consider a "real" database like postgres, depending on what you're actually doing with this data.

[dense barn]

something1 = await self.client.pg_db.fetch("SELECT card_name,card_id,card_url,card_rarity,card_set_name FROM cardsclone WHERE card_rarity = 'Rare Holo' AND card_set_name = 'Base'")
            print(something1)
            something3 = random.choice(something1)

ok so im fetching these things from my db and its 100% not empty, but whenever it tries to pick a random thing from the list it says "list index is out of range", anyone know why?

@ me if you are answering

[fading patrol]

What does type(something1) tell you?

[dense barn]

type(something1)?

<class 'list'>

@fading patrol

[fading patrol]

Ok, and the print statement shows items in the list, but the last line defining something3 with random is what's throwing the index error (not some other line somewhere in your code)? If that all checks out, I'm stumped

[dense barn]

yes

well, thats atleast what it shows in the error

that line defining something3

[fading patrol]

Sorry, that's all I got

[dense barn]

dam

thanks anyway

[dense barn]

anyone?

[civic oracle]

eyyyy there

im having some issues in my code

im relating sqlite w python

the program works very good but im having an issue w the conections to the db

is there anyone that can give me a hand?

im stupid af , i forget about the .db

[fleet knoll]

Greeting,
Is there a way to speed up writing to the database?
example:
I have a parquet file and it takes me 70-75 seconds to write it, which is not much, but if I had a bigger file, it would take longer.
I use FastAPI, ArangoDB, docker and pandas

[oak oyster]

How do I update a document in mongodb by using the document? EDIT: found a way

[signal lintel]

i need help w this pymongo thingy in repl.it

[burnt turret]

The error tells you what to do, install dnspython

[signal lintel]

nvm fixed

[reef monolith]

are you using replit console?

[signal lintel]

i fixed it

[reef monolith]

pip commands are runned in terminal

[signal lintel]

ye

[delicate field]

Hey @signal lintel!

Uh-oh! It looks like your message got zapped by our spam filter. We currently don't allow .txt attachments, so here are some tips to help you travel safely:

• If you attempted to send a message longer than 2000 characters, try shortening your message to fit within the character limit or use a pasting service (see below)

• If you tried to show someone your code, you can use codeblocks
(run !code-blocks in #bot-commands for more information) or use a pasting service like:

https://paste.pythondiscord.com

[signal lintel]

im having errors now

@reef monolith can you help me

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @torn sphinx until <t:1637249613:f> (9 minutes and 58 seconds) (reason: duplicates rule: sent 4 duplicated messages in 10s).

[torn sphinx]

can somebody help meeeeeeeeee i have problem in sql. i cant execute something in sql

@signal lintel

@reef monolith

so nobody know's??

[fading patrol]

Why not just ask your question instead of pinging random people? If it's complex, grab a help channel per #❓｜how-to-get-help

[agile blade]

Hi Expers, I am trying to connect to an Oracle Database using a Python script(.py file), I have installed cx_Oracle in my Mac OS, the Python script has a simple connection to a database on cloud, please note I have no Oracle client installed in my system due to restrictions at my Org. I am getting error "x_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "dlopen(libclntsh.dylib, 1): image not found". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help", please note I have already done "pip install jaydebeapi" and "pip install jpype" steps. Any help would be highly appreciated

[mossy hornet]

Hello ,
I want to free space in Mongodb by deleting the mongod.log file. I no longer need the previous logs.

If i delete the file the file will be hold by the mongod process ?

Can i trucate to size 0 instead of deleting file. please suggest some steps.

[torn sphinx]

let me ask a question

is better to have 2 VPS clustered ( for exmaple 20$ ) or a VPS of 40-50$

Preformance matter

for a better understanding

2 VPS of $12/mo $0.01786/hr Memory 2GB vCPUs 1 Intel CPU SSD 50GB NVMe SSDs Transfer 2TB

or 1 $24/mo $0.03571/hr Memory 4GB vCPUs 2 Intel CPUs SSD 80GB NVMe SSDs Transfer 4TB

MariaDB cluster

[torn sphinx]

echo '' >> file

[ionic smelt]

^^^^^^

[dawn moss]

I am having a confusion with postgres sql

People say to do DEFAULT 0 NOT NULL because they say Default should be kept before

But for me NOT NULL DEFAULT 0 works

[sterile pelican]

How to put bytes to sqlite3 db in python

BLOB type?

[grim vault]

The order of column constraints doesn't matter. I personally also write not null before the default constraint.

[dawn moss]

👍

[harsh pulsar]

Yes, a "blob" is a cute name for "bytes with unknown/arbitrary format"

[foggy owl]

  File "/home/container/main.py", line 188, in <module>
    bot.loop.run_until_complete(create_db_pool())
.............
.............
Task was destroyed but it is pending!
task: <Task pending name='Task-1' coro=<ch_pr() running at /home/container/main.py:171> wait_for=<Future pending cb=[<TaskWakeupMethWrapper object at 0x7f732d10e1c0>()]>>```

I have no clue what this error means

async def create_db_pool():
    bot.db = await asyncpg.create_pool(database = DBNAME, user = DBUSER, password = DBPASS)
    print("Connection successful to database.")```
this is the function

[harsh pulsar]

@foggy owl ask in #async-and-concurrency

[sharp void]

In Python to MySQL, how do you find a row in a table where, for example, only the first field contains data ('t','','','','','','','')
Trying to use a select statement with "where field = ''" isn't working, and only inputting "where field = NULL" is working. because i search using user-input values, I don't quite know how to convert a user-input '' to a NULL

[foggy owl]

Alright!

[scenic gale]

anyone know if you can delete entries in mongodb based on if an entry contains a certain letter, etc?

[torn sphinx]

something like this, did this quickly so it could have errors. but using a case expression should work

mysql> select * from userinput;
+-------+--------------+
| input | anotherinput |
+-------+--------------+
|       | hello        |
| hello |              |
|       | hello        |
|       |              |
| hello | hello        |
+-------+--------------+
5 rows in set (0.00 sec)

mysql> select case when input != '' then 'input' when anotherinput != '' then 'anotherinput' else null end as foundit from userinput;
+--------------+
| foundit      |
+--------------+
| anotherinput |
| input        |
| anotherinput |
| NULL         |
| input        |
+--------------+
5 rows in set (0.00 sec)

[sharp void]

Oh i forgot to delete my thing... I managed to bypass the issue by adding a primary key to the table and using it as an identifier in queries instead of strictly what the csv file had.

Thank you though!

[leaden lake]

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

[arctic granite]

Why isn't this SQL code working? I accidentally created my customer names with the data type char.

ALTER TABLE Mine
ALTER column CustomerName varchar(50);

[fading patrol]

Give us something to work with here... What database, are you getting an error, is it used by a foreign key on another table, etc.

[arctic granite]

@fading patrol I went ahead an recreated the table to be correct but now I am just getting a syntax error when I try to do and INSERT statement into that table. My code:

INSERT INTO Mine (CustomerName, Contact Name, Address, City, PostalCode, Country)
VALUES (COMP 185 Databases, John Bruh, 63457 Gin road, Ginter, 57345, United States);

[fading patrol]

Your strings are missing quotes

[arctic granite]

My data needs quotes?

[fading patrol]

'John Bruh' for example

Anything that's not numerical basically

[arctic granite]

OMG I totally forgot about that

Been stressing over my web design project lately

[rustic geyser]

I'm using sqlite3 & something seems very wrong with the database I got very unexpected value today, the database is for my server it's an analytics bot which updates database whenever a message is sent and whenever someone joins and using a command i reset the value everyday and store it using which i also make a graph, after reseting the value yesterday night everything was fine and message sending count was like 600 and today its like 300 it reduced on its own the reset command is blocked by an if statement & can only be ran by me i hv no idea how the value got reduced when i re-started the bot again the value went to 0!!! and after sending some msgs value went up to like 20 and when i rebooted again it was fine then how come it go to 0 when rebooted a while ago

the value is no longer going to 0 when rebooted and its working as expected then at that time how did it go to 0 when rebooted i checked the code that runs whenever bot is online and it's not supposed to reset value

i have 3 table and same issue with each of them

[vale helm]

hi

can somebody help me with this query?

    SELECT *
        , DESTINATION_AIRPORT
    FROM (
    SELECT COUNT(SUM(FLIGHTRS-CANCELLED)AS TOTAL
    FROM flights
    ) AS results
    GROUP BY DESTINATION_AIRPORT
    ORDER BY TOTAL DESC;
""")```

but i get the following error

mismatched input 'AS' expecting {')', ','}(line 5, pos 40)

[brazen charm]

you open a ( onCOUNT( but never close it

COUNT(SUM(FLIGHTRS-CANCELLED)) AS TOTAL
--^

[vale helm]

have to retrieve how many flights each destination gets and show the first 5 in descendent order. YEAR: string (nullable = true) |-- MONTH: string (nullable = true) |-- DAY: string (nullable = true) |-- DAY_OF_WEEK: string (nullable = true) |-- AIRLINE: string (nullable = true) |-- FLIGHT_NUMBER: string (nullable = true) |-- TAIL_NUMBER: string (nullable = true) |-- ORIGIN_AIRPORT: string (nullable = true) |-- DESTINATION_AIRPORT: string (nullable = true) |-- SCHEDULED_DEPARTURE: string (nullable = true) |-- DEPARTURE_TIME: string (nullable = true) |-- DEPARTURE_DELAY: string (nullable = true) |-- TAXI_OUT: string (nullable = true) |-- WHEELS_OFF: string (nullable = true) |-- SCHEDULED_TIME: string (nullable = true) |-- ELAPSED_TIME: string (nullable = true) |-- AIR_TIME: string (nullable = true) |-- DISTANCE: string (nullable = true) |-- WHEELS_ON: string (nullable = true) |-- TAXI_IN: string (nullable = true) |-- SCHEDULED_ARRIVAL: string (nullable = true) |-- ARRIVAL_TIME: string (nullable = true) |-- ARRIVAL_DELAY: string (nullable = true) |-- DIVERTED: string (nullable = true) |-- CANCELLED: string (nullable = true) |-- CANCELLATION_REASON: string (nullable = true) |-- AIR_SYSTEM_DELAY: string (nullable = true) |-- SECURITY_DELAY: string (nullable = true) |-- AIRLINE_DELAY: string (nullable = true) |-- LATE_AIRCRAFT_DELAY: string (nullable = true) |-- WEATHER_DELAY: string (nullable = true) this is the database

do you know a way to retrieve a count of all the flights?

[ionic smelt]

^^^^^^^^^^^^^^^^^^^^^^^^^^^6

[ionic smelt]

bumping this again

[random bobcat]

my error for trying to connect to mongo db```Traceback (most recent call last):
File "main.py", line 34, in <module>
db1.create_collection("Economy")
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymongo/database.py", line 417, in create_collection
with self.__client._tmp_session(session) as s:
File "/usr/lib/python3.8/contextlib.py", line 113, in enter
return next(self.gen)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1948, in _tmp_session
s = self._ensure_session(session)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1935, in _ensure_session
return self.__start_session(True, causal_consistency=False)
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1883, in __start_session
server_session = self._get_server_session()
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1921, in _get_server_session
return self._topology.get_server_session()
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymongo/topology.py", line 520, in get_server_session
session_timeout = self._check_session_support()
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/pymongo/topology.py", line 504, in _check_session_support
self._select_servers_loop(

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

;-;

[floral thistle]

Can someone recommend a ER diagram tool?

[fading patrol]

Draw.io is pretty good if you're doing it by hand and want something free

But there's code-based stuff that will generate them for you like PGadmin and others

Here's a pretty good overview of the different kinds of tools... If you know which approach you want that helps narrow things down https://dataedo.com/blog/different-categories-of-tools-you-can-use-to-create-an-erd-for-existing-database

[harsh pulsar]

i have used this https://editor.ponyorm.com/

[cyan crow]

how do i remove columns in sqlite?

[empty vale]

        code = self.regcode.text()
        if len(code) == 0:
            self.error.setText("The field is empty.")
        else:
            con = pymysql.connect(host="localhost", user="root", passwd="", db="dbcodes")
            cur = con.cursor()
            query = 'SELECT Codes FROM mycodes WHERE Codes ='
            cur.execute(query)
            result_code = cur.fetchone()[0]
            if code == result_code:
                reg = RegScreen()
                widget.addWidget(reg)
                widget.setCurrentIndex(widget.currentIndex() + 1)
                print("Valid code.")
                print("no")
            else:
                self.error.setText("Invalid code.")```
this code doesnt seems to work. All i want is to compare my string  ````code ```` to the rows of column Codes  but seems the output is its getting only the first row of the column and cant read the rest

[weak yoke]

https://www.sqlite.org/lang_altertable.html

DROP COLUMN

[weak yoke]

You might prefer to use a migration framework like alembic

[static zealot]

Hi, I have a dictionary of key and value pair. Need to check if key is already part of db, if present update the key if not need to insert it. what would be the better way to doing it?

BTW I am speaking about sql

is it better to use "IF Exists" or separate those keys which needs to be inserted and updated?

[inland sparrow]

Are you interacting with your SQL DB via Python or do you need to this directly in SQL?

[potent sentinel]

Do you guys know why program is failing to find my csv file?

[inland sparrow]

You need the full path to the csv

[west pelican]

I'm getting a pymongo.errors.ServerSelectionTimeoutError: error whenever I try to post data into my db

[gritty harness]

Im asking here because I dont need a quick response, but anyone know how to update a file in json like this:

{
  name: "john doe",
  name2: "jane doe"
}

into something like this:

{
  name: "john cena",
  name2: "jane doe"
}

[shell ocean]

don't worry about it

it's just practice

[rough hearth]

@harsh pulsar what makes mysql more "big boy" than sqlite?

(continuing a discussion from #data-science-and-ml)

[shell ocean]

scalability, I guess?

[true swift]

I agree

For web projects, SQLite is ok. But if you want to create a complete project, hosted in the cloud and with APIs, a "complete" database engine is better

[fierce sierra]

    @commands.Cog.listener()
    async def on_message(self, message):
        try:
            guild =  message.guild

            limit1 = db14.find_one({"guild_id": guild.id})[(int('limit'))]
            check = db14.find_one({"guild_id": guild.id})['antimassmention']

is this the proper way of retrieving an int? It isn't working

[harsh pulsar]

it's an actual server application that requires "care and feeding", and generally has more features to learn about

sqlite does not really require a lot of "administration" as such

[fading field]

türk olan var mı acaba

[frank cloak]

anyone willing to help me setup access to psql via remote?

🤔 yes

I have never done this before, so could I get some help?

[gloomy spindle]

Hello im trying to use a LIKE statement wit postgre that accepts user input:
.fetch("SELECT x, z, description FROM coords WHERE description LIKE $1%", 's')
but that of course gives an error, how would I do this with proper sanitization.
basically a .startswith(user_input) but psql

[frank cloak]

pretty sure psql

uses $1

for santzination

[frank cloak]

psql uses $1 for sql

[gloomy spindle]

ik

[frank cloak]

then for the next variable $2

[gloomy spindle]

ikikik

[frank cloak]

ok

[gloomy spindle]

look at my sample query

that doesnt work

[frank cloak]

look

[frank cloak]

you put a %1 here

not a $1

[gloomy spindle]

% is a wildcard

for the LIKE thing

[frank cloak]

🤔 even for a variable?

[gloomy spindle]

but i need user input

[frank cloak]

pretty sure you use $1 to get variables working