Python-Ref > Databases and Python > Python and MySQL > Adding new data into the database
 
 

<-^^->
Klíčová slova
Moduly
Knihovní funkce

Adding new data into the database

Expand/Shrink
Zdroj: (mysql4-1.py)
  1   import MySQLdb
  2   from MySQLdb.cursors import DictCursor
  3   
  4   # connect
  5   connection = MySQLdb.connect( user="test", passwd="pass123", db="world", host="localhost", cursorclass=DictCursor)
  6   cursor1 = connection.cursor()
  7   
  8   # create the insert command
  9   command = '''INSERT INTO City
 10                 (Name,CountryCode,District,Population)
 11                VALUES
 12                 ("Kladno","CZE","Stredni Cechy",72354);'''
 13   
 14   # put the data into the database
 15   cursor1.execute( command)
 16   city_id = int( cursor1.lastrowid)  # lastrowid get the id of last processed row
 17   print "Kladno has id", city_id
 18   
 19   # check if it works
 20   cursor1.execute( "SELECT Name FROM City WHERE ID=%d;" % city_id)
 21   print cursor1.fetchone()['Name']
 22   cursor1.execute( "SELECT ID FROM City WHERE Name='Kladno';")
 23   city_id2 = cursor1.fetchone()['ID']
 24   print city_id2, city_id2 == city_id
 25   
 26   cursor1.close()
 27   connection.close()
stdout:
Kladno has id 4080
Kladno
4080 True
Doba běhu: 1021.2 ms
Expand/Shrink
Zdroj: (mysql4-2.py)
  1   # encoding: utf-8
  2   import MySQLdb
  3   from MySQLdb.cursors import DictCursor
  4   
  5   # connect
  6   connection = MySQLdb.connect( user="test", passwd="pass123", db="world", host="localhost", cursorclass=DictCursor)
  7   cursor1 = connection.cursor()
  8   
  9   # create the insert command
 10   command = u'''INSERT INTO City
 11                  (Name,CountryCode,District,Population)
 12                 VALUES
 13                  ("%s","CZE","%s",%d);'''
 14   
 15   data = [("Kladno",u"Střední Čechy",72354),
 16           (u"Slaný",u"Střední Čechy",15414),
 17           (u"Oplany",u"Střední Čechy", 59),
 18           ]
 19   
 20   # put the data into the database
 21   for record in data:
 22     cursor1.execute( (command % record).encode('utf-8')) # encode is not needed when encoding is properly set
 23     city_id = int( cursor1.lastrowid)
 24     print ("%s has id %d" % (record[0], city_id)).encode('utf-8')
 25   
 26   # check if it works
 27   cursor1.execute( "SELECT * FROM City WHERE CountryCode='CZE';")
 28   for i in range( cursor1.rowcount):
 29     city = cursor1.fetchone()
 30     print city['ID'], city['Name'], city['District'], city['Population']
 31   
 32   cursor1.close()
 33   connection.close()
stdout:
Kladno has id 4080
Slaný has id 4081
Oplany has id 4082
3339 Praha Hlavní mesto Praha 1181126
3340 Brno Jizní Morava 381862
3341 Ostrava Severní Morava 320041
3342 Plzen Zapadní Cechy 166759
3343 Olomouc Severní Morava 102702
3344 Liberec Severní Cechy 99155
3345 Ceské Budejovice Jizní Cechy 98186
3346 Hradec Králové Východní Cechy 98080
3347 Ústí nad Labem Severní Cechy 95491
3348 Pardubice Východní Cechy 91309
4080 Kladno Střední Čechy 72354
4081 Slaný Střední Čechy 15414
4082 Oplany Střední Čechy 59
Doba běhu: 1362.8 ms
The following code shows a subtle change that triggers MySQLdb's internal escaping mechanism and thus saves us some fuss over escaping and quotation of inserted values.
Expand/Shrink
Zdroj: (mysql4-3.py)
  1   # encoding: utf-8
  2   import MySQLdb
  3   from MySQLdb.cursors import DictCursor
  4   
  5   # connect
  6   connection = MySQLdb.connect( user="test", passwd="pass123", db="world", host="localhost", cursorclass=DictCursor)
  7   cursor1 = connection.cursor()
  8   
  9   # create the insert command
 10   command = u'''INSERT INTO City
 11                  (Name,CountryCode,District,Population)
 12                 VALUES
 13                  (%s,"CZE",%s,%s);''' 
 14   
 15   data = [("Kladno",u"Střední Čechy",72354),
 16           (u"Slaný",u"Střední Čechy",15414),
 17           (u"Oplany",u"Střední Čechy", 59),
 18           ]
 19   
 20   # put the data into the database
 21   for record in data:
 22     cursor1.execute( command, record) # we do not use "%", but simply a second argument 
 23     city_id = int( cursor1.lastrowid)
 24     print ("%s has id %d" % (record[0], city_id)).encode('utf-8')
 25   
 26   # check if it works
 27   cursor1.execute( "SELECT * FROM City WHERE CountryCode='CZE';")
 28   for i in range( cursor1.rowcount):
 29     city = cursor1.fetchone()
 30     print city['ID'], city['Name'], city['District'], city['Population']
 31   
 32   cursor1.close()
 33   connection.close()
stdout:
Kladno has id 4080
Slaný has id 4081
Oplany has id 4082
3339 Praha Hlavní mesto Praha 1181126
3340 Brno Jizní Morava 381862
3341 Ostrava Severní Morava 320041
3342 Plzen Zapadní Cechy 166759
3343 Olomouc Severní Morava 102702
3344 Liberec Severní Cechy 99155
3345 Ceské Budejovice Jizní Cechy 98186
3346 Hradec Králové Východní Cechy 98080
3347 Ústí nad Labem Severní Cechy 95491
3348 Pardubice Východní Cechy 91309
4080 Kladno Střední Čechy 72354
4081 Slaný Střední Čechy 15414
4082 Oplany Střední Čechy 59
Doba běhu: 810.2 ms