well I guess not, but are there any other things I could do
#databases
digital bobcat
steel slate
@digital bobcat think of how you would represent the structure in a linear 'string' or 'array' and then you can consider each column as a column OR consider each node as a row in the table (in which case you would need to assemble the tree (or walk through the rows at least) to work as if by tree
if each depth layer of a tree could be considered different (and the total/max depth is known) then you could actually put the root nodes in one table, the gen 2 nodes in another, and so on and so forth.
digital bobcat
I'm confused
sterile ferry
if it's your primary structure then you might wanna consider document store like mongo, otherwise maybe have one table for nodes and one table for relationship between the nodes (edges) like: nodes_table (id, name, ...) edges_table (parent_id, child_id)
digital bobcat
I'm sorry, I'm a database noob. What are nodes?
sterile ferry
let me try to be more concrete: humans (identifier, name, height, weight, ...) relationship (parent_identifier, child_identifier)
nodes would be the actual items, your actual data and the edges are the relationship between them
sterile ferry
how deep is your tree, is there just one tree for entire application?
can you tell what it describes?
digital bobcat
it's kinda deep but after the first layer the parts are pretty small
sterile ferry
you might want to switch to json.dump since it's more safe than pickle although still not a database, if you are sure you need a database I'd look in the direction of document store or trying to model your data to be relational to fit more nicely into any classic relational database (sqlite, postgres, mariadb...)
digital bobcat
no I'm actually not sure that I need a database
but if I just save it to a file it would be too large to open
digital bobcat
no, just in memory
sterile ferry
that sounds like a really considerable size tree then already š
you might wanna check which parts of your data actually occupy the space here, do you have like binary data / large strings or so contained somewhere inside?
sterile ferry
it matters for the size, sure
digital bobcat
I don't think it can be smaller without compression
sterile ferry
are the items in there all of the same "type" on all depth levels, like humans in my initial example?
sterile ferry
in this case you might wanna look into specific database types (this one seems to fit the bill https://github.com/NicolasLM/bplustree) or go with my initial proposal
digital bobcat
boreal shale
Hi guys ive got a problem My error is:
Traceback (most recent call last):
File "DATABASE.py", line 30, in <module>
add_u(usr_f)
File "DATABASE.py", line 21, in add_u
c.execute("INSERT INTO login VALUES (?, ?)", (usr.user, usr.password))
File "/usr/local/lib/python3.6/site-packages/mysql/connector/cursor.py", line 561, in execute
"Not all parameters were used in the SQL statement")
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement
and my code is:
import mysql.connector as mariadb
from classes import *
Connect = mariadb.connect(
host="127.0.0.1",
user="root",
passwd=">Insert here what you think my pw is<",
database="login_db"
)
c = Connect.cursor()
# cu.execute("CREATE DATABASE login_db")
### cu.execute("CREATE TABLE login (username VARCHAR(255), password VARCHAR(255))")
#user_1 = add_user('Papa', 1522)
#user_2 = add_user('Mama', 1522)
def add_u(usr):
c.execute("INSERT INTO login VALUES (?, ?)", (usr.user, usr.password))
def rem_u(usr):
c.execute("DELETE from login WHERE username = ? ", (usr.user))
new_usr_n = input("Add new users name: ")
new_usr_p = input("Add new users password: ")
usr_f = add_user(new_usr_n, new_usr_p)
add_u(usr_f)
Connect.close()
and the class code is:
class add_user():
def __init__(self, user, password):
self.user = user
self.password = password
fair laurel
First, I like how your password has special characters and upper/lowercase, very secure. To answer your question, this is a guess, but you might need to use %s instead of ? for your parameters: (https://mariadb.com/resources/blog/how-to-connect-python-programs-to-mariadb/)
ashen flower
should I replace _id of mongodb object with discord user ID in a collection or i should just add another field for discord user_id in document ?
full geyser
If u donāt need the storage, add extra field
ashen flower
actually i am storing this document in redis but redis spits out this error
and to avoid this, i have to manually do document.pop("_id") everytime.
So should I make _id: discord_user_id in this case ?
rustic yarrow
is there a recommended mysql package for python?
I found MySQL-python and mysqlclient
oh nvm it doesnt look like first one is being updated anymore
potent vortex
@rustic yarrow I use pymysql
rustic yarrow
thanks ill try that
potent vortex
Theres a way to change the type of data structure that the cursor will use to give you the queried data
lemme look for that code i know i have something related
self.connection = pymysql.connect(
host=host,
user=user,
password=password,
db=db,
cursorclass=pymysql.cursors.DictCursor
)
self.cursor = self.connection.cursor()
@rustic yarrow granted its in a snippit from a class I used, but the cursorclass=pymysql.cursors.DictCursor makes your fetch methods return dictionaries rather than tuples
rustic yarrow
oh cool
rustic yarrow
anyone know of any packages that are good for sanitizing input just for SQL
ionic pecan
sanitizing input..? you mean parameter substitution, or what do you have in mind
rustic yarrow
yeah parameter substitution
quick hill
is it better to keep a connection open with the db and edit every now and again or open it, change, close, repeat
velvet jay
Hi all.
I gotta q about databases. I've thought about creating a terminal based python program where I can enter information about my current plants, along with genes and the like.
Are there any resources on database creation which could point me in the right direction? I currently have 2 personal projects I need to finish, so atm I'm just researching and scoping things out.
Btw, I mean biological, breathing plants.
Like grasswhich I'm not growing rn.
velvet jay
Oh, awesome! Thanks a million Tron!
tropic zealot
From its README (https://github.com/jreese/aiosqlite/blob/master/README.md#usage):
aiosqlite replicates the standardĀ sqlite3module, but with async versions of all the standard connection and cursor methods, and context managers for automatically closing connections:
quick hill
k
pure cypress
Are you asking if you can store the object itself or just the date it represents?
SQLite doesn't have a datatype for date/time but you can store it as text or an integer (unix timestamp)
undone apex
On my discord bot, I am currently using gspread as a simple database to append data users enter and get the row value. The issue i am finding is that if more than one person runs the command at the same time, the row value will be the same for all users who were running the command. (the actual data is appended to the proper rows). I found the issue was just with the speed of gspread, as the logic of my code is 100% sound. I have never even touched a database in my life, and am wondering what the most functional/learnable database would be for a complete beginner. If you need more details I can provide.
tldr - google sheets is a bad databse. I know nothing about databases. What is best one to learn
pure cypress
sqlite is easy for beginners to set up and pick up
though it is a bit more restricted on features than some of the other databases out there
undone apex
a quick rundown on what I plan to do with databases
pure cypress
Besides sqlite I'd recommend postgresql
undone apex
my bot is a "ticket" bot. data the ticket creators enter will be appended to a DB. the bot handles maybe 5-6 hundred tickets daily. In the future I plan to store all kinds of bot data, from what options the users chose, which staff are closing the most tickets, average messages sent per ticket, etc etc.
so in the future idk if sqlite would be feasible for how much I plan on doing
https://www.youtube.com/watch?v=hSPhZTCAvG0 I found the best possible tut I think lol.
dull scarab
Postgresql with asyncpg sounds like a path you should consider with a high traffic db
quasi holly
I got postgre and it seems so complicated I'll need to research it
next shore
hey all, I got a question regarding Whoosh
pine pivot
say it
next shore
my problem is that i want to group results but also paginate
apparently search_page() returns ResultsPage which does not provide an interface to the groups
so currently i'm doing this but i think performance would not be too good
results = searcher.search(query, sortedby=sort_by, reverse=(direction=='desc'), groupedby=group_f acets, limit=None)
results = results[offset*(page - 1):offset*page]
sorry
` results = searcher.search(query, sortedby=sort_by, reverse=(direction=='desc'), groupedby=group_f acets, limit=None)
results = results[offset*(page - 1):offset*page]
`
pine pivot
its ok
i dont know much about datatbases but a helper will probably see this and help since they know the problem now!
next shore
okay thanks š
spare geode
@pliant cedar set score to None?
steel slate
@next shore I would store the total results in one variable and then page through using another
it looks like you are slicing a chunk out of the results and losing the rest
next shore
i didn't express myself properly - i want to search the results and get count of each group
so return 10 results and show in the search fields how many we have for each group
steel slate
that's likely to be Whoosh specific if that is how you want to do it
I'd write:
select column_name, count(*)
from table_name
where condition = "TRUE"
group by column_name
order by column_name
yeah it's whoosh specific
i'm thinking maybe i could use mask to remove documents already found, but that might not be the best
steel slate
there should definitely be a way to count things
next shore
from the docs
``Currently, searching for page 100 with pagelen of 10 takes the same amount of time as using Searcher.search() to find the first 1000 results. That is, this method does not have any special optimizations or efficiencies for getting a page from the middle of the full results list. (A future enhancement may allow using previous page results to improve the efficiency of finding the next page.)`
thats relating to search_page
steel slate
results = searcher.search(myquery, groupedby="category")
I don't know your data, but usually a set of counts shouldn't require paging
if there are only 100 types of thing to count then you should be able to get them back all at the same time
I've got to step out to get some lunch real quick before the cafeteria closes.
next shore
so apparently performance-wise search_page and search are no different
so i can just go with what i'm using now
i was getting a 20ms higher response time, but it seems to be because of the grouping
steel slate
glad to help, I guess
next shore
thanks a lot š
steel slate
yw
glass relic
Hey, I have a 2.4TB database that will be growing at about 10% a year... the existing database is with SQLite , any recommendations if I should try to move the data to another type of DB?
Would hate to build site around this database to then have recode it all cause SQLite wasnāt good for some reason
dire light
my dates include a timestamp
I am trying to use the date fields in a group by but absolutely failing
select t.md_bookedon,
t.md_actualstart,...ā¦ā¦ā¦ā¦ā¦ā¦(extra code)
where ( (convert(date, t.md_actualstart,103) >= getdate() - 14) and (convert(date, t.md_actualstart,103) <= getdate() + 3))
group by [some columns][actualstart][bookedon]```im pretty sure the '103' styling would do this, but nope
any ideas? am a lil braindead rn
wait im an idiot
(convert(date,t.md_bookedon,103)) as md_bookedon
etc
next shore
@glass relic i've never heard someone using sqlite for such big volumes
Don't you use some kind of wrapper around your db interface?
glass relic
Well itās entire ledger for xrp crypto, so when I download it I can interact anyway I want after that
But itās all raw data in SQLite
bleak dome
what are your thoughts about django channels & mysql for websockets real time application ?. the system is not a social media,and it will have around 10 people simultaneously using it.
I am thinking if mysql is capable.
dreamy oar
Can MySQL Tables include lists & dictionaries?
ionic pecan
yes it can, but if you ask a question like that, youāre probably not modeling your data right. what are you trying to build?
@bleak dome any web framework is capable of handling 10 people simultaneously
you are referring to 10 web socket connections, right?
quasi holly
Currently with my database I have to open and close the file everytime I do an operation in order for it not to be locked, this only happens with cogs on Discord py, if its all in one file its fine because one doesnt interfere with the other. Is there a more efficient way to do this?
I am currently using sqlite3 and if more than one edit happens together than database locks
ionic pecan
you need to make a queue and put your database operations in there
then execute the queue in background
... or use a database that supports concurrency
gilded narwhal
you could try using the WAL journal mode: https://www.sqlite.org/wal.html
PRAGMA journal_mode=WAL;
@quasi holly
quasi holly
I am starting to experiment with postgresql! I am just stuck somewhere and tryying to figure it out
But it says command test not found
quasi holly
I am lost with postgresql in python lmao
proven agate
try placing the command code outside of the bot class
and this is more of a #303906096458891264 issue as its not erroring because of the DB
quasi holly
Ah sorry!
copper sphinx
What is wrong with my PostgreSQL syntax?
def post(self):
user_id = request.args.get('user_id')
action = request.args.get('action')
value = request.args.get('value')
if user_id is not None and action == str('update_permission_revoked') and value is not None:
with db.engine.begin() as connection:
connection.execute('UPDATE discord_users SET "permission_revoked" = $1', value)
return {'status': 'success'}, 200
else:
return {'status': 'failed'}, 400
Using SQLA and psycopg2
Error:
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) there is no parameter $1
LINE 1: UPDATE discord_users SET "permission_revoked" = $1
^
[SQL: 'UPDATE discord_users SET "permission_revoked" = $1'] [parameters: ('2019-01-23 13:50:33.578',)] (Background on this error at: http://sqlalche.me/e/f405)
Umm, I don't get what you mean.
patent glen
what database library are you using?
nm psycopg2
@copper sphinx psycopg2 wants parameters as %s, it converts it to $1 automatically [asyncpg uses $1 raw though]
copper sphinx
Oh, so that's my problem.
copper sphinx
Ideas why this is not returning anything?
elif action == str("getJulkisuusAsetukset") and user_id is not None:
settings_value_1 = int(6)
settings_value_2 = int(7)
settings_Value_3 = int(8)
query = db.session.query(Profile.settingsvalue)\
.filter(Profile.userid == user_id, Profile.settingsid == settings_value_1, Profile.settingsid == settings_value_2)\
.all()
return {'status': 'success', 'data': query}, 200
The query part says nothing in the output.
copper sphinx
SQLA Echo:
2019-02-08 08:36:42,030 INFO sqlalchemy.engine.base.Engine select version()
2019-02-08 08:36:42,030 INFO sqlalchemy.engine.base.Engine {}
2019-02-08 08:36:42,093 INFO sqlalchemy.engine.base.Engine select current_schema()
2019-02-08 08:36:42,093 INFO sqlalchemy.engine.base.Engine {}
2019-02-08 08:36:42,159 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2019-02-08 08:36:42,159 INFO sqlalchemy.engine.base.Engine {}
2019-02-08 08:36:42,190 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2019-02-08 08:36:42,190 INFO sqlalchemy.engine.base.Engine {}
2019-02-08 08:36:42,250 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2019-02-08 08:36:42,250 INFO sqlalchemy.engine.base.Engine {}
2019-02-08 08:36:42,312 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-02-08 08:36:42,313 INFO sqlalchemy.engine.base.Engine SELECT "Users_Profile".settingsvalue AS "Users_Profile_settingsvalue"
FROM "Users_Profile"
WHERE "Users_Profile".userid = %(userid_1)s AND "Users_Profile".settingsid = %(settingsid_1)s AND "Users_Profile".settingsid = %(settingsid_2)s
2019-02-08 08:36:42,313 INFO sqlalchemy.engine.base.Engine {'userid_1': '157970669261422592', 'settingsid_1': 6, 'settingsid_2': 7}
2019-02-08 08:36:42,346 INFO sqlalchemy.engine.base.Engine ROLLBACK
ok so I have a sqlite database that is hosted on a droplet
how do I open that in a UI such as DB browser?
viral crag
You don't
undone apex
didn't think so.
undone apex
I have tested locally
table is working as intended
just don't know how to open that data visually
viral crag
I mean you could download the file I guess
but you shouldn't be trying to open it
the whole point of writing an application that uses it is that you can get the information you need from that application
ornate storm
hi, does anyone know why executing this command
cur.execute("SOURCE /home/standa/PycharmProjects/mh_selenium/dump-mh-201902072316.sql")
throws this error?
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SOURCE /home/standa/PycharmProjects/mh_selenium/dump-mh-201902072316.sql' at line 1")
.
because if I execute
MariaDB [mh]> SOURCE /home/standa/PycharmProjects/mh_selenium/dump-mh-201902072316.sql;
it works as expected...
Thanks
I forgot to meantion that I use pymysql module
viral crag
I'm pretty sure that's not a command that you're expected to run remotely
So perhaps you might be better off reading the file and executing it yourself
ornate storm
yeah I thought it, I will keep experimenting... cannot find much about how to import a db scheme via pymysql, only manually using command line or this SOURCE command
quasi holly
@commands.command()
async def balance(self, ctx):
row = await self.bot.db.fetchrow("SELECT * FROM information WHERE 'playerID' = $1", str(ctx.author.id))
print(row)
This returns "None" for some reason even though there is a playerID inside information with the ctx id.
patent glen
@quasi holly 'playerID' should not be in single quotes
quasi holly
It gives me an error if it isn't.
patent glen
what error
quasi holly
patent glen
can you show me the create table statement for information?
anyway, 'playerID' silenced the error, but it did so by making it just the literal string 'playerID', not a column reference
quasi holly
patent glen
very weird
quasi holly
It turns bigint into string too for some reason
patent glen
use double quotes
quasi holly
where?
patent glen
around playerID
so like py 'SELECT * FROM information WHERE "playerID" = $1'
the reason is
all column names that are not double quoted, will be converted to lowercase, and matching itself is case sensitive (so they will not match columns that are really in uppercase)
so since your column name has uppercase letters in the real column, it has to be double quoted
if you want to be able to select without quotes, make the table column names in all lowercase
patent glen
so yeah all of this is expected, even if it is kind of a dumb design
also in the official SQL standard they made the opposite decision: they're converted to uppercase
and sqlite is case-insensitive-case-preserving, which is the only sane way to handle it IMO
quasi holly
Yeah I transferred from SQLite so it was weird.
quasi holly
Is there an autoincrement like sqlite3 or can we fetch rowid?
patent glen
what were you using it for? do you need a permanent unique identifier for each row?
looks like there's a special data type SERIAL http://www.postgresqltutorial.com/postgresql-serial/
not a real data type, it creates a sequence
i think that's the same as what autoincrement does on ms sql
quasi holly
Yes for itemIDs so one thing won't be confused with another
patent glen
oracle you have to create sequences by hand
dreamy oar
How do I insert lists inside a table?
heavy horizon
Any easy tutorial for installation and setup for pgBouncer?
patent glen
@dreamy oar you could convert the list to json or pickle, but in many cases it makes more sense to create a table that will have a separate row for each entry of the list.
dreamy oar
@patent glen Example?
patent glen
like i said different solutions are appropriate for different situations so it's important to know what you are trying to do
alpine bramble
Just going to repost from channel five, as here might be better for it:
Trying to install the MySQL connector for Pythons 3.6.8, but it's telling me it can't find Python v3.6. Any ideas?
patent glen
how are you instsalling it and what exactly is it saying (and how did you instal python 3.6.8)
and what OS are you on
alpine bramble
Installed python 3.6.8 via executable from the website. I'm using Windows Server 2019 Base.
I'll copy the message. Two seconds
Afraid it wouldn't let me select the text, so hope the screenshot will do
patent glen
(you can often press ctrl-c on a dialog box to copy the whole text)
As far as I can tell no version of python 3 has ever been available as an MSI, so I have no idea what they're asking
did you install it into your user directory or for all users?
are you using 32 or 64 bit version
alpine bramble
64 bit, and for all users
patent glen
did you download the 64 bit mysql connector?
do you have any other versions of python installed?
various explanations from other people who have run into this issue https://stackoverflow.com/questions/30467495/mysql-installer-fails-to-recognize-python-3-4/39704698
alpine bramble
Embarrassed to admit it, but I accidentally downloaded the 32 bit connector. Thank you for your help, it's installed fine now š
dreamy oar
@patent glen Just show me one way to include lists inside tables
quasi holly
When I have this:'INSERT INTO items VALUES ($1, $2, $3, $4) RETURNING "itemID"' How do I get the value of itemID?
itemID is a SERIAL if it matters.
Ah nevermind found it, you have to use fetchval instead of execute and save the statement to a variable.
alpine bramble
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
passwd="yourpassword"
)
When we connect to a database, do we have to do it every time a function runs, or just once at the very top of the python file?
quasi holly
If I want to move my python files to a raspberry pi do I just change the IP from localhost to the one the computer uses? Also I assume the computer has to be on for the database to work, if so would it be better to move the database to a raspberry pi?
ionic pecan
iām not following you - where do you correlate āmy python filesā and āthe IPā
patent glen
@dreamy oar ok convert the list to a string with json.dumps and load back with json.loads when you select
there are lots of use cases this isn't suitable for though which is why i was trying to get more information
vagrant cloud
Bit of a database newbie here. I have a SQLite database that will be a few GB maximum size that I want to access as quickly as possible to lookup a single row at a time. When I āconnectā to the database with SQLAlchemy and perform a lookup it seems to take some time to connect and then perform a lookup (which is quick). Any suggestions on how to have my script keep the DB connection open and just service requests as they come in?
quasi holly
@ionic pecan sorry forgot to mention I use asyncpg, the IP due to connecting to the database
dreamy oar
@patent glen thanks
alpine bramble
What don't you just have a column for the server?
quasi holly
What would be the best way to move my database to a droplet? PostgreSQL.
quasi holly
I have a droplet and I have the psql dump at the Desktop, I tried restoring through psql but it doesn't seem to work
astral kettle
is there a database somewhere for english words and their type (e.g. verb, adverb) that can be used programmitcally (meaning not just a dictionary, something like a json or a .txt file that can easily be read from)?
pine pivot
scowl?
astral kettle
what's that?
astral kettle
yeah, that's the part I'm needing
I'm basically making a robot, needs to be able to find the verbs in a sentence
and yes, I tried microsoft's intent recognition, it wouldn't download
astral kettle
ok, how do I run html from python?
pine pivot
can you send the url
astral kettle
there is a .js github
astral kettle
ok, thanks
pine pivot
you need git installed
astral kettle
I have it
also, vs's pip can't install it
----- Installing 'git+https://github.com/meetDeveloper/googleDictionaryAPI' -----
Collecting git+https://github.com/meetDeveloper/googleDictionaryAPI
Cloning https://github.com/meetDeveloper/googleDictionaryAPI to c:\users\shadow~1\appdata\local\temp\pip-req-build-b6v_e8ag
Complete output from command python setup.py egg_info:
Traceback (most recent call last):
File "<string>", line 1, in <module>
File "C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python36_64\lib\tokenize.py", line 452, in open
buffer = _builtin_open(filename, 'rb')
FileNotFoundError: [Errno 2] No such file or directory: 'C:\Users\SHADOW~1\AppData\Local\Temp\pip-req-build-b6v_e8ag\setup.py'
----------------------------------------
Command "python setup.py egg_info" failed with error code 1 in C:\Users\SHADOW~1\AppData\Local\Temp\pip-req-build-b6v_e8ag
You are using pip version 18.1, however version 19.0.2 is available.
You should consider upgrading via the 'python -m pip install --upgrade pip' command.
----- Failed to install 'git+https://github.com/meetDevelo
astral kettle
ok, so how do I get the json data from that site?
like this json data: https://googledictionaryapi.eu-gb.mybluemix.net/?define=example&lang=en
pine pivot
run a web scraper to go over a list of english words you can get from any other place from the rawdata of this site?
all i can think of
if you do please upload it thatd be cool
astral kettle
yeah, I'm meaning, how do I get the json data itself from the site?
like, the json shown
pine pivot
just use a webscraper combined with another dictionary online
abstract herald
uhhhhhhhhh yaml isnt really a database is it
stupid question
ill just go to ojne of the helps
serene hare
.
copper sphinx
Could someone show some light why this does not work?
def getBirthday(user_id):
permission_id = int(6)
settings_id = int(5)
query = db.session.query(Profile.settingsvalue)\
.filter(Profile.userid == user_id)\
.filter(Profile.settingsid == settings_id)\
.filter(Profile.settingsid == permission_id)\
.all()
if query == []:
return {'status': 'failed', 'message': "Not allowed"}, 420
return query
SQLA echo:
2019-02-11 15:55:30,710 INFO sqlalchemy.engine.base.Engine select version()
2019-02-11 15:55:30,710 INFO sqlalchemy.engine.base.Engine {}
2019-02-11 15:55:30,722 INFO sqlalchemy.engine.base.Engine select current_schema()
2019-02-11 15:55:30,722 INFO sqlalchemy.engine.base.Engine {}
2019-02-11 15:55:30,734 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2019-02-11 15:55:30,734 INFO sqlalchemy.engine.base.Engine {}
2019-02-11 15:55:30,741 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2019-02-11 15:55:30,741 INFO sqlalchemy.engine.base.Engine {}
2019-02-11 15:55:30,751 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2019-02-11 15:55:30,751 INFO sqlalchemy.engine.base.Engine {}
2019-02-11 15:55:30,763 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-02-11 15:55:30,764 INFO sqlalchemy.engine.base.Engine SELECT "Users_Profile".settingsvalue AS "Users_Profile_settingsvalue"
FROM "Users_Profile"
WHERE "Users_Profile".userid = %(userid_1)s AND "Users_Profile".settingsid = %(settingsid_1)s AND "Users_Profile".settingsid = %(settingsid_2)s
2019-02-11 15:55:30,765 INFO sqlalchemy.engine.base.Engine {'userid_1': '157970669261422592', 'settingsid_1': 5, 'settingsid_2': 6}
2019-02-11 15:55:30,773 INFO sqlalchemy.engine.base.Engine ROLLBACK
mild sierra
@copper sphinx I can see 2 times Profile.settingsid in query one sould be permissionid ?
copper sphinx
No it should be like that.
mild sierra
it is AND cant have bot equal, you need OR then
copper sphinx
This works in pure PostgreSQL:
select p5.settingsvalue as p5value
from public."Users_Profile" p5 inner join public."Users_Profile" p6 on p5.userid = p6.userid and p6.settingsid = 6
where p5.userid = 157970669261422592 and p5.settingsid = 5
So how would I do the same in SQLA?
mild sierra
you can use raw QUERY option
copper sphinx
I don't want to use the raw one, so that's why I'm asking.
mild sierra
copper sphinx
permission_id = int(6)
settings_id = int(5)
with db.engine.begin() as connection:
query = connection.execute(f'select p5.settingsvalue as value from public."Users_Profile" p5 '
f'inner join public."Users_Profile" p6 on p5.userid = p6.userid and p6.settingsid = {permission_id} '
f'where p5.userid = {user_id} and p5.settingsid = {settings_id}')
#if query == []:
# return {'status': 'failed', 'message': "Not allowed"}, 420
return query
I did it like that, but now my problem is that it's not json seriazable.
dim bear
Does anyone have any idea how to connect to a mssql server in OpenShift? I get ('IM004', "[IM004] [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (0) (SQLDriverConnect)") error whenever I try.
I think its a permission issue, since it works if I do not specify user locally, but OpenShift forces me on a user starting on 1001.
So in the Dockerfile i got
USER 1001
and with USER 1001 I get the same error locally.
I've tried to look at the stacktrace, but its not very human readable, to say the least
torn sphinx
Hello i have some issu with my database :/
conn = sqlite3.connect('picture.db')
cursor = conn.cursor()
table = "Ulysses - Jeanne d'Arc to Renkin no Kishi"
print(table)
cursor.execute("""SELECT Name FROM "%s" """%table)
i have this issue :
sqlite3.OperationalError: near "-": syntax error
i think it's because i have this string in my table : '
but i use "%s", why it's don't work ?
thanks advance !
torn sphinx
but all of my other table with a "-" will work, i have a issue just with this "-" and " ' "
For exemple "Saint Seiya - Saintia Shou" will work perfectly
raven rain
is Saint Seiya - Saintia Shou after Ulysses - Jeanne d'Arc to Renkin no Kishi in the code ?
torn sphinx
before
and if i remove " ' " from table name it's will work for "Ulysses - Jeanne dArc to Renkin no Kishi"
raven rain
are you sure the error comes from py conn = sqlite3.connect('picture.db') cursor = conn.cursor() table = "Ulysses - Jeanne d'Arc to Renkin no Kishi" print(table) cursor.execute("""SELECT Name FROM "%s" """%table) ? Can you share the line where is the error and the line corresponding in the code ?
or just share the entire error
torn sphinx
Ignoring exception in on_message
Traceback (most recent call last):
File "/usr/local/lib/python3.6/site-packages/discord/client.py", line 227, in _run_event
await coro(*args, **kwargs)
File "Picture_db.py", line 912, in on_message
cursor.execute("""SELECT Name FROM "%s" """%table)
sqlite3.OperationalError: near "-": syntax error
print table show
print(table)
("Ulysses - Jeanne d'Arc to Renkin no Kishi",)
thanks anyway š
pine nacelle
Anyone got any knowledge with sqlite3 and python? Having issues inserting into a table
delicate fieldBOT
pine nacelle
class g_FoundDatabase:
def __init__(self, file=re.sub("\.\w+", '.db', __file__)):
self.file = file
self.conn = sqlite3.connect(file)
self.cur = self.conn.cursor()
def create_tabels(self):
self.cur.execute('CREATE TABLE IF NOT EXISTS r_SubmissionDB (SubmissionID TEXT)')
self.conn.commit()
def insert(self, _SubmissionID):
self.cur.execute('INSERT INTO r_SubmissionDB VALUE=?', (_SubmissionID))
self.conn.commit()
def has_id(self, _SubmissionID):
return bool(self.cur.execute('SELECT * FROM r_SubmissionDB WHERE SubmissionID=(?)', [_SubmissionID]).fetchall())
g_FoundDatabase.insert(submission.id)
That is the code I am using and am getting "TypeError: insert() missing 1 required positional argument: '_SubmissionID'"
I think I just figured it out
I just dumped submission.id
And its not an INT
Huh still getting the error
hazy mango
You usually have to do py self.cur.execute('INSERT INTO {table}({what column}) VALUES (?)', (submission.id)
pine nacelle
I editted the code above with what I currently have
hazy mango
You need to do ```py
self.cur.execute('INSERT INTO r_SubmissionDB(SubmissionID) VALUES (?)', (submission.id)
Sorry I dont follow
hazy mango
edited
pine nacelle
Same error :L
pine nacelle
I fixed it I think
def insert(self, _SubmissionID):
self.cur.execute('INSERT INTO r_SubmissionDB VALUES(?)', [_SubmissionID])
self.conn.commit()
Its inserting correctly into the DB
opaque yacht
General database advice. I am going to be reading values from some tools every 30 seconds. I will have 12 tools and I need them all to save data to the same datatable. I was thinking of preventing too many sql executions by saving to a text document and uploading that every few minutes. The database is a MSSQL database. Is it necessary? Should the database be able to handle 12 executions every 30 seconds?
torn sphinx
It should being a database and all
opaque yacht
thanks!
upper beacon
When should I use a relational vs non-relational database?
torn sphinx
depends on your data
an example would be..
consider you have stats of players in a particular sport..
that can go in to relational.. because the fields are related to each other..
what if you had stats for a bunch of different sports... what is number of goals scored field doing empty against a basketball player? number of holes in 1 for a tennis player.. etc.. so.. you get the idea
these things aren't related.. and can go into non relational..
upper beacon
hmm then if I wanted to go into a instagram clone type app, would i use non relational since some people may have certain settings/data that others don't?
upper beacon
it would have links, picture/videos, bio, really just like instagram
all of the data would prob fall under their userID
trying to figure out if i should use AWS or Firebase as well and firebase is a non-relational database
torn sphinx
use Firebase..
craggy coyote
anyone have experience with sqlalchemy? Trying to find if there's a way to catch exceptions before committing
e.g.
new_instance = User(email=1) <--- email should be string
db.session.add(new_instance)
db.session.commit() <--- throws error but want to be able to check before i run db.session.commit
upper beacon
@torn sphinx any specific reason?
raven rain
Does anyone knows why my tables are cleared when i reload the program ? https://hastebin.com/zadobamulu.py
I don't use my function reset_all()
wind pelican
do you call connection.commit ever?
if not it doesnt save
i think for sqlite 3 its recommended that you call it after every transaction aka 'series of related queries'
so like if you want to delete a couple of related entries, commit after the whole group has been deleted
upper beacon
@torn sphinx do u think firebase being a non relational database would hurt?
torn sphinx
can anyone tell me what a Pcollection is
glad bobcat
Hi guys
I've been using somebody else's SQL alchemy code for a few weeks, and I wanna fly by my own wings now
Is there a good human-readable tutorial on how to create a table structure from scratch in SQL alchemy? Because the doc on the website is pretty hard to swallow.
(Also non-Python question: how bad is it if I have a column of VARCHAR that actually only has ~10 different strings in it (for millions of rows)? Will it be compressed properly by MySQL/MariaDB?)
Other question: how hard is it to use inheritance for SQLAlchemy classes? I have two very similar tables (but representing objects different enough to really not want to put them in the table), and I want to minimise code duplication.
errant phoenix
return groupsController().findAll({
where: {
community
},
include: [{
model: gControl.Members,
where: {
[sql.Op.or]: {
allowed: issuer,
muted: issuer,
admin: issuer
}
},
as: 'members'
},
{
model: gControl.Mods,
where: {
[sql.Op.or]: {
allowed: issuer,
muted: issuer,
}
},
as: 'moderators'
}]
}).then((result) => {
how can i use the or operator on two models?
it works if i remove one of the item in includes
urban cradle
they behave the same kinda
full geyser
so... is there a difference?
urban cradle
yes
full geyser
ok what is it?
urban cradle
cast expressions
full geyser
what's that? (looked it up but didnt really get it)
like found that... but any chance you have a more concise explanation?
urban cradle
cast expression is for like uhhh
and i'm not super hot on this
but like casting one affinity to another? or some such
Casting a TEXT or BLOB value into NUMERIC first does a forced conversion into REAL but then further converts the result into INTEGER if and only if the conversion from REAL to INTEGER is lossless and reversible. This is the only context in SQLite where the NUMERIC and INTEGER affinities behave differently.```thats literally the image i sent u...
urban cradle
did you read it though?
full geyser
ok wait... can i store anything in numeric that i cant store in integer, regardless of whether i plan to cast it or not?
(or vice versa)
i did, but it didnt really go into my head :/
urban cradle
ĀÆ_(ć)_/ĀÆ
i've never directly used it, just read about it
judging by documentation i guess you can't store certain shit in integer, but numeric can store all 5 types of affinity
and the difference in behaviour is how they cast things to store them
full geyser
ah k
urban cradle
like you can't store a hex value in int because it'll stop the cast when it hits x in 0x
so it'll just store a 0
same for exponents or stuff i guess
full geyser
ok that helps - thnx š
urban cradle
np, documentation is sometimes ugly to read :3
sharp crown
hello
torn sphinx
hello
crude basin
Does anybody have a good non video tutorial for a database efficient way to sort and search a large table? It can be fairly technical, as anything I don't know in the article I'll just go learn. Just been stuck for a few days. Python 3.6, Django 2., MySQL...
OH also if this is the wrong place please let me know.
glad bobcat
Not really a Python question again, but can somebody explain to me real fast what ROW_FORMAT=DYNAMIC or COMPRESSED do exactly for InnoDB?
Not finding any clear answer
In particular I have a column with strings that get repeated a lot but can't be enumerated.
ruby tangle
when calling stored procedures on postgresql psycopg2.callproc how to specify type of parameters? this equalent CALL login('Pera'::text,'perapass'::text, false::boolean)?
am gettin
Error while connecting to PostgreSQL login(unknown, unknown, boolean) is a procedure
LINE 1: SELECT * FROM login('Pera','perapass',false)
``` head same issue on serwer when called without ::text ::text thingy
sudden tree
Having some trouble with aiomysql pools. Creating a pooled connection causes queries to run oddly. I've switched back to open/close for now but would love to be able to move back to a pooled status.
async def query(self, sql, args=None, commit=False, return_value=False, return_one=False):
date = self.bot.get_cog("Mod").get_date()
value = None
if self.bot.get_cog("Mod").debug == True:
print("\n\n[{}] SQL QUERY: \"{}\"\n[{}] SQL ARGS: \"{}\"".format(date, sql, date, args))
try:
pool = await self.get_pool()
async with pool.acquire() as conn:
async with conn.cursor() as cursor:
await cursor.execute(sql, args)
if commit == True:
await conn.commit()
if return_value == True or return_one == True:
if return_one == True:
value = await cursor.fetchone()
else:
value = await cursor.fetchall()
await cursor.close()
if return_value == True or return_one == True:
if self.bot.get_cog("Mod").debug == True:
print("[{}] SQL RESULT: {}".format(date, value))
except Exception:
print("Error running {}\nArgs: {}".format(sql, args))
traceback.print_exc()
return False
if value:
return value
if (return_value == True or return_one == True) and not value:
return None
return True```
hearty iron
Ok so i was trying to store a channel id as an integer in a RethinkDb document like this {"channel": channel.id} but the problem is that it doesn't store it the correct way. It has some e+17 at the end. Is that something wrong which i am doing or is this a problem with large numbers being stored?
ionic pecan
you need to use strings
we had some issues with storing discord snowflakes in rethink iirc
hearty iron
Ohhk š . Thanks
abstract saddle
how can i add ids to a database? so if i add a new user the first user will get an id of 0 and the 2nd one will get an id of 1 and so on...
outer grove
AUTO_INCREMENT
abstract saddle
thx
abstract saddle
why isn't this working? cur.execute("""INSERT INTO addresses VALUES(?, ?, ?, ?, ?, ?)""", (id, fname, lname, address, phone, email)) sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.
dull scarab
is id auto incremented?
abstract saddle
no
dull scarab
How did you define your table
abstract saddle
cur.execute("""CREATE TABLE IF NOT EXISTS addresses(ID INTEGER PRIMARY KEY, fname TXT, lname TXT,address TXT, phone INT, email TXT)""")
abstract saddle
its just id
outer grove
and its an int?
median sparrow
if i want to store who follows someone for each person and there can be unlimited followers for each person
would i create a new table for each person?
or what else can i do?
if anyone has any ideas please answer this and mention me so i can see this cos i might forget š
please try to answer this fast cos i need to do this before i can carry on with my discord bot
tranquil sparrow
just save it in memory as a dict
hazy mango
You can have a json with the keys as each user and the values as a list of followers.
Probably best to use USER IDs instead of discord names/tags btw
median sparrow
i only know how to use sql
misty crow
Looking for the best solution and not really sure where to start any opinion greatly apprieciated... The Problem#
team_name;
Table 2: team_id; ```
I need to have team_name in table 2 for querying purposes. Would you match and add the team_name to table 2? or is there a way with foriegn keys to share the team_name data?can someone help me with this?
i want to store what people someone follows or who follows someone or both but im not sure how i can do this for every person
dull scarab
Have 2 tables, one for users and one for follower relations
The follower table could look like
User. | following
Id. | id.```could i do something like this?
cur.execute("""SELECT * FROM addresses WHERE id OR name OR address = ?""", (search))
ok... it doesn't seem to work
what should i do?
patent glen
@abstract saddle (search,)
abstract saddle
thx! š
silver vortex
Hi. I'm trying to update a array inside of a object array using pymongo but I'm having troubles trying to have that work, my document looks like { "disabled_commands_per_channel": [ { "id: 123, "commands": [] } ] } this is what I have as of now py self.client.database['guilds'].update_one({'id': ctx.guild.id, 'disabled_commands_per_channel': {'id': channel.id}}, {'$push': {'disabled_commands_per_channel': }}) but not sure how to finish it to push a string into the commands array as well as remove it
dusky cape
Hello, having some trouble with my program. I created a disp_Current_ledger function to load/refresh a QTableWidget in PyQt5 with data from Sqlite3. However, it appears to improperly load the data. Any thoughts as to the cause of this? to follow will be the code and an image of the issue.
def disp_current_ledger(self, comboBox, tableWidget):
ledgerName = comboBox.currentText()
modifiedLN = modify_for_sql(ledgerName)
selectionStatement = "SELECT * FROM " + modifiedLN
try:
conn = sqlite3.connect(self.refUserDB)
with conn:
cur = conn.cursor()
cur.execute(selectionStatement)
result = cur.fetchall()
except Error:
print("Error: 558")
finally:
conn.close()
tableWidget.setRowCount(len(result))
tableWidget.setColumnCount(8)
row = 0
for data in result:
sublist = [data[y] for y in range(0, 9)]
for col in range(0, 9):
tableWidget.setItem(row, col, QTableWidgetItem(sublist[col]))
row += 1
tableWidget.setHorizontalHeaderLabels(["Transaction Date",
"Transaction Method",
"Transaction Description",
"Category",
"Debt (-)",
"Credit (+)",
"Notes",
"Status"])
tableWidget.resizeColumnsToContents()
# tableWidget.setSortingEnabled(True)
# tableWidget.sortByColumn(0, 0)
# it appears my sorting method is total wrong. I need to work on that to ensure updated/edited rows are sorted properly
i should specify that the data that appears to be loaded improperly is the "Debit" and "Credit" columns which are left blank. (REAL not TEXT cells if that matters)
raw onyx
hello, so i've learned that you can use context managers with your database connections
import sqlite3
with sqlite3.connect('example.db') as conn:
code goes here
so, now i have a connection object in my class
self.conn = sqlite3.connect(DATABASE_NAME)
now, my question is if it's better to do what i just did (having the connection be part of the class) or every time i need to access info from the database i just call the context manager again
strong kiln
This is a sqlalchemy question but a sql answer could help me work backwards too:
So I'm trying to use datatables to make a nice table of accounts with child rows of characters and I'm trying out sqlalchemy-datatables which takes a query, the request params, and column list and outputs a json version of the query (+ whatever filtering via request params).
The main table (Account) has a relationship with a child table (Character), each account can have many characters. So I need to figure out how to query all of the accounts and display the columns for account and a few for each character so it ultimately pulls these fields (id, isgm, isactive, steamid, characters.name, characters.lastname, characters.strength, etc.) and when run through jsonify would look like this:
[
{
'ID':2,
'IsActive':1,
'IsGM':0,
'SteamID':76561197961558003,
'characters':[
{
'Agility':20130683,
'Constitution':20019990,
'ID':1,
'Intellect':10166114,
'LastName':'eeeeeeeee',
'Name':'reeee',
'Strength':20051274,
'Willpower':20392930,
'guild':None
},
{
'Agility':15000000,
'Constitution':15000000,
'ID':2,
'Intellect':15000000,
'LastName':'maple',
'Name':'whiskey',
'Strength':15000000,
'Willpower':15000000,
'guild':None
}
]
}
]
(Models https://pastebin.com/t3zfED5m )
quick hill
how would i see if a table exists in sqlite
dusky cape
@quick hill https://sqlitebrowser.org/
Should help. I use it to check if my code works
dusky cape
Are you looking to generate the table visually? Or just get a true false?
dusky cape
I suggest using a "Create table if not exists ..." Statement. I usually call that statement to ensure it exists or is generated as a result.
Hope that helps
slender dirge
Hey guys. Not sure where to ask but anyway. Its my first time using github and I have a project due monday. I've cloned a repository from my school to do the work and I understand how to open it on notepad and edit. What I don't understand is how do I open the code somewhere else to test functionality. Do I need to download an App? If so which one?
worthy prism
@slender dirge once you've edited it, you need to push it back up to github
i reccomend a client
quiet ermine
what does index=True/False mean in SqlAlchemy?
serene hare
.
strong kiln
Indexes for fast search I believe.
pine pivot
in the pandas documentation it uses ('A', 'i4'),('B', 'f4'),('C', 'a10') as a datatype, how is this a data type? looks like a bunch of strings to me
fresh dove
where do you see this ?
pine pivot
fresh dove
i couldnt find that, was asking for source
pine pivot
i sent it
fresh dove
pine pivot
oh okay thanks
fresh dove
ruby tangle
is there some auto maper from db or do i have to manuly create my models?
like in c# entityFR can create models form db?
buoyant breach
You mean DB-first approach? I think most of the tools are code-first.
So that you can make migrations etc.
ruby tangle
ok so no DB frist approach exists?
copper sphinx
def update_email(user_id, email):
settings_id = int(1)
if EMAIL_REGEX:
profile = Profile.query.filter_by(userid=user_id, settingsid=settings_id).first()
profile.settingsvalue = email
db.session.commit()
return {'status': 'success'}, 200
else:
return {'status': 'failed'}, 400
What is the SQLA ORM way to do this?
@potent vortex
pine pivot
whats the useful difference between DataFrame.from_records(data) and DataFrame(data)?
fresh dove
theyre both constructors so
if data is allowed by both then none
except the different options ofc
pine pivot
what are the different options exactly?
fresh dove
from_records has exclude (exclude fields), coerce (convert from non-numeric to float) and nrows (number to read if iterator)
DataFrame just has dtype and copy
pine pivot
oh wow
how does non-numeric to float work exactly?
is it just '0.304039' > 0.304039?
pine pivot
like what a list?
fresh dove
honestly not sure, i dont use panda
pine pivot
oh
fresh dove
yea
im gonna guess it does lists
including lists of dicts and series n stuff
basically taking an object and turning it into float
pine pivot
okay
fresh dove
yea it looks like that. i checked out the src
and then the abstracted functions just convert object to array
pine pivot
how do i display all the collumns of a dataframe
south robin
df.columns or df.columns() i forget
pine pivot
random helped me figure it out in #ot2-the-original-pubsta
south robin
I just realzied that question was from yesterday haha
pine pivot
lol yea i asked it like 5 hours before that in #data-science-and-ml but noone answered that either, hard to get help sometimes especially if youre in the topical chat š
dull scarab
Depends whos online tbh
Some people watch topical channels, and completely ignore the help channels, and vice versa
pine pivot
yea
rustic yarrow
Yeah I think you were asking about series vs lists?
pine pivot
me?
sleek fractal
can i ask about SQL here ?
near cradle
yep
pine pivot
should i use a .dat file or .txt file for my database? which one is better and why
shell chasm
@pine pivot ill-advisable to use .txt for binary data, many OSes will auto-interpret that extension as a file containing text
shell chasm
@pine pivot you tell me!
pine pivot
text that has information for my database
like description will have something like "description1" "description2" "description3"
shell chasm
@pine pivot what type of database do you plan to use?
pine pivot
uhh im not sure...? im using pandas
shell chasm
@pine pivot that doesn't mean much in this context. pandas is a data analysis library. we're talking about databases here... are we not?
pine pivot
yes but im not sure what you mean, im really new to databases and such
shell chasm
@pine pivot then you're putting the cart before the horse! you should be able to answer those questions before worrying about what file format your DB is stored in (if it's even stored in a single file, or if its concrete storage is even something you need concern yourself with!)
pine pivot
okay where do i start then
shell chasm
uh... seriously? I have no context about you whatsoever, so that question is so broad as to be impossible to answer. what're you doing? how much do you know already? what are your goals? your requirements? what data are you storing?
pine pivot
im storing strings and images, a database of all PS2 games and i want to be able to simply search the database like you would on a site. I was told to work on one thing at a time so right now im working on making sure i can do all that then ill be moving data over to something like flask where i can visually see the data then ill set something up to get the data I need from a few sites ive selected
does that make sense?
shell chasm
it does, thank you! in that case, you're looking at (effectively) tabular data so the easiest/most common is to look into an RDBMS like SQLite, MySql, Postgres, etc. I'd suggest SQLite to start for many reasons based on the info you've given me, but really it sounds like the particular choice of DB is not yet important. just learn a bit about relational databases, that should get you going in the right direction.
pine pivot
okay am I able to use pandas instead on its own?
since I heard that it works fine ive been studying it
shell chasm
@pine pivot absolutely you will! pandas is a data analysis library, entirely different
pine pivot
so i can use it instead of SQL or something like that?
anyways ill be looking into rational databases now, thanks!
shell chasm
@pine pivot you'll be very happy to soon learn that SQL is the query language of relational databases š
pine pivot
alright then
what resource would you recommend for rational databases?
@shell chasm
delicate fieldBOT
pine pivot
something like in that
outer grove
I mean any course on SQL would be good
Honestly basic SQL is easy enough that you can look up very specific use cases for each task you need to do and learn that way
pine pivot
yes but i meant something that someone who was experienced could recommend
south robin
kazo, do you already have a dataset that you will be working with?
i.e. do you already have some file or other data source of all these ps2 games, or will you be generating this list yourself?
pine pivot
i have a site i will be gathering the data from but i decided to figure out how i wanted the data to actually be stored before i start gathering it
2 sites*
wikipedia and gamerankings.com
gamerankings will be the major source while wikipedia will be a secondary to fill in any gaps made by gamerankings
sorry for replying so late i didnt see the message until now.
pine pivot
so, no. i do not yet.
plain radish
@pine pivot if you haven't set yourself on the sources of data yet, I'd recommend looking into something that isn't breaking TOS by scraping it. Gamerankings is managed by CBS Interactive which have a hardline of not allowing scraping of any kind from their sites:
Without limiting any other provision in these Terms, you agree not to do the following, or assist others to do the following:
...
- Engage in unauthorized spidering, āscraping,ā data mining or harvesting of Content, or use any other unauthorized automated means to gather data from or about the Services;
If you intend on scraping this site, we aren't able to help you at all as it's against rule 5 of the server rules.
plain radish
Can't really help you then if you're relying on it.
pine pivot
okay
rustic yarrow
how do you check if something is breaking TOS on a site
pine pivot
check their TOS
following up on this, I found a site that doesn't seem to have a TOS and hosts a good amount of games
problem is they only have the title and maybe an image
wind pelican
you could also check robots.txt
which is usually at the root of the site eg www.example.com/robots.txt
wind pelican
a file that tells software how to or how not to scrape them
invented back when search engines first got popular
pine pivot
well whats this mean User-agent: * Noindex: /ads/ Noindex: /queue.php?
wind pelican
means dont scrape those paths
if there is no other TOS anad thats all they give you, then thats about it
most sites have a ToS though
rustic yarrow
what does user-agent: * mean
wind pelican
the user agent is a string a web client will send to tell the server what kind of client they are
the star means that the following information applies to all of them
rustic yarrow
oh so basically display different ways for different browsers?
wind pelican
sometimes they can yea
its up to the server to decide what to do with it but in the past they did
torn sphinx
Hey, i need a help with postgres:
I'm trying to acess the pg_hba.conf but he requires me a password, and i don't remember for add a password for postgres.
sleek fractal
im having trouble with a join on sql server. I believe it might have to do with the Convert() i need to do for the ON clause
LEFT JOIN (select * from [AUX].[CAT_LMP_V2] where [curriculum?]=1) d on CONVERT(varchar,c.[id produc])= CONVERT(varchar,d.[id_prod]) --and c.Editorial = d.editorial
so the thing is that while c.[id produc] is only numerical, d.[id_prod] as some weird alfanumeric ones like "AB9870"
i did the conversion and only a small subset of the ids match (and if I do a WHERE on d with some of the bad results it works)
maybe its a "trimming" problem? like having to cut whitespaces?
quasi holly
Can I pg_dump the new version of my database to the old one? On the new one I have new rows and tables. Or is there another way?
torn sphinx
any good recommendations for setting up a database using python at an introductory level
pine pivot
datacamp
shell chasm
@torn sphinx what type of database are you looking to use, RDBMS? "NoSQL"? other?
torn sphinx
No database in mind. The problem domain is simply just saving/retrieving field data from user accounts
shell chasm
@torn sphinx that sounds pretty tabular so an RDBMS would be a good fit. check out SQLite, it'd be a great fit for you I believe
torn sphinx
I've looked at dataset but it constantly breaks
spring crane
Anyone know of any database hosting services that will let you host for free (personal use only)
ionic pecan
There is no good free hosting
shell chasm
@spring crane Heroku lets you host DBs for free, but sqlite is not at all a bad option either
pseudo pollen
If I have a MySQL DB such as:
ID Name Surname
1 Jack Smith
2 John James
3 Tim Cook
I understand how to get all of ID or Name but how would I get Name and Surname by passing the Primary Key (ID)? So I would pass 3 and it would return Name and Surname.
Please @ me in response.
nova hawk
SELECT Name, Surname FROM Tablename WHERE ID = the_id
pseudo pollen
Thanks.
tardy sable
I need some help with a database
for example
I need a database that records the medical history of people
can someone help me out?
carmine heart
That's a very vague question. What kind of answer are you looking for?
tardy sable
So,
I need like, documentation links I guess?
It's a project I'm doing for the NHS.
for e.g.
if I searched data with the name "Andrew Collinson"
it would show up the medical history of Andrew Collinson
etc
carmine heart
So, you're creating a database system to hold medical records without knowing anything about databases yet?
tardy sable
Pretty much.
carmine heart
Okay, I don't really know where to start. It's not an easy task
unborn sentinel
That kind of sounds like a HIPAA violation time bomb waiting to happen
unborn sentinel
It's hard to suggest if we don't know what the infrastructure is going to be
What kind of database, etc.
tardy sable
hmmm
lets see
All I know from the team is that we need it in the format of an excel spreadsheet
well not excel
but in a table
could be excel
unborn sentinel
Well that would be nearly any database
torn sphinx
what's the idea behind your project.. you might want to look at things that already exist
tardy sable
that's how it needs to be presented
torn sphinx
unborn sentinel
That really doesn't tell us much
tardy sable
Let me give you a backstory
unborn sentinel
Yeah I would REALLY advise against making your own on this one
unborn sentinel
There are plenty of commercial products that would properly and safely house that kind of information
tardy sable
so
for example
a lot of people don't keep the documents that verify they have been vaccinated
in order to get a visa
so this records the medical history of people
unborn sentinel
No, we understand the concept
But you're really going to have to worry about the privacy and the protection of that information
And when your first example of what the database is going to be like is Excel, it's really hard to figure out where to start with what to suggest
tardy sable
What type would you recommend?
unborn sentinel
A commercial product that has already been rigorously tested and is secure.
In my experience, you don't mess around when it comes to medical records.
unborn sentinel
Are you the only person on this project?
Or is this like a competition for who can make the best product? Bidding process?
tardy sable
sort of both
i mean
sort of a team project but also competition
its work experience
I'm obviously not messing around with real people's medical records
unborn sentinel
Well I guess my question is: Is this something that is ACTUALLY going to be used by the UK public
Okay, gotcha
That's where my concern was
tardy sable
oh okay
wind pelican
ah yea there are serious legal complications when storing real medical records
tardy sable
Let me explain this
if I make the cut
and do it good
I join the actual team
like working for the NHS
wind pelican
ah hm
unborn sentinel
You're going to need to study hard on database structure, function, etc.
tardy sable
Yeah,
unborn sentinel
I'm looking right now
wind pelican
then you need to both encrypt the data and at least have a way to prevent anyone from looking at the data that is not authorized to
possibly even admins
unborn sentinel
I think there's a lot more going into this than you might think
wind pelican
in america HIPPA is super complicated to conform to
yeah but you want to prove you can do it right?
tardy sable
Yes.
wind pelican
anyone can make a database
unborn sentinel
Exactly
wind pelican
but only some people can make a private legally acceptable database
unborn sentinel
They're not going to pick you if you make a database just as is. It has to be able to conform with the requirements
wind pelican
its the attention to detail that makes you qualified here imo
tardy sable
Can I make the database first and then work on encryption and access?
unborn sentinel
They kind of go hand in hand, but you can certainly start out with one, then the other, then combine the two
tardy sable
I'll speak to one of the guys on the team about encryption.
unborn sentinel
So what do you know about databases in general, let's start there. Are you familiar with SQL?
tardy sable
I've never programmed in SQL but I understand the concepts.
wind pelican
yeah youll have to design the whole system with privacy in mind
unborn sentinel
Man I'm just really not sure where to start on all this, I'm trying to wrap my head around the whole thing and what you'll need
wind pelican
eg i assume there are people that decide who can see which information, and then you also have detailed audit logs of every access and change
tardy sable
Yes.
wind pelican
but that audit log also has to be in the encrypted space
and basically nothing can touch the hdd outside of the encryption
or you risk a privacy violation
this is all just guesses based on my geeneral security and privacy knowledge, the details will depend on your location
tardy sable
Well yeah, I just messaged someone on the team to work out the encryption part of it,
But for now my job is the database itself.
unborn sentinel
Have you actually created a database before? Even in something like SQLite?
tardy sable
Yes.
unborn sentinel
I'm really not sure what we can really offer you, honestly. Like I'm racking my brain
tardy sable
Should I just try using SQLite for this?
unborn sentinel
GOD no
tardy sable
I've been told to use something call PII?
wait no
my team is confusing
oh that's the privacy policy
deft badge
PII is Personally Identifiable Information
tardy sable
I'll research on python MySQL?
deft badge
You might want to check out a few of the UK laws regarding data processing before you adventure on this journey
unborn sentinel
Even if this is for a prototype, they're going to want you to be up to code
deft badge
https://en.wikipedia.org/wiki/Data_Protection_Act_1998
https://en.wikipedia.org/wiki/Computer_Misuse_Act_1990
https://en.wikipedia.org/wiki/General_Data_Protection_Regulation
these come to mind
tardy sable
the law aspect of it has been handled by the other guys
after all the project is council approved and any further movement requires the council's approval.
unborn sentinel
You still need to know it as someone who is going to be building the framework of this
tardy sable
Okay, I'll take a look.
unborn sentinel
Don't rely on "the other guys" to always cover your ass
If you want the government to hire you, you have to know your shit, not pass it to another
tardy sable
Alright, will do.
deft badge
Especially in the UK case, you'll have to look very deeply into the laws regarding this because you might be applicable to some exception cases regarding health data handing (iirc the DPA has some clauses regarding this)
unborn sentinel
https://www.quora.com/How-do-I-create-a-database-for-medical-records
https://www.ncbi.nlm.nih.gov/pmc/articles/PMC2805138/
These seem to refer more to US aspects, but they might still be useful to glance over
tardy sable
Okay, thanks š
torn sphinx
how would i make a database for individual users stats
like best lib and how i would do it
dull scarab
Best lib / db varies on use case
How many entries are you going to store for starters
tardy sable
okay so since the project is scheduled be started in May I'm just going to practice and research different types of databases and stuff.
outer grove
@torn sphinx I like Postgres with asyncpg
rustic yarrow
There are a lot of different types of medical databases you will want to know more about what you are working on
dusky cape
I have a feeling I know the answer to this. However I wanted to check with you guys. I am using Sqlite3 for my PyQt5 program. Currently tweeking an Update Statement for a ledger I have built. Without Backtracking and inputing a transaction id into the table (which I might). I have gotten the following statement to work.
The question is: Is my statement getting to long to be properly handled in the program and thus timing out
updateStatement = "Update " + modifiedLN \
+ " SET Transaction_Date='" + widgetlist[2].date().toString("yyyy/MM/dd") \
+ "', Transaction_Method='" + widgetlist[3].text() \
+ "', Transaction_Description='" + widgetlist[4].text() \
+ "', Category=' " + widgetlist[5].currentText() \
+ "', Debit=' " + widgetlist[6].text() \
+ "', Credit=' " + widgetlist[7].text() \
+ "', Note=' " + widgetlist[8].toPlainText() \
+ "', Status=' " + status \
+ "' WHERE Transaction_Date='" + widgetlist[1].item(row, 0).text() \
+ "' AND Transaction_Method='" + widgetlist[1].item(row, 1).text() \
+ "' AND Transaction_Description='" + widgetlist[1].item(row, 2).text() + "'"
# + "' AND Debit=' " + widgetlist[1].item(row, 4).text() + "'"
# + "' AND Note=' " + widgetlist[1].item(row, 6).text() + "'"
# + "' AND Status=' " + widgetlist[1].item(row, 7).text() + "'"
rain field
someone answered me already in private but thanks, if Google was the answer i wouldn't of had asked
pine pivot
why should i have SQL queries take up 2 lines instead of 1? SQL SELECT title FROM films;vsSQL SELECT title FROM films;
patent glen
it makes more sense to break up sql queries into multiple lines if you have a bunch of stuff
lots of columns, joins, big where clause, etc
pine pivot
oh alright
jagged shore
Question maybe you guys can help with in #help-falafel
pine pivot
why not post it in here?
jagged shore
^Wrong question :^)
pine pivot
what
pine pivot
should I use SQL or MarieDB?
what are the advantages to learning each
i heard theyre basically the same except one is open source
nova hawk
MariaDB strives to be a drop in replacement for MySQL.
It won't make a difference when learning
torn sphinx
learn Big Query
pine pivot
why?
shell chasm
@pine pivot SQL is a standard language - Structured Query Language - used for querying relational databases (and, in modified forms, all sorts of databases). MySQL is a software implementation of a relational database. The first is a language, the other is a specific piece of software. Other relational DBs very similar to MySQL are Postgres and Microsoft SQL (among others).
pine pivot
so is MySQL like pandas?
shell chasm
no
pandas is a library for data analytics
an API, if you will
MySQL is software. you install it, you run it.
pine pivot
okay so what would i use MySQL to do then?
pine pivot
so a software version of pandas without the flexibility?
opal zephyr
Anyone with experience with peewee-async that can hit me up with an example of how to get multiple rows from a mysql database? I have tried using the Manager.get method, but it seems that's not supposed to be used for retrieving more than one row and the documentation is quite lacking so I am unsure what to try from here.
Thanks in advance!
novel juniper
got a question for anyone familiar with sqlite3 in python
I've got a relational database system for a revision website where I have a main table of items thatincludes the ItemCreatorID, MainitemID and ItemType it is, with the MainItemID acting as a foreign key in all the tables of different ItemTypes. if I delete a record from the main table, will it automatically delete from the other tables that link to the record via a foreign key, and if not, are there settings that I need to include in the main table in order to do so?
def create_revision_table():
sql = """create table RevisionItem
(ItemCreatorID integer,
ItemType text,
MainItemID integer,
primary key(MainItemID))"""
create_table("neaprototype.db", "RevisionItem", sql)
def create_flashcard_table():
sql = """create table FlashCardItem
(ItemTypeRef text,
RevisionItemID integer,
FCItemID integer,
Term text,
Definition text,
Subject text,
Topic text,
VoteCount integer,
primary key(FCItemID)
foreign key(RevisionItemID) references RevisionItem(ItemID)"""
create_table("neaprototype.db", "FlashCardItem", sql)
odd cloud
hey all! quick question. a bit new to django. I'm tryinig to limit to only the last most recent entry of objects on my .get with [:1] at the end but it's still telling me there are a number of rows
what could i be doing wrong here?
ionic pecan
this seems to be what you want
worn harness
is there a specific python library required to interact with .odb files?
Trying to learn python and databases at the same time :/
only have open source software (libre-office)
gilded narwhal
@pliant cedar https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executescript you can with this method
š
raw onyx
hello, im new and learning databases, and im trying to add blob values to my database
my_list = [1, 2, 3, 4]
c = conn.cursor()
c.execute('CREATE TABLE IF NOT EXISTS a_table (item BLOB)')
conn.commit()
c.execute('INSERT INTO a_table(item) VALUES (?)', (my_list,))
the thing is, i get this error:
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.
what am i doing wrong?
zenith anvil
Have you tried breaking it apart or do you have which line is actually causing the runtime error? @raw onyx
raw onyx
line 11, in <module> c.execute('INSERT INTO a_table(item) VALUES (?)', (my_list,)) sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.
right here
zenith anvil
Possibly because your (my_list,) is a list of truple, with an extra index/element. Try removing the ,
Let's start with that.
raw onyx
okay, i'll try that
on the same line, i get this error
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 4 supplied.
zenith anvil
Oh
raw onyx
yeah
zenith anvil
Do this SQL
raw onyx
also, is it because im creating the table incorrectly?
zenith anvil
Mm.. I thought you wrote your insert cmd wrong, but I looked it up real quick it make sure.. Which isn't the case. Mmm
Hold on, your using postgresql's sql library correct?
raw onyx
im using the builtin sqlite3 library for python 3.6
zenith anvil
Ohhhhh
Wow that looks similar to PostgreSQL's library.
Umm..
I was about to send an old Github project that I wrote for PostgreSQL
Idk, if this can help you but... I'm looking through my old code, it's been awhile. š
So I used the {} over the ?.
raw onyx
from what i heard, it's a bad idea to use .format for your sql queries
because somebody might try to exploit that
zenith anvil
Yes, because that like inserting it by hand.
Wait.........
Your inserting a list into a single column.
Moving your code down here:
my_list = [1, 2, 3, 4]
c = conn.cursor()
c.execute('CREATE TABLE IF NOT EXISTS a_table (item BLOB)')
conn.commit()
c.execute('INSERT INTO a_table(item) VALUES (?)', (my_list,))
So since blob a binary thing, this isn't the best approach. Typically when I'm storing arrays or any type of dictionary, tuple, etc. I store it in a JSON format.
raw onyx
i updated my code to do that before you posted your idea
still this error:
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.
zenith anvil
With that you can modify it, how ever you like. You technically could use binary buffers. But that's not really ideal in a learning stage.
So let's write something that can store a list.
raw onyx
alright
wait, hold on, so the blob datatype is meant for just storing binary data?
because i thought it just stays the same way it was put in the database
zenith anvil
import json
cursor = conn.cursor() # Your SQL Cursor
# We are going to give it a MAX Support for VARCHAR.
# SERIAL basically gives it an auto updated ID as more get added.
cursor.execute('CREATE TABLE IF NOT EXISTS a_table (id SERIAL, data TEXT NOT NULL)')
cursor.commit() # Creation of Table and Execution of that.
# Add Some Data
myData = [1, 2, 3, 4]
cursor.execute('INSERT INTO a_table (data) VALUES (?)', (json.dump(myData)))
cursor.commit()
so essentially, im inserting JSON text in to my database and then retriveving it as JSON?
zenith anvil
Yup.
Because Array cannot really~ be added to a Database, because their more of an Memory Address linked together.
But there's other methods like binary buffers, which are more efficient. But I don't know, how to do that. Because I never needed to worry about it. Since I section my JSON data.
raw onyx
or technically, i could do
str(my_list)
c.execute('My SQL code')
list(my_list)
To do what?
raw onyx
like, store my list as a string, and then retrieve it as a string, and then convert it later to a list
raw onyx
alright, i'll see what i can do
zenith anvil
Mmka, yeah hopefully this gives you some direction. š
raw onyx
thanks
zenith anvil
Np @raw onyx
glad bobcat
It's more SQL than Python but I have a question regarding tables sizes
Is there a way to efficiently compress a table that's rarely accessed?
I've tried ROW_FORMAT='COMPRESSED' but it didn't change anything
(and there's a lot of redundancy in this table)
This is the table in question, which represents items for a player in a game. From my limited understanding of RDMS, it is good practice for them to be in their own table.
I even used a surrogate key to make the table as light as possible.
Despite this, this table is 800Mb heavy for ~18 million rows.
For example, this table, with exactly the same number of rows, has the same size:
It's really weird to me, seeing that it has the same primary key, and floats values with very low redudancy. I think I screwed up somewhere.
torn sphinx
what would Simplest database be for saving user stats
shell chasm
@torn sphinx "Simplest" how?
torn sphinx
Hmm
Well I kinda want a spreadsheet sorta thing
hmm
Kinda like a dictionary
like
316373998378418176
ā¢ Coins: 10
542877415152680963
ā¢ Coins: 10
and easy to get and post data
shell chasm
Personally I'd recommend Redis, but I admit I'm a big fan of it. It does have hashes as a native data type, which would fit exactly what you're describing (in fact, the example they use here is for saving user information!): https://redis.io/topics/data-types-intro#redis-hashes
what you're describing is more-broadly called "NoSQL" sometimes, aka in-memory key-value data stores. there are a bunch of slightly-different implementations of that idea. I think Redis is better than them. š
however, when you say "spreadsheet" you're more-closely describing tabular data which folks usually would say best fits in a traditional RDBMS like MySQL/MSSQL/Postgres/etc.
keen rock
So I just finished learning discord.py and now I want to learn how to incorporate a database with it. Which language is best to learn?
ionic pecan
SQL
ionic pecan
oracle is proprietary and made by oracle
if you're looking for a database that works with SQL, go for postgres
fresh dove
i wouldnt recommend postgre unless you dont mind the speed, and you have a need for a large, complex database
(and optionally work with java and complex procedures)
if you need flexibility and scalability with a large database, oracle is great.
if you need a high degree of replication and low concurrency, and dont plan to scale up much, then go for mysql
ionic pecan
yes, i'm sure someone who wants to learn sql needs any of those things
do you work for oracle, or what?
bright pelican
I do recommend postgres
ionic pecan
@keen rock to give you some resources, this includes full database server setup along with a good introduction to SQL to get you pretty far
if you have any questions, feel free to ask here
ionic pecan
It's great for getting started
in a production setting you'll usually want to go for a database server, although I've also read people using sqlite for terrabyte-big databases
plain radish
yes, SQL is a good language to get acquainted with.
There's different flavours, but the differences are minor and there's an overall standard, so you can pick pretty much anything and learn starting with it. SQLite is fine to start with since it's simple to create a db with it, and it's simple to develop with.
silent tapir
I have a mysql database and connect to it using a pool with aiomysql. I noticed that aiomysql seems to load the database into cache. If I change an entry inside a table, the change is not picked up by the pool. Is this the intended behaviour?
ionic pecan
can you send some code showcasing where you see the issue
silent tapir
A simple example:
async with self.bot.pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(f"SELECT `value` FROM `test`;")
test = await cur.fetchone()```
returns for example 20
if I change the value now with for example phpmyadmin to 40 and run the query above again, it still returns 20 instead of 40
floral mason
@silent tapir it may be due to your transaction isolation
To ensure that youāre getting the most recent data on read, make sure you always commit at the end of every read, otherwise new data wonāt be visible, since that write has occurred in a transaction that happened after you began your read transaction
silent tapir
that means I just add await conn.commit() after fetching?
floral mason
You should also be able to set your read cursor to autocommit, as thatās a part of the DBAPI spec
Which will essentially ensure thatās done for you
User warning ā ļø you probably only want autocommit on reads
If you have it set to true for writes just be aware of it and make a note of it in your code so you donāt forget
silent tapir
I see
I will take a look at it how far I can set this since it does not seem like I can set it for reading only
undone apex
if there is nothing populating my cursor what does fetchall return?
I figured it out nvm
full geyser
Ah k
full geyser
Any chance u know how fetchone works?
undone apex
I do not š¦
full geyser
Like if I make a query, fetchone, and then make another query... will the rest of whatever was in the first query be returned with whatever is in the second queries result?
Or is it disagreed?
*discarded
queen dove
I am pretty new to mysql, and am having some problems understanding the locking for InnoDB.
I have some code that seems to run perfectly fine when I had them under MyISAM. I decided to see how InnoDB works, but it's causing everything to lock. Just a simple SELECT will put a lock that prevents any inserts or updates from running. What should I be doing differently with the switch? Do I have to manually unlock rows after every select?
copper sphinx
I don't know if this is related to databases exactly, but I'm using SQLA ORM, and I need to figure out how can I combine the result from two queries so I get one string?
def get_etunimisukunimi(user_id):
settings_value_etunimi = int(2)
settings_value_sukunimi = int(3)
permission_value_1 = int(1)
permission_value_2 = int(2)
query1 = db.session.query(Profile.settingsvalue)\
.filter(Profile.userid == user_id)\
.filter(Profile.settingsid == settings_value_etunimi, Profile.permissionsvalue == permission_value_1)\
.all()
query2 = db.session.query(Profile.settingsvalue)\
.filter(Profile.userid == user_id)\
.filter(Profile.settingsid == settings_value_sukunimi, Profile.permissionsvalue == permission_value_2)\
.all()
if query1 and not query2:
return jsonify(data=query1)
elif not query1 and not query2:
return {'status': 'failed'}, 204
return jsonify(data=query1 + query2)
Hey I'm trying to use PostgreSQL with the asyncpg library as such:
conn = await asyncpg.connect(user='postgres', password='a_pass',database='keywords', host='a host)
ds = await conn.fetch("SELECT word FROM keywords WHERE userid = $1" , str(ctx.author.id))
However once I fetch how can I deal with the data that this action returns?
The data is returns looks like : [<Record word='Faker'>, <Record word='nike'>, <Record word='jkjk'>]
torn sphinx
Yes iterate through the list, but how should I deal with the <Record word= and the '>
dull scarab
ds[1]["word"] should get you nike in this case
torn sphinx
Ahh ok. Thank you
dull scarab
If you have multiple variables you can unpack them
patent glen
yeah those aren't actually strings containing "<Record", they're objects
torn sphinx
Yes, I tried a split earlier, didn't work
dull scarab
if you select word1, word2 from sometable
then you can split the record instance into 2 variables
word1, word2 = record
not quite sure if that's possible with 1 value in record
torn sphinx
Ah ok. I don't think I need that at the moment. Will use the indexing to get them. Thanks!!
dull scarab
word, * = record maybe?
patent glen
word, = record
dull scarab
Ah, wasn't sure if that worked
patent glen
[like i don't use that framework but if it's like any other sequence]
dull scarab
or word[0] should probably work as well
torn sphinx
Alright. Thanks!
patent glen
yeah, but the comma lets you also do for word, in result
dull scarab
True
patent glen
of course, using tuple syntax for unpacking is only a convention
dull scarab
Depends on use case
patent glen
you're free to for [word] in result instead
abstract herald
is my sql syntax incorrect?
async def profinsert(self, did, k, v): #discord id , key, value
async with (await connect()) as conn:
async with conn.cursor() as cur:
try:
await cur.execute(f"REPLACE INTO profile(did,{did}) VALUES(%s,%s)", (k, v)) #profile is the table
conn.close()
return True
except:
conn.close()
raise KeyError
using aiomysql
await cur.execute(f"REPLACE INTO profile (did,{k}) VALUES (%s,%s)", (did,v)) that looks more correct? i think?