I think mods could maybe pin a few messages to this channel with links to beginner resources?
#databases
proven arrow
proven arrow
This website is good https://sqlbolt.com/ for interactive tutorial on SQL
proven arrow
Not sure. Maybe someone else can share better.
random creek
can someone help me. this code is working but not as I excepected
@client.command()
async def fight(ctx, user: discord.Member):
await ctx.send(f"{user.mention} do you accept {ctx.author.mention}'s challenge? Y/N")
msg = await client.wait_for('message', check=lambda message: message.author == user)
if (msg.content) == "N":
await ctx.send(f" The battle is cancled")
if (msg.content) == "Y":
await ctx.send(f""" The battle has begun its {user.mention}'s turn what would you like to do?
1) punch
2) kick
3) slap
4) lick""")
if (msg.content) == "punch":
user1 = random.randit(50, 60)
if (msg.content) == "kick":
user1 = random.randit(100, 120)
if (msg.content) == "slap":
user1 = random.randit(30, 60)
if (msg.content) == "lick":
user1 = random.randit(0, 120)
await ctx.send(f""" The battle has begun its {ctx.author.mention}'s turn what would you like to do?
1) punch
2) kick
3) slap
4) lick""")
if (msg.content) == "punch":
user2 = random.randit(50, 60)
if (msg.content) == "kick":
user2 = random.randit(100, 120)
if (msg.content) == "slap":
user2 = random.randit(30, 60)
if (msg.content) == "lick":
user2 = random.randit(0, 120)
if (user1) > (user2):
await ctx.send(f"{user.mention} did {user1} damage and {ctx.author.mention} did {user2} damage. {user.mention} Won! ")
if (user2) > (user1):
await ctx.send(f"{user.mention} did {user1} damage and {ctx.author.mention} did {user2} damage. {ctx.author.mention} Won! ")
can someone help?
random creek
it was supposed to be a game
naive sandal
Also this is the wrong channel
quaint tiger
can someone help me. this code is working but not as I excepected
@random creekrandom.randitshould probably berandom.randintno?
random creek
ok anything else?
quaint tiger
Dunno, I don't work with discord.py. Just the thing that glared out at me.
torn sphinx
hi, i need help to design my database.
So i want like Orders table, and Products table.
- Each order can have many products, but only 1 of each
- Each product can be in many orders
I dont know how to link this
minor venture
Plz help with SQL
INSERT INTO used_command (user, user_id, whitelist) VALUES (wolfy, 123456545678765456, true);
gives this error psycopg2.errors.SyntaxError: syntax error at or near "user" LINE 1: INSERT INTO used_command (user, user_id, whitelist) VALUES (...
harsh pulsar
@torn sphinx maybe something like this? i think this is how most ORMs would do it
create table "user" (
"id" bigserial primary key,
"created_at" timestamp,
"is_active" boolean,
);
CREATE TABLE "product" (
"id" bigserial primary key,
"display_name" text,
);
create table "order" (
"id" bigserial primary key,
"created_at" timestamp,
"status" bigint,
"user_id" bigint references "user" ("id")
);
create table "order_product" (
"order_id" bigint references "order" ("id"),
"product_id" bigint references "product" ("id"),
primary key ("order_id", "product_id")
);
in postgres you can also have an array column in order and avoid the order_product table but you can't the foreign key constraint that way
create table "order" (
"id" bigserial primary key,
"created_at" timestamp,
"status" integer,
"user_id" bigint references "user" ("id"),
"product_ids" bigint[]
);
Thanks very much. This will be useful!
harsh pulsar
@torn sphinx note that i am not a database expert and there might be better architectures
but again this is what an ORM like django would create for you
torn sphinx
i see but thanks, i now see where to go in direction. 🙂
snow walrus
guys, im making a card game, and having some doubts about database, a card when its picked up by a player have difrent status, that mea a lot of unic cards, even one card could have a lot of variation, the problem is, if i want to change any cards atributes that changes will have to aply to all the existent variations of a card, i dont know if there is a way to make this quickly, a simple querry to change every single variation of card will not cost a lot of time or something?
torn sphinx
@harsh pulsar Hi sorry disturb you again, but quick question. In tables above, the order_product would not be a class/model correct? But the others would?
harsh pulsar
@torn sphinx right, an orm would typically create it for you and you wouldn't specifically create an OrderProduct class
torn sphinx
cool thanks
novel elbow
I am working on creating a program for my Intro to Programming course and will be making a small program where users can enter what alcohol they have and it will recommend cocktails they can make with what they have.
Does anyone have any advice on this idea?
tepid cradle
You just need one ingredients table with all ingredients for all cocktails you have. You can then filter using the name of alcohol provided
novel elbow
Can I set ingredients as true or false maybe?
Since I want them to be able to add each ingredient
tepid cradle
No, ingredient will be the name of the alcohol
tepid cradle
It will be like
Cocktail | ingredient | proportion
bloody mary | cranberry juice | 0.2
bloody mary | vodka | 0.1
bloody mary | soda | 0.7
novel elbow
Ah that makes sense
tepid cradle
*not an actual recipe
novel elbow
How would I link them?
tepid cradle
If someone enters vodka, just search for cocktails which have vodka as ingredient
tepid cradle
If someone enters vodka and tequila, search cocktails which have both
novel elbow
Nice!
tepid cradle
If someone enters vodka and tequila, search cocktails which have both
This will be slightly more complicated than the first
snow walrus
guys, im making a card game, and having some doubts about database, a card when its picked up by a player have difrent status, that mea a lot of unic cards, even one card could have a lot of variation, the problem is, if i want to change any cards atributes that changes will have to aply to all the existent variations of a card, i dont know if there is a way to make this quickly, a simple querry to change every single variation of card will not cost a lot of time or something?
novel elbow
Is there a way that I could do a search where they can type a handful of ingredients
like I could type vodka, cranberry juice, soda, tequila, egg white and it would list cocktails like this:
2/2 Vodka Cranberry
1/2 Tequila Sunrise
1/4 White Russian
novel elbow
Well I have till december
I just want to make sure it impresses my professor so I can get a letter of recommendation
boreal niche
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: UndefinedTableError: relation "accounts" does not exist```
minor ruin
Let’s drop some Postgres tables!
boreal niche
i use
minor ruin
You leaked your password
snow walrus
oops
twilit marlin
i copy pasted it tho
boreal niche
ill change it
twilit marlin
i dont even know how to use it
boreal niche
@client.command(aliases=['balance'])
async def bal(ctx):
db = await asyncpg.connect(user = "xwdwyzcwfuadcy", password = "", host = "", port = "", database = "")
SQL = await db.cursor('SELECT balance from Accounts')
USER_ID = ctx.message.author.id
USER_NAME = str(ctx.message.author)
await SQL.execute('create table if not exists Accounts("Num" integer primary key autoincrement,"user_name" text, "user_id" int not null, "balance" real)')
await SQL.execute(f'select user_id from Accounts where user_id="{USER_ID}"')
result_userID = SQL.fetchone()
if result_userID is None:
SQL.execute('insert into Accounts(user_name, user_id, balance) values(?,?,?)', (USER_NAME, USER_ID, START_BALANCE))
db.commit()
await SQL.execute(f'select balance from Accounts where user_id="{USER_ID}"')
result_userbal = SQL.fetchone()
embed = discord.Embed(
title = f'{ctx.message.author}s Balance',
description = f'\n**Bal:** {result_userbal[0]}',
colour = discord.Colour(0xFFC100)
)
embed.timestamp = datetime.datetime.utcnow()
await ctx.send(embed=embed)```very cool
minor ruin
Nope
boreal niche
anybody know asnycpg tables?
snow walrus
Guys, im making a card game, and having some doubts about database, a card when its picked up by a player have difrent status, that mea a lot of unic cards, even one card could have a lot of variation, the problem is, if i want to change any cards atributes that changes will have to aply to all the existent variations of a card, i dont know if there is a way to make this quickly, a single querry and a update to change every single variation of card will not cost a lot of time or something?
tepid cradle
No need for double quotes around column names
anybody know asnycpg tables?
@boreal niche
Also, asyncpg is a library. It has no tables. The tables are in the postgres database
torn sphinx
hello, i was sent here from #discord-bots
def getRichestPlayers(self):
mycursor.execute("SELECT charactername,money + bankmoney FROM `dubairpv4`.characters WHERE money + bankmoney < 250000000 order by money + bankmoney DESC LIMIT 20")
myresult = mycursor.fetchall()
return myresult
def outputRichestPlayer(self):
x = PrettyTable()
x.field_names = ["Character Name", "Money"]
num = 0
for k in self.getRichestPlayers():
num += 1
charactername = str("{0:02d}".format(num)) + " - " + k[0]
money = "${:,.2f}".format(k[1])
PrettyTable.add_row(x, [charactername, money])
x.align["Character Name"] = "l"
return x.get_string()
this code keeps showing me the same results
am i calling it uncorrectly?
as from the code you see i'm calling it from ```python
for k in self.getRichestPlayers():
but this table shows over and over, and doesn't output newer informations
craggy jackal
Do you expect it to have new info?
Are people losing money or gaining?
They would need to actually have more money than the lowest for it to change.
Or someone would need to drop out.
torn sphinx
well here is the latest info using same query
timber karma
try:
with open("config.json", encoding="UTF-8") as config:
data = config.read()
cursor.execute("TRUNCATE TABLE configuration")
cursor.execute(f'INSERT INTO configuration (configdata) VALUES ({data});', multi=True)
cn.commit()
cursor.close()
cn.close()
print("Task completed successfully\n")
input("Press any key to leave...")
except Exception as e:
print(f"There was an error\nINFO: {e}\n")
input("Press any key to leave...")
``` why isnt this adding anything to the database?you see the results being outputted is the same until i restart the script
so i have to restart the script in order for the table to be updated
could it be from PrettyTable?
timber karma
cursor.execute("INSERT INTO `configuration` (configdata) VALUES (%s);" % (data), multi=True)
``` something wrong with this?i was able to detect where the issue was coming from
def getRichestPlayers(self):
mycursor.execute("SELECT charactername,money + bankmoney FROM `278`.characters WHERE money + bankmoney < 250000000 order by money + bankmoney DESC LIMIT 20")
myresult = mycursor.fetchall()
print(myresult)
return myresult
when i print the result
it keeps showing the same information
anyone knows how can i fix this?
i'm lost
even tho i changed info in mysql
naive sandal
What's a good async mongodb lib?
torn sphinx
10.5.3 MySQLCursor.close() Method
Syntax:
cursor.close()
Use close() when you are done using a cursor. This method closes the cursor, resets all results, and ensures that the cursor object has no reference to its original connection object.
should i close cursor or what, i really need help
i hope the python gods can look down on us to help 
harsh pulsar
it's a good habit @torn sphinx
west plover
has anyone here used the motor driver with the mongo database before
torn sphinx
@harsh pulsar wait, what is lol
harsh pulsar
closing the cursor
usually the cursor is closed when it is deleted, or when it goes out of scope (and is therere implicitly deleted because of how python works)
but that's up to the library developer
west plover
how are async drivers used in databases in general?
do you just put some code at the beginning and then all of the database code is async? or do all of the database code lines have to be changed to work with the driver
for example i have this code:
db=cluster["discord"]
collection=db["users"]
post1={"_id":0, "name":"tim","score":5}
post2={"_id":1, "name":"joe","score":5}
results=collection.insert_many([post1,post2])
do i have to rewrite that so it works with an async driver?
manic geyser
anyone know why my database keeps saying my tables dont exist? I am using heroku, flask, and postgres
I run a db upgrade when I push the application, but it seems to still not register the updates to the db
lime echo
I just created my database using PgAdmin 4, and now I don't know where to locate it.
How do I locate it?
boreal niche
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: UndefinedColumnError: column "571035294829117450" does not exist```
postgresSQL
polar pelican
just chilling out with some databases over here, but I just have a question. I want to store a JSON string as a column (using Flask-SQLAlchemy). the best approach to do this, is it just to save a str() of that object and then convert it?
@boreal niche might wanna take some of that stuff out, people can hack u
boreal niche
@boreal niche might wanna take some of that stuff out, people can hack u
@polar pelican the pasword is changed :/
minor venture
Plz help with SQL
INSERT INTO used_command (user, user_id, whitelist) VALUES (wolfy, 123456545678765456, true);
gives this error psycopg2.errors.SyntaxError: syntax error at or near "user" LINE 1: INSERT INTO used_command (user, user_id, whitelist) VALUES (...
torn sphinx
hi
lament notch
https://sqlbolt.com is good for learning sql
lament notch
@minor venture i'm not quite sure why it's saying there's an error at user, but wolfy should probably be in quotes
minor venture
ok i think it is becouse someone told me user is a unavalible varible name
lusty grail
@void bane remember my issue of yesterday night with postgresql and nullify of numeric?
We resolved it today
Was the most stupid thing
void bane
@lusty grail what was it?
lusty grail
@Sami#0234 I was loading our own module that is supposed to manage DB stuff and there we had defined psycopg2.extensions that changes the type handling for float and int
It's idea was to handle cases where we have numpy nan that doesn't get properly inserted
void bane
huh
lusty grail
But it was written in a wrong way and I didn't even see it initially as was not written by me
Basically it is supposed to convert np.nan to proper postgres null
But instead was converting all floats and int to null 😂
I don't have a code with me, but it was really really stupid mistake
void bane
yea
lusty grail
Like as soon as we saw the code it was evident that it's wrong
Thing is, wd didn't see it
And, as a matter of fact, you don't suspect loaded but unused module to impact your other code usually
Probably costed us in total 6 hours for such a simple thing
I really feel bad for taking your time yesterday on this
void bane
don't worry about that. at least you figured it out
lusty grail
Thanks :)
Yeah that's main thing, it was driving me mad lol
Too bad the person who wrote that piece of code is no longer here, or I would definitely show that (cause it was her who had problems with the code she wrote and asked me to look into 😂)
quaint tiger
@echo urchin both are fairly similar, tbh. I feel MySQL is a tiny bit easier, but that is probably me learning it first.
With my current experience, I would rather learn Postgres
frozen loom
I'm working on a discord bot and I'm undecided on how I should use databases to store data
Right now I have a table called guilds which stores guild related stuff like prefixes and reaction roles. Then I have another table called users which stores user data such as warns and afk statuses.
Would it make sense to store data this way where all guild related stuff are stored in the guild table and user stuff in the users table? Or is it better to have them separately like a separate prefixes table and reaction role table
craggy pawn
is there an easy way to every value in a table? When I print some of my smaller tables out, some of the strings are really long and it wraps around and makes it unreadable
select left(*, 10) is what I would want to happen if it worked
shell ocean
is there an easy way to every value in a table? When I print some of my smaller tables out, some of the strings are really long and it wraps around and makes it unreadable
@craggy pawn are you missing a word after "way"?
truncate?
craggy pawn
Yes but not sql drop truncate
craggy pawn
I have a little streamers table that has the stream title in one column
shell ocean
like are you doing it with a Python library? or like with a CLI tool?
craggy pawn
And sometimes these morons have like a 250 character long title
Just straight up mysql in terminal
shell ocean
hm.
not sure if you can customise the display
but how about applying a function
to that column?
so you actually select a substring
craggy pawn
Left does what I want it to do but I can only do 1 column at a time
And sometimes I really just wanna select *
craggy pawn
yeah I would have to do like SELECT LEFT(id, 10) LEFT(name, 10) LEFT(region, 10)
shell ocean
there's PROBABLY a way to programmatically apply it to each column
but I am not enough of an SQL expert to suggesth ow
sorry 😦
torn sphinx
hi guys someone told me to ask about sqlite
i want to make a pokemon feature but i don't know how to get them to pick a starter
everything is good
the only issue i have...
is actually saving the data
when they view their .pokemon
but i don't even have a command for that either 😅
twilit marlin
INSERT INTO Pokémon_table VALUES(name="Dialga", type="i dont remember", health=69); @torn sphinx something like this
you'll have to create the Pokémon_table first ofc
torn sphinx
you think i want them to start with dialga 
twilit marlin
if you are feeling ambitious, why not? @torn sphinx it's your game/code, you can do anything you want
twilit marlin
yeah looks fine to me
torn sphinx
alright!
twilit marlin
shouldn't the table name be Pokémon tho? instead of users
torn sphinx
@twilit marlin so here is my code for my catch command so far
@client.command()
async def pstart(ctx):
embed=discord.Embed(title="Welcome to the world of Pokémon!", description="To Start your adventure, choose one of these pokémon just simply type in ``pick <pokémon>`` like this: ``pick Squirtle``, no need for command prefix :)")
embed.set_author(name="Start a new journey")
embed.add_field(name="Generation I", value="Bulbasaur | Charmander | Squirtle", inline=False)
embed.add_field(name="Generation II", value="Chikorita | Cyndaquil | Totodile", inline=False)
embed.add_field(name="Generation III", value="Treecko | Torchic | Mudkip", inline=False)
embed.add_field(name="Generation IV", value="Turtwig | Chimchar | Piplup", inline=False)
embed.add_field(name="Generation V", value="Snivy | Tepig | Oshawott", inline=False)
embed.add_field(name="Generation VI", value="Chespin | Fennekin | Froakie", inline=False)
embed.add_field(name="Generation VII", value="Rowlet | Litten | Popplio", inline=False)
embed.add_field(name="Generation VIII", value="Grookey | Scorbunny | Sobble", inline=False)
embed.set_image(url="https://i.imgur.com/oSHo1IZ.png")
await ctx.send(embed=embed)
picking = ['pick Bulbasaur', 'pick Charmander', 'pick Turtwig']
def check (m):
return m.content in picking and m.channel == ctx.channel
msg = await client.wait_for('message', check=check)
await ctx.channel.send(f'{ctx.author.mention} You picked! {msg.content}')```well i named it users because... i can name the table what ever i want right?
twilit marlin
ummm I can't help with actual sql code, i dont know enough
torn sphinx
twilit marlin
@torn sphinx just look online how to rename sql table
torn sphinx
no no not that
i just wanted like
to know
how to
actually get started
not the code
@twilit marlin
twilit marlin
get started with?
torn sphinx
saving the data for this simple pick a starter code
twilit marlin
you can write a python script which asks for user input
and using that saves values to a database using INSERT statements
@torn sphinx
torn sphinx
oh alright
twilit marlin
high geyser
thanks
@twilit marlin do u know the location of databases
like where they default to
to be stored?
twilit marlin
you mostly have the option where to store them
twilit marlin
but in case of sqlite3, they are mostly stored in your current working directory unless you specify otherwise @high geyser
solar gale
what are some legit reasons to use pg over mysql?
high geyser
how to check if there is something in a coloumn in sqlite?
like if the id of discord guild is there in discord coloumn in a table
twilit marlin
@high geyser do you know sql?
twilit marlin
yeah no offense lol
high geyser
ill learn
twilit marlin
im pretty sure you can find a yt playlist from someone who knows much more sql than i do
Corey Schafer has one
twilit marlin
good luck!
little flume
it says i have invalid syntax
mycursor.execute("CREATE TABLE CountriesClaimed (, countryname text NOT NULL, isclaimed text NOT NULL, owningcountry text NOT NULL, owninguser text NOT NULL, id int PRIMARY KEY AUTO_INCREMENT NOT NULL)")
```it is mysql
twilit marlin
you have a hanging , before opening paranthesis @little flume
little flume
like this python mycursor.execute("CREATE TABLE CountriesClaimed, ( countryname text NOT NULL, isclaimed text NOT NULL, owningcountry text NOT NULL, owninguser text NOT NULL, id int PRIMARY KEY AUTO_INCREMENT NOT NULL)")
@twilit marlin
??????
twilit marlin
yeah obviously
doesn't it work?
also remove the comma after CountriesClaimed @little flume
quaint tiger
("CREATE TABLE CountriesClaimed (, countryname
little flume
idk
mycursor.execute("CREATE TABLE CountriesClaimed( countryname text NOT NULL, isclaimed text NOT NULL, owningcountry text NOT NULL, owninguser text NOT NULL, id int PRIMARY KEY AUTO_INCREMENT NOT NULL)")```that doesnt work
@quaint tiger @twilit marlin
quaint tiger
Run it manually using the mysql client, and see where it says the error is?
twilit marlin
don't you need to add a trailing ;?
quaint tiger
Not in queries from other languages... that's just when using mysql client
It tells the shell that your command is complete.
lime echo
Does Postgre use a json file to store the data?
brazen charm
No database uses json as a actual storage type
polar dagger
what is better? (mysql-installer-**web**-community-8.0.21.0.msi), or (mysql-installer-community-8.0.21.0.msi)?
the web so I assume most tasks are done on the web
tepid cradle
It's the same. The installer is offline installer, web will download the actual application only while installing
twilit marlin
@polar dagger https://dontasktoask.com/
polar dagger
Oof ok
sorry
this
i'm trying to set up a database for my bot
i'm using discord.py
as well
so basically i want to change from json to a database
tepid cradle
what do you need help with?
tepid cradle
You're still not asking a question. What is the problem here?
twilit marlin
are you asking what all options to select?
tepid cradle
Can you explain what problem you are facing with which you need help? I don't see a problem statement anywhere
polar dagger
No I mean I need help setting it up. I have data stored in a json, I want to add all of that data to mysql
tepid cradle
That's like saying "I need help cooking". That's too broad a statement to respond to.
polar dagger
Well how do I transfer all of the data from the json to mysql
tepid cradle
You convert your JSON data into tabular format, then write them to a MySQL table.
If the JSON is flat (no nesting), then it's very straightforward. But if it is nested, you'll have to think and design the tables accordingly and then convert the JSON to multiple tables.
twilit marlin
I think they just need help with mysql installation, not the actual database management
tepid cradle
It's difficult to help unless they are able to actually frame a good/specific question
polar dagger
Sorry about that, it's just I don't know how to explain
I installed mysql (i think) and now I want all of my data which is in a json to transfer it to mysql and use it with my code
minor venture
cur.execute("INSERT INTO used_command (discord_user, user_id, whitelist) VALUES (wolfy, 123456545678765456, true);")
Is giving me
LINE 1: ...ommand (discord_user, user_id, whitelist) VALUES (wolfy, 123...```
wolfy is supposed to be a value going into column discord_userthis is SQL btw
echo urchin
Is there a way to connect to postgres just by the url? without explicitly declaring parts of the url like host, database, user, password?
white moth
I have a 5gb csv with 11 columns and over 30M rows. I have to connect it to a MySQL db which I will then connect to AWS (I believe RDS) and then access in Tableau.
I am unfamiliar with MySQL and AWS. I know this is a python channel but can someone please help me set this up? I can pay you
tepid cradle
true should be True @minor venture
Is there a way to connect to postgres just by the url? without explicitly declaring parts of the url like host, database, user, password?
@echo urchin depends on the library, you can do this with sqlalchemy, for instance
I have a 5gb csv with 11 columns and over 30M rows. I have to connect it to a MySQL db which I will then connect to AWS (I believe RDS) and then access in Tableau.
I am unfamiliar with MySQL and AWS. I know this is a python channel but can someone please help me set this up? I can pay you
@white moth you'll have to do your own research first. This is likely to take a day or two to accomplish, how do you expect someone in the channel to help with this?
south cobalt
I am developing a program that stores its data in a SQL database. I would like for everyone that has the program to have a local copy of the database is it possible to somehow package the database with the program
rough hearth
@south cobalt you can have a database in a git repo, though storing copies of the database over time would get quite large.
I think
quaint tiger
@south cobalt just use sqlite and add a setup_db function with default values.
rough hearth
I don't think it would be an issue if you want everyone to start out with the same version of a database
quaint tiger
That's what it is designed for
rough hearth
patryk's suggestion sounds better
spark ravine
You can store a sqlite db file. Even a copy of Postgres DB is fine. But it'll ruin your git as git has to go line by line and cache all of that data.
south cobalt
@rough hearth & @quaint tiger i am using MS SQL server
idk how much of a pain it would be to switch over to sqlite3
minor ruin
MSSQL equivalent is LocalDB
quaint tiger
Unless you know your users all run MS-SQL, it's a terrible idea.
south cobalt
the users will access the program from within a virtual machine
we control what they use
its just that each time to program is run it needs its own instance of the database not some unified shared database
minor ruin
south cobalt
watching a video on it now
ty rabbit, is it possible to load the db from like a skelton db that has like all the tables already set up but no data in it
minor ruin
no idea
quaint tiger
ty rabbit, is it possible to load the db from like a skelton db that has like all the tables already set up but no data in it
@south cobalt add a setup function.
south cobalt
is all this stuff posible to be done via PYODBC?
quaint tiger
don't ask me Windows is the worst
quaint tiger
MSSQL equivalent is LocalDB
That's interesting to know regardless
icy hound
Anyone interested in testing out my MySQL + GUI app ??
icy hound
yes
I can give you the source
I would suggest downloading the executable from the release
I used tkinter
south cobalt
ok i was just gonna say if you wanna sell this you may wanna change the name lol
icy hound
Always open for suggestions 😀
I just wanted to share with someone because this is my first real world application project
Haha yes
minor ruin
be a little hard to sell since HeidiSQL is a thing
icy hound
Yes it can't compete with already available products
minor ruin
Mrpolymath, pyodbc is Microsoft recommended MSSQL library
so try it with local DB, I think LocalDB is just weird Connection string
icy hound
But hey it's made in python which is the toughest to design GUIs in or so I think
Oh and also please read the documentation hehe
icy hound
Yeahh
south cobalt
@minor ruin not home right now so i'll have to play around with it then sounds lik a good option though
if i set it up with local db could anyone just pull the project from github and run it for themselves
quaint tiger
Python and GUI is pretty rough
Not the chan for this, so just one quick comment.... PyQt5 is amazing and very simple.
Probably not ideal unless you are ok with GPL though.
boreal niche
@client.command(aliases=['balance'])
async def bal(ctx):
USER_ID = ctx.message.author.id
USER_NAME = str(ctx.message.author)
db = await asyncpg.connect()
async with db.transaction():
await db.cursor('SELECT balance from BankAccounts')
async with db.transaction():
result_userID = await db.fetch('select user_id from BankAccounts where user_id={0}'.format(USER_ID))
if result_userID is None:
async with db.transaction():
await db.execute('insert into BankAccounts(user_name, user_id, balance) values($1,$2,$3)', (USER_NAME, USER_ID, START_BALANCE))
db.commit()
async with db.transaction():
result_userbal = await db.fetch('select balance from BankAccounts where user_id={0}'.format(USER_ID))
embed = discord.Embed(
title = f'{ctx.message.author}s Balance',
description = f'\n**Bal:** {result_userbal[0]}',
colour = discord.Colour(0xFFC100)
)
embed.timestamp = datetime.datetime.utcnow()
await ctx.send(embed=embed)```
```py
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: IndexError: list index out of range```didn't understand what Vex said @boreal niche ? 😂
white moth
can someone please help me load up a sql db with data from a csv on a mac
quaint tiger
can someone please help me load up a sql db with data from a csv on a mac
@white moth did you runSET GLOBAL local_infile=1;?
white moth
dm'ing
toxic rune
Using PostgreSQL (with asyncpg) is it possible to whether know if: There's a row matching the criteria and if there is, return the value I'm looking for?
For example, I can achieve this by doing fetchrow which will return a Record object which lets me know if the row exists. Then I can just grab the value from this Record object and work with it. But maybe there's a better way for this?
quaint tiger
Not familiar with asyncpg, but that sounds like the right way to do it.
In raw SQL, you'd do something like:
select * from foo where bar="baz";
Probably, I wanted to be sure because I might've missed something
quaint tiger
And then you see how many rows it returns.
toxic rune
Basically what I'm doing is SELECT my_column FROM my_table WHERE my_column2 = etc
Which I'm always certain it will return only one row and I only need that column. The reasoning is that I want to know if the row exists and I want to know the value of my_column, but I think this works because of how the library works
brazen charm
It will return everything depending on the call
fetch_all will return anything that matches
fetchone will return a single row
quaint tiger
the alternative would be to do select count(*) from my_table where my_column2 = etc first...
brazen charm
fetchmany will return a number of rows upto what you specify
torn sphinx
mind if I ask about SQLite, I want to know how info is stored in a DB because it is written like anything I have ever seen. Almost like a string? like so ```python conn.execute('''CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
Attempting to create a WHERE filter, but failing...:
WHERE discord_id IN (379138589394468866)
Issue is, is discord_id is VarChar and 379138589394468866 is BigInt. How do I go about converting BigInt to VarChar in the filter to make this work?
remote nova
I took a course in Oracle 5 years ago and have forgotten most the SQL commands for SQL and Oracle, do you know of any good references where I can find all the commands in SQL and Oracle
I am trying to return to programming and have an idea for a little project and I'll need to remember my SQL commands.
@torn sphinx if I remember correctly don't you wrap var's in ""
I think you also need more info to complete the where like the select command
torn sphinx
All the other stuff is done, since I'm doing this in pgAdmin 4 to try it out.
Just that line that isn't working.
remote nova
and the select by itself returns you data including what you are looking for?
torn sphinx
Yeup
Issue is that it can't compare VarChar to BigInt
So I need to convert my BigInt to VarChar, but no idea how to do so.
remote nova
it's stored in varchar right? if so ('379...866') if I remember syntax correctly
if you are sending a number it's an INT, if you were to send a name, wouldn't you add the 'name'
I could be wrong, it's beenb 5yrs since I touched a DB
And to use IN as a command wouldn't you have more than one value
If your SSELECT and FROM works, then WHERE discord_id = '379138589394468866'; should be correct.
Unless you are trying to compare multiple conditions, then IN would be correct
but a string seems to always be sent in ' ' not ( )
does that help @torn sphinx
torn sphinx
Yep, that works now. Thanks a ton!
remote nova
Glad to see I remember a bit
So can anyone recommend a good up to date SQL or Oracle reference?
Better question, is RDMS still the right path to follow? have noSQL databases taken off? or are people just using constructs or other methods these days?
harsh pulsar
Relational databases are still very much standard
NoSQL is rightly relegated to niche use cases
Does Oracle not have good docs? Also why Oracle specifically and not e.g. Postgres or even Sqlite depending on what you are doing?
tepid cradle
Hello, fellow RDBMS fans 😃
I don't like NoSQL databases either
@remote nova, as salt rock lamp said, you can go ahead with SQL. There's no reason to jump to NoSQL unless you have a very specific usecase which SQL Dbs cannot fulfil. I would recommend getting back with PostgreSQL or MariaDB (a MySQL implementation). Both are free and open source and relatively easy to setup.
high geyser
I want to store a bool value into a table's column
what datatype i should use?
am using the sqlite3 module (the prebuilt one in python)
pls ping me when help
astral skiff
@high geyser ```SQLite Boolean Data type
SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).```
pseudo cove
yo
astral skiff
@high geyser Seems like SQLite doesn't have an explicit datatype for Booleans
pseudo cove
Can someone here explain to @rain field why you don't use varchars to store every datatype
rain field
high geyser
o
high geyser
c.execute("""SELECT set_pings_in_tags
FROM guildstats
WHERE guildid = ?""", (ctx.guild.id))``` I want to get values from a column [se_pings_in_tags] with the conditonal WHERE. But I get this error. Why? please help me to fix it. ```py
ValueError: parameters are of unsupported type```please ping me when help
proven arrow
@high geyser When you pass the parameters to the query you need to pass a tuple. At the moment your just passing the integer ctx.guild.id.
(ctx.guild.id) is an integer but (ctx.guild.id,) is a tuple of length one containing the guild ID.
Note how I added the comma after, to convert it to a tuple
boreal niche
didn't understand what Vex said @boreal niche ? 😂
@naive sandal um i posted this here before thatz
In dat other server
naive sandal
Ah
high geyser
hey
code py c.execute("""SELECT authorid FROM tags WHERE guildid = ?,keyword =?""", (ctx.guild.id,shlexsplitted[0])) error:py discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near ",": syntax error welp why dis error occured
pls help me
to fix it
ping me when help
pls
vestal silo
Could not connect to any servers in your MongoDB Atlas cluster. One common reason is that you're trying to access the database from an IP that isn't whitelisted. Make sure your current IP address is on your Atlas cluster's IP whitelist:
this how fixed?
high geyser
how can i view my database
like visually
i cant do it in pycharm
like i can see json files
pls welp me
proven arrow
Which db are you using?
high geyser
sqlite3
proven arrow
You can use https://sqlitebrowser.org/
lime echo
Does Postgre use Json files? I am kinda confused.
proven arrow
How do you mean use them?
lime echo
like, on which file type does it store the data?
twilit marlin
no postgres has its own method of storing data
it's a binary code, you can't read it yourself
high geyser
oh
when I update a value in sqlite it isnt actually getting updated
pls help heres code
if final_key_msg != prev_key:
c.execute("""UPDATE tags
SET key =?
WHERE keyword = ? AND guildid = ?""",(final_key_msg, key_word, ctx.guild.id))
print("Tag key updated")
aw_database.commit()
embed = discord.Embed()
embed.colour = discord.Colour.blurple()
embed.description = "Tag successfully edited."
await ctx.send(embed=embed)``` the code runs because the embed is being sentbut if i query the db based on the valu
i can see that its not updated
pls ping me when help
lime echo
@twilit marlin ohh ok. So, I want to use Postgre with Discordpy. Do I need to create a specific file in my bot folder to store my data?
twilit marlin
@lime echo if you have postgres setup correctly then no
high geyser
pls help me
am I updating properly
because I got no errors
I also commited changes
but the value wasnt updated for key
c.execute("""UPDATE tags
SET key =?
WHERE keyword = ? AND guildid = ?""",(final_key_msg, key_word, ctx.guild.id))```here
celest zodiac
that looks right (BTW you don't need all the spaces in the query). how about printing the variables you're using in the query beforehand to make sure they're what you think they are?
@high geyser
high geyser
oh ok
and the dms was late
xD
but still
@celest zodiac the values arent updated
I also commited the changes
but why?
celest zodiac
like I said, see if the values are what you think they are before the .execute
there's a chance you're recommitting the same data, for instance
high geyser
this should edit key column for the specified keyword
oh ok
@celest zodiac how can i be sure and commit the new data only?
celest zodiac
like I said, print the values you're committing, and make sure they're what you expect them to be
and do the print before the actual commit
celest zodiac
as far as I can tell your .execute statement is well-formed, and since you're not getting errors, this seems the most likely next avenue of troubleshooting
for instance if any element in the WHERE clause is wrong -- that is, not matching existing data -- the UPDATE will run without errors, but won't actually change anything
high geyser
oh I see
thanks i solved it
it was a simple issue but my brain is so stuffed up i couldnt see it
it happens when I code for a long time
xD
celest zodiac
awesome, glad you got it
crisp arch
Can anyone point me in a direction of a decent asynch db library?
crisp arch
Thanks, I'll give it a look!
harsh pulsar
sqlite or postgres
twilit marlin
or json if you don't need a db
harsh pulsar
@twilit marlin i see too many people here asking "how do i update a record in my json file"
its just not worth it
a database is for persistent mutable data
json is for immutable data storage
twilit marlin
thats why I said 'if you don't need a db' @harsh pulsar
harsh pulsar
my point is, too many people here don't realize that they do in fact need a db
twilit marlin
well yeah, but json is pretty easy to use and most of the times does suffice
but yeah I agree, db are more useful obviously
uneven smelt
class DBInterface:
def __init__(self, config: core.ConfigInfo):
connection_url = sqlalchemy.engine.url.URL(
"postgresql", config.username, config.password, config.host, config.port, config.dbname)
try:
self.engine = create_engine(connection_url, connect_args={
'connect_timeout': 20})
except Exception:
raise
def main():
try:
db = DBInterface(config)
except Exception as e:
log.error(
f"Database connection could not be established. Please check your configuration. Error message {e}")
Any idea why I cant catch the error like this?
It just prints me the usual two pages long sqlalchemy error information.
but yeah I agree, db are more useful obviously
@twilit marlin @harsh pulsar I would only use JSON for really simple data storage which does not get changed. For everything else sqlite is also pretty easy to use.
harsh pulsar
try except BaseException maybe?
it might be that the sqlalchemy exception class is ill-behaved and inherits from BaseException and not Exception
uneven smelt
BaseException does not work
@harsh pulsar
Even except:
does not work
class DBInterface:
def __init__(self, config: core.ConfigInfo):
connection_url = sqlalchemy.engine.url.URL(
"postgresql", config.username, config.password, config.host, config.port, config.dbname)
try:
self.engine = create_engine(connection_url, connect_args={
'connect_timeout': 2})
except:
print("test")
exit()
this also does not work :/
manic olive
how can i use a database?
twilit marlin
@manic olive https://www.youtube.com/watch?v=pd-0G0MigUA
lime echo
I am using psycopg2.py with python in order to use Postgre.
connection = psycopg2.connect() I use this but I don't know from where to get my user and password?
twilit marlin
@lime echo you'll have to create a user and password in the postgres shell itself
look up how to create a database in postgresql
river sedge
I'm builind a project with flask+ flask_sqlalchemy and flask migrations. With sqlite, when I try to apply a migration on a model that have not null columns, and In that migration I'm adding another not null column, it gives me an error saying that I cannot assign a null value for a not null column (in this case, the older columns), ans that makes sense but when I do the same thing using MySQL, I don't get an error, because it assign an empty string to the older columns, since they cannot be null
anyone knows how to bypass this using sqlite?
tepid cradle
I am inserting some records in a postgresql database using psycopg2. After insert, I'm trying to fetch the just inserted rows. Psycopg2 documentation says I can simply use cursor.fetch*() method to fetch the rows, but it's not working for me.
Here's my code:
connection = psycopg2.connect("dbname=mf user=user password=pwd host=192.168.0.1")
create_query = "insert into user_folios values(%s, %s, %s, %s)"
params = [4, 'abcd1239', 27, True]
with connection.cursor() as cur:
cur.execute(create_query, params)
connection.commit()
created_folio = cur.fetchone()
When I run this, the entry gets created in the database, but I get this error:
---------------------------------------------------------------------------
ProgrammingError Traceback (most recent call last)
<ipython-input-28-c965e32f1dd8> in <module>
10 cur.execute(create_query, params)
11 connection.commit()
---> 12 created_folio = cur.fetchone()
13
14 created_folio
ProgrammingError: no results to fetch
what do I need to do differently?
pure cypress
An insert query doesn't return anything unless you have a returning clause in the query.
To fetch rows, you need to run a query that returns something and then do fetchone()
Like a select ... from ... query, or insert into ... returning ... as mentioned previously
tepid cradle
OK, so I need to add the returning part. Let me try it out
Thanks, I just added returning user_id, folio_number at the end of the query and got the result I needed.
nocturne bay
do i need to download pgadmin4 to create a db?
pure cypress
No, you can do it via the command line with psql
nocturne bay
whats psql @pure cypress
pure cypress
The command line tool to interact with postgresql
nocturne bay
oh
tepid cradle
@nocturne bay PgAdmin is an software to work with postgresql database. It's not a database in itself.
This is a fairly common confusion which I too had the first time I wanted to work with the Db.
The database is a separate software than PgAdmin. The purpose of the database is to organise & store data and allow you to query it. But it has no native UI, you can only interact with it using command line by default.
But you can connect any Db browser application to it as long as it supports PostgreSQL databases.
PgAdmin is the official UI, but it's neither the only one, nor the best one
torn sphinx
does anyone know how to fix the peer authentication error in pgadmin4 running in server mode i have changed its mode before but i forgot how any help? thanks in advance. PS the password and username are correct.
nocturne bay
@nocturne bay PgAdmin is an software to work with postgresql database. It's not a database in itself.
This is a fairly common confusion which I too had the first time I wanted to work with the Db.
The database is a separate software than PgAdmin. The purpose of the database is to organise & store data and allow you to query it. But it has no native UI, you can only interact with it using command line by default.
But you can connect any Db browser application to it as long as it supports PostgreSQL databases.
PgAdmin is the official UI, but it's neither the only one, nor the best one
@tepid cradle ohhh i needed this help ty btw do i need to download it?
twilit marlin
@nocturne bay You should download it if you're a beginner. But if you are familiar with the command line interface, you don't really need to.
nocturne bay
so how to carry on without downloading @twilit marlin
twilit marlin
Just goto https://www.postgresql.org/download/ to download and https://www.pgadmin.org/download/ if you want a graphical interface
I also suggest you follow a video tutorial to setup postgres on your pc, rather than follow the docs yourself
@nocturne bay
tepid cradle
@nocturne bay I agree with Ignis, it would be better to install with a tutorial the first time rather than guessing and asking at each step. I would recommend installing it with PgAdmin, it will help you get started.
Follow this https://www.postgresqltutorial.com/install-postgresql/
nocturne bay
kk
lilac axle
how to use other languages in mysql???
like when im entering another language into a varchar value it is giving me an error
please ping me when replying😊
tepid cradle
@lilac axle it's not very straightforward apparently. I found this guide : https://www.toptal.com/php/a-utf-8-primer-for-php-and-mysql
Ok, check it's instead https://adayinthelifeof.nl/2010/12/04/about-using-utf-8-fields-in-mysql/
tepid cradle
Ignore the previous one
lilac axle
ok
alright thanks
btw is it possible not to give a limit for varchar??
so that it takes up as many spaces as entered??
lilac axle
oh thanks
reef zodiac
when using order by, how can I make it so if a row has a value it's automatically at the top
tepid cradle
You mean null rows should go at the bottom? @reef zodiac
reef zodiac
No, I have something like this
+----------------------------------+-----------------------------+--------+----------------------------------+------+
| id | item_name | price | extra_value | ending | bin |
+----------------------------------+-----------------------------+--------+-----------------------------------+-----+
| 00040b521f854c17a7311a576b432fe8 | AAAAAAAAAA | 3048 | 0 | 2020-09-12 12:44:57 | 0 |
| 0000a9c933a0448faa633809bdc0e21c | AAAAAAAAAAAAAAAAAAAAAAAAAAA | 701 | 0 | 2020-09-12 17:44:19 | 0 |
| 000524122d8f46489d2dc4fcc380ee63 | AAAAAAAAAAAAAAAAAAAAAA | 150000 | 0 | 2020-09-12 13:56:28 | 1 |
+----------------------------------+-----------------------------+--------+-------------+--------------------+------+
and I want it to return
+----------------------------------+-----------------------------+--------+----------------------------------+------+
| id | item_name | price | extra_value | ending | bin |
+----------------------------------+-----------------------------+--------+-----------------------------------+-----+
| 000524122d8f46489d2dc4fcc380ee63 | AAAAAAAAAAAAAAAAAAAAAA | 150000 | 0 | 2020-09-12 13:56:28 | 1 |
| 0000a9c933a0448faa633809bdc0e21c | AAAAAAAAAAAAAAAAAAAAAAAAAAA | 701 | 0 | 2020-09-12 17:44:19 | 0 |
| 00040b521f854c17a7311a576b432fe8 | AAAAAAAAAA | 3048 | 0 | 2020-09-12 12:44:57 | 0 |
+----------------------------------+-----------------------------+--------+-------------+--------------------+------+
so that all columns where bin is true (or 1) are at the top, followed by the rest of the rows sorted by ending
i have used ORDER BY bin DESC, ending but it takes a ridiculous amount of time to run since there is 50k rows
when I only use ORDER BY ending it does it within 2 seconds
(the result is limited to the first 1k too)
but as soon as I sort by bin (even alone) it can take up to a minute
so i assume sorting isn't the best option
@tepid cradle
tepid cradle
If sorting by bin is going to be a common enough use case, you might want to add an index on the column
Other than that, I don't see why sorting by bin and ending would take a shorter time than sorting by bin, I've never encountered this myself.
reef zodiac
i mean that sorting by ending
is taking a lot less time than sorting by bin
also, bin a boolean
so when i say sort by bin, I mean rows where bin is true should be at the top, otherwise sort by ending
tepid cradle
it could mean that you have an index on ending
You can create another combined index on bin desc and ending
reef zodiac
i'm not sure bin DESC, ending is what i want anyway
rows where bin is true should be at the top. Everything else should be sorted by ending
whats an index?
both ending and bin could have the same value though
the only thing that's guaranteed to be unique is the ID
tepid cradle
Index is a way of storing sorting information with a table. If you create an index on a column, the database stores the information for that column in a sorted manner. This speeds up sorting and filtering.
I'm not very good at explaining what it is, I just know what role it plays
reef zodiac
tepid cradle
It doesn't need to be unique for creating an index, that requirement is there for primary key
reef zodiac
the table is updated once a minute
with thousands of entries
it's a large database
tepid cradle
yes, which is why I said in the very beginning that if this is a frequent use case, then you can create an index
Also, that statement is not entirely true
reef zodiac
With all the processing that happens, it currently takes ~40s to update the database
so i don't really want to add any more time
tepid cradle
If you run update queries against an indexed column, the time saved in searching can often more than offset the time taken to update the index
With all the processing that happens, it currently takes ~40s to update the database
@reef zodiac These areupdatequeries orinsert intoqueries?
tepid cradle
Then your index might actually help. But only if you're searching for duplicates on any of these two columns
tepid cradle
Where is that screenshot from? A blanket statement like that is not indicative of a great source?
reef zodiac
w3schools
tepid cradle
OK, w3 has a habit of leaving out the nuances
Good for beginners, not great intermediate or advanced level
reef zodiac
after some thinking, it might be more time efficient to just separate it into 2 queries
tepid cradle
If you can make that work, then give it a shot
reef zodiac
it's going to be used in a api, so I'll just have separate endpoints
a union could work too right?
like SELECT * FROM auctions WHERE bin=1 UNION (SELECT * FROM auctions WHERE bin=0 ORDER BY ending)
tepid cradle
Union might not work because a union doesn't necessarily return the results in the order of the query
tepid cradle
then why were trying to order by bin earlier?
BTW, see this, union query results can be completely random in order
reef zodiac
not sure how to explain the use, but i think it should still work for me. theres some other filtering going on, and I want things with bin to always be returned first followed by ending soonest
so as long as it gets say the 20 most recent ending and all the bins I should be good
tepid cradle
OK
quartz moon
Hello, how can I reuse a mongoengine connection in different files so that I do not have to create an instance every time I make a query. Ty in advance! Any help is very welcome
cold fiber
hello can someone explain why either of these raises an error? it works when i input the argument directly but not if i do it like this.
cursor.execute(
"SELECT * FROM bank WHERE user_id=?",
(234567,)
)
cursor.execute(
"SELECT * FROM bank WHERE user_id=:user_id",
{"user_id": id}
)
im using postgresql btw
psycopg2
earnest parcel
does anyone know where how i can get some type of information on the latency using asyncpg?
Never mind I'll just make a query I guess
south cobalt
is this the best palce to ask about a snippit written in SQL i know this is a python discord but i don't have a similar resource for help with SQL
feels like most people in here stick to talking to sql via python not writing in sql directly
twilit marlin
most people here know sql so ask away @south cobalt
south cobalt
i wanna make a table in sql similar to how its done here MSSQL but idk what the things before and after the talbe is made are
like if they are standard practice or for this persons specific use case
i dont think i need a key
but as for like quoted stuff or ANSI stuff not sure if i should use that when creating my table either
tepid cradle
hello can someone explain why either of these raises an error? it works when i input the argument directly but not if i do it like this.
@cold fiber Psycopg2 uses%sfor placeholders and%(name)sfor named placeholders. So the statements should look like these:
cur.execute("select * from table where col = %s", (23,))
cur.execute("select * from table where first_name = %(first_name)s and surname = %(surname)s", {'first_name': 'foo', 'surname': 'bar'})
is this the best palce to ask about a snippit written in SQL i know this is a python discord but i don't have a similar resource for help with SQL
@south cobalt No harm in asking pure SQL questions in this channel. People often do
cold fiber
ah ok thanks!
tepid cradle
I don't know what those keywords are either, not that familiar with MS SQL. However, regarding your question, you can't create a table in "SQL". SQL is just a language you use to query databases. Which database do you want to use?
south cobalt
does this look right for creating a very basic table
/****** Object: Table [dbo].[FIC-100] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FIC-101](
[PV] [float] NOT NULL,
[OP] [float] NOT NULL,
[SP] [float] NOT NULL)
GO
SET ANSI_PADDING OFF
GO
oh i was sent a .sql script that creates a database and adds tables to it so what i would like is to create a .sql script that does that
tepid cradle
SET and GO are not parts of SQL itself. They are more MS SQL studio commands. So they will need to go if you are using anything else
You first need to have a database before you can run an SQL script that creates tables.
Do you have a database?
south cobalt
what i want to do is from my python file run this script to create a database
i do have one but i want to be creating a new one each time i need it
essentially when the program starts it should create the DB when it clsoes it should dump it
tepid cradle
That doesn't sound like a solution to any problem. The whole idea of a database is to store persistent data. If you are creating a new one every time, then you don't need a database at all
You can use SQLite for that kind of usage. But IDK why you'd want to use a database that way
with sqlite, you can actually create an in-memory database, so it wouldn't persist beyond the session at all
south cobalt
well incase its too much to store in memory
i think i generate like 50 datapoints every 5 or so seconds
tepid cradle
and how many seconds do you expect this to run?
south cobalt
dunno could be like 10min +
tepid cradle
Go here, read the sections in the following order - 11, 10, 9, 1, 2, 3
Unless you want to go through the entire tutorial, in which case, just go serially
south cobalt
i tend to run it around 10 min at a time but you could deff go longer if you want or just run continuously
tepid cradle
600 data points per minute, 6000 data points in 10 minutes, 36000 data points in an hour, that's nothing
south cobalt
hmmmm, well right now all my queries are written for MSSQL in pyodbc seems like it would be quite a pain to redo everything for sqlite
tepid cradle
that's 10 million data points, 38 MB
south cobalt
wow, i really thought it would use more memory
i was previosuly using numpy arrays for everything and switching to MSSQL sped things up incredibly
tepid cradle
That it will. I was just showing the relative memory usage, it's not a recommended way to store data
Querying will probably be faster with a database, can't be sure though, haven't compared
south cobalt
would rewriting all my queries in sql lite take long?
i have like ehh 25ish functions/quereies
tepid cradle
no. One primary difference I'm aware of is that select top 10 * from table becomes select * from table limit 10
tepid cradle
Other than that, unless you've used a lot of window functions, the syntax should mostly be same
reef zodiac
hey again, run into another problem
SELECT auctions.id,
auctions.item_name,
auctions.price,
auctions.seller,
auctions.ending,
auctions.bin,
auctions.bytes,
extra_value + p.latest_price AS total_value,
extra_value + p.latest_price - auctions.price AS profit
FROM auctions
LEFT JOIN (SELECT LatestPrice(item_name) AS latest_price, auctions.id FROM auctions) p ON p.id = auctions.id
WHERE extra_value + p.latest_price - 200000 > CalcTax(price) and bin = 0 LIMIT 0, 50;
``` using a query like this when I use bin=0 it taken ~2s however when i use bin=1 it takes upwards of 1m30sany reasoning?
tepid cradle
Maybe bin = 1 has a lot more items
Whtat is the meaning of Limit 0, 50? I'm not familiar with this
wait, which Db is this? Postgres asked me to use separate limit and offset clauses when I ran this. So I'm guessing this syntax is supported for that kind of query by some other Db
reef zodiac
its mariadb
similar to mysql
it's the same as limit 50 offest 0
roughly the same
bin has 5k more rows which is only like 15% more
CalcTax is also a custom function
torn sphinx
I'm Raji, I'm trying to do a API, in python flask and SQLAlchemy, I've made some models and I have a ID Sequence like this.
db.Column(db.Integer, db.Sequence("auto-seq", start=100000, increment=1))
I don't get the starting number in the correct order. I want my starting number to be 100000 but it startswith 1 instead.
Thank you for your kind help.
The increment works fine, it increments numbers right
tepid cradle
LatestPrice() is also a custom function?
what is the row count of the auctions table? Total row count
@reef zodiac
reef zodiac
60,241
tepid cradle
Then something is seriously wrong with your table
reef zodiac
what do you mean?
tepid cradle
I have a db on my Raspberry Pi with a table with some 11 million rows. I just tried selecting 300,000 rows from it, it took 1.9 seconds to fetch all the 300,000 records
reef zodiac
I'm doing this remotely, let me check whether the time includes the connection time
but that's what i mean
it takes 2 seconds to run when bin=0
but like 1.5mins when bin=1
LatestPrice makes 2 other select calls to another table so this is the reason that it's slower
but 2s is not a problem
it takes 1.6s to run the query on the machine
and to select everything it takes .04s
it still takes 1m 24s to run with bin=1
CREATE FUNCTION IF NOT EXISTS LatestPrice(i_name VARCHAR(255)) RETURNS FLOAT
RETURN (
SELECT price FROM price
INNER JOIN (SELECT item_name, MAX(time_checked) AS rec FROM price GROUP BY item_name)
p_tbl ON price.item_name = p_tbl.item_name AND time_checked = p_tbl.rec
WHERE price.item_name=i_name)
my price table has multiple prices for each item, each taken at different times. the function returns the latest price for an item
without the bin check, the majority of the returned values have bin=0
tepid cradle
I think the whole query could use some optimisation. For the above, could you try this and check if it runs faster:
select * from (
select price, item_name, rank() over (order by time_checked desc) as rk
from price
where item_name = i_name
) t1 where rk = 1
is rk an abbreviation of something?
tepid cradle
no, that's just the pseudonym I've given so that I can use it outside in the where clause
You can use anything, it's just like a variable name
reef zodiac
seems to be working a bit faster
the original is .007s yours is .001s
but i assume yours is rounded up
tepid cradle
might be. Basically your query is scanning the entire table for the max(time_checked), my query is first filtering for the item, then scanning only those for max of time_checked
reef zodiac
ah so mine returns the max time for every item, yours only returns the max for the specific item
makes sense
i will change this now but i doubt this is the problem
tepid cradle
exactly. You're taking max for all then filtering. I'm first filtering then taking the max for that
reef zodiac
but i'm really confused as to what could be
torn sphinx
I'm Raji, I'm trying to do a API, in python flask and SQLAlchemy, I've made some models and I have a ID Sequence like this.
db.Column(db.Integer, db.Sequence("auto-seq", start=100000, increment=1))
I don't get the starting number in the correct order. I want my starting number to be 100000 but it startswith 1 instead.
Thank you for your kind help.
@torn sphinx
tepid cradle
Even your first query seems like it could be optimised a bit. But because of the use of custom functions, and the lack of knowledge of the schema, I'm not able to fully understand
reef zodiac
AUCTIONS
+-------------+--------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------------------+-------------------------------+
| id | varchar(36) | NO | PRI | NULL | |
| item_name | varchar(255) | NO | | NULL | |
| price | float | NO | | NULL | |
| seller | varchar(36) | NO | | NULL | |
| extra_value | float | NO | | NULL | |
| ending | timestamp | NO | | current_timestamp() | on update current_timestamp() |
| bin | tinyint(1) | NO | MUL | NULL | |
| bytes | text | NO | | NULL | |
+-------------+--------------+------+-----+---------------------+-------------------------------+
PRICE
+--------------+--------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------------------+-------+
| item_name | varchar(255) | NO | PRI | NULL | |
| price | float | NO | | 0 | |
| time_checked | datetime | NO | PRI | current_timestamp() | |
+--------------+--------------+------+-----+---------------------+-------+
And can you tell me what you are trying to get out of this, so that I can think independently of your existing queries
reef zodiac
I'm trying to get the soonest expiring auctions OR auctions that are 'buy it now' (where bin is 1) that would potentially make a 200,000 profit (i keep track of the average prices and then calculate the worth of an object). I'm using a limit because it'll be used in a rest api that will be paginated so it can be loaded quicker
the actual query i'm using looks like this
I changed by query based on our last discussion but it didn't have much effect since it's always been the bin = 1 thing slowing it down
SELECT auctions.id,
item_name,
price,
seller,
UNIX_TIMESTAMP(ending),
bin,
bytes,
extra_value + p.latest_price AS total_value,
extra_value + p.latest_price - price AS profit
FROM auctions
LEFT JOIN (SELECT LatestPrice(item_name) AS latest_price, auctions.id FROM auctions) p ON p.id = auctions.id
WHERE extra_value + p.latest_price - 200000 > CalcTax(price) AND bin = 0
ORDER BY ending
LIMIT 0, 50
it would seem bin is a keyword
and so is bytes
would that affect it?
CalcTax just returns the price multiplied by some float depending on the size
it just a chain of if else statements
I changed the query so that i either only get buy it now auctions, or only get regular auctions
but ideally, it would return all the bin auctions first, and then all of the soonest auctions
it would seem that's the case
i mean it's taking 3s
hmm
that can't be the cause, right? @tepid cradle
tepid cradle
Going to sleep, it's quite late here. Will see in the morning.
reef zodiac
alright, no worries. you've been a massive help today
sage crater
Anyone here familiar with using Dataset?
worn nimbus
Hello, i have a doubt about SQLAlchamy. I'm working on a trello clone so let's say i have 2 models.
Lists (which contains an id and a title)
Tasks (which contains and id, list_id and a title)
Actually i have a query who joins the Tasks on the lists and gives the next output:
(Lists<id>, Tasks<id>)
(Lists<id>, Tasks<id>)
...
But i'd like it to give only one output in the next way:
(Lists<id>, (Tasks<id>, Tasks<id>, Tasks<id>, Tasks<id>, Tasks<id>))
Is it possible with SQLAlchemy? i remember using an aggregation function with raw sql queries back ago.
My query:
result = db.session.query(Lists, Tasks).\
join(Tasks, Tasks.list_id == list_id).\
filter(Lists.id == list_id)
Thanks in advance!
sinful condor
2 things to start I am using aiosqlite3 and I am making a discord bot leaderboard for a level system. My database looks like this
I am trying to make it ranked based on xp as well as I need it to only get the things that have /ctx.guild.id at the end
how would I do that
sinful condor
ping me if anyone responds
novel moat
i transformed a json into a data frame, but there is still a nested dict in one column. i want to have hours and average price in individual columns. You can see my try below. Whats wrong about that?
data_bitskins = get_price_data_for_items_on_sale() #new data in json format
dict_bitskins = data_bitskins.json()
#print(dict_bitskins)
del dict_bitskins['status']
dict_bitskins_reduced = dict_bitskins['data']['items']
#print(dict_bitskins_reduced)
dict_bitskins_reduced['hours'] = dict_bitskins_reduced['recent_sales_info']['hours']
dict_bitskins_reduced['average_price'] = dict_bitskins_reduced['recent_sales_info']['average_price']
df_bitskins = pd.DataFrame.from_dict(dict_bitskins_reduced)
TypeError: list indices must be integers or slices, not str
tepid cradle
how would I do that
@sinful condor I don't fully understand your question, but to calculate rank in SQL, you can doselect *, rank() over (order by xp desc) from...
This is called a window function
queen saffron
{'Domain_Name': 'GOOGLE.COM', 'Organization': 'Google LLC', 'Name': None, 'Country': 'US', 'Emails': ['abusecomplaints@markmonitor.com', 'whoisrequest@markmonitor.com'], 'Registrar': 'MarkMonitor, Inc.', 'Creation_date': '1997-09-15 04:00:00', 'Updated_date': '2019-09-09 15:39:04', 'Expiration_date': '2028-09-14 04:00:00', 'Creation_date_diff': 8399}``` Anyone have any suggestions on what the best format for time is with Mongo databases? Next im going to try and use jinja and flask to allow searching for ranges of datesI think i should get rid of the hour minute second, useless tbh
{'Domain_Name': 'GOOGLE.COM', 'Organization': 'Google LLC', 'Name': None, 'Country': 'US', 'Emails': ['abusecomplaints@markmonitor.com', 'whoisrequest@markmonitor.com'], 'Registrar': 'MarkMonitor, Inc.', 'Creation_date': '1997-09-15', 'Updated_date': '2019-09-09', 'Expiration_date': '2028-09-14', 'Creation_date_diff': 8399, 'HTTP': '{"url": "https://google.com", "title": "Google", "status_code": 200, "content_length": 12136}'}``` This is what they are all going to look like ... anyone with experience with mongo databases have any suggestions ? 🙂on aiosqlite, is it possible to connect to the database outside of an async function?
torn sphinx
I'm Raji, I'm trying to do a API, in python flask and SQLAlchemy, I've made some models and I have a ID Sequence like this.
db.Column(db.Integer, db.Sequence("auto-seq", start=100000, increment=1))
I don't get the starting number in the correct order. I want my starting number to be 100000 but it startswith 1 instead.
Thank you for your kind help.
mental quiver
What is the difference between:
-varchar(n)
-text
-nvarchar
In the context of SQLite.
@ me when answering, please.
south cobalt
is there a way to merge two tables like this with no overlap?
ie: Table 1 has colums A B C Table 2 has columns D E F
i want to take all the content of each table and output an array or something in the shape
A B C D E F
torn sphinx
Hello, there?
twilit marlin
general kenobi?
torn sphinx
kenobi?
I'm confused on how I should setup my asyncpg Postgres connection pooling with multiple Python processes
Should I create one pool, or a pool for each process?
torn sphinx
Hello?
fringe crater
hi
tepid cradle
@torn sphinx if you're not getting an answer, it's probably because
- Your question doesn't have sufficient details
- You are in the wrong channel
In this case, it's probably the second one. This channel is used more for SQL related discussion and people using ORM don't really visit here. So ask in a help channel or #web-development channel.
@south cobalt unless there's a common column which you can use to link the two tables, it's not really possible to join them. And that's not SQL, that's just plain data. How do you know what relates to what unless you have a common data point which tells you so.
@fringe crater Hello. Go ahead and ask your question, don't wait for people to respond. People will not hang around waiting for your question.
twilit marlin
@tepid cradle I think Deluzi was responding to the hello above them
tepid cradle
Oh, ok. Didn't realise.
fringe crater
actually i have a question
how do i delete all row from selected column in sqlite
this is only for rowid 2
how do i delete all values from all rowid
got it
tepid cradle
You can't 'delete rows from specific columns'. You can either delete the entire row, or update the values of specific columns to null
To delete all the rows, just leave out the where clause
mental quiver
I am having some issues with the SQLite3 module. I am attempting to execute the following:
ALTER TABLE new_table ADD COLUMN Table INT(255);
But I get the error:
sqlite3.OperationalError: near "Table": syntax error
Based on everything I have looked up, I still cannot figure out what I am doing wrong. Help? @ me when responding please.
twilit marlin
i believe Table might be a reserved keyword for sql
mental quiver
Goddamnit how did i not think of that
This is what happens when you code at 4 AM. Thanks very much lmfao
twilit marlin
I am not sure though
mental quiver
No, you were correct.
torn sphinx
I'm confused on how I should setup my asyncpg Postgres connection pooling with multiple Python processes
Should I create one pool, or a pool for each process?
sinful condor
heres my code
@commands.command()
async def rank(self, ctx, member: discord.Member=None):
member = member or ctx.author
userguild = f'{member.id}'
async with aiosqlite3.connect("C:/Users/runne/OneDrive/Desktop/dsbotaiosql/level.db") as db1:
async with db1.execute("SELECT level FROM level WHERE userguild = ?", (userguild,)) as cursor1:
async with aiosqlite3.connect("C:/Users/runne/OneDrive/Desktop/dsbotaiosql/level.db") as db2:
async with db2.execute("SELECT xp FROM level WHERE userguild = ?", (userguild,)) as cursor2:
exp = await cursor2.fetchone()
level = await cursor1.fetchone()
async with aiosqlite3.connect("C:/Users/runne/OneDrive/Desktop/dsbotaiosql/level.db") as db3:
async with db3.execute("SELECT userguild, RANK() OVER(ORDER BY xp DESC) FROM level") as is1:
ids = await is1.fetchall()
await ctx.send(f"{member.display_name}'s rank is {ids[0]} of {len(ids)}")
embed = discord.Embed(title=f"{member}'s Level and Rank Info", color=0xffffff)
embed.add_field(name="User Level Info:", value=f'Username: **{member.name + "#" + member.discriminator}**\nLevel: **{level[0]}**\nXp: **{exp[0]}**\nRank: **{ids[0]} of {len(ids)}**', inline=False)
embed.set_thumbnail(url=member.avatar_url)
await ctx.send(embed=embed, delete_after=30)```
I am trying to make a rank command for a level system I have but don't know what to put in place of {ids[0]} on the third to last row to get the users rank. Does anyone know what should go there to get the rank of member?holy fucking crist
sorry but oof
dont have a new db for every single item
thats what tables are for
sinful condor
it goes into the same table but ok ill fix it
brazen charm
well it clearly doesnt because you're opening 3 diffrent db files todo the job a table
also you're gonna need to put this in a paste bin cuz its very hard to read with it wrapping in discord
brazen charm
i mean atleast we're using context managers
rather than having 3 DB (which btw is a bad idea in itself because its slow af and also very hard to transfer down the line)
Use tables
you're already using it for each DB
but put those 3 tables into the 1 db
brazen charm
yeah i just notices
well new question
why do you reopen the db 3 times
and why is it in OneDrive 🤔
brazen charm
then lets move these context managers out so theyre not all nested because its pretty mesyy
async with xyz as cursor:
my_value = await cursor.fetchone()
async with xyz as cursor:
my_other_value = await cursor.fetchone()
that would be a much cleaner way of doing it
quaint tiger
async with db1.execute("SELECT level FROM level WHERE userguild = ?", (userguild,)) as cursor1:
async with aiosqlite3.connect("C:/Users/runne/OneDrive/Desktop/dsbotaiosql/level.db") as db2:
async with db2.execute("SELECT xp FROM level WHERE userguild = ?", (userguild,)) as cursor2:
Why not select level, xp... ?
sinful condor
ok
quartz moon
Hello! Is it the correct channel to ask something about mongoengine?
brazen charm
@quartz moon sure
quartz moon
Ok i'll prepare the question 🙂
sinful condor
@brazen charm whats next
brazen charm
right
now we can read through everything easily we can see some easy things to simplify it further
as patryk.tech pointed out
we have two essentially duplicate queries
sinful condor
ok how do I combine it
brazen charm
a great thing about sql is that we can select multiple columns from a table
SELECT (column1, column2) FROM table WHERE x = y
if we're using fetchone() it will then return either None (doesnt exist) or a tuple (column1_value, column2_value)
sinful condor
then doesn't that just make it more complicated
sinful condor
yeah but instead of just getting say level 1 its 1, 4
brazen charm
yeah thats fine?
sinful condor
and you have to seperate it from xp
brazen charm
thats just a simple slice manipulation
brazen charm
tuple[0] returns the first value tuple[1] returns the second etc...
quaint tiger
yeah but instead of just getting say level 1 its 1, 4
@sinful condor It's actually better for performance and standard to get as much data as possible in as little queries as possible.
sinful condor
oh ok
quartz moon
I have this file in which I register a connection to my database: https://hastebin.com/duhanutoju.py. How can I use that connection in different files so that I do not have to create a new connection in other files before I make a query.
brazen charm
@quartz moon i wouldnt use mongoengine, use pymongo which is the official wrapper by Mongo themselves
sinful condor
I have this but it says row value misused https://sourceb.in/c18b13a811
brazen charm
Its alot cleaner than engine
quartz moon
but if I'm right, It doesn't allow to have class schemas which is what I have
quartz moon
https://hastebin.com/sakidudine.py this is one of my schemas
brazen charm
well for sharding the connection just pass it via functions
you could us a global var but i wouldnt recommend it
quartz moon
Shall I use pymongo instead, I think that I'm making it too complicated
brazen charm
I mean you dont have to
but i think your file layout is probably making it harder for you
dense steeple
I am getting this error when I try to run my program ```py
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ConfigurationError: The DNS operation timed out after 21.60023546218872 seconds
@sinful condor what was the actual traceback
sinful condor
File "C:\Users\runne\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\bot.py", line 903, in invoke
await ctx.command.invoke(ctx)
File "C:\Users\runne\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 855, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\runne\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 94, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: row value misused```Traceback (most recent call last):
File "C:\Users\runne\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\ext\commands\core.py", line 85, in wrapped
ret = await coro(*args, **kwargs)
File "C:\Users\runne\OneDrive\Desktop\dsbotaiosql\cogs\level.py", line 119, in rank
async with db.execute("SELECT (level, xp) FROM level WHERE userguild = ?", (userguild,)) as cursor:
File "C:\Users\runne\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiosqlite3\utils.py", line 153, in __aenter__
resp = yield from self._coro
File "C:\Users\runne\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiosqlite3\connection.py", line 126, in _execute
future = yield from self._loop.run_in_executor(
File "C:\Users\runne\AppData\Local\Programs\Python\Python38-32\lib\concurrent\futures\thread.py", line 57, in run
result = self.fn(*self.args, **self.kwargs)
sqlite3.OperationalError: row value misused```ah nice
sinful condor
but theres still something
dense steeple
sinful condor
in the embed on Rank how do I tell it to do rank out of totalranks
embed.add_field(name="User Level Info:", value=f'Username: **{member.name + "#" + member.discriminator}**\nLevel: **{levelxp[0]}**\nXp: **{levelxp[1]}**\nRank: **{ids[0]} of {len(ids)}**', inline=False) on this one
brazen charm
whats your code now?
sinful condor
@brazen charm https://sourceb.in/64569dbdd8
brazen charm
ill have to look in a few mins, gotta fix washing machine
sinful condor
ok
torn sphinx
anyone know an async pymongo module
dense obsidian
Yeah, that.
quaint tiger
create tables users (id, username, email, password, extra data);
dense obsidian
👋🏻 If you're NYC based I'm not at all surprised.
glass gorge
xD
quaint tiger
(with appropriate types, of course)
glass gorge
i see thanks
erm idk if this is the right channel
storing secret keys in env variables, how does one do that
quaint tiger
Probably more of a web-dev or tools-and-devops q? but since you already asked.... depends how you deploy, and your OS
glass gorge
im reading an article now
well
do
but like... I have more than one app
same droplet
flask
linux ubuntu droplet
dense obsidian
Yeah, there's a bunch of tools that do it, but mostly, you can read the expected secrets from os.environ in the most naive solution
quaint tiger
On linux, SECRET=foo flask run app
I store them in gitlab, and use gitlab-ci to deploy (with a locally install gitlab-runner)
There are things like hashicorp vault (I think it was)....
How do you start your app(s)?
quaint tiger
glass gorge
thanks
how would i test this locally though
@quaint tiger
and i have multiple apps as well on then droplet
quaint tiger
and i have multiple apps as well on then droplet
@glass gorge do they all have their own.servicefiles?
You can add a EnvironmentFile line to each one that needs env settings, and point each to its own .env file (or all to the same one).
It's hard to say without seeing your project(s)
quaint tiger
You can replicate the environment and try it, but really, maybe it's better to just do it in production.
If you can use 2 servers, one for staging one for prod is ideal.
I tried running staging and prod on the same box, and it .... does not work well with docker lol
glass gorge
yeah im using docker as well
i dont understand, so then you're manually making these changes before you push
quaint tiger
I keep my environment variables in gitlab....
I have gitlab-runner on my server that builds my containers on every push
And docker-compose files that are configured to use them.
sinful condor
How would I get what rank something is using this code it already has rank I just dont know how to use it.
potent jackal
Hi
La verdad hablo español, no sé si les moleste
De ser así lo traduzco. Quisiera saber si existe un programa que me saque todo el proceso aplicado a un número siguiendo sistemáticamente la conjetura de collatz
near cradle
@potent jackal I'm afraid we can't help you in Spanish here. You must speak English in this community.
ingles es obligatorio en Python Discord, lo siento.
potent jackal
Yeah I thought that could happen. So I want to know if someone knows any program written in python about the collatz conjecture
Srry if I make mistakes in the grammar or something like that
The problem is that I need the process for each number of the sequence
near cradle
Srry if I make mistakes in the grammar or something like that
@potent jackal
don't worry about this here, most of this server has English as a second language, even me.
Afraid I can't help you with the collatz conjecture problem, but hopefully someone around here can.
potent jackal
Thank you so much ❤️ :3
I hope that
harsh pulsar
@potent jackal maybe #algos-and-data-structs can help with the collatz conjecture
potent jackal
Ohhh, srry I didn't see that chanel XD
quartz moon
Hello I have a question, Am I right if I say that mongoengine internally keeps the Pymongo.MongoClient connection when you use connect()?
tepid cradle
@sinful condor You need to combine these two queries:
async with db.execute("SELECT level, xp FROM level WHERE userguild = ?", (userguild,)) as cursor:
levelxp = await cursor.fetchone()
async with db.execute("SELECT userguild, RANK() OVER(ORDER BY xp DESC) FROM level") as is1:
ids = await is1.fetchall()
like this
query = """select * from (
SELECT level, xp, userguild, rank() over (order by xp desc) as user_rank
FROM level
) t1 WHERE userguild = ?
"""
async with db.execute(query, (userguild,)) as cursor:
levelxp = await cursor.fetchone()
What is happening here is that in the internal query (sub-query) you calculate the rank for all users. Then in the main query, you just filter for the user you want.
If you filter for the user in the subquery, the filter will happen first and then the rank will be assigned. Therefore the rank will always be 1. So you want to assign the rank first, and the use that as a sub-query to filter for the required user.
Also note that for this query to work as expected, userguild should be unique in your table.
torn sphinx
well, someone redirected me to #web-development but it was #databases
@tepid cradle you're wrong I was in the correct channel, you redirected me to wrong one
tepid cradle
Lol.. I just explained why you were not getting an answer. As I said, people who use ORM don't frequent this channel.
torn sphinx
well, I didn't even got my answer in #web-development
and they again redirecting to #databases
tepid cradle
Better post on stackoverflow.com then as the life of a question is very short on this forum
visual viper
halo, i just got this error py Foreign key associated with column 'rule.id_treshold' could not find table 'mintreshold' with which to generate a foreign key to target column 'id' when executing create_all()
and this is my model ```py class MinTreshold(db.Model):
id = db.Column(db.Integer, primary_key=True)
min_sup = db.Column(db.Float, nullable=False)
min_conf = db.Column(db.Float, nullable=False)
lift = db.Column(db.Float, nullable=False)
rules = db.relationship('Rule', backref='mintreshold', lazy=True, cascade="all, delete-orphan")
class Rule(db.Model):
id = db.Column(db.Integer, primary_key=True)
rule = db.Column(db.String(300),nullable=False)
support = db.Column(db.Float, nullable=False)
confidence = db.Column(db.Float, nullable=False)
lift = db.Column(db.Float, nullable=False)
treshold_id = db.Column(db.Integer, db.ForeignKey('mintreshold.id'), nullable=False) ```
tepid cradle
Try db.ForeignKey(MinThreshold)
I'm just guessing by the way, I have no clue about ORMs
@visual viper
visual viper
it throws Column-bound argument expected
tepid cradle
Ok