databases

[stark gust]

people who don't have a database usually pick one that exists and use that...

like SQL or Mongo or something

[agile flame]

maybe I'm using the wrong term. I'm not going to make up my own datbase language

[stark gust]

so what are you trying to do?

[mint wharf]

Using psycopg2 for interacting with a postgresql database to replace my SQLite database in my app. Is there a way to get the values returned to return in a form similar to SQLite? Where I can specifically refer to columns like a dictionary?

[agile flame]

Make an inventory database. Is it not clear what that means? I can go into the weeds 🙂

[stark gust]

you want to make an inventory database
otherwise known as a plain simple database
and you don't want to make up your own database language
but you aren't using anything that exists either

no, it is not clear what you mean

[agile flame]

I haven't chosen yet.
I've been practicing using my algo on a fake pandas dataframe I generated.
Currently I am

1. Looking for advice on how to structure it
2. Open to suggestions

[stark gust]

I can't get "620 BC" to turn into a valid timestamp, please help me out if you can

[stark gust]

have you ever used a database yet?

[agile flame]

Great question! No I have not

[stark gust]

but you already have a ton of assumptions about how you should do things, without knowing what a database's own requirements might be... sounds like you're trying to feed this cat through its ass

take a peek at Mongo for example. you are basically wasting your time with what you do now if you'd end up using Mongo
and your modeling of the schema could be easily done through Python and SQLAlchemy while you're thinking about it, and then if you chose to do SQL you already have a finished model you can use

I mean that's my opinion

[grim vault]

I thought you solved it with 4 digit year: to_timestamp('0620 BC', 'YYYY BC')

[stark gust]

it threw the same error with that too
then I tried with a 3-digit YYY marker, that didn't work either

[agile flame]

Well I value your opinion! I don't think I've made any assumptions yet. I can get my algo to work on a fake pandas table, but now I want to move that into a database. I was asking for help on this.
I will

1. Look at Mongo (overhwlemed with all the choices though. there are like a dozen options)
2. Look into "model schema"

[stark gust]

psycopg2.errors.InvalidDatetimeFormat: invalid input syntax for type date: "620"
LINE 1: ...INTO birth (date, approximate, id) VALUES ('620', tru...

[grim vault]

select to_timestamp('0620 BC', 'YYYY BC') -> -000619-01-01T00:00:00.000Z

[agile flame]

What do you mean by "if you choose to do SQL you already have a finished model you can use"?

an inventory database seems so common that I thought there might be some guides or boilerplate stuff out there. Anyway, I think I have enough to move forward.

[stark gust]

I don't get it. it didn't work for me. let me try this again

[agile flame]

Actually, my company uses Azure for stuff. Any reason you would shoot down using Azure?

[stark gust]

I don't know anything about Azure

[stark gust]

it seems that to_timestamp doesn't convert the year only values into timestamp, they are passed as strings

oh no

I'm so stupid

var1 = string value
var2 = func.to_timestamp(var1)
which do I try to add to the database? var1 of course

well, this was a fun hour or so completely wasted

I didn't realize from the error messages that it was writing out a string, not a timestamp...

[torn sphinx]

I have a question

Is it possible to have the user of a program enter something in the output and have that text inputted change a variable of a list

So like I have a list and one item in it is A and then the user inputs B and it changes A to B

Is that possible and if so how do I do it

[stark gust]

by "enter something in the output" you mean provide an input?

the user input is like any other string value, you can do with it whatever you can with any other string
including adding it to a list
you can replace a list's element by value or by index
not sure why you're asking this in #databases though

pgAdmin 4 appears to be an especially terrible software. or does it work fine and fast for others?

it reminds me of the iTunes Windows app user experience

what's the quickest way to get the autogenerated id of the latest inserted row from an SQL table?

[fading patrol]

The little bit that I've used it, seemed OK, but I mostly use DBeaver

[fading patrol]

https://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id

RETURNING is the way to go

[stark gust]

thanks. I too like when the values just magically return 🙂

[novel hinge]

Does indexing a column help in query optimization? also how to know which column to index on

[stark gust]

you index the column that you want to search in, and yes, the point of the indexing is to make querying faster

like MUCH faster

[sturdy wyvern]

Hey guys....super noob question. Is there an advantage to breaking up a simple Python script in an orchestration tool like LUIGI vs. just scheduling the Python script to run?

[torn sphinx]

        await ctx.send("Updated DB")
        await cursor.execute('UPDATE users SET id = ? WHERE ad = ?, AND guild = ?, AND welcomechannel = ?, AND announcechannel = ?, AND welcomemessage = ?, AND leavemessage = ?, AND adchannel = ?', (member.id, '', ctx.guild.id, 0, 0, '', '', 0))

Traceback (most recent call last):
  File "/home/runner/cross-ad-bot/venv/lib/python3.8/site-packages/discord/ext/commands/core.py", line 229, in wrapped
    ret = await coro(*args, **kwargs)
  File "main.py", line 140, in setup
    await cursor.execute('UPDATE users SET id = ? WHERE ad = ?, AND guild = ?, AND welcomechannel = ?, AND announcechannel = ?, AND welcomemessage = ?, AND leavemessage = ?, AND adchannel = ?', (member.id, '', ctx.guild.id, 0, 0, '', '', 0))
  File "/home/runner/cross-ad-bot/venv/lib/python3.8/site-packages/aiosqlite/cursor.py", line 37, in execute
    await self._execute(self._cursor.execute, sql, parameters)
  File "/home/runner/cross-ad-bot/venv/lib/python3.8/site-packages/aiosqlite/cursor.py", line 31, in _execute
    return await self._conn._execute(fn, *args, **kwargs)
  File "/home/runner/cross-ad-bot/venv/lib/python3.8/site-packages/aiosqlite/core.py", line 129, in _execute
    return await future
  File "/home/runner/cross-ad-bot/venv/lib/python3.8/site-packages/aiosqlite/core.py", line 102, in run
    result = function()
sqlite3.OperationalError: near ",": syntax error

anyone know why I keep getting this error?

I have no idea whats wrong

code is above

[storm magnet]

Hello, new coder here

Would it be able to get some help with exporting data scraped to a text or csv file?

Its just showing up in powershell but I cant get it to go anywhere else

I know im close but dont know what im missing

[torn sphinx]

          await cursor.execute("SELECT adchannel FROM users WHERE guild = ?", (ctx.guild.id,))
          result = await cursor.fetchone()
          if result is None:
            sql = ("INSERT INTO users(guild, adchannel), values(?, ?)")
            val = (ctx.guild.id, channel2)
            await ctx.send(f"Ad Channel has been set to {channel}")
          elif result is not None:
            sql = ("UPDATE users SET adchannel = ? WHERE guild = ?")
            val = (channel2, ctx.guild.id)
            await ctx.send(f"Ad Channel has been updated to {channel}")
          await cursor.execute(sql, val)
          await db.commit()    

[torn sphinx]

Traceback (most recent call last):
  File "/home/runner/cross-ad-bot/venv/lib/python3.8/site-packages/discord/ext/commands/core.py", line 229, in wrapped
    ret = await coro(*args, **kwargs)
  File "main.py", line 223, in adchannel
    await cursor.execute(sql, val)
  File "/home/runner/cross-ad-bot/venv/lib/python3.8/site-packages/aiosqlite/cursor.py", line 37, in execute
    await self._execute(self._cursor.execute, sql, parameters)
  File "/home/runner/cross-ad-bot/venv/lib/python3.8/site-packages/aiosqlite/cursor.py", line 31, in _execute
    return await self._conn._execute(fn, *args, **kwargs)
  File "/home/runner/cross-ad-bot/venv/lib/python3.8/site-packages/aiosqlite/core.py", line 129, in _execute
    return await future
  File "/home/runner/cross-ad-bot/venv/lib/python3.8/site-packages/aiosqlite/core.py", line 102, in run
    result = function()
sqlite3.OperationalError: near ",": syntax error

@hollow oar 🫡

still stuck on these errors

[real berry]

https://tenor.com/view/hacker-gif-18341137

[stark gust]

but of course the dumb thing breaks my code when I load it from the db too... ValueError: year -620 is out of range 😠

btw I just realized that the lack of month and day data is compensated with inserting 01-01... so january 1 will have way more birthdays than it should... this method sucks

[torn sphinx]

      await cursor.execute("SELECT adchannel, welcomemessage, welcomechannel, announcechannel, leavemessage FROM users WHERE guild = ?", (ctx.guild.id,))
      configs = await cursor.fetchone()
      adchannel, welcomemessage, welcomechannel, announcechannel = configs[0], configs[1], configs[2], configs[3]
      leavemessage = configs[4]

Traceback (most recent call last):
  File "/home/runner/cross-ad-bot/venv/lib/python3.8/site-packages/discord/ext/commands/core.py", line 229, in wrapped
    ret = await coro(*args, **kwargs)
  File "main.py", line 365, in configs
    adchannel, welcomemessage, welcomechannel, announcechannel = configs[0], configs[1], configs[2], configs[3]
TypeError: 'NoneType' object is not subscriptable

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

Traceback (most recent call last):
  File "/home/runner/cross-ad-bot/venv/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 1349, in invoke
    await ctx.command.invoke(ctx)
  File "/home/runner/cross-ad-bot/venv/lib/python3.8/site-packages/discord/ext/commands/core.py", line 1023, in invoke
    await injected(*ctx.args, **ctx.kwargs)  # type: ignore
  File "/home/runner/cross-ad-bot/venv/lib/python3.8/site-packages/discord/ext/commands/core.py", line 238, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: 'NoneType' object is not subscriptable

[wintry scaffold]

hey could i get some help so using mongodb to get data and display on a web page but it pull everything in that object id and not just the field i need

[stark gust]

on one hand you can choose what fields to return from the database (called projecting) by setting a second dictionary in find()

values = mongodb.collection.find({}, {"_id": 1, "username": 1}})

here's the documentation for it: https://www.mongodb.com/docs/manual/tutorial/project-fields-from-query-results/
on the other hand, adding projection to the query slows it down dramatically. you might be better off picking the values after the entire document is returned

values = list(mongodb.collection.find({}))
values = [x["_id"] for x in values]   # list-comprehension replaces the documents with their _id values only

there is of course also the option to only place the information you need into the field where you need it, not the whole thing. this is probably achievable whatever you use by concatenating the dictionary keys in your code. for example in Flask's Jinja2 templates you'd do something like this

{{ myvariable.dict_key }}

and this would place the key's value only, not the whole myvariable document

[peak lion]

            case "delete":
                dict_one = {"_id": inter.guild.id}
                exists_check = await inter.client.settings.find(dict_one)
                
                if exists_check is not None:
                    if "global_id" in exists_check:
                        c =  inter.client.get_channel(int(exists_check["global_id"]))
                        if c:
                            question = nextcord.Embed(description=f"{config.DiscordSupport} Are you sure to deactivate the global Chat ({c.mention})?")
                            
                            view = Yes_OR_No() sorry aber

                            await inter.response.send_message(embed=question, ephemeral=True, view=view)
            
                            view.message = await inter.original_message()
            
                            await view.wait()
                            if view.value is None:
                                return
        
                            elif view.value:
                                unset_field = {"global_id": c.id}
                                unset_date = {"_id": inter.guild.id}
                                await inter.client.settings.unset(unset_date, unset_field)
                            
                        else:
                            return
                    else:
                        reply = nextcord.Embed(description=f"{config.DiscordError} You don't have an active global chat!", colour=config.red)client.py
                        await inter.response.send_message(embed=reply, ephemeral=True)
                else:
                    reply = nextcord.Embed(description=f"{config.DiscordError} You don't have an active global chat!", colour=config.red)
                    await inter.response.send_message(embed=reply, ephemeral=True)```

Traceback (most recent call last):
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\nextcord\client.py", line 512, in _run_event
    await coro(*args, **kwargs)
  File "c:\Discord\Maja Projekt\MajaSystem_Test\bot.py", line 191, in on_application_command_error
    raise error
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\nextcord\application_command.py", line 910, in invoke_callback_with_hooks
    await self(interaction, *args, **kwargs)
  File "c:\Discord\Maja Projekt\MajaSystem_Test\modules\setup\cog.py", line 549, in global_chat
    await inter.client.settings.unset(unset_date, unset_field)
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\alaric\document.py", line 377, in unset
    await self._document.update_one(filter_dict, {"$unset": {field: True}})
TypeError: unhashable type: 'dict'```

[stark gust]

how can I update a field of many rows in a table with a random value using SQLAlchemy?
I mean I want a different random integer for each row's field
I assume it can be done somehow without looping through the table and doing a session for each row

[fading patrol]

Selecting the ids of the relevant rows and then inserting random values in the relevant fields should be trivial. If you're stuck on something specific show some code

[stark gust]

one of the columns in a table has random numbers. I want to update all of them with new random numbers. Even if this is trivial to you, it isn't for me, that's why I'm asking.

[wintry scaffold]

@stark gust thanks so much

[stark gust]

I found a solution in the meantime btw, I just can't make it work, but the error seems to be something else... whenever I reference this column of random numbers I end up with an error, but even if I do SELECT

[fading patrol]

again if you share some code, or at least specific error details, maybe someone can help

[stark gust]

I didn't say anything because I don't think that this shows what the solution might be, but have a look

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "order"
LINE 1: SELECT * FROM Documents WHERE order = 1000

if I use any other column, it runs, it doesn't run with the order column.

the column exists. even if it wouldn't, I think it'd normally give a different error, not SyntaxError...

[waxen finch]

not familiar with sqlalchemy but order is also a reserved keyword, so it needs to be double-quoted as an identifier

[stark gust]

ah, thanks for the info

yep, that was the problem 😄

is type a reserved keyword too by any chance? because I have columns named type in some tables too

okay, so I'm trying to add different random numbers to every element in a column, and I found that people do abs(checksum(newid())) % someinteger to do it. but if I try to pass this in SQLAlchemy like so:

query = session.execute('UPDATE Documents SET "order" = abs(checksum(newid())) % 200000')

then I end up with this error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) function newid() does not exist
LINE 1: UPDATE Documents SET "order" = abs(checksum(newid())) % 200000
                                                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

how can I resolve this?

[stoic pewter]

how to raise an exception if the user id is not in the database? (in @namee.error)

[pure sleet]

you can raise an exception in python

[stoic pewter]

can i have an example please?

[stark gust]

you can google simple things like "how to raise exception in python" but here's some examples for you https://www.w3schools.com/python/gloss_python_raise.asp

[stoic pewter]

thanks

[stark gust]

I joined "the" SQL discord server... I have to provide my phone number to discord in order to be allowed to write there... I don't get it

