MySQL Forums - Python

sys_exec () can run a python program (no replies)

Luca Lucchesi — Tue, 21 May 2013 14:39:24 +0000

hello,

for test reasons, I have a python program who writes to a file. txt date and the current time.

select sys_exec('python /var/test/script/aa.py');

I gave chown mysql:mysql aa.py and chmod 777 aa.py

result is:
+-------------------------------------------+
| sys_exec('python /var/test/script/aa.py') |
+-------------------------------------------+
| 0 |
+-------------------------------------------+
1 row in set (0.23 sec)

but nothing appears in the text file

will you give me some suggestions please?

thanks

MySQL TRIGGER and python (1 reply)

Luca Lucchesi — Thu, 09 May 2013 14:37:01 +0000

Hello,
I'm trying to understand how the MySQL TRIGGER work and if they can be useful for my purpose.

I would like to monitor the UPDATE of data in a table.
trying to print on screen the changed data and all the line pertaining to.

could I have an example of TRIGGER and python?

Thank you very much

Luca

INSERT with SELECT (1 reply)

Philippe Idlas — Wed, 01 May 2013 02:22:37 +0000

Hi,

I run a python program which takes a flat log file as input to load it into a database in order for administrators to monitor their application.

I have five tables : server table with two columns (id and name), status table with two columns, directive table with two columns, rule table with two columns and finally a server_status_directive_rule table with five columns (id_server, id_status, id_directive, id_rule, date).

I can easily insert into the first four tables.
My problem is with trying to insert a variable into the fifth table.
The code is :




query = """INSERT INTO serveur_etat_directive_regle (id_serveur, id_etat, id_directive, id_regle, date)


SELECT id_serveur, id_etat, id_directive, id_regle, %s FROM serveur, etat, directive, regle


WHERE nom_serveur = %s AND nom_etat = %s AND nom_directive = %s AND nom_regle = %s"""


cursor.execute(query, (serveur, etat, directive, regle, date,))

The problem is with the date column. If I remove it from my INSERT and subsequently in the SELECT and cursor.execute, the table is filled in properly.

The other thing to know is I have no error message returned although I do manage error messages. My python code runs with no messages and my table is still empty at the end.

The complete SQL code is below :




     try:


            # accès et mise à jour de la BDD


            db = mdb.connect('localhost', 'root', '', 'rudder');


            cursor = db.cursor()


            query = """INSERT IGNORE INTO serveur (nom_serveur) VALUES (%s)"""


            cursor.execute(query, (serveur,))


            query = """INSERT IGNORE INTO etat (nom_etat) VALUES (%s)"""


            cursor.execute(query, (etat,))


            query = """INSERT IGNORE INTO directive (nom_directive) VALUES (%s)"""


            cursor.execute(query, (directive,))


            query = """INSERT IGNORE INTO regle (nom_regle) VALUES (%s)"""


            cursor.execute(query, (regle,))


            query = """INSERT INTO serveur_etat_directive_regle (id_serveur, id_etat, id_directive, id_regle, date)


                    SELECT id_serveur, id_etat, id_directive, id_regle, %s FROM serveur, etat, directive, regle


                    WHERE nom_serveur = %s AND nom_etat = %s AND nom_directive = %s AND nom_regle = %s"""


            cursor.execute(query, (serveur, etat, directive, regle, date,))


        except mdb.Error, e:


            print "Error %d: %s" % (e.args[0],e.args[1])


            sys.exit(1)


        db.commit()

Any help would be much appreciated.

KR.

TypeError when inserting a date (no replies)

Philippe Idlas — Thu, 25 Apr 2013 09:35:24 +0000

Hi,

As far as I know I can insert a date into MySQL as long as the format corresponds to the date field attribute.
My date column in MySQL is declared as datetime.
So if I insert a date like '2013-04-21 11:29:00' into it it should work fine.
Indeed when I insert using phpmysql it works but when I insert from my python programm it fails with the following error message :

