Hi
I want to be able to ask for a genre of movie in my sqlite database
How would I code it so the user can input the genre they are looking for
#databases
marble osprey
pseudo summit
@ashen lintel My post above still stands. You don't need the quotes around %s
marble osprey
So far it looks like:
SELECT title
FROM tblFilms
WHERE genre = " "
ORDER BY filmID DESC
pseudo summit
%s is a placeholder, it implies the appropriate quotations so you don't have to
ashen lintel
if i dont put " ' " i get an error
ashen lintel
yes
ashen lintel
int
CREATE TABLE IF NOT EXISTS vrp_users(
id INTEGER AUTO_INCREMENT,```%s > %d
?
@pseudo summit
pseudo summit
yes
ashen lintel
the error its still there
marble osprey
:o
pseudo summit
and the error is?
@marble osprey sorry, trying to help str first, then I can try to help you too
ashen lintel
proven arrow
@marble osprey Im assuming you already know how to connect to the database and execute queries. Then you can do something like this.
cursor = connection.cursor()
cursor.execute("SELECT title FROM tblFilms WHERE genre= ? ORDER BY filmID DESC", (user_input,))
result = cursor.fetchone()
The ? is a placeholder and makes this a parameterised query. So the ? will be substituted by the parameter user_input.
fetchone() method will get the result from that query.
ashen lintel
@marble osprey Im assuming you already know how to connect to the database and execute queries. Then you can do something like this.
cursor = connection.cursor() cursor.execute("SELECT title FROM tblFilms WHERE genre= ? ORDER BY filmID DESC", (user_input,)) result = cursor.fetchone()The
?is a placeholder and makes this a parameterised query. So the?will be substituted by the parameteruser_input.
fetchone()method will get the result from that query.
@proven arrow thanks bro
pseudo summit
@ashen lintel I think you still have quotes around your %d?
ashen lintel
thanks
bro
look
=))
it works
:))
@ashen lintel I think you still have quotes around your %d?
@pseudo summit thanks to you too :))
proven arrow
That is insecure so you should avoid doing that. @ashen lintel
What you had before was the correct approach.
ashen lintel
😅
marble osprey
look at printgenre the rest is irrelevant
Can you fix that to work for me
I tried and it got stuck on cursor.execute not being an attribute of a built in function
Then when i removed that bit it displayed nothing so
proven arrow
You said you wanted to take in user input? Where is the input?
proven arrow
Well you should try paste code here or in a paste service if its too long, otherwise i just have to type it out again
And show the code you tried, that didnt work
marble osprey
I already deleted it
It got stuck on the cursor.execute bit with the bottom three rows and didn't print anything without them
proven arrow
Your error regarding the built in function is because of your third line
You have connection.cursor when instead it should be connection.cursor() since cursor is a function so your missing the brackets. Anyways you should avoid using global cursor like that.
def printgenre(user_input):
conn = sqlite3.connect("MyFilms.db")
cursor = conn.cursor()
sql = "SELECT title FROM tblFilms WHERE genre= ? ORDER BY filmID DESC"
for row in cursor.execute(sql, (user_input,)):
print(row)
Thats kind of how it should be
marble osprey
Sorry
Most of this is me editing examples my school has given me
So i need to put an input named user_input into my function?
proven arrow
When you call the function, it will ask you to provide an parameter for that function which corresponds to the user_input variable. So it would be the value of your Genre.
Something like, printgenre("Horror")
marble osprey
So do I need to add something because right now it's bringing up an error
Missing 1 required positional argument: 'user_input'
proven arrow
How did you use the function?
marble osprey
In a menu?
proven arrow
But how do you call it in your code?
proven arrow
So then why ask for it?
marble osprey
Well I want to pass something into user input that is what I've been tryong to do the whole time
Get it to ask for a genre then print all the films in the genre
proven arrow
Then that should be your question, and what you should have tried to solve first. How is the application being ran? If terminal, then use input() function
toxic jay
Hi so I was using pickledb and was wondering how to use lappend
marble osprey
I'm a complete novice
Alot of what you're saying is not coming across to me at all
I'm running it in python¿
proven arrow
Then you might be better off asking your question in #python-discussion or in a help channel #❓｜how-to-get-help since your question on how to take user input is not database related. If you say your a novice, and this seems too much, then i would recommend breaking things down and solve the problem that way, rather than attempting it as a whole. Your first question should be how to take in user input. Once you understand that, then you can proceed forward into your next task, which is using that function. @marble osprey
marble osprey
What is supposed to happen with the code you gave me?
Regarding the user_input bit
proven arrow
It answers your original question, on how user input can be used for the database query.
marble osprey
But right now it doesn't do anything because there is no input
There is nothing being passed into it
I just want to know what to write to make an input that passes a value into there
proven arrow
I already mentioned you can use the input() function to take your input.
https://docs.python.org/3/library/functions.html#input
https://www.w3schools.com/python/ref_func_input.asp
marble osprey
I know how an input works
I just need to know how to get said input to pass it's value to user_input
In printgenre
proven arrow
The example shows pretty clearly, in the w3schools link.
user_input = input("Enter here: ")
printgenre(user_input)
I got it
torn sphinx
@client.command()
async def setchannel(ctx, channel:discord.TextChannel):
idforserver = ctx.guild
channelid = channel.id
conn = await aiosqlite.connect('servers.db')
try:
await conn.execute("INSERT INTO main VALUES (?, ?)", (idforserver, channelid))
except:
await ctx.send("Oh, No a error has happend!")
return
im using aiosqlite would this work for storing a channel id and a server id in a db
heres my db layout
async def create_db(client):
await client.wait_until_ready()
conn = await aiosqlite.connect('servers.db')
await conn.cursor()
await conn.execute("""CREATE TABLE main (
serverid integer PRIMARY KEY,
channelid integer
)""")
await conn.commit()
await conn.close()
client.loop.create_task(create_db(client))
nvm i got it
limber stone
Apparently I have a syntax error at or near user:
INSERT INTO event_log(uuid,function_name,team,user,event_type,event_data,date) VALUES($1,$2,$3,$4,$5,$6,$7)
But I'm not entirely sure what that syntax error is
Can anyone maybe point out what it is?
toxic mist
anyone here?
i want the a variable to be used for comparison but in all 4 statement
including name,german_name,japanese_name,french_name
how to do it
silk vortex
does anyone know where I do the pg_dump command for postgresql?
toxic mist
can u help me
silk vortex
I tried it in cmd but it says pg_dumb is not a valid command
toxic mist
celest zodiac
@toxic mist each ? in your query has to have an accompanying argument in the list of parameters. So where you have just a, you'd say a,a,a,a
celest zodiac
oh, I think that might be because the 2nd argument (where you have a now) needs to be a tuple. E.g., (a,a,a,a)
celest zodiac
ok, it's going to be hard to help you with only screenshots of the code
I understand you're probably using your phone because you don't have a choice, but it's still going to be hard to do anything
toxic mist
ee i did got a pc recently
but making sql table is easier on mobile
coz i have an app for ez editing
so i am making simple programs on it and will run bot on pc
celest zodiac
anyway, in general, posting screenshots of code and errors is going to make it hard to help you.
scenic zinc
Has anyone tested sqlalchemy v1.4?.0
shell ocean
Has anyone tested sqlalchemy v1.4?.0
@scenic zinc ooh it's OUT?
I heard it supports async
proven arrow
I tried it in cmd but it says pg_dumb is not a valid command
@silk vortex You can find the executable from wherever your postgres installation is. Should be in the bin folder.
stone blade
I would like there to be a redirect if both records are already in the database
server_ip_addr = get_server_data["address"]["ip"]
server_ip_port = get_server_data["address"]["port"]
cur.execute("SELECT ip_addr,ip_port FROM servers WHERE ip_addr=%s,ip_port=%s", (server_ip_addr,server_ip_port,))
check_ip = cur.fetchall()
if check_ip:
return jsonify({"message": "Serwer z takim adresem ip jest już na liście."}), 400
@silk vortex You can find the executable from wherever your postgres installation is. Should be in the bin folder.
@proven arrow how do I find where I installed my postgresql? I don't remember where I installed it.
proven arrow
Check in program files
steady lava
@proven arrow Hey, any suggestion for sql ?
I meant, I have never used sql and right now, I am using python here, I meant my entire project is written in python and some bash... so is it okay if I first do sql and then look for python related libraries for sql or sqlite ? or should I go with python library of sql ?
proven arrow
SQL is the query language that allows you to communicate and do operations on the Database. So you would definitely need to be familiar with that first before you pick your python related libraries (although sqlite module comes with python, so no need to look far). Regarding SQL you have two options. Either learn basic SQL, or if you don't want to write raw SQL queries you can use an ORM (you can look them up, a common one people use with Python is SQLAlchemy), which will do the queries for you. However, this might be an overkill for your project where you only might need a few handful queries in which case writing raw queries will be a better choice.
SQL is pretty high level, and very human readable, so its not too difficult to learn as well. You may want to take a look at this website which takes you through it through the browser, https://sqlbolt.com/. Maybe this is a good project for you to learn some basic SQL? So give it a try.
steady lava
thanks again for the help, Although I was using this website -> https://mystery.knightlab.com/walkthrough.html but official ones seems a better choice. The one i mentioned has implemented a mystery game which you've to solve via using sql commands.
Yes, I don't want to overkill or like implementing some features that might use high cpu usage or consume time/space.. I want to store the song links with their names, considering as a playlist of each user.
like you said last night, JSON format is not good to modify things such as select a song that all the user have in their playlist (similar songs). This might take little extra computation in python coz first you've to check the DictObj of user1, then 2, 3 4.... and so on.. then you have to extract the similar {key: value} presented in all users playlist... yeah SQL might gives me a better access and efficient too.
Thanks, I'll start the basics first, and If i stuck anywhere then I'll let you know, is that okay?
torn sphinx
Hi everyone, I am trying to insert some raw json data into a psql table. I just want to insert all of the data in one column called json. I am using psycopg2. My code was working a few months ago and successfully inserting the data into the table but coming back to it today, it is not inserting the data into the table. I get no error msg but the data is not there. Am I going about it the right way???
torn sphinx
@proven arrow ingest is the name of the schema in which the table is located. I will try without it
proven arrow
Oh ok, well Schema name is fine there then
torn sphinx
ok great
maybe I have not connected to the db properly then and have the wrong credentials if the code is looking ok
silk vortex
@proven arrow I can't find the files. I went to the apps and features settings and it shows postgresql but I can't find the postgresql folder on my computer.
nvm
I found it
this is what it looks like
I don't have a bins folder
scenic zinc
@scenic zinc ooh it's OUT?
@shell ocean well 1.4.0b1 is out. So far I prefer using it over encode/databases. I like its native support for async.
weak tinsel
can i get help for asyncpg integration with dpy?
so this is a basic function that creates a table and adds values to it
async def add_new_value_to_db(name):
db_connection = await asyncpg.connect(host=host,
password=password,
user=user,
port=port,
dsn=dsn,
database=database)
try:
await db_connection.execute("CREATE TABLE IF NOT EXISTS BOTGAMEDB(Name TEXT(30) PRIMARY KEY,POINTS INT(10) NOT NULL,DEFAULT POINTS = 500)")
except:
pass
try:
await db_connection.execute(f'INSERT INTO BOTGAMEDB VALUES("{name}",500)')
await db_connection.commit()
except:
pass
await db_connection.close()
where host, password etc are the values given in heroku site
this does not work though
the bot command that calls the function is :
@bot.command()
async def register(ctx, member: discord.Member):
await ctx.send(member.name)
await add_new_value_to_db(member.name)
ping me :))
proven arrow
@weak tinsel Your create table syntax is wrong
weak tinsel
oh?
i know mysql
so i thought it is the same in postgre 😅
what is the correct syntax? @proven arrow
proven arrow
CREATE TABLE IF NOT EXISTS BOTGAMEDB(
Name TEXT PRIMARY KEY,
POINTS INT NOT NULL DEFAULT 500
)```
Your default was incorrect and the text/int dont take length in brackets.oh
ok so if this is indeed correct
will my function work?
as in will it connect to heroku and store data?
proven arrow
And use parameterised query for your insert. 1) its safer and 2) what you have currently wont work since you double quote the name value (it expects single quotes)
proven arrow
Also i don't think asyncpg has a method called commit() under connection. I don't remember on top of my head, so you may want to double check their docs, since it may commit itself.
weak tinsel
hmm
i remember there being a commit()
but i will check again
so you are saying that it auto-commits?
proven arrow
Yes (but double check documentation)
weak tinsel
hello
so i changed it into parameterised query form
and tried with and without commit()
the table is created but values are not inserted
async def add_new_value_to_db(name):
db_connection = await asyncpg.connect(host=host,
password=password,
user=user,
port=port,
dsn=dsn,
database=database)
try:
await db_connection.execute("CREATE TABLE IF NOT EXISTS BOTGAMEDB(Name TEXT PRIMARY KEY,POINTS INT NOT NULL DEFAULT 500)")
except:
pass
try:
await db_connection.execute("INSERT INTO BOTGAMEDB VALUES('{}',500)").format(name)
# await db_connection.commit()
except:
pass
await db_connection.close()```@weak tinsel Because your using format on execute. And that is still unsafe. Someone can still inject something like name = "';DROP TABLE BOTGAMEDB;--'"
proven arrow
await db_connection.execute("INSERT INTO BOTGAMEDB VALUES($1,500)", name)
Thats two variables there, see again. One is $1, other is 500. That 500 is what you had, i just copied it over.
wild wedge
hey guys a quick question. I am trying to connect to a mysql server using python (mysql.connector) on windows and I keep getting this error. (10060 A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond)
pallid badger
Create a student table and insert data. Implement the following SQL commands on the student table: ALTER table to add new attributes / modify data type / drop attribute UPDATE table to modify data ORDER By to display data in ascending / descending order DELETE to remove tuple(s) GROUP BY and find the min, max, sum, count and average..
torn sphinx
is this for homework @pallid badger
pallid badger
Nope! @torn sphinx
I've created student table and also inserted few data.
You may see it here!
wild wedge
@proven arrow I figured it out. Thanks for the assistance
pallid badger
How to fill data in email one!
Also how to use ORDER By to display data in ascending / descending order DELETE to remove tuple(s) GROUP BY and find the min, max, sum, count and average.
@torn sphinx
pallid badger
I also asked there 10 hours ago but no one gave answer there!
pallid badger
Actually it's from my project.
Teacher has given few python and sql work.
I've done all python one but stuck in sql one!
torn sphinx
hi, I am using SQlite 3 for my database. How do I turn values from a table into a list so that I can use the list in a for-loop? Or, how can I use those values in a for-loop? I am wanting it to loop through all the values for 1 value. Or, how do I get every single value from all the values that are in the table?
rough hearth
What are the alleged virtues of mongodb?
torn sphinx
MongoDB doesn't work and sucks because of that
brazen charm
@rough hearth as a general question? or for a particular use case
rough hearth
@brazen charm general question. I'm using it for a project and I don't understand why the extra steps are worth it.
brazen charm
overall i find it pretty meh
a simple docker container and basic db setup is fine but after that its pretty pointless especially after i got to grips with the Scylla monster
torn sphinx
how to i make a steady connection with my postgresql DB so i dont have to reconnect every command so it only connects when the code is run. i am using asyncpg . ping me so i see it faster pls
(this is for a discord.py bot)
earnest parcel
Just store the connection in a variable and reuse it?
torn sphinx
Just store the connection in a variable and reuse it?
@earnest parcel i need to use await and await cant be outside of a def
earnest parcel
You can use asyncio.create_task to run the coroutine
torn sphinx
im using asyncpg
earnest parcel
I know
And you can use asyncio.create_task yo run the coroutine
Since asyncpg uses asyncio
earnest parcel
Ok?
torn sphinx
sorry im not really experience with the DB stuff
i have it connecting per command rn
earnest parcel
import asyncio
class Db:
async def connect():
self.conn = await asyncpg.connect(...)
db = Db()
asyncio.create_task(db.connect())
async def do_something():
await db.conn.fetch(...) #do stuff
That's a minimal example
torn sphinx
so question where wuld i put all my commads tehn
earnest parcel
You can use asyncio.Event to ensure the database is connected and asyncio.Lock to ensure exclusive access to the conn
so question where wuld i put all my commads tehn
How you implement this into your code base is up to you
torn sphinx
TypeError: connect() takes 0 positional arguments but 1 was given```self has a red line
earnest parcel
Just add the self argument
async def connect(self):
oh ok
asyncio.create_task(db.connect())
File "C:\Program Files (x86)\Python38-32\lib\asyncio\tasks.py", line 381, in create_task
loop = events.get_running_loop()
RuntimeError: no running event loop
sys:1: RuntimeWarning: coroutine 'Db.connect' was never awaited``` when i add awair it doesnt workawait*
earnest parcel
Are you using discord.py?
torn sphinx
yes
async def connect(self):
self.conn = await asyncpg.create_pool("postgresql://**************@db-postgresql-nyc1-********-do-user-*************-0.b.db.***************8.com:********/****8?***88=*******8")
this seems to work
do i have to close it?
earnest parcel
You can just use bot (or whatever you named your bot instance).loop.create_task(db.connect())
torn sphinx
oh ok
earnest parcel
And no you don't have to close it.
earnest parcel
db.connect()
torn sphinx
db isnt defined
earnest parcel
Well what does your code look like now?
torn sphinx
async def connect(self):
self.conn = await asyncpg.create_pool("8888888888888")
client.loop.create_task(db.connect("88888888888888"))
What happened to the class?
torn sphinx
class Db:
async def connect(self):
self.conn = await asyncpg.connect(1111111111111111111)
db = Db()
asyncio.create_task(db.connect())
async def do_something():
await db.conn.fetch(...) #do stuff
client.loop.create_task(db.connect("1111111111111111111111"))
class Db:
async def connect():
self.conn = await asyncpg.connect("credentials")
db = Db()
client.loop.create_task(db.connect())
ohhhh ok
do i put the loop at teh eend of my code?
i have users table, and guilds table. User table has FK guild_id, and this is the PK of guilds table. Guild table also has FK owner_id, which is a user_id and PK from users table. How i can get all guilds, and the owner of that guild from users?
class Db:
async def connect(self):
self.conn = await asyncpg.connect("creds")
db = Db()
@client.command()
async def coolbeans(ctx):
await conn.execute
client.loop.create_task(db.connect())``` it says conn is not defineedanyone? smokey you know?
earnest parcel
db.conn
torn sphinx
db.conn
@earnest parcel You sir are a god of life it works thank you sm
who else uses asqlite?
proven arrow
i have users table, and guilds table. User table has FK
guild_id, and this is the PK of guilds table. Guild table also has FKowner_id, which is a user_id and PK from users table. How i can get all guilds, and the owner of that guild from users?
@torn sphinx Its hard to give an exact answer without seeing your table structure but you can useJOINfor this.
SELECT * FROM guilds_table
INNER JOIN users_table
ON guilds_table.user_id = users_table.user_id;
Generally something like this to make the join. You can replace table names with your actual table names, and change the * for whatever columns you require from the respective tables. @torn sphinx
torn sphinx
ok well let me try, and sorry for not give table. It was very long, and i didnt know how to format correctly here with the lines and stuff.
proven arrow
who else uses asqlite?
@torn sphinx Just ask your question.
proven arrow
I dont understand what you mean?
torn sphinx
I meant, who else uses the asqlite module that was created by Danny?
pretty easy to understand
proven arrow
Whos danny?
torn sphinx
Y0u Don't knOw who he is???!!
He's the creator of discord.py!!!
Oh yeah, sorry, some people don't know who the creator of discord.py is, I'm sorry for that
proven arrow
Ok, well i didnt know they are also the creator of aiosqlite.
weak tinsel
hello again !
async def fetch_db_value(name):
db_connection = await asyncpg.connect(host=host,
password=password,
user=user,
port=port,
dsn=dsn,
database=database)
await db_connection.execute("SELECT * FROM BOTGAMEDB")
amount_fetched = db_connection.fetchall()
await db_connection.close()
return amount_fetched```whats the postgre syntax for fetching all results?
because this doesnt work
proven arrow
@weak tinsel You can fetch directly. And its fetch() to get all rows.
weak tinsel
oh
proven arrow
data = await db_connection.fetch("SELECT * FROM BOTGAMEDB")
Will give you a list of Record objects. https://magicstack.github.io/asyncpg/current/api/index.html#record-objects
lusty grail
With lots of foreign keys and other constraints just looking at your tables can be super overwhelming @rough hearth
With diagram, assuming it's interactive you can isolate specific relay
meager vine
^ this
lusty grail
Of course if you work with 30-40 tables you likely can live without any
meager vine
I also find that if your DB structure is reflecting some real-life system then often diagrams are useful for helping people without domain knowledge to quickly visualise the system
lusty grail
🤷♂️ Didn't have that kind of experience, but can see how it can be
jaunty yew
who else uses asqlite?
@torn sphinx
I do, sqlite is great
dreamy cedar
Still getting this error... Even both Apache and Mysql both are now active.. Can some one please help
jolly acorn
none of those things are python
That being said, the UI says the httpd is on port 4433
jaunty yew
Or 8080
weak tinsel
does anyone know how to create connection pools with asyncpg module?
dreamy cedar
Or 8080
@jaunty yew you mean on browser?
jaunty yew
Yeah xampp says apache is bound to 4433 and 8080
Like @jolly acorn said or also http://localhost:8080
dreamy cedar
When I click on Admin button which is there on Apache it say it's working..
But, when I am trying what you mentioned it's throwing error
Any idea what should I do?
jolly acorn
Write some python and stop using phpmyadmin
dreamy cedar
Write some python and stop using phpmyadmin
@jolly acorn
I actually need to work on backend.. So, that's
the reason..
jolly acorn
MySQL comes with a native management tool
Or use a python migrations library
Or HeidiSQL.. or debug why php isn't working
dreamy cedar
Or use a python migrations library
@jolly acorn not sure what aare you saying. But sound good.. I am using VS code..
jolly acorn
jaunty yew
Mysql Workbench, better than phpmyadmin
proven arrow
@dreamy cedar What happens if you click on the admin button next to MySQL?
Or you might want to try MAMP, an alternative to xammp. Sometimes xampp breaks with things like UAC enabled, (that's what I've experienced anyways).
grave eagle
hi, iim new with working in databases, i'm making a database for my discord bot, but i dont know if what i've written is right
thinghs i've written:
'''connet to database'''
con = sqlite3.connect('users.db')
cur = con.cursor()
'''create table'''
cur.execute('CREATE TABLE IF NOT EXISTS mod(id INTGER PRIMARY KEY, warns TEXT, reasons TEXT)')
'''select user and import user'''
cur.execute('SELECT * FROM mod WHERE id= ?', (user.id,))
mod = cur.fetchone()
nwarn = mod[1]
reasons = mod[2]
'''insert values in user'''
cur.execute('INSERT INTO users(id, warns, reasons) VALUES(?,?,?)',(user.id, nwarn, reasons))
'''close database'''
con.commit()
con.close()
tell me if i'm doing good
proven arrow
@grave eagle Your creating a table called mod but you are inserting into a table called users. Maybe just change the name in insert to mod (which is what i think you meant to put anyways).
Other than that, it looks fine and it should work. Although you may want to consider normalising the data, if you want to have more tables in the future.
torn sphinx
Hi, I have sucessfully connected to a db using psycopg2 but when I use environment variables (host=os.environ['HOST'],) to connect to the db I get this error:
raise KeyError(key) from None
KeyError: 'HOST'
Does anyone know how I can connect using environment variables. I believe my code is right but there might be a configuration issue but I don't know where to look. My env variables are set in intellij.
grave eagle
@proven arrow yes x,x i misstyped
what do you mean by "normalising the data", it's the 1st time i use sqlite/databases in general
proven arrow
@grave eagle TLDR; In short its just a way of trying to make the database more efficient. So basically instead of storing data in multiple places, you store it in a single place. Essentially each table should store specific/related data and avoid storing nothing more.
More importantly its a way how we can avoid data redundancy and help enforce data integrity. Data redundancy is just when the same piece of data is stored in multiple places. As an example in your case you stored user_id as a column, and now say you had another table called giveaways, where you also had to store the user_id. Thats fine, however what if the user_id changed? It would mean you got to update it in both the tables now. This is data redundancy which you should try and avoid by having a single source of truth (i.e. store it in a single place, and make reference to this, so if it needs changing its changed in a single location, where the changes are reflected everywhere).
If your thinking then how to link the tables together if you were to split the data up, you can link them through Primary and Foreign Keys. I wouldn't worry about it too much, just understand the concept and why its important, and everything else will fall in place. No system is perfect, however these are just ways to make your life easier, and your systems more efficient. You will probably fine more information on the internet that may help, but this is what i could think of on top of my head.
steady lava
Hey everyone,
I just installed mariaDB on my manjaro distro.. had to follow a medium article to install it and run it (default port = 3306)
Now, I'm trying to login as a root user but whenever I execute the command
mysql -u root -p and hit enter when password prompt was appeared, don't know why but it was showing this error :
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
Although at the time of installation, i passed mysql value to the flag --user here :
# mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
so, does it mean, only this user mysql has an access to it ? even though i tried passing other names to this flag but didn't work.. so yeah, this user is dedicated to mysqld service ?
nvm, running that command with sudo did the task haha
icy fable
Hello everyone! I have been learning about ORMs (focused on SQLAlchemy) and I created my first attempt at a database using that tool. Would anyone be able to take a look at my ERD (https://github.com/Majestic95/BellBankLMS/blob/master/BBLMS - ERD.JPG) and my short code block (https://github.com/Majestic95/BellBankLMS/blob/master/Database_test.py) to critique the syntax or structure of the code? I want to make sure I'm creating one-to-many and many-to-many relationships correctly Thank you to anyone helping!
quasi yew
how do i find an empty row in an excel file using openpyxl? to fill info with.
proven arrow
@icy fable Diagram doesnt look right
glass gorge
having some issues inserting something into mysql db
pymysql.err.DataError: (1366, "Incorrect string value: '\\xE2\\x80\\x8BThe...' for column 'content' at row 1")
i did some research and \\xE2\\x80\\x8B corresponds to zero width utf8 character
some preliminary googling showed that i should switch the character encoding of my table to utf8
idk what the implications of that would be to my information
icy fable
@proven arrow Lol could I bother you for what looks wrong about it? Also, did the syntax make sense in the code block? Thanks for taking a look at these, I appreciate it
dusky plaza
Guys I need some help querying PostgreSQL. I have a user table with uuid column. However I just cannot select using it in a where statement no matter what I try
ERROR: column "020c5727-9ccf-4f6a-a846-246f8580830a" does not exist
LINE 1: SELECT * FROM users WHERE uuid = "020c5727-9ccf-4f6a-a846-24...
^
SQL state: 42703
Character: 34
the full query SELECT * FROM users WHERE uuid = "020c5727-9ccf-4f6a-a846-246f8580830a"
proven arrow
@icy fable For many-to-many: The arrows should be the other way around (many should point to the pivot table). Also on the pivot table you only have a Not Null constraint, so your missing the others like PKs
Regarding the other side, shouldnt it be: A curriculum has many modules, and each module can have many quizzes? But thats up to you on how you see it (you know your design/intentions better)
Module Table - (maybe rename the FK so its called curriculum_id, since that is what it should be referencing too anyways)
Quiz table - It has no FK field, even though you have a 1-M relationship on it.
Curriculum table - I dont see the purpose of having the curriculum_id/quiz_id/module_id columns.
As a hint: If Table A, has a one to many relationship with Table B, then the foreign key fields should be in Table B.
The child has the FK, so the "Many" side has FK over the "One" side.
@dusky plaza use single quotes around your value, not double
dusky plaza
@proven arrow Can't believe that was the error lol. Thanks
proven arrow
Can't believe that was the error lol. Thanks
Double quotes for identifiers
plis halp lufthansa plane man
@glass gorge Ive just finished work. Home time now 😛
proven arrow
But utf-8 is fine
glass gorge
:/
dusky plaza
It looks like you are inserting bytes to me
glass gorge
i mean should i be managing this in my python code
or should i just convert the table to utf 8
ALTER TABLE roll_call MODIFY content VARCHAR(10000) CHARACTER SET utf8;
dusky plaza
well converting table is easier so try that first
glass gorge
currently the character set is Latin1
dusky plaza
but I would definitely handle that inside the code
glass gorge
ALTER TABLE roll_call MODIFY content VARCHAR(10000) CHARACTER SET utf8;
i ran this and im waiting for it to process, it's taking a long time for a table with basically nothing in it
it's been almost an hour and it hasn't stopped processing
umbral osprey
kill the process and try running it with debugger this time, might work
glass gorge
kill the process and try running it with debugger this time, might work
@umbral osprey is that done in themy.cnf
umbral osprey
try that
torn sphinx
@torn sphinx
I do, sqlite is great
@jaunty yew aSQlite is a coroutine version of SQlite 3, not another alias for SQlite 3 lol
how do i print all teh names of the tables in my db
@client.command()
async def tables(ctx):
tables = await db.conn.execute('''SELECT * FROM ''')
print(tables)```what would i add to print the tables?
im using postgresql
proven arrow
@torn sphinx try this, #databases message
glass gorge
yeah idk
torn sphinx
@torn sphinx try this, #databases message
@proven arrow it printsSELECT 3
glass gorge
try that
@umbral osprey im stuck
proven arrow
You want fetch instead of execute
torn sphinx
ohhh
umbral osprey
then you might have to run your software as a admin so it gets more ram and cores dedicated to it
if that doesnt work, might either be your software, or your code is somehow looping
all i can think of
glass gorge
its run as root
and my pymysql config has utf encoding already
def db_connection_content():
return pymysql.connect(host=os.environ.get('HOST'),
user=os.environ.get('PYMSQL_USER'),
password=os.environ.get('PASSWORD'),
db='content',
charset='utf8',
cursorclass=pymysql.cursors.DictCursor)```hmm i may be trying to debug under root and not the admin account
umbral osprey
then maybey your software couldnt handle it, if you have another software, try that, doesnt work i dont know anymore
umbral osprey
i ment your computer
glass gorge
i see
umbral osprey
is it overheated
umbral osprey
i mean i dont know alot about software and thier cpacity
umbral osprey
np
errant knoll
I want to use mongodb (or potentially similar) as a database for my discord bot using the discord.py library. It seems like my two options are pymongo or motor, however, motor says it is the one to use with asyncio (what discord.py uses). Does anyone who has some experience have any recommendations?
brazen charm
generally the recommended db is Postgres with asyncpg
because:
- Its the most performant DB
- It has the most performant async driver
- it has the most correct and sane driver
- most if not all of the data you'll encounter with your bot will be relational and therefore suited to SQL
Motor used to be really really bad overall
not as bad now that they finally dropped py2 support
but still pretty awkward in places and slow
but mongo in general is like that
errant knoll
From my research I'm pretty sure a NoSQL db would be best for my situation, as I intend on adding new things regularly as well as the fact that I potentially will have different data for each user...
vernal spade
I am creating a reputation system in my discord bot
I want it to be cross server
where should I start
postgresql or mysql, or Maria?
errant knoll
I was under the impression that with SQL you sort of needed to have a really good idea of the data you planned to store so you could create your db based on that and that if you wanted to add new fields it was a pain/takes a while?
dusky plaza
it's fairly simple and quick to add new columns
also I suggest using an orm like sqlalchemy
you can do powerfull stuff, but you can also store plain json files if you wish
daring stratus
This post dives into patterns that most data scientists/analysts/engineers have seen: ways DAGs grow, and how that leads to a compounding level of effort to maintain data systems. We’ve found this vocabulary very helpful in our own work---hope you do, too!
https://greatexpectations.io/blog/deeper-wider-thicker/
errant knoll
I'll take a look
All my user data atm is just in a few json files if that adds anything
slow river
Hey
I need some help with sqlalchemy
I have multiple instances of a model, I want to sum a specific column from all the instances
How do I do that?
left scaffold
How do I transfer ownership of a postgresql table? I tried ALTER TABLE muterole OWNER to Slio but no work
icy fable
@slow river https://stackoverflow.com/questions/11830980/sqlalchemy-simple-example-of-sum-average-min-max
@slow river Is your code open-source? I'm using Python and SQLite and I'd love to learn more from someone who is far more experienced than me!
winged cargo
hey all. Does anyone have a recommendation for a SQL database manager? Just have a few large SQL databases I just want to tidy up and search through etc - dont need network capability or anything I'm just looking for a good one 🙂 I tried postgreSQL and it's far too confusing I think
main gazelle
@winged cargo Take a look at JetBrains' DataGrip if that is something you like
https://www.jetbrains.com/datagrip/
winged cargo
sweet I shall
i dont need any advanced SQL programming features it's litterally just combining db's. removing duplicates, etc
main gazelle
I find the above really good for any work with databases no matter if it is small or big. Its just a personal preference.
tiny stratus
Hello
winged cargo
yeah im just pretty new to SQL dbs and it seems like it's just full of such advanced packages if you just google haha
im not a sysop in a ftse100 tech company managing thousands of complex dbs haha
my previous experience was MS ACCESS, then i dove into postgre and i was like oh god this doesnt look right 😄
so jetbrains datagrip is not free, is there a more open source alternative?
not going to dump £70 on it
winged cargo
oh wicked
so is SQL just a type of database yeah?
or is it a file format
well thats kinda the same thing
main gazelle
SQL stands for structured query language. Its used for databases. You could call it a programming language.
cunning copper
Hey guys, someone with SqlAlchemy experience here?
icy fable
@cunning copper I don't have a ton, but am actively trying to learn. What's your question?
cunning copper
thanks @icy fable , was about when you've to prform queries, but you don't have entities, because you are just connecting to an database and has to extract some data
I saw a lot of tutorial, but always with the models :/
winged cargo
@main gazelle thanks so much appreciate it
@main gazelle so HeidiSQL requires a SQL server in place. So not a real use to me but I appreciate it. I'll go hunt some more 🙂
main gazelle
Sorry that I couldnt be of too much help
winged cargo
no worries, some times it helps others reading this 🙂
if anyone else can recommend a local SQL database manager (dont need any client/server stuff) I'd appreciate it!
proven arrow
@winged cargo Which database are you using?
sudden phoenix
Hi all, someone knows a good/link/tuto for sqlalchemy many to many queries ?
I had two table, linked by a many many relation assoc table
I simply try go get name for table 2 where id in table is X
cunning copper
@icy fable actualy I think it is necessary to declare the database, this from sqlalchemy doc:
When using the ORM, the configurational process starts by describing the database tables we’ll be dealing with, and then by defining our own classes which will be mapped to those tables. In modern SQLAlchemy, these two tasks are usually performed together, using a system known as Declarative, which allows us to create classes that include directives to describe the actual database table they will be mapped to.
sudden phoenix
to be clear, yes I had declared the database in sqlalchemy.
like this : https://pastebin.com/QXvExe5g
winged cargo
@winged cargo Which database are you using?
@proven arrow I've got some SQL files
and some other large files csv files i'd like to add to the db
icy fable
What is the proper syntax in SQLAlchemy to create a new Object (also a new entry into the database table) when the Object's required attribute is user input? Is it new_user = User(username=?, pd=?, date_added=? WHERE (userval1, userval2, userval3))
proven arrow
@winged cargo SQL is just the query language to interact with the Database. I meant what database as in sqlite, postgres, MySQL, was those sql files created with?
winged cargo
@winged cargo SQL is just the query language to interact with the Database. I meant what database as in sqlite, postgres, MySQL, was those sql files created with?
@proven arrow I don't actually know...
@proven arrow MySQL 10.13
just looked
proven arrow
Ok well then MySQL is a client/server database, which you were trying to avoid right?
winged cargo
yeah i dont need that, just need something local on my PC you know?
winged cargo
yeah im guessing using localhost - was just wondering if there was a bit less faff way
winged cargo
to me setting up a client/server relationship on my localhost seems a tad overkill
and there must be a more simple way
but i've never used SQL before so perhasp that is me being naïve?
brazen charm
most DB's are server based
if you dont want that because its only a small project look at SQLite
which is just a single file instead
winged cargo
it's about 250gb
proven arrow
What is it you are trying to do with the database?
proven arrow
If you want something easy to setup then go with something like downloading MAMP or Xampp. This comes with phpmyadmin, which allows you to interact and manage the database over the web. Things like importing and stuff are pretty straightforward as well, and gui is quite simple.
proven arrow
It's easy to setup as well (just a single install), and you can start/close from the admin panel.
winged cargo
sweet i'll have a look
ref the other comment on SQLite
what is classed as a small db
im looking at 250-500gb
proven arrow
Maybe medium or crossing just over small.
But still small considering some have many millions of rows.
brazen charm
sqlite can go upto what? 256TB now or something dumb
winged cargo
ok thanks
proven arrow
Something like that I read. But whoever hits that in sqlite
brazen charm
but size wise you probably want something that can crunch the data a bit better like Postgres
winged cargo
okay, so now i get that DBs are mostly client/server
should i just boot up postgres and use it like that?
brazen charm
probably
winged cargo
i havent used SQL so has it got a nice GUI interface?
brazen charm
pgadmin
its a lil daunting at first because it gives you so much info
but its pretty simple
proven arrow
Yep, was just going to say that.
winged cargo
sweet - thanks a lot
ill check it out
appreciate the help, never done databases before :)!
so it seems like because my db is a mysql db i need to convert it to a postgreSQL db
ahhh hmm
maybe i should just download mySQL?
brazen charm
depends what you wanna use
i personally wouldnt use mysql because it provides nothing but downsides compared to postgres but yeah
if it already exists as a full mysql just roll with mysql
unless you plan on using it for a long time ig
winged cargo
do you recommend a good way to convert then?
or will it take ages
up for recommendations 🙂
found that - will check it out
glass gorge
uhhh quick question
when i set the character set and collation to utf8
do i have to do that by column, or can i just do it at the end for the whole table
brazen charm
Which dB?
glass gorge
mysql
glass gorge
oh?
yeah i tried to upload some stuff and it couldnt encode zero width spaces
the char set was latin1
so i tried to alter the table to utf8
and it took a really long time, so I was told to drop the table and reupload data, after i create a new table with the right encoding
brazen charm
Mysql is know for hating anything unicode
glass gorge
im about to quit programming and become a monk
glass gorge
i tried
it proved too difficult for me
the installation was lame
but yeah
still didn't answer the original question @brazen charm
errant knoll
Please ping me on reply
I am using the discord.py library, and making a game in python. Players interact with it through a discord bot so it is basically a text/command based game. But I want to host my bot on a vps and would like to set up a proper database server/system rather than what I have currently which is just a few json files that store user data as well as game data (eg. weapon stats and stuff like that). Currently to change user data I load the json file, make the change then overwrite the file - this is not a great way of doing it and most likely will not be able to cope if the player base grows. So I would need a database that supports a python API and that works with the async branch of python. I would really appreciate any advice anyone has. Here is an example of some user data:
grim pier
Hey 🙂
cursor.execute("SELECT PlayerName from players WHERE Online = 'Yes' ORDER BY PlayerName ASC")
data = cursor.fetchall()
if not data:
print(f"No players are online no bounty has been set.")
else:
victim = data
print(victim)```it prints like this [('EntropYToucH',), ('Poochington',), ('Tanyol',), ('suvipetraa',)] is there a way to select it without the brackets and commas. or should i just str replace them to remove ?
sudden phoenix
print (victime.PlayerName) gives what ?
@grim pier
in fact .. for row in data:
print (row.PlayerName)
try that
grim pier
okay ill give it a try 🙂
grim pier
just comes as row isnt defined
sudden phoenix
.... non...
else:
victim = data
print(victim)
change it to :
else:
for row in data:
print (row[0])
grim pier
That works yes but only selects the first player
Instead of printing all of them online
😛
sudden phoenix
you have done data[0] ?? past your code please..
sudden phoenix
i did not tell you tat..
grim pier
i hit backspace by accident
sudden phoenix
what is the output now ?
grim pier
The code above works but only prints the first name 😛
sudden phoenix
try that.. I can't believe it :
print(data) for row in data: print(row[0]) print(row)
WAIT ..
sudden phoenix
no data is an list .. and you have inside the list a tuple
[] -> list in python... kind of array
(X,X) the tuple
the first print data should print the object..
that's what the " for " is 🙂
for row in data
will iterate all tuple in a variable named row.
typically
sudden phoenix
are you sure they're are still in your db 🙂 ... the first print data should print all of them.
in the test i asked for before the for loop.
so the print data still print [('EntropYToucH',), ('Poochington',), ('Tanyol',), ('suvipetraa',)]
sorry it work here 🙂
grim pier
hmmm weird
sudden phoenix
can't help more .. i did not have a msql here 🙂
grim pier
Ill have a play around
grim pier
Thanks dude 🙂 will do
hushed flicker
what is the formatting when using google sheets api to only import the data from the cell instead of the whole cell details, e.g. ( <Cell R3C2 'Name1 Name2'> )
Is sent whereas i want it to just output ( Name1 Name2 )
burnt turret
I'm not very sure, but I think the avg function works on an entire column, but after the WHERE you only have access to individual rows (so it can't compute the average of the entire column)
raven falcon
ok that makes sense, thanks
that would be the reason you would have to sub-query the avg part of the code
burnt turret
Yeah
dusky siren
If I were to transfer my code from sqlite3 to aiosqlite, how much would I have to change if I just imported aiosqlite as sqlite3
Like would I just be awaiting everything, or are there massive changes
torn sphinx
idk this is dumb question but just a doubt ,cant i use msaccess instead of postgresql becoz mine is a simple bot?And if yes would there be any problems due to that?
they both are database isn t it?
grim pier
Anyone free? 🙂
sqlite3.OperationalError: no such table: bounty
The table is created though :/
elif data:
randombounty = random.choice(data[0])
print(f'Bounty has automatically been set against: {randombounty} Bounty will be active for 6 hours.')
now = datetime.now()
time = now.strftime("%H:%M")
sql = sqlite3.connect(r'databases\bounty.db')
cursor.execute(f"INSERT INTO bounty (victimname, playername, timeset)"
f"VALUES ('{randombounty}','TEC 1 Corp','{time}')")
sql.commit()
log(green("bounty database updated"))```Where are you getting the cursor from for that execute statement?
sharp dragon
Hello, i'm trying to count every occurrence of a value in each column, for each unique value in the final column, but it always returns an empty list, the only thing that returns data is a "SELECT * FROM NewData", merely trying "SELECT * FROM NewData WHERE 'External ID' = 'external ID 1'" returns an empty list, any help or advice would be appreciated
proven arrow
@sharp dragon your column name has a space, so use the backtick character ` instead of the single quotes around the column name
You can also double quote the column name as well, or also use [] around the column name.
sharp dragon
@proven arrow omg thank you so much!, ive never seen in SQL before, it works, but what exactly does do?
wait it does something in discord too, i mean (`)
proven arrow
ahah yeah discord uses it for code blocks, i had the same issue trying to show you
but in sql its for quoting identifiers (although different databases use different characters for this.)
Usually you use it when your identifier (something you create like table name, column name) has a special character or one of the reserved words, so itll treat it as a string
sharp dragon
ohh, thank you, ive been breaking my head for so long on this
steady lava
Anyone can help me here :
MariaDB [(none)]> CREATE DATABASE mydb1;
ERROR 1044 (42000): Access denied for user 'hi-man'@'localhost' to database 'mydb1'
I've granted all the permissions to hi-man on mydb but it never allows me create a database, why ?
proven arrow
how did you grant the perms?
steady lava
I did this -> GRANT ALL PRIVILEGES ON mydb.* TO 'hi-man'@'localhost';
proven arrow
did you also create the user?
steady lava
yeah i did
Actually i followed the arch wiki
$ mysql -u root -p
MariaDB> CREATE USER 'hi-man'@'localhost' IDENTIFIED BY 'some_pass';
MariaDB> GRANT ALL PRIVILEGES ON mydb.* TO 'hi-man'@'localhost';
MariaDB> FLUSH PRIVILEGES;
MariaDB> quit
You have db specific priveleges, and not access to create more dbs.
Check the rights with SHOW GRANTS;
steady lava
means, I can access the database, but not create anymore ?
proven arrow
not with that user,
steady lava
GRANT ALL PRIVILEGES ON mydb.* TO hi-man@localhost`
steady lava
ohh okay okay... please wait
ERROR 1045 (28000): Access denied for user 'hi-man'@'localhost' (using password: YES)
I got this error @proven arrow
proven arrow
Do it with the root user
steady lava
can you tell me what's the difference?
Oh wait, is it like, i can't alter or set any preferences with user hi-man ?
proven arrow
Well to do something with hi-man you need the privs
torn sphinx
can anyone help?i am making a discord bot and i wanted postgreysql so which all components should i install from the installer?
steady lava
@proven arrow Thanks sir, It worked now 🙂
but you know what other changes occured, before creating any other database I had only 1 database i.e., information_schema but now it's showing two others including the one I wanted to create
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| ronin_playlist |
+--------------------+
what other databases are used for ?
proven arrow
@torn sphinx What are the options? I think you can install all of them except for the stackbuilder option which is not always necessary
torn sphinx
proven arrow
@steady lava those are part of the database
torn sphinx
only stack builder not needed?
proven arrow
Yeah i guess you can leave that out, unless you need it?
torn sphinx
what is it used for ?
steady lava
those are part of the database
the one which i just created ?
proven arrow
pgadmin is the GUI interface so you can access the db through the browser,
torn sphinx
ok
thanks for the help
so there is only password and no username?
i am new to sql
proven arrow
the one which i just created ?
No i meant the other 3.
mysql - is the system database
information_schema - has the database metadata
performance_schema - is for monitoring certain things
You should just leave them as they are.
@torn sphinx default username is postgres
torn sphinx
ok
steady lava
ohhh okay okay got it now... they have nothing to do with ronin_playlist ..
Great, Thanks for the help 🙂
torn sphinx
what is port should i leave it as default?
steady lava
default is 3306 i guess (for mariaDB)
torn sphinx
mine starts with 5 tho
proven arrow
Yes
torn sphinx
thanks for helping me setup
knotty gyro
if i am using a DB like MongoDB, then where to download the DB in order to take data from it? am using repl.it for hosting my bot
pls ping me if u know
cunning copper
What is the proper syntax in SQLAlchemy to create a new Object (also a new entry into the database table) when the Object's required attribute is user input? Is it
new_user = User(username=?, pd=?, date_added=? WHERE (userval1, userval2, userval3))
@icy fable I use this way and works, like thisclient = self.clientModel(username=_username, email=_email, password=generate_password_hash(_password) ) self.db.session.add(client) self.db.session.commit()
If you want check my repo, could be more clear: https://github.com/acpn/brasilprev-challenge
sage scaffold
dreamy cedar
getting this error when i am trying to install pip install mysql through command prompt
dreamy cedar
Sure give me one sec
try pip3 install whatever your trying to install
@sage scaffold
Same error
dreamy cedar
Sure..
dreamy cedar
Didn't understand what that means.. What seems hard to solve
..
dreamy cedar
Okay sure.. Please let me try
dreamy cedar
sure!
@sage scaffold ohh i need 6 GB data..
knotty gyro
i need some help
am new to databases and stuff
and i wanna know how to host a mysql db online for free so that i can access data from it
proven arrow
@knotty gyro Either you can find a free host, or if you already have a server for your application, you could run it on that.
proven arrow
Also if your application is local on your PC, then you can also run MySQL on your pc too
proven arrow
What about it?
knotty gyro
Am getting that the host ain't listening to TCP/IP on the port and ip
It's IPv4
0.0.0.0
And some port
proven arrow
Is your postgres server running?
knotty gyro
Yes
proven arrow
How do you know?
knotty gyro
-_- cz I started the server myself
I will show the pic
In 2 min
Oh wait
I got the problem
psycopg2 connection timed out (0x0000274C/10060)
This problem am getting
Pls ping me if u know
Postgres
proven arrow
Double check your connection details, they are probably incorrect
torn sphinx
So i have this query,
select count(*) as aggregate from products where status in (0, 'NULL') and name like '%Tommy Hilfiger Mens Jacket%'
Double check your connection details, they are probably incorrect
@proven arrow those are correct
Wait
Should I change the node port to 5432?
torn sphinx
So this should get me results where, status is 0 or is null, but it is not, instead just give me 0 results. It works if status is 0, but not if null
proven arrow
Should I change the node port to 5432?
if thats what its running on then yeah
knotty gyro
Oh ok
proven arrow
generally if on you local pc, then it should be on localhost over that port, so you only specify the host as localhost.
@torn sphinx Because thats not how null is evaluated.
.... FROM `products` WHERE `dormant` = 0 OR `dormant` IS NULL AND `name` LIKE ....
is how you should check it
torn sphinx
ohhh
torn sphinx
should i use asyncpg or pygscop2 for discord bots?
knotty gyro
Second one
torn sphinx
why?in discord.py one person said asyncpg. whats the difference between them?
knotty gyro
It's different modules for different databases
The second one is for postgres
Libraries
torn sphinx
so if i install em both would any problems occur?
torn sphinx
i have postgres
knotty gyro
BTW
torn sphinx
but the problem is asynpg or psycopg
knotty gyro
those are different libraries for different db
torn sphinx
but asyncpg is also there for postgrey
torn sphinx
there is a github page can i link it here?
knotty gyro
?
torn sphinx
torn sphinx
oh okay
brazen charm
you should very much not use psycopg2 for bots
asyncpg is the async lib
its also faster than psycopg2 by alot
torn sphinx
ok thanks i installed asyncpg rn
knotty gyro
@brazen charm am also making dpy bot, so I should use asyncpg instead of the other?
brazen charm
yes
brazen charm
yes... Just google it
knotty gyro
Hmm ok
steady lava
anyone can tell why it didn't add the songs from the 1st Id (or say index) ?
Also, I've set Auto_Increment when defining Id (Primary key), and later I set Values to only Title column.. is this causing a problem ?
proven arrow
@steady lava it's because you never told it to insert into the first three rows.
Your first insert you are inserting three values which the database adds to the rows 1-3. The second insert is just carrying on from row 3. You would have to do UPDATE to add to the previous three rows, instead of insert.
steady lava
@proven arrow ohh yes you were right, and I guess when it didn't recieve any value, it just set it to NULL.
Now, I've used the update operation like this :
UPDATE song_info SET Path = ("/Music/LP/Pushing\ Me\ Away.mp4") WHERE Id=1;
and I had to do it three times, isn't there any other approach where I can set all the column values at once? like a WHERE condition ?
brazen charm
just remove the where
it will automatically override all rows to that if it doesnt have a where
just like DELETE does
steady lava
I've 3 different values or say three different song links to store
proven arrow
You can either insert and on duplicate update, or use update with a case statement.
proven arrow
Yeah but more like an if else
steady lava
ohh that's nice.. lemme check it out
Thanks for the help 😄
proven arrow
Also when you insert for the first time you can specify all the values then, in case you wasn't aware.
icy fable
Does anyone have any recommended code examples or tutorials/guides on SQLAlchemy? I have some nuanced questions that the documentation glosses over very briefly about program architecture (ex. Where do I place 'create_engine()' or 'declarative_base()'? Do they go into the same .py file as my table declarations?)
Any SQLAlchemy code examples or programs would be very much appreciated!
random furnace
Hello people.
I'm trying to pull data out of this mongo db. But I think it has invalid dates in them?
Because when I'm iterating through the cursor,
I'm getting hit with the
InvalidBSON: year -1 is out of range
Any suggestions on what to do?
icy fable
Is SQLAlchemy pretty dead?
hybrid horizon
I still use it, and plenty of apps use that as their ORM of choice, it's pretty mature
speaking of the beast, I'm running into trouble with it right now
I have this declarative base https://paste.pythondiscord.com/virecinoro.py
Which I use this way https://paste.pythondiscord.com/aralacoqok.py
but when I do alembic revision --autogenerate -m "message"
I get sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'bases' and 'players'
this is my player model
there's no foreign keys
I've been using this documentation page to get it working and it doesn't https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/mixins.html
anyone able to help ?
silk vortex
I'm trying to do a pg_dump command but it says access denied. Does anyone know why this is happening?
pg_dump.exe -U postgres EXO > C://Desktop this is the command I did
silk vortex
I just tried doing the same command in my admin account and it still says access denied
hybrid horizon
did you run the command shell as an administrator ?
That might help
oh I didn't see
the path should be C:\Users\Username\Desktop
the root of the drive usually is protected on windows
@silk vortex
silk vortex
oh I already got it
but I need help with something else
I did the psql command in my vps
but how do I check if the database got successfully restored?
@hybrid horizon
hybrid horizon
no idea about that though sorry
knotty gyro
Traceback (most recent call last):
File "main.py", line 11, in <module>
loop.run_until_complete(run())
File "/usr/lib/python3.8/asyncio/base_events.py", line 616, in run_until_complete
return future.result()
File "main.py", line 5, in run
conn = await asyncpg.connect(user='pterodactyl', password='ZPWgpMN4hETqjXAV',
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/asyncpg/connection.py", line 1718, in connect
return await connect_utils._connect(
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 674, in _connect
raise last_error
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 663, in _connect
con = await _connect_addr(
File "/opt/virtualenvs/python3/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 632, in _connect_addr
tr, pr = await asyncio.wait_for(
File "/usr/lib/python3.8/asyncio/tasks.py", line 490, in wait_for
raise exceptions.TimeoutError()
asyncio.exceptions.TimeoutError
am getting this error
pls ping me if u know
grim pier
it works but is there a way to get rid of the ( and ,
Any ideas anyone? 😛
shell ocean
Any ideas anyone? 😛
@grim pierlistcomprehension
grim pier
@shell ocean Would it just be better to str.replace them?
torn sphinx
h
meager vine
@grim pier they aren't strings...
grim pier
It's a list right?
meager vine
cursor.fetchall returns by default, a list of tuples
What?
The point is, those () and , are from the tuple
They aren't part of any string
so you can't replace them
Not without some transformation...
If you know that your query always returns just 1 column (or you only ever care about the first column returned) then you can use a list-comp as @shell ocean says
cursor.execute("SELECT expirytime from bounty")
data = cursor.fetchall()
if data:
data = [row[0] for row in data]
print(data)
median night
In MongoDB, am I missing something or is there no easy way to have one document per day in mongodb?
like i mean i could do something like _id = "2020-10-10" to have a unique document for each day
but then i cant query against datetime objects obviously
In general i would want to write daily statistics to a document in a collection but i should later be able to find for example the statistics of the last n days
median night
nvm i figured out that you can use $toDate and this works well for me (https://docs.mongodb.com/manual/reference/operator/aggregation/toDate/)
cinder gull
im trying to add items in a db transaction (sqlalchemy) that have a unique constraint on their name, so its flipping out when i commit because i have a whole bunch of dups
is there any way i can access pending inserts so i can check for existing?
velvet ember
Guys, pls help I use MongoDB and I need the user to be entered into 1 collection for each guild separately, but it gives
pymongo.errors.DuplicateKeyError: E11000 duplicate key error collection: ecodb.colldb index: _id_ dup key: { _id: 337954786190295050 }, full error: {'index': 0, 'code': 11000, 'keyPattern': {'_id': 1}, 'keyValue': {'_id': 337954786190295050}, 'errmsg': 'E11000 duplicate key error collection: ecodb.colldb index: _id_ dup key: { _id: 337954786190295050 }'}
elif not self.db.find_one({"_id": message.author.id, "guild_id": message.guild.id}):
await message.add_reaction(':white_check_mark:')
post = {
"_id": message.author.id,
"guild_id": message.guild.id,
"guild_name": message.guild,
"name": str(message.author),
"balance": 100,
"xp": 0,
"lvl": 1,
"minvoice" : 1,
"messages" : 1,
"reports" : 0,
"rep" : 0,
"timely": 0
}
if not self.db.find_one({"_id": message.author.id, "guild_id": message.guild.id}):
self.db.insert_one(post)
Please tell me, I really need to, but I can't solve the problem for the second day
Here is the structure of the database, it cannot add the same only with another guild and I cannot understand why, but I am writing self.db.find_one({"_id": message.author.id, "guild_id": message.guild.id})
wintry stream
@velvet ember so the problem is that mongo does not support duplicates
And you have the user ID as the primary key on what it lists everything by
velvet ember
yes, but I don't know how else to do it
wintry stream
also you're using pymongo right?
velvet ember
yes
wintry stream
which is kinda a bad choice, since discord.py is async and database requests can take some time and will cause massive blocking
motor (async driver for mongo) is almost the same but async
meaning you have no more blocking
also for the error you're having, you need to turn your logic around
since you cannot store the user multiple times
velvet ember
I'll try to do
wintry stream
you can either have a list within the user record with all guilds they are part of
or
you can have a list in the guild record with all members they have
databases are relational
so you have like 1 entry per user
and 1 entry per guild
and you put the main value (in this case the id) in one and it makes it relational
it links the 2 entries together
that's what databases are all about
relational
velvet ember
ok, got it, thank you!
viscid osprey
I've just started with the aysyncpg lib and its docs, and I have this little example program:
>>> import asyncpg
>>> import asyncio
>>> async def run():
... con = await asyncpg.connect(user='postgres')
... types = await con.fetch('SELECT * FROM pg_type')
... print(types)
...
>>> asyncio.get_event_loop().run_until_complete(run())
If I want to place and use e.g. function run() in a class, where do I put the asyncio.get_event_loop() code in the class?
Or must I have 2 functions, one with my query called __run and then another 'public' function called run, with asyncio.get_event_loop().run_until_complete(__run())?
steady lava
>>> table_name = 'user2_playlist'
>>> c.execute('CREATE TABLE ?', table_name)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
sqlite3.OperationalError: near "?": syntax error
Anyone can help me here? It is mentioned in python's sqlite docs that, whenever we need to set the value explicitly then always use this ? as a placeholder for values..
but when I did it for table name then it's showing this error... should I use f strings instead of using ? for table name ?
torn sphinx
do we need to cursor.close() and db.close() everytime we are finished with stuff?
like in def func
proven arrow
@torn sphinx You should close the cursor once done using it, but the database connection is generally global and is only closed when your application closes. Or you can use pooling. But this might differ for your use case.
proven arrow
@steady lava Not for table names. Only values.
Also why do you need to create dynamic table names?
Looks like you can better design your schema
fair crescent
Can someone help me with some PL/SQL code
fair crescent
PL/SQL is python?
torn sphinx
well yeah
fair crescent
huh didn't know that
fair crescent
It's just oracle SQL
fair crescent
DECLARE
v_numbercredits students.current_credits%TYPE;
v_surname students.last_name%TYPE;
v_comment VARCHAR(2);
BEGIN
SELECT current_credits
INTO v_numbercredits
FROM students
WHERE v_surname = 'Smith';
IF v_numbercredits > 0 AND v_numbercredits < 5 THEN
v_comment :='Not enough credits';
ELSIF v_numbercredits >5 AND v_numbercredits < 10 THEN
v_comment := 'Credits need to be improved';
ELSIF v_numbercredits > 10 AND v_numbercredits < 15 THEN
v_comment :='Credits Completed';
ELSIF v_numbercredits = NULL OR v_surname = NULL THEN
v_comment :='Student smith does not exist';
END IF;
dbms_output.put_line(v_comment);
END;
/ ```
I'm writing a PL/SQL block that will check the current_credits for a Student who’s surname is Smith.
If the credits are > 0 but < 5 display out “Not Enough Credits”
If the credits are > 5 and < 10 display out “Credits need to be improved”
If the credits are > 10 and < 15 display out “Credits Completed”;
And ensuring the code can handle a NULL being returned. If a NULL is returned display out “Student Smith Does not Exist” to the user.So can anyone help me with it please?
torn sphinx
@proven arrow i have 2 files running at one time so if i am done with the db stuff in 1 file do i have to close in that only or smt else
proven arrow
What database are you using?
Also if the 2 files are part of the same application, then you can still refer to a single connection.
proven arrow
It's just oracle SQL
the real beast, unfortunately ive never had the chance to experience it
torn sphinx
cogs for dpy
proven arrow
You can close sqlite connections since they are cheap, so should be fine if you close in both files.
torn sphinx
okii
closing db when we are done with sqlite stuff right?
and closing cursor when you are done using the cursor in a def function?
@proven arrow
proven arrow
Close the cursor when your done with it.
proven arrow
with cursor.close(), and since you mentioned its for a bot, make sure to use aiosqlite module
proven arrow
Hello, anyone know how to store boolean in mysql?
@torn sphinx just useTINYINT(1). Thats what mariadb does as well internally
So then 0 is false, 1 true.
proven arrow
Its the async module for sqlite. Since your code is async you dont want to be nuking your bot each time you make a DB call now do you. https://pypi.org/project/aiosqlite/
proven arrow
you just got to await calls
knotty gyro
the SQLite3 db insterts data as only rows?
torn sphinx
like closing db and cursor when done with "sqlite stuff" in a SINGLE file
proven arrow
every relational db inserts as rows @knotty gyro
proven arrow
@torn sphinx again close the cursor. and keep a single connection.
proven arrow
From python or sql?
torn sphinx
so it requires server starting and stuff???
fair crescent
Can anyone help me with my question? Does anyone know oracle SQL?
proven arrow
c.execute('SELECT * FROM table_name')
data = c.fetchall()
knotty gyro
@fair crescent just ask yr question here and if anyone would know, then he/she would tell 🙂
fair crescent
Well I did I was just asking again since I saw more people typing
knotty gyro
oh ok
fair crescent
Lufthansa has already said he/she doesn't know much about oracle SQL so I didn't want bother him/her
torn sphinx
so it requires server starting and stuff???
@torn sphinx
proven arrow
so it requires server starting and stuff???
@torn sphinx What do you mean?
proven arrow
Yeah sqlite is file based db so it doesnt
torn sphinx
in aiosqlite
proven arrow
aiosqlite is just an asynchronous interface for it. See the link i sent. Explains it.
And no it doesnt require a server
torn sphinx
soo it doesnt requires cursors
as i can see in that doc
cursor = db.cursor()
😕
proven arrow
I dont understand what your asking?
torn sphinx
like making a cursor
cursor = db.cursor() - for sqlite3
??????? - for aiosqlite
what should be for aiosqlite
because in the docs
cursor = await db.execute('SELECT * FROM some_table')```Well you are not reading the docs properly i guess. Because it does say Helper to create a cursor and execute the given query. https://aiosqlite.omnilib.dev/en/latest/api.html#aiosqlite.Connection.execute
And the examples show of this as well, by storing it in the variable cursor
torn sphinx
just use
TINYINT(1). Thats what mariadb does as well internally
@proven arrow oh right thanks, did not know this was like this.
fair crescent
Can anyone even tell me what are the errors in this PL/SQL code
DECLARE
v_Radius NUMBER := 2;
BEGIN
LOOP
dbms.output.put_line('The AREA of the circle is '|| v_Radius * v_Radius ||);
EXIT WHEN v_Radius > 10;
END LOOP;```
dreamy pebble
hi please wanna ask abt mysql.connector , what does cursor.execute return on success ??
what I wanna do exactly is like
if thestatement executed succesfully :
smtng
else :
smntng
ValueError: no active connection```mhm?
i didnt event close the db
wt
oh i am nto awaiting it
xd
proven arrow
@dreamy pebble Itself it doesn't return anything. Instead you should be fetching the results and checking if results are present or not. Alternatively use try catch to get any errors, or you also have cursor.rowcount that will give you rows returned by the select, or other DML statements like (Insert, Update).
dreamy pebble
thaaank yaa buddy appreciate it <3
Itself it doesn't return anything. Instead you should be fetching the results and checking if results are present or not. Alternatively use try catch to get any errors, or you also have
cursor.rowcountthat will give you rows returned by the select, or other DML statements like (Insert, Update).
sinful condor
here is my code async with aiosqlite.connect("./database.db") as db: query = """ SELECT RANK() OVER(ORDER BY xp DESC) AS rank, userguild, xp, level FROM level;""" async with db.execute(query) as cursor2: level22 = await cursor2.fetchall() print(level22) exp = '1' intexp = int(exp) multiplier = '3' intmultiplier = int(multiplier) #1st place if level22[0] is not None: user1 = ctx.guild.get_member(user_id=level22[0][1]) userlvl1 = level22[0][3] userxp1 = level22[0][2] levelxp1 = userlvl1 + intexp lvl_end1 = int(levelxp1) ** intmultiplier #2nd place if level22[1] is not None: user2 = ctx.guild.get_member(user_id=level22[1][1]) userlvl2 = level22[1][3] userxp2 = level22[1][2] levelxp2 = userlvl2 + intexp lvl_end2 = int(levelxp2) ** intmultiplier #3rd place if level22[2] is not None: user3 = ctx.guild.get_member(user_id=level22[2][1]) userlvl3 = level22[2][3] userxp3 = level22[2][2] levelxp3 = userlvl3 + intexp lvl_end3 = int(levelxp3) ** intmultiplier I am trying to fix
list index out of range
and make it so if there are less than 3 people it will still work
is there a way to do that without errors
weary warren
ah i think i found the channel im looiking for
just started using sqllite for database and im trying to make a simple table
i feel like im doing everyting right but only 4 / 6 fields that im trying to create actually get made, could someone explain what im doing wrong that would be great
proven arrow
@sinful condor Can you not make a loop and loop X times where X is the length of the results from the db?
@weary warren Show your queries/code
sinful condor
@proven arrow would that work with what I am trying to do when it is a differnet user every time and I am trying to se if parts of a database exist or not
proven arrow
In a loop you wont have to check, since you only looping how many elements the database returns you
So if it gave you 2 rows, you loop 2 times.
And you can cut down all that extra code as well. Just make a dict which you append to from within the loop
sinful condor
um I dont really know how to make what you mean can you send an example
proven arrow
The only None check you would do is once, where you check if the database actually returned something. If it did proceed with the loop else no.
results = whatever_your_db_returns
if not results:
# no results returned from db
else:
my_data = {}
for index, value in enumerate(results):
my_data[index] = {}
my_data[index]['userlvl'] = results[index][3]
my_data[index]['userxp'] = results[index][2]
# ... store whatever else you want inside the subdict here
@sinful condor Something like that.
my_data =
{
0: {
'userlvl': 1,
'userxp': 2
},
1: {
'userlvl': 4,
'userxp': 5
}
...
}
End result will be like something like this.
torn sphinx
Best database format for a small business (analysis will be done using Pandas)
I'm looking to replace excel with Pandas. Pandas is great for analyzing data, not so good for inputting new data (to my knowledge). That's why I'm looking for a good DB format which makes it easy to view, append, modify.
The type of data I will be keeping in this DB is: product dimensions, logistics costs, sales projections, actual sales, etc.
Has anyone come across a blog post of someone who has done this? Does anyone have any suggestions for an easy to use database formats for this type of thing?
brazen charm
sounds like SQL would work well for this sort of thing
would recommend checking out Postgres
torn sphinx
would recommend checking out Postgres
@brazen charm thanks for the response. Will check it out. Just looking for easy input and reading of the DB when needed