[brazen hornet]

Hey guys I come from java language I have some general questions about sqlalchemy can anyone help me?

[hot stag]

So this is my code:

c.execute("DELETE from backlog WHERE oid=" + update_box.get())

And it works, the issues is that the oid stays and the rows don't shift to reflect the changes. Any idea on how to accomplish this?

maybe I should do "rowid" or "foreign_key" instead

[stark gust]

did you commit? or do you not have to do that? because otherwise the change is not permanent

[hot stag]

I do, I just didn't include that in the code snipit. It deletes the entry just fine, but all future entries get a new oid based off what the latest oid is....

so say I have

OID 1 | data | data | data
OID 2 | data | data | data
OID 3 | data | data | data

After I delete oid 1, when I add a new entry to next entry is oid 4

[stark gust]

isn't your OID column autoincremented by any chance?

because that is how autoincrement works

[light ice]

guys I have problem when try deleting (using python and cs50 module same as sqlite3) a record from a table that has a foreign key.
ValueError: FOREIGN KEY constraint failed

@app.route("/deregister_7")
def deregister_7():
    id = request.form.get("id")
    if id:
        #db.execute("PRAGMA foreign_keys = OFF")
        db.execute("DELETE FROM Exemplaire WHERE id_exemp = ?", id)
    return redirect("/exemplaires")

[torn sphinx]

You must delete any records that reference the row you are trying to delete first.

[vapid hawk]

or set the foreign key relation mode to cascade

so when you delete a user,
all the pets that reference user

on pet.owner_id = user.id
```get dropped as well

[wise goblet]

@stark gust nicer option renaming bad variables from order to order_ is available. No need for quotes then

[brazen hornet]

Hi guys in sqlalchemy the database connection is closed automatically in ORM ? I can execute native queries in orm sqlalchemy?

[brazen hornet]

I need a serious and complete guideline for using sqlalchemy plz. If anyone can help I would appreciate it

[paper flower]

Depends on how you use it, but generally connection gets "freed" and is sent to connection pool to be reused later

[brazen hornet]

I have learnt to work with database transactions with java and in python there is a little bit different logic. Can I send you personal nessage to explain you what I want to do?

[paper flower]

I don't mind but it would be better if you cold ask your questions here so others could correct me or learn something new too

[brazen hornet]

You have right I'm sorry. If i don't make any mistake I have seen two ways for executing queries, the first way is with a core and the second is ORM. I have read that in the core way when the script execute the . execute method then automatically creates a connection but this connection where is closed? How can ensure that my database is Safe? For the second way with ORM I make a session l, when i execute the query must I write session.close() or it works automatically? I hope my English is good enough and the message point is clear for you

[paper flower]

you could use context managers to ensure that your connection is freed

It won't be closed though

If you have some sort of shutdown event you could use engine.dispose() so close all connections before your program exits 🤔

[brazen hornet]

This application is including with fastapi so for the performance point of View it is good to make engine.dispose()? I have read that the connection pool has 10 connection, is it right this information? Please let me ask one more question, can I right native SQL query with.execute method? What's your advice for my crud project with fastapi and sqlalchemy? For the production project?

[paper flower]

10 might be the default, yes

You can execute raw sql with .execute too

I'd use orm/core where possible

[brazen hornet]

This situation what effect it could have when the API accept 30 request, this means the API will use 30 connection from the connection pool in the same time. This is the last question 😃 than you for your patience

[paper flower]

If you have 30 requests active at all times - yes

Most likely you can have say 100rps, and each takes ~0.2 seconds, you'd need only 20 connections for that

[brazen hornet]

Ok thank you very much for your help. If you have a good course or guide if you want send it. You have a nice day!

[paper flower]

I don't have anything like that but sqlalchemy documentation covers a lot of topics 👀

[brazen hornet]

Ok then I will search better thank you

[paper flower]

For example connection management: https://docs.sqlalchemy.org/en/20/core/connections.html

[lost zinc]

How to get table name with SQLAlchemy, Let say I want to add data at specific cell by using table name and index as picture, for instance i will append to balance in date 1, How can i add Data, I am new to this framework.
SELECT balance FROM december WHERE date = 9;

[torn sphinx]

`createsql = "CREATE TABLE ACCOUNT (id INT(10) PRIMARY KEY, name VARCHAR(255), user_name VARCHAR(255))"

import mysql.connector as mysql

db = mysql.connect(
host = "localhost",
user = "root",
passwd = "simplycoding",
database = "mydb" )

cursor = db.cursor()
cursor.execute(createsql)
cursor.execute("DESC ACCOUNT")

details = cursor.fetchall()

print(details)`

what does the following code do

[formal lintel]

!code can you format properly please

[delicate field]

Here's how to format Python code on Discord:

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

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

[torn sphinx]

createsql = "CREATE TABLE ACCOUNT (id INT(10) PRIMARY KEY, name VARCHAR(255), user_name VARCHAR(255))"

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "root",
    passwd = "simplycoding",
    database = "mydb"  )

cursor = db.cursor()
cursor.execute(createsql)
cursor.execute("DESC ACCOUNT")

details = cursor.fetchall() 

print(details)```

I wanna know what does db.cursor, cursor.execute, fetchall does.

[formal lintel]

do you know how mysql works?

[torn sphinx]

yeah

[thorny parcel]

from the documentation

cursor = cnx.cursor([arg=value[, arg=value]...])
This method returns a MySQLCursor() object, or a subclass of it depending on the passed arguments. The returned object is a cursor.CursorBase instance. For more information about cursor objects, see Section 10.5, “cursor.MySQLCursor Class”, and Section 10.6, “Subclasses cursor.MySQLCursor”.

The MySQLCursor class instantiates objects that can execute operations such as SQL statements. Cursor objects interact with the MySQL server using a MySQLConnection object.

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor.html

[wispy spindle]

Hey, I'm making a application and needs a help about sqlite3, have some way to get the name of the columns from a table, that is the same datatype that I want, in my example needs datatype of datetime, someone knows how to do that?

[fading patrol]

does this help? https://database.guide/5-ways-to-check-a-columns-data-type-in-sqlite/

[wispy spindle]

Thanks

[lost zinc]

I have new question About update method with SQLAlchemy ORM, How can I update value at specific cell as UPDATE december SET balance = 1000 WHERE date = 10;

I have no idea.

[paper flower]

If you want to do it with orm then just retrieve your model from db and update one of it's fields:

model = session.get(Model, <id here>)
model.balance = 1000
session.add(model)
session.flush()

If you want to update all models on specific condition then you could use update:

stmt = update(Model).values(balance=1000).where(Model.date == 10)
session.execute(stmt)

[pearl copper]

how to use database with python?

postgresql can work with python

[paper flower]

yes

[mint wharf]

Let's say I have a table of books and bookRentals.
I want a query that returns all books and their latest rental, including ongoing rental. bookRentals has timestamp fields for checkout and returned. If a rental's returned value is NULL, that means it's an ongoing rental, which still counts as the "last rental" for a book.
Let's say that books and rentals are joined by a field called ISBN.

[hard burrow]

intents = discord.Intents.all()
bot = commands.Bot(command_prefix='!', description=BOT_NAME, intents=intents)
bot.engine = create_async_engine(r'sqlite+aiosqlite:///C:\Tsekis\invites.db')

async def create_tables():
    async with bot.engine.begin() as conn:
        await conn.execute(text('PRAGMA foreign_keys=ON'))
        await conn.run_sync(Base.metadata.create_all)

asyncio.run(create_tables())


    return sqlite3.connect(loc, **kwargs)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unable to open database file
