it still dooesnt work
#databases
kindred python
The only database I've used is SQLite, and while I don't know anything about how it compares to PostgreSQL (Apart from scalability), Postgres is already my favorite because its mascot is an elephant.
I'm a simple man.
rain wagon
@vocal moon It pretty much says that the connection was refused
How is the database configured on the host?
rain wagon
what port did you set, which ip space is it listening to?
vocal moon
i didnt set a port but i set the ip thing to 0.0.0.0
rain wagon
then it should use 3306
rain wagon
did you create the user with@%?
vocal moon
lemme get the command i ran
GRANT ALL ON economy.* TO dareal@my_computer_ip IDENTIFIED BY "no this my pass :)";
Does the database run on your computer?
vocal moon
no a debian server
vocal moon
its a google cloud instance
rain wagon
and your computer ip starts with 192 or 172 or 10?
vocal moon
94
vocal moon
dareal@%ip
like this?
rain wagon
no dareael@%
vocal moon
ok
rain wagon
vocal moon
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server ve
rsion for the right syntax to use near '% IDENTIFIED BY "pass"' at line 1
'user'@'%' as always
rain wagon
yeah that was the point
vocal moon
same issue
vocal moon
on my terminal or pc?
rain wagon
from your pc if you got a linux
because it is either not accepting a remote connection at all or it's not accepting the user
vocal moon
im on windows
rain wagon
you can also try all ports with nmap -p- ip
rain wagon
it's of no use to run it on the vps
I always find WSL very useful, but then many useful linux tools don't work there
like mtr
vocal moon
what should I do?
rain wagon
yeah, nmap does not work on wsl either
vocal moon
whats wsl?
rain wagon
Windows Subsystem for Linux
vocal moon
idk what that is
rain wagon
but that terminal is your vps, right?
vocal moon
yes
rain wagon
then you are running the command on your vps
that is of no use
anyway, bottom line: the computer is not accepting the connection. You need to dig in and find the cause
either firewall or mysql config not loaded
have you restarted the service after editing the config?
vocal moon
which service
vocal moon
Failed to connect to bus: No such file or directory
rain wagon
what does journalctl -xe say?
vocal moon
some long logs and stuff
rain wagon
wait, is this a docker inside a vps?
vocal moon
wdym
rain wagon
well no shit, what do the logs say?
vocal moon
there is a bunch of ip's should i get rid of em
May 20 11:28:35 darealbot sshd[13918]: Received disconnect from some number port some number Normal Shutdown, Thank you for playing [preauth]
nothing in there about mysql?
but coming back to this: the database was installed on the vps right?
it's not a docker, right?
vocal moon
whats a docker
rain wagon
a container
vocal moon
i dont think so
rain wagon
how did you install the database?
Because this error: Failed to connect to bus: No such file or directory comes from fucking docker
vocal moon
rain wagon
and if you didn't tell me that this is a docker container in a vps I am going to be very mad
lapis oriole
I'm using sqlite3, and I'd like to store a SQL type in a variable to then pass it to SQL in a secure way using question marks (like db.execute("ALTER TABLE foo ADD COLUMN bar ?", (datatype,)) ). How can I do this ?
rain wagon
This cannot be done in a safe way
And you should not dynamically expand a relational database
lapis oriole
Well thanks. I think I'll do it on the unsafe way as the datatype is hardcoded, so no risk of someone trying to mess up with it
rain wagon
It's a type hint on the return value
it means this function returns nothing
def multiply(a: int, b:int) -> int:
return a*b```Another example, for a function using 2 ints and returning an int
But again, these are just hints, not absolute types
blazing inlet
Then is a hint for the program or more as an annotation?
rain wagon
It's used by IDEs like pycharm or linters to spit out warnings if you pass arguments with wrong types or assign values where you should not
# this would work
a = _showAnswer()```As it says, this would work, but pycharm would complain about it
a would always be None though
blazing inlet
Mmm so its more like what the program SHOULD return regardless what the function does?
blazing inlet
So you were able to print "I am atman" because None is only an annotation for the program, right?
rain wagon
it's what the author claims the function returns
But saying it returns nothing and then returning something is at the very least very bad design
blazing inlet
But in this case the author is wrong?
blazing inlet
Oh ok
So it's an expectation from the author
But if the suit or block code is bad designed, it could throw any value different from the annotation
I get it!
The books never mentioned that man 😦 haha that's why I stopped reading and started watching source codes
Thank you very much for your patience and help
Another little question...
When you use the colon on the parameters of a function
You are forcing that parameter to be an integer?
I mean, the variable "ease" would need to be an integer to be valid on that function?
lapis oriole
No, it's the same : a hint. It should be a called, and usually the type is checked right away. But you can define a function with type hinting and enter another type
>>> def f(a : int, b : int) -> int:
return a+b
>>> f('a','b')
'ab'
Perfectly correct
spring spruce
does anyone know mongodb
prime grotto
a little bit why
gloomy pike
how do i convert between python datetime and sql datetime?
rain wagon
@gloomy pike You don't have to, that is what the database library does
just give it a datetime object and it will handle things
Also there isn't really a "SQL datetime"
most use ISO formats
gloomy pike
o
spare shuttle
Is there a way to manage connection pools with Firebase? I am using the python-firebase library(https://ozgur.github.io/python-firebase/).
I am trying to get the data access latency low and read somewhere that managing connection pools will help
uncut egret
hi
torn sphinx
quick question
we have keyspaces in cassandra which is a collection of tables i.e. column families ... is there a collection type of methodology used in sql databases?
like.. in a postgresql database, is there something that's above tables that denotes collection of tables?
rain wagon
@torn sphinx The database itself is a collection of tables
In Postgres there is another layer called Schema
In mysql there are just single databases on one server
What is a schema in PostgreSQL. In PostgreSQL, a schema is a namespace that contains named database objects such as tables, views, indexes, data types, functions, and operators.
lapis oriole
How can you dynamically assign a default value to a column in an ALTER statement ? I'd like to do something like this :
db.execute("ALTER TABLE foo ADD COLUMN bar INT DEFAULT ?", (default_value,))
@rain wagon I thought the schema was something we defined for the tables by declaring the expected types and data types of fields.. from your explanation about database objects being under them, I am not able to correlate that concept
thorn nymph
guys how would you update boolean type with sqlalchemy in db?
runic pilot
That’s a table schema, but there’s also a schema as a collection of tables, by default I believer# they’re in a schema that has the same name as the database itself (or “public”, I can’t remember which)
thorn nymph
if request.method == 'POST':
post.pinn = request.form['pinn']
if post.pinn is True:
post.pinn = False
db.session.commit()
return redirect('/posts')```i am trying to do it like this
random zodiac
anyone familiar with erds? I am making one based on a scenario, just wanted to check if I am on right trach
this is the scenario
and these are my entities
jovial wave
I need to use sqllite db in my database application. Is there a way I can encrypt/password protect it in such a way it is not readable outside of the application?
jovial wave
Oops. What else can I use if I need to store sensitive data in db but also be able to retrieve it? I cannot hash it as it will make it impossibel to retrieve the original value
brazen charm
what it for?
runic pilot
you could encrypt the data going in, but I'd only recommend that for sensitive things like API keys of connected services
as CF8 says, we can help better if we have more context
jovial wave
Thats's my exact use case. Im storing api keys
So can I just use pycrypto and encrypt the data before I insert and decrypt it when I need them?
I have another question, if I need to execute multiple queries on different tables of same db, is it better to have a global cursor object or create local cursor objects as and when required inside functions?
runic pilot
yes, and I'd recommend making the encryption key unique to each user or account
I'm not used to cursors as I usually deal with sessions/transactions, but I typically create one db connection pool for the whole app and let that connector (cursor) manage connections itself
lavish ferry
how can I insert localtime in my column?
INSERT INTO adm (time)
VALUES (localtime)```
the data type of `time` is dateI would need only one connection open for the entire lifetime of the app, should I still use a pool? Isn't it better to make use of a global conn object?
golden warren
With SQLITE3, do you know how to create all databses in a specific folder ?
jovial wave
@golden warren
sqlite3.connect(requiredpath/dbname.db)
jovial wave
yes
wait
is the database name stored in self.GuildsDB ? If that's the case you should do self.connection = sqlite3.connect('data/'+self.GuildsDB)
torn sphinx
Hey, what is better? Django serializer or someModel.objects.prefetch_related('otherModel').values([list of values])? I wondering why mostly is suggesting get data by serializer, when it creates n+1 queries to database and someModel.objects.prefetch_related('otherModel').values([list of values]) is simplier?
golden warren
I use SQLITE3 but i have an error when i wan't to close the db.
AttributeError: 'NoneType' object has no attribute 'close'
Code:
import sqlite3
import discord
from utils.functions import fetchAllGuilds
"""
Class DataBase:
- Connection to db.
- deconnection to db.
"""
class DataBase():
def __init__(self, UsersDB: str = 'users.db', GuildsDB: str = 'guilds.db'):
self.UsersDB = UsersDB
self.GuildsDB = GuildsDB
self.ConnectUsersDB = None
self.ConnectGuildsDB = None
self.cursorUsersDB = None
self.cursorGuildsDB = None
def ConnectionUsersDB(self):
try:
self.ConnectUsersDB = sqlite3.connect('data/' + self.UsersDB)
self.cursorUsersDB = self.ConnectUsersDB.cursor()
except Exception as e:
print(f'[ERROR]: {e}')
def ConnectionGuildsDB(self):
try:
self.ConnectGuildsDB = sqlite3.connect('data/' + self.GuildsDB)
self.cursorGuildsDB = self.ConnectGuildsDB.cursor()
except Exception as e:
print(f'[ERROR]: {e}')
def CreateGuildsDB(self):
self.cursorGuildsDB.execute(
f"CREATE TABLE IF NOT EXISTS test(id INTEGER)")
def DisconnectAllDB(self):
try:
self.ConnectUsersDB.close()
self.cursorUsersDB.close()
except Exception as e:
print(f'[ERROR]: {e}')
if __name__ == '__name__':
pass
I think the seconde choie is better @torn sphinx but i'm not sure about it.
clever topaz
@golden warren Where are you calling DisconnectAllDB from? It seems that you are calling it before ConnetUsersDB or cursorUsersDB are set and thus the error. Ensure the DB is open first in your code and/or add
if self.ConnectUsersDB:
self.ConnectUsersDB.close()
if self.cursorUsersDB:
self.cursorUsersDB.close()
I'm calling here :
import discord
from discord.ext import commands
from database.database import DataBase
"""
Class Logout:
- Shutdown Sentinel.
"""
class Logout(commands.Cog):
def __init__(self, Sentinel):
self.Sentinel = Sentinel
self.db = DataBase()
@commands.command()
async def logout(self, ctx):
self.db.DisconnectAllDB()
print("All database where closes.")
await self.Sentinel.logout()
def setup(Sentinel):
Sentinel.add_cog(Logout(Sentinel))
I don't understand what you mean @clever topaz
clever topaz
In the DataBase.__init__ function you set the variables to None:
self.ConnectUsersDB = None
self.cursorUsersDB = None
Then you run DisconnectAllDB():
self.ConnectUsersDB.close()
self.cursorUsersDB.close()
Which just equates to:
None.close()
None.close()
Which gives you the error you are getting.
You need to call self.db.ConnectionUsersDB() so that you get these values:
self.ConnectGuildsDB = sqlite3.connect('data/' + self.GuildsDB)
self.cursorGuildsDB = self.ConnectGuildsDB.cursor()
Also, close should only be called on the connection - not on the cursor.
golden warren
Okay i see what you mean now but where you want to call self.db.ConnectionUsersDB() ? In database.py ?
Or maybe in logout i think. @clever topaz
clever topaz
After self.db = DataBase()
golden warren
Oh in my logout file.
golden warren
No it connect DB in my event on_ready. Not necessary in a command.
What else thx i try it and i tell you if it work :)
golden warren
@clever topaz
AttributeError: 'function' object has no attribute 'close' I have this error when i putt this code in my logout cmd.
import discord
import sqlite3
from discord.ext import commands
from database.database import DataBase
"""
Class Logout:
- Shutdown Sentinel.
"""
class Logout(commands.Cog):
def __init__(self, Sentinel):
self.Sentinel = Sentinel
self.db = DataBase()
self.db.ConnectionUsersDB()
self.db.ConnectionGuildsDB()
@commands.command()
async def logout(self, ctx):
self.db.ConnectionUsersDB.close()
self.db.ConnectionGuildsDB.close()
print("All database where closes.")
await self.Sentinel.logout()
def setup(Sentinel):
Sentinel.add_cog(Logout(Sentinel))
self.db.ConnectionUsersDB.close()
self.db.ConnectionGuildsDB.close()
Should be:
self.db.ConnectGuildsDB.close()
self.db.ConnectGuildsDB.close()
(The variables rather than the functions)
golden warren
So if i putt like this it should be work :
import discord
import sqlite3
from discord.ext import commands
from database.database import DataBase
"""
Class Logout:
- Shutdown Sentinel.
"""
class Logout(commands.Cog):
def __init__(self, Sentinel):
self.Sentinel = Sentinel
self.db = DataBase()
self.db.ConnectionUsersDB()
self.db.ConnectionGuildsDB()
@commands.command()
async def logout(self, ctx):
self.db.ConnectGuildsDB.close()
self.db.ConnectGuildsDB.close()
print("All database where closes.")
await self.Sentinel.logout()
def setup(Sentinel):
Sentinel.add_cog(Logout(Sentinel))
Looks good.
golden warren
Yep it works ! thx :)
brazen charm
I wish Postgres had as nice of a DB manager as mongo compass
While i like pg admin
somtimes i just want a simple gui not a full mad house
ebon zephyr
hi guys i have a quick question
i posted it over at stackoverflow can i post a link here as a reference?
im looking to scrape all of the contents/entries from the table on this page (https://poker.bettor-status.net/) and i have had no luck, i'd like to be able to compare a name vs the entries on the list so id like to scrape all 10k entries i've tried using many different methods none susccessfully.
any help would be hugely appreciated
pseudo summit
So, maybe I'm making this too simple, but did you look at your network console and see the file they're calling?
And then adjust the parameters to account for 0-10443 entries?
That returns a json of all the results, 10443 of them.
So what is left to scrape?
@ebon zephyr That was meant for you. Again, probably under-thinking what you're trying to do but ...
rich trout
You need to use a connection pool
rich trout
@torn sphinx https://magicstack.github.io/asyncpg/current/usage.html#connection-pools is what you're looking for
torn sphinx
Hi
I'm confused about replication factor vs horizontal scaling
replication is number of replicas so there's back ups, right? how is it different from horizontal scaling where we add more nodes
rain wagon
No replication is not backup
If you delete something from A, it is gone on B
Just like RAID 1 is not a backup
torn sphinx
I don't understand
replication means it's an active-active configuration right.. like one node fails, you still have the other node to retrieve data from
rain wagon
@torn sphinx That is called a redundancy
A backup implies the data is safe and can be restored at any time
torn sphinx
ok.. but am I getting this concept right? that redundant nodes are where the writes are also done along with the master
@rain wagon
rain wagon
If set up in this way, yes
torn sphinx
I dont understand
if replicas are 3, then that means it's one master and two workers.. so anything written on the master is also available on the workers.. for availability and fault tolerance in case master is not available
is that right?
rain wagon
Yes, but there is also master-master
where each redundancy also receives information and syncs it to the other one
master -> slave vs master <-> master
steady epoch
@commands.command(name="add_clan")
async def add_clan(self, ctx,user:discord.User, clan_tag):
"""Command is used to register a user to the database"""
fixed_tag = utils.correct_tag(clan_tag)
clan = await self.coc_client.get_clan(fixed_tag)
db = sqlite3.connect("bot_db.sqlite")
try:
db.execute("INSERT INTO register (discord_id,clan_tag) VALUES(user.id,clan.tag) WHERE NOT EXISTS (SELECT clan_tag FROM register WHERE clan_tag = clan_tag)")
db.commit()
await ctx.send(f"{clan.name} linked to {user.mention} ")
except sqlite3.IntegrityError:
await ctx.send(f"{user} is already registered {clan.tag}")``` File "C:\Users\hp\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\ext\commands\core.py", line 83, in wrapped
ret = await coro(*args, **kwargs)
File "C:\Users\hp\Desktop\bot\cogs\clan_info.py", line 32, in add_clan
db.execute("INSERT INTO register (discord_id,clan_tag) VALUES(user.id,clan.tag) WHERE NOT EXISTS (SELECT clan_tag FROM register WHERE clan_tag = clan_tag)")
sqlite3.OperationalError: near "WHERE": syntax error
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "C:\Users\hp\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\ext\commands\bot.py", line 892, in invoke
await ctx.command.invoke(ctx)
File "C:\Users\hp\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\ext\commands\core.py", line 797, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\hp\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\ext\commands\core.py", line 92, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "WHERE": syntax error```@torn sphinx This is an example for a simple load balancing set up on the web
The databases replicate each other, so that all data can be input and output on each side
db.execute("INSERT INTO register (discord_id,clan_tag) VALUES(?,?) WHERE NOT EXISTS (SELECT clan_tag FROM register WHERE clan_tag=?)", [user.id, clan.tag, clan.tag]) would make more sense
torn sphinx
Master<--> Master means, writes happen on both and both sync the data between them so that data can be retrieved from either, is that right? how is it different from master-slave @rain wagon
rain wagon
Yes, that is what it means. Master -> slave means, that only the master sends its data to the slave
master/master is basically a set up where each db has the other one as a slave
torn sphinx
ok.. and this is the same whether it's relational or nosql?
rain wagon
The concept should be the same, but maybe they use some fancier buzzwords for it
torn sphinx
let's say there's 3 nodes in a nosql db, with horizontal scaling, we add more nodes, what happens to the writes on the new nodes, how does the data get replicated
rain wagon
Just to clarify: This isn't a feature of SQL, each database may have varying features there
torn sphinx
yes, SQL db's are usually scaled vertically with master-master or master-slave set up
rain wagon
I can only answer that last question in the context of mysql: You can give a pointer to the binlog, which is where replication starts
The binlog is a log of all DML transactions done on the database
torn sphinx
is there a concept of horizontal scaling in mysql
rain wagon
So, if you want all data to be in the new nodes, you need to give the pointer to the binlog that has been used in the original initial setup
steady epoch
@commands.command(name='add_player')
async def add_player(self, ctx,user:discord.User, player_tag=None):
"""Command is used to register a user to the database"""
fixed_tag = utils.correct_tag(player_tag)
player = await self.coc_client.get_player(fixed_tag)
db = sqlite3.connect("bot_db.sqlite")
stmt = db.cursor()
stmt.execute(f"SELECT discord_id FROM register WHERE discord_id ={user.id}")
result = stmt.fetchone()[0]
if result is None:
stmt.execute("INSERT INTO register (discord_id,player_tag) VALUES(?,?)",(user.id,player.tag))
db.commit()
await ctx.send(f"{player.name} linked to {user.mention} ")
elif result is not None:
stmt.execute('UPDATE register SET player_tag =? WHERE discord_id =?',(player.tag,user.id))
db.commit()
await ctx.send(f'{user.mention} account has been registered to Clash Account :- {os.getenv(str(player.town_hall))} {player.name}')```nice sql injection bug there
maybe you want to read up on how to use a database before you handle other peoples data
stmt.execute("SELECT discord_id FROM register WHERE discord_id =?", [user.id])
rain wagon
bold pelican
i use asnyc pg
brazen charm
but a whole fucking lot more useful in places
steady epoch
how can i add clan tag to my discord id in nxt column
bold pelican
What are the advantages and disadvantages?
steady epoch
actually i wnt to check if clan tag is empty append the value
i think u should google it
bold pelican
How can I sort this with xps?
"701465933041893427": {
"435394854017826817": {
"xps": 3140,
"level": 8,
"last_message": 1588350254
},
"660702877546840075": {
"xps": 1685,
"level": 7,
"last_message": 1589561109
},
"702722966039035965": {
"xps": 585,
"level": 4,
"last_message": 1587915578
},
"703469461927362613": {
"xps": 40,
"level": 0,
"last_message": 1587792479
},
"614120525761085450": {
"xps": 95,
"level": 1,
"last_message": 1587878540
},
"693401671836893235": {
"xps": 0,
"level": 0,
"last_message": 0
}
}
Advantages:
- Very useful inbuilt data types like json, datetime etc...
- Very quick
- Easy to scale
- Very good with large data sets
Disadvantages:
- Takes a bit longer to setup compared to something like MySQL
rain wagon
Allowing connections from outside also requires a bit more networking knowledge
should know how to write CIDR notations
and be fluent in how linux users work and how they are set up
steady epoch
@rain wagon https://discordapp.com/channels/267624335836053506/342318764227821568/713317541463720008
how can i add
nxt to my empty discord tag
flint wadi
I want to have a go at using PostgreSQL. How do I set it up in a way that prevents other internet users from accessing it/my linux pc?
steady epoch
async def add_player(self, ctx,user:discord.User, player_tag=None):
"""Command is used to register a user to the database"""
fixed_tag = utils.correct_tag(player_tag)
player = await self.coc_client.get_player(fixed_tag)
db = sqlite3.connect("bot_db.sqlite")
stmt = db.cursor()
stmt.execute(f"SELECT discord_id FROM register WHERE discord_id ={user.id}")
result = stmt.fetchone()[0]
if result is None:
stmt.execute("INSERT INTO register (discord_id,player_tag) VALUES(?,?)",(user.id,player.tag))
db.commit()
await ctx.send(f"{player.name} linked to {user.mention} ")
elif result is not None:
stmt.execute('INSERT INTO register(player_tag) VALUES(NULL) WHERE discord_id={user.id}',(player.tag))
db.commit()
await ctx.send(f'{user.mention} account has been registered to Clash Account :- {os.getenv(str(player.town_hall))} {player.name}')```what i am doing wrong
Traceback (most recent call last):
File "C:\Users\hp\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\ext\commands\bot.py", line 892, in invoke
await ctx.command.invoke(ctx)
File "C:\Users\hp\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\ext\commands\core.py", line 797, in invoke
await injected(*ctx.args, **ctx.kwargs)
File "C:\Users\hp\AppData\Local\Programs\Python\Python37\lib\site-packages\discord\ext\commands\core.py", line 92, in wrapped
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: OperationalError: near "WHERE": syntax error```if anyone is around that is good with postgres permissions could you @ me
jovial wave
Is the primary key on the sqlite db case insensitive and always unique?
rain wagon
@flint wadi Make sure to not expose the connection in pg_hba.conf
If it is set to localhost only, nobody from outside can connect to it
@jovial wave PK are always unique
jovial wave
Thanks!
tough mesa
Hi, is there anyone using Replication for MySQL, or reading High Performance MySQL book?
delicate fieldBOT
spare shuttle
Has anyone worked with the python-firebase library?
Specifically, has anyone encountered memory leak issues while using it?
fathom adder
Hello, does anybody know what is the type of an instance in SQLAlchemy?, for example:
def get_user(db_session, user_id):
user = db_session.query(User).filter_by(id=user_id).first()
return user
If I wanted to add typing...
def get_user(db_session: Session, user_id: int) -> ???:
user = db_session.query(User).filter_by(id=user_id).first()
return user
What should I put on those ????
runic pilot
it's either going to be an instance of User or None
I think you want to use Optional[User] https://docs.python.org/3/library/typing.html#typing.Optional
you might need to define your own user type for a typing https://docs.python.org/3/library/typing.html#newtype - I haven't really dug deep into type hints so I can't tell you exactly
fathom adder
ok, thank you @runic pilot what if I had a function like this:
def get_one(db_session, model, id):
obj = db_session.query(model).filter_by(id=id).first()
return obj
Its just an example but here I could create the same get_user function like this:
def get_user(db_session, user_id):
return get_one(db_session, User, user_id)
It can return multiple types, the declaration of user would look like this:
from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class User(Base):
id=Column(Integer, primary_key=True)
first, I'd mention that you can use User.query.get(id) instead of what you're doing
but you can use Union if you have multiple types https://docs.python.org/3/library/typing.html#typing.Union
fathom adder
first, I'd mention that you can use
User.query.get(id)instead of what you're doing
oh I didnt know that
but you can use Union if you have multiple types https://docs.python.org/3/library/typing.html#typing.Union
ok thanks
@runic pilot I am getting this error: AttributeError: type object 'User' has no attribute 'query' when using User.query.get(id). I know it works in Flask-SQLAlchemy but here I am using normal SQLAlchemy
runic pilot
ah, I usually have my models inherit from sqlalchemy.Model
that adds a query to your models
so instead of session.query(ModelClass) you can do ModelClass.query
tribal dome
does anyone have experience using mongodb with python? I'm studying someone's project code and a bit confused at syntax
wispy steeple
can i get some help with basic mysql
basically i need to create a view of two tables but the "natural join" code wont give me the columns in the order i need them to be in
pseudo summit
@wispy steeple What do you mean?
dusty helm
golden warren
Yop!
I have my code database for my databases and i would like to import cursorGuildsDB into my event on_ready in other file. How can i do that ?
import sqlite3
import discord
from utils.functions import fetchAllGuilds
"""
Class DataBase:
- Connection to db.
- deconnection to db.
"""
class DataBase():
def __init__(self, UsersDB: str = 'users.db', GuildsDB: str = 'guilds.db'):
self.UsersDB = UsersDB
self.GuildsDB = GuildsDB
self.ConnectUsersDB = None
self.ConnectGuildsDB = None
self.cursorUsersDB = None
self.cursorGuildsDB = None
def ConnectionUsersDB(self):
try:
self.ConnectUsersDB = sqlite3.connect('data/' + self.UsersDB)
self.cursorUsersDB = self.ConnectUsersDB.cursor()
except Exception as e:
print(f'[ERROR]: {e}')
def ConnectionGuildsDB(self):
try:
self.ConnectGuildsDB = sqlite3.connect('data/' + self.GuildsDB)
self.cursorGuildsDB = self.ConnectGuildsDB.cursor()
except Exception as e:
print(f'[ERROR]: {e}')
def CreateGuildsDB(self):
self.cursorGuildsDB.execute(
f"CREATE TABLE IF NOT EXISTS test(id INTEGER)")
if __name__ == '__name__':
pass
import discord
import sqlite3
import glob
from discord.ext import commands
from database.database import DataBase
from utils.functions import *
class Ready(commands.Cog):
def __init__(self, Sentinel):
self.Sentinel = Sentinel
self.status = discord.Status.do_not_disturb
self.db = DataBase()
@commands.Cog.listener()
async def on_ready(self):
DBFiles = [f for f in glob.glob("**/*.db")]
# Connect all db.
self.db.ConnectionUsersDB()
self.db.ConnectionGuildsDB()
self.db.CreateGuildsDB()
await self.Sentinel.change_presence(status=self.status)
print('+ ------------------------------------------------------------ +')
for f in DBFiles:
print(
f'| [SUCCESSFULL] Databases created: {f}')
print('+ ------------------------------------------------------------ +')
print(f'| {self.Sentinel.user.name} is online.')
print('+ ------------------------------------------------------------ +')
for id in fetchAllGuilds(self.Sentinel):
self.db.ConnectionGuildsDB.cursorUsersDB(sql)
def setup(Sentinel):
Sentinel.add_cog(Ready(Sentinel))
DBFiles = [f for f in glob.glob("**/*.db")] if I put a rogue file in there, your code is in trouble
make a list of files to load if you are doing something like that
and that list should only be writable by certain processes/users
pseudo summit
@dusty helm I prefer grouping models with their blueprint. Like with like.
dusty helm
alright
@dusty helm I prefer grouping models with their blueprint. Like with like.
@pseudo summit
i ended up putting my models inside the routes
hahaha
to avoid circular imports
neon fog
Is it possible to prepopulate a Model in Django with data scraped from a website, and then access that data later to post onto a Django application? I tried to write a migration to do this for me, but I think I might have written it incorrectly because it seems to give me multiple instances of the model rather than a single instance with all of the data.
scenic zinc
For those who are familiar with sqlalchemy, are you able to access the schema equivalent of a mapping without having to explicitly define the scehma?
@as_declarative()
class Base:
id: Any
__name__: str
@declared_attr
def __tablename__(self) -> str:
return self.__name__.lower()
class User(Base):
id = Column(Integer, primary_key=True, index=True)```This is an example
Apparently in the documents it says I can use User.__table__ but I do not see that attribute.
flint wadi
Thanks very much @rain wagon
dire cedar
Where do lock apply with the UPDATE and SELECT and DELETE statements in sql?
Only for the data that is being updated/readed
Or the whole table
Like could I write to two different rows at the same time if those two rows don't have any relation?
grave carbon
Hi, where would I get started learning about using PostgreSQL , before I start integrating it into my python applications?
wraith rampart
Hello, in MySQL how do I find the actual hostname instead of localhost because i dont think that will work when i actually publish my application:
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="password",
database="database"
)
If its on your pc and you want remote clients to connect or for you to connect from elsewhere
go to whatismyip.com from your home pc and place that in the host quotes
if you put on vps use that ip addr
You may have to do port forwarding on mysql port 3306 to get connected if your home has firewall on the router tho
quiet hatch
Hello I am looking to filter out entries of a dataframe that contain a certain string in the 'ingredients' column. I have two dataframes, one containing all the data and another containing all the data that contains the specific string. I tried to concat them and erase the duplicates but it is not seeming to work. The issue is somewhere with the function 'filtered_list'
import pandas
from tabulate import tabulate
food_df = pandas.read_csv('complete_data.csv', dtype = str)
food_df = food_df.drop('fdc_id.1', 'columns')
food_df = food_df.dropna()
branded_food_category = food_df['branded_food_category']
food_categories = branded_food_category.value_counts()
brand_owner = food_df['brand_owner']
food_brands = brand_owner.value_counts()
def filtered_list(ing_1):
'''Filters through food_df dataframe to filter out certain ingredient(ing_1)'''
filtered_food = food_df[food_df['ingredients'].str.contains(ing_1)]
difference = pandas.concat([food_df, filtered_food]).drop_duplicates(keep=False)
print(difference)
ingredient_1 = input('Enter an Ingredient you do not want ')
filtered_list(ingredient_1)
#...
how can I use limit in asyncpg?
DELETE FROM adm WHERE user = 'kami' LIMIT 1```
i'm getting this error:
`asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "LIMIT"`@lavish ferry You cannot limit on DELETE
and you shouldn't delete by a non-unique identifier
lavish ferry
how can I delete just one row then?
rain wagon
use a unique identifier for the row you want to delete
for example, user id
names are probably not unique
and limiting it does not guarantee you delete the correct one
if it would work, it would just delete the first it'd find
rain wagon
then use that to delete the user
lavish ferry
how can I turn some in unique?
rain wagon
I don't understand the question
user id should always be unique
at the very least, each row has a primary key and that one is always unique
if done correctly
lavish ferry
ok, i turn the user_id in PK, how can I insert the PK two times?
asyncpg.exceptions.UniqueViolationError: duplicate key value violates unique constraint "adm_pkey"
I have some command that insert guild_id, user_id and time
INSERT INTO adm (guild_id, user_id, time)
rain wagon
you can not
that is the point of a PK
user_id should be unique, guild_id a foreign key
lavish ferry
if i want to add two time, for the same user_id, i wouldn't have to repeat it? using insert into
rain wagon
Why would you want the same id twice?
Normal form 1: Identify each set of related data with a primary key
lavish ferry
I think I understand, first I create an insert with everyone, and when I add new columns, I see if there is a pk, if it exists, instead of using insert, I use alter table
rain wagon
Alter table, what?
You mean UPDATE right?
@lavish ferry
I think you really need to read up on how databases work
What you say makes very little sense I'm afraid
shell ocean
not sure where to put this, but...is there a way to tell whether there exists a continuous string of 1s, of arbitrary length, in the binary representation of an integer?
in SQL
e.g. if I wanted a minimum length of 4, for 00111100 it would return true, and for 00110011 it would return false
I need to be able to filter on this
pseudo summit
@shell ocean depends on the db. A lot have regular expression syntax you can use. Varies per db. Just would be easy with a regex
shell ocean
so there's no simpler way than casting the integer to bit string and searching in it?
grave carbon
When I update my postgres table
it brings the updated row to the lowest primary id
i.e. user_id 1 was once primary id 1 as well but now is 4 after updating username field
is that supposed to happen
rain wagon
@grave carbon Did you use UPDATE or did you drop the row and re-insert it?
grave carbon
I used UPDATE
This is a test database for learning postgres but I used this command exactly,
UPDATE chitter_user
SET username = 'Ryan C', email = 'ryan@gmail.com'
WHERE username = 'Ryan';```
zinc maple
for dynamodb is there a technical distinction between ExpressionAttributeNames and ExpressionAttributeValues or do they both simply replace placeholders in the expression?
online I see a lot of inconsistent use between the two when dealing with maps
tepid crow
Any tutorial for databases?
celest blaze
the postgres docs have a decent tutorial
tepid crow
Alright thanks
celest blaze
tepid crow
heidiSQL has his own docs right?
grave carbon
Are you using postgresql because it is good sir
I just went through this whole video series in the last 2 days and learned a lot about db's and postgresql in particular
It is a series not just one beginner tutorial fyi
tepid crow
okey then, i will try to work with postgreSQL
grave carbon
@tepid crow A lot of the experienced guys here told to me that postgres was their choice, for scalability and functionality - works great for me in my learning so far
celest blaze
I wouldn't touch MySQL if I could avoid it
brazen charm
^^ Seconded
lime echo
db = sqlite3.connect("ban_list.sqlite")
c = db.cursor()
await c.execute("""INSERT INTO ban_listtab(member_id, ban_date, unban_date) VALUES(?,?,?)""", (message.author.id, ban_date, unban_date,))
db.commit()
print("worked")
db.close()```
this doesn't print `"worked"` nor does it insert any value into my dbOh my God, I just noticed that I added await
well, still
celest blaze
if you await "execute", I'd imagine you'd also want to await "connect" and "commit" and "close"
lime echo
I fixed that issue thanks offby1
It wasn't loaded on main.py this is why I didn't get any response
lost mortar
sqlalchemy question.
I'm trying to limit the total number of queries that my app is making to a sql database by making a big query, then iterating through rows, updating one row at a time. I would like to commit my changes after each row modification to avoid losing data incase something crashes.
My understanding is that session.commit() will write for each object in the session. Is there a way to specify a single object instead? Or maybe an entirely different way to handle this that people with ORM experience might know?
Current code:
def __init__(self, session, model, limit):
self.session = session
self.table = model
self.rows = self.get_rows(session, model, limit)
self.index = 0
@property
def current_row(self):
return self.rows[self.index]
def get_rows(self, session, model, limit):
session.query(model).filter(model.needs_review == 1).limit(limit)
def mark_row_updated(self):
"""Writes an update statement for current row to change needs_review from
1 to 0
"""
self.current_row.needs_review = 0
self.session.commit()
self.index += 1```
carmine mortar
anyone familiar with asyncpg?
I was wondering if None corresponds to a NULL value in pgsql
it is not mentioned here in the docs
kindred python
This guys postgres tutorial is very good. Lots of short and sweet clips so you can at-least watch a few a day.
https://www.youtube.com/playlist?list=PLwvrYc43l1MxAEOI_KwGe8l42uJxMoKeS
topaz gazelle
I don't know the context, but maybe you just needed list(ctx.message_ids)
wicked lynx
any reason why you're using executemany and not just execute?
you could do ... WHERE message_id in $1', ctx_message_ids) I'm pretty sure
@torn sphinx
yay! good luck
grave carbon
Do databases have an undo ability or how should I be safeguarding from my failures of accidental deletion or otherwise corrupting / destroying my databases
topaz gazelle
Some have things like transactions, but not undo, no.
grave carbon
What can I do to safeguard from my failed attempts at fixing or upgrading or etc my programs that work with my dbs?
I'm prone to testing by trying and if I fail at my trial and error with a db my data is ruined ;o
fervent tulip
Backup database
topaz gazelle
Backups are a thing and some DBs have incremental backups, but usually the aim is to not screw up your DB
grave carbon
Should I backup db every time I make a change to it and store X amount and prune ?
fervent tulip
If you want
It just create a test database
And only connect to the database with code that doesn’t break it
grave carbon
What about overflows and such is that a thing of the past or if I put a number too big does it buffer overflow etc?
Do I need strict entry rules for my data etc
fervent tulip
Idk
topaz gazelle
@grave carbon Every database system is different
grave carbon
I'm talking postgresql specifically*
stable glen
yo
whats the issue with this?
await cur.execute("UPDATE bal SET spentbal = %s WHERE uuid = %s", (addedbal, uuid))
await conn.commit()
table is bal
it has 3 colums in this order:
uuid spentbal buybalance
wicked lynx
what does the error message say?
off the top of my head, I think the syntax should be SET spentbal VALUES %s WHERE uuid= %s
@stable glen
stable glen
ah yea
Traceback (most recent call last):
File "C:\Python38\lib\site-packages\discord\client.py", line 312, in _run_event
await coro(*args, **kwargs)
File "C:\Users\Dan\Downloads\discord.py-master\trial bot\bot.py", line 214, in on_raw_reaction_add
await cur.execute("SELECT * FROM discordsrvx_accounts WHERE discord = %s", (ur,))
File "C:\Python38\lib\site-packages\aiomysql\cursors.py", line 231, in execute
conn = self._get_db()
File "C:\Python38\lib\site-packages\aiomysql\cursors.py", line 165, in _get_db
raise ProgrammingError("Cursor closed")
pymysql.err.ProgrammingError: Cursor closed
Hey, maybe someone knows how to serialize django model's object with select_related, what woudn't be like client__id : 1, but would be client: {id : 1}. With simple ModelSerializator I got n+1 problem and cannot solve it
wicked lynx
@torn sphinx can you show some code? then it'll be easier to see what you're trying to do
stable glen
wait @wicked lynx dude the thing you sent, shouldnt it include the name of the table? Idk where to put that ://
wicked lynx
yeah sorry I was lazy and didn't write the whole query; just prefix it with UPDATE bal
stable glen
oh ahah
torn sphinx
class ItemSerializer(ModelSerializer):
owner = OwnerSerializer()
class Meta:
model = Item
field = (fields list)
class OwnerSerializer(ModelSerializer):
class Meta:
model = Owner
field = (fields list)
For example I need to get all Item with Owners
stable glen
its fine you werent lazy :p
wicked lynx
🙃
stable glen
aa wait same error msg ://
torn sphinx
@wicked lynx and I need get json format like this
id : 1,
owner: {
id: 2,
name: "name"
}
wicked lynx
@stable glen the error message you're getting has nothing to do with the SQL query; it's saying that your cursor is closed for some reason
torn sphinx
But problem what there is huge database, so thousands queries
stable glen
ah i see
aa
hmm
could it be bcz of await conn.commit()?
wait no, even w/ out it it gives the msg
OOOOOOOOOHHHHHHHHHHHH
guys dont mind me im being stooopidd and didnt actually connect the db yet T-T
ahaha
torn sphinx
Its example, I mixed it
wicked lynx
ah okay
also can you show code where you're doing the actual serialisation?
coz I don't see why it shouldn't be giving you the correct JSON if you have the correct fields in the fields list
stable glen
ok wait so now i have a brand new 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 'VALUES 75001 WHERE uuid= '73ee0243-f3d8-3e29-b216-a40f31d2ef04'' at line 1")
with await cur.execute("UPDATE bal SET spentbal VALUES %s WHERE uuid= %s", (addedbal, uuid))
wicked lynx
oh, maybe your previous syntax was correct (I assumed postgres, not mariadb)
stable glen
¯_(ツ)_/¯
stable glen
eyyy that workkss thanks Kimvid ^^
torn sphinx
@wicked lynx Sorry, I am trying optimize project at work and cannot show real code
wicked lynx
yeah that's fine
and you've tried Item.objects.select_related('owner').all()?
@stable glen no worries!
What json/error do you get?
torn sphinx
Yes, still n+1 problem. I am trying to solve it since last Thursday 😄
wicked lynx
Sorry, what do you mean by n+1 problem?
torn sphinx
With every foreign key there is calling new query, for example there are 100 item, for would be call 101 query, 1 for items and 100 for each Owner
torn sphinx
@wicked lynx from which country are you? Just wondering about 'aha'
wicked lynx
I'm Australian, is "aha" weird? :D
torn sphinx
I tried to use simple Item.objects.select_related ('owner).values('id', 'owner__id'), but then i got owner__id as string, but I need Owner as object
Lithuania use the same 'aha' 😄
wicked lynx
Aha 😉
Hmm, you could take a look at this post, it seems similar to your problem https://stackoverflow.com/questions/35021461/django-rest-serializer-doing-n1-database-calls-for-multiple-nested-relationship
So use a Prefetch object and prefetch_related instead? Worth trying
Anyway I should get back to work now, feel free to ping me if you get it working!
torn sphinx
Okey, thanks and have a good day
torn sphinx
what does your trigger look like?
livid cairn
Nvm. Got it.
Made a foreing key
What would you recommend: 1 table for each task or 1 table for every task?
In the, case I don't know yet how many tasks I'll have.
torn sphinx
@wicked lynx it worked, not exactly that way, but ir worked
wicked lynx
Oooh, using the prefetch stuff?
@dusk hollow I think you want to select row_id from the table
livid cairn
Guys, I have the following structure:
"accounts" table:
id | name
1 | test1
2 | teste2
"settings" table:
id | setting1 | setting2
1 | 1 | 0
2 | 0 | 1
"task1" table:
id | status | datetime
1 | 1 | 2020-01-01 00:00:00
2 | 0 | 2020-01-01 00:00:00
"task2" table:
id | status | datetime
1 | 0 | 2020-01-01 00:00:00
2 | 1 | 2020-01-01 00:00:00
There are a FK to link all id column from all the tables to the accounts' id column.
I made a trigger that creates a new row for settings, task1 and task2 everytime a row in accounts is inserted.
What would be the best way to make sure that the status columns (from both task1 and task2 tables) will always have the same value as in setting1 and setting2 columns, respectively, from settings table (task1 should be linked to setting1 and task2 should be linked to setting1), as shown above?
Here is the trigger I have:
CREATE TRIGGER account_creation
AFTER INSERT
ON accounts FOR EACH ROW
BEGIN
INSERT INTO settings VALUES (DEFAULT, DEFAULT, DEFAULT);
INSERT INTO task1 VALUES (DEFAULT, DEFAULT, DEFAULT);
INSERT INTO task2 VALUES (DEFAULT, DEFAULT, DEFAULT);
END
@wicked lynx yes, exactly setup_eager_loading in serializer
somber hatch
how can i add a number with a . like 1.1 in mongo db
when i try
uinfo.update_one({"User id": f"{ctx.author.id}"}, {"$set": {"version": int(version)}})
# it only adds 1
# but
uinfo.update_one({"User id": f"{ctx.author.id}"}, {"$set": {"version": version}})
# will give an error
Why with Psycopg2 does my pycharm not know the function names
like when I type cur. it doesn't list any options in the autocomplete stuff
for my cursor object
cur = conn.cursor()
how am I supposed to know what methods exist
such as fetchall() and close() etc
noble oak
I was watching a database tutorial for PostgresSQL and I was wondering
What does 'VARCHAR()' mean/do
livid cairn
It's a data type.
grave carbon
Anyone know why Pycharm doesn't know the methods of objects from psycopg2
sharp mango
most likely because the cursor obj is implemented in C and pycharm can't read the binary
jolly wolf
can someone help me
grave carbon
@sharp mango oof okay 😦
@sharp mango So basically just trust and use the docs instead
sharp mango
yep the docs for psycopg2 are pretty comprehensive anyways and it's very stable if memory serves
livid cairn
I have two tables:
"settings" table:
id | setting1
1 | 1
2 | 0
"task" table:
id | task1
1 | 1
2 | 0
Could someone help me making an after update trigger to update task1 when setting1 is updated?
sharp mango
@livid cairn can you describe what the relationship is between tasks and settings?
livid cairn
Well, none atm. I tried to make a fk. However, setting1 have only bit(1) values. I don't think I could make that a primary key.
There are foreing keys that link id columns from task and settings to accounts' table id.
each row on both tables is a different account.
sharp mango
so how should task1 update after an update to settings is applied?
sharp mango
i don't understand.. is the task1 column meant to mirror the setting1 column?
sharp mango
why not just create a FK from tasks to settings then so that your data is properly normalized?
livid cairn
I tried. Didn't work. Lemme try again. Hang on.
sharp mango
id should be the primary key...
livid cairn
like that?
sharp mango
what DB are you using?
livid cairn
mysql
I'm rebuilding the scenario I made on that example.
create schema teste;
USE teste;
-- ACCOUNTS
create table accounts
(
id tinyint(2) null
);
alter table accounts
add constraint accounts_pk
primary key (id);
alter table accounts modify id tinyint(2) auto_increment;
create unique index accounts_id_uindex
on accounts (id);
alter table accounts
add name varchar(16) not null;
create unique index accounts_name_uindex
on accounts (name);
-- SETTINGS
create table settings
(
id tinyint(2) null
);
alter table settings
add constraint settings_pk
primary key (id);
alter table settings modify id tinyint(2) auto_increment;
create unique index settings_id_uindex
on settings (id);
alter table settings
add setting1 bit default 0 not null;
alter table settings
add constraint accounts_settings
foreign key (id) references accounts (id)
on update cascade on delete cascade;
-- TASKS1
create table task
(
id tinyint(2) null
);
alter table task
add constraint task_pk
primary key (id);
alter table task modify id tinyint(2) auto_increment;
create unique index task1_id_uindex
on task (id);
alter table task
add task1 bit default 0 not null,
add constraint accounts_task
foreign key (id) references settings (id)
on update cascade on delete cascade,
add constraint task_settings_setting1_fk
foreign key (task1) references settings (setting1)
on update cascade on delete cascade;
USE teste;
-- TRIGGER
CREATE TRIGGER account_creation
AFTER INSERT
ON accounts FOR EACH ROW
BEGIN
INSERT INTO settings VALUES (DEFAULT, DEFAULT);
INSERT INTO task VALUES (DEFAULT, DEFAULT);
END
K. I think that's it. I'm testing now.
gloomy pike
async def sql_write(ctx, memberID: int, _time: int, db):
"""Write/update Mute/Ban action to DB"""
if _time == -1:
_time = 0
else:
"""How to convert back to datetime:
datetime.datetime.fromtimestamp(_time)"""
_time = int(time.time()) + _time * 60
if not await in_db(ctx, memberID, db):
if ctx.command.name == "mute":
await db.execute("insert into Timestamps (GuildID, MemberID, Timeunmuted, Roles)"
"values (?, ?, ?, ?)", (ctx.guild.id, memberID, _time,
json.dumps([role.id for role in ctx.guild.get_member(memberID).roles if role != ctx.guild.default_role])))
await db.commit()```
Why does it not write anything? No error is returned. It just doesn't write anything to the DB when it is called.this is the command under which it's called
@commands.has_permissions(manage_roles=True)
@commands.command(description="Mute a member. Give the time in minutes as an integer; do -1 to mute permanently.")
@commands.guild_only()
async def mute(self, ctx, member: discord.Member, time: int = 15, *, reason: str = None):
...
await actions_checks.sql_write(ctx, member.id, time, db)```and this is in_db
async def in_db(ctx, memberID: int, db) -> bool:
"""Check if member is in database, this is for updating the DB"""
cursor = await db.execute("select count(MemberID) from Timestamps where MemberID = ? and GuildID = ?",
(memberID, ctx.guild.id))
return await cursor.fetchone()```@sharp mango
alter table task
add constraint task_settings_setting1_fk
foreign key (task1) references settings (setting1)
on update cascade on delete cascade;
This FK?
steel patrol
anyone know why i'm not getting any response here? not even an error code
@commands.command()
async def tryget(self, ctx):
uid = colle.find({"_id":ctx.author.id})
for mon in uid:
await ctx.send(mon["level", "pet"])
maybe uid is empty, so your loop never runs
wicked lynx
@torn sphinx cool, good to know about that method 👍
dusk hollow
for sqlite3 database, how can I check if there is this value, if there is, remove the rows which have them?
grave carbon
How would I use psycopg2 to insert many rows based on a dict
i.e. py dict = { a: {1, 2, 3}, b: {2, 3, 4}, c: { 3, 4, 5} } for obj in dict: insert obj to table
would I have to do an insert x for each object or is there a way to build a single insert command from many objects
I saw a video showing it can take 10x time to do 1000 inserts of 1 item than 1 insert with 1000 items
dusk hollow
ok
wintry stream
Hey there, I want to learn simple database in python, and I see MySQL, I'm trying to install it but it keeps saying the check for 64 bit python is failing
but I do have python installed (and 64 bit if i'm correct)
anyone else has ever seen this?
or would sqlite be better?
lucid wharf
import pymongo
from pymongo import MongoClient
cluster = MongoClient("no")
db = cluster["discord"]
cllctn = db["test"]
authorid = cllctn.find({"_id":"699882706850414622"})
for result in authorid:
print(result)
python script just stops executing
dead stop without any message
{"_id":{"$numberLong":"699882706850414622"},"quantity":"69","item":"qweasdzxc"}
this is the content of my cllctn in db
hardy pine
column "userid" is of type numeric[] but expression is of type boolean
how to fix that
I have this list [347724952100667394, 705660467292536865]
and this table cur.execute("Create table UserTickets (userid numeric Array)
lucid wharf
is that answer for me? @hardy pine
hardy pine
wait
lucid wharf
tyt bro no rush
hardy pine
whats the problem
lucid wharf
python script just stops executing
dead stop without any message
won't give me any result
when i don't do for it gives me cursor etc
but when i do for it gives me nothing
hardy pine
ohkk means thats correct
{"_id":{"$numberLong":"699882706850414622"},"quantity":"69","item":"qweasdzxc"}
there is no id what you are searching for?
lucid wharf
authorid = cllctn.find({"_id":"699882706850414622"})
hardy pine
yup
lucid wharf
is this wrong?
hardy pine
yes
lucid wharf
so i shouldn't make "_id" a ctx.author.id incase i'm using it in discord.py?
hardy pine
You may not interfere that _id
make a new column name user_id
that would be good
or just store it like {_id : ID}
and a note
Pymongo is blocking, it may slow ur bot
stiff swift
hello
lucid wharf
should i use another database instead?
stiff swift
for what @lucid wharf
lucid wharf
discord bot
hardy pine
yes
lucid wharf
pymongo not good?
hardy pine
u can use postgresql
stiff swift
btu i have the basic bot like mee6 pokecord and groovy
merry mirage
Hey guys. I have a relatively big data project, including data science/machine learning.
I'm going to use:
Database for data storage
Pyspark for data manipulation/post processing before ML
Keras for ML
What do you suggest to use as database? The data consists of text, all of it. A RDBS would be good, but It's hard to maintain, I kinda afraid of digging into it. So I think I may use Mongodb in local as Nosql database. What do you think, which one is more compatible to use alongside Pyspark: Mysql, Postgresql or Mongodb? Any suggestion would be appreciated.
Or should I ask this somewhere else? data-science or maybe python-general?
bold pelican
Hello
I have a doubt. How can I save a list in postges database?
{
"699705249602076753": {
"prefix": [
"?",
"!",
"#",
";",
"\\",
"/",
"."
]
}
}
How can I save this to a postgreSQL?
Please help
hardy pine
yes
bold pelican
"699705249602076753": {
"prefix": [
"?",
"!",
"#",
";",
"\",
"/",
"."
]
}
How can I save this to a postgreSQL?
@hardy pine
eternal raptor
`
@commands.command()
async def reklamazamow(self, ctx, args, member = None):
await ctx.send(args + ' ' + "przekaż ten kod do właściciela serwera oraz zrób wpłatę.")
zamow = '{"user": "%s", "kod": "%s", "wplata": "None"}' % (ctx.author, args)
with open('zamowienia.txt', 'w') as json_file:
json.dump(zamow, json_file, indent = 4, sort_keys=True)
`
how to rechange code in order to variable "zamow" doesn't delete, when new person use this command???
it's PYTHON + JSON
brazen charm
just straight up dont use Json
not for bot DBs
along with you are aware Json is just a format
and what zamow can be is a dict
and just do ['user'] = xyz rather than string formatting
grave carbon
Why isn't psycopg library setup pythonically
bold pelican
{
"706721332653719583": {
"voice_text": {
"test 1": "test-1",
"test 2": "test-2",
"test 3": "test-3"
}
}
}
How can I flatten this?
grave carbon
I'm just passing cmd line postgresql commands into a python function feels awkward
I was expecting something more like ```py
cursor.insert(tablename, (table, columns), (values1), (values2), ...)
but I got py cursor.execute("""INSERT INTO tablename (column1, column2) VALUES ('val1', 'val2'), ('val1a, val2a');""")
@bold pelican what do you mean flatten
bold pelican
I don't know how to flatten this one so that I can save it as Table
I want to save it as a Table
in PostgreSQL
grave carbon
as a table or place the data into a table
either way I think you will have to place it into an already existing table
So first design your table
Is test 1 test 2 etc are they voice to text examples
bold pelican
yup
grave carbon
Will there ever be more than 3 of the voice to text objects
grave carbon
You can either design your table to have a column for each sample, or 1 column that contains a list of the text samples
bold pelican
Any example?
grave carbon
a column for a list of the text's would have more capacity potential , i.e., it could fit 100 into the single column
But if you know there will never be more than say, 10 texts, you can make 10 columns, one for each potential text
If you do a single column with a list of texts, you will have to parse out the list every time you pull that data yourself in your script
grave carbon
Okay so you could make a table that looks like this
bold pelican
But the guild_id has duplicate values
bold pelican
if you do like this
guild_id | voice_channel | text_channel
706721332653719583 | test 1 | test-1
706721332653719583 | test 2 | test-2
like this
it has duplicate values which would be difficult to parse out
grave carbon
Do you need to parse them out if so you can insert a timestamp or other identifier for each object
i.e.
timestamp | guild_id | voice_channel | text_channel
2020-05-25-110404 | 706721332653719583 | test 1 | test-1
2020-05-25-110628 | 706721332653719583 | test 2 | test-2```anyother better way?
grave carbon
It depends a lot on the context this data is being generated and used in
bold pelican
discord
grave carbon
oh
bold pelican
that's why I have problem
grave carbon
I dont think you will need a unique identifier then tbh
just having the whole row as something unique should be enough for your purpose I would think
guild_id | voice_channel | text_channel
706721332653719583 | test 1 | test-1
706721332653719583 | test 2 | test-2```You can find the text channel for guild_id 706721332653719583 voice channel 1 by doing this
bold pelican
fetch or fetchrow please?
grave carbon
SELECT text_channel FROM mytable WHERE guild_id = 706721332653719583 and voice_channel = test 1
and then fetchone()
bold pelican
fetchone?
grave carbon
yea
bold pelican
Well
I just don't want to change that much
code
how can I fetch multiple rows using asyncpg?
grave carbon
I only know psycopg2 for postgresql
bold pelican
how can I fetch multiple rows?
grave carbon
SELECT * FROM mytable WHERE guild_id = 706721332653719583;
fetchall()
bold pelican
fetchall?
grave carbon
it will return a list of unspecified amount for all rows that have guild_id == 706721332653719583
bold pelican
unspecified?
grave carbon
If you have 10,000,000 rows with that guild_id it will return 10,000,000 object list
if you have only 3 it will return 3
If you have 0 I think it will return an error
bold pelican
oh
grave carbon
so you may want to wrap it in a try: except:
grave carbon
Yes but it wont return all in one object
you will have to fetch over and over until you get all the data
grave carbon
oh , well thats basically fetchall() it seems like from psycopg2
Fetch all (remaining) rows of a query result, returning them as a list of tuples. An empty list is returned if there is no more record to fetch.
>>> cur.execute("SELECT * FROM test;")
>>> cur.fetchall()
[(1, 100, "abc'def"), (2, None, 'dada'), (3, 42, 'bar')]```so looks like you'll be fine with that in your library
bold pelican
text or varchar for channels?
Or channel_ids?
@grave carbon How to cast record object into dict?
grave carbon
I would do text
@bold pelican You have to do a for loop that adds each to a dict
You can adapt my function to do so if you can
def GetStockBalancesAsDict():
stockBalances = { }
query = "select ticker, balance from accrued_balances"
cur.execute(query)
rows = cur.fetchall()
for row in rows:
stockBalances[row[0]] = float(row[1])
return stockBalances```you can use the record.__dict__ provided by SQLAlchemy on the object
narrow niche
not sure where else to ask, but is there a good way to replace CROSS APPLY in aws snowflake? anyone know if i can just use a JOIN
eternal raptor
Hi, i would like to learn database effective with python. Could someone advise some course online?
grave carbon
@eternal raptor I havea great course for you sir
This is video one of the playlist its like 12 videos long gave me almost everything basic I needed to know
It should autoplay the whole course if you go there
It does sql basics, then postgresql specifically, and then a bit into the python integration of it
maiden dew
not sure where else to ask, but is there a good way to replace CROSS APPLY in aws snowflake? anyone know if i can just use a JOIN
@narrow niche Depends on what you are trying to achieve.
lost gorge
ruby dagger
hey, dumb question but im trying to essentially have a variable become true if a value is in the column in my database and false if not in my database. seems that assigning a value to my select statement with asyncpg returns the PK "select 0, select 1, etc"
snippet of what im going for
result = await conn.execute('SELECT serverID FROM public.statusuwu WHERE serverID= $1', server)
print(result)
if result:
return True
else:
return False
quaint kite
use conn.fetchrow() or conn.fetch() depending on if you want to get the first result or all results, respectively @ruby dagger
ruby dagger
actually, i realized that the thing that result becoms equal to is the amount of entries in my database
so i can just do if not equal to select 0
yeah i didnt understand what the value was
cause of the way i was testing it
lol
quaint kite
I am running into issues with performing a postgresql query, which looks something like this:
conn.fetchval(
"UPDATE discord_oauth SET access_token=$1, token_type=$2, expires_on=$3, refresh_token=$4, scope=$5 WHERE user_id=$6;",
data['access_token'], data['token_type'], data['expires_on'], data['refresh_token'], data['scope'], int(data['user_id'])
)
however I am getting an error:
asyncpg.exceptions.DataError: invalid input for query argument $1: 'None' (invalid UUID 'None': length must be between 32..36 characters, got 4)
Given the fact that this is my table schema:
I dont understand why it thinks im even giving it a uuid in the first place
update, it works when I use the query directly in an database application, so now im doubly confused
livid cairn
create database "database1";
create schema "schema1";
create table "schema1".accounts
(
...```
When I run this script, schemas aren't created on the database1. Instead, it's created in the postgre db.
On MySQL, I could use `USE database1'. Is it possible to do the same on PostgreSQL?try google
but I believe that no, it isnt possible
your postgresql connections are to a specific database
grave carbon
You use a db by connecting to a specific db in your connection @livid cairn
narrow niche
@maiden dew just micking functionality
*mimicking
Porting over old mssql queries to snowflake as is at the moment, no improvement
So i have a table valued function that mssql has it cross applying with, guess i can just inner join w the function on snowflake?
harsh pulsar
@quaint kite "not null" is checked
i.e. you can't have nulls in that column
so it doesn't let you insert a null there
it's a uuid because the database schema says it is 🤷♂️
quaint kite
Turns out my mistake was something to do with how I was handling cookies on the web page side of it that then extended to causing issues in my request
@harsh pulsar I managed to figure that one out
harsh pulsar
good 👍
quaint kite
im now struggling with why flask is being big dumb
nocturne blade
Going to try the Azure fundamentals exam again tomorrow.
And then hopefully actually get to database stuff.
grave carbon
@nocturne blade Is azure straight VPS' or does it require you to use MS SQL for db's etc?
bold pelican
hello
I have a doubt here
@bot.group(aliases=["vtl", "voice_link"])
async def voice_text_link(ctx):
voice_text_data: asyncpg.Record = await bot.pg_conn.fetch("""
SELECT * FROM voice_text_data
WHERE guild_id = $1
""", ctx.guild.id)
embed = discord.Embed()
embed.title = "Available voice text links!"
msg = ''
for voice_text_link1 in voice_text_data:
for index, (voice_channel, text_channel) in enumerate():
index += 1
print(voice_channel, text_channel)
voice_channel = discord.utils.get(ctx.guild.voice_channels, id=voice_channel)
text_channel = discord.utils.get(ctx.guild.text_channels, id=text_channel)
msg += f"{index}. {voice_channel.mention} -> {text_channel.mention}\n"
embed.description = msg
embed.set_author(name=ctx.me.name, icon_url=ctx.me.avatar_url)
await ctx.send(embed=embed)
this is my code
I get a record object like this [<asyncpg.Record(guild_id=9384303820280, voice_channel_id=048048040849, text_channel_id=2184649649794942 at 48FAC85>]
How can get the key and value pair and use voice_channel_id and text_channel_id?
@grave carbon
hot grove
How do I make sure a key is unique, but only if the primary key is the the same?
I want to have separate unique keys per primary key
spiral dirge
if I wanted my bot to show, the average price of items sold in the market with filters by time(today, this week, etc) what would be the best way to do this and conserve db space
minor ruin
Primary Keys in almost all databases must be unique
Ceres, doing math on orders when asked for
SELECT orderprices FROM orders WHERE time > lastweek;
sum(orderprices) / len(rows)
chrome vault
i am a beginner and i want to learn database in python
can someone help me?
which db i should go and where i can learn?
pseudo summit
Get comfortable with python first imho.
golden warren
What it mean ?
[ERROR]: 'SentinelBot' object has no attribute 'sqlite3'
bold pelican
@tasks.loop(seconds=10)
async def add_guild_to_pg():
await bot.wait_until_ready()
guilds_data = await bot.pg_conn.fetch("""
SELECT * FROM cogs_data
""")
# guilds_data = []
for guild, record in zip(bot.guilds, guilds_data):
if guild.id not in record.keys():
print(record.keys())
print("check passed")
loop doesn't work
check passed doesn't get printed
minor ruin
how is that query too long?
lime echo
@bold pelican
I think that you haven't opened the database or even connect to it?
what are you using, SQL or?
bold pelican
Postgres
Oh
So
Is there any way to stop till when the connection is complete?
I'll try with a global variable.
livid cairn
Is it possible to make this query a function (or a store procedure), so it can be easily called (PostgreSQL)?
SELECT aid, tid, dt from task1
WHERE status = true
UNION ALL
SELECT aid, tid, dt from task2
WHERE status = true
@livid cairn You could use the prepare statment
PREPARE some_name AS (Query statement here)
you could do it with a function but idk if its worth it
brazen charm
if i can remember off the top of my head
you can do EXECUTE <prepared statement name here>
lime echo
c.execute("""DELETE FROM ban_listtab WHERE member_id=?, unban_date=?""", (record[0], record[1],))
db.commit()```this doesn't work actually 😦
neither this
c.execute("""DELETE FROM ban_listtab WHERE member_id=?""", (record[0]))
c.commit()```
vocal hare
what does ? do
lime echo
@vocal hare it supposed to set member_id as a variable
so that I can insert record[0] into it
vocal hare
just use fstrings
vocal hare
y
brazen charm
Thats asking for injection attacks
vocal hare
whats that
lime echo
something related to hacking
brazen charm
brazen charm
isnt it connection.commit() rather than cursor
lime echo
c.execute("""DELETE FROM ban_listtab WHERE member_id""", record[0])
db.commit()```like this
lime echo
db=sqlite3.connect("something.sqlite")
brazen charm
yh
lime echo
im testing it rn
still doesn't work
for record in list:
#print(type(unban_object))
if str(right_now)>=record[1]:
print(str(right_now))
print(record[1])
print("worked")
#await record[0].unban()
c.execute("""DELETE FROM ban_listtab WHERE member_id=?""", record[0])
db.commit()
db.close()```@brazen charm here is the code
it doesn't work
it just prints 1 time then stops printing
and it doesn't delete anything from my db
vocal hare
hmm it looks like theyre only vulnerable if its actual sql code
well
not exactly, actually
well so just dont have user input to the database
brazen charm
or... Just dont use f strings
and use actually good practices
@lime echo Is it supposed to close the Db connection after the first record
lime echo
uhm actually no, I need to fix that too but it's not the issue
oh well, it might be
let's me try
It worked
brazen charm
boom
lime echo
thank you so much!
minor ruin
Read from Mongo and write to Postgres
lavish ferry
what should I do? 
asyncpg.exceptions.InvalidAuthorizationSpecificationError: no pg_hba.conf entry for host "123123123", user "yyy", database "xxxx", SSL off
golden warren
Yop! I have this error but, i don't why it doens't work. I juste would like to create my db file in e specific forlder.
self.connect = sqlite3.connect('data/' + self.filename) sqlite3.OperationalError: unable to open database file
@lavish ferry I donn't understand your error, it's not a real config here ?
clever topaz
@golden warren Have you got a data folder?
golden warren
@clever topaz
[DATABASE]: <bound method Client.connect of <src.main.SentinelBot object at 0x7f4f893414c0>>
What dose it mean ?
lavish ferry
@golden warren ofc is a real config
clever topaz
Ah right.
lavish ferry
my user, database and password is not it, its just a example
clever topaz
@golden warren Can you paste your print statement.
@lavish ferry A quick search will turn up the cause:
https://confluence.atlassian.com/jirakb/error-connecting-to-database-fatal-no-pg_hba-conf-entry-for-host-x-x-x-x-user-jiradbuser-database-jiradb-ssl-off-950801726.html
If that's not enough, you'll find plenty more online.
It's basically because the IP you are connecting from isn't allowed.
And you have to add it to pg_hba.conf
golden warren
It's that @clever topaz
<bound method Client.connect of <src.main.SentinelBot object at 0x7f4f893414c0>>
clever topaz
No the actual line of code that prints that.
golden warren
print(f'[DATABASE]: {self.connect}')
neon marten
/opt/lib/pgsql/your version/data/pg_hba.conf
thats the location in linux
@lavish ferry
clever topaz
Or run a locate.
lavish ferry
the problem is, how am I going to fix this if I can't even open this file on heroku
clever topaz
Why not?
lavish ferry
I don't even know how to open it directly on the host
lime echo
bruh im having an issue
I fixed this before but it became problematic now
c.execute("""DELETE FROM ban_listtab WHERE member_id=?""", (record[0],))
print("unbanned")```@golden warren Where did you put that print statement? Before self.connect = ? Can you paste a bit more of the code.
clever topaz
What is it?
golden warren
self.connect = sqlite3.connect(f"./src/data/{self.filename}")
solemn ridge
seems like you are passing the function object
solemn ridge
golden warren
because i just prefere ;)
umbral granite
Whats the best way to approach a tournament database in the following
- Each individual player stats will be recorded so you can see their overall seasons stats
-each tournament has up to 6 games
Can you have like nested databases?
celest blaze
nah
just have a database whose rows each contain: a player ID, a game ID, and the score for that player in that game
umbral granite
and just query a players entire stats after that
celest blaze
dunno what you meant
umbral granite
so you would have table for each game showing every player and placement/score. But then if you wanted to store an entire players stats somewhere, would that also be a new table?
celest blaze
I have no idea why you think there should be a table for each game.
since you have more than one tournament, you could add a "tournament" column.
you could go crazy and have one table mapping tournaments to games, and another mapping players to games, and then join those; but I think a single table with all three would be fine
| tournament | game | player |
|------------+------+--------|
| t1 | g1 | p1 |
| t1 | g1 | p2 |
| t1 | g1 | p3 |
| t1 | g2 | p1 |
| t1 | g2 | p2 |
| t1 | g2 | p3 |
| t2 | g1 | p1 |
| t2 | g1 | p2 |
| t2 | g1 | p3 |
| t2 | g2 | p1 |
| t2 | g2 | p2 |
| t2 | g2 | p3 |
fresh nova
Hi
fresh nova
Can someone help me how to write login function with postgre?
regal drift
How to give a unique id for each row in a postgres view?
maiden heart
do any of those tuorials are good for learning SQLlite ?
or I should see sth else ?
NVM :(
flint wadi
Am I passing raw SQL to the filter method?
User.query.filter(User.username.like(name + "%")).all()
woven flower
idk if this counts as a database question but How do you add hyperlinks in python with the google sheets api? Ive looked everywhere and cant find anything
weary lantern
Hello friends! How are you? I have a little lack of time and I needed someone's help.
I need a program with a simple Python interface with two views (user and operator), being an application for electric car charging machines, compatible with mobile phones or tablets.
The context is as follows: Electric car users need to charge their car. This application will handle the loading of all these users (which would not be many) in a small charging station in a building, so that there are no overload or low spikes. With a graphically constant curve of electrical energy. Also, the energy consumption of the building is linked to the consumption of cars on this curve.
The user arrives at the station (or in the application when he is already at the station) and enters the desired time of departure, the type of car, the desired level of charge in the car, name, mobile phone number, identification number and a function to pay (form of payment by debit or credit card). The program must make the best optimization so that all users have what they have requested of loading in the best possible way.
The system would have to save the customer's data in a database and present data specific to each car and its battery for better charging.
The building may also contain aid in the production of energy from renewable sources, such as solar panels, which would aid consumption.
The charge for charging afterwards is XX per KW / h. (Where XX must be changeable from time to time).
Can anybody help me? I would be very grateful..
unborn sentinel
Huh who what?
Oh man
@weary lantern Please only ask your question in one channel, and preferably by using our help system (see #❓｜how-to-get-help for details).
Also, it should be noted that we are willing to help you with programs if you get stuck making them or need advice, but we're not a jobs board and we will not do any code for you.
crimson urchin
Hello, my question is :
Is there a way for csv.DictReader to read files that are UTF-8-BOM? I don't like having these characters being read at the beginning of my csv files
clever topaz
@crimson urchin Can you give an example of the file?
crimson urchin
@clever topaz Here is my file
It's a .txt file provided from an external source
Meant to say .txt files in the form of a csv
clever topaz
Perhaps, before reading it, text = file.read().decode('utf-8-bom')?
crimson urchin
Not exactly sure how that would work in this instance:
with open("D:/Bot/Discord/Zachline/primary/data/gtfs/{}/routes.txt".format(agency),'r') as routesFile:
routesReader = csv.DictReader(routesFile)
for i in routesReader:```
subtle condor
Howdy! Is there a way to see what queries SQLAlchemy is performing on the database? Or maybe directly in sqlite? I want to verify what is exactly queried through the ORM.
clever topaz
@crimson urchin
with open(f"D:/Bot/Discord/Zachline/primary/data/gtfs/{agency}/routes.txt",'r',encoding='utf-8-bom') as routesFile:
routesReader = csv.DictReader(routesFile)
for i in routesReader:
Maybe?
timber karma
subtle condor
to answer my own question... no idea why I did not find this earlier... https://stackoverflow.com/questions/31128764/show-the-sql-generated-by-flask-sqlalchemy
oak schooner
lbDb = col.find_one({"docName" : "leaderboard"})
what exactly will this(mongodb) return if the doc is not found
@me
raw geyser
and sqlite3 gods around
pseudo summit
@oak schooner None
Per the docs
warped bramble
what database shud i use, im currently scraping data and loading it into a .csv file
stable heath
Csv file seems just fine, until it isn't. Are you facing problems with just using Csv?
If the volumes increase or the structure becomes more complex, you can start exploring other methods of storage. No need to jump to a database if you don't need one.
If you just want to do it for the sake of using a database you can start with sqlite. Pretty painless
past widget
Um, help, I actually made a table main, and in it a row called status, but I'm getting this error
db = sqlite3.connect('main.sqlite')
cursor = db.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS main(
status TEXT
)
''')
db = sqlite3.connect('main.sqlite')
cursor = db.cursor()
cursor.execute(f"SELECT status FROM main")
result = db.fetchone()
cursor.execute(f"SELECT status FROM main")
sqlite3.OperationalError: no such table: main
Ignoring exception in on_message
Traceback (most recent call last):
File "C:\Users\kanna\.virtualenvs\OpenCityBot-MovingJSON-PostgreSQL-J0GRFsut\lib\site-packages\discord\client.py", line 312, in _run_event
await coro(*args, **kwargs)
File "F:\PyCharm Python Works\OpenCityBot-MovingJSON-PostgreSQL\Bot\cogs\Leveling.py", line 130, in on_message
await self.update_data(message.author)
File "F:\PyCharm Python Works\OpenCityBot-MovingJSON-PostgreSQL\Bot\cogs\Leveling.py", line 41, in update_data
user_data = await self.bot.pg_conn.fetchrow("""
File "C:\Users\kanna\.virtualenvs\OpenCityBot-MovingJSON-PostgreSQL-J0GRFsut\lib\site-packages\asyncpg\connection.py", line 454, in fetchrow
data = await self._execute(query, args, 1, timeout)
File "C:\Users\kanna\.virtualenvs\OpenCityBot-MovingJSON-PostgreSQL-J0GRFsut\lib\site-packages\asyncpg\connection.py", line 1401, in _execute
with self._stmt_exclusive_section:
File "C:\Users\kanna\.virtualenvs\OpenCityBot-MovingJSON-PostgreSQL-J0GRFsut\lib\site-packages\asyncpg\connection.py", line 1840, in __enter__
raise exceptions.InterfaceError(
asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress
Please help
grave carbon
How do I use psycopg2 to insert multiple records in one sql command
a dynamic amount of records
Do I need to build an SQL Code string to then execute to make it work?
wicked lynx
I think there's an executemany for psycopg2
wet ravine
Please, i need help how do i put my variables in my query
cursor.execute(sql)```the date is variable
and all_stations[0] is list
i want the specific variable value to put in to database
torn sphinx
@wet ravine sql = """INSERT INTO planned_out(col1,col2,col3) VALUES (?,?,?)"""
then cursor.execute(sql,(variable1,variable2,variable3))
wet ravine
@torn sphinx why do we use triple inverted commas ?
torn sphinx
triple?
wet ravine
"""
wet ravine
how is it risky ?
wet ravine
ok thanks