so whenever you make a connection (after sqlite3.connect) run con.row_factory = sqlite3.Row
#databases
deft badge
then do something like
username = input("Please enter your username: ")
cursor = db.execute('''SELECT * FROM Customer WHERE Username=?''', (username,))
user = cursor.fetchone()
cursor.close()
if not user:
print("User not found")
password = input("Enter your password: ")
if user["password"] == password:
print("Welcome back")
else:
print("Incorrect password")
spare geode
oh ok so you can find from in the row
thats good
user = cursor.fetchone()
if not user:
would u have to do if username not in user?
deft badge
no
spare geode
oh ok i printed user it makes snece
@deft badge the way u did
sql = """create table Customer
(Username text,
Password text,
Spent real,
Postcode text,
Username text primary key)"""
would i need to remove Username text at the top
as otherwise it causess
duplicate column name: Username
spare geode
but then i still have 2 usernames
deft badge
remove the last one
spare geode
yeah got it
ty
NameError: name 'con' is not defined
Do i have to download sqlite3.row?
I used with sqlite3 connect
as db
so i use db for con
got it
Omg this is amazing
thank yousm
spare geode
@deft badge one last question, how would i update Spent
(Want to add a variable to it)
orchid lagoon
Hi, I was wondering if any one can help me get this python script working for creating fastloads
or does any one know of a script that would create a fastload based off CSV data?
spare geode
sql = """UPDATE Customer SET Spent=Spent+totalcost WHERE Username=username"""
sqlite3.OperationalError: no such column: totalcost
@deft badge is there a way around this?
patent glen
cursor.execute("update customer set spent=spent+? where username=?", (totalcost, username))``` probably @spare geodeoh u can go straight through execute
cool
@patent glen I want to add Spent value to total costs
patent glen
that's... the opposite of what it looked like you were doing
set totalcost=totalcost+? and pass spent in the parameters then
patent glen
it's a bit confusing because it's not clear from your question what's meant to be a variable and what's a sql column
dry patio
Ok so I have this
conn.execute('UPDATE tickets SET sales_rep = ? WHERE channel_id = ?', (newrep, channel))```
this is what is inside my database and it's the only row in there at the moment: http://prntscr.com/m62lp4
Here is the error I'm getting: Error binding parameter 0 - probably unsupported type.
newrep is a str and channel = cnys2361, I don't understand why it isn't working.
wait wrong pic
1 sec
patent glen
are you sure it's a str? print(type(newrep))
dry patio
Yes I am
patent glen
what kind of database library are you using? maybe it doesn't want params in a tuple, i know postgresql is weird that way
patent glen
are you sure it's a string not a User/Member object?
dry patio
Yes bc newrep = ctx.author.name which is a string
patent glen
just because you print sometihng and it's not like <whatever kind of object at 0x123123123123> doesn't
hmm weird then
dry patio
not just ctx.author which returns an object.
patent glen
maybe the 0 is wrong, what type is channel
dry patio
Here is how I get the row I want
for row in conn.execute('SELECT * FROM tickets WHERE channel_id=?', (channel, )):
And when I print it, it works and I get a list of all of the items in that row
channe;_id is the first so it's index 0
Here is what I get when I print the 0 index...
Let me show the whole code for it
dry patio
@commands.command()
@commands.has_any_role('Management', 'Sales Representatives')
async def setrep(self, ctx, channel, newrep: discord.Member):
management = discord.utils.get(ctx.guild.roles, name='Management')
database = sqlite3.connect('botdata\\databases\\ticketinfo.db')
conn = database.cursor()
try:
for row in conn.execute('SELECT * FROM tickets WHERE channel_id=?', (channel, )):
ticket = row
sales_rep = ticket[7]
except:
await ctx.send(embed=lang.setrepfalse(ctx, channel))
if sales_rep == 'Not Set':
await ctx.send(embed=lang.setrepsuccess(ctx, newrep, channel))
try:
conn.execute('UPDATE tickets SET sales_rep=? WHERE channel_id=?', (newrep.name, channel))
except Exception as e:
print(e)```
In the database, channel_id = cnys2361 and sales_rep = Not Set. When I execute the command it sends the message but doesn't update the database.@dry patio newrep is a Member object. it's definitely not ctx.author.name as you stated above
dry patio
I had it like that but I just switched it when I sent this
I just tried it again just in case
But
When I add .name, it doesn't print any errors in the exception...
patent glen
probably this time it worked then
dry patio
I check the DB it's still sales_rep = Not Set
I think it's breaking at conn.execute and not reaching the print, I added a print('hi') below conn.execute and it didn't print anything.
checked*
patent glen
is the channel id correct
dry patio
Command I'm executing -setrep cnys2361 @AhdB#0001
patent glen
also try removing the try/except and just letting it crash
dry patio
@patent glen It doesn't, for some reason without excepting it won't print anything on my console 😦
patent glen
sales_rep = ticket[7] are you sure 7 is the correct column number? Maybe you'd be better off with an explicit 'SELECT sales_rep' (and whatever other columns you're using ticket for
like i'm wondering if it's maybe not hitting sales_rep == 'Not Set':
dry patio
Yes when I print 7 it prints not Set
Not Set*
So yes I am 100% sure it's the right row and column and it's the exact cell.
patent glen
well then i don't know what else to check
certainly no idea why it wouldn't be printing
...wait
do you have the database browser open while it's running?
it might be locked
@dry patio
dry patio
It's ok
dry patio
Ok just reposting it so if someone sees it later, they could read it a lot better and maybe be able to help. Also please tag me if you do.
This is my code: http://prntscr.com/m63cvi
It sends the message that it updated the db, but it's not updating it. And here is the command im runnig -setrep cnys2361 @AhdB#0001
robust lagoon
any doubles or anything
commit it
@dry patio
conn.commit()
always need to do that after setting somthin
robust lagoon
lol ive done it before too
dry patio
that got me mad
robust lagoon
youre not the only one
dry patio
Thank you very much
robust lagoon
c:
patent glen
🤦 no idea how i didn't see that
hazy mango
import sqlite3 as sql
db = sql.connect('logins.txt')
cursor = db.cursor()
db.row_factory = sql.Row
#cursor.execute("""DROP TABLE logins""")
#db.commit()
# Insert a table
cursor.execute('''CREATE TABLE IF NOT EXISTS logins(id INTEGER PRIMARY KEY unique, username TEXT unique, password TEXT, admin BOOLEAN)''')
def add_login():
username = input("Enter username:\n")
password = input("Enter password:\n")
try:
cursor.execute('''INSERT INTO logins(id, username, password, admin) VALUES (NULL,?,?,?)''', (username,password,False))
db.commit()
except sql.IntegrityError:
print("That username is already taken")
def login():
username = input("Enter username:\n")
valid = cursor.execute("""SELECT username FROM logins""")
if valid:
user_cursor = cursor.execute("""SELECT * FROM logins WHERE username=?""", (username))
user = user_cursor.fetchone()
user_cursor.close()
if user:
password = input("Enter password:\n")
if user["password"] == password:
print("Logged In!")
else:
print("Invalid Password")
else:
print("Invalid Login")
else:
print("No stored logins.")
mode = input("Enter mode:\n")
if mode == "add":
add_login()
elif mode == "login":
login()
db.close()
```Running the `login()` gives me an error after entering the username: ```py
user_cursor = cursor.execute("""SELECT * FROM logins WHERE username=?""", (username))
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 6 supplied.```This is the code Joseph was helping me with earlier, unfortunately it doesn't seem to work.
slate spire
you need a comma, ie it needs to be (username, ), not (username)
because (username) is the whole string
slate spire
no probs
hazy mango
That fixes that issue, but I've now got another
if user["password"] == password:
TypeError: tuple indices must be integers or slices, not str```nvm, I just changed to corresponding position in the database and it works
How would I view everything that's stored in the database? @slate spire
Well, I've kinda got it
>>> logins = list(cursor.execute("""SELECT * FROM logins"""))
>>> print(logins)
[(1, 'user01', 'pass01', 0)]```I don't want it to contain a tuple though
slate spire
the tuple is your row iirc
hazy mango
yeah, but can I make it a list instead of tuple?
So like
Instead of [(1, 'user01', 'pass01', 0), (2, 'user02', 'pass02', 0)]
It's [[1, 'user01', 'pass01', 0], [2, 'user02', 'pass02', 0]]
???
I mean ig I could manipulate it by converting to a string and doing .replace("(", "[").replace(")", "]") but that's really messy
slate spire
you could cast it to list for each one...
hazy mango
How would I do that?
@slate spire
Using select seems to always put it in a tuple
Even if I do cursor.execute("""SELECT username FROM logins""")
plain radish
if you want the tuples to be lists, it's actually pretty simple thankfully
you can transform a tuple into a list like
data = (1, 'user01', 'pass01', 0)
new_list = list(data)
and it'll end up
[1, 'user01', 'pass01', 0]
That doesn't work though
plain radish
it will if you're doing it to each tuple
hazy mango
Converting it to a list results in [(1, 'user01', 'pass01', 0), (2, 'user02', 'pass02', 0)]
plain radish
you're likely just casting the entire containing list
the outer container
not the inner tuples
hazy mango
logins = list(cursor.execute("""SELECT * FROM logins"""))is what I've got
So what do I need to change?
plain radish
yep that's just casting the outer
hazy mango
for x in logins:
x = list(x)
print(x)```This kinda works, except it does update the value of `x in logins`, just `x`so if you want each item in the outer list
you can just create a new list with a list comp and casts them
logins = [list(i) for i in cursor.execute("""SELECT * FROM logins""")]
something like that should work
they're originally tuples to avoid them being editable
if you want to alter the entries, you can simply cast any entry to a list when you change it at that time too, rather than iterating through the whole thing immediately
hazy mango
That works, ty :~)
So how would I go about changing the values saving in database
AKA change (1, 'user01', 'pass01', 0), (2, 'user02', 'pass02', 0) to (1, 'user01', 'pass01', 1), (2, 'user02', 'pass02', 0)
@plain radish
hazy mango
ty
hazy mango
cursor.execute("""UPDATE logins SET admin = True WHERE username = 'user01'""")?
hazy mango
In this case I only want to update one
I tested it out and it seems to work
I just had to change True to 1
But got another querypy logins = [] if logins: print("Logins are stored") else: print("No logins are stored")Why does this print "Logins are stored" in my program???
I've got logins = [list(i) for i in cursor.execute("""SELECT * FROM logins""")], but when I do print(logins) it outputs []
So I did py if logins: //Do stuff else: //Why does this part run
Even when I do if len(logins) > 0
But when I print len(logins) it outputs 0
da flip
plain radish
no idea actually
hazy mango
def login():
logins = [list(i) for i in cursor.execute("""SELECT * FROM logins""")]
print(logins)
print(len(logins))
username = input("Enter username:\n")
# valid = cursor.execute("""SELECT username FROM logins""")
if len(logins) > 0:
user_cursor = cursor.execute("""SELECT * FROM logins WHERE username=?""", (username,))
user = user_cursor.fetchone()
user_cursor.close()
if user:
password = input("Enter password:\n")
if user[2] == password:
if user[3] == True:
print("Logged in as administrator")
else:
print("Logged in as default")
else:
print("Invalid Password")
else:
print("Invalid Username")
else:
print("No stored logins.")
```This is my full codeif it's truly an empty list, it should be falsey
hazy mango
Exactly
But I dont get why the length statement doesn't work either
len(logins) > 0 is False, but it continues anyway
plain radish
weird
hazy mango
xD
import sqlite3 as sql
db = sql.connect('logins.txt')
cursor = db.cursor()
#cursor.execute("""DROP TABLE logins""")
#db.commit()
# Insert a table
cursor.execute('''CREATE TABLE IF NOT EXISTS logins(id INTEGER PRIMARY KEY unique, username TEXT unique, password TEXT, admin BOOLEAN)''')
#Always ensure that the first account created is admin
cursor.execute("""UPDATE logins SET admin = 1 WHERE id = 1""")
def add_login():
username = input("Enter username:\n")
password = input("Enter password:\n")
try:
cursor.execute('''INSERT INTO logins(id, username, password, admin) VALUES (NULL,?,?,?)''', (username,password,False))
db.commit()
except sql.IntegrityError:
print("That username is already taken")
def login():
logins = [list(i) for i in cursor.execute("""SELECT * FROM logins""")]
print(logins)
if logins:
username = input("Enter username:\n")
user_cursor = cursor.execute("""SELECT * FROM logins WHERE username=?""", (username,))
user = user_cursor.fetchone()
user_cursor.close()
if user:
password = input("Enter password:\n")
if user[2] == password:
if user[3] == True:
print("Logged in as administrator")
else:
print("Logged in as default")
else:
print("Invalid Password")
else:
print("Invalid Username")
else:
print("No stored logins.")
mode = input("Enter mode:\n")
if mode == "add":
add_login()
elif mode == "login":
login()
db.close()
```This is working :~)Is there anything you think I should change/tidy though?
To make it more 'pythonic'
plain radish
well you don't really need logins
as you wouldn't really want to print all of the different login options open like that obviously later when you've stopped testing and devving
hazy mango
I use logins to see if there are stored logins as well
plain radish
you'll likely just want to check the count of the logins to make sure at least one exists
SELECT * FROM logins
this outputs all records in the logins table
if you want the count of all the records, you just do:
SELECT COUNT(*) FROM logins
and it'll return a number
hazy mango
So if there's nothing stored in logins, then the result would be 0?
plain radish
yup
hazy mango
So I can do```py
stored = cursor.execute("""SELECT COUNT(*) FROM logins""")
if not stored:
print("No stored logins")```?
plain radish
yep 😃
hazy mango
Awesome, thanks :3
plain radish
don't forget to return too at that point
you don't need to continue the function if that's the case
and you also don't have to do else: and indent the rest of the code
anytime you can avoid indenting, take the chance because it's better looking
hazy mango
So something like this?
def login():
logins = [list(i) for i in cursor.execute("""SELECT * FROM logins""")]
print(logins)
stored = cursor.execute("""SELECT COUNT(*) FROM logins""")
if not stored:
raise SystemExit(print("No stored logins"))
username = input("Enter username:\n")
user_cursor = cursor.execute("""SELECT * FROM logins WHERE username=?""", (username,))
user = user_cursor.fetchone()
user_cursor.close()
if user:
password = input("Enter password:\n")
if user[2] == password:
if user[3]:
print("Logged in as administrator")
else:
print("Logged in as default")
else:
print("Invalid Password")
else:
print("Invalid Username")```Just habit doing it like that
And okay
btw, when I run in pycharm I get the error sqlite3.OperationalError: database is locked
Any ideas?
plain radish
just leave the descriptive text as is without the print and it'll be fine
maybe you have another process using the file already
check your current processes
hazy mango
Yep... still had it open in IDLE 🤦
plain radish
it won't if there's always something in the table
hazy mango
(still got the logins for debugging)
plain radish
can you print stored
hazy mango
hazy mango
yea
plain radish
nah
you know its a single item
so just get it by index
on the same line we added the fetchone to
you remember how to get a certain item in a list by index?
hazy mango
stored = cursor.execute("""SELECT COUNT(*) FROM logins""").fetchone()[0]?
It works, so gonna take that as a yes 😛
plain radish
hahah good stuff
yeah always check in your own code first
no point asking us usually
because we're just human and it's better to verify with the interpreter first
hazy mango
👌
def change_password(username, password):
while 1:
new_pass = input("Enter new password:\n")
if new_pass == "":
print("Cancelled")
raise SystemExit()
if new_pass == password:
print("That's already your password.")
elif len(new_pass) < 6:
print("Password must be at least 6 characters")
else:
break
cursor.execute("""UPDATE logins SET password = '{}' WHERE username = '{}'""".format(new_pass, username))
db.commit()
print("Successfully changed your password to '{}'".format(new_pass))```This works, but is there a more 'pythonic' way? *(I can't use f-strings as using Python3.4)*@plain radish
plain radish
it's not recommended to set values with str.format. it's insecure
easy to just close the current sql statement in one of the fields and then start a new one that drops all your tables
instead you should use the parameterization that comes with sqlite3 lib
you're using it already in your previous code:
cursor.execute("""SELECT * FROM logins WHERE username=?""", (username,))
so be sure to use it anytime you're setting user given values
it'll sanitize the sql for you
hazy mango
So py cursor.execute("""UPDATE logins SET password =? WHERE username =?""", (new_pass, username))?
@plain radish
plain radish
remember, test your code first and if there's an issue come back with your current code and traceback error
no use asking us first lol
plain radish
plus it gets you into the habit of checking and debugging your own stuff
rather than asking and depending on others by habit or reflex
hazy mango
Yeah, it works :~)
plain radish
👍
hazy mango
Ty :3
try:
xxx
except:
yyy
#This is only run if the try statement succeeded```How would I do something like that?
@plain radish
Need it for this ```py
try:
cursor.execute("""UPDATE logins SET username = ? WHERE username = ?""", (new_username, username))
db.commit()
print("Successfully changed your username to '{}'".format(new_username))
except sql.IntegrityError:
print("That username is already taken")
change_username(username)```
Currently just putting it at bottom of try statement, but was wondering if there's a better way
plain radish
that's fine
hazy mango
So there's not really a better way?
hazy mango
*a more 'pythonic' way
plain radish
that's pretty pythonic
only possible thing is maybe line length
that's being padantic even
hazy mango
I've only got 2 lines that go over the suggested limit of pycharm
plain radish
pythonic just means conforming to the ideals of python and typical usages. the vast majority of that can be captured by reading the zen of python + reading pep8
!t zen
oh wrong command
delicate fieldBOT
hazy mango
:p
plain radish
no probs
hazy mango
Gonna have a break, will be back if I need further assistance 😃
hazy mango
def give_admin():
to_give = input("Enter user to give admin to:\n")
cursor.execute("""UPDATE logins SET admin = ? WHERE username = ?""", (1, to_give,))
db.commit()```So this works, but I want to make it so that if the username `to_give` isn't in the database, instead of doing nothing (it doesn't raise an error so I can't do try/except), it says that it can't find the userI also want to make it so that if the user already has 1 as the admin column, it will output "User is already an admin"
(@me upon response)
plain radish
@hazy mango if you want check it exists, just do a query on it beforehand to check
if it returns nothing, then you can send the error
if it returns something, then continue to update 😃
plain radish
what did you try
plain radish
what have you got atm
hazy mango
admins = list(cursor.execute("""SELECT admin FROM logins"""))
users = list(cursor.execute("""SELECT username FROM logins"""))```But the users will be ('user01',)
So idk how to check it in an if statement
Then for the admins I want to add WHERE username = to_give put that didn't work
plain radish
don't cast the cursor to a list
use the proper retrieval methods
.fetchall() will get all the rows
then you can simply if admins like you tried before
as if it's got nothing, it'll contain no rows, so it'll be an empty list
hazy mango
But I don't get how to implement that stuff
That's where I'm getting lost
I mean
I might have got it actually by looking at what u said earlier
Yep, got it working
Sorry
def give_admin():
while 1:
to_give = input("Enter user to give admin to:\n")
user_cursor = cursor.execute("""SELECT * FROM logins WHERE username= ?""", (to_give,)).fetchall()
print(user_cursor)
try:
if user_cursor[0][3]:
print("{} is already an admin".format(to_give))
else:
break
except IndexError:
print("Unable to find {}".format(to_give))
cursor.execute("""UPDATE logins SET admin = ? WHERE username = ?""", (1, to_give))
db.commit()
print("Successfully gave {} admin".format(to_give))```@hazy mango no stress. sorry i been bouncing between multiple places and failed to respond promptly though
glad you got it sorted though
hazy mango
It's good :~)
btw, am I right in saying that if you have an empty string, it isn't classified as falsey? I've tried checking this and it seems to be the case but just want to confirm
plain radish
empty string is falsey
hazy mango
>>> a = input()
>>> a
''
>>> a == False
False
>>> a == True
False```Ah, I see
plain radish
btw with True and False, they're global objects, and it's best to say instead bool(a) is False
hazy mango
So I can do py while 1: var = input("Enter something") if not bool(var): print("var is false")
plain radish
you don't need to cast to bool when just doing if (not) x
it's already assuming they're comparing as a bool
as if expects a bool
hazy mango
Ah, ok
Other than deleting and then recreating it, is there a way to completely empty a database?
@plain radish
plain radish
ah then that yeah
hazy mango
So would that automatically delete everything, or do you have to add *?
plain radish
just that
hazy mango
kk
plain radish
in other sql dbs, there's a ```sql
TRUNCATE tablename
which effectively wipes a table and keeps the schema
sqlite doesn't have this
hazy mango
What's the schema?
hazy mango
Is that the like id, username, password, admin?
plain radish
yep
hazy mango
But I can't do this with sqlite3?
plain radish
since it doesn't go through each row and wiping them like a ```sql
DELETE tablename WHERE *
as that would be quite slow in a big table
so instead truncate is an optimisation to just simply clear the data
ever since sqlite 3.6.5, there's been the ability to omit the WHERE clause in a DELETE statement
this is known as the "truncate optimisation"
as in, they implemented truncate with the delete statement
if you omit WHERE, it won't go through each row, and it'll simply clear the whole table data in one hit
effectively adding the same feature as other dbs, but without officially adding a TRUNCATE statement
this is good to know mainly because if you ever move away from sqlite, you may go looking for the same feature
and it won't be implemented the same way
hazy mango
Is there anything I can make more 'pythonic'? https://paste.pythondiscord.com/bawihafeci.py
plain radish
yep
if user:
password = input("Enter password:\n")
if user[2] == password:
admin = user[3]
if admin:
print("Logged in as administrator")
option = input("Do you wish to:\n1) Change your password\n2) Change your username\n3) Give admin\n4) "
"Remove admin\n\n")
if option == "1":
change_password(username, password)
elif option == "2":
change_username(username)
elif option == "3":
give_admin()
elif option == "4":
remove_admin()
else:
print("Logged in as default")
else:
print("Invalid Password")
else:
print("Invalid Username")
change this to not nest so much
it's minor, but i guess that's the only thing standing out
plain radish
nesting means how it's indenting in multiple times
since you don't do anything after the elses print their error response
you can simply swap the if statement to check the opposite thing
and then return after the print
and then the rest of the continuing code can be unindented
hazy mango
if not user:
print("Invalid Username")
elif not user[2] == password:
print("Invalid Password")
else:
admin = user[3]
if admin:
print("Logged in as administrator")
...
...
...
else:
print("Logged in as default")```Like this?if not user:
return print("Invalid Username")
if not user[2] == password:
return print("Invalid Password")
admin = user[3]
if not admin:
return print("Logged in as default")
print("Logged in as administrator")
...
...
...
candid pike
Ayone know how the fuck im suppost to connect to a VPS Without its root password
Like wtf
frail dagger
@candid pike Login to the Virtualizor Panel.
frail dagger
They are using Virtualizor I think
candid pike
Idk
ionic pecan
that has nothing to do with databases nor python
candid pike
I was just asking since most people here would use a vps
plain radish
most people in the entire server would use a vps
hazy mango
sqlite3.OperationalError: no such column: recent_time```cursor.execute('''CREATE TABLE IF NOT EXISTS logins(id INTEGER PRIMARY KEY unique, username TEXT unique, password TEXT,
admin BOOLEAN, created_date TEXT, created_time TEXT, recent_date TEXT, recent_time TEXT)''')```is how I create the tableThe table has recent_time as it's last argument, but says it doesn't exist when I try to assign a value to it???
ionic pecan
did you create it previously???
hazy mango
yes
ionic pecan
if not exists
ionic pecan
check the schema in the database file
hazy mango
How do I do that?
hazy mango
I don't have the sqlite terminal thing
ionic pecan
are you sure?
hazy mango
I didn't install it?
ionic pecan
pretty sure that comes with python since its a dependency
hazy mango
Wait actually, it seems to work after I cleared the table again
yeah, it works now
Guess I didn't clear it but I thought I did
ionic pecan
👍
hazy mango
Actually
It doesn't
nvm, forgot to commit
All works :~)
BTW Scragly said earlier to make a function flatter so it's more 'pythonic'. Is this good? ```py
def login():
logins = [list(i) for i in cursor.execute("""SELECT * FROM logins""")]
print(logins)
stored = cursor.execute("""SELECT COUNT(*) FROM logins""").fetchone()[0]
if not stored:
print("No stored logins")
raise SystemExit()
username = input("Enter username:\n")
password = input("Enter password:\n")
user_cursor = cursor.execute("""SELECT * FROM logins WHERE username= ?""", (username,))
user = user_cursor.fetchone()
if not user:
print("Invalid username")
raise SystemExit()
if not user[2] == password:
print("Invalid Password")
raise SystemExit()
admin = user[3]
date_time = dt.now()
time = date_time.strftime('%H:%M:%S')
date = date_time.strftime('%d/%m/%Y')
cursor.execute("""UPDATE logins SET recent_date = ? WHERE username = ?""", (date, username))
cursor.execute("""UPDATE logins SET recent_time = ? WHERE username = ?""", (time, username))
db.commit()
if not admin:
print("\nLogged in as default")
else:
print("\nLogged in as administrator")
option = input("Do you wish to:\n1) Change username\n2) Change password\n3) Give admin\n4) Remove admin\n\n")
if option == "1":
change_username(username)
elif option == "2":
change_password(username, password)
elif option == "3":
give_admin()
elif option == "4":
remove_admin()```
https://discordapp.com/channels/267624335836053506/342318764227821568/533622935332585482 is what I previously had
@ionic pecan
ionic pecan
hmmm, you could try tuple unpacking the row
user_row = cursor.fetchone()
if user_row is None:
# ...
_, _, password, is_admin = user_row
you get the idea
ionic pecan
I‘m on phone right now, gimme like 10 and I‘ll explain
forest sage
Does anyone know of a tool that looks and works like phpMyAdmin, but is not a website php written tool
But just a normal client side program, like workbench
ionic pecan
@hazy mango sorry for the delay. so the thing about dbapi2 is that .fetchone returns a tuple or none, and with tuples its a bit hard to figure out which element is which sometimes
so you can use tuple unpacking to make it more humane for code readers to understand
>>> result = fetchone()
>>> if result is None:
... # custom handling: cannot unpack None
... pass
...
>>> (username, password) = result
>>> username
'bill'
>>> password
'hotdogs33'
like this
quasi holly
What I do is put [0] after fetchone() then use result[0] or result[1] in your example.
hazy mango
Ah, I see. Thanks @ionic pecan
hazy mango
@ionic pecan Got another issue py creation_date = cursor.execute("""SELECT created_date WHERE username = ?""", (to_check,)).fetchone() raises the error saying created_date doesn't exist
cursor.execute('''CREATE TABLE IF NOT EXISTS logins(id INTEGER PRIMARY KEY unique, username TEXT unique, password TEXT,
admin BOOLEAN, created_date TEXT, created_time TEXT, recent_date TEXT, recent_time TEXT)''')```is how I create the table(@me upon response please)
creation_date = list(cursor.execute("""SELECT ? WHERE ? = ?""", ('created_date', 'username', to_check)))
creation_time = list(cursor.execute("""SELECT ? WHERE ? = ?""", ('created_time', 'username', to_check)))
print(creation_date)
print(creation_time)```Doesn't raise any errors, but it prints two empty lists even though the `to_check` is stored in databasedid you recreate the table
hazy mango
Ig I'll try that again
Changing back to the first still gives the error
And still prints two empty lists for the second
@ionic pecan
ionic pecan
no idea what "changing back to the first still gives the error" means
I can't really do much more than tell you the same thing the error is telling you though
hazy mango
Andpy creation_date = cursor.execute("""SELECT created_date WHERE username = ?""", (to_check,)).fetchone() sqlite3.OperationalError: no such column: created_dateis error for the first
@ionic pecan
ionic pecan
yes
I can't really do much more than tell you the same thing the error is telling you
hazy mango
But it is created
So why does it say it isn't
I thought both methods would work, but neither do
How can I get what I want
@ionic pecan
torn sphinx
you're not specifying a table in that query
hazy mango
Omg
I’m so dumb
That works, thanks @torn sphinx
def check_creation():
while 1:
to_check = input("Enter username to check creation of:\n")
users = cursor.execute("""SELECT username FROM logins WHERE username = ?""", (to_check,)).fetchone()
if not to_check:
print("Cancelled")
raise SystemExit()
if not users:
print("Unable to find {}".format(to_check))
else:
break
creation_date = cursor.execute("""SELECT created_date FROM logins WHERE username = ?""", (to_check,)).fetchone()
creation_time = cursor.execute("""SELECT created_time FROM logins WHERE username = ?""", (to_check,)).fetchone()
print("{} created their account on {} at {}".format(to_check, creation_date[0], creation_time[0]))
def check_last_login():
while 1:
to_check = input("Enter username to check last login of:\n")
users = cursor.execute("""SELECT username FROM logins WHERE username = ?""", (to_check,)).fetchone()
if not to_check:
print("Cancelled")
raise SystemExit()
if not users:
print("Unable to find {}".format(to_check))
else:
break
login_date = cursor.execute("""SELECT recent_date FROM logins WHERE username = ?""", (to_check,)).fetchone()
login_time = cursor.execute("""SELECT recent_time FROM logins WHERE username = ?""", (to_check,)).fetchone()
if not login_time[0]:
print("{} has never logged into their account.".format(to_check))
else:
print("{} last logged into their account on {} at {}".format(to_check, login_date[0], login_time[0]))```Anything I can do to make either of these more 'pythonic'?(they both do pretty much the same thing, just one gets created_data and created_time whereas the other gets recent_data and recent_time)
(@me upon response please)
fast adder
HI all. sorry i know this isn't specifically a python question but I'm having an issue understanding how GROUP_CONCAT and GROUP BY work together
I can show the query here
tender hollow
Please do
fast adder
SELECT
Instrument.Name AS Name,
Instrument.SerialNumber,
InstrumentType.Name AS InstrumentType,
InstrumentManufacturer.Name AS Manufacturer,
InstrumentModel.Name AS Model,
Instrument.LabName AS Lab,
Instrument.InstallDate,
InstrumentStatus.Name AS InstrumentStatus,
StatusDataKey.Name AS StatusDataKey,
InstrumentOperation.OperationTime,
CalibrationInterval.Name AS CalibrationIntervalName,
InstrumentOperation.LastCalibration,
PmInterval.Name AS PmIntervalName,
InstrumentOperation.LastPm,
GROUP_CONCAT(MaintenanceKey.Name SEPARATOR ',') AS RequiredMaintenance,
Instrument.Notes AS Notes
FROM Instrument
JOINS...
WHERE Instrument.Name = "Telecaster" GROUP BY Instrument.Name, InstrumentStatus.Name, StatusDataKey.Name, InstrumentOperation.OperationTime, InstrumentOperation.LastCalibration, InstrumentOperation.LastPm;
had to shorten syntax for discord
but basically what happened is, This query works fine without the GROUP_CONCAT(MaintenanceKey.Name SEPARATOR ',') AS RequiredMaintenance, and the GROUP BYs
once i add that line, it tells me i need to GROUP BY certain fields. however it only picks some fields to group by, ie. the fields i have listed
I was wondering why it only requires that i group by those specific fields, and not all of them, or less of them
considering that without the RequiredMaintenance query I get unique rows for the query, ie each line has a unique Instrument.Name and only returns to me as many entries as i have instruments
tender hollow
I'm not sure what you're trying to do
GROUP_CONCAT (from my quick perusing of mysql docs :P ) operates on multiple rows, together
fast adder
oh of course, some context
lets say without the group concat line, i get this result:
Telecaster | 12345 | Guitar | Fender ...
without group concat, and still joining on MaintenanceKey.Name, i get this
Telecaster | 12345 | Guitar | Fender ... | Daily
Telecaster | 12345 | Guitar | Fender ... | Weekly
Telecaster | 12345 | Guitar | Fender ... | Monthly
my desired result is:
Telecaster | 12345 | Guitar | Fender ... | Daily,Weekly,Monthly
so this query i wrote works fine, im just confused as to why i only need to group by SOME of the fields, and not either ALL of them, or just the Instrument.Name is which unique
tender hollow
Ahhhh yes, I see what you mean. Unfortunately, I'm absolutely moronic when it comes to SQL so hopefully someone more knowledgeable than I will pop in here in the near future 
fast adder
ah ok, no problem. yes hopefully!
tender hollow
In the meantime, perhaps this page could be of use to you
http://www.mysqltutorial.org/mysql-group_concat/
Probably not, though :P
There is one, albeit very simple example using both group concat and group by
fast adder
yeah ive been reading these docs but not really helping with my udnerstanding 😦
dry patio
Ok so im using mysql.connector with python and I'm doing this
try:
mydb = mysql.connector.connect(
host="hostip (clovux)",
user="username",
passwd="password",
database="dbname"
)```I get this
1045 (28000): Access denied for user 'username'@'ip and address stuff.provider.com' (using password: YES)
I'm on windows 10
tender hollow
Can you connect to that specific database with those specific credentials otherwise?
dry patio
yeah, but when i run it in code it doesn't work
tacit horizon
I'm running this query:
values = await conn.fetch(query)```What I get back is [<Record json_agg='[{"product":1,"title":"Vacuum","price":15000,"count":10}]'>]. I can can access the Record object itself, so it'll look like <Record json_agg='[{"product":1,"title":"Vacuum","price":15000,"count":10}]'>, but how do I get the JSON?
This is using asyncpg, which is really similar to psycpg, except it's async
slate spire
Record objects are basically like dicts
so you'd do my_record_object['column_i_want']
torn sphinx
yea ik, i was just wondering whether the json_agg made anything different
slate spire
oh, my bad
tacit horizon
this is how I ended up doing it
product = json.loads([value for value in output[0].values()][0])
That gives me the JSON array
dict didn't work, neither does just doing my_record_object["col_i_want"]
Since I wanted the json object/array object that was passed back
plain radish
yeah, json_agg is the aggregate func that turns the record into a json string
if you don't want it as a json string, just don't convert it to json with the aggregate func
just do ```sql
SELECT * FROM product
and you'll be able to access things normally as a record as originally
plain radish
but you're working with the data in the mean time
so have to load the string with the json module anyway
seems weird to do something only to undo it
tacit horizon
All I want to do is return what is in the db as json
Is there an easier way of doing that?
plain radish
are you storing it as json in a file somewhere
plain radish
then why do you want a json string
tacit horizon
Because it's a RESTful API
plain radish
so you send the json via the api?
tacit horizon
if a certain endpoint is hit, I want to return all the data as json
yes, i return the json if a certain endpoint is hit
plain radish
so it's only after checking the data
if that's the case, I'd recommend not converting to json until you're about to send the data, as json.dumps is a c extension and really quick in making your record a json string for sending
you'll avoid unnecessary processing beforehand that way
a normal result returns as a dict-like Record object, which can be json dumped instantly with json.dumps(dict(record))
that way you can check all your conditions beforehand
plain radish
you should try see what happens when you do json.dumps(results) without using the aggregate func
if Record is subclassing dict, it'll likely convert fine
and it'll look exactly the same as your aggregate result
tacit horizon
Hmm, I'll take a look - though I think getting it as json from the db might be faster
Lemme try
tacit horizon
No, json.dumps(result) does not work. Where result is the result of this query: '''SELECT * FROM product'''
plain radish
whats the error?
tacit horizon
uh
let's see
there's no error.. it just doesn't work
I know that sounds strange
but when I do
t = json.dumps(output)
print('This is t:', t)```I get the output correctly, as a list of records
but the 'this is t:' print line is never executed
however, there's no exception being raised, because that would've crashed my server I think
I'm just going to go back to my old way of doing things :X
copper sphinx
How do I get rows with this in "Roles" columm? [] in PostgreSQL
Like if my Roles has a entry with [], how do I only get those entries?
And Roles columm has a date type of json.
ionic pecan
what have you tried already?
copper sphinx
I hae tried to use where = "[]" and where = '[]' without any luck
copper sphinx
Neither of those work
tacit horizon
Show ur query and ur table schema
copper sphinx
Database structure
sql> select "UserID" from "Users" WHERE "Users"."Roles" = [] [2019-01-13 22:43:07] [42601] ERROR: syntax error at or near "[" [2019-01-13 22:43:07] Position: 54
tacit horizon
SELECT UserID FROM Users WHERE Roles = [] or SELECT UserID FROM Users WHERE Roles = '[]'
Try those 2
copper sphinx
sql> SELECT UserID FROM Users WHERE Roles = [] [2019-01-13 22:48:27] [42601] ERROR: syntax error at or near "[" [2019-01-13 22:48:27] Position: 40
sql> SELECT UserID FROM Users WHERE Roles = ''[]'' [2019-01-13 22:51:40] [42601] ERROR: syntax error at or near "[" [2019-01-13 22:51:40] Position: 42
Incorrect syntax.
This is PostgreSQL.
tacit horizon
Yes, what Tizzy said
copper sphinx
sql> SELECT UserID FROM Users WHERE Roles = '[]' [2019-01-13 23:11:34] [42P01] ERROR: relation "users" does not exist [2019-01-13 23:11:34] Position: 20
As said, does not work.
tacit horizon
Oh, you got a different error this time
relations users does not exist
Does doing select * from users work?
copper sphinx
I have to use "Users" for it to work.
select * from "Users" => Works.
There is a difference as you can see.
sql> select * from "Users" [2019-01-13 23:38:25] 500 rows retrieved starting from 1 in 388 ms (execution: 40 ms, fetching: 348 ms) sql> select * from Users [2019-01-13 23:38:45] [42P01] ERROR: relation "users" does not exist [2019-01-13 23:38:45] Position: 15
tacit horizon
Okay, does this work? select UserID from "Users" WHERE "Roles" = [] or select "UserID" from "Users" WHERE "Roles" = []
idk
I'm just throwing stuff out now
dusky cape
Hey, Having an issue with the attached code. In short, it wont add a new user to the User table. I am not sure why it wont due that.
copper sphinx
Nope, both commands end in the same error as in:
sql> select UserID from "Users" WHERE "Roles" = [] [2019-01-13 23:45:58] [42601] ERROR: syntax error at or near "[" [2019-01-13 23:45:58] Position: 45 sql> select "UserID" from "Users" WHERE "Roles" = [] [2019-01-13 23:46:13] [42601] ERROR: syntax error at or near "[" [2019-01-13 23:46:13] Position: 46
tacit horizon
idk then.. google how to query json data then?
copper sphinx
Well, the thing is. It's not perfectly JSON that's stored.
copper sphinx
Problem solved by doing:
sql> select "Users"."UserID", "Users"."Roles" from "Users" where cast("Users"."Roles" as varchar) = '[]'
@tacit horizon
tacit horizon
Huh, cool - thanks for letting me know
copper sphinx
Is there a MSSQL async library?
stable wyvern
is it good or bad that im making a database entirely out of if/else commands?
spare geode
Erm
IS that even possible?
with sqlite3.connect(...) <--- howre you doing this then lmao
ionic pecan
thats a client library, not a database
broken linden
and yes building databases out of if and else statements is very likely gonna end up in a slower database than most others as those use special forms of trees and whatnot
patent glen
using a synchronous database library while using asyncio for other things is possible, but it's something you need to be careful about to avoid deadlocks (especially sqlite3, which can't handle writes on concurrent connections - I mean, it can, but it does it by waiting for the first writer to finish, which isn't going to happen on a single-threaded asyncio app)
ionic pecan
@copper sphinx you generally use quotes around curly braces for arrays in pgsql, so in your case:
WHERE "Roles" = '{}'
sometimes you may need explicit type casts, so append ::VARCHAR[] to that or whatever your col type is
copper sphinx
Did you see that I did solve it yesterday/last night?
ionic pecan
yes, my apologies for trying to suggest the idiomatic approach. stick with your sql
copper sphinx
I was just simply making sure.
Let me try that approach.
sql> select * from "Users" WHERE "Roles" = '{}'
[2019-01-14 19:57:44] [42883] ERROR: operator does not exist: json = unknown
[2019-01-14 19:57:44] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
[2019-01-14 19:57:44] Position: 37
@ionic pecan
ionic pecan
ah, thought its an empty regular array based on „its not perfectly JSON“ - try comparing to '[]'::JSON then (or jsonb if you‘re using that)
copper sphinx
That does not work either.
sql> select * from "Users" WHERE "Roles" = '[]'::JSON [2019-01-14 19:59:56] [42883] ERROR: operator does not exist: json = json [2019-01-14 19:59:56] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. [2019-01-14 19:59:56] Position: 37 sql> select * from "Users" WHERE "Roles" = '{}'::JSON [2019-01-14 20:00:56] [42883] ERROR: operator does not exist: json = json [2019-01-14 20:00:56] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. [2019-01-14 20:00:56] Position: 37 sql> select * from "Users" WHERE "Roles" = '{}'::VARCHAR [2019-01-14 20:01:31] [42883] ERROR: operator does not exist: json = character varying [2019-01-14 20:01:31] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. [2019-01-14 20:01:31] Position: 37 sql> select * from "Users" WHERE "Roles" = '[]'::VARCHAR [2019-01-14 20:02:03] [42883] ERROR: operator does not exist: json = character varying [2019-01-14 20:02:03] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. [2019-01-14 20:02:03] Position: 37
This on the other hand do work:
select "Users"."UserID", "Users"."Roles" from "Users" where cast("Users"."Roles" as varchar) = '[]'
huh, interesting. json is stored as text internally, and jsonb as the parsed structure, but I thought you could compare json to json since that works with jsonb
then the other functions like json_array_length are also not an option, since they add the overhead of parsing the entire thing
so i figure casting it like that is probably the most portable way to do it
hazy mango
Anything I can change to make this more 'pythonic'? https://paste.pythondiscord.com/ukotocubas.py
dull scarab
Maybe not very relevant, but why are you lowering the input at the end for mode if you're checking for numbers anyway?
hazy mango
Not sure 🤔
I think at one point it used a text input
That's probably why
thx
Anything else? (@me upon response)
quasi holly
I was wondering what's the best way to keep a player's inventory if it's infinite. Right now I have a single text cell and I just have to keep each item ID with commas like "1, 2, 3", is there an easier way?
buoyant breach
@quasi holly see "many to many relationship".
spare geode
Hi
dull scarab
Start out your script by making sure you have your table
spare geode
def add_pokemon():
Y=input("Add A Pokemon?")
while Y.startswith("Y"):
try:
Rank=int(input("Rank: "))
if Rank == "E":
quit()
Name=input("Name: ")
Photo=input("Photo: ")
Owned="Not Owned"
sql = """INSERT INTO Pokemon
VALUES (?, ?, ?, ?)"""
with sqlite3.connect("DiscordBot.db") as db:
db.row_factory = sqlite3.Row
cursor = db.cursor()
cursor.execute(sql, (Rank,Name,Photo,Owned))
db.commit()
except:
print("Pokemon or Rank already exsists")
add_pokemon()
That's the first step
just get the connection and py with connection: connection.execute(...) and it should be good
spare geode
def create_pokemon():
con = sqlite3.connect("Discord DB.db")
cursor = con.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables=cursor.fetchall()
if ('Pokemon',) not in tables:
sql = """create table Pokemon
(rank INTEGER primary key,
Name text,
Photo text,
Owned text)"""
with sqlite3.connect("Discord DB.db") as db:
db.row_factory = sqlite3.Row
cursor = db.cursor()
cursor.execute(sql)
db.commit()
This works and created it
dull scarab
but you're doing so much unnecessary stuff
And opening multiple connections to the db
setting row factory, querying the master table
Just fetch the connection, and execute a create table if not exists
and you have your table
then either close the connection and reopen it in a bot or wherever you're using the db
or pass the connection to your bot
spare geode
sql = """create table if not exists Pokemon
(Rank INTEGER primary key,
Name text,
Photo text,
Owned text)"""
with sqlite3.connect("Trying.db") as db:
cursor = db.cursor()
cursor.execute(sql)
db.commit()
Am i doing anything unnessessary here?
dull scarab
You could use a context manager with db to execute and commit with
and you dont need the cursor really
with sqlite.connect("db.db") as conn:
with conn:
conn.execute(...)```This will open a connection, execute a statement, commit the statement, and close the connection
spare geode
def create_pokemon():
sql = """create table if not exists Pokemon
(Rank INTEGER primary key,
Name text,
Photo text,
Owned text)"""
with sqlite3.connect("Trying.db") as conn:
with conn:
conn.execute(sql)
^ works 😃
dull scarab
👍
Also, are you using this with a discord bot?
Then you could look into the few changes needed to migrate to aiosqlite
unsure if you could do the same with the connection context manager there, but just copying the example of the repo it's pretty straight forward to execute something py async with aiosqlite.connect(...) as db: await db.execute('INSERT INTO some_table ...') await db.commit() https://github.com/jreese/aiosqlite#usage
but that's within an async environment, like a discord bot.
spare geode
@dull scarab Its basically a pokemon collecting bot
Like Mudae bot if uve ever seen that
But i shall read into it
spare geode
db.execute("update Pokemon set owned=? where name=? if owned!='Not Owned'", (user.id, Name))
How would i pull something like this off?
dull scarab
Not sure how to do it with sql, but you could select that pokemon first, then update if the field is Not Owned
with python
patent glen
@spare geode I already gave you the answer yesterday, and you decided arbitrarily that you didn't think it looked right and never explained why.
spare geode
I actually did read it but i had to go afterwards
i responded with shouldnt it be !=
Because i dont want it to equal "Not Owned"
Sorry if it came across that i was ungreatful for the help
spare geode
@dull scarab the whole issue, was due to it not being in the same folder
I wanna cry xD
patent glen
@spare geode you want to take the row that was equal to 'Not Owned' before, and change it to something else, right?
think of sql update whatever set owned=whatever where owned='Not Owned'
equivalent to py if x.owned == 'Not Owned': x.owned = whatever
or, for the whole thing, think of the sample I gave as equivalent to py for row in pokemon: if row.name == user.id and row.owned == 'Not Owned': row.owned = Name
get it?
patent glen
the where is the "before", it's the condition you use to decide which rows to update, based on what's in there right now
so it's = because you're updating the ones that are currently not owned
spare geode
Ahhhhh yeah, thank you 😃
patent glen
and i didn't mean to accuse you of being ungrateful
spare geode
Ima try it ill let you know 😛
patent glen
it just seemed like you kind of disappeared after saying the != thing and never really followed up on my explanation, so I wasn't sure if you'd seen it
spare geode
Well, still thank you it was a bit shit of me to leave without saying i have to go
patent glen
its fine
and the reason to not use f-strings, since i didn't explain it properly
if you have something like f"update... set owned='{Name}' where..."
then it'll work for a little while
until Name is something with an apostrophe in it
since then you get sql update pokemon set owned='bad'string' where whatever = whatever
And if it doesn't just have an apostrophe, if someone knows that's what they're doing, then they can do something like
update pokemon set owned='My Name'--' where ...``` and take over all the rows, or ```sql
update pokemon set owned='who cares'; drop table pokemon --' where...``` and delete the whole thingthe -- makes a comment so that the rest of the statement doesn't make a syntax error
spare geode
Oh so if it has a " or ' it will fuck it up basically
patent glen
in that case, we can assume the school did something like py f"insert into Students(Name) values('{Name}')"
so it came out like sql insert into Students(Name) values('Robert'); DROP TABLE Students;--')
spare geode
DROP table?!
And lmao yeah would cause issues
Ima have to look up on drop table
that could come in v handy
Could u have liek
a drop down For each server?
Warning (from warnings module):
File "C:/Users/#/Documents/Discord Game/Full Discord Bot.py", line 58
OwnedOrNah(user)
RuntimeWarning: coroutine 'p.<locals>.OwnedOrNah' was never awaited
async def OwnedOrNah(user):
print (data[iterate][3])
if data[iterate][3]=="Not Owned":
print ("HI")
db.execute("UPDATE Pokemon set Owned=? where Name=?", (user.id, Name))
db.commit
else:
await Client.say(user.mention+"That pokemon already belongs to someone else!")
print(user.id+" tried to take someone elses pokemon")
i awaited the client.say?
tropic zealot
You didn't await the actual function.
await OwnedOrNah()
spare geode
Says invalid syntax?
async def foo():
await bar()
I dont rlly get it :3
(its discord py and databases)
tropic zealot
Asynchronous functions need to be awaited.
Remove the function call at the Red Arrow, then await the two at the black lines.
spare geode
@tropic zealot
deft ingot
the error message says the answer
u use def check(reaction, user), and u should use: async def check(reaction, user)
U can use await stamenet only at async function
if u wanna schedule an async task not in an async function, u can still do asyncio.ensure_future(future,loop)
spare geode
As you see
I call for check at the bottom
When i async def check
it says "check was never awaited"
But where can i await check
deft ingot
so, did u fix the fucntion?
deft ingot
nice 
spare geode
thanks 🐶
deft ingot
i dont really know how discord.py wait_for_reaction works, i use my own api wrapper, so i cant help with that.
but i think
it wants not async function as check
or it shouldnt raise that error
spare geode
Warning (from warnings module):
File "C:\Users\harri\AppData\Local\Programs\Python\Python36-32\lib\site-packages\discord\client.py", line 322
getattr(self, handler)(*args, **kwargs)
RuntimeWarning: coroutine 'p.<locals>.check' was never awaited
Or without async def check
deft ingot
not async function means, it cant contain awaits either
i will check the source code
gonna take some minutes
yeah, it says
@client.event
async def on_message(message):
if message.content.startswith('$react'):
msg = await client.send_message(message.channel, 'React with thumbs up or thumbs down.')
def check(reaction, user):
e = str(reaction.emoji)
return e.startswith(('\N{THUMBS UP SIGN}', '\N{THUMBS DOWN SIGN}'))
res = await client.wait_for_reaction(message=msg, check=check)
await client.send_message(message.channel, '{0.user} reacted with {0.reaction.emoji}!'.format(res))
Checks aren't supposed to be asynchronous.
deft ingot
tropic zealot
If your problems are with discord.py, you should move back to that channel, BTW.
SQL Overlords can't really help with discord.py, amirite?
hazy mango
This works as expected```py
cursor.execute("""UPDATE logins SET password = ? WHERE username = ?""", (new_pass, username))
date_time = dt.now()
time = date_time.strftime('%H:%M:%S')
date = date_time.strftime('%d/%m/%Y')
cursor.execute("""UPDATE logins SET changepass_date = ? WHERE username = ?""", (date, username))
cursor.execute("""UPDATE logins SET changepass_time = ? WHERE username = ?""", (time, username))
db.commit()```
But this only updates username and not changeuser_date/changeuser_time
try:
cursor.execute("""UPDATE logins SET username = ? WHERE username = ?""", (new_username, username))
date_time = dt.now()
time = date_time.strftime('%H:%M:%S')
date = date_time.strftime('%d/%m/%Y')
cursor.execute("""UPDATE logins SET changeuser_date = ? WHERE username = ?""", (date, username)) # doesn't work
cursor.execute("""UPDATE logins SET changeuser_time = ? WHERE username = ?""", (time, username)) # doesn't work
db.commit()
print("Successfully changed your username to '{}'".format(new_username))
except sql.IntegrityError:
print("That username is already taken")
change_username(username)```I can't see any issues
And an error isn't raised, it just doesn't work
And it's not as though I'm forgetting to commit it because a) it has db.commit() and b) username updates
Only difference I can see is it's in a try-except block -- but that shouldn't make a difference?
(@me upon response please)
spare geode
@deft ingot thanks for trying to help 😃
deft ingot
np
hazy mango
Anyone have any ideas with my issue? Still haven't found a resolution :*(
spare geode
How have u connected?
unborn sentinel
Also what's the exact error code, traceback and all
hazy mango
No error
and this is the setup code:```py
import sqlite3 as sql
from datetime import datetime as dt
db = sql.connect('logins.txt')
cursor = db.cursor()
Insert a table
cursor.execute('''CREATE TABLE IF NOT EXISTS logins(id INTEGER PRIMARY KEY unique, username TEXT unique, password TEXT,
admin BOOLEAN, created_date TEXT, created_time TEXT, recent_date TEXT, recent_time TEXT)''')
Always ensure that the first account created is admin
cursor.execute("""UPDATE logins SET admin = 1 WHERE id = 1""")
txt?
hazy mango
That's part of the confusion, it doesn't work but no errors @unborn sentinel
spare geode
o.O
hazy mango
And I just used a random ending - it doesn't matter what the ending is
Just means it doesn't cause it to delete on my PC - my PC deletes files it can't open (such as .db)
spare geode
dont you have to put type unique?
CREATE TABLE table_name(
...,
column_name type UNIQUE,
...
);
Hey can anyone help me....i have a db with 20 entries and i delete entry 19...is there anyway i can automatically change entry 20's id to entry 19?
or just to the next entry number etc
spare geode
Im new to it all too tizzy so I wont be able to help as well as most others , im stumped :3
Blaka do u have like
a column for that
or do u mean the sides
torn sphinx
yeah first column is int literally called entry
torn sphinx
i'm using pysqlcipher and everything works perfect just not that experienced with databases don't know if there is anything inbuilt for this or if i just need to program it myself
spare geode
blabla=[1,2,3,4,5,6,7,8,9,10,11.......]
for item in blabla:
with sqlite3.connect("Trying.db") as conn:
with conn:
conn.execute("UPDATE TABLE set Rank=? where Rank>(NUMBER U WANT TO MOVE DOWN TO)", (Rank))
you could do something like that
you would want to item-1 obcourse and remove the number you removed from the db
carmine heart
@spare geode How much experience do you have with databases?
spare geode
Minimal but if i can help someone ill try to
torn sphinx
didn't think of that...think am just going to implement that anytime i delete an entry it goes through the db and incremently resets the entry number so basically i don't even need to supply a number to move down to...bad practice?
patent glen
@spare geode ok I think you misunderstood my example with a for loop - that was meant as pseudocode to explain what it'd be instead of sql
dull scarab
Does the order of the rows matter @torn sphinx Or do you just want to fill the gap?
patent glen
@torn sphinx I'd probably do update table set rank=rank-1 where rank>? if you wanted to keep the same order. And note that when executing a query with a single parameter, it still needs the parameters as a tuple so conn.execute(..., (rank,))
dull scarab
Might be a silly idea, but I was thinking you could swap the row with the highest rank to replace the one deleted. It would only work if order didn't matter though, and probably has a prettier way of doing so with sql itself
torn sphinx
yeah that is probably the proper way of doing it
patent glen
I don't know offhand how to get the highest rank without doing a separate query, iirc it ends up being some pretty advanced sql
it might be worth considering if you really need the rank stored in the database
torn sphinx
say what if i delete entry 3 tho...then would i just keep iterating through the table with update table set rank=rank-1 where rank>? until they have all moved down an entry number?
patent glen
no, that query will move all of them down. you'd pass 3 in for the ?
torn sphinx
ohhhh
patent glen
But anyway, as I was saying, if instead you store something absolute that the rows can be sorted by (is this just oldest to newest? timestamp then) you could generate the rank with enumerate() when you query the database
torn sphinx
so it does it for all in table not just that entry yeah?
patent glen
it'd do it for all entries that have the rank greater than the number you pass in
in general, select and update statements apply to all rows that are matched by the where clause
torn sphinx
all of them are dated aswel so keeping the entry numbers right keeps the dates in order
spare geode
@patent glen I understood it im pretty sure i was just using code from mine that i knew would be somewhat useful
torn sphinx
and yeah will just be trying to fill the gap(s)
pysqlcipher.dbapi2.OperationalError: near "entry": syntax error
Process finished with exit code 134 (interrupted by signal 6: SIGABRT)```you forgot a space between the table name and set
now, normally i wouldn't recommend this, because it's tempting to do it for data values that you really shouldn't do this with
but in this particular case, for a dynamic table name, probably the best way is to format the query with an f-string: py self.cur.execute(f'UPDATE {self.table_name} SET entry = entry - 1 WHERE entry > ?', (update_num,))
why do you need the table name in a variable though?
torn sphinx
problem was just the space, works perfect now haha thank you! and i use the table name in a few other places aswel...laziness basically
i didn't know you could use operators with sql commands aswel, something new everyday!
craggy coyote
alright guys, very specific problem. If anyone could help, i would appreciate it. Basically using flask-sqlalchemy and have a bunch of properties I have to query constantly and want to see if i can make it a property or something like that to keep my code dry.
basic setup is
class User(db.Model):
tablename = 'user'
id = db.Column(db.Integer, primary_key=True)
artificial = db.Column(db.Boolean, default=False)
artificial = db.Column(db.Boolean, default=True)
.... some other columns
db.session.query(User).filter(User.artifical == True, User.verified_email == True) // then i also do some other verifications
then have to proceed with rest of query. Thing is i'd love to make it something like
db.session.query(User).filter(User.is_not_artificial_and_is_verfied_and_passes_other_booleans == True)
Anyone have experience or have suggestions. I've spent hours looking at hybrid properties and so many other things. It's slowly killing me inside.
hazy mango
Re-post as still haven't found answer - https://stackoverflow.com/questions/54222874/sqlite3-table-not-saving-correctly
ionic pecan
uhhhh
cursor.execute("""UPDATE logins SET username = ? WHERE username = ?""", (new_username, username))
date_time = dt.now()
time = date_time.strftime('%H:%M:%S')
date = date_time.strftime('%d/%m/%Y')
cursor.execute("""UPDATE logins SET changeuser_date = ? WHERE username = ?""", (date, username)) # doesn't work
cursor.execute("""UPDATE logins SET changeuser_time = ? WHERE username = ?""", (time, username)) # doesn't work
db.commit()```you update using the old username
after updating the username
shouldnt lines -2 and -3 pass new_username instead of username
dull scarab
Given it's all in a try it wont execute the db.commit() if it raises any exception btw
hazy mango
But an exception isn't raised
dull scarab
Just saying.
hazy mango
Yeah ik, but I don't want it save if there's an exception
cursor.execute("""UPDATE logins SET changeuser_date = ? WHERE username = ?""", (date, new_username)) # doesn't work
sqlite3.OperationalError: no such column: changeuser_date
```Now I've got this errorAnd it is a valid column
@ionic pecan
ionic pecan
i cant tell you more than the error is telling you. the column does not exist
patent glen
even if you don't commit, the value is still updated in your context.
in addition to the other stuff
gilded narwhal
@hazy mango you can open your db with this https://sqlitebrowser.org/ and see for yourself
hazy mango
Thanks @gilded narwhal
Installing that now so I can see what's wrong
I didn't recreate the table when I added the extra columns 🤦
patent glen
does sqlite support alter table
ok it does for limited scenarios including adding columns
hazy mango
Is there a way to view the rows of the columns in sqlite browser?
@gilded narwhal @patent glen
hazy mango
Thanks :3
try:
date_time = dt.now()
time = date_time.strftime('%H:%M:%S')
date = date_time.strftime('%d/%m/%Y')
cursor.execute("""
UPDATE logins SET (username = ?, changeuser_date = ?, changeuser_time = ?) WHERE username = ?""",
(new_username, date, time, username))
db.commit()
print("Successfully changed your username to '{}'".format(new_username))
except sql.IntegrityError:
print("That username is already taken")
change_username(username)```
Raises the error
```py
(new_username, date, time, username))
sqlite3.OperationalError: near "=": syntax error
@patent glen @gilded narwhal
ionic pecan
drop the ()
sqlite> CREATE TABLE bogus (username, somedate, sometime);
sqlite> INSERT INTO bogus VALUES ("bill", "bob", "nicholas");
sqlite> UPDATE bogus SET (username = "mike", somedate = "bill", sometime = "bob");
Error: near "=": syntax error
sqlite> UPDATE bogus SET username = "mike", somedate = "bill", sometime = "bob";
Thanks, that works :~)
ionic pecan
patent glen
or if there is some other valid syntax "update name set (name"...
ionic pecan
the sqlite statement documentation is super useful
judging from it you can still use braces likeee
copper sphinx
asyncpg.exceptions.UniqueViolationError: duplicate key value violates unique constraint "Users_Profile_pkey"
DETAIL: Key ("UserID")=(157970669261422592) already exists.
If I'm building a table with a user's userID multiple times, I shouln't use PK?
patent glen
wait lol what
ionic pecan
sqlite> UPDATE bogus SET (username, somedate, sometime) = ("bill", "bob", "mickey");
primary keys must be unique and cannot be null
patent glen
sqlite> select*from foo;
1|2|3|4
sqlite> update foo set (b, c) = (5, 6);
sqlite> select*from foo;
1|5|6|4
nifty
ionic pecan
maybe you want a pk over user ID and profile key, assuming that's a junction table
patent glen
i wonder if real databases can do it, that makes some things a lot easier
copper sphinx
Database is like that.
So I need to probably make a new columm for a serial, which will be unique and get rid of the PK on the UserID columm.
patent glen
You'll want a single unique index that include both columns
which may be the primary key or not
(at least, on real databases you can have a multi column primary key, don't know about sqlite)
copper sphinx
This is PostgreSQL.
ionic pecan
you need to run ALTER TABLE ... DROP CONSTRAINT <pk_constraint_name> to drop the user ID pk
ALTER TABLE context DROP CONSTRAINT context_pkey;
ALTER TABLE context ADD PRIMARY KEY (identifier);
oh you are in a real database, for some reason i was assuming sqlite
ionic pecan
like that
patent glen
a primary key can have multiple columns, it'll require a unique combination for all the primary key columns
copper sphinx
Umm, I'm not sure what you mean by that Random.
patent glen
i'm not sure how to explain it better
ionic pecan
you can create so-called "composite primary keys" which contain multiple columns
this means that all columns together must be unique for the entire table, and none of the columns in the composite pk can be null
torn sphinx
What would be the easiest database to use?
copper sphinx
Volcy, that's something I have never came across yet.
ionic pecan
yes, that's why we're trying to explain it to you
this looks like a good explanation https://www.techopedia.com/definition/6572/composite-key
@torn sphinx easiest? sqlite - but sooner or later you'll want to learn something proper and it'll be well worth the time
with sqlite it's just "open some database file", with postgres its a bit more involved. but postgres is very useful
torn sphinx
Mk
copper sphinx
Can you provide me an example of a Composite key table thingy?
ionic pecan
CREATE TABLE voting (
QuestionID NUMERIC,
MemberID NUMERIC,
PRIMARY KEY (QuestionID, MemberID)
);```straight from stackoverflow
copper sphinx
Oh, wow.
ionic pecan
constraints like this are pretty useful for data integrity - for example, I have the following indices for a table on my discord bot:
-- One guild may have at most one entry with the type `delete_invocation`
"accept_action_guild_id_action_index" UNIQUE, btree (guild_id, action) WHERE action = 'delete_invocation'::accept_action_type
-- One guild may have at most one action where the action's data contains
-- a role ID per role ID
"accept_action_guild_id_data__role_id__index" UNIQUE, btree (guild_id, (data -> 'role_id'::text)) WHERE data ? 'role_id'::text
it's a bit cryptic, but maybe it gives you an idea of what you can do with this
copper sphinx
That is indeed cryptic.
ALTER TABLE "Users_Profile" (
PRIMARY KEY (UserID, SettingsID)
);
This is not supported?
near cradle
@patent glen I know I'm late to the party but it was the first =
because when it gets to that operator it says "oh, I have no idea how to deal with a = right now. this was unexpected."
and then it produces that error.
which, admittedly, isn't great.
spare geode
Its not giving me an error but it isnt updating
elif sel == 2:
DepAmount = int(input("How Much Do You Want To Deposit? (Only Dollars not cents): $"))
with sqlite3.connect("lol.db") as db:
cursor = db.cursor()
cursor.execute('SELECT * FROM Customer where Username=username')
data = cursor.fetchall()
DepAmount+=int(data[0][2])
cursor = db.cursor()
cursor.execute('SELECT * FROM Customer where Username=username')
data = cursor.fetchall()
with sqlite3.connect("lol.db") as conn:
with conn:
conn.execute("UPDATE Customer set Balance=? where Username=?", (DepAmount,username))
customer_actions(username)
yeah i was confused because i didn't know about the (a,b)=(c,d) syntax
so i didn't know how the ( could be valid
copper sphinx
How do I return the record value?
paikkakunta_id = int(3)
kotikaupunki = await conn.fetch('SELECT "settingsvalue" from "Users_Profile" '
'WHERE "userid" = $1 '
'AND "settingsid" = $2', ctx.message.author.id, paikkakunta_id)
As kotikaupunki outputs <Record settingsvalue='<city>'>
patent glen
oh, i thought you were already that far because you said it outputs <Record ...>, how are you getting the <Record >
I think it'd be something like for record in kotikaupunki: ... record[0]...
or, i think fetch returns a list, so just kotikaupunki [0][0]
copper sphinx
I mean that embed.add_field(name='Kotipaikkakunta', value=f'{kotikaupunki}') => returns a record.
AttributeError: 'list' object has no attribute 'record' Yup, it's a list.
And got it. :)
torn sphinx
Hi, i'm trying to use sqlite to keep data for each server, by naming the table to the guild id, but cant figure it out.
thats the code
spare geode
try:
sql = """INSERT INTO Admin VALUES (?, ?, ?, ?, ?)"""
with sqlite3.connect("EMP.db") as conn:
with conn:
print("Trying")
conn.execute(sql, ("Admin2", "Password", 0, "POST CODE","POTATOES"))
conn.commit()
except:
print("Admin User Is Already In Your Database!")
Why isnt this working o.O
it prints trying
Figured the probkem
No Comma DansGame
Ok New PROBLEM
How do i make it so i can have 2 primary keys
I want to make it unique if u understand
Identifier text UNIQUE
I keep fixing my own problems lmao
Sorry for the spam :3
copper sphinx
How would I calculate a age from DDMMYYYY format directly on the database? I need a computed columm which has that information. I'm using PostgreSQL.
ionic pecan
I‘m pretty sure there‘s a strftime function but I can‘t find it right now
gilded narwhal
is there something wrong with this query? parent_id = self.database.query('select id from folders where path = ?', (parent,)).fetchone()[0]
it keeps freezing
inserting values into the database works fine.
spare geode
conn.execute("""UPDATE Customer set Balance=0 and set Overdraft=? where Username=username""",(WithAmount,))
Anyone know how to do this?
the And isnt working
dull scarab
isn't it comma separated?
It indeed is sql UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
spare geode
AH thank you
spare geode
Trying to export all my data from my database into a txt file
any ideas?
with sqlite3.connect("EMP.db") as db:
cursor = db.cursor()
cursor.execute('SELECT * FROM Customer')
data = cursor.fetchall()
iterate=0
for x in (data):
print (f"User: {data[iterate][0]} Password: {data[iterate][1]} Balance: {data[iterate][2]} Postcode: {data[iterate][3]}")
iterate+=1
I have this