(Background on this error at: https://sqlalche.me/e/14/e3q8)
Press any key to continue . . .

[torn sphinx]

"SELECT * FROM users WHERE id NOT like ? AND (gender2 = ? OR gender2 = ?) AND corp like ?"
how to make correctly much '''AND''' and '''or''' together?

[wintry scaffold]

can i get some help

i only want the row that Dewie is at to show up how would i have to format the db.collection.find

[hybrid canyon]

db.collection.find({'station': 'Dewie'})

[wintry scaffold]

weird thats what i have that all the data is showing up but thanks for taking your time

[hybrid canyon]

Then all your data must have a station of Dewie

Refine your query further

[wintry scaffold]

yeah dewie is a name of one the weather radar so theres multiple data gathered by it so im not sure how to only show its name on that field

or another stations name when the data with previous stations ends

[grim vault]

Sort by date and time descending and take only the first one?
db.collection.find({'station': 'Dewie'}).sort({"date": -1, "time": -1}).limit(1)

[torn sphinx]

I am trying to add a date value into SQLite db:
INSERT INTO SRSdatabase (lastSeen) VALUES (2022-12-11)
but the value it adds is:
1999 instead of 2022-12-11
Anyone knows why?

[pure sleet]

what's the datatype for the field?

[red nebula]

Guys anyone runs python on rstudio?

Seaborn plots are all messed up

[torn sphinx]

It's text

I just tried to convert the datatype to other types and run the code again but the result is still the same :(

[pure sleet]

text should be wrapped in quotes no?

"2022-12-11"

[austere salmon]

Single quotes preferred (usually required) in case of plain sql query

[elfin crown]

Wassup mans ! I just want to know if anyone can help me for a project python ?

If yes come to message private please that will be really cool

[robust steppe]

Hello people
Anyone have good SQL book recommendations ? 🙏

Something that doesnt spend too much time on the basics perhaps

[grim vault]

This is not text:
INSERT INTO SRSdatabase (lastSeen) VALUES (2022-12-11)
That's an expression: 2022 minus 12 minus 11 equals 1999, you need single quotes:
INSERT INTO SRSdatabase (lastSeen) VALUES ('2022-12-11')
now it's text.

[frank cloak]

CREATE TABLE SICRONI_GLOBAL_CHAT(
   server_id BIGINT NOT NULL,
   channel_id BIGINT NOT NULL,
   webhook_url TEXT,
   chat_type SMALLINT DEFAULT 0 NOT NULL,
   UNIQUE (server_id, channel_id),
   PRIMARY KEY (server_id, chat_type)
)

This good, right?
I should make chat_type's default be 0 though

[frank cloak]

[frank cloak]

What I am using uses postgresql not sqlite

[naive rivet]

Need help on SQL

And Tkinter

I want to generate an output based on value of dropdown menu

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @naive rivet until <t:1670821378:f> (10 minutes) (reason: newlines rule: sent 104 newlines in 10s).

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

[radiant elbow]

!unmute 832277530680623125

[delicate field]

:incoming_envelope: :ok_hand: pardoned infraction mute for @naive rivet.

[radiant elbow]

!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.

[radiant elbow]

^ Use that paste link

otherwise the bot will mute large pastes as spam.

[naive rivet]

what should i do after posting there

[radiant elbow]

paste the URL here

like the instructions in that embed say

[naive rivet]

https://paste.pythondiscord.com/izetogutul

Need help on SQL
And Tkinter
I want to generate an output based on value of dropdown menu

[torn sphinx]

I am trying to open a database in postgres through a link

ERR_CONNECTION_TIMED_OUT

this is what i get

what should i do ?

[smoky hare]

I want to access a .dB file from an FTP server. And I have no idea what to do.

[lyric ember]

Please I need a free database design book in pdf. Recommendations and links to the pdf will be helpful

I am quite struggling to get the grip of database design so wanna read a book

Is MySQL workspace good for designing database

I wanna design first then implement it in postgresql

I think it will not be a problem to implement the design in postgresql after designed the database in MySQL workspace

[sleek raft]

Google is your friend 🙂
https://ptgmedia.pearsoncmg.com/images/9780321884497/samplepages/0321884493.pdf

[lyric ember]

Thanks

I did like hundreds of search but wasn't that helpful for me. It's been 3 days Googling and trying to find some good stuff to learn database from.

YouTube has some videos but they are also not that good

Thank you for the book . I am gonna read this one.

Anyone has more please share

[lyric ember]

This book is not the complete one

It only has some samples chapters

85 pages of 500

[tawdry needle]

Hi there I am working with SnowFlake and creating a connection via a python script. We would like to have a window pop up for the user to sign in, but we don't currently have SSO set up on SnowFlake. Is there a way to still make use of this authentication method without SSO or is that the only way forward?

[proud crater]

probably you will need an identity manager to authenticate users, for example this one https://www.keycloak.org it also has ldap integration

[tawdry needle]

@proud craterWould Okta or some Azure tool work as well?

[proud crater]

azure has this one https://azure.microsoft.com/en-us/solutions/active-directory-sso/?ef_id=CjwKCAiAv9ucBhBXEiwA6N8nYFZMVTu8WqlKZvY5SiEKLGmldPQj0HVcQ8TZwSE0W4ZW7kXg_DXM-hoCWtAQAvD_BwE%3AG%3As&OCID=AIDcmm22fzqsrc_SEM_CjwKCAiAv9ucBhBXEiwA6N8nYFZMVTu8WqlKZvY5SiEKLGmldPQj0HVcQ8TZwSE0W4ZW7kXg_DXM-hoCWtAQAvD_BwE%3AG%3As&gclid=CjwKCAiAv9ucBhBXEiwA6N8nYFZMVTu8WqlKZvY5SiEKLGmldPQj0HVcQ8TZwSE0W4ZW7kXg_DXM-hoCWtAQAvD_BwE#overview

[tawdry needle]

You rock@proud crater Thank you!

[green smelt]

Anyone here familiar with t-sql Microsoft ? Ive been trying to figure out an error now for HOURSSS

[raven summit]

!code

[delicate field]

Here's how to format Python code on Discord:

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

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

[livid coyote]

Anyone particularly handy with SQLAlchemy 2.0?

[livid coyote]

I'm moderately competent - I might be able to help

[dense imp]

what is better place for learn DATABASES?

i need understand the concepts like File Organization in DBMS, and another concepts focus on teoric?

or just know how make querys, using the commands of SQL?

[fading patrol]

There are loads of good resources so use whichever ones you find engaging. I like DataQuest and DataCamp, SQL Zoo is another popular one

[dense imp]

Right

For example I was studying for geekforgeeks

But I guess that the content is very dense, because explain on how things work in background

[fading patrol]

I find that site great for specific pieces of information you might be searching for... the ones I recommended are more interactive, and I think that's the right way to learn SQL, you need to do it

[dense imp]

Right, this site have a lot information but for beginner I guess a lot information

And depends of your case don't necessarily know something that have there

[livid coyote]

  File "D:\Workspace\OBO\MCE\venv\Lib\site-packages\sqlalchemy\engine\result.py", line 1177, in _tuple_getter
    return self._metadata._row_as_tuple_getter(keys)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\Workspace\OBO\MCE\venv\Lib\site-packages\sqlalchemy\engine\result.py", line 176, in _row_as_tuple_getter
    indexes = self._indexes_for_keys(keys)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "D:\Workspace\OBO\MCE\venv\Lib\site-packages\sqlalchemy\engine\result.py", line 152, in _indexes_for_keys
    raise NotImplementedError()
NotImplementedError

Anyone run into this with SQLAlchemy 2.0 before? It happens intermittently and will often resolve on subsequent attempts, until some time has passed, in which case is throws again.

This is what I'm executing when it happens:

on_or_before = cast(ColumnElement, model_data >= target_time)
stmt = select(model).where(on_or_before)
results: Result = session.execute(stmt)

[proven escarp]

Hi all --- I wanted some help with: When running a function on Jupyter notebook, the csv module correctly appends a row to an existing CSV file. However, when executing the script on a Mac terminal, the csv module does not append a row to the CSV. Any idea on how to fix or any workarounds?

Here is my post in the #1035199133436354600 channel: https://discord.com/channels/267624335836053506/1052336893150375946

[real timber]

Is there much difference between using SQLAlchemy with reflective models vs a model defined in SQLA ? I'm wondering if there are any limitations to reflective models (other than they just represent what's on the database).

Once it's imported, is it functionally equivalent ?

[soft gorge]

What's the best way to insert a row into a database before any other operation like a select statement with sqlite

[mystic shale]

INSERT INTO

[soft gorge]

Just have that before every select statement?

[mystic shale]

maybe I'm not understanding what you want

you want to insert a new row into the db, right?

[soft gorge]

yes

before any select statement

[mystic shale]

yeah just do that before your select statements

[soft gorge]

Okay i'm thinking theres another way but i really don't think there is

I was trying executescript but it won't work with variables

[mystic shale]

INSERT INTO table (column1, column2, column3) VALUES (value1, value2, value3);

if you want to replace a row, you can do the same with INSERT OR REPLACE INTO

[soft gorge]

Okay thank you

I just thought there would be another way to do it within one execute

[mystic shale]

do what in one execute?

insert and select it at the same time?

[soft gorge]

Like a insert then a select

yea

well an insert or ignore

to ensure a row is returned

[mystic shale]

something like this? ```sql
INSERT INTO users (id, name, email)
SELECT 1, 'John Doe', '[e-mail]'
WHERE NOT EXISTS (SELECT * FROM users WHERE id = 1);

SELECT * FROM users WHERE id = 1;

[soft gorge]

I guess

Problem is with sqlite at least it's like impossible to do that at once

[mystic shale]

are you doing it from the sqlite command prompt?

[soft gorge]

nah in python

with aiosqlite

[mystic shale]

why not sqlite3 module for python

[soft gorge]

it's with a discord bot

so async is "more efficient"

[mystic shale]

ah need threading?

[soft gorge]

yea

[mystic shale]

you can make a multiline comment inside the query

never used aiosqlite but with sqlite 3 it would be like ```py
conn.execute('''
SELECT *
FROM table
WHERE something==something
''')

[soft gorge]

Would it be more appropriate to basically make a member add them self to the database first instead of having the member added when they do a command

[mystic shale]

it would probably be easier perhaps

[soft gorge]

Yea i just feel like it’s not as professional

[mystic shale]

well you could always do something like this, just use 3 quotes to make it multiline inside your python script

be careful if you are storing passwords in your db, it's easy to leave python code open for sql injection attacks

like if you have a login feature

if you do, look into parameterized queries

[soft gorge]

I got you

I tried that with execute script and f string but all i got was unrecognized token error

[mystic shale]

don't use fstring

use ?

question mark is like format for sql

conn.execute('''
    INSERT INTO users (id, name, email)
    VALUES (?, ?, ?)
''', (1, 'John Doe', '[e-mail]'))

[soft gorge]

Oh i see what you mean

hold up let me see

[mystic shale]

you can use variables there too if you want

instead of the strings 'John Doe' or w/e

if you use fstring, I could do something like JohnDoe123'-- as my username, and then the rest of your query becomes a comment, then it won't even require a password but I can log in as JohnDoe123

or I can even execute code between the ' and -- if I want

and drop tables or something lol

the ? parameter prevents this

[soft gorge]

            async with aiosqlite.connect(database=self.databsePath) as db:
                async with db.cursor() as cursor:

                    sql = """
                        INSERT OR IGNORE INTO members(guild_name, guild_id, member_name, member_id) VALUES(?, ?, ?, ?)
                        SELECT * FROM members WHERE guild_id = ? AND member_id = ?
                    """, (guild_name, guild_id, member_name, member_id, guild_id, member_id)
                    await cursor.executescript(sql_script= sql)
                    await db.commit()

This is what i have

But now i get an error saying script argument must be unicode

[mystic shale]

did you mean to spell self.databsePath like that?

[soft gorge]

nope

It worked tho haha

[mystic shale]

I think it's something to do with this line await cursor.executescript(sql_script= sql) though as well

it's not reading it in the right codec

maybe reading it as binary or something, instead of utf-8

or whatever is the right codec for this

[soft gorge]

yea i think it's because it's getting params put in

It's not supposed to apparently

[mystic shale]

what's the error name?

aiosqlite documentation is pretty slim

[soft gorge]

ValueError: script argument must be unicode

It's gotta be with sqlite3

[mystic shale]

maybe you can just use sqlite3 with asyncio library and still use async and await

I'm not seeing anything in aiosqlite documentation about parameterized queries

[soft gorge]

True but i don't think sqlite3 allows for variables in executescript

[mystic shale]

but sqlite3 it works just fine

they do tho

[soft gorge]

with variables?

[mystic shale]

yeah sec I'll show you

[soft gorge]

oka

[mystic shale]

https://www.youtube.com/watch?v=D-1kNFO568c&t=7418s

2 hr and 5 minutes in, he tells you how to parameterize it safely and why

[soft gorge]

pain

Still can't get execute script with variables tho even with regular sqlite3

[mystic shale]

hmm maybe the cs50 library wrapped something else to make that work

https://github.com/cs50/python-cs50/blob/3ccbd99c58d18df7e9385d421e85e8b7631bbe9d/src/cs50/sql.py here is the source code for it

oh they use sqlalchemy

and sqlite3

[soft gorge]

I think i got something

I mean it's with postgreSQL but maybe this would work with sqlite3

Where i can manage to pass multiple statments with begin and end

and throw it into an execute instead of execute script

[mystic shale]

or switch to postgresql 🤣

[soft gorge]

Yea

I was thinking that at this point

I guess sqlite3 isn't really made for advanced sql statements

[mystic shale]

sqlite is great for simple needs

[soft gorge]

time to move on

Postgresql can use a local db still?

I don't really need a remote host

[mystic shale]

Yes it can

But you could also try to fstring your variables in the parameters after the query maybe

[soft gorge]

Like with the ? marks

[mystic shale]

Yeah where you input the function after you could do like f'{guild_id}' or something

But leave the ?

[soft gorge]

I don't think it'll work and i can't check it rn because i'm trying to change my folders

[mystic shale]

ok lol

hmm chat gpt says this works

import sqlite3

# Connect to the database
conn = sqlite3.connect("mydatabase.db")

# Create a cursor
cursor = conn.cursor()

# Define the values to be inserted
name = "Alice"
age = 21

# Define the query string with placeholders
query_string = "INSERT INTO users (name, age) VALUES (?, ?)"

# Execute the query
cursor.execute(query_string, (name, age))

# Commit the changes
conn.commit()

[storm mauve]

you really shouldn't trust ChatGPT at all

[mystic shale]

true lol

[storm mauve]

that code does looks about right though, assuming that you have created the users table previously place

[storm mauve]

....wait wtf are you trying to do?

if an user is not in the database, you want to insert it?
just select first and then, in python code, check if it actually found someone (and if not, insert)

[radiant halo]

Hey! I'm using sqlite3 to insert users names, and data associated with them, into a database. That all works fine, however when I try to lookup the data nothing shows up before I restart my bot/script. After some searching, I believe my connection to the database isnt being closed. Problem is, I'm ending my function with commit and close..

    filename = inspect.getframeinfo(inspect.currentframe()).filename
    path = os.path.dirname(os.path.abspath(filename))
    conn = sqlite3.connect(path + '/team_database.db')
    c = conn.cursor()
    c.execute(f"INSERT INTO {team} (player_name, steam, bm) VALUES (?, ?, ?)",
              (player_name, steam, bm))
    print(f"Successfully added player to {team}'s team")
    conn.commit()
    c.close()
    conn.close()```
.

[wise goblet]

import sqlite3
from contextlib import contextmanager
from pathlib import Path

@contextmanager
def get_connection(path: str):
    try:
        con = sqlite3.connect(path)
        yield con
    finally:
        con.close()

@contextmanager
def get_cursor(con: sqlite3.Connection):
    try:
        cur = con.cursor()
        yield cur
    finally:
        cur.close()

def add_player_data(team, player_name, steam='Null', bm='Null'):
    with db.get_connection(str(Path(__file__).parent / "team_database.db")) as conn:
        with db.get_cursor(conn) as cursor:
            result = cursor.execute(f"INSERT INTO {team} (player_name, steam, bm) VALUES (?, ?, ?)",
              (player_name, steam, bm))
            conn.commit()

python way to a problem ensuring that it will be always closed

[radiant halo]

Thanks man, I'll try to get this to work. Reading over it trying to get understand it all 😄

[wise goblet]

inspect.getframeinfo(inspect.currentframe()).filename this stuff is intended to discover path relative to current script location, right?

[radiant halo]

I am getting an error: UnboundLocalError: local variable 'con' referenced before assignment I have to be completely honest. I don't have much knowledge of how to use yield or if __name__=="__main__"

[radiant halo]

Yep, honestly it's not needed as I currently just have it in the root of my script directory

[wise goblet]

u were supposed to add your variables... fixed code

as well as replaced this path discovery with more simple analog

[radiant halo]

Huh.. The only thing I missed was having .parent in my connection, this works now. Thanks man! Sorry if this is basic stuff, but I couldn't wrap my head around it

[safe kernel]

variable.commit() what does it do ?

[paper flower]

Depends on what variable is

[safe kernel]

it is the database connector

[paper flower]

It should commit current transaction

[safe kernel]

should i put one here

[crimson tangle]

how can i fix this

ita a ubuntu installed mongodb

i want to access through compess

[stark gust]

this code will break if anything other than digits would be provided as the account input (and the password in the other image). just saying, it's a bad practice to immediately try to convert the input string to int without checking if you can. possibly something like isinstance(inputvalue, int) or inputvalue.isnumeric()

[safe kernel]

indeed i am aware of that , we haven't learnt to this extent so far sooo we cant really do this

[stark gust]

alright

[torn sphinx]

how do i get ID, name, address of all customers, plus the last payment they made and when it was made ?

my thought process is creating a cte that joins the 3 tables (customers,accounts and payments)

SELECT CUST_ID,CUST_NAME,CUST_ADDRESS,PAYMENT_AMOUNT FROM CTE GROUP BY CUST_ID ORDER BY PAYMENT DATE DESC

[fleet cape]

Do you just want the last payment. If so I would group by everything but the payment date and select the max of the payment date along with your other variables

SELECT CUST_ID,CUST_NAME,CUST_ADDRESS,PAYMENT_AMOUNT, max(PAYMENT_DATE) FROM CTE Group BY CUST_ID,CUST_NAME,CUST_ADDRESS,PAYMENT_AMOUNT

[torn sphinx]

this is my solution

I havent tested it though

[ancient kiln]

first time coming across databases, installed sqlite3 and "tried" to create a basic data file but after the "mydatabase.db" line, anything I type won't have an effect. Can anyone point out whats going on please

[fading patrol]

you're missing the .open: https://www.datacamp.com/tutorial/beginners-guide-to-sqlite

[strong compass]

What do u guys think about 4/6/8 month-1 year data science bootcamp? Especially for undergraduate IT student

[ancient kiln]

thank you very much

[crimson tangle]

help me with this auth issue

pls

[haughty breach]

hello, why Redis is used on broadcasting messages instead of using websockets?
In my case, I want to use Redis as a cache database on my websocket.
Is it okay if I did this flow:

1- Websocket normal send/recieve messaging

2- Whenever a message is sent/recieved store it on Redis cache

3- When websocket is closed, take all data from Redis and store it into a database (mysql, postrgresql) and clear Redis cache

4- When websocket connection is on again, get all data from database initially and redo the flow

[torn sphinx]

@bot.event
async def on_ready():
    print(f"{bot.user.name} is online!")
    setattr(bot, "db", await aiosqlite.connect("main.db"))
    async with bot.db.cursor() as cursor:
        await cursor.execute("CREATE TABLE IF NOT EXISTS bank (wallet INTEGER, bank INTEGER, maxbank INTEGER, user INTEGER)")

async def create_balance(user):
    async with bot.db.cursor() as cursor:
        await cursor.execute("INSERT INTO bank VALUES (?, ?, ?, ?)", (0, 100, 2736652635562362, user.id,))
    await bot.db.commit()
    return

async def get_balance(user):
    async with bot.db.cursor() as cursor:
        await cursor.execute("SELECT wallet, bank, maxbank FROM bank WHERE user = ?", (user.id,))
        data = await cursor.fetchone()
        if data is None:
            await create_balance(user)
            return 0, 100, 2736652635562362
        wallet, bank, maxbank = data[0], data[1], data[2]
        return wallet, bank, maxbank


async def update_wallet(user, amount: int):
    async with bot.db.cursor() as cursor:
        await cursor.execute("SELECT wallet FROM bank WHERE user = ?", (user.id,))
        data = await cursor.fetchone()
        if data is None:
            await create_balance(user)
            return 0
        await cursor.execute("UPDATE bank SET wallet = ? WHERE user = ?", (data[0] + amount, user.id,))
    await bot.db.commit()

@bot.command(aliases=['bal'])
async def balance(ctx, member: nextcord.Member = None):
    if not member:
        member = ctx.author
        wallet, bank, maxbank = await get_balance(member)
        em = nextcord.Embed(title=f"{member.name}#{member.discriminator}'s Balance")
        em.add_field(name="Wallet", value=wallet)
        em.add_field(name="Bank", value=f"{bank}/{maxbank}")
        await ctx.send(embed=em)

error:

full error:

Traceback (most recent call last):
  File "C:\Users\PC\AppData\Roaming\Python\Python39\site-packages\nextcord\ext\commands\core.py", line 165, in wrapped
    ret = await coro(*args, **kwargs)
  File "c:\Users\PC\Desktop\Aleccy Bot\main.py", line 86, in balance
    wallet, bank, maxbank = await get_balance(member)
  File "c:\Users\PC\Desktop\Aleccy Bot\main.py", line 62, in get_balance
    await cursor.execute("SELECT wallet, bank, maxbank FROM bank WHERE user = ?", (user.id,))
  File "C:\Users\PC\AppData\Roaming\Python\Python39\site-packages\aiosqlite\cursor.py", line 37, in execute
    await self._execute(self._cursor.execute, sql, parameters)
  File "C:\Users\PC\AppData\Roaming\Python\Python39\site-packages\aiosqlite\cursor.py", line 31, in _execute
    return await self._conn._execute(fn, *args, **kwargs)
  File "C:\Users\PC\AppData\Roaming\Python\Python39\site-packages\aiosqlite\core.py", line 129, in _execute
    return await future
  File "C:\Users\PC\AppData\Roaming\Python\Python39\site-packages\aiosqlite\core.py", line 102, in run
    result = function()
sqlite3.OperationalError: no such column: maxbank```

happens when i run the command !bal

[forest blaze]

hello. can someone help me? i need a database to store images for a discord bot, which database should i use?

[crimson tangle]

use link b

to store images

discord will store your images

hmm any sql

[livid coyote]

  File "C:\Users\obo\HubSpot Integration\MCE\venv\Lib\site-packages\sqlalchemy\orm\loading.py", line 112, in instances
    *[
     ^
  File "C:\Users\obo\HubSpot Integration\MCE\venv\Lib\site-packages\sqlalchemy\orm\loading.py", line 113, in <listcomp>
    query_entity.row_processor(context, cursor)
  File "C:\Users\obo\HubSpot Integration\MCE\venv\Lib\site-packages\sqlalchemy\orm\context.py", line 2571, in row_processor
    _instance = loading._instance_processor(
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\obo\HubSpot Integration\MCE\venv\Lib\site-packages\sqlalchemy\orm\loading.py", line 781, in _instance_processor
    primary_key_getter = result._tuple_getter(pk_cols)
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\obo\HubSpot Integration\MCE\venv\Lib\site-packages\sqlalchemy\engine\result.py", line 1177, in _tuple_getter
    return self._metadata._row_as_tuple_getter(keys)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\obo\HubSpot Integration\MCE\venv\Lib\site-packages\sqlalchemy\engine\result.py", line 176, in _row_as_tuple_getter
    indexes = self._indexes_for_keys(keys)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\obo\HubSpot Integration\MCE\venv\Lib\site-packages\sqlalchemy\engine\result.py", line 152, in _indexes_for_keys
    raise NotImplementedError()
NotImplementedError

Anyone seen an error like this in SQLAlchemy 2.0? It gets thrown by each one of my ORM classes in turn the first time the application runs after some time period, such that it runs fine after all of the objects have thrown the error once. That along with loading being the responsible module tells me it's something happening when the objects are first being loaded into memory, but I can't pin down specifically what is happening.

[calm chasm]

where can i get info on how to send a textfile from python to pgadmin4 database?

[fading patrol]

Wrong channel (this belongs in #career-advice) but normally boot camps are most relevant for people with non-technical degrees changing careers. If you're a student it sounds like you might want to change majors instead if your program doesn't cover the skills you want to learn. Or you could persue an MS, which will be much more valuable then a boot camp for data science

[fading patrol]

I assume you mean a Postgres database... this might get you started: https://pynative.com/python-postgresql-insert-update-delete-table-data-to-perform-crud-operations/

[calm chasm]

yes this is what i meant thank you

[magic gale]

Hello i'm new here hope someone help me !

I have thous 2 examples of a data i want to retrieve!
I want to know what is the best structure to store it !
In an object and one field have an array of object OR strait to an array of objects??

[haughty breach]

do u use any sort of ORM

[magic gale]

NOPE

the data is in mongoDb

[haughty breach]

yes so use an ORM, it will help u on the structure

make a model

whatever your object u want to name, could be the model name.
and the fields are; id, name, capital

I hope I understood ur question and answered it

[calm chasm]

can someone help me with a link for uploading a python file to pgadmin4 postgreSQL

i can't find it on google

[thin python]

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
gender varchar(1) CHECK ('Male', 'Female')
);

why does this not work?

[hollow crypt]

gender varchar(1) that’s why?

[hollow crypt]

what are you trying to do?

[real laurel]

Hello everyone can someone help me out with a problem I have in my sqlite3

[torn sphinx]

i font think its the problem but u need to add , at the end too

[real laurel]

???

can you provide a little details?

[torn sphinx]

when u defined what are the values so u need to add , at the end

“ruler_title,”

idk how to explain why but its just like that

[calm chasm]

cur = conn.cursor()


    query = "INSERT INTO leerlingen (id, name, email, book, author) VALUES (%s,%s,%s,%s,%s)"
    with open("OLCtest_tabletext") as f:
        f_record = f.readlines()[5:]
        values = [line.strip().split(' , ') for line in f_record]
        cur.execute(query, values)
    # commit the changes
    conn.commit()
    count = cur.rowcount
    print(count, "Record inserted successfully into OLC table")

except (Exception, psycopg2.Error) as error:
    print("Failed to insert File into table", error)


finally:
        if cur is not None:
            cur.close()
        if conn is not None:
            conn.close() 

error is index out of range

def OLCtest():
    id = input("Wat is het idnummer ")
    name = input("Wat is het name ")
    email = input("Wat is het email ")
    book = input(("Naam van het book "))
    author = input("Naam van de authorr ")
    volledig = id + " , " + name + " , " + email + " , " + book + " , " + author + "\n"
    with open("OLCtest_tabletext", "a") as f:
        f.write(str(volledig))
    return print("Het is gelukt")

print(OLCtest())

this is the def i use to send the written part to the file

then i want to send the file with the written parts in it to the database

[hollow crypt]

the only thing i can think of is that in your file in some rows one of the column values are not there. Can you manually inspect your file?

[calm chasm]

5 , Ahmed Belkachem , [e-mail] , Grijze Jager  , Unknown
6 , Amin Nounouh , [e-mail] , Solo Leveling , Chugong Geuk-jin Jeon

this is what it is written in the file

[hollow crypt]

can you check either via a debugger or manually printing out what is the value of f_record?

[calm chasm]

(build 223.7571.203)

when i print it

[]

[calm chasm]

this is what i get

[hollow crypt]

Clearly your list comprehension is not working the way its supposed to. f_record = f.readlines()[5:] why have your indexed f.readlines() to 5? Doing this will not give you any data. I think you should replace with f_record = f.readlines() and check

[grim vault]

It also looks like it needs cur.executemany(query, values)

[calm chasm]

now it says ```
DataBase connected
0 Record inserted successfully into table

so it's connected but sends 0

[grim vault]

Well, if your file only has two lines, f_record will be empty because you start with the fifth line.

[calm chasm]

i have put in more lines

but still same 0

wel fixed it myself

@hollow crypt @grim vault Thank you so much for helping me without you 2 i couldn't have done it thank you have a very nice day

[torn sphinx]

`# connect to db
con <- dbConnect(SQLite(), dbname="sample.sqlite")

list all tables

tables <- dbListTables(con)

exclude sqlite_sequence (contains table information)

tables <- tables[tables != "sqlite_sequence"]

view tables

tables

extract data to dataframes account, account_date_session, iap_purchase

account <- getDatabaseTables(dbname="sample.sqlite", tableName="account")
account_date_session <- getDatabaseTables(dbname="sample.sqlite", tableName="account_date_session")
iap_purchase <- getDatabaseTables(dbname="sample.sqlite", tableName="iap_purchase")`

Error in dbConnect(SQLite(), dbname = "sample.sqlite"): could not find function "dbConnect"
Traceback:

why am i getting this error

[eager tide]

got this error after passing value = " "

error : com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.

[hollow crypt]

is this R code?

[torn sphinx]

Is sigfigures an actual parameter in sql?

[wise goblet]

Why won't u try to run your code 😅
Or using Google / digging database documentation, to which database (and version!) it is valid data type

[torn sphinx]

it didn't run

that's why I asked

If there's a different way to write it maybe

[torn sphinx]

btw do you know how can I retrieve all the values in a enum type that I created. in sql

[wise goblet]

better to start with question, which is your database engine (postgres, mysql, mariadb and etc)

databases are quite different in syntax beyond standard

[torn sphinx]

postgresql

@wise goblet

[wise goblet]

Googled it for you:
psql select enum types

https://stackoverflow.com/questions/1616123/sql-query-to-get-all-values-a-enum-can-have
As array:

SELECT enum_range(NULL::myenum)

Separate rows:

 SELECT unnest(enum_range(NULL::myenum))

[torn sphinx]

can't believe how easy it was

thanks mate

I also found something similar but they have a very big query

How did you find that post? I was trying to google it for long. DId you go directly on stack overflow?

this is the whole query that they use, but it is more generic than just enum types

It lists all types i think

[wise goblet]

Just started to write in Google
postgresql query enum types
Google autofixed to
psql select enum types

[torn sphinx]

Anyways thank you

[graceful widget]

one very dumb question

i wonder is it possible to use a variable as a query

just thought of it while i was pee-ing

like

var1 = 'SELECT * FROM TABLE'
query.execute(var)

[torn sphinx]

lol

ofcourse

[torn sphinx]

Hihi, What are the usual ways of preventing SQL injections? I've read about prepared statements but I'm not sure what else is used. I appreciate the help!
I'm using Postgresql with no ORM

[torn sphinx]

what is session_count and session duration_sec

in row 3, session count = 12, duration = 4703. what does this mean ?

[delicate field]

:incoming_envelope: :ok_hand: applied mute to @oak grove until <t:1671292584:f> (10 minutes) (reason: duplicates rule: sent 4 duplicated messages in 10s).

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

[uncut moss]

Does anyone know what is the Django equivalent to SQL's 'Point' database type? I have geolocations i want to save to my db using my Django models

[twilit moss]

Traceback (most recent call last):
File "/home/runner/uNomics/venv/lib/python3.8/site-packages/discord/app_commands/commands.py", line 862, in _do_call
return await self._callback(interaction, **params) # type: ignore
File "main.py", line 51, in balance
await open_account(str(author))
File "main.py", line 68, in open_account
users[author]['Wallet'] = 0
KeyError: '928109349140824125'

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

Traceback (most recent call last):
File "/home/runner/uNomics/venv/lib/python3.8/site-packages/discord/app_commands/tree.py", line 1242, in _call
await command._invoke_with_namespace(interaction, namespace)
File "/home/runner/uNomics/venv/lib/python3.8/site-packages/discord/app_commands/commands.py", line 887, in _invoke_with_namespace
return await self._do_call(interaction, transformed_values)
File "/home/runner/uNomics/venv/lib/python3.8/site-packages/discord/app_commands/commands.py", line 880, in _do_call
raise CommandInvokeError(self, e) from e
discord.app_commands.errors.CommandInvokeError: Command 'bal' raised an exception: KeyError: '928109349140824125'

halp

[fresh ermine]

PointField

or ```from django.contrib.gis.geos import Point

[uncut moss]

Thats whats tricky, i cannot use SQLite with that ? It would be ok but theres a ton of configurarions i read from the documentation i dont get to work on my mac. I have to switch to Ubuntu or smth

Would floating numbers saved to the d do the trick if i convert them to Location-objects in view.py ?

[paper flower]

Sqlite doesn't have support for geospatial data types, I'd try postgres 🤔

[wise goblet]

According to this cheatSheet, for geospatial other database types are more fitting

Although i pretty much sure Postgresql can handle it too, it has even geo spatial indexes already

although mentioned in spreadsheet PostGis is just an extension to Postgres https://arctype.com/blog/geospatial-data-postgres/

Postgres for the win 😆

[torn sphinx]

@bot.event
async def on_ready():
    print(f"{bot.user.name} is online!")
    setattr(bot, "db", await aiosqlite.connect("main.db"))
    async with bot.db.cursor() as cursor:
        await cursor.execute("CREATE TABLE IF NOT EXISTS bank (wallet INTEGER, bank INTEGER, maxbank INTEGER, user INTEGER)")

async def create_balance(user):
    async with bot.db.cursor() as cursor:
        await cursor.execute("INSERT INTO bank VALUES (?, ?, ?, ?)", (0, 100, 2736652635562362, user.id,))
    await bot.db.commit()
    return

async def get_balance(user):
    async with bot.db.cursor() as cursor:
        await cursor.execute("SELECT wallet, bank, maxbank FROM bank WHERE user = ?", (user.id,))
        data = await cursor.fetchone()
        if data is None:
            await create_balance(user)
            return 0, 100, 2736652635562362
        wallet, bank, maxbank = data[0], data[1], data[2]
        return wallet, bank, maxbank


async def update_wallet(user, amount: int):
    async with bot.db.cursor() as cursor:
        await cursor.execute("SELECT wallet FROM bank WHERE user = ?", (user.id,))
        data = await cursor.fetchone()
        if data is None:
            await create_balance(user)
            return 0
        await cursor.execute("UPDATE bank SET wallet = ? WHERE user = ?", (data[0] + amount, user.id,))
    await bot.db.commit()

@bot.command(aliases=['bal'])
async def balance(ctx, member: nextcord.Member = None):
    if not member:
        member = ctx.author
        wallet, bank, maxbank = await get_balance(member)
        em = nextcord.Embed(title=f"{member.name}#{member.discriminator}'s Balance")
        em.add_field(name="Wallet", value=wallet)
        em.add_field(name="Bank", value=f"{bank}/{maxbank}")
        await ctx.send(embed=em)

error:

[somber niche]

there is no column named max bank

maybe u tried to create maxbank after creating the table

u can do this instead:
await cursor.execute("ALTER TABLE bank ADD COLUMN maxbank DataType)

do this temporary it will automate create the column, after creating column remove this line or else u will get duplicate column errror

[torn sphinx]

Yes i did

[torn sphinx]

Oh, didnt knew that was an option tyy

[somber niche]

add this line after the CREATE TABLE execution

[torn sphinx]

Yeah tyy

[torn sphinx]

Traceback (most recent call last):
  File "C:\Users\PC\AppData\Roaming\Python\Python39\site-packages\nextcord\client.py", line 502, in _run_event
    await coro(*args, **kwargs)
  File "c:\Users\PC\Desktop\Aleccy Bot\main.py", line 39, in on_ready
    await cursor.execute("ALTER TABLE bank ADD COLUMN maxbank INTEGER")
  File "C:\Users\PC\AppData\Roaming\Python\Python39\site-packages\aiosqlite\cursor.py", line 37, in execute
    await self._execute(self._cursor.execute, sql, parameters)
  File "C:\Users\PC\AppData\Roaming\Python\Python39\site-packages\aiosqlite\cursor.py", line 31, in _execute
    return await self._conn._execute(fn, *args, **kwargs)
  File "C:\Users\PC\AppData\Roaming\Python\Python39\site-packages\aiosqlite\core.py", line 129, in _execute
    return await future
  File "C:\Users\PC\AppData\Roaming\Python\Python39\site-packages\aiosqlite\core.py", line 102, in run
    result = function()
sqlite3.OperationalError: duplicate column name: maxbank```

the command is working but that-

[somber niche]

@torn sphinx

u should have read properly xd

[torn sphinx]

ill check what duplicate means

oh

wdym i didnt

await cursor.execute("CREATE TABLE IF NOT EXISTS bank (wallet INTEGER, bank INTEGER, user INTEGER)") await cursor.execute("ALTER TABLE bank ADD COLUMN maxbank INTEGER")

[somber niche]

await cursor.execute("ALTER TABLE bank ADD COLUMN maxbank INTEGER")

remove this line now

[torn sphinx]

oh okay

[somber niche]

as because maxbank now created

so no need of this line

[torn sphinx]

OH

didnt know

thanku sm!

[somber niche]

told u on my last sentence xd

[somber niche]

ur welcome ;]

[torn sphinx]

Oh lol

[grim vault]

Be aware that after that:

await cursor.execute("CREATE TABLE IF NOT EXISTS bank (wallet INTEGER, bank INTEGER, user INTEGER)")
await cursor.execute("ALTER TABLE bank ADD COLUMN maxbank INTEGER")

Your table columns sequence is: wallet, bank, user, maxbank
You'll need to update your insert command accordingly.
You should also change the create statement to add the new column.

await cursor.execute("CREATE TABLE IF NOT EXISTS bank (wallet INTEGER, bank INTEGER, user INTEGER, maxbank INTEGER)")

[torn sphinx]

Oh thank u!

[night badge]

im scraping names and add new records to my db daily. what im interested in is counting the occurrence of the same names and then basically show the names with the most records.
i only need two columns. name and count. i have the scraping part finished but need some help as im new to databases. would already help if i know the sql functions and read up on them.

[twilit moss]

Im new to json databases and idk the error here:

#code:
users[author] = str(author)
    users[str(author)]['Wallet'] = str(0)
    users[author]['Bank'] = 0
#err
Traceback (most recent call last):
  File "/home/runner/uNomics/venv/lib/python3.8/site-packages/discord/app_commands/commands.py", line 862, in _do_call
    return await self._callback(interaction, **params)  # type: ignore
  File "main.py", line 59, in balance
    await open_account(int(author))
  File "main.py", line 77, in open_account
    users[str(author)]['Wallet'] = str(0)
TypeError: 'str' object does not support item assignment

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

Traceback (most recent call last):
  File "/home/runner/uNomics/venv/lib/python3.8/site-packages/discord/app_commands/tree.py", line 1242, in _call
    await command._invoke_with_namespace(interaction, namespace)
  File "/home/runner/uNomics/venv/lib/python3.8/site-packages/discord/app_commands/commands.py", line 887, in _invoke_with_namespace
    return await self._do_call(interaction, transformed_values)
  File "/home/runner/uNomics/venv/lib/python3.8/site-packages/discord/app_commands/commands.py", line 876, in _do_call
    raise CommandInvokeError(self, e) from e
discord.app_commands.errors.CommandInvokeError: Command 'bal' raised an exception: TypeError: 'str' object does not support item assignment

[warped turtle]

Is there a SQL query I can use to get the number of rows before a target point?
For example, lets say I have the days of the week in a database and I want to get the count of days before friday (starting at monday.) from my database.

[remote ginkgo]

First, JSON isn't a database.
Second, your value in users[str(author)] is a string

!e

""["meow"] = 0

[delicate field]

@remote ginkgo :x: Your 3.11 eval job has completed with return code 1.

001 | Traceback (most recent call last):
002 |   File "<string>", line 1, in <module>
003 | TypeError: 'str' object does not support item assignment

[torn sphinx]

Hello. I want to delete all records in a database after a certain time. I am using SQLite and Peewee as ORM. Should I schedule a task for this, if true, how do I do it?

[hollow crypt]

You mean records in a table? Schedule using a chron job, drop the table and then create one.

[torn sphinx]

Yes I meant table. I know I need cronjobs but how do I implement this?

[autumn pulsar]

async def newsuggest(interaction: discord.Interaction):     

    cur.execute("SELECT * FROM xx WHERE x = %s",
                (interaction.guild.id,))
    exist_channels = cur.fetchone()

    if exist_channels == None:
        await interaction.response.send_message("A first suggestions channel must be configured **/update-manager-channel** ⚠",ephemeral=True)
        return
  
    feedback_modal = FeedbackModal()
    feedback_modal.user = interaction.user
    
    await interaction.response.send_modal(feedback_modal)

class FeedbackModal(discord.ui.Modal, title="Send us your feedback"):
    
    message = discord.ui.TextInput(
        style=discord.TextStyle.long,
        label="Message",
        required=False, 
        max_length=500,
        placeholder="Write to your suggestion as you want thinking!"
    )
    
async def on_submit(self, interaction: discord.Interaction):
    embed = discord.Embed(title="💡 SUGGESTIONS | SUPERKENOS 💡", 
                              description=self.message.value, 
                              color=0xAC00FF)
    embed.set_footer(
    text=f"BY: {interaction.user} - ID: {interaction.user.id}", icon_url=interaction.user.avatar)

    cur.execute("SELECT * FROM xx WHERE x = %s",
                (interaction.guild.id,))
    exist_channels = cur.fetchone()

    if exist_channels == None:
        await interaction.response.send_message("A first suggestions channel must be configured **/update-manager-channel** ⚠",ephemeral=True)
        return

        message_channel = await client.fetch_channel(str(exist_channels[1]))

        message =  await message_channel.send(embed=embed)
        await message.add_reaction("✅")
        await message.add_reaction("❌")

        is_cancelleds = False

        message2 = await interaction.response.send_message(f"{Interaction.user.mention}, your suggestion has been successfully submitted! Check <#{exist_channels[1]}>", ephemeral=True)
        if is_cancelleds == True:
         return
        await message2.delete(delay=30)
        
    async def on_error(self, interaction: discord.Interaction, error : Exception):
        traceback.print_tb(error.__traceback__)```

Problem error no cant sent:

there look database "select from WHERE " and other..

[acoustic remnant]

hello there i wrote a query that order my table using rank() function but i want to make it ignore the rank for the equal values (like 1st/2nd rows) is it possible to achieve using rank or there is another specific function for this or what should i do to achieve what i want and thanks!

[shut forge]

helo

i have a python game with rectangle colors, how to save state of color of each rect in json, with lop is more easy ?

[wise goblet]

Sounds to me like aggregating functions.
Count (name) Group By Name
Or something like that

[shut forge]

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

this is my code @wise goblet

[heady cradle]

Hi

Im trying to create a view named ‘AgentJobs’ which displays the number of different
jobs carried out by each Agents

Confused on where Im going wrong I dont get why its not identifier

I just got this but Idk how to do it so it can show the number of different jobs

[shut forge]

its python ?

what plataform you use ?

@heady cradle

[heady cradle]

SQL ORacle

Not Python

Nah

[shut forge]

:/

you use python json ?

[heady cradle]

What?

[shut forge]

https://textdoc.co/AwCsrLba6mHSVT5t

[uneven lava]

using sqlalchemy for a postgresql db with asyncpg, should I create a new session for each request to the database (or group of requests if I need to) or should I create one session for the bot, and keep using one single session while the bot is running ?

[dense imp]

Architecture multi tenancy is recommended for Saas b2b?

[somber niche]

you want to add 1 more column or what?

[dark crescent]

mood

[brave bridge]

A bit of a theoretical question: unless you maintain indices, how do you combine pagination and sorting?

Is it just so impractical that the best way is to just index stuff? But what if there are just too many combinations?

[paper flower]

Most of the time you don't need to support arbitrary sorting

[gleaming arch]

I want to log my Discord status changes (online, dnd, idle, offline) via a bot, and use that data to draw a pie chart of how much time I spend with each status

what would be an optimal schema for this use case?

I have considered using something like

CREATE TABLE StatusLog(
    before TEXT,
    after TEXT,
    ts TIMESTAMP
)
``` which keeps a record of each status change that happened with *before* and *after*
but with this, I will have to fetch all the records and do a lots of calculations in the python code

is there an efficient way to do this? I am expecting some schema which lets me query time spent with each statues directly instead of calculating that in the python code

[paper flower]

You can store status changes, you just need your status and time when it was changed

Probably user_id too in case you want to support multiple users too

[gleaming arch]

In the table I posted, I plan to store the status before change as before and the changed one as after, the time is stored in timestamp
as for the user_id, I only plan to monitor my own status so I don't think I need it

how would the query look like, if I want to fetch time intervals of each statuses (without having to calculate that in the python code)

ex:

status  | time
---------------
idle    | 1h
online  | 2h
dnd     | 3h
offline | 4h

[grim vault]

Something like:

SELECT "status", SUM("time")
  FROM (
    SELECT "before" as "status",
           "ts" - lag("ts") OVER (ORDER BY "ts") AS "time"
      FROM StatusLog
    ) as A
 GROUP BY "status";

You can limit the inner select with WHERE "ts" between <starttimestamp> and <endtimestamp>

It's using the window function lag() for the previous timestamp to calculate the difference.

[gleaming arch]

oh thanks, I will need some time to read and understand what's going on there

[grim vault]

You can play here: https://www.db-fiddle.com/f/gGMJY2H1tqycPuUmVtKEre/0

[worthy shell]

hi, im trying to get last row from an sqlite3 table

what query can i use to achieve this?

i don't have a primary key column in my table, or any other column with numeric values that i could ORDER BY

[gleaming arch]

TYSM

[fading patrol]

https://stackoverflow.com/questions/9902394/how-to-get-last-record-from-sqlite

[worthy shell]

Thanks

[uneven lance]

does anyone know why this offset happpens when i try to insert a list of strings into a column?

for item in strings:
        cursor.execute(f"INSERT INTO {table} ({column}) VALUES (?)", (item,))
conn.commit()

i dont know why there are so many null spaces above it

for some reason it inserts it at whatever row the other column stops at

[torn sphinx]

If it’s “null” then you inserted no value for that column or set it to be null. Check the queries that are generated, and see what’s actually being run. And a side note, building queries with f strings like that can expose you to vulnerabilities like sql injection.

[uneven lance]

Oh yes this is not going to be for outside use, i am just using this for a personal project. And when i print the queries it prints what i am expecting

its not that its inserting the items wrong

for the first time it works fine

but then when i try to insert another item in a different column it puts it at where the other columns left off

how do i make these columns act independently?

[grim vault]

One insert equals one row. If you only insert one column, all other columns of that row will be null.

[uneven lance]

Oh

is there anyway around that?

[grim vault]

That depends on your data handling. Your insert can use more than one column or you can update a column of a ow later on.

[graceful widget]

how do i make exception for this

[somber niche]

try:
...
except:
print("error happened")

except will handle all error

[graceful widget]

nono

i want to handle that specific error only

[graceful widget]

.

[somber niche]

using sqlite3?

or...?

[graceful widget]

postgre

[somber niche]

demn

[torn sphinx]

idk if this is right channel for this but my help post went dormant can anyone can help

ftp = FTP()
ftp.connect(HOST, PORT)
ftp.login()
ftp.cwd(path)
files = ftp.nlst()
for file in files:
  try:
    ftp.retrbinary("RETR " + file ,open(download_folder_path + file, 'wb').write)
ftp.quit()

I am trying to download multiple files from an FTP folder but my ftp.nlist is not working ftp.dir wont work in its place

Error
- 502 Sorry, command not implemented. :(
- files = ftp.nlist()

[somber niche]

#1035199133436354600 will be good for you

[torn sphinx]

I posted and it said it was closed because it was dormant

[grim vault]

You can do a select first and check if there is an entry already or use the ON CONFLICT clause of the insert to handle it in SQL.
Pseudo code:

cursor.execute("select * from armors where playerid = %s", (user.id,))
data = cursor.fetchone()
if data is None:
  curs.execute("insert into armors ...")
else:
  curs.execute("update armors set ... where playerid = %s", (..., user.id))

curs.execute("insert into armors ... on conflict(playerid) do ...")

[graceful widget]

aight thanks

never know ON CONFLICT is a clause

[grim vault]

It's general referred to as UPSERT (update or insert).

[gilded holly]

Hi ppl
i wanna ask for some help in related to apache airflow

i do wanna replicate my db, into leader db and follower db in my project
and can you help me by charing with me some resources and any idea that can help me

[solemn lava]

I want to ask if a Collation simply means Rules for how to sort or anything beyond that?

[torn sphinx]

I need some1 good with phpMyAdmin to help me please

[floral cobalt]

This is my error:
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Deck.children_deck - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

HOW DO I LINK A TABLE TO ITSELF

LIKE A SET OF CARDS TO A SET OF CARDS

[vocal parrot]

I have no idea, but what I would have done is have a new table with 2 columns that both are foreign keys to the same table

[floral cobalt]

class Deck(db.Model,UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    Name = db.Column(db.String(300))
    date = db.Column(db.DateTime(timezone=True),default=func.now())#func gets current date and time and stores it as a default value
    #Store the foregin key in the child object for the parent, Classname(lower case).primarykey column name
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    children_deck = db.relationship('Deck')
    cards = db.relationship('Card')

this was my code

[vocal parrot]

Codeblock please

[floral cobalt]

im trying to have deck table that can have a deck child

but i dont know how to do this

i did children_deck

but thinking about this now, i would also need a parent_deck?

[vocal parrot]

Oh well true

[vocal parrot]

From what experience i have with orms, their models seem to break when you have multiple relations to the same table. Rather implement it yourself like i said in this ^

[floral cobalt]

how would i code that as i get an error rn
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Deck.children_deck - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

[floral cobalt]

i dont understand that

new table with 2 columns that are both foreign keys to what table?

[vocal parrot]

The Deck table

Sorry but I've never used sqlalchemy. I've used django and tortoise

You should wait around for someone with experience in sqlalchemy to help you

[floral cobalt]

whats the best way to learn django btw

[vocal parrot]

Uh idk youtube?

And examples in docs and github

[floral cobalt]

so many oudated videos

that use older versions of django

[vocal parrot]

I learnt like this

[vocal parrot]

And lot of hours spent debugging 💀

[floral cobalt]

rip

[woven roost]

databases are cool

[full mountain]

gyes what is the best database for like a startup which is cheapst

[proven root]

hey im useing mongo db and experanceing this error in on a shared cluster any help

pymongo.errors.OperationFailure: bad auth : authentication failed, full error: {'ok': 0, 'errmsg': 'bad auth : authentication failed', 'code': 8000, 'codeName': 'AtlasError'}```

i haven't changed anything about my code it just apeared

[modern pike]

hello! anybody with experience in oracle business intelligence(obi)? I am trying to automate pulling accounting subledgers from obi using python for my finance team, but am unable which module to use. Is cx_Oracle the correct module? i cant find anything about obi in cxoracle docs

[vocal parrot]

PostgreSQL is quite good. It scales vertically nicely

[proven ginkgo]

can i connect asyncpg with phpmyadmin database?

[fading patrol]

The error says authentication failed, so verify the username/password

[proven root]

That’s the thing it’s right tho

[maiden acorn]

I need help. I keep getting this error:

Error: Undefined binding(s) detected when compiling SELECT. Undefined column(s): [destination] query: select * from [OffreDeplacements] where [destination] = ?

[jagged frigate]

ive learnt the MySQL syntax and heard that PostgreSQL would be prob a way better DBMS, so i was considering to change databases, however is there any specific syntax differences between these 2 or is very similar

[mild rivet]

Do primary keys make queries faster in a nonrelational database?

[rapid ginkgo]

Please tell me how can I update number_of_registered field. Can't write proper query in MongoDB for this to work

[wise goblet]

Postgresql is cheapest and most sane choice in general

[haughty flame]

class Database:

    def __init__(self, config: dict[str, Any]) -> None:
        self.config = config


    async def create_pool(self):
        async with asyncpg.create_pool(dsn=self.config["postgres_url"]) as pool:
            self.pool = pool
            async with pool.acquire() as conn:
                self.conn = conn
```Hello, I have no idea how to use asyncpg, is this how you do it?

[empty willow]

Which is the best data structure to represent table attributes as class variable

[haughty flame]

Does this structure make sense? I'm trying to rewrite my MongoDB to PostgreSQL.

{
  $jsonSchema: {
    properties: {
      guild_id: {
        bsonType: 'long',
        description: 'must be long and is required'
      },
      author_id: {
        description: 'must be long and is required',
        bsonType: 'long'
      },
      title: {
        bsonType: 'string',
        description: 'must be string and is required'
      },
      link: {
        bsonType: 'string',
        description: 'must be string and is required'
      }
    },
    bsonType: 'object',
    required: [
      'guild_id',
      'author_id',
      'title',
      'link'
    ]
  }
}
```This is the schema for mongo.

[vocal parrot]

Is there a need for the guildauthor table? You can just add both the guild and the author to submission

[torn sphinx]

hello i have quesstion how can i download browser-cookie-3???

[wise goblet]

Picture of scheme looks good enough to me being applied to SQL.
At least assuming u made correctly, that one author can register in multiple guilds.

Oh. Except one thing

U have in author table Id, and author_id
But they serve same purpose. Delete one of them.
Same for Guild table.

If u use raw SQL, I recommend deleting id named attributes. It will be more comfortable to use foreign key as table-name+Id.
If u use ORM, then just id is fine too

[wise goblet]

Also missmatch in data types. U link foreign keys in another tables as big int to just int. Fix to anything but of same type

[twilit marsh]

please can I ask you what you mean by data migrations, i am currently using mongodb and wish to be aware of pitfalls

[wise goblet]

Lets say you have application version 1. You already set Schema of your tables.
And then u made application version #2, added new table, or deleted some table.

SQL has mechanism for ALTER TABLE/COLUMN whatever, that will migrate whole data of your current users from previous application schema to new one, not just schema, but your data too!
Works well with Python Django ORM and SQLAlchemy/Alembic at least.

They make data migration to database, and record at its innerside, which were already applied and which not.
So when u create new migrations and application versions, only not applied one will be applied to bring your data according to new application version schema.

You are confident that.. all your data matches current application version.
Especially considering that Postgresql is having good typing and all columns defining schema. You are confident in data integrity between different tables.
Postgresql/SQL was made for this, and different stuff like FOREIGN KEY ON DELETE PROTECT/CASCADE whatever, will make sure your foreign key relationships between tables will be matching always too.

basically... u will not have surprises after u decided to rollout new version of application :/ Or tenth, hundrenth version.

[twilit marsh]

in depth answer seemingly, thank you!, just reading

[twilit marsh]

just read, i didnt understand everything but im saving to re read later, i imagine setting up a function to alter python dictionaries to a new format would be similar to writing an ALTER TABLE statement no?

[wise goblet]

technically yes, but still less reliable.
SQL database has data type enforcement and no extra possible column(attribute) can exist, or can be missing. Everything is enforced by SQL CONSTRAINTS(and its Scheme) to be valid
Including ensuring that Foreign Key is trully present in another table which was linked to another one (and in time child objects were deleted if they aren't longer needed)

[vocal parrot]

Hey can you tell how you can migrate data to newer structures with django orm? Previously when I've wanted to do that, i use asyncpg and raw SQL queries to do it myself

[wise goblet]

change ORM models, run python3 manage.py makemigrations to create new migrations?
run python3 manage.py migrate to apply them

(optionally u can even write raw SQL migrations as part of the chain of migrations)

[vocal parrot]

Oh hmm ic

Thanks I'll look into it

[soft gorge]

  table_a 
  value1(references table_c value1)
  value2(references table_c value2)
  
  table_c
  value1
  value2

What is the most logical way to create a trigger that will ensure that when ever an insert is occurred on table_a that an insert will occur on table_c and is this a good practice?

[wet linden]

Good day! I am new to SQLite3 and DB Browser. I would want to add autoincrement to one of my columns
I am only given these options. And I still can't find a way to edit the query of the table (even after searching the internet). I would appreciate your help here. Thank you!

actually i found the autoincrement function already HAHAHA

[ocean tapir]

Sup, I'm trying to figure out a way to find all documents in a collection where any EmbeddedDocument in a MapField where a certain field is greater than or equal to a number. Is there any way to do this at all, or would I just have to loop through every single document and check manually?

FYI I'm using MongoEngine, so a PyMongo query would work just fine.

For the structure of my document:

Guild:
    Giveaways (MapField):
        id - Giveaway:
            end (timestamp)

I just want to check if the current time is bigger than or equal to the end time for any giveaway in the documents. (I want to list all the documents meeting the condition)

[sullen glacier]

Hello everyone !
I am wondering what kind of database solution would be the best in my case.
I have an ETL coming from ERP that I do transform.

I need to save the reworked data so that an API can call those reworked datas and supply different tools such as Salesforce, internal applications, BI etc

What would be your choice in term of intermediate storage here?
I thought of course of an Amazon RDS database but I don't want to create a normalized database here just saving datasets.

The database will be queried many times per day and for some under a batch architecture every minute.
No streaming imagined yet.

Thanks !

(I could use S3 of course and save flat files overwritten regularly. The API would read and send data.
I do this actually locally at the moment and it works fine. Is that something pro? Or you d never do this in a prod context?)

(+ other related question, what kind of EC2 instance would you choose for a prod session to be sure that the processing and the answer can be quickly handled.
I planned a Flask or FastAPI development)

[primal path]

the index keeps being printed even with the df.reset_index(drop=True)

fine, you?

good luck!

[soft gorge]

What would be the most ideal way to save notes for a specific row in it's own column or is that something that shouldn't be done?

[torn sphinx]

Anyone know a good place to host a sql database for free? like none locally?

[sullen glacier]

HI it depends on the size of the DB?

[torn sphinx]

I'm doing a small project to practice

I'm trying to make a basic login/registration script

[torn sphinx]

Eh nvm

[paper flower]

Why not host it locally if you're just practicing?

[torn sphinx]

I wanna advance ofc

[paper flower]

?

[torn sphinx]

I am bored

[sullen glacier]

well for small practicing project lots of hosting db suppliers offer free services
You re looking for relationnal databases exclusively?

[sullen glacier]

give a try to supabase then @torn sphinx. I think it s cool for testing projects

[vocal parrot]

You can host a database on railway.app

[vestal oracle]

does mysql-connector-python 8.0.31 supports mysql version 5.1.33 ?

[vocal parrot]

Idk but i feel you can really benefit from SQL relations

[hard roost]

is it possible to use excel as database

you need a server

[torn sphinx]

J

K

[soft gorge]

What would be an easy way to ensure that a value is not negative inside of an on conflict

ON CONFLICT(value) DO UPDATE SET count = table.count - value OR 0 IF table.count - value < 0
something like this

[grim vault]

Use the case statement:

... UPDATE SET count = CASE WHEN table.count > value THEN table.count - value ELSE 0 END```

[sage raft]

Try to look the website x10hosting, it’s a local to host your site and have a place to publish your database

[torn sphinx]

Ty

[wise night]

Can anyone let me which datatype is used to store an image in the database?

[soft gorge]

Thank you so much man!

[upbeat marsh]

What is the best database regardless of price

[dreamy storm]

Opinion ofc, but in my eyes a nice beefy Postgres database on a humungous VPS

Postgres is imo the most robust of the SQL databases, not incorporating nosql into consideration

!d aiosqlite.Connection.close

[delicate field]

Nope.

No documentation found for the requested symbol.

[dreamy storm]

Oh

Well That

[peak lion]

How can i stop this?
async with aiosqlite.connect("maja.db") as db:

[dreamy storm]

Ah context manager

db.close()

[peak lion]

Okay and the Connection is gone

[dreamy storm]

Although the context manager should handle that itself upon exit

Nice to include regardless in case of Exceptions though

[peak lion]

Ignoring exception in on_application_command_error
Traceback (most recent call last):
  File "c:\Discord\Maja Projekt\MajaSystem_Test\modules\setup_new_v2_00\cog.py", line 48, in test_acces_one_role
    async with db.cursor() as cursor:
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\aiosqlite\context.py", line 44, in __aexit__
    await self._obj.close()
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\aiosqlite\cursor.py", line 69, in close
    await self._execute(self._cursor.close)
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\aiosqlite\cursor.py", line 31, in _execute
    return await self._conn._execute(fn, *args, **kwargs)
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\aiosqlite\core.py", line 130, in _execute
    raise ValueError("Connection closed")
ValueError: Connection closed```

Traceback (most recent call last):
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\nextcord\client.py", line 489, in _run_event
    await coro(*args, **kwargs)
  File "c:\Discord\Maja Projekt\MajaSystem_Test\bot.py", line 174, in on_application_command_error
    raise error
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\nextcord\application_command.py", line 888, in invoke_callback_with_hooks
    await self(interaction, *args, **kwargs)
  File "c:\Discord\Maja Projekt\MajaSystem_Test\modules\setup_new_v2_00\cog.py", line 47, in test_acces_one_role
    async with aiosqlite.connect("maja.db") as db:
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\aiosqlite\core.py", line 161, in __aexit__
    await self.close()
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\aiosqlite\core.py", line 179, in close
    await self._execute(self._conn.close)
  File "C:\Users\domin\AppData\Local\Programs\Python\Python310\lib\site-packages\aiosqlite\core.py", line 75, in _conn
    raise ValueError("no active connection")
ValueError: no active connection```

    @nextcord.slash_command(name="test_acces_one_role", description="-----", guild_ids=[config.TESTGUILD])
    async def test_acces_one_role(self, inter: nextcord.Interaction,
                                  option: str = SlashOption(name="option", description="Please choose an option", choices=["activate", "show", "delete"])):
        

        match option:
            case "activate":
                async with aiosqlite.connect("maja.db") as db:
                    async with db.cursor() as cursor:
                        
                        #Check if id is already in setup
                        await cursor.execute('SELECT id FROM setup WHERE id = ?', (inter.guild.id,))
                        
                        data = await cursor.fetchone()
                        print(data)
                        
                        if data is None:
                            view_select = admin_role_select_role_test(inter)
                            reply = nextcord.Embed(description=f"{config.DiscordSupport} Please choose an option.", colour=config.blurple)
                            await inter.response.send_message(embed=reply, view=view_select)
                            view_select.message = await inter.original_message()
                            await db.commit()
                            await db.close()
                            
                        else:
                            pass```

[vale echo]

I am so lost and stuck about intergrating MongoDB with flask. Any help would be great

[torn sphinx]

perhaps you know @low chasm (posting here because help channel closed)

[torn sphinx]

i see. i'm still a little confused because it seems like user authentication is different to what i used when using firebase in android studio/java

pretty sure i didn't do any of that before

[paper flower]

What's the problem?

[vale echo]

I added the URI link for in my python script I keep on wanting to connect to the DB but throws me an SSL Certificate error

[torn sphinx]

Are there any test/blogs about out of ram performance with the most common databases? Especially text search based related databases.

[supple mirage]

include validation checks to make sure that the ward number has whole
number values from 1 to 10, and that the bed number has whole number values
from 1 to 8.
where and how do i type???
idk if this is the right channel or not

[earnest mauve]

how can i handle a select statement that found nothing

[earnest mauve]

nvm i figured something out

[hasty quest]

HI

[torn sphinx]

Hello i didn't understand this sentence. What dump means ?

Yeah it looks like any sql dump file which you can dump into a mysql instance and then use that database as any regular sql db

I asked to someone to extract data's from a GitHub database

[lucid topaz]

a dump is a kind of backup of a database

so when they say you can dump in to.. they mean you can restore it to a mysql database

[torn sphinx]

hmm

is this easy ?

[grim knoll]

why is pymongo not working? it says FileNotFoundError: [Errno 2] No such file or directory: '/etc/resolv.conf' whenever i try to make a client connection

[torn sphinx]

Hello, I was recommended to enable WAL mode while using the aiosqlite library for a new Discord bot project. Some extra files were added to the same folder as my main.py script when I ran the code:

    async def open_conn(self):
        self.db = await aiosqlite.connect('data.db')
        await self.db.execute('PRAGMA journal_mode=wal')

Can anyone explain what those extra data.db files are and what they do? And is WAL mode necessary for a bot that's currently in ~50 servers? Thanks

[torn sphinx]

hi anyone knows why i cant start the server please i dont know

[tranquil glen]

perhaps the installer cant make the datadir, so i guess it is lacking permission to do so?

[torn sphinx]

and how can I fix it?

if u have an idea ofc

[vocal parrot]

WAL is write ahead logging

Enabling that mode enables concurrency between reader and write. Most of the times it's decently faster than the normal mode

Check this out
https://sqlite.org/wal.html

WAL mode might be insignificantly slower (1-2%) if your read operations greatly outnumber the write operations

I found this-
https://news.ycombinator.com/item?id=26108042
It seems to improve performance

polyrand

Regarding SQLite's performance, some things I've found very useful:Use WAL mode (writers don't block readers): PRAGMA journal_mode = 'WAL'

Use memory as temporary storage: PRAGMA temp_store = 2

Faster synchronization that still keeps the data safe: PRAGMA synchronous = 1

Increase cache size (in this case to 64MB), the default is 2MB PRAGM...

Hacker News · Feb 11, 2021, 10:08 PM

[vocal parrot]

Try running the installer as administrator

But I'm not sure if that helps

[waxen finch]

in the case of "concurrency benefits", i believe WAL only really matters when you actually have multiple connections to the same database, for example two commands like this: ```py
@bot.command()
async def reader(ctx):
async with aiosqlite.connect('data.db') as conn:
... # read some stuff from the database

@bot.command()
async def writer(ctx):
async with aiosqlite.connect('data.db') as conn:
... # write some stuff``` if there are multiple commands running at the same time, and the database is using a rollback journal instead of the write-ahead log, the writing connection will block the other connections and potentially time them out with the exception, sqlite3.OperationalError: database is locked - with WAL, writer connections only block other writer connections

but since you have a single, global connection, it shouldn't matter too much

[vocal parrot]

I've heard it's not a good idea to use a single global connection, but rather create a new one every time you need it

But I'm not sure if that's true

[waxen finch]

i think the main risk is mixing up state from two transactions, which is why i personally combine it with a lock to prevent concurrent transactions

this is a good read too https://sqlite.org/atomiccommit.html#_single_file_commit

the writer needs an exclusive lock before it can start writing but it cant gain that exclusive lock if there are existing readers, which presumably isnt the case with WAL

[vocal parrot]

Oh hmm

[supple mirage]

include validation checks to ensure that 8 characters must be entered starting
with HN followed by 6 digits for example HN123456

[torn sphinx]

Yo i want to dump a GitHub database into my sqldatabase someone can help me please ?

[uncut moss]

Anyone know what FID as a db-field might stand for? I got a db from someone i dont know and it has objects called 'Stations'.. It has a 'station_id' but also a 'FID' field... i wonder what the original db creator intended...

it determines whether i should use station_id as PK or the FID

the CSV file had data with FID incrementing from 1 upwards but 'Station_id' runs from 200 or so upwards

[torn sphinx]

Hello, I should have made this more clear, it's not a 'single global connection' as such. The function I showed is a method from a class UserData that I've created to deal with reading/writing user data. So in another function (such as a bot command) I might have something along the lines of:

@commands.command()
async def money(ctx):
  user_data = UserData(ctx.author)
  await user_data.open_conn() #  Opens a DB connection
  await user_data.edit_balances(money_delta=25)  #  Add money, XP etc to the user's account
  await user_data.close_conn() #  Closes the DB connection

It is indeed possible for me to have multiple connections at once, but I'm usually only opening a connection when I'm reading or writing. So I'm guessing WAL mode will indeed help me with performance in this case? I hope this makes it more clear.

[torn sphinx]

Thanks for the help!

[waxen finch]

oh in that case yeah, wal will reduce the need for writers to copy the database's contents and should make sense here

though i suggest rewriting your api to support the asynchronous context manager protocol because if an error happens to occur before your close_conn(), it might prevent aiosqlite's worker thread (and the underlying sqlite connection) from closing properly

[torn sphinx]

Alright. Thank you very much :)

[soft gorge]

  INSERT INTO transactions_count(id, current_count) VALUES(9821, 0) ON CONFLICT(id) DO UPDATE SET current_count = transactions_count.current_count + current_count; 

When I try to use this method to add to a value on conflict I get an error:
column reference "current_count" is ambiguous
I can't figure out what i should references to add the two values together

[molten sparrow]

Hello, how are you? I leave the following link to see if someone can help me http://linkode.org/#JJK7Le7E2Qa16zcsiJTPr4 , the issue is that it gives me the error django.db.utils.OperationalError: no such table: users_user and indeed not Create the USERS table in my database, I already deleted the database, I created it again, I deleted the migrations mask that is created when doing the migrations, could someone please help me

[pastel wren]

anyone have recommendations for db hosts? I'm frustrated with azure being complicated on hooking up my webapp to a new db I'm making.

[grim vault]

If you want to use the values of the insert command you use excluded as table name to use them:

INSERT INTO transactions_count(id, current_count) VALUES(9821, 0) ON CONFLICT(id) DO UPDATE SET current_count = transactions_count.current_count + excluded.current_count;

This would add 0 to the stored value (because 0 is the value for current_count of insert).

[lament parcel]

I'd recommend managed database service from digitalocean, very easy to setup and get started, you could also try Amazon RDS

[floral cobalt]

How do I add a subdecks to each deck of cards
parent_id = db.Column(db.Integer, db.ForeignKey('deck.id'))#This is the foreign key for the parent deck children_deck = db.relationship('Deck', backref=db.backref('parent', remote_side=[id]), primaryjoin='Deck.parent_id == Deck.id')

[torn sphinx]

Btw, big shoutout to https://quickwit.io/. If you guys have a huge ton of text data to index (logs, etc.) and you are running on a budget (not "unlimited" ram), give it a try. It solved my problems greatly. My dataset was around 400GB text and mongodb for example ate my ram like its nothing. Lookup times under 1s were acceptable for me. Currently I am having a lookup time of 0.3s on 400GB text only consuming under 1GB of ram. And this runs on old and slow HDDs. This is not an ad, but something that really helped me.

[torn sphinx]

hello
I can't install mysql server someojne can help me?

[light pendant]

Does anyone know how "expensive" it is to expunge entities from a session with SQLALchemy? I'm trying to decide between that or grabbing the columns as scalars for use in producer/consumer background jobs

[hard burrow]

sqlite_file_name = "test.db"
sqlite_url = f"sqlite+aiosqlite:///{sqlite_file_name}"

bot.engine = create_async_engine(sqlite_url)

ValueError: the greenlet library is required to use this function. DLL load failed while importing _greenlet: The specified module could not be found.

[paper flower]

You just disassociate object from your session, it should be pretty cheap

[paper flower]

Which sqlalchemy version are you using? Sqlalchemy 1.4+ should install greenlet too

[hard burrow]

https://pypi.org/project/SQLAlchemy/

The newest

[paper flower]

Can you check?

[hard burrow]

SQLAlchemy 1.4.45

[paper flower]

Maybe install greenlet manually

[hard burrow]

i did it using pip

but nothing changes

[hard burrow]

C:\Users\Administrator>python -m pip install greenlet
Requirement already satisfied: greenlet in c:\users\administrator\appdata\local\programs\python\python39\lib\site-packages (2.0.1)

[paper flower]

Are you sure you're not running your project inside of a virtualenv?

[hard burrow]

its a discord bot

[paper flower]

It does not matter

[hard burrow]

no i dont, i have python 3.9

[brazen hornet]

Hello guys, I have a problem with sqlalchemy. I'm trying to write an API with fastapi which communicates with mysql. I have used the asyncio library for database transactions but I have problem with native queries. The error is sqlalchemy.exc.NoSuchColumnError: Could bot locate column in row for column 'user.user_id'. In this query I have 4 Join

And I have got many problems with more complex textual queries

Have anyone suggest me anything?

The sexond problem I haven is: sqlalchemy.exc.InvalidRequestError: Ambiguous column name 'user.user_id' in result set column descriptions

[pastel wren]

thank you. i'll check it out

[paper flower]

Are you sure that column exists?

[hard burrow]

any fix?

[grim knoll]

anyone?

it only happens when i add +srv to the hostname

[paper flower]

Same version of sqlalchemy works for me, I don't know what's wrong with your environment

[opal sandal]

Hello

Guys is there a way where i can get the output in a particular format
like, i have two database tables and i am comparing them right now, getting all the values that are mismatch using python
but i want all the values to display in a particular format

these is the output i am getting which is right but i want the mismatch values of a particular column to display in a single row, as you can see in the picture the mismatch value for 1315 is printed twice

i want the values to print somewhat like these
my english is not very good so i dont know how should i explain this in more detail, but if anyone helps me pls feel free to ask for more details
import pandas as pd
import datacompy
CAWD=pd.read_excel("E:\Microsoft Excel\Databases\cawd_original.xlsx")
MARK=pd.read_excel("E:\Microsoft Excel\Databases\mark_revised.xlsx")

camparison=datacompy.Compare(CAWD,MARK,join_columns="PAGE,C,4")

print(camparison.report())
these is the code

is there anything i should try instead of report

[opal sandal]

anyone?^

[brazen hornet]

Yes Im sure because when I execute a simple query with that column it doesn't seem any problem. Generally I have many issues when I want to execute more complex query with asyncio and 2.0 ORM style. I don't find clear examples or tutorials and it seems a little bit chaotic the official docs

[paper flower]

Official documentation has a tutorial 🤔

[brazen hornet]

Yes off docs have tutorials but for basic queries I didnt find something for advance queries yet. I used the method execute () and without method text () and all the problems have solved! But I don't know why. Before that I tried with select () and doesn't work

[torn sphinx]

w

[lofty hull]

hello, im unable to recollect my root password for mysql

what am i to do

i dont have any service of the server running as well

when i fresh install it requires me to reconfigure and im unable to recollect my password

[dreamy storm]

Do you have access to the installation directory?

[lofty hull]

is this the installation directory? @dreamy storm

[dreamy storm]

Looks like it

Okay so you're gonna wanna start the server in safe mode

[lofty hull]

windows safe mode then start server?

@dreamy storm

[dreamy storm]

Not windows safe mode, mysql safe mode

mysqld --skip-grant-tables

[lofty hull]

oh

i dont have access to cmd line

[dreamy storm]

You don't have access to cmd?

[lofty hull]

it immediately closes as i open

no mysql cmd line i meant

in normal cmd line^^

do i add to path?

[dreamy storm]

Mysqld should be how you invoke mysql server

How have you been invoking it in the past?

[lofty hull]

im unsure if i ever called it in cmd

@dreamy storm i dont have the bin folder as the installation is not complete how do i add to path now

fixed nvm

ty for ur time

cya

[floral cobalt]

How do i have nodes in falsk-sqlalchemy, as in self-relationships

[floral cobalt]

AND SHOULD I PUT MY METHODS WITH THE MODELS FILE OR IN THE VIEWS FILE WITH THEIR OWN CLASSES

[dense imp]

guys i am with a small problem in a design of my database

anyone can help me?

[dense imp]

i need create a way of create a tabela of purchase order
with fields number_order, name and description

but can make a purchase_order with a lot name and description how i do that?

[boreal magnet]

Do you just need to create a table with those fields?

[soft gorge]

   CREATE TABLE test(
    id BIGINT NOT NULL
    hex_color VARCHAR(7) DEFAULT "0x5865F2"
);

When I try to create a table and set the hex value as a default i'm getting error cannot use column reference in DEFAULT expression
Even when I try to set this value as TEXT DEFAULT it's giving the same error. Any idea why?

[light pendant]

The general wisdom I see online is that database entities should not have any logic associated with them, i.e. they're just dumb containers of data. However, most resources I come across on this are very Java/C# centric, and I'm wondering if the same principles apply when working with databases in Python (since there are many patterns from strict OOP languages that don't translate well to Python)

[torn sphinx]

Isn't there a comma missing after NOT NULL?

[grim vault]

String literals needs to be enclosed with single quotes '. It also looks like the length is 8 not 7.

CREATE TABLE test(
  id BIGINT NOT NULL,
  hex_color CHAR(8) DEFAULT '0x5865F2'
);

Double quotes are used for SQL identifiers, that's why you get the column reference error, because the SQL parser thinks "0x5865F2" is a column name in this context.

[hard burrow]

Code:

engine = create_async_engine("sqlite+aiosqlite:///:memory:")

the greenlet library is required to use this function. DLL load failed while importing _greenlet: The specified module could not be found.

[hearty token]

Hi guys, i'm currently working with pymysql and i'm in need to specify an execution time limit of a query. Unfortunately this is not implemented in the connector. Does somebody know what the best practice is for implementing such a query maximum execution time. I know of a couple solutions but i'm wondering what the best solution is.

[hearty token]

Sure, i can just run a timer and kill the query with a linux signal on the client side. However, idk if this is really a good solution. I'm just wondering if somebody maybe has encountered the same problem and was able to solve it in a clean and well thought out way. Thank you.

[grim vault]

~~You can setup a threat which will call the interrupt() method of the connection: ~~ SQLite, sorry. Maybe pymysql has something similar.

[torn sphinx]

hello
anyone knows how to transfer data from a github's databse to a mysql databse ?
https://github.com/doodspav/LegendLeagueSeasons
transfer this into my db

[vocal parrot]

Download the SQL file you want data from
And then check this
https://stackoverflow.com/questions/17666249/how-do-i-import-an-sql-file-using-the-command-line-in-mysql

[torn sphinx]

alr thank you i'll check

[silent grotto]

How to convert db query to txt file?

Sqlite3?

Is there any example?

[smoky latch]

You should do it in the backend, what about writing the query in a pandas dataframe and then using .to_string ?

with open(writePath, 'a') as f:
    dfAsString = df.to_string(header=False, index=False)
    f.write(dfAsString)

[hearty token]

Yeah i took a look at the github repos of some common python/c# connectors who implement such a functionality and the common way to implement this seems to be: setup a timer with threading and then kill the query on the database backend ("KILL QUERY {pid}"). So quite like you described.

[unkempt prism]

a csv is just a text file that is a little more opinionated. See https://stackoverflow.com/a/10522863

[soft gorge]

Thank you for the clarification!

[smoky hare]

Hi, I'm not much of a database guy, but any easy to read way to transfer data from sqlite to postgresql? I have a .db file.

[silent grotto]

Mmm good idea then I have to import it...

[silent grotto]

Doesn't csv has excel structure?

[unkempt prism]

Excel also opens txt files and views them neatly as a spreadsheet so what’s your point?

[silent grotto]

I am doing this project in Android

So they need to install excel software to view csv file

Isn't it easy to view if I convert db query to txt file?

As every android device has txt viewer....

[unkempt prism]

Nope. A csv is viewable with a text file too.

[grim vault]

CSV is a text file which uses Commas to Separate the Values inside the file.

https://www.rfc-editor.org/rfc/rfc4180

[silent grotto]

Mmm got it

Didn't have an idea aboit csv extension ;)

[calm obsidian]

why database when you can just write the values in a text document and then pull it from a text document like silly old me

[pure cypress]

Because a database is more efficient and can offer some guarantees about the data (e.g. Referential integrity, acid, etc)

[real timber]

How to connect to a postgres docker container, using psql from the host?

eg for the following:

docker run --name example --rm -d -e POSTGRES_PASSWORD=password -e POSTGRES_DB=db -e POSTGRES_USER=user -p 5432:5432 postgres:14.6-bullseye

Once that's running, how can I connect to it with psql ? I tried:

psql -h localhost --port 5432 --dbname db -U user

But this returns an error

psql: error: connection to server at "localhost" (::1), port 5432 failed: server closed the connection

And I'm not sure how to fix, though I was under the impression that connecting to the container with psql should be fine

[unkempt prism]

"localhost" (::1)

My first guess is that you have an IPv6 issue.

localhost resolves to the loopback of 127.0.0.1 with IPv4 and ::1 with IPv6

Try to replace localhost with 127.0.0.1 as below and see if that helps.

psql -h 127.0.0.1 --port 5432 --dbname db -U user

[real timber]

same error unfortunately

$ psql -h 127.0.0.1 --port 5432 --dbname db -U user 
psql: error: connection to server at "127.0.0.1", port 5432 failed: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

edit - sorry, not exactly the same error - but it didn't work

[unkempt prism]

Lets try the docker name and use that.

psql -h example --port 5432 --dbname db -U user

[real timber]

Hm :

$ psql -h example --port 5432 --dbname db -U user
psql: error: could not translate host name "example" to address: Temporary failure in name resolution

I tried that -h example again, failed the same way

[pure cypress]

Are you sure the pg container is actually runnng and healthy

[unkempt prism]

hate to say it though works for me!

t@ubuntu:~$ docker run --name example --rm -d -e POSTGRES_PASSWORD=password -e POSTGRES_DB=db -e POSTGRES_USER=user -p 5432:5432 postgres:14.6-bullseye
2f0903290e7ef8a106484a88d3e2d31e5dd0cb1264e739be3a10d40292c7be5d
t@ubuntu:~$ psql -h 127.0.0.1 --port 5432 --dbname db -U user
Password for user user: 
psql (15.1 (Debian 15.1-1.pgdg110+1), server 14.6 (Debian 14.6-1.pgdg110+1))
Type "help" for help.

db=# exit
t@ubuntu:~$ docker ps
CONTAINER ID   IMAGE                    COMMAND                  CREATED          STATUS          PORTS                                       NAMES
2f0903290e7e   postgres:14.6-bullseye   "docker-entrypoint.s…"   54 seconds ago   Up 53 seconds   0.0.0.0:5432->5432/tcp, :::5432->5432/tcp   example

[real timber]

it shows up in docker ps, not sure how to comment on health though

[pure cypress]

Well try restarting it as an easy fix hopefully

[real timber]

I'll delete and re-run the command, but i've done that several times :S

[pure cypress]

You can launch a shell within the pg container and try to connect to it from within

[unkempt prism]

is it MacOS? I vaguely possible the is a checkbox somewhere to allow networking.

[real timber]

No I'm on ubuntu

[real timber]

to check things? bc i want to connect to it from the host

[pure cypress]

Yes just to check

[real timber]

ok - i've deleted and rerun the docker run cmd, and started a bash shell in the container, what would i check ?

[real timber]

this failed for me after wiping the container just now

[unkempt prism]

example doesn't work for me either. It was 127.0.0.1 that worked.

[real timber]

os:

No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 22.04.1 LTS
Release:        22.04
Codename:       jammy

[real timber]

yea that's what i tried, didn't work

[unkempt prism]

Comparing my docker ps output above I'll be interested what you have under PORTS

[real timber]

should have posted sorry - this is what i have:

$ docker ps 

CONTAINER ID   IMAGE                    COMMAND                  CREATED         STATUS         PORTS                                       NAMES
1494d69fa711   postgres:14.6-bullseye   "docker-entrypoint.s…"   2 minutes ago   Up 2 minutes   0.0.0.0:5432->5432/tcp, :::5432->5432/tcp   example

[pure cypress]

Try to connect to the db with psql from within the container

[real timber]

don't mean to be thick but what would the syntax be ? I'm not sure about ports / host if i'm in the container

[unkempt prism]

It will just be

    docker exec example psql -d db -U user

[pure cypress]

Port would be the default 5432 and host localhost

[real timber]

that didn't do anything - do i need to launch bash interactively then run psql ?

[pure cypress]

Yeah I believe so

[unkempt prism]

Yeah I forgot -it

docker exec -it example psql -d db -U user

[real timber]

OK that has done something:

$ docker exec -it example bash
root@1494d69fa711:/# psql -d db -U user
psql (14.6 (Debian 14.6-1.pgdg110+1))
Type "help" for help.

db=# 

[pure cypress]

Okay so it can connect, we know the server is running fine

That kind of confirms the issue is networking between container and host

[real timber]

yea, anything i should try ? Could this be some install related thing or something... i've no idea what to try really 🤔

[unkempt prism]

how have you installed psql

[real timber]

i think that was installed with postgres

sudo apt install postgresql postgresql-contrib

[pure cypress]

It should just work out of the box, but I know there are some networking options for docker. This comes to mind, but not sure what the equivalent is for.docker run https://github.com/compose-spec/compose-spec/blob/master/spec.md#network_mode

Does the container show any logs when you try to connect from the host by the way?

[real timber]

idk i'll try and find some

[pure cypress]

It may also be worth trying to connect via some other tool e.g. A simple python script with psycopg

[real timber]

i can't connect with dbeaver either

[pure cypress]

Not sure what's wrong, sorry

[real timber]

😩 yea it seems screwed and i'm clueless.

PostgreSQL init process complete; ready for start up.

2023-01-01 00:38:21.666 UTC [1] LOG:  starting PostgreSQL 14.6 (Debian 14.6-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2023-01-01 00:38:21.666 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-01-01 00:38:21.666 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2023-01-01 00:38:21.668 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-01-01 00:38:21.672 UTC [62] LOG:  database system was shut down at 2023-01-01 00:38:21 UTC
2023-01-01 00:38:21.678 UTC [1] LOG:  database system is ready to accept connections

is in the log but idk what that says

[calm obsidian]

oh no i know, it was more of a i messed up and now i gotta fix it

its a i goofed statement

[little smelt]

how do i use cassandra with django ?

[pastel wren]

so a problem i've run across recently is that i'm trying to secure users' emails in my db by hashing their email when they sign up and then when they try to login in using that email i'd hash what the email they enter and make a select into my db with that hash as the WHERE. This doesn't work though since when i hash it when the user logs in the hash is different. Is there some way to secure users emails that avoids this issue or is my implementation of securing emails slightly off?

[lament parcel]

Hey, as far as i know, a hash function should always give the same output. What hash algorithm are you using ? Do you use any salt ? And could you send the piece of code that you use to store and the one to compare

[grim vault]

!e If you use the same hash algorithm it should work:

import hashlib

def hash_email_sha256(email: str) -> str:
    return hashlib.sha256(email.encode("UTF-8")).hexdigest()

email_register = "[e-mail]"
email_login_test = [
    "[e-mail]",
    "[e-mail]",
    "[e-mail]",
]

saved_hash = hash_email_sha256(email_register)
for login_entry in email_login_test:
    check_hash = hash_email_sha256(login_entry)
    print(f"{login_entry} -> {saved_hash == check_hash}")

[delicate field]

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

001 | [e-mail] -> False
002 | [e-mail] -> False
003 | [e-mail] -> True

[grim vault]

I would only hash the password (with some salt). If you hash the email/username you won't know who the send the forgot password link.

[torn sphinx]

anyone knows a free mysql hosting?

[vocal parrot]

railway.app has mysql

you get 21 days free
and if you verify with a cc you get over a month. still free

[delicate field]

@cursive delta Per Rule 6, your invite link has been removed. If you believe this was a mistake, please let staff know!

Our server rules can be found here: https://pythondiscord.com/pages/rules

[pastel wren]

how do you secure the emails without hashing them then? I'd rather not get potentially breached and have all the emails in plain text

[obsidian barn]

hashing an email?

you could encrypt an email, or hash it separately, but I don't think hashing does what you think

https://www.thesslstore.com/blog/difference-encryption-hashing-salting/

[grim vault]

reads like a simple group sum with a limit

select name, sum(points) as "total" from tablename group by name order by total desc limit 10

[serene sonnet]

how much time does pymysql need to execute a select * from table?

[paper flower]

Depends on the table

[pastel wren]

what would you recommend for encrypting an email?

[serene sonnet]

would it be better to get data from a dict or execute a select code every time?

[vocal parrot]

Rather than dict, you should rather cache on redis

It will be faster than SQL select queries

Remember to update redis when updating SQL server

[ivory lava]

is it faster to query my sqlite db every iteration of a loop or do it first and search through it with python?

[fading patrol]

It probably depends on exactly what you are doing so find out with timeit

[grim vault]

I would use julianday() or unixepoch() to get a number. Also week is not supported, you'll need -7 days.

[obsidian barn]

thanks, I ended up using days instead of weeks. might consider the other methods you've mentioned as well.

[wraith carbon]

squil

[torn sphinx]

CREATE TABLE IF NOT EXISTS greetings (
    guild_id BIGINT NOT NULL,
    accent_color INT,
    ignore_bots BOOLEAN,

    welcome_channel_id BIGINT,
    goodbye_channel_id BIGINT,

    welcome_message VARCHAR(2000) DEFAULT "Hello {member}, welcome to {guild}",
    goodbye_message VARCHAR(2000) DEFAULT "{member} left." 
);
``` is there something wrong with this query? i get this error when i execute it with asyncpg ```py
  File "asyncpg/protocol/protocol.pyx", line 338, in query
asyncpg.exceptions.FeatureNotSupportedError: cannot use column reference in DEFAULT expression

[torn sphinx]

turns out sql supports single quotes only

[delicate field]

Hey @ashen mountain!

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

[ashen mountain]

Im trying to setup mongodb for the first time but I’m doing something wrong

Code:
https://paste.pythondiscord.com/kiwapitohe

I keep getting this error

Traceback (most recent call last):
  File "C:\Users\Lucaq\AppData\Roaming\Python\Python310\site-packages\lightbulb\app.py", line 1163, in invoke_application_command
    await context.invoke()
  File "C:\Users\Lucaq\AppData\Roaming\Python\Python310\site-packages\lightbulb\context\base.py", line 326, in invoke
    await self.command.invoke(self)
  File "C:\Users\Lucaq\AppData\Roaming\Python\Python310\site-packages\lightbulb\commands\base.py", line 690, in invoke
    await self(context, **kwargs)
  File "C:\Users\Lucaq\AppData\Roaming\Python\Python310\site-packages\lightbulb\commands\base.py", line 605, in __call__
    return await self.callback(context, **kwargs)
  File "C:\Users\Lucaq\Novemeber Project\Bologne\bologne\extensions\ship.py", line 93, in echo
    insert_ship_doc()
  File "C:\Users\Lucaq\Novemeber Project\Bologne\bologne\extensions\ship.py", line 73, in insert_ship_doc
    collection.insert_one(ship_document)
  File "C:\Users\Lucaq\AppData\Roaming\Python\Python310\site-packages\pymongo\collection.py", line 3207, in __call__
    raise TypeError(
TypeError: 'Collection' object is not callable. If you meant to call the 'insert_one' method on a 'Database' object it is failing because no such method exists.

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

Traceback (most recent call last):
  File "C:\Users\Lucaq\Novemeber Project\Bologne\bologne\__main__.py", line 43, in errorHandler
    raise event.exception
lightbulb.errors.CommandInvocationError: An error occurred during command 'ship' invocation

i dont know what im doing wrong

cause i think i did everything correct

[torn sphinx]

rip ken block

[wintry sparrow]

can someone tell me why you'd use a SQL (postgres specifically) over something like mongodb?

i'm learning postgreSQL for school (with java) after working with monogodb (with python) and so far apparently you'd go for SQL when you have closely related data?

like apparently you can't query and stuff with a nonsql database but i had no issues querying with mongodb even when it came to nested objects and stuff like that it was pretty simple with pymongo

sql on the other hand looks like a living hell i'm confused what benefits you could get over something like mongodb unless it was performance related i suppose

i'm knew to databases so please excuse my ignorance 😅 sql makes my brain fog up just looking at it

[forest raptor]

Hello!

I'm using MongoDB to store my items. When I try to use the find_one() method, it returns None even though there is data there.

def get_balance(user_id):
        return collection.find_one({'user_id': user_id})['currency']```

[wintry sparrow]

is the user id supposed to be your primary key?

if so i think you have to access it with "_id" and converting the user_id into an ObjectId

[forest raptor]

Nope

I have a field called user_id

here's a datamodel

[wintry sparrow]

what's the type of the the user_id variable?

[forest raptor]

I'm not too sure

[wintry sparrow]

if it's a string that could be why it's not finding it

[forest raptor]

but Ive tried int and str

oh it works

wth

[wintry sparrow]

you've tried converting the user_id to an integer?

[forest raptor]

i changed nothing

[wintry sparrow]

oh

[forest raptor]

welp thats a good thing ig

ty for your help and time

lol

[wintry sparrow]

ofc even though i didn't do anything 😅

[forest raptor]

You still tried!

[fading patrol]

Basically the advantage of SQL is to ensure data is structured and consistent.

[mint mauve]

how can I make it that if user_id1 or user_id2 will be user.id?

    friends = db.relationship("Friend", backref="user", passive_deletes=True, primaryjoin="and_(Friend.user_id1==User.id, " "Friend.status=='friends')") 

[smoky latch]

Eventually you'll realise that like 90% of the data you're working with is actually relational. That means you need data integrity. Your queries will generally be simpler too (I don't know how SQL could be more complex than the MongoDB queries but eh, besides you have numerous ORMs today to make your life easier). Also, as the SQL databases avoid data duplication, that means that they'll generally be smaller.

Nowadays you can pretty much do things both ways, and notice that the technical differences aren't that relevant if you don't plan on making relatively small databases without much workload anyways.

[high agate]

How do i select all the suggestions_id rows? if you get what i mean

and then put all the returned values in the dictionary fruits

[waxen finch]

use cursor.fetchall() instead, you'll get a list of rows that way - or alternatively .fetchone() repeatedly until you get None

[high agate]

with the fetchone() repeatedly could i append the fetched value to the dict each time