Traceback (most recent call last):
File "C:\Users\pidlas\Documents\rudder_find.py", line 58, in
extract(info)
File "C:\Users\pidlas\Documents\rudder_find.py", line 46, in extract
cursor.execute(query, (serveur, etat, directive, regle, date,))
File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 183, in execute
query = query % db.literal(args)
TypeError: not all arguments converted during string formatting

I have tried different ways of formatting my python date variable but I could not until now found the right thing to do.

I would appreciate any ideas or thoughts.

Regards.

My python code follows :

# -*- coding: utf-8 -*-
# modules nécessaires
from os import chdir
import MySQLdb as mdb
import sys
import datetime

# variables pour la BDD
serveur = ''
etat = ''
regle = ''
directive = ''

# répertoire où se trouve les logs
chdir("c:/Users/pidlas/Documents/rudder/")

def extract(log):
for ligne in log:
debut_date = ligne.find('[')
fin_date = ligne.find(']')
date = ligne[debut_date+1:fin_date-5]
date = datetime.datetime.strptime(date, "%Y-%m-%d %H:%M:%S")
sep_N = ligne.find('N:')
serveur = ligne[ligne.find('[',sep_N)+1:ligne.find(']',sep_N)]
sep_S = ligne.find('S:')
etat = ligne[ligne.find('[',sep_S)+1:ligne.find(']',sep_S)]
sep_R = ligne.find('R:')
regle = ligne[ligne.find('[',sep_R)+1:ligne.find(']',sep_R)]
sep_D = ligne.find('D:')
directive = ligne[ligne.find('[',sep_D)+1:ligne.find(']',sep_D)]

try:
db = mdb.connect('localhost', 'root', '', 'rudder');
cursor = db.cursor()
query = """INSERT IGNORE INTO serveur (nom_serveur) VALUES (%s)"""
cursor.execute(query, (serveur,))
query = """INSERT IGNORE INTO etat (nom_etat) VALUES (%s)"""
cursor.execute(query, (etat,))
query = """INSERT IGNORE INTO directive (nom_directive) VALUES (%s)"""
cursor.execute(query, (directive,))
query = """INSERT IGNORE INTO regle (nom_regle) VALUES (%s)"""
cursor.execute(query, (regle,))
query = """INSERT INTO serveur_etat_directive_regle (serveur, id_etat, id_directive, id_regle, date)
SELECT id_serveur, id_etat, id_directive, id_regle FROM serveur, etat, directive, regle
WHERE nom_serveur = %s AND nom_etat = %s AND nom_directive = %s AND nom_regle = %s"""
cursor.execute(query, (serveur, etat, directive, regle, date,))
except mdb.Error, e:
print "Error %d: %s" % (e.args[0],e.args[1])
sys.exit(1)
db.commit()

info.close()
db.close()
return logfile

if __name__ == "__main__":
with open('non-compliant-reports.log','r') as info:
extract(info)

import sql connector (1 reply)

Paul Fenwick — Wed, 24 Apr 2013 11:42:17 +0000

Hi Folks

I amusing Windows 7 x64, Python 3.3.1, mysql connector 1.0.9

I have tried my code line by line in the Python shell which works fine, I can import the connector and connect to the database and fetch back data, BUT when I save the code as a .py the import mysql.connector does not want to work for some reason, I do have the Path variable setup and when I installed the mySQL connector it placed the relevant files in my Python install path \Lib\site-pacakges folder.

import mysql.connector #this is failing as a .py but works in the shell

cnx = mysql.connector.connect(user='root', password='mypassword', host='my_ip_address', database'name_of_database') #this works in the shell

cursor = cnx.cursor()

I have also tried my .py with just:

import mysql.connector

I get the following error:

Traceback (most recent call last):
File "", line 1518, in _find_and_load_unlocked
AttributeError: 'module' object has no attribute '__path__'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "C:\Users\Paul\Desktop\scripts\mysql.py", line 2, in
import mysql.connector
File "C:\Users\Paul\Desktop\scripts\mysql.py", line 2, in
import mysql.connector
ImportError: No module named 'mysql.connector'; mysql is not a package

