Jump to content
Team Avolition
Sign in to follow this  
revan114

how 2 sql

Recommended Posts

revan114    1186

So for the past few days I've been working on designing my own webhole, and I've finished the design side(kinda)and now I wanna start work on getting the functionality down. I'm trying to have a part of the site where I can upload stories I write, mostly because I just want to have a place to put the shit I write. I figured I'd need a database.

 

I COULD just dump html docs in and just do it that way but that's too tedious and it'll get too cramped. I wanna do this as elegantly as possible. Plus, I'd like to learn how to into databases.

 

So I know I need a database of stories, and it also needs to have an account for me to sign in. I'm starting with the login first, because I already have the template done and it works. All I have to do is make the database and query it for the account.

 

Problem is: I have no idea what I'm doing with this SQL shit. There's alot of stuff about it that is confusing me so far. Stuff like SQLAlchemy, SQLite, MySQL, it's all a clusterfuck to me so far.

 

From what I understand, if I were to use SQLite, then SQLite would be the engine, SQLAlchemy would be the ORM to map the database as objects(?), and SQL is just the syntax.

 

But I have no idea how to create the actual database, or do all the things. I'm just not sure where to start. It seems like databases as a topic is just such a massive amount of shit that I need to understand.

Share this post


Link to post
Narka    614

SQL is Structured Query Language. The syntax is roughly the same between all of them. Do yourself a favor and avoid SQLite *for this specific application*. SQLite is incredibly useful for applications where the end user would not normally have a database engine installed, or when you want to include a specific version in your software. For example, Firefox uses SQLite for storing bookmarks; Average Joe would have no previous engine installed and the packaged one is tailored to work well with Firefox.

 

Go with something like MySQL or MariaDB. They're well documented and easy to start with. If you want to learn what exactly you're doing, find some tutorials online or pick up the O'Reilly Learning SQL book. If you want your life to be easier and to focus more on your CMS than the database, also install Adminer, have it do the heavy lifting (which most CMSs will have you do anyway, because it's highly situational and if you wanted to force a solution you would have picked SQLite), and then just worry about the tables, data, and structure.

 

Try installing Wordpress on a Linux machine. The installation is really simple and you can do a quick post-mortem and glean just about everything you need to know about how you would use an SQL DB in this situation.

Share this post


Link to post
revan114    1186

I should probably say now that I managed to get my site working within a day of posting this. Using MySQL without an ORM.

 

I think about 90% of the SQL queries I use are fucking horrible and might be prone to injection or bad things, but I have no idea.

 

 

@app.route('/login', methods=['GET', 'POST'])
def login():
if request.method == 'POST':
 _username = request.form['username']
 _password = request.form['password']
 cursor = mysql.connect().cursor()
 cursor.execute("SELECT * from users where username='" + _username + "' and password='" + _password + "'")
 data = cursor.fetchone()
 if data is None:
	 return render_template("login.html", error="Invalid login credentials.")
 else:
	 session['logged_in'] = True
	 return redirect(url_for('index'))
return render_template("login.html")

 

 

 

That's how I decide my logins. Uses Flask session and it's fucking horrible. I really don't know how else to do that shit though. I guess if it works it works. :#

 

Anyways,

For the sake of showing what I've done so far, here is the story submission page, the story list page, and the story read pages.

 

 

#redesigned

8i96OFC.png

story submission

slnAiTy.png

story listing

HlpeJwx.png

and finally the actual story page

NcAy261.png

 

 

I have alot of work to do before it's actually presentable(the fucking fonts for one thing need massive loads of changes, and the color scheme as well obviously), but right now all the basics are done and ready to go.

Compared to what my ability to design shit was about 3 weeks ago...

vGEk9W1.png

I'm happy with how I'm progressing so far in my retarded journey of web design land.

Edited by revan114
  • Upvote 1

Share this post


Link to post
revan114    1186

Actually I'm just going to scrap those three HTML documents entirely and do them from the beginning, since I was using bootstrap components I only just learned about. If you saw all the fucking horrible quirks I have to deal with you'd probably take a swig of alcohol.

 

did all but the stories list

Edited by revan114

Share this post


Link to post
revan114    1186

Gotta link that shit for the mouse-over text (not that it's really relevant here, but come on, it's xkcd).

 

Alright here's my new login function using bcrypt:

 

@app.route('/login', methods=['GET', 'POST'])
def login():
if request.method == 'POST':
 _username = request.form['username']
 _password = request.form['password']
 cursor = mysql.connect().cursor()
 cursor.execute("SELECT * from users where username=%s", _username)
 data = cursor.fetchone()
 if data is None or bcrypt.check_password_hash(data[2], _password) == False:
	 return render_template("login.html", error="Invalid Login Credentials.")
 else:
	 session['logged_in'] = True
	 return redirect(url_for('index'))
return render_template("login.html")

Edited by revan114

Share this post


Link to post
mmaton    175

RAKIRU YOU FUCKER WHAT THE FUCK WHY NO IRC EVER. WANKER.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×