#databases | Python | Page 61

copper sphinx Mar 10, 2019, 11:31 PM

#

ERROR: syntax error at or near "FULL"
LINE 8: FULL OUTER JOIN discord_channels b ON a.channel_id = b.chann...
^
SQL state: 42601
Character: 140

eager star Mar 10, 2019, 11:43 PM

#

in mysql its truncate table_name

copper sphinx Mar 10, 2019, 11:44 PM

#

This is Postgres

red wagon Mar 11, 2019, 3:23 AM

#

Does anyone happen to still use Teradata here?

copper sphinx Mar 11, 2019, 3:31 AM

#

Is there some way to automate Azure MSSQL -> Postgres syncronization?

opaque palm Mar 11, 2019, 10:24 AM

#

Hi,
Can someone give some feedback on my database structure please and let me know if there's a better way of achieving this.

I'm making some animal-rescue software, and want to be able to tag photos with which animals are in them. so that when someone clicks on the animal, all the relevant images pop up. But as a photo can contain multiple animals, I want to be able to tag them all.

so I have a table with animal ID's + names.
A table with photo names (They'll all get renamed to 1,2,3 etc on importing)

Am I best to just add a column to the photo table called "animal_ids" and store the animal ids in a comma separated list or something so I can just go :
"SELECT * FROM photo WHERE animal_ids like '%(insert animal id here)%'

many thanks in advance

torn sphinx Mar 11, 2019, 11:14 AM

#

Have anyone made a discord bot work with aiosqlite? I'd like to see a proper example, if possible. Otherwise I'll just abandon it and try to understand asyncpg instead

torn sphinx Mar 11, 2019, 12:26 PM

#

The actual problem is creating a persistent connection to the database because my bot uses it frequently

torn sphinx Mar 11, 2019, 1:30 PM

#

hey guys, anyone can help me restore a postgres database?

torn sphinx Mar 11, 2019, 3:29 PM

#

@torn sphinx ive done it, dont have a example though

#

it connects and gets data then closes

#

if it has to it updates

torn sphinx Mar 11, 2019, 4:02 PM

#

Would be nice to know how you did it

patent glen Mar 11, 2019, 4:32 PM

#

@torn sphinx if you're careful you can just use normal sqlite

#

but anyway, if you really need a persistent connection (you probably don't, tbh), try connecting in on_ready

torn sphinx Mar 11, 2019, 4:36 PM

#

On_ready triggers multiple times. Anyway, I just couldn't understand how to create a task for that

patent glen Mar 11, 2019, 4:39 PM

#

anyway, the important thing when dealing with sqlite from an async program is that you can't connect to the database while another task is in the middle of a write transaction, and ~~i think aiosqlite doesn't actually protect you from that~~ (EDIT: not sure, looks like maybe it uses threading for this)

#

maybe it does though

#

it looks like aiosqlite3 does a connection pool, so you should actually be fine just connecting every time you need to use the database instead of having a persistent connection

#

@torn sphinx anyway look at run_until_complete https://docs.python.org/3/library/asyncio-eventloop.html

#

you should be able to do ```py
loop = asyncio.get_event_loop()
connection = loop.run_until_complete(aiosqlite3.connect(...))

#

I don't have a working discord bot to test with though, so let me know if this works or not

#

as always, note that you cannot do run_until_complete from inside an async function - in that case just await the connect as normal

torn sphinx Mar 11, 2019, 4:57 PM

#

I'll try that, thanks

wind pelican Mar 11, 2019, 7:33 PM

#

@opaque palm Typically in this case you will have three tables. animals, photos, animal_photo_assoc. both animals and photos have a unique id, and each row in animal_photo_assoc has both an animal_id and a photo_id. this links a photo and an animal and lets you have as many animals in as many photos as you want.

opaque palm Mar 11, 2019, 7:35 PM

#

ah, that makes much more sense

#

thanks !

torn sphinx Mar 12, 2019, 5:10 AM

#

What complicates things is that I wanted to pack it in a module which I'd import to the cogs where I need database interactions

#

But I guess I could save cursor as a bot variable and use it everywhere?

plain radish Mar 12, 2019, 5:29 AM

#

On_ready triggers multiple times
then check if the variable was assigned already first

torn sphinx Mar 12, 2019, 5:30 AM

#

It's not a problem, I guess

#

Haven't done it yet

torn sphinx Mar 12, 2019, 6:23 AM

#

I made it work, but I need someone to help me optimize the shitcode I wrote

#

Bot.py

import discord
from discord.ext import commands
from discord.ext.commands import has_permissions
import time, datetime
import asyncio
import json
import aiosqlite3
from asyncdatabase import asyncdb

bot = commands.Bot(command_prefix=commands.when_mentioned_or('u!'))

with open('config.json') as cfg:
    config = json.load(cfg)

async def connect_db(loop):
    if not bot.cursor:
        bot.db_connection = await aiosqlite3.connect('cake.db', loop=loop)
        bot.cursor = await bot.db_connection.cursor()

@bot.event
async def on_ready():
    await connect_db(bot.loop)
    print("Async DB test")

@bot.command() #fill members DB table from discord
@has_permissions(administrator=True)
async def dbfillmembers(ctx):
    await asyncdb.fillmembers(asyncdb, bot.db_connection, bot.cursor, ctx.guild)
    await ctx.message.add_reaction(u"\U0001F44C")    

if __name__ == '__main__':
    bot.cursor = None
    bot.run(config['token'])

asyncdatabase.py

import aiosqlite3
import discord

ADD_MEMBER_QUERY = "INSERT OR IGNORE INTO members VALUES (?, datetime(?, 'unixepoch'))"
#user id, join date

class asyncdb:
    def __init__(self):
        pass

    async def add_member(self, connection, cursor, member):
        await cursor.execute(ADD_MEMBER_QUERY, (member.id, member.joined_at.timestamp()))
        await connection.commit()
    
    async def fillmembers(self, connection, cursor, guild):
        for member in guild.members:
            await self.add_member(self, connection, cursor, member)

I have to pass the database class to itself for some reason, as well as cursor and connection. How can I optimize this?

merry garden Mar 12, 2019, 12:35 PM

#

Establish the connection and cursor inside asyncdb?

torn sphinx Mar 12, 2019, 12:38 PM

#

You mean transfer connect_db() to asyncdb, then call it in Bot.py? It will probably work, yeah. But why do I have to pass asyncdb to asyncdb functions?

merry garden Mar 12, 2019, 12:44 PM

#

Because of self probably, make them both into classes 😄

#

Do you know about discord.py cogs?

torn sphinx Mar 12, 2019, 12:57 PM

#

Transfering connection to the extension is harder than I thought
I'm relatively new and I'd like to avoid passing class as an argument to itself, I'm clearly doing something wrong here
Yeah, and I want the database stuff to work in multiple cogs, that's why I was going to pack DB functions into a module and import it wherever I need them

#

I'm mostly doing this at work and it's time to go home, so... I'll continue tomorrow

cinder path Mar 12, 2019, 4:52 PM

#

How to drop rows from pandas data frame that contains '-' in its column?

patent glen Mar 12, 2019, 4:57 PM

#

are they negative numbers, or just strings that have '-' in them

#

@cinder path

cinder path Mar 12, 2019, 5:25 PM

#

Im currently working on an exercise whixh wants me to drop nulls but it has '-'s in it too

#

I dropped nulls but couldnt figure out how to drop - s

torn sphinx Mar 12, 2019, 6:13 PM

#

There can only be one primary key column afaik

empty loom Mar 12, 2019, 6:18 PM

#

Hello,i need a help with Flask framework in python..I completed most of my project,but i need a search form to find the data from MySQL database..i really need it

#

anyone know how to do it?

novel wharf Mar 12, 2019, 6:19 PM

#

What have you got so far?

empty loom Mar 12, 2019, 6:19 PM

#

just a sec,ill send ss]

#

I just need a search bar near this,so that i can find the names which i give inside the search

📎 1.PNG

#

Mysql table from which i need to retrieve data

📎 2.PNG

#

there is no tutorial for search form in google😕

novel wharf Mar 12, 2019, 6:29 PM

#

What column are you searching in?

empty loom Mar 12, 2019, 6:31 PM

#

student name

novel wharf Mar 12, 2019, 6:31 PM

#

In SQL, you want to make a request to search for data you're interest in. Something along the lines of ```py
data = query_db('SELECT * FROM studentname WHERE studentname = ?', ["bob"], True)

empty loom Mar 12, 2019, 6:33 PM

#

i know the query,but i dont know how to make a class for it in flask

#

something like this

#

📎 3.PNG

#

ill send the whole code if needed

empty loom Mar 12, 2019, 7:00 PM

#

😕

eager star Mar 12, 2019, 10:39 PM

#

assuming a large db, it is almost always better to use cursor.fetchmany(n) vs other fetches where n isnt crazy big, right?

torn sphinx Mar 12, 2019, 11:02 PM

#

    conn = await aiosqlite3.connect('level.db')
    cur = await conn.cursor()```

#

wrong thing wait

#

File "IceCream.py", line 71, in on_message
guildid, lvlsetting = lvlthing
TypeError: cannot unpack non-iterable NoneType object

#

await that.execute('SELECT * FROM settings WHERE guildid = ?', [message.guild.id])
            lvlthing = await that.fetchone()
            guildid, lvlsetting = lvlthing```

#

thats code

#

i really only want the lvlsetting, how would i do that then

#

uh

#

File "IceCream.py", line 73, in on_message
lvlsetting = lvlthing[1:]
TypeError: 'NoneType' object is not subscriptable

#

now i get this

#

ugh

#

can someone help please

#

uh

#

hello?

#

ugh

#

i cant figure out what to do

#

its set up like this

#

guildid interger lvlmsg text

#

hello?

#

uhh

gilded narwhal Mar 12, 2019, 11:51 PM

#

when fetchone returns None that means either there are no more rows that satisfy the query, or, if it happens on the first call, there weren't any to begin with

torn sphinx Mar 12, 2019, 11:51 PM

#

hmm, how wouldi fix this?

gilded narwhal Mar 12, 2019, 11:51 PM

#

so you need to double check your input and data cause there aren't any rows in your db with that guildid

torn sphinx Mar 12, 2019, 11:51 PM

#

hmm

gilded narwhal Mar 12, 2019, 11:52 PM

#

you can add an if check after that to see if it returned anything

#

lvlthing = await that.fetchone()
if lvlthing:
    # do things

torn sphinx Mar 12, 2019, 11:52 PM

#

i think i know why it isnt working, ty

#

await cur.execute('SELECT * FROM levels WHERE userid = ?', [message.author.id])
File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/cursor.py", line 130, in execute
res = yield from self._execute(self._cursor.execute, sql, parameters)
File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/cursor.py", line 56, in _execute
res = yield from self._conn.async_execute(func, *args, **kwargs)
File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/connection.py", line 137, in async_execute
return (yield from self._execute(func, *args, **kwargs))
File "/usr/local/lib/python3.7/dist-packages/aiosqlite3/connection.py", line 128, in _execute
func
File "/usr/lib/python3.7/concurrent/futures/thread.py", line 57, in run
result = self.fn(*self.args, **self.kwargs)
sqlite3.OperationalError: database is locked

#

great

gilded narwhal Mar 13, 2019, 12:04 AM

#

by default, you can't read and write to an sqlite db at the same time

torn sphinx Mar 13, 2019, 12:04 AM

#

mk....

#

also

gilded narwhal Mar 13, 2019, 12:04 AM

#

however, you can set its journal mode to Write Ahead Logging to fix that

#

https://www.sqlite.org/wal.html

torn sphinx Mar 13, 2019, 12:04 AM

#

ive been making this config command

#

it sets it

#

but for some reason

#

when it reads it it doesnt seem to do what it should

gilded narwhal Mar 13, 2019, 12:05 AM

#

that's a bit vague

#

what does not doing what it should mean

torn sphinx Mar 13, 2019, 12:05 AM

#

one sec actually

#

testing something

#

okay so

#

lvlsetting is ('dm',)

#

thats what i get from printing

#

how would i check the setting for if its dm

#

or something else

gilded narwhal Mar 13, 2019, 12:07 AM

#

what you're receiving from fetchone is a tuple

torn sphinx Mar 13, 2019, 12:07 AM

#

okay, should i string it

gilded narwhal Mar 13, 2019, 12:07 AM

#

you can access the elements in a tuple using tuple[index]

torn sphinx Mar 13, 2019, 12:08 AM

#

so how would i access the 'dm'

gilded narwhal Mar 13, 2019, 12:08 AM

#

so in this case it'd be lvlsetting[0]

torn sphinx Mar 13, 2019, 12:08 AM

#

okay, tysm

gilded narwhal Mar 13, 2019, 12:08 AM

#

👍

#

keep in mind this is read-only

torn sphinx Mar 13, 2019, 12:09 AM

#

yeah ik

#

it worked omg tysm

gilded narwhal Mar 13, 2019, 12:09 AM

#

no prob

torn sphinx Mar 13, 2019, 4:56 AM

#

using postgresql, how can i insert a value into a column with an array type, e.g. bigint[]

#

so, how can i add/remove things to/from the array

#

more or less

#

im using the asyncpg library, btw

dull scarab Mar 13, 2019, 5:51 AM

#

Why do you need to insert an array

red wagon Mar 13, 2019, 8:33 AM

#

hi anyone use postgis here?

torn sphinx Mar 13, 2019, 10:00 AM

#

I couldn't find the solution, so I'll try my luck and ask here, why can't I install postgresql? I tried a couple of different versions and both show this error. OS is Win7

📎 G4qCK.png

#

It doesn't even start

#

I already launch it with --install_runtimes 0 to avoid another error

#

Will try to install it on another pc later

empty loom Mar 13, 2019, 11:19 AM

#

I need Code for Search Form for MySQL with flask..Anyone have?

ionic pecan Mar 13, 2019, 5:38 PM

#

no

#

if you have specific questions we‘re happy to help

prime prism Mar 13, 2019, 5:49 PM

#

What dashboard/utility for sqlite for creation of database would you recommend?

warped shuttle Mar 13, 2019, 5:51 PM

#

does anyone know a good introduction to implementing databases with your programs? i dont quite understand how kt all works?

steel slate Mar 13, 2019, 5:52 PM

#

You can use an ORM that handles that stuff for you

#

@warped shuttle if you need frontend stuff then django will give you ORM and frontend stuff together

ionic pecan Mar 13, 2019, 6:00 PM

#

@prime prism not sure what dashboard you have in mind but in terms of tools definitely alembic

warped shuttle Mar 13, 2019, 6:00 PM

#

ty ill look into that!!

soft bolt Mar 13, 2019, 8:25 PM

#

soh, design question. Should i have an object during which construction, data is verified except for the parts that need access to db itself, that would be done in inside the db

flint bobcat Mar 13, 2019, 9:47 PM

#

I have a postgres db as follow

📎 unknown.png

#

I am actually using the following query to retrive 'score'

select coaster_solver_discordid, sum(difficulty) from cc_games group by coaster_solver_discordid order by sum desc limit 10 ;

#

I'd like to count difficulty twice if coaster_solver_id and park_solver_id are the same

#

and not if not and so on ... Does someone have nidea as to how to forge my request ?

#

anyone ?

pure cypress Mar 13, 2019, 10:18 PM

#

So you want to add 1 to the difficulty column when it gets summed based on a certain condition?

flint bobcat Mar 13, 2019, 10:36 PM

#

Not exactly

#

I want to count difficulty once for a said discordid if only in park or coaster_solver and twice if the silver is the same

#

In fact difficulty field is the number of points for a said question. Each question have 2 answers. I want to give twice the points if the played find both

pure cypress Mar 13, 2019, 10:40 PM

#

So would it be multiplication by 2 instead?

flint bobcat Mar 13, 2019, 10:41 PM

#

Yep

#

2x difficulty if played found both

pure cypress Mar 13, 2019, 10:42 PM

#

Multiplication on the column of a specific row right? Rather than multiplying the entire sum

flint bobcat Mar 13, 2019, 10:42 PM

#

Il not sure To get what you mean

pure cypress Mar 13, 2019, 10:42 PM

#

Well I think you want to use a case statement inside the sum

flint bobcat Mar 13, 2019, 10:42 PM

#

Possibly

#

It should be multiplied by the "sum" of the discordid in the row

#

Not sure if this is clear

pure cypress Mar 13, 2019, 10:44 PM

#

No, it isn't

flint bobcat Mar 13, 2019, 10:45 PM

#

Il not sure that i can an easier way to explain it

pure cypress Mar 13, 2019, 10:45 PM

#

Maybe if I explain in python terms it will help. sum([diff * 2 if row.id1 == row.id2 else diff for diff in row])

#

Oops

#

Let me fix that

#

I'm on mobile

flint bobcat Mar 13, 2019, 10:46 PM

#

Me too :D

pure cypress Mar 13, 2019, 10:47 PM

#

There

#

Is that what you want?.if so, you can do that with a case statement in sql

flint bobcat Mar 13, 2019, 10:47 PM

#

Yes I think that's it

pure cypress Mar 13, 2019, 10:49 PM

#

I'm not sure sorry

flint bobcat Mar 13, 2019, 10:50 PM

#

Do you have a case statement exemple Mark?

#

No hurry but I really struggled on it today

pure cypress Mar 13, 2019, 10:53 PM

#

sum(case when [condition] Then [output] Else [other-output] End)

flint bobcat Mar 13, 2019, 10:54 PM

#

Oh ok

#

I will try that tomorrow as I'm in bed now

#

Thanks a lot

torn sphinx Mar 14, 2019, 3:04 AM

#

hey, can i get some help with sql?
I keep getting the "sql server has gone away"... I already changed all the max thresholds, etc.
Been trying to figure out how to close the connection and then reopen it upon command...

eager star Mar 14, 2019, 3:06 AM

#

are you opening a lot of concurrent connections

#

also what variables did you change

torn sphinx Mar 14, 2019, 3:08 AM

#

This is for discord bot. I perform:

db = pymysql.connect(host="localhost",port=9999, user="stuff",passwd="stuff",db="db") # Open database connection
db.autocommit(True)
cursor = db.cursor() # prepare a cursor object using cursor() method

#

within program, i do sql statements, cursor.execute(sql), and db.commit()
i never close the connection or reopen it anywhere.

SQL keeps changing constantly, affecting cursor.execute(sql) and db.commit() of course.

eager star Mar 14, 2019, 3:10 AM

#

yeah you should close cursors and database connections when you arent using them

#

mysql has some configs that might stop you if you do long or big operations

#

you will have to update the configs to change them

torn sphinx Mar 14, 2019, 3:11 AM

#

yea, i made those unlimited

#

so the issue isn't with that

eager star Mar 14, 2019, 3:11 AM

#

which ones did you change

torn sphinx Mar 14, 2019, 3:11 AM

#

that's not the issue.

#

could i just do a db.close() ?

#

and would i just run that after every operation is executed and performed fully?

eager star Mar 14, 2019, 3:12 AM

#

just close and remake the cursor

torn sphinx Mar 14, 2019, 3:13 AM

#

the cursor is a global variable

#

should i just remove it and only add it right when my functions to call to the database start?

eager star Mar 14, 2019, 3:18 AM

#

it isnt going to take up any resources if you dont interact with it through some database query

#

anyway your problem is probably still a config one

#

either mysql timeout length or max query size if i had to guess

torn sphinx Mar 14, 2019, 3:35 AM

#

once again

#

i set those to max

torn sphinx Mar 14, 2019, 5:02 AM

#

In SQLite, is using a single cursor for everything a normal practice?

#

My requests are pretty frequent and I thought that it would be better to establish the connection and cursor once and use them for everything

pure cypress Mar 14, 2019, 5:17 AM

#

I don't think sqlite cares and it will work fine either way. Discussions on best practice for this seem to all come down to the topic of performance. If you care about performance, you should just run your own benchmarks

torn sphinx Mar 14, 2019, 5:18 AM

#

Is there a difference for aiosqlite?

pure cypress Mar 14, 2019, 5:18 AM

#

I should make it clear I'm only talking about the connection. Not sure on the cursor

torn sphinx Mar 14, 2019, 5:18 AM

#

Alright

pure cypress Mar 14, 2019, 5:19 AM

#

I'm not sure about aiosqlite either

torn sphinx Mar 14, 2019, 5:19 AM

#

I'd use postgres is I was able to install it on my work pc 🤷

pure cypress Mar 14, 2019, 5:19 AM

#

At the least, it typically uses a context manager for both the connection and cursor I think

#

So if you have them global just be sure to close them properly

torn sphinx Mar 14, 2019, 5:22 AM

#

I don't close anything, all I do is await connection.commit() if I write something down

pure cypress Mar 14, 2019, 5:23 AM

#

I mean they should be closed when your program terminates

torn sphinx Mar 14, 2019, 5:24 AM

#

I think it's handled without my participation, I can access the DB after I close the program

pure cypress Mar 14, 2019, 5:26 AM

#

Perhaps, I don't know if it's safe to assume it will close gracefully without explicit calls to close it. I was just being on the safe side

torn sphinx Mar 14, 2019, 5:27 AM

#

Makes sense. So I'll need to play around with cursors to find out whether it makes any difference

#

Thanks for your time 👌

pure cypress Mar 14, 2019, 5:28 AM

#

Np.

polar osprey Mar 14, 2019, 9:46 AM

#

Is there a data type for something like this 08:30PM in MySQL?

#

I only found Time but that is kinda weired since that goes from '-838:59:59' to '838:59:59'

torn sphinx Mar 14, 2019, 11:06 AM

#

I store datetime stuff as text

soft bolt Mar 14, 2019, 11:11 AM

#

does sqlite not support foreign keys?

nova hawk Mar 14, 2019, 11:13 AM

#

It does, but it doesn't suport adding them later, only on table creation

storm hawk Mar 14, 2019, 12:39 PM

#

How to get all the distinct values from my collection in mongodb?

db.foodcollection.find()
{ "_id" : ObjectId("5c88b339fbad023567ff7a62"), "apples" : 8 }
{ "_id" : ObjectId("5c88b33afbad023567ff7a63"), "oranges" : 6 }
{ "_id" : ObjectId("5c88b33afbad023567ff7a64"), "eggs" : 2 }
{ "_id" : ObjectId("5c88b33afbad023567ff7a65"), "bazz" : 6 }
{ "_id" : ObjectId("5c88e472fbad021e75d6c547"), "oranges" : 8 }
{ "_id" : ObjectId("5c88e472fbad021e75d6c548"), "bazz" : 8 }
{ "_id" : ObjectId("5c88e472fbad021e75d6c549"), "apples" : 8 }
{ "_id" : ObjectId("5c88e472fbad021e75d6c54a"), "eggs" : 2 }
{ "_id" : ObjectId("5c88e6d4fbad0236548e71f1"), "eggs" : 2 }
{ "_id" : ObjectId("5c88e6d4fbad0236548e71f2"), "bazz" : 8 }
{ "_id" : ObjectId("5c88e6d4fbad0236548e71f3"), "oranges" : 8 }
{ "_id" : ObjectId("5c88e6d4fbad0236548e71f5"), "apples" : 8 }
{ "_id" : ObjectId("5c8a09cefbad0251407f2a24"), "eggs" : 2 }
{ "_id" : ObjectId("5c8a1f0bfbad0222dbb1ad82"), "eggs" : 2 }
Type "it" for more

#

this is my collections

torn sphinx Mar 14, 2019, 1:20 PM

#

hey anyone can help me to restore a postgres database?

#

when i run psql database < file

#

i guet a things already exists

#

but i just creat the database, don't put anything inside it

torn sphinx Mar 14, 2019, 1:54 PM

#

Hopefully another quick question... when entering data into a mysql database with mysql.connector, when would an error be generated? on mycursor.execute() or conn.commit() ? (So I can 'catch' the error)

warped shuttle Mar 14, 2019, 3:44 PM

#

so what i have in mind, is a database to store various pieces of information for my program, but it would only be used locally, and only when this program is open. Is there a way to stop and restart it upon closing and restarting the program? or would it better just to leave it all up? Also if i wanted to distribute this, would people have to set up the database themselves on their machines, and then run the program? or is there a way to just have the program do it all? The only way I can think is like bash scripting, but is there a way to do it all within the python program?

flint bobcat Mar 14, 2019, 4:00 PM

#

I have a DB as follow

📎 unknown.png

#

I actually generate a leaderboard using this query

select coaster_solver_discordid, sum(difficulty) from cc_games group by coaster_solver_discordid order by sum desc limit 10

But it does only rely on coaster_solver_discordid. I'd liek to also count difficulty one time if the user_id is also in park_solver_discordid but I don't know how to forge my request

#

anyone ? :3

eager star Mar 14, 2019, 4:34 PM

#

can you explain more what you are trying to do exactly :3

warped shuttle Mar 14, 2019, 4:35 PM

#

are you asking me or repz?

flint bobcat Mar 14, 2019, 4:37 PM

#

I am actually only counting difficulty if a said user_id is in coaster_solver_discordid. I'd like to do difficulty * (amount of user_id in the row)

#

just like I actually do but with park_solver_discordid ANd coaster_solver_discordid

warped shuttle Mar 14, 2019, 5:52 PM

#

do databases have to be kept ongoing or can they be localized to just a program? and if the former, is there a better system for data persistance if you only want it for a single program?

buoyant breach Mar 14, 2019, 7:29 PM

#

Depends on how you put it. Database itself is a very broad abstract thing, it does not have anything to do with the specific technology.

#

JSON, XML or even raw TXT file stored in the filesystem can be a database.

#

So, there are MySQL and PostgreSQL that you need a server running for in order to use them.

#

There is SQLite that is just a file that does not require a standalone server.

#

@warped shuttle

prime prism Mar 14, 2019, 8:24 PM

#

Does sqlite3 cursor have no close() method? It feels a bit dirty not to close a cursor.

gilded narwhal Mar 14, 2019, 9:11 PM

#

@prime prism it's a bit weird. there's a close method in the 3.6 docs https://docs.python.org/3.6/library/sqlite3.html#sqlite3.Cursor.close

#

not sure what's going on there

#

oops, my mistake. i was looking at the python2 docs for sqlite. yep, looks like the current docs document close too

#

https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.close

#

i think you can also use the cursor in a with block which'll automatically close it once it's done.

#

i.e.

    # do stuff with cursor

warped shuttle Mar 14, 2019, 10:35 PM

#

thank you diraven!

the project is a character sheet manager. and i was storing characters as a class and i thought a good way to go about it would be the class holds ids for the various details and the whenever it was going to display information for the thing itd check the details in the database.

so for example i might load john snow, which will check the db to grab his class, background, race, inventory etc. and in his inventorty it might have sword, bow, knife. and if you wanted the mechanical aspects of sword, you could click on it and itd check the db again for those details if that makes sense?

does that sound like something youd use a dedicated server db for, or a series of files?

modern berry Mar 14, 2019, 11:53 PM

#

How come conda doesn't install DB drivers when you install the relevant package? It installs BLAS and LAPACK and stuff, don't see why it can't install the DB drivers.

prime prism Mar 15, 2019, 6:51 AM

#

@gilded narwhal thanks, with block loos even cleaner

torn sphinx Mar 15, 2019, 8:26 AM

#

Need some help regarding aiosqlite3
In main bot file I establish connection:

import aiosqlite3
import database as adb

@bot.event
async def on_ready():
    if not bot.db_connection:
        bot.db_connection = await aiosqlite3.connect('apoc.db', loop=bot.loop)

if __name__ == '__main__': 
    bot.db_connection = None #On launch it will be None. When the bot is ready it will establish connection

And try to save an incoming message

@bot.event 
async def on_message(message):
    if message.guild == bot.apoc: #my server
        await adb.add_message(bot.db_connection, message)
    await bot.process_commands(message)```
Database module I import is just a list of queries:
```py
import aiosqlite3
import discord
ADD_MSG_QUERY = "INSERT OR IGNORE INTO messages VALUES (?, ?, ?, ?, datetime(?, 'unixepoch'))"
#message id, author id, channel id, content, datetime

async def add_message(connection, message):
    content = message.content if message.content else 'NULL'
    time = message.created_at.timestamp()
    cursor = await connection.cursor()
    await cursor.execute(ADD_MSG_QUERY, (message.id, message.author.id, message.channel.id, content, time))
    await connection.commit()

And all this gives me a missing table error.
apoc.db is in the same folder as main Bot file and it contains the messages table
http://s2.micp.ru/iZ9Et.png

📎 10EXF.png

#

Also somehow NULL for content is considered text

pure cypress Mar 15, 2019, 8:40 AM

#

if you want to write null you should give it None in Python

torn sphinx Mar 15, 2019, 8:40 AM

#

I'll try that, thanks

#

What's really weird with that message storing is that it works in my test bot, but not in the main one

#

Although I moved everything 1:1

torn sphinx Mar 15, 2019, 9:15 AM

#

Damn, I'm struggling with this for hours

#

The database file was empty wtf. I replaced it with the old one and it works now

pure cypress Mar 15, 2019, 9:22 AM

#

I tend to include some initialisation SQL for the db

#

Creating tables and such

#

That way I don't have to worry about the file not existing or tables missing

#

It has a handy create table if not exists so you don't even have to check if it exists beforehand

torn sphinx Mar 15, 2019, 9:24 AM

#

I had it when I used blocking sqlite3 library because I just started then and I needed to create tables. But now I already have data stored, and I only need to add to it

#

Mate I'm exhausted by searching for mistakes when everything was so simple

pure cypress Mar 15, 2019, 9:31 AM

#

Welcome to programming 😉

torn sphinx Mar 15, 2019, 11:11 AM

#

Is this normal practice for aiosqlite3? I'm trying to avoid some overlapping transaction errors

async def add_message(connection, message):
    content = message.content if message.content else 'NULL'
    time = message.created_at.timestamp()
    try:
        cursor = await connection.cursor()
        await cursor.execute(ADD_MSG_QUERY, (message.id, message.author.id, message.channel.id, content, time))
        await connection.commit()
        await cursor.close()
    except sqlite3.OperationalError:
        await asyncio.sleep(10)
        await add_message(connection, message)

#

Since I added try/except there were no issues with add_message

barren garden Mar 15, 2019, 7:52 PM

#

by visiting the interwebs and asking the great sage Google

#

?

#

Least thats how im doing it

void otter Mar 15, 2019, 10:21 PM

#

may i ask something?

#

so

#

im using sqlalchemy

#

from datetime import datetime
from app import db,login_manager
from flask_login import UserMixin


@login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id))


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='default.jpg')
    password = db.Column(db.String(60), nullable=False)
    posts = db.relationship('Post', backref='author', lazy=True)

    def __repr__(self):
        return "User({}, {}, {})".format(self.username, self.email, self.image_file)


class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    content = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"), nullable=False)

    def __repr__(self):
        return "User({}, {})".format(self.title, self.date_posted)

so the table Post is what i'm aiming for
but i want to query posts backwards

@app.route("/")
def home():
    posts = Post.query.all()
    return render_template('home.html', posts=posts)

#

how can i query posts backwards?

#

pls tag me when answer

silent tapir Mar 16, 2019, 9:45 AM

#

I am using a INSERT IGNORE query to make it ignore duplicate entries, but aiomysql is still printing Warning: Duplicate entry warnings. Is there a way to hide them?

torn sphinx Mar 16, 2019, 9:48 AM

#

why not add a field to account for duplicate entries..

void otter Mar 16, 2019, 10:40 AM

#

Can someone help me?

hazy mango Mar 16, 2019, 10:46 AM

#

!ask Don't ask to ask @void otter, just ask

delicate fieldBOT Mar 16, 2019, 10:46 AM

#

ask

Asking good questions will yield a much higher chance of a quick response:

• Don't ask to ask your question, just go ahead and tell us your problem.
• Try to solve the problem on your own first, we're not going to write code for you.
• Show us the code you've tried and any errors or unexpected results it's giving
• Keep your patience while we're helping you.

You can find a much more detailed explanation on our website.

void otter Mar 16, 2019, 10:46 AM

#

@hazy mango i mentioned my problem above

#

Look above

hazy mango Mar 16, 2019, 10:46 AM

#

mb, might wanna resend so it's easier to see

#

and I don't know sqlalchemy so can't help, sry

void otter Mar 16, 2019, 10:47 AM

#

K

#

Thanks anyways

regal portal Mar 16, 2019, 10:54 AM

#

Error :

mysql.connector.errors.IntegrityError: 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Code :

import mysql.connector

mydb = mysql.connector.connect(
  host="∆∆∆∆∆",
  user="∆∆∆∆∆",
  passwd="∆∆∆∆∆∆",
  database="∆∆∆∆∆∆"
)

mycursor = mydb.cursor()

sql = "INSERT INTO economy (userid, money) VALUES (%s, %s)"
val = (1,  66)
mycursor.execute(sql, val)

mydb.commit()

#

PHPMyAdmin :

📎 IMG_20190316_115606.png

#

I dont have auto increments

ionic pecan Mar 16, 2019, 1:25 PM

#

not sure what you‘re asking

storm turret Mar 16, 2019, 5:17 PM

#

is there any way this code could be escaped to be in a different column?

commandContent = "N/A"
if message.content[:len(prefix)] == prefix:
     commandContent = message.content[len(prefix):].replace("\"", "'")
statLogLine = '"{}","{}","{}","{}"\n'.format(message.timestamp,message.author,message.channel.name,commandContent)
print("Saved: " + statLogLine)
f = open('statistics.csv','a')
f.write(statLogLine)
f.close()

#

if a user uses a command the command content is logged

#

is there any combination of backslashes or quotes a user could use to get past content.replace("\"", "'")

regal portal Mar 16, 2019, 7:27 PM

#

sql = "SELECT money FROM economy WHERE userid={}".format(ctx.message.author.id)

Hi, I was told that I can not use {} for sql queries, how do i do it?

nova hawk Mar 16, 2019, 7:29 PM

#

You can use a question mark

#

cursor.execute("query with ?", variables)

#

The .format way leaves you vulnerable to injection attacks

regal portal Mar 16, 2019, 7:31 PM

#

Oh, why?

nova hawk Mar 16, 2019, 7:31 PM

#

It treats it like normal text

#

So if someone puts in an SQL statement that ends yours and is followed up by a malicious query, that would get executed

#

With the ? notation it will be considered a string object instead of being part of the wuery

regal portal Mar 16, 2019, 7:33 PM

#

Oh

#

Thank you

#

mycursor.execute("SELECT money FROM economy WHERE userid=?", ctx.message.author.id) @nova hawk so like that?

nova hawk Mar 16, 2019, 7:43 PM

#

Does it work?

regal portal Mar 16, 2019, 7:43 PM

#

No

#

ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

#

I try with an %s

nova hawk Mar 16, 2019, 7:47 PM

#

The examples in the docs do look like that.

#

%s is the same as .format

regal portal Mar 16, 2019, 7:47 PM

#

Oh

#

How do i do?

nova hawk Mar 16, 2019, 7:47 PM

#

https://docs.python.org/3.7/library/sqlite3.html

#

Oh, I think it needs to be a tuple

#

(ctx.message.author.id, )

#

The , is needed for a single item tuple

regal portal Mar 16, 2019, 7:50 PM

#

Okay i test iy

#

it

#

Command raised an exception: ProgrammingError: Not all parameters were used in the SQL statement

#

nova hawk Mar 16, 2019, 7:55 PM

#

t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)```
is an example from the docs, which looks the same to me

#

You don't save the return value of mycursor to a variable btw, you get the results from the cursor itself later

regal portal Mar 16, 2019, 7:56 PM

#

🤔

#

it means that the SQL query works but I do not misuse the variable value

nova hawk Mar 16, 2019, 8:01 PM

#

How are you getting the cursor?

regal portal Mar 16, 2019, 8:03 PM

#

mycursor = mydb.cursor()

nova hawk Mar 16, 2019, 8:03 PM

#

Is this sqlite3?

regal portal Mar 16, 2019, 8:04 PM

#

Nop

#

Its mysql

nova hawk Mar 16, 2019, 8:08 PM

#

Most connectors for that should also support ?

regal portal Mar 16, 2019, 8:09 PM

#

Yes

#

Yes

nova hawk Mar 16, 2019, 8:10 PM

#

Which is the exect one you are using?

#

That you impor

regal portal Mar 16, 2019, 8:10 PM

#

import mysql.connector ?

nova hawk Mar 16, 2019, 8:13 PM

#

Oh, in mysql-connector docs they don't use the standard python api but instead do

mycursor.execute("SELECT money FROM economy WHERE userid=%s", (ctx.message.author.id, ))

regal portal Mar 16, 2019, 8:14 PM

#

Okay

#

    await ctx.send(str(value))``` it return me None @nova hawk

nova hawk Mar 16, 2019, 8:22 PM

#

Yeah like I said earlier.

#

You don't get the value from that

#

You do something with the cursor to get the data after the execute

regal portal Mar 16, 2019, 8:23 PM

#

How can i get the value

#

How can i get the value ?

nova hawk Mar 16, 2019, 8:25 PM

#

cursor.fetchall() will get you all of the results at once

#

there are also methods like, I think it's called fetchone, where you can get one at a time.

torn sphinx Mar 16, 2019, 8:38 PM

#

I still have to [0] from fetchone though

#

For stuff like sum or count

regal portal Mar 16, 2019, 8:41 PM

#

@nova hawk when i do fetchall, i becom ('300',), but i want to get only the int 300

nova hawk Mar 16, 2019, 8:41 PM

#

You can get the 300 just like a list.

#

With [0]

#

And [1],[2] etc if you had more than one result

regal portal Mar 16, 2019, 8:42 PM

#

Oh . _.

#

Have i to Filter the result?

nova hawk Mar 16, 2019, 8:43 PM

#

Often time people have queries that returns more than one value.

#

This time it's only one.

torn sphinx Mar 16, 2019, 8:52 PM

#

You should be able to guess how many results your query returns

torn sphinx Mar 16, 2019, 9:24 PM

#

sqlite

cursor = await db.execute("UPDATE users SET license = :license WHERE id = :id", {'license': 0, 'id': ctx.message.author.id})
``` How can I set license to NULL?

#

Try passing 'NULL' to the database

#

ty

wintry axle Mar 17, 2019, 12:39 AM

#

Hello, I have Python Flask- SQL Web App. User fill out HTMLform and upload data(name, email) to Postgres DB, than I will download and display the data in an html template. That works. Now I want to have user upload image (.png) along with name, email. So 1st thing I read .png must be type BYTEA instead of VARCHAR. I create DB:
db.execute("CREATE TABLE user(id SERIAL PRIMARY KEY, name VARCHAR, email VARCHAR, image BYTEA)") When I receive the image via html form in Flask I name it 'image' and insert it into the DB. <input type="file" name="image" enctype="multipart/form-data" accept="image/*"> image = request.form.get("image") db.execute("INSERT INTO user(image) VALUES (:image)", {"image":image}) #removed name, email for this example.
So my question is am I doing this part correct? Because when I try to retrieve them it is not showing. I think I need to change the data from bytea to .png?

glass relic Mar 17, 2019, 3:39 PM

#

Hey guys, I have a DB that is currently 42billion rows. Problem is that it’s in SQLite. It takes so long for complete query’s. My server has 156gb ram and 2 cpu 48cores. I need to put information into another dB that can query faster

#

Any ideas? SQLite is not cutting it. It only uses 1 core at 100% for hours

plain radish Mar 17, 2019, 3:40 PM

#

you're welcome to look into a full sql server such as postgresql

#

it may assist a bit by splitting the load across multiple threads and cores

#

also much better caching

glass relic Mar 17, 2019, 3:40 PM

#

Yeah that’s what I need. Multiple threads/cores

#

The dB is 2.4TB

plain radish Mar 17, 2019, 3:41 PM

#

sqlite dies in scaling up to a point, since it's a single-file db

#

especially when it comes to writes

#

migrate over, look well into index tables

#

and you should see a big benefit

glass relic Mar 17, 2019, 3:41 PM

#

It have indexes already inside it but idk how to use them to be honest

plain radish Mar 17, 2019, 3:42 PM

#

at that data size it's still worth moving entirely

#

but definitely look into taking advantage of index tables later once you've migrated as the impact in a full sql server is much more than a sqlite db

glass relic Mar 17, 2019, 3:43 PM

#

Yeah. I moved a smaller one to MySQL and I needed to house both DB at first. Sense the size of this is 2.4TB and I only have 4TB do you think there is a way to migrate over without buying new HDD?

plain radish Mar 17, 2019, 3:44 PM

#

do a table at a time perhaps

#

unless that's one table

#

lol

glass relic Mar 17, 2019, 3:44 PM

#

No there is 2 tables inside there

#

Then like 4 index’s

plain radish Mar 17, 2019, 3:44 PM

#

you could do a compressed backup

#

it won't take as much space while keeping an original copy

#

otherwise chunks?

glass relic Mar 17, 2019, 3:45 PM

#

So compress the original and then start the migration?

plain radish Mar 17, 2019, 3:45 PM

#

or borrowing an external maybe

glass relic Mar 17, 2019, 3:45 PM

#

Yeah I was debating buying external to hold the original dB files

plain radish Mar 17, 2019, 3:45 PM

#

it's a lot of data

#

you can't clean house a bit beforehand?

glass relic Mar 17, 2019, 3:46 PM

#

Took 5 days to download so having backup would be good too lol

#

I can’t because it’s ledgers on a crypto and I’m using whole thing to run query’s and track accounts

#

So postgressql would be a safe starting point for good dB?

plain radish Mar 17, 2019, 3:48 PM

#

it's a good option, yeah

#

either that or mariadb

#

depends on what you're familiar with

#

if neither, i'd recommend pg

glass relic Mar 17, 2019, 3:48 PM

#

I’ve only ever used SQLite or sql alchemy

#

I can learn though. Just want a good starting point so don’t need to migrate to another one again later

plain radish Mar 17, 2019, 3:49 PM

#

then fo sho, go pg

#

it's pretty simple to pickup and it's datatypes are somewhat similar to sqlite

glass relic Mar 17, 2019, 3:50 PM

#

I’ll start looking for dB migrate tool from SQLite to pg

#

Ok cool. Python have some good modules to use with pg or should just get an existing GUI tool, similar to db browser for SQLite

#

@plain radish huge thanks for your help btw

plain radish Mar 17, 2019, 3:53 PM

#

pg definitely have quite a few libs that you can use

#

to interact with it

#

if you want a gui manager, it comes with one normally pgadmin

glass relic Mar 17, 2019, 3:53 PM

#

Awesome thanks. You saved me huge headaches going dB to dB lol

plain radish Mar 17, 2019, 3:54 PM

#

no problem at all

#

hope it goes well

ionic pecan Mar 17, 2019, 5:29 PM

#

I would first try to place proper indices

#

Are specific queries running long or is it overall load?

#

also: https://sqlite.org/wal.html

#

also: https://sqlite.org/wal.html

#

also: https://sqlite.org/wal.html

#

thanks discord

onyx seal Mar 17, 2019, 5:48 PM

#

from my own experience, Postgres is a good option for databases, especially in Python due to async supported libraries existing (asyncpg for example). Although one or two exists for MySQL/MariaDB, my experience with them was poor so I only use sync modules (I primarily use the MySQL Connector from Oracle's site now, due to it being a lot better for my use case). But if you're looking to handle huge amounts of data, like in the gigabytes of size, you're better off with Postgres.

phppgadmin is okay, but it is a bit "weird" in some cases. For example, if you want to convert a varchar column to int, it rejects the query it forms, telling you to run a manual query which includes a USING statement. Not sure if that is just a bug from the fact I use slightly older versions for compatibility reasons with cPanel, but it does have some oddities that can make it a bit annoying to work with.

torn sphinx Mar 17, 2019, 7:21 PM

#

is sqlite3 good?

clever crypt Mar 17, 2019, 7:44 PM

#

I'm no expert, but imo sqlite3 is very nice for projects that won't have a large number of users

#

It's a good fit for the sorts of projects I do.

#

I have no idea how it scales to large numbers of users. I don't deal with those sorts of projects at this time

proven grove Mar 17, 2019, 8:08 PM

#

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JSON NULL,
  PRIMARY KEY (`id`))' at line 8
SQL Statement:
CREATE TABLE `database`.`press` (
  `id` INT NULL AUTO_INCREMENT,
  `header` VARCHAR(255) NULL,
  `created` DATETIME NOT NULL,
  `from` DATETIME NOT NULL,
  `to` DATETIME NOT NULL,
  `expired` INT NULL DEFAULT 0,
  `pages` JSON NULL,
  PRIMARY KEY (`id`))

#

can someone help me?

nova hawk Mar 17, 2019, 8:09 PM

#

What database are you using

proven grove Mar 17, 2019, 8:09 PM

#

let me search

#

InnoDB

#

Is it compatible with JSON?

nova hawk Mar 17, 2019, 8:16 PM

#

What version are you running?

#

JSON on mysql/mariadb is a fairly recent addition

proven grove Mar 17, 2019, 8:25 PM

#

Hmmm give me a moment

#

5.6, @nova hawk

nova hawk Mar 17, 2019, 8:27 PM

#

Yeah, you need 5.7.8 or later for JSON

proven grove Mar 17, 2019, 8:27 PM

#

ah, alright thanks

torn sphinx Mar 18, 2019, 10:32 AM

#

When do I need to use cursor in aiosqlite? I just found out that I can INSERT without it

lethal shore Mar 18, 2019, 11:37 AM

#

does someones got the imgen db by mistake? from this repo? https://github.com/DankMemer/meme-server

GitHub

DankMemer/meme-server

API server for image manipulation, focused on Dank Memer (discord bot) - DankMemer/meme-server

lucid aspen Mar 18, 2019, 9:31 PM

#

Copying this from general help since I'm not getting any answer

#

Since I'm not sure if I should ask in database or web development, I might as well ask here
I've made a database using peewee and I'm trying to make an API out of it, so I've been exploring options
flask-restplus seems nice since I'm already somewhat familiar with flask, and the object marshalling integrates nicely with peewee, however they are removing argument parsing support and it seems like the project is discontinued since the developer went NC
fastapi also seems really sweet since it's really modern and uses a lot of new features (like type hinting), however all documentation regarding relational databases use SQLAlchemy and all my personal attempts at using peewee (without manually marshalling the data into a dict or something) have failed
I'm not sure what else there it is or what should I do
should I migrate form peewee to SQLAlchemy? Should I just get something done with restplus? Is there anything else I could use?
Do I use fastapi but I marshal the data manually in the endpoints?
there's a lot of approaches I could take but I'm not sure which one would be the most optimal
ideally I'd love to use fastapi since it looks really sweet, but the fact that I'd have to either replace peewee with SQLalchemy or marshal all the data manually is throwing me back a bit

torn sphinx Mar 19, 2019, 2:01 AM

#

This is supposed to give users coins if the random number that was selected is 2, 4, 6, 8, or 10 but remove the coins they bet if it is 1, 3, 5, 7, or 9

    @commands.command()
    async def gamble(self, ctx, guess):
        member = discord.Member
        member_id = str(member.id)
        guild_id = str(ctx.guild.id)
        user = await self.bot.pg_con.fetch("SELECT * FROM users1 WHERE user_id = $1 AND guild_id = $2", member_id, guild_id)


        win_loss = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
        win = [2, 4, 6, 8, 10]
        loss = [1, 3, 5, 7, 9]
        secure_random = random.SystemRandom()
        win_loss_result = secure_random.choice(win_loss)

        if int(guess) == 0:
            print(int(guess))
            await ctx.send("You want to bet 0 coins?")
        elif int(guess) < 0:
            await ctx.send("You cant bet negative coins...")
        elif int(guess) >= user[0]['coins']:
            if win_loss_result in win:
                await self.bot.pg_con.execute("UPDATE users1 SET coins = $1 WHERE user_id = $2 AND guild_id = $3", user['coins'] + guess, author_id, guild_id)
                await ctx.send("You won {} coins!".format(guess))
            elif win_loss_result in loss:
                await self.bot.pg_con.execute("UPDATE users1 SET coins = $1 WHERE user_id = $2 AND guild_id = $3", user['coins'] - guess, author_id, guild_id)
                await ctx.send("You lost {} coins...be more careful next time".format(guess))```
what am i doing wrong here?

#

I keep getting this error

#

Ignoring exception in command gamble:
Traceback (most recent call last):
  File "C:\Program Files\Python37\lib\site-packages\discord\ext\commands\core.py", line 63, in wrapped
    ret = await coro(*args, **kwargs)
  File "C:\Users\drewm\PycharmProjects\MyProjects\Discord Bots\AIO_Bot\cogs\Misc.py", line 31, in gamble
    elif int(guess) >= user[0]['coins']:
IndexError: list index out of range

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Program Files\Python37\lib\site-packages\discord\ext\commands\bot.py", line 782, in invoke
    await ctx.command.invoke(ctx)
  File "C:\Program Files\Python37\lib\site-packages\discord\ext\commands\core.py", line 683, in invoke
    await injected(*ctx.args, **ctx.kwargs)
  File "C:\Program Files\Python37\lib\site-packages\discord\ext\commands\core.py", line 72, in wrapped
    raise CommandInvokeError(exc) from exc
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: IndexError: list index out of range```

#

I am using Postgres as my database to store the users coins

Ping me if anyone can help, thanks 😃

reef musk Mar 19, 2019, 4:59 AM

#

@torn sphinx it looks like the list user is empty, so when you try to access user[0] it is giving you that error. There is probably a bug in whatever code is generating that list

#

looks like you need to put a check after the line user = await self.bot.pg_con.fetch(...) to check if it returned no results

torn sphinx Mar 19, 2019, 5:35 AM

#

No, I figured it out tho @reef musk

#

Where I was Selecting the rows I needed fetchrow, not just fetch

reef musk Mar 19, 2019, 5:40 AM

#

cool, glad you figured it out ... but you should still put a check for cases where it returns no result (invalid username, etc) ... otherwise, it's going to crash your program at runtime if the function receives bad input

torn sphinx Mar 19, 2019, 6:16 AM

#

How would it receive an invalid name? It uses the discord.id of the user calling the command

lucid aspen Mar 20, 2019, 6:27 AM

#

What are the main advantages of using SQLAlchemy over peewee?

#

the general consensus is that SQLAlchemy is more powerful and widespread, but I'd love to see some specific examples on stuff that peewee doesn't handle well while SQLAlchemy excels at it

pallid pewter Mar 20, 2019, 9:34 AM

#

which one do you guys suggest? sqllite, mysql or something else?

merry garden Mar 20, 2019, 9:42 AM

#

For what purpose?

#

If you're going to store a lot of stuff and scale out eventually, go with mysql

pallid pewter Mar 20, 2019, 9:45 AM

#

a discord gamble bot

merry garden Mar 20, 2019, 9:46 AM

#

Youll probably be happy with mysql

pallid pewter Mar 20, 2019, 9:46 AM

#

thanks

merry garden Mar 20, 2019, 9:46 AM

#

Remember to use a async library, like aiomysql

torn sphinx Mar 20, 2019, 10:56 AM

#

pandas sets datatypes of columns to float after the second row is added to the database?

#

any ideas why that might be a thing?

#

📎 unknown.png

#

like all of those values are int's and they are ints when there is just 1 row in the dataframe, but as soon as the second row get's added it literally turns all of the column datatypes to float

#

when the only time when i actually use astype is when initially creating a db if it doesn't exist

#

📎 unknown.png

#

why does that happen?

#

https://stackoverflow.com/questions/38003406/pandas-why-is-default-column-type-for-numeric-float

Stack Overflow

Pandas: Why is default column type for numeric float?

I am using Pandas 0.18.1 with python 2.7.x. I have an empty dataframe that I read first. I see that the types of these columns are object which is OK. When I assign one row of data, the type for nu...

#

found this, but i still have no clue how to get around that and why is this happening

tough geyser Mar 20, 2019, 12:22 PM

#

hypothetical Postgres DB:

my_table (
id serial PRIMARY KEY
some_user_id BIGINT
some_info TEXT
timestamp timestamptz
)

adding values with:

INSERT INTO my_table (some_user_id, some_info, timestamp) VALUES (...)

The INSERTs are happening multiple times a second, and in only really need to store the last ~10 or so entries per user ID.
How would DELETE all but the last x rows for a given user ID?

Or ist there even a way to remove the old ones while INSERTing?

#

(lib is asyncpg)

torn sphinx Mar 20, 2019, 12:36 PM

#

@tough geyser thanks mate

#

for overtaking my spot in the line

merry garden Mar 20, 2019, 12:39 PM

#

So, if no one knows the answer to your question, everyone will be forbidden to ask their question until we find the solution to yours? 🤔 @torn sphinx

torn sphinx Mar 20, 2019, 12:40 PM

#

@merry garden how come you made a conclusion that nobody knows an answer to my question

merry garden Mar 20, 2019, 12:40 PM

#

No one has gave you a solution yet, so i can assume nobody currently knows

plain radish Mar 20, 2019, 12:43 PM

#

ok guys, that's enough thanks.
while this is a help channel, it's not quite the same as those in Python Help category. People can post additional questions here

#

we just hope people respect each other when posting enough to give each question a little time and space though

torn sphinx Mar 20, 2019, 12:44 PM

#

Yeah, and don’t moderate the channels yourself. If you think something is wrong, maybe ping or DM one of the moderators

#

well frankly speaking, he has a point

#

i couldnt find absolutely anything online regarding my question

#

as if it was legit just a bug

#

so yeah i would imagine nobody knowing an answer

#

peace

torn sphinx Mar 20, 2019, 1:16 PM

#

okay never mind, apparently it's discord related issue

#

not sure how that works, but if a server was just created, and has only 2 default categories, and 2 default channels in it, then at some point the code turns channel.id and owner.id and also the amount of members into float

#

i will recheck my code, but i have no clue what might be causing that

novel axle Mar 20, 2019, 1:33 PM

#

I started to learn MySQL

#

😇

merry garden Mar 20, 2019, 1:40 PM

#

Good boy pat pat

torn sphinx Mar 20, 2019, 2:04 PM

#

pandas is keep on turning ints into floats

#

raises

#

TypeError: Cannot cast array from dtype('float64') to dtype('int32') according to the rule 'safe' now

#

ValueError: cannot safely convert passed user dtype of int32 for float64 dtyped data in column 0

#

this also

#

i don't understand how does this still result in pandas making the values floats

📎 unknown.png

#

i even put some test prints for a logging right before the program enters this function, and all of the values integers, which is obvious, because neither does guild.id return float, nor does the channel.id, owner.id or len(guild.members)

plain radish Mar 20, 2019, 2:13 PM

#

whats the full traceback?

torn sphinx Mar 20, 2019, 2:14 PM

#

Traceback (most recent call last):
  File "pandas\_libs\parsers.pyx", line 1156, in pandas._libs.parsers.TextReader._convert_tokens
TypeError: Cannot cast array from dtype('float64') to dtype('int32') according to the rule 'safe'```

plain radish Mar 20, 2019, 2:14 PM

#

that's the full output?

#

normally it would show more of the stack

torn sphinx Mar 20, 2019, 2:14 PM

#

there is also During handling of the above exception, another exception occurred...

#

below

#

but will it matter

plain radish Mar 20, 2019, 2:14 PM

#

yes

torn sphinx Mar 20, 2019, 2:14 PM

#

its the initial exception that matters

#

that starts it all

plain radish Mar 20, 2019, 2:15 PM

#

can you just send the full traceback please

torn sphinx Mar 20, 2019, 2:18 PM

#

what's the site where i could insert my code

plain radish Mar 20, 2019, 2:18 PM

#

paste.pydis.com

torn sphinx Mar 20, 2019, 2:19 PM

#

https://paste.pydis.com/uwepehepim.py

plain radish Mar 20, 2019, 2:24 PM

#

are you able to share the code for main.py minus any tokens?

torn sphinx Mar 20, 2019, 2:25 PM

#

i can share a code of that full method called "appending_to_DB"

#

not sure how anything apart from that matters

#

it does successfully enter this func each time

#

with the right parameters

#

that are ints

#

each and every time

#

it's the pandas stuff clearly

#

does*

#

typo

plain radish Mar 20, 2019, 2:28 PM

#

i'm just trying to help and get a clear picture of the code, the exception and the surroundings regarding it. if you keep saying "not sure how it matters" then you're seemingly resistive to any help and I can't really be bothered trying to convince you otherwise, as you're welcome to wait for someone else to come along.

torn sphinx Mar 20, 2019, 2:31 PM

#

screw it, just gonna use strings

torn sphinx Mar 20, 2019, 3:22 PM

#

i am so done with pandas, with solving one problem comes another

#

apparently it is almost impossible to disable postprocessing on strings

#

pandas literally has no sense of datatypes

#

df.drop(guild.id, axis=0) somehow executes just fine and actually finds an int index, although the index field is set to string.

#

every damn thing is set to string and it still somehow manages to find an integer somewhere

#

📎 unknown.png

#

god damn it

next smelt Mar 20, 2019, 3:26 PM

#

I have a quick SQL question

#

I'm bad at SQL

#

I have 2 tables A and B, and a A_B table that link IdA with IdB

#

now

#

I want to create another table with the foreign keys IdA and IdB

#

but only the couples defines in the table A_B

#

I'm a bit lost, how can I do that ? rn I'm thinking about adding a primary key to the table A_B but I'm not sure that's the best way to go

next smelt Mar 20, 2019, 4:21 PM

#

So, this is the table I want to use the Ids from

CREATE TABLE T_MACHINE_MATERIAL (
    ID_MACHINE int NOT NULL, 
    ID_MATERIAL int NOT NULL, 
    CONSTRAINT Foreign_key01 FOREIGN KEY (ID_MACHINE) REFERENCES T_MACHINES (Id),
    CONSTRAINT Foreign_key01 FOREIGN KEY (ID_MATERIAL) REFERENCES T_MATERIALS (Id)
)

#

and this is what I'm trying right now

CREATE TABLE T_TRUC (
    ID_MACHINE int NOT NULL, 
    ID_MATERIAL int NOT NULL, 
    PARAM_VALUE int,
    CONSTRAINT Foreign_key01 FOREIGN KEY (ID_MACHINE) REFERENCES T_MACHINE_MATERIAL (ID_MACHINE),
    CONSTRAINT Foreign_key01 FOREIGN KEY (ID_MATERIAL) REFERENCES T_MACHINE_MATERIAL (ID_MATERIAL)
)

#

and when I try to insert some datas, I got "foreign key mismatch blablabla"

rustic yarrow Mar 20, 2019, 4:26 PM

#

Why are you creating another table

#

Can you do what you need just join using inner join

next smelt Mar 20, 2019, 4:27 PM

#

it's the first step of a bigger thing

rustic yarrow Mar 20, 2019, 4:27 PM

#

Have you considered creating a view

next smelt Mar 20, 2019, 4:27 PM

#

no, I'm not sure what it is tbh

#

a bit of context

rustic yarrow Mar 20, 2019, 4:28 PM

#

Usually you don't want to just join 2 tables and make a new table in relational db

#

You want to know what you are doing first

next smelt Mar 20, 2019, 4:29 PM

#

I'm not sure to understand, you're saying T_MACHINE_MATERIAL should be a view ?

rustic yarrow Mar 20, 2019, 4:29 PM

#

I have no idea what you are trying to do

next smelt Mar 20, 2019, 4:29 PM

#

I'll try to give a bit of context : there's a Machine table, there's a Material table, and there's a Parameter table

#

now, machine and material can have params

#

so there's a MachineParam table and a MaterialParam table

#

also, a machine can only have a set of materials

#

so there's a MachineMaterial table

#

now

#

we want to add params to a couple Machine/Material

#

.. I hope I'm clear enought u_u

#

so what I wanted to do is create another table that use a couple Machine/Material as ID as described in the table MachineMaterial

#

and add parameters

rustic yarrow Mar 20, 2019, 4:34 PM

#

Maybe you are having issues because you are naming your constraints the same?

#

You might be better served by creating your tables using some software instead of using queries

next smelt Mar 20, 2019, 4:34 PM

#

(the first block "CREATE TABLE" is generated by a tools)

#

oh well

#

@rustic yarrow are we talking about the constraint name of the second table, the first or both ?

rustic yarrow Mar 20, 2019, 4:36 PM

#

Sorry I never create FK constraints from query but I think you need to look at documentation to check how to do it

next smelt Mar 20, 2019, 4:36 PM

#

ok, thanks

misty sigil Mar 20, 2019, 4:41 PM

#

@next smelt how did you create a seperate box for the code previously?

#

in discord

next smelt Mar 20, 2019, 4:45 PM

#

wdym

#

@misty sigil dis ?

pouet pouet

misty sigil Mar 20, 2019, 4:46 PM

#

@next smelt yeah that

next smelt Mar 20, 2019, 4:46 PM

#

simple ` for one-liner

#

tripler for block

#

after the first three, specify the language

#

no spaces in between, no trailing spaces

#

```sql
POUET
```

#

POUET

misty sigil Mar 20, 2019, 4:47 PM

#

xd

next smelt Mar 20, 2019, 4:48 PM

#

yeah, carefull on the spacing and trailing spaces

#

dem

#

I should have trade the answer :c

misty sigil Mar 20, 2019, 4:49 PM

#


skrt```

next smelt Mar 20, 2019, 5:05 PM

#

I succeeded

#

@rustic yarrow a bit of follow-up if you're interested

#

the core of the issue was : the original table where I draw the composite foreign keys

#

didn't have primary key setted

rustic yarrow Mar 20, 2019, 5:06 PM

#

Yeah that would be an issue

next smelt Mar 20, 2019, 5:06 PM

#

so I added the two foreign keys as composite primary key

#

and it's working now

rustic yarrow Mar 20, 2019, 5:08 PM

#

Why aren't you using primary keys from the original tables

next smelt Mar 20, 2019, 5:09 PM

#

because I'm very very bad at SQL

#

somehow it didn't cause any issues before

#

i'll suggest my boss to buy me a SQL course

rustic yarrow Mar 20, 2019, 5:10 PM

#

Do you have a single parameter to add or can there be multiple

#

W3 schools and a lot of places have some basic SQL info

next smelt Mar 20, 2019, 5:11 PM

#

yeah but it's almost always better when your boss pays for it

rustic yarrow Mar 20, 2019, 5:11 PM

#

Hmm yeah hope so

next smelt Mar 20, 2019, 5:11 PM

#

:p

#

there will be 2 new params

tame quartz Mar 20, 2019, 6:39 PM

#

anyone familiar enough with th emariadb connector to understand how each cursor interation works?

#

if i create multiple cursors off of a single DB connection, are they independent?

#

db = mariadb.connect(server details)
fkcur = db.cursor(exec some commands)
fkcur2 = db.cursor(exec more commands)

onyx seal Mar 20, 2019, 7:42 PM

#

I don't think a MariaDB connector for Python exists? at least, when I google search, I don't find anything about that existing (it seems to exist for some other languages, but not Python), though I get results about the MySQL Python Connector from dev.mysql.com (which works on mariadb databases as well), perhaps that's what you're referring to?

#

although generally, afaik, it's usually one cursor per connection that is recommend, you can create more cursors if you want but remember to close them after they have been used.

river glen Mar 20, 2019, 8:04 PM

#

MariaDB is an implementation of MySQL so should try MySQL

merry garden Mar 20, 2019, 8:14 PM

#

Use aiomysql lib, if you going to make a disord bot or anything async 😉

onyx seal Mar 20, 2019, 8:18 PM

#

I use MySQL Connector (https://dev.mysql.com/doc/connector-python/en/) and have no problems with it at all on an async bot connected to 30 guilds.

#

from my experience and research, the aiomysql lib is based on PyMySQL and I had no end of issues with that library, random errors and just sloppy code resulted in my bot from all the bugs I was running into. When I converted over to the Oracle mysql connector, my work with the db was much simpler because I wasn't running into so many random issues with it, due to the library handling many of the requirements for me much cleaner and more effectively than pymysql did.

merry garden Mar 20, 2019, 9:21 PM

#

Is MySQL connector even async? If not, its blocking

onyx seal Mar 20, 2019, 9:31 PM

#

It’s not async but like i said, i have run into zero issues with it on an async bot on the discord.py rewrite branch. My bot has many queries calling the mysql db through it and literally never ran into any blocking issues due to it not being async. It even runs a number of tasks every 10-15 seconds calling the db, still no issues of blocking queries.

#

It’s only blocking if you misuse the lib imo. In my implementation/usage of mysql connector, i run into zero issues. Aiomysql is bad because it is based on a bad implementation of pymysql. With pymysql, i spent most of my time having to escape half the stuff i wrote due to invalid syntax, but mysql connector does all the escaping for me so i don’t run into any issues with things like apostrophes or quote marks breaking the queries in my bot.

#

There are others i know who use mysql exclusively and don’t ever run into issues of blocking code.

#

Because db latency is like 2ms so it’s not even noticeable.

merry garden Mar 20, 2019, 9:36 PM

#

Well, I dont have any issues with aiomysql 🤷 It’s only blocking if you misuse the lib imo, in your opinion? 🤔 Yeah thats wrong

onyx seal Mar 20, 2019, 9:36 PM

#

you’re free to disagree with my opinion, i know what blocking stuff is. 😃

#

But like i said, i use mysql connector, it’s not async and i have absolutely no issues with it blocking anything in my asyncio based bot. 😄

keen rock Mar 20, 2019, 9:59 PM

#

So this is how the document in my database looks like (MongoDB):

_id : 5c92b32c49020728c06bd42f
users mention: : "@keen rock"
gamertag : "YetusThyDexterus"
platform : "pc"

I want to search for the key users mention then I want to get the value of the platform key of that document.

#

How can I do that?
I know it's either query or find.
But I don't know the process.

tame quartz Mar 21, 2019, 1:08 AM

#

Yea I'm using MySQL connector, just aliasing it as mariadb

onyx seal Mar 21, 2019, 1:28 AM

#

@tame quartz oh i see, then yeah you should be able to make multiple cursors, haven’t seen any documentation saying you can’t do that.

merry garden Mar 21, 2019, 7:03 AM

#

Why would you want multiple cursors? 🤔

next smelt Mar 21, 2019, 9:34 AM

#

Hey !

#

I want to make sure every lines in a table is unique

#

should I put every fields as primary key ?

hazy mango Mar 21, 2019, 9:35 AM

#

No

#

Only ever have one primary key

next smelt Mar 21, 2019, 9:35 AM

#

I mean

pale crest Mar 21, 2019, 9:35 AM

#

https://www.w3schools.com/sql/sql_unique.asp

next smelt Mar 21, 2019, 9:35 AM

#

a composite primary key

hazy mango Mar 21, 2019, 9:35 AM

#

You can usually use the UNIQUE key word

next smelt Mar 21, 2019, 9:35 AM

#

with all fields

#

I though "unique" was working on only one field at a time ?

pale crest Mar 21, 2019, 9:36 AM

#

You can apply unique to any number of fields.

#

See the examples in my link.

next smelt Mar 21, 2019, 9:37 AM

#

oh the constraint

#

thank you !!

pale crest Mar 21, 2019, 9:38 AM

#

You're welcome

next smelt Mar 21, 2019, 9:40 AM

#

(it's working, thanks)

pale crest Mar 21, 2019, 9:40 AM

#

Cool

torn sphinx Mar 21, 2019, 9:49 AM

#

been 2 days, still struggling with pandas

#

how is this even possible that there are different datatypes in the same column

#

📎 unknown.png

#

none of that makes any sense, i am not only always str() each and every value that i insert into the columns, but also have dtype set to str literally everywhere, i am so done with pandas

pure cypress Mar 21, 2019, 9:58 AM

#

That's just scientific notation. Is it really a different type?

torn sphinx Mar 21, 2019, 9:59 AM

#

well technically those 2 might be strings, but first one is naturally float wrapped into the string

#

why is pandas doing that on the background

#

in absolutely all cases i insert strings

pure cypress Mar 21, 2019, 10:00 AM

#

It's not actually a float, if you convert it to normal notation you'll see it has no decimal points. Full disclosure I am not familiar with pandas but this just seems like it's merely displaying the number differently when printed, but the data is still the same underneath.

torn sphinx Mar 21, 2019, 10:02 AM

#

i swear to god when i was going through a 15 hours course on pandas a year ago, it was going just perfectly, and i was so fascinated about it, what the hell happened even.

#

like i searched all the internet and couldn't find anything regarding my problem. I am literally setting everything to string, and it still on it's side is converting it to a float. I cant even get around that

pure cypress Mar 21, 2019, 10:05 AM

#

Did you not read anything I said?

torn sphinx Mar 21, 2019, 10:05 AM

#

i have read that it might not be a float in reality

#

it appears to be so to my eyes anyways

#

which i was not encountering back then at all

pure cypress Mar 21, 2019, 10:07 AM

#

This seems to be relevant https://stackoverflow.com/questions/21137150/format-suppress-scientific-notation-from-python-pandas-aggregation-results

Stack Overflow

Format / Suppress Scientific Notation from Python Pandas Aggregati...

How can one modify the format for the output from a groupby operation in pandas that produces scientific notation for very large numbers. I know how to do string formatting in python but I'm at a l...

torn sphinx Mar 21, 2019, 10:11 AM

#

although index is set to string as well, it somehow does manage to find a row via int index. Absolutely great

📎 unknown.png

#

apparently, some of my prints were freezing the program and thus the windows shell, although i was using sys.stdout.flush() each time

#

after getting rid of them, apparently it all works fine now

#

i have absolutely 0 idea how come prints could mess the whole program up that much

#

https://stackoverflow.com/questions/230751/how-to-flush-output-of-print-function

Stack Overflow

How to flush output of print function?

How do I force Python's print function to output to the screen?

This is not a duplicate of Disable output buffering - the linked question is attempting unbuffered output, while this is more genera...

#

still not sure how was the program freezing even if i was flushing after each print, gonna try print(flush=True)

#

yeah works great now

torn sphinx Mar 21, 2019, 6:00 PM

#

Would anybody be able to help me with converting some SQLite to Postgre, I'd be ever so thankfull

ionic pecan Mar 21, 2019, 10:40 PM

#

!ask

delicate fieldBOT Mar 21, 2019, 10:40 PM

#

ask

Asking good questions will yield a much higher chance of a quick response:

• Don't ask to ask your question, just go ahead and tell us your problem.
• Try to solve the problem on your own first, we're not going to write code for you.
• Show us the code you've tried and any errors or unexpected results it's giving
• Keep your patience while we're helping you.

You can find a much more detailed explanation on our website.

unkempt sable Mar 22, 2019, 3:50 AM

#

can anyone recommend a nice database design tool/website for Postrgres? or is that not the way to go normally?

torn sphinx Mar 22, 2019, 5:16 AM

#

https://pgmodeler.io/

pgModeler - PostgreSQL Database Modeler

Open source data modeling tool designed for PostgreSQL. No more DDL commands written by hand. Let pgModeler do the job for you!

#

I used to use a different one..but can't remember the name at the moment..

quiet ermine Mar 22, 2019, 4:53 PM

#

Does [model].query.filter_by(something=something) get an array of all, opposed to [model].query.filter_by(something=something).first() only getting 1?

opaque palm Mar 22, 2019, 4:58 PM

#

Hi all,
What library do people recommend to use with SQL? are there any recommended ones that help against SQL injection like sqlite3

#

is SQLalchemy decent?

torn sphinx Mar 22, 2019, 5:01 PM

#

With which sql database?

quiet ermine Mar 22, 2019, 5:05 PM

#

Use SQLAlchemy and sqlite is not the same

#

@opaque palm

weary heart Mar 22, 2019, 5:09 PM

#

@quiet ermine That doesn't look like django, but in django the first would return a queryset and the second would return the first result as a single object, IIRC. Is this SQLAlchemy?

quiet ermine Mar 22, 2019, 5:09 PM

#

Oh yeah, SQLAlchemy, sorry for not mentioning

weary heart Mar 22, 2019, 5:11 PM

#

@opaque palm What do you want to do with the database? If you want to use it to store data for a website for instance, you could jump directly into django which comes with a really nice ORM, if you just want to mess around with a relational database, SQLAlchemy is really mature and feature-rich

quiet ermine Mar 22, 2019, 5:11 PM

#

Hes probs doing Flask

#

Why did I say that

weary heart Mar 22, 2019, 5:12 PM

#

@quiet ermine Yea, I'd say your assumption is right, though I'd be careful with the term "all" in this context, if you are applying a filter you may not actually be getting all results in the table

quiet ermine Mar 22, 2019, 5:12 PM

#

Yeah

#

I'm basically checking an is_hidden tag

#

Doing pagnation so I need to filter it when I first get listings

weary heart Mar 22, 2019, 5:13 PM

#

Yea, I'd expect to get some sort of iterable back, even if only one result matched the filter

quiet ermine Mar 22, 2019, 5:13 PM

#

👍

opaque palm Mar 22, 2019, 5:25 PM

#

sorry i had to leave work so missed your replies @weary heart , @quiet ermine , @torn sphinx .
I'm making an app to run on our desktops at work that will just send/receive data from a server running MSSQL with a bunch of databases on.
I've written the app in ahk over the last couple years but want to port it to python now

quiet ermine Mar 22, 2019, 5:27 PM

#

Ah

#

Definatly use SQLAlchemy + Flask then

weary heart Mar 22, 2019, 5:27 PM

#

Oh, you might be a bit more limited with MSSQL, I think there might just be the one library, not sure

#

Does SQLAlchemy support MSSQL?

quiet ermine Mar 22, 2019, 5:27 PM

#

Pretty sure it does, MSSQL is decently popular

#

Django would be very heavyweight for something like this

opaque palm Mar 22, 2019, 5:28 PM

#

i use this most commonly at the moment in AHK https://autohotkey.com/board/topic/83542-func-adosql-uses-ado-to-manage-sql-transactions-v503l/

AutoHotkey Community

[Func] ADOSQL - Uses ADO to manage SQL transactions (v5.03L) - Scr...

Page 1 of 2 - [Func] ADOSQL - Uses ADO to manage SQL transactions (v5.03L) - posted in Scripts and Functions: Heres yet another wrapper of ADO COM objects for doing SQL stuff. All it does is open a connection to a database, submit a SQL statement, parse the results, then retu...

weary heart Mar 22, 2019, 5:28 PM

#

It does but only via one native it looks like, basically pymssql or an odbc module

opaque palm Mar 22, 2019, 5:28 PM

#

cheers, I'll check out the sqlalchemy link

quiet ermine Mar 22, 2019, 5:28 PM

#

Don't, thats not flask

#

https://docs.sqlalchemy.org/en/latest/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pyodbc

opaque palm Mar 22, 2019, 5:29 PM

#

I've nearly actually looked into what flask is tbh. but I guess now'd a good enough time to learn

quiet ermine Mar 22, 2019, 5:29 PM

#

And this may help: https://stackoverflow.com/questions/46739295/connect-to-mssql-database-using-flask-sqlalchemy

Stack Overflow

Connect to MSSQL Database using Flask-SQLAlchemy

I'm trying to connect to a local MSSQL DB through Flask-SQLAlchemy.

Here's a code excerpt from my init.py file:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__...

#

I'm confident after 3 months of very on and off

opaque palm Mar 22, 2019, 5:30 PM

#

i see it posted on nearly every python related job advert so i guess its about time i learn lol

quiet ermine Mar 22, 2019, 5:30 PM

#

Basically its website backends

#

A bit like PHP's CAKE, express fro node.js or asp.net for c#

weary heart Mar 22, 2019, 5:30 PM

#

pymssql will let you connect, execute commands and queries, sqlalchemy is both an engine wrapper and an ORM, makes working with DBs programatically easier

#

flask is a web framework, serves up your data to the web

opaque palm Mar 22, 2019, 5:33 PM

#

ah ok, is sqlalchemy a part of flask then? then example shows import flask_sqlaclhemy

quiet ermine Mar 22, 2019, 5:33 PM

#

[dev-packages]
autopep8 = "*"
pylint = "*"

[packages]
flask = "*"
flask-migrate = "*"
flask-sqlalchemy = "*"
flask-restful = "*"
flask-httpauth = "*"
nose = "*"
``` ^ These are the modules I use for a small-medium sized flask website with an api

#

SQLAlchemy is both a normal thing on it's own and modified quite a lot for using with flask, aka flask-sqlalchemy

#

I think the flask version is better tbh, less configuration and works with flask ofc

opaque palm Mar 22, 2019, 5:34 PM

#

ah, noted. Thanks

#

i was only looking for something similar to the sqlite3 module which i've used in the past. something thats not feature rich, but I'll scrap that and go binge some flask tutorial vids this weekend instead

#

thanks all!

quiet ermine Mar 22, 2019, 5:35 PM

#

👍

#

It can use sqlite, basically any db with some rarer ones being supported with an extra module

#

(I'm using sqlite for development, postgre for production)

opaque palm Mar 22, 2019, 5:36 PM

#

tbh after using sqlite, i much prefer my mssql database at work

quiet ermine Mar 22, 2019, 5:37 PM

#

Everything is abstracted with sqlalchemy so apart from more datatypes to use, there doesn't feel like much diffrence

opaque palm Mar 22, 2019, 5:37 PM

#

does that mean I dont build up a raw sql query then? I dont get how they all look the same on it?

quiet ermine Mar 22, 2019, 5:38 PM

#

Normally you don't but you can (reasons are it's easier to use with non-raw query and prevents any sql injection)

opaque palm Mar 22, 2019, 5:39 PM

#

thats why i liked the python sqlite3 module. it let you build the raw query but then it still handled the variabled to protect against the injection

#

where you just pass it a dictionary of values

quiet ermine Mar 22, 2019, 5:40 PM

#

Why not use automatic ones?

weary heart Mar 22, 2019, 5:40 PM

#

I mean you could, but the idea is to abstract the database away so that you could swap in sqlite for postgres without a bunch of heavy changes

quiet ermine Mar 22, 2019, 5:40 PM

#

^

opaque palm Mar 22, 2019, 5:41 PM

#

no reason not to other than i'm already comfortable with the raw sql from using mssqlsstudio

#

but i'll learn the correct/bettter way though

quiet ermine Mar 22, 2019, 5:41 PM

#

Better idea using automatic ones tbh

opaque palm Mar 22, 2019, 5:42 PM

#

no worries, i'll learn them instead 😃

quiet ermine Mar 22, 2019, 5:42 PM

#

I'm learning SQL right now but if it's not early web development when no tools are around or PHP, use automatic ones :)

weary heart Mar 22, 2019, 5:42 PM

#

Unless you are writing incredibly heavy queries, joining across a bunch of tables with conditionals, you are much better off using the ORM so that database changes, upgrades, etc, don't affect your code. And it you are writing heavy queries for a web application, it probably means the model isn't optimized

quiet ermine Mar 22, 2019, 5:42 PM

#

It does still allow manual ones for some reall abstract things

opaque palm Mar 22, 2019, 5:43 PM

#

i'm coming at this from the opposite end. I'm more than capable with sql but the python side is all new lol

#

@weary heart, i will be doing exactly that tbh. data warehousing etc

weary heart Mar 22, 2019, 5:43 PM

#

@opaque palm I was in the same boat a year ago, there are some really good tutorials out there for for sql devs moving to an orm

quiet ermine Mar 22, 2019, 5:44 PM

#

Yeah :P

weary heart Mar 22, 2019, 5:44 PM

#

@opaque palm Ah, in that case is sqlinjection is big risk? Are users running these queries templated from a front-end, or is it all occuring in the backend?

opaque palm Mar 22, 2019, 5:45 PM

#

to be honest, not really a big risk, but thought i better at least cover myself incase the IT guy audits it and has a moan

weary heart Mar 22, 2019, 5:45 PM

#

Not that it should be ignored, just that you might want to make a list of priorities and order them

opaque palm Mar 22, 2019, 5:46 PM

#

our IT guy is the kinda person to try and force through an injection then moan about it. He doesn't like the access i have already and I'd rather not annoy him any more lol

weary heart Mar 22, 2019, 5:46 PM

#

Well, you should be ok with plain ol' SQLAlchemy, it'll have those protections built in and you can write regular old SQL if you need to

#

Or just go directly to pymssql and see if it has everything you need

#

http://pymssql.org/en/stable/

opaque palm Mar 22, 2019, 5:47 PM

#

Well , I've got a time sensitive piece i want to get our before financial year end (1st april), so I'll probably use plain sqlalchemy for that for now, then re-write the app all in flash-sqlalchemy

#

I'll check out that too though

#

thanks a bunch though, you've all given me plenty to go away and research 👍

weary heart Mar 22, 2019, 5:48 PM

#

http://pymssql.org/en/stable/ref/pymssql.html?highlight=injection

#

Yea, at least you can built up, sqlalchemy uses pymssql for connecting to mssql, flask-sqlalchemy is modified sqlalchemy, etc.

#

So you won't be wasting your time

opaque palm Mar 22, 2019, 5:50 PM

#

ah good to know then

#

I'm off to stick some dinner in now anyway. thanks for the help all!

weary heart Mar 22, 2019, 5:50 PM

#

No worries, take care!

ember bolt Mar 22, 2019, 6:58 PM

#

https://i.imgur.com/vBQVcVF.png Can anyone help me with this, its the postgresql database but Im trying to connect via my remote server. However because my database isnt on the same port it refuses to connect?

Imgur

merry garden Mar 22, 2019, 8:03 PM

#

Is it on the same network?

#

Is the database open for remote connections?

ember bolt Mar 22, 2019, 8:07 PM

#

@merry garden Ive tried to fix it but idk how to implament that , ive checked the docs (for postgresql) and it said opening a config file and editing it

#

ive tried to edit but idk if ive done it right

#

https://i.imgur.com/YnXuWby.png , https://i.imgur.com/V6ENyYv.png

Imgur

merry garden Mar 22, 2019, 8:09 PM

#

Did you restart the database after the edit?

ember bolt Mar 22, 2019, 8:10 PM

#

Ive just done that, im going to restart it now, but is that code correct?

merry garden Mar 22, 2019, 8:10 PM

#

Dont know sorry 😄 Ive only used mysql

ember bolt Mar 22, 2019, 8:10 PM

#

ah idk how to connect to that

#

the code wasnt right :(

ionic pecan Mar 22, 2019, 10:43 PM

#

@ember bolt pg_hba.conf is definitely the wrong place

#

you need to adjust postgresql.conf

#

The listen_addresses line

#

but make sure you dont allow unauthenticated public connections

ember bolt Mar 22, 2019, 10:56 PM

#

@ionic pecan okay thanks

keen rock Mar 22, 2019, 11:17 PM

#

_id:5c953ea64902072c909f5e2d
users mention::"@keen rock"
gamertag:"YetusThyDexterus"
platform:"pc"
players level:12

#

How can I search for users mention and then get the platform key of that document.

#

MongoDB document ^

novel gust Mar 22, 2019, 11:28 PM

#

good luck dex 😦

ember bolt Mar 22, 2019, 11:39 PM

#

how would I do it @ionic pecan ?

ionic pecan Mar 23, 2019, 8:11 AM

#

Which part?

polar osprey Mar 23, 2019, 9:17 AM

#

📎 unknown.png

#

I have a table "bookings" with the following columns:
team_short, date, time, enemy_btag, enemy_discord, enemy_sr, special and id

I display this data on a website with a joomla plugin called Fabrik (see picture above).

As you can see the data is grouped by date. What I would want though is to display the week day of this day next to it. I know that there is a MySQL command WEEKDAY() but unfortunately the only thing that Fabrik allows me to display up there is information that is stored within the database. So I was thinking of just storing the weekday in a seperate column in the database. The Problem I have with that is that I don't want to rely on user input for this.

My Question therefore is:
Is there any way to define a column as weekday(date) so it would always be accurate? Or do you see any other way I could realize what I want?

ember bolt Mar 23, 2019, 10:53 AM

#

You said there was something wrong with the listen_adress but I don’t have a clue on how im meant to write it @ionic pecan

ionic pecan Mar 23, 2019, 10:53 AM

#

check the file itself

#

at the end of the line there's a comment

#

"addresses to bind on "

ember bolt Mar 23, 2019, 10:55 AM

#

I saw that , and I assumed I was meant to write “host replication all listen_adress = ,*, trust ?

ionic pecan Mar 23, 2019, 12:40 PM

#

Noo

#

Why do you think so

ember bolt Mar 23, 2019, 1:10 PM

#

I watched a tutorial and it said that was the way to go

#

idek what type of code it is so I cant research , as on atom it simply says "plain text"

ionic pecan Mar 23, 2019, 1:35 PM

#

yes, it is plain text

#

I don't know what kind of tutorial that is but for PostgreSQL documentation is king

#

https://www.postgresql.org/docs/11/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS

ember bolt Mar 23, 2019, 1:51 PM

#

thanks you

#

@ionic pecan ive read it and understood it a bit more , but I still wouldnt know how to make it authorize my aws server

ionic pecan Mar 23, 2019, 2:01 PM

#

define "authorize"

#

log it in to the server?

copper sphinx Mar 23, 2019, 4:57 PM

#

So you try to login remotely to your postgres database, correct?

#

@ember bolt

torn sphinx Mar 23, 2019, 5:20 PM

#

Hey so i decided to move to aiosqlite instead of normal sqlite however what changes are there? for example this code ```
import aiosqlite

conn = aiosqlite.connect('database.db')
c = conn.cursor()

``` gives me this error sys:1: RuntimeWarning: coroutine 'cursor' was never awaited

#

I can't just await it top of the file

ionic pecan Mar 23, 2019, 5:24 PM

#

you know, using regular sqlite isn‘t as bad as you think, local file access has pretty low latency. iirc aiosqlite just runs everything you do in a thread so you aren‘t really doing yourself a big favour

midnight verge Mar 23, 2019, 5:24 PM

#

@torn sphinx you have to run an event loop, more info here https://docs.python.org/3/library/asyncio.html

ionic pecan Mar 23, 2019, 5:24 PM

#

but to answer your question, you need to instantiate the cursor in a place where you can await it

torn sphinx Mar 23, 2019, 5:24 PM

#

ik it's not a major thing however i get told 20 times a day "ItS bLoCkIng" which is true

ionic pecan Mar 23, 2019, 5:25 PM

#

usually you only instantiate it in the function that needs it, e.g. your web framework views

midnight verge Mar 23, 2019, 5:25 PM

#

@torn sphinx it will block anyway, just "look" asynchronous

ionic pecan Mar 23, 2019, 5:25 PM

#

yes, everything blocks

midnight verge Mar 23, 2019, 5:26 PM

#

SQLite is not made for concurrent write accesses

ionic pecan Mar 23, 2019, 5:26 PM

#

iirc even the asyncio primitives to write to sockets block, but maybe i misinterpreted that

midnight verge Mar 23, 2019, 5:27 PM

#

@ionic pecan you can read/write to sockets in an asynchronous way, either using threads or relying on kernel sockets handling (with select for instance)

torn sphinx Mar 23, 2019, 5:27 PM

#

import aiosqlite
import asyncio

async def mainn():
    conn = aiosqlite.connect('database.db')
    c = await conn.cursor()

midnight verge Mar 23, 2019, 5:27 PM

#

In the end asyncio is just a programming convenience, it will not make your SQLite database allow concurrent writes

torn sphinx Mar 23, 2019, 5:27 PM

#

Liek that? ^^

ionic pecan Mar 23, 2019, 5:27 PM

#

yes

torn sphinx Mar 23, 2019, 5:28 PM

#

It's not giving me any errors however it's not creating the database like normally

ionic pecan Mar 23, 2019, 5:28 PM

#

did you await it?

torn sphinx Mar 23, 2019, 5:28 PM

#

Class 'Connection' does not define 'await', so the 'await' operator cannot be used on its instances more... (Ctrl+F1)

#

Says that if i do

midnight verge Mar 23, 2019, 5:41 PM

#

@torn sphinx async functions must be run in an event loop, read asyncio docs for more details

regal portal Mar 23, 2019, 5:44 PM

#

📎 IMG_20190323_184418.png

#

Can i do something like WHERE %s IN clan-members?

high gust Mar 23, 2019, 5:48 PM

#

@regal portal Presuming you're using sqlite3, you can do py cursor.execute("SELECT * FROM clans WHERE name=?", (clan_name,))

regal portal Mar 23, 2019, 5:48 PM

#

I use msyql

#

No I want to get the clan name from a user

#

But the clan-members table Is for exemple :

John / Mike / Fred

#

Somthing like
SELECT clan-name FROM clans WHERE "John" IN clan-members @high gust

#

I hope you understand what I mean

high gust Mar 23, 2019, 5:52 PM

#

yeah, hold on

#

@regal portal I think this is what you want: https://www.w3schools.com/sql/sql_union.asp

regal portal Mar 23, 2019, 5:56 PM

#

Nop

midnight verge Mar 23, 2019, 5:58 PM

#

@regal portal what is your data model?

regal portal Mar 23, 2019, 5:58 PM

#

Data model?

#

I just want to get the clan-name where "Fred" Is in clan-members

high gust Mar 23, 2019, 6:00 PM

#

Does the clan-members table have information on the clan name in it?

regal portal Mar 23, 2019, 6:00 PM

#

No

high gust Mar 23, 2019, 6:02 PM

#

what does the clans table look like?

regal portal Mar 23, 2019, 6:03 PM

#

📎 IMG_20190323_190334.png

ionic pecan Mar 23, 2019, 6:03 PM

#

what the fuck is union doing here

#

is clan-members an array or a string?

regal portal Mar 23, 2019, 6:04 PM

#

I want to get the clan-name where John is in clan-members

#

String

ionic pecan Mar 23, 2019, 6:04 PM

#

use LIKE

regal portal Mar 23, 2019, 6:04 PM

#

Example?

ionic pecan Mar 23, 2019, 6:04 PM

#

https://www.w3schools.com/SQL/sql_like.asp

regal portal Mar 23, 2019, 6:04 PM

#

Thx

ionic pecan Mar 23, 2019, 6:05 PM

#

SELECT clan-name FROM clans WHERE clan-members LIKE "%John%";

should do what you want

#

doesn't account for when somebody else has parts of the name though, for that you'd have to split the string

regal portal Mar 23, 2019, 6:05 PM

#

Can this work with?

SELECT clan-name FROM clans WHERE clan-members LIKE "%Fred%";

ionic pecan Mar 23, 2019, 6:05 PM

#

or have one row per name

#

try it

#

i don't see why not

#

then again, it's mysql. god knows what mysql is doing

regal portal Mar 23, 2019, 6:08 PM

#

Why?

novel gust Mar 23, 2019, 6:25 PM

#

and beware the injection attack

regal portal Mar 23, 2019, 6:39 PM

#

  File "/usr/local/lib/python3.6/site-packages/aiomysql/cursors.py", line 239, in execute
    await self._query(query)
  File "/usr/local/lib/python3.6/site-packages/aiomysql/cursors.py", line 457, in _query
    await conn.query(q)
  File "/usr/local/lib/python3.6/site-packages/aiomysql/connection.py", line 428, in query
    await self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.6/site-packages/aiomysql/connection.py", line 622, in _read_query_result
    await result.read()
  File "/usr/local/lib/python3.6/site-packages/aiomysql/connection.py", line 1105, in read
    first_packet = await self.connection._read_packet()
  File "/usr/local/lib/python3.6/site-packages/aiomysql/connection.py", line 593, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-members, clan-owner, clean-level, clean-xp) VALUES (380711465210085377, 3807114' at line 1")

ionic pecan Mar 23, 2019, 6:40 PM

#

is that a question?

regal portal Mar 23, 2019, 6:40 PM

#

Yes

#

Why I have this error?

regal portal Mar 23, 2019, 6:56 PM

#

await cursor.execute("INSERT INTO clans (clan-owner) VALUES (me)")

Hello I have this error

#

I use aiomysql

ember bolt Mar 23, 2019, 7:05 PM

#

@copper sphinx Im trying to allow my AWS server to connect to the postgres server so I can access the database for my bot

copper sphinx Mar 23, 2019, 7:06 PM

#

Alright, so what does your postgres.conf look like for the bind part?

ember bolt Mar 23, 2019, 7:07 PM

#

lemme open the file

#

@copper sphinx https://i.imgur.com/v5EvjLw.png

Imgur

copper sphinx Mar 23, 2019, 7:11 PM

#

Good, and how about pg_hba.conf?

ember bolt Mar 23, 2019, 7:12 PM

#

https://i.imgur.com/Q4CMXi8.png

Imgur

copper sphinx Mar 23, 2019, 7:12 PM

#

Does your AWS server have an IPv6 address by any chance?

ember bolt Mar 23, 2019, 7:12 PM

#

i wouldnt know how to check

copper sphinx Mar 23, 2019, 7:12 PM

#

On the aws server, SSH into it and do ip addr show

#

If any of the addresses start with 2001, you got one.

#

I mean, if the server is based of Linux.

ember bolt Mar 23, 2019, 7:13 PM

#

its a windows server

copper sphinx Mar 23, 2019, 7:13 PM

#

Fuu, alright, then ipconfig on a terminal.

ember bolt Mar 23, 2019, 7:13 PM

#

okay one sec

regal portal Mar 23, 2019, 7:14 PM

#

cursor.execute("SELECT `nom_du_clan` FROM `clans` WHERE `clan-members` LIKE %%s%", (user.id, ))ValueError: incomplete format

copper sphinx Mar 23, 2019, 7:14 PM

#

Just so we know if there needs to be a rule that accomidates that too.

ember bolt Mar 23, 2019, 7:14 PM

#

okie

regal portal Mar 23, 2019, 7:14 PM

#

What is the error?

copper sphinx Mar 23, 2019, 7:14 PM

#

That too.

ember bolt Mar 23, 2019, 7:15 PM

#

https://i.imgur.com/93nrCoX.png

Imgur

copper sphinx Mar 23, 2019, 7:15 PM

#

Also, that host all all 0.0.0.0/32 md5 line is not recommended.

ember bolt Mar 23, 2019, 7:16 PM

#

ik , I think Volcyyy told me that but I just wanted to thing to work then I could adjust it

copper sphinx Mar 23, 2019, 7:16 PM

#

Okey, what is the exact error you're getting?

#

For the database

ember bolt Mar 23, 2019, 7:17 PM

#

lemme check

copper sphinx Mar 23, 2019, 7:17 PM

#

Let's forget about the ip for now.

ember bolt Mar 23, 2019, 7:18 PM

#

https://i.imgur.com/95z8NC5.png

Imgur

copper sphinx Mar 23, 2019, 7:18 PM

#

Umm, is that the correct IP even? Does not look like correct.

#

Oh, it is.

#

Just checked.

ember bolt Mar 23, 2019, 7:19 PM

#

okie , im happy it is xd

copper sphinx Mar 23, 2019, 7:19 PM

#

Okey, then on the server running postgres, what os is it?

ember bolt Mar 23, 2019, 7:20 PM

#

idk how to check , its just a basic postgres one I think

copper sphinx Mar 23, 2019, 7:21 PM

#

I mean, is it a Postgres image from AWS console or something else?

ember bolt Mar 23, 2019, 7:21 PM

#

wdym by that?

copper sphinx Mar 23, 2019, 7:21 PM

#

I mean that I would need to know what OS the server is running so I know how to restart the service, as you would need to restart the postgres service AND allow it though the AWS firewall.

ember bolt Mar 23, 2019, 7:22 PM

#

my aws server is windows 10

copper sphinx Mar 23, 2019, 7:22 PM

#

The server running postgres is windows 10?

ember bolt Mar 23, 2019, 7:22 PM

#

wait

#

ill get the actual thingy

#

https://i.imgur.com/jBsEjHn.png

Imgur

river barn Mar 23, 2019, 7:22 PM

#

Did you put in the config file that the DB can receive connections from other places then localhost?

copper sphinx Mar 23, 2019, 7:23 PM

#

Okey, where is your Postgres instalation?

ember bolt Mar 23, 2019, 7:23 PM

#

I copied the config over from my pc @river barn as I used atom to edit it

river barn Mar 23, 2019, 7:23 PM

#

And?

ember bolt Mar 23, 2019, 7:24 PM

#

it is C:\Program Files\PostgreSQL

#

I thought you was asking me?

river barn Mar 23, 2019, 7:24 PM

#

Yes but that doesn't answer my question

ember bolt Mar 23, 2019, 7:24 PM

#

wait nvm I read it wrong

river barn Mar 23, 2019, 7:24 PM

#

Without editing it a psql db can only accept connection from localhost

ember bolt Mar 23, 2019, 7:24 PM

#

I thought I did , but apparently it isnt right

#

https://i.imgur.com/caAYzRD.png

Imgur

copper sphinx Mar 23, 2019, 7:25 PM

#

On a terminal, do netstat -tulp to check if it's listening.

ember bolt Mar 23, 2019, 7:26 PM

#

on my aws server?

copper sphinx Mar 23, 2019, 7:26 PM

#

The one running Postgres

ember bolt Mar 23, 2019, 7:27 PM

#

Works on my local host , not on my aws server

#

https://i.imgur.com/tJxTAUG.png

Imgur

copper sphinx Mar 23, 2019, 7:27 PM

#

Have I understood correctly, the Postgres and Bot are on the same machine?

ember bolt Mar 23, 2019, 7:27 PM

#

yes they are

#

however, I can only connect to my server via my own pc

copper sphinx Mar 23, 2019, 7:28 PM

#

That's odd that that command does not work on the server.

ember bolt Mar 23, 2019, 7:28 PM

#

https://i.imgur.com/oJJO3eC.png

Imgur

copper sphinx Mar 23, 2019, 7:29 PM

#

https://discordapp.com/channels/267624335836053506/342318764227821568/559093593382584321 try this with the IP address 127.0.0.1

ember bolt Mar 23, 2019, 7:29 PM

#

I can try reinstalling postgres admin and seeing if it works better

#

should I edit the config file?

copper sphinx Mar 23, 2019, 7:29 PM

#

No.

#

Just pgadmin with the ip address 127.0.0.1

#

I mean, try connecting to the Postgres with pgadmin, without the external IP.

ember bolt Mar 23, 2019, 7:38 PM

#

how would I do that?

#

@copper sphinx https://i.imgur.com/ulg7Kik.png

Imgur

copper sphinx Mar 23, 2019, 7:42 PM

#

Noo, I meant on the server settings

ember bolt Mar 23, 2019, 7:43 PM

#

ohhhh

#

where do i put it @copper sphinx https://i.imgur.com/cCuERAl.png

Imgur

copper sphinx Mar 23, 2019, 7:44 PM

#

Ah, so it already works with that IP, as localhost is the same thing.

#

So wait what, is the Postgres on your LOCAL MACHINE?

ember bolt Mar 23, 2019, 7:44 PM

#

I tried on my aws server thing

regal portal Mar 23, 2019, 7:45 PM

#

How can I use the LIKE %% by putting a variable between %%?

ember bolt Mar 23, 2019, 7:45 PM

#

yea but I want to connect to it via my aws server

copper sphinx Mar 23, 2019, 7:45 PM

#

Okey, I strongly recommend you to install it onto the AWS server and get rid of the local instalation.

#

Then when developing the bot, you can connect from your local machine to the AWS Postgres.

ember bolt Mar 23, 2019, 7:47 PM

#

wait so am I making a new postgres on my aws server?

copper sphinx Mar 23, 2019, 7:47 PM

#

It would be recommended approach, unless your own machine is 24/7 up.

ember bolt Mar 23, 2019, 7:47 PM

#

yeah its not

#

okay , ill uninstall and reinstall it now

#

brb

#

@copper sphinx https://i.imgur.com/zqI5ZbT.png is this bad?

Imgur

copper sphinx Mar 23, 2019, 8:00 PM

#

Umm, why from there?

ember bolt Mar 23, 2019, 8:00 PM

#

wdym?

copper sphinx Mar 23, 2019, 8:01 PM

#

Please use official installers. https://www.postgresql.org/download/windows/

ember bolt Mar 23, 2019, 8:01 PM

#

I did

#

I went from the postgesql site

#

and it forwarded me to the enterprise site

copper sphinx Mar 23, 2019, 8:01 PM

#

https://www.openscg.com/bigsql/postgresql/installers.jsp/

#

Oof, I see.

#

The first link is to enterpriseDB.

#

Okey, so does it give you any indication as to why?

ember bolt Mar 23, 2019, 8:03 PM

#

No, ive just uninstalled it, ill retry the one u linked

copper sphinx Mar 23, 2019, 8:03 PM

#

https://webkul.com/blog/postgresql-windows-installation-problem-running-post-install-step-installation-may-not-complete-correctly/

Webkul Blog

Mohit Chandra

PostgreSQL Windows Installation Problem running post-install step....

Yesterday, when I install PostgreSQL on Windows 7, it always report the following error: [stextbox id=”alert”]Problem running the post-install step. Installation may not complete correctly.[/stextbox] … when the installation progress bar reached the end. After doing a b...

ember bolt Mar 23, 2019, 8:03 PM

#

that wasnt my error though

#

wait nvm

#

it was

#

im stupid .

#

@copper sphinx the thingy doesnt work for some reason , the cmd command didnt work and theres no "unknown user"

copper sphinx Mar 23, 2019, 8:07 PM

#

Okey, then just try the other installer.

ember bolt Mar 23, 2019, 8:07 PM

#

okie

#

@copper sphinx https://i.imgur.com/W5VjxRZ.png

Imgur

copper sphinx Mar 23, 2019, 8:09 PM

#

Well, that's "great". This is why I don't install anything on Windows servers, but umm, can you find the log file?

ember bolt Mar 23, 2019, 8:09 PM

#

idk how to find it

copper sphinx Mar 23, 2019, 8:09 PM

#

Is there somewhere a postgres directory?

#

Like C:\Program Files etc

ember bolt Mar 23, 2019, 8:09 PM

#

oh yeah

#

okay

#

no because I uninstalled the prostgres and that didnt install , theres no directory

#

@copper sphinx I can try again w the other installer and if I get the error try regardless?

copper sphinx Mar 23, 2019, 8:15 PM

#

Somewhere is supposed to be a log file.

#

How about the same directory where the installer is?

ember bolt Mar 23, 2019, 8:16 PM

#

thats in downloads

copper sphinx Mar 23, 2019, 8:16 PM

#

Yes, but is there a log file?

ember bolt Mar 23, 2019, 8:16 PM

#

nope:(

copper sphinx Mar 23, 2019, 8:27 PM

#

:(

#

I don't know how to help you, sorry.

ember bolt Mar 23, 2019, 8:28 PM

#

its fine, ill try and use the other installer again

#

ill see if it works and ill inform you if it does

copper sphinx Mar 23, 2019, 8:28 PM

#

If it would be me, I would just install a Linux server rather than try to get it working in Windows.

#

But I understand that it's not always an option.

ember bolt Mar 23, 2019, 8:29 PM

#

idk how to use linux , as I prefer being able to remotly connect and be able to do stuff like an actual PC

copper sphinx Mar 23, 2019, 8:29 PM

#

Yeah, Linux is managed useally just by a terminal.

ember bolt Mar 23, 2019, 8:30 PM

#

yeah , im not v good at that as I cant see anything running and id be here more than I would via windows xD

#

@copper sphinx it installed :D

copper sphinx Mar 23, 2019, 8:37 PM

#

Good. :D

floral kraken Mar 23, 2019, 8:38 PM

#

Any of yall know how to merge two tables by row based on a column which is the same in both, but they are not ordered the same way?

ember bolt Mar 23, 2019, 8:39 PM

#

@copper sphinx so what should I do now?

copper sphinx Mar 23, 2019, 8:39 PM

#

Start the Postgres

ember bolt Mar 23, 2019, 8:39 PM

#

okie

copper sphinx Mar 23, 2019, 8:39 PM

#

Or rather first make sure the bind address is correct, and add your OWN IP address to the PG_HBA file

ember bolt Mar 23, 2019, 8:39 PM

#

wdym by that

#

(sorry im slow, im new to databases)

copper sphinx Mar 23, 2019, 8:40 PM

#

So first, go to the Postgres folder, find postgres.conf and edit it, oki?

ember bolt Mar 23, 2019, 8:40 PM

#

https://i.imgur.com/4PBL33V.png

Imgur

#

im so fucking done

copper sphinx Mar 23, 2019, 8:40 PM

#

Ofc that would say that.

#

As you don't have it anymore there.

ember bolt Mar 23, 2019, 8:40 PM

#

No , i reinstalled the postgres

copper sphinx Mar 23, 2019, 8:41 PM

#

So confusing. Please tell me WHERE you reinstalled it?

ember bolt Mar 23, 2019, 8:41 PM

#

I deleted all postgres files from the program files and reinstalled it into the default place it wanted too

copper sphinx Mar 23, 2019, 8:42 PM

#

On which system?

ember bolt Mar 23, 2019, 8:42 PM

#

the aws server

copper sphinx Mar 23, 2019, 8:43 PM

#

Okey, then go the the folder it was installed into.

ember bolt Mar 23, 2019, 8:43 PM

#

there

copper sphinx Mar 23, 2019, 8:43 PM

#

Find the postgresql.conf file.