databases

[proven arrow]

i think its the same as this, #databases message

[ripe matrix]

Yeah, I added a pk already, not sure what it does but so long as it helps me edit my tables faster haha

[lofty radish]

next question:
i have 3 tables:
-posting(nickname|postingid)
-retweets(nickname|postingid|retweetnickname)
-likes(nickname|postingis|likeenickname)

i must get the 10 posts with the max ammount of retweets+likes

atm i managed to get the count of likes and retweet in 2 different querys, but i need to put it all together with posting table, some hints/solutions?

FROM retweet
group by nickname,postingid

SELECT nickname,postingid, COUNT(*)
FROM likes
GROUP BY nickname, postingid```

i must do something like

   FROM person join retweets join likes

[stone cairn]

    @commands.Cog.listener()
    async def on_message_delete(self, message):
        if message.embeds or message.activity or message.application or message.stickers:
            return
        conn = await asqlite.connect(f"./databases/{message.guild.id}.db")
        c = await conn.cursor()
        await c.execute("""CREATE TABLE IF NOT EXISTS snipe(
            channel TEXT,
            author TEXT,
            content TEXT,
            attachment TEXT)""")
        await conn.commit()
        if message.attachments:
            await c.execute("REPLACE INTO snipe(channel, author, content, attachment) VALUES(?, ?, ?, ?)", (message.channel.id, message.author.id, message.clean_content, message.attachments[0].url))
            await conn.commit()
        else:
            print(message.clean_content)
            await c.execute("REPLACE INTO snipe(channel, author, content, attachment) VALUES(?, ?, ?, ?)", (message.channel.id, message.author.id, message.clean_content, ""))
            await conn.commit()
        await c.close()
        await conn.close()```
asqlite doesnt seem to be modifying the entry

it updates if i delete the .db and the other 2 temp files but not normally

this is a cog listener with discord.py btw but that all works properly

asqlite is an async sqlite3

[dense barn]

How would I insert the current time in postgresql?

[stone cairn]

ping on reply pls

[proven arrow]

there is no conflict in your table for it to replace the row

https://www.sqlitetutorial.net/sqlite-replace-statement/

[stone cairn]

ty

[south arch]

hi

[lofty radish]

FROM posting,likes,retweet
where posting.nickname = likes.nickname AND posting.postingid = likes.postingid and posting.nickname = retweet.nickname and posting.postingid = retweet.postingid
group by posting.postingid,posting.nickname```

count gives me number of likes * number of retweets and i know why

can someone tell me how to write the query to habe number of likes + number of retweets pls ?

[proven arrow]

Allora, please provide some minimal example of dataset, table structure. It’s not so clear to understand what you mean with just column names.

[chilly nimbus]

I downloaded this from heroku, it is a manual postgres database backup, i can download it but idk how to open or use it, can somebody guide me pls

[proven arrow]

You would import it into your local database system.

[lofty radish]

Sorry PosteID it above

#databases message

[dense barn]

How would I insert the current time in postgresql? I tried using datetime.now but didn't work.

[soft rose]

what happens if i forget to conn.commit() or my program breaks before it commits? (in sqlite3)

[proven arrow]

Transaction rolls back

[soft rose]

what about if i forget to conn.close()

[proven arrow]

It doesn’t really matter

[soft rose]

ok

thanks

[dense barn]

im trying to insert datetime.now() in postgresql but im getting an error which im not familiar with.
error:

discord.ext.commands.errors.CommandInvokeError: Command raised an exception: DataError: invalid input for query argument $7: datetime.datetime(2021, 7, 3, 12, 9, 50,... (expected str, got datetime)
```im new to postgresql so i have no idea what this means. I think its something related to the data type  my table has?

[burnt turret]

You're right, it looks like you defined the column to have some kind of TEXT type, and now you're trying to insert a datetime object

[dense barn]

ah

what should the data type be? i have no idea.

[burnt turret]

https://www.postgresql.org/docs/13/datatype-datetime.html refer here

Pick one that meets your needs

[dense barn]

ok thanks!

[crisp spade]

# Import module
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pprint import pprint
  
scope = ["https://spreadsheets.google.com/feeds", 'https://www.googleapis.com/auth/spreadsheets',
         "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive"]
  
  

creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
sheet = client.open("Python DATABASE").sheet1
  
  
  
# Display data
data = sheet.get_all_records()


# Making Account
username = input("What username would you like your account to be?:")
password = input("What would you like your password to be?:")
  
# Inserting data
insertRow = [username, password, [], 0]
sheet.insert_row(insertRow, 2)
print("\nAll Records:")
pprint(data)

Doesnt work

Traceback (most recent call last):
  File "/Users/myname/Desktop/RPG Bot Scripts/DATABASE TESTING/basic_def_db.py", line 27, in <module>
    sheet.insert_row(insertRow, 2)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/gspread/models.py", line 1523, in insert_row
    return self.insert_rows([values], index, value_input_option='RAW')
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/gspread/models.py", line 1560, in insert_rows
    return self.spreadsheet.values_append(range_label, params, body)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/gspread/models.py", line 165, in values_append
    r = self.client.request('post', url, params=params, json=body)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/gspread/client.py", line 76, in request
    raise APIError(response)
gspread.exceptions.APIError: {'code': 400, 'message': 'Invalid values[1][2]: list_value {\n}\n', 'status': 'INVALID_ARGUMENT'}```

says that

[proven arrow]

Any reason why you are using google sheets for a database?

[crisp spade]

online

it's for a python game

[proven arrow]

So its your idea to use google sheets or someone elses?

[crisp spade]

mine

[burnt turret]

some popular youtube channel had recently uploaded a video suggesting something like this lol

fireship i think

https://www.youtube.com/watch?v=K6Vcfm7TA5U got it

knew this would lead to bad ideas

[proven arrow]

Yeah

[crisp spade]

i didn't watch that tho

[proven arrow]

It doesnt matter, the reasoning is the same

[crisp spade]

should I watch it or...

[proven arrow]

You should try to use the proper tools for the job. As it will make your life easier.

[remote plinth]

can we use a connection string in aiomysql to connect?

[proven arrow]

First you should start by asking some questions, like what is it your working on? and what do you want to store, and why? Then you can pick the right tool for the job.

[proven arrow]

https://aiomysql.readthedocs.io/en/latest/connection.html#connection

[remote plinth]

is that a 'no'?

cuz i dont find it there

[proven arrow]

Then it wont implement it. More info why here https://www.python.org/dev/peps/pep-0249/#id40

under the first footnote

@remote plinth actually it might implement it, you should just try it

if it works it works, if not it doesnt

[brave bridge]

@proven arrow async libraries can't implement DBAPI

well, they try to be sort of similar

[burnt turret]

checked the source, aiomysql doesn't take a connection string by the looks of it

[proven arrow]

Well not implement directly but aiomysql is based of pymysql

[brave bridge]

ah

[burnt turret]

probably because pymysql is dbapi compliant

[remote plinth]

ok

also, sometimes its not working. i made a variable pool as attribute to commands.Bot, i use this in all commands: ```py
async with bot.pool.acquire() as conn:
async with conn.cursor() as cur:
.....
await conn.commit()

i mean it does work but when using same command 2 times it doesn't work second time

[proven arrow]

How do you know if it doesnt work?

[remote plinth]

the command should work

it doesn't respond (like the whole bot is not working)

[proven arrow]

Yes but this could be so many things, theres no way to know without seeing surrounding code/db structure. Showing simplified code like that will only get you simplified answers.

What makes you say its a db issue but not issue with your bot or some other part of the code?

[remote plinth]

umm

only commands that includes a database connection have that problem

[torn sphinx]

What are some good database software to manage database on my pc?

[proven arrow]

Well i dont see anything wrong with what you sent. So you will have to debug or share more code.

[dense barn]

how would i format a datetime.datetime result from a postgresql query

[harsh pulsar]

!d g datetime.datetime.strftime

[delicate field]

datetime.datetime.strftime

datetime.strftime(format)```
Return a string representing the date and time, controlled by an explicit format string. For a complete list of formatting directives, see [strftime() and strptime() Behavior](https://docs.python.org/3/library/datetime.html#strftime-strptime-behavior).

[dense barn]

ty

[remote plinth]

can i use ```mysql
CREATE TABLE IF NOT EXISTS

the IF NOT EXISTS

nvm it works

another question

how do i commit db in aiomysql without closing connection

!d

[delicate field]

All inventories (`28` total)

• python
• requests
• rich
• scikit_learn
• scipy
• urllib3

Page 4/4

[remote plinth]

how do i not raise Warning when database already exists

tried try/except

[proven arrow]

Use If not exists

[remote plinth]

i already did

but i get a Warning that table already exists

[proven arrow]

Yes because it will raise a warning

You should not ignore the warning

[remote plinth]

i don't want that

[proven arrow]

Well why not

[remote plinth]

i mean

[proven arrow]

Its warning you that the database is already there

Acknowledge it and take the appropriate action

[remote plinth]

k

[zealous parcel]

Hi how to select by name instead of using an ID? Line 14

http://prntscr.com/18c16g2

[proven arrow]

Use a dictcursor https://aiomysql.readthedocs.io/en/latest/cursors.html?highlight=dict#DictCursor

[zealous parcel]

ok thanks

@proven arrow Does aiomysql work well with large databases?

[proven arrow]

What makes you doubt?

[zealous parcel]

I've been using mysql.connector and it's getting lag

[zealous parcel]

I want to know if it is like this late

[proven arrow]

Well aiomysql has different use case mysql.connector

aiomysql is async so if you were using connector with asynchronous code then yes it would seem to be slower

[zealous parcel]

Is this async? @proven arrow
http://prntscr.com/18c30c3

[remote plinth]

yes

aiomysql = asyncio mysql

[zealous parcel]

oky

how to fix?
http://prntscr.com/18c4gfv

DeprecationWarning: "@coroutine" decorator is deprecated since Python 3.8, use "async def" instead
  def test_example():

@remote plinth

[brazen charm]

my advise is dont follow that outdated code

[zealous parcel]

Is there anything new?

@brazen charm

[proven arrow]

You seemed to use the new code in your inital screenshot? #databases message

And the error clearly explains it, so are you coding by brute force or what?

[zealous parcel]

I don't know how to choose a table by name @proven arrow

[torn sphinx]

Hi idk if any1 can help but I made a tkinter gui that connects to a database I made but I’m getting a error when trying to run the adddata button (il link the error, front end and back end of the script)

https://pastebin.com/egJxU2is is the front end

back end

https://pastebin.com/6cnhEDLf

and the error:

Exception in Tkinter callback Traceback (most recent call last): File "C:\Program Files\WindowsApps\PythonSoftwareFoundation.Python.3.9_3.9.1520.0_x64__qbz5n2kfra8p0\lib\tkinter\__init__.py", line 1892, in __call__ return self.func(*args) File "C:\Users\ethan\Desktop\db\projects_db.py", line 75, in addData backend.addData(ID_lol.get(), Name.get(), Start_date.get(), End_date.get(), Made_by.get()) TypeError: addData() missing 1 required positional argument: 'made_by'

[proven arrow]

@torn sphinx although you have ? for each query, your not passing any parameters to the query

[torn sphinx]

oh

[proven arrow]

I gave you a link that shows how to do it, just add aiomysql.DictCursor to the cursor object

[zealous parcel]

oh oky

[proven arrow]

As for this error your not passing all the data through

[torn sphinx]

do u know how to fix it?

[proven arrow]

It tells you, addData() missing 1 required positional argument: 'made_by'

[torn sphinx]

what would i have to replace

ye

[proven arrow]

🤷‍♂️ pass in whatever you want

[torn sphinx]

wdym

[upper juniper]

hola friends, i have two tables, items and entries, and an association table linking them together
is this the correct way to get all the items linked to a particular entry?
http://sqlfiddle.com/#!17/37837/7

[proven arrow]

Yeah

[late hinge]

does saving to a txt file count here?

[dense barn]

would this be a data type of text or something else? : 1st Edition Base Set Machamp Holo

nvm got it

[chilly nimbus]

how do i do that

[proven arrow]

Thanks for taking 13 hours to reply and pinging me at 6 in the morning.

[chilly nimbus]

sorry, but i didnt know my question got a response :c because i wasnt pinged, I searched if someone answered or not

[torn sphinx]

anyone can help to make docker file from django app?

I have tried but I got error

in docker-compose up

django.db.utils.OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")

please help me

[torn sphinx]

u need to install / restart mysql

[austere portal]

So if I give current_timestamp as the default value of a PostgreSQL database column, what time zone will the default datetime value be?

[unkempt prism]

I assume you have a seperate docker container for mysql and django? If so you need to set the database connection string to use TCP and not a unix socket.

[lone island]

hi, anyone knows how to restore data using heroku backup files? since i don't know what kind of file format this is

[wispy loom]

are there any pythonic databases where i can use things like "data.push("this")" for example

[errant arch]

Hey! So my question: How do I log into mysql on an ubuntu system? MySQL Version: 8.0.25
After I installed Ubuntu I ran: sudo mysql_secure_installation utility
There I configured my root password (I also enabled remote access to root)
Then I got the ERROR 1045 (28000) when I wanted to log into mysql with: sudo mysql -u root -p And with sudo mysql -u root -h localhost -p I reset my password successfully and FLUSHED PRIVILEGES; I also COMMITED; the thing. (Password was 1. set with this: update mysql.user set authentication_string=MD5(‘root’) where user=’root’; 2. update mysql.user set authentication_string=’root’ where user=’root’; When I tried to login with password root. It says Sorry, try again. Did I do anything wrong? Does the password need some specific encryption? Encryption-list: https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html

[proven arrow]

@errant arch You can follow the instructions here how to reset the password if you forgot https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

[chilly nimbus]

@proven arrow can you tell me

[tepid cradle]

You should use now() as the default value.
It will be the timezone of the machine running the postgres instance. But do note that if the column type is timestamp without timezone, it will take UTC time.

[errant arch]

idk why but it threw 10 error messages in my face

[austere portal]

Thanks

[remote plinth]

is there a way i can store images in aiomysql?

[brazen charm]

store them as blobs

but you do have a size limit with mysql

[remote plinth]

is LONGBLOB enough good for videos?

[brazen charm]

that would depend on the size of videos

but if they're long videos I wouldnt advise mysql at all

Postgres is probably best suited for mega large blob storage because it has a set of special types that can stream the data in chunks rather than load it all in memory at once and over the network at once

so you save alot of time with latency waiting for the first chunk of data rather than the whole thing

[stable nymph]

Traceback (most recent call last):

pandas_datareader._utils.RemoteDataError: Unable to read URL: https://finance.yahoo.com/quote/TSLA/history?period1=1356993000&period2=1577917799&interval=1d&frequency=1d&filter=history
Response Text:
b'<!DOCTYPE html>\n <html lang="en-us"><head>\n <meta http-equiv="content-type" content="text/html; charset=UTF-8">\n <meta charset="utf-8">\n <title>Yahoo</title>\n <meta name="viewport" content="width=device-width,initial-scale=1,minimal-ui">\n <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">\n <style>\n html {\n height: 100%;\n }\n body {\n background: #fafafc url(https://s.yimg.com/nn/img/sad-panda-201402200631.png) 50% 50%;\n background-size: cover;\n height: 100%;\n text-align: center;\n font: 300 18px "helvetica neue", helvetica, verdana, tahoma, arial, sans-serif;\n }\n table {\n height: 100%;\n width: 100%;\n table-layout: fixed;\n border-collapse: collapse;\n border-spacing: 0;\n border: none;\n }\n h1 {\n font-size: 42px;\n font-weight: 400;\n color: #400090;\n }\n p {\n color: #1A1A1A;\n }\n #message-1 {\n font-weight: bold;\n margin: 0;\n }\n #message-2 {\n display: inline-block;\n *display: inline;\n zoom: 1;\n max-width: 17em;\n _width: 17em;\n }\n </style>\n <script>\n document.write('<img src="//geo.yahoo.com/b?s=1197757129&t='+new Date().getTime()+'&src=aws&err_url='+encodeURIComponent(document.URL)+'&err=%<pssc>&test='+encodeURIComponent('%<{Bucket}cqh[:200]>')+'" width="0px" height="0px"/>');var beacon = new Image();beacon.src="//bcn.fp.yahoo.com/p?s=1197757129&t="+new Date().getTime()+"&src=aws&err_url="+encodeURIComponent(document.URL)+"&err=%<pssc>&test="+encodeURIComponent('%<{Bucket}cqh[:200]>');\n
</script>\n </head>\n <body>\n <!-- status code : 404 -->\n <!-- Not Found on Server -->\n <table>\n <tbody><tr>\n <td>\n <img src="https://s.yimg.com/rz/p/yahoo_frontpage_en-US_s_f_p_205x58_frontpage.png" alt="Yahoo
Logo">\n <h1 style="margin-top:20px;">Will be right back...</h1>\n <p id="message-1">Thank you for your patience.</p>\n <p id="message-2">Our engineers are working quickly to resolve the issue.</p>\n </td>\n </tr>\n </tbody></table>\n </body></html>'

pls help

me getting this error

[oak furnace]

So what type of database is easy to get into, and able to handle more connections than sqlite3. I just learned how to make a table, refactored my code to work with it just realize it can't handle the ammount of connections I'm going to be getting to it. 😦

Dang database locked

[dense barn]

I've heard that postgresql is good

[lone island]

ye postgresql is pretty good in my opinion

guys i installed command line tools for postgresql but cmd doesn't recognized pg_restore or psql, what's the problem here ??

[solid pine]

i used terraform to stand up some aws resource, and i want to take a csv from s3 > transform some data > insert into redshift table

should i use boto3 or psycopg2? seeing both in many places but dont see the diff

[solid tundra]

I got a question, I have created a code which whenever someone joins a specific channel a new channel will get created with the Number 1, this will get saved in a SQLite database and if someone else joins that specific channel again, a new channel will get created with the Number 2 by doing += 1 for every join.

Now the problem I have is that I want whenever someone joins the Number 1 channel and then leaves and no one is inside that channel, to get deleted together with the data inside of the Database

for example

This is the specific channel

the moment I click on that channel I'll get moved to a new channel with the name 1

Then if I leave that channel and no one is inside I want it to get deleted with the Data saved in the Databse

The data that is saved is the guild_id, user_id, channel_number and channel_id

how will I use this Data to somehow find the created channel and check when it has 0 members?

so it get's deleted together with that row

[paper bluff]

i have a project where i read dbf files from a foxpro-based accounting system (still actively used and data is entered/deleted/updated daily), write them to csv files, read them in pandas and do various joins and filtering and create reports (pdf, email templates, etc. using jinja2 templates and weasyprint)

this has been treating me fine (a bit slow sometimes, but bearable) except for any scripts that require me to read a 12 million row dbf file of invoice line items.

so i've been told to start looking into database solutions. i'm not sure how to go about doing this because i will need to write the database fully with 'fresh' data, since the foxpro accounting system is still actively used everyday. i would have no way of knowing which records from which tables need to be updated, added, or removed in my project files

i found this tool https://pypi.org/project/dbf-to-sqlite/
would i just write a script to write/overwrite the same sqlite .db file on a nightly basis?

then once i do that, use python/sqlite to query the data i need, joining various tables and filtering as needed.... then convert them into pandas dataframes which i further process into dictionaries and lists that i then pass thru jinja templates 🤔

or should i be piping sql query results directly into jinja templates somehow, and bypass using pandas altogether?

[harsh pulsar]

You can probably push off a lot of the data manipulation logic to the database itself with SQL

If you find that you can push off all of it, then yeah you can bypass pandas entirely

But pandas isn't a bad tool if you find yourself having to do tabular data manipulation or processing that doesn't work well in the SQL model

Generally I wouldn't recommend using a specialty "x to y" library

[paper bluff]

i'm having more and more doubt about building a way to migrate dbf files to a sql database.. 1-2x a day.. just for the purpose of dealing with 1 aspect of my needs in reading from specifically just 1 very large table once in a while

mayhaps i instead look into something that lets me use pandas but on chunks of the file at a time or something

[harsh pulsar]

Whatever you do to generate the csv files, just save to sqlite instead

[paper bluff]

so i just discovered dask, which is really really fast... i don't know why, or how. but for now will keep using this. the syntax is all the same too https://docs.dask.org/en/latest/dataframe-joins.html

but yes i will further research your suggestion as well, since one day i will have to learn SQL

[reef monolith]

Wich database will be good to learn first

[dense barn]

Can postgresql handle about 10+ fetches and executes at once?

Database is basically the heart of my bot

[dense barn]

Sql and then postgresql imo

[wind smelt]

that's basically nothing

databases can handle a lot

they're built with that in mind

[jaunty galleon]

Well depends, if you are going to use postgresql as service from elephantsql for example, the free version only allows 5 connections at once

[dense barn]

Ohh

[unkempt prism]

Most libraries have some sort of connection pooling so it shouldn't be a big deal.

[median swift]

Postgres / Asyncpg question:

I got a table which will have a lot of records, I want to use a select statement to select around 20? From the selected 20, how do I get the nth row of the selected 20? Note that the nth row doesn't mean the nth row in the table if you get me.

[mossy socket]

I guess you'd add LIMIT 1 OFFSET 4 to get the fifth row from your query

although you should also use an ORDER BY clause to make the returned row predictable

[median swift]

Hi there, thanks for your reply. I have managed to do what I wanted now in another way. Basically I've made an empty list and use RANDOM() in the query. Then it is a loop until it picks one that's ID is not already in the list. Probably not the most efficient way, but it works for me at the moment

[proven arrow]

Because that’s what you asked for?

Remove the quotes around case2

[proven arrow]

Each row is returned as a tuple. So you just index it to get the column you want

res[0]

[burnt turret]

CASE is a keyword, you'd want to wrap that in quotes

[lone island]

i have postgresql command line tools installed in my windows 10 but cmd doesn't recognize pg_restore or psql commands, can anyone help ?

[burnt turret]

try it and see

if they don't work, use double quotes

or at least i think backticks ` work 🤔 unsure

[proven arrow]

Probably because it’s not in your path

You can find the tool in the Postgres bin folder

[burnt turret]

don't ping people randomly

but the error tells you exactly what's wrong? you're not providing any query to execute

[burnt turret]

they've since deleted the message

fetchone returned None, so when you try sending it that "cannot send empty messag" exception is raised

don't use f strings for your queries

#databases message explains why

[austere portal]

Yes

Maybe there isn't a warn with the given id

Fetchone only returns the first one

[burnt turret]

someone had already told this to you i feel 🤔

#databases message same thing is happening now

just use backticks instead of quotes there i guess

[wispy escarp]

Hi everyone

[frozen plover]

Gello

[dense barn]

im getting an error saying my column name is not defined. Im trying to connect to 2 databases with one loop. i dont think thats the problem.

HINT:  Perhaps you meant to reference the column "pokesetspc.pc ".

[tidal turtle]

CREATE TABLE IF NOT EXISTS guilds (
    `GuildID` bigint PRIMARY KEY,
    `Prefix` text DEFAULT '?',
    `Currency` text DEFAULT '€',
    `IsPremium` bool DEFAULT false
);

I have a problem, if a new row gets inserted the default value of Currency is not set to € but to ?. How do i fix that? I tried some things from stackoverflow but all of them didn't work.

[halcyon cobalt]

Hey can anyone help me with this. I have a Table with 10 records and from that there is a colum salary. I want to select 3 rd lowest salary.... can anyone help me with this please... I am baffled and tried with all limit combinations and nested queires but i am getting lost in all that and confused.. please Help

[lone island]

it says my password is wrong but the password is correct, it's the same password i use when i try to login with pgadmin and it works fine, any ideas why is this happening ?

[pearl sapphire]

Hello

trying to do some hw

CREATE TABLE LessonSchedule (
HorseID smallint(5) UNSIGNED NOT NULL,
StudentID smallint(5) UNSIGNED NOT NULL,
LessonDateTime DATETIME NOT NULL,

FOREIGN KEY (HorseID)
REFERENCES Horse (ID),

FOREIGN KEY(StudentID)
REFERENCES Student(ID),

);

this is giving me a syntax error on the first line

wow after posting it here i noticed i added an extra comma on my lest line

[proven arrow]

Then the column doesn’t exist. Look at the error, your column name has a space in it.

[proven arrow]

That wouldn’t be possible with the code you show. The default will only be ? If that’s what you entered. Please share more of the code.

[proven arrow]

Which database?

[dense barn]

ah thats a new thing to me, thanks!

[halcyon cobalt]

mysql

[tidal turtle]

i don't specify currency in my code so there isn't anything to show. But i found out what the error was, it had something to do with Sequel Pro. (A free MySQL database viewer for M1 macs). When i executed the query in PhpMyAdmin it worked correctly.

i just inserted and specified GuildID to a number, nothing more.

[dense barn]

also, is it fine if you connect to 2 databases with 1 loop?

[proven arrow]

Select * from table_name order by salary_column asc limit 2, 1

That should work

[halcyon cobalt]

limit 2 will take 2 records but 1 ?

[proven arrow]

Why would you want to do that?

[halcyon cobalt]

what does 1 do

[proven arrow]

2 here is n-1 where n is 3

Is for limit

[halcyon cobalt]

ok thanks man i will try andet you know 😁

[dense barn]

idk, im just asking if its better to do 2 things in 1 loop or in 2 seperate loop as im new to postgresql

[proven arrow]

Honestly I have no idea what you mean. Why is a database connection in a loop anyway?

[dense barn]

aren't you supposed to do this?

async def create_pokesets_pool():
    client.pg_db = await asyncpg.create_pool(database="pokesets",user="postgres",password="")
    client.pg_dbb = await asyncpg.create_pool(database="pokesetspc",user="postgres",password="")

and then at the end of the code

client.loop.run_until_complete(create_pokesets_pool())

[proven arrow]

Yeah that’s fine

I thought you were creating the connecting each time in a loop

[dense barn]

ohk

[proven arrow]

Actually not fine, I didn’t read the code.

Yeah why would you even do that?

[dense barn]

what would you do if you were me?

[proven arrow]

Just have one pool like every other sane person

[pearl sapphire]

hello

how can i alter a table in mysql

my attempt at the bottom

[remote plinth]

how can i execute a .sql file in aiomysql

[median wave]

what's the ideal way to get the lowest value from a row in SQLITE?

iirc there is a min function?

[unkempt prism]

To use the min, max, avg you may also need to use a group by

[median wave]

is that optional?

[unkempt prism]

Depends what’s your query?

[median wave]

basically for a tempmute command

get the lowest amount of seconds for a mute remaining

@unkempt prism so would i need that group by?
Or should i even use min for this

[proven arrow]

Just order by

[solid tundra]

How do I insert data to a row that already exists in SQLite?

[remote plinth]

^^^^

[faint blade]

Open the file, read the text, and execute the queries

[remote plinth]

i thought there's a specific way but ok

does conn.commit() really close the connection?

aiomysql

[unkempt prism]

I don't understand. Try to create a https://stackoverflow.com/help/minimal-reproducible-example

[wise goblet]

if I have only like 100 records of one table in docker composed postgresql

how much resources does postgresql consume

I can see that it is taking 200 mb of RAM

but how much of CPU load as well there?

hmm, anyway. I need some Django good library to make db locks hmm

https://lincolnloop.com/blog/distributed-locking-django/
oh, it looks like mentioning all ways to do that

looking at solutions which are kept up to date, I have literally one choice: django-redis

well, there is available mysql too, but I don't want to use mysql

postgres locks look quite outdated

memcache looks good too though, but nope. not quite developed yet.

mm nah. django-redis is the most developed solution out of them. The choice is made.

[median wave]

For SQLITE, if i wanted to get the singe least value in a row, i would do order by ascending and limit to one?

[unkempt prism]

Yes that should work nicely.

[median wave]

Thanks

[flat isle]

alright what's going on here cuz i def have a table named factions in here

Traceback (most recent call last):
  File "C:\Users\Bashir\AppData\Local\Programs\Python\Python39\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\Bashir\Desktop\Bashir\code\MiracleBoxCode\levels\FactionBattles.py", line 45, in register
    cursor.execute(f"SELECT role_id FROM Factions WHERE guild_id = '{ctx.guild.id}'")
sqlite3.OperationalError: no such table: Factions```

[pure cypress]

The error doesn't lie. Maybe you opened the wrong db file or the command to create the table hasn't been executed yet.

[flat isle]

i've checked through, both commands were executed and i have the right path and file so idk

[median wave]

How do I reduce all values in a column by a number at the same time? SQLITE

[proven arrow]

Use update without a where clause

[proven arrow]

Are you sure table name is spelt correctly?

[median wave]

like this?

@tasks.loop(seconds=5)
async def tempmute_loop():
    await bot.db.execute('UPDATE tempmute SET duration = duration - ?', (60))
    await bot.db.commit()
    tempmute_loop.start()

I tried this but it didn't seem tp update

[proven arrow]

Try pass value as list or tuple

[median wave]

By that you mean

[proven arrow]

Also that’s probably not the best way of doing unmute

[median wave]

Then?

[proven arrow]

You just need to check if the time has passed. Why deduct the time?

What if your bot stops? Then people will be muted for longer than they should

You just store the time their mute ends. Loop, and check if the time has passed the current time. If it has then unmute.

[median wave]

So datetime now plus mute duration

[proven arrow]

Yes stored as a timestamp

[median wave]

Yeah, i guess I'll try that

The datatype would be text?

[cyan yacht]

Correct me if I'm wrong

For less number of users, I need to use a single connection with multiple cursors
For more users, I use connection pool, and each connection works with only one cursor?

[proven arrow]

Which database?

[median wave]

database?

[proven arrow]

Although this theory might work depending on database it’s not correct.

Connection pooling is a way to keep database connections open, so you can reuse them without having to create a new connection each time. And since opening connections mean you have some extra work to do, you would use connection pooling to lower the cost of opening connections.

[full veldt]

if i use the json module to store data, then what's really the difference in saving that data as .txt or .json or any other extension

[median wave]

databases would be better for that

[full veldt]

yeah but what are the advantages of using it

@median wave

[median wave]

there are many reasons that its recomended above json

examples:

[cyan yacht]

I understand now, thank you

[median wave]

- It's a file-based data storage, which makes it vulnerable to race conditions
- You'll need to implement your own synchronization primitives to avoid corrupting data
- If you're not careful, you could accidentally wipe your entire JSON file.
``` @full veldt

[full veldt]

I see

[median wave]

either way can i ask why you are not using a db?

[full veldt]

Cuz it's for a personal project

I would use db if it was a big project, but it's not

[median wave]

understandable, then I guess a json is okay for that

[full veldt]

Ok, thanks

[lofty summit]

Howdy everyone, 'been a long time since I posted here. I've a small issue on how to sanitise input with sqlite3 when the table is a parameter.
How it's normally done:

        self.cur.execute('''INSERT INTO table
            (
            a,
            b
            ) VALUES (?, ?)
            ''', (foo, bar))

However the build-in sanitizer does not work with (or at least couldn't make it work) when tables are a parameter.
POC:

self.cur.execute(f'SELECT * from ?', table_name)
# sqlite3.OperationalError: near "?": syntax error

named placeholders do not work either. Any idea, beside avoiding using variable table names? Pings appreciated

[proven arrow]

Placeholders only work for values

[maiden light]

I don't use sqlite but about comparing datetime objects you just use >, <, ==

[median wave]

well yeah, i know what bit. But what about the checking a single value from more than one values of a column

[maiden light]

¯_(ツ)_/¯

[quiet birch]

So I'm using MongoDB

and I am getting data from a game, that data may vary depending on what the game/player provided

in example I have a key for stats in which all general stats will be placed and then a key for each character the player owns

Given this context

I was looking forward to getting all existing keys

I want a full list of existing keys in database both in each character and stats key...

[delicate field]

Hey @quiet birch!

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

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

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

https://paste.pythondiscord.com

[quiet birch]

https://paste.pythondiscord.com/ligeviyite.json

that would be the complete sample version

Now this may change overtime

and people may not have all stats

I could mention the purposed of getting all existing options is to make a leaderboard

[zealous parcel]

Hi how to fix this?
I use DB aiomysql

task: <ClientEventTask state=pending event=on_message coro=<bound method charts.on_message of <system.charts.charts object at 0x7fc5e9c19cd0>>>
Task was destroyed but it is pending!

[median wave]

I want to make a tempmute command, using SQLITE. I made it insert the time of unmute when the command is done, now how would i check if there is a value from the columm that is equal to or past compared to the utcnow
just basically out of the cells in a column, how would i check if one has passed

[jovial nymph]

when i try to get smth from database its a list idk why

[median wave]

If I have

async with bot.db.execute("SELECT * FROM tempmute WHERE unmute_time > ?", (now,)) as cursor:```
how would i get another columm from that row

[dense barn]

But aren't you getting all the things from that row?

[median wave]

nvm it was answered

[lone island]

it says my password is wrong but the password is correct, it's the same password i use when i try to login with pgadmin and it works fine, any ideas why is this happening ?

[jovial nymph]

how can i check if there is name = tom in table names?

[remote plinth]

do i need await conn.commit() if i have autocommit=True?

aiomysql

[pearl sapphire]

woooo guys, just passed my phone interview with HR for this programming gig. my next phone interview is with the guy ill be working under and need to convince him i know programming, which i know 1% 😩

[proven arrow]

Good luck

[pearl sapphire]

thanks ill neeeeeeed it

but they also know i know 0.1% so lets see if they dont go crazy

[proven arrow]

yeah should be fine then, just show that your eager to learn and can learn 🤷‍♂️

[pearl sapphire]

That’s what I’m hoping for

It’s pretty bad pay but I really want the real world experience so when I graduate I can apply to all those jobs needing some exp

[jovial nymph]

whats wrong?

mariadb.DataError: Data type 'tuple' in column 0 not supported in MariaDB Connector/Python

[burnt turret]

why are you doing that? just passing it as a list should be fine

(i'm talking about how you've made each element a set, and then put all those sets in a tuple)

[jovial nymph]

its my first time trying to work with database idk

[burnt turret]

i'd recommend you learn some more of the basic stuff like data types quickly then

alright

just doing [message.guild.id, message.author.id, level, msg] should work

[jovial nymph]

i tried

[burnt turret]

and?

[jovial nymph]

wait

isnt it in a list

nvm

[burnt turret]

it is in a list but you've also

[jovial nymph]

yea i saw

[burnt turret]

think you've got it

[jovial nymph]

ill try

why is it giving me an error with "set" now there is no set

[burnt turret]

because you've made each element a set

[burnt turret]

this is what you need

[jovial nymph]

ah

[burnt turret]

by doing {message.guild.id} it has now become a single element set, with only message guild id as it's element

[jovial nymph]

ig it worked

it worked thank you

someone knows how i can do this:

without any errors

[proven arrow]

whats the goal of this?

What are you trying to do?

[jovial nymph]

just check if the guildid is already in the table

[proven arrow]

you can do: exists = cursor.execute('select exists (select * from level_system where guildid = ?)', (the_id,))

[jovial nymph]

so exists is then either true or false?

[proven arrow]

you need to fetch, and yes it will be 1 or 0

[jovial nymph]

ok

thanks

[jovial nymph]

I do not know if I am stupid but when i try if exists ==1: when its 1 it is executing the else and if im trying if exists is True: its executing the else

[proven arrow]

You should inspect the value of exists, it wont be a straight up 1 or 0, because thats not how the db returns it

Depending how you fetched, the result should be a tuple, or a list of tuples. You would need to extract the value and then make the check

[jovial nymph]

when i have a number in the database how can i get the number as an intenger

[tame ravine]

Hi, I'm trying to figure out a proper database design for my project idea. I haven't had any formal database training so I'm not to sure what the best way to do this is.

So far, I have a table of Players, a table of Servers, and a table of Interactions; but, I'm not sure how I should be connecting them. I have this so far:

[torn sphinx]

  File "/home/turtle/dusk-transfer/bank.py", line 8, in <module>
    cluster = MongoClient(f"mongodb+srv://Turtle:{PWD}@cluster0.kmnpl.mongodb.net/myFirstDatabase?retryWrites=true&w=majority")
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/mongo_client.py", line 639, in __init__
    res = uri_parser.parse_uri(
  File "/home/turtle/.local/lib/python3.8/site-packages/pymongo/uri_parser.py", line 461, in parse_uri
    raise InvalidURI('Bad database name "%s"' % dbase)
pymongo.errors.InvalidURI: Bad database name "home/turtle/dusk-transfer@cluster0"```
how is this a bad database name?

[harsh pulsar]

@torn sphinx didn't we go through this already?

clearly PWD isn't what you think it is

and you would need to url encode the password anyway

[torn sphinx]

I'm using the password that worked perfectly last time

[harsh pulsar]

somewhere in your code you set PWD to be a directory name, not your password

[harsh pulsar]

this seems reasonable

[bright hound]

Hey guys can anyone tell me the proper way to pull data from a postgres database into a pandas dataframe? I've seen 3 types of solutions.

1. Sqlalchemy engine, and I don't really want to use this one but I will if it's better for some reason.
2. Using a psycopg2 cursor
   3 Using the psycopg2 connection string directly.

I think the last two are basically the same but using the cursor handles the connection state so that it doesn't have to be manually closed, correct?
So is it better to use SQLAlchemy for any reason?

Here's an example of the sqlAlchemy version. I've seen it also where people are passing the connection string as an argument instead of engine, but I've never seen it where someone passes a cursor. Would that work to pass a cursor or would that not make sense?

Like this, but I guess I don't understand why you don't need to use a cursor

[flat isle]

if result is None:
            await ctx.send(f'This role is already registered in this battle.')
        else:
            sql = ("INSERT INTO Factions(guild_id, role_id, points, members) VALUES(?, ?, ?, ?)")
            val = (ctx.guild.id, role_id, 0, 0)
            cursor.execute(sql, val)
            connection.commit()```
how can i change this to if result already exists

[bright hound]

if result:

@flat isle

[bright dune]

if result can be a value like [], you need to be explicit: if result is not None:

[flat isle]

i should've been more specific, that's on me
if that specific role id already exists in the database, then it needs to say This role is already registered in this battle.

[bright hound]

You could make a list of roles like roles = ['warrior', 'tank', 'archer'] and say if result in roles: @flat isle

[peak stone]

im using mysql,

can i put database into pythons

[halcyon cobalt]

SELECT Salary
FROM Worker W1
WHERE 5 = (
SELECT COUNT( DISTINCT ( W2.Salary ) )
FROM Worker W2
WHERE W2.Salary >= W1.Salary
);

can someone explain me this ? pls.... whats the value of w1.salary and w2.salary and 5 = ??
the rest i got that inner query gets count of all uniques and outer is getting salary column where 5=(this i didnt understand)

[halcyon cobalt]

yes connect using pymysql,mysql.connector

[rose tide]

I'm using Postgres with CUBE_MAX_DIM set to 128, but it turns out my array is a 2622 dimensional vector. Is this safe? Will I see performance issues? In terms of comparison distances between arrays(using Euclidean distance)?

[eternal vessel]

I wanted to create a table with user inputs

[calm prawn]

can someone tell me how can I add more field in badges field using pymongo?

[lone island]

@bot.listen()
async def on_guild_join(guild):
    guild_id = str(guild.id)
    guild_name = str(guild.name)
    guild_prefix = '-'
    await bot.db.execute(f'INSERT INTO g_info (g_id, g_prefix, g_name) VALUES ({guild_id}, {guild_prefix}, {guild_name})')

syntax error using asyncpg

asyncpg.exceptions.PostgresSyntaxError: syntax error at or near ","

what am i writing wrong ?

[wild harbor]

You are trying to do and invite tracker?

[lone island]

i'm just trying to insert the data from the variables

[wild harbor]

try this

@bot.listen()
async def on_guild_join(guild):
    guild_id = str(guild.id)
    guild_name = str(guild.name)
    guild_prefix = '-'
    await bot.db.execute(f"INSERT INTO g_info (g_id, g_prefix, g_name) VALUES (?,?,?)", (guild_id, guild_prefix, guild_name))
    await bot.db.commit()

Why when i try to display SQL data only display mine?

[lone island]

ur still using F string, right ?

[wild harbor]

yes

[lone island]

i got the error again

[wild harbor]

try now

[lone island]

it's still happening lol

[wild harbor]

send print of the error please

[proven arrow]

Asyncpg uses $n for variables no?

[lone island]

idk havnt worked with asyncpg, not sure how should i write them that fits asyncpg, whats ur idea ?

[proven arrow]

Well first you would start by looking at how to use the library. Which means going to their docs.

[lone island]

what is $n ?

yes i see their docs, not much information about this

[proven arrow]

https://magicstack.github.io/asyncpg/current/usage.html

[lone island]

i still don't understand what do they mean by $

[proven arrow]

Are you just smashing code together or what?

😂

[lone island]

SQL is not my area, But i still need to write some amount of codes for my project

[proven arrow]

Then I would suggest trying to read up some stuff, rather than coding by brute force and wishing things work. This article should explain why we use placeholders like ? and $n https://realpython.com/prevent-python-sql-injection/

[wild harbor]

Any1 know how can i change it to display 10 different users?

[proven arrow]

You need to get data for 10 different users

Currently you just reference the same value each time

[wild harbor]

can you tell me how?

[proven arrow]

It’s not clear what you are trying to do

[wild harbor]

I have some results on SQL

Im trying to display 10 different

But on the embed only display the data from who did the command

And i want to display from top 10 invites

[proven arrow]

Then break it down and do it step by step. First get the data you need and make sure it’s correct. Then use that data to make your leaderboard.

[wild harbor]

That's what im trying to do but i dont know what i did wrong

[wild harbor]

@proven arrow

[proven arrow]

Your question is simplified so you will only get simplified answer. Please share more details, table structure, sample data, expected output. It’s very hard to understand what things like “normal”, “left”, “fake” mean.

[wild harbor]

I want to display on embed top 10 invites. But when i do the command only display the data who did the command

My SQL structure:

Normal = invited players who stand on the server.

[proven arrow]

What is top 10? How do you know if invite is in top 10?

[wild harbor]

The invites is ordered Descending. Organized by Normal Invites

The objective is display the 10 users with higher normal invites

[proven arrow]

Select * from invites where guild_id = ? order by normal desc limit 10

[wild harbor]

Like this?

[proven arrow]

Value needs to be a list/tuple. And you have syntax errors. Also you should fetchall and then loop over results

[torn sphinx]

can someone help me to use the replit database

[astral yew]

anyone familiar with Hive/HDFS? I am trying to load data into a table with hive load data inpath '<file-path>' into table <table-name>. But it gives me an error:
SemanticException: Invalid path: No files matching path.
When I manually check through hadoop fs -ls <file-path> I do see the directory, but not the files.

[harsh pulsar]

I do see the directory, but not the files.
maybe the error means it can't find any files in the directory, if the directory is empty

[flat isle]

can anyone give me a hand creating an exp leaderboard from sqlite entries

[dense barn]

So im getting a list of things from asyncpg query. The list has 12 things in it, how would i display only 10 things on the first page of the embed and then 2 other things on the next page?

[proven arrow]

How you display it to the user is not really a database question.

Better to ask on #discord-bots for ember d

[dense barn]

alr ty

[harsh pulsar]

you can fetch 10 records at a time from a query with asyncpg

[dense barn]

And by at a time you mean use LIMIT 10?

[harsh pulsar]

i'm suggesting that you fetch batches of 10 records from it

[dense barn]

Ah

[harsh pulsar]

rather than limiting the query itself

[dense barn]

Ok

[harsh pulsar]

cursor.execute('...')
while True:
    page = cursor.fetch(10)
    if page:
        break
    do_something(page)

or in python 3.8 and above

cursor.execute('...')
while page := cursor.fetch(10):
    do_something(page)

[dense barn]

Yep ty

[dense barn]

ok so instead of fetching multiple times i just did this so it would automatically format it as 10 per page.
results is fetchall

[results[i:i + 10] for i in range(0, len(results), 10)]

[orchid pebble]

I am having a hard time connecting to my database from a Blueprint using flask_mysqldb in a flask project

I have a flask application I am working on and I am getting an error while using flask_mysqldb

Here is the error I am receiving

KeyError: 'MYSQL_UNIX_SOCKET'
My directory structure is

main.py
auth.py
website
templates
static
init.py
In my main.py I have the following

from flask import Flask
from flask_mysqldb import MySQL

def create_app():
app = Flask(name)
app.config['MYSQL_USER'] = 'flask'
app.config['MYSQL_PASSWORD'] = 'mypw'
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_DB'] = 'flask_test'
app.config['MYSQL_CURSORCLASS'] = 'DictCursor'
app.config['MYSQL_PORT'] = '3306'

from views import views
from auth import auth

app.register_blueprint(views, url_prefix='/')
app.register_blueprint(auth, url_prefix='/')

return app

and in my auth.py I have the following as you can see I am attempting to print out the result to the console.

If I move the the code for mysql into my main.py it does work and I am able to print out the data. Its only when I attempt to use the mysql code within my auth.py that I get the error.

from flask import Blueprint, render_template, request, flash
from flask_mysqldb import MySQL
auth = Blueprint('auth', name)

@auth.route('/sign-up', methods=['GET', 'POST'])
def sign_up():
mysql = MySQL()
cur = mysql.connection.cursor()
cur.execute('''SELECT * FROM peeps''')
results = cur.fetchall()
print(results)

return render_template("sign_up.html")

my mistake the first bit of code there is not in my main.py but rather in my init.py

any help would be appreciated

[grim badger]

I understand now

[carmine quail]

anyone good with pyspark?

[torn sphinx]

doesn't sqlite3 throw an exception if a foreign key doesn't exist?

I needed to pass in cursor.execute('PRAGMA foreign_keys = ON;') for it to raise an integrity error.

Seems i have to call it every time after loading the db, is there maybe a flag in .connect() for this?

[mortal scarab]

those are on by default

[torn sphinx]

whats going on then? i dont have them on by default

[mortal scarab]

like this is a new DB i just created u can also check with DB browser

i use the pragma journling for batch exe on the memory journling and switch it back to defalut adn i would not suggest the FK to be kept false queries break if u have them in the DB

[gaunt surge]

Hello, how to import/download data from api?

Example, If an endpoint returns a zip file,
how do I import/download that zip file?

[torn sphinx]

Its not really raising the integrity error unless i explicitly call cursor.execute('PRAGMA foreign_keys = ON;')

However same db it does complain when using sqlitestudio to insert with a wrong fk

import sqlite3

db_connection = sqlite3.connect(':memory:')
cursor = db_connection.cursor()
# cursor.execute('PRAGMA foreign_keys = ON;')
# db_connection.commit()
#
cursor.execute("""CREATE TABLE categories (
ID   INTEGER PRIMARY KEY AUTOINCREMENT,
                         NAME TEXT    UNIQUE
NOT NULL
COLLATE NOCASE
);
""")

db_connection.commit()

cursor.execute("""
CREATE TABLE tags (
    ID          INTEGER PRIMARY KEY AUTOINCREMENT,
    NAME        TEXT    NOT NULL,
    CATEGORY_ID INTEGER NOT NULL
                        REFERENCES categories (ID) ON DELETE CASCADE MATCH [FULL],
    UNIQUE (
        NAME COLLATE NOCASE,
        CATEGORY_ID
    )
);
""")

db_connection.commit()

cursor.execute(f'INSERT INTO tags VALUES(null, "Test", 1);')  # -> I would assume i get an exception here, but nothing happens unless i uncomment lines 5 and 6
db_connection.commit()

[median swift]

Hi there, I am wondering what the best way is to get the rank of a record in postgres (asyncpg). Let me explain, I have a select statement that selects all the user's based on a condition. I want to order them based on "points" - then, I want to find out their position in the leaderboard so their rank. Hope that makes sense.

[proven arrow]

You can use order by to sort the result?

[proven arrow]

That’s expected since sqlite might disable them for backwards compatibility.

[median swift]

Yes I can use order by, but how do I get the rank?

[proven arrow]

Use a row_number window function

[median swift]

ok thanks

[proven arrow]

Here’s an example #databases message

I think it’s for the same leaderboard stuff

[median swift]

👍

[ruby jewel]

Dumb question... Say I have 2 tables in a RDBMS and I want to create another table based on the data within those tables. What would you call that table?

Like terminology wise. I have been trying to research it but I feel like I don't have the terminology to start.

[proven arrow]

Does the other table hold the data or just reference it with a foreign key?

If not then your looking for a view

[ruby jewel]

Nope it wouldn't be a view

It would be holding actual data, not just reference it.

I was thinking maybe derived table... but thats a different thing

[proven arrow]

Then there is no such thing

If it holds the data then it’s just another table it self

[ruby jewel]

Ah okay, thanks!

[languid gate]

Hey general DB question, I have a handful of Python processes writing and reading to a single table. If I read a record, update in memory, then perform an Update, how can I be certain that another process has not changed the record since I last performed the update?

[harsh pulsar]

It depends on what you're concerned about. The database itself can deal with concurrent writes, although the exact behavior will vary across databases, and is often configurable.

But if you're worried about something like "I need to be very very sure that the data in Python exactly reflects what's in the database" then you might want to head over to #async-and-concurrency

[copper wyvern]

I have a column in a table that I created as a TEXT and it holds duplicate records that are either "RED" "BLUE" or "YELLOW" would converting these to integers like 0, 1, and 2, increase query speed and if so how do I turn an already filled out column with half a million rows into integers without affecting the rest of the row

SQLITE btw

[harsh pulsar]

what kind of query?

[mortal scarab]

@languid gate db has Sync on data mod queries

[copper wyvern]

like SELECT item1, item2 FROM table WHERE color == "RED"

[torn sphinx]

is there any databases i can use with repl

[jovial nymph]

what am i doing wrong

[bronze bone]

I have a postgres performance question between two query patterns. Anyone free to riff? I'm a bit new to structuring dbs for a project.

[proven arrow]

Yes ints are faster. You should first make a new column, and so this way you don’t break any existing code. Ideally there should be a separate table and foreign key pointing to it to store the colours. More importantly you should index the column if you will be searching that column frequently.

[hot trellis]

Hey, I was thinking of trying to learn how to use databases, right now I have only been using json files and with I have been told it's a big no no, so are there any free databases you guys would recommend? Any useful answers are really appreciated! :)

[wanton trout]

You can use SQLAlachemy and mySQL

its old school as relational but you will learn a lot from it

shows you how to use object relational mapping in python and such.

[hot trellis]

K thx, I'll have a look at it now

[wanton trout]

https://www.mysql.com/

https://www.sqlalchemy.org/

https://realpython.com/python-mysql/

🙂

[primal maple]

Hi guys. I am making a dictionary bot discord that my database on Firecloud. So my question is to look for a string (word) in Firecloud, what should read/study. Many thanks

[uneven stream]

createdb: error: could not connect to database template1: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

am using a cloud ide, does it have something to do with it?

[marsh mango]

Where to type the second part in?

[wanton trout]

Just in the same terminal

You using Linux?

[wanton trout]

Check ports open and stuff

[uneven stream]

what's that

[austere portal]

#help-mango Thanks

[marsh mango]

no windows on my pc - linux on my raspy (soon)

[harsh pulsar]

it depends on the database. postgresql has a native ARRAY type, and you can also use an array in JSONB

sqlite has JSON1

mysql has a JSON type https://dev.mysql.com/doc/refman/8.0/en/json.html

that said, consider using a normalized data layout instead of an array. one might be better than the other

what kind of data are you storing?

[austere portal]

Hi, I am writing tests for my module which uses asyncpg I am using pytest-asyncio to write the tests.
My code py @pytest.mark.asyncio async def test_table_creation(): async with pool.acquire() as conn: await Users.create_table(conn) # I have defined Users and Post classes above await Post.create_table(conn)But when I run the file using python -m pytest path/to/my/file I get this error powershell FAILED test_code2.py::test_table_creation - asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progressBut when I change my code to this ```py
import asyncio
run = asyncio.get_event_loop().run_until_complete

async def test_table_creation():
async with pool.acquire() as conn:
await Users.create_table(conn)
await Post.create_table(conn)

run(test_table_creation())```And when I run the file as a normal python file it works and I don't get any errors

[languid gate]

I have a large table(PG) that does a TON of updates(12m) in a 24hr period, causes a ton of dead tuples. I have my worker process stop, vacuum, and start again when tuples exceed 1m -- which has seen better performance then just the default autovacuum process(even when tuning), is there any other suggested way ot improve update performance?

To preface, the table colum nis a large JSON type, which, realistically, we should move to Dynamo or the like, but was looking for solutions that might not involve adding additional dependencies

[languid gate]

Yeah, my problem is that I was debating having multiple writers to the same table from a fan-out queue process. Which, I think is an antipattern anyway.

[still whale]

To respond to your original question, row updates should be wrapped in a transaction such that transactions will only begin when a currently running transaction commits/ends. Simple answer: rely on transactions

And yea that depends on your database. Strong support for standard SQL databases

[languid gate]

So, with PG, even with Tx's, if I read a value from the db, update it in mem, and then perform a update(e.g., on a JSON column), it's still a valid operation, regardless if someone edited that row following my initial read, but prior to my update(write)

[still whale]

You won't be able to avoid using transactions using normal mechanisms for PostgreSQL. You'd have to hack the dbdata directory or do something horrible. Not sure what you mean by "valid operation" I think you're thinking about which data might be read, and that's critically dependent on the timing of a transaction commit, whether or not the DB session is shared somehow, or if you have some locks in the mix

[languid gate]

Sorry, I worded my question poorly. I'm looking for a mechanism, outside of row-level locks, that would let two separate working threads/vms change a cell value(e.g., every time someone accesses the row, they need to increment in the Python process by 1, not in the db query => so, p1 pull record, update count cell, and update, p2 would do the same, and count cell should be value 2, but, if p1 reads the same time p2 does, both count values would be 0, and the final count value would be 1 -- if that makes any sense

[still whale]

Yeah, you're good-to-go with transactions out-of-the box. In your description one thing is that p2 may need to "refresh" or "reread" the data if it is performing an update with old data, a previous select or whatever

[languid gate]

Yeah, thats the issue, how do I let P2 know the data has been updated if they are non-communicative processes on separate instances or threads

Outside of just using a row-lock

[still whale]

That is an issue. You basically need to poll for a lock to release, have another event-emitting type system, etc.

I recommend building without the requirement to have the data be updated

Or maybe the process you really need cannot be parallelized

@languid gate

[languid gate]

@still whale Yeah, having multiple concurrent writers for a process that performs select/update and those updates occur app side is definitely an anti-pattern(the multiple writers, that is)

[still whale]

Yes 🙂 Fun 🙂

[languid gate]

Any insight on cutting down on excess of dead-tuples when doing large bulk load/update processes? Updates are not on/do not include indexes, so was thinking would be a HOT tuple, rather than allocating dead row. Doing 20m insert/updates to json field.

Looking at pg_stat_get_tuples_hot_updated, they do increase, but a dead_tup is also allocated on the name

[still whale]

Not sure about that. How about 1m insert + update per thread 20 threads?

(minimize on transactions especially transaction blocking)

[languid gate]

@still whale Well, based on our prior convo, correct pattern should be single reader/writer, which is what I use now, and bulk insert/update 3-5k records at a time. After awhile dead-tuples increase, vacuum process kicks off, everything stalls

@still whale So, realistically, I only ever have a single transaction at any time ( unless a process not involved in keep records up to date is reading from the table )

[still whale]

I'm reading that dead tuples arise when data is deleted. When are you deleting rows? Also, are you using autovacuum?

I recommend using smaller transactions too. Unless performance is really the best at 3-5k records per transaction. That's a load

[languid gate]

because of mvvc -- dead tuples occur with any delete or update operation, how pg handles concurrency

@still whale Whats a general avg bulk insert size?

[still whale]

Looks like "COPY" is recommended. I haven't used this before

https://www.postgresql.org/docs/current/populate.html#POPULATE-COPY-FROM

[languid gate]

I believe COPY is used for strictly bulk inserts, rather than updates

I'm doing a blend of both, depending on existing records

[still whale]

Do updates elsewhere, then use COPY

[elder karma]

I think copying to a temporary table and upserting is a good pattern in that case

[austere portal]

thanks

[austere portal]

I'm not sure if it is a problem with pytest-asyncio or asyncpg

[sage summit]

hey, im writing a discord bot atm, and for every message sent, it has a configurable chance of doing certain things
i'm storing the configurable chances in a mongodb, but how can i prevent querying the db for every single message sent?

[weak raft]

I have a discord bot and I've been using MongoDB for it.

I lately heard a lot of things about MongoDB that it's bad in general or it is not good for discord bots, I'm now confused that should I migrate?

The only option I have other then MongoDB is SQLite, Should I move to SQLite or stick with MongoDB?

Ping me when someone replies, Any suggestions are appreciated 👍

[raw tendon]

I use MongoDB and I’ve had zero problem so far. It depends on if you’re looking for a big discord bot though

http://cryto.net/~joepie91/blog/2015/07/19/why-you-should-never-ever-ever-use-mongodb/

https://dzone.com/articles/mongodb-the-good-the-bad-and-the-ugly

[weak raft]

I actually need to store a large amount of data

[raw tendon]

Hmm, maybe go for SQL?

maybe PostgresSQL if you want that json like data

[weak raft]

sigh, I have to migrate

[torn sphinx]

can someone teach me mongodb

[torn sphinx]

https://realpython.com/introduction-to-mongodb-and-python/#using-sql-vs-nosql-databases

[austere portal]

Thanks

[torn sphinx]

Hi, i have this code

@commands.command(aliases=['modlogs', 'ml'])
    async def logs(self, ctx, member : discord.Member):
        #db = await bot.db
        async with self.db.execute("SELECT case, mod_id, time, reason, type FROM modlogs WHERE guild_id = ? AND user_id = ?", (ctx.guild.id, member.id)) as cursor:
            data = await cursor.fetchall()
            loglen = len(data)
            #formatted_warnings = "\n".join([f"""**Reason:** {warn[0]}
                                    #**Moderator:** {warn[1]}
                                    #**Time:** {warn[2]}\n""" for warn in data])

            formatted_warnings = "\n".join([f"""**Case:** {warn[4]}
                                        **User:** {member} 
                                        **Type:** {warn[3]}
                                        **Moderator:** {warn[1]}
                                        **Time:**: {warn[2]}
                                        **Reason:** {warn[0]}\n""" for warn in data])
            if loglen > 0:                            
                embed = discord.Embed(description=f"**{loglen} warnings found for {member} (ID: {member.id})**\n \n {formatted_warnings}", color=discord.Color.blurple())
            #reason = "\n".join([f'Reason: {warn[0]}' for warn in data])
            #modid = "\n".join([f'Moderator: {x[1]}' for x in data])
            #time = "\n".join([f'Time: {y[2]}' for y in data])
            
            #embed=discord.Embed(description=f"{loglen} logs found for {member} \nUser: {member} \nReason: {reason} \nModerator: {modid} \nTime: {time}")
                return await ctx.send(embed=embed)
                
            else:
                emoji = bot.get_emoji(id=863349227451318302)
                embed=discord.Embed(description=f"{emoji} No logs found for {member}", color=0xffffff)
                return await ctx.send(embed=embed)

But I get this error sqlite3.OperationalError: near ",": syntax error

[cyan yacht]

Its because "case" is a keyword in SQL
You should put backticks around it
Or it will be much better to use some other name for that column

[median wave]

backticks?

why backticks

shouldn't it be "

or '

[cyan yacht]

Column names use backticks iirc 🤔

[torn sphinx]

Thank you!

[torn sphinx]

backticks worked

[median wave]

oh idk, but iirc you can do both

[simple valley]

cursor.execute("""CREATE TABLE IF NOT EXISTS tbluserresource (
userid INTEGER,
resourceid INTEGER,
quantity INTEGER DEFAULT 0 NOT NULL,
FORIEGN KEY (userid) REFERENCES tbluser (userid),
FOREIGN KEY (resourceid) REFERENCES tblresource (resourceid),
PRIMARY KEY (userid, resoureceid)
)""")``` This returns `C:\Users\joshu\OneDrive\Desktop\Python Projects\RAC>main.py Traceback (most recent call last): File "C:\Users\joshu\OneDrive\Desktop\Python Projects\RAC\main.py", line 33, in <module> cursor.execute("""CREATE TABLE IF NOT EXISTS tbluserresource (sqlite3.OperationalError: near "userid": syntax error`

... why?

[median wave]

well try making the table manually

its easier

[simple valley]

nah i want it run in my code every time it's ran as the program can be used on multiple devices and it needs to create the database every time it's moved

or more importantly, I want it to work. I don't want to do this any differently :/

[median wave]

i'm sorry, i can't see what's wrong/idk much about making it like that, i make it all manually, takes just like 10 seconds lol

[simple valley]

yeah ik it's easier to manually make it but i'm not telling my users in a readme.txt to manually create a database and about 8 tables

lol

[austere portal]

Thanks

[velvet coral]

good night, i wanted to create a plugin module in jpython for the autopsy tool. fetch data from SQLLITE databases.

[austere portal]

Any help is appreciated :)

[sinful vault]

has anyone here used redis? preferably with heroku

[stuck wraith]

hi

can someone help me with this?

https://stackoverflow.com/questions/68329450/use-mongodb-with-python2

[brazen charm]

Dont use Python 2 🙂

[sinful vault]

https://stackoverflow.com/questions/68329581/heroku-redis-issue-on-python

can someone help with this

[surreal nimbus]

haven't they some sort of documentation for this?

[austere portal]

Hello...

[sinful vault]

for the heroku redis?

[surreal nimbus]

yeah if i saw correctly heroku has a section of redis

[sinful vault]

but it doesnt provide the redis functions and everything

that's just redis itself

but do you know why the r.set() doesn't work when i host on heroku, but when i do it locally it works?

[surreal nimbus]

i have no really a clue atm, i also was thinking is this not a limitation on their side? like i remember on my early days i wanted to host a bot on their platform but most of my functions would not work on their free plan or something

[sinful vault]

well heroku offers their own redis service

so that is what i'm using

[surreal nimbus]

but i assume if you wrap it around a try and except and if it doesn't run that bit it should than throw a error?

[keen gorge]

How can I get the auto increment value from an INSERT in aiomysql?

[proven arrow]

https://aiomysql.readthedocs.io/en/latest/cursors.html?highlight=lastrowid#Cursor.lastrowid

[keen gorge]

Thanks!

[sterile yoke]

@simple kestrel

[empty creek]

can i get some help with json?

im trying to make multiple dicts inside a list and im getting told that dict has no attribute append

code:

async def BankSetup(self, guild):
       with open("bank.txt") as json_file:
           self.points=json.load(json_file)
       for user in guild.members:
            if any([id in self.points == user.id]):
                pass
            else:
                self.points.append({
                    "id": user.id,
                    "points": 0
                })
       with open("bank.txt", "w") as outfile:
            json.dump(self.points, outfile)

error: Unable to find reason for error. Original Error: 'dict' object has no attribute 'append'

according to a user on stack overflow this code worked perfect for them. idk if im being stupid and doing something wrong or what

https://stackoverflow.com/questions/63625197/how-to-make-a-currency-system-in-discord-py

anyone know why?

[dense barn]

how do i change my postges column from read only to where i can edit it directly from pgadmin?

[proven arrow]

?

[dense barn]

can i edit a column manually?

[proven arrow]

You can edit any column you like

[dense barn]

it wont let me.

[proven arrow]

Who doesn’t?

Not sure what you mean you can’t edit

[dense barn]

Ok, you know how you can edit columns from DB browser for sqlite?

[proven arrow]

Ok

[dense barn]

when you hover over that lock, it says read-only, how would i change that where i can type in the column?

[proven arrow]

Does the table have a primary key?

Adding one should fix the issue

And pgadmin is horrible

[dense barn]

what should i use then?

[proven arrow]

There’s others but I guess it comes down to personal preference. You can try out others when you have time.

[dense barn]

didnt help.

[dense barn]

alr

[proven arrow]

Well that’s the way. So clearly you must not be following the right steps

Here is the pgadmin doc for your reference

[dense barn]

ah

it worked. Thanks!

[serene pivot]

How can I combine these two queries into one?

DELETE FROM avatars a
USING useravatars u
WHERE a.hash = u.avatar
AND u.user_id = $1;
DELETE FROM useravatars u
WHERE u.user_id = $1;

[serene pivot]

postgresql by the way

I know I can use left join or inner join in mysql to get this done

something like this..

DELETE  avatars , useravatars FROM avatars INNER JOIN useravatars
WHERE avatars.hash= useravatars.avatar AND useravatars.user_id = 782479134436753428

but I don't know how to do that in postgres

[untold sleet]

self.points is a dictionary

[serene pivot]

Nevermind. Got it. Just needed to use a with statement.

[uneven stream]

is there a fetchrow for aiosqlite? I had seen it somewhere but idk what I had seen

[burnt turret]

It's called fetchone

[uneven stream]

I used that one but I had seen something like ```py
row = ____.fetchrow()
row['key']

[burnt turret]

https://aiosqlite.omnilib.dev/en/stable/api.html?highlight=fetch#aiosqlite.cursor.Cursor.fetchone

Think that's on asyncpg?

[uneven stream]

oh

thanks anyways!

[burnt turret]

fetchone is fetching a single row so it's doing what you need

[uneven stream]

o

[austere portal]

thanks

[uneven stream]

wait a min
I need to close cursors in aiosqlite as well? isn't it just commit?

[pure sleet]

what does your initial definition for points look like? cause this seems like points is a dict and not a list

[austere portal]

I've been try to fix this problem for days, so any help is appreciated

[bleak crown]

Hi, i'm using sqlite, i have a table like this and i want "rank" column to be the sum of ffa_rank, teamer_rank and duel_rank. Is it possible to do it inside of the sqlite directly?

[brazen charm]

you shouldddd be able to do this with a aggregate function 1 sec

SELECT SUM(rating) AS summed_rank FROM api_anime_data ORDER BY summed_rank DESC; You should be able to do a similar thing as this

might need to change SUM though

[bleak crown]

Hum

[brazen charm]

ditch the SUM and just use standard addition for the columns

I jumped to SUM just by default but that adds up the grouped columns

[bleak crown]

No, can it be in the construction?

Not in selecting

[brazen charm]

oh yeah thats even easier

sorry when you said rank i thought you mean order

[bleak crown]

No no 😄

[bleak crown]

Awesome :3

So, how my "rank" column can be dynamic to the sums of other ranks?

[brazen charm]

for the insert just do rank = column1 + column2 + column3 should work in the insert INSERT INTO foo (c1, c2, c3) VALUES (1, 2, c1 + c2)

that should work for sqlite

i think thats what you wanted?

[bleak crown]

Hummm

Yes, this could work but again not the thing i wanted

😄

[brazen charm]

😅 Sorry, can you explain a lil more about what you wanted then

Not sure if i follow now

[bleak crown]

I'm asking are there any methods or things at creating the "rank" column, so rank column can dynamically take datas from the other 3 columns.

Like, i want rank to be a script or something like that

So i can assing rank to be c1 + c2 + c3

Or, in my case ffa_rank + teamer_rank + duel_rank

[brazen charm]

do you want to do this at insertion time

[bleak crown]

Nope, i want it to be automatically

[brazen charm]

or at the time of creating the column e.g alter table

[bleak crown]

Yes, at the time of creating the column. In CREATE TABLE or something like that

I want it to be dynamic

[brazen charm]

Err you cant reliably do it like that tbh, The easiest way is at insertion time with what i sent above

[bleak crown]

Yeahh, you are right but yeap, oky 😄

[brazen charm]

that will calculate the rank when you insert the table, you can use this for updates as well though

[bleak crown]

I was asking for can a column be a script but thanks for the help too ❤️

Was helpfull

[proven arrow]

You could use a generated column for that

[obsidian mortar]

hi i need somehelp...

docs_path = os.path.abspath(args.C:/Users/User/Downloads/boolean-retrieval-engine-master/boolean-retrieval-engine-master/data)

is it correct to write the doc path like that?

[austere portal]

Thanks :)

[rustic geyser]

@austere portal

@client.command()
async def banneron(ctx):
     db = sqlite3.connect('memes.sqlite')
     cursor = db.cursor()
     cursor.execute("SELECT channel_id FROM memes WHERE guild_id = ?", (ctx.guild.id,))
     result = cursor.fetchone()
     if not result:
         await ctx.send("You have not assigned a welcome chane yet use `*setwelcomechannel #channel`")
         return
     sql = ("INSERT INTO memes(guild_id, banner) VALUES(?,?)")
     val = (ctx.guild.id, "yes",)
     cursor.execute(sql,val)
     db.commit()
     cursor.close()
     db.close()
     await ctx.send("Done, Now i will send banner whenver someone joins")```

i think the error is where i put "yes"

its not correct way ig

[austere portal]

where have you put "yes"?

[rustic geyser]

in banner

column

db2 = sqlite3.connect('memes.sqlite')
    cursor2 = db2.cursor()
    cursor2.execute('''
              CREATE TABLE IF NOT EXISTS memes(
              guild_id TEXT,
              channel_id TEXT,
              banner TEXT
              )
              ''')
    db2.commit()

also it once said no column as banner

[austere portal]

have you connected to the correct db?

[shell ivy]

there are 3 columns and u are inserting only 2

[rustic geyser]

yes

[rustic geyser]

i only wanna insert in 2

[shell ivy]

add None as str in channel_id

[rustic geyser]

k

[shell ivy]

"None" or as u wish

[austere portal]

i think if you dont pass in a value it will be automatically set to null

[shell ivy]

maybe, not sure though

[wintry stream]

Situation:
Using asyncpg: I have a table with 2 rows. Both are arrays, one for varchar and one for bigint. I sometimes get a dataset of string and a long integer, and if either doesn't exist already, insert into with the new data. If one already exists, update that row to insert the other value into the other column. So lets say i have

varchar | bigint
"ABCD" | 1234
"EFGH","IJKL" | 5678

I get the set "ABCD" | 910111213. I want to add 910111213 to the bigint on "ABCD" (since it already exists), and stop.

New situation
varchar | bigint
"ABCD" | 1234, 910111213
"EFGH","IJKL" | 5678

Now I get a set "MNOP" | 14151617, so in this case i want to make a new row with both values. So just add these at the bottom as a new row

Now i know that i can use SELECT WHERE value = ANY (column) to check if it already exists, and use UPDATE SET column = column || array to append a value to the current value.

But the issue i am having is i'm stuck on the logic, so anyone able to give a small pointer or an idea on how to test the situation and do the appropriate thing?

[austere portal]

can you send the error?

[shell ivy]

https://stackoverflow.com/questions/23818934/sql-insert-only-two-values-into-a-table-with-10-fields

[rustic geyser]

sql = ("INSERT INTO memes(guild_id, channel_id, banner) VALUES(?,?,?)")
val = (ctx.guild.id, None, "yes")

like this

[shell ivy]

None in ""

[rustic geyser]

what

[shell ivy]

or the link

[rustic geyser]

oh ok

[austere portal]

no, dont pass in None, it will automatically set the value to null

[shell ivy]

ya

[rustic geyser]

sql = ("INSERT INTO memes(guild_id, channel_id, banner) VALUES(?,?,?)")
val = (ctx.guild.id, "", "yes")

like this

[shell ivy]

...

[austere portal]

no, look at the stackoverflow post Shaun sent

[rustic geyser]

what

should i give gap?

[austere portal]

@rustic geyser

[rustic geyser]

its same way

I saw!

[austere portal]

ok, you dont need to pass in none or ""

[rustic geyser]

um ok

a part of my code just flew away

and repl.it is showing blue therre

WTF

my code went away

WTF

it disappeared

some part of code

hopefully i got backup

[wintry stream]

shouldn't have used replit :p

[austere portal]

[wintry stream]

get a proper IDE/editor

VSC, pycharm, atom, sublime

[austere portal]

Show your new query

[rustic geyser]

sql = ("INSERT INTO memes(guild_id, channel_id, banner) VALUES(?,?)")
     val = (ctx.guild.id, "yes")
     cursor.execute(sql,val)```

like this?

[rustic geyser]

i have pycharm tho it cant keep project on 24/7

[shell ivy]

bruh

[wintry stream]

replit is NOT a host

[shell ivy]

remove the channel_id

[rustic geyser]

ok

[austere portal]

remove the unwanted field

[wintry stream]

it's an online IDE and a very bad one at that

so your bot token and database login info

[austere portal]

which is channel_id

[wintry stream]

how do you know no one can just steal that from you?

[rustic geyser]

sql = ("INSERT INTO memes(guild_id, banner) VALUES(?,?)")
     val = (ctx.guild.id, "yes")```

[shell ivy]

ya

[rustic geyser]

i have 3 columns

[shell ivy]

try

[rustic geyser]

IT WAS LIKE THIS BEFORE

uhhhh ok

[austere portal]

why did you change it lol?

[rustic geyser]

shaun told me to

[shell ivy]

🥲

[austere portal]

Show how you are executing it

[rustic geyser]

not working no error

@client.command()
async def banneron(ctx):
     db = sqlite3.connect('memes.sqlite')
     cursor = db.cursor()
     cursor.execute("SELECT channel_id FROM memes WHERE guild_id = ?", (ctx.guild.id,))
     result = cursor.fetchone()
     if not result:
        await ctx.send("You have not assigned a welcome chane yet use `*setwelcomechannel #channel`")
        return
     sql = ("INSERT INTO memes(guild_id, banner) VALUES(?,?)")
     val = (ctx.guild.id, "yes")
     cursor.execute(sql,val)
     db.commit()
     cursor.close()
     db.close()
     await ctx.send("Done, Now i will send banner whenver someone joins")

[shell ivy]

also u don't need cursor.close()

[rustic geyser]

ohh alright

[austere portal]

try doing cursor.execute("INSERT INTO memes(guild_id, banner) VALUES(?,?)", val)

And the sqlite3 module is blocking

[rustic geyser]

um ok

blocking?

[shell ivy]

use aiosqlite

[austere portal]

ye

[rustic geyser]

NOOO

i dont wanna learn aiosqlite for 1 command

id rather give up

[shell ivy]

it's the same

[rustic geyser]

its not even guraenteed that my bot'll get verified

[shell ivy]

just async/await

[rustic geyser]

oh whatever

sqlite3 works perfect for other cmds

[austere portal]

Yeah, its the same but you need to await some stuff...

[shell ivy]

lol okay

[austere portal]

your bot wont work for a few seconds/milliseconds

[rustic geyser]

cursor.execute("INSERT INTO memes(guild_id, banner) VALUES(?,?)", (ctx.guild.id, "yes",))```

like this?

[shell ivy]

this should work though

[shell ivy]

ya

[rustic geyser]

k

rebooting

wait my whole bot is not working

[shell ivy]

can u send the whole file? https://pastebin.com/

[rustic geyser]

oh faaack repl.it is rebooting and IT WILL RE INSTALL ALL BLOODY PACKAGES CUZ ITS THE WORST EDITOR EV EVER

[austere portal]

idk, dont use it then...

[rustic geyser]

im not ready to send 1.5K lines of code filled with api keys and my reddit password

lets wait for 1 minute cuz its gonna install 27 packages

bruh repl.it stuck is stopping

and my changes would not be saved

[austere portal]

just use vs code

[rustic geyser]

an editor that keeps my bot online even when my pc is off

tell

pycharm profesiionals?

lmao

[shell ivy]

replit will get the bot ratelimited

[rustic geyser]

why?

YES IT GOT

[shell ivy]

shared ip

[rustic geyser]

discrod messaged me once

[austere portal]

i used replit and got banned from the api lol

[rustic geyser]

for rebooting my bot 1000 times

why

[shell ivy]

lol

[rustic geyser]

what

really

[austere portal]

a person in the same ip got perma banned and that effected me

[rustic geyser]

dont scare me

then u got ur bot back?

u contafcted discrod?

tell

u got?

[austere portal]

i just switched to vs code and everything was fine

[rustic geyser]

oh-

[shell ivy]

worked?

[rustic geyser]

uhh

stuck

opening new website

ps my changes wouldnt be saved

best ide ever

repl.it is loading

i use pycharm

i dont like vsc

[shell ivy]

then use it now

[rustic geyser]

alright ig

FINALLY REPL.IT LOADED

[shell ivy]

[rustic geyser]

and ofc my changes r not saved

[austere portal]

code it pycharm deploy it to replit when you are done, ez

[rustic geyser]

yea nice idea

[shell ivy]

-_+ bruh

[rustic geyser]

+_+

@client.command()
async def banneron(ctx):
     db = sqlite3.connect('memes.sqlite')
     cursor = db.cursor()
     cursor.execute("SELECT channel_id FROM memes WHERE guild_id = ?", (ctx.guild.id,))
     result = cursor.fetchone()
     if not result:
        await ctx.send("You have not assigned a welcome chane yet use `*setwelcomechannel #channel`")
        return
     cursor.execute("INSERT INTO memes(guild_id, banner) VALUES(?,?)", (ctx.guild.id, "yes",))
     db.commit()
     db.close()
     await ctx.send("Done, Now i will send banner whenver someone joins")
``` no error doesnt works

[austere portal]

try printing result

[rustic geyser]

ok

[austere portal]

what does it print?

[rustic geyser]

wait

hoping repl.it doesnt gets stuck again

[shell ivy]

....

[rustic geyser]

cant even deploy

wait for a minute

not stuck

but re-installing packages

DUMBEST NOTEPAD EVER

finally online

it prints it out @austere portal @shell ivy

('863051313902387270,)1

it prints that

[austere portal]

i dont think a id is supposed to be str

[rustic geyser]

um

its a tuple

[austere portal]

change the guild_id and channel_id from text to integer

[rustic geyser]

oh ok

[austere portal]

it will make more sense

[rustic geyser]

INTEGER

[odd grove]

clustered primary keys always better anyway

[austere portal]

its supposed to be a tuple

[rustic geyser]

the i dont need to change it to integer?

[austere portal]

What's that 🤔

[rustic geyser]

it prints the same thing

@austere portal

[austere portal]

ok

no error?

[rustic geyser]

i think it prints korrect thing

NOPE

[austere portal]

does it insert the data?

[rustic geyser]

wait lemme see

no

it does not creates a banner column

wtf

[austere portal]

db files are not to be opened like that

[rustic geyser]

db2 = sqlite3.connect('memes.sqlite')
    cursor2 = db2.cursor()
    cursor2.execute('''
              CREATE TABLE IF NOT EXISTS memes(
              guild_id INTEGER,
              channel_id INTEGER,
              banner TEXT
              )
              ''')
    db2.commit()```

[austere portal]

that wont work cuz you already have a table made

[rustic geyser]

idc

[rustic geyser]

oh F

[austere portal]

use ALTER TABLE

[rustic geyser]

then what to do

k

ALTER TABLE IF NOT EXISTS

?

[austere portal]

no

alter is used to edit a existing table

[rustic geyser]

db2 = sqlite3.connect('memes.sqlite')
    cursor2 = db2.cursor()
    cursor2.execute('''
              ALTER TABLE memes(
              guild_id INTEGER,
              channel_id INTEGER,
              banner TEXT
              )
              ''')
    db2.commit()```

[austere portal]

no

[rustic geyser]

then

....

[austere portal]

ALTER TABLE table_name
ALTER COLUMN column_name new_data_type;

[rustic geyser]

confusing

[austere portal]

nah, not really

[rustic geyser]

why u put t hat colon

this aint js

[austere portal]

this is sql

[rustic geyser]

ok

[jaunty galleon]

In sql you can use ; as well I think, I use it for sql shell

[rustic geyser]

o

    db2 = sqlite3.connect('memes.sqlite')
    cursor2 = db2.cursor()
    cursor2.execute('''
              ALTER TABLE memes
              ALTER COLUMN guid_id INTEGER,
              ALTER COLUMN CHANNEL_ID INTEGER,
              ALTER COLUMN banner TEXT
              ''')
    db2.commit()```

@austere portal

like this

[austere portal]

yes

[rustic geyser]

wait really

really????

[austere portal]

but why are you altering the banner column?