I have tried the msi install and the independent install

Cannot display MySQL latin2_croation_ci characters in Python script (3 replies)

Paul Pikowsky — Wed, 24 Apr 2013 15:42:27 +0000

I am trying to apply MySQL records in a Python script. The fields I am concerned with use latin2_croatian_ci collation characters. When I try to print out the following characters,

Karadžić
Stanišić & Simatović
Boškoski & Tarčulovski
Đorđević
Ražnatović, Željko

I get only,

Karadži?
Staniši? & Simatovi?
Boškoski & Tar?ulovski
?or?evi?
Ražnatovi?, Željko

I have tried numerous strategies from both MySQL and Python. In MySQL I have tried both CONVERT and CAST in various combinations. In Python I have tried applying the unicode() function. Nothing seems to work. In Flash, I had to set the embedding to "latin extended A" to solve a similar problem. In the old MySQL Query Browser, I have the same display problems when a try to display the records.

Any and all tips and or clues would be appreciated.

Question with python connector 1064 (42000): You have an error in your SQL syntax; (1 reply)

John Doe — Wed, 24 Apr 2013 11:11:51 +0000

I have a small python program to generate following sql

update AggGMDB set GMDBDIFF1 = ifnull(GMDB200603,0)- ifnull(GMDB200512,0)

but it gives the following error

mysql.connector.errors.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 ''GMDBDIFF1' = ifnull('GMDB200603',0)- ifnull('GMDB200512',0)' at line 1

What am I doing wrong

here is a fragment of the python program

count=0
for column1,column2 in columns:
count +=1
print count
NewColumn = "GMDBDIFF" + str(count)
query = "alter table AggGMDB add "+ NewColumn + " decimal(30,2)"
cursor.execute(query)
query ="update AggGMDB set %s = ifnull(%s,0)- ifnull(%s,0)"
print query
print "Computing the difference between %s and %s" %(column1,column2)
print query %(NewColumn,column1,column2)
cursor.execute(query,(NewColumn,column1,column2))
cnx.commit()
cursor.close()
cnx.close()

Urgent Problem import mysql.connector (11 replies)

Armin Karner — Tue, 12 Mar 2013 13:50:38 +0000

Dear Sir or Madam,
I have an urgent problem with Sublime Text2 using Python.
How can I import mysql.connector to use that stuff in my PlugIn in
Sublime? It's possible to use it in Python IDLE, but in Sublime Text 2
it doesn't work.
I look forward that you can help me.
Actually I don't know where to store the library and how to install it.
Maybe that's the reason.
I'm new in this field and it would be awesome if you could help me.
Thank you very much.
Yours faithfully,
Armin Karner

Wrong number of arguments during string formatting (1 reply)

Dan Survivalist — Tue, 12 Mar 2013 11:44:31 +0000

Been working on a project with the MySQL connector and it's been pretty painful. The docs don't seem to be very complete...

(1) Does dict substitution work if the same value appears multiple times? It seems not to. For example:

INSERT INTO some_table ( id, name ) VALUES ( %(id)s, %(name)s) ) ON DUPLICATE UPDATE some_table SET name = %(name)s WHERE id = %(id)s

Here name is used once, though it's only in the dict once of course.

This has inevitably lead to mysql.connector.errors.ProgrammingError: Wrong number of arguments during string formatting

(2) Is only %s supported? What if I'm inserting into a column that is INTEGER? Use of %i gives the same error. Which leads to goofy things like VALUES ( %s + 0 )

Python/Connect and Placeholders (2 replies)

Shu Chow — Fri, 03 May 2013 06:50:25 +0000

Maybe this is too late in the evening, but this is driving me nuts. I have an SQL statement with one placeholder:

sql = ("SELECT * FROM source AS s INNER JOIN bar AS B ON B.id = bar WHERE s.name = %s")

cursor = conn.cursor()
cursor.execute(sql, (source))

I always get an SQL error: mysql.connector.errors.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 '%s' at line 1

However, when I modify the where clause to take the same variable twice, it works as intended.

sql = ("SELECT * FROM source AS s INNER JOIN bar AS B ON B.id = bar WHERE s.name = %s OR s.name = %s")

cursor = conn.cursor()
cursor.execute(sql, (source, source))

Anyone with an idea of what's going on?

MySQL Connector/Python (1 reply)

PBL Narasimha Rao — Wed, 20 Feb 2013 17:36:15 +0000

Hi,

We are developing app using Python 2.6 and MySQL 5.5.3 as DAtabase Server.

can any one tell where can i get MySql Connector for Python 2.6.

from below post, i find python connector for 2.7,3.3 and 3.2

http://forums.mysql.com/read.php?50,564290,564290#REPLY

I need it for Python 2.6. Till now we used 1.0.5 beta for testing. But it has a InterfaceError (error=2013) bug which was fixed in 1.0.8 according to the release notes.

Let me know if any one know how to get 1.0.8 for Python 2.6

mysql.connector/python vs MySQLdb (2 replies)

Terence Chan — Fri, 22 Feb 2013 01:14:02 +0000

It possible a novice question. If I want to develop Python program to work with MySQL. I should use mysql.connector or MySQLdb? What's the advantage and disadvantage?

Load Data Infile error with local-infile option enabled (1 reply)

Mike Magnuson — Wed, 20 Feb 2013 18:08:30 +0000

Hello,

I'm trying to load a local csv file into my database with the command LOAD DATA LOCAL INFILE '/tmp/myfile.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

I'm getting the following error.
"Error Code: 1148. The used command is not allowed with this MySQL version"

I've looked around and apparently, I need to make changes to my.cnf to allow loading LOCAL files.

I've made all the changes that I've seen required and restarted the mysql server, but I still receive the same error message.

Snippets of my.cnf :::
[client]
local-infile = 1

[mysqld]
local-infile = 1

[mysql]
local-infile = 1

I can also verify that this option gets set when mysql is started because "show variables" returns: local_infile ON.

Everything I know to check is reporting that the local-infile option is enabled, but I still get the error "The used command .... ".

Any ideas on what else I can check?

I'm running Mysql version 5.5.29 on Ubuntu Linux x64 on my local dev machine.

cursor close and db commit (2 replies)

jupiter hce — Wed, 13 Feb 2013 08:20:32 +0000

Hi,

I am using MySQLdb and InnoDB, appreciate anyone help to clarify following questitions:

(1) When I run select sql, I use cursor.execute(sql) without db.commit(), I only use db.commit() for insert and update. Someone said to me that I should use db.commit() in select as well, is it necessary?

(2) Which of following action is a good practice, (a) or (b)?
(a) Open db connect, run cursor execute(), close cursor and close the db.
(b) Open db connect for one WEB session connection to run all cursor execute,
and only close the db when a session is closed.

(3) Should cursor.close() be called before db.commit(), or it does not matter?

(3) For following multiple sql executions, should the cursor close for each execution or when all executions are finished? Which of following statements is correct or better practice (a) or (b), or it does not matter?

(a) Close cursor for each sql execution:

sql = "select ...."
cursor.execute(sql)
rows = cursor.fetchall()
cursor.close()

sql = "insert ..."
cursor.execute(sql)
cursor.close()
db.commit()

sql = "select ..."
cursor.execute(sql)
rows = cursor.fetchall()
cursor.close()

db.close()

(b) Close cursor at last:

sql = "select ...."
cursor.execute(sql)
rows = cursor.fetchall()

sql = "insert ..."
cursor.execute(sql)
db.commit()

sql = "select ..."
cursor.execute(sql)
rows = cursor.fetchall()

cursor.close()
db.close()

(4) In above example, should cursor.close() be called before db.commit(), or it does not matter?

Thank you.

Kind regards.

Error while loading table with text file (2 replies)

Atul Kakrana — Wed, 13 Feb 2013 05:14:37 +0000

I have data in a text file which I need to upload into a table. My script in python 3 and uses mysql.connector (https://launchpad.net/myconnpy) to connect to DB and execute commands. I have been able successfully use mysql.connector in past without any problems but I am facing problem in using the command that uploads file to a table. My code is as follows:

def TableUpload(con2):
cur = con2.cursor()##Connect to destination server with table
res_file = 'extend2'
cur.execute("TRUNCATE TABLE data.results")## Clear table before writing
cur.execute("LOAD DATA LOCAL INFILE './extend2' INTO TABLE data.results FIELDS TERMINATED BY ','")

The code clears the table and than try to upload data from text file to table. It successfully clears the table but generated following error while filling table:

Traceback (most recent call last):
File "cl3.py", line 575, in
TableUpload(con2)
File "cl3.py", line 547, in TableUpload
cur.execute("LOAD DATA LOCAL INFILE './extend2' INTO TABLE kakrana_data.mir_page_results FIELDS TERMINATED BY ','")
File "/usr/local/lib/python3.2/site-packages/mysql/connector/cursor.py", line 333, in execute
res = self.db().protocol.cmd_query(stmt)
File "/usr/local/lib/python3.2/site-packages/mysql/connector/protocol.py", line 137, in deco
return func(*args, **kwargs)
File "/usr/local/lib/python3.2/site-packages/mysql/connector/protocol.py", line 495, in cmd_query
return self.handle_cmd_result(self.conn.recv())
File "/usr/local/lib/python3.2/site-packages/mysql/connector/connection.py", line 180, in recv_plain
errors.raise_error(buf)
File "/usr/local/lib/python3.2/site-packages/mysql/connector/errors.py", line 84, in raise_error
raise get_mysql_exception(errno,errmsg)
mysql.connector.errors.NotSupportedError: 1148: The used command is not allowed with this MySQL version

When I use the command for uploading file directly from terminal than it works well. It is just that command is not working from script. The error says that command is not allowed with mysql version though it works from terminal. Please suggest what mistake I am making or alternative way to achieve data upload to a table from local file.

MySQL Connector/Python Django 1.4 (1 reply)

Dilip M — Tue, 27 Nov 2012 10:01:09 +0000

Hi,

I read that django support is removed. https://launchpad.net/myconnpy/+announcement/3934

Is there a way to get the latest version working with django 1.4?

Thanks, Dilip

Create Stored Procedure using Python plugin in MySQL Workbench (no replies)

Pedro Márquez — Thu, 22 Nov 2012 22:16:53 +0000

Good afternoon,

I'm working on creating open source plugins for MySQL Workbench.

I'm making some tests using the available classes for Python. I've found very usefull tutorials about creating columns and keys in tables.

But what I can't really found is how to create stored procedures using the default classes.

Could you help me to know how to do this?

Thanks.

EDIT:

I asked about this in the IRC and they provide me an answer:

routine = schema.addNewRoutine("db.mysql")
routine.name = 'myroutine'
routine.sqlDefinition = sp_sql_code

And this will add the SP defined in sp_sql_code to the model.

callproc() with parameter doesnt return resultset (2 replies)

Privida Kristiono — Sun, 02 Dec 2012 14:17:06 +0000

I have this simple stored procedure which return resultset

CREATE PROCEDURE spGetProductList()
BEGIN
SELECT ProductName, UnitPrice, UnitsInStock FROM products;
END

When I use callproc() to call these stored procedure, it work fine

h=csr.callproc('spGetProductList')

for result in csr.stored_results():
print (result.fetchall())

But If I add a parameter to stored procedure, it doesnt work

CREATE PROCEDURE spGetProductList( qCategoryId INT)
BEGIN
SELECT ProductName, UnitPrice, UnitsInStock
FROM products
WHERE CategoryId=qCategoryId;
END

I use this code to call these stored procedure, no resultset is returned

args = (2,)
h=csr.callproc('spGetProductList', args)

for result in csr.stored_results():
print (result.fetchall())

Reading microseconds datetime (mysql 5.6.7) (no replies)

nick birren — Mon, 29 Oct 2012 09:27:00 +0000

I'm using python's MySQLdb to fetch rows from a MySQL 5.6.7 db, that supports microsecond precision datetime columns. When I read a row with MySQLdb I get "None" for the time field. Is there are way to read such time fields with python?

Error: Failed parsing handshake; end byte not precent in buffer (-1) (2 replies)

Beth Hartman — Mon, 17 Dec 2012 18:49:02 +0000

Using Python 3.2 and the MYSQL Connector for Windows, I get this error attempting to connect to a specific database.

Ths code I'm using:

try:
	self.db = mysql.connector.connect(host='localhost', user='root',
	password='blah', database='env')
	self.db.autocommit = True
except mysql.connector.Error as e:
	print("\n\nError: "+e.msg+" ("+str(e.errno)+")")

Note that this exact code will connect just fine to a different dtabase, on the same server, with the same user and password.

I thought it might be because I was attempting to connect to two different databases, so I commented out the first (working) database connection entirely, and I'm still getting this error, so it seems to just have a problem with this specific database.

Also note the typo in the error message: 'precent'

In order to give you a stack trace, I took out the try/except:

 C:\Python32\lib\site-packages\mysql\connector\connection.py in connect(self=, **kwargs={'database': 'env', 'host': 'localhost', 'password': 'blah', 'user': 'root'})
    330 
    331         self.disconnect()
=>  332         self._open_connection()
    333         self._post_connection()
    334     
self = , self._open_connection = >

 C:\Python32\lib\site-packages\mysql\connector\connection.py in _open_connection(self=)
    291         self._socket = self._get_connection()
    292         self._socket.open_connection()
=>  293         self._do_handshake()
    294         if self._client_flags & ClientFlag.SSL:
    295             self._socket.set_ssl(self._ssl['ca'], self._ssl['cert'],
self = , self._do_handshake = >

 C:\Python32\lib\site-packages\mysql\connector\connection.py in _do_handshake(self=)
    121         except Exception as err:
    122             raise errors.InterfaceError(
=>  123                 'Failed parsing handshake; {}'.format(err))
    124 
    125         regex_ver = re.compile(b"^(\d{1,2})\.(\d{1,2})\.(\d{1,3})(.*)")
err undefined

InterfaceError: Failed parsing handshake; end byte not precent in buffer
      args = ()
      errno = -1
      msg = 'Failed parsing handshake; end byte not precent in buffer'
      sqlstate = None
      with_traceback =

executemany statement with a subquery (1 reply)

Robert Koziol — Mon, 22 Oct 2012 13:23:22 +0000

Hi all,

I have to insert many rows into DB with some nested select subquery.

I tried something like that:

eventsFlat = []
eventsFlat.append((event.id1, event.id2, event.league, event.date, event.type, event.subtype, event.period))
...
self.__cursor.executemany(
"""INSERT INTO events (mainevent_id, eventtype_id, eventsubtype_id, period_id) VALUES
((select id from mainevents where first_competitor_id=%s and second_competitor_id=%s and league_id=%s and event_date=%s), %s, %s, %s)""",
eventsFlat)

All I got is the error:

TypeError('not all arguments converted during string formatting',)

Is this possible to use executemany to insert rows but with some select subquery included in?

Kind regards,
Rob

Storing & Retriving Image to displaying (2 replies)

PBL Narasimha Rao — Tue, 30 Oct 2012 08:02:28 +0000

Hi,

I connect to MySql with Python using python-mysql-connector.

I want to know how can i store and retrive the image and display it on the python window.

MySQL Connector/Python vs MySQLdb with DBUtils.PooledDB (3 replies)

Normann Koldrack — Tue, 02 Oct 2012 20:46:55 +0000

Hi,

I did some very simple benchmarks with MySQL Connector/Python, MySQLdb and DBUtils.PooledDB.

1000 selects (1 thread)

Connector + pool : 20,9 sec
MySQLdb + pool : 4,0 sec
Connector : 13,3 sec (new connection every time)
Connector : 2,4 sec (same cursor)
Connector : 4,8 sec (same connection)

13,3 sec with new connections every time, but 20,9 sec with a pool?
How is this possible? Is it normal? Why is MySQLdb + pool 5 times faster?

Get errors when click mySql doc library (1 reply)

Inung Huang — Tue, 20 Nov 2012 20:08:59 +0000

I got an error message when i tried to click on mySQL doc library.
The error message is
"Error during Open Document Library"

"error calling Python module function WbDocLib.openDocLib"

Do I missing the help file?
If I do, where can I donwload and install on MySQL to let Workbench to access it?

Your help and information is great appreciated,

Inung

update statement not working right (no replies)

Vaibhav Chauhan — Wed, 12 Sep 2012 19:33:11 +0000

def batch(corpora,iput):
import pyodbc,nltk,array,re,itertools,file_ip_console
from nltk.tokenize import sent_tokenize
from nltk.tokenize import word_tokenize
from operator import itemgetter
from nltk.corpus import stopwords
from nltk.tag import UnigramTagger
from nltk.corpus import treebank
from nltk.tag import SequentialBackoffTagger
from nltk.corpus import wordnet
from nltk.probability import FreqDist
from nltk.tag import tnt
from nltk.corpus import conll2007
from nltk.tag import AffixTagger
from nltk.corpus import brown
from nltk.corpus import reuters
from nltk.corpus import stopwords

#########################################
# Connect to database #
#########################################

cnxn = pyodbc.connect('Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=information_schema;User=root; Password=1234;Option=3;')
cursor = cnxn.cursor()
cursor.execute("use collegedatabase ;")
cursor.execute("select * from tblalldata;")
cnxn.commit()
def isAcceptableChar(character):
return character not in "~@#$%^&*()_+`1234567890-={}|<>[]\',/\""

if corpora ==1:
train_sents = treebank.tagged_sents()[:6000000]

if corpora ==2:
train_sents = conll2007.tagged_sents()[:6000000]
if corpora ==3:
train_sents = brown.tagged_sents(categories='news')[:6000000]

tagger = UnigramTagger(train_sents)
tnt_tagger = tnt.TnT()
tnt_tagger.train(train_sents)
affix_tagger = AffixTagger(train_sents)

s=[];ji=[];text=[];tokens = [];pos_tagging= [];length_of_pos_tagging=[];nnp_array=[];nnp_array_index_coloumn=[];nnp_array_index_row=[];uniques = [];c= [];add1 = 0;add2 =0;regex = re.compile("\w+\.")
english_stops = set(stopwords.words('english'))

for entry in cursor:
s.append(entry.propernoun_SRNO),ji.append(entry.regular_text)

if iput ==1:

for i in range(0,len(s)):

text.append(filter(isAcceptableChar, ji))
tokens.append([word for word in nltk.regexp_tokenize(text,r"\w+") if word not in english_stops])
pos_tagging.append(tagger.tag(tokens))
length_of_pos_tagging.append(len(pos_tagging))

for j in range(0,length_of_pos_tagging):

if pos_tagging[j][1] == "NNP" or pos_tagging[j][1] == "NP-TL" or pos_tagging[j][1] == "NP$" or pos_tagging[j][1] == "NP" or pos_tagging[j][1] == None or pos_tagging[j][1] == "Unk":

nnp_array.append(list(pos_tagging[j])[0])
nnp_array_index_coloumn.append(i)
nnp_array_index_row.append(j)

starting_index = file_ip_console.sql_var_index
ending_index = starting_index +len(s)

i = 0
for j in range(starting_index,ending_index):
for i in range(0,len(s)):
c.append(nnp_array_index_coloumn.count(i))
add1 = add1 + c[i-1]
add2 = add1 + c
xx = str(iput),str(corpora)
cursor.execute("update tblauto_tagged set tagger_train = ? ,propernoun = ? ,propernoun_ID = ? where propernoun_SRNO = ? ",str(xx),str(nnp_array[add1:add2]),str(c),j )
for item in nnp_array_index_coloumn:
if item not in uniques:
uniques.append(item)
add1=0;add2=0

write_ending_loop_index = open("C:\\Users\\vchauhan\\Dropbox\\Code\\sql_var.txt","w")
write_ending_loop_index.write(str(j))
write_ending_loop_index.close()

cnxn.commit()
return()

########################################################################

The problem with this code is it does not write back into table tblauto_tagged python code does not return any error and I am not so good at programming and database;
########################################################################

MySQL Connector/Python (no replies)

Edwin Desouza — Tue, 07 Aug 2012 19:53:47 +0000

MySQL Connector/Python: v1.0.5 (BETA) (no replies)

Edwin Desouza — Tue, 07 Aug 2012 19:48:46 +0000

MySQL Connector/Python v1.0.5 beta released (no replies)

Geert Vanderkelen — Tue, 07 Aug 2012 18:14:45 +0000

MySQL Connector/Python v1.0.5 beta has been released. The official announcement
can be read here:
http://forums.mysql.com/read.php?3,564274,564274#msg-564274

Downloads:
http://dev.mysql.com/downloads/connector/python/

Please report any bugs or inconsistencies you observe to our Bugs Database:
http://bugs.mysql.com

Blogs:
http://geert.vanderkelen.org/mysql-connector-python-1-0-5-beta/

Enjoy!

create PL/Python Functions (no replies)

Vladyslav Tarasenko — Thu, 05 Jul 2012 20:59:22 +0000

Hello.

Is it possible to use some python function in directly in mysql ?
Or can i write extension with python\cpython for mysql ?
Extension i means something like this - http://www.codeproject.com/Articles/15643/MySQL-User-Defined-Functions
but with python, not with C.

thank you.

PYLOTDB: A Python-MySQL database management and analysis tool (no replies)

Daniel Barnette — Fri, 25 May 2012 22:39:46 +0000

Many readers have asked whether there's Python GUI software for creating,
managing, and analyzing (plotting from) MySQL databases. I have just released such software as completely open source. Source code is available on GitHub as follows:

1. Install Git on your machine from github.com
2. Initialize the target directory with "git init"
3. Download the software using "git pull git://github.com/dwbarne/PYLOTDB"
4. Read README_first.rtf file using Microsoft Word and follow directions.

PYLOTDB consists of two codes, PylotDB and Co-PylotDB. The software allows
users to access either local or remote MySQL servers and easily display
table data in a GUI format.

In PylotDB data can be filtered, and table columns are indexed with
radiobuttons and checkboxes so data can be easily selected for plotting or
statistical analysis. Plotting capability includes X-Y, semi-log, log-log,
Kiviat (radar charts), scatter, and scatter with polynomial curve fits of
the data. Entire databases, selected databases, or selected tables in
selected databases can be backed up and restored as desired. Interfaces are
provided for individual entry edits and additions.

PylotDB's companion software Co-PylotDB is used to send data files to a
user-selected database table. If data files are in YAML format, PylotDB can
automatically extract each entry in the file, expand the database table
with new fields with names taken from the YAML entries, and insert the data
in those fields for plotting and analysis. This is a tremendous time saver
for analysts. This allows the cycle of "data capture to storage to
analysis" to be completed in a matter of minutes.

A powerful feature of PylotDB is the storage buffer where selected data
from any table on any server can be stored and mathematically combined to
generate new data not currently in any accessed table. The new data can
then be plotted along with other data from the currently displayed table.
This allows the user to generate desired data on the fly without the
necessity of modifying any of the stored tables.

PYLOTDB's dependencies include matplotlib, numpy, MySQLdb, Python
MegaWidgets (Pmw), and YAML. All of these dependencies are included in the
download for Windows machines only. They can easily be found on the web for
Mac or Linux machines.

Sample databases are also included to help users get up to speed quickly.

We are using this software in my company for analyzing results from our
computer performance modeling analysis, but it can be used for any type of
analysis where database storage and analysis is desired.

Hope this proves helpful for your readers.