MySQLdb and python

This is the place for queries that don't fit in any of the other categories.

MySQLdb and python

Postby sheffieldlad » Sat Feb 09, 2013 4:23 pm

Hi all,

I'm trying to insert information into a MySQL database.

I've created the database and a table. I've also created two rows. A primary key and a row called url to hold the data I'm trying to write into the DB.
All is good....Sort of...

I can establish a connection like so.....

Code: Select all
try:

    con = MySQLdb.connect('localhost','sheffieldlad','pass','urls');
    cur = con.cursor()
    cur.execute("SELECT VERSION()")
   
    data = cur.fetchone()
   
    print "Database version : %s " % data
   
except MySQLdb.Error, e:
 
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)



The script returns the version of the database.

My problems begin when I try to insert data into a row.

This is the code I am using to insert.....


Code: Select all
cur.execute("""INSERT INTO links(url) VALUES('crawling')""")
      con.commit()



Nothing happens. My script gives no error but nothing is inserted into my database.

Can anyone spot my mistake?

TIA

Paul.
Python 2.7
Windows XP
sheffieldlad
 
Posts: 37
Joined: Sat Feb 09, 2013 3:03 pm
Location: UK

Re: MySQLdb and python

Postby Yoriz » Sat Feb 09, 2013 7:50 pm

I use mysql but with sqlalchemy so I'm not used dealing directly with execute.
Does the following work ?
Code: Select all
cur.execute ("INSERT INTO links (url) VALUES (%s) ",  ('crawling'))
New Users, Read This
Join the #python-forum IRC channel on irc.freenode.net!
Spam topic disapproval technician
Windows7, Python 2.7.4., WxPython 2.9.5.0., some Python 3.3
User avatar
Yoriz
 
Posts: 871
Joined: Fri Feb 08, 2013 1:35 am
Location: UK

Re: MySQLdb and python

Postby stranac » Sat Feb 09, 2013 7:54 pm

If you're getting no error, your code should be working.

Can you show us the entire code you're using?
Both to insert the data into the database, and to get it from it.

Maybe that will help us see what's going on.


As Yoriz is showing, sql parametrization is always a good idea, but I'm not sure that's what is causing your problem.

Also, I believe oursql is still the recommended python library for dealing with mysql.
Friendship is magic!

R.I.P. Tracy M. You will be missed.
User avatar
stranac
 
Posts: 1155
Joined: Thu Feb 07, 2013 3:42 pm

Re: MySQLdb and python

Postby sheffieldlad » Sat Feb 09, 2013 9:15 pm

Many thanks for replying.
I haven't started pulling information from the database yet but when I look in phpmyadmin the table is empty.
That is my full code for now. Debug shows no errors when I step through the code and the script exits with no output other than what I print to the screen.
A PHP page on a website I'm developing will handle db requests, I'm only trying to use python to add information to the datab
I will look into using the modules you reccomend instead of mysqldb.

Thanks again.
sheffieldlad
 
Posts: 37
Joined: Sat Feb 09, 2013 3:03 pm
Location: UK

Re: MySQLdb and python

Postby sheffieldlad » Sat Feb 09, 2013 9:16 pm

Sorry, I forgot to add I'm not in front of a computer at the moment but I will try the code suggested and report back :) many thanks.
Python 2.7
Windows XP
sheffieldlad
 
Posts: 37
Joined: Sat Feb 09, 2013 3:03 pm
Location: UK

Re: MySQLdb and python

Postby darter » Sun Feb 10, 2013 5:32 am

I looked at some of my old code:

Maybe you need the ?,?,? as placeholders for the data ;

and the close.

Code: Select all

cur.execute("INSERT INTO albumcrate2 ( folder1,folder2,folder3,filescounted,format,artist)  VALUES (?, ?,?,?,?,?)", stuff)
                            print "now in itunes stuff"           

                        if place == "C:\Music B" and stuff[0] != "C:\Music B" :
                            cur.execute("INSERT INTO albumcrate3 ( folder1,folder2,folder3,filescounted,format,artist)  VALUES (?, ?,?,?,?,?)", stuff)           
               

                    con.commit()
                    con.close()
darter
 
Posts: 7
Joined: Sun Feb 10, 2013 5:19 am

Re: MySQLdb and python

Postby sheffieldlad » Tue Feb 12, 2013 11:59 pm

I'm still struggling with this...

Here is the code I am currently working with....

Code: Select all
ls="foobar"
""" The begining of the DB setup..."""
con = None

try:

    db_connection = oursql.connect(host='127.0.0.1',user='user',passwd='pass',db='urls')
    print "Connected to SQL Server"
    cur=db_connection.cursor()
    print "Reading current DB Entries"
    cur.execute("SELECT * FROM `links`")
    for row in cur.fetchall():
        print row[0]
   
except oursql.Error, e:
 
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

cur.execute('INSERT INTO links(url) VALUES (%s)'(ls))


Gives the traceback
TypeError: 'str' object is not callable

Code: Select all
cur.execute('INSERT INTO links(url) VALUES (%s)', ls)


Gives the traceback
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 '%s)' at line 1", None)

as does
Code: Select all
cur.execute('INSERT INTO links(url) VALUES (%s)', (ls))


Can someone point me in the right direction for the correct syntax please?
I'm trying to add 1 the string ls into one column of data called url in a table called links.

Many thanks.
Python 2.7
Windows XP
sheffieldlad
 
Posts: 37
Joined: Sat Feb 09, 2013 3:03 pm
Location: UK

Re: MySQLdb and python

Postby sheffieldlad » Wed Feb 13, 2013 12:03 am

sorry forgot to mention... using oursql now instead of MySQLdb
Python 2.7
Windows XP
sheffieldlad
 
Posts: 37
Joined: Sat Feb 09, 2013 3:03 pm
Location: UK

Re: MySQLdb and python

Postby stranac » Wed Feb 13, 2013 12:18 am

Code: Select all
cur.execute('INSERT INTO links(url) VALUES (%s)'(ls))

You're missing a comma there.
That's why python thinks you're trying to call the string 'INSERT INTO links(url) VALUES (%s)' with an argument ls.

And (ls) is not a tuple, it's just a string surrounded by parens.
(ls, ) is how you make a tuple with a single element.

Also, oursql uses ? as placeholder, not %s.
I would suggest going through their tutorial, to catch any other differences with what you were using before.
Friendship is magic!

R.I.P. Tracy M. You will be missed.
User avatar
stranac
 
Posts: 1155
Joined: Thu Feb 07, 2013 3:42 pm

Re: MySQLdb and python

Postby sheffieldlad » Wed Feb 13, 2013 9:37 am

stranac wrote:
Code: Select all
cur.execute('INSERT INTO links(url) VALUES (%s)'(ls))

You're missing a comma there.
That's why python thinks you're trying to call the string 'INSERT INTO links(url) VALUES (%s)' with an argument ls.

And (ls) is not a tuple, it's just a string surrounded by parens.
(ls, ) is how you make a tuple with a single element.

Also, oursql uses ? as placeholder, not %s.
I would suggest going through their tutorial, to catch any other differences with what you were using before.



In the first one I'm missing a comma sure but in the other 2 the comma is there.
I'll try again with (?) rather than (%s)
The tutorial covers inserting large strings with a wrapper but doesn't really mention the simple stuff....

Thanks for the help :)
Python 2.7
Windows XP
sheffieldlad
 
Posts: 37
Joined: Sat Feb 09, 2013 3:03 pm
Location: UK

Re: MySQLdb and python

Postby stranac » Wed Feb 13, 2013 11:31 am

sheffieldlad wrote:In the first one I'm missing a comma sure but in the other 2 the comma is there.

One time is enough for your script to throw an exception.

sheffieldlad wrote:The tutorial covers inserting large strings with a wrapper but doesn't really mention the simple stuff....

You just have to read it carefully, and take note of the examples.
You can see all the examples in the Using Cursors section use ? as a placeholder.
Friendship is magic!

R.I.P. Tracy M. You will be missed.
User avatar
stranac
 
Posts: 1155
Joined: Thu Feb 07, 2013 3:42 pm

Re: MySQLdb and python

Postby Froweey » Thu Feb 14, 2013 1:38 am

While utilizing MySQLdb for basic Python scripts might be sufficient, using the module on large-scale projects can be troublesome. What I usually do is create a Django-ORM-standalone and work from there. It compliments large scale applications and allows you to follow Python PEP's more efficiently.

All you need is Django installed on your primary Python distribution and a Django-ORM-standalone application.
Froweey
 
Posts: 2
Joined: Thu Feb 14, 2013 1:32 am

Re: MySQLdb and python

Postby stranac » Thu Feb 14, 2013 11:30 pm

Froweey wrote:While utilizing MySQLdb for basic Python scripts might be sufficient, using the module on large-scale projects can be troublesome. What I usually do is create a Django-ORM-standalone and work from there. It compliments large scale applications and allows you to follow Python PEP's more efficiently.

All you need is Django installed on your primary Python distribution and a Django-ORM-standalone application.

The Django ORM is actually pretty bad.
It's probably the worst part of Django.

If you need an ORM, use SQLAlchemy instead.
Friendship is magic!

R.I.P. Tracy M. You will be missed.
User avatar
stranac
 
Posts: 1155
Joined: Thu Feb 07, 2013 3:42 pm


Return to General Coding Help

Who is online

Users browsing this forum: snippsat and 2 guests