show what you tried, dont just describe it
#databases
harsh pulsar
ionic marsh
I tried this:
try:
print(await DB.get("SELECT * FROMm memberCurrencyBank"))
except aiosqlite.Error:
print("test 1")
this
async with aiosqlite.connect(cls._DATABASE) as db:
try:
async with db.execute(query) as cursor:
async for row in cursor:
print(row)
except Exception:
print("test 2")
and this
try:
async with aiosqlite.connect(cls._DATABASE) as db:
async with db.execute(query) as cursor:
async for row in cursor:
print(row)
except Exception:
print("test 2")
restive stone
Code ```py
async def get_prefix(client, message):
client.cur.execute("CREATE TABLE IF NOT EXISTS prefix(guild INT, prefix TEXT DEFAULT '-')")
prefix = client.cur.execute(f"SELECT prefix FROM prefix WHERE guild = {message.guild.id}").fetchall()
if prefix == []:
return "-"
return prefix[0]
Error ```
File "Breeze.py", line 37, in get_prefix
prefix = client.cur.execute(f"SELECT prefix FROM prefix WHERE guild = {message.guild.id}").fetchall()
AttributeError: 'NoneType' object has no attribute 'id'
Discord.py - MySQLite3
modest matrix
This is most likely happening because the message is a dm, which doesn't have guild
Or you're not actually passing a message instance to this, but something different
restive stone
ahhh okay how could I make it ignore if it has no guild id? Like if guild.id = none print wutever?
modest matrix
Will still error, because you're trying to access an attribute of something that's none
So you check whether guild is none or not
restive stone
Would this work? ```py
async def get_prefix(client, message):
if guild != None:
client.cur.execute("CREATE TABLE IF NOT EXISTS prefix(guild INT, prefix TEXT DEFAULT '-')")
prefix = client.cur.execute(f"SELECT prefix FROM prefix WHERE guild = {message.guild.id}").fetchall()
if prefix == []:
return "-"
return prefix[0]
Try it out
But no, guild isn't defined
fallow elm
@ionic marsh i think the problem is not that exception handling doesn't work but that aoisql does a logging.exception so it just looks like it doesn't work
async with aiosqlite.connect("test.sqlite") as db:
try:
print("Before Execute")
async with db.execute("Bad Query") as cursor:
print("In Execute")
async for row in cursor:
print(row)
print("After Execute")
except Exception as err:
print("Caught Exception: ", err)
prints
Before Execute
returning exception near "Bad": syntax error
Traceback (most recent call last):
File "/home/gary/.local/python-venvs/aiosqlite/lib/python3.8/site-packages/aiosqlite/core.py", line 95, in run
result = function()
sqlite3.OperationalError: near "Bad": syntax error
Caught Exception: near "Bad": syntax error
if i added
logging.getLogger("aiosqlite").setLevel(logging.CRITICAL)
then i get
Before Execute
Caught Exception: near "Bad": syntax error
orchid yacht
@earnest parcel I found the issue it was something I typed wrong earlier in the code that interacted with it. Sorry for wasting your time
earnest parcel
๐
ionic marsh
@fallow elm holy crap! Thanks man! ๐ I would have never figured that out. I've spent the last hour or so googling and couldn't find anything. Much appreciated! I'll have to do some studying on that Logging library ๐
fallow elm
basically any library can setup a logger so that line is just ratcheting up the level it logs at. logging.exception is a level below critical so it doesn't log it
fallow elm
that one was def a bit tricky
because it did look like it wasn't catching the exception... 
crimson tapir
okay so I'm not sure if its related to security or databases more but here goes, where and how do you store the salt that you used when hashing a password without risking security?
fallow elm
restive stone
Looking for help! How could I make it so it only sends the prefix and not the things holding the prefix?
Code
@commands.command()
@commands.is_owner()
async def prefix_in(self, ctx, guild):
prefix = self.client.cur.execute(f"SELECT prefix FROM prefix WHERE guild={guild}").fetchall()
embed = discord.Embed(
title=f"The Prefix In That Server Is `{prefix}`",
colour=0x00ffff
)
await ctx.send(embed=embed)
odd spade
Another script that opens another file (they work the exact same way)
Both my scripts have the same directory structure:
base
|- cogs
|- database
|- utils
|- my script that opens the file
However, I have an issue: Only one of these scripts woprks
One throws a file not found
One works totally fine
I am very confused, and I can't see any typos
fallow elm
@restive stone you might want fetchone() if you only expect one row. right now prefix is a list of rows
fetchone() will return only a single row and then you index into the row to get the 0th element which is your prefix
odd spade
The file certainly exists
I just checked
Copied and pasted the name
There aren't any typos
Whaaat is happening?
fallow elm
@odd spade do you run the scripts from the same directory?
odd spade
No they are different programs all together
But they are in the same directory structure
There's a base file called like "mycoolprogram" and inside are those 3 folders
And inside utils is the file opener
inside database are the .json
And inside cogs are .py file from which I load the utils script and use it to access file
fallow elm
try printing your current working directory in your script
odd spade
How do I do that?
odd spade
But relative path works great for all my programs except this one
Hang on ill do os.getcwd
fallow elm
or do something like dir_path = os.path.dirname(os.path.realpath(__file__)) and look up the file relative to the python file that way
odd spade
Ok
This is really weird
In the script with issues
Os.getcwd says "C:/Users/Lumiobyte"
Let me do the same thing on my script that's working fine
fallow elm
how are you running your programs
odd spade
Same thing
Same working directory
I run my programs by running them in a powershell
Hang on, I have an idea
Let me check something
Nope, it's not that
Here, look, how I run them:
fallow elm
so then the following exist?
C:/Users/Lumiobyte/database/levelsDB.json
C:/Users/Lumiobyte/database/blacklist.json
No, because one script is on my D:/ drive, and even though the working dir says "C:/" it works fine
The oither is on my E:/ drive
And it's causing issues
fallow elm
if you print os.path.abspath("./database/blacklist.json" or whichever one works what does it show?
fallow elm
relative paths should be relative to the cwd
odd spade
The good file: C:\Users\Lumiobyte\database\levelsDB.json
Wait
Hang on
How tf does the good file even work?
Whaaat?
Maybe because I import it into a file on D:/ the relative path changes?
fallow elm
do you happen to have that file on your c:\ drive? 
odd spade
No I don't, lol
And problem file prints the same thing
Please help this is so confusing 
Why does good file even work
fallow elm
i don't know windows too well but could you humor me and check the result of os.path.exists("C:/Users/Lumiobyte/database/levelsDB.json") ๐
odd spade
Ok, I will
crimson tapir
i think it also depends on the cd of the powershell
odd spade
But If i paste that into windows file explorer, it can't find it
WAIT WHAT
WHAT
WHAT
LOL
My base folder had spaces
Removed them
It was fixed
Wahaaat?????
Never knew that was an issue
Well now I know
@fallow elm all good, we are fixed, what a strange issue it was
Wait
...Nevermind
It's not fixed
Thought it was
Ok, I will do os.path.exists
fallow elm
roller coaster ride
odd spade
Tf's going on
Lmao
Why good file work but this one dont ๐ก
Ah well, will just put in the full path for now
fallow elm
relative paths should open relative to cwd so very weird
odd spade
Yup
fallow elm
you put the print of os.getcwd right before the open?
odd spade
Yep
Apparently it works only if i have
import sys
sys.path.insert(0, './')
But that issue is only with this porogram
None of my other programs need those lines
This is so confusing and it pisses me off
Files are the worst part of coding period
fallow elm
now i'm even more confused
odd spade
Same
odd spade
the sys path insert is only for the files in which i import the file that loads the databases .json
fallow elm
there's something weird about your environment but i don't know windows well enough to troubleshoot this :/
sorry
tepid cradle
I'm also confused about why the above discussion was happening in the Db channel ๐
fallow elm
i've seemed to notice that this is the database and anything related to json channel ๐
or the persistence channel
heh
tepid cradle
Ah, ok
I have a question. When I perform some calculations in Postgresql using window function, say, a cumulative sum, is the actual calculation offloaded to C code?
I don't really understand the internal workings of a DBMS. My primary intention in asking this is to determine whether it's better to fetch data in python and use numpy or do the calculation using SQL Query and fetch the result directly. I understand there can't be a single yes or no answer to this, just wanted to know people's opinions.
torn sphinx
hello there, i am starting out with databases
- what database should i use?
- whats the best way to start with it?
- which is more preffered, DBMS with console or IDE?
rich trout
@tepid cradle as far as I'm aware the calculation happens within the DBMS, so yes. I'd probably suggest doing the calculation there for other reasons too: that way you don't have to transfer all X records over to your program before transforming them and doing the calculation locally
Generally, the idea is to minimize queries, minimize transferred data (if large), keep application logic in the application (this is my opinion and controversial, and basically means "avoid stored procedures")
Of course, if you're doing a hairy calculation, need precise control over something SQL does not expose, etc, you'll need to drag the data out manually. Then it might be worth it to just transfer the records once, and sum/otherops at the same time locally, rather than do a record transfer and querying for sums and windows
tepid cradle
Thanks Bast. Yes, I've noticed that SQL doesn't have some fine control options, like I wanted to return null if any of the four data points being summed up were null. Trivial to achieve in Pandas, but couldn't do in SQL.
split ivy
Hello, I'm using Mysql and getting an error with this code py def modify_setting(self, name, value, user_id): cursor.execute("UPDATE users SET %s = %s WHERE id = %s", (name, value, user_id)) db.commit() The error says Command raised an exception: 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 ''language' = 'french' WHERE id = 364074769102143490' at line 1. The problem here is the string formatting after the SET
wary locust
@split ivy try this:
cursor.execute("UPDATE users SET %s = '%s' WHERE id = %s", (name, value, user_id))
split ivy
Ok i'll try ๐
Same error ''language' = ''french'' WHERE id = 364074769102143490' at line 1 @wary locust
tepid cradle
You can't parameterize column names, %s works only for values.
And don't use quotes around %s, apart from throwing an error, it will also create sql injection vulnerability
split ivy
So how should I do ?
tepid cradle
Why do you want to parameterize the column name? Are you allowing the user to pass the column name to be updated?
split ivy
Yes, but I put many conditions so he can't update everything
tepid cradle
What kind of usecase requires the user to specify the column name?
The only way to handle column name is to use string concatenation using string.format() syntax
split ivy
It's for a discord command, allowing the user to change some values like the language, gender etc
tepid cradle
If you use that for SQL Queries, make sure you validate the column name against information schema before passing it to the query
The column name provided by the user must be already present in the information schema.
Also make sure that the credentials you use for this bot doesn't have delete or drop permissions
split ivy
Ok, ty ๐
tepid cradle
And do it in two steps, prepare the query first using the column name and %s, then use parameters while executing the query
fluid wind
@tepid cradle old rule of thumb for Oracle was
- if it can be done as a SQL query, do it
- if not, use PL/SQL stored procedure
- if not, use Java/C/Python etc
lucid needle
@tepid cradle is it possible to have sqlite3 in pi because i am using sqlite3 in desktop
tepid cradle
It is. But sqlite is not a good idea as it doesn't allow for networking access. So basically you'll have to share your disk and load the entire Db every time. Would recommend MySQL for a client server model
lucid needle
I have important things in sqlite3 and innever tried other db if i change it rn i have to change whole code for MySQL
And im using aiosqlite3 for detail
mellow vapor
It seems that sqlite3.IntegrityError is raised when check fails or such a unique item already exists. Is there a way to tell why the error was raised: due to check or unique column?
tepid cradle
I have important things in sqlite3 and innever tried other db if i change it rn i have to change whole code for MySQL
@lucid needle You can use it, it just won't be a network connection. So if you're working directly on the Raspberry pi, it's fine. Otherwise it will load the entire db over the network even when you might need just a small part of it. Which again, would be fine if your db is 10-15 MB in size.
Over time, I would recommend moving to MySQL or PostgreSQL though
@tepid cradle old rule of thumb for Oracle was
- if it can be done as a SQL query, do it
- if not, use PL/SQL stored procedure
- if not, use Java/C/Python etc
@fluid wind Thanks. I don't know how to write stored procedures. So for me, mostly the choice boils down to writing a query Vs. writing Python code. It's good to know that writing queries is the preferred option.
sacred horizon
lucid needle
@tepid cradle how do i use sqlite3 in pi then?
tepid cradle
You want to use it locally on the pi or over a network on your LAN?
mild vector
Hey, I don't know why but with asyncpg (postgres) all my Selections return None but before that did not happen to me
When I do with asyncpg it returns None but when I do on console it returns what I expected
tepid cradle
It's really not possible to answer questions like this. It's like saying "I'm not able to start my car, please help".
mild vector
But I have no clue...
await cursor.fetchval("SELECT channel_id FROM inter_servers WHERE guild_id = $1", guild_id)
Returns None
console --> SELECT channel_id FROM inter_servers WHERE guild_id = '719640689893441596';
Returns the channel id I wanted to have
tepid cradle
Run the exact same thing in the console
mild vector
That is what I do
mellow vapor
I'm using sqlite3 lib. How do I know if IntegrityError was raised because of Check constraint failure and not because of Unique constraint failure?
fluid wind
Not sure if you can name check constraints. If you can, then by looking at exception message?
fluid wind
E.g. ```sql
sqlite> create table test_table
(
id int not null,
name text not null,
constraint uk_id unique(id),
constraint ck_id check (id > 0)
);
sqlite> insert into test_table(id, name) values (1, 'Bob');
sqlite> insert into test_table(id, name) values (1, 'Alice');
Error: UNIQUE constraint failed: test_table.id
sqlite> insert into test_table(id, name) values (0, 'Alice');
Error: CHECK constraint failed: ck_id
sqlite>```
fluid wind
And ```python
In [25]: try:
...: con.cursor().execute("insert into test_table(id, name) values (?, ?)", (0, 'Alice'))
...: except sqlite3.IntegrityError as ex:
...: error, = ex.args
...: print(f"{error=}")
...:
error='CHECK constraint failed: ck_id'
In [26]: try:
...: con.cursor().execute("insert into test_table(id, name) values (?, ?)", (1, 'Alice'))
...: except sqlite3.IntegrityError as ex:
...: error, = ex.args
...: print(f"{error=}")
...:
error='UNIQUE constraint failed: test_table.id'
How would i make a database of accounts? Im making a tool, and you have to have an account to use it, how would i make it so they can enter a username and password, and if the user and pass match then it will do an action? I'd be filling in the DB like this:
{"id": 1, "user": "Admin", "Password": "Password"}
Current code:
with open('tempdb.json'. 'r') as handle:
ID = (tempdb["id"])
Username = (tempdb["user"])
Password = (tempdb["Password"])
User = input("Please enter your username, to register, DM Gotta2K#5348: ")
Pass = getpass.getpass("Please enter your password: ")
print(User)
print(Pass)
Tempdb:
{"id": 1, "user": "Admin", "Password": "Password"}
normal igloo
Do you care about security at all?
are you using local files or mysql or a different db technology?
lean nebula
I know heroku is bad for hosting, but I had a json file that got reset, so I saw that there's an alternative :sqlite but does sqlite get reset at each restart? I saw PostgreSQL too
I have to use PostgreSQL
runic pilot
heroku's filesystem is ephemeral https://devcenter.heroku.com/articles/active-storage-on-heroku meaning any changes to local files in your code are liable to get wiped
lean nebula
I know
runic pilot
and heroku isn't bad for hosting, it's just specific in it's use case- so it's bad for hosting certain things
lean nebula
That's why json got wiped
runic pilot
sqlite will also be wiped because it's file-based
lean nebula
I saw PostgreSQL as an alternative that should work what do you think? https://devcenter.heroku.com/articles/sqlite3
runic pilot
yup, that would work, they also offer free postgres for up to 10,000 rows
runic pilot
no, postgres and sqlite are entirely different, they only share the fact that they're both SQL databases
lean nebula
Oh okay
fleet blade
If I'm developing a backtester which will test how trading strategies would have performed over past, historical data, which database is easiest to work with python? Is there any disadvantage to simply having CSV files and read them with pandas?
I've heard about timescaledb but not sure how big of a learning curve that would mean for someone with no previous database experienced just some basic pandas knowledge.
runic pilot
how much data is it?
fleet blade
If I do it at a highly granular level such as in milliseconds, math seems to show me that I should expect up to 14 billion rows of data in 10 columns for one year's worth of historical data for one stock. @runic pilot
At the 1 second resolution, I would be working with 14 million rows of data for 1 year's worth of one stock's historical data
About 225k rows at the 1 minute level.
Each dataset is double in size due to options having both sides, the call and put side as opposed to stocks.
So it's safe to say that once I have my complete dataset, with milliseconds I will get upwards of a trillion rows of data, with seconds, up to a billion, while 1 minute will be in the millions.
odd stream
I need a bit of help with MongoDB with Python. I need help on putting stuff in a collection.
import pymongo
client = pymongo.MongoClient()
database = client["Database"]
collection = database["Collection"].find_one()`
collection["Key"] = "Value"```
Thats how it works?I'll ask in a help channel
worthy lark
I have a quick question on queries. I switched over from SQLite3 to MySQL, and they are pretty similar, from what I have changed up, but I'm at a point where I'm having trouble finding an answer. That's with variables. Would someone be able to help me with how it works in MySQL? I'll post the code.
sql = ("UPDATE welcome SET channel_id = ? WHERE guild_id = ?")
val = (channel.id, ctx.guild.id)
cursor.execute(sql, val)
I'll update anything else that might be wrong. I just can't find the answers for the variables.
fallow elm
what's the question? how to do parameterized queries?
that'll depend on the library. which mysql library are you using?
worthy lark
Sorry, meant to include that. mysql-connector-python
fallow elm
i believe you want
sql = "UPDATE welcome SET channel_id = %s WHERE guild_id = %s"
val = (channel.id, ctx.guild.id)
cursor.execute(sql, val)
Ah, I see. So %s is what I'd use instead, and drop the parentheses.
fallow elm
the parens are a noop
but yea you use %s if you're passing a tuple of values or %(somename)s if you're passing values by dictionary
worthy lark
Oh, that makes since. I actually had that page opened and looking over it I was confused by the latter- for the dictionary.
But that make since. Thank you so much for the help @fallow elm
fallow elm
yea, in that case somename matches the key in the dictionary
it's nice if you want to pass the same value to multiple places in the query instead of adding it to the tuple multiple times
worthy lark
I'll have to keep that in mind.
Also, I notice I get Not all parameters were used in the SQL statement which I'm guessing I will need to pass one for my primary key column id
...I think
Or wait... ๐ค It has something to do with the last line in my code block above.
fallow elm
what does your code look like now?
worthy lark
Let me do a pastebin.
Ah, it's a different part in the same function that is the issue.
sql = ("INSERT INTO welcome(guild, channel) VALUES(?,?)")
val = (ctx.guild.id, channel.id)
Because the function was suposed to insert a row if there was none to be updated.
Would this one be a little bit different? Maybe it should be this?
sql = "INSERT INTO welcome(guild, channel) VALUES(%s,%s)"
val = (ctx.guild.id, channel.id)
You have to use %s for placeholders with that library
worthy lark
Right, so my bottom code block should work. I'll test that out.
I got a DataError.
I think I made a mistake. I think my values are strings, not ints. I'll have to read the docs.
slow crystal
Hey, I'm trying to connect to a microsoft sql database (from within some python code) hosted on rds (part of aws). I've tried numerous pacakages and configurations of commands and nothing seems to work, various errors
Googling turned up nothing, is there just no way to directly write to a rds mysql database?
pale elbow
am i dumb or is my postgres somehow just fucking me hardcore?
query = '''
INSERT INTO application_logs (manager, user, type, info)
VALUES ($1, $2, $3, $4)
'''
info = await self.db_handler.fetchval(query, 1, 1, "test", "test")```
`asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "user"`
https://i.imgur.com/jNwVSep.png
oh my gods its because i cant fucking have a field named user
torn sphinx
i am having a problem in postgresql, i downloaded it , but where to query ( dont wanna query using the console)
deep hill
Hi chaps, is anybody available to help me understand why this isn't working?
I have run an SQL query and i am pulling the data I expected (results) however in the format it is in, i cant use it. So i tried to convert it to a list but it is empty? (results2)
await cursor.execute("SELECT PlayerName, SteamID from users WHERE online = 'Yes'")
results = cursor.fetchall()
print(results)
results2 = list(cursor.fetchall())
print(results2)```
output:
<Future finished result=(('BOB', '76561199039141548'), ('AdrianKaa', '76561198868338220'))>
[]
median fable
You'd need to iterate over the results
results = cursor.fetchall()
for row in results:
print(row)
I think should work @deep hill
deep hill
thanks @median fable
that code prints nothing so i assume the list is empty
is there another way to deal with the SQL data?
I need to use the returned fields seperately so I can format it how we need.
tepid crow
It prints nothing? This mean you dont have data stored in your database
deep hill
results printed what i expected
results2 didnt
ive figured it out
i needed to await my cursor.fetchall()
๐
tepid crow
Alrightt!
scarlet atlas
Should I use motor.motor_tornado or motor.motor_asyncio to connect to MongoDB in a discord.py bot?
harsh pulsar
the latter
tired sorrel
CAN I ASK TENSORSLOW QUESTIONS HERE?
tepid cradle
only if you don't shout it out.
But not even then, actually. Tensorflow has nothing to do with databases
torn sphinx
@tepid cradle Hi, I dmed you, not sure if you saw.
smoky turtle
I'm not sure if I should ask this in the databases or web dev channel, but I'm using flask sqlalchemy (sqlite3 i think) for a database and I've realized that it makes sense for each entry to have one more column. Would I be able to add a column after the database has already been created, or would I have to reset it with the additional column?
I probably haven't asked the question very well, but any help is appreciated
gloomy pike
"Update TriviaTop set Correct = Correct + 1, Total = Total + 1, "
"Score = square(Correct) / Total "```
in score = square(correct) / total will ``correct`` and ``total`` be the original ``correct`` and ``total`` or them + 1 because i did that beforelist = [row[0] for row in table]
shell ocean
you generally don't want to call your list list
but yes, that will work
alternatively: [row for row, in table] (note the comma)
gaunt pasture
if i have an integer in my database, how can i add to it or subtract from it?
shell ocean
that is something quite Googleable
gaunt pasture
i googled it and the wrong stuff came up
tepid cradle
I'm not sure if I should ask this in the databases or web dev channel, but I'm using flask sqlalchemy (sqlite3 i think) for a database and I've realized that it makes sense for each entry to have one more column. Would I be able to add a column after the database has already been created, or would I have to reset it with the additional column?
@smoky turtle you can add a column later.
in score = square(correct) / total will
correctandtotalbe the originalcorrectandtotalor them + 1 because i did that before
@gloomy pike the original value
umbral heron
got this...
File "C:\Users\ppswo\Desktop\Stormbot\stormbot.py", line 4, in <module>
bot = commands.Bot(commands_prefix="!")
NameError: name 'commands' is not defined
Process returned 1 (0x1) execution time : 1.487 s
Press any key to continue . . .```I'm trying to grab messages from discord
File "C:\Users\ppswo\Desktop\Stormbot\stormbot.py", line 4, in <module>
bot = commands.Bot(commands_prefix="!")
NameError: name 'commands' is not defined
Process returned 1 (0x1) execution time : 1.487 s
Press any key to continue . . .``` my codeimport sqlite3
bot = commands.Bot(commands_prefix="!")
@Client.event
async def on_message(message):
db= sqlite3.connect("Discord Messages.sqlite")
cursor = db.cursor()
value1 = message.content
value2= None
cursor.execute("INSERT INTO Discord_Messages(messages, images) VALUES(?,?)", (value1, value2,))
db.commit()```*
brazen charm
BTW you really aught to not store every message in a event
Like that will fuck your database as well as be very tetchy with the data protection
dim plinth
I'm in
brazen charm
scarlet bramble
I'm in
slender atlas
if no user can be in more than 2 communities, then yes, for each community have a table with all the users
if users can be in more than 2 communities, then create a table for all users with another columns for the communities names
or ids
idk how you organize that
ye
crisp arch
Im trying to get the password by using this query query(f"SELECT password FROM users WHERE email='{email}';")[0]. But when I return it I get ('secret',). Anyone know how to remove the parentheses?
brazen charm
its a tuple
just [0] would work as tuples have the same indexing rules as lists generally
dusty phoenix
wait we are not in sql right?
crisp arch
ty
brazen charm
wait we are not in sql right?
What
summer citrus
If I was to save someone's discord ID, what would be the best datatype to store it as?
I was thinking BIGINT but not sure if there is a better one
torn sphinx
What's your purpose of saving discord id's?
For like a discord bot storing info about those users?
summer citrus
yeah, but using sql, what datatype would I use to store their ID as? it's a 17/18 digit long integer
wondered if there was a recommended data type for something like that or just a BIGINT
summer citrus
ah they store it as text, might just do it that way
torn sphinx
Well good luck :)
summer citrus
also is there a way to set up the db so that a field can only have a set amount of allowed values to choose from? for example maybe a user has a favourite colour, but they can only pick from green, blue or red, rather than picking something that's not even a colour?
I was trying to use ENUM but I can't seem to get it to work
torn sphinx
Yeah I don't really know ngl
craggy belfry
Is there an easy way to visualize what parts of a DB that a function would affect?
In a flow-chart sort of way
tepid cradle
Function of query?
mellow abyss
app.config['MYSQL_USER'] = 'person'
app.config['MYSQL_PASSWORD'] = 'person'
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_DB'] = 'scicode'
app.config['MYSQL_CURORcLASS'] = 'DictCursor'
mysql = MySQL(app)
cur = mysql.connection.cursor()
table = "CREATE TABLE users ( username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL UNIQUE)"
cur.execute(table)
mysql.connection.commit()
I am trying to set up a mysql db and I get this error
cur = mysql.connection.cursor()
AttributeError: 'NoneType' object has no attribute 'cursor'```gkrou I think so
mellow abyss
what
grim pier
await cursor.execute("SELECT pic_url from weapons WHERE scum_code = %s", (weapon))
Does anyone see a problem here? it returns as "none"
Fairly new to SQL
Just thought id ask incase its something obvious in the formatting?
simple turret
This is probably a beginner question but I'm not finding anything useful on google that actually works. I'm working on a backend site for my webserver that will allow me to easily set up websites and email without having to remember all of the specific commands to make everything work. I'm working on how the database will work first, and I've created a minimal app to experiment with how the database will work. What it's supposed to do is show all "users" on the main page (just the list of usernames in ul>li's) and have a /create/<username> url to create the user to add to the list and then redirect to the main page where it should then show in the list. But I can't get the user added to the database. Instead I get an error with the "id" primary key that I created in the model.
But I thought the key was supposed to be autogenerated by sqlalchemy
Here's the code:
# __init__.py
import os
import flask
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
def create_app():
App = Flask(__name__, static_folder='./public', template_folder='./templates')
print(os.getcwd())
if os.getenv('FLASK_RESET_DATABASE') == '1' and os.path.exists('./data.db'):
print('Removing Database File')
os.environ['FLASK_RESET_DATABASE'] = '0'
os.remove('data.db')
App.config['SECRET_KEY'] = 'SomeSecretKey'
App.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///data.db'
App.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db.init_app(App)
from .blueprints import main
App.register_blueprint(main)
if not os.path.exists('./data.db'):
print('Creating Database File')
db.create_all(app=App)
return App
# model.py
from sqlalchemy import Column, BigInteger, String
from . import db
class User(db.Model):
# __tablename__ = 'user'
id = Column(BigInteger, primary_key=True, autoincrement=True)
username = Column(String(100), unique=True, nullable=False)
password = Column(String(500), nullable=False)
def __repr__(self):
return f'<User {self.username}>'
# blueprints.py
from flask import Blueprint
from flask import render_template, url_for, redirect
from . import db
from .model import User
main = Blueprint('main', __name__)
@main.route('/')
def index():
print(User.query.all())
return render_template('index.html', Users=User.query.all())
@main.route('/create/<username>', methods=['GET'])
def create_user(username):
user = User.query.filter_by(username=username).first()
print(username)
print()
print(user)
print()
if user:
print(f'user {username} already exists')
return redirect(url_for('main.index'))
print('creating new user')
new_user = User(username=username, password='password', id=None)
db.session.add(new_user)
print('user added to the session')
db.session.commit()
print('committing session')
return redirect(url_for('main.index'))
celest yacht
say i'm selecting something from the integrated sqlite3 database in python
import sqlite3
conn = sqlite3.connect('database.db')
c = conn.cursor
c.execute("SELECT value FROM table WHERE value=whatever")
c.fetchall()```c.fetchall returns something like [(value,)]
what's the most elegant way to get value by itself? i'm currently just re.sub-ing the excess out, but this feels wrong
harsh pulsar
...re.sub?
that's a list of tuples, where each tuple is a row
can you show you're using the result?
worthy axle
C:\Users\karim\anaconda3\envs\Discord\pythonw.exe D:/Python/Kian/bot.py
Eingelogged als Error
---------------------
Nachricht: Rythm#0562 schrieb: 213
Ignoring exception in on_message
Traceback (most recent call last):
File "C:\Users\karim\anaconda3\envs\Discord\lib\site-packages\discord\client.py", line 312, in _run_event
await coro(*args, **kwargs)
File "D:\Python\Kian\cogs\Levels.py", line 30, in on_message
user = await self.bot.pg_con.fetch("SELECT * FROM users WHERE user_id = $1 AND guild_id = $2", author_id, guild_id)
File "C:\Users\karim\anaconda3\envs\Discord\lib\site-packages\asyncpg\pool.py", line 542, in fetch
return await con.fetch(query, *args, timeout=timeout)
File "C:\Users\karim\anaconda3\envs\Discord\lib\site-packages\asyncpg\connection.py", line 420, in fetch
return await self._execute(query, args, 0, timeout)
File "C:\Users\karim\anaconda3\envs\Discord\lib\site-packages\asyncpg\connection.py", line 1403, in _execute
query, args, limit, timeout, return_status=return_status)
File "C:\Users\karim\anaconda3\envs\Discord\lib\site-packages\asyncpg\connection.py", line 1411, in __execute
return await self._do_execute(query, executor, timeout)
File "C:\Users\karim\anaconda3\envs\Discord\lib\site-packages\asyncpg\connection.py", line 1423, in _do_execute
stmt = await self._get_statement(query, None)
File "C:\Users\karim\anaconda3\envs\Discord\lib\site-packages\asyncpg\connection.py", line 328, in _get_statement
statement = await self._protocol.prepare(stmt_name, query, timeout)
File "asyncpg/protocol/protocol.pyx", line 163, in prepare
asyncpg.exceptions.UndefinedTableError: Relation ยปusersยซ existiert nicht
why am i getting this error
pls help
user = await self.bot.pg_con.fetch("SELECT * FROM users WHERE user_id = $1 AND guild_id = $2", author_id, guild_id)
this is the code
hello?
please
:/
proven arrow
@worthy axle Read the error it says it right there: asyncpg.exceptions.UndefinedTableError: Relation ยปusersยซ existiert nicht
gloomy pike
is it best practice to use json over adding new columns every time i need to store another data?
torn sphinx
hey, how would i update a column by selecting the title? i cant type 's
https://i.imgur.com/tvxwk9m.png
so if i wanted to select 'a bug's life' title, i cant
i tried to do something like a bug\'s life but it didnt work
sorry if i phrased my question badly, i mean how do i type in apostrophes in queries without breaking stuff?
shell ocean
i tried to do something like
a bug\'s lifebut it didnt work
@torn sphinx IIRCa bug''s lifewould work
torn sphinx
wouldnt that technically be 2 separate strings though?
torn sphinx
ohh
it does work, interesting. thanks
isnt datetime a thing in SQL?
this is SQLite browser so maybe its the program or sqlite?
shell ocean
it is
torn sphinx
hm
shell ocean
but SQLite doesn't support it
torn sphinx
ah
shell ocean
so it kind of fakes it with text
torn sphinx
yea
i guess im just gonna input it as string and then encode/decode that with python
sorry, *text
tepid cradle
is it best practice to use json over adding new columns every time i need to store another data?
@gloomy pike it is best practice not to store JSON in a relational database. Whether you add columns or rows or a separate table altogether will depend on the use case.
gloomy pike
ok
here's my schema for one table
"create table if not exists Timestamps(GuildID int not null, MemberID int not null, Timeunmuted int, Timeunbanned int, Roles JSON, primary key(GuildID, MemberID))"`
``` I want to store their roles when they are muted so that i can give them back when they are unmuted, but the number of roles they could potentially have is indefinite, so i am not sure what to do heretf am i doing wrong?
for mem in ctx.guild.members:
dbcon.execute("INSERT INTO users VALUES (%s, 0, 'Not given','Not given')",(mem.id))
ct += 1
just tosses a syntax error cause of the %
its sqlite3 btw. if i use ? it just says unsupported parameter type
nvm, its cause i forgot a comma
tepid cradle
@gloomy pike create a separate mapping table for users and roles. One column has user id and another has roles. Then you can filter using select * from roles where user_id = 5
gloomy pike
alright
tepid cradle
@gloomy pike Also, it's recommended to have table names and column names in small case with underscore as separator. Just telling for future reference
tepid cradle
You don't need to change it in this, it will break your code. As I said, for future
tepid cradle
I think it's available as an extension
outer spindle
Hey so I'm using sqlite3 and I assigned sqlite3.Row to the connection row factory
Is there a way I can switch back to default tuple based query without closing and opening the connection again?
bronze moat
- Write a simple Python program using an editor on your laptop, e.g., a calculator, a game, etc., but something different . The program should be interactive, i.e., ask the user for input, perform some processing, and present an output. Then copy it onto your RPi via the Ethernet connection. Then ssh into your RPi and run the code.
somber isle
anyone here with aiosqlite plz dm me
tepid cradle
- Write a simple Python program using an editor on your laptop, e.g., a calculator, a game, etc., but something different . The program should be interactive, i.e., ask the user for input, perform some processing, and present an output. Then copy it onto your RPi via the Ethernet connection. Then ssh into your RPi and run the code.
@bronze moat ask in help channels. But before that, google it and try to solve at least a few steps on your own.
why is it not very safe to use f strings to access database information?
@LuckyCloudz#6561 read about SQL Injection attack.
Basically using f-string leaves your code vulnerable to malacious sql being passed with the parameter.
Take the following query
select * from products where product_id = 12
Now suppose you use string concatenation like this
"select * from products where product_id = {}".format(request.product_id}
Where, let's say, request.product_id is the value passed by the user
I, being a helpful ethical hacker, put the following in my request in place of product_id12; drop table products;
Now your query ends up beingselect * from products where product_id = 12; drop table products;
Since SQL interprets;as end of line, it executes both the queries and you end up without a products table in your database.
This is, of course, an oversimplified example. Real attacks can be far more nefarious
torn sphinx
Ok thank you
timid zodiac
Ok so basically I have my facebook bot storing logs in a txt file on my pc right.
If I wanted to use a service to have that file uploaded, then whenever the base file on my pc was changed, the uploaded file automatically would update, what service would that have to be?
(Basically whenever a txt file on my desktop is changed, an online copy is automatically updated with the edits)
tepid cradle
umm.. Dropbox?
timid zodiac
Oh lmfao
lavish pike
I need an Async MySQL connector that is similar to mysql-connector
Can someone recommend one for me?
brazen charm
aiomysql is pretty much the only one
lavish pike
ok ty
dim plinth
hi
marsh tinsel
Does this take 5 randomly? SELECT * FROM guilds LIMIT 5
brazen charm
i imagine it will be the FIFO logic
it will take the first 5 row in the db which will probably the first 5 rows inserted
fallow elm
@marsh tinsel records are usually returned deterministically depending on the index. i know mysql supports something like order by rand() but i'm not sure how effecient it is.
naively done it could require a full table scan. you'd probably want to run a profiler to determine if it's efficient. but also it depends on the database you're using. they're all a bit different
fallow elm
according to this https://www.gab.lc/articles/bigdata_postgresql_order_by_random/ that does in fact pull the whole table which will be bad on a large table
gloomy pike
hello, in sqlite case expression such as https://www.sqlitetutorial.net/sqlite-case/
is there good way to find which case the query executed?
marsh tinsel
How could i do it better?
And is it very bad to take the whole table at the start and loop through it
fallow elm
depends on your data. if the whole table fits in memory it's not too bad but when your dataset starts to exceed memory you have to start pulling from disk so you end up thrashing on the cache
marsh tinsel
Rn the data is really small but it could get pretty big
The looping is meant to check if a server is removed
fallow elm
you shouldn't have to loop over your data to do that
marsh tinsel
How then?
fallow elm
what does your schema look like?
marsh tinsel
Wdym
marsh tinsel
I have 3 columns
1 is bigint and 2 are text
I select all guild ids and then get the guild and if none then delete from db
fallow elm
so 1 is the guild_id?
marsh tinsel
Yes
fallow elm
you can just do select guild_id from foo where guiild_id = 1234567891234567 or whatever
if you get no rows back then it doesn't exist
marsh tinsel
Yes but i donโt have the guild id
I just check if bot has been kicked when it was offline
grim pier
Hey, can anyone shed any light on this? ```Traceback (most recent call last):
File "C:/Users/Jorda/PycharmProjects/BanditBOT-v2.0/BanditBot.py", line 232, in process_adminlogs
async with db.cursor() as cursor:
File "C:\Users\Jorda\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiomysql\connection.py", line 401, in cursor
self._ensure_alive()
File "C:\Users\Jorda\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiomysql\connection.py", line 1064, in _ensure_alive
raise InterfaceError("(0, 'Not connected')")
pymysql.err.InterfaceError: (0, 'Not connected')
[09:54:06] Error in post admin feed function.
[09:54:06] Finished mainLoop
Process finished with exit code -1
await cursor.execute("INSERT INTO admin_logs (command) VALUES (%s)",(line))
await db.commit()
db.close()```ah so you have a list of ids from when you startup and you want to prune guilds you're not a member of anymore @marsh tinsel?
brazen charm
you just want a random guild or what>
brazen charm
if the bot inst connected tho it wont have a guild cache
marsh tinsel
It is
grim pier
Anyone any ideas? ๐
fallow elm
@grim pier did you ignore the reply you got about not being connected?
you'd need to show more code but the error very clearly says you're not connected to the database
grim pier
oh sorry, i never saw ๐
if os.stat(logfile).st_size > 0:
log(cyan("Processing admin logs."))
try:
#connect to SQL here so that only 1 connection is established
db = await aiomysql.connect(
host=config['SQL']['host'],
user=config['SQL']['user'],
password=config['SQL']['password'],
db=config['SQL']['db']
)
log(cyan("Connected to SQL Database."))
except Exception as e:
log(red("Unable to connect to SQL Database."))
log(e)
pass
for line in f:
if len(line) > 50:
async with db.cursor() as cursor:
print(line)
await cursor.execute("INSERT INTO admin_logs (command) VALUES (%s)",(line))
await db.commit()
db.close()
Its printing correctly, saying its connected
fallow elm
have you tried connecting to mysql directly with the same parameters outside of python?
grim pier
Okay so ive got it posting to the DB but still getting the error 0 - not connected on the except block. i dont understand?
marsh tinsel
hmm it seems like the only option is to loop over the guild_ids in table
but if its too big?
grim pier
File "C:/Users/Jorda/PycharmProjects/BanditBOT-v2.0/BanditBot.py", line 233, in process_adminlogs
async with db.cursor() as cursor:
File "C:\Users\Jorda\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiomysql\connection.py", line 401, in cursor
self._ensure_alive()
File "C:\Users\Jorda\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiomysql\connection.py", line 1064, in _ensure_alive
raise InterfaceError("(0, 'Not connected')")
pymysql.err.InterfaceError: (0, 'Not connected')```@normal igloo Got it sorted, thanks for the help ๐
Just a quick question. is it possible to SELECT KILLS and DEATHS from users WHERE SteamID %s...... or do i need to split it down?
so i can store kills and deaths into a var
lean nebula
Okay this might take a while, I'm making a discord bot hosted with heroku, To store data I tried JSON but it gets reset each time the bot is restarted, I searched a bit and found that PostGreSQL was an alternative , i'm trying to store basic data to it can someone walk me through the process, thanks in advance!
hallow mango
green roost
i have no clue what im doing wrong, can someone point me in the correct direction
so basically, im loading data from a sqlite database row by row
connection.execute("SELECT * FROM data LIMIT 1 OFFSET (?)", (str(counter)))
counter counts up
but the offset doesn't change
it just prints out the same thing every time
im getting to the point where theres gonna be a fat hole in my dry wall soon
languid vector
I'm getting connections that aren't closing when using asyncpg and psql and they stay idle for a while and end up breaking my program when it hits max connections
I use a connection pool:
bot.pg_conn = await asyncpg.create_pool(**credentials, max_size=50, max_inactive_connection_lifetime=0)
and this is how I acquire from the pool:
async with bot.pg_conn.acquire() as con:
async with con.transaction():```
I can't figure out where the source of this issue is and was wondering if someone could help me some how debug this and figure it out like for example somehow get what query the connections were running to narrow down where it is or something.
Thank you in advance, please ping me!I think I've got it I just realised max_inactive_connection_lifetime=0 doesn't actually close the connection as it disables the mechanics
grim pier
somehow the if statement isnt working?
normal igloo
If statements don't just not work
Your condition doesn't have the value you think it does
tepid cradle
connection.execute("SELECT * FROM data LIMIT 1 OFFSET (?)", (str(counter)))
@green roost Remove brackets around the question mark
Okay this might take a while, I'm making a discord bot hosted with heroku, To store data I tried JSON but it gets reset each time the bot is restarted, I searched a bit and found that PostGreSQL was an alternative , i'm trying to store basic data to it can someone walk me through the process, thanks in advance!
@lean nebula No, I don't think anyone can walk you through the process, it can take a long time when you're trying to explain it to someone over chat, people don't have that kind of free time.
There are many tutorials available for setting up a database. And heroku already offers a deployed database, so you just have to create the tables and put your data in. So any basic tutorial will get you through
harsh pulsar
Setting up postgres is not an easy task for a novice
Use sqlite instead @lean nebula
tepid cradle
Setting up postgres is not an easy task for a novice
@harsh pulsar Heroku takes care of the deployment part. You can directly get the credentials and start working with the Db. That should be easy to get started with. Can't use SQLite on Heroku
harsh pulsar
ah
burnt turret
im using mongodb/pymongo, getting this error
pymongo.errors.ServerSelectionTimeoutError: localhost:27017: [WinError 10061] No connection could be made because the target machine actively refused it
a similar error comes when i just use the mongo command in cmd
tepid cradle
where is MongoDb installed and where are you connecting to it from?
burnt turret
connecting to it from a python file using pymongo
im not sure where it is installed? i think on my c drive itself
i'd been using it fine, today this error started popping up
eternal raptor
hi, i have a problem, because i'm trying write a code, which will add to my pocket cash in database where id == xxxxxxx, but my code update pocket without sum, it does mean: new_added_cash, should be: pocket+new_added_cash, code:
@commands.command()
@commands.has_role("ADMIN")
async def doladuj(self, ctx, member : discord.Member, ilosc = None):
async with aiosqlite.connect("bazaNinjaSerwer.db") as db:
await db.execute(f"UPDATE ekonomia SET NinjaGold =+ {ilosc} WHERE ID == {member.id}")
query = await db.execute(f"SELECT NinjaGold FROM ekonomia WHERE ID == {member.id}")
query2 = await query.fetchone()
await db.commit()
await ctx.send(f"Pomyลlnie doลadowano uลผytkownikowi {member} {ilosc} NinjaGold-รณw. Posiada teraz {query2} NinjaGold-รณw.")
await db.commit()
@harsh pulsar i know, that you can this xDD sorry for ping
eternal raptor
@tepid cradle
tepid cradle
While I'm flattered that you thought of me, it's generally not a good idea to ping random people. Wait and someone will answer, there are many active users here
eternal raptor
Iโm sorry,
tepid cradle
To answer your question, UPDATE ekonomia SET NinjaGold =+ {ilosc} WHERE ID == {member.id} this doesn't look like a valid query. Maybe you wanted set ninjjagold = ninjagold + ilosc
eternal raptor
Yes, thank you
tepid cradle
+= is not supported in SQL and I've not seen =+ used anywhere before. Did you get that from some tutorial?
also, avoid using f-strings for SQL query. scroll up to see an explanation from me as to why
eternal raptor
ok, i didn't get from some tutorial, because in tutorials were only update on examples, example: UPDATE ekonomia SET NinjaGold = 500 WHERE ID == 1
not with operators
harsh pulsar
@eternal raptor you know better than to use f strings for sql...
i dont think += is valid sql either and that is likely your problem
eternal raptor
@tepid cradle if not f-strings, Must I delete f from start of string?
and correctly code is? : query = await db.execute(f"SELECT NinjaGold FROM ekonomia WHERE ID == (?)", (member.id))
?
tepid cradle
No, that will not work. You need to use query parameterization.
yes, that. And remove the f from here
eternal raptor
ok
@commands.command(pass_context=True)
@commands.has_role("Zarabiacz")
async def mojstankonta(self, ctx):
async with aiosqlite.connect("bazaNinjaSerwer.db") as db:
end = await db.execute("SELECT NinjaGold FROM ekonomia WHERE ID == (?)", (ctx.message.author.id))
await end.fetchone()
wynik = await end.fetchone()
embed = discord.Embed(title="Bank", description=f"{ctx.message.author.name} Posiadasz: {wynik} NinjaGoldรณw ")
await ctx.send(embed=embed)
await db.commit()
parameters are of unsupported type @tepid cradle
tepid cradle
Please wait for someone else to respond. I'm a bit preoccupied with code-jam project whose deadline is in less than 5 hours
harsh pulsar
"don't randomly ping people for help" is advice that applies to everyone, not just gkrou
also sql uses = not ==
(ctx.message.author.id) is not a tuple. (ctx.message.author.id,) is a tuple.
and you don't need (?) just ? although that probably wont cause your problem
eternal raptor
ok
you aren't random people ๐
random person*
@commands.command()
@commands.has_role("ADMIN")
async def doladuj(self, ctx, member : discord.Member, ilosc = None):
async with aiosqlite.connect("bazaNinjaSerwer.db") as db:
await db.execute("UPDATE ekonomia SET NinjaGold + ? WHERE ID = ?", (ilosc, member.id,))
query = await db.execute("SELECT NinjaGold FROM ekonomia WHERE ID = ?", (member.id,))
query2 = await query.fetchone()
await db.commit()
await ctx.send(f" Pomyลlnie doลadowano uลผytkownikowi {member} {ilosc} NinjaGold-รณw. Posiada teraz {query2} NinjaGold-รณw. ")
await db.commit()
near "+": syntax error
harsh pulsar
stop
think about what youre writing
SET NinjaGold + WHERE
does that make sense to you?
eternal raptor
SET NinjaGold + ? (? = ilosc)
i want to sum NinjaGold and ilosc to one whole
@harsh pulsar
harsh pulsar
huh?
eternal raptor
i want to sum NinjaGold and ilosc to one whole
harsh pulsar
and do what?
eternal raptor
sorry for a ping, but i see in your profile, that you were offline (grey char)
harsh pulsar
please just read the docs for UPDATE SET in your particular sql engine
if you still have questions people here can help
you are trying to modify a row in your data right?
or do you just want to select some output?
anyway you need to stop guessing at syntax
eternal raptor
i want to my output will be: example if is:await db.execute("UPDATE ekonomia SET NinjaGold + ? WHERE ID = ?", (ilosc, member.id,)), NinjaGold value = 7, ilosc value = 10 output: 17
i want to: if i send n!doladuj @eternal raptor 600
bot add to my database where id is == my this value to earlier value
example.
earlier value of my pocket was 1200
bot add to this value 600
now value of my pocket is 1800
@harsh pulsar
i learned SQL from SoloLearn
harsh pulsar
explain what you actually want to do
eternal raptor
await db.execute("UPDATE ekonomia SET NinjaGold = (NinjaGold + ?) WHERE ID = ?", (ilosc, member.id,))
this is correctly now?
harsh pulsar
try it
eternal raptor
works
@commands.command(pass_context=True)
@commands.has_role("Zarabiacz")
async def mojstankonta(self, ctx):
async with aiosqlite.connect("bazaNinjaSerwer.db") as db:
member = int(ctx.message.author.id)
end = await db.execute(f"SELECT NinjaGold FROM ekonomia WHERE ID ='{member}'")
await end.fetchone()
wynik = await end.fetchone()
embed = discord.Embed(title="Bank", description=f"{ctx.message.author.name} Posiadasz: {wynik} NinjaGoldรณw ")
await ctx.send(embed=embed)
await db.commit()
BOT: embed: Bank
~|Ninjaaa|~ Posiadasz: None NinjaGoldรณw (why still None, how i have 650 NinjaGolds?)
harsh pulsar
again, why are you using f-strings for sql queries
i dont understand
multiple people have told you not to do that
eternal raptor
oh shit, i forgot change
@commands.command(pass_context=True)
@commands.has_role("Zarabiacz")
async def mojstankonta(self, ctx):
async with aiosqlite.connect("bazaNinjaSerwer.db") as db:
member = int(ctx.message.author.id)
end = await db.execute("SELECT NinjaGold FROM ekonomia WHERE ID ='?'", (member))
await end.fetchone()
wynik = await end.fetchone()
embed = discord.Embed(title="Bank", description=f"{ctx.message.author.name} Posiadasz: {wynik} NinjaGoldรณw ")
await ctx.send(embed=embed)
await db.commit()
now, correctly
I'm sorry.
@harsh pulsar
hybrid remnant
Somebody please correct me if I'm wrong (I don't know how clever aiosqlite is) but I think you need to have end = await db.execute("SELECT NinjaGold FROM ekonomia WHERE ID ='?'", (member,)) because (member) is not a tuple in python. the parser doesn't recongnise that as a tuple, so you need to use (member,) instead
harsh pulsar
^
also you don't need the quotes around ?, the whole point of using query params is that the sql engine does the quoting for you
eternal raptor
@commands.command(pass_context=True)
@commands.has_role("Zarabiacz")
async def mojstankonta(self, ctx):
async with aiosqlite.connect("bazaNinjaSerwer.db") as db:
member = ctx.message.author.id
end = await db.execute("SELECT NinjaGold FROM ekonomia WHERE ID ='?'", (member,))
await end.fetchone()
wynik = await end.fetchone()
embed = discord.Embed(title="Bank", description=f"{ctx.message.author.name} Posiadasz: {wynik} NinjaGoldรณw ")
await ctx.send(embed=embed)
await db.commit()
``` Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.@harsh pulsar
@hybrid remnant
harsh pulsar
you don't need the quotes around ?, the whole point of using query params is that the sql engine does the quoting for you
hybrid remnant
end = await db.execute("SELECT NinjaGold FROM ekonomia WHERE ID = ?", (member,))
eternal raptor
oo
~|Ninjaaa|~ Posiadasz: None NinjaGoldรณw
Why None???
I have 650 NinjaGolds
so...
code is above with this solve
eternal raptor
maybe other python script opened database
or you
if this is it , you must close all
@hybrid remnant
@harsh pulsar
hybrid remnant
Is this about your 'None NinjaGolds'? I'm not entirely sure, but I wonder if the await end.fetchone() on its own is the problem. You're advancing the cursor and just ignoring the value. Maybe you need to do that, but it looks weird to me
If you delete that line, what happens to your result?
eternal raptor
wait
result: command prints nothing, nothing in database\
because end = .... is everywhere. await end.fetchone(), wynik = end.fetchone()
what should i do?
@hybrid remnant
hybrid remnant
Hmm. Honestly I'm not sure. I would have to experiment with aiosqlite to see why it's misbehaving. If you give me 10 minutes I'll have some free time to setup a test system
eternal raptor
ok
hybrid remnant
still working on it. got sidetracked
eternal raptor
Ok
hybrid remnant
@eternal raptor https://repl.it/repls/UnhealthyDiscreteGraphics#main.py
Note lines 25-34 and lines 38-46. They are very similar to your code. The problem is the line await end.fetchone() (not wynik = await end.fetchone() but the one above it).
The one the database library works is you have a "cursor" (like a mouse cursor) that points to a record in the result of your SELECT query. When you execute end.fetchone() it reads the record the cursor is pointing to, then advances the cursor. However, the first time you execute end.fetchone() you are not saving the value anywhere, so it is lost! The second time you execute end.fetchone() (in the line wynik = await end.fetchone()) you have already reached the end of all the data from the query result, so the cursor returns None.
To fix your problem, simply remove the first line that contains await end.fetchone().
eternal raptor
Thank you so much! It works!
heavy warren
hello
is there any appotunity to work with few database files with the same table structure throught the one cursor? like
sourceDB = {
path1 : '...',
path2 : '...',
...
path_n : '...'}
conn = sqlite3.connect( sourceDB.get('path') )
cursor = conn.cursor()
what's the conn = ... line doing? is it connecting to a single database, or is it connecting to multiple databases?
hybrid remnant
I've never seen a python database driver have a single cursor for connecting to multiple databases. I don't think that was the design (you can see the design philosophy for the DB API in this PEP https://www.python.org/dev/peps/pep-0249/ ). If you have multiple database files then you're going to have to manage separate connection resources and separate cursors for each of them.
There may be some libraries in the ecosystem that help facilitate query distribution across multiple databases, but I don't know of any.
heavy warren
ty
languid vector
Hello, I'm having trouble with my connections and connection pool using asyncpg and postgresql
For some reason the db stops fetching queries, to elaborate if I run a select query it doesn't respond and just doesn't do anything until I restart all the connections using
sudo /etc/init.d/postgresql restart
Could someone guide me in the right direction of how I could debug such issue
How I use a connection pool:
bot.pg_conn = await asyncpg.create_pool(**credentials, max_size=50, max_inactive_connection_lifetime=5)
and this is how I acquire from the pool:
async with bot.pg_conn.acquire() as con:
async with con.transaction():```Please ping me
clear reef
So, using discord.py I've set together a warning system. My database stores the warnings (of course), but the only problem I'm having, is that the warnings are not local to the server guild, but to all servers. So you can be warned in a completely different server, and it will still display the same warnings that you've gotten in other servers. How can I make it check for warnings in a guild when displaying all the warnings?
Using SQLite ^^^
proven arrow
@clear reef Have a column which stores the guild_id and then when getting the warnings use this to filter.
clear reef
yes but
let me show
i have a guilds table, and a warns table
the fk_guildId is a foreign key for id in Guilds
robust summit
You'll need to consider that guild foreign key when you determine if a warning needs to be shown
robust summit
if the guild is 123, and the user is abc, check the table to see if there is an entry with guild 123 and user abc. If not, you know they haven't gotten the warning
clear reef
When trying to get the guild id it tells me IntegrityError: UNIQUE constraint failed: Guilds.id
it worked fine before
cant i just put guild.id into the same table
and im guessing ill have to do some in this?
like where guild id is this and bruh
wind sand
so I have been switching from sqlite to asyncpg with python, and I want to know what the equivalent of fetchmany is for asyncpg
does anyone know?
proven arrow
Its just fetch which gives you a list of Record types.
https://magicstack.github.io/asyncpg/current/api/index.html#asyncpg.connection.Connection.fetch
wind sand
oh, thought they meant fetch as in fetch all records
do I need to use the LIMIT clause for that to fetch a number or rows?
proven arrow
Yes
summer pawn
Hey guys, I'm not used to connect database with python. What is the best way to do it? I have created a database on mysql
summer pawn
Hey guys, I'm not used to connect database with python. What is the best way to do it? I have created a database on mysql
The solution was to remove the module mysql-connector and install mysql-connector-python by using pip.
import mysql.connector #Import the connector
mydb=mysql.connector.connect(host="localhost",user="user_here",passwd="password_here",auth_plugin='mysql_native_password') #Connect to the database
blissful narwhal
what does it mean to output sql in this format? Where COMPANY is the table name and ID is a column name?
Oh, I think it is a typo, I think it is supposed to be "NAME.ID" as in "company name"
idk if this is standard though
proper bane
Anyone that understands association objects on SQLAlchemy? 
I'm stuck trying to something that at least in theory, should be really simple
I have an Association object that hosts a Many-to-Many relationship between two other classes. The object has an inner attribute which specifies the nature of the relationship.
I need to take an object on either end and access the list of relationships and that inner attribute of the Association object.
class Attendance(Base):
__tablename__ = "attendances"
id = Column(Integer, primary_key=True, unique=True)
name = Column(CHAR(200), ForeignKey("attendants.name"))
date = Column (Date, ForeignKey("council_sessions.date"))
council = relationship("Council", backref=backref("attendances", cascade="all, delete-orphan"))
attendant = relationship("Attendant", backref=backref("attendances", cascade="all, delete-orphan"))
did_attend = Column(CHAR(1))
class Council(Base):
__tablename__ = "council_sessions"
date = Column(Date, nullable=False, unique=True, primary_key=True)
attendants = relationship("Attendant", secondary="attendance")
class Attendant(base):
__tablename__ = "attendants"
name = Column(CHAR(200), primary_key=True, unique=True, nullable=False)
attended = relationship("Council", secondary="attendance")
The idea is to be able to know what council sessions an Attendant, well, attended. (Getting the did_attend attribute from every relationship)
proper bane
The in operator doesn't exist in SQL
steep lava
It does in postgre
proper bane
there goes my knowledge
:p
However, you can use AND and OR operators, so WHERE guild_id $1 OR $2
should work just fine
And seeing the PostgreSQL docs, WHERE guild_id IN ($1, $2) should work as well
fallow elm
IN exists in mysql, postgres, sqlite, cassandra, scylla, etc. not sure i've used a sql database without it
tepid cradle
Afaik, IN is a part of SQL standard and available across most Dbs
SELECT channel_id, message_id, reaction, role_id, unreact_bool FROM rr_info WHERE guild_id = $1 or $2<-- i want to do something like this to check if the guild_id is equal to either 1 or 2
@torn sphinx this, however, is incorrect. Not just in SQL, but pretty much all languages. No language understands OR conditions like that.
It's alwayswhere guild_id = %s or guild_id = %s
SQL does however provide a range operator called BETWEEN. So you can say where guild_id between 10 and 12
๐
tepid cradle
It is, from what I can find
brazen charm
Raw
tepid cradle
People who prefer ORM don't visit the database channel ๐
green karma
@quartz stump #โ๏ฝhow-to-get-help
quartz stump
@green karma so my question won't be considered as convenient for Topical Chat / Help?
Seems like an easy noob question though ๐ better to move I guess
chrome dock
How I can save data in PostgreSQL?
native vapor
what are you using to interact with PostgreSQL @chrome dock ?
tepid cradle
How I can save data in PostgreSQL?
@chrome dock that's a very broad question.
Have you gone through SQL basics?
chrome dock
@tepid cradle some basics yes
tepid cradle
Do you know INSERT INTO and UPDATE queries?
tepid cradle
Then you already know how to save data to PostgreSQL
hallow cloud
orms can be nice for simpler queries
but the deeper you go in a query, the more you wish you were writing raw sql, imo
tepid cradle
Mostly, the better you know SQL, the more you wish you were writing raw sql.
ORMs feel rather redundant to me. I can already do all that using SQL, why learn a new, less intuitive system just to replicate 30-40% of what I can already do with SQL
chrome dock
But how I do it with python? @tepid cradle
tepid cradle
Now that's a question. See, to get good answers, you have to ask specific questions, broad and vague ones are difficult to answer
You need to use psycopg2 library.
If you want async functionality, you can use asyncpg
lean nebula
can I convert a sqlite3 database to postgre ?
lean nebula
huh
Can someone make me a basic Postgre file with a table ? (and maybe rows) I want to try my database connection
proven arrow
If you see the docs of the DB library you are using they should have examples.
tepid cradle
You can't convert one database to another
They are different software
You can create a postgresql database and copy your data and schema
lean nebula
Okay
hybrid remnant
I have heard of, but never used, a tool called pgloader which claims to be able to migrate from sqlite to postgres. Might be worth seeing if that would help
gloomy pike
some sql errors randomly happen:
"cannot start a transaction within a transaction"
"cannot commit- no transaction is active"
What do they mean? After every update to the table like select, update etc i put a commit() statement after that
aiosqlite3 btw
pure cypress
Are you sure it hasn't already been committed?
It may have been configured to implicitly commit
harsh pulsar
@gloomy pike what library
runic pilot
you don't need a commit() call if you don't have a transaction and you can't create a new transaction if you've already created on and haven't rolled back or committed
harsh pulsar
^
runic pilot
aiosqlite3 btw
harsh pulsar
What do their docs say about making queries
Usually there is a usage example
Sometimes you have to async with a cursor
And maybe async with a transaction as well
The latter seems like the one you should probably use
hybrid remnant
The first one seems pretty unmaintained. I'd probably go with the second one
harsh pulsar
If you share your code maybe someone can help further
Or a small sample program that reproduces your problen
vapid willow
does excel count as a database?
pure cypress
By some definition, yes, I suppose.
Rather, spreadsheets are databases and Excel is a tool to view and manipulate them.
But it's not what people typically mean when referring to databases in the context of software development
torn sphinx
im trying to check if a user's discord snowflake is already in a database, and if not, to add it. is this the best way to do this? is this flawed at all?
for mem in ctx.guild.members:
if not bool(dbconcur.execute("SELECT EXISTS (SELECT * FROM users WHERE Discordsnowflake=?)", (mem.id,))):
dbconcur.execute("INSERT INTO users VALUES (?, 0, 'Not given','Not given')", (mem.id,))
it seemed to be having problems before when there were no rows
wind sand
so I been switching from sqlite3 to postgres, and I have been using an python library for it called aysncpg. But however, I keep getting the error asyncpg.exceptions.UndefinedTableError: relation "settings" does not exist when I try to query some data. But the thing is, the table "settings" does indeed exist. But if I run an query in the console, I get the expected output. Anyone know what could be going on here?
tepid cradle
it seemed to be having problems before when there were no rows
@torn sphinx you can try using a count query instead.select count(*) from users where some_value = ?
This will just return 0 when there are no rows. It's also faster to check as you are not fetching unnecessary data
so I been switching from sqlite3 to postgres, and I have been using an python library for it called aysncpg. But however, I keep getting the error
asyncpg.exceptions.UndefinedTableError: relation "settings" does not existwhen I try to query some data. But the thing is, the table "settings" does indeed exist. But if I run an query in the console, I get the expected output. Anyone know what could be going on here?
@wind sand Can you show the code?
torn sphinx
@tepid cradle thank you, i've just replaced it
also does anyone know how tf to make pycharm piss off with making this yellow? https://i.imgur.com/tlIaI5Z.png
it says this but like, i went into more actions and clicked configure data source, and sqlite is installed
changing project dialect to sqlite doesnt work either
tepid cradle
Db queries return tuples, not values. So your if condition won't work
torn sphinx
tuples containing what?
tepid cradle
tuples containing the values
torn sphinx
oh
so could i check the len of it then?
to see if it returned the value, or what should i do
tepid cradle
No, len will always be 1, because it will either be zero or some positive number
First run the query in the console and check what it returns
Don't try to guess at it
torn sphinx
ok
tepid cradle
or just put a print statement after the query
The other one is a message from IDE, I have no idea about that
It's not a Python error
torn sphinx
yeah, its all this https://i.imgur.com/Azp81Sv.png
wind sand
@tepid cradle what code are you looking for? the code for connecting, my query code, or something else?
tepid cradle
That's a tuple. You can get the value using normal string subsetting using square brackets. tuple_name[0], like this
tepid cradle
@tepid cradle what code are you looking for? the code for connecting, my query code, or something else?
@wind sand Usually the code which generates the error and any code which it is dependent on
wind sand
got it, I will send that over rn
tepid cradle
should i just do .fetchone() ?
@torn sphinx Break that step into 3 separate lines, use an intermediate variable
torn sphinx
wat? im confused now lol
why wouldnt .fetchone() work? that would produce the tuple, no?
tepid cradle
Run the query in one line, fetch the result in the next line using fetchone and store that in a vaariable, then in the next line check for equality
wind sand
here you are, here is the entire section that is causing the issue
torn sphinx
@tepid cradle ty btw
wind sand
if you want everything else that is in that function let me know
@commands.Cog.listener()
async def on_member_update(self, before, after):
cursor = await self.bot.db.acquire()
global guild, member, role, word
guildid = after.guild.id
# detects if an user is streaming and gives an role accordingly
if not before.activity == after.activity:
guild = after.guild
stream = await cursor.fetchrow(f"SELECT live FROM settings WHERE guild = $1", (guildid,))
role = guild.get_role(role_id=stream)
if role is not None:
for activity in after.activities:
if activity.type == discord.ActivityType.streaming:
await after.add_roles(role, reason='User Is Streaming')
else:
await after.remove_roles(role, reason='User is no longer Streaming')
thats what im doing right now, all in one line
@torn sphinx It's just easier to identify what is causing the problem if you run it one step at a time. You can always merge it into a single line once your code is working.
wind sand
error in full
Ignoring exception in on_member_update
Traceback (most recent call last):
File "C:\Users\-----\PycharmProjects\role manager bot\venv\lib\site-packages\discord\client.py", line 312, in _run_event
await coro(*args, **kwargs)
File "C:\Users\-----\PycharmProjects\role manager bot\cogs\events.py", line 196, in on_member_update
stream = await cursor.fetchrow(f"SELECT live FROM settings WHERE guild = $1", (guildid,))
File "C:\Users\-----\PycharmProjects\role manager bot\venv\lib\site-packages\asyncpg\connection.py", line 454, in fetchrow
data = await self._execute(query, args, 1, timeout)
File "C:\Users\sawye\PycharmProjects\role manager bot\venv\lib\site-packages\asyncpg\connection.py", line 1402, in _execute
result, _ = await self.__execute(
File "C:\Users\----\PycharmProjects\role manager bot\venv\lib\site-packages\asyncpg\connection.py", line 1411, in __execute
return await self._do_execute(query, executor, timeout)
File "C:\Users\-----\PycharmProjects\role manager bot\venv\lib\site-packages\asyncpg\connection.py", line 1426, in _do_execute
stmt = await self._get_statement(query, timeout)
File "C:\Users\-----\PycharmProjects\role manager bot\venv\lib\site-packages\asyncpg\connection.py", line 328, in _get_statement
statement = await self._protocol.prepare(stmt_name, query, timeout)
File "asyncpg\protocol\protocol.pyx", line 163, in prepare
asyncpg.exceptions.UndefinedTableError: relation "settings" does not exist
hello anyone??
gentle silo
its an undefined table error
wind sand
am I just being stupid or does no one not know?
gentle silo
your table is undefined
wind sand
I literally just said this a little while ago
so I been switching from sqlite3 to postgres, and I have been using an python library for it called aysncpg. But however, I keep getting the error
asyncpg.exceptions.UndefinedTableError: relation "settings" does not existwhen I try to query some data. But the thing is, the table "settings" does indeed exist. But if I run an query in the console, I get the expected output. Anyone know what could be going on here?
gentle silo
wind sand
yeah
its wired
the other dude was helping me, and he wanted to see the error and my code, but he dipped
:/
gentle silo
are you logging into the same database with the same user
wind sand
yes, and I am using postgress
anyways, as bastion said, it very well could be
nah, sqlite to pg
but ye, that could be the issue. I am using my pycharm to connect to it so I can easily see tables and what not in the same program while I edit my code
ill work on it more and the morning and create that new user and have my pycharm and pg use two different ones. Ill update you two on results here by then. Thanks for your help!
@torn sphinx @gentle silo
ik, its the thought that counts after all :)
tepid cradle
I think asyncpg doesn't require brackets around parameters
gentle silo
excuse sir those are paranthesis
tepid cradle
does not
tepid cradle
yes, it just takes *args, and you have passed a tuple
tepid cradle
@gentle silo didn't help?
heady shoal
How could I get my database latency? (Using mongoDB and pyMongo)
mention me for the answer
meager mulch
any1 works with array datatype?
tepid cradle
!ask
delicate fieldBOT
meager hound
Hello, I have problems with the readout of '.ini' files. The parser does not recognize them, does not show me sections or their contents.
The ini
The code
lean nebula
sql = "INSERT INTO servers (server_id,channel_id) VALUES (%s,%s)"
cur = conn.cursor()
cur.execute(sql,(server_id,channel_id))
conn.commit()``` relation "servers" does not exist, why?datagrip
I set the search path to the appropriate schema too
it's postgre sql
using psycopg2
what am I doing wrong?
hybrid remnant
where are you setting the schema? you are creating the cursor which, IIRC, is where the schema search path needs to be set. however you don't have the statement for stting the schema in your code snippet
lean nebula
print(cur.execute(f"SELECT EXISTS(SELECT 1 FROM servers WHERE server_id = {ctx.guild.id});"))
I'm trying to check if a row exists using psycopg2,but this returns none, any idea?
tepid cradle
cur.execute() genereally doesn't return anything
you have to do cur.fetchone() or cur.fetchall() after that
harsh pulsar
Also don't use f strings for sql
narrow saffron
cursor.execute(f"DELETE FROM levels, welcome, member_count_channel WHERE guild_id = {guild.id}")
sqlite3.OperationalError: near ",": syntax error```
is near "," supposed to mean anything?are you using postgres?
ah, sqlite I see
looks like you can only specify one table at a time https://sqlite.org/lang_delete.html
narrow saffron
ohh
quick nebula
How psql works where did it store my dbs?
analog bobcat
hi guys im a begginer with databases and i had a problem
i cant move this db file from pycharm to here
and i get that error
what can i do?
little flume
im using mysql and where do i find port?
torn sphinx
default port is usually 3306 @little flume
craggy shore
My entire postgresql prod database has gone offline with record with incorrect prev-link it's in a docker image. It's urgent any help is appreciated
torn sphinx
I feel like stack can help you on this one
craggy shore
Ye can't find shit
gloomy pike
is there an sqlite equivalent to similarity() of postgres?
torn sphinx
ok
little flume
k
@torn sphinx when i'm logged into https://mysql.plox.host/phpmyadmin
it works but when i'm not it doesnt
how do i keep it online
torpid moth
gloomy pike
is there an sqlite equivalent to similarity() of postgres?
analog gull
Is there any way to create an inventory system without json and using only sqlite3?
fallow elm
sure. people have designed inventory systems on top of relational databases for years before json was even a thing
do you have a specific question or concern about that?
shell ocean
let's say I have a table with two columns: timestamp and duration, each representing a specific time period. I have two separate queries that give me subsets of this table. how can I get an answer to the question "do the time periods returned by one query intersect at all with any of the time periods returned by the second query"?
tepid cradle
What are the two queries?
usually you'd see something like min(dates from query 2) < max(dates from query 1) OR min(dates from query 1) < max(dates from query 2)
If this returns true, then there's an overlap
shell ocean
I'm using Django, actually, but I'm not even sure if this can be expressed through the ORM
also, what I mean is more something like this:
each timestamp/duration pair represents a booking, at a set time, for a set duration. so, for example, I might have this:
# first query
id | timestamp | duration
1 | 12/08/2020 20:00 | 1:00:00
2 | 13/08/2020 16:00 | 1:30:00
3 | 15/08/2020 12:00 | 1:00:00
# second query
id | timestamp | duration
4 | 12/08/2020 19:00 | 2:00:00
5 | 13/08/2020 10:00 | 1:00:00
in this case, the function I'm thinking of would return True, because the booking at ID 4 starts at 7 PM and lasts for 2 hours, and the booking at ID 1 starts at 8 PM.
on the other hand, say the booking at ID 4 started at 5 PM. there would then be no conflict, and the function would return False.
tepid cradle
how is the duration stored?
it's better to store start time and end time rather than storing the duration
duration is not a standard data type and can be difficult to work with
tepid cradle
oh, ok. In PostgreSQL it is
Then I'd recommend adding the duration to the timestamp to calculate the ending time in your query, then compare the overlap
The other thing you could do is the calculate the duration between one start time and the next and the compare the first duration to check if it less than this calculated duration.
But I think the first approach is cleaner and more intuitive
dusty helm
guys
is mongodb worth it?
i've been studying python for 11 months
now i want to get into a db technology
i saw good some graphs that shows mongodb growing
but idk, i want some opinion from you that are in the area
tepid cradle
It depends a lot on the usecase. Personally, I'm not a big fan of MongoDb. I feel like NoSQL Dbs solve one problem and create 10 in its place (or rather, don't solve 10 of the problems which are already solved by SQL DBs). But there are still quite a few usecases where the flexibility of NoSQL comes in handy.
I would say start with SQL. Moving from SQL to NoSQL is not that difficult, but the other way round might feel more tedious. And eventually, if you're going to be working in real world projects in today's world, at some point you'll have to deal with both
tepid cradle
Not the right channel @open walrus Pandas is not a database
tepid cradle
You can check in #data-science-and-ml or just claim a help channel from the available section
open walrus
Thanks
shell ocean
Then I'd recommend adding the duration to the timestamp to calculate the ending time in your query, then compare the overlap
@tepid cradle yeah, I would do that for a single datetime
but the thing is in this case there are multiple on both side
tepid cradle
I'm not entirely sure what the two tables represent
shell ocean
basically just some rows
effectively
from a table that contains more columns and stuff, but I don't think that's relevant
tepid cradle
I mean to say, I was talking about checking between successive rows because I thought the bookings would be ordered chronologically. I don't understand what your two separate queries are doing
shell ocean
yeah, they're not
basically
they are obtained through filtering some other columns
and the point is to check if any record in one set overlaps with any record in the other set
dusty helm
i know this is not #career-advice but you guys that are in the market, do you have any advice? i have no profissional experience with programming, which kind of technologies should I especialize?
i'm comfortable with python and i'm ok with js, but what to go for?
i like games, i would really enjoy developing games
shell ocean
i know this is not #career-advice but you guys that are in the market, do you have any advice? i have no profissional experience with programming, which kind of technologies should I especialize?
@dusty helm why don't you ask in #career-advice?
marsh tinsel
How could i take items out of a database if TEXT is in a column
torn sphinx
ok
torn sphinx
you mean row?
marsh tinsel
yeah
torn sphinx
take column as a category
ok
so you want to
remove "hello" if the input is yellow?
hello*
marsh tinsel
no?
marsh tinsel
wdym
I want a search bar that searches for words in description
and if there then take out
torn sphinx
ok
SELECT item FROM table WHERE item=?
then make a check
if it is there
from that
marsh tinsel
but it will take if the description is the text
torn sphinx
DELETE FROM table WHERE item=?
marsh tinsel
its "hi" == "hi"
but i want it to take like this description: "Hello hi my" and where ..... = "my"
marsh tinsel
k got it
tepid cradle
You're using very confusing terminology, that's the reason for the other person's confusion.
Don't confuse row and columns, that mistake can be very costly or very frustrating in Databases.
Then when you take out, people will generally interpret it as delete. Use select or extract instead
Finall, to answer your question, what you need is select * from table where column like '%hi%'
% is a wildcard which will match any number of any characters
If you want the search to be case-insensitive, then the answer will change depending on what Db you're using. So you'll have to tell us that
marsh tinsel
i got it to work with ~*
wraith rampart
hello, i am relatively new to sqlite3, how would I get a column and check if a value that i have previously assigned to a variable exists within that column
tepid cradle
i got it to work with ~*
@marsh tinsel that's regex match. Good if you need regex, but otherwise it will be slower than like queries
hello, i am relatively new to sqlite3, how would I get a column and check if a value that i have previously assigned to a variable exists within that column
@wraith rampart Ca you explain a bit more?
wraith rampart
I have a database for my discord bot and I have a column that is the guild id's of all the servers the bot is in, I want to check if the guild id of a server, which I have put into a variable beforehand, exists within the database.
tepid cradle
You can simply do a count for the guild_id
select count(*) from table_name where guild_id = 123456
If it's 0, then it doesn't exist
wraith rampart
ah ok thanks
also, how would I insert a string into a specific cell in the database?
tepid cradle
You can't insert into a specific cell. I understand it must be a confusion about terminology, but insert queries are used to insert new rows.
If you want the value to be put in an existing row, then you use update query. The general syntax is
update table_name set column_name = value where column_name = some_value
wraith rampart
ok
torn sphinx
Im having an error with a flask sqlite3 databse
Is it ok if I post it in here?
database*
Actually I can because it isn't actually interacting with the flask website yet
So I am following this tutorial (https://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-iv-database) it is kind of dated (around 3 years) and I doubt I would get any help from the author. I used the exact same code that was used in the tutorial yet it is returning an sqlalchemy.exc.InvalidRequestError, I looked in the comments and found nothing related. code: ```py
from datetime import datetime
from app import db
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), index=True, unique=True)
email = db.Column(db.String(120), index=True, unique=True)
password_hash = db.Column(db.String(128))
posts = db.relationship('Post', backref='author', lazy='dynamic')
def __repr__(self):
return '<User {}>'.format(self.username)
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
body = db.Column(db.String(140))
timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
def __repr__(self):
return '<Post {}>'.format(self.body)``` The commands that I used in the python terminal ```py
from app import db
from app.models import User, Post
u = User(username='john', email='john@example.com')
db.session.add(u)
db.session.commit()
What does the stack trace that is thrown show? Which line is it pointing to?
magic sentinel
Hello all, I'm not sure if I should be asking this in the discord.py section or the databases section, but I am trying to make a discord bot, and have the following basic cog just as a bare minimum test to see if I could figure out the problem:
from discord.ext import commands
class ModeratorCommands(commands.Cog):
def __init__(self, bot):
self.bot = bot
@commands.command(aliases=["lmr", "listmodroles", "modrole", "listmodrole"])
@commands.guild_only()
async def modroles(self, ctx):
async with self.bot.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute("SELECT role_name FROM mod_roles WHERE guild_id = %s", str(ctx.guild.id))
result = await cur.fetchall()
await cur.close()
if not result:
return
print(result)
def setup(bot):
bot.add_cog(ModeratorCommands(bot))
but I noticed that if I use this command, then go to mysql workbench and update a value, then use the command again, the updated value is not displayed. If I set autocommit = true when making the pool, then it will always show updated values, but I dont understand why it doesn't show updated values if I'm just selecting and not changing anything with the command, I thought commits were just if you changed stuff. And I was also told that workbench commits changes you make to it automatically on its end, so I would have thought it would be reflected in the command. I'm super new at this though so Im probably just doing something really obviously wrong...
pure cypress
It depends on the transaction isolation level
I believe both serializable and repeatable read levels mean that running a select during a transaction means you'll get the same results as when the transaction first began.
So, what's going is that you always have a transaction open when autocommit=false
If autocommit mode is disabled within a session with SET autocommit = 0, the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts.
Therefore, combined with the behaviour of the isolation mode, your select statements are always going to return the same data as when the session was first opened it seems.
You can either do the commit explicitly, autocommit, or change the isolation level https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
magic sentinel
I see, thank you so much for your explanation and advice! It was very helpful!
I will try to do more research on isolation levels and check out that link you included (I was actually reading it just now after your first post) to get a better understanding.
Meanwhile, do you think using autocommit = True is a bad idea? I know it probably depends on the situation, so it may not be something you can just say yes or no to, but I don't think any of my use cases would ever need to rollback the data, so I'm wondering if perhaps I should just leave that on, since it works that way. My only worry was that it would be bad for performance...
Either way though, thank you again!
pure cypress
Generally, leaving autocommit enabled is better. In fact, it's set to on by default for MySQL
It's just more convenient to have it on, and you can still override it by explicitly starting and ending transactions in your SQL queries.
magic sentinel
I see, that is good to know, the opposite of what I expected. Just shows I have a lot to learn lol
Thanks again for taking the time to help
pure cypress
However, you should stay mindful of when you do need to manage a transaction yourself
This is generally when one statement relies on the success of a previous statement.
So if the first statement fails, you'd be able to use transaction control to abort the subsequent statements that rely on it too.
With autocommit enabled, you'd need to explicitly start and end the transaction
magic sentinel
I will make sure to keep that in mind, so that if I come across that I know what to do ๐
analog bobcat
no such table: main.auth_user__old what does this mean?
grim pier
Hey, when creating a new collum on a SQL database using PhpMyAdmin is there a way to automatically fill it with a unique ID say 8 random numbers?
could i just Autoincrement it i suppose and start at like 10000 or something? that would work?
dusty helm
hey, I came here in #databases yesterday asking about mongodb, a guy told me that it solves a question and creates other 10, what are the problems/inconsistencies of mongo?
the idea of it sounded really cool and easy to use, i'm just a bit worried of spending time learning it and it's not worth it
tepid cradle
could i just Autoincrement it i suppose and start at like 10000 or something? that would work?
@grim pier see if the type drop down has something like serial or auto-increment
grim pier
@tepid cradle Either way would work. a unique code or A_I from 10000
aslong as they will never collide which they shouldnt ๐
tepid cradle
the idea of it sounded really cool and easy to use, i'm just a bit worried of spending time learning it and it's not worth it
@dusty helm yeah, I was that guy.
This is a hotly debated issue and I'd recommend doing some reading if you're so worried about it. But as I said yesterday, eventually you're going to have to learn both, so don't worry so much about it. It will never be not worth it because some job of yours is going to require it in future.
However, as I said yesterday, I would recommend starting with SQL because that gives you a better understanding of what a DBMS is supposed to be. While learning SQL DBs, you'll also learn how to properly organise data and how to optimize your queries. These skills can be transferred to Mongo Db.
However, Mongo Db causes you to think less about data structures and optimisation initially, only to make you later realise that it was all the more important because data is fundamentally unstructured in Mongo Db.
Regarding the cool and easy to use sounding part, the marketing of Mongo Db is quite deceptive. It's not any easier to use when compared to SQL DBs. In fact, SQL has such a natural flow that it's far more easy to understand as compared to Mongo DB's query language. Besides, because everyone's data is tabular, and also because SQL has existed for a much longer time, help is easier to find for SQL queries.
The problems that Mongo Db doesn't solve are
- Joins are difficult to achieve
- Date filtering can be nightmare, there's no internal casting of datatypes
- There's very little computational capability. SQL has a lot of analytics built in and many times you can offload simple calculations to the Db engine to make your work faster
- By making you think less about the structure, it encourages you to store data in sub-optimal ways
None of these are flaws which cannot be overcome. But unless your usecase is heavily dependent on unstructured data, there is no reason to choose NoSQL Db over a SQL Db.
So I'll reiterate, eventually you're going to have to learn both, but start with SQL Db
vale lodge
Do you need the row/values if they do exist?
If so, just grab the data. But if you really just need to check if a row exists the canonical solution is
SELECT COUNT(1) FROM table WHERE id=1
Some flavors of SQL may have an EXISTS functionality as well
What is your database?
What type of database?
MySQL, Postgres, SQL server, ...
I'm pretty sure that's incorrect syntax for MySQL's EXISTS functionality
It would be
SELECT EXISTS(SELECT * FROM table WHERE condition)