the id should go by 1-2-3 and so on. according to the row on the table itself
#databases
proven arrow
Which database?
proven arrow
Yes but if you had data in there before it will continue
torn sphinx
oh ok
is there any way to "reset" it?
oh ok i figured this out
just need to truncate the table before the data transfer
thanks for the heads up man @proven arrow
mystic vale
Hello guys! Who can tell me. How I can deploy telegram bot if I have the Django administrating. And I need deploy the bot with Django project and DB.
Maybe any documentations?
States.
marsh tinsel
how do you make expiring documents in pymongo
runic pilot
you can set a TTL on documents, read about it here https://docs.mongodb.com/manual/tutorial/expire-data/
(TTL = time to live)
grim lotus
How do big companies manage Thier Database , they get like 10 million rows a days , how so they still maintaining the speed and efficiency ?
marsh tinsel
how come pymongo cant find any documents
glass gorge
i tried to uninstall postgress from my unix
i hit this error at the end
When the uninstaller finishes, a Warning notifies you that the data directory and service user account have not been removed
I don't really understand why it wouldn't remove them
I also don't understand why it originally installed postgress in a super user directory instead of a regular one
novel elbow
I am wanting to create a function that allows the user of an app to print a specific text file at any point with typing a key word, any advice or tips?
limpid sand
I am wanting to create a function that allows the user of an app to print a specific text file at any point with typing a key word, any advice or tips?
@novel elbow This doesn't really need a database? Does it? What are you planning?
make a dict inside the function, mapping keywords to filename,
novel elbow
I am working on my final project for college:
The project is a cocktail book
The program has a large amount of cocktails saved as subclasses to a main class of "Cocktail"
The player can search for cocktails with certain ingredients
The player can pull a recipe for any of the cocktails
limpid sand
how many ingredients are you planning for this project?
novel elbow
well to start I will take a few dozen cocktails and input their ingredients
I am considering having a true/false system
limpid sand
Is OOP necessary for the project? as in a requirement?
novel elbow
No I just need to use python
limpid sand
I was thinking just simple lists
pseudo cove
wrong channel btw
novel elbow
What channel should I use
fairly new to python
class Cocktail():
def init(self, name, noIngredients, ingredients, noDirections, directions):
self.name = name
self.noIngredients = noIngredients
self.ingredients = ingredients
self.noDirections = noDirections
self.directions = directions
self.serves = serves
negroni = Cocktail('Negroni', 5, [gin, sweetvermouth, campari, ice, orange], 4, ['Add the ingredients together in a cocktail shaker.', 'Stir well with cracked ice.', 'Strain into a glass over cubed ice.', 'Garnish with a twist of orange peel.'])
grim lotus
humble sundial
upbeat marsh
Hello! I have a question, I have the class/table AbstractZone with:
__mapper_args__ = {"polymorphic_on": discr}
and another one called CommercialZone that extend from the previous one and using:
__mapper_args__ = {"polymorphic_identity": "commercial_zone"}
how can I make AbstractZone sort of like abstract? (AbstractZone has a few relationship that the children should be able to use, like country)
little flume
my code
MySQLConnection.mycursor.execute("SELECT cash_amount FROM Money WHERE moneyuser = %s", [str(usercash)])
result = MySQLConnection.mycursor.fetchone()
print(result)
MySQLConnection.mycursor.execute("UPDATE Money SET cash_amount = %s WHERE moneyuser = %s", (int(cashamountdownload) + amountcashadd, str(usercash)))
MySQLConnection.db.commit()
cashprnt1 = int(0 if result is None else result)
cashprnt2 = int(cashprnt1) + amountcashadd
the error:
raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: AttributeError: 'NoneType' object has no attribute 'fetchone'
Ignoring exception in command None:
how do i fix?
also copy and pasting screwed up the indents
chrome dock
Is better to use sqlalchemy or psycopg2 or asyncpg π
brazen charm
asyncpg is the best postgre driver but its a async system so ig that depends on if your system is async or not
sqlalchemy uses psycopg2 under the hood anyway @chrome dock
jovial notch
@tepid cradle hey bro i need some help u here?
tepid cradle
@jovial notch what's your question?
Is better to use sqlalchemy or psycopg2 or asyncpg π
@chrome dock sqlalchemy uses psycopg2 underneath, so it's not really very different. However, if you want to use an ORM like system, where you don't have to write raw SQL queries, then you use Sqlalchemy. Otherwise stick to psycopg2.
I haven't really used asyncpg.
jovial notch
I was wondering how I could only get the 5 top joiners of the voice channel lowest being 0.
tepid cradle
How do you define top joiners?
jovial notch
wym
tepid cradle
As in, what do you mean by top joiners? People who have joined most number of times or joined most recently, etc.
tepid cradle
Usually it's better to try to resolve a question by understanding the required logic rather than by looking at the code.
Also, it's difficult to understand code without knowing the logic.
So basically you want people ordered by amount in descending order?
keen wind
deleted message
jovial notch
so what i want is to get only 5 people from the whole column and i want them ordered from highest to lowest being 0
@tepid cradle
tepid cradle
Ok
So you can do it in the query itself like this
query = "select * from table order by amount desc limit 5"
How do big companies manage Thier Database , they get like 10 million rows a days , how so they still maintaining the speed and efficiency ?
10 million rows a day and in 1month you db will be slow ?
What magick does they do
brazen charm
In reality 10 million rows isn't that much for a relational dB, but for speed and efficient generally involves giving the DB more ram and cpu allowance, creating index for faster lookup, better disk types e.g ssd over HDD
Defragging etc...
Ig most people just go with the throw more server at it
Caching as well is a big thing
With proper caching you can cut down on dB queries by alot of done right
lucid temple
A big thing is using partitioning for dynamic data https://www.postgresql.org/docs/10/ddl-partitioning.html Or using a column-based db such as Clickhouse https://clickhouse.tech/ - I remember Cloudflare showing how they do it for their analytics platform https://blog.cloudflare.com/http-analytics-for-6m-requests-per-second-using-clickhouse/
tepid cradle
In addition, to the above points, it is also important to design your database correctly. Database tables are not a place where you just dump your data.
People generally underestimate how important it is to structure your database well.
Divide data in tables so that data which will be fetched together, stays together. Design tables in such a way that you have minimal duplication. Create indices on the right columns or set of columns to make lookups faster. Use correct data types to avoid using up unnecessary space and avoid casting of data types in queries.
All these are just as important as any other.
I have a table in a PostgreSQL Db with 11 million rows. Over my local network, fetching about 300 rows takes ~12 ms. And that Db resides on a Raspberry Pi.
Imagine the kind of performance databases can give on enterprise grade SSDs backed by Octa core Xeon processors and 64 gigs of RAM.
grim lotus
I mean i don't see a way to setup the db ram usage ?
Is it automatic ? Means the more ram and the more cpu power my system will have on which the db is running will that make the database faster automatically ?
torn sphinx
I have a script that uploads to database every hour. I upload like 500 items which i get from an api. I do every hour in case data from api changes so i need to refresh data in the database.
However if item already exists with PK i get error duplicate entry for this PK.
What can I do to ensure that if the data has changed then just update the value/row that has changed?
I update to the database like this currently. Is MySQL db.
cursor = db_connection.cursor()
statement = "INSERT INTO products (id, name, description, image, price) VALUES (%s, %s, %s, %s, %s)"
cursor.executemany(statement, data_tuple)
db_connection.commit()
hasty juniper
await db.execute("UPDATE Count SET Name_Channel_text_channels = ? WHERE Guild_ID = ? WHERE id"
``` i can do that?The last one is a bit fishy. It's not syntactically incorrect unless id is a boolean column. But id is hardly ever a boolean column. So what is your objective in ending the query with where id?
Everything before that looks correct
@hasty juniper
tepid cradle
I have a 6 value in table, I need to sort one value by two values
@hasty juniper it seems you've used the word value to refer to all of value, row, and column.
Can you rephrase with the correct terms so that I can understand what you're trying to do
hasty juniper
like a have id_member and guild_id and I need to find a value by two values @tepid cradle
tepid cradle
Yes, for that you dvd to specify the id where id = something
The syntax is where id = ? and guild_id = ?
The word where should appear only once, rest should be added with and
torn sphinx
Um anyone can help me please? #databases message
upbeat marsh
@torn sphinx catch the duplicate key error and compare the data. if you believe is the same just run an UPDATE query with the diff?
torn sphinx
What if i want to force update each record?
Also to mention i have access to the previous data, and new data in my script before i upload to db
Data is in tuple format.
rough hearth
I'm trying to make sure I understand the terminology around databases. The closure of a set of attributes is all the other attributes you'd get if you follow each arrow out recursively. So does that mean that the minimum candidate key is the smallest set of attributes, the closure of which includes all attributes?
so I think the minimum candidate key would be {A, B, G}+ because following {A, B}+ gets you everywhere except G and F, and G gives you F.
runic pilot
looks like you might only need G and B, since G gives you A
sly wren
hey all, I've been trying to connect with a local database and pull stuff from it. I've made it connect succesfully as far as I can see but when I try to send specific data or all the data from a table to console it won't work, itll say no such table named dayindicators. While in DB Browser I have a table named dayindicators. I've never worked with databases before so I could be doing something. Anyone got an idea on what the problem could be? Here is the code I have currently: ```py
connection = sqlite3.connect("notsurewhattocallthis.db")
cursor = connection.cursor()
cursor.execute("SELECT * FROM dayindicators")
results = cursor.fetchall()
print(results)
cursor.close()
devout garnet
Hello folks, I could use some help with a simple dict() (python dictionary) function. I'm new to using the module and having a bit of trouble with sorting the values
devout garnet
thanks. I got it to sort using l.sort(reverse=True)
i guess it was converted to a 'list'
can I show you the code?
brazen charm
sure
devout garnet
string = input("Enter any collection of words, and the output will show the number of times each word occurs in the collection.\n")
lower = string.lower()
#print(lower)
lower = string.split()
#print(lower)
#print(lower)
counts = dict()
l=list(counts.items())
i = 0
#word = ######
for word in lower :
if word in counts:
counts[word] += 1
else:
counts[word] = 1
i += 1
l.sort(reverse=True)
num = dict.keys(counts)
#print word counts
print("Total words:")
print("============")
#print index (how many words total)
print(i)
print("Word count")
print("==========")
for key in sorted(dict.keys(counts)) :
print(key , " :: " , counts[key])
commented parts were just testing
brazen charm
you know what would be alot easier to use todo that
brazen charm
they essentially do what you've done there
would defo be worth looking at https://discordapp.com/channels/267624335836053506/723655946672275466/733039892371865621
with Jetbrain's videos
devout garnet
thanks. that's helpful. however, my code that I showed above does exactly what I want EXCEPT it is sorting alphabetically, instead of sorting by number of word frequency
brazen charm
as for the main question/discussion, it .keys() is essentially returning a list of the keys
you're then ordering it alphabetically
devout garnet
i imagined it would be...
when getting caught up in loops and indexes, I sometimes get mixed up
thanks for the chat. I suppose I should look for 'sorting by value of dict'
brazen charm
look at the the Jetbrain's video
they did and example of what youre trying todo
with the different collections but also regular dicts
devout garnet
great, thanks
grand estuary
Just asking to see if its common for databases to have integer primary keys in some tables and uids in another, or is it exclusively (most of the time) one or the other?
spice kindle
hey guys, I'm new to this community π and hoping to help others if I can π , but currently need help/guidence on how I can trigger some code a week before a date+time set on MongoDB
i've looked at MongoDB triggers, but it doesn't look like CRON will help me much
jovial notch
karmic flicker
Hey yβall, I was thinking of making a simple gratitude journal. Basically I want to list 3 things or events Iβm grateful for. But I also want to store them somewhere and be able to access them and see the data and the time. Is there a way to do this through python? SQL?
twilit marlin
@karmic flicker sure it is completely doable in python
you could even go a few steps further and create a web interface for it using something like django or flask
karmic flicker
Thanks!
tepid cradle
Just asking to see if its common for databases to have integer primary keys in some tables and uids in another, or is it exclusively (most of the time) one or the other?
@grand estuary it's entirely up to you, keep whatever primary key you want.
Personally, I believe the primary key should be meaningful, something you'll actually use in queries. A dummy id column which has no meaning is actually useless.
torn sphinx
how fetch the data in mongodb
late lake
okay so... i keep gettings errors.. Lost connection to MySQL server at 'reading initial communication packet', system error: 0 when ive got port="8080" in my connector, and Can't connect to MySQL server on 'localhost' (61) when i dont have port... the first error comes up after a minute ish of the bot 'running' but the bot doesnt actually run yet..
high geyser
c.execute("BEGIN TRANSACTION")
c.execute("""UPDATE guildstats
SET ?=?
WHERE guildid=?""",(column_name,1,guildid))
c.execute("COMMIT")``` c.execute("""UPDATE guildstats
sqlite3.OperationalError: near "?": syntax error```error
why did this happen? pls help
am using sqlite3
brave bridge
@high geyser You can't parametrize a column name like that, I believe.
high geyser
what
brave bridge
only the values
high geyser
oh no but then how will I
I need to do that bro
column_name = self.get_column_name(plugin_name=plugin_name)
c.execute("""SELECT * FROM guildstats
WHERE guildid=?""",(guildid,))
result = c.fetchall()
if result != []:
c.execute("BEGIN TRANSACTION")
c.execute("""UPDATE guildstats
SET ?=?
WHERE guildid=?""",(column_name,1,guildid))
c.execute("COMMIT")```see?
like that
brave bridge
If you really trust the source of column_name (i.e. it does not come from the user), you might just interpolate it. But maybe there is a better solution.
brave bridge
With f-strings, for example.
I haven't used sqlite that much (if that's sqlite), maybe there's some built-in transaction stuff in the adapter.
high geyser
c.execute(f"""UPDATE guildstats
SET {}={}
WHERE guildid={}""",(column_name,1,guildid))```like this?
brave bridge
no, just substitute the column name
f"""UPDATE guildstats set {column_name}=? WHERE guildid=?""", (1, guildid)
But I really doubt there's no better way to do what you want.
brave bridge
If column_name is somehow coming from user input, don't do that, of course
Also (and now for something completely different), you shouldn't do blocking calls (without running them if your program is async. There's probably an async adapter for your DBMS.
If you're using SQLite, there's aiosqlite
If you're using PostgreSQL, there's asyncpg
brave bridge
Right now -- yes
If there really is no safer solution, I would mark that with a TODO comment
# TODO: ...
or
# HACK: ...
i am new to databases
how do i store and make databases?
i want to store id of users
like if someone is afk i want to store that
when someone runs the afk command
tepid cradle
@high geyser is your query resolved?
When someone tries to parameterize column names, I generally say two things to them
- You don't really need to do that. You think you do, but there's almost always a better solution
- If you absolutely have to do it, then a database has something called an information schema (at least, that's what it's called in Postgresql), which has a list of all column names. After getting the user input, first check that the column name is there in the information schema, then join them using f-string. Proceed to pass parameters the usual way
high geyser
yes thanks bro
can I explain what I want in detail like is there any alternative? @tepid cradle
tepid cradle
Sure
high geyser
so Ive got a command called enable where py @config.command() async def enable(self,ctx,*,plugin:str): print(f"plugin is {plugin}") existing_status = self.check_plugin_status(plugin=plugin,guildid=ctx.guild.id) if existing_status != 1: await self.enable_plugin(plugin_name=plugin,guildid=ctx.guild.id) embed=discord.Embed() embed.colour=discord.Colour.blurple() embed.description = f"Successfully enabled plugin `{plugin}`" await ctx.send(embed=embed) else: embed=discord.Embed() embed.colour=discord.Colour.red() embed.description = f"Plugin `{plugin}` has already been enabled." await ctx.send(embed=embed)
I get the plugin name from the user
this is in a class called Tags
class Tags(commands.Cog):
def __init__(self, client):
self.client = client
self.id = 744149940082835577
self.plugins = [("nsfw filter","nsfw_filter",0)]```in this function py def check_plugin_status(self,plugin:str,guildid:int): column_name = self.get_column_name(plugin) c.execute(f"""SELECT {column_name} FROM guildstats WHERE guildid = ?""",(guildid,)) result = c.fetchone() print(f"result is {result}.") if result is None: for (pluginname,_,status) in self.plugins: if pluginname.lower() == plugin: return status else: return result[0]
in the tags class
I have self.plugins= a tuple
the tuple contains the following:
(the plugin's name, the plugin's column in the database which needs to be updated, the default value of the plugin like hwether it is set to true or false
so now in that function
I need to match the column with the plugin name
and then get the query
so what am doing is the best practice or are there any alternatives? @tepid cradle
tepid cradle
Can you show this table's schema?
Like, either the create table command or a screenshot of the first couple of rows and all columns
high geyser
@tepid cradle
tepid cradle
See if you can design it like this :
id guild plugin active
1 abc some plugin true
2 xyz another plugin false
@high geyser
tepid cradle
So?
Guilds can also have multiple rows
That's how tables are actually designed, that's the best practice
high geyser
okay thanks Ill look into it. It would take a while to change the code though
tepid cradle
Yes. But that's the usual way of designing database tables. Because you may want to add more and more plugins over time. You can't keep modifying the table's schema every time.
pulsar kestrel
anyone know why get a null verein_id 1? i have datas in line 1 like msv duisburg etc.
plush vector
Hi, I'm creating an API with FastAPI and I'm using postgresql as DB. I'm creating new postgres schemas for new clients dynamically. I want alembic auto migration to work on for all schemas if for example I add a new model the table should be created in all the schemas. I found https://gist.github.com/h4/fc9b6d350544ff66491308b535762fee this works for any updates to the model columns but not with new models i,e if I change column to json from text alembic will detect the change and update all schemas likewise but if I add new model alembic won't detect it for all schemas it will just detect it for public schema only can anyone help?
azure herald
anyone got any idea how to get this kind of thing to work https://paste.pythondiscord.com/iyubonukih.py trying to use sqlite3 with discord.py
ashen shadow
Hello guys I would like to develop a version control of exe files
Does someone have any idea?
tired sorrel
Traceback (most recent call last):
File "drawFeatures.py", line 7, in <module>
cv2.error: OpenCV(4.3.0) C:\projects\opencv-python\opencv\modules\imgproc\src\resize.cpp:3929: error: (-215:Assertion failed) !ssize.empty() in function 'cv::resize'
do u know the solution guys
dont forget to mention my name
high geyser
@tepid cradle I changed the db design now because I got the time. thanks bro
it helped me a lot
tepid cradle
@plush vector can you explain what you're doing? Creating a new schema for each client doesn't sound like a good idea
plush vector
@tepid cradle I am storing data of a new client (organization) in a different schema
I have to separate data of each organization via schemas
tepid cradle
Is that a design you've created or is that a requirement you've received from someone?
plush vector
thats a requirement
tepid cradle
Ok. Then not much you can do. But that's usually not how databases are designed. Data for all users is generally kept in a single database. Databases are split for micro-services or based on how the application is designed
plush vector
actually clients are different organizations each schema will store data pertaining to an organization
so what I want is that if I add a new model alembic should automatically generate a script that will create the table across all schemas
tepid cradle
actually clients are different organizations each schema will store data pertaining to an organization
@plush vector yeah, that's what databases do. There is no need to create separate database for each client, unless the database is hosted on the client side, i.e., your application will be deployed on the client's own servers.
I have no idea about FastAPI or Alembic. Was only discussing from a design perspective.
plush vector
Yeah from the design prespect I also had suggested that we should only use one database but it didn't fit well in their head
inland canopy
Hi everybody, hope you could give me some advice. I am currently using SQLite to save real-time data obtained from a websocket connection, do you think there could be done some improvement using other Relational DB Management System, for example, PostgreSQL or any other. It's a ton of data being saved for like 6 hours every day.
torn sphinx
still not working
proven arrow
Define not working? What doesn't work? Show any error you are facing if so.
Also question is not entirely related to #databases
wheat path
Do I need to install mysql on my pc for the mysql-connector-python package to work?
proven arrow
Yes
MySQL is the actual database system
The python connector package is what allows you to interact with it through the python language
proven arrow
No then you don't need to install the database locally
You would just put in the database connection details in python.
wheat path
Ah
I pip installed it and whenever I try to import it, it says ModuleNotFoundError: No module named 'mysql.connector'; 'mysql' is not a package
proven arrow
Did you install the correct version for your python version and environment?
pip install mysql-connector-python
wheat path
Yeah
I created a venv
i went into it and pip install mysql-connector-python
then when i tried to run the file
which is just a test file that only connects to it
and it gives me that error
proven arrow
if you enter the command in your environment pip show mysql-connector-python what output do you get?
And also make sure to not name your file mysql.py
wheat path
oh
thats the problem
haha i didnt know that bothered it
yup it works now
Thanks!
torn sphinx
idk why people dislike sqlite3, I love it.
inland canopy
@torn sphinx, I have only used sqlite and it's been fine for me, just was interested in listening to some opinions, pros and cons
meager vine
SQLite has limited datatypes for a start
Is using the Automap capability of SQLAlchemy a sensible idea?
It seems excessive to manually map a pre-existing DB but at the same time I feel like there must be drawbacks to doing it automatically?
marsh tinsel
How can i make a searchbar with pymongo
Like postgres has WHERE item LIKE something
high geyser
sql = """SELECT name, id
FROM tag_lookup
WHERE location_id=$1 AND name % $2
ORDER BY similarity(name, $2) DESC
LIMIT 100;
"""
results = await ctx.db.fetch(sql, ctx.guild.id, query)``` how do I write this statement in sqlite? pls help me there is no similarity functionpls ping me when help
tepid cradle
Hi everybody, hope you could give me some advice. I am currently using SQLite to save real-time data obtained from a websocket connection, do you think there could be done some improvement using other Relational DB Management System, for example, PostgreSQL or any other. It's a ton of data being saved for like 6 hours every day.
@inland canopy SQLite is also and RDBMS. But yes, write performance would be much better on PostgreSQL, especially if you have multiple threads/scripts writing to the db at the same time. SQLite isn't really built for concurrent write operations.
torn sphinx
If you have more writes than reads them I'll suggest you to use MongoDB.
tepid cradle
idk why people dislike sqlite3, I love it.
@torn sphinx I don't think anyone dislikes sqlite. It has its uses. It's just not built for hosted web applications with multiple simultaneous users. If your application is self contained, where the db will reside on the client machine, then sqlite is the best option for such an application. For instance, Firefox uses sqlite to store it's data on your computer, so do many other applications.
If you have more writes than reads them I'll suggest you to use MongoDB.
@torn sphinx any specific reason for this suggestion?
How can i make a searchbar with pymongo
Like postgres has WHERE item LIKE something
@marsh tinsel I'm not well versed with Mongo, but you could use regex
{field: {$regex: 'pattern', $options: 'i'}}
Actually a better answer would be: Use LSM Tree based DBMS if you have large writes.
tranquil hill
[postgres]
Hello, let's say I have a vote table votes with user id uid and count of votes.
I expect the table to be frequently polled with getting top 50 most voted users, eg select * from votes order by count limit 50
It would make sense for me to have a "view" that gets automatically updated with this query everytime the counts change (can go up one, or down one).
I might me mixing views with indexes but I've made myself a simple view for this, though the question is then, is the view automatically updated ? thanks
tepid cradle
Essentially creating a view is like storing the underlying query in the database itself
So instead of writing the query in your program, you just call that view
The query is then run in real-time
So there's no performance benefit of creating a view
tranquil hill
have just stumbled upon some SO answer which says exactly this, thanks a lot anyway:)
tepid cradle
You might want to look at materialized view
Or just create an index on the main table, as you said so yourself
tranquil hill
will have to try both (to wrap my head around what I actually need) ty
valid thistle
anyone familiar with postgis around here?
valid thistle
it's more to do with geometries, geojsons
shell ocean
anyone familiar with postgis around here?
@valid thistle what about?
golden shadow
I have something like this to get length of all posts by in users_posts table:
total_member_posts = Posts.query.order_by(desc(Posts.time)).all()
I have two issues:
-> By using .all() I am actually loading each and every single post and later I am using len(total_member_posts) to get total number of posts
-> And second, I want to filter this in a way that the current_user's posts don't get displayed, I have another variable for displaying current_user's posts but that's not what I want this variable to do
Any help?
torn sphinx
Well, maybe use filter then
velvet coyote
hey guys just arrived here
golden shadow
Well, maybe use filter then
@torn sphinx that's the problem, I don't know what to pass to filter_by()
torn sphinx
What uniquely identifies the user?
torn sphinx
@client.command(name='welcomesetup')
async def welcomesetup(message: discord.Message, arg1 = str, arg2 = str, arg3 = str):
async with aiosqlite.connect(...) as database:
await database.execute(f"INSERT INTO Welcomes VALUES ({arg1}, {arg2}, {arg3});", (message.guild.id, message.channel.id, message.content[:-6]))
await database.commit()
await database.close
await client.send(f"Your welcome channel has been set to `{message.channel}` with the message of: {message.content[:-6]}")
how far off am i
golden shadow
What uniquely identifies the user?
@torn sphinx current_user.username
hazy mango
Show full code @torn sphinx
torn sphinx
def create_table():
cur.exute('CREATE TABLE IF NOT EXISTS Welcomes')
@client.command(name='welcomesetup')
async def welcomesetup(ctx,):
async with aiosqlite.connect(...) as database:
await database.execute(f"INSERT INTO Welcomes VALUES (?, ?, ?);", (ctx.message.guild.id, ctx.message.channel.id, ctx.message.content[:-6]))
await database.commit()
await database.close
await ctx.send(f"Your welcome channel has been set to `{ctx.message.channel}` with the message of: {ctx.message.content[:-6]}")
@hazy mango
hazy mango
cur.exute should be cur.execute
hazy mango
And your creation isn't valid either I don't think
You have to specify what columns and type of said columns should be in the table
As well as commit the creation of the table which you aren't currently doing
torn sphinx
how do i do that..
golden shadow
username__ne
@torn sphinx what?
torn sphinx
@hazy mango in my use case what kind of setup would i want?
regarding the database
filter(username__ne=username)
torn sphinx
Well...
Did you do this on queryset returned from all?
It's basically remove posts from that particular user.
torn sphinx
Can you reiterate your problem?
if anyone can answer why aiosqlite cant read the datebase but sqlite can
welcomes is a table.
it shows up on sqlite but not aiosqlite
golden shadow
Can you reiterate your problem?
@torn sphinx I want to grab all posts from posts table except posts of the current_user.username (current user)
nvm
grim lotus
Hello i want to make a todo application and can't figure out what Database scheme should i take
I thinked of
username TEXT
todo_title TEXT
todo_description TEXT
done BOOL
but i wanted to bruteforce/ pen test the following application with 1 million user
Each user can make 20 todos a day , with the following scheme i can definitely claim my Database is getting slow after 5-6 days which is 20 million Γ 5 = 100 million rows
I also thought of using List as the todo_description and title so that it will be 1 row a day
But due to online search and many pals over here suggesting me to not use list in db. I can't take my dicision
Can anyone help me in making a fast / reliable database table scheme , to make my task more efficient
torn sphinx
@torn sphinx I want to grab all posts from posts table except posts of the current_user.username (current user)
@golden shadow yeah
Ok
ne doesn't exist. Don't know from where did I pick up that.
Use exclude
all().exclude(username=***)
golden shadow
you mean like this @torn sphinx :
Posts.query.all().exclude(username=current_user.username) ?
because that just didn't work
harsh pulsar
@golden shadow in general if something "didn't work" you need to explain what exactly didn't work about it. was there an error? were the results not what you expected?
golden shadow
Oh jesus
Don't show passive aggression against me.
@torn sphinx Quote aggression parts on my reply
@golden shadow in general if something "didn't work" you need to explain what exactly didn't work about it. was there an error? were the results not what you expected?
@harsh pulsar I assume you didn't follow my question from beginning
harsh pulsar
@grim lotus i dont understand your question. the schema you described is fine, but it sounds like your application needs a rate limit or some other way to prevent someone from spamming the database. you might also need to periodically re-index the username field
dusky shore
I'm working on an asynchronous project and I'm using asyncpg (Asynchronous Postgres library), I know I'll need a lot of tables for it and I'm not sure how to scale it.
Right now I'm using functions to easily set and access these stored values and right now I'm just using functions like:
def get_default_role(guild_id):
pass
def set_default_role(guild_id):
pass
...
While these work great for smaller projects, it's getting really messy since I already have about 40 of these and I know that I'll be adding more.
I'd like to separate them into something more convenient. I was thinking to make 1 class per db table with its functions contained there, but I'm not at all sure how to do that. Initially, I wanted to simply use SQLAlchemy's ORM but that isn't supported for asyncpg and so I'm not sure how to proceed.
It isn't that important but here's my code (I removed most of these access function to keep it cleaner):
https://paste.pythondiscord.com/ebadudijuf.py
torn sphinx
if anyone can answer why aiosqlite cant read the datebase but sqlite can
@torn sphinx
torn sphinx
postgresSQL for windows
@velvet coyote what?
tropic kayak
and its postgreSQL not postgresSQL
velvet coyote
ye
@tropic kayak i went to the website and then I went to wndose section and then now I am stuck here:
velvet coyote
thx
nova marlin
welcome
velvet coyote
ye i did not see that thx
velvet coyote
wwhen I run postgresql
why does visual c++ opent up?????????????????????????
hey it asks this what do I do
blissful narwhal
that is the port your db will be running on, I believe the one you have in the box is the default for postgres
velvet coyote
ohh do i have to change that
blissful narwhal
you can, but you should probably leave it default
blissful narwhal
where what?
velvet coyote
oh it's the location i just set that to defaukt
now gotta learn that
and send a json dict to
sqlite3
blissful narwhal
kk, I have only used terminal, so this is fairly new to me as well xD
velvet coyote
xd
blissful narwhal
well, you are using postgreSQL, not sqlite
velvet coyote
well just learning the basics for sqlite
blissful narwhal
if you have a postgres db, you will need to use postgres. No need to use sqlite if you are trying to learn
velvet coyote
no but i need to send python stuff to db
and for that i need sqlite
i am learnign postgress basics for sqlite
blissful narwhal
???
velvet coyote
what
blissful narwhal
blissful narwhal
they are very similar, I am not sure experienced in DB honestly. But postgress basics for sqlite seems contradicting to me, hopefully someone else can elaborate though
velvet coyote
ohh ok
velvet coyote
hey guys why do I get this error:
import sqlite3
conn = sqlite3.connect('employee.db')
c = conn.cursor()
c.execute("""Create Table Employees (
first text,
last text,
pay integer,
) """)
conn.commit()
conn.close()
ERROR
Traceback (most recent call last):
File "c:/Users/user/Desktop/database/db.py", line 6, in <module>
c.execute("""Create Table Employees (
sqlite3.OperationalError: near ")": syntax error
@grim lotus i dont understand your question. the schema you described is fine, but it sounds like your application needs a rate limit or some other way to prevent someone from spamming the database. you might also need to periodically re-index the
usernamefield
@harsh pulsar but as i mentioned 30 million rows a day
In a week its like 200 million rows that means my database slow af
and what do you mean by username re- indexing
Answering from this question
dark dagger
@velvet coyote you have an extra comma at the last field
velvet coyote
i got it thx though
SO my json looks like this
[{"User": {"Abhigya": "Pokharel", "Sujan": "Shrestha", "Nishan": "Ghimire"}, "Anshu": "6ixer"}]
is there a way to make it look like this ?
[{
"User":
{
"Abhigya": "Pokharel",
"Sujan": "Shrestha",
"Nishan": "Ghimire"},
"Anshu": "6ixer"
}]
if there is pls ping me
pseudo cove
or find a json formatter lib
pseudo cove
idk
dark dagger
import json
_json = '[{"User": {"Abhigya": "Pokharel", "Sujan": "Shrestha", "Nishan": "Ghimire"}, "Anshu": "6ixer"}]'
parsed_json = json.loads(_json)
print(json.dumps(parsed_json,indent =3,sort_keys=False))
@velvet coyote
torn sphinx
Also i am getting error in this code:
statement = """INSERT INTO products (id, name, description, image,
qty, value, weight) VALUES (%s, %s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE id=%s, name=%s, description=%s, image=%s,
qty=%s, value=%s, weight=%s"""
cursor.executemany(statement, data_y)
db_connection.commit()
An error occured whilst uploading to the database: 1064 (42000): 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 '%s, name=%s, description=%s, image=%s, qty=%s, v...' at line 3
proven arrow
You may use the values function instead
So in the ON DUPLICATE part you would do ON DUPLICATE KEY UPDATE id=VALUES(id), name=VALUES(name), description=VALUES(description),....
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
@torn sphinx
What you have there would work, for a single row/execute but since you have bulk update query, then you can only have a single UPDATE clause in the bulk update statement.
scarlet geyser
What database to use? Atm i use mongodb for my discord bot it holds the token and everything.
proven arrow
Depends on the size of bot. Sqlite is good for small bots. If you want something that will be scalable or good for larger bots, then postgres is good. Whichever you use be sure to use the async version of the db driver. (aiosqlite for sqlite, and asyncpg for postgres).
torn sphinx
I'm currently using heroku for my discord.py bot, is there any good tutorial I could learn postgresql from?
I need to store some ids and additional 2 parameters for each.
vernal sun
how do i autoincrement in sql python ;-; aint working
proven arrow
@torn sphinx You can take a look at this short playlist. https://www.youtube.com/watch?v=xaWlS9HtWYw&list=PL-osiE80TeTsKOdPrKeSOp4rN3mza8VHN
Or more in depth at: https://www.youtube.com/watch?v=qw--VYLpxG4
@vernal sun You can declare the field type as INTEGER PRIMARY KEY for sqlite. Answered in their FAQ, https://www.sqlite.org/faq.html#q1
proven arrow
Yes so if you provide the type as INTEGER PRIMARY KEY it will auto increment for you
crude root
Making a Python game and wanna save some data in a file.. any clue how I could do that?
scarlet geyser
Get the variabels for the data and send the values to database?
vernal sun
use pickle?
harsh pulsar
@grim lotus for that kind of volume, you probably need separate tables (and possibly separate databases) for storing data vs doing analytics queries
this is getting into professional grade database admin and data engineering, that is a very big amount of data
and its probably outside the scope of what you will get substantive help for on a python chat room
velvet coyote
is channel suitable for json question
grim lotus
@harsh pulsar is there any advice you can give/ provide ?
harsh pulsar
you should look into indexes @grim lotus , since you seemed confused when i mentioned it
it's an important tool in database admin
its not going to help your write throughput though
but it could help make certain queries faster
grim lotus
Then I don't think so table splitting is what i can do right now
#databases message
As i mentioned table splitting is not a solution for me , then i have to lookup into multiple tables for a single row fetch
vernal sun
anyone know how to instantiate an async init ? i just want to make a connection during init inside a class..any clues?
hasty juniper
@commands.Cog.listener()
async def on_member_join(self,member):
async with aiosqlite.connect('data/Coins.sql') as db:
await db.execute("UPDATE Gem SET Member_Id = ?, Member_Cash = ?, Member_Hours = ?, Member_Messages = ?, Member_Roles = ?, Member_Play = ? WHERE Guild_ID = ? VALUES (?,?,?,?,?,?,?)", (member.id,0,0,0,0,True,member.guild.id))
``````cmd
sqlite3.OperationalError: near "VALUES": syntax error
dont u need to add "\" before values?
hasty juniper
o
vernal sun
OwO
hasty juniper
nwm
vernal sun
π
tepid cradle
@hasty juniper your syntax is incorrect
hasty juniper
ye i fix it ```py
await db.execute("UPDATE Gem SET Member_Id = ?, Member_Cash = ?, Member_Hours = ?, Member_Messages = ?, Member_Roles = ?, Member_Play = ? WHERE Guild_ID = ?", (member.id,0,0,0,0,True,member.guild.id))
yes, this is correct
for future reference, use lower case for column names as far as possible
hasty juniper
why?
@commands.Cog.listener()
async def on_member_remove(self, member):
async with aiosqlite.connect('data/Coins.sql') as db:
await db.execute("DELETE FROM Gem WHERE Member_Id = ? and Guild_ID = ? ", (member.id,member.guild.id,))
await db.commit()
``` and by the way, this removes all server members, although only member should@tepid cradle
I hope you understand
I kind of want to level the fields
tepid cradle
why?
@hasty juniper Just a generally followed convention in sql databases. Although, column names are case insensitive, so it doesn't really matter. It just makes typing queries easier, you don't have to keep track of the capitalisation of column names
harsh pulsar
@grim lotus writing to one table will always be than writing to several tables. its not clear to me whether your "lag" is related to query performance or write performance. they are different problems with different solutions
tepid cradle
and by the way, this removes all server members, although only member should
@hasty juniper I don't see why this would remove all rows when you've specified member_id and guild_id. Try doing a select query with the same parameters first, see what it returns. If it's as per expectation, then change it to a delete query
hasty juniper
lemme try
@commands.Cog.listener()
async def on_guild_join(self, guild):
async with aiosqlite.connect('data/Coins.sql') as db:
for member in guild.members:
await db.execute("INSERT INTO Gem (Guild_ID,Member_Id,Member_Cash,Member_Hours,Member_Messages,Member_Roles,Member_Play)", (guild.id,member.id,0,0,0,0,True))
await db.commit()
returning exception incomplete input
Traceback (most recent call last):
File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiosqlite\core.py", line 95, in run
result = function()
sqlite3.OperationalError: incomplete input
Ignoring exception in on_guild_join
Traceback (most recent call last):
File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python38-32\lib\site-packages\discord\client.py", line 312, in _run_event
await coro(*args, **kwargs)
, line 13, in on_guild_join
await db.execute("INSERT INTO Gem (Guild_ID,Member_Id,Member_Cash,Member_Hours,Member_Messages,Member_Roles,Member_Play)", (guild.id,member.id,0,0,0,0,True))
File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiosqlite\core.py", line 160, in execute
cursor = await self._execute(self._conn.execute, sql, parameters)
File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiosqlite\core.py", line 109, in _execute
return await future
File "C:\Users\NotToxic89\AppData\Local\Programs\Python\Python38-32\lib\site-packages\aiosqlite\core.py", line 95, in run
result = function()
sqlite3.OperationalError: incomplete input
why do you need this class
just use ```py
async with aiosqlite.connect('...') as db:
i need to use class, cause i need it inside my cogs (cogs = classes ) in discord py bot :/
grim lotus
My query is related to slow down of db / performance
answering to salt rock
I have problem for read from db
as database is big it will have a slow db issue
For reading
chrome dock
I started using PostgreSQL and created new user "dominik" but if I start pgadmin I'm logged as "postgres"
I want be logged as dominik
velvet coyote
do u consider json as a database
solar gale
json is just a file format
it's def not an relational database management system (RDBMS) like postgreSQL, MySQL, or SQLite
vernal sun
how do i pick everything of a specific row except one column,
like i know how to
"SELECT * FROM RPG WHERE id =?;",(id,)
but i dont want one column from the list..how do i remove?
hazy mango
What's the order of return values for a select statement in SQLite?py self.cur.execute("SELECT FormID FROM forms")It always returns the same thing but the order seems weird (FormID is numbers 1-9 and it always returns in the order 2, 3, 4, 5, 7, 6, 1, 9, 8)
Was wondering what the logic behind that specific sequence was
harsh pulsar
@vernal sun you can't remove a column from a select query, you can only list the columns that you want
@hazy mango in general i think of database tables as unordered. but usually i think its just returning in insertion order
hazy mango
That's weird because I did insert in 1-9 order
But yea I guess it's just being weird lol
harsh pulsar
!e ```python
import sqlite3
db = sqlite3.connect(":memory:")
with db:
db.execute("create table y (i integer primary key, k text)")
db.execute("insert into y values (1, 'a')")
db.execute("insert into y values (2, 'b')")
db.execute("insert into y values (3, 'c')")
db.execute("insert into y values (4, 'd')")
curs = db.execute("select * from y")
rows = curs.fetchall()
print(rows)
delicate fieldBOT
@harsh pulsar :white_check_mark: Your eval job has completed with return code 0.
[(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd')]
π€·ββοΈ
hazy mango
π€·ββοΈ
proven arrow
Did you delete any rows and then insert after?
Because the ROWID values can be reused.
minor ruin
salt rock lamp, it's however database engine returns
generally optimizing speed
if you want order, then issue "Sort by"
harsh pulsar
right, thats what i do
torn sphinx
Anybody with heroku postgres experience can help me set up 1 table to store and update some data after the command is executed?
proven arrow
What exactly do you need help with? What have you done so far?
torn sphinx
So i created a slot machine system yesterday with accounts and balance. I used csv files to store the data but heroku resets all those everyday and I decided to use a sql, which apparently I dont know how to, I just finished watching basics tutorial 30 minutes ago. I need to get data from the table and put it into these temporary .csv files and then when csv gets updated (balance mainly) i want to update the table aswell
proven arrow
You can just update data from your program to the postgres database, and then read it when you need. Is that right? or Do you need the csv?
torn sphinx
Well I need a table with 3 columns: member id, balance and max win. I need 1st column so if the member doenst have an account it automatically creates one row with member's id, balance of 500 and win of 0. Then when he spins the slot machine, the balance should change according to how much he won/lost.
If it is possible to get and update table info directly, it would be nice, I just dont know how to access those or create any.
proven arrow
Yes but first, have you managed to make a connection to the database through your code?
torn sphinx
No and thats what I need, I have never worked with databases before.
proven arrow
So you would need a python library that will allow you to connect/interact with the database first. Are you using this for a discord bot?
torn sphinx
Yes, as u said, I installed asyncpg
Now as I'm trying to connect I get the InvalidAuthorizationSpecificationError
proven arrow
How are you trying to connect?
You would need to import the asyncpg module, and then make the connection. To do so you can do something like:
import asyncpg
bot = commands.Bot(....) #assuming you already have something like this.
async def setup_db(bot):
# This function makes the db connection, and assigns it to the bot variable.
bot.my_db = await asyncpg.create_pool(user="username", password="pass", database="database name", host="host")
bot.loop.create_task(setup_db(bot))
In the example, above i assign the database connection to the bot instance. This is so its easier for you to get a reference to the connection in other parts of the code. Also i made a connection pool here. You can see other examples or read more of making the connection at: https://magicstack.github.io/asyncpg/current/api/index.html#connection
torn sphinx
I am just testing it aside from the bot
the basic code provided here
getting some error, telling that SSL is off
proven arrow
Whats the full error? Im guessing it may be a heroku thing
Ive never used heroku before
proven arrow
Try adding ssl=True to the connection details
torn sphinx
now it failed ssl certificate
But its ok I think once I upload it to the bot it will work, I'll stick with the local database now
Ok I managed to fetch some data.
proven arrow
The ssl may be a heroku thing, im not sure you may want to wait for someone who has knowledge of heroku.
torn sphinx
I think if i get it right to get info and update the database it will be fine
proven arrow
The next step would be to create your tables inside the db. You can do this through the code or directly through the DB
torn sphinx
await connection.execute(f"INSERT INTO data VALUES({ctx.author.id}, 500, 0)")```should I use like this to insert a new row?
proven arrow
Did you setup using the pool as showed you previously?
torn sphinx
I havent run it yet honestly, just getting rid of csv files first.
I can create it with a gui
much simplier, isnt it?
proven arrow
Umm, maybe to visualise it yes but you will need to run it through the code later on
torn sphinx
I can create it directly on the heroku website, is it still neesarry?
I tested it on other table and it seems to be working
test
proven arrow
Also dont use f strings for sql queries as it can be used for sql injection.
Instead you can use a parameterised query like the following:
async with connection.transaction():
await connection.execute("INSERT INTO data VALUES($1, $2, $3)", ctx.author.id, 500, 0)
Here the $1 references ctx.author.id, $2 references 500 and so on
If its a static value like 500, or 0 then you can directly insert into the values function for the query like you did before, but above is just an example, of how you can do with multiple params.
torn sphinx
Oh thank you, I used fstrings everywere actually π
echo urchin
Why doesn't this work python column "asd" does not exist LINE 1: UPDATE CONFIGS SET PREFIX = ASD WHERE GUILDID = 680152209450...
quaint tiger
Because column ASD does not exist
If you are trying to set it to the string ASD, you need to enclose it in quotes.
UPDATE CONFIGS SET PREFIX="ASD" WHERE GUILDID=...
@echo urchin
hidden osprey
glass gorge
im getting a ```ModuleNotFoundError: No module named 'MySQLdb'
when running my script
but i installed mysql onto my mac
i think i need to install it onto my ide version of python
torn sphinx
I'm completely new to the part with Databases. I want to set up a database for my Discord bot. How do I do this & what do i need.
glass gorge
I was having the same issue as this stack overflow question, and I implemented the first solution using pymysql
but now I'm getting a ```sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1130, "ool-457bf9ba.dyn.optonline.net' is not allowed to connect to this MySQL server")
(Background on this error at: http://sqlalche.me/e/13/e3q8)
I changed my URI to something similar in the stackoverflow post
eternal umbra
Could anyone help me with this ? For each row in table 1 need to use certain column values from table 1 to pass to a query for table 2. How would such an sql statement look like ?
shell ocean
Could anyone help me with this ? For each row in table 1 need to use certain column values from table 1 to pass to a query for table 2. How would such an sql statement look like ?
@eternal umbra ...what do you mean?
give an example
eternal umbra
@shell ocean
Consider for customerid 1, need to query all matching rows in product table for product type between min and max budget
torn sphinx
Are you familiar with data analysis?
hazy mango
SELECT * FROM Forms WHERE FormID in (SELECT FormID FROM (SELECT FormID, COUNT(*) c FROM Students GROUP BY FormID HAVING c > 24)) ORDER BY FormID
```This statement will output all forms with > 24 students in it. It outputs the information in the image below.
How can I make it so that it also outputs the student count (c)? I've tried a few things but always get an error along the lines of `No such column: c` or `sub-select returns 2 columns β expected 1`.how would i prevent a sql attack
so users cant do something extremely stupid like list tables lMAO
tired sparrow
proven arrow
@torn sphinx You can use parameterised query, which uses a placeholder for the data you want to provide the query. Each placeholder will have a value that is provided as a parameter to that query.
For example,
("SELECT * FROM table WHERE column = %s AND other_column = %s", (param1, param2))
This would prevent sql injection, however you should still consider sanitizing/verifying user input.
wintry stream
Hey there, I use a postgres database for a discord bot using discord.py. As some of you might know, discord.py is async. I currently use psycopg2 as I found that was the most popular postgres library for python. But psycopg2 is not async and thus results in some slowdowns when searching for much data. This happens often enough for me that i'm willing to transition over to an async library, but i am unsure which one to use as there are multiple around
the 2 most popular i found were asyncpg and aoipg, would you recommend one of these or something completely different?
please @ me when you have an answer
glass gorge
hello
i am working on a dashboard project
using mysql
currently im getting a sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1046, 'No database selected') [SQL: DESCRIBE `users`]
my run.py file contains
db.create_all()
user1 = User(first_name='test')
db.session.add(user1)
db.session.commit()
app.run()```the db URI connects to a remote server
shouldn't my script automatically create the db?
db.create_all()
brazen charm
@wintry stream asyncpg is hands down the best postgre lib for python overall not just the best async liv
It's the most perfomant, maintained, developed and consistent lib
wintry stream
okay thank you. I'll look into migrating over
glass gorge
@brazen charm do you know off the top of your head; why do i need to specify a db name in my URI if I am creating it in my run file?
should I jsut not create it in my run file I suppose?
and have it built already
brazen charm
@glass gorge probably because it's coded to support multiple dbs at once ig
glass gorge
that makes sense
whats confusing is that I need to specify one, otherwise it can't create it through mysqlalchemy. Maybe it can and I just don't know how.
fair crescent
Can someone help with this table entry, I have a table with a foreign key reference but I keep getting an identifier error for the first foreign key SQL create table Photos ( photo_id varchar2(255), photo_title varchar2(255), photo_desc varchar2(255), upload_date date, view_num number, team_id varchar2(255), primary key(photo_id), foreign key(Mem_ID) references Mem(Mem_ID), foreign key(Album_ID) references Album(Album_ID), foreign key(Comment_ID) references Comments(Comment_ID), foreign key(Location_ID) references Location(Location_ID) );
create table Mem
(
Mem_ID varchar2(255),
Mem_Name varchar2(255),
Mem_Phone number,
Mem_Email varchar2(255),
primary key (Mem_ID)
);```You need to reference the foreign key columns inside the photos table
For example, you do foreign key(Mem_ID) references Mem(Mem_ID), and so that would mean you need a column inside the Photos table named Mem_ID. This is the column you want the Foreign Key to be in. @fair crescent
fair crescent
Okay, and does the column need to named the same as the foreign key? Can I use the reference to get other columns from another table?
proven arrow
Well when referencing you need to reference by the name of that column, but it doesnt need to be the same name as the PK of the other table you are referencing.
fair crescent
I'm kind of confused, so if I want to use the Mem_ID column in "Photos" I need to make a Mem_ID column in it and reference the Mem_ID in the table "Mem"?
proven arrow
You would reference it in the table where you want the Foreign Key. So in your Photos table.
What you have above is almost correct, except that you are telling it to create a FK reference on a column that doesnt exist. So you would need to create those columns in your Photos table so the FK can be stored in it.
proven arrow
CREATE TABLE Photos(
photo_id int NOT NULL,
PRIMARY KEY (photo_id)
);
CREATE TABLE Users(
user_id int NOT NULL,
photo_id int NOT NULL,
PRIMARY KEY (user_id),
FOREIGN KEY (photo_id) REFERENCES Photos(photo_id)
);
Consider that example. The FK is inside the Users table.
fair crescent
Could I also use a different column apart from the primary key in a table? For example ```SQL
CREATE TABLE Photos(
photo_id int NOT NULL,
test varchar2(255),
PRIMARY KEY (photo_id)
);
CREATE TABLE Users(
user_id int NOT NULL,
photo_id int NOT NULL,
PRIMARY KEY (user_id),
FOREIGN KEY (photo_id) REFERENCES Photos(photo_id)
);``` Could I get "test" to be used in the Users table?
would I just replace it with FOREIGN KEY (photo_id) REFERENCES Photos(test)
proven arrow
Test would need to have a unique constraint
surreal thorn
I have a question. I need tens of thousands of user profiles. Each profile will probably have a name, email, and a profile picture. Which database solution (MySQL, PostreSQL, NoSQL) will be best for me? And what is the best way to store these profile images after compressing them
proven arrow
@fair crescent You can use the UNIQUE constraint. However remember this would mean that each value in this column in your table would have to be unique.
grim lotus
Is there anything like postresql sharding to make the db fast
glass gorge
i accidentaly selected the same db twice
so it's example.example
how do I undo that
sorry i used the same db twice
torn sphinx
fair crescent
Can someone help me with a one small issue
select Mem.Mem_Name photos.photo_id, photos.photo_title, photos.photo_desc,photos.privacy,photos.upload_date, photos.view_num,photos.img_path,photos.Mem_ID,photos.Album_ID,photos.Location_ID
FROM Mem
JOIN photos
ON Mem.Mem_ID = photos.Mem_ID
WHERE Mem.Mem_Name LIKE '%Name Here%';```
I get a error saying FROM is not where expected
I can't see the issue@fair crescent you're missing a comma after Mem.Mem_Name
obsidian vapor
harsh pulsar
hah
fair crescent
Thank you both
harsh pulsar
good formatting is important for readability which is important for catching errors like this
unreal topaz
is there any way to check if a table is partitioned in athena or awswrangler help xd
with pythjon
gentle plaza
if I have a JSON with entries that go from 1 to 8 (and 8 is inside a value), how do I make my script get all the values from 1 to 8 without making a huge mountain of if else blocks?
unreal topaz
any amazon athena expert would like to help me :'v}
shell ocean
if I have a JSON with entries that go from 1 to 8 (and 8 is inside a value), how do I make my script get all the values from 1 to 8 without making a huge mountain of if else blocks?
@gentle plaza example?
gentle plaza
I figured out the answer by rephrasing what I needed to know inside #python-discussion, sorry
vernal sun
Does big discord bots like mee6, dankmemer use a SQL or nosql db? To store uswr data
vernal sun
oh right..so nosql π ty..
white ridge
Anyone here well versed with elasticsearch?
charred zephyr
How do you implement a database/postgres in a larger project? Where do you place it in the project structure?
I was thinking about writing some kind of class with a connect/commit/close functionality and the different methods to insert/update/delete. Then call those methods somewhere in the application
glad bobcat
SQLAlchemy is likely the way to go @charred zephyr
I usually have an SQLAlchemy-based ORM package in my projects that require a database
PostreSQL question, could use an outside opinion.
I have two tables account and player which both have a uuid linked through a foreign key. For multiple reasons, I would like to use a surrogate key.
We agree that once itβs created as SERIAL in account, the right way to propagate it is:
UPDATE player
SET account_id = account.id
FROM account
WHERE player.puuid = account.puuid;
This looks right to me, but the query has been running for ten minutes despite operating on an index (puuid has a unique constraint in account). Also, for some reason, EXPLAIN tells me this is not using the index...
fluid tusk
how do i reset table id after deleting from it
like
content | id
---------+----
cn | 1
fn | 2
after deleting the first row the rowid still 2
content | id
---------+----
fn | 2
Isnt ID your primary key?
fluid tusk
it is
proven arrow
Also what is the purpose of such a thing? That would seem like a very expensive operation especially if you have many rows, and then that would mean you would have to shift the ids of each row down.
high geyser
c.execute("BEGIN TRANSACTION")
for nsfw_word in args:
if not check_for_duplicate(nsfw_word, 0):
interfered_word_list.append(f"||{nsfw_word}||")
try:
c.execute("""INSERT INTO nsfw_word_lookup (guildid, word, type)
VALUES (?,?,?)""", (ctx.guild.id, nsfw_word, 0))
except Exception:
c.execute("""UPDATE nsfw_word_lookup
SET type = ?
WHERE guildid= ? AND word = ?""",
(0, ctx.guild.id, nsfw_word))
c.execute("COMMIT")``` is this good practice I am doing this because I want to add a list of words given by the user from the table. Even if one word isnt added due to constraints, the whole process will stop. Moreover, I also need to find out which words failed to be added to the table. So, can I loop through deletion after beginning transaction?
steady narwhal
I'm using django models/ORM to process some data. I have two models that have similar, but different information. I'm trying to join them on certain columns matching... [i.e. For ClassAObj.date == ClassBObj.date and ClassAObj.age == ClassBObj.age]... i've done this sort of thing with pandas where i merge/join them on certain columns matching, but im wondering if there was a way to do this easily via shell_plus/django ORM
high geyser
c.execute("BEGIN TRANSACTION")
for nsfw_word in args:
try:
c.execute("""DELETE FROM nsfw_word_lookup
WHERE word = ? AND guildid=?""", (nsfw_word,ctx.guild.id))
except Exception as err:
print(err)
else:
interfered_word_list.append(f"||{nsfw_word}||")
c.execute("COMMIT")```sqlite3 this doesnt delete a thing from the table and no error occurswhy? pls help
tepid cradle
Why don't you use something like
Delete from table where word in (...)
This way you can delete all words in a single query
torn sphinx
Hope itβs not a problem for a newbie to just come in here and just post a small question... π§.
Iβm trying to do a team vs team win/loss stat logging and canβt think of how to utilize a database for it. I need to be able to get Team A, B, Cβs win/loss when theyβre playing A, B, or C. Surely making a grid-like database structure is wrong, and I canβt have a thousand rows and a thousand columns? How would you do it?
I already have career stats, but I need the team vs team breakdown
grim lotus
Are INDEXES in-memory or stored in disk ?
do i have to re - create indexes after each boot ?
jovial yew
@torn sphinx normalise the data and store into seperate tables. This is simple example
team > stores team info
matches > team wise match info
match_vs > has info about who played with whom
@grim lotus that depends on the database provider, it is usually stored in disk and you have an option to have it in memory in most popular databases.
It doesn't needs to be recreated on boot, but you might have to re index based on how much of the data has changed in the table to improve performance. Db experts say, you have to re index after modifying 10% of the data volume on the indexed column.
torn sphinx
@jovial yew thanks for the response! I had not thought of anything to do with multiple tables, but for match_vs I still donβt know what the column structure should look. Like
Team A | Team B?
Iβd still need to plug in wins/losses
keen gorge
so, im currently helping a friend out with his server, but when we trying to use mysql this error occurs
File "/usr/local/lib/python3.8/dist-packages/mysql/connector/connection_cext.py", line 216, in _open_connection
self._cmysql.connect(**cnx_kwargs)
_mysql_connector.MySQLInterfaceError: Host 'vmd61223.contaboserver.net' is not allowed to connect to this MySQL server
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "main.py", line 36, in <module>
sql.sqlsetup()
File "/home/ruby/sql.py", line 15, in sqlsetup
temp=c()
File "/home/ruby/sql.py", line 3, in c
myuser=mysql.connector.connect(
File "/usr/local/lib/python3.8/dist-packages/mysql/connector/__init__.py", line 264, in connect
return CMySQLConnection(*args, **kwargs)
File "/usr/local/lib/python3.8/dist-packages/mysql/connector/connection_cext.py", line 80, in __init__
self.connect(**kwargs)
File "/usr/local/lib/python3.8/dist-packages/mysql/connector/abstracts.py", line 966, in connect
self._open_connection()
File "/usr/local/lib/python3.8/dist-packages/mysql/connector/connection_cext.py", line 218, in _open_connection
raise errors.get_mysql_exception(msg=exc.msg, errno=exc.errno,
mysql.connector.errors.DatabaseError: 1130 (HY000): Host 'vmd61223.contaboserver.net' is not allowed to connect to this MySQL server```
he wanna use this server for his discord bot
what could cause this?
when I setted up my server i didnt got this error@jovial yew i use psql , is there a way to index on every new row/every insert ?
Or should i re - index on each and every row ?
jovial yew
@jovial yew thanks for the response! I had not thought of anything to do with multiple tables, but for match_vs I still donβt know what the column structure should look. Like
Team A | Team B?
@torn sphinx try something like this, i know the naming is butchered
joining all the tables will give you win/loss vs teams
torn sphinx
Hmm, thank you for the effort. Iβll have to see if I can put this together, I think I get it tho
jovial yew
@jovial yew i use psql , is there a way to index on every new row/every insert ?
Or should i re - index on each and every row ?
@grim lotus by psql you mean postgres right?
infact it does kinda gets indexed everytime you insert a new row, think of an index as another table which only has indexed column data along with some metadata, every record in the main table will have a corresponding record in this index table and it keeps updating everytime you modify the original table, so don't think about it too much
reindexing is done to remove fragmentaions, which is a whole another topic which i'm not very competent in π
torn sphinx
@client.command(name='welcomesetup', brief='sets the current channel as welcome, provide the message after')
@commands.has_permissions(administrator=True)
async def welcomesetup(ctx, *, welcome_msg: str):
async with aiosqlite.connect(database=(r"C:\Users\\Videos\bot\database.db")) as database:
await database.execute("INSERT INTO Welcomes VALUES (?, ?, ?);", (ctx.guild.id, ctx.channel.id, welcome_msg))
database.commit
await ctx.send( f"Your welcome channel has been set to `{ctx.channel.mention}` with the message of: {welcome_msg}")
this works but im trying to make it so if the guild id is already present in my database, it edits the channelid and message in that row matching the guildid rather than making a new entry
proven arrow
@torn sphinx You can use INSERT OR REPLACE
But for this you would need a unique constraint. I guess you can make the guild ID unique if each guild can only have one entry.
INSERT OR REPLACE would insert if the row does not exist, or replace the values if it does.
torn sphinx
ive basically made it so it ignores the guildid and channel id if they match
cur.execute('CREATE TABLE IF NOT EXISTS welcum(guildid INTEGER UNIQUE ON CONFLICT IGNORE, channelid TEXT UNIQUE ON CONFLICT REPLACE, messagecont TEXT UNIQUE ON CONFLICT IGNORE')
sqlite3.OperationalError: near "IGNORE": syntax error
``` ?def create_table() :
cur.execute('CREATE TABLE IF NOT EXISTS welcum(guildid INTEGER UNIQUE ON CONFLICT IGNORE,'
'channelid TEXT UNIQUE ON CONFLICT REPLACE,'
'messagecont TEXT UNIQUE ON CONFLICT REPLACE');
database.commit()
database.close()
upbeat cypress
Anyone good with flask sqlalchemy please i need help so
naive pasture
I've used it before, ask your question @upbeat cypress
delicate fieldBOT
Hey @upbeat cypress!
upbeat cypress
sqlalchemy.exc.OperationalError
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: students.addr
[SQL: SELECT students.student_id AS students_student_id, students.name AS students_name, students.city AS students_city, students.addr AS students_addr, students.pin AS students_pin
FROM students]
(Background on this error at: http://sqlalche.me/e/13/e3q8)
Getting this error bro @naive pasture
I don't know where i am doing it wrong
delicate fieldBOT
Hey @worn flint!
worn flint
Am I using this correctly?
user = user_name_ent.get()
password = user_pass_ent.get()
ok_button = tk.Button(master=confirm_buttons_frm, text="OK",
command=lambda: create_user(user, password))
The create user function work independently, but the parameters are passed to the DB.
steady narwhal
I have a bunch of objects in a database, under 2 separate classes.
The classes have similar information, but one of their attributes differs from one another (both classes contain important information)
I'm trying to create a third class of objects that will have ALL attributes and match the objects of the two classes together.... (think merge/join objects on a category)...
what is the best way to do this in POSTGRES/django orm?
example:
Class 1:
{'_state': <django.db.models.base.ModelState at 0x7efce8b6f6a0>,
'id': 1,
'away_team': 'Philadelphia',
'home_team': 'Boston',
'away_game_spread': '211',
'home_game_spread': '211',
'away_game_odds': '1.95',
'home_game_odds': '1.95',
'periods': {'1': {'away': 21, 'home': 21},
'2': {'away': 21, 'home': 26},
'3': {'away': 24, 'home': 30},
'4': {'away': 21, 'home': 28}},
'date': 'Oct-16-2018'}
Class 2:
{'_state': <django.db.models.base.ModelState at 0x7efce8b6d8b0>,
'id': 1,
'away_team': 'Philadelphia',
'home_team': 'Boston',
'away_game_spread': '53.5',
'home_game_spread': '53.5',
'away_game_odds': '1.86',
'home_game_odds': '2.03',
'periods': {'1': {'away': 21, 'home': 21},
'2': {'away': 21, 'home': 26},
'3': {'away': 24, 'home': 30},
'4': {'away': 21, 'home': 28}},
'date': 'Oct-16-2018'}
These objects are similar, but different, and i want to create a third object/class that will consolidate the info of both objects
glass gorge
should i be hashing the passwords for my seed first and then putting it on the db
or can i put it on the db first and then hash
west furnace
for your seed?
glass gorge
my initial load up onto the db
brave wave
MongoDB is good over JSONs?
west furnace
We can't tell you
It depends on the scope of the project
brave wave
Well, the project is big and it'll be applied on a Verified Bot.
west furnace
hmm.... well, if you need to access the data from multiple places (processes/servers) or if the data is too large to fit into your ram, than yeah, you'd probably have to switch
but it will add complexity to the code and slow down the bot
torn sphinx
Trying to learn sqlite3, will this work like I'm expecting?```py
self.cursor.execute("""CREATE TABLE IF NOT EXISTS
message_map(original INTEGER PRIMARY KEY, message INTEGER, log INTEGER)""")
message, log = self.cursor.execute("""SELECT * FROM message_map WHERE original=?""", (payload.message_id,))```
vestal vine
if I don't feel like running conn.commit() every time after running a command with sqlite, could I instead just run it before the program exited?
celest yacht
in sqlite3, why is
c.execute("SELECT ? FROM table WHERE value=?", (var1, var2))
different than
c.execute(f"SELECT {var1} FROM table WHERE value=?", (var2,))
the first statement returns me var1, the second does the proper select
pure cypress
Because the ? substitution doesn't work for column names
Among other things
String formatting is the only way to use dynamic column names in your query
celest yacht
ah, someone mentioned earlier string formatting leads to injection attacks or whatever though
i assume there isn't any way around that though?
pure cypress
Yeah, it certainly can. But that doesn't always apply.
If your column names are coming from an internal source, like a constant list of names, then you don't have to worry since you can assume the list that you made yourself is safe.
But if you're letting a use input an arbitrary name then that's problematic. You have to sanitise the input somehow e.g. check if it's a valid column name.
celest yacht
okay, ty
pure cypress
In this specific case, the other way to avoid is to select * and then pick the index for the column you want
high geyser
Why don't you use something like
Delete from table where word in (...)This way you can delete all words in a single query
@tepid cradle what if some words in the list cannot be deleted due to contraints? will the other words be deleted if some error like that occurs too?
and also bro I want to add to the list which words are deleted
any ways to do that? I cant do that within the execution statement right
pls ping me when help
rich fulcrum
Question:
I'm making a small game where i store various info about many players in a database. Is there any reason to create a Player class for every player? Or does the db make the class obsolete? And rather just run various function that manipulate the entries in db
charred zephyr
I'm looking for help regarding SQLAlchemy
How do I create this relationship with SQLAlchemy?
plain island
I want to save some programme data in a database. Do somebody know how I can do this?
quaint tiger
Or does the db make the class obsolete?
@rich fulcrum It most certainly does not. The role of a class is to decouple your data from your logic. The class shouldn't care where your data comes from (e.g. which DB), and would allow you to refactor / modify your project easier.
In fact, if you look at ORMs, they wrap databases into classes.
rich fulcrum
But isn't a instance variable of something that's already in the database an unneccesary duplicate?
quaint tiger
Your logic should still live inside class methods.
Then you get cool things like polymorphism, etc.
Assuming you have different classes.
Anyway, you don't have to use OOP, but I would.
rich fulcrum
Because what i'm doing atm is whenever something specific triggers i run the function "database_player_update_health(id, amount):"
This is a discord bot, so it might as well be an sql game
I guess π€
vague haven
do i need to have something like db.open() if i use db.close()?
high geyser
yes or else u cant access db info @vague haven
vague haven
oh crap
high geyser
why do you want to close it in the first place?
grim lotus
@grim lotus by psql you mean postgres right?
infact it does kinda gets indexed everytime you insert a new row, think of an index as another table which only has indexed column data along with some metadata, every record in the main table will have a corresponding record in this index table and it keeps updating everytime you modify the original table, so don't think about it too much
reindexing is done to remove fragmentaions, which is a whole another topic which i'm not very competent in π
@jovial yew i m not talking aboutPRIMARY KEYANDUNIQUEKEY indexing ,
I m talking about the indexes that are made my
CREATE INDEX ON table (cloumn)
spring gull
I know this is a python server but has anyone worked with making their own mysql or mariadb database and know why
ADD CONSTANT chk_date_added CHECK (date_added !< GETDATE());```
isnt working. It throws error number 1064 which just say syntax error
nocturne basin
Hey all! Anyone on has spent a lot of time using Psycopg2? I have a question about executemany vs execute_batch
Which one should we use for performance enhancement?
It's for a select statement
brazen charm
executemany is always slower
even specified in their docs that execute many is slower than just using a for loop
nocturne basin
What really?!
for stock in symbols:
stock_info[stock] = get_dict_resultset("SELECT
date, close
FROM security_price
WHERE
security_price.id=%s;", [stock])
So I should just leave this as it?
get_dict_resultset() connects to my dB, pulls data, and stores it into a python dictionary.
I would rather not have to keep connecting to the dB in a loop
Wouldn't it just be better to use executemany ?
jovial yew
@jovial yew i m not talking about
PRIMARY KEYANDUNIQUEKEY indexing ,
I m talking about the indexes that are made my
CREATE INDEX ON table (cloumn)
@grim lotus that shouldn't be a problem
torn sphinx
hello there
can i get help downloading mySQL for my pc and connecting it with python
await database.execute("INSERT INTO testwelcome VALUES (?, ?, ?);", (ctx.guild.id, ctx.channel.id, welcome_msg))
doesnt write to my table.
aiosqlite is the libary, and the table is just a ordinary setup with no other parameters other than the basic ones
jovial yew
@torn sphinx do you get some error?
torn sphinx
nope
jovial yew
So you don't see the data in the table??
I've not used aiosqlite, but are you committing the transaction?
warm glade
what's the best way to connect in a database (mariadb)?
scarlet schooner
what's the best way to connect in a database (mariadb)?
@warm glade I suggest you SQL Alchemy if you want either high level object ORM or just basic queries. The wiki is nice too
rich fulcrum
Why does this give me an "sqlite3.OperationalError: near ":entry": syntax error" ?
scarlet schooner
Never tried like this, but replace your string with format option
rich fulcrum
But it works if i remove the :entry
scarlet schooner
ex : " UPDATE table SET {} = {}".format(entry, value)
rich fulcrum
I heard using .format is bad for sql injections?
scarlet schooner
Well, like every word replacement with content made by users, you can do sql injection
rich fulcrum
true
scarlet schooner
If you have a blog and write a small sql injection, you could crash the thing if the backend is not made safe
rich fulcrum
I will try it your way
scarlet schooner
I did used that in a function to sanitize the query and I didn't have issues yet
warm glade
@scarlet schooner I'll see, thank you!
scarlet schooner
@scarlet schooner I'll see, thank you!
@warm glade https://stackoverflow.com/questions/54834088/python-database-connection-for-mariadb-using-sqlalchemy
celest yacht
do sqlite3 SELECT statements need to be conn.commit()'ed before doing other things? i'm thinking no because it's just a read
brazen charm
SELECT statements dont modify data so it does not need to be saved before hand
only if you modify data
e.g
UPDATE, INSTERT etc...
celest yacht
ok ty
earnest parcel
HINT: Check the query against the passed list of arguments. Note that parameters are supported only in SELECT, INSERT, UPDATE, DELETE, and VALUES statements, and will *not* work in statements like CREATE VIEW or DECLARE CURSOR. what should I use then... an fstring?
glass gorge
if i have a remote server with my db on it
and a local fiile that i want to upload as the seed
do i need to scp the content onto the server? or how would I upload it to the remote db?
nocturne dock
Is there a way to speed up upserts to PSQL? I am getting an estimated 7 hours to load a 400MB parquet file (20 million rows)
gray surge
So I decided to use sqlite3 for a password authentication thing, so how do I insert hashed passwords into the database?
clever nimbus
Hi everyone. Does anyone use turbodbc? I can't for the life of me get it installed on a windows 10 machine.
glass gorge
So I decided to use sqlite3 for a password authentication thing, so how do I insert hashed passwords into the database?
@gray surge im working on this same issue right now, i would look at flask-bcrypt
clever nimbus
@gray surge im working on this same issue right now, i would look at flask-bcrypt
@glass gorge a text field would work no or am I missing something?
tepid cradle
You can use a varchar field. You just have to decode it using utf-8 before storing
password_for_db = hashed_password.decode('utf-8')
Something like this
misty carbon
hi guys i want to create a new database file (sqlite3) for my project and couldn't find any good answers online could anyone help me with that?
quaint tiger
hi guys i want to create a new database file (sqlite3) for my project and couldn't find any good answers online could anyone help me with that?
@misty carbon Ask a more precise question... Or just runsqlite3 foo.db.
sleek quarry
is there anyone here familiar with sqlalchemy?
having some issues with association tables
sleek quarry
I just made an SO post about this right now, so instead of writing everything out again here ill link my SO article for anyone interested in the issue
TL;Dr im getting an error with association tables
That should contain the necessary parts of the code for this issue
vast relic
I'm extremely new to SQL and even actively learning it as we speak. For my discord bot, I am trying to update two values. Every guild should have two columns: prefix and GuildID. You should be able to get a guild's prefix by referencing the guildId and getting it from there.
db.execute(
'''
UPDATE guilds
SET Prefix = prefix
WHERE GuildID = ctx.guild.id
''')
print('table updating')
guild = ctx.guild.id
g = db.execute('''
SELECT Prefix
FROM guilds''')
print(g)
Right now, that is my current code in my command. My SQL table looks like this:
CREATE TABLE IF NOT EXISTS guilds (
GuildID bigint PRIMARY KEY,
Prefix text DEFAULT "sb!"
);
Do you know why I end up getting an sqlite3.OperationalError: no such column: ctx.guild.id?
Any help is appreciated.
also when i try referencing g, i get none
coarse patrol
If I have a python list of 512 floats, do I need to do anything special to add it as a single column entry in a row?
vast relic
im assuming since i havent actually made any entries its basically updating nothin
potent sparrow
How would I use the database from a Django project, to be analyzed using Pandas/Numpy? Itβs a .py file
lament basin
what is a Python file? (not the database used by the django project for sure)
ember marsh
I've got a tricky one
twilit oyster
How do I check if a variable is in a collection with pymongo
ember marsh
I'm trying to sync the state of a collection of YAML files with a DB
Using a sort of cloudformation inspired syntax
Is this dumb
wispy mason
can someone help me with a bot database that i am using I need someone to help me generate akamai sensor data
glass gorge
i keep getting a ERROR 1366 (HY000): Incorrect integer value: ' ' for column 'id' at row 1
every solution ive tried on google has not really worked
proven arrow
@glass gorge What is your query like?
You are probably entering invalid type when its expecting integer
quaint tiger
i keep getting a
ERROR 1366 (HY000): Incorrect integer value: ' ' for column 'id' at row 1
@glass gorge sounds like you're passing an empty string and not an integer
glass gorge
so i am actually trying to load a csv
into the db
load data infile '/var/lib/mysql-files/users.csv' into table users fields terminated by ',' enclosed by '"' lines terminated by '\r' ignore 1 rows;
the id column, is set to autoincrement
most of the solutions online for this error are for scenarios where you add one row at a time to the db
using insert into or something similar (forgot the call)
they usually involve setting the values of the column to NULL, or 0
I've tried to do that, and even removed the column. And I keep getting the same error
I've tried to populate the column as well with integers
but it's the same error
I would greatly appreciate any help, I've hit a wall
proven arrow
Isnt it supposed to be ignore 1 lines ?
And id say you may want to manually specify the columns, and then set the ID field to null
glass gorge
you might be right on the 2nd point
i guess i have to specify the columns names and not just col1,col2 etc
proven arrow
Yeah
glass gorge
ok made progress
but now...
The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
I hate life
lmao
it shouldn't be doing this
because the file is on the actual server
it's a local file
like I had this problem before
quaint tiger
load data local infile I think
proven arrow
Which is the directory you have set for loading such files?
proven arrow
I know there is something you can execute to check this directory if it has been set cant quite remember what it is
glass gorge
/var/lib/mysql-files/
proven arrow
Ok so if the csv file is in that folder then it should be good to load from there
glass gorge
one moment
uhhhh
wtf
ERROR 2 (HY000): File 'var/lib/mysql-files/users.csv' not found (Errcode: 2 - No such file or directory)
but it's there
Which is the directory you have set for loading such files?
@proven arrow i didnt set any directory
twilit oyster
proven arrow
As an alternative option you could try setting the value for secure priv to null or just removing it from the config file @glass gorge
The server would need restarting for these changes to take effect though
In the config file youll see secure-file-priv = "your_path", which you can remove.
vocal moon
async def create_db_pool():
global pg_con
try:
pg_con = await asyncpg.create_pool(host='192.168.1.156', database="ChatParty", user="postgres", password='f')
except asyncpg.exceptions.InvalidAuthorizationSpecificationError:
pg_con = await asyncpg.create_pool(host='localhost', database="ChatParty", user="postgres", password='f')
print('[DB] Connection Established.')
@app.route('/login', methods=['GET', 'POST'])
@async_action
async def login():
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
get_username = await pg_con.fetch(f"SELECT * FROM Accounts WHERE username='{username}'")
print(f'data: {get_username}')
return render_template('login.html', PageName='Login')
if __name__ == '__main__':
## -- ASYNCIO -- #
loop = asyncio.get_event_loop()
loop.run_until_complete(create_db_pool())
## -- FLASK -- #
app.run(debug=True)
asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress
glass gorge
@proven arrow it's the my.cnf file that i need to edit?
yeah idk whats going on, secure-file-priv is not actually in my my.cnf file
proven arrow
Yeah something like that. If its not there then it wont be set. What happens if you enter SHOW VARIABLES LIKE "secure_file_priv"; into your db console?
glass gorge
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)```I tried to add the line in the cnf file to specify the path to where files should be loaded
and doing so resulted in my server not running
even after restart
maybe im in the wrong user or something
proven arrow
The file may be different for the OS, what platform are you on?
For an issue like this you may get better answers online
glass gorge
ubuntu droplet
vocal moon
can someone help me?
proven arrow
If your on ubuntu you can try editing the file at /etc/mysql/mysql.conf.d/mysqld.cnf If the secure_file_priv doesn't exist then you can specify one or set it to empty string or null @glass gorge
And dont forget to restart the server after
frozen hinge
glass gorge
adkjhasdkjash
ERROR 2 (HY000): File 'var/lib/mysql-file/users.csv' not found (Errcode: 2 - No such file or directory)
quaint tiger
@glass gorge do you have a leading slash? /var/ and not var/
vocal moon
can someone help me
potent sparrow
I have a Apples Numbers Sheet of Stock Tickers ..... can I create a graph that will plot the tickers day by day..... but also the stock price???
glass gorge
you can do whatever your heart desires with computers
minor zealot
how to connect my database to a host or i want to make my pc as a small server to recive data from other users that use my app
glass gorge
@quaint tiger that was the issue -__-
clever nimbus
hey everyone, any one deal with connection issues -> [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: An existing connection was forcibly closed by the remote host. ?
torn sphinx
can i use sqlite3 for my bot(discord.py) in many servers?
torn sphinx
you can, yes
ember marsh
sqlite can handle a lot of traffic you'll be fine
as long as you have a single writer
velvet coyote
what is a single writer
vast relic
g = db.field("SELECT enabled FROM logs WHERE GuildID = ?", ctx.guild.id)
if g is None:
db.execute('''INSERT INTO logs (enabled, GuildId, msgLogs)
VALUES (?, ?, ?)''', True, ctx.guild.id, 1)
await ctx.send("Starting setup for log channels.")
try:
logCategory = await ctx.guild.create_category(name="Logs", reason="Server manager requested setup.")
messageLogs = await ctx.guild.create_text_channel(name="message-logs", category=logCategory)
db.execute("UPDATE logs SET msgLogs = ? WHERE GuildId = ?", messageLogs.id, ctx.guild.id)
when trying to detect if a discord server already has a guild, it checks successfully and returns none
if it is none, i make a entry with the columns enabled, guildId and msgLogs
when i run the code, i get an error saying tabe logs doesnt have column msgLogs though
CREATE TABLE IF NOT EXISTS logs (
GuildID BIGINT PRIMARY KEY,
logCategory BIGINT,
msgLogs BIGINT,
enabled BOOLEAN DEFAULT FALSE
);
tag me if you get a solution pls
high geyser
pls help me
c.execute("""SELECT keyword,
tag_use_count,
COUNT(*) OVER() AS "Count",
SUM(tag_use_count) OVER () AS "Uses"
FROM tags
WHERE guildid=? AND authorid = ?
ORDER BY
tag_use_count DESC
LIMIT 3""",(guildid,member.id))
results = c.fetchall()
emoji = 129351 # ord(':first_place:')
tag_string = ""
if results:
owned = results[0]['Count']
uses = results[0]['Uses']``` I get this error owned = results[0]['Count']
TypeError: tuple indices must be integers or slices, not str```how to fix this?
I am using sqlite3
naive sandal
high geyser
@naive sandal
no I got it
I was using sqlite3 so things were somewhat different
I used int instead thanks
inland stone
Hello. So I have 3 tables:
table_1, table_2, table_3
all 3 tables have a column called nameX (but in table 1, the values in this column may contain extra characters)
I need to
select all columns from table1 where
(nameX in table_1) contains (nameX in table_2 where nameX is not in table_3)
how do I achieve this π
trying things like
select * from testi.jobs2 where NameAtCustomer not REGEXP (select NameAtCustomer from testi.pos where NameAtCustomer not in (select NameAtCustomer from testi.Invoices));``` not getting a good result
grizzled dagger
Some how my heroku mysql database wants to auto increment everytime with 10. How I can fix this thing?
I have try the query ALTER TABLE table_name AUTO_INCREMENT = 1; but this won't help that number automaticlly goes back to 11.
high geyser
c.execute("""SELECT keyword,
tag_use_count,
COUNT(*) OVER() AS "Count",
SUM(tag_use_count) OVER () AS "Uses"
FROM tags
WHERE guildid=? AND authorid = ?
ORDER BY
tag_use_count DESC
LIMIT 3""",(ctx.guild.id,member.id))``` over here I want to select keyword which doesnt have nsfw words in itso I am using betterprofanity
I check it like
if not profanity.contains_profanity(keyword)```how can I query the sqlite db to select only words which dont contain profanity?
pls ping me when help
I tried this
test_list = ["yeah"]
c.execute(f"""SELECT keyword NOT IN ?,
tag_use_count,
COUNT(*) OVER() AS "Count",
SUM(tag_use_count) OVER () AS "Uses"
FROM tags
WHERE guildid=? AND authorid = ?
ORDER BY
tag_use_count DESC
LIMIT 3""",(test_list,ctx.guild.id,member.id))
results = c.fetchall()```where test_list is a list of profane words
but that didnt work out either
I got profane words which were in the list in my query
pls help me what to do???
pls ping me when help
test_list = ["yeah"]
c.execute(f"""SELECT keyword,
tag_use_count,
COUNT(*) OVER() AS "Count",
SUM(tag_use_count) OVER () AS "Uses"
FROM tags
WHERE guildid=? AND authorid = ? AND keyword not in ?
ORDER BY
tag_use_count DESC
LIMIT 3""",(ctx.guild.id,member.id,test_list))```this doesnt work either
wispy hazel
is there any database that i can install with pip except Sqlite?
surreal flame
every?
wanton sentinel
Every database?
torn sphinx
@grizzled dagger Postgres uses serial type for auto increment
You want this looks like, https://www.postgresql.org/docs/9.1/sql-altersequence.html
@wispy hazel pip is package manager. you can use this to install a package/library that allows you to connect to a database using python code
high geyser
test_list = ["yeah"]
c.execute(f"""SELECT keyword,
tag_use_count,
COUNT(*) OVER() AS "Count",
SUM(tag_use_count) OVER () AS "Uses"
FROM tags
WHERE guildid=? AND authorid = ? AND keyword NOT IN {test_list}
ORDER BY
tag_use_count DESC
LIMIT 3""", (ctx.guild.id, member.id))
results = c.fetchall()```this doesnt work eitherpls help me
I use sqlite3
tepid cradle
@high geyser what do you want to do with count(*) over()?
The syntax of both count and sum are incorrect.
high geyser
oh
I want to count sum of tag_use_count where keyword is with given conditional
also the number of keywords selected like that
@tepid cradle
and also do you have ideas how to go about my problem?
to check if keyword is not in an external list
tepid cradle
I want to count sum of tag_use_count where keyword is with given conditional
@high geyser I don't understand this statement, but count and sum are aggregation functions which need to be used with group by clause. The general syntax is like this:
select user_id, count(transactions), sum(transaction_value)
from user_transaction_history
where user_id in (1, 2, 3, 4, 5)
group by user_id
this will give a count of all transactions and the sum of transaction value for the users 1, 2, 3, 4, and 5
If you use count or sum, you have to use group by, otherwise count and sum can be applied only over the entire table.
tepid cradle
I have selected the user_id in my sample query, you can select the keyword the same way
the selected columns must be repeated in the group by clause
high geyser
oh
@tepid cradle I want to check if the user_id is in a list suppose
but that list contains variables I will add
what will I do then?
test_list = ["yeah"]
c.execute(f"""SELECT keyword,
tag_use_count,
COUNT(keyword),
SUM(tag_use_count)
FROM tags
WHERE guildid=? AND authorid = ? AND keyword NOT IN {test_list}
ORDER BY
tag_use_count DESC
LIMIT 3""", (ctx.guild.id, member.id))
results = c.fetchall()```