Which will return 1 or 0, that it exists or does not exist
#databases
vale lodge
dusty helm
That's a pretty complete explanation, thank's a lot
vale lodge
Yes, you can just try to grab a row. Unless you're using that value though the exists query is more performant.
placid flicker
How are images usually stored in a db? Is it commonly stored as a path?
vale lodge
Normally you'll store images outside the DB and just store references to them in the DB. Although that's not always the case.
placid flicker
@vale lodge Thanks for the resource, makes sense to store paths. So I have all my images on Server A (typical Ubuntu server) and I want to access them on Server B which contains a web server (Flask). However, Server A is simply a storage server and I cannot create databases on it. Is it possible to store image paths from Server A into a database on Server B?
vale lodge
Yeah sure, just store the relative paths. Is Server A going to be serving your static files? For example with Apache or Nginx?
placid flicker
Unfortunately server A does not have a web server like nginx or apache
vale lodge
Typically that's your setup and you'll store the relative path (what you'll use for your URL) in the DB. If you have multiple sources you can store the root path too.
placid flicker
It simply serves as storage
vale lodge
How will the files be served then?
placid flicker
Yeah that's what I'm tasked with unfortunately. Idk how they expect me to serve images without having some kind of http server.
vale lodge
Yeah that's kinda necessary. A typical setup would be to use Server A as a proxy layer. So it runs Nginx which will serve static files from disk. Nginx then also has a forwarding rule to send traffic that is not static files to the backend server B.
This has a few benefits, having a proxy in front of your server will improve performance and provide you with a caching layer. It also has a security advantage that you can lock down your flask server to only accept traffic from Nginx.
Note here that Nginx could be any of many choices, it's just a common example.
placid flicker
Yeah I'm gonna have to explain to the superiors that having something like nginx is not really a security risk
vale lodge
Having a proxy layer provides better security.
placid flicker
Another question I had was in order to access Server A (particularly SSH) we have to use a VPN. However, if I do end up setting up Nginx to serve images to Flask on Server B to host the relative paths in a db, will that require VPN access too?
vale lodge
One easy way to explain it, your application server has a lot of data. It's best to keep that server firewalled off from the public internet. A proxy layer acts as a bastion host.
Networking is hard to say without more details on your setup. But typically your servers are colocated (in a data center or the cloud) and don't need any special software to connect to each other. you only need the vpn to connect from your local machine since you're outside the network
But for this to work one of the servers has to be opened up on port 80/443 without a VPN
unless your service is only available inside a VPN in which case security is probably a moot point.
placid flicker
Yeah so Server A can only be accessed remotely if using this specific VPN or if we're physically connected on the same network.
vale lodge
Is your service that you're building going to be available publicly?
placid flicker
It will be available to the employees only
vale lodge
Ok so it's all within the VPN more than likely.
placid flicker
So in theory I can run Server B on that same network and people can just access my Flask app using the VPN?
vale lodge
Yes
But even with security as a non-issue nginx can help you with your static files and it is a better choice for performance. https://flask.palletsprojects.com/en/1.1.x/deploying/uwsgi/
placid flicker
@vale lodge Thank you so much for the help!!
vale lodge
No problem.
fiery cypress
how do you guys do a quicksort with the pivot as the left most element?
also what happens when the pivot = an element
grim pier
Is this correct syntax? python await cursor.execute("UPDATE SteamID = %s WHERE VerificationID = %s",(steamid,verifyint))
@vale lodge You still there dude? ๐
tepid cradle
@grim pier mention the name of the library, otherwise we can't tell if the syntax is correct or not with certainty.
Although, here the syntax is definitely not correct. It's update table_name set column = value where other_column = other_value
You're assigning the value directly to the table
how do you guys do a quicksort with the pivot as the left most element?
@fiery cypress are you asking about database? If yes, then mentioning the name of the database would be helpful. Also, a little more detail about your question would also help
wraith rampart
I have written this code:
db = sqlite3.connect("db.sqlite")
cursor = db.cursor()
cursor.execute(f"SELECT createChannel from db WHERE guild_id = {ctx.guild.id}")
result = cursor.fetchone()
print(result)
if result == "TestCreateChannel":
print("works")
However, the bot prints ('TestCreateChannel',) and not works so how exactly would I put this into an if statement?
proven arrow
Because in your if statement your comparing against a string. The value of result is a tuple, and not a string, so it will always fail.
What are you trying to check exactly? If the result exists, or the data stored inside?
wraith rampart
I am trying to check the data stored inside
proven arrow
Its a tuple, so you can access the first item by the index. For example, if you were to do value = result[0] then it would return you "TestCreateChannel" as a string type.
Then you can make the comparison against this value.
wraith rampart
ohh yeah i completely forgot about that lol i used that in the other part of my code
thanks
harsh pulsar
@wraith rampart use query params, not string interpolation:
cursor.execute("SELECT createChannel from db WHERE guild_id = ?", [ctx.guild.id])
shell parrot
Hi I have a large time series data, I'm trying the detect positions of of missing in python. anyone have knowledge?
wraith rampart
ok
hasty hinge
What are the differences between MySQL.connector, AioMySQL and PyMySQL?
blazing valve
hi
cedar violet
hi yall, the pandas query function is giving me a lotta grief
i am trying to get an 'or' operator working '|'
here's what ive got - https://pastebin.pl/view/0134e30c
if i replace day with '22/03/2019' it'll work. but the whole point of the code is to iterate thru all days
error msg: pandas.core.computation.ops.UndefinedVariableError: name 'day' is not defined
wraith rampart
hello, i seem to be getting a syntax error on my code but i dont know what i have done wrong
if result == "None":
val = ("on")
sql_statement = "UPDATE db SET createChannel = ?"
cursor.execute(sql_statement, (val)
db.commit()
cursor.close()
db.close()
print("createChannel toggled on")
return
error:
db.commit()
^
SyntaxError: invalid syntax
also, this works, but has no or (|) ofcourse: print(df.loc[df['DATE STARTED'] == day])
date formats tried are 2020/02/23 and 19-02-2020. both sorta work when input carefully, but not as iterated variables
torn sphinx
hello, i seem to be getting a syntax error on my code but i dont know what i have done wrong
if result == "None": val = ("on") sql_statement = "UPDATE db SET createChannel = ?" cursor.execute(sql_statement, (val) db.commit() cursor.close() db.close() print("createChannel toggled on") returnerror:
db.commit() ^ SyntaxError: invalid syntax
@wraith rampart what module are u using?
torn sphinx
does someone know how to store invites in a database?
tepid cradle
Hi I have a large time series data, I'm trying the detect positions of of missing in python. anyone have knowledge?
@shell parrot First, this is not a database question, so it doesn't belong here.
second, just calculate the difference from previous row. If it's daily data and the difference is greater than 1, then you have missing data. Change the numbers according to what frequency you have
does someone know how to store invites in a database?
@torn sphinx Invites is not a data type.
It's either a number or an alphanumeric string. It will be stored just like any other data.
@cedar violet Pandas is not a database, therefore this is the wrong channel. Ask in help channels
torn sphinx
@torn sphinx Invites is not a data type.
It's either a number or an alphanumeric string. It will be stored just like any other data.
@tepid cradle so you cant store invite links in databases
tepid cradle
@torn sphinx You can store any data in a database, it doesn't make a difference if it is an invite link. Data is just data
torn sphinx
good
tepid cradle
For a database, invite link, shirt size, and the name of the POTUS are all just strings
torn sphinx
is there a python to database converter
woven finch
py = r'discord.gg/python'
?
torn sphinx
@bot.command()
`async def buy(ctx, str: amount):`
USER_NAME = str(ctx.message.author)
USER_ID = str(ctx.message.author.id)
INV = await ctx.channel.create_invite(max_uses=amount)
SQL.execute('create table if not exists Accounts("Num" integer primary key autoincrement,"user_name" text, "user_id" integer not null, "balance" real)')
result_inv = SQL.fetchone()
SQL.execute(f'select balance from Accounts where user_id="{USER_ID}"')
result_userbal = SQL.fetchone()
if amount > int(result_userbal[0]):
await ctx.send(f"{ctx.message.author.mention} You cant afford this")
return
SQL.execute('update Accounts set balance = balance - ? where user_id = ?', (amount, USER_ID))
db.commit()
dbb.commit()
await ctx.send(f"Done")```
```Traceback (most recent call last):
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 903, in invoke
await ctx.command.invoke(ctx)
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/ext/commands/core.py", line 855, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/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: TypeError: '>' not supported between instances of 'str' and 'int'```@torn sphinx it looks like amount is a string. discord commands don't automatically convert types for you, it's always sent as a string. also you should use quey params in the first select, like you do in the update
harsh pulsar
in the update query you use ? placeholders. but in the select query you use f-string interpolation
you should use ? placeholders in both
also you need to write amount = int(amount) or something like that
normal grove
this is mongo db
and I want to remove the Ad: "ad"
idk how
I want to keep the rest of the data but each time I use $unset it removes the whiole data
adc.update_one({"_id": "Weekly"}, {"$unset": {dotw: {time: ""}}})
dotw is thursday and time is 10:00
but it deletes everything for thursday
delicate fieldBOT
Hey @torn sphinx!
torn sphinx
!code-blocks
Traceback (most recent call last):
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
ret = await coro(*args, **kwargs)
File "/Users/family/Desktop/Pluss/main.py", line 34, in buy
SQLL.execute('insert into Links(inv) values(?)', str(INV))
sqlite3.OperationalError: no such table: Links
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 903, in invoke
await ctx.command.invoke(ctx)
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/ext/commands/core.py", line 855, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/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: no such table: Links```@bot.command()
async def buy(ctx, amount: int):
USER_NAME = str(ctx.message.author)
USER_ID = str(ctx.message.author.id)
INV = await ctx.channel.create_invite(max_uses=amount)
SQL.execute('create table if not exists Accounts("Num" integer primary key autoincrement,"user_name" text, "user_id" integer not null, "balance" real)')
result_inv = SQL.fetchone()
SQLL.execute('insert into Links(inv) values(?)', str(INV))
SQL.execute(f'select balance from Accounts where user_id="{USER_ID}"')
result_userbal = SQL.fetchone()
if amount > int(result_userbal[0]):
await ctx.send(f"{ctx.message.author.mention} You cant afford this")
return
SQL.execute('update Accounts set balance = balance - ? where user_id = ?', (amount, USER_ID))
bal_db.commit()
invites_db.commit()
await ctx.send(f"Done")```can someone help with a sql query?
select extract(month from tstamp) as mon, extract(year from tstamp) as yyyy, count(number)
FROM table
WHERE mon != 8 and yyyy != 2020
GROUP BY 1,2
ORDER BY 2,1```it's saying it cant find the name 'mon' in the WHERE clause
grizzled dagger
Hey I've asked this question so many in the past but noone/never got any suitable answer for it. My question was, Why I am always fetching old data from my MySQL database I was using pymysql previously but later on I have switched to aiomysql5 with pool and autocommit being true.
Yes, I have check if my data is updated or not in the database. Coz If I restart the program it fetches the updated data but after few more updates/inserts to database program refuses to fetch them instead fetch old database.
torn sphinx
are you sure autocommit is true @grizzled dagger ?
and if you share code is better so we can see
also you can also manually make a commit
for example by await cursor.execute("COMMIT;")
torn sphinx
ok i see it looks fine, can you try add this after you fetch?
await cur.execute("COMMIT;")
make sure you are remove the return statement
@grizzled dagger
tired granite
Hey there, I'm about to start building a database, but I failed to understand how important the design of that database would be until now. Does anyone have good resources for wisely designing a database so I don't have to built it more than once lol?? Any help would be great, I'll be googling on my own in the meantime.
spark ravine
Always use an autoincrementing primary key. The rest of my tips are just experience. You'll never get the perfect db design by first deployment.
proven arrow
Also try and use the correct data types, and try to be abstract. Dont just store everything in a single table. For example, if you were modelling a online store, then payment details, and order details would be in different tables.
bold geode
Mild pymysql issue. I'm trying to use ' in a query.
I understand it must be escaped in sql. If I manually send the backslash, pymysql adds a second, so the db receives \\' resulting in an error. If I leave pymysql to escape it, it doesn't. What do I do?
harsh pulsar
@bold geode can you show your code as it currently exists
hasty hinge
Why in MySQL (or at least in MySQL-connector-python) using cursor.execute(query, (arg)) fails, but cursor.execute(query, (arg, )) works?
I just have 1 arg in my query, so I don't understand why it doesn't works if I remove the comma and the space
harsh pulsar
@hasty hinge (arg) in python is just arg. but (arg,) is a length-1 tuple containing arg
you can write [arg] instead of (arg,) if you prefer
hasty hinge
Ohh, I understand, thanks then, I guess I will use [] when making 1 arg queries.
torn sphinx
hey there, graphql with python? anybody done that?
rancid oasis
I am looking for some help. I am having trouble with an sqlite function that I have.
self.lstList1.delete(0,'end')
con = sqlite3.connect('db_groceryList.db')
with con:
cursor = con.cursor()
cursor.execute("""SELECT COUNT(*) FROM tbl_foodList""")
count = cursor.fetchone()[0]
i = 0
while i < count:
cursor.execute("""SELECT food_name FROM tbl_foodList""")
var_list = cursor.fetchall()[i]
for item in var_list:
self.lstList1.insert(0, str(item))
i+=i
con.close()```Whenever I run it with an empty database, it works fine and the program starts up. When there are entries in the database, the program runs, but none of my widgets show up
Actually, the window doesn't show up at all. I all that happens is the IDLE comes up saying the program started
okay I just figured out that it was i+=i... second i should be 1 heh heh...
tepid cradle
it's saying it cant find the name 'mon' in the WHERE clause
@torn sphinx you can't use assigned names in the group by clause. You'll have to repeat the calculation you did in select or use it as a sub-query
select extract(month from date) as mon, count(*)
from table_name
group by extract(month from date)
Like this
torn sphinx
ohhhh
i was able to solve it but i never knew that you cant use aliases in a groupby. thank you.
tepid cradle
You also cannot use it in where. Not just group by
torn sphinx
what about order by
tepid cradle
Nothing, you can't use it in the same query
torn sphinx
ah okay
tepid cradle
Of you want to use it, you'll have to make it as a sub-query
torn sphinx
is it only psql or all databases? i think ive used aliases before (in the where/groupby/orderby)..
or maybe im just thinking that i have used aliases before
tepid cradle
select * from (
select extract(month from date) as mon, *
from table1
) t1 where mon = 1
Like this
tepid cradle
is it only psql or all databases? i think ive used aliases before (in the where/groupby/orderby)..
@torn sphinx not entirely sure about this. My experience with Dbs other than pgsql is very limited
torn sphinx
i think it might be a psql-only thing. however im not 100% sure
tepid cradle
Yes, apparently it's possible in MySQL
https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html
It's disallowed in standard SQL, and Postgres is more adherent to SQL standards than MySQL, so it makes sense that Postgres won't support it.
torn sphinx
i see, thanks
grim pier
Anyone any decent with SQL? im getting await cursor.execute("UPDATE users SET DiscordID = '%s' WHERE PlayerName = %s",discordid,playername) from this python await cursor.execute("UPDATE users SET DiscordID = '%s' WHERE PlayerName = %s",discordid,playername)
shell ocean
you posted the same code twice
but anyway I'm going to guess that you need [discordid, playername] as a single second argument
torn sphinx
^
tepid cradle
No, the syntax is correct. It's the quotes around the first %s which is wrong
torn sphinx
some libs might require a tuple tho
iirc that syntax doesnt work in sqlite. does it?
grim pier
File "C:/Users/Jorda/PycharmProjects/BanditBOT-v2.0/BanditBot.py", line 618, in post_raid_alarm
await cursor.execute("UPDATE users SET DiscordID = '%s' WHERE PlayerName = %s",discordid,playername)
TypeError: execute() takes from 2 to 3 positional arguments but 4 were given``` oh sorry my bad heres the errorThe await tells that it's something like asyncpg, in which case the syntax is correct
tepid cradle
or that ^
tepid cradle
Oh, right
But you still need to remove the quotes around the %s
the library will put the quotes where necessary
grim pier
@shell ocean execute() takes from 2 to 3 positional arguments but 4 were given
grim pier
@tepid cradle Remove the quotes you say?
grim pier
@torn sphinx now im getting (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '10007\\''' WHERE PlayerName = ''Poochington''' at line 1") [
torn sphinx
send new code
grim pier
if "SB_" in line:
discordid = line.split()[-1][3:]
db = await connect_to_sql_db()
async with db.cursor() as cursor:
await cursor.execute("UPDATE users SET DiscordID = '%s' WHERE PlayerName = '%s'",(discordid,playername))```oh take them out? ๐
torn sphinx
mhm
grim pier
Seems to have fixed it ๐
torn sphinx
grim pier
@torn sphinx It runs through fine and prints as it should but it isnt updating the DB, weird
torn sphinx
you most likely need to commit()
it'll be await cursor.commit() or await db.commit()
grim pier
db = await connect_to_sql_db()
async with db.cursor() as cursor:
await cursor.execute("UPDATE users SET DiscordID = %s WHERE PlayerName = %s",(discordid,playername))
log(green(f"Charecter: {playername} has been linked to DiscordID: {discordid}. Updating SQL"))
embed = discord.Embed(title="Account linked!", color=0xff0000)
embed.set_thumbnail(url=f"http://www.scumbandit.com/weapons/Unknown.png")
embed.add_field(name="Your account has been linked to your Discord.",value="**You will now be able to receive alerts straight to your discord!**", inline=False)
embed.set_footer(text=f"SCUM Bandit Bot v{version}")
await db.commit()``` Im commiting it already ๐this is how it prints Charecter: Poochington has been linked to DiscordID: 286227680381239296'. Updating SQL
@torn sphinx any ideas what it could be? ๐ค
torn sphinx
nope ;-;
hasty hinge
Why is my MySQL adding quotes to my query with REGEXP?
1064 (42000): You have an error in your sql syntax check the manual that corresponds to your mysql server version for the right syntax to use near ''peliculas' WHERE title REGEXP '(?i).*'animales'.*'' at line 1```There shouldn't be any quotes in animales
tabla = "peliculas"
nombre = "animales"
query = "SELECT * FROM %s WHERE title REGEXP '(?i).*%s.*';"
cursor.execute(query, (tabla, nombre))
^ That is my code.
patent glen
you can't do it that wa
y
best way is
tabla = "peliculas"
nombre = "animales"
regex = f'(?i).*{nombre}.*'
query = f"SELECT * FROM {tabla} WHERE title REGEXP %s;"
cursor.execute(query, (regex,))```
I know it's generally considered bad to build a query with string formatting, but that's the only way to do dynamically selected tables [which is an unusual thing to want] - and building the regex is fine.tbh i'm surprised it even tried to put 'animales' in at all - sqlite wouldn't have
@hasty hinge
tepid cradle
I discovered this very interesting query which allows you to sum the values of any column over the last 4 rows.
It goes like this:
SELECT date, value,
sum(value) over (order by date rows between 3 preceding and current row)
FROM table1
Not a question, just sharing because it was interesting
harsh pulsar
i cant imagine thats supported by all databases
torn sphinx
SQL = bal_db.cursor()
SQLL = invites_db.cursor()
START_BALANCE = 0.0
C_NAME = "Coins"
SEARCH = search_db.cursor
c = test.cursor()
@bot.command()
async def buy(ctx, amount: int):
USER_NAME = str(ctx.message.author)
USER_ID = str(ctx.message.author.id)
INV = await ctx.channel.create_invite(max_uses=amount)
SQL.execute('create table if not exists Accounts("Num" integer primary key autoincrement,"user_name" text, "user_id" integer not null, "balance" real)')
result_inv = SQL.fetchone()
SQLL.execute('insert into (inv) values(?)', (str(INV),))
SQL.execute('select balance from Accounts where user_id="{USER_ID}"')
result_userbal = SQL.fetchone()
if amount > int(result_userbal[0]):
await ctx.send("{ctx.message.author.mention} You cant afford this")
return
SQL.execute('update Accounts set balance = balance - ? where user_id = ?', (amount, USER_ID))
bal_db.commit()
invites_db.commit()
await ctx.send("Done")```Traceback (most recent call last):
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 903, in invoke
await ctx.command.invoke(ctx)
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/ext/commands/core.py", line 855, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/ext/commands/core.py", line 94, in wrapped
raise CommandInvokeError(exc) from exc
iMac-van-Family:Pluss family$ /usr/local/bin/python3 /Users/family/Desktop/Pluss/main.py
Logged in as: Humans+ Sqlite
-----------
Ignoring exception in command buy:
Traceback (most recent call last):
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/ext/commands/core.py", line 85, in wrapped
ret = await coro(*args, **kwargs)
File "/Users/family/Desktop/Pluss/main.py", line 45, in buy
SQLL.execute('insert into (inv) values(?)', (str(INV),))
sqlite3.OperationalError: near "(": syntax error
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/ext/commands/bot.py", line 903, in invoke
await ctx.command.invoke(ctx)
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/discord/ext/commands/core.py", line 855, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/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: near "(": syntax error```
hazy mango
Remove the () around inv @torn sphinx
torn sphinx
ok
which line @hazy mango
SQLL.execute('insert into (inv) values(?)', (str(INV),))
hazy mango
Yes
hazy mango
Anywhere you have () around the table name is wrong
torn sphinx
ok
radiant prism
If your table is named "(inv)", rename it to just inv
torn sphinx
what
radiant prism
You read me
torn sphinx
SQLL.execute('create table if not exists Links("Link" integer primary key autoincrement,"inv")')
this is my table
@radiant prism
is my table ok?
hazy mango
A table name can't have () in it
The actual table
So you do SELECT FROM table not SELECT FROM (table)
Etc.
bold geode
Kinda vanished after my earlier question, oops.
Mild pymysql issue. I'm trying to use ' in a query.
I understand it must be escaped in sql. If I manually send the backslash, pymysql adds a second, so the db receives \\' resulting in an error. If I leave pymysql to escape it, it doesn't. What do I do?
await cur.execute(f'insert into {table} {columns[:-1]} values {values[:-1]}')```
I'm aware of issues that can occur with injection, but I need to solve this issue first. This is in a helper function, and `values`/`columns` can contain `'` which need to be escaped with `\` but I can't seem to get that working..I'm not able to escape the ' at all? In normal sql queries it's escaped with \
harsh pulsar
col_spec = ', '.join(f'"{c}"' for c in columns[:-1])
val_spec = ', '.join(['?'] * len(columns))
query = f'insert into "{table}" ({col_spec}) values ({val_spec})'
await cur.execute(query, params)
why do you have column names with apostrophes in them
why do you have dynamically constructed table and column names
obviously use the right placeholder for your database library, if not ?
bold geode
Sorry the column name won't contain ' but the values definitely will
harsh pulsar
oh.
use query parameters
look up the correct syntax in your particular database library
some use ?, some use %s, some use $1 $2 $3 ...
cur.execute('insert into mytable (x, y) values (?, ?)', [5.5, -5.5])
this inserts 5.5 into x and -5.5 into y
the database library will handle all the escaping and quoting for you
torn sphinx
@bot.command()
async def buy(ctx, amount: int):
USER_NAME = str(ctx.message.author)
USER_ID = str(ctx.message.author.id)
INV = await ctx.channel.create_invite(max_uses=amount)
SQL.execute('create table if not exists Accounts("Num" integer primary key autoincrement,"user_name" text, "user_id" integer not null, "balance" real)')
SQLL.execute('create table if not exists Links("inv" integer primary key autoincrement,""inv")')
result_inv = SQLL.fetchone()
SQLL.execute('insert into inv values(?)', (str(INV),))
SQL.execute('select balance from Accounts where user_id="{USER_ID}"')
result_userbal = SQL.fetchone()
if amount > int(result_userbal[0]):
await ctx.send("{ctx.message.author.mention} You cant afford this")
return
SQL.execute('update Accounts set balance = balance - ? where user_id = ?', (amount, USER_ID))
bal_db.commit()
invites_db.commit()
await ctx.send("Done")```will this store invites in my database?
clear reef
I cant know for sure without knowing what your database looks like @torn sphinx
torn sphinx
sqlite3
torn sphinx
huhh
dusky siren
They mean what are the columns called, what variable types do they store, etc
clear reef
^
dusky siren
For example,
Column 1 - Invites: Number
Column 2 - UserID: String
Column 3 - Currency: Number
torn sphinx
this is a command to store invites in database
clear reef
But
Your database
What columns
Does it have
I need to know if the columns you're using is valid
torn sphinx
SQL = bal_db.cursor()
SQLL = invites_db.cursor()
START_BALANCE = 0.0
C_NAME = "Coins"
SEARCH = search_db.cursor```u mean this?
harsh pulsar
thats not what cursors are for
its not how theyre meant to be used
and things break mysteriously and randomly when you do it
bold geode
Cursors aren't even supported by most sql software but pep standards encourage them, right?
harsh pulsar
yes
its purely an abstraction to give the user more control over how data is transferred from the database back to python
they dont even really make sense outside of select queries
steady epoch
Sqlite has cursor
harsh pulsar
sqlite3 itself i dont think supports CURSOR
brazen charm
sqlite3 uses cursor factories ye
steady epoch
Yup
harsh pulsar
in sql itself? or in the C api?
brazen charm
Im not sure
i imagine sqlite itself because it supports prepared factories directly
harsh pulsar
thats not what im talking about, that's in the python API
im talking about in the sql language itself or in the C api
it appears that the latter at least exists for virtual tables. its not easy to search the sqlite docs though
brazen charm
they're pretty plain text lul
harsh pulsar
im just using the website
hasty hinge
For #discord-bots bots, (Discord.py uses async), is it better to use mysql-connector-python, PyMySQL or AioMySQL?
tepid cradle
I'm not able to escape the
'at all? In normal sql queries it's escaped with\
@bold geode you're using MySql?
bold geode
@hasty hinge use something async, aiomysql is async for example while pymysql isn't
@tepid cradle yes (technically aiomysql)
tepid cradle
can you repost the query you're trying to run? It kind of got lost in the above discussion, I'm not able to find where you were facing the problem
mention me when you reply
bold geode
https://discordapp.com/channels/267624335836053506/342318764227821568/743835822557626398
I'm not at my PC rn so I can't really give too much more info though.. @tepid cradle
tepid cradle
OK, so basically you need to use single quotes to escape single quotes. I just tried it out in Dbeaver as well as with mysql-connector-python and it works.
I have a kodi Db setup on my Raspberry py which is on MariaDB, I ran the following code
query = "select idMovie , c00 , c03 from movie where c00 like '%''%'"
cursor = cnx.cursor()
cursor.execute(query)
result = list(cursor)
The result had Ender's game, No man's land, Pete's dragon, etc. Worked perfectly for me.
Let me know if you try that and whether it works or not. I have not used aiomysql, but I'd guess the query works the same way regardless of the library
bold geode
Aiomysql pretty much directly bridges to pymysql.
I'll give it a try once I'm home, do you mind dming me that in case it gets buried? @tepid cradle
smoky turtle
I have PyCharm professional and it has this automatic database thingy, but I have no idea how to use SQL in general. Is the name of my table summaries if I wanted to do a query?
sqlite3 if the information is needed
tepid cradle
It seems so, yes
torn sphinx
hi guys
pliant cliff
What is a collation?
bold geode
@tepid cradle python await cur.execute(f"insert into {table} '%s' values '%s'", (columns, values))
Like that or?
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '''id, permissions, prefix, disable'' values ''2343435, {\\'44545\\' : [\\'8ball\\', ' at line 1")
Gives that error. Current information being stored:
#column: value
{
"id": str(2343435),
"permissions": "{'44545' : ['8ball', 'bird', 'cat', 'connect', 'dog', 'help', 'np', 'patreon', 'play', 'pun', 'queue', 'rps', 'roles', 'server', 'setup', 'tag', 'wholesome']}",
"prefix": "?",
"disabled": '["linkfilter"]'
}```
rich trout
I don't think you can dynamically specify column names
you may have to do that with the f-string as well
bold geode
Hmm. I don't think injection is a risk with them..
Much better. Guess I have a ton of , to escape now @rich trout
insert into guilds (fid, permissions, prefix, disable) values (2343435, {'44545' : ['8ball', 'bird', 'cat', 'connect', 'dog', 'help', 'np', 'patreon', 'play', 'pun', 'queue', 'rps', 'roles', 'server', 'setup', 'tag', 'wholesome']}, ?, ["linkfilter")
(1136, "Column count doesn't match value count at row 1")```I'm pretty sure %s represents one column
bold geode
%s represents one variable iirc
But I think the db is seeing all of those , and taking them as value seperators
rich trout
What I've done before is something like:
execute(f"INSERT INTO {table} {columns} VALUES {', '.join('?'*len(columns)}", ())
Which expands to "Insert into 'thing' id, name, etc values (?, ?, ?)"
bold geode
Hmm
I'm still stumped. Doesn't help that I'm storing a dictionary casted to a string I'm sure
async def insert(self, table, data: dict):
columns = []
values = []
for k,v in data.items():
columns.append(k)
values.append(v.replace(",", "\\,").replace("'", "\\'"))
columns = ", ".join(columns)[:-1]
values = ", ".join(values)[:-1]
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
print(f"insert into {table} (f{columns}) values ({values})")
await cur.execute(f"insert into {table} (f{columns}) values (%s)", (values))```
My full helper function.How can I deal with passing
{
"id": str(2343435),
"permissions": "{'44545' : ['8ball', 'bird', 'cat', 'connect', 'dog', 'help', 'np', 'patreon', 'play', 'pun', 'queue', 'rps', 'roles', 'server', 'setup', 'tag', 'wholesome']}",
"prefix": "?",
"disabled": '["linkfilter"]'
}
Etc?
Ping me if you reply please
tepid cradle
Not sure what you're trying to do here.
Can you do one thing, remove all the string concatenation and show me one query how it would look in its final form, with all the values in place, like, if you were writing the query manually.
@RaVen
Ping me if you reply please
@bold geode not able to ping for some reason
bold geode
Give me a few, I'll get that yeah
bold geode
INSERT INTO `guilds` (`id`, `permissions`) VALUES ('6767345', '{\'323323\' : [\'8ball\', \'bird\', \'cat\', \'connect\', \'dog\', \'help\', \'np\', \'patreon\', \'play\', \'pun\', \'queue\', \'rps\', \'roles\', \'server\', \'setup\', \'tag\', \'wholesome\']}"');
``` is how an sql client sends it @tepid cradleYou need to convert the entire dict to a single string
Unless the Db you're using supports JSON and this is a JSON column
@bold geode
bold geode
@tepid cradle the larger dict is split by the helper function and the permissions one is just a string
tepid cradle
Right. All the escaped quotes threw me off.
And what is the error that this gives you?
@bold geode
bold geode
Through everything I've tried a mixture of https://discordapp.com/channels/267624335836053506/342318764227821568/743949421133168740 and https://discordapp.com/channels/267624335836053506/342318764227821568/743987404284755969 @tepid cradle
Sorry if I'm replying slow
tepid cradle
Do you have any other program you use to interact with the Db? Like php mysql or something?
Because the query looks fine. I'd be interested in knowing what error it gives if run directly on the Db
bold geode
@tepid cradle the query with escapes everywhere was from an sql client, it worked fine. I can't seem to work out exactly what pymysql sends to the db though, I'd imagine it'd be similar to that..
tepid cradle
OK. Then it should work with Python as well. Did you try casting the dict to string explicitly? Like cur.execute("insert into table (id, permissions) values (%s, %s)", (id, str(permissions_dict)))
bold geode
I haven't, no, as it's already a string (that exact string was what I gave to the sql client in the permissions column)
tepid cradle
OK. That seems weird. Can you show me the rest of your code?
bold geode
I can do, although not rn as its 4:30 am and I'm in bed.
https://discordapp.com/channels/267624335836053506/342318764227821568/743991198724653116
My full helper function is there, although I imagine you've already seen it.
tepid cradle
OK. I'll check the code, kind of lost track of it in all the messages. Ping me when you are up next.
bold geode
Will do. Thank to for being so patient and sorry for this aha
tepid cradle
async def insert(self, table, data: dict):
columns = []
values = []
for k,v in data.items():
columns.append(k)
values.append(v.replace("'", "''"))
columns = ", ".join(columns)
async with self.pool.acquire() as conn:
async with conn.cursor() as cur:
sql_query = f"insert into {table} ({columns}) values (%s, %s)"
print(sql_query)
await cur.execute(sql_query, values)
Let me know if this code works by any chance.
deft pasture
def img_point(userid:int, username:str, point:int=1):
conn = sqlite3.connect(f'{serverInfo["server"]}points.db')
cursor = conn.cursor()
cursor.execute("""CREATE TABLE IF NOT EXISTS points(
server TEXT,
uniqueusers INTEGER PRIMARY KEY,
username TEXT,
discordID INTEGER UNIQUE,
images INTEGER,
points INTEGER
)""")
conn.commit()
cursor.execute(f'SELECT * from points where discordID={userid}')
if cursor.fetchone() == None:
cursor.execute("""INSERT INTO points(server, username, discordID, images, points) VALUES(?, ?, ?, ?, ?)""", (serverInfo["server"], username, userid, 0, 0))
cursor.execute(f'SELECT * from points where discordID={userid}')
allinfo = cursor.fetchone()
usernamedb = allinfo[2]
if usernamedb.lower() != username.lower():
string = f'{str(username)[:-5]}'
cursor.execute(f"""UPDATE points SET username={string} WHERE discordID={userid}""")```Why am i recieving error on updating username
syntax error
name is like junior instead thats passed
tepid cradle
You are getting error because you don't have quotes around string. However, putting quotes there is not the right solution.
In the insert query, you have used ?, ?, ... as placeholder for values and then passed them separately. That's called query parameterization. That's how you are supposed to write SQL queries inside your programs. All queries should use query parameters, not f-strings.
So your update query should be cursor.execute("update points set username = ? where discordID = ?", (string, userid))
The other two select queries should also be changed to use query parameters
@deft pasture
deft pasture
okok.
raven cedar
I connected to my heroku postgres database via pgadmin. why is there a lot of (1600+) random databtase names and users?
and I am tring to take backup. I checked not saving owner of database there is still something along the sql queries, ALTER whatever user role "iyhbj4bhj5" one of users
which gives error when I try to restore it in blank db it says there is no user "iyhbj4bhj5" which was that random user
bold geode
@tepid cradle
insert into guilds (id, permissions, prefix, disabled) values (%s, %s)
not all arguments converted during string formatting```Show me the output of the print statement
tepid cradle
Ok
bold geode
(Can get full traceback if need be)
tepid cradle
Your dict had four keys, I thought it had only 2, that's why I put only 2 %s.
If you want to insert all 4, then put 4 times %s
bold geode
Ah, I see. One second..
Aand another sql error aha
Ooh i think that ones on me
Yeah, it worked this time. So now we should dynamically generate how many %s we need
Which I have now done.
Seems to be working well. Do you think you could highlight what you changed (and what each change is for) so I can understand this better for future reference? @tepid cradle
tepid cradle
Sure. Give me a few minutes, I'll send the explanation when I'm on PC. Right now I'm on phone, so can't really type that much
bold geode
Ah, fair enough. Thank you
tepid cradle
@bold geode
I have added explanatory comments to your code as well as my code. Let me know if any of the points are not clear:
https://paste.pythondiscord.com/luxegowoce.py
bold geode
passing values as a string might not work as the library will try to pass it as a single value
That might have been the cause of some of the errors then.
I think I get it all, thank you.
tepid cradle
You can further simplify it by doing:
columns = ", ".join(data.keys())
values = [v.replace("'", "''") for v in data.values())
This will do away with the entire following part:
columns = []
values = []
for k,v in data.items():
columns.append(k)
#I have used ' to escape ', so instead of \', I'll get ''. That works in SQL
values.append(v.replace("'", "''"))
columns = ", ".join(columns)
@bold geode I think you missed my last message
torn sphinx
could i ask about json here?
tepid cradle
You can always go ahead and ask. Even if it's the wrong channel, nobody is going to ban you for it. People will generally point you to the right channel
But yeah, it's fine to ask JSON questions here
torn sphinx
ok so im making a work command like this
@client.command()
async def work(ctx):
data = random.randint(1,21)
await ctx.send(f"You got {data} cash!")
with open("totalcash.json", "w") as write_file:
json.dump(data, write_file)
``` and it works ok and shows the cash i just want it to do it like i will have certain users like its not one number like every user has there own amount so like
```json
{
"DiscordName": 0
"ImBugle": 103
"xDPLa": 344
}
```like there name then there amount of cash im new to json, also i have a command that would show the users cash
```python
@client.command()
async def cash(ctx):
with open("totalcash.json", "r") as file:
json_dict = json.load(file)
await ctx.send(f"You have {json_dict}")
I have a DB full of weapon names and ive found a problem python await cursor.execute("SELECT wep_name from weapons WHERE %s LIKE concat('%%', scum_code, '%%')", ([weapon])) i use this to select the weapon name because the code always changed. but 1H_Small_Axe_C_2147211759 isnt working
On the DB that same axe is stored as 1H_Small_Axe and this method works for other weapons just not this axe.
Can anyone shed any light to why this 1 axe is being stubborn and not working ๐
tepid cradle
I remember this was pointed out to you yesterday or day before as well. You cannot use query parameters to pass column names
Query parameters can be used only for vlaues
torn sphinx
@tepid cradle can u help me
gaunt frigate
In my sqlite3 code
Is it better to
a. Create a connection everytime I make a query or insert or something
b. Create a connection when I init and use that same connection throughout the use of the program
brazen charm
same connection throughout generally
little ridge
can we talk about NoSql here?
fallow elm
yea
rose parrot
Anyone here decided to deploy multiple dbs on an application while using SQLalchemy as ORM? If so, Can u point me why? I have several different unrelated models on my application, each needed on different blueprints so I'm kind-of considering it but not sure if that's a good idea at all
fallow elm
can you clarify what problem or concern you're worried about?
i've run applications past / present with multiple databases because we wanted isolation of workloads
or if you need to shard a large workload across multiple databases
but usually i'd just handle that at the time when it became a problem
rose parrot
I've deployed an app to catch leads for my family business. The main blueprint runs the landing pages for the products but there's another blueprint that is responsible for admin and staff access. So do u think I should run multiple dbs on that case, or should I just store clients and products information of my main blueprint along with users and staff information of the admin/staff blueprint?
One interact with the other on some level, since the admin stuff manages it all while staff privileges only have restricted access to view and edit some stuff, but not complete control of it all
fallow elm
when you say multiple databases are you asking about multiple servers for each or multiple databases on a single server
rose parrot
Single server, I have deployed my app using heroku's free stuff
fallow elm
i'm not familiar with how heroku databases work and if you can join between them or if they're completely isolated so would depend on what's possible there and what your needs are
rose parrot
Should I just concern about that once that becomes a problem?
fallow elm
there's not really a yes or no answer here. neither way is better than the other and you can always migrate later
rose parrot
I might be overthinking security
fallow elm
it depends on your needs
regardless of whether it's on different databases you can write code that leaks the admin info if you're not careful and if the application is compromised it'd presumably have access to both
if the datasets were completely isolated and you had a different app just for admin then i could see splitting them for security making more sense
rose parrot
Gotcha! It's not completely isolated at this point but I think there's room for isolating it since the staff management stuff contains so much things that are completely isolated from the marketing/leads catching stuff
Maybe I should split the admin views of the main blueprint from the admin views on the staff views. Will think more about that as I build it. This way I won't be joining staff files and info with clients files and info...the only thing that is common between would be the products information
heady galleon
hi guys
im looking to create a simple database table for use in discord.py
would like to make it online so me and 1 other user can update the table
any recommendations on a service to use? which will be easy to query from? im a total noob at discord bots
smoky turtle
I have a database that I created through flask_sqlalchemy (sqlite3) and it has entries of the type PickleType. When I tried reading them using the sqlite3 module I got some odd results with weird sets of characters in them. Should I only be accessing them with flask_sqlalchemy which gives me no problems, or could I use sqlalchemy in general? Alternatively, is this issue fixable/known?
Here is part of the sample output:
I think.\x94\x8c$Um so we can\'t put it on left-click.\x94\x8c!We could put it on a right-click.\x94\x8c5We could for example have a little menu that pops up.\x94\x8c(Yeah, it might be quite good, I suppose.\x94\x8cgSo right-click on at menu, you know, and you can click on speaker characterisation, up pops the window.\x94\x8c$What else could we have in the menu?\x94\x8c\x14That\'s a good point.
any recommendations on a service to use? which will be easy to query from? im a total noob at discord bots
@heady galleon you can check Heroku. They give free PostgreSQL database for up to 10000 rows of data
heady galleon
@tepid cradle I ended up going with mongoDB
tepid cradle
Their free tier they offer? Which they call Atlas or something?
heady galleon
yes, thats right
now I'm just trying to figure out how to return a balance in my discord bot with it
for a specific user, from a db I've made there
tepid cradle
Oh. I tried that once. But I don't have a static IP. Couldn't figure a way around that as they only allow whitelisted IPs to access it
heady galleon
There's an option to allow all IPs
which I did, as I'm sharing the database with another guy, and I don't really want to ask him for his IP address
tepid cradle
Oh. Good to know, I was not able to find it. Will check once more
Install pymongo. The basic query is pretty straightforward. Something like db.collection.find({'user_id': 1234})
heady galleon
oh
nice
thanks
gkrou, have you any experience with discord.py?
i guess Ill ask in discord.py
heady galleon
TypeError: query() got an unexpected keyword argument 'lifetime'
got this error from pymongo
this was an error generated by an outdated version of dnspython. solved.
junior ivy
can someone give me an idea on how i should proceed in making the global xp command
the table has user_id,xp and server id
i was trying to fetch the top 5 rows with xp
but that might include data of same user in different guilds
so can someone help me out here
info = await self.bot.pg_con.fetch("SELECT DISTINCT user_id FROM profiles ORDER BY xp DESC LIMIT 5")
tried this but doesnt work
distant patrol
Yeah I've had that problem too @junior ivy , add a GROUP BY user_id and it should work for you
junior ivy
thanks a lot for replying
distant patrol
DISTINCT isn't super reliable
distant patrol
At least from my experience in my like, 3 weeks of working with databases XD
Yeah, so add GROUP BY right before your ORDER BY
And you should get 5 results of unique user IDs
Because it's grouping them together
Or at least that's how it worked with SQLite, not sure what you're using
junior ivy
im using postgres
distant patrol
Ah ok. No guarantees then, but they should be similar at least
junior ivy
info = await self.bot.pg_con.fetch("SELECT GROUP_BY user_id FROM profiles ORDER BY xp DESC LIMIT 5")
so smthn like this?
distant patrol
SELECT user_id FROM profiles GROUP BY user_id ORDER BY xp DESC LIMIT 5
junior ivy
oh ok
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: GroupingError: column "profiles.guild_id" must appear in the GROUP BY clause or be used in an aggregate function
@distant patrol i get this error
distant patrol
Hmm, that's no good. If you group by guild_id as well that defeats the point
Oh I think I found it
Are you using postgresql by any chance?
DISTINCT applies to the entire row, which is why it isn't working
postgresql looks like it has DISTINCT ON, which you can use on a specific attribute
Like user_id
junior ivy
oh ok
info = await self.bot.pg_con.fetch("SELECT * FROM profiles DISTINCT ON user_id ORDER BY xp DESC LIMIT 5")
so this will fetch the data of the non-duplicate rows
i hope the syntax is correct
distant patrol
DISTINCT ON goes in your select statement
so SELECT DISTINCT ON (user_id)
And yeah, it should give you the data for non-duplicate user_ids
Unlike DISTINCT which just made sure the entire row was unique
distant patrol
SELECT DISTINCT ON (user_id) * FROM profiles...
According to the response I found there, that's how you do that then
junior ivy
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: InvalidColumnReferenceError: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
there seems to be a problem
distant patrol
Back to Google we go!
junior ivy
yep
distant patrol
Looks like postgres demands that you order by the attribute you're requiring be unique, because that's how it checks for uniqueness
distant patrol
Yeah, subquery seems to be the common answer
junior ivy
yep
tepid cradle
Distinct just selects distinct values from a column. Group by performs aggregation on one column using a grouping on another column.
So something like select user_id, sum(xp) from users group by user_id
This will give you the sum of xp column for each user in user_id column
rustic harness
do you guys know how to return values which appear multiple times in a column using pandas
tepid cradle
Pandas is not database, use the help channels
torn sphinx
for member in guild.members:
await self.bot.pg_con.execute(
'''INSERT INTO Users VALUES($1,$2,$3) ON CONFLICT(user_id) DO NOTHING''',
member.id, True, True
)
Hi, is there a way to bulk insert a data into the database? I'm a bit concerned of my way here, this happens when the bot first joins the server, which will insert every member from the server into the database
brazen charm
asyncpg?
torn sphinx
yes
brazen charm
there is a function dedicated to it, let me grab it from my orm
torn sphinx
o okie
brazen charm
copy_records_to_table is the function
await conn.copy_records_to_table(
table_name=self._table,
columns=sub_query['columns'],
records=sub_query['values']
)```Where table name is obs the table name
columns should be a tuple/list of column names
and records a list of lists / tuple of tuples
brazen charm
np
whole mica
i am using google sheets as the database
mainly because it is easy to access
my python programme needs to constantly get the info from the sheet
and to check if anything is changed
sheet = client.open("Database").get_worksheet(1)
while True:
old_data = 0
target = sheet.get_acell('A1')
if target != old_data:
print(f'Target changed from {str(old_data)} to {str(target)}')
old_data = target
time.sleep(1)
however, i always get this error
Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com' for consumer
is there any other ways to replace google sheets?
torn sphinx
@brazen charm is there a way to upsert the copy_records_to_table function?
fading pine
brazen charm
@torn sphinx not that I'm aware of, the docs might give more Information but iirc it's only for bulk deposits nothing more, executemany would probably be more suited ig
torn sphinx
i see
torn sphinx
how would i fetch the users cash
@client.command()
async def reg(ctx):
id = ctx.author.id
print(id)
c.execute(f"INSERT INTO ecom VALUES ('{id}', '1')")
conn.commit()
@client.command()
async def cash(ctx):
id = ctx.author.id
user = c.execute(f"SELECT * FROM ecom WHERE id='{id}'")
print(c.fetchall())
print(user.cash)
conn.commit()
my other file
import sqlite3
conn = sqlite3.connect('ecom.db')
c = conn.cursor()
#c.execute("""CREATE TABLE ecom (
# id integer,
# cash integer
# )""")
#Finding user cash
#{userid} = Ecom(f'{userid}', f'{cash}')
#print(userid.cash)
# c.execute("INSERT INTO ecom VALUES ('656692071335329839', '100')")
c.execute("SELECT * FROM ecom WHERE id='656692071335329839'")
print(c.fetchall())
conn.commit()
conn.close()
some of the blanked out stuff is just testing
tepid cradle
what is the output of this command?
print(c.fetchall())
torn sphinx
fetch's all users that have used the command >reg
let me get the output
[(656692071335329839, 100), (656692071335329839, 1)]
^ user id ^ user cash
hmmmm
i dont want users to register more then 1 time
@torn sphinx you have to make it a primary key for the id column
btw im brand new to sqlite3
please explain
you mean SQL in general?
ye
well i wanted a command like !work they get money it saves it then they could do !cash to see there cash
when you have a table, you usually want the rows to be identifiable for each one of them, to keep it simple, making a column a primary key, say for example id would restrict the database to keep a rule that the id column needs to be Unique, hence if you insert another data into that table with the same id, it will not let you
ok but how would i make the id a prime/Unique columm
the problem with your code is that, you did not check if the user is already in the table
alr ...
torn sphinx
c.execute("""CREATE TABLE ecom (
id integer unique,
cash integer
)""")
@torn sphinx Stella dose this work
@torn sphinx well, it will make each row unique for that column, but it's not a primary key
hmm
making it primary would be id integer PRIMARY KEY,
instead of unique
yes
alr one other question that i needed
how would i get the users cash?? i really dident know
your data you've shown was [(656692071335329839, 100), (656692071335329839, 1)], it's a tuple inside a list, since it's inside c.fetchall()
assign it to a var, var = c.fetchall(), you can access it by index, var[0] would give you (656692071335329839, 100), var[0][1] would give you 100
Thanks for helping you probs think im stupid i could agree LoL
Most people say learn and dont help
oky
what are the ways to pass in list so i can use in keyword? This seems rather inefficient
await bot.pg_con.fetch(f"SELECT * FROM Users WHERE user_id in ({', '.join(str(user.id) for user in bot.users)})")
my table name is money the user id name / prime columm is id and they money is called cash
brazen charm
@torn sphinx you shouldnt really use that
torn sphinx
what's the appropriate approach
brazen charm
using the ARRAY operator in postgre
you can do $1 as an array providing you specify that its a array and its type first
ANY($1::INTEGER[][]) would represent a 2 depth array of ints
torn sphinx
what's ANY
brazen charm
its like the any() function in python
torn sphinx
o
brazen charm
you would essentially do WHERE user_id IN ANY...
torn sphinx
@client.command()
async def reg(ctx):
id = ctx.author.id
print(id)
c.execute(f"INSERT INTO money VALUES ('{id}', '1')")
conn.commit()
print(f"I have added {id} To my list")
``` would this work for adding a user to the databasedepends on your table
you also shouldnt use f strings in queries
or any method of string formatting for that matter
torn sphinx
this is my table
import sqlite3
conn = sqlite3.connect('money.db')
c = conn.cursor()
#c.execute("""CREATE TABLE money (
# id integer PRIMARY KEY,
# cash integer
# )""")
conn.commit()
conn.close()
i have already created my table
brazen charm
okay, in that case it wouldnt work
torn sphinx
seems to be raising asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "ANY" when i did
await _bot.pg_con.fetch(f"SELECT * FROM Users WHERE user_id in ANY($1::INTEGER[])", [user.id for user in _bot.users])
'' tells SQL that its a string
ehhhhhhhhh, 1 sec i gotta check how i did it again lol
torn sphinx
wait so when should i use in 
brazen charm
if you have a enum if i remember correctly
or if you are querying based of the results of another query
e.g
SELECT * FROM bobs WHRE id IN (SELECT ids FROM some_thing)
tho im going completely off the top of my head lol
torn sphinx
oH duh ofc, okie thx u
brazen charm
@torn sphinx you need to remove the '' and replace the f string with a standard string and use ? as place holders
torn sphinx
0_0 im new to this remeber that LOL
soooo.. like this
@client.command()
async def reg(ctx):
id = ctx.author.id
print(id)
c.execute("INSERT INTO money VALUES (?, ?)")
conn.commit()
print(f"I have added {id} To my list")
nearly
you just need to add a tuple in that execute with the values you want it to fill in
torn sphinx
@client.command()
async def reg(ctx):
id = ctx.author.id
print(id)
c.execute("INSERT INTO money VALUES (?, ?)", (id, 1))
conn.commit()
print(f"I have added {id} To my list")
``` ... sooo that should workmhmm
torn sphinx
yea thanks
torn sphinx
@client.command()
async def cash(ctx):
pass
@client.command()
async def work(ctx):
id = ctx.author.id
rancash = random.randint(5,56)
c.execute("UPDATE money SET cash=? WHERE id=?", (rancash, id))
conn.commit()
``` may i have some help with these commands
`cash` needs to how there cash
`work` gives them money but needs to + there money they already hade with the money they just gotYou can use += which will add to it @torn sphinx
proven arrow
SET cash+=?
narrow saffron
can you guys suggest me any alternatives for a code that does multiple insertions at the same time, I'm currently using sqlite3 and it kept facing the "database locked" error
fallow elm
are you asking about batch inserting or do you have multiple writers?
fallow elm
sqlite doesn't support multiple writers. you'll want to use a different database or have a single thread/task/worker/etc doing writes
fallow elm
you could look at mysql/mariadb or postgres are the most common
both are completely viable choices that work well
torn sphinx
would this work
@client.command()
async def work(ctx):
id = ctx.author.id
rancash = random.randint(5,56)
c.execute("UPDATE money SET cash = cash + ? WHERE id=?", (rancash, id))
conn.commit()
it should work. does it not?
torn sphinx
it dose
but quick question
@client.command()
async def cash(ctx):
pass
``` how would i see the authors cashc.execute("""CREATE TABLE money (
id integer PRIMARY KEY,
cash integer
)""")
``` this is my table
stark nest
can anyone here assist with a time series analysis of 1m stock data? need some guidance.
torn sphinx
Wrong channel i think
torn sphinx
@client.command()
async def cash(ctx):
id = ctx.author.id
usercash = c.execute("SELECT cash FROM money WHERE id=?", (id))
await ctx.send(f"You have `{usercash}$` !")
``` how i keep getting
`discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: parameters are of unsupported type` error@torn sphinx You have to fetch the data after
For example, result = c.fetchone()
And you should be using a async driver for discord bots.
proven arrow
Well, it will block/freeze your application, whilst it connects to the database to do the operations.
torn sphinx
what would i use then
i just dont want to haft to recode all of the crap i just did i have been working on this for so long
@proven arrow
proven arrow
Which database do you use?
torn sphinx
sqlite3
proven arrow
For that you can use aiosqlite. The syntax is the same since they are sql. Only major difference will be you have to await things.
https://pypi.org/project/aiosqlite/
https://aiosqlite.omnilib.dev/en/latest/
proven arrow
No
torn sphinx
this is my current
table
import sqlite3
conn = sqlite3.connect('money.db')
c = conn.cursor()
#c.execute("""CREATE TABLE money (
# id integer PRIMARY KEY,
# cash integer
# )""")
conn.commit()
conn.close()
You just have to await stuff now. The pypi link I sent has examples, you can have a look at.
For example, The following:
conn = sqlite3.connect('money.db')
Would become:
conn = await aiosqlite.connect('money.db')
i looked at the aiosqlite and it looked like a lot of different stuff
dosent seem to bad
its just i wouldent know what to await and what not to await
proven arrow
Pretty much everything that is used to communicate with the DB
proven arrow
And actually, my bad i linked you the wrong documentation. Here is the correct one: https://aiosqlite.omnilib.dev/en/latest/
proven arrow
If you didnt what?
torn sphinx
use aio
proven arrow
Basically when you make a database connection/request, it needs to connect to the db and get the data. This takes time. When you dont use async, it will freeze all code running and go ahead and get the data from your DB. If for example, this process takes 2 seconds, your bot will become unresponsive to other parts of the code for those 2 seconds, until the database returns with the results. Consider it nuking your bot for X seconds. By using async version it allows the code to do other things whilst the db request takes place as well.
And if you block for too long eventually you will get disconnected by discord.
torn sphinx
do i haft to change the way i select or is it just awaiting the stuff?
proven arrow
Well yes, just refer to the docs and their example. Most of it is there.
Example from their docs: cursor = await db.execute('SELECT * FROM some_table')
torn sphinx
no im talking about
c.execute("UPDATE money SET cash = cash + ? WHERE id=?", (rancash, id))
other then awaiting this do i haft to change anything than the UPDATE part
proven arrow
No, the sql should be the same
torn sphinx
just await pretty much
sooo if im correct my main db file would look like this
import aiosqlite
conn = await aiosqlite.connect('money.db')
c = await conn.cursor()
await c.execute("""CREATE TABLE money (
id integer PRIMARY KEY,
cash integer
)""")
await conn.commit()
await conn.close()
@proven arrow
proven arrow
Yeah looks good, although you can probably just do conn.execute(..) instead of getting the cursor. You should try it and see if it works.
torn sphinx
its good
hold on what
why am i using c
im a idoit
your right
UHH UH UH
File "c:/Users/hedge/Desktop/Discordbot/ecom.py", line 3
conn = await aiosqlite.connect('money.db')
^
SyntaxError: 'await' outside function
Well you can only await things inside an async function
torn sphinx
so ooo o oo o o i haft to make my connect a function
proven arrow
Yes an async function
proven arrow
Well for most of your queries you will probably be inside an async function anyways since they would be inside a command, which needs async by default
If you want a function to create your tables you could have something like, for example
async def create_db(bot):
await bot.wait_until_ready()
#do database stuff
bot.loop.create_task(create_db(bot))
proven arrow
Well at least itโll save you tears later when things break out of random as it would if you used the sync version
torn sphinx
LOL so could i just yeet all of my stuff that i did in the db file to my main bot file?
proven arrow
How you do that is up to you
torn sphinx
well ile just keep it in my main db file
question do i close and commit in side of the function
proven arrow
Yes since they use await as well
torn sphinx
client undefined
client.loop.create_task(create_db(client))
n that part
im in a dead crying kind of emotion rn 
ima just put them in the bot file
im having so many errors
@proven arrow
.-.
proven arrow
Well yeah client must be defined. It wonโt come out of nowhere.
proven arrow
Did you import it?
torn sphinx
import aio yea
proven arrow
How did you define the connection?
proven arrow
Yeah that looks fine.
proven arrow
Oh thatโs probably your IDE
torn sphinx
?
proven arrow
Where does it give you that error? In the console?
torn sphinx
let me see
i got no error and the db was created but still undefined conn error
want me to try a command ?
proven arrow
Yeah as i said its probbaly just your linter being funny
torn sphinx
error
when i run the command
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: NameError: name 'conn' is not defined
proven arrow
Whats the command look like?
torn sphinx
@client.command()
async def work(ctx):
id = ctx.author.id
rancash = random.randint(5,56)
await conn.execute("UPDATE money SET cash = cash + ? WHERE id=?", (rancash, id))
await conn.commit()
await ctx.send(f"You earned `{rancash}$` !")
Well yeah conn is not defined anywhere there
proven arrow
No you defined it locally to that function create_db
torn sphinx
EMMMM
every time i make a command i haft to have
conn = await aiosqlite.connect('money.db')
await conn.cursor()
??
that requires the db
proven arrow
def foo():
x = 5
def bar():
print(x)
This is what you are trying to do currently.
torn sphinx
ye ye i know now
so every db command needs
conn = await aiosqlite.connect('money.db')
await conn.cursor()
Yes or you can have a single connection maybe stored in the client instance/ or some global var
torn sphinx
lets do the client instance one
ile just define them
in the command
alr now that i have the aio setup
may i have help with my original issue
torn sphinx
@client.command()
async def cash(ctx):
conn = await aiosqlite.connect('money.db')
await conn.cursor()
id = ctx.author.id
usercash = await conn.execute("SELECT cash FROM money WHERE id=?", (id))
records = await conn.execute_fetchall()
for row in records:
print("Id: ", row[0])
print("Cash: ", row[1])
await ctx.send(f"You have `{row[1]}$` !")
```i have tried all i can i dont know how to get there cash at this point@torn sphinx records = await usercash.fetchall()
Or if you want a single record then use .fetchone() instead
execute_fetchall is for executing a query and returning the data
torn sphinx
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: ValueError: parameters are of unsupported type
error
my code
@client.command()
async def cash(ctx):
conn = await aiosqlite.connect('money.db')
await conn.cursor()
id = ctx.author.id
usercash = await conn.execute("SELECT cash FROM money WHERE id=?", (id))
records = await usercash.fetchall()
for row in records:
print("Id: ", row[0])
print("Cash: ", row[1])
await ctx.send(f"You have `{row[1]}$` !")
id in the table is probably a string
also
(id,)
not (id)
@torn sphinx
still dont work lol
hm
is id a string in the db?
try using (str(id),) instead
alr
invalid syntax (<unknown>, line 252)pylint(syntax-error)
send code.
@client.command()
async def cash(ctx):
conn = await aiosqlite.connect('money.db')
await conn.cursor()
id = ctx.author.id
usercash = await conn.execute("SELECT cash FROM money WHERE id=?", (str(id),))
records = await usercash.fetchall()
for row in records:
print("Id: ", row[0])
print("Cash: ", row[1])
await ctx.send(f"You have `{row[1]}$` !")
whish is line 252
HUH
records = await usercash.fetchall()
thats not a error anymore
tho
do you know how DB's work?
uuhhhh im just going to open a help page for this
heady galleon
Is there a service which can be used to both publish a spreadsheet for viewing online, as well as turn the spreadsheet into a csv and host it as a database?
currently using mongodb and publishing the spreadsheet as a google doc, but would be better to streamline thr process
warped frigate
So at the moment I'm using PostgreSQL with psycopg2 and Python (the python is statically typed and compiled with Cython for speed) for a project, and the project involves manipulating large amounts of financial data. The table I'm working with has about 1.15m rows, and I need to match each of the 1.15m values from one of the columns to another list of 1.13m values (some values appear more than once). Currently, the bottleneck seems to be "AND IN %s", as the list that is passed to that is 1.15m values long, aka the symbol from the other data that I need to match to. I've chunked the list into 1000 chunks (1158 values each), and I ran 1000 queries with each part of that smaller list, and I found it took about 50 seconds for each individual query, or 50,000 seconds total. I expect if I just ran a single query it would take a bit less time because less overhead, but I don't think it would make much of a difference relatively, and doing 1000 individual queries allows me to log every 50 seconds when a single query completes.
Is there any clear way to speed it up so it takes less than 50k seconds total (preferably under 500 seconds in the final version)?
SELECT * FROM IV_working WHERE date_id = %s AND symbol IN %s ORDER BY row_id LIMIT %s
I'm happy to give more code/info if needed.
Oh, and the limit value is 1158, or however many values are in the current chunk of the list
Just bolded what I think the important parts are, and underlined the main question
fallow elm
@warped frigate can you share the schema?
a million rows isn't a lot so this shouldn't take anywhere near that long
but passing large lists to IN is not efficient for sure
if you can share the schema + and indexes you have plus the query you're using to get the symbol list i'm sure someone can help
warped frigate
oh
how do i view the schema @fallow elm
(i have pgadmin4 if that helps)
I'm a total beginner with sql databases, all my previous experience with databases is with mongodb
fallow elm
\d+ tablename when connected to the database
warped frigate
oh ok
it says 'did not find any relation named "IV_working"' when I've used that specific table in queries successfully
fallow elm
are you connected to the specific database?
fallow elm
\c dbname
warped frigate
if there's a way to do this with pgadmin4, that'd be helpful because I know my way around that
fallow elm
or you can pass it on the command line
i'm not familiar with pgadmin4 but i'm sure there's a way
fallow elm
yeah, just put in code block
warped frigate
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+------------------+-----------+----------+---------+----------+--------------+-------------
row_id | integer | | not null | | plain | |
date_id | date | | not null | | plain | |
ticker | text | | | | extended | |
expiration | text | | | | extended | |
strike | real | | | | plain | |
type | text | | | | extended | |
iv | double precision | | | | plain | |
symbol | text | | | | extended | |
Indexes:
"iv_working_pkey" PRIMARY KEY, btree (row_id, date_id)```also i'm not querying postgresql for the symbol list, i'm loading the symbol list from a file
at some point i may input the file into postgres but i haven't needed to yet
fallow elm
and that file has how many unique symbols in it? 1.3 million?
fallow elm
alright, so your PK is (row_id, date_id) so your not getting the benefit of that index only querying by row_id. you probably want to add a secondary index on date_id and possibly symbol so that you're not doing a full table scan for each of your batch queries
warped frigate
ah ok
how do I do that
actually wait i'll google
should I make the indexes "concurrent" if I want to take advantage of parallel queries (on postgresql 10)
fallow elm
Yea
Doesn't matter of you're not in production but if it's taking live queries it avoids locking the table
warped frigate
the docs don't seem to be very clear about what I have to specify when building an index
so far I've specified a table name, but I can't figure out what the minimum amount of inputs is
oh i just added "ON <column>"
still not working tho
fallow elm
create index concurrently date_id_idx on table iv_working (date_id)
warped frigate
ah thanks!
testing it out now
psycopg2.errors.ActiveSqlTransaction: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
hm
fallow elm
is anything using this database rigth now?
warped frigate
maybe pgadmin4 ig
warped frigate
oh nothing critical that i know of
fallow elm
shouldn't be a long lock for only a million rows
warped frigate
i just removed it and it seems to be adding the indexes correctly now
based on your experience, how much of a speed benefit does an index on the right columns provide for you
fallow elm
what do you mean "right" columns?
warped frigate
columns that are extremely slow to query without an index
fallow elm
depends on your data shape. how many rows out of 1.15m should your date_id = %s filter down to?
approximately
warped frigate
the date_id is actually the same for every row in the working table (it's just a remnant of when I stored the data for every day in the table that i was querying)
i should remove it tbh
fallow elm
alright then that index will do nothing
haha
since it's still going to have to pull the whole table
warped frigate
the symbol though is unique for nearly every row
fallow elm
but the symbol index should help a lot since it can jump to exactly to the right record
fallow elm
heh, so you should remove the date_id index since it's not going to be helpful and probably remove it from the PK and remove the column as well
heh
warped frigate
well the column is going to come in handy when I write the data to the main table containing every day's data
but i'll make sure not to do the date_id index
fallow elm
well if you'll eventually have lots of data per day then the index would be useful in that case to filter the dataset being queried
though you should remove it from the PK unless you expect to have queries with row_id = %s AND date_id = %s which seems unlikely
warped frigate
that's possible
fallow elm
unless you expect row_id to be re-used across dates
warped frigate
row_id is unique
fallow elm
when you have a multi column index in looks things up from left to right
warped frigate
hm ok
fallow elm
so you don't get the benefit for date_id in the PK for lookups unless you're also looking up row_id but if you're looking up row_id you wouldn't need to specify date_id
warped frigate
actually it looks like much of the disk space is being used by old files that i don't need anymore
fallow elm
so it doesn't make sense to have a multi-column index on your PK
warped frigate
yeah I only made a composite pk because I thought it would be good practice in case I needed it later
I shouldn't have done that in hindsight
fallow elm
right but they don't work that way. you get no benefit of having a composite pk key if the left most item is unique
fallow elm
no worries
just explaining it
no need to apologize ๐
composite keys do work with subsets but only subsets from left to right
warped frigate
it looks like it's taking so long to make the indexes because it's super slow on the 4 tables with 30m or so rows
so i just killed the process and am redoing it on only the tables with 1m or so rows
is that safe
i don't want corrupted data or anything, but i had to kill it by pid (ubuntu)
fallow elm
you killed the postgres pid?
i'm not sure what happens if you kill the process during index creation
warped frigate
yeah
fallow elm
though postgres is designed to be resiliant to process/host failure
warped frigate
it had 2 processes running at 50% cpu each
oh huh you're discord staff, super cool of you to be helping a newbie
gtg, meeting my boss about my progress on this project in like 10 minutes lmao
fallow elm
good luck. hope the indexes work out
whole mica
i am using google sheets as the database
mainly because it is easy to access
my python programme needs to constantly get the info from the sheet
and to check if anything is changed
sheet = client.open("Database").get_worksheet(1)
while True:
old_data = 0
target = sheet.get_acell('A1')
if target != old_data:
print(f'Target changed from {str(old_data)} to {str(target)}')
old_data = target
time.sleep(1)
however, i always get this error
Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com' for consumer
is there any other ways to replace google sheets?
fallow elm
are you asking for an alternative to google sheets?
whole mica
yep
fallow elm
depends. rdbms are most typical. mysql and postgres being applications you can run, sqlite being a local file that you can use
depends on your needs though
and how you're hosting your application code
whole mica
i want it to be accessible in different computers
fallow elm
you could use something like gcp cloudsql if you want a hosted solution
not sure if that's what you're asking
narrow saffron
i just started mysql and i'm having a problem with the syntax?, im not sure myself as ive checked it and it looked fine
code:
import mysql.connector
from datetime import datetime
# Connects to mysql
db = mysql.connector.connect(
host = "localhost",
user = "user1",
passwd = "---",
database = "testdatabase"
)
# Creates a cursor object
cursor = db.cursor()
# Creates a table inside the accessed database
# VARCHAR = String, smallint = small bit integers
# personID creates a primary key that is unique for every entry
# auto increment changes the ID everytime a new entry is added
# ENUM means you can choose from the given options
cursor.execute("CREATE TABLE test (name VARCHAR(50) NOT NULL, created datetime NOT NULL, gender ENUM('M', 'F', 'O') NOT NULL, id int PRIMARY KEY NOT NULL AUTO_INCREMENT")
File "C:\Users\User\AppData\Local\Programs\Python\Python38\lib\site-packages\mysql\connector\connection_cext.py", line 487, in cmd_query
self._cmysql.query(query,
_mysql_connector.MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1```u missed a ) at the end i think?
narrow saffron
okay
shell ocean
uh
that's easily Googleable
but anyway a "locale" is basically a bunch of settings regarding location-specific behaviour
e.g. in India I believe numbers are grouped in hundreds, so, 10,00,000 instead of 1,000,000
your locale would affect that
pliant cliff
uh huh... i see
pliant cliff
i have postgres...
now how do i use it??
tepid cradle
Did you Google this?
pliant cliff
how to launch it?
tepid cradle
Follow a tutorial to set up the database
pliant cliff
fair
has it changed much?
all the videos seem to be from a year ago
tried looking it up, but i don't understand, how do i get this confluenceuser
and why the password that i set up doesn't work?
reef crag
@whole mica Perhasp you are looking for different platforms like Libre Office has an open sourced sheets that is very close to Office Excel.
whole mica
@reef crag does it have an api for accessing from python?
also, can it be accessed from other computers?
i mean like, does it work like a cloud?
ive never used libre office before ๐
harsh pulsar
@young gyro is purge a keyword in mysql? maybe try quoting the column names
that all looks correct to me :/
in the future it would be helpful if you could post your code as text and not as images
!code-block
delicate fieldBOT
young gyro
@young gyro is
purgea keyword in mysql? maybe try quoting the column names
@harsh pulsar Thank you very much, I have already forgotten this
harsh pulsar
๐
whole mica
@reef crag is there any documentation for uno? cuz i dont think i know how to use it after reading some info online, sorry for ping ๐
torn sphinx
how can i delete a row from a csv file
torn sphinx
@client.command()
async def rank(ctx):
id = ctx.message.author.id
conn = await aiosqlite.connect('money.db')
cursor = await conn.execute("SELECT cash FROM money WHERE id=?", (id,))
records = await cursor.fetchall()
print("id: ", id)
print("Cash: ", records[0])
callablecash = records[0]
callablecash - 10
await conn.execute("UPDATE money SET cash = cash - ? WHERE id=?", (callablecash, id))
trying to make a buy command still need help
deft pasture
anyone here experienced with sql?
would like to grab the placement based on an integer value
torn sphinx
yea but what for
deft pasture
select * from points order by points desc tried something like this, im trying to sort by points
sql
tried to do AS after desc but cant :/
torn sphinx
hhmhmhm
deft pasture
tried max but it only returns 1
narrow saffron
code:
@commands.group(name="gtags", aliases=["gt"], invoke_without_command=True)
async def global_tags(self, ctx, name):
db = sqlite3.connect('main.sqlite')
cursor = db.cursor()
cursor.execute(f"SELECT content FROM global_tags WHERE name = '{name}'")
result = cursor.fetchone()
await ctx.send(result[0])
cursor.close()
db.close()
``` i made a record name "test" with contents "12345" but when i tried to access it by doing !gtags test it gives out a weird error saying `there is no such column as test` but the logic was to get the contents of which the names are identical, any idea why this is happening guys?Dont use f strings for passing data to sql queries
narrow saffron
What should i use instead?
south cobalt
is there like a general consensus as to what's best between like pyodbc or psychpg2
harsh pulsar
https://docs.python.org/3/library/sqlite3.html read from the section that says "Instead, use the DB-APIโs parameter substitution." @narrow saffron
It gives an example
narrow saffron
okay thanks
harsh pulsar
@south cobalt i dont see any reason to use odbc for postgres unless you specifically need to for some reason
south cobalt
well i have never used SQL in my life before and right now im storing a lot of data in some large numpy arrays id like to move it over to SQL but i have no idea how to get started
torn sphinx
@client.command()
async def rank(ctx):
id = ctx.message.author.id
conn = await aiosqlite.connect('money.db')
cursor = await conn.execute("SELECT cash FROM money WHERE id=?", (id,))
records = await cursor.fetchall()
print("id: ", id)
print("Cash: ", records[0])
callablecash = records[0]
callablecash - 10
await conn.execute("UPDATE money SET cash = cash - ? WHERE id=?", (callablecash, id))
``` is my buy command can sombody help it dosent workwhat code can i use to test if my database is prone to SQL injection attacks? and if so, how do i prevent it?
harsh pulsar
@south cobalt maybe sqlite is easier to start with
@narrow saffron if you use parameterized queries you should be safe
if you use string interpolation to construct queries from user-provided data, you are not safe
south cobalt
if i am using sqllite can i use any third party GUI's to view the database like ms sql server studio
harsh pulsar
@torn sphinx what "doesnt work" about it?
torn sphinx
give me a sec
harsh pulsar
@south cobalt yes, DB Browser
narrow saffron
@narrow saffron if you use parameterized queries you should be safe
@harsh pulsar thanks
harsh pulsar
@narrow saffron the post by ImBugle is actually a good example of correct usage
south cobalt
well @harsh pulsar i think a coworker of mine uses ms sql server so i want to be sure they can acess my database from their program
harsh pulsar
no you cannot use ms sql server studio to access a sqlite database
in that case you should probably use ODBC
narrow saffron
oh okay, thanks
harsh pulsar
sql server studio should support generic odbc connections unless i am mistaken
south cobalt
ok TY, is odbc hard learn the basics
torn sphinx
if dose not get red of my 10$ and it gives this error
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: TypeError: unsupported operand type(s) for -: 'tuple' and 'int'
@harsh pulsar
harsh pulsar
oh
narrow saffron
sql = ("INSERT INTO global_tags(name, content) VALUES (%s, %s)", (name, content,))
cursor.execute(sql)``` is this the correct usage for INSERTING with parameterized statements?@torn sphinx records is a list of tuples
each element of records is a tuple corresponding to 1 row
torn sphinx
how would i fix it
harsh pulsar
it looks like you meant to write fetchone instead of fetchall?
torn sphinx
o crap LOL
harsh pulsar
@narrow saffron
query = "INSERT INTO global_tags(name, content) VALUES (%s, %s)"
params = (name, content)
cursor.execute(query, params)
fixed it the fecth part
narrow saffron
okay thanks
torn sphinx
@harsh pulsar
dident work i use the command 2 times it dosent - 10$ cash from the db and after 2 times of running the command it locks down the DB
hollow field
cursor.execute("INSERT INTO product (fields) VALUES (%s);", (Values))
cursor.execute("INSERT INTO productinfo (field) VALUES (%s);", Value)
does anyone know why this gives me an error?
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s)' at line 1
narrow saffron
@hollow field i dont think you need ; after (%s)
hollow field
like the thing is the first line works fine by itself
second line by itself doesn't work
I'm not even sure why
I tried without ; and it still gives an error
narrow saffron
oh wait
do this
query = cursor.execute("INSERT INTO product (fields) VALUES (%s)")
params = (Values)
cursor.execute(query, params)
I'm not sure why that one line doesn't work but this is the way i execute my queries
hollow field
ohh okay
it still gives the same error, I have absolutely no idea why
like the first line works fine by itself
so technically the second should work too
hollow field
query = "INSERT INTO productinfo (field) VALUES (%s)"
params = values[0]
cursor.execute(query, params)
# connection.commit()```like this is the main code and it should work but I'm not sure why
but yea thanks
torn sphinx
@client.command()
async def test(ctx):
id = ctx.message.author.id
conn = await aiosqlite.connect('money.db')
cursor = await conn.execute("SELECT cash FROM money WHERE id=?", (id,))
records = await cursor.fetchone()
print("id: ", id)
print("Cash: ", records[0])
callablecash = records[0]
callablecash - 10
await conn.execute("UPDATE money SET cash = cash - ? WHERE id=?", (callablecash, id))
``` how would i make the data base remove a certain amount of cash like the command that im making i need to remove the users cash for a buy command@torn sphinx do you need to commit a transaction
torn sphinx
ye
@commands.command(brief="Coming Soon!", aliases=["rmwarn"])
@commands.has_permissions(manage_messages=True)
async def delwarn(self, ctx,member : discord.Member, WarnID):
embed = discord.Embed(color=color)
embed.set_footer(text=ctx.guild)
gah = ctx.message.author
with open("warns.csv", 'a', newline='') as deleter, open("warns.csv", "r") as readerr:
reader = csv.DictReader(readerr)
writer = csv.writer(deleter)
if member.top_role <= gah.top_role:
for row in reader:
if WarnID == row["warnid"]:
embed.add_field(name="Warn Deleted!", value=f"Warning {WarnID} deleted successfully!")
writer.writerow(row)
await ctx.send(embed=embed)
else:
embed.add_field(name="Warn Not Found!", value="Try Re-Cecking Warn ID")
await ctx.send(embed=embed)
else:
await ctx.send(f"> You need your role higher than `{member.name}`'s to execute this command. ")```
im trying to delete only one row from my csv file but it isnt working...
is there any specific attribute in csv to use to delete a row?
i tried using deleterow but it wasnt a thing
ye but... i want to tell them they cannot buy this item if they dont have money for it
@harsh pulsar
harsh pulsar
@torn sphinx a csv file is a "stream" of rows, you cant easily insert or delete in the middle
torn sphinx
......
normal igloo
you can read the entire thing from the csv file and then write the entire thing back
torn sphinx
@client.command()
async def test(ctx):
id = ctx.message.author.id
conn = await aiosqlite.connect('money.db')
cursor = await conn.execute("SELECT cash FROM money WHERE id=?", (id,))
records = await cursor.fetchone()
print("id: ", id)
print("Cash: ", records[0])
callablecash = records[0]
callablecash - 10
await conn.execute("UPDATE money SET cash = cash - ? WHERE id=?", (callablecash, id))
await ctx.send("Test")
await conn.commit()
``` how would i make a error so like if they dont have `10$` they cant `buy` the `item`how tho
torn sphinx
if id in cursor > 10:
pass
else:
await ctx.send("Cannot not buy")
nah somthing like that
That could maybe work
@normal igloo
normal igloo
sure, but obviously not pass in that if statement