Last active
August 29, 2015 13:56
-
-
Save bchhun/8909903 to your computer and use it in GitHub Desktop.
La magie de SQLAlchemy
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import os | |
import inspect | |
from datetime import datetime | |
from fabric.colors import red, green | |
from sqlite3 import dbapi2 as sqlite | |
from sqlalchemy import create_engine | |
from sqlalchemy.orm import sessionmaker | |
from sqlalchemy.ext.declarative import declarative_base | |
from sqlalchemy import ( | |
Column, Integer, String, DateTime, Date | |
) | |
CURRENT_DIRECTORY = os.path.dirname( | |
inspect.getfile(inspect.currentframe()) | |
) | |
DATABASE_NAME = "users" | |
Base = declarative_base() | |
class User(Base): | |
__tablename__ = "users" | |
id = Column("id", Integer, primary_key = True) | |
nas = Column("nas", Integer) | |
country = Column("country", String(200)) | |
fullname = Column("fullname", String(200)) | |
created_at = Column("created_at", DateTime) | |
birthday = Column("birthday", Date) | |
email = Column("email", String(200)) | |
def connect(debug=False): | |
path = os.path.join(CURRENT_DIRECTORY, "{}.db".format(DATABASE_NAME)) | |
engine = create_engine( | |
'sqlite+pysqlite:///{}'.format(path), | |
module=sqlite, | |
echo=debug | |
) | |
SessionMakerInstance = sessionmaker(bind=engine) | |
return SessionMakerInstance() | |
def init_db(): | |
path = os.path.join(CURRENT_DIRECTORY, "{}.db".format(DATABASE_NAME)) | |
if os.path.isfile(path): | |
print(red(u"La base de données existe déjà...")) | |
print(red(u"Sa création est annulé")) | |
return | |
sqlite.connect(path) | |
engine = create_engine('sqlite:///%s' % path, convert_unicode=True) | |
Base.metadata.create_all(bind=engine) | |
print(green(u"Base de données créé !")) | |
# =========================== | |
import csv | |
from glob import glob | |
from datetime import datetime | |
CSV_PATH = os.path.join("..", "data", "*.csv") | |
csv_files = glob(CSV_PATH) | |
csv_files.reverse() | |
if __name__ == '__main__': | |
init_db() | |
session = connect(debug=True) | |
for csv_file in csv_files: | |
# ouverture du fichier | |
with open(csv_file) as f: | |
csv_content = csv.DictReader(f) | |
session.add_all(( | |
User( | |
nas=row["nas"], | |
country=row["country"], | |
fullname=row["fullname"], | |
# Tue Dec 31 1996 11:35:43 | |
created_at=datetime.strptime( | |
row["created_at"], "%a %b %d %Y %H:%M:%S" | |
).date(), | |
birthday=datetime.strptime( | |
row["birthday"], "%d/%m/%Y" | |
).date(), | |
email=row["email"] | |
) for row in csv_content | |
)) | |
session.commit() |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
country | nas | fullname | created_at | birthday | ||
---|---|---|---|---|---|---|
Maldives | 122 | Tre Kunde | Sat Oct 04 1980 19:18:34 | 23/5/2007 | [email protected] | |
Christmas Island | 123 | Nina Sauer I | Sun Sep 11 2011 07:11:11 | 6/3/2008 | [email protected] | |
Western Sahara | 124 | Jerod O'Reilly | Mon May 24 1993 03:34:03 | 27/5/1982 | [email protected] | |
Maldives | 125 | Aliza Nienow | Fri Nov 29 1996 07:39:35 | 26/8/1993 | [email protected] | |
Sierra Leone | 126 | Litzy Altenwerth MD | Sun Jun 06 1993 04:05:20 | 14/1/1981 | [email protected] | |
Peru | 127 | Willow Bahringer | Tue Sep 15 1992 12:20:10 | 4/12/1988 | [email protected] | |
Andorra | 128 | Ward Bode | Mon Oct 12 1987 17:56:50 | 10/8/1992 | [email protected] | |
Philippines | 129 | Maureen Boehm | Wed Sep 09 1998 00:22:14 | 7/6/1996 | [email protected] | |
United Arab Emirates | 130 | Linda Bins | Sat May 09 1992 06:41:04 | 15/1/1984 | [email protected] | |
San Marino | 131 | Abdiel Hahn | Wed Mar 10 2004 03:07:17 | 22/8/1991 | [email protected] | |
Cape Verde | 132 | Cheyanne Donnelly | Wed Oct 07 1992 17:17:25 | 26/6/1982 | [email protected] | |
Antigua and Barbuda | 133 | Kamron Schiller | Wed Mar 08 2000 01:04:44 | 17/4/1997 | [email protected] | |
Canada | 134 | Katheryn Parker | Sun Mar 30 1980 14:57:16 | 5/2/1988 | [email protected] | |
Bangladesh | 135 | Lisandro Hane Sr. | Wed Oct 27 1993 05:48:02 | 1/11/1990 | [email protected] | |
Barbados | 136 | Ms. Christophe Robel | Thu Mar 22 2007 22:03:45 | 12/3/1996 | [email protected] | |
Australie | 137 | Dr. Hilton Rowe | Fri Oct 22 1982 17:58:23 | 2/1/2011 | [email protected] | |
Germany | 138 | Tavares Considine | Tue Aug 01 1995 20:54:49 | 19/12/2011 | [email protected] | |
Hong Kong SAR China | 139 | Ali Rice | Tue Mar 29 1994 21:24:33 | 25/5/1992 | [email protected] | |
Malta | 140 | Monte Leannon | Tue Dec 21 2010 00:37:19 | 10/8/2010 | [email protected] | |
Bosnia and Herzegovina | 141 | Emanuel Krajcik | Thu Nov 28 1996 09:14:44 | 22/11/2000 | [email protected] | |
Malawi | 142 | Ms. Norma Volkman | Wed Sep 14 1983 18:18:53 | 21/6/1992 | [email protected] | |
Saint Lucia | 143 | Alek Thiel | Thu Oct 11 2007 04:53:34 | 5/3/2013 | [email protected] | |
Trinidad and Tobago | 144 | Valerie Bartell | Wed Dec 17 1997 04:11:10 | 24/8/2007 | [email protected] | |
Tuvalu | 145 | Gloria Bradtke | Mon Dec 09 1991 12:28:28 | 3/8/1988 | [email protected] | |
Neutral Zone | 146 | Laverne Hoeger | Sun Mar 17 1996 19:00:57 | 28/2/1996 | [email protected] | |
New Zealand | 147 | Deondre Pfannerstill | Thu Nov 09 2000 10:44:49 | 28/10/1983 | [email protected] | |
Kyrgyzstan | 148 | Afton Wisozk | Wed Jun 13 2007 13:38:44 | 24/12/1985 | [email protected] | |
Lesotho | 149 | Arturo Leannon | Mon May 23 2005 18:08:51 | 15/4/2012 | [email protected] | |
Kiribati | 150 | Annamae Mosciski | Tue Dec 30 2008 22:20:02 | 19/7/1996 | [email protected] | |
Niger | 151 | Oleta Cummerata | Fri Apr 15 1994 00:54:48 | 8/11/2010 | [email protected] | |
Lithuania | 152 | Evans Zboncak | Thu Feb 10 1983 22:57:11 | 4/3/1997 | [email protected] | |
Brazil | 153 | Mason Hagenes | Wed Aug 09 1989 09:02:59 | 11/7/1992 | [email protected] | |
Burundi | 154 | Kaya Klocko | Sun Aug 12 2001 15:50:55 | 22/6/1996 | [email protected] | |
Falkland Islands | 155 | Asa Hand | Mon Nov 09 1998 12:17:30 | 26/8/2012 | [email protected] | |
Pitcairn Islands | 156 | Georgiana Reynolds | Thu Jul 04 2013 09:56:51 | 1/12/1997 | [email protected] | |
South Georgia and the South Sandwich Islands | 157 | Lyric Doyle | Fri Mar 14 2003 20:17:53 | 8/2/2008 | [email protected] | |
Saint Pierre and Miquelon | 158 | Santina Ward II | Sun May 26 1985 09:51:59 | 28/6/1980 | [email protected] | |
Hungary | 159 | Pascale Barton | Sun Sep 15 1991 09:44:21 | 10/10/2010 | [email protected] | |
Metropolitan France | 160 | Lenny Bogan | Wed Feb 26 1986 17:30:05 | 19/1/1987 | [email protected] | |
Comoros | 161 | Miss Alexzander Goldner | Fri Sep 20 1985 00:46:48 | 12/12/1991 | [email protected] | |
Madagascar | 162 | Lawson Krajcik | Sun Jul 12 1981 01:22:33 | 11/11/2013 | [email protected] | |
Brunei | 163 | Antoinette Rice | Wed Jul 30 1980 01:42:00 | 31/1/1980 | [email protected] | |
Canton and Enderbury Islands | 164 | Macey Stoltenberg | Mon Sep 06 1999 12:41:31 | 29/3/1986 | [email protected] | |
Faroe Islands | 165 | Frederick Botsford | Sat Sep 03 1983 22:43:53 | 12/10/2005 | [email protected] | |
Johnston Island | 166 | Meagan Hoppe | Wed Dec 07 1988 06:38:00 | 1/8/1986 | [email protected] | |
Peru | 167 | Wilton Bosco | Sat Dec 11 1999 07:48:03 | 10/10/2008 | [email protected] | |
Turks and Caicos Islands | 168 | Wayne Effertz | Thu Sep 24 2009 04:48:32 | 24/3/1983 | [email protected] | |
Samoa | 169 | Daryl Kerluke | Sat Oct 25 1986 18:36:18 | 13/8/1999 | [email protected] | |
Uruguay | 170 | Andres Turcotte | Mon Mar 02 2009 04:24:52 | 10/1/2006 | [email protected] | |
Finland | 171 | Aaliyah Prosacco | Thu Jul 05 2001 18:47:50 | 9/3/2009 | [email protected] | |
Midway Islands | 172 | Ryleigh Terry | Sat Jul 31 1982 13:10:20 | 5/4/1983 | [email protected] | |
British Indian Ocean Territory | 173 | Miss Luz Ullrich | Fri Feb 18 1994 01:01:06 | 7/6/1989 | [email protected] | |
Malta | 174 | Liliane Mitchell | Mon Dec 02 1991 22:37:18 | 25/12/2003 | [email protected] | |
Puerto Rico | 175 | Dedrick Ortiz | Sat Sep 27 2003 21:38:49 | 5/12/1995 | [email protected] | |
Gambia | 176 | Durward Emard | Thu Jul 20 2006 13:23:22 | 26/2/2009 | [email protected] | |
Mauritania | 177 | Rogers Skiles | Sat Nov 09 1996 21:55:48 | 1/8/1995 | [email protected] | |
Haiti | 178 | Alexie Reilly | Tue May 25 2010 09:46:23 | 5/4/1989 | [email protected] | |
Argentina | 179 | Ibrahim Steuber | Sat Jun 02 2012 21:25:44 | 11/6/2009 | [email protected] | |
Israel | 180 | Kyleigh Bayer | Sat Apr 22 2006 17:05:39 | 2/8/1995 | [email protected] | |
Cayman Islands | 181 | Lyric Aufderhar | Tue Sep 22 1992 17:25:56 | 30/1/2002 | [email protected] | |
Libya | 182 | Rashad Romaguera | Tue Jul 10 2007 15:06:27 | 13/12/1981 | [email protected] | |
Latvia | 183 | Corbin Gerhold PhD | Sun May 03 2009 11:53:41 | 5/6/2009 | [email protected] | |
British Indian Ocean Territory | 184 | Lois Zieme IV | Fri Nov 03 1989 16:07:23 | 3/8/1996 | [email protected] | |
United Kingdom | 185 | Rosie Bogan PhD | Sun Sep 03 1989 14:57:28 | 7/1/1996 | [email protected] | |
Iraq | 186 | Anissa Gerhold | Thu Mar 25 1999 09:17:32 | 23/10/2006 | [email protected] | |
Saint Lucia | 187 | Garrick Cole | Fri Oct 07 1983 11:20:47 | 10/10/1988 | [email protected] | |
Antarctica | 188 | Clemens Ortiz | Mon May 21 1990 04:12:31 | 24/6/1994 | [email protected] | |
Myanmar [Burma] | 189 | Miss Efrain Tremblay | Fri Nov 19 1982 09:37:49 | 15/11/1984 | [email protected] | |
Jamaica | 190 | Tia Torphy | Thu Dec 23 1993 23:10:50 | 19/8/1980 | [email protected] | |
South Africa | 191 | Isabella Will | Thu Jul 14 1994 18:35:11 | 1/5/1999 | [email protected] | |
Saint Helena | 192 | Justen Hessel | Wed Jun 04 1997 00:28:03 | 23/12/1982 | [email protected] | |
Lebanon | 193 | Miss Rosalee Cassin | Sat Feb 18 2012 03:15:44 | 15/7/1986 | [email protected] | |
Afghanistan | 194 | Edison Beatty | Mon Feb 02 1998 22:54:00 | 16/9/1982 | [email protected] | |
Chile | 195 | Edmund Becker | Thu May 12 1988 17:46:46 | 1/7/2011 | [email protected] | |
French Polynesia | 196 | Zetta Crona | Mon Apr 14 1997 17:12:34 | 23/10/2011 | [email protected] | |
Oman | 197 | Henry Wilderman | Mon Dec 17 1984 09:39:52 | 15/2/1997 | [email protected] | |
Vanuatu | 198 | Chance Stroman | Wed Apr 30 1986 16:05:16 | 14/7/1992 | [email protected] | |
Guyana | 199 | Alvera Reilly | Tue Mar 03 1998 06:30:03 | 4/10/1985 | [email protected] | |
Anguilla | 200 | Keegan Bruen MD | Sat Apr 09 1994 09:25:48 | 15/9/1993 | [email protected] | |
Falkland Islands | 201 | Roy Moen | Sun Aug 24 2003 16:27:44 | 11/10/1981 | [email protected] | |
Pitcairn Islands | 202 | Allison Hansen | Sun Feb 26 2006 06:09:11 | 1/3/1999 | [email protected] | |
Norway | 203 | Mary Macejkovic | Tue Dec 07 1999 07:48:20 | 16/10/1996 | [email protected] | |
Afghanistan | 204 | Mrs. Florian McClure | Wed Sep 30 1981 04:32:15 | 1/4/1996 | [email protected] | |
Iceland | 205 | Ms. Miracle Brown | Sun Nov 14 1982 00:13:18 | 12/5/1986 | [email protected] | |
Saint Lucia | 206 | Ryder Kuhlman I | Fri Aug 16 1985 14:25:21 | 3/12/1997 | [email protected] | |
Israel | 207 | Ena Jaskolski | Thu Dec 03 1998 02:12:18 | 10/8/1988 | [email protected] | |
Ghana | 208 | Maddison McDermott | Fri Oct 21 1988 14:44:03 | 31/1/2000 | [email protected] | |
Guadeloupe | 209 | Marcos Cummerata | Sun Jan 14 1990 07:09:43 | 17/2/1988 | [email protected] | |
Kiribati | 210 | Clint Brakus | Tue May 09 1989 00:34:30 | 24/6/1987 | [email protected] | |
Saint Lucia | 211 | Ambrose Nikolaus | Tue Nov 21 2006 18:52:06 | 4/4/2013 | [email protected] | |
Trinidad and Tobago | 212 | Dr. Desmond Grady | Wed Aug 07 1996 12:58:51 | 5/10/2008 | [email protected] | |
Wake Island | 213 | Ms. Antone Hara | Sun Apr 08 2007 16:04:52 | 25/7/1998 | [email protected] | |
Barbados | 214 | Charley Nienow | Fri Nov 26 1993 16:35:00 | 3/5/1995 | [email protected] | |
Cambodia | 215 | Paige Kozey | Sun Apr 12 1981 13:45:46 | 22/11/2013 | [email protected] | |
India | 216 | Malika Mraz | Tue Oct 13 1981 02:05:36 | 26/11/1992 | [email protected] | |
Zimbabwe | 217 | Dr. Alexandre McGlynn | Mon May 27 1991 19:48:11 | 2/6/1984 | [email protected] | |
Brunei | 218 | Sunny Olson | Sat Nov 19 2011 15:34:19 | 11/4/1989 | [email protected] | |
Bermuda | 219 | Greta Huels | Wed Jan 03 1996 19:40:57 | 11/7/1980 | [email protected] | |
Netherlands | 220 | Audra Ziemann | Sat Jan 07 1995 13:04:54 | 5/12/2006 | [email protected] | |
Dronning Maud Land | 221 | Reggie Zemlak | Wed Oct 29 1980 09:46:34 | 10/1/2011 | [email protected] | |
Falkland Islands | 222 | Fletcher Mayer | Tue Sep 16 1997 11:20:57 | 29/10/2001 | [email protected] | |
Colombia | 223 | Odell Jacobs II | Thu Nov 07 1991 20:01:05 | 21/4/1986 | [email protected] | |
Antarctica | 224 | Maye Koch Jr. | Wed Sep 18 1985 22:06:20 | 18/11/1983 | [email protected] | |
Falkland Islands | 225 | Ms. Jaquelin Wiegand | Mon Dec 21 1987 19:14:54 | 21/3/1992 | [email protected] | |
Algeria | 226 | Mr. Dariana Braun | Mon Jul 16 2012 23:04:37 | 16/2/1982 | [email protected] | |
Antigua and Barbuda | 227 | Gregg Mueller | Sat Mar 05 1994 08:47:00 | 6/9/2006 | [email protected] | |
Tonga | 228 | Malcolm Murray | Thu May 05 1988 20:43:39 | 10/2/1991 | [email protected] | |
Cocos [Keeling] Islands | 229 | Nathanial Hilll DVM | Sat Jan 29 2000 14:53:26 | 17/8/2005 | [email protected] | |
Union of Soviet Socialist Republics | 230 | Art Dickens | Fri Nov 19 2010 11:55:21 | 5/9/2005 | [email protected] | |
Denmark | 231 | William Nicolas Jr. | Mon Sep 03 2012 04:03:39 | 10/6/1989 | [email protected] | |
Brunei | 232 | Holly Reynolds | Sat Nov 24 2012 10:04:45 | 4/12/2006 | [email protected] | |
Nicaragua | 233 | Orlo Kovacek | Wed Feb 06 2013 04:33:49 | 4/10/1983 | [email protected] | |
Swaziland | 234 | Dr. Esther Reichert | Tue Jul 19 1983 02:21:34 | 13/2/2001 | [email protected] | |
Aruba | 235 | Esteban Waelchi | Fri Mar 13 1998 07:46:38 | 25/9/1987 | [email protected] | |
Algeria | 236 | Kara Osinski | Mon Jul 09 2007 11:44:30 | 14/9/1994 | [email protected] | |
China | 237 | Daron Ortiz | Sun Jun 24 2007 20:40:45 | 14/5/1991 | [email protected] | |
Norway | 238 | Mallory Schaefer | Fri May 12 1989 18:16:49 | 18/8/1994 | [email protected] | |
Bermuda | 239 | Alize Heathcote | Thu Nov 07 1996 17:33:58 | 17/7/2000 | [email protected] | |
Tokelau | 240 | Tillman Kerluke I | Fri Jul 29 1994 23:12:02 | 17/4/1990 | [email protected] | |
Svalbard and Jan Mayen | 241 | Yasmin Ledner | Tue Jul 13 1993 10:37:56 | 9/2/1997 | [email protected] | |
Jamaica | 242 | Nestor Armstrong | Sat Nov 09 2013 16:19:03 | 29/9/1995 | [email protected] | |
Qatar | 243 | Americo Wuckert | Fri Oct 01 1999 18:07:01 | 19/3/1990 | [email protected] | |
Isle of Man | 244 | Fleta Rau | Wed Apr 04 2001 10:20:39 | 7/11/1984 | [email protected] | |
Equatorial Guinea | 245 | Mr. Stevie Quitzon | Mon Jan 28 2013 17:32:49 | 28/9/1988 | [email protected] | |
Somalia | 246 | Henri Smitham | Fri Jun 10 1983 19:22:42 | 7/11/1988 | [email protected] | |
Latvia | 247 | Dr. Reina Homenick | Sun Jan 28 1996 12:32:36 | 19/6/1986 | [email protected] | |
Panama Canal Zone | 248 | Verna Murphy | Fri Mar 16 2007 11:46:35 | 14/2/2008 | [email protected] | |
South Korea | 249 | Jeremy Crist DVM | Sat May 21 2011 18:03:51 | 17/7/1983 | [email protected] | |
Lebanon | 250 | Amie Padberg | Tue Apr 19 1994 15:49:36 | 30/4/2012 | [email protected] | |
Falkland Islands | 251 | Sonia Dibbert | Tue Jul 13 1993 18:39:38 | 13/3/2000 | [email protected] | |
Germany | 252 | Landen Emard | Thu May 16 1985 21:43:20 | 30/11/2000 | [email protected] | |
Libya | 253 | Giuseppe Purdy | Sun Aug 14 1988 11:28:26 | 21/10/1982 | [email protected] | |
United States | 254 | Mr. Esteban Thompson | Mon Jun 20 1988 10:44:24 | 3/7/2010 | [email protected] | |
Tunisia | 255 | Eusebio Lehner | Tue Nov 29 1994 15:15:38 | 14/6/2007 | [email protected] | |
Palau | 256 | Isobel Emard | Wed Nov 01 1995 06:22:36 | 28/1/1980 | [email protected] | |
Aruba | 257 | Furman Harann | Tue Sep 25 2001 16:29:27 | 18/11/1998 | [email protected] | |
Australie | 258 | Kelley Considine | Thu May 07 1998 23:20:53 | 10/11/1981 | [email protected] | |
Guam | 259 | Dax Robel | Sun Nov 15 1998 12:34:13 | 21/12/2013 | [email protected] | |
Vatican City | 260 | Dr. Mikel Ortiz | Mon Sep 30 2013 01:06:06 | 11/10/2003 | [email protected] | |
Rwanda | 261 | Mercedes Pollich | Thu Apr 26 2001 04:36:54 | 11/3/1989 | [email protected] | |
Antarctica | 262 | Tavares Bauch | Sun Jul 03 2011 06:10:55 | 18/6/1997 | [email protected] | |
Australie | 263 | Fernando Lind | Mon Oct 31 2011 21:47:10 | 20/8/1982 | [email protected] | |
Maldives | 264 | Patsy Graham | Wed Mar 07 2007 01:25:03 | 12/8/2011 | [email protected] | |
New Zealand | 265 | Marcelle Lynch I | Fri Nov 29 2002 12:26:08 | 4/10/1989 | [email protected] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment