Python code can't connect to MySQL railway
Does anyone have any experience with creating a scheduling bot?
I want a bot that posts facts in a set schedule.
All this isn't an issue, I got that
The issue is when the bot goes offline for a few seconds the schedule is lost.
So I tried to put the schedule in a SQL database
I use Github and Railway,
However I can't seem to properly connect to MySQL database in railway.
I checked the variables about 20times now
I even deleted everything and started over, but still I can't manage to connect.
I am a beginner so I think I'm missing something, overlooking something I just have no idea what.
If anyone has any advice I would love to hear it 🙂
27 Replies
Project ID:
2e253b3c-55fc-46c6-806c-f9d32de29a882e253b3c-55fc-46c6-806c-f9d32de29a88
Is the interval the same between posts? Ie. every 10 minutes?
hey, you could probably use Railway's cron jobs for this
https://docs.railway.app/reference/cron-jobs
its really easy to setup and monitor
the interval can be defined by the user
daily at X time
2 daily at X time
or weekly at X time
so yes the intervals are the same but have to be determined by the user, not the bot
oh its per user and not a fixed time
Are you trying to connect to the database from your local machine, or from a service deployed on railway?
no per discord server. (sorry)
not local
I use github and railway
and only 1 schedule an be active so everytime you set a schedule the previous one gets overwritten.
all that I got covered in my first code
But then I ran into the issue that if railway has a "hick up" and the bot loses connection the schedule is lost.
what do you mean by "schedule is lost"? its erased from the database?
at first I didn't have a database, just memory
but then the schedule got lost so someone suggested saving the schedule in an SQL database to prevent losing it after loosing connection
I had a perfectly working code just the way I wanted it
But then railway went offline for a few seconds and the schedule got lost
And then it hit me that that is not convenient 🙈
what error are u getting when connecting to mysql? (you could probably use sqlite for this tbh)
I'm getting a connection error when trying to connect to my MySQL database on Railway. The error states 'Access denied for user 'root'@'...' (using password: YES)'. I’ve checked the credentials multiple times and even tried starting over with a fresh setup, but I still can’t connect.
send your mysql connection code
def create_connection():
connection = None
try:
connection = mysql.connector.connect(
host=os.environ.get("DB_HOST"),
user=os.environ.get("DB_USERNAME"),
password=os.environ.get("DB_PASSWORD"),
database=os.environ.get("Database")
)
print("Connection to MySQL DB successful")
except Error as e:
print(f"The error '{e}' occurred")
return connection
did you do a print of all the env variables in there at runtime in Railway? maybe
Database is causing case sensitive issuesokay I'm making progress, I changed the Database to database and I am getting a bit further 🙂
I will try some other stuff.
Thank you for the help (sometimes a second pair of eyes does wonders)
great! i would recommend you to follow the convention of every env being uppercase, it prevents those pitfalls
you're also missing the database port variable
I successfully connected to the database using your feedback—thank you! However, I'm encountering a NameError indicating that guild_id is not defined during the insertion process.
I have the following code for creating the schedules table:
cursor.execute('''
CREATE TABLE IF NOT EXISTS schedules (
guild_id BIGINT PRIMARY KEY,
channel_id BIGINT NOT NULL,
hour INT NOT NULL,
minute INT NOT NULL,
timezone VARCHAR(50) NOT NULL,
frequency ENUM('daily', '2-daily', 'weekly') NOT NULL
)
''')
And this for the callback:
async def schedule_callback(interaction, frequency, hour, minute, timezone):
await interaction.response.defer()
ctx = interaction.channel guild_id = interaction.guild.id
ctx = interaction.channel guild_id = interaction.guild.id
whats your insertion code
connection = create_connection()
cursor = connection.cursor()
cursor.execute('''
INSERT INTO schedules (guild_id, channel_id, hour, minute, timezone, frequency)
VALUES (%s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
guild_id = VALUES(guild_id), # Adding guild_id here
channel_id = VALUES(channel_id),
hour = VALUES(hour),
minute = VALUES(minute),
timezone = VALUES(timezone),
frequency = VALUES(frequency)
''', (guild_id, channel_id, hour, minute, timezone, frequency))
connection.commit()
cursor.close()
connection.close()
try this one:
its been a while since i've written a mysql query 😄
or maybe this one:
cursor.execute('''
INSERT INTO Schedules (guild_id, channel_id, hour, minute, timezone, frequency)
VALUES (%s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
guild_id = VALUES.guild_id,
channel_id = VALUES.channel_id,
hour = VALUES.hour,
minute = VALUES.minute,
timezone = VALUES.timezone,
frequency = VALUES.frequency
''', (guild_id, channel_id, hour, minute, timezone, frequency))
connection.commit()
cursor.close()
connection.close()
this gave the same error as before.
weird, its activately saying that guild_id is not defined, can you do a print on guild_id?
and just to be sure, its right below this?
async def schedule_callback(interaction, frequency, hour, minute, timezone):
await interaction.response.defer()
if frequency == "daily":
days_interval = 1
elif frequency == "2-daily":
days_interval = 2
elif frequency == "weekly":
days_interval = 7
ctx = interaction.channel
guild_id = interaction.guild.id
channel_id = ctx.id # Get the current channel ID
await interaction.followup.send(f"Scheduling {frequency} posts at {hour}:{minute} in {timezone}.", ephemeral=True)
# Print values for debugging
print(f"Guild ID: {guild_id}")
print(f"Channel ID: {channel_id}")
print(f"Hour: {hour}")
print(f"Minute: {minute}")
print(f"Timezone: {timezone}")
print(f"Frequency: {frequency}")
# Ensure only one schedule exists for each guild
reset_schedule_for_guild(guild_id)
#Save the new task
scheduled_tasks[guild_id] = asyncio.create_task(schedule_fact(ctx, int(hour), int(minute), timezone, days_interval, guild_id))
#Save schedule to the database (with channel_id)
connection = create_connection()
cursor = connection.cursor()
cursor.execute('''
INSERT INTO schedules (guild_id, channel_id, hour, minute, timezone, frequency)
VALUES (%s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
guild_id = VALUES.guild_id,
channel_id = VALUES.channel_id,
hour = VALUES.hour,
minute = VALUES.minute,
timezone = VALUES.timezone,
frequency = VALUES.frequency
''', (guild_id, channel_id, hour, minute, timezone, frequency))
connection.commit()
cursor.close()
connection.close()
I think I'm going crazy 🙈
Going to close this out as we can't provide coding assistance here.
I would suggest searching sites like stack overflow for these kinds of questions.