@lapis idol what are you after?
#databases
worthy prism
manic wedge
what type of database would you guys recommend for storing 2 integers. It must be quick and efficient as it could be storing millions of entries. I also need to be able to search up one int from the other (lets say we had ID and counter, I need to be able to get counter from ID but not vice versa)
I am also obviously coding this in Python so I need ones that have Python modules to utilise them
vernal ocean
cant hurt to just use postgres
topaz wharf
@manic wedge what about Redis?
steel slate
if you only need one server to access it then maybe sqllite (django has built in handling of it)
although I would be wondering if the data is a good fit for a db to start with
will the id be unique?
junior stone
anyone familiar with mongodb and the "$expr" operator?
how can I put a "$in" operator inside a $expr?
junior stone
nvmd, got it
ionic pecan
redis is not a database, redis is a remote data structure server with support for persistence. if you want to prevent data loss with redis you need to either configure save at a low interval or use AOF. if you need O(1) key lookups for e.g. IDs and your use case is the counting as you said, then redis is a good pick, as long as you configure persistence properly. e.g. postgresql on the other hand ensures data is always written after a transaction with sync. see also: https://news.ycombinator.com/item?id=3010665
manic wedge
@ionic pecan Yeah I just started researching Redis and although it sounds very good, the persistence doesnt seem very good
So I might go for postgresql instead
I wanted it to be a simple operation like SQL where you can just open the file, search for a row in the dataset and get the corresponding value, edit the value and save the file again
And also it is kinda counting but not just incrementing or anything. I will just be adding or subtracting from the integer (its to do with currency)
shy gale
can I use redis for web app to store messages?
sonic kindle
Okay, I've got a rather long question here:
I am working on a project that uses neural networks and satellite data to predict wildfires. I am using the Google Earth Engine Javascript API and will use Keras to train a deep ANN. The network will take the temperature, humidity (if I can get the data), and vegetation (I might use NDVI if possible). (Just in advance, part of my question won't necessarily have to do with datasets and more with neural networks, I just want to get more done in one question).
I am using the MODIS satellite to find the temperature of given areas within a given timeframe using the Land Surface Temperature and Emissivity dataset. I am able to do this with the following code:
var dataset = ee.ImageCollection('MODIS/006/MOD11A1')
.filter(ee.Filter.date('2018-12-10', '2018-12-23'));
var landSurfaceTemperature = dataset.select('LST_Day_1km');
var landSurfaceTemperatureVis = {
min: 13000.0,
max: 16500.0,
palette: [
'040274', '040281', '0502a3', '0502b8', '0502ce', '0502e6',
'0602ff', '235cb1', '307ef3', '269db1', '30c8e2', '32d3ef',
'3be285', '3ff38f', '86e26f', '3ae237', 'b5e22e', 'd6e21f',
'fff705', 'ffd611', 'ffb613', 'ff8b13', 'ff6e08', 'ff500d',
'ff0000', 'de0101', 'c21301', 'a71001', '911003'
],
};
Map.setCenter(6.746, 46.529, 2);
Map.addLayer(landSurfaceTemperature, landSurfaceTemperatureVis, 'Land Surface Temperature');
print(landSurfaceTemperature);
// map over the image collection and use server side functions
var tempToDegrees = landSurfaceTemperature.map(function(image){
return image.multiply(0.02).subtract(273.15);
});
// print and add to the map
print('image collection in temp in degrees', tempToDegrees);
Map.addLayer(tempToDegrees, {min: -20, max: 40, palette: landSurfaceTemperatureVis.palette}, 'temp in degrees');
With this code, I can click on a specific area on the map and get a graph of the temperature within a specified timeframe. How would I go about turning this into a Python array, with the temperatures of 1 km squares with their respective coordinates? I also want to be able to find such array for humidity and vegetation.
Second, I am also using the Terra Thermal Anomalies & Fire Daily Global 1km MODIS dataset for my wildfire data. I want to combine this data with the temperature data to find whether a wildfire will occur in a 1 km square within a month. How can I turn this into an array that corresponds with the other array(s)?
Overall, I want to build a neural network that, for input data, takes the temperature, humidity, and vegetation in a given area and output the likelihood of a fire occurring in the area within a month.
I apologize for the long message!
nimble vigil
Hello everyone, im trying to use postgres with peewee ORM, i downloaded postgres via sudo apt-get install postgresql postgresql-contrib, but running my script i get a peewee.ImproperlyConfigured : Postgres driver not installed! how can i set this up?
bright hornet
Hi guys, how do you name the class that is responsible for collecting data from database/checking updates and passing received data for further processing?
dawn bloom
Anyone here familiar with asyncpg? I just started using it, and was looking at a repo that did this:
async with pool.acquire() as conn:
async with conn.transaction():
result = await pool.fetchval("SOMETHING")
While the docs (from what I understand) want me to do it like this:
async with pool.acquire() as conn:
async with conn.transaction():
result = await conn.fetchval("SOMETHING")
Anyone knows which one is correct? Both seem to work, so is there actually any difference at all?
tawny sail
which is the most widly used package/library for access database(mysql)
like PyMYSQL or mysql-connector?
and can anyone tell me abt postgress
ionic pecan
@nimble vigil you need to check which dependencies peewee has for usage with postgres, the packages you installed are for the database server, you probably want something like psycopg2
@bright hornet why would you put that in a class
nimble vigil
@ionic pecan What does the dependency do exactly? do you have any sources i can read?
bright hornet
@ionic pecan because there is a little more than that
ionic pecan
@nimble vigil http://docs.peewee-orm.com/en/latest/peewee/installation.html:
„To use Peewee, you typically won’t need anything outside the standard library, since most Python distributions are compiled with SQLite support. You can test by running import sqlite3 in the Python console. If you wish to use another database, there are many DB-API 2.0-compatible drivers out there, such as pymysql or psycopg2 for MySQL and Postgres respectively.“
so you want psycopg2 for usage with postgres - its essentially a DBAPI compliant adapter for using PostgreSQL in Python
ionic pecan
@tawny sail I wouldn‘t go by „most widely used“, just use one that looks up-to-date and DBAPI compliant
maybe also fitting your requirements, like thread safety
hazy peak
Any recommends on what libraries to use for using aync mysql? Or should I just use "import asyncio" and make myself do that?
full geyser
theres an async mysql library?
oh huh
theres that
had no idea there even was an async mysql library
i dont think you can use this to do multiple queries at once tho
its just for if you want to run something else while u make the query
harsh pulsar
that "something else" could be another query...
full geyser
no it cant
i think...
afaik mysql doesnt support parallel queries (connections aren't thread safe or something)
so if you tried to make another query over the same connection as one currently in progress, it wouldnt work
tawny sail
@ionic pecan okay thanks
golden comet
Sorry in advance for any un-clear wording/phrasing in explaining my question/what I'm trying to do - I'm not even sure the specific term is for what this (general idea, just not specific) - From the reading I've done as far as best practices go when building mysql database queries (user either mysql.connector or peewee) the method to go with is: sql = "SELECT col1 col2 FROM table WHERE col1 > %s" and then state said value in the execute statement, however I'm having issues with a particular statment because where the %s is the tablename so it keeps telling me (in pycharm) that the colname is unfound. Here's the statement where instead I use a tablename since trying to dynamically build that keeps not working.
sql = ("""SELECT subreddit, COUNT(*) AS cnt FROM ModlogModel WHERE created >= DATE_SUB(NOW(), INTERVAL %s %s) GROUP BY subreddit ORDER BY cnt DESC""" % (val, interval)) - Idealy I have about 5 or so tables that I want this info and I'd like to be able to just keep this but change out tablename according to what I define - does that make any sense? I can give the full def if needed for context.
pure cypress
It's called query parameter substitution
golden comet
hi Mark!
pure cypress
Unfortunately, I believe the issue is that parameter substitution does not allow substituting table or column names
Hi
golden comet
thank you I'll do some searching on that and hhmmm so the above statement I gave works just fine - and I'm able get this:
+-------------------+------+
| subreddit | cnt |
+-------------------+------+----- |
| LetsNotMeet | 22 |
| sewing | 26 |
| UkrainianConflict | 29 |
| TwoXChromosomes | 2259 |
+-------------------+------+---- |
from:
def log_stats(interval, printme=False):
val, interval = get_interval(interval)
sql = ("""SELECT subreddit, COUNT(*) AS cnt FROM ModlogModel WHERE created >= DATE_SUB(NOW(), INTERVAL %s %s)
GROUP BY subreddit ORDER BY cnt DESC""" % (val, interval))
c.execute(sql)
allpretty = makepretty(c)
db.commit()
if printme:
print(allpretty)
return allpretty
ack my prettytable is not so pretty!
pure cypress
If you need to change the table name per query then you can just use string formatting
assuming the table name isn't coming directly from user input you should be safe
golden comet
I mean I guess I can just do a def for each table - and then build a def to grab each of these, and give me a expanded stats outline for each query for the interval I'm requesting
pure cypress
If you want, but I think it's perfectly fine if you have a "helper method" of sorts for this query
and just use string formatting for the table name
golden comet
hhmm ok and here's where I feel stupid but string formatting would be "blah blah {}".format(string)" right?
golden comet
because everything I've looked at says to not do that in sql queries
pure cypress
Yeah, that's generally true
It makes you vulnerable to sql injection
but the table name presumably is not being supplied by a user, rather it's likely hard coded in some list of table names
so you don't have to worry about sql injection
that's my understanding of it
golden comet
ok let me try that but if I recall correctly when I had tried, it kept giving me no results it was weird
visual flint
is json considered a database 🤔
golden comet
oh yah I verify/delcare all tables before I start stuff
thanks Mark let me go try that - next up.....JOINS with no forignkey - 😃 😃
pure cypress
You're welcome
@visual flint No, on it's own it isn't but there are NoSQL databases that make use of JSON
like MongoDB
visual flint
im saying in an abstract sense for python
golden comet
rbd maybe?
also yah I'm getting the same erroring or maybe I'm not writing it correctly?
sql = ("""SELECT subreddit, COUNT(*) AS cnt FROM {} WHERE created >= DATE_SUB(NOW(), INTERVAL {} {})
GROUP BY subreddit ORDER BY cnt DESC""".format(TBNAME, val, interval))
exemany(sql)
Traceback (most recent call last):
File "<input>", line 1, in <module>
File "<input>", line 5, in exemany
File "C:\Users\ri0td_000\AppData\Local\Programs\Python\Python36-32\lib\site-packages\mysql\connector\cursor.py", line 566, in execute
self._handle_result(self._connection.cmd_query(stmt))
File "C:\Users\ri0td_000\AppData\Local\Programs\Python\Python36-32\lib\site-packages\mysql\connector\connection.py", line 537, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
File "C:\Users\ri0td_000\AppData\Local\Programs\Python\Python36-32\lib\site-packages\mysql\connector\connection.py", line 436, in _handle_result
raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'created' in 'where clause'
@golden comet Maybe you ran into issues because you didn't properly escape the table name. When you use parameter substitution, escaping is taken care of you by the library. However, when you have to resort to string formatting, you also have to handle escaping yourself
Look up how table names need to be escaped in mysql
golden comet
thank you!
I was going to ask if I needed a specific cursor
currently I use buffered=True
pure cypress
By the way, you can use string formatting in conjunction with parameter substitution
So, sticking to best practices, I'd say try to use the latter for as much as possible
rather than converting it all to string formatting
do it at your discretion
golden comet
I'll have to go look up stuff on parameter substitution and excaping table stuff
yah I usually use to fallback on string formatting but recently what with my big convert all databases to mysql I've been trying to adhere to best practices and doing things the longer/slower way so in the long run I don't run into issues
pure cypress
@visual flint You're probably thinking of the larger concept of data persistence.
Which both databases and JSON files (really any file saved to a disk) would fall under
golden comet
OH while I'm thinking about it - is there a query/statement I can do to disable shoot - it's not called strict col names but .... ah yes "ONLY FULL GROUP BY" - or well I'll do some more digging thanks for your help!
golden comet
hey @pure cypress was this what you were referring to about parameter substitution? https://dev.mysql.com/doc/refman/8.0/en/user-variables.html <-- I'm doing a search on that to get more info/insight but not getting a lot of exact search maches in google :\
pure cypress
No, that's not it.
It's not an SQL or MySQL specific thing
it's part of libraries
docs for mysql.connector
golden comet
thank you!
golden comet
sorry to keep pesting you guys - does anybody have some suggestions as far as getting pretty formatted data from sql queries (right now I"m using prettytable, but when I do that and try to return said data, it returns the prettytable object, not the formatted output)
tawny sail
Hey guys, can anyone tell me a good data analysis with python cource either online or YouTube, I'm having a hard time choosing (prolly in YouTube as there r many n hard to choose which to go with)
fierce oar
I lack the experience to be able to tell whether or not https://github.com/tortoise/tortoise-orm is a good fit for my project. I want an easy to use async ORM, and I'm using PostgreSQL. I can't see a reason why not to use tortoise, but maybe you can. ^^
livid oriole
@tawny sail if you have some money you can use datacamp
shy magnet
Im doing some stuff with psycopg2, wondering if there's any good solution to getting sql tuples as actual tuples instead of strings. it seems that psycopg parsing the returned data only goes one layer deep so doing something like cursor.execute("SELECT (1, 2), 3") will return cursor.fetchall() [('(1,2)', 3)] where I get a list, with the first element being a string representing the tuple of 1,2, while the second element is an actual int value of 3
I'd like it so that it would return a nested structure where the first element would be an actual python list containing 1 and 2 instead of just a string
full geyser
u could use ast.literal_eval on the string if it's safe to do so... otherwise idk
harsh pulsar
Ew looks like a psycopg2 bug
Maybe try asyncpg if possible? That one uses the binary pg protocol
cunning ginkgo
How could I fetch all rows from a postgresql database that have a set id, and then iterate over them to print each individual value?
I tried
@commands.command()
@commands.guild_only()
async def tags(self, ctx):
query = "SELECT tagname FROM tags WHERE serverid=$1;"
query = await self.bot.db.execute(query, ctx.guild.id)
msg = ""
for r in query:
msg += r
await ctx.send(msg)
and it returns SELECT 1
I know that there's one entry, but I want to get a specific column from it, rather than just the string SELECT 1
slate spire
you want fetch, not execute
sterile ferry
hey guys, what is the most portable parameter for server_default on boolean column value in sqlalchemy?
hm, '0' seems to be doing the trick for postgres and sqlite, guess I'll go with that
sterile ferry
hi again, so I was using backref for a bit but then went for back_populates since it's more clear/explicit to see the overall model relationship that way, but now I feel like going back to backref since back_populates makes me write lots of duplicate excessive code. You think it's a good idea to use backref but with an explicit dummy field on the other side e.g. orders = [] # on Client model and client = None # on Order model?
dull scarab
This is the 2nd time you've asked that, and in the wrong channel. Are you sure you don't want to ask in #data-science-and-ml and not * #databases *
novel rune
What language is usually used to write a bot
harsh pulsar
@novel rune depends on what you mean by "bot"
ionic pecan
doesnt sound like something about databases
dull scarab
And language since you're asking in #databases
candid bronze
Hello, beginner to SQL here, I just finish Corey's Schafer's guide on SQL but that was too short and it was discontinued, is there any youtube video or courses where I can learn SQL enough to apply in job? prefer an up-to-date video/courses, thanks 😄
modest spire
with sqlite3 if I set a variable to a query like "SELECT * FROM table" could I use the variable (lets say example is the variable here) to query different columns like example.id?
harsh pulsar
Good q actually. Try it? My guess is "no"
modest spire
Does SELECT in sql show them or just select them for editing? ❔
harsh pulsar
select for editing? thats not really a thing in sql..
think like code. SELECT is a function that "returns" a table
deft badge
yeah, just explained that in #python-discussion
please don't ask questions in multiple channels
modest spire
async def reportquery(self, id):
async with aiosqlite.connect('utils/bot.db') as db:
await db.execute(f'SELECT user, date, body FROM reports WHERE id = {id}')``` so would this work?You shouldn't use string format for your queries as it leaves your database vulnerable to sql injections
deft badge
it would work but nothing will happen
dull scarab
User prepared statements instead py sql = "select something from table where id = ?" ... db.execute(sql, (id,))
deft badge
isn't it ??
dull scarab
I mix em up all the time
deft badge
lol
dull scarab
why can't they all work with ?
modest spire
what is the ? for?
dull scarab
I'm just going to go back to being hungover.
deft badge
@modest spire It says that when you send it to the SQL server the ? should be repalced with id
harsh pulsar
the ? is a placeholder that the sql library replaces with the desired content
deft badge
but it replaces it in a safe fashion which escapes it to prevent SQL injection
harsh pulsar
^
deft badge
no
because the library escapes it for you
for example
if I was to enter 1 OR 1=1 as the ID
and you used an f-string
the query would be something like SELECT user, date, body FROM reports WHERE id = 1 OR 1=1
since 1=1 is always true the SQL server will return all the results and if you send that to the client you have a potential security breach
gilded narwhal
i think any iterable will do for the second argument. i think.
deft badge
yeah, I've always used tuples though because it is what should be used
anyway, if you use that ? instead of an f-string and tell aiosqlite to substitute it then it will be replaced with a safe escaped version of the user input
modest spire
but how do I set what the value of ? is
deft badge
Chibli showed you
you pass it in as an argument to your execute function
username = input("What is your username? ")
my_query = "SELECT email, password FROM users WHERE username=?"
db.execute(my_query, (username,))
ooh
deft badge
and that ? will be automatically escaped
modest spire
Should I do that when putting information into my database?
deft badge
As a rule of thumb you should always be using ? instead of f-strings but you should especially use ? if any database operation is handling user input
harsh pulsar
you can't use placeholders for column names or table names though
its only for actual data stored in the database
deft badge
you shouldn't have a scenario where column names are being selected by the user
deft badge
if they are then you should make use of SELECT * FROM users and return the selected columns
harsh pulsar
or you should have a whitelist of column names
deft badge
Yeah
modest spire
async def reportquery(self, id):
async with aiosqlite.connect('utils/bot.db') as db:
sql = "SELECT user, date, body FROM reports WHERE id = ?"
await db.execute(sql, (id,))```So something like that?
deft badge
indeed
harsh pulsar
ALLOWED_COLUMN_NAMES = {
'foo',
'bar',
'baz'
}
if requested_column not in ALLOWED_COLUMN_NAMES:
raise ValueError('Invalid column name requested')
you may want to store the result of that db.execute though since that is the data you are requesting
harsh pulsar
async def reportquery(self, id):
async with aiosqlite.connect('utils/bot.db') as db:
sql = "SELECT user, date, body FROM reports WHERE id = ?"
return await db.execute(sql, (id,))
example taken from the aiosqlite docs though
async with aiosqlite.connect(...) as db:
cursor = await db.execute('SELECT * FROM some_table')
row = await cursor.fetchone()
rows = await cursor.fetchall()
await cursor.close()
be aware that what is returned by that query is not the data until you ask for the data
happy new year to you too!
modest spire
so if I want to display the data I need to do py variable = await db.execute(sql, (id,)) row = await variable.fetchone() await variable.close()?
modest spire
so return row
deft badge
yeah that will return it
modest spire
Whats the difference between that one and the one we just did?
deft badge
do you understand what returning does?
modest spire
Yeah
I mean the first one we worked on with the ?
Whats the difference between em two
deft badge
what and the one taken from the docs?
modest spire
async def reportquery(self, id):
async with aiosqlite.connect('utils/bot.db') as db:
sql = "SELECT user, date, body FROM reports WHERE id = ?"
return await db.execute(sql, (id,))``` and
```py
async with aiosqlite.connect(...) as db:
cursor = await db.execute('SELECT * FROM some_table')
row = await cursor.fetchone()
rows = await cursor.fetchall()
return row
await cursor.close()```so in the first one you are returning the cursor which is an interface between python and the section of the database you are working on
that second one is kind of broken though
you've added something after the return
the cursor should be closed before you return
modest spire
oh
deft badge
but that second one selects one thing from the results & returns it
modest spire
how would it be formatted
deft badge
async with aiosqlite.connect("my_database.sqlite") as db:
cursor = await db.execute("SELECT username, password FROM my_table")
row = await cursor.fetchone()
await cursor.close()
return row
doesnt dbapi2 only have cursors able to execute, and execute not returning anything?
modest spire
So to get the username it'd be row.username?
modest spire
like row["username"]
deft badge
modest spire
Thanks
async def reportquery(self, id):
db.row_factory = aiosqlite.Row
async with aiosqlite.connect('utils/bot.db') as db:
sql = 'SELECT body, user, date FROM reports WHERE id = ?'
cursor = await db.execute(sql, (id,))
row = await cursor.fetchone()
await cursor.close()
return row
@commands.command()
@commands.guild_only()
async def records(self, ctx, id):
await self.reportquery(id)```
I'm completely stuck here I want to get the body, user and date and then set them as a variable in another function (records) from reportqueryare you using a console at all or just trying to write code in a text editor?
i strongly recommend using a console so you can explore objects
but the row object i assume will contain body, user, and date attributes
which you can then pass on elsewhere
winter flax
Hi I have a very limited knowledge on db & would like some help/pointers if the way I am approaching this is right. I save a continuous stream of rows containing json text in a postgresql db table.
A user uses frontend webapp to mostly run these queries: 1) To see the last x entries filtered for particular keywords from json 2) To see entries between datetime: X & datetime: Y.
Now I am thinking instead of auto-incremented integers for primary key, is it better to use epoch time with milliseconds as primary key. Would this significantly improve performance for my use cases mentioned above? Are there any better approaches?
modest spire
@harsh pulsar but the row is local to reportquery not records where I need it to be
deft badge
no you have returned it
so in r ecords
row = self.reportquery(id)
then you can do ```py
row['body']
and stuffOoooh
somber storm
SQLalchemy: How can I add a column in my object?
my model.py class looks like this:
class User(db.Model, UserMixin):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(20), unique=True, nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
image_file = db.Column(db.String(20), nullable=False, default="ProfitBank.png")
password = db.Column(db.String(60), nullable=False)
joined_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow())
posts = db.relationship("Post", backref="author", lazy=True)
def __repr__(self):
return f"User('{self.username}','{self.email}', '{self.image_file}')"
but even though I import db and write db.create_all() -- the table still doesn't take in the joined_at variable
is there any way to add columns one at a time?
@me when you have a suggestion
ionic pecan
you should use a proper migration tool for things like this, iirc create_all can only do initial table setup
alembic is a good pick
formal pumice
Part of my job requires me to monitor and track service requests for well over 100+ properties. I decided to start developing some tools to automate and make my life easier. I have ran through a million ways to accomplish it but I am having trouble figuring out exactly how the workflow needs implemented. I believe I want to go the database route. Essentially, start and maintain a database that would include what is going on at each location. The 'automation' side is I want to parse emails to update the database as emails and new information rolls in. Am I on the right track here or do I need to re-think the workflow?
novel wharf
It's a bit hard to tell what exactly you're doing and how it should be done, but if you have a job that you can automate with programming, by all means give it a go.
Using a database means you probably want to access the information for later. If you are using these emails as input and storing the results somewhere, you could use a database.
@formal pumice
formal pumice
I know and it's hard to explain via a chat channel. Thank you @novel wharf for your input. One way to explain what I am trying to accomplish, would be to describe it as a 'help desk ticket' or an 'incident report'. Essentially, I get emails saying "We have a roof leak at location x", or "We have a plumbing issue at location y". The way I see it, when I get to my office in the morning, I could export the previous days emails, and parse the file. Then use the data extracted from the emails to update a database.
I just want to make sure the workflow makes sense and that I am thinking this through in a logical manner.
novel wharf
Parsing the language into actual tasks and locations would be an rather difficult project, but collecting them and presenting quickly could be a thing.
Or are you looking for a way to create a filter that sorts out specific letters that are relevant?
formal pumice
I don't think I need actual tasks. More like, the kind of request. I would think this would be rather straight forward. However, I'm not sure if this is explicitly a parsing task (can be accomplished with 'if' statements) or goes in to ML territory. Locations should also be rather straight forward because the sender's email address has a location abbreviation in it.
I say labelling the type of request should be straight forward because there are very indicative keywords that would label something as a 'plumbing issue'. For example, emails regarding a 'plumbing issue' almost always include words like: clog, sink, toilet, water, plumber, leak, bathroom, etc
novel wharf
Sounds like you could make a decent filter from that, yeah.
Do you have a way of having access to and parsing the emails?
formal pumice
Yes. But I'm glad you brought that up. So the way I see it, I have 2 options. The first option would be to figure out if real time monitoring of my inbox would be possible. The second option, and much more likely option, is to use an export. I can export Outlook data into a CSV or a TSV.
novel wharf
What email client are you using?
formal pumice
It a Microsoft Exchange through my employer.
I found this today: https://github.com/ecederstrand/exchangelib
If I am forced to parse from an export, I would basically just be a day behind, which is not a killing matter. I would just have a routine of exporting the previous day when I arrive in the morning, and then running my system on the previous days data.
novel wharf
That sounds like a good starting point.
formal pumice
Okay, thanks for lending an ear. It's good to know that I am not completely of base here.
novel wharf
I mean, there oughta be some way of being able to parse it immediately, or checking it every hour. Can be tricky to access it.
formal pumice
Yes thats what I am thinking too. I may have to sweet talk an IT administrator. haha
novel wharf
I mean, if you have access to a web-based email client, you could be scraping it.
But the repository you linked looks like it could be useful. Not sure why it'd be one day behind.
formal pumice
I was saying I would be one day behind if I was forced to parse from an Outlook export. Because I would basically export the data first thing in the morning from the previous day, and parse it from that point. If that makes sense.
novel wharf
Sure, but if you could automate it, you could make it parse several times a day, perhaps on demand.
I mean, it would sound rather neat getting back from lunch and starting up your program to scan through the new emails.
formal pumice
Definitely. And thats what I am aiming for.
So, let's say I have everything up and running. The database, and the parser. The program runs and it says "Location X has requested service for an electrician".
This is where my brain isn't exactly sure what happens next. I'm thinking a Python object gets created. The reason I'm thinking this, is because I need to be able to detect if a new email pertains to an existing request.
Say for example, 5 days ago I received an email requesting service, and then today I get an email saying that the job was complete.
Really, the first step in a decision tree would be to decide if the email is in regards to an existing email thread or not.
Not sure about the application of that.
It would update the 'object' or 'request' that is stored in the database
teal rampart
When I run add_user_ranking func: https://hastebin.com/boqacucovo.py
Traceback (most recent call last):
File "C:\Users\Matteo's PC\OneDrive\Desktop\Matteo's PC\Programming\Athomos v2\utils\db.py", line 82, in add_user_ranking
c.execute('INSERT INTO levels(id, guild, name, level, exp, rawexp, time) VALUES(?,?,?,?,?,?,?)', (user.id, user.guild.id, user.name, 1, 0, 0, time.time()))
sqlite3.IntegrityError: UNIQUE constraint failed: levels.id
Do you already have an entry for that user?
That is what the error is saying, that you're trying to insert a row with a column (from user.id) that already exists
If you want to change that row use UPDATE instead of INSERT
teal rampart
Ok
dull scarab
Does rethinkdb auto increment primary keys?
terse stump
do not think so
UUID is already unique, hence the need for auto incremental pk is not needed
mysql solves this with auto increment, rethinkdb uses UUID to solve the same problem (i think)
weary flax
I have a CSV file with two values on each row, like
"9300685","300685"
How can I insert this into my database? The question should be, hey where ID is 9300685 (first cell), insert 300685, (second cell)
i dont really have any experience with sql
dull scarab
which database are you using?
weary flax
mysql
weary flax
are u refering to my question earlier today?
dull scarab
Im refering to the one you just asked.
dull scarab
That sounds like a first step then
dull scarab
You can either use the csv module, or do it manually given the simplicity of the csv
weary flax
that needs the second cell added for each first cell
dull scarab
What do you mean the first cell exists?
You create a table for the problem, in this case you want 2 columns, one for each value and maybe a third for a primary key unless you can use one of the other columns as a primary key
weary flax
Uh, just "9300685","300685" means if 9300x exists, insert 300x at a specific column that i decide
they exist though, i just need to import that second comma separated value
dull scarab
Like your csv already has the x & y pairs right?
weary flax
the first comma separated value is basically an ID
dull scarab
Does that table have the 2nd column ready?
dull scarab
I'm just going to call them Column X and Y for now, but you could write a query like SQL UPDATE table_name SET Y = %s WHERE X = %s
and create a list of your id, value (x, y) pairs like py data = [ (x1, y1), (x2, y2), (x3, y3), ... ] And execute it all with the executemany method ```py
... # connect / get cursor etc
cursor.executemany(sql_query, data)
query is the sql above as a string, and data is the list of tuples ```
weary flax
how would i load my csv file as the list?
dull scarab
This uses prepared statements (the %s and passing it as a parameter to execute instead of formatting query string directly), and the executemany method for better performance
You could generate it manually by reading each line, split on "," and append a tuple of each value to your list
or look into the csv module if they have a method for it
Most notably, a csv file is just a normal text file where values are separated by a comma
weary flax
so essentially
("F023805","F007547, 94242442, 94248420"),
are those 2 entries, or 1
weary flax
thats one, with two cells
first is the id
second is the string of the value that needs to go into a column of that id
dull scarab
yeah, you'd create a tuple like that and append it to a list
when you're done with the file pass the list to executemany
Oh, my mistake
but you should have them be ordered (y, x)
or name the variables in the prepared statement
if mysql supports that
¯_(ツ)_/¯
dull scarab
is the key the 2nd index?
dull scarab
You may have to swap them around so the key is the 2nd value of the tuple given the order of the SQL query written
UPDATE table_name SET Y = %s
WHERE X = %s```since it asks for the value first.
dull scarab
seems good
full geyser
wheres the complaint?
dull scarab
Is that python code or sql
The first part is SQL, which should be a string in python code, rest is just normal python
weary flax
Oh i thought we were doing sql lol
dull scarab
It is a python server after all :P
weary flax
I dont even have a clue how to connect to my database with python lol
dull scarab
https://www.w3schools.com/python/python_mysql_getstarted.asp May be a good starting point then
proper field
Here.
proper field
I was planning on using this but if you think your sources would be easier for me to understand then I will gladly go with that.
unborn sentinel
Oh yeah, Sololearn would be a good start
Also, just fussing around with it on your computer as well. The https://docs.python.org/3/library/sqlite3.html docs will be useful as well. I'd say start with the Sololearn stuff, then futz around with the sqlite3 library, and then if you have more questions come on in here.
Databases will make your life a LOT easier if you're going to have quite a few entries and what not
Certainly more pleasant to deal with than dictionaries or JSON
And traditionally much faster.
proper field
Sure, could you give me some exercises for ahead of time or maybe a subreddit/site that holds some SQL tasks I could potentially do once I've learned SQL?
Or learned it up to a point.
unborn sentinel
Of course. I'll see what I can scrounge up. But the example I learned on (and the whole reason I started learning it) was for when I was making my weapons inventory for my RPG. Weapon names, types, upper and lower damage ranges, stuff like that.
And most of those tutorials or guides out there will have examples for you to work along with
Usually involving workers and salaries and all that
proper field
Ah, I'll most likely be needing to make some sort of warning "class"/table or something.
I'll see how the examples work out and if they don't work I'll ask around here.
unborn sentinel
-nods- Yeppers. In that particular table you'll probably have Username, User ID, Date of infraction, who made it, what the type of it was, and any comments that went along with it
Another table might be one for your moderators
And so on and so forth. But worry about the syntax first with the lessons and we can guide you through the rest if you get stuck
proper field
Yessir, thanks a bunch. 
wind nexus
I need to store in a database information about users skills, Name of the skill and how good tey are at it(0-5) so that i can easily search users who have a certain skill at or above certain level, what would be a efficient model for this?
i currently have this
class Skills(db.Model):
id = db.Column(db.Integer(), primary_key=True)
skill_name = db.Column(db.String(255))
level = db.Column(db.Integer) # Skill level = 0 = Beginner 5 = Expert??
levels = {
0: 'None',
1: 'Minimal',
2: 'Novice',
3: 'Intermediate',
4: 'Advanced',
5: 'Expert'
}
but realized that doing it like this leads me to having many duplicates of same skill name with different levels
taking up space on the database
im using SQLalchemy
dusky owl
I might use the user ID as your primary key, and then have columns labeled as the skill name, with the value being their skill. If the number of skill names is known
So you’d have your DB look like
ID | Fletching | Mining | Fishing | RuneCrafting
547373 | 5 | 3 | 2 | 5 | -1/null
I've done some searches to no avail. My problem is, I need to be able to develop my PostgresQL database in my office, and at home. How do I accomplish this? Is it feasible? I'm picturing a PostgresQL environment installed on both machines (home and office) and then a data repository on an external hard drive or thumbdrive. Am I completely off base here?
wind nexus
The ammount of skills is dynamical and more can be added (skills as in job skills: python, Java, Photoshop)
so that i can find all offers and all users who have same skill at certain level
maybe i could have a table for skill names, another for user skills that would be like this :```
ID | User_ID | Skill_ID | Skill_level
1 3 2 4
what would be the proper way to search from that a user with skill id = 2 at skill level 4 or above using sqlalchemy
dusky owl
I’ve never used sql alchemy but if the value is an integer the full query would be like select user from table where skillid = ID and level >= 4
Something like that with correct column names
wind nexus
Hmm, ill try and make that work, i need to always make sure that users dont have to wait too long for results
vale mulch
Hello everyone, so going back to an issue I've had in the past I have an asynchronous discord.py bot and a normal flask application both in which use a MySQL database. I've encountered an issue where if I were to commit something in the flask app and then tried to retrieve it manually through the discord bot, the discord bot would not find that information in the Database until I recreated the connections. I was wondering if there was a solution to do this/any preferences I can change to ensure this isn't an issue. Currently I'm creating a connection for each query and definitely don't want to use this going forward.
Any help on the matter is greatly appreciated!
wind pelican
are you remembering to commit your transactions?
in sqlite3's bindings every time you submit a command it starts a transaction and you need to remember to commit it for proper behavior. might want to see if the mysql bindings do something similar
vale mulch
I'm definitely committing all transactions
Well not all, no point in committing select statements and other things.
It worked fine with sqlite3 but... I had to set check_same_thread to False
vale mulch
@wind pelican Sorry for the @ but apparently the issue has resolved itself, but another question if you don't mind. When I load a dashboard page I execute a query for information from the dB but it's inconsistent. Sometimes returning the correct value sometimes returning None, do you have any insight on this issue? Any help is once again greatly appreciated!
wind pelican
i would expect it to be a query issue if the db server is behaving properly
vale mulch
I would expect it to be as well, but when I execute a Database call where the information originated from it'll find it no problem.
But otherwise it doesn't find it until I restart the application
sonic kindle
I've asked this question before, but I'm still having trouble with it:
I'm trying to get MODIS satellite data with Google Earth Engine to find temperature data of given points on a map. I would like to get an array of all the points in an area (every 1km square in California) and their temperatures in a given timeframe. The array would be kind of like this:
[[temperature on December 10, temperature on December 11...], [...], [...]]
With each element corresponding to a different 1km square. So far, I have the following code to find the temperature of a specific point:
var dataset = ee.ImageCollection('MODIS/006/MOD11A1')
.filter(ee.Filter.date('2018-12-10', '2018-12-23'));
var landSurfaceTemperature = dataset.select('LST_Day_1km');
var landSurfaceTemperatureVis = {
min: 13000.0,
max: 16500.0,
palette: [
'040274', '040281', '0502a3', '0502b8', '0502ce', '0502e6',
'0602ff', '235cb1', '307ef3', '269db1', '30c8e2', '32d3ef',
'3be285', '3ff38f', '86e26f', '3ae237', 'b5e22e', 'd6e21f',
'fff705', 'ffd611', 'ffb613', 'ff8b13', 'ff6e08', 'ff500d',
'ff0000', 'de0101', 'c21301', 'a71001', '911003'
],
};
Map.setCenter(-6.746, 46.529, 10);
Map.addLayer(landSurfaceTemperature, landSurfaceTemperatureVis, 'Land Surface Temperature');
//print(landSurfaceTemperature);
// map over the image collection and use server side functions
var tempToDegrees = landSurfaceTemperature.map(function(image){
return image.multiply(0.02).subtract(273.15);
});
// print and add to the map
//print('image collection in temp in degrees', tempToDegrees);
Map.addLayer(tempToDegrees, {min: -20, max: 40, palette: landSurfaceTemperatureVis.palette}, 'temp in degrees');
var point = ee.Geometry.Point(coordinates);
var data = dataset.select('LST_Day_1km').get('LST_Day_1km');
var dataN = ee.Number(data);
print(dataN);
When I print dataN, the output is null. I want to print the temperature in degrees. How can I do this?
wind nexus
skill_set1 = [
{ 'name': 'Python', 'level': 3 },
{ 'name': 'C#', 'level': 4 },
{ 'name': 'Java', 'level': 2 }
]
skill_set2 = [
{ 'name': 'Python', 'level': 5 },
{ 'name': 'C#', 'level': 1 },
{ 'name': 'Java', 'level': 5 }
]
users1 = [
{
'name': 'john',
'skills': [
{ 'name': 'Python', 'level': 3 },
{ 'name': 'C#', 'level': 5 },
{ 'name': 'Java', 'level': 3 }
]
}
]
I have a database that simplified comes down to this, with those kind of values and strctures, when i search for users who have at least the skills and level equal or higher than what is defined at skill_set1, i should get the object for john, but when i search using skill_set2 i should get no user object
i cant figure out a good way to filter/search/query using the name of skill and and making sure that the users level is equal or higher to the wanted level for that skill
wind nexus
i managed to do a crude inefficent solution like this:
for skill in skill_set1:
for user in users1:
user_skills={}
for skillB in user['skills']:
user_skills[skillB['name']] = skillB['level']
if skill['name'] in user_skills:
if skill['level'] <= user_skills[skill['name']]:
print("match "+ skill['name'])
But i cant do that when there are hundreds of user records to go thru
severe bridge
INSERT INTO shop_order
VALUES ( '123123E', '123X', 'XER342', 1, '5001 Summer Street', '2017-02-14'), ( '456456W', '456Y', 'XY3K19', 3, '2000 Winter Road', '2017-10-02'), ( '789789G', '789T', 'X2Z34T', 2, '666 Spring Avenue', '2017-06-06');
how do i format this so that it works on sql lite?
obviously each ( ) is 1 entry
dull scarab
as in multiple orders ?
Write out the sql for 1 order with prepared statements sql INSERT INTO shop_order VALUES(?, ?, ? ?, ?, ?)
Build a list of tuples with all the entries you want to insert
data = [
( '123123E', '123X', 'XER342', 1, '5001 Summer Street', '2017-02-14'),
(...),
...
]```
And use the executemany method which you pass the query and the data list
```py
# connect (and get a cursor if you need one)
cursor.executemany(sql_query, data)```@severe bridge
severe bridge
yea thx
wind nexus
is there a good way to query with sqlalchemy when i want to get objects that have all the values that are in a dict or a list on a many to many relationship
talent_skills = db.Table('talent_skills',
db.Column('talent_id', db.Integer(),
db.ForeignKey('talent.id')),
db.Column('skill_id', db.Integer(), db.ForeignKey('skills.id')))
class Talent(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255))
skills = db.relationship('Skills', secondary='talent_skills',
backref=db.backref('talents', lazy='dynamic'))
class Skills(db.Model):
id = db.Column(db.Integer(), primary_key=True)
skill_name = db.Column(db.String(255))
level = db.Column(db.Integer)
if i have a list like this:
needed = [{ "skill_name":"Python", "level":3}{ "skill_name":"C#", "level":2}]
i want to query from the database that returns the users who have all those skills, but may also have more than just those
something like user.skills contains all(needed)
but that kind of query doesnt work
craggy coyote
Something something like
skill_names = needed.keys()
Skills.query.filter(Skill.name.in_(skill_names))
the method is actually in underscore. the underscore is not showing
wind nexus
list object has no attribute keys,
do i need to make a for item in needed loop?
craggy coyote
also messed that up. you'd need the name of all the skills to be in a list.
yeah basically
here's an example of in_ being used
craggy coyote
oh yeah so you'd have to take users, join skills and filter the skills using the in clause.
wind nexus
how do i do the joining?
craggy coyote
you should probably read a tutorial on sqlalchemy. lot of good videos out there for basics.
wind nexus
i have been trying out the joining stuff, but cant wrap my head around it,
i tried Talent.query.join(Skills).#rest of the query, but keep getting error Can't find any foreign key relationships between 'talent' and 'skills'
wind nexus
@craggy coyote , This seems to work:
db.session.query(Talent).join(Skills,Talent.skills).filter(Skills.skill_name.in_(skill_names)).all()
now i just need to figure out if i can somehow have it filter with that as well as the for every skill Talent.skills.level >= x
is i understand i cant have a for loop in the query filters, right?
stable violet
for sqlite3, you have to always define your column right? you can't do
SELECT ? FROM TABLENAME WHERE....
stable violet
Alrighty thank you, was trying to stick with DRY but won't be able to do that here it looks like
torn sphinx
I have 2 tables: user, and room. I would like to make another table dedicating it to Teachers. Is it the right approach or not?
So how could I make it different?
Since I have a boolean set in the user class. isTeacher
wind nexus
Ok, This is the solution i now hacked together for finding users who have all required skills and skill level:
def find_matches(startup):
open_jobs = startup.job_offerings
for job in open_jobs:
job_skills = dict([(x.skill_name,x.level) for x in job.skills])
matches = db.session.query(Talent).join(Skills,Talent.skills).filter(Skills.skill_name.in_(list(job_skills.keys()))).all()
for user_match in matches:
user_skills = dict([(x.skill_name,x.level) for x in user_match.skills])
matching_skills = list(compare_intersect(list(user_skills.keys()), list(job_skills.keys())))
qualified_skills = []
for y in matching_skills:
if user_skills[y] >= job_skills[y]:
qualified_skills.append(y)
if set(matching_skills) == set(qualified_skills):
print("Match made")
def compare_intersect(x, y):
return frozenset(x).intersection(y)
I wonder if there is some things that could be improved on it?
sonic kindle
How can I select a specific image from an imageCollection with the Google Earth Engine JavaScript API?
dull scarab
@torn sphinx you could have 2 tables if theres any relations youd like to make between teachers and students, else just having the isTeacher flag in a people table sounds fine as well
wind nexus
anyone online who can help me with database desgin?
#= empty space, used as a filler
User------------------------|
| ############## |
Talent ########### Startup
| ############## |
| ############# Jobs
| ############# |
L_______Skills_________J
Both Talent and Job have a Many to Many relation to Skills
I run the code in few messages above to find matching users for each job, But how should i store the matches in the database
would something like this be good:
Both Talent and Job have a bidirectional One to Many Relationship to table called Matches
Would that allow me to go from User-->Talent-->Matches[x]-->Job with a query of some sort
buoyant breach
It looks like you are doing JOIN in your python code.
What kind of backend do you use as your database, @wind nexus ?
wind nexus
Its a azure sql server (mssql)
torn sphinx
@dull scarab yeah I used jinja and current_user to do the job. Thank you
wind nexus
@buoyant breach hmm, im using sqlalchemy on my backend and the db server is mssql *
buoyant breach
@wind nexus well then perform join using sqlalchemy. =]
modest spire
I've got a table of commands for my discord bot. How would I paginate it and split it into 10 commands per page?
craggy coyote
Alright guys got a problem this time. Using subquery load and can't seem to figure out why my rows aren't matching up (initial query and subquery don't match up as in first query returns different results than second query).
Essentially my sqlalchemy query looks like this (Users have many comments)
Comment.query.join(Comment.user).options(subqueryload(Comment.user).distinct(User.id).order_by(User.id, Comment.id).limit(10)
Now if I only order by User.id then the subquery loads the proper data. However, with the above query, the subquery is returning the wrong data.
Any thoughts?
Link to docs on subqueryloading and importance of ordering;
https://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#the-importance-of-ordering
dull scarab
@modest spire That sounds like a question for the the d.py channels?
Also why are you storing your commands in a db?
cunning ginkgo
File "C:\Users\Paws\AppData\Local\Programs\Python\Python37-32\lib\site-packages\discord\ext\commands\core.py", line 62, in wrapped
ret = await coro(*args, **kwargs)
File "C:\Users\Paws\Desktop\pawbot\cogs\economy.py", line 20, in leaderboard
embed.add_field(name="**:dizzy: Leaders**", value=f""":first_place: | {self.bot.get_user(row[0][0]).mention}: **{row[0][1]}** \n:second_place: | {self.bot.get_user(row[1][0]).mention}: **{row[1][1]}** \n:third_place: | {self.bot.get_user(row[2][0]).mention}: **{row[2][1]}** """)
AttributeError: 'NoneType' object has no attribute 'mention'
Command:
@commands.command(aliases=["lb", "leaders"])
@commands.cooldown(1.0, 10.0, commands.BucketType.user)
async def leaderboard(self, ctx):
"""Shows global leaderboard"""
query = "SELECT * FROM userbal ORDER BY money DESC LIMIT 3;"
row = await self.bot.db.execute(query)
embed=discord.Embed(title="The Richest People", color=random.randint(0x000000, 0xFFFFFF), timestamp=ctx.message.created_at)
embed.add_field(name="**:dizzy: Leaders**", value=f""":first_place: | {self.bot.get_user(row[0][0]).mention}: **{row[0][1]}** \n:second_place: | {self.bot.get_user(row[1][0]).mention}: **{row[1][1]}** \n:third_place: | {self.bot.get_user(row[2][0]).mention}: **{row[2][1]}** """)
Does anyone know how to fix this?
Like, it will get the stuff
But it won't on the code
nvm, got it to work with fetch rather than execute
hazy mango
What's the quickest database to use for uploading images that can then be opened in the database? Im currently using Google Drive API but I feel there must be a quicker way as it's rather slow.
terse stump
not sure what you are asking about @hazy mango what are you trying to do?
I do not recommend storing image data in a database, it is better to store the image to the filesystem and make a file and path reference in the database
hazy mango
Essentially I have a while 1 loop generating graphs, and I don't want to store to my PC as it really slows my PC, making it take longer to generate new graphs
@terse stump
That's why I'm storing it to my drive atm, but I was wondering if there's a quicker way
(what I'm literally doing atm is storing graph to my PC, uploading it, then removing graph from my PC as I can't upload without first saving it to my PC)
terse stump
from a database perspective, it is unwise to store binary data in a database since it will cause the database to get really large really quick. I have done this in the past and I had to stop due to this limitation. Now I save images and video on the file system and running the database along side it. If you where to save binary data I would look into mongodb as an alternative.
hazy mango
Okay, thanks
pure scroll
why mongo over any bucket systems such as s3 ?
pure scroll
I think it depends on your usecase, if you don't; have any compliance constraints I would go for s3 or similar, since you would benefit from their CDN and all the heavy traffic would go through their networks.
All your system would have to do it is to only keep metadata of your binaries and forward download requests to s3.
craggy coyote
Hey so trying to construct a query in sqlalchemy that'll do the following,
grab all the users in the database that have an email that's the same as the email of another user in the database.
pure scroll
select * from users
having count(email) > 1
groupby email;
not sure about having/groupby order might be opposite
craggy coyote
ty soosleek. Yeah i got it with raw sql. sqlachemy is bothering me though 😃 will read docs
pure scroll
well it's the same with dsl:
session.query(User).group_by(User.email).having(func.count(User.email) > 1)
craggy coyote
yep that was it and just got it. Ty muchly
hazy mango
file_metadata = {'name': 'photo{}.jpg'.format(to_find)}
media = "C:/Users/*user*/Documents/DesktopFiles/Python/Google Drive API/p{}.png".format(to_find)
file = service.files().create(body=file_metadata,
media_body=media,
fields='id').execute()
print('File ID: %s' % file.get('id'))
os.remove("C:/Users/*user*/Documents/DesktopFiles/Python/Google Drive API/p{}.png".format(to_find))
to_find += 1
print("Uploaded\n")```Is there any reason why some files upload twice instead of the once? The weird thing is, p666 uploaded after p665 AND after 812 which then gives more anomalies *(see image)*you called it twice?
hazy mango
It shouldn't call twice though
Because I call it in a loop, increment to_find every time it runs
to_find = 1
while 1:
file_metadata = {'name': 'photo{}.jpg'.format(to_find)}
media = "C:/Users/*user*/Documents/DesktopFiles/Python/Google Drive API/p{}.png".format(to_find)
file = service.files().create(body=file_metadata,
media_body=media,
fields='id').execute()
print('File ID: %s' % file.get('id'))
os.remove("C:/Users/*user*/Documents/DesktopFiles/Python/Google Drive API/p{}.png".format(to_find))
to_find += 1
print("Uploaded\n")```Excuse the random indentation xD
But it should never upload the same file, because when the function next runs, to_find has been incremented, thus the file name has increment so it shouldn't be able to select the same file
And assuming it does repeat, why does it repeat 666, 777 and 805-812, after reaching 812 for the first time?
It's so weird
I don't have a clue what's happening
hazy mango
Anyone have any theories as to what's happening...?
wispy fable
You should post the entire program in case the problem lies elsewhere.
Sometimes issues are in places that you may have not considered.
Seeing that he is offline, I'll go ahead with my question:
def read_row_manual(filters, table, db):
conn = sqlite3.connect(db)
curr = conn.cursor()
curr.execute(f'SELECT * FROM {table} WHERE {condition}')
curr.close()
conn.close()
def remove_row(row, table, db, limit):
conn = sqlite3.connect(db)
curr = conn.cursor()
curr.execute(f'DELETE * FROM {table} WHERE {condition}')
curr.close()
conn.close()```Is this an efficient way of doing things?
Or should I be opening just one single connection at the start of the program and then instance and close multiple cursors and then close the connection when the program terminates?
wispy fable
This definitely isn't urgent, as my application will be relatively low-traffic, feel free to ignore me and answer the guy above me if you can.
wind pelican
no reason to close the sqlite 'connection' really, might as well open it at the start and close it at the end of your program
pure scroll
yeap, you can keep connection open always, that would save you lots of time in the long run
also what is condition and where it comes from?
because using string formatting with user input in queries could lead to sql injections
wispy fable
You're right, I probably shouldn't have that function.
Well, I didn't intend on using it with user input, it was moreso of a helper function so that I'm not always rewriting the same SQL statements.
pure scroll
better use built in query formatting
in addition to that is you have either of those statements used frequently you could use prepared statements on db
that makes it run much faster
vestal mango
I run this command:
query.exec('INSERT INTO texts' \
'(ID, description)' \
'VALUES ({}, "abc")' \
.format(id))```
and my target database is edited just fine. "id" is an integer and the 2nd column in the db requires text as input.
If I do this:
```python
query.exec('INSERT INTO texts' \
'(ID)' \
'VALUES ({})' \
.format(id))```
it just adds the id, with no problem.
However, if I do any of the following:
```python
#desc = "abc"
query.exec('INSERT INTO texts' \
'(ID, description)' \
'VALUES ({}, {})' \
.format(id, desc))```
```python
query.exec('INSERT INTO texts' \
'(ID, description)' \
'VALUES ({}, {})' \
.format(id, "abc"))```
nothing is added to the database.
(Btw I use slashes for a reason that's about the rest of my program, they don't cause any problem)
Why do I have this problem?I use Python 3.5 and PyQt5
(If this helps at all, when I open the database, the box where "abc" is supposed to be inserted shows the NULL word, but since it accepts direct lone strings idk if it matters ofr the ways I want to insert one)
ionic pecan
never use str.format or anything similar to insert values from code into the database
use parameter substitution - i'm not sure which database driver you're using, they all have some different form of syntax
vestal mango
I'll have a look at it when I can
Why is it bad to use str.format ?
Also, I use SQLITE (for sqlite3)
dull scarab
Because of sql injections
Which, if a user has any control of the input for this query, then they can tailor query to do what they want
This can be done by preemptively end your query and start their own, or insert additional conditions to trick yours
Like if your query is py sql = """ SELECT * FROM table WHERE username = "{}" AND password= "{}"; """ query = sql.format(username, password)
So if password is, for instance " or "1"="1 then it will always give you the entry for username given, even if password is "wrong"
Alternatively they can insert malicious queries that could alter your table, show the entirety of it, or even drop it completely. Like is password was "; DROP table where ""="
@vestal mango
vestal mango
The target database is free for everyone to edit, there's nothing like a password or hidden detail to worry about
So I want simply to edit the database
@dull scarab
dull scarab
The password was a n example of a query you would give it
Any field that a user can give without sanitizing can let them delete your database, fetch any row, alter it etc.
If someone enters a string to drop tables as desc, then your entire database is dropped
vestal mango
In my case, there's nothing to worry about. The database is part of a game and everyone who owns the game has a copy of it as well and there is no point to edit it, except to add records to make it better just for themselves; the changes don't affect anyone else.
Also noone can have access to my code; just the utility I'm preparing
so all I need is a way to insert records sucessfully, like with my case at the start. Currently I'm looking for alternatives and hope to see one that suits me, but if there's a fix for my current situation that would suffice too 🙂
torn sphinx
So I guess this is a database question. I'm trying to get a list of all custom commands in a server. But it's only getting one from the table not getting all items
@tag.command(aliases=['list'])
async def llist(self, ctx):
main = sqlite3.connect('main.sqlite')
cursor = main.cursor()
cursor.execute("SELECT name, content FROM tags WHERE guild_id = '{}'".format(ctx.message.guild.id))
result = cursor.fetchone()
if result is None:
await ctx.send('There are no tags yet.')
else:
await ctx.send(f'Here is the list of tags in this guild!\n{list(result[0])}')
main.commit()
cursor.close()
main.close()```That's my code for it ^
Ping if anyone can help out
dull scarab
Dont use str formatting for your query (read my response a bit further up)
And ofc it only returns one, you specifically said fetchone()
Believe what youre after if fetchmany or something in that ballpark, i cant recall if thats the correct one, but you get the idea
torn sphinx
oh true
Yea idk why I thought fetchone was the thing to use
kinda had a crazy last night still trying to get my head on straight XD
copper sphinx
This is probably related to databases so, here goes.
What am I doing wrong?
async def tutkinta(self, ctx, *, member: discord.Member):
"""Takes away user's roles and put's them to tutkinnassa role. After executing it again, re-applies their
roles. """
tutkinnassa_role_id = 511599920533471242
member_roleids = (f"{a.id}" for a in member.roles)
member_role_ids_json = json.dumps(member_roleids)
# Making the database connection.
connection_details = config.postgresql
conn = await asyncpg.connect(connection_details)
await conn.execute(f'INSERT INTO messis_python_db.public."UserRoles" VALUES ({member.id}, {member_role_ids_json})')
#print(f'INSERT INTO UserRoles(UserID, Roles) values ({member.id}, {member_role_ids_json})')
await conn.close()
await ctx.message.delete()
Traceback:
In tutkinta:
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\venv\lib\site-packages\discord\ext\commands\core.py", line 62, in wrapped
ret = await coro(*args, **kwargs)
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\cogs\mod.py", line 31, in tutkinta
member_role_ids_json = json.dumps(member_roleids)
File "C:\Users\Sami\AppData\Local\Programs\Python\Python36\lib\json\__init__.py", line 231, in dumps
return _default_encoder.encode(obj)
File "C:\Users\Sami\AppData\Local\Programs\Python\Python36\lib\json\encoder.py", line 199, in encode
chunks = self.iterencode(o, _one_shot=True)
File "C:\Users\Sami\AppData\Local\Programs\Python\Python36\lib\json\encoder.py", line 257, in iterencode
return _iterencode(o, 0)
File "C:\Users\Sami\AppData\Local\Programs\Python\Python36\lib\json\encoder.py", line 180, in default
o.__class__.__name__)
TypeError: Object of type 'generator' is not JSON serializable
it's not about DBs,
but you problems is
member_roleids = (f"{a.id}" for a in member.roles) that is not a tuple or a list, that is a generator expression
and the error you get basically explains it, you can't serialise generator.
most likely you want to use list comprehension to generate a list instead, or cast it to the list before calling json.dumps
copper sphinx
INSERT INTO messis_python_db.public."UserRoles" VALUES (157970669261422592, ["446419809668694016", "521402027826348052", "525708208812326913", "511460929297186831", "504759682984968201", "477405784427462656"])
What is wrong with using this? My conn.execute does not like it.
Error:
In tutkinta:
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\venv\lib\site-packages\discord\ext\commands\core.py", line 62, in wrapped
ret = await coro(*args, **kwargs)
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\cogs\mod.py", line 37, in tutkinta
await conn.execute(f'INSERT INTO messis_python_db.public."UserRoles" VALUES ({member.id}, {member_role_ids_json})')
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\venv\lib\site-packages\asyncpg\connection.py", line 273, in execute
return await self._protocol.query(query, timeout)
File "asyncpg\protocol\protocol.pyx", line 316, in query
PostgresSyntaxError: syntax error at or near "["
patent glen
don't use f-strings like that for database stuff
conn.execute('INSERT INTO messis_python_db.public."UserRoles" VALUES ($1, $2)', (member.id, member_role_ids_json))```What do you mean by that?
patent glen
like, the fundamental reason for the problem is that your json string isn't getting 'quotes' put around it. But just adding them isn't the right way, there's functionality built into the database for passing the object directly instead of just adding it into the insert statement
oh wait, asyncpg
edited above, pg needs $1 $2 for parameters
copper sphinx
Now what?
In tutkinta:
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\venv\lib\site-packages\discord\ext\commands\core.py", line 62, in wrapped
ret = await coro(*args, **kwargs)
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\cogs\mod.py", line 37, in tutkinta
await conn.execute('INSERT INTO messis_python_db.public."UserRoles" VALUES ($1, $2)', (member.id, member_role_ids_json))
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\venv\lib\site-packages\asyncpg\connection.py", line 275, in execute
_, status, _ = await self._execute(query, args, 0, timeout, True)
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\venv\lib\site-packages\asyncpg\connection.py", line 1414, in _execute
query, args, limit, timeout, return_status=return_status)
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\venv\lib\site-packages\asyncpg\connection.py", line 1422, in __execute
return await self._do_execute(query, executor, timeout)
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\venv\lib\site-packages\asyncpg\connection.py", line 1444, in _do_execute
result = await executor(stmt, None)
File "asyncpg\protocol\protocol.pyx", line 178, in bind_execute
File "asyncpg\protocol\prepared_stmt.pyx", line 120, in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg
InterfaceError: the server expects 2 arguments for this query, 1 was passed
HINT: Check the query against the passed list of arguments.
ok sorry that's asyncpg being different again 🙄
conn.execute('INSERT INTO messis_python_db.public."UserRoles" VALUES ($1, $2)', member.id, member_role_ids_json)```
My mistake on the extra parentheses first time i told youyeah... asyncpg doesn't like dbapi, make of that what you will
patent glen
anyway, pretty much any database library will provide some way to do this, but they all have their own little quirks like this
copper sphinx
Okey good to know and thanks. :)
copper sphinx
How would I check if database has a entry in specific columm and return true if so?
patent glen
i'm not sure what exactly you mean by that
copper sphinx
check_query = await conn.execute(
'SELECT count(messis_python_db.public."UserRoles"."UserID") from "UserRoles" where messis_python_db.public."UserRoles"."UserID" = $1', member.id)
if check_query == '0':
# Do things
Something like that.
patent glen
print the value of check_query and see what it is, I think execute will give you something that includes the number of rows
or you could use fetch instead of execute and check for an empty list
patent glen
wait nevermind, you're using count() in the query so it will always be one row
just use fetch instead of execute, and it'll return the count in the row
probably something like [(0,)] print to be sure
copper sphinx
Are you aware that I'm using asyncpg?
patent glen
fetch is an asyncpg specific command
copper sphinx
Oh.
patent glen
(in normal db libs you'd just use execute)
copper sphinx
So then how do I check it better?
patent glen
Sorry, missed you reply. Did you try using fetch?
just trying to keep track of what you've tried and what you're currently running into
copper sphinx
How do I get my roles from the db now? I'm confused.
async def tutkinta(self, ctx, *, member: discord.Member):
"""Takes away user's roles and put's them to tutkinnassa role. After executing it again, re-applies their
roles. """
tutkinnassa_role_id = 511599920533471242
member_roleids = (f"{a.id}" for a in member.roles if a.id != ctx.guild.id)
member_roles_list = list(member_roleids)
member_role_ids_json = json.dumps(member_roles_list)
# Making the database connection.
connection_details = config.postgresql
conn = await asyncpg.connect(connection_details)
check_query = await conn.fetch(
'select messis_python_db.public."UserRoles"."userid" from "UserRoles" where messis_python_db.public."UserRoles"."userid" = $1', member.id)
if check_query == []:
await conn.execute('INSERT INTO messis_python_db.public."UserRoles"("userid", "roles") VALUES ($1, $2)', member.id, member_role_ids_json)
try:
for role in json.loads(member_role_ids_json):
#print(role)
await member.remove_roles(ctx.guild.get_role(int(role)))
await member.add_roles(ctx.guild.get_role(int(tutkinnassa_role_id)))
await ctx.send(f'Kaikki roolit poistettu käyttäjältä {member.name} ja lisätty tutkinnassa rooli.')
# Database connection cleanup.
await conn.close()
except discord.Forbidden:
await ctx.send(f'Roolin/roolien poisto epäonnistui.')
else:
restore_query = await conn.execute(
'SELECT roles from "UserRoles" where messis_python_db.public."UserRoles"."userid" = $1', member.id)
for role in json.loads(restore_query):
print(role)
await ctx.message.delete()
I think you need fetch again
which returns a list of rows, so you'll need to get the json out of that before json.loads
@copper sphinx
[Also, if you're relying on presence of the row in the database to decide what to do, you'll need to delete the rows afterward if you're treating this as a toggle state]
copper sphinx
The restore_query when I print it gives me: [<Record roles='["521402027826348052", "504759682984968201"]'>]
And somehow it's not getting past the sql query.
patent glen
ok so you probably wnat restore_query[0].roles
tbh i'd probably rework that to run the query only once instead of doing the count
e.g. ```py
conn = await asyncpg.connect(connection_details)
roles_query = await conn.fetch(
'SELECT roles from "UserRoles" where messis_python_db.public."UserRoles"."userid" = $1', member.id)
if roles_query == []:
...
else:
roles = json.loads(roles_query[0].roles)
for role in roles:
print(role)
Hmm.
formal pumice
Hi all. I'm struggling to get my environment set up. I'm trying to connect to a Google Cloud instance of MySQL. I want to utilize VS Code and SQLAlchemy but I can't establish a connection for some reason. I feel like I've tried everything. If someone is willing to help I can provide more details. Thanks in advance.
copper sphinx
roles_query = await conn.fetch(
'SELECT roles from "UserRoles" where messis_python_db.public."UserRoles"."userid" = $1', member.id)
print("Test")
if roles_query == []:
print("It's empty.")
else:
roles = json.loads(roles_query[0].roles)
print("Test 2")
for role in roles:
print(role)
@patent glen for some odd reason, it's not reaching to test 2 print.
Python 3.7 maybe a reason?
patent glen
are you getting an error now?
copper sphinx
No. No errors nada
copper sphinx
Exactly.
patent glen
its not empty either?
copper sphinx
No, there's also a print, which is not printed.
patent glen
print(roles) before the loads line
anyway i meant that code to slot into your existing function including all the stuff you already had in the == [] section
just didn't type it all because laziness
copper sphinx
It is with all the other things.
patent glen
ok
patent glen
print(roles_query[0])
print(roles_query[0].roles)
copper sphinx
print(roles_query[0].roles) outputs nothing
patent glen
ok oops this was just my dumb assumptions about asyncpg again
bet it should be roles_query[0]['roles']
or maybe roles_query[0][0]
copper sphinx
Oh, that might do the trick.
roles_query[0]['roles']did the trick
It works now like it should. :)
proven grove
sql_query = "SELECT * FROM warnings WHERE id = %s OR uid = %s OR member = %s;"
await crsr.execute(sql_query, (id, id, id))
It's giving Warning: Truncated incorrect DOUBLE value: '61-78179', how can I fix it?
wind pelican
looks like your uid is a string but your sql schema expects a double
specifically a string that cant be translated to just a number
wind pelican
shouldnt, I have done that before. What does your warnings schema look like? you might also put some debugging print() s in there
like right before this line do
print(id)
print(type(id))```def should check your schema though
coral yarrow
Hey everyone! I've been having some trouble with a program using SQLite3 and Tkinter. I've outlined my problem here: https://www.reddit.com/r/learnpython/comments/aee5fd/a_problem_with_sqlite3_and_tkinter/
pure cypress
You should format your code properly. It's a giant mess right now.
coral yarrow
I'm sorry, I'm not very good at formating. Would you like me to put it on pastebin?
I think they have auto-format
Ok I reformatted it on the Reddit post
dry patio
ok so I have this
conn.execute('SELECT * FROM tickets WHERE channel_id={}'.format(id))
for row in conn.fetchall():
ticket = row```And
I try to print tickets but I get nothing, not even an error...
(Yes the id given is the exact one as channel_id)
dry patio
Please tag me in case I'm afk 😅 (yes i still can't figure it out)
terse stump
your code does not show you printing out anything @dry patio
carmine heart
@dry patio Don't use format for queries, use the ? parameter substitution
swift frigate
and your loop should be for row in conn.execute('SELECT * FROM tickets WHERE channel_id={}'.format(id)):
terse stump
yeah SQL injection is always a thing to think about
carmine heart
I don't think it should break the code, but just wanted to note that
dry patio
I never had a problem with using the format so...
Also I tried this py for row in conn.execute('SELECT * FROM tickets WHERE channel_id={}'.format(id)): print(row)
swift frigate
try this: for row in conn.execute('SELECT count(*) FROM tickets WHERE channel_id={}'.format(id)): print(row)
it should print the number of rows where channel_id = id
also, you shouldnt use id as a variable name, its a built in python function
swift frigate
try removing the where clause
ionic pecan
„i never had a problem with“ is horrible reasoning. If you sell your code to a customer and he gets a segfault, are you just gonna tell him you „never had a problem with it“? using str.format and other formatting methods is vulnerable to sql injection and as soon as someone runs a query thats problematic you‘re gonna „have a problem with it“
swift frigate
one thing at a time @ionic pecan 😃
dry patio
Nop not printing anything...
Leme share more code
@commands.command()
@commands.has_any_role('Management', 'Sales Representatives')
async def setrep(self, ctx, channel):
management = discord.utils.get(ctx.guild.roles, name='Management')
database = sqlite3.connect('botdata\\databases\\ticketinfo.db')
conn = database.cursor()
for row in conn.execute('SELECT count(*) FROM tickets WHERE channel_id={}'.format(channel)):
print(row)```opps leme fix the syntax
(i changed id to channel to see if it made a difference)
swift frigate
try for row in conn.execute('SELECT * FROM tickets'): print(row)
swift frigate
yeah
it will print all rows
did the channel id that was printed match what you were looking for?
swift frigate
ah
ok i see
for row in conn.execute('SELECT * FROM tickets WHERE channel_id=\'{}\''.format(channel)):
or for row in conn.execute("SELECT * FROM tickets WHERE channel_id='{}'".format(channel)):
since channel id is a string, it needs to be surrounded by single quotes in the query
dry patio
I just tried this
for row in conn.execute('SELECT * FROM tickets WHERE channel_id=?', (channel)):
print(row)```Idk if it's correct...
buoyant breach
this form looks correct.
buoyant breach
Don't format values into the query strings.
dry patio
But still didn't print 😦 😭
Also @swift frigate the id is a string when it's passed, since it's a discord message.
swift frigate
for row in conn.execute('SELECT * FROM tickets WHERE channel_id=?', (channel, )):
dry patio
uhhh
swift frigate
the parameters need to be a sequence, (channel) is the same as channel
(channel, ) is a tuple
dry patio
THAT WORKED ❤
TTHANK YOU
Also could you please explain the difference between ? and .format and why does .format make it vulnerable for sql injections?
swift frigate
when you use ? , the SQL driver will make sure that the value passed in is properly escaped
its a feature of the SQL driver
dry patio
ahhh, ok thank you 😄
torn sphinx
Oh yeah, why are you doing that, lol, that leaves your queries open to injection attacks
Oh, you mentioned that already 
vestal mango
Oh, there are actually so many people that have the .format VS ? confusion
stray night
Can anyone help me... How to connect sql server through the sqlalchemy..??i want to connect remot server
copper sphinx
Code: https://paste.pythondiscord.com/janesosexa.py
Traceback:
Ignoring exception in command tutkinta:
Traceback (most recent call last):
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\venv\lib\site-packages\discord\ext\commands\core.py", line 62, in wrapped
ret = await coro(*args, **kwargs)
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\cogs\mod.py", line 53, in tutkinta
async with self.bot.pool.acquire() as conn:
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\venv\lib\site-packages\asyncpg\pool.py", line 762, in __aenter__
self.connection = await self.pool._acquire(self.timeout)
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\venv\lib\site-packages\asyncpg\pool.py", line 601, in _acquire
self._check_init()
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\venv\lib\site-packages\asyncpg\pool.py", line 722, in _check_init
raise exceptions.InterfaceError('pool is not initialized')
asyncpg.exceptions._base.InterfaceError: pool is not initialized
So how do I should be doing this?
Could someone please explain how do I initialize a asyncpg database pool?
patent glen
@copper sphinx what code do you have now for setting up the pool (x out passwords if applicable)
copper sphinx
bot.py: bot.pool = asyncpg.create_pool(config.postgresql)
That config.postgresql has a dsn.
patent glen
you have to await it (which means it has to be done in a place where you can await it, which means i really need to know more about the overall structure of your bot)
copper sphinx
It's in on_ready event.
patent glen
ok
patent glen
ok
yeah i forgot about on-ready, i was worried i'd have to walk you through making a "create if it's not already created" pattern
copper sphinx
Indeed, that would have been annoying.
copper sphinx
Code: https://paste.pythondiscord.com/ufaruriway.py
ideas how I should be clean about the connections?
Traceback:
Ignoring exception in command add_users:
Traceback (most recent call last):
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\venv\lib\site-packages\discord\ext\commands\core.py", line 62, in wrapped
ret = await coro(*args, **kwargs)
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\cogs\mod.py", line 42, in add_users
await self.bot.pool.release(conn)
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\venv\lib\site-packages\asyncpg\transaction.py", line 72, in __aexit__
self._check_conn_validity('__aexit__')
File "C:\Users\Sami\PycharmProjects\Parempi-Messis-Bot\venv\lib\site-packages\asyncpg\connresource.py", line 38, in _check_conn_validity
'to the pool'.format(self.__class__.__name__, meth_name))
asyncpg.exceptions._base.InterfaceError: cannot call Transaction.__aexit__(): the underlying connection has been released back to the pool
Context manager as in what exactly?
dull scarab
with x as y: is a context manager
It has an init, enter and exit method which are called depending on where it is in the code. When you create it with with x() as y x() is the init
self.bot.pool.acquire() would call the init in your code,
enter is the function that returns the value for as y and exit is the cleanup part that is called when you exit the scope of the context manager
Iirc that is, been a while since I dug into how they work
copper sphinx
Okey.
delicate fieldBOT
oblique moss
hello, i was wondering if someone can help me the query line where i'm selecting sum from finances, I want to select the sum between todays date and date from 1 week ago. i've tried everything but it keeps coming up as blank. thanks in advance
this was also what i tried
buoyant breach
You can try to select terms from your WHERE clause one by one.
And make sure they return what you expect them to. And slowly build the whole expression.
vast field
What is the most used or best database for python? any suggestions
lofty quest
SQL definitely @vast field
lofty quest
SQLite is better for Discord Bots because it allows you to host it locally in a file, PostgreSQL is for large databases, I haven't touched MySQL that much
vast field
alright thx
pure scroll
there are no best/worst databases for language
you pick DBs for the tasks(usecases) but not for the language
spare geode
How do i create a dropdown list for a list of things a customer has brought?
hazy mango
import sqlite3 as sql
db = sql.connect('logins.txt')
cursor = db.cursor()
# Insert a table
cursor.execute('''CREATE TABLE IF NOT EXISTS logins(username TEXT unique,password TEXT)''')
username = input("Enter username:\n")
password = input("Enter password:\n")
cursor.execute('''INSERT INTO logins VALUES (?,?)''', (username,password))
db.commit()
rows = []
data = db.execute("select * from logins")
for row in data:
rows.append(list(row))
print(rows)
db.close()
```Why doesn't this save to the database? It inserts, but when I re-run the program all data is lost(@me upon response please)
patent glen
@hazy mango works fine for me
$ python3 tizzysaurus.py
Enter username:
test
Enter password:
test
[['test', 'test']]
$ python3 tizzysaurus.py
Enter username:
123
Enter password:
123
[['test', 'test'], ['123', '123']]
what kind of environment are you running it on
patent glen
probably not gonna be able to store database files between runs like that, you should install python on your own computer if you want to experiment with sql
hazy mango
Yeah, I have it, just I work on it at school and at home so use repl.it to keep code
yeah, it works fine when I download it - guess it's a limitation of repl.it
Also, the data stored is unreadable (probs because it's been compiled?) Is there a way to actually read it properly from the file?
patent glen
Well, you could always import sqlite3 in the interactive interpreter and do whatever to it
hazy mango
Interactive interpreter?
patent glen
other than that, there are other sqlite data browsers out there, or you could install sqlite itself for a sql command line (it's not hard to write one in python either)
the >>> prompt when you open python by itself
hazy mango
Ah, I see
Okay, thanks
data = db.execute("select * from logins")
rows = [list(row) for row in data]```And is this correct for the generator in the list?I want it to be mutable
That's why I change to a list
(I seem to remember tuples being immutable???)
patent glen
yeah they are, it just seemed unusual to me because usually you'd either just read the data or need to do something more interesting than just make a list
without knowing more about your design though i guess idk
hazy mango
TBH, I think it will eventually prove to be useless, but I'll leave it for now
Thanks btw :~)
hazy mango
import sqlite3 as sql
db = sql.connect('logins.txt')
cursor = db.cursor()
# cursor.execute("""DROP TABLE logins""")
# db.commit()
# Insert a table
cursor.execute('''CREATE TABLE IF NOT EXISTS logins(id INTEGER PRIMARY KEY, username TEXT unique,password TEXT)''')
mode = input("Enter mode:\n")
if mode == "update":
username = input("Enter username:\n")
password = input("Enter password:\n")
cursor.execute('''INSERT INTO logins VALUES (?,?)''', (username,password))
db.commit()
elif mode == "login":
data = db.execute('''select * from logins''')
logins = [list(row) for row in data]
print(logins)
username = input("Enter username:\n")
if username in dict(logins):
password = input("Enter password:\n")
if dict(logins)[username] == password:
print("Logged in")
else:
print("Invalid Passowrd")
else:
print("Invalid Username")
db.close()
How do i get it to enter the correct ID?
Currently the update doesn't work as it says I need to provide 3 arguments (id, username, password) and I'm only providing 2 (username, password)
(@me upon response please)
deft badge
i mean
it makes sense
you have created a table with id, username and password but you are only giving a username and password on creation @hazy mango
deft badge
do you want like, incremental IDs?
deft badge
hm ok so
it looks like sqlite will do this automatically if you do something like
let me just read some docs
i wonder why it is not doing what it should
deft badge
yeah, I'm reading it on the sqlite docs as well
Ah okay I think I might have it?
try this
cursor.execute('''INSERT INTO logins(id, username, password) VALUES (NULL, ?, ?)''', (..., ...))
if you explicitly set it to NULL maybe sqlite will get the kick in the ass that you want a unique ID
well, auto-ID, it isn't unique
hazy mango
cursor.execute('''CREATE TABLE IF NOT EXISTS logins(id INTEGER PRIMARY KEY unique, username TEXT unique, password TEXT)''')```This right for creating the table then?yeah looks like it
although
maybe not unique on the ID
the ROWID thing is not a unique thing
for example, if I make 3 items in my database
they will be assigned IDs 1, 2, 3
but I believe that if I delete 1
so say I delete 2
then I have 1, 3
it tries to create another 3
let me test it
hazy mango
ok, thanks
cursor.execute('''INSERT INTO logins(id, username, password) VALUES (NULL,?,?)''' , (username,password))```And does this look right for the insertion?yeah
deft badge
i was wrong lol
hazy mango
:p
logins = [list(row) for row[1:3] in data]```What's wrong with this? Says row isn't definedBut I only want the 2st and 3nd element
deft badge
logins = [list(row[1:3]) for row in data]
hazy mango
oh, I just put in wrong place?
deft badge
indeed
hazy mango
Yep, that works. Thanks :~)
deft badge
what you did would be like
for row[1:3] in data:
list(row)
but you must do that inside the loop
for row in data:
list(row[1:3])
no problem :)
hazy mango
btw, would db and cursor be global by default or not?
I know some things from modules are global which is why I ask
spare geode
Sorry to interupt
deft badge
@spare geode they are not global no
wait actually
yeah they would be, read code wrong, whoops
hazy mango
u tagged wrong person?
deft badge
oh yep
hazy mango
:p
spare geode
but
import sqlite3
import time
class Customer:
def __init__(self,Username,Password,Spent=0,Brought,Postcode):
self.name = Username
self.password = Password
self.spent = Spent
self.Brought = Brought
self.Postcode = Postcode
def create_customer():
sql = """create table Customer
(self.name text,
self.password text,
self.spent real,
self.brought
primary key(Item))""".format(item)
with sqlite3.connect(db_name) as db:
cursor = db.cursor()
cursor.execute(sql)
db.commit()
how would i go around storing what a user has brought?
hazy mango
Okay, thanks xd
deft badge
they would be global because they are not defined inside a function (which has a separate, local, scope)
so uh
that code is a little messed up
spare geode
how so :3
deft badge
so you are a creating a table everytime you want to insert a customer
you are also doing this weird primary key(Item) thing
and then formatting a sql statement
spare geode
oh im just down to brought
thats code i used from previous code
lemme change it 2s
deft badge
you don't want to call it self.*
you can just call it name, password, spent
will brought be a list? @spare geode
spare geode
import sqlite3
import time
class Customer:
def __init__(self,Username,Password,Spent=0,Brought,Postcode):
self.name = Username
self.password = Password
self.spent = Spent
self.Brought = Brought
self.Postcode = Postcode
def create_customer():
sql = """create table Customer
(Username text,
Password text,
Spent real,
Brought
primary key(Username))""".format(item)
with sqlite3.connect(db_name) as db:
cursor = db.cursor()
cursor.execute(sql)
db.commit()
thats what im trying to figure out
deft badge
well what are you passing into customer
sqlite doesn't have an array storage method afaik
what you could do was create a BLOB
and use pickle to convert your python list into a sequence of bytes
spare geode
I have no idea how to do any of that
hazy mango
I got an exception when trying to except an exception 🤔
deft badge
>>> import pickle
>>> pickle.dumps(["item", "blah", "etc..."])
b'\x80\x03]q\x00(X\x04\x00\x00\x00itemq\x01X\x04\x00\x00\x00blahq\x02X\x06\x00\x00\x00etc...q\x03e.'
>>> data = _
>>> pickle.loads(data)
['item', 'blah', 'etc...']
pickle converts a python object into a sequence of bytes
but more importantly your code is still not going to work
spare geode
thats liek a tiny bit of it
deft badge
I know
and that bit is broken
also, for the record, never use .format or anything format-related on database queries
spare geode
Ah, that was for earlier when i was creating
I was creating 3 tables
So i used .format to do all 3
deft badge
don't ever do it
spare geode
oh ok
deft badge
you leave yourself vulnerable to sql injection attacks
hazy mango
If you want to catch an exception that's raised when catching an exception, do you just put two except blocks?
try:
logins[username] = password
except KeyError:
print("Key Error")
except:
print("Other Error")```Is this right @deft badge?this is where users could place dodgy input in your queries and modulate your database
@hazy mango no, you try/except the try/except
ideally fix the error though
hazy mango
I'm trying to comprehend how this would work 🤔
deft badge
try:
try:
logins[username] = password
except KeyError:
print("blah")
except:
print("lol")
try:
try:
logins[username] = password
except KeyError:
print("Key Error")
except:
print("Other Error")```Like this?yeah, ok
Thanks
deft badge
but that is a horrible solution and ideally you should handle the errors before they are raised
spare geode
How would i go around storing all the brought items from the user?
By making a table for each user?
deft badge
no
hazy mango
btw, for the data type (INTEGER, STRING) is BOOLEAN valid @deft badge?
deft badge
like I said, sqlite does not have an array type, so you would use something like pickle to serialise it into a blob
@hazy mango i'm not sure actually
let me try
hazy mango
Ok, thanks
deft badge
sqlite> CREATE TABLE blah(some_data INTEGER)
...> ;
sqlite> INSERT INTO blah (some_data) VALUES (True);
sqlite> SELECT * FROM blah;
1
sqlite> INSERT INTO blah (some_data) VALUES (false);
sqlite> SELECT * FROM blah;
1
0
so yes, you can store it i think
(that is for integer)
hazy mango
Can u use BOOLEAN when creating the table though?
CREATE TABLE blah(some_data BOOLEAN)
deft badge
oh yeah
hazy mango
k, thx
deft badge
sqlite> CREATE TABLE blah(some_data BOOLEAN);
sqlite>
And is that the correct spelling?
deft badge
yep
hazy mango
k, thx
To create the table I dopy cursor.execute('''CREATE TABLE IF NOT EXISTS logins(id INTEGER PRIMARY KEY unique, username TEXT unique, password TEXT, admin BOOLEAN)''')
To add the values I dopy cursor.execute('''INSERT INTO logins(id, username, password, admin) VALUES (NULL,?,?,False)''', (username,password))but that gives the error py sqlite3.OperationalError: no such column: False
deft badge
hmm
maybe format that with python
cursor.execute('''INSERT INTO logins(id, username, password, admin) VALUES (NULL,?,?,?)''', (username,password, False))
that's how I did it last time I did use booleans
hazy mango
Yep, thanks :3
deft badge
awesome
hazy mango
def login():
data = db.execute('''select * from logins''')
logins = [list(row[1:3]) for row in data]
print(logins)
if logins:
username = input("Enter username:\n")
if username in dict(logins):
password = input("Enter password:\n")
if dict(logins)[username] == password:
print("Logged in")
else:
print("Invalid Password")
else:
print("Invalid Username")
else:
print("There are no stored logins")```What's the quick way to check what's in data[id][3] #The value that's False in the message you sent aboveWould I have to redo the list comprehension?
@deft badge
deft badge
well for a start
logins = [list(row[1:3]) for row in data]
this might as well be removed
actually no it is somewhat functional keep it there
hazy mango
^
deft badge
but you will have to change it to accommodate the new row
hazy mango
I have it like that so that I have a dict of {"username":"password"}
hazy mango
Rather than {"ID":["username","password","admin"]} or whatever it would be
And idk what that is
spare geode
import sqlite3
import time
class Customer:
def __init__(self,Username,Password,Spent=0,Postcode):
self.name = Username
self.password = Password
self.spent = Spent
self.Brought = Brought
self.Postcode = Postcode
def create_customer():
sql = """create table Customer
(Username text,
Password text,
Spent real,
Postcode text,
primary key(Username))"""
with sqlite3.connect("CustomerDB.db") as db:
cursor = db.cursor()
cursor.execute(sql)
db.commit()
def new_customer():
Username=input("Username: ")
Password=input("Password: ")
Postcode=input("Postcode: ")
Spent = 0
sql = """INSERT INTO Customer
VALUES ('Username','Password',Spent,'Postcode')"""
with sqlite3.connect("CustomerDB.db") as db:
cursor = db.cursor()
cursor.execute(sql)
db.commit()
I think im confusing myself
deft badge
@hazy mango ok so
after you run sqlite3.connect
db = sqlite3.connect("...")
db.row_factory = sqlite3.Row
then when you want to get the users do something like
def login():
username = input("Enter username: ")
user_cursor = db.execute("SELECT * FROM logins WHERE username=?", (username,))
user = user_cursor.fetchone()
user_cursor.close()
if not user:
print("Could not find user")
return
password = input("Enter password: ")
if user["password"] == password:
print("Welcome back!")
else:
print("Incorrect password")
So would I do user["admin"] to get whether or not they're an admin?
deft badge
yep
deft badge
laters! best of luck with whatever you are making!
hazy mango
Thanks :~)
deft badge
how much Python experience do you have?
deft badge
ok, what about sqlite?
spare geode
Erm, Somewhat
deft badge
because this code just won't work
it is formatted incorrectly
you are creating tables for every customer
you aren't placing in your variables
spare geode
Because this creates the Customer DB
deft badge
yeah
spare geode
sql = """create table Customer
(Username text,
Password text,
Spent real,
Postcode text,
primary key(Username))"""
with sqlite3.connect("CustomerDB.db") as db:
cursor = db.cursor()
cursor.execute(sql)
db.commit()
sql = """create table Customer
(Username text,
Password text,
Spent real,
Postcode text,
Username text primary key)"""
with sqlite3.connect("CustomerDB.db") as db:
cursor = db.cursor()
cursor.execute(sql)
db.commit()
Ok so its just the bottom like thats differnt
deft badge
the next issue you will have is this
sql = """INSERT INTO Customer
VALUES ('Username','Password',Spent,'Postcode')"""
with sqlite3.connect("CustomerDB.db") as db:
cursor = db.cursor()
cursor.execute(sql)
db.commit()
i was doing it like this before
deft badge
yeah but that doesn't actually insert the data
spare geode
sql = """INSERT INTO {}
VALUES ('{}',{},'{}')""".format(table+"(Item,Price,Stock)", Item, Price, Stock)
sql = """INSERT INTO Customer
VALUES (?, ?, ?, ?)"""
with sqlite3.connect("CustomerDB.db") as db:
cursor = db.cursor()
cursor.execute(sql, (Username, Password, 0, Postcode))
db.commit()
yeah you pass in the values in execute where they are escaped
spare geode
ahhh ok
i have a feeling this is wrong:
class Customer:
def __init__(self,Username,Password,Spent=0,Postcode):
self.name = Username
self.password = Password
self.spent = Spent
self.postcode = Postcode
for the record, this is why you shouldn't use .format https://xkcd.com/327/
that looks right to me
spare geode
xD
deft badge
the only thing that irks me is that brought is the past participle of bring
but I think you are looking for bought maybe?
spare geode
Im removing the brought part
Ima save that for later
import sqlite3
import time
class Customer:
def __init__(self,Username,Password,Spent=0,Postcode):
self.name = Username
self.password = Password
self.spent = Spent
self.Brought = Brought
self.Postcode = Postcode
def create_customer():
sql = """create table Customer
(Username text,
Password text,
Spent real,
Postcode text,
Username text primary key)"""
with sqlite3.connect("CustomerDB.db") as db:
cursor = db.cursor()
cursor.execute(sql)
db.commit()
def new_customer():
Username=input("Username: ")
Password=input("Password: ")
Postcode=input("Postcode: ")
Spent = 0
sql = """INSERT INTO Customer
VALUES (?, ?, ?, ?)"""
with sqlite3.connect("CustomerDB.db") as db:
cursor = db.cursor()
cursor.execute(sql, (Username, Password, 0, Postcode))
db.commit()
I cant put the class inside a def can i?
deft badge
that won't work
you need to indent the contents of the create_customer function by 4 spaces
you can put the class in def but that isn't a good idea
spare geode
Because basically i want to check if the table Customer already excists at the start and if it does then carry on, if not create it
deft badge
do that in the start of the application
also, why are you defining these methods inside a class anyway?
they are all classmethods without the self argument
deft badge
You say that you are copying this from an old project but most of what you have written doesn't seem to fit
it might be best to start writing new stuff
spare geode
Yeah i think it would be best
that makes this alot easier
import sqlite3
import time
def create_customer():
con = sqlite3.connect("CustomerDB.db")
cursor = con.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables=cursor.fetchall()
if ('Customer',) not in tables:
sql = """create table Customer
(Username text,
Password text,
Spent real,
Postcode text,
Username text primary key)"""
with sqlite3.connect("CustomerDB.db") as db:
cursor = db.cursor()
cursor.execute(sql)
db.commit()
else:
Username=input("Username: ")
Password=input("Password: ")
Postcode=input("Postcode: ")
Spent = 0
sql = """INSERT INTO Customer
VALUES (?, ?, ?, ?)"""
with sqlite3.connect("CustomerDB.db") as db:
cursor = db.cursor()
cursor.execute(sql, (Username, Password, 0, Postcode))
db.commit()
@deft badge how would i check for a username and then check it against the password?
deft badge
as I recommended Tizzy earlier, you should use sqlite